[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
>