def trigger_schema_builder(database):
conn = database.parent.connection
t = OrderedDict()
sql = """
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
ACTION_STATEMENT, ACTION_TIMING
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='%s'
"""
triggers = conn.execute(sql % database.name)
if not triggers:
return t
for trigger in triggers:
trig_name = trigger['TRIGGER_NAME']
trig = TriggerSchema(name=trig_name, parent=database)
body = trigger['ACTION_STATEMENT']
trig.statement = re.sub('\s\s+', ' ', body)
trig.timing = trigger['ACTION_TIMING']
trig.event = trigger['EVENT_MANIPULATION']
trig.table = trigger['EVENT_OBJECT_TABLE']
t[trig_name] = trig
return t
def view_schema_builder(database):
conn = database.parent.connection
v = OrderedDict()
sql = """
SELECT TABLE_NAME
FROM information_schema.views
WHERE TABLE_SCHEMA = '%s'
ORDER BY TABLE_NAME
"""
views = conn.execute(sql % database.name)
if not views:
return v
for view in views:
vname = view['TABLE_NAME']
sql = "SHOW CREATE VIEW %s"
view_desc = conn.execute(sql % vname)
if not view_desc:
continue
view_desc = view_desc[0]
vv = ViewSchema(name=vname, parent=database)
s = re.search('\(?select', view_desc['Create View'], re.IGNORECASE)
if not s:
continue
vv.definition = view_desc['Create View'][s.start():]
v[vname] = vv
return v
class TestOrderedDict(unittest.TestCase):
def setUp(self):
self.test = OrderedDict()
self.test['name'] = "John Smith"
self.test['location'] = "New York"
def test_eq_dict_keys(self):
self.assertEqual( ['name', 'location'], list(self.test.keys()) )
def test_neq_dict_keys(self):
self.assertNotEqual( ['location', 'name'], list(self.test.keys()) )
def test_eq_dict_items(self):
self.assertEqual( [('name', 'John Smith'), ('location', 'New York')], list(self.test.items()) )
def test_neq_dict_items(self):
self.assertNotEqual( [('location', 'New York'), ('name', 'John Smith')], list(self.test.items()) )
def test_dict_iterkeys(self):
for i, v in enumerate(self.test.keys()):
if i == 0:
self.assertEqual(v, 'name')
if i == 1:
self.assertEqual(v, 'location')
def test_dict_iteritems(self):
for i, v in enumerate(self.test.items()):
if i == 0:
self.assertEqual(v, ('name', 'John Smith'))
if i == 1:
self.assertEqual(v, ('location', 'New York'))
def test_index(self):
self.assertEqual(1, self.test.index("location"))
self.assertRaises(ValueError, self.test.index, ("unknown_key",))
def test_insert(self):
self.assertFalse("age" in self.test)
self.test.insert(1, ("age", 100))
self.assertTrue(1, self.test.index("age"))
self.assertTrue(2, self.test.index("location"))
def test__delitem__(self):
self.assertTrue("location" in self.test)
del self.test['location']
self.assertFalse("location" in self.test)
def options(self):
"""
Dictionary of the supported MySQL database options. See OptionSchema for usage.
* CHARACTER SET == ``options['charset']``
* COLLATE == ``options['collation']``
"""
if self._options == None:
self._options = OrderedDict()
return self._options
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 IndexSchemaBuilder(table):
"""
Returns a dictionary loaded with all of the indexes available in the table.
``table`` must be an instance of TableSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables[name].indexes`` when you create an instance of SchemaObject
"""
conn = table.parent.parent.connection
idx = OrderedDict()
indexes = conn.execute("SHOW INDEXES FROM `%s`.`%s`" %
(table.parent.name, table.name))
if not indexes:
return idx
for index in indexes:
n = index['Key_name']
if n not in idx:
Indexitem = IndexSchema(name=n, parent=table)
Indexitem.non_unique = (bool(index['Non_unique'])) # == not unique
Indexitem.table_name = index['Table']
key_type = index['Index_type'].upper()
if index['Key_name'].upper() == "PRIMARY":
Indexitem.kind = "PRIMARY"
elif not Indexitem.non_unique:
Indexitem.kind = "UNIQUE"
elif key_type in ('FULLTEXT', 'SPATIAL'):
Indexitem.kind = key_type
else:
Indexitem.kind = "INDEX"
if key_type in ('BTREE', 'HASH', 'RTREE'):
Indexitem.type = key_type
Indexitem.collation = index['Collation']
Indexitem.comment = index['Comment']
idx[n] = Indexitem
if index['Column_name'] not in idx[n].fields:
idx[n].fields.insert(
index['Seq_in_index'],
(index['Column_name'], index['Sub_part'] or 0))
return idx
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 options(self):
"""
Dictionary of the supported MySQL table options. See OptionSchema for usage.
* ENGINE == ``options['engine']``
* CHARSET, CHARACTER SET == ``options['charset']``
* COLLATE == ``options['collation']``
* ROW_FORMAT == ``options['row_format']``
* AUTO_INCREMENT == ``options['auto_increment']``
* CREATE_OPTIONS == ``options['create_options']``
* COMMENT == ``options['comment']``
"""
if self._options == None:
self._options = OrderedDict()
return self._options
def column_schema_builder(table):
"""
Returns a dictionary loaded with all of the columns availale in the table.
``table`` must be an instance of TableSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables[name].columns``
when you create an instance of SchemaObject
"""
conn = table.parent.parent.connection
cols = OrderedDict()
sql = """
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT,
IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, CHARACTER_MAXIMUM_LENGTH,
CHARACTER_SET_NAME, COLLATION_NAME, EXTRA, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='%s'
AND TABLE_NAME='%s'
ORDER BY ORDINAL_POSITION
"""
columns = conn.execute(sql % (table.parent.name, table.name))
if not columns:
return cols
for col in columns:
field = col['COLUMN_NAME']
column = ColumnSchema(name=field, parent=table)
column.ordinal_position = col['ORDINAL_POSITION']
column.field = col['COLUMN_NAME']
column.type = col['COLUMN_TYPE']
column.charset = col['CHARACTER_SET_NAME']
column.collation = col['COLLATION_NAME']
column.key = col['COLUMN_KEY']
column.default = col['COLUMN_DEFAULT']
column.extra = col['EXTRA']
column.comment = col['COLUMN_COMMENT']
if col['IS_NULLABLE'] == "YES":
column.null = True
else:
column.null = False
cols[field] = column
return cols
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 procedure_schema_builder(database):
conn = database.parent.connection
p = OrderedDict()
sql = """
SELECT ROUTINE_NAME
FROM information_schema.routines
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_SCHEMA='%s'
"""
procedures = conn.execute(sql % database.name)
if not procedures:
return p
for procedure in procedures:
pname = procedure['ROUTINE_NAME']
sql = "SHOW CREATE PROCEDURE %s"
proc_desc = conn.execute(sql % pname)
if not proc_desc:
continue
proc_desc = proc_desc[0]
pp = ProcedureSchema(name=pname, parent=database)
if not proc_desc['Create Procedure']:
pp.definition = "() BEGIN SELECT 'Cannot access to mysql.proc in source DB'; END"
pp.raw_definition = pp.definition
else:
s = re.search('\(', proc_desc['Create Procedure'])
if not s:
continue
definition = re.sub('--.*',
'',
proc_desc['Create Procedure'][s.start():])
pp.definition = re.sub('\s+', ' ', definition)
pp.raw_definition = proc_desc['Create Procedure'][s.start():]
p[pname] = pp
return p
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
class TestOrderedDict(unittest.TestCase):
def setUp(self):
self.test = OrderedDict()
self.test['name'] = "John Smith"
self.test['location'] = "New York"
def test_eq_dict_keys(self):
self.assertEqual(['name', 'location'], list(self.test.keys()))
def test_neq_dict_keys(self):
self.assertNotEqual(['location', 'name'], list(self.test.keys()))
def test_eq_dict_items(self):
self.assertEqual([('name', 'John Smith'), ('location', 'New York')],
list(self.test.items()))
def test_neq_dict_items(self):
self.assertNotEqual([('location', 'New York'), ('name', 'John Smith')],
list(self.test.items()))
def test_dict_iterkeys(self):
for i, v in enumerate(self.test.keys()):
if i == 0:
self.assertEqual(v, 'name')
if i == 1:
self.assertEqual(v, 'location')
def test_dict_iteritems(self):
for i, v in enumerate(self.test.items()):
if i == 0:
self.assertEqual(v, ('name', 'John Smith'))
if i == 1:
self.assertEqual(v, ('location', 'New York'))
def test_index(self):
self.assertEqual(1, self.test.index("location"))
self.assertRaises(ValueError, self.test.index, ("unknown_key", ))
def test_insert(self):
self.assertFalse("age" in self.test)
self.test.insert(1, ("age", 100))
self.assertTrue(1, self.test.index("age"))
self.assertTrue(2, self.test.index("location"))
def test__delitem__(self):
self.assertTrue("location" in self.test)
del self.test['location']
self.assertFalse("location" in self.test)
def foreign_key_schema_builder(table):
"""
Returns a dictionary loaded with all of the foreign keys available in the table.
``table`` must be an instance of TableSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables[name].foreign_keys`` when you create an instance of SchemaObject
"""
conn = table.parent.parent.connection
fkeys = OrderedDict()
def _get_reference_rules(information_schema, table_name, constraint_name):
"""
Returns tuple of strings (update_rule, delete_rule)
(None,None) if constraint not found
"""
# select UPDATE_RULE, DELETE_RULE from information_schema.REFERENTIAL_CONSTRAINTS where CONSTRAINT_SCHEMA = 'sakila' and TABLE_NAME = 'payment' and CONSTRAINT_NAME = 'fk_payment_customer';
sql = """
SELECT UPDATE_RULE,
DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = '%s' and TABLE_NAME = '%s' and CONSTRAINT_NAME = '%s'
"""
result = conn.execute(
sql % (information_schema, table_name, constraint_name))
if result:
return result[0]['UPDATE_RULE'], result[0]['DELETE_RULE']
else:
return None, None
sql = """
SELECT K.CONSTRAINT_NAME,
K.TABLE_SCHEMA, K.TABLE_NAME, K.COLUMN_NAME,
K.REFERENCED_TABLE_SCHEMA, K.REFERENCED_TABLE_NAME, K.REFERENCED_COLUMN_NAME,
K.POSITION_IN_UNIQUE_CONSTRAINT
FROM information_schema.KEY_COLUMN_USAGE K, information_schema.TABLE_CONSTRAINTS T
WHERE K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
AND T.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND K.CONSTRAINT_SCHEMA='%s'
AND K.TABLE_NAME='%s'
AND K.REFERENCED_TABLE_NAME is not null
"""
constraints = conn.execute(sql % (table.parent.name, table.name))
if not constraints:
return fkeys
for fk in constraints:
n = fk['CONSTRAINT_NAME']
if n not in fkeys:
fk_item = ForeignKeySchema(name=n, parent=table)
fk_item.symbol = n
fk_item.table_schema = fk['TABLE_SCHEMA']
fk_item.table_name = fk['TABLE_NAME']
fk_item.referenced_table_schema = fk['REFERENCED_TABLE_SCHEMA']
fk_item.referenced_table_name = fk['REFERENCED_TABLE_NAME']
fk_item.update_rule, fk_item.delete_rule = _get_reference_rules(
fk_item.table_schema, fk_item.table_name, fk_item.symbol)
fkeys[n] = fk_item
# POSITION_IN_UNIQUE_CONSTRAINT may be None
pos = fk['POSITION_IN_UNIQUE_CONSTRAINT'] or 0
# columns for this fk
if fk['COLUMN_NAME'] not in fkeys[n].columns:
fkeys[n].columns.insert(pos, fk['COLUMN_NAME'])
# referenced columns for this fk
if fk['REFERENCED_COLUMN_NAME'] not in fkeys[n].referenced_columns:
fkeys[n].referenced_columns.insert(pos,
fk['REFERENCED_COLUMN_NAME'])
return fkeys
def options(self):
if self._options == None:
self._options = OrderedDict()
return self._options
def setUp(self):
self.test = OrderedDict()
self.test['name'] = "John Smith"
self.test['location'] = "New York"
def setUp(self):
self.test = OrderedDict()
self.test['name'] = "John Smith"
self.test['location'] = "New York"
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 ForeignKeySchemaBuilder(table):
"""
Returns a dictionary loaded with all of the foreign keys available in the table.
``table`` must be an instance of TableSchema.
.. note::
This function is automatically called for you and set to
``schema.databases[name].tables[name].foreign_keys`` when you create an instance of SchemaObject
"""
conn = table.parent.parent.connection
fkeys = OrderedDict()
sql = """
SELECT K.CONSTRAINT_NAME,
K.TABLE_SCHEMA, K.TABLE_NAME, K.COLUMN_NAME,
K.REFERENCED_TABLE_SCHEMA, K.REFERENCED_TABLE_NAME, K.REFERENCED_COLUMN_NAME,
K.POSITION_IN_UNIQUE_CONSTRAINT
FROM information_schema.KEY_COLUMN_USAGE K, information_schema.TABLE_CONSTRAINTS T
WHERE K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
AND T.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND K.CONSTRAINT_SCHEMA='%s'
AND K.TABLE_NAME='%s'
"""
constraints = conn.execute(sql % (table.parent.name, table.name))
if not constraints:
return fkeys
table_def = conn.execute(
"SHOW CREATE TABLE `%s`.`%s`" %
(table.parent.name, table.name))[0]['Create Table']
for fk in constraints:
n = fk['CONSTRAINT_NAME']
if n not in fkeys:
FKItem = ForeignKeySchema(name=n, parent=table)
FKItem.symbol = n
FKItem.table_schema = fk['TABLE_SCHEMA']
FKItem.table_name = fk['TABLE_NAME']
FKItem.referenced_table_schema = fk['REFERENCED_TABLE_SCHEMA']
FKItem.referenced_table_name = fk['REFERENCED_TABLE_NAME']
reference_options = re.search(REGEX_FK_REFERENCE_OPTIONS % n,
table_def, re.X)
if reference_options:
#If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT.
FKItem.update_rule = reference_options.group(
'on_update') or 'RESTRICT'
FKItem.delete_rule = reference_options.group(
'on_delete') or 'RESTRICT'
fkeys[n] = FKItem
#columns for this fk
if fk['COLUMN_NAME'] not in fkeys[n].columns:
fkeys[n].columns.insert(fk['POSITION_IN_UNIQUE_CONSTRAINT'],
fk['COLUMN_NAME'])
#referenced columns for this fk
if fk['REFERENCED_COLUMN_NAME'] not in fkeys[n].referenced_columns:
fkeys[n].referenced_columns.insert(
fk['POSITION_IN_UNIQUE_CONSTRAINT'],
fk['REFERENCED_COLUMN_NAME'])
return fkeys