SQLite and Drupal 7 -- let's try this again
Hi, Apparently despite my intentions, I did not manage to express myself clearly so let's try this again. Drupal 7 has a very interesting new feature which we call the registry. The registry holds information about which function, class and interface lives in which file. This information never changes if you do not change your modules and themes. Drupal always had a system table which holds information on available themes and modules. This information never changes if you do not add new modules nor new themes. If we put these tables in an SQLite table then the only time this file changes is when your code changes. Therefore, when people with high performance sites deploy their new code, they can deploy the new database. There is no new procedure to be created, just one more file added to the roster. Now, if I ask this way, are people still against putting registry and system into an SQLite database? NK
I thought one of the goals is to make Drupal database independent so requiring SQLite seems to run counter to this. My development box does not have SQLite and some of the hosts I have used do not include it The bottom line requiring SQLite seems like more that could prevent or at least keep people from deploying Drupal and that does not seem like the right direction for Drupal. So personally I vote against requiring SQLite Steve Ringwood aka nevets
Drupal 7 has a very interesting new feature which we call the registry. The registry holds information about which function, class and interface lives in which file. This information never changes if you do not change your modules and themes.
Drupal always had a system table which holds information on available themes and modules. This information never changes if you do not add new modules nor new themes.
If we put these tables in an SQLite table then the only time this file changes is when your code changes. Therefore, when people with high performance sites deploy their new code, they can deploy the new database. There is no new procedure to be created, just one more file
So, what you're roughly saying is: * the speed of a file read/parse of a SQLite table is always going to be faster than a MySQL/etc. read? Even if the above is true, I'm not a huge, huge fan of it myself. Is there an automatic backup in place - something that, if SQLite is enabled, but the SQLite database isn't there (or can't be created), we'd fall back to the MySQL version? Random comments and reasons for disliking it: * Not immediately easy to explain. What's this file doing here? Why is Drupal the only application I know that requires both SQLite and a MySQL store? Why isn't everything in MySQL? Why aren't more things in SQLite? * Generally speaking, "backup your databases" and "backup your site" happen in two separate mental processes (and usually technical processes too). Unless the feature handles a failsafe when the SQLite database goes missing, I'm not too happy with the idea of "ok, to backup the 'database' now requires a mysqldump AND a copy of this SQLite database." * Aesthetically, I like being able to see what's in a database. I don't personally know how to access a SQLite database, nor do I want to. * Similarly, in Drupal 6 (I dunno about Drupal 7), the system table is also NOT UPDATED when I *remove* modules or themes. I regularly am pruning it manually, because it bugs me. I'm obsessive about cleanliness and organization. Unless I move the DELETE FROM {system} code to a module, you're implying I can't do that anymore? * When I'm debugging, it tends to be useful to quickly modify up/down the weight of a module stored in, yep, the system table. Would I still be able to do that here, from a MySQL prompt? Or would I have to devolve into learning SQLite again, when I've never ever had to use SQLite ever? * Are the gains *really* so incredibly huge that SQLite is the only solution, as opposed to "just" throwing the same ol' chainsaws at the problem: memcached, opcode cache, etc.? If this is so great for high-profile sites, they've probably already got their own specific set of tweaks for improving things, no? (I know I do.) * "Therefore, when people with high performance sites deploy their new code, they can deploy the new database." Where is this new SQLite file being created? This particular comment, I dunno, feels ooky to me. When I deploy code, I want to, you know, deploy *code*, not a set of caches (for the same reason that I don't deploy memcache instances or previously opcoded cache files). -- Morbus Iff ( cheese and rice saves ) 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
So, what you're roughly saying is:
* the speed of a file read/parse of a SQLite table is always going to be faster than a MySQL/etc. read?
That's likely but that's not the big win here. Read on.
* Aesthetically, I like being able to see what's in a database. I don't personally know how to access a SQLite database, nor do I want to.
Well, there is a command line utility but sure.
* When I'm debugging, it tends to be useful to quickly modify up/down the weight of a module stored in, yep, the system table. Would I still be able to do that here, from a MySQL prompt?
From a sqlite prompt. Not a big difference IMO. The changes from SQLite will not, however, automatically sync back to MySQL so I admit this is not ideal.
It seems I need to make sure that Drupal can pull together itself enough to rebuild the missing SQLite database by copying the tables from MySQL. And then reload itself in a useable state. This means that actually deploy is a lot lot easier than I originally thought. (See this is why I am not too fond of emails, I like the issue queue a lot better)
* Are the gains *really* so incredibly huge that SQLite is the only solution, as opposed to "just" throwing the same ol' chainsaws at the problem: memcached, opcode cache, etc.? If this is so great for high-profile sites, they've probably already got their own specific set of tweaks for improving things, no? (I know I do.)
Here we go. The big win is NOT performance. The big win is nice code. I forgot to mention this, silly me. So the problem is that currently we have a fairly ugly way to allow pluggable subsystems and they are not really flexible. You can pick one cache subsystem, that's it. We are planning something called handlers which will let you pick cache subsystems by the table for example. For this, we will change the cache implementations to become a class. However, if we want -- and yes, we want -- to serve cached pages without touching MySQL then we need to somehow specify the pathes to the cache and session implementation. If we have the registry available immediately then autoload just works.
* "Therefore, when people with high performance sites deploy their new code, they can deploy the new database." Where is this new SQLite file being created?
In your files directory because it needs to be Apache writeable but it's kinda irrevelant here.
It seems I need to make sure that Drupal can pull together itself enough to rebuild the missing SQLite database by copying the tables from MySQL. And then reload itself in a useable state. This means that actually deploy is a lot lot easier than I originally thought.
Yep, that'd be great. But, I'm still iffy on REQUIRING SQLite. I'd be a lot happier if it was a non-interactive *optional* thing. * If SQLite is built into PHP, use it (so, you'd do your function exists around relevant pieces of code). Anything we continue to build will have to support reading from MySQL in the absence of. This would also stop all the whiners who are complaining about SQLite without actually understanding the point of it (as well as the *very real* lack of support for SQLite in some hosting environments). * Since it's a cache, it should probably end up in files/db, similarly to files/css and files/js. Likewise, that means it should also be deleted when one Clears the cache under the Performance page in admin. This, along with your "rebuild when it disappears" change, would allow me to continue to do my MySQL {system} modifications without the cache getting too heavily in the way. -- Morbus Iff ( if god is my witness, god must be blind ) 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 7:58 AM, Morbus Iff wrote:
* If SQLite is built into PHP, use it (so, you'd do your function exists around relevant pieces of code)
Also important to ensure that in the case of migrating a site between systems that you don't have to do anything if SQLite isn't present. I'm sure most developers have SQLite on their own machines, and it would lead to significant confusion for newbies if they had to "prepare" their site before deploying on a host without SQLite.
I retract any security concerns I had. The ones mentioned aren't. -- Morbus Iff ( i've no more shoulders, only chips ) 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
It's a very interesting idea, as Robert said. Can you explain a bit further how the code is improved, and maybe a couple things which are enabled, by this idea? Which larger problem is being solved by this solution -- that is, where is the code really troublesome right now in handlers and implementing pluggable subsystems? ..chris On Tue, Feb 3, 2009 at 10:44 PM, Karoly Negyesi <karoly@negyesi.net> wrote:
Here we go. The big win is NOT performance. The big win is nice code. I forgot to mention this, silly me. So the problem is that currently we have a fairly ugly way to allow pluggable subsystems and they are not really flexible. You can pick one cache subsystem, that's it. We are planning something called handlers which will let you pick cache subsystems by the table for example. For this, we will change the cache implementations to become a class. However, if we want -- and yes, we want -- to serve cached pages without touching MySQL then we need to somehow specify the pathes to the cache and session implementation. If we have the registry available immediately then autoload just works.
On Wednesday 04 February 2009, Karoly Negyesi wrote:
If we put these tables in an SQLite table then the only time this file changes is when your code changes. Therefore, when people with high performance sites deploy their new code, they can deploy the new database. There is no new procedure to be created, just one more file added to the roster.
Now, if I ask this way, are people still against putting registry and system into an SQLite database?
If settings.php just specifies the SQLite file for the system and registry tables, and the MySQL database for the other tables, wouldn't that solve the issue and make it an optional feature for people who want this? Or is there something more to be gained by hardcoding SQLite access? JP
Why does it have to be SQLLite? You are effectively proposing to split the datastore. Why hardcode it to SQLLite? Potentially I would put it in another MySQL database on a different server or a pgsql db somewhere. PDO let's me do that. In general I am against this idea. Drupal already, mainly because of MyISAM on MYSQL I suppose, falls behind on fundamental good database design ideas; like foreign keys for example. Splitting the data into two stores just does more to reduce the referencial integrity and safe state of our data. Drupal definitely doesn't achieve second normal form. I am not sure whether we could argue it achieves first normal form. In my opinion this is a non starter in hardcoding to SQLLite and a slow starter even if you let people choose a second data store for certain schemas/tables Stew
chx, are there also implications for how one would deploy sites in your suggestion? Would it make it easier or harder to move stuff from staging to production, for example? I'm fascinated by your idea and think that if it can be made an optional requirement (yes intentional wording), then it could be very interesting. I encourage people to keep asking questions until they understand the full idea, because so far I think a lot of people essentially stopped reading at "Should we require SQLite?" -Robert
Trying my best to understand the benefits of this. What benefits are to be gained from moving these tables to an SQLite database? Performance improvements? Does it ease anything for developers? What exactly are the pros and cons? Whatever they are, they just don't hit me as intuitive. On Wed, Feb 4, 2009 at 12:41 PM, Robert Douglass <rob@robshouse.net> wrote:
chx, are there also implications for how one would deploy sites in your suggestion? Would it make it easier or harder to move stuff from staging to production, for example? I'm fascinated by your idea and think that if it can be made an optional requirement (yes intentional wording), then it could be very interesting. I encourage people to keep asking questions until they understand the full idea, because so far I think a lot of people essentially stopped reading at "Should we require SQLite?"
-Robert
-- Ashraf Amayreh http://aamayreh.org
Also commercial support of SQLite is sparse. I mailed my hosting provider and they won't support SQLite and have no plans to. They'll let us use it but won't own it. All of my websites live under a managed service agreement so that the hosting company can simply look after them. This could be a step in the wrong direction for corporate adoption if SQLite is forced. Stew On Wed, Feb 4, 2009 at 11:05 AM, Ashraf Amayreh <mistknight@gmail.com>wrote:
Trying my best to understand the benefits of this. What benefits are to be gained from moving these tables to an SQLite database? Performance improvements? Does it ease anything for developers? What exactly are the pros and cons? Whatever they are, they just don't hit me as intuitive.
On Wed, Feb 4, 2009 at 12:41 PM, Robert Douglass <rob@robshouse.net>wrote:
chx, are there also implications for how one would deploy sites in your suggestion? Would it make it easier or harder to move stuff from staging to production, for example? I'm fascinated by your idea and think that if it can be made an optional requirement (yes intentional wording), then it could be very interesting. I encourage people to keep asking questions until they understand the full idea, because so far I think a lot of people essentially stopped reading at "Should we require SQLite?"
-Robert
-- Ashraf Amayreh http://aamayreh.org
If I understand chx correctly having the database in a deployable file would make installation even smoother AND install profiles would be able to come 'fully deployed' as all the modules will have been already set up in the system table. This isn't really a huge performance increase (although, a file read is faster than a db query). It will make deploying from staging a little trickier in multi server environments. -Sam chx correct me if I am wrong, but are those the main goals of this? On Wed, 2009-02-04 at 13:05 +0200, Ashraf Amayreh wrote:
Trying my best to understand the benefits of this. What benefits are to be gained from moving these tables to an SQLite database? Performance improvements? Does it ease anything for developers? What exactly are the pros and cons? Whatever they are, they just don't hit me as intuitive.
On Wed, Feb 4, 2009 at 12:41 PM, Robert Douglass <rob@robshouse.net> wrote: chx, are there also implications for how one would deploy sites in your suggestion? Would it make it easier or harder to move stuff from staging to production, for example? I'm fascinated by your idea and think that if it can be made an optional requirement (yes intentional wording), then it could be very interesting. I encourage people to keep asking questions until they understand the full idea, because so far I think a lot of people essentially stopped reading at "Should we require SQLite?"
-Robert
-- Ashraf Amayreh http://aamayreh.org
participants (10)
-
Andrew Berry -
Ashraf Amayreh -
Chris Johnson -
Jakob Petsovits -
Karoly Negyesi -
Morbus Iff -
Robert Douglass -
Sam Tresler -
Steve Ringwood -
Stewart Robinson