Re: [Phpwiki-talk] SQLite database backend problems

[Matthew P. <mp...@he...>]

On Wed, Mar 17, 2004 at 02:53:01PM +0100, Reini Urban wrote:
> Matthew Palmer schrieb:
> >I'm working on an SQLite backend helper for PHPWiki, so that I don't have 
> >to
> >use BDB (ugh!), flat file, or do nassssty setup on a real SQL engine. 
> >However, it wouldn't be an SQL database without foibles, and I'm getting
> >them by the truckload.
> 
> Do you really want it now, and not wait for the official PHP and PearDB 
> + ADODB support?

Timeframe on the new DB system?  I couldn't find anything on the SF site
about it.  I know it's been discussed here before, but was anything planned
re: release date?

What exactly is meant by "official PHP and PearDB + ADODB support", anyway? 
Surely one would be enough?

> >If someone with a smattering more experience at hacking PHPWiki could try
> >out the sqlite.php backend stub and the sqlite.sql schema at
> >http://www.hezmatt.org/~mpalmer/sqlite-phpwiki and try and work out what's
> >*really* going on behind the scenes, I'd be grateful.  I've been banging my
> >head against these problems for hours now, without getting anywhere useful
> >(lots of dead ends and useless tracebacks, but that's about it).
> 
> Ok, I'll have a look, but it's not top-priority.

I was planning on going to bed after making my plea, but decided to give it
one more shot.  As always, you find the bug after you've made the public
announcement that you're too dumb to find it.  <grin>

The problem is ambiguous SQL and lazy client library authors.  "SELECT
foo.bar, baz.wombat" is inconsistent as to what the field names will be on a
DB_FETCHMODE_ASSOC.  MySQL (and I presume PgSQL) will return just the field
name - potentially losing information.  SQLite goes the other way, and
returns table.field instead, so when the iterator goes looking for the
'pagename' index in a returned row (backend/PearDB.php:~797) it bombs on
SQLite, because the field name there is linkee.pagename (or linker.pagename,
but you get the idea).

The solution is to fully qualify all field names when operating in a query
environment with ambiguous field names (which should be SOP anyway).  So,
instead of coding "SELECT $want.*" in backend/PearDB.php:~412, you write
"SELECT $want.id as id, $want.pagename as pagename" and so on.

I count five places in each of PearDB.php and ADODB.php where the .* problem
needs to be fixed, and there's probably a bunch of places which are using
ambiguous names without the all field catcher.  A fix will be in the next
Debian PHPWiki release, at least, and if anyone thinks it's useful, I'll put
the patch into the SF patch tracker.


-- 
"People aren't smart enough to write thread-safe code."
		-- Rasmus Lehdorf, LCA 2004