Merge lp:~james-page/charms/precise/mysql/shared-db-multi into lp:charms/mysql

[James Page]

This MP adds a new feature to the shared-db relation; specifically it
allow multiple databases/users to be created across the interface in
a single transaction.

This was required to support deploying the Quantum API server as part
of the nova-cloud-controller charm; Quantum requires its own database
as it namespace conflicts with Nova.

The hook was originally written in bash; as this is a bit more complex
I took the opportunity to rewrite it in python to make handling the
multiple database creation a bit easier.

The existing charm interface remains intact.

Credit to Adam G for this approach; its the same as used in Keystone.

[Benjamin Kerensa (bkerensa)]

Looks like a clean change to the hook to me... Good work

[Clint Byrum (clint-fewbar)]

Looks good. +1

review: Approve

[Adam Gandelman (gandelman-a)]

Thanks James. This works really well. I thought I was half-insane when I came up with this strategy for multi-endpoints in Keystone. Glad to see its working well elsewhere.

Merged with some whitespace cleanup.

review: Approve

=== modified file 'hooks/common.py'
--- hooks/common.py	2012-11-02 06:41:12 +0000
+++ hooks/common.py	2012-12-03 15:24:32 +0000
@@ -59,8 +59,61 @@
 def get_db_cursor():
     # Connect to mysql
     passwd = open("/var/lib/juju/mysql.passwd").read().strip()
-    print passwd
     connection = MySQLdb.connect(user="root", host="localhost", passwd=passwd)
-
     return connection.cursor()
 
+
+def database_exists(db_name):
+    cursor = get_db_cursor()
+    try:
+        cursor.execute("SHOW DATABASES")
+        databases = [i[0] for i in cursor.fetchall()]
+    finally:
+        cursor.close()
+    return db_name in databases
+
+
+def create_database(db_name):
+    cursor = get_db_cursor()
+    try:
+        cursor.execute("CREATE DATABASE {}".format(db_name))
+    finally:
+        cursor.close()
+
+
+def grant_exists(db_name, db_user, remote_ip):
+    cursor = get_db_cursor()
+    try:
+        cursor.execute("SHOW GRANTS for '{}'@'{}'".format(db_user,
+                                                          remote_ip))
+        grants = [i[0] for i in cursor.fetchall()]
+    except MySQLdb.OperationalError:
+        print "No grants found"
+        return False
+    finally:
+        cursor.close()
+    return "GRANT ALL PRIVILEGES ON `{}`".format(db_name) in grants
+
+
+def create_grant(db_name, db_user,
+                 remote_ip, password):
+    cursor = get_db_cursor()
+    try:
+        cursor.execute("GRANT ALL PRIVILEGES ON {}.* TO '{}'@'{}' "\
+                       "IDENTIFIED BY '{}'".format(db_name,
+                                                   db_user,
+                                                   remote_ip,
+                                                   password))
+    finally:
+        cursor.close()
+
+
+def cleanup_grant(db_user,
+                  remote_ip):
+    cursor = get_db_cursor()
+    try:
+        cursor.execute("DROP FROM mysql.user WHERE user='{}' "\
+                       "AND HOST='{}'".format(db_user,
+                                              remote_ip))
+    finally:
+        cursor.close()

=== removed file 'hooks/shared-db-common'
--- hooks/shared-db-common	2011-11-09 19:43:42 +0000
+++ hooks/shared-db-common	1970-01-01 00:00:00 +0000
@@ -1,41 +0,0 @@
-#!/bin/bash
-#
-# Common utility functions used for hooks in shared-db-relations
-#
-# Notes:
-#   - /var/lib/juju/mysql.passwd is created during install hook
-#
-# Author: Adam Gandelman <[e-mail]>
-
-DEFAULT_ETH=$(ip route  | grep default | awk '{ print $5 }')
-
-database_exists() {
-  mysql -u root -p$(cat /var/lib/juju/mysql.passwd) \
-    -NBe "SHOW DATABASES" | grep $DATABASE >/dev/null
-}
-
-create_database() {
-  juju-log "mysql - shared-db: Creating database $DATABASE"
-  mysql -u root -p$(cat /var/lib/juju/mysql.passwd) \
-    -NBe "CREATE DATABASE $DATABASE" >/dev/null
-}
-
-grant_exists() {
-  [[ $(mysql -u root -p$(cat /var/lib/juju/mysql.passwd) \
-    -NBe "SELECT User, Host FROM user \
-            WHERE User='$DB_USER' AND Host='$REMOTE_IP'" mysql | wc -l) -gt 0 ]]
-}
-
-create_grant() {
-  juju-log "mysql - shared-db: Creating grant for $DB_USER@$REMOTE_IP"
-  mysql -u root -p$(cat /var/lib/juju/mysql.passwd) \
-    -NBe "GRANT ALL PRIVILEGES ON $DATABASE.* TO '$DB_USER'@'$REMOTE_IP' \
-          IDENTIFIED BY '$PASSWORD'"
-}
-
-cleanup_grant() {
-  juju-log "mysql - shared-db: Cleaning up grant for $DB_USER@$REMOTE_IP"
-  mysql -u root -p$(cat /var/lib/juju/mysql.passwd) \
-    -e "DROP FROM user WHERE user='$DB_USER' AND HOST='$REMOTE_IP'" mysql
-}
-

=== modified file 'hooks/shared-db-relations'
--- hooks/shared-db-relations	2012-10-29 19:35:34 +0000
+++ hooks/shared-db-relations	2012-12-03 15:24:32 +0000
@@ -1,4 +1,4 @@
-#!/bin/bash
+#!/usr/bin/python
 #
 # Create relations between a shared database to many peers.
 # Join does nothing.   Peer requests access to $DATABASE from $REMOTE_HOST.
@@ -7,63 +7,131 @@
 #
 # Author: Adam Gandelman <[e-mail]>
 
-set -ue
-
-FORMULA_DIR=$(dirname $0)
-
-if [[ -e $FORMULA_DIR/shared-db-common ]] ; then
-  . $FORMULA_DIR/shared-db-common
-else
-  juju-log "mysql - shared-db: ERROR Could not load $FORMULA_DIR/shared-db-common"
-fi
-
-shared-db_changed() {
-  juju-log "mysql - shared-db: Relation CHANGED"
-
-  DATABASE=`relation-get database`
-  DB_USER=`relation-get username`
-  REMOTE_HOST=`relation-get hostname`
-  PASSWD_FILE="/var/lib/juju/mysql-$DB_USER.passwd"
-
-  # peer not ready, exit 0 and wait.
-  [[ -z $DATABASE ]] || [[ -z $REMOTE_HOST ]] || [[ -z $DB_USER ]] \
-    && echo "DATABASE||REMOTE_HOST||DB_USER not set. Peer not ready?" && exit 0
-
-  # to be used for GRANTs
-  # if the remote unit is actually placed on the same system,
-  # it'll need a grant for a user connecting from 127.0.0.1
-  local_hostname=$(hostname -f)
-  if [[ $REMOTE_HOST != $local_hostname ]] ; then
-    REMOTE_IP=$(python -c "import socket ; print socket.gethostbyname('$REMOTE_HOST')")
-  else
-    REMOTE_IP="127.0.0.1"
-  fi
-
-  if [[ ! -e $PASSWD_FILE ]] ; then
-    PASSWORD=$(pwgen -s 16)
-    echo $PASSWORD >$PASSWD_FILE
-  else
-    PASSWORD=$(cat $PASSWD_FILE)
-  fi
-
-  if ! database_exists ; then
-    juju-log "mysql - shared db: Creating database $DATABASE"
-    create_database || exit 1
-    echo "Done"
-  fi
-  if ! grant_exists ; then
-    juju-log "mysql - shared-db: Granting $DB_USER@$REMOTE_HOST access to $DATABASE"
-    create_grant || exit 1
-  fi
-  relation-set db_host=$(hostname -f) password=$PASSWORD
-  juju-log "mysql - shared-db: Returning access to $DATABASE to $DB_USER@$REMOTE_HOST."
-}
-
-RELATION_ACTION=${0##*/shared-db-relation-}
-juju-log "HOOK: shared-db-relation-$RELATION_ACTION fired."
-
-case $RELATION_ACTION in
-  "joined") exit 0 ;; # do nothing on join, wait for peer to request access
-  "changed") shared-db_changed ;;
-  *) exit 0 ;;
-esac
+
+from common import *
+import sys
+import subprocess
+import json
+import socket
+import os
+
+
+def pwgen():
+    return subprocess.check_output(['pwgen', '-s', '16']).strip()
+
+
+def relation_get():
+    return json.loads(subprocess.check_output(
+                        ['relation-get',
+                         '--format',
+                         'json']
+                        )
+                      )
+
+
+def relation_set(**kwargs):
+    cmd = [ 'relation-set' ]
+    args = []
+    for k, v in kwargs.items():
+        if k == 'rid':
+            cmd.append('-r')
+            cmd.append(v)
+        else:
+            args.append('{}={}'.format(k, v))
+    cmd += args
+    subprocess.check_call(cmd)    
+    
+
+def shared_db_changed():
+    
+    def configure_db(hostname,
+                     database,
+                     username):
+        passwd_file = "/var/lib/juju/mysql-{}.passwd"\
+                        .format(username)        
+        if hostname != local_hostname:
+            remote_ip = socket.gethostbyname(hostname)
+        else:    
+            remote_ip = '127.0.0.1'
+                    
+        if not os.path.exists(passwd_file):
+            password = pwgen()
+            with open(passwd_file, 'w') as pfile:
+                pfile.write(password)
+        else:
+            with open(passwd_file) as pfile:
+                password = pfile.read().strip()
+                
+        if not database_exists(database):
+            create_database(database)
+        if not grant_exists(database,
+                            username,
+                            remote_ip):
+            create_grant(database,
+                         username,
+                         remote_ip, password)
+        return password
+
+    settings = relation_get()
+    local_hostname = socket.getfqdn()
+    singleset = set([ 
+        'database',
+        'username',
+        'hostname'
+        ])
+  
+    if singleset.issubset(settings):
+        # Process a single database configuration    
+        password = configure_db(settings['hostname'],
+                                settings['database'],
+                                settings['username'])        
+        relation_set(db_host=local_hostname,
+                     password=password)
+    else:
+        # Process multiple database setup requests.
+        # from incoming relation data:
+        #  nova_database=xxx nova_username=xxx nova_hostname=xxx
+        #  quantum_database=xxx quantum_username=xxx quantum_hostname=xxx
+        # create
+        #{ 
+        #   "nova": {
+        #        "username": xxx,
+        #        "database": xxx,
+        #        "hostname": xxx
+        #    },
+        #    "quantum": {
+        #        "username": xxx,
+        #        "database": xxx,
+        #        "hostname": xxx
+        #    }
+        #}
+        #        
+        databases = {}
+        for k, v in settings.iteritems():
+            db = k.split('_')[0]
+            x = '_'.join(k.split('_')[1:])
+            if db not in databases:
+                databases[db] = {}
+            databases[db][x] = v
+        return_data = {}
+        for db in databases:
+            if singleset.issubset(databases[db]):
+                return_data['_'.join([ db, 'password' ])] = \
+                    configure_db(databases[db]['hostname'],
+                                 databases[db]['database'],
+                                 databases[db]['username'])
+        relation_set(**return_data)
+        relation_set(db_host=local_hostname)
+
+hook = os.path.basename(sys.argv[0])
+hooks = {
+    "shared-db-relation-changed": shared_db_changed
+    }
+try:
+    hook_func = hooks[hook]
+except KeyError:
+    pass
+else:
+    hook_func()
+
+sys.exit(0)
\ No newline at end of file

=== modified file 'revision'
--- revision	2012-11-02 06:41:12 +0000
+++ revision	2012-12-03 15:24:32 +0000
@@ -1,1 +1,1 @@
-164
+165