SQLite and Drupal 7 -- third coming
Hi, Every morning, I am distilling the discussion. *) SQLite is not supported. If you have Drupal 5.2 and PDO which are already requirements then you have SQLite unless it's explicitly disabled and so far noone was able to answer the challenge at http://drupal4hu.com/node/177 . It's embedded into PHP so "supporting" it is like supporting the SimpleXML extension. Unlike MySQL where you need to run a separate server, here we have an embedded SQL engine right inside PDO. Also note that if we want (and I think we want) easier and more powerful install profiles then we will require SQLite for the install anyways. *) Deployment and rebuild. As it stands, now I believe that Drupal should be able to boot up enough to be able to copy the tables from MySQL to SQLite. I am not saying I am expecting it to be functional. After the tables are copied back into SQLite, it should either restart the bootstrap process or reload at the same URL. This solves deployment. *) Why do I want this? Once again, nicer code, especially for caching. Various pluggable subsystems (cache, session and path being the biggest wins here) will just work instead of ugly hacking. But in general, instead of trying to figure out during bootstrap coding "do we have a DB connection yet" the answer is always yes. Kind regards NK
Hi, I think the main issue here is that chx is viewing things from the side of a Drupal Core developer while others are seeing it from the side of a user. The users don't particularly care that the code that bootstraps Drupal is nice (although they propably should!) so are mostly concerned about what's in it for them... 1. If I am a new Drupal user that just wants to install the thing and do usual stuff will this SQLite issue ever cross my radar? Will I notice it exists? (let's assume that my host setup supports it and I don't have to change anything myself) 2. If I am a web developer putting together Drupal sites everyday will this help me put them together sites any faster? 3. Will it help me save a site if a user messes things up? Will it help with upgrading to a new version of Drupal? 4. Will I need to touch it when I add or remove a module? If this affects ends users (in that it makes things more complex) then it needs to be seriously considered and it has a lot going against it. If it does not affect end users but makes the core code nicer and easier to handle then it has a lot going for it. One last note, anything that could make deploying a Drupal profile easier with the profile being as completely set-up as possible would be a huge bonus for Drupal. Best, Ronald Karoly Negyesi wrote:
Hi,
Every morning, I am distilling the discussion.
*) SQLite is not supported. If you have Drupal 5.2 and PDO which are already requirements then you have SQLite unless it's explicitly disabled and so far noone was able to answer the challenge at http://drupal4hu.com/node/177 . It's embedded into PHP so "supporting" it is like supporting the SimpleXML extension. Unlike MySQL where you need to run a separate server, here we have an embedded SQL engine right inside PDO. Also note that if we want (and I think we want) easier and more powerful install profiles then we will require SQLite for the install anyways.
*) Deployment and rebuild. As it stands, now I believe that Drupal should be able to boot up enough to be able to copy the tables from MySQL to SQLite. I am not saying I am expecting it to be functional. After the tables are copied back into SQLite, it should either restart the bootstrap process or reload at the same URL. This solves deployment.
*) Why do I want this? Once again, nicer code, especially for caching. Various pluggable subsystems (cache, session and path being the biggest wins here) will just work instead of ugly hacking. But in general, instead of trying to figure out during bootstrap coding "do we have a DB connection yet" the answer is always yes.
Kind regards
NK
Hi all, I'm all with chx on this one, supposing we manage to actually make it works. Main advantages: - the registry is local, we don't need to retrieve a big blob of cached data at each request. This is far more efficient for cluster scenarios, (by the way, the same could/should apply to the locale cache, which is currently nearly a 1MB blob of data, fetch from the database server at *each* request) - the bootstrap process will be simpler and more efficient. Currently, we can't guarantee that users or developpers will not see difficult to debug error messages or exceptions. Our autoloader is just not robust enough, because it has to fetch registry data from the database - hence initialize the database) before anything else. - we will be able to design database less scenarios. Drupal available right out of the box, with advanced installation profiles. That could lower a lot the barrier to entry of the software. Now to more answers: On Wed, Feb 4, 2009 at 4:32 PM, Ronald Ashri <ronald@istos.it> wrote:
1. If I am a new Drupal user that just wants to install the thing and do usual stuff will this SQLite issue ever cross my radar? Will I notice it exists? (let's assume that my host setup supports it and I don't have to change anything myself)
No. That should be perfectly transparent to you. Just properly configure the files directory and everything will be done for you.
2. If I am a web developer putting together Drupal sites everyday will this help me put them together sites any faster?
Yes. See above. 3. Will it help me save a site if a user messes things up? Will it help with
upgrading to a new version of Drupal?
Not really.
4. Will I need to touch it when I add or remove a module?
No. The process should be completely transparent to you. Damien Tournoud
From a security point of view, any time the web server process has write access to any directory or file, it makes me nervous. For this SQLite scheme to work, obviously the web server process will have to be able to create and update the file in which the SQLite database resides. This seems like it provides another possible vector for exploits. Tell me how we will protect against such attacks.
..chris On Wed, Feb 4, 2009 at 10:02 AM, Damien Tournoud <damz@prealable.org> wrote:
Hi all,
I'm all with chx on this one, supposing we manage to actually make it works. Main advantages:
- the registry is local, we don't need to retrieve a big blob of cached data at each request. This is far more efficient for cluster scenarios, (by the way, the same could/should apply to the locale cache, which is currently nearly a 1MB blob of data, fetch from the database server at *each* request)
- the bootstrap process will be simpler and more efficient. Currently, we can't guarantee that users or developpers will not see difficult to debug error messages or exceptions. Our autoloader is just not robust enough, because it has to fetch registry data from the database - hence initialize the database) before anything else.
- we will be able to design database less scenarios. Drupal available right out of the box, with advanced installation profiles. That could lower a lot the barrier to entry of the software.
Now to more answers:
On Wed, Feb 4, 2009 at 4:32 PM, Ronald Ashri <ronald@istos.it> wrote:
1. If I am a new Drupal user that just wants to install the thing and do usual stuff will this SQLite issue ever cross my radar? Will I notice it exists? (let's assume that my host setup supports it and I don't have to change anything myself)
No. That should be perfectly transparent to you. Just properly configure the files directory and everything will be done for you.
2. If I am a web developer putting together Drupal sites everyday will this help me put them together sites any faster?
Yes. See above.
3. Will it help me save a site if a user messes things up? Will it help with upgrading to a new version of Drupal?
Not really.
4. Will I need to touch it when I add or remove a module?
No. The process should be completely transparent to you.
Damien Tournoud
From a security point of view, any time the web server process has write access to any directory or file, it makes me nervous. For this SQLite scheme to work, obviously the web server process will have to be able to create and update the file in which the SQLite database resides. This seems like it provides another possible vector for exploits. Tell me how we will protect against such attacks.
This brings up a good point, I believe. One potential avenue would be a webuser rewriting the file to point to a different directory for, say, the user.module, and then capturing all entered passwords in his own custom code. This isn't on the same mentality/vein as "well, we have to *trust* that the MySQL database is secure too, don't we?", because databases almost always get their own username and password - but the Apache webserver is most often run as a single user, without suexec'ing. -- Morbus Iff ( *splutch* ... /me respawns ) Technical: http://www.oreillynet.com/pub/au/779 Enjoy: http://www.disobey.com/ and http://www.videounderbelly.com/ aim: akaMorbus / skype: morbusiff / icq: 2927491 / jabber.org: morbus
On 4-Feb-09, at 12:00 PM, Morbus Iff wrote:
This isn't on the same mentality/vein as "well, we have to *trust* that the MySQL database is secure too, don't we?", because databases almost always get their own username and password - but the Apache webserver is most often run as a single user, without suexec'ing.
Since the web server can read settings.php, presumably the SQL DB password could be extracted as well. So the same user module attack could be executed regardless of SQLite? --Andrew
On Wed, Feb 4, 2009 at 5:23 PM, Chris Johnson <cxjohnson@gmail.com> wrote:
From a security point of view, any time the web server process has write access to any directory or file, it makes me nervous. For this SQLite scheme to work, obviously the web server process will have to be able to create and update the file in which the SQLite database resides. This seems like it provides another possible vector for exploits. Tell me how we will protect against such attacks.
That's an excellent point. It has been chx' concern from the beginning. If you read http://drupal.org/node/367660, you will see that a whitelist of paths retrieved from the registry has been made just for that. Damien Tournoud
From a security point of view, any time the web server process has write access to any directory or file, it makes me nervous. For this SQLite scheme to work, obviously the web server process will have to be able to create and update the file in which the SQLite database resides. This seems like it provides another possible vector for exploits. Tell me how we will protect against such attacks.
I find your lack of faith disturbing :P For two months I am aware http://drupal.org/node/332303#comment-1145308 of this problem and I have only brought the question of SQLite to the greater public now that I have http://drupal.org/node/367660 a solution.
Heh. I have plenty of faith in your, Karoly. I just like to make sure everything is covered. Humans sometimes overlook obvious things. :-) I like the whitelist idea for paths. Nice. ..chris On Wed, Feb 4, 2009 at 11:55 AM, Karoly Negyesi <karoly@negyesi.net> wrote:
From a security point of view, any time the web server process has write access to any directory or file, it makes me nervous. For this SQLite scheme to work, obviously the web server process will have to be able to create and update the file in which the SQLite database resides. This seems like it provides another possible vector for exploits. Tell me how we will protect against such attacks.
I find your lack of faith disturbing :P For two months I am aware http://drupal.org/node/332303#comment-1145308 of this problem and I have only brought the question of SQLite to the greater public now that I have http://drupal.org/node/367660 a solution.
My concerns are extra dependencies, complexity of deployment, making it mandatory, ...etc. But now I am starting to see the benefits of this now, having been a bit skeptical at first. On Wed, Feb 4, 2009 at 10:12 AM, Karoly Negyesi <karoly@negyesi.net> wrote:
If you have Drupal 5.2 and PDO which are already requirements then you have
SQLite unless it's explicitly disabled You mean *PHP* 5.2, right? Which is the minimum for Drupal 7.x. -- Khalid M. Baheyeldin 2bits.com, Inc. http://2bits.com Drupal optimization, development, customization and consulting. Simplicity is prerequisite for reliability. -- Edsger W.Dijkstra Simplicity is the ultimate sophistication. -- Leonardo da Vinci
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available. I am still not convinced that we can always assume PHP 5.2 => SQLite with no further effort. Some distros do weird things (eg, Debian as someone mentioned already). I also think the ability to do a complete migration or backup just using the primary DB is a critically important feature for maintainability, and having to migrate a magical file on disk as well is a major WTF for users used to WordPress. (And let's face it, that's a major market we need to be able to hit.) However, the idea of treating it as a cache-like system has a lot of merit, especially as it is possible to then run without it. It's possible (although not recommended) to run without the CSS compressor, clean URLs, etc. A "system cache SQLite database" could be treated in the same way. That is, the logic in bootstrap would be something like: if (we can open the magic DB) { Open it and assign it to connection key default, target "system". } elseif (it doesn't exist) { Try to create it. Populate it off of the main DB. Open it and assign it to connection key default, target "system". } elseif (we can't create the DB) { Do nothing. Queries run against the "system" target will just run against the main DB on their own. } OK, we'll probably want to speed optimize that somewhat since it's on the critical path, but you get the idea. Then a drupal_flush_caches() would simply delete that DB. On the installer front, what if installers do not REQUIRE an SQLite to work, but require them for certain features? So "advanced" install profiles can require SQLite, but the basic ones don't. --Larry Garfield On Wed, 4 Feb 2009 07:12:46 -0800, Karoly Negyesi <karoly@negyesi.net> wrote:
Hi,
Every morning, I am distilling the discussion.
*) SQLite is not supported. If you have Drupal 5.2 and PDO which are already requirements then you have SQLite unless it's explicitly disabled and so far noone was able to answer the challenge at http://drupal4hu.com/node/177 . It's embedded into PHP so "supporting" it is like supporting the SimpleXML extension. Unlike MySQL where you need to run a separate server, here we have an embedded SQL engine right inside PDO. Also note that if we want (and I think we want) easier and more powerful install profiles then we will require SQLite for the install anyways.
*) Deployment and rebuild. As it stands, now I believe that Drupal should be able to boot up enough to be able to copy the tables from MySQL to SQLite. I am not saying I am expecting it to be functional. After the tables are copied back into SQLite, it should either restart the bootstrap process or reload at the same URL. This solves deployment.
*) Why do I want this? Once again, nicer code, especially for caching. Various pluggable subsystems (cache, session and path being the biggest wins here) will just work instead of ugly hacking. But in general, instead of trying to figure out during bootstrap coding "do we have a DB connection yet" the answer is always yes.
Kind regards
NK
What impact would this have on sites utilizing multiple Apache/PHP servers? I'm not that up on SQLite, but I was always under the impression it was more or less designed for a single server scenario (though I guess you could put the file on a shared file system, which of course opens up a whole new can of worms). Jamie Holly Larry Garfield wrote:
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
I am still not convinced that we can always assume PHP 5.2 => SQLite with no further effort. Some distros do weird things (eg, Debian as someone mentioned already). I also think the ability to do a complete migration or backup just using the primary DB is a critically important feature for maintainability, and having to migrate a magical file on disk as well is a major WTF for users used to WordPress. (And let's face it, that's a major market we need to be able to hit.)
However, the idea of treating it as a cache-like system has a lot of merit, especially as it is possible to then run without it. It's possible (although not recommended) to run without the CSS compressor, clean URLs, etc. A "system cache SQLite database" could be treated in the same way.
That is, the logic in bootstrap would be something like:
if (we can open the magic DB) { Open it and assign it to connection key default, target "system". } elseif (it doesn't exist) { Try to create it. Populate it off of the main DB. Open it and assign it to connection key default, target "system". } elseif (we can't create the DB) { Do nothing. Queries run against the "system" target will just run against the main DB on their own. }
OK, we'll probably want to speed optimize that somewhat since it's on the critical path, but you get the idea. Then a drupal_flush_caches() would simply delete that DB.
On the installer front, what if installers do not REQUIRE an SQLite to work, but require them for certain features? So "advanced" install profiles can require SQLite, but the basic ones don't.
--Larry Garfield
On Wed, 4 Feb 2009 07:12:46 -0800, Karoly Negyesi <karoly@negyesi.net> wrote:
Hi,
Every morning, I am distilling the discussion.
*) SQLite is not supported. If you have Drupal 5.2 and PDO which are already requirements then you have SQLite unless it's explicitly disabled and so far noone was able to answer the challenge at http://drupal4hu.com/node/177 . It's embedded into PHP so "supporting" it is like supporting the SimpleXML extension. Unlike MySQL where you need to run a separate server, here we have an embedded SQL engine right inside PDO. Also note that if we want (and I think we want) easier and more powerful install profiles then we will require SQLite for the install anyways.
*) Deployment and rebuild. As it stands, now I believe that Drupal should be able to boot up enough to be able to copy the tables from MySQL to SQLite. I am not saying I am expecting it to be functional. After the tables are copied back into SQLite, it should either restart the bootstrap process or reload at the same URL. This solves deployment.
*) Why do I want this? Once again, nicer code, especially for caching. Various pluggable subsystems (cache, session and path being the biggest wins here) will just work instead of ugly hacking. But in general, instead of trying to figure out during bootstrap coding "do we have a DB connection yet" the answer is always yes.
Kind regards
NK
Drupal Lite! On Wed, Feb 4, 2009 at 9:16 PM, Jamie Holly <hovercrafter@earthlink.net>wrote:
What impact would this have on sites utilizing multiple Apache/PHP servers? I'm not that up on SQLite, but I was always under the impression it was more or less designed for a single server scenario (though I guess you could put the file on a shared file system, which of course opens up a whole new can of worms).
Jamie Holly
Larry Garfield wrote:
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
I am still not convinced that we can always assume PHP 5.2 => SQLite with no further effort. Some distros do weird things (eg, Debian as someone mentioned already). I also think the ability to do a complete migration or backup just using the primary DB is a critically important feature for maintainability, and having to migrate a magical file on disk as well is a major WTF for users used to WordPress. (And let's face it, that's a major market we need to be able to hit.)
However, the idea of treating it as a cache-like system has a lot of merit, especially as it is possible to then run without it. It's possible (although not recommended) to run without the CSS compressor, clean URLs, etc. A "system cache SQLite database" could be treated in the same way.
That is, the logic in bootstrap would be something like:
if (we can open the magic DB) { Open it and assign it to connection key default, target "system". } elseif (it doesn't exist) { Try to create it. Populate it off of the main DB. Open it and assign it to connection key default, target "system". } elseif (we can't create the DB) { Do nothing. Queries run against the "system" target will just run against the main DB on their own. }
OK, we'll probably want to speed optimize that somewhat since it's on the critical path, but you get the idea. Then a drupal_flush_caches() would simply delete that DB.
On the installer front, what if installers do not REQUIRE an SQLite to work, but require them for certain features? So "advanced" install profiles can require SQLite, but the basic ones don't.
--Larry Garfield
On Wed, 4 Feb 2009 07:12:46 -0800, Karoly Negyesi <karoly@negyesi.net> wrote:
Hi,
Every morning, I am distilling the discussion. *) SQLite is not supported. If you have Drupal 5.2 and PDO which are already requirements then you have SQLite unless it's explicitly disabled and so far noone was able to answer the challenge at http://drupal4hu.com/node/177 . It's embedded into PHP so "supporting" it is like supporting the SimpleXML extension. Unlike MySQL where you need to run a separate server, here we have an embedded SQL engine right inside PDO. Also note that if we want (and I think we want) easier and more powerful install profiles then we will require SQLite for the install anyways. *) Deployment and rebuild. As it stands, now I believe that Drupal should be able to boot up enough to be able to copy the tables from MySQL to SQLite. I am not saying I am expecting it to be functional. After the tables are copied back into SQLite, it should either restart the bootstrap process or reload at the same URL. This solves deployment. *) Why do I want this? Once again, nicer code, especially for caching. Various pluggable subsystems (cache, session and path being the biggest wins here) will just work instead of ugly hacking. But in general, instead of trying to figure out during bootstrap coding "do we have a DB connection yet" the answer is always yes. Kind regards NK
On Thu, Feb 5, 2009 at 12:03 AM, Larry Garfield <larry@garfieldtech.com>wrote:
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
[...]
However, the idea of treating it as a cache-like system has a lot of merit, especially as it is possible to then run without it. It's possible (although not recommended) to run without the CSS compressor, clean URLs, etc. A "system cache SQLite database" could be treated in the same way.
Apparently you are completely missing the point chx is trying to make. Damien
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
Stop. Read. We only get the primary benefit (code cleanliness) if we *require* SQLite. There is no point in doing this half-way. If you know of web hosts where Drupal would no longer work, please take the challenge at http://drupal4hu.com/node/177. So far, no such place is known. Also, Lets stop talking about deployment. chx solved that (no code yet). Apache would write SQLite file when it needs to. Site admins can be oblivious.
I understand how this improves installation, and i think it's fine to require it for installation, however I think that it should be optional after installation. Dmitri On Feb 4, 2009, at 4:28 PM, Moshe Weitzman wrote:
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
Stop. Read. We only get the primary benefit (code cleanliness) if we *require* SQLite. There is no point in doing this half-way. If you know of web hosts where Drupal would no longer work, please take the challenge at http://drupal4hu.com/node/177. So far, no such place is known.
Also, Lets stop talking about deployment. chx solved that (no code yet). Apache would write SQLite file when it needs to. Site admins can be oblivious.
Has anyone looked into version compatibility between the different versions of sqlite? For example, on the same ISP, one server I checked has sqlite2, the other has sqlite3. Neither server has both drivers installed. Neither server actually has the sqlite binaries installed, though I assume that isn't common, but worth checking on other SQLite supporting hosts. The SQLite site mentions using the command line tools to dump and restore the DB, and I haven't found anything about doing that from pure PHP. This probably wouldn't be an issue for caches and such, since an incompatible DB can just be recreated. I could see it causing headaches for "Drupal Lite" scenarios. http://www.sqlite.org/formatchng.html On 4-Feb-09, at 7:39 PM, Dmitri Gaskin wrote:
I understand how this improves installation, and i think it's fine to require it for installation, however I think that it should be optional after installation.
Dmitri
On Feb 4, 2009, at 4:28 PM, Moshe Weitzman wrote:
So I am still -1 on requiring SQLite in order for Drupal to function. However, I am leaning more and more toward automatically using it if available.
Stop. Read. We only get the primary benefit (code cleanliness) if we *require* SQLite. There is no point in doing this half-way. If you know of web hosts where Drupal would no longer work, please take the challenge at http://drupal4hu.com/node/177. So far, no such place is known.
Also, Lets stop talking about deployment. chx solved that (no code yet). Apache would write SQLite file when it needs to. Site admins can be oblivious.
Hi Andrew, On Thu, Feb 5, 2009 at 5:02 PM, Andrew Berry <andrewberry@sentex.net> wrote:
Has anyone looked into version compatibility between the different versions of sqlite? For example, on the same ISP, one server I checked has sqlite2, the other has sqlite3. Neither server has both drivers installed. Neither server actually has the sqlite binaries installed, though I assume that isn't common, but worth checking on other SQLite supporting hosts.
Drupal 7 only supports SQLite 3, that is the only version supported by PDO.
The SQLite site mentions using the command line tools to dump and restore the DB, and I haven't found anything about doing that from pure PHP. This probably wouldn't be an issue for caches and such, since an incompatible DB can just be recreated. I could see it causing headaches for "Drupal Lite" scenarios.
Pure-PHP solutions (like the Backup and Migrate module) should work in that scenarios. This module is easy enough to install. Damien
participants (12)
-
Andrew Berry -
Chris Johnson -
Damien Tournoud -
Dmitri Gaskin -
Jamie Holly -
Karoly Negyesi -
Khalid Baheyeldin -
Larry Garfield -
Morbus Iff -
Moshe Weitzman -
Ronald Ashri -
Victor Kane