[drupal-devel] Re: SQLite

[Carl McDade]

Negyesi Karoly wrote:
>>I just wanted to let you know that I upgraded my development environment
>>to PHP5.  The reason?  I believe that it is important that Drupal 4.6
>>will work with PHP5.
> 
> 
> Aye. And, as SQLite is default with PHP5, it is now time, to talk about 
> that, too: http://drupal.org/node/13192 
> 
> As there was no interest, I've stopped the development. ALTER TABLE is 
> still missing but as I have written, it is done by someone else, just need 
> copy-paste. Most importantly, some decisions are missing.
> 
> If you take a look at 
> http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/chx/database.sqlite.inc?rev=1.2
> you'll see some strange looking code, especially in the _db_query fuction.
> 
> Problem is, that if you SELECT table1.field1 FROM table1,table2 then the 
> result column is called table1.field1 -- to get what you expect you need 
> SELECT table1.field1 AS field1 FROM table1,table2. The problem is even 
> better with SELECT table1.* FROM table1,table2 because you need to extract 
> that * to be able to alias.
> 
> There are a few solutions. One, you may do a CREATE TEMPORARY VIEW on every 
> SELECT statement. After that, you are SELECTing from one VIEW, so this 
> effect does hit you. Alas, this is a bit slow. Not too much, but still.
> 
> Second, you may write some hellish-looking regexps to rewrite your queries. 
> Problem is, you will need to debug those. And the expanding part is far 
> from trivial. This solution is the best if you have more than a handful of 
> records, 'cos the preparation time in PHP is much smaller than the SQLite 
> execution time.
> 
> Third is, you rewrite every query in Drupal core to be properly aliased...
> 
> Ah, and one more SQLite bug: if there are aliased JOINed tables, forget 
> USING clause. Thankfully, this appeared only once in Drupal core when I 
> was coding this.
> 
> Regards
> 
> Karoly Negyesi
> 

Hi,

This is mostly a test of the mailing system. But you might think about 
parsing those unsupported queries into an array and drop/write new 
tables. We this is done in textdb api a flat file db system project that 
I work on occasionally. You can find it on source forge. Take a look at 
the SQL parser. That parser was borrowed by the Mambo lite project also 
which uses flat files. Using it as a patch to SQLite should work.

Carl McDade