phpwiki-talk

[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

Re: [Phpwiki-talk] ansi sql

[Brian C <br...@be...>]

Hi,

Either your enthusiasm will bring new life to what looks to me to be a 
dying project or it will fall on deaf ears. This list mainly receives 
spam right now and actual questions about phpwiki tend to go unanswered. 
My installation has been broken for months, searching on Google shows 
many people have the same problem, but I cannot get any info on a fix. 
See 
http://sourceforge.net/mailarchive/forum.php?thread_id=8990578&forum_id=4517

I believe my solution is going to be to move to mediawiki, despite the 
extra work this will create for me, because it is possible to get 
support for it. Since Wikipedia uses it there is regular development, 
and its markup format is becoming something of a standard.

If this project does want to remain viable that's one thing it should 
seriously look into: a script to convert phpwiki markup to mediawiki 
markup and then an adoption of mediawiki markup style in a new version.

Good luck to you,

Brian

David Rhodes wrote:
> 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

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/

Re: [Phpwiki-talk] ansi sql

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

Yep, but first I want to check my own logic in our dev enviroment here :)
Shouldn't be too long.

Regards

David

Reini Urban wrote:

>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=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
>>
>>
>>
>>
>>-------------------------------------------------------
>>This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
>>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=7637&alloc_id=16865&op=click
>>_______________________________________________
>>Phpwiki-talk mailing list
>>Php...@li...
>>https://lists.sourceforge.net/lists/listinfo/phpwiki-talk
>>
>>    
>>
>
>
>--
>Reini Urban
>http://xarch.tu-graz.ac.at/home/rurban/
>
>  
>


-- 
David Rhodes,  Network Unit Leader
Information Technology Services Division
Deakin University Waterfront Campus Victoria 3217 Australia
Phone:   03 5227 8912 International: +61 3 52278912
Fax:     03 5227 8866 International: +61 3 5227 8866
E-mail:  dav...@de...
Website: http://www.deakin.edu.au
Deakin University CRICOS Provider Code 00113B (Vic)

Important Notice: The contents of this email transmission, including any attachments, are intended solely for the named addressee and are confidential; any unauthorised use, reproduction or storage of the contents and any attachments is expressly prohibited. If you have received this transmission in error, please delete it and any attachments from your system immediately and advise the sender by return email or telephone. Deakin University does not warrant that this email and any attachments are error or virus free. 

Re: [Phpwiki-talk] ansi sql

[John S. <jst...@gm...>]

On 12/18/05, Reini Urban <ru...@x-...> wrote:
>
> 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.
>
>
Note that David is talking about the stuff in lib/WikiDB/backend/PearDB.php=
,
which I previously ranted about.  We are running Oracle and there are no
overrides to the functions that use it in PearDB_oci8.php.

Yes, David works with me and as he has said is a great supporter of the Wik=
i
concept.  We are both VERY interested in seeing phpwiki continue to develop
and fix all of these niggling problems, particularly as it fits the SOE of
the organisation.

Last time I brought this sql stuff up, I was told that it was the realm of
the PearDB guys and should post /rant over there.  Fair enough, but since
these libraries are part of phpwiki, why should we not fix these and provid=
e
the patches back to them?

If you want, I will try and make the correct additions to PearDB_oci8.php
and test them.  May not get it done before next year though.  Then I can
provide you with the patches for the next release, which I am hoping will b=
e
relatively soon.

BTW, Reini and others are VERY helpful and responsive to problems.  I guess
there just aren't enough developers and supporters to get to everything, an=
d
Reini has enough other stuff to be doing.  Probably my only real criticism
is relying on CVS for patches and fixes.  I am not sure if all of my
suggested patches have made it into CVS yet, so I am loath to rely on it.
Release and bugfix announcments to this list may help alleviate this issue,
but without tracking/knowing what fixes are in or out, I cannot use it to
keep our production wiki up to date.

Regards