[Phpwiki-talk] ansi sql

[David R. <dr...@de...>]

Hi,

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=linker.id AND linkto=linkee.id AND linker.pagename='XXX-YYYY' 
AND linkee.pagename='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=linker.id
AND linkto=linkee.id
AND linker.pagename='XXXX-YYYY'
AND linkee.pagename='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