Add a sql-create command

I don't see enough broad beed for this. Please list a couple common use cases if you want this in core drush.

sql-create has been rejected in the past, but I hope it can be accepted now, because I think this would be really useful. To create an sql database, I literally run the drush sql-sync command in simulate mode, and then copy the create db output to the command prompt. I've been meaning to revisit this issue for some time now; thank you for submitting this.

You might want to wait until @moshe weighs in before implementing the suggestions below, although my suggestions are fairly trivial.

1. It would be better if sql-create was part of the sql.drush.inc commandfile. Put the command definition there, at least; the implementation may be in its own file using the same technique as used in sql-sync, if desired.

2. Right now, drush sql-create @site works, but to use sql-create on the bootstrapped site you must use drush sql-create @self. For consistency, it would be good to support drush sql-create / drush @site sql-create by making a missing destination parameter default to '@self'. (function drush_create_sql_create_init($destination = '@self').

OK, we can proceed with Greg's suggestions and move toward bringing this in.

Not sure all those options make sense in this command. The comments mention --create-db when that isn't even a valid option.

Needs tests.

Hi, I am interested in this too, I think it is useful especially for test setups.

Some comments:

In my use case I add a new user for each new drupal database I create, with a script like this:

#!/bin/sh

[ $# -eq 3 ] || { echo "usage: $0 <db_name> <db_user> <db_password>" >&2; exit 1; }

DB_NAME="$1"
DB_USER="$2"
DB_PASS="$3"

mysql -u root -p <<EOM
CREATE DATABASE IF NOT EXISTS ${DB_NAME};
USE ${DB_NAME};
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES \
ON ${DB_NAME}.*
TO '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASS}';
FLUSH PRIVILEGES;
EOM

maybe options to create users can be added too to this command, or would a new command sql-grant-user be better? What do you think?

What about adding a delete (i.e. DROP DATABASE dbname) command for symmetry? Or extend the current sql-drop to remove the whole database.

Thanks,
Antonio

OK, a tentative patch attached.

I am using drush_sql_empty_db which does what we want already. I tried to make the command looks like the other ones already defined. Let me know if there is anything left. I didn't use an external file as the callback is quite short.

I think the aliases are handled by some common _init code, right? I tested and they work.

The patch was created with git format-patch, so you can use git am to import it (this way I'll show up as the author in the git history).

@fago is it OK to you that I am taking over?

Thanks,
Antonio

There is no guarentee that drush_sql_empty_db will necessarily use the technique of creating the database to perform its function; that is an implementation detail that might change in the future, or be implemented differently for some future database. If you want to share code between drush_sql_empty_db and drush_sql_create, factor the common code out into a separate function with clearly-defined behavior. In other words, it is okay for sql_empty to call sql_create, but not the other way around.

@greg.1.anderson, thanks, I'll factor out a _drush_sql_create() and send an updated patch.

Patch updated as per comments in #7.

Changelog since version1:

• Factor out a _drush_sql_create() function in order to share some code with drush_sql_empty_db()
• Don't pass any parameters to drush_sql_create(), the command does not accept
  arguments, only options.
• Improve the last example a little bit.
• Use single quotes for the value of the description field, like other commands do.

Nice; works great, and current tests pass. I like it, but perhaps should have at least one test.

@greg.1.anderson I'll try adding a test in version 3, but maybe we need a way to drop the database too? So the test does not leave traces of the created database?

what about a --delete-database or --drop-database option to the drush sql-drop command?

I am also thinking about factoring out the db-su options as they are used by more than one command.

I think I would favor a separate sql-delete command to delete databases. Extra factoring, where it makes sense, is a good idea.

I see, I'll give the "sql-delete" way a try.

BTW in the create command, should I handle the sqlite case explicitly?

AFAICS databases are not created with SQL in sqlite, but the file gets created when it is opened the first time, right? The current code will just perform a void query with sqlite.

It does not look like you need to explicitly create tables in sqlite. http://www.sqlite.org/sqlite.html

Thanks for improving this further; adjusting status, since work still in progress.

Seems to work great, thanks.

FY, in case anyone else is confused, the patch in #9 was actually committed to HEAD on July 10:
http://drupalcode.org/project/drush.git/commitdiff/7f67114993e26acc83586...

Yes, #9 was committed. Just setting status here to fixed to avoid further confusion; other suggested improvements can be done in a separate issue if someone comes back to pick them up.