SQLite and Drupal 7
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
On Monday 02 February 2009 10:14:02 pm Karoly Negyesi wrote:
Questions are
1) Are we OK with making Drupal rely on SQLite for every request?
No.
2) Do we want "deploy your SQLite table and be done"?
I don't get what this means exactly, so I will say no. :-)
3) Do we want table versions?
Seems like a lot of extra tracking we don't want to have to deal with.
4) Should I just shut up and go back into my corner?
Of course not. :-P For those playing our home game, the approach that I have been envisioning here has been to, as chx alludes to above, introduce a "clone table" operation within the DB layer. The implementation shouldn't be overly complex; it's just a select, multi-insert, delete, execute multi-insert. (Give or take some performance tuning.) The race condition window would be quite small. Then on certain operations, particularly things like menu rebuild or registry rebuild, after the main DB table is rebuilt we check if there is a "system" target defined and if so, run the clone table operation from the "default" target to the "system" target. (Probably that would be checked within the clone method on the database layer itself, which internally becomes a no-op if the target is not defined.) The "system" target then *need not be SQLite*. It could be any supported database, on whatever system. Optimize your site as needed. We then set queries that rely just on those tables to use the the "system" target. If it's defined, it gets used. If not, the default database still has all of the data so the query falls back to that and everything keeps working. -- Larry Garfield larry@garfieldtech.com
On Monday 02 February 2009 10:39:10 pm Larry Garfield wrote:
On Monday 02 February 2009 10:14:02 pm Karoly Negyesi wrote:
Questions are
1) Are we OK with making Drupal rely on SQLite for every request?
No.
2) Do we want "deploy your SQLite table and be done"?
I don't get what this means exactly, so I will say no. :-)
OK, on further review I get this. The idea would be to require uploading the sqlite file to a live server from a dev server at the same time you upload your main database dump (or however you do it). That would be a required part of site migration. Put me in the -1 column there. -- Larry Garfield larry@garfieldtech.com
On Mon, 2009-02-02 at 22:39 -0600, Larry Garfield wrote:
4) Should I just shut up and go back into my corner?
Of course not. :-P
Heartily agreed. Whether or not we adopt chx's suggestions, it is an idea worthy of discussion. Having a read-only initial conditions database in SQLite also offers us the potential for an administrative "reset entire site to defaults" option, which could reset all settings but not delete nodes or users (or could offer the admin the option of these two things). This would be a great last-resort recovery option for confused novices, and it would be a fine testing aid for us developers. I am not yet ready to say +1 to adopting chx's suggestion, but I'm ready to say +1 for discussing it seriously. Scott -- Syscrusher <syscrusher@4th.com>
On Tue, 03 Feb 2009 10:09:11 -0500, Syscrusher <syscrusher@4th.com> wrote:
On Mon, 2009-02-02 at 22:39 -0600, Larry Garfield wrote:
4) Should I just shut up and go back into my corner?
Of course not. :-P
Heartily agreed. Whether or not we adopt chx's suggestions, it is an idea worthy of discussion.
Having a read-only initial conditions database in SQLite also offers us the potential for an administrative "reset entire site to defaults" option, which could reset all settings but not delete nodes or users (or could offer the admin the option of these two things). This would be a great last-resort recovery option for confused novices, and it would be a fine testing aid for us developers.
I am not yet ready to say +1 to adopting chx's suggestion, but I'm ready to say +1 for discussing it seriously.
Scott
To clarify, I am not against an SQLite DB as a "Safe mode" fallback, or as an install mechanism. Those are both definitely worth considering. It's making a secondary DB required for normal operation that I do not believe is a good idea. --Larry Garfield
A thought as well -- install profiles could ship with an sqllite db with all the customized site configuration. Then as part of the regular installation, the info from that db would be copied to the regular mysql db. Kyle Research Assistant eBusiness Center @ BYU kyle.mathews2000.com/blog On Tue, Feb 3, 2009 at 11:29 AM, Larry Garfield <larry@garfieldtech.com>wrote:
On Tue, 03 Feb 2009 10:09:11 -0500, Syscrusher <syscrusher@4th.com> wrote:
On Mon, 2009-02-02 at 22:39 -0600, Larry Garfield wrote:
4) Should I just shut up and go back into my corner?
Of course not. :-P
Heartily agreed. Whether or not we adopt chx's suggestions, it is an idea worthy of discussion.
Having a read-only initial conditions database in SQLite also offers us the potential for an administrative "reset entire site to defaults" option, which could reset all settings but not delete nodes or users (or could offer the admin the option of these two things). This would be a great last-resort recovery option for confused novices, and it would be a fine testing aid for us developers.
I am not yet ready to say +1 to adopting chx's suggestion, but I'm ready to say +1 for discussing it seriously.
Scott
To clarify, I am not against an SQLite DB as a "Safe mode" fallback, or as an install mechanism. Those are both definitely worth considering. It's making a secondary DB required for normal operation that I do not believe is a good idea.
--Larry Garfield
To me, this creates as many problems as it solves. Not a fan at this time.
Deploying, maintaining and developing in my environment (100 sites sharing 3 version of source code, load-balanced front end, LDAP, Jabber, etc.) is complex enough. Adding this makes it quite a bit more complex. Depending on SQLite for each request is almost a certain nail in the coffin. Very opposed to that idea. Installing from or falling back to an install "profile/config" in SQLite -- now that's doable. ..chris On Tue, Feb 3, 2009 at 9:12 AM, Moshe Weitzman <weitzman@tejasa.com> wrote:
To me, this creates as many problems as it solves. Not a fan at this time.
Karoly Negyesi wrote:
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.
I don't think it's a good idea to add such a requirement for something you only use at install time.
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. <snip>
I've been managing a few Drupal enterprise sites and this sounds like a nightmare to me. People who have a dedicated database server and an highly optimized setup don't want to add extra overhead and make things more complicated with a database in a file. You won't find sqlite support in properly configured servers for high performance sites. And another example: in Debian PHP5 doesn't have sqlite support by default while it has pdo (you have to install a separate package for sqlite support). So I don't think it's that safe to say that everybody with pdo support also has sqlite... Luc
On Feb 3, 2009, at 12:36 PM, Luc Stroobant wrote:
Karoly Negyesi wrote:
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.
I don't think it's a good idea to add such a requirement for something you only use at install time.
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. <snip>
I've been managing a few Drupal enterprise sites and this sounds like a nightmare to me. People who have a dedicated database server and an highly optimized setup don't want to add extra overhead and make things more complicated with a database in a file. You won't find sqlite support in properly configured servers for high performance sites.
I think this functionality may be disabled, but for most it's a performance boost.
And another example: in Debian PHP5 doesn't have sqlite support by default while it has pdo (you have to install a separate package for sqlite support). So I don't think it's that safe to say that everybody with pdo support also has sqlite...
Luc
On Mon, 2009-02-02 at 20:14 -0800, Karoly Negyesi wrote:
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.
This is not a viable solution for content heavy sites such as newpapers and radio stations. If I understand it correctly it would involve pausing the entire content creation staff while development pushed the new db file live. It would also entail having site admins, that might not be administrators required to either know what was a safe change in a clustered environment and what wasn't, or attempting to restrict the access of site-admins in complex ways. I'm not shut off to the idea, but I don't see it working well in a clustered environment. -Sam Tresler
chx: Was your basic idea to isolate the more static parts of the database from the more active parts? It strikes me that could be done without SQLite. On Tue, Feb 3, 2009 at 4:32 PM, Sam Tresler <sam@treslerdesigns.com> wrote:
On Mon, 2009-02-02 at 20:14 -0800, Karoly Negyesi wrote:
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.
This is not a viable solution for content heavy sites such as newpapers and radio stations. If I understand it correctly it would involve pausing the entire content creation staff while development pushed the new db file live.
It would also entail having site admins, that might not be administrators required to either know what was a safe change in a clustered environment and what wasn't, or attempting to restrict the access of site-admins in complex ways.
I'm not shut off to the idea, but I don't see it working well in a clustered environment.
-Sam Tresler
participants (10)
-
Chris Johnson -
Dmitri Gaskin -
Earl Dunovant -
Karoly Negyesi -
Kyle Mathews -
Larry Garfield -
Luc Stroobant -
Moshe Weitzman -
Sam Tresler -
Syscrusher