Re: [Phpwiki-talk] ansi sql

[Reini U. <ru...@x-...>]

Do you want to provide a patch against current CVS?
I'm still busy with private stuff until Thursday or so.

Converting SELECT IF() to CASE() is not on my high priorities,
but we already did it for the other queries so it will appear in 1.3.12.


2005/12/16, David Rhodes <dr...@de...>:
> First, before I start, I am dying to get into this wiki in a large way.
> It makes documentation so accessible.
> There are several impediments though that could be changed to improve
> it's portability, mainly with the focus of the sql away from mysql
> functions, and more to the ansi standard.
>
> For example, can I suggest we replace SELECT IF(...) in SELECTS with
> more non-mysql (and more ansi based) functionality.
>
> As a case in point (pardon the pun), for the page locking function
> rather then
>
> (SELECT IF(linkee.pagename,1,0) as result FROM phpwiki_link,
> phpwiki_page linker, phpwiki_page linkee, phpwiki_nonempty WHERE
> linkfrom=3Dlinker.id AND linkto=3Dlinkee.id AND linker.pagename=3D'XXX-YY=
YY'
> AND linkee.pagename=3D'ModeratedPage' LIMIT 1
>
> it would be :
>
> SELECT
> case
> when linkee.pagename <> 0
> and linkee.pagename <> NULL
> then 1 else 0
> end
> as result
> FROM phpwiki_link, phpwiki_page linker, phpwiki_page linkee,
> phpwiki_nonempty
> WHERE linkfrom=3Dlinker.id
> AND linkto=3Dlinkee.id
> AND linker.pagename=3D'XXXX-YYYY'
> AND linkee.pagename=3D'ModeratedPage'
> and rownum < 2
>
> As for the LIMIT,... unfortuately I can see this is not so easy as mysql
> doesn't support the ansi row_number syntax. Instead I guess you could
> support it with a DBI type check (if DBI is oracle then use row_number,
> if DBI is mysql then use limit, etc), which then selects the correct
> rows return syntax.
>
> These are not major changes, but will make it much more portable, and
> increase it's user base if that is important to you...
>
> Thoughts?
>
> (yep, I am also sure the case can be further refined. :)
>
> Regards
>
> David
>
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc. Do you grep through log fi=
les
> for problems?  Stop!  Download the new AJAX search engine that makes
> searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
> http://ads.osdn.com/?ad_id=3D7637&alloc_id=3D16865&op=3Dclick
> _______________________________________________
> Phpwiki-talk mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwiki-talk
>


--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/