views:

51

answers:

2

I'm writing a small deployment SQL script for my first database-driven app.

In the process, I find that I repeat myself a lot, for instance:

GRANT USAGE ON *.* TO 'foo'@'localhost';
DROP USER 'foo'@'localhost';
CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password';

It would be fantastic if I could use a variable or a macro to replace commonly occurring data. Is it possible to implement something like the the following snippet?

#define USER 'foo'        #or "Type USER = 'foo'"
#define HOST 'localhost'  #or "Type HOST = 'localhost'"

GRANT USAGE ON *.* TO USER@HOST
DROP USER USER@HOST
CREATE USER USER@HOST IDENTIFIED BY 'password'
A: 

You can certainly do something like:

SET @user='foo';
VoteyDisciple
+1  A: 

Most SQL databases have some kind of bind variables that you can use for that.

For instance, in PostgreSQL you use the \set command in plsql:

\set user = foo
drop user :user;
create user :user identified by 'password';

However, I am not sure if MySQL have something like that. It do have variables, and since the host and user is a string, you might be able to do something like this:

select @user = 'foo';
select @host = 'localhost;

drop user @user@@host;
create user @user@@host identified by 'password';

If variables doesn't work with the drop and create user statements, you can always modify the mysql.user table directly, just don't forget to execute flush privileges after.

insert into user values(host,@user,PASSWORD('some_pass'),
       'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
flush privileges;
Jimmy Stenke