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

[Sami Barakat]

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