local/mysql-database-create

   1 #!/bin/sh -eu
   2 tool=$(readlink -e "${0%/*}/..")
   3 . "$tool"/local/lib.sh
   4
   5 db="$1"
   6 user="${2:-$1}"
   7 sudo -u mysql mysql -u mysql --batch --verbose <<-EOF
   8         DROP PROCEDURE IF EXISTS mysql.create_database;
   9         DELIMITER //
  10         CREATE PROCEDURE mysql.create_database (dbname VARCHAR(16), username VARCHAR(16), hostname VARCHAR(60))
  11          BEGIN
  12                 IF NOT (EXISTS (SELECT SCHEMA_NAME
  13                         FROM INFORMATION_SCHEMA.SCHEMATA
  14                         WHERE SCHEMA_NAME = dbname
  15                         LIMIT 1))
  16                 THEN
  17                         SET @QUERY = CONCAT("CREATE DATABASE ", dbname, " CHARACTER SET utf8 COLLATE utf8_general_ci");
  18                         PREPARE stmt FROM @QUERY;
  19                         EXECUTE stmt;
  20                  END IF;
  21                 SET @QUERY = CONCAT("GRANT ALL PRIVILEGES ON ", dbname, ".* TO ", username, "@", hostname);
  22                 PREPARE stmt FROM @QUERY;
  23                 EXECUTE stmt;
  24          END;
  25          //
  26         CALL mysql.create_database('$db', '$user', 'localhost');
  27         EOF