def delete_database(self, database):
"""Delete the specified database"""
client = LocalSqlClient(get_engine())
with client:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
t = text("""DROP DATABASE `%s`;""" % mydb.name)
client.execute(t)
def delete_database(self, database):
"""Delete the specified database"""
with LocalSqlClient(get_engine()) as client:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
dd = query.DropDatabase(mydb.name)
t = text(str(dd))
client.execute(t)
def create_database(self, databases):
"""Create the list of specified databases"""
with LocalSqlClient(get_engine()) as client:
for item in databases:
mydb = models.MySQLDatabase()
mydb.deserialize(item)
cd = query.CreateDatabase(mydb.name, mydb.character_set,
mydb.collate)
t = text(str(cd))
client.execute(t)
def delete(self, req, tenant_id, instance_id, id):
LOG.info(_("Deleting schema for instance '%s'") % instance_id)
LOG.info(_("req : '%s'\n\n") % req)
context = req.environ[wsgi.CONTEXT_KEY]
try:
schema = guest_models.MySQLDatabase()
schema.name = id
models.Schema.delete(context, instance_id, schema.serialize())
except (ValueError, AttributeError) as e:
raise exception.BadRequest(msg=e)
return wsgi.Result(None, 202)
def create_database(self, databases):
"""Create the list of specified databases"""
client = LocalSqlClient(get_engine())
with client:
for item in databases:
mydb = models.MySQLDatabase()
mydb.deserialize(item)
t = text("""CREATE DATABASE IF NOT EXISTS
`%s` CHARACTER SET = %s COLLATE = %s;""" %
(mydb.name, mydb.character_set, mydb.collate))
client.execute(t)
def load_with_client(cls, client, limit, marker, include_marker):
schemas, next_marker = client.list_databases(
limit=limit, marker=marker, include_marker=include_marker)
model_schemas = []
ignore_dbs = CONF.ignore_dbs
for schema in schemas:
mysql_schema = guest_models.MySQLDatabase()
mysql_schema.deserialize(schema)
if mysql_schema.name in ignore_dbs:
continue
model_schemas.append(
Schema(mysql_schema.name, mysql_schema.collate,
mysql_schema.character_set))
return model_schemas, next_marker
def list_users(self, limit=None, marker=None, include_marker=False):
"""List users that have access to the database"""
LOG.debug(_("---Listing Users---"))
users = []
client = LocalSqlClient(get_engine())
with client:
mysql_user = models.MySQLUser()
q = Query()
q.columns = ['User']
q.tables = ['mysql.user']
q.where = ["host != 'localhost'"]
q.order = ['User']
if marker:
q.where.append(
"User %s '%s'" %
(INCLUDE_MARKER_OPERATORS[include_marker], marker))
if limit:
q.limit = limit + 1
t = text(str(q))
result = client.execute(t)
next_marker = None
LOG.debug("result = " + str(result))
for count, row in enumerate(result):
if count >= limit:
break
LOG.debug("user = "******"^'(.+)'@", db['grantee'])
if (matches is not None
and matches.group(1) == mysql_user.name):
mysql_db = models.MySQLDatabase()
mysql_db.name = db['table_schema']
mysql_user.databases.append(mysql_db.serialize())
users.append(mysql_user.serialize())
if result.rowcount <= limit:
next_marker = None
LOG.debug("users = " + str(users))
return users, next_marker
def list_databases(self, limit=None, marker=None, include_marker=False):
"""List databases the user created on this mysql instance"""
LOG.debug(_("---Listing Databases---"))
databases = []
client = LocalSqlClient(get_engine())
with client:
# If you have an external volume mounted at /var/lib/mysql
# the lost+found directory will show up in mysql as a database
# which will create errors if you try to do any database ops
# on it. So we remove it here if it exists.
q = Query()
q.columns = [
'schema_name as name',
'default_character_set_name as charset',
'default_collation_name as collation',
]
q.tables = ['information_schema.schemata']
q.where = [
'''schema_name not in (
'mysql', 'information_schema',
'lost+found', '#mysql50#lost+found'
)'''
]
q.order = ['schema_name ASC']
if limit:
q.limit = limit + 1
if marker:
q.where.append(
"schema_name %s '%s'" %
(INCLUDE_MARKER_OPERATORS[include_marker], marker))
t = text(str(q))
database_names = client.execute(t)
next_marker = None
LOG.debug(_("database_names = %r") % database_names)
for count, database in enumerate(database_names):
if count >= limit:
break
LOG.debug(_("database = %s ") % str(database))
mysql_db = models.MySQLDatabase()
mysql_db.name = database[0]
next_marker = mysql_db.name
mysql_db.character_set = database[1]
mysql_db.collate = database[2]
databases.append(mysql_db.serialize())
LOG.debug(_("databases = ") + str(databases))
if database_names.rowcount <= limit:
next_marker = None
return databases, next_marker
def populate_databases(dbs):
"""
Create a serializable request with user provided data
for creating new databases.
"""
try:
databases = []
for database in dbs:
mydb = guest_models.MySQLDatabase()
mydb.name = database.get('name', '')
mydb.character_set = database.get('character_set', '')
mydb.collate = database.get('collate', '')
databases.append(mydb.serialize())
return databases
except ValueError as ve:
raise exception.BadRequest(str(ve))
def _associate_dbs(self, user):
"""Internal. Given a MySQLUser, populate its databases attribute."""
LOG.debug("Associating dbs to user %s at %s" % (user.name, user.host))
with LocalSqlClient(get_engine()) as client:
q = query.Query()
q.columns = ["grantee", "table_schema"]
q.tables = ["information_schema.SCHEMA_PRIVILEGES"]
q.group = ["grantee", "table_schema"]
q.where = ["privilege_type != 'USAGE'"]
t = text(str(q))
db_result = client.execute(t)
for db in db_result:
LOG.debug("\t db: %s" % db)
if db['grantee'] == "'%s'@'%s'" % (user.name, user.host):
mysql_db = models.MySQLDatabase()
mysql_db.name = db['table_schema']
user.databases.append(mysql_db.serialize())
def create_user(self, users):
"""Create users and grant them privileges for the
specified databases"""
host = "%"
client = LocalSqlClient(get_engine())
with client:
for item in users:
user = models.MySQLUser()
user.deserialize(item)
# TODO(cp16net):Should users be allowed to create users
# 'os_admin' or 'debian-sys-maint'
t = text("""GRANT USAGE ON *.* TO '%s'@\"%s\"
IDENTIFIED BY '%s';""" %
(user.name, host, user.password))
client.execute(t)
for database in user.databases:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
t = text("""
GRANT ALL PRIVILEGES ON `%s`.* TO `%s`@:host;
""" % (mydb.name, user.name))
client.execute(t, host=host)
def create_user(self, users):
"""Create users and grant them privileges for the
specified databases"""
with LocalSqlClient(get_engine()) as client:
for item in users:
user = models.MySQLUser()
user.deserialize(item)
# TODO(cp16net):Should users be allowed to create users
# 'os_admin' or 'debian-sys-maint'
g = query.Grant(user=user.name,
host=user.host,
clear=user.password)
t = text(str(g))
client.execute(t)
for database in user.databases:
mydb = models.MySQLDatabase()
mydb.deserialize(database)
g = query.Grant(permissions='ALL',
database=mydb.name,
user=user.name,
host=user.host,
clear=user.password)
t = text(str(g))
client.execute(t)
def setUp(self):
super(MySQLDatabaseTest, self).setUp()
self.mysqlDb = dbmodels.MySQLDatabase()
self.origin_ignore_db = self.mysqlDb._ignore_dbs
self.mysqlDb._ignore_dbs = ['mysql']
