SQLite

csn

Don't think I'd ever heard of it. They're talking about making it a default extension for PHP5. Don't think I'd ever use it, even if it is "10-20x faster than PG (7.1.3), and 2x faster than Mysql ".

http://www.hwaci.com/sw/sqlite/

Sxooter

SQLite is to MySQL what MySQL is to Postgresql, kinda. :-)

It's an ultra simple, ultra lightweight flat file handler that lets you read and write data to a flat file in a kinda sorta SQL environment.

My problem with all these SQL wannabes is that people might think they're using a real database and not know the difference.

I prefer to use the GDBM interface, since you KNOW it's not a real database and it's easier than trying to use SQL for flat file work, in my opinion.

csn

Actually, looks like it supports a lot of stuff mysql doesn't ( http://www.hwaci.com/sw/sqlite/omitted.html ) - subqueries, views, partial trigger support, transactions (oh, I guess mysql has those now). But still, there's no:

foreign keys
alter table
grant/revoke (multiple files and file permissions would have to do)

It's looking like it'll definitely be a default extension with PHP5. Still can't really see using it much though.

Sxooter

True, it supports those things, but I think it has issues with parallel access, i.e. it's not a multiple-connection oriented database, you have to do your own locking.

At least that was the impression I got from one of the folks on the PHP list who was familiar with it.

chriskl

That 10-12x slower comment is total rubbish. If you read the article, you'll notice that he takes that statement back because he didn't know how to tune a postgres server.

His current results show PostgreSQL running at the same speed as MySQL.

No-one in their right mind believes that PostgreSQL is slower than MySQL anymore...

Chris

csn

Damn! SIX years later and I'm finally getting around to trying out SQLite! Pretty amazing that after all this time it still doesn't support foreign keys (though there's a workaround with triggers) and alter table only supports add column. It also appears PHP 5.3 promised support for version 3 at least a couple of years ago, and it's STILL not out (PDO supports SQLite3 though). Free GUIs for it are kinda clunky--think I like the Firefox one the best (SQLiteman keeps crashing).

However, after playing with SQLite I think it'll very nicely fill a gap between using text files and an "enterprisey" database. Maybe I'll check out gdbm...doesn't seem like there's a PHP extension for that though.

laserlight

csn wrote:
However, after playing with SQLite I think it'll very nicely fill a gap between using text files and an "enterprisey" database.

D. Richard Hipp has described SQLite as a "replacement for fopen()".

csn wrote:
Pretty amazing that after all this time it still doesn't support foreign keys (though there's a workaround with triggers)

That is probably what I really dislike about SQLite. Other than that, I would say that Sxooter's criticism was and is unjustified for the kind of use cases that SQLite was designed to handle.

Weedpacket

Yeah; SQLite isn't meant to be a substitute for a standalone DBMS, but a way for an application to have its own "inboard" relational database.

It would be silly, for example, if Firefox required MySQL/PostgreSQL/Firebird to be installed, configured and running just so that it could have a database. So it uses SQLite instead (when it's not using that mork thing; there's a refactoring opportunity).

For a web environment, the issue about multiple users is roughly valid: it's the same user, but with multiple concurrent "connections" that have to be managed.

laserlight wrote:
That is probably what I really dislike about SQLite.

Ditto. I had started looking at porting SQLite (long and irrelevant story) but when I noticed this it made the whole idea pointless.

But doesn't the Ruby crowd say that all key constraints should be maintained by the application anyway, and you should use the database as a place to persist your objects?

csn

Well, I think I'm liking SQLite. I really really like how all I need is the CLI or library and a single database file, as opposed to installing and admin'ing a database server on my desktops for single-user apps (which I used to think nothing of in the past!). Besides Firefox, other apps like Aperture use SQLite, and it totally makes sense. SQLite fills this niche nicely, and appears to be more powerful than gdbm, etc.

I don't think I care for the dynamic field types, but then vars in PHP, et. al. are the same way.

On thing that confused me was ROWID. The docs gave me the impression that I wouldn't even need to specify primary IDs. But after further investigation, that aint so if tables are related and use foreign keys. One vacuum can destroy much of the relations when ROWIDs are resequenced.

The INSERT OR REPLACE|IGNORE|ETC statement is interesting. I think I like it--saves a step.