async def database_schema_builder(instance):
"""
Returns a dictionary loaded with all of the databases availale on
the MySQL instance. ``instance`` must be an instance SchemaObject.
.. note::
This function is automatically called for you and set to
``schema.databases`` when you create an instance of SchemaObject
"""
conn = instance.connection
d = OrderedDict()
sql = """
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
"""
if conn.db:
sql += " WHERE SCHEMA_NAME = %s"
params = conn.db
else:
params = None
databases = await conn.execute(sql, (params,))
if not databases:
return d
tasks = []
for db_info in databases:
name = db_info['SCHEMA_NAME']
db = DatabaseSchema(name=name, parent=instance)
db.options['charset'] = SchemaOption("CHARACTER SET", db_info['DEFAULT_CHARACTER_SET_NAME'])
db.options['collation'] = SchemaOption("COLLATE", db_info['DEFAULT_COLLATION_NAME'])
d[name] = db
cot = db.build_tables()
cov = db.build_views()
cop = db.build_procedures()
cor = db.build_triggers()
tasks.extend([asyncio.ensure_future(cot),
asyncio.ensure_future(cov),
asyncio.ensure_future(cop),
asyncio.ensure_future(cor)])
await asyncio.wait(tasks)
return d
def ProcedureSchemaBuilder(database):
"""
Returns a dictionary loaded with all of the tables available in the database.
``database`` must be an instance of DatabaseSchema.
.. note::
This function is automatically called for you and set to
``scheme.databases[name].procedures`` when you create an instance of SchemaObject
"""
conn = database.parent.connection
sp = OrderedDict()
sql = """
SELECT ROUTINE_NAME, ROUTINE_DEFINITION, ROUTINE_COMMENT,
SECURITY_TYPE, SQL_MODE,
CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
FROM information_schema.`ROUTINES`
WHERE ROUTINE_SCHEMA = '%s'
AND ROUTINE_TYPE ='procedure'
"""
procedures = conn.execute(sql % database.name)
if not procedures:
return sp
for procedure_info in procedures:
name = procedure_info['ROUTINE_NAME']
if "COLLATION_CONNECTION" not in procedure_info:
charset = None
pos = procedure_info['COLLATION_CONNECTION'].find('_')
if not pos:
charset = procedure_info['COLLATION_CONNECTION']
else:
charset = procedure_info['COLLATION_CONNECTION'][:pos]
procedure = ProcedureSchema(name=name, parent=database)
procedure.options['definition'] = SchemaOption(
'DEFINITION', procedure_info['ROUTINE_DEFINITION'])
procedure.options['charset'] = SchemaOption('COLLATE', charset)
procedure.options['comment'] = SchemaOption(
'COMMENT', procedure_info['ROUTINE_COMMENT'])
sp[name] = procedure
return sp
def DatabaseSchemaBuilder(instance):
"""
Returns a dictionary loaded with all of the databases availale on
the MySQL instance. ``instance`` must be an instance SchemaObject.
.. note::
This function is automatically called for you and set to
``schema.databases`` when you create an instance of SchemaObject
"""
conn = instance.connection
d = OrderedDict()
sql = """
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
"""
if conn.db:
sql += " WHERE SCHEMA_NAME = %s"
# VoltDB patch: Made it a list, as required by conn.execute().
params = [conn.db]
else:
params = None
databases = conn.execute(sql, params)
if not databases:
return d
for db_info in databases:
name = db_info['SCHEMA_NAME']
db = DatabaseSchema(name=name, parent=instance)
db.options['charset'] = SchemaOption(
"CHARACTER SET", db_info['DEFAULT_CHARACTER_SET_NAME'])
db.options['collation'] = SchemaOption(
"COLLATE", db_info['DEFAULT_COLLATION_NAME'])
d[name] = db
return d
def TableSchemaBuilder(database):
"""
Returns a dictionary loaded with all of the tables available in the database.
``database`` must be an instance of DatabaseSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables`` when you create an instance of SchemaObject
"""
conn = database.parent.connection
t = OrderedDict()
sql = """
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, AUTO_INCREMENT,
CREATE_OPTIONS, TABLE_COLLATION, TABLE_COMMENT
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA='%s'
AND not isnull(ENGINE)
"""
tables = conn.execute(sql % database.name)
if not tables:
return t
for table_info in tables:
name = table_info['TABLE_NAME']
if "TABLE_COLLATION" not in table_info:
charset = None
pos = table_info['TABLE_COLLATION'].find('_')
if not pos:
charset = table_info['TABLE_COLLATION']
else:
charset = table_info['TABLE_COLLATION'][:pos]
table = TableSchema(name=name, parent=database)
table.options['engine'] = SchemaOption('ENGINE', table_info['ENGINE'])
table.options['charset'] = SchemaOption("CHARSET", charset)
table.options['collation'] = SchemaOption(
"COLLATE", table_info['TABLE_COLLATION'])
table.options['row_format'] = SchemaOption('ROW_FORMAT',
table_info['ROW_FORMAT'])
table.options['auto_increment'] = SchemaOption(
'AUTO_INCREMENT', table_info['AUTO_INCREMENT'])
table.options['create_options'] = SchemaOption(
None, table_info['CREATE_OPTIONS'])
table.options['comment'] = SchemaOption('COMMENT',
table_info['TABLE_COMMENT'])
t[name] = table
return t
def test_neq(self):
opt = SchemaOption('key', 'value1')
opt2 = SchemaOption('key', 'value2')
assert opt != opt2
def test_set_value(self):
opt = SchemaOption("key")
assert opt.value == None
opt.value = "value"
assert opt.value == "value"
def test_create_value_non_string(self):
opt = SchemaOption("key", 1234)
assert opt.create() == "key=1234"
def test_create_value_string_without_spaces(self):
opt = SchemaOption("key", "value")
assert opt.create() == "key=value"
def test_create_no_name(self):
opt = SchemaOption(None, "value")
assert opt.create() == "value"
def test_create_no_value(self):
opt = SchemaOption("key")
assert opt.create() == ""
def test_set_value(self):
opt = SchemaOption('key')
assert opt.value == None
opt.value = "value"
assert opt.value == "value"
def test_get_value(self):
opt = SchemaOption('key', 'value')
assert opt.value == "value"
def test_eq(self):
opt = SchemaOption('key', 'value')
opt2 = SchemaOption('key', 'value')
assert opt == opt2
async def table_schema_builder(database):
"""
Returns a dictionary loaded with all of the tables available in the database.
``database`` must be an instance of DatabaseSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables`` when you create an instance of SchemaObject
"""
conn = database.parent.connection
t = OrderedDict()
sql = """
SELECT TABLE_NAME, ENGINE, ROW_FORMAT, AUTO_INCREMENT,
CREATE_OPTIONS, TABLE_COLLATION, TABLE_COMMENT
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA='%s'
AND not isnull(ENGINE)
"""
table_names = database.parent.table_names
if table_names:
table_names = list(map(lambda s: '\'%s\'' % s, table_names.split(',')))
sql += "AND TABLE_NAME IN (%s)" % ','.join(table_names)
tables = await conn.execute(sql % database.name)
if not tables:
return t
tasks = []
for table_info in tables:
name = table_info['TABLE_NAME']
if "TABLE_COLLATION" not in table_info:
charset = None
pos = table_info['TABLE_COLLATION'].find('_')
if not pos:
charset = table_info['TABLE_COLLATION']
else:
charset = table_info['TABLE_COLLATION'][:pos]
table = TableSchema(name=name, parent=database)
table.options['engine'] = SchemaOption('ENGINE', table_info['ENGINE'])
table.options['charset'] = SchemaOption("CHARSET", charset)
table.options['collation'] = SchemaOption(
"COLLATE", table_info['TABLE_COLLATION'])
table.options['row_format'] = SchemaOption('ROW_FORMAT',
table_info['ROW_FORMAT'])
table.options['auto_increment'] = SchemaOption(
'AUTO_INCREMENT', table_info['AUTO_INCREMENT'])
table.options['create_options'] = SchemaOption(
None, table_info['CREATE_OPTIONS'])
table.options['comment'] = SchemaOption('COMMENT',
table_info['TABLE_COMMENT'])
t[name] = table
# await table.build_columns()
# await table.build_create()
# await table.build_indexes()
# await table.build_foreign_keys()
tasks.extend([
asyncio.ensure_future(table.build_columns()),
asyncio.ensure_future(table.build_create()),
asyncio.ensure_future(table.build_indexes()),
# TODO 外键太慢,暂时注释掉
# asyncio.ensure_future(table.build_foreign_keys())
])
await asyncio.wait(tasks)
return t
def test_create_value_string_without_spaces(self):
opt = SchemaOption('key', 'value')
assert opt.create() == 'key=value'
def test_create_value_string_with_spaces(self):
opt = SchemaOption('key', 'my value')
assert opt.create() == "key='my value'"
def test_create_value_non_string(self):
opt = SchemaOption('key', 1234)
assert opt.create() == 'key=1234'
def test_create_no_value(self):
opt = SchemaOption('key')
assert opt.create() == ''
def test_create_no_name(self): opt = SchemaOption(None, 'value') assert opt.create() == 'value'