def __init__(self, connection_url, charset):
self._databases = None
self.connection = DatabaseConnection()
self.connection.connect(connection_url, charset)
self.host = self.connection.host
self.port = self.connection.port
self.user = self.connection.user
self.version = self.connection.version
def __init__(self, connection_url):
self._databases = None
self.connection = DatabaseConnection()
self.connection.connect(connection_url)
self.host = self.connection.host
self.port = self.connection.port
self.user = self.connection.user
self.version = self.connection.version
async def build_database(self, connection_url, charset, table_names=None):
self.connection = DatabaseConnection()
await self.connection.connect(connection_url, charset)
self.version = await self.connection.version()
self.host = self.connection.host
self.port = self.connection.port
self.user = self.connection.user
self.table_names = table_names
self.databases = await database_schema_builder(instance=self)
class SchemaObject(object):
"""
Object representation of a single MySQL instance.
If database name is not specified in ``connection_url``,
all databases on the MySQL instance will be loaded.
``connection_url`` - the database url as per `RFC1738 <http://www.ietf.org/rfc/rfc1738.txt>`_
>>> schema = schemaobject.SchemaObject('mysql://*****:*****@localhost:3306/sakila', 'utf8')
>>> schema.host
'localhost'
>>> schema.port
3306
>>> schema.user
'username'
>>> schema.version
'5.1.30'
>>> schema.selected.name
'sakila'
"""
def __init__(self, connection_url, charset):
self._databases = None
self.connection = DatabaseConnection()
self.connection.connect(connection_url, charset)
self.host = self.connection.host
self.port = self.connection.port
self.user = self.connection.user
self.version = self.connection.version
@property
def selected(self):
"""
Returns the DatabaseSchema object associated with the database name in the connection url
>>> schema.selected.name
'sakila'
"""
if self.connection.db:
return self.databases[self.connection.db]
else:
return None
@property
def databases(self):
"""
Lazily loaded dictionary of the databases within this MySQL instance.
See DatabaseSchema for usage::
#if database name is specified in the connection url
>>> len(schema.databases)
1
>>> schema.databases.keys()
['sakila']
"""
if self._databases == None:
self._databases = DatabaseSchemaBuilder(instance=self)
return self._databases
class SchemaObject(object):
"""
Object representation of a single MySQL instance.
If database name is not specified in ``connection_url``,
all databases on the MySQL instance will be loaded.
``connection_url`` - the database url as per `RFC1738 <http://www.ietf.org/rfc/rfc1738.txt>`_
>>> schema = schemaobject.SchemaObject('mysql://*****:*****@localhost:3306/sakila')
>>> schema.host
'localhost'
>>> schema.port
3306
>>> schema.user
'username'
>>> schema.version
'5.1.30'
>>> schema.selected.name
'sakila'
"""
def __init__(self, connection_url):
self._databases = None
self.connection = DatabaseConnection()
self.connection.connect(connection_url)
self.host = self.connection.host
self.port = self.connection.port
self.user = self.connection.user
self.version = self.connection.version
@property
def selected(self):
"""
Returns the DatabaseSchema object associated with the database name in the connection url
>>> schema.selected.name
'sakila'
"""
if self.connection.db:
return self.databases[self.connection.db]
else:
return None
@property
def databases(self):
"""
Lazily loaded dictionary of the databases within this MySQL instance.
See DatabaseSchema for usage::
#if database name is specified in the connection url
>>> len(schema.databases)
1
>>> schema.databases.keys()
['sakila']
"""
if self._databases == None:
self._databases = DatabaseSchemaBuilder(instance=self)
return self._databases
def app(sourcedb='',
targetdb='',
version_filename=False,
output_directory=None,
log_directory=None,
no_date=False,
tag=None,
charset=None,
sync_auto_inc=False,
sync_comments=False):
"""Main Application"""
options = locals()
if not os.path.isabs(output_directory):
print "Error: Output directory must be an absolute path. Quiting."
return 1
if not os.path.isdir(output_directory):
print "Error: Output directory does not exist. Quiting."
return 1
if not log_directory or not os.path.isdir(log_directory):
if log_directory:
print "Log directory does not exist, writing log to %s" % output_directory
log_directory = output_directory
logging.basicConfig(filename=os.path.join(log_directory, LOG_FILENAME),
level=logging.INFO,
format='[%(levelname)s %(asctime)s] %(message)s')
console = logging.StreamHandler()
console.setLevel(logging.DEBUG)
if len(logging.getLogger('').handlers) <= 1:
logging.getLogger('').addHandler(console)
if not sourcedb:
logging.error("Source database URL not provided. Exiting.")
return 1
source_info = schemaobject.connection.parse_database_url(sourcedb)
if not source_info:
logging.error("Invalid source database URL format. Exiting.")
return 1
if not source_info['protocol'] == 'mysql':
logging.error("Source database must be MySQL. Exiting.")
return 1
if 'db' not in source_info:
logging.error("Source database name not provided. Exiting.")
return 1
if not targetdb:
logging.error("Target database URL not provided. Exiting.")
return 1
target_info = schemaobject.connection.parse_database_url(targetdb)
if not target_info:
logging.error("Invalid target database URL format. Exiting.")
return 1
if not target_info['protocol'] == 'mysql':
logging.error("Target database must be MySQL. Exiting.")
return 1
if 'db' not in target_info:
logging.error("Target database name not provided. Exiting.")
return 1
if source_info['db'] == '*' and target_info['db'] == '*':
from schemaobject.connection import DatabaseConnection
sourcedb_none = sourcedb[:-1]
targetdb_none = targetdb[:-1]
connection = DatabaseConnection()
connection.connect(sourcedb_none, charset='utf8')
sql_schema = """
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
"""
schemas = connection.execute(sql_schema)
for schema_info in schemas:
db = schema_info['SCHEMA_NAME']
sourcedb = sourcedb_none + db
targetdb = targetdb_none + db
try:
app(sourcedb=sourcedb,
targetdb=targetdb,
version_filename=version_filename,
output_directory=output_directory,
log_directory=log_directory,
no_date=no_date,
tag=tag,
charset=charset,
sync_auto_inc=sync_auto_inc,
sync_comments=sync_comments)
except schemaobject.connection.DatabaseError, e:
logging.error("MySQL Error %d: %s (Ignore)" %
(e.args[0], e.args[1]))
return 1
def app(sourcedb='',
targetdb='',
version_filename=False,
output_directory=None,
log_directory=None,
no_date=False,
tag=None,
charset=None,
sync_auto_inc=False,
sync_comments=False):
"""Main Application"""
options = locals()
if not os.path.isabs(output_directory):
print("Error: Output directory must be an absolute path. Quiting.")
return 1
if not os.path.isdir(output_directory):
print("Error: Output directory does not exist. Quiting.")
return 1
if not log_directory or not os.path.isdir(log_directory):
if log_directory:
print("Log directory does not exist, writing log to %s" %
output_directory)
log_directory = output_directory
logging.basicConfig(filename=os.path.join(log_directory, LOG_FILENAME),
level=logging.INFO,
format='[%(levelname)s %(asctime)s] %(message)s')
console = logging.StreamHandler()
console.setLevel(logging.DEBUG)
if len(logging.getLogger('').handlers) <= 1:
logging.getLogger('').addHandler(console)
if not sourcedb:
logging.error("Source database URL not provided. Exiting.")
return 1
source_info = schemaobject.connection.parse_database_url(sourcedb)
if not source_info:
logging.error("Invalid source database URL format. Exiting.")
return 1
if not source_info['protocol'] == 'mysql':
logging.error("Source database must be MySQL. Exiting.")
return 1
if 'db' not in source_info:
logging.error("Source database name not provided. Exiting.")
return 1
if not targetdb:
logging.error("Target database URL not provided. Exiting.")
return 1
target_info = schemaobject.connection.parse_database_url(targetdb)
if not target_info:
logging.error("Invalid target database URL format. Exiting.")
return 1
if not target_info['protocol'] == 'mysql':
logging.error("Target database must be MySQL. Exiting.")
return 1
if 'db' not in target_info:
logging.error("Target database name not provided. Exiting.")
return 1
if source_info['db'] == '*' and target_info['db'] == '*':
from schemaobject.connection import DatabaseConnection
sourcedb_none = sourcedb[:-1]
targetdb_none = targetdb[:-1]
connection = DatabaseConnection()
connection.connect(sourcedb_none, charset='utf8')
sql_schema = """
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
"""
schemas = connection.execute(sql_schema)
for schema_info in schemas:
db = schema_info['SCHEMA_NAME']
sourcedb = sourcedb_none + db
targetdb = targetdb_none + db
try:
app(sourcedb=sourcedb,
targetdb=targetdb,
version_filename=version_filename,
output_directory=output_directory,
log_directory=log_directory,
no_date=no_date,
tag=tag,
charset=charset,
sync_auto_inc=sync_auto_inc,
sync_comments=sync_comments)
except schemaobject.connection.DatabaseError as e:
logging.error("MySQL Error %d: %s (Ignore)" %
(e.args[0], e.args[1]))
return 1
source_obj = schemaobject.SchemaObject(sourcedb, charset)
target_obj = schemaobject.SchemaObject(targetdb, charset)
if utils.compare_version(source_obj.version, '5.0.0') < 0:
logging.error("%s requires MySQL version 5.0+ (source is v%s)" %
(APPLICATION_NAME, source_obj.version))
return 1
if utils.compare_version(target_obj.version, '5.0.0') < 0:
logging.error("%s requires MySQL version 5.0+ (target is v%s)" %
(APPLICATION_NAME, target_obj.version))
return 1
# data transformation filters
filters = (
lambda d: utils.REGEX_MULTI_SPACE.sub(' ', d),
lambda d: utils.REGEX_DISTANT_SEMICOLIN.sub(';', d),
lambda d: utils.REGEX_SEMICOLON_EXPLODE_TO_NEWLINE.sub(";\n", d))
# Information about this run, used in the patch/revert templates
ctx = dict(app_version=APPLICATION_VERSION,
server_version=target_obj.version,
target_host=target_obj.host,
target_database=target_obj.selected.name,
created=datetime.datetime.now().strftime(TPL_DATE_FORMAT))
p_fname, r_fname = utils.create_pnames(target_obj.selected.name,
tag=tag,
date_format=DATE_FORMAT,
no_date=no_date)
ctx['type'] = "Patch Script"
p_buffer = utils.PatchBuffer(name=os.path.join(output_directory, p_fname),
filters=filters,
tpl=PATCH_TPL,
ctx=ctx.copy(),
version_filename=version_filename)
ctx['type'] = "Revert Script"
r_buffer = utils.PatchBuffer(name=os.path.join(output_directory, r_fname),
filters=filters,
tpl=PATCH_TPL,
ctx=ctx.copy(),
version_filename=version_filename)
db_selected = False
for patch, revert in syncdb.sync_schema(source_obj.selected,
target_obj.selected, options):
if patch and revert:
if not db_selected:
p_buffer.write(target_obj.selected.select() + '\n')
r_buffer.write(target_obj.selected.select() + '\n')
p_buffer.write(target_obj.selected.fk_checks(0) + '\n')
r_buffer.write(target_obj.selected.fk_checks(0) + '\n')
db_selected = True
p_buffer.write(patch + '\n')
r_buffer.write(revert + '\n')
if db_selected:
p_buffer.write(target_obj.selected.fk_checks(1) + '\n')
r_buffer.write(target_obj.selected.fk_checks(1) + '\n')
for patch, revert in syncdb.sync_views(source_obj.selected,
target_obj.selected):
if patch and revert:
if not db_selected:
p_buffer.write(target_obj.selected.select() + '\n')
r_buffer.write(target_obj.selected.select() + '\n')
db_selected = True
p_buffer.write(patch + '\n')
r_buffer.write(revert + '\n')
for patch, revert in syncdb.sync_triggers(source_obj.selected,
target_obj.selected):
if patch and revert:
if not db_selected:
p_buffer.write(target_obj.selected.select() + '\n')
r_buffer.write(target_obj.selected.select() + '\n')
db_selected = True
p_buffer.write(patch + '\n')
r_buffer.write(revert + '\n')
for patch, revert in syncdb.sync_procedures(source_obj.selected,
target_obj.selected):
if patch and revert:
if not db_selected:
p_buffer.write(target_obj.selected.select() + '\n')
r_buffer.write(target_obj.selected.select() + '\n')
p_buffer.write(target_obj.selected.fk_checks(0) + '\n')
r_buffer.write(target_obj.selected.fk_checks(0) + '\n')
db_selected = True
p_buffer.write(patch + '\n')
r_buffer.write(revert + '\n')
if not p_buffer.modified:
logging.info(("No migration scripts written."
" mysql://%s/%s and mysql://%s/%s were in sync.") %
(source_obj.host, source_obj.selected.name,
target_obj.host, target_obj.selected.name))
else:
try:
p_buffer.save()
r_buffer.save()
logging.info("Migration scripts created for mysql://%s/%s\n"
"Patch Script: %s\nRevert Script: %s" %
(target_obj.host, target_obj.selected.name,
p_buffer.name, r_buffer.name))
except OSError as e:
p_buffer.delete()
r_buffer.delete()
logging.error("Failed writing migration scripts. %s" % e)
return 1
return 0