SQLite support

sqlite is not tested at all. its unsupported. feel free to contribute patches that make it work better with drush.

I ran into this as well ... as the cli runs as the current user I though chmod 666 would solve it but it did not.

Even 'sudo su www-data ; path-to-drush/drush status' did not work.

Even copying the database file to /tmp and hardcode the filepath did not work. (Based on http://www.mail-archive.com/sqlite-users@sqlite.org/msg28190.html)

subscribe

Drush needs SQLite support, because there's at least one person out there who is just itching to be able to leave MySQL in the technological dustbin of history, and that's enough for me.

Here's a start. This stops the bootstrapping errors, although it's not enough yet. My way is made difficult by the fact that SQLite seems to be a real nazi when it comes to permissions; chmod 777ing the database file isn't enough; you need to run Drush as the user that owns the database file. Not even being a user in the group that owns the file seems to be sufficient… I don't understand why. Even when things bootstrap without errors, commands seem to just silently fail… Also, this is my first time trying to hack Drush, and I'm pretty confused by, well, just about everything. How do I get drush_log() to actually print or log or whatever it should be doing?

Subscribing.

Subscribing.

Use the --debug option whn you run your commands. Then you see all logs.

Anyone available to hack on this?

Re-roll. It's essentially identical to the previous patch… the trick is file permissions thing. It's not enough for the user running the Drush command to have write permissions to the database file; they must also have write permissions to the directory the database file is in. This is because SQLite writes a file for some journaling stuff while it runs in the same directory as the database file. This is probably common sense to some people (and is also what's required for getting databases to work with PHP/Drupal, come to think of it), but it's a fact that I had to rediscover.

With this patch (and proper file permissions), I was able to run several common Drush commands successfully on a stock D7 Beta 3 site (though I wasn't able to test all of them). So it appears that, so long as the file permissions are right, Drush could have been working with SQLite all along!

Please test!

Verified and committed.

I'm leaving this Active since several commands hard code for postgres and mysql. We need to handle sqlite here also. Not sure what we do about sql-sync.gr

Sweet!

Some improvements. I basically went through all the "sql-*" commands and tried to make them work.

One exception is "sql-drop," due the the fact that "SHOW TABLES;" isn't valid to SQLite. Instead you can do ".tables" (commands which aren't really SQL are prefixed with a dot in SQLite), but since this command seems to invariably output the list as tables, we need to do some post-processing to get the output to match what MySQL will do… Still not sure of the best way to fix that. The other exception is "sql-sync", which might be easy - I've yet to look into it.

But sql-query, sql-dump, and the others all work!

Update with "sql-drop" apparently working. Besides the "SHOW TABLES/.tables" difference, there's also the fact that SQLite doesn't support "DROP TABLE foo, bar;". We can only do one table per DROP TABLE, so instead we have to do "DROP TABLE foo; DROP TABLE bar;". So that part of the patch is kinda ugly…

Nice work.

Note that per #716412: add a --delete-all-tables-first option for sql-sync ?, sql-drop is going to be rewritten to use the mysqldump | grep 'drop' trick. Please examine the code in that patch and see if something similar can be done with sqllite.

Don't bother changing sql-sync now since we plan to refactor that soon to reply on sql-dump and sql-drop directly ... site-install surely needs some work to get sqlite support. i can do that if it looks too foreign to you.

+++ commands/sql/sql.drush.inc	14 Nov 2010 20:38:39 -0000
@@ -426,9 +451,38 @@ function drush_sql_drop() {
+    // We can't use drush_op('db_query', $sql) because it will only perform one
+    // SQL command and we're technically performing several.

Really? We are sending one compound statement (separated by semicolon) to sqlite so I would think that drush_op('db_query', $sql) would work.

My only guess is that something in db_query() is maybe parsing out everything after the first semicolon. Might even be happening at the PDO level. But when I tried it, every time it ran, only the first table alphabetically would actually be dropped.

I can't do it now, but I'll try to take a peek at site-install soon.

Decided to commit most recent patch. Lets keep on implementing recent feedback.

I should be more clear. I just committed #12. Lets keep working on sql-drop per #13 and site-install.

site-install. Damn, I'm good.

For the --db-url parameter, use something like:
sqlite:/path/to/db-file

Note that the path must be absolute (begin with a slash) and not relative, just like when installing Drupal through a browser.

I've spent some time looking over #716412: add a --delete-all-tables-first option for sql-sync ?. I don't think it's relevant to SQLite because dumping and importing SQL is not necessary for SQLite. Instead, we can just rsync the source DB file to the remote one. Since rsync only copies changed parts of a file, it'll be much faster, too, since we're not having to import heaps of data that's already there. So I guess my next task will be implementing that… but not tonight.

Committed site-install patch. Thanks.

Lets use other issues for follow-up work.

I notice that the drush help si help text offers this example:

drush site-install --db-url=sqlite://sites/default/files/.ht.sqlite

I don't think this will actually work. It's not the way I coded the patch, and it didn't work when I tested it either - it tried to create the file at /sites/default/files/.ht.sqlite, where the first / is the root of my filesystem, not my Drupal directory. However, the fact that nobody has mentioned having a problem like this in either Drush's queue or the Drupal issue where compatibility with drush si was discussed makes me wonder if it's just me…?

If it's not just me, here's a patch to fix the documentation. Note that it also doesn't suggest that people put their database files in their site's files directory; Drupal core suggests this, and though I can understand the logic, it still strikes me as a dumb idea.

Yes, thats much better. Committed.

I swear I tried the prior code before I documented it. I might have put an empty file there first.