[development] SQLite and Drupal 7

[Karoly Negyesi]

Hi,

In case you do not know, SQLite is a one-file database which Drupal 7 supports.

One thing we can do, and this is easy, to ship with Drupal install in
a read-only SQLite database, say, in the Drupal root. This would allow
install.php to become much cleaner and also make install profiles
pretty much regular modules with an access to the whole Drupal system.
This will make Drupal require SQLite, but I ran a poll, I ran a
challenge and seemingly every site that has PHP 5.2 and PDO has SQLite
as well.

Now, there is more. We could SQLite as a sort of SQL cache. We could
put the following tables into SQLite:

a) system
b) registry*
c) menu_router
d) variables
e) some cache tables (caution: SQLite is not that good at concurrent
writing. It works but only up to a certain point as it locks the whole
database.)
f) any other table that's read often and written not-so-often.

The first two needs some security hardening but that's ongoing.

The advantages are somewhat clear: very very early we have access to
these things, in a standard, smooth SQL accessible way. On, say,
registry rebuild or module enable, DBTNG could easily copy the
relevant table from MySQL.

There is a disadvantage, however on every site that uses multiple
webfrontends against the same MySQL database. There are two kinds of
these, high performance sites and development sites. Development sites
can put the SQLite database on a network share -- this is neither
performant nor safe because both Unix and Windows has bugs with shared
FS file locks. But, it will do for development. We could say that for
high performance sites you only change system or the registry when you
deploy and thus the SQLite file is just one more to be deployed.
Otherwise you will end up with a bad situation, where the frontend
that handled the rebuild will have its SQLite databases properly
updated while others wont. Of course, there are other ways. We can
store a "table version number" in a MySQL table for the tables that
sit in SQLite and on every request compare and if necessary, copy the
table from MySQL to SQLite. if we happen to have a setup where you can
serve cached pages from SQlite without touching MySQL then there might
be a slight lag with this method.

Questions are

1) Are we OK with making Drupal rely on SQLite for every request?
2) Do we want "deploy your SQLite table and be done"?
3) Do we want table versions?
4) Should I just shut up and go back into my corner?

Karoly "chx" Negyesi