[geeklog-devel] Fwd: MSSQL Class - SQL Coding

[Randy Kolenko]

In the long run:

http://www.geeklog.net/filemgmt/index.php?id=757
Write new plugins with a database abstraction layer.


Failing that, writing simple standard/portable sql should probably be sufficient for 95% of the plugins out there.

We also have the ability to do an array with the key as the db layer
$sql['mssql'] 
$sql['mysql'] to differentiate different sql statements -- so this will have to be checked in to for improvements when postgres goes into play.

-randy




> -----Original Message-----

> From: Sami Barakat [mailto:furiousdog at gmail.com] 

> Sent: May-29-09 8:51 AM

> To: Geeklog Development

> Subject: Re: [geeklog-devel] Fwd: MSSQL Class - SQL Coding

> 

> 

> Just a rather random idea that poped into my head (but will 

> probably be too hard to implement....)

> 

> If you are wanting to add support for various databases why 

> not take the same approach as support for languages. Say for 

> instance the english language file has all the strings set to 

> variable which are then used in the code. When wanting to 

> make a translation you just copy this file and start 

> translating. The core will then pick up the new language file 

> and use that. The same may be possible for sql strings. Then 

> when support is added for a new database all that needs to be 

> done is translate the file to the required syntax for that 

> database and create the database class. Is this even possible?

> 

> Geeklog is probably too far down the line to even try to 

> implement this as its a very large change not only to the 

> core but to all the plugins. But just an idea...

> 

> Sami

> 

> 2009/5/28 ȘϮⱥȵіṥʟɑ√ <yankees26an at gmail.com>:

> > Hey,

> >

> > Just of the top of my head, a nice thing to do would be to pass the 

> > sql to dbError(). If you don't, then pgSQL will have to run 

> an extra 

> > function call to sniff out the last execution query, which 

> wastes some 

> > precious cycles. Also you should preferably use timestamp 

> instead of 

> > datetime because timestamp is ansi sql, while datetime is MySQL's 

> > little invention. :P Those two things arent critical, but 

> the next one 

> > is, which has been mentioned.. Avoid any variations of REPLACE like 

> > the plague. Seriously :P It's only supported by MySQL, and while it 

> > may make MySQL queries a hair faster, it will make msssql and pgsql 

> > queries alot slower because they dont support it.

> >

> > Your best bet is to just code ANSI SQL compliant code. If 

> you've only 

> > been used to Mysql, it can be very tempting to cheat, but 

> it will pay 

> > off in the end ;) On Thu, May 28, 2009 at 4:45 PM, Dirk Haun 

> > <dirk at haun-online.de> wrote:

> >>

> >> I dug out Randy's old post (below) for Tim, who asked for 

> advice on 

> >> writing DBMS-agnostic SQL requests.

> >>

> >> Shouldn't this be on the wiki? It could serve as the basis for a 

> >> "guide on writing SQL requests", to which Stan would 

> surely want to 

> >> add a few Postgres Gotchas ...

> >>

> >> ---------------- Anfang Weiterleitung ----------------

> >> Betreff: [geeklog-devel] MSSQL Class - SQL Coding

> >> Gesendet: Donnerstag, 23. März 2006 8:53 Uhr

> >> Von: Randy Kolenko <randy at nextide.ca>

> >> An: geeklog-devel at lists.geeklog.net

> >>

> >>

> >> "GOTCHAs" within GL and plugin SQL coding:

> >>

> >> - Stray away from DB_save.  Use the appropritate Update or Insert 

> >> statements as necessary.  Although this works in the MSSQL 

> class, its 

> >> not a standard SQL call.

> >>

> >> -Avoid REPLACE INTO at all costs.  While DB_save approximates this 

> >> functionality, it only uses one primary key to match the incoming 

> >> columns and data against rather than ALL incoming primary key 

> >> columns.  Just use an update or delete + insert combination to 

> >> perform the REPLACE INTO functionality.

> >>

> >> -While very handy, LIMIT is not a standard sql statement.  

However a 

> >> statement such as :

> >>      select * from table LIMIT 1

> >> will be translated into:

> >>      selet TOP 1 * from table

> >> And will not incurr any in-code overhead to approximate the limit 

> >> command.

> >>

> >> LIMIT-ing your result sets like this:

> >>     select * from table LIMIT 100,10

> >> to pick off the 100th to 109th rows is absolutelly not 

> supported by 

> >> sql server and there is no equivalent.  While handy for 

> paging, the 

> >> LIMIT approximation for this scenario is handled in the 

> MSSQL class 

> >> code and thus may not perform as quickly on extremely LARGE result 

> >> sets.

> >>

> >> -Ensure all selected columns show up in the group by 

> clause - there 

> >> are a few instances of missing columns in group by 

> clauses.  Mysql is 

> >> a little more forgiving than SQL server is.

> >>

> >> - Note that '' (single quotes with nothing between them) does not 

> >> represent NULL in SQL server. The triggers I've implemented are to 

> >> cleanse any data that appears to be null and replace the empty 

> >> varchar data with NULL.

> >>  This

> >> way, when PHP tests empty() on a column, it returns empty properly.

> >>

> >>

> >> _______________________________________________

> >> geeklog-devel mailing list

> >> geeklog-devel at lists.geeklog.net 

> >> http://lists.geeklog.net/listinfo/geeklog-devel

> >>

> >>

> >> ----------------- Ende Weiterleitung -----------------

> >>

> >> --

> >> http://www.geeklog.net/

> >> http://geeklog.info/

> >>

> >> _______________________________________________

> >> geeklog-devel mailing list

> >> geeklog-devel at lists.geeklog.net 

> >> http://eight.pairlist.net/mailman/listinfo/geeklog-devel

> >

> >

> >

> > --

> > Warmly,

> >

> > Stanislav

> >

> >

> >

> > _______________________________________________

> > geeklog-devel mailing list

> > geeklog-devel at lists.geeklog.net 

> > http://eight.pairlist.net/mailman/listinfo/geeklog-devel

> >

> >

> _______________________________________________

> geeklog-devel mailing list

> geeklog-devel at lists.geeklog.net 

> http://eight.pairlist.net/mailman/listinfo/geeklog-devel

>