def _drop_database(self):
sql = """\
SELECT 'DROP PUBLIC SYNONYM ' || SYNONYM_NAME ||';' FROM ALL_SYNONYMS \
WHERE OWNER = 'PUBLIC' AND TABLE_OWNER = '%s' \
UNION ALL \
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM ALL_SYNONYMS \
WHERE OWNER = '%s' AND TABLE_OWNER = '%s' \
UNION ALL \
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';' FROM USER_OBJECTS \
WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND \
OBJECT_TYPE<>'TRIGGER' AND OBJECT_TYPE<>'LOB' \
UNION ALL \
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;' FROM USER_OBJECTS \
WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME NOT LIKE 'BIN$%%'""" % (self.__user.upper(), self.__user.upper(), self.__user.upper())
conn = self.__connect()
cursor = conn.cursor()
try:
cursor.execute(sql)
rows = cursor.fetchall()
failed_sqls = ''
for row in rows:
drop_sql = row[0]
try:
self.__execute(drop_sql)
except Exception, e:
failed_sqls = failed_sqls + "can't execute drop command '%s' in database '%s', %s\n" % (drop_sql, self.__db, str(e).strip())
if failed_sqls != '':
CLI.msg('\nThe following drop commands failed:\n%s' % (failed_sqls), "RED")
CLI.msg('\nDo you want to continue anyway (y/N):', "END")
to_continue = self.std_in.readline().strip()
if to_continue.upper() != 'Y':
raise Exception("can't drop database objects for user '%s'" % (self.__user) )
def _verify_if_exception_is_invalid_user(self, exception):
import pdb
pdb.set_trace()
#TODO validar como isso funciona no mongodb
if 'ORA-01017' in exception.__str__():
try:
cli = CLI()
cli.msg(
'\nPlease inform dba user/password to connect to database "%s"\nUser:'
% (self.__host), "END")
dba_user = self.std_in.readline().strip()
passwd = self.get_pass()
conn = self.__driver.connect(dsn=self.__host,
user=dba_user,
password=passwd)
cursor = conn.cursor()
cursor.execute("create user %s identified by %s" %
(self.__user, self.__passwd))
cursor.execute("grant connect, resource to %s" % (self.__user))
cursor.execute("grant create public synonym to %s" %
(self.__user))
cursor.execute("grant drop public synonym to %s" %
(self.__user))
cursor.close()
conn.close()
except Exception, e:
raise Exception("check error: %s" % e)
def _drop_database(self):
sql = """\
SELECT 'DROP PUBLIC SYNONYM ' || SYNONYM_NAME ||';' FROM ALL_SYNONYMS \
WHERE OWNER = 'PUBLIC' AND TABLE_OWNER = '%s' \
UNION ALL \
SELECT 'DROP SYNONYM ' || SYNONYM_NAME ||';' FROM ALL_SYNONYMS \
WHERE OWNER = '%s' AND TABLE_OWNER = '%s' \
UNION ALL \
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';' FROM USER_OBJECTS \
WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND \
OBJECT_TYPE<>'TRIGGER' AND OBJECT_TYPE<>'LOB' \
UNION ALL \
SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;' FROM USER_OBJECTS \
WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME NOT LIKE 'BIN$%%'""" % (self.__user.upper(), self.__user.upper(), self.__user.upper())
conn = self.__connect()
cursor = conn.cursor()
try:
cursor.execute(sql)
rows = cursor.fetchall()
failed_sqls = ''
for row in rows:
drop_sql = row[0]
try:
self.__execute(drop_sql)
except Exception, e:
failed_sqls = failed_sqls + "can't execute drop command '%s' in database '%s', %s\n" % (drop_sql, self.__db, str(e).strip())
if failed_sqls != '':
CLI.msg('\nThe following drop commands failed:\n%s' % (failed_sqls), "RED")
CLI.msg('\nDo you want to continue anyway (y/N):', "END")
to_continue = self.std_in.readline().strip()
if to_continue.upper() != 'Y':
raise Exception("can't drop database '%s'" % (self.__db) )
def run():
cli = CLI()
try:
(options, args) = cli.parse()
if options.simple_db_migrate_version:
msg = 'simple-db-migrate v%s' % SIMPLE_DB_MIGRATE_VERSION
cli.info_and_exit(msg)
if options.show_colors:
CLI.show_colors()
# Create config
config = FileConfig(options.config_file, options.environment)
config.put('schema_version', options.schema_version)
config.put('show_sql', options.show_sql)
config.put('show_sql_only', options.show_sql_only)
config.put('new_migration', options.new_migration)
config.put('drop_db_first', options.drop_db_first)
config.put('paused_mode', options.paused_mode)
config.put('log_dir', options.log_dir)
config.put('label_version', options.label_version)
config.put('force_use_files_on_down', options.force_use_files_on_down)
config.put('force_execute_old_migrations_versions',
options.force_execute_old_migrations_versions)
# paused mode forces log_level to 2
log_level = int(options.log_level)
if options.paused_mode:
log_level = 2
config.put('log_level', log_level)
# Ask the password for user if configured
if config.get('db_password') == '<<ask_me>>':
if options.password:
passwd = options.password
else:
cli.msg(
'\nPlease inform password to connect to database "%s@%s:%s"'
% (config.get('db_user'), config.get('db_host'),
config.get('db_name')))
passwd = getpass()
config.remove('db_password')
config.put('db_password', passwd)
# If CLI was correctly parsed, execute db-migrate.
Main(config).execute()
except KeyboardInterrupt:
cli.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
cli.error_and_exit(str(e))
def _verify_if_exception_is_invalid_user(self, exception):
if 'ORA-01017' in exception.__str__():
CLI.msg('\nPlease inform dba user/password to connect to database "%s"\nUser:' % (self.__host), "END")
dba_user = self.std_in.readline().strip()
passwd = self.get_pass()
conn = self.__driver.connect(dsn=self.__host, user=dba_user, password=passwd)
cursor = conn.cursor()
try:
cursor.execute("create user %s identified by %s" % (self.__user, self.__passwd))
cursor.execute("grant connect, resource to %s" % (self.__user))
cursor.execute("grant create public synonym to %s" % (self.__user))
cursor.execute("grant drop public synonym to %s" % (self.__user))
except Exception, e:
raise Exception("check error: %s" % e)
finally:
def _verify_if_exception_is_invalid_user(self, exception):
if 'ORA-01017' in exception.__str__():
CLI.msg('\nPlease inform dba user/password to connect to database "%s"\nUser:' % (self.__host), "END")
dba_user = self.std_in.readline().strip()
passwd = self.get_pass()
conn = self.__driver.connect(dsn=self.__host, user=dba_user, password=passwd)
cursor = conn.cursor()
try:
cursor.execute("create user %s identified by %s" % (self.__user, self.__passwd))
cursor.execute("grant connect, resource to %s" % (self.__user))
cursor.execute("grant create public synonym to %s" % (self.__user))
cursor.execute("grant drop public synonym to %s" % (self.__user))
except Exception, e:
raise Exception("check error: %s" % e)
finally:
def run():
cli = CLI()
try:
(options, args) = cli.parse()
if options.simple_db_migrate_version:
msg = 'simple-db-migrate v%s' % SIMPLE_DB_MIGRATE_VERSION
cli.info_and_exit(msg)
if options.show_colors:
CLI.show_colors()
# Create config
config = FileConfig(options.config_file, options.environment)
config.put('schema_version', options.schema_version)
config.put('show_sql', options.show_sql)
config.put('show_sql_only', options.show_sql_only)
config.put('new_migration', options.new_migration)
config.put('drop_db_first', options.drop_db_first)
config.put('paused_mode', options.paused_mode)
config.put('log_dir', options.log_dir)
config.put('label_version', options.label_version)
config.put('force_use_files_on_down', options.force_use_files_on_down)
config.put('force_execute_old_migrations_versions', options.force_execute_old_migrations_versions)
# paused mode forces log_level to 2
log_level = int(options.log_level)
if options.paused_mode:
log_level = 2
config.put('log_level', log_level)
# Ask the password for user if configured
if config.get('db_password') == '<<ask_me>>':
if options.password:
passwd = options.password
else:
cli.msg('\nPlease inform password to connect to database "%s@%s:%s"' % (config.get('db_user'), config.get('db_host'), config.get('db_name')))
passwd = getpass()
config.remove('db_password')
config.put('db_password', passwd)
# If CLI was correctly parsed, execute db-migrate.
Main(config).execute()
except KeyboardInterrupt:
cli.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
cli.error_and_exit(str(e))
def _verify_if_exception_is_invalid_user(self, exception):
import pdb;pdb.set_trace()
#TODO validar como isso funciona no mongodb
if 'ORA-01017' in exception.__str__():
try:
cli = CLI()
cli.msg('\nPlease inform dba user/password to connect to database "%s"\nUser:' % (self.__host), "END")
dba_user = self.std_in.readline().strip()
passwd = self.get_pass()
conn = self.__driver.connect(dsn=self.__host, user=dba_user, password=passwd)
cursor = conn.cursor()
cursor.execute("create user %s identified by %s" % (self.__user, self.__passwd))
cursor.execute("grant connect, resource to %s" % (self.__user))
cursor.execute("grant create public synonym to %s" % (self.__user))
cursor.execute("grant drop public synonym to %s" % (self.__user))
cursor.close()
conn.close()
except Exception, e:
raise Exception("check error: %s" % e)
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.cli = CLI()
self.config = config or {}
self.mysql = mysql
if self.mysql is None and not self.config.get("new_migration"):
self.mysql = MySQL(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
def execute(self):
self.cli.msg("\nStarting DB migration...", "PINK")
if self.config.get("new_migration"):
self.create_migration()
else:
self.migrate()
self.cli.msg("\nDone.\n", "PINK")
def create_migration(self):
# TODO: create file in the migrations directory, not in current
new_file = Migration.create(self.config.get("new_migration"))
self.cli.msg("- Created file '%s'" % (new_file))
def migrate(self):
destination_version = self.get_destination_version()
current_version = self.mysql.get_current_schema_version()
self.cli.msg("- Current version is: %s" % current_version, "GREEN")
self.cli.msg("- Destination version is: %s" % destination_version, "GREEN")
# if current and destination versions are the same,
# will consider a migration up to execute remaining files
is_migration_up = True
if int(current_version) > int(destination_version):
is_migration_up = False
# do it!
self.execute_migrations(current_version, destination_version, is_migration_up)
def get_destination_version(self):
destination_version = self.config.get("schema_version")
if destination_version is None:
destination_version = self.db_migrate.latest_version_available()
if destination_version is not '0' and not self.db_migrate.check_if_version_exists(destination_version):
raise Exception("version not found (%s)" % destination_version)
return destination_version
def get_migration_files_to_be_executed(self, current_version, destination_version):
mysql_versions = self.mysql.get_all_schema_versions()
migration_versions = self.db_migrate.get_all_migration_versions()
# migration up: the easy part
if current_version <= destination_version:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_versions_to_execute = [version for version in remaining_versions_to_execute if version <= destination_version]
return remaining_versions_to_execute
# migration down...
down_versions = [version for version in mysql_versions if version <= current_version and version > destination_version]
for version in down_versions:
if version not in migration_versions:
raise Exception("impossible to migrate down: one of the versions was not found (%s)" % version)
down_versions.reverse()
return down_versions
def execute_migrations(self, current_version, destination_version, is_migration_up):
# getting only the migration sql files to be executed
versions_to_be_executed = self.get_migration_files_to_be_executed(current_version, destination_version)
if versions_to_be_executed is None or len(versions_to_be_executed) == 0:
self.cli.msg("\nNothing to do.\n", "PINK")
return
up_down_label = is_migration_up and "up" or "down"
if self.config.get("show_sql_only"):
self.cli.msg("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW")
else:
self.cli.msg("\nStarting migration %s!" % up_down_label)
if self.config.get("log_level") >= 1:
self.cli.msg("*** versions: %s\n" % versions_to_be_executed, "CYAN")
sql_statements_executed = []
for migration_version in versions_to_be_executed:
migration = self.db_migrate.get_migration_from_version_number(migration_version)
sql = is_migration_up and migration.sql_up or migration.sql_down
if not self.config.get("show_sql_only"):
if self.config.get("log_level") >= 1:
self.cli.msg("===== executing %s (%s) =====" % (migration.file_name, up_down_label))
log = None
if self.config.get("log_level") >= 2:
log = self.cli.msg
self.mysql.change(sql, migration_version, is_migration_up, execution_log=log)
# paused mode
if self.config.get("paused_mode"):
raw_input("* press <enter> to continue... ")
# recording the last statement executed
sql_statements_executed.append(sql)
if self.config.get("show_sql") or self.config.get("show_sql_only"):
self.cli.msg("__________ SQL statements executed __________", "YELLOW")
for sql in sql_statements_executed:
self.cli.msg(sql, "YELLOW")
self.cli.msg("_____________________________________________", "YELLOW")
def _execution_log(self, msg, color="CYAN", log_level_limit=2):
if self.config.get("log_level", 1) >= log_level_limit:
CLI.msg(msg, color)
self.log.debug(msg)
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.__cli = CLI()
self.__config = config
self.__mysql = mysql
if self.__mysql is None and not self.__config.get("new_migration"):
self.__mysql = MySQL(config)
self.__db_migrate = db_migrate
if self.__db_migrate is None:
self.__db_migrate = Migrations(config)
def execute(self):
self.__cli.msg("\nStarting DB migration...", "PINK")
if self.__config.get("new_migration"):
self._create_migration()
else:
self._migrate()
self.__cli.msg("\nDone.\n", "PINK")
def _create_migration(self):
new_file = self.__db_migrate.create_migration(self.__config.get("new_migration"))
self.__cli.msg("- Created file '%s'" % (new_file))
def _migrate(self):
destination_version = self._get_destination_version()
current_version = self.__mysql.get_current_schema_version()
self.__cli.msg("- Current version is: %s" % current_version, "GREEN")
self.__cli.msg("- Destination version is: %s" % destination_version, "GREEN")
# if current and destination versions are the same,
# will consider a migration up to execute remaining files
is_migration_up = True
if int(current_version) > int(destination_version):
is_migration_up = False
# do it!
self._execute_migrations(current_version, destination_version, is_migration_up)
def _get_destination_version(self):
destination_version = self.__config.get("schema_version")
if destination_version is None:
destination_version = self.__db_migrate.latest_schema_version_available()
if not self.__db_migrate.check_if_version_exists(destination_version):
self.__cli.error_and_exit("version not found (%s)" % destination_version)
return destination_version
def _get_migration_files_to_be_executed(self, current_version, destination_version):
mysql_versions = self.__mysql.get_all_schema_versions()
migration_versions = self.__db_migrate.get_all_migration_versions()
# migration up: the easy part
if current_version <= destination_version:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_versions_to_execute = [
version for version in remaining_versions_to_execute if version <= destination_version
]
return remaining_versions_to_execute
# migration down...
down_versions = [
version for version in mysql_versions if version <= current_version and version > destination_version
]
for version in down_versions:
if version not in migration_versions:
self.__cli.error_and_exit(
"impossible to migrate down: one of the versions was not found (%s)" % version
)
down_versions.reverse()
return down_versions
def _execute_migrations(self, current_version, destination_version, is_migration_up):
# getting only the migration sql files to be executed
versions_to_be_executed = self._get_migration_files_to_be_executed(current_version, destination_version)
if versions_to_be_executed is None or len(versions_to_be_executed) == 0:
self.__cli.msg("\nNothing to do.\n", "PINK")
sys.exit(0)
up_down_label = "up" if is_migration_up else "down"
if self.__config.get("show_sql_only"):
self.__cli.msg("WARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW")
else:
self.__cli.msg("\nStarting migration %s!" % up_down_label)
self.__cli.msg("*** versions: %s\n" % versions_to_be_executed, "GRAY")
sql_statements_executed = []
for migration_version in versions_to_be_executed:
sql_file = self.__db_migrate.get_migration_file_name_from_version_number(migration_version)
sql = self.__db_migrate.get_sql_command(sql_file, is_migration_up)
if not self.__config.get("show_sql_only"):
self.__cli.msg("===== executing %s (%s) =====" % (sql_file, up_down_label))
self.__mysql.change(sql, migration_version, is_migration_up)
# recording the last statement executed
sql_statements_executed.append(sql)
if self.__config.get("show_sql") or self.__config.get("show_sql_only"):
self.__cli.msg("__________ SQL statements executed __________", "YELLOW")
for sql in sql_statements_executed:
self.__cli.msg(sql, "YELLOW")
self.__cli.msg("_____________________________________________", "YELLOW")
def run(options):
""" Initial Module. Treat Parameters and call Main Module for execution """
try:
if options.get('simple_virtuoso_migrate_version'):
msg = ('simple-virtuoso-migrate v%s' %
SIMPLE_VIRTUOSO_MIGRATE_VERSION)
CLI.info_and_exit(msg)
if options.get('show_colors'):
CLI.show_colors()
# Create config
if options.get('config_file'):
config = FileConfig(options.get('config_file'),
options.get('environment'))
else:
config = Config()
config.update('schema_version', options.get('schema_version'))
config.update('show_sparql', options.get('show_sparql'))
config.update('show_sparql_only', options.get('show_sparql_only'))
config.update('file_migration', options.get('file_migration'))
#config.update('add_ttl', options.get('add_ttl'))
config.update('load_ttl', options.get('load_ttl'))
config.update('log_dir', options.get('log_dir'))
config.update('database_user', options.get('database_user'))
config.update('database_password', options.get('database_password'))
config.update('host_user', options.get('host_user'))
config.update('host_password', options.get('host_password'))
config.update('virtuoso_dirs_allowed',
options.get('virtuoso_dirs_allowed'))
config.update('database_host', options.get('database_host'))
config.update('database_port', options.get('database_port'))
config.update('database_endpoint', options.get('database_endpoint'))
config.update('database_graph', options.get('database_graph'))
config.update('database_ontology', options.get('database_ontology'))
if options.get('database_migrations_dir'):
config.update("database_migrations_dir",
Config._parse_migrations_dir(
options.get(
'database_migrations_dir')))
config.update("database_migrations_dir",
config.get("database_migrations_dir")[0])
config.update('log_level', int(options.get('log_level')))
# Ask the password for user if configured
if config.get('database_password') == '<<ask_me>>':
CLI.msg('\nPlease inform password to connect to virtuoso (DATABASE) "%s@%s"' % (config.get('database_user'),
config.get('database_host')))
passwd = getpass()
config.update('database_password', passwd)
is_local = config.get('database_host', '').lower() in ["localhost",
"127.0.0.1"]
if config.get('load_ttl') and\
config.get('virtuoso_dirs_allowed') is None and\
not is_local:
if config.get('host_password') == '<<ask_me>>':
msg = '\nPlease inform password to connect to virtuoso (HOST) "%s@%s"'
CLI.msg(msg % (config.get('host_user'),
config.get('database_host')))
passwd = getpass()
config.update('host_password', passwd)
# If CLI was correctly parsed, execute db-virtuoso.
Main(config).execute()
except KeyboardInterrupt:
CLI.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
CLI.error_and_exit(unicode(e))
def run(options):
try:
if options.get('simple_db_migrate_version'):
msg = 'simple-db-migrate v%s' % SIMPLE_DB_MIGRATE_VERSION
CLI.info_and_exit(msg)
if options.get('show_colors'):
CLI.show_colors()
# Create config
if options.get('config_file') or os.path.exists('simple-db-migrate.conf'):
config = FileConfig(options.get('config_file') or 'simple-db-migrate.conf', options.get('environment'))
else:
config = Config()
config.update('schema_version', options.get('schema_version'))
config.update('show_sql', options.get('show_sql'))
config.update('show_sql_only', options.get('show_sql_only'))
config.update('new_migration', options.get('new_migration'))
config.update('drop_db_first', options.get('drop_db_first'))
config.update('paused_mode', options.get('paused_mode'))
config.update('log_dir', options.get('log_dir'))
config.update('label_version', options.get('label_version'))
config.update('force_use_files_on_down', options.get('force_use_files_on_down'))
config.update('force_execute_old_migrations_versions', options.get('force_execute_old_migrations_versions'))
config.update('utc_timestamp', options.get('utc_timestamp'))
config.update('database_user', options.get('database_user'))
config.update('database_password', options.get('database_password'))
config.update('database_host', options.get('database_host'))
config.update('database_port', options.get('database_port'))
config.update('database_name', options.get('database_name'))
if config.get('database_port', None):
config.update('database_port', int(config.get('database_port')))
if options.get('database_migrations_dir'):
config.update("database_migrations_dir", Config._parse_migrations_dir(options.get('database_migrations_dir')))
config.update('database_engine', options.get('database_engine'))
if not config.get('database_engine', None):
config.update('database_engine', "mysql")
config.update('database_version_table', options.get('database_version_table'))
if not config.get('database_version_table', None):
config.update('database_version_table', "__db_version__")
# paused mode forces log_level to 2
log_level = int(options.get('log_level'))
if options.get('paused_mode'):
log_level = 2
config.update('log_level', log_level)
# Ask the password for user if configured
if config.get('database_password') == '<<ask_me>>':
if options.get('password'):
passwd = options.get('password')
else:
CLI.msg('\nPlease inform password to connect to database "%s@%s:%s"' % (config.get('database_user'), config.get('database_host'), config.get('database_name')))
passwd = getpass()
config.update('database_password', passwd)
if options.get('info_database'):
if options.get('info_database').lower() == 'last_label':
CLI.info_and_exit(Main(config).last_label() or "NONE")
elif options.get('info_database').lower() == 'labels':
labels = Main(config).labels()
CLI.info_and_exit(labels and "\n".join(labels) or "NONE")
else:
CLI.error_and_exit("The '%s' is a wrong parameter for info" % options.get('info_database').lower())
# If CLI was correctly parsed, execute db-migrate.
Main(config).execute()
except KeyboardInterrupt:
CLI.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
CLI.error_and_exit(str(e))
class Main(object):
def __init__(self, config=None, mysql=None, db_migrate=None):
self.cli = CLI()
self.config = config or {}
self.log = LOG(self.config.get("log_dir"))
self.mysql = mysql
if self.mysql is None and not self.config.get("new_migration"):
self.mysql = MySQL(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
def execution_log(self, msg, color="CYAN", log_level_limit=2):
if self.config.get("log_level") >= log_level_limit:
self.cli.msg(msg)
self.log.debug(msg)
def execute(self):
self.execution_log("\nStarting DB migration...", "PINK", log_level_limit=1)
if self.config.get("new_migration"):
self.create_migration()
else:
self.migrate()
self.execution_log("\nDone.\n", "PINK", log_level_limit=1)
def create_migration(self):
migrations_dir = self.config.get("migrations_dir")
new_file = Migration.create(self.config.get("new_migration"), migrations_dir[0], self.config.get("db_script_encoding", "utf-8"))
self.execution_log("- Created file '%s'" % (new_file), log_level_limit=1)
def migrate(self):
destination_version = self.get_destination_version()
current_version = self.mysql.get_current_schema_version()
# do it!
self.execute_migrations(current_version, destination_version)
def get_destination_version(self):
destination_version = self.config.get("schema_version")
if destination_version is None:
destination_version = self.db_migrate.latest_version_available()
if destination_version is not '0' and not self.db_migrate.check_if_version_exists(destination_version):
raise Exception("version not found (%s)" % destination_version)
return destination_version
def get_migration_files_to_be_executed(self, current_version, destination_version, is_migration_up):
mysql_versions = self.mysql.get_all_schema_versions()
migration_versions = self.db_migrate.get_all_migration_versions()
# migration up
if is_migration_up:
remaining_versions_to_execute = Lists.subtract(migration_versions, mysql_versions)
remaining_migrations_to_execute = [self.db_migrate.get_migration_from_version_number(version) for version in remaining_versions_to_execute if version <= destination_version]
return remaining_migrations_to_execute
# migration down...
destination_version_id = self.mysql.get_version_id_from_version_number(destination_version)
migrations = self.mysql.get_all_schema_migrations()
down_migrations_to_execute = [migration for migration in migrations if migration.id > destination_version_id]
for migration in down_migrations_to_execute:
if not migration.sql_down:
if migration.version not in migration_versions:
raise Exception("impossible to migrate down: one of the versions was not found (%s)" % migration.version)
migration_tmp = self.db_migrate.get_migration_from_version_number(migration.version)
migration.sql_up = migration_tmp.sql_up
migration.sql_down = migration_tmp.sql_down
migration.file_name = migration_tmp.file_name
down_migrations_to_execute.reverse()
return down_migrations_to_execute
def execute_migrations(self, current_version, destination_version):
"""
passed a version:
this version don't exists in the database and is younger than the last version -> do migrations up until the this version
this version don't exists in the database and is older than the last version -> do nothing, is a unpredictable behavior
this version exists in the database and is older than the last version -> do migrations down until this version
didn't pass a version -> do migrations up until the last available version
"""
is_migration_up = True
# check if a version was passed to the program
if self.config.get("schema_version"):
# if was passed and this version is present in the database, check if is older than the current version
destination_version_id = self.mysql.get_version_id_from_version_number(destination_version)
if destination_version_id:
current_version_id = self.mysql.get_version_id_from_version_number(current_version)
# if this version is previous to the current version in database, then will be done a migration down to this version
if current_version_id > destination_version_id:
is_migration_up = False
# if was passed and this version is not present in the database and is older than the current version, raise an exception
# cause is trying to go down to something that never was done
elif current_version > destination_version:
raise Exception("Trying to migrate to a lower version wich is not found on database (%s)" % destination_version)
# getting only the migration sql files to be executed
migrations_to_be_executed = self.get_migration_files_to_be_executed(current_version, destination_version, is_migration_up)
self.execution_log("- Current version is: %s" % current_version, "GREEN", log_level_limit=1)
if migrations_to_be_executed is None or len(migrations_to_be_executed) == 0:
self.execution_log("- Destination version is: %s" % current_version, "GREEN", log_level_limit=1)
self.execution_log("\nNothing to do.\n", "PINK", log_level_limit=1)
return
self.execution_log("- Destination version is: %s" % (is_migration_up and migrations_to_be_executed[-1].version or destination_version), "GREEN", log_level_limit=1)
up_down_label = is_migration_up and "up" or "down"
if self.config.get("show_sql_only"):
self.execution_log("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW", log_level_limit=1)
else:
self.execution_log("\nStarting migration %s!" % up_down_label, log_level_limit=1)
if self.config.get("log_level") >= 1:
self.execution_log("*** versions: %s\n" % ([ migration.version for migration in migrations_to_be_executed]), "CYAN", log_level_limit=1)
sql_statements_executed = []
for migration in migrations_to_be_executed:
sql = is_migration_up and migration.sql_up or migration.sql_down
if not self.config.get("show_sql_only"):
if self.config.get("log_level") >= 1:
self.execution_log("===== executing %s (%s) =====" % (migration.file_name, up_down_label), log_level_limit=1)
self.mysql.change(sql, migration.version, migration.file_name, migration.sql_up, migration.sql_down, is_migration_up, execution_log=self.execution_log)
# paused mode
if self.config.get("paused_mode"):
raw_input("* press <enter> to continue... ")
# recording the last statement executed
sql_statements_executed.append(sql)
if self.config.get("show_sql") or self.config.get("show_sql_only"):
self.execution_log("__________ SQL statements executed __________", "YELLOW", log_level_limit=1)
for sql in sql_statements_executed:
self.execution_log(sql, "YELLOW", log_level_limit=1)
self.execution_log("_____________________________________________", "YELLOW", log_level_limit=1)
def run(options):
try:
if options.get('simple_db_migrate_version'):
msg = 'simple-db-migrate v%s' % SIMPLE_DB_MIGRATE_VERSION
CLI.info_and_exit(msg)
if options.get('show_colors'):
CLI.show_colors()
# Create config
if options.get('config_file'):
config = FileConfig(options.get('config_file'), options.get('environment'))
else:
config = Config()
config.update('schema_version', options.get('schema_version'))
config.update('show_sql', options.get('show_sql'))
config.update('show_sql_only', options.get('show_sql_only'))
config.update('new_migration', options.get('new_migration'))
config.update('drop_db_first', options.get('drop_db_first'))
config.update('paused_mode', options.get('paused_mode'))
config.update('log_dir', options.get('log_dir'))
config.update('label_version', options.get('label_version'))
config.update('force_use_files_on_down', options.get('force_use_files_on_down'))
config.update('force_execute_old_migrations_versions', options.get('force_execute_old_migrations_versions'))
config.update('utc_timestamp', options.get('utc_timestamp'))
config.update('database_user', options.get('database_user'))
config.update('database_password', options.get('database_password'))
config.update('database_host', options.get('database_host'))
config.update('database_name', options.get('database_name'))
if options.get('database_migrations_dir'):
config.update("database_migrations_dir", Config._parse_migrations_dir(options.get('database_migrations_dir')))
config.update('database_engine', options.get('database_engine'))
if not config.get('database_engine', None):
config.update('database_engine', "mysql")
config.update('database_version_table', options.get('database_version_table'))
if not config.get('database_version_table', None):
config.update('database_version_table', "__db_version__")
# paused mode forces log_level to 2
log_level = int(options.get('log_level'))
if options.get('paused_mode'):
log_level = 2
config.update('log_level', log_level)
# Ask the password for user if configured
if config.get('database_password') == '<<ask_me>>':
if options.get('password'):
passwd = options.get('password')
else:
CLI.msg('\nPlease inform password to connect to database "%s@%s:%s"' % (config.get('database_user'), config.get('database_host'), config.get('database_name')))
passwd = getpass()
config.update('database_password', passwd)
# If CLI was correctly parsed, execute db-migrate.
Main(config).execute()
except KeyboardInterrupt:
CLI.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
CLI.error_and_exit(str(e))
class Main(object):
def __init__(self, config=None, sgdb=None, db_migrate=None, execution_log=None):
self.cli = CLI()
self.config = config or {}
self.log = LOG(self.config.get("log_dir", None))
self.sgdb = sgdb
if self.sgdb is None and not self.config.get("new_migration", None):
if self.config.get("db_engine") is 'mysql':
from mysql import MySQL
self.sgdb = MySQL(config)
elif self.config.get("db_engine") is 'oracle':
from oracle import Oracle
self.sgdb = Oracle(config)
self.db_migrate = db_migrate or SimpleDBMigrate(config)
if execution_log:
self.execution_log = execution_log
def execution_log(self, msg, color="CYAN", log_level_limit=2):
if self.config.get("log_level", 1) >= log_level_limit:
self.cli.msg(msg)
self.log.debug(msg)
def execute(self):
self.execution_log("\nStarting DB migration...", "PINK", log_level_limit=1)
if self.config.get("new_migration", None):
self.create_migration()
else:
self.migrate()
self.execution_log("\nDone.\n", "PINK", log_level_limit=1)
def create_migration(self):
migrations_dir = self.config.get("migrations_dir")
new_file = Migration.create(self.config.get("new_migration", None), migrations_dir[0], self.config.get("db_script_encoding", "utf-8"), self.config.get("utc_timestamp", False))
self.execution_log("- Created file '%s'" % (new_file), log_level_limit=1)
def migrate(self):
destination_version = self.get_destination_version()
current_version = self.sgdb.get_current_schema_version()
# do it!
self.execute_migrations(current_version, destination_version)
def get_destination_version(self):
label_version = self.config.get("label_version", None)
schema_version = self.config.get("schema_version", None)
destination_version = None
destination_version_by_label = None
destination_version_by_schema = None
if label_version is not None:
destination_version_by_label = self.sgdb.get_version_number_from_label(label_version)
"""
if specified label exists at database and schema version was not specified,
is equivalent to run simple-db-migrate with schema_version equals to the version with specified label
"""
if destination_version_by_label is not None and schema_version is None:
schema_version = destination_version_by_label
self.config.remove("schema_version")
self.config.put("schema_version", destination_version_by_label)
if schema_version is not None and self.sgdb.get_version_id_from_version_number(schema_version):
destination_version_by_schema = schema_version
if label_version is None:
if schema_version is None:
destination_version = self.db_migrate.latest_version_available()
elif destination_version_by_schema is None:
destination_version = schema_version
else:
destination_version = destination_version_by_schema
else:
if schema_version is None:
destination_version = self.db_migrate.latest_version_available()
elif (destination_version_by_label is None) or (destination_version_by_schema == destination_version_by_label):
destination_version = schema_version
if (destination_version_by_schema is not None) and (destination_version_by_label is not None) and (destination_version_by_schema != destination_version_by_label):
raise Exception("label (%s) and schema_version (%s) don't correspond to the same version at database" % (label_version, schema_version))
if (schema_version is not None and label_version is not None) and ((destination_version_by_schema is not None and destination_version_by_label is None) or (destination_version_by_schema is None and destination_version_by_label is not None)):
raise Exception("label (%s) or schema_version (%s), only one of them exists in the database" % (label_version, schema_version))
if destination_version is not '0' and not (self.db_migrate.check_if_version_exists(destination_version) or self.sgdb.get_version_id_from_version_number(destination_version)):
raise Exception("version not found (%s)" % destination_version)
return destination_version
def get_migration_files_to_be_executed(self, current_version, destination_version, is_migration_up):
if current_version == destination_version and not self.config.get("force_execute_old_migrations_versions", False):
return []
schema_versions = self.sgdb.get_all_schema_versions()
migration_versions = self.db_migrate.get_all_migration_versions()
# migration up
if is_migration_up:
remaining_versions_to_execute = Lists.subtract(migration_versions, schema_versions)
remaining_migrations_to_execute = [self.db_migrate.get_migration_from_version_number(version) for version in remaining_versions_to_execute if version <= destination_version]
return remaining_migrations_to_execute
# migration down...
destination_version_id = self.sgdb.get_version_id_from_version_number(destination_version)
migrations = self.sgdb.get_all_schema_migrations()
down_migrations_to_execute = [migration for migration in migrations if migration.id > destination_version_id]
force_files = self.config.get("force_use_files_on_down", False)
for migration in down_migrations_to_execute:
if not migration.sql_down or force_files:
if migration.version not in migration_versions:
raise Exception("impossible to migrate down: one of the versions was not found (%s)" % migration.version)
migration_tmp = self.db_migrate.get_migration_from_version_number(migration.version)
migration.sql_up = migration_tmp.sql_up
migration.sql_down = migration_tmp.sql_down
migration.file_name = migration_tmp.file_name
down_migrations_to_execute.reverse()
return down_migrations_to_execute
def execute_migrations(self, current_version, destination_version):
"""
passed a version:
this version don't exists in the database and is younger than the last version -> do migrations up until the this version
this version don't exists in the database and is older than the last version -> do nothing, is a unpredictable behavior
this version exists in the database and is older than the last version -> do migrations down until this version
didn't pass a version -> do migrations up until the last available version
"""
is_migration_up = True
# check if a version was passed to the program
if self.config.get("schema_version"):
# if was passed and this version is present in the database, check if is older than the current version
destination_version_id = self.sgdb.get_version_id_from_version_number(destination_version)
if destination_version_id:
current_version_id = self.sgdb.get_version_id_from_version_number(current_version)
# if this version is previous to the current version in database, then will be done a migration down to this version
if current_version_id > destination_version_id:
is_migration_up = False
# if was passed and this version is not present in the database and is older than the current version, raise an exception
# cause is trying to go down to something that never was done
elif current_version > destination_version:
raise Exception("Trying to migrate to a lower version wich is not found on database (%s)" % destination_version)
# getting only the migration sql files to be executed
migrations_to_be_executed = self.get_migration_files_to_be_executed(current_version, destination_version, is_migration_up)
self.execution_log("- Current version is: %s" % current_version, "GREEN", log_level_limit=1)
if migrations_to_be_executed is None or len(migrations_to_be_executed) == 0:
self.execution_log("- Destination version is: %s" % current_version, "GREEN", log_level_limit=1)
self.execution_log("\nNothing to do.\n", "PINK", log_level_limit=1)
return
self.execution_log("- Destination version is: %s" % (is_migration_up and migrations_to_be_executed[-1].version or destination_version), "GREEN", log_level_limit=1)
up_down_label = is_migration_up and "up" or "down"
if self.config.get("show_sql_only", False):
self.execution_log("\nWARNING: database migrations are not being executed ('--showsqlonly' activated)", "YELLOW", log_level_limit=1)
else:
self.execution_log("\nStarting migration %s!" % up_down_label, log_level_limit=1)
self.execution_log("*** versions: %s\n" % ([ migration.version for migration in migrations_to_be_executed]), "CYAN", log_level_limit=1)
sql_statements_executed = []
for migration in migrations_to_be_executed:
sql = is_migration_up and migration.sql_up or migration.sql_down
if not self.config.get("show_sql_only", False):
self.execution_log("===== executing %s (%s) =====" % (migration.file_name, up_down_label), log_level_limit=1)
label = None
if is_migration_up:
label = self.config.get("label_version", None)
try:
self.sgdb.change(sql, migration.version, migration.file_name, migration.sql_up, migration.sql_down, is_migration_up, self.execution_log, label)
except Exception, e:
self.execution_log("===== ERROR executing %s (%s) =====" % (migration.abspath, up_down_label), log_level_limit=1)
raise e
# paused mode
if self.config.get("paused_mode", False):
raw_input("* press <enter> to continue... ")
# recording the last statement executed
sql_statements_executed.append(sql)
if self.config.get("show_sql", False) or self.config.get("show_sql_only", False):
self.execution_log("__________ SQL statements executed __________", "YELLOW", log_level_limit=1)
for sql in sql_statements_executed:
self.execution_log(sql, "YELLOW", log_level_limit=1)
self.execution_log("_____________________________________________", "YELLOW", log_level_limit=1)
def _execution_log(self, msg, color="CYAN", log_level_limit=2):
if self.config.get("log_level", 1) >= log_level_limit:
CLI.msg(msg, color)
self.log.debug(msg)
def run(options):
""" Initial Module. Treat Parameters and call Main Module for execution """
try:
if options.get("simple_virtuoso_migrate_version"):
msg = "simple-virtuoso-migrate v%s" % SIMPLE_VIRTUOSO_MIGRATE_VERSION
CLI.info_and_exit(msg)
if options.get("show_colors"):
CLI.show_colors()
# Create config
if options.get("config_file"):
config = FileConfig(options.get("config_file"), options.get("environment"))
else:
config = Config()
config.update("schema_version", options.get("schema_version"))
config.update("show_sparql", options.get("show_sparql"))
config.update("show_sparql_only", options.get("show_sparql_only"))
config.update("file_migration", options.get("file_migration"))
# config.update('add_ttl', options.get('add_ttl'))
config.update("load_ttl", options.get("load_ttl"))
config.update("log_dir", options.get("log_dir"))
config.update("database_user", options.get("database_user"))
config.update("database_password", options.get("database_password"))
config.update("host_user", options.get("host_user"))
config.update("host_password", options.get("host_password"))
config.update("virtuoso_dirs_allowed", options.get("virtuoso_dirs_allowed"))
config.update("database_host", options.get("database_host"))
config.update("database_port", options.get("database_port"))
config.update("database_endpoint", options.get("database_endpoint"))
config.update("database_graph", options.get("database_graph"))
config.update("database_ontology", options.get("database_ontology"))
if options.get("database_migrations_dir"):
config.update(
"database_migrations_dir", Config._parse_migrations_dir(options.get("database_migrations_dir"))
)
config.update("database_migrations_dir", config.get("database_migrations_dir")[0])
config.update("log_level", int(options.get("log_level")))
# Ask the password for user if configured
if config.get("database_password") == "<<ask_me>>":
CLI.msg(
'\nPlease inform password to connect to\
virtuoso (DATABASE) "%s@%s"'
% (config.get("database_user"), config.get("database_host"))
)
passwd = getpass()
config.update("database_password", passwd)
is_local = config.get("database_host", "").lower() in ["localhost", "127.0.0.1"]
# import pdb; pdb.set_trace()
if config.get("load_ttl") and config.get("virtuoso_dirs_allowed") is None and not is_local:
if config.get("host_password") == "<<ask_me>>":
CLI.msg(
'\nPlease inform password to connect to\
virtuoso (HOST) "%s@%s"'
% (config.get("host_user"), config.get("database_host"))
)
passwd = getpass()
config.update("host_password", passwd)
# If CLI was correctly parsed, execute db-virtuoso.
Main(config).execute()
except KeyboardInterrupt:
CLI.info_and_exit("\nExecution interrupted by user...")
except Exception, e:
CLI.error_and_exit(unicode(e))
