def _schema_alter_table(dsn, table, fields, indexes, foreign_keys, exists):
sql_fields = exists['sql'][exists['sql'].find('(') +
1:exists['sql'].rfind(')')]
sql_fields = ''.join([(e[:e.find('--')] if e.find('--') != -1 else e)
for e in sql_fields.split('\n')])
sql_fields = sql_fields.split(',')
exist_fields = {}
for e in sql_fields:
e = e.strip()
exist_fields[e[:e.find(' ')]] = True
for e in fields:
if (e[1].name or e[0]) in exist_fields:
continue
sql = SqliteDriver._schema_by_attrs(e)
if sql:
dpModel().execute(
'ALTER TABLE `%s` ADD COLUMN %s;' %
(table.__table_name__, sql), None, dsn)
return True
def migrate(dsn, table, fields, indexes, foreign_keys, migrate_data=True):
succeed = True
try:
exists = dpModel().row(
"""
SELECT
*
FROM
`sqlite_master`
WHERE
`type` = 'table' AND `tbl_name` = :table_name
""", {'table_name': table.__table_name__}, dsn)
if not exists:
SqliteDriver._schema_create_table(dsn=dsn,
table=table,
fields=fields,
indexes=indexes,
foreign_keys=foreign_keys)
else:
SqliteDriver._schema_alter_table(dsn=dsn,
table=table,
fields=fields,
indexes=indexes,
foreign_keys=foreign_keys,
exists=exists)
for e in indexes:
fields = ','.join(['`%s`' % ee for ee in e[1].fields])
if e[1].index_type == dpAttribute.IndexType.UNIQUE:
tp = 'UNIQUE'
else:
tp = ''
sql = """
CREATE %s INDEX
IF NOT EXISTS `%s` ON `%s` (%s)""" % (
tp, e[0], table.__table_name__, fields)
dpModel().execute(sql, None, dsn)
logging.info('Table migration succeed : %s :: %s' %
(dsn, table.__table_name__))
except Exception as e:
succeed = True
logging.exception(e)
logging.error('Table migration failed : %s :: %s' %
(dsn, table.__table_name__))
if migrate_data:
return SqliteDriver.migrate_data(dsn, table) and succeed
return succeed
def _field_attrs_to_query(key, field, ai=False):
data_type = field.data_type.name
unsigned = 'unsigned' if field.un else ''
default = "DEFAULT '%s'" % field.default if field.default is not None else ''
comment = "COMMENT '%s {%s}'" % (field.comment, key)
null = 'NOT NULL' if field.nn or field.ai or field.pk else 'NULL'
zerofill = 'ZEROFILL' if field.zf else ''
auto_increment = 'AUTO_INCREMENT' if ai and field.ai else ''
if getattr(field.data_type, 'size', None) and not zerofill:
size = getattr(field.data_type, 'size', None)
if isinstance(size, (tuple, list)):
data_type = '%s%s' % (data_type, tuple(size))
else:
data_type = '%s(%s)' % (data_type, size)
elif getattr(field.data_type, 'enums', None):
data_type = '%s%s' % (data_type, getattr(field.data_type, 'enums', None))
ret = '{data_type} {zerofill} {unsigned} {null} {default} {auto_increment} {comment}' \
.replace('{data_type}', data_type) \
.replace('{unsigned}', unsigned) \
.replace('{null}', null) \
.replace('{zerofill}', zerofill) \
.replace('{default}', default) \
.replace('{auto_increment}', auto_increment) \
.replace('{comment}', comment)
return ret.decode('utf8') if dpModel().helper.misc.system.py_version <= 2 else ret
def _field_attrs_to_query(key, field, ai=False):
data_type = field.data_type.name
unsigned = 'unsigned' if field.un else ''
default = "DEFAULT '%s'" % field.default if field.default is not None else ''
comment = "COMMENT '%s {%s}'" % (field.comment, key)
null = 'NOT NULL' if field.nn or field.ai or field.pk else 'NULL'
zerofill = 'ZEROFILL' if field.zf else ''
auto_increment = 'AUTO_INCREMENT' if ai and field.ai else ''
if getattr(field.data_type, 'size', None) and not zerofill:
size = getattr(field.data_type, 'size', None)
if isinstance(size, (tuple, list)):
data_type = '%s%s' % (data_type, tuple(size))
else:
data_type = '%s(%s)' % (data_type, size)
elif getattr(field.data_type, 'enums', None):
data_type = '%s%s' % (data_type,
getattr(field.data_type, 'enums', None))
ret = '{data_type} {zerofill} {unsigned} {null} {default} {auto_increment} {comment}' \
.replace('{data_type}', data_type) \
.replace('{unsigned}', unsigned) \
.replace('{null}', null) \
.replace('{zerofill}', zerofill) \
.replace('{default}', default) \
.replace('{auto_increment}', auto_increment) \
.replace('{comment}', comment)
return ret.decode(
'utf8') if dpModel().helper.misc.system.py_version <= 2 else ret
def migrate_priority(dsn, table, fields):
succeed = True
proxy = dpModel().begin(dsn)
try:
exist = MySqlDriver._get_exist(proxy, table)
exist = [(e[0], e[1])
for e in sorted(exist['col'].items(),
key=lambda o: o[1].__field_priority__)]
exist_priority = [
e[0]
for e in sorted(exist, key=lambda o: o[1].__field_priority__)
]
exist_fields = dict(exist)
modified = False
for i in range(len(fields)):
if fields[i][0] != exist_priority[i]:
modified = True
if modified:
previous = None
queries = []
for k, v in fields:
if previous is None:
p = 'FIRST'
else:
p = 'AFTER `%s`' % previous
previous = v.name
queries.append('CHANGE COLUMN `%s` %s %s' %
(v.name, exist_fields[k].query, p))
proxy.execute('ALTER TABLE `%s`\n%s' %
(table.__table_name__, ',\n'.join(queries)))
proxy.commit()
logging.info('Table priority rearrange succeed : %s :: %s' %
(dsn, table.__table_name__))
except Exception as e:
succeed = False
proxy.rollback()
logging.exception(e)
logging.error('Table priority rearrange failed : %s :: %s' %
(dsn, table.__table_name__))
return succeed
def migrate_data(dsn, table):
succed = True
dummy_data = getattr(table, '__dummy_data__', None)
if dummy_data:
dummy_data = dummy_data if isinstance(dummy_data,
(tuple,
list)) else list(dummy_data)
proxy = dpModel().begin(dsn)
try:
for e in dummy_data:
fields = []
params = []
for k, v in e.items():
fields.append(k)
params.append(v)
proxy.execute(
"""
INSERT INTO {table_name}
({fields}) VALUES ({params})
ON DUPLICATE KEY UPDATE
{updates}""".replace(
'{table_name}', table.__table_name__).replace(
'{fields}', ','.join([
'`%s`' % ee for ee in fields
])).replace('{params}', ','.join(
['%s' for ee in fields])).replace(
'{updates}', ','.join([
'`%s` = VALUES(`%s`)' % (ee, ee)
for ee in fields
])), params)
proxy.commit()
logging.info('Table data insertion succeed : %s :: %s' %
(dsn, table.__table_name__))
except Exception as e:
succed = False
proxy.rollback()
logging.exception(e)
logging.error('Table data insertion failed : %s :: %s' %
(dsn, table.__table_name__))
return succed
def migrate(dsn, table, fields, indexes, foreign_keys, migrate_data=True):
for k, v in fields:
if not v.name:
setattr(v, 'name', k)
created = False
succeed = True
for val in (False, True):
proxy = dpModel().begin(dsn)
try:
exist = MySqlDriver._get_exist(proxy, table)
create = MySqlDriver.migrate_fields(proxy,
table,
fields,
exist,
change=val)
if not val:
created = create
if not val:
MySqlDriver.migrate_indexes(proxy, table, indexes, exist)
MySqlDriver.migrate_foreign_keys(proxy, table,
foreign_keys, exist)
proxy.commit()
if val:
logging.info('Table migration succeed : %s :: %s' %
(dsn, table.__table_name__))
except Exception as e:
succeed = True
proxy.rollback()
logging.exception(e)
logging.error('Table migration failed : %s :: %s' %
(dsn, table.__table_name__))
break
if MySqlDriver.migrate_priority(dsn, table, fields) is False:
return False
if migrate_data:
return MySqlDriver.migrate_data(dsn, table) and succeed
return succeed
def _schema_create_table(dsn, table, fields, indexes, foreign_keys):
field_queries = []
for e in fields:
sql = SqliteDriver._schema_by_attrs(e)
if sql:
field_queries.append(sql)
field_queries = ',\n'.join(field_queries)
return dpModel().execute(
'\n'.join([
'CREATE TABLE `%s`(' % table.__table_name__, field_queries, ')'
]), None, dsn)
def migrate_priority(dsn, table, fields):
succeed = True
proxy = dpModel().begin(dsn)
try:
exist = MySqlDriver._get_exist(proxy, table)
exist = [(e[0], e[1]) for e in sorted(exist['col'].items(), key=lambda o: o[1].__field_priority__)]
exist_priority = [e[0] for e in sorted(exist, key=lambda o: o[1].__field_priority__)]
exist_fields = dict(exist)
modified = False
for i in range(len(fields)):
if fields[i][0] != exist_priority[i]:
modified = True
if modified:
previous = None
queries = []
for k, v in fields:
if previous is None:
p = 'FIRST'
else:
p = 'AFTER `%s`' % previous
previous = v.name
queries.append('CHANGE COLUMN `%s` %s %s' % (v.name, exist_fields[k].query, p))
proxy.execute('ALTER TABLE `%s`\n%s' % (table.__table_name__, ',\n'.join(queries)))
proxy.commit()
logging.info('Table priority rearrange succeed : %s :: %s' % (dsn, table.__table_name__))
except Exception as e:
succeed = False
proxy.rollback()
logging.exception(e)
logging.error('Table priority rearrange failed : %s :: %s' % (dsn, table.__table_name__))
return succeed
def migrate(dsn, table, fields, indexes, foreign_keys, migrate_data=True):
for k, v in fields:
if not v.name:
setattr(v, 'name', k)
created = False
succeed = True
for val in (False, True):
proxy = dpModel().begin(dsn)
try:
exist = MySqlDriver._get_exist(proxy, table)
create = MySqlDriver.migrate_fields(proxy, table, fields, exist, change=val)
if not val:
created = create
if not val:
MySqlDriver.migrate_indexes(proxy, table, indexes, exist)
MySqlDriver.migrate_foreign_keys(proxy, table, foreign_keys, exist)
proxy.commit()
if val:
logging.info('Table migration succeed : %s :: %s' % (dsn, table.__table_name__))
except Exception as e:
succeed = True
proxy.rollback()
logging.exception(e)
logging.error('Table migration failed : %s :: %s' % (dsn, table.__table_name__))
break
if MySqlDriver.migrate_priority(dsn, table, fields) is False:
return False
if migrate_data:
return MySqlDriver.migrate_data(dsn, table) and succeed
return succeed
def migrate_data(dsn, table):
succed = True
dummy_data = getattr(table, '__dummy_data__', None)
if dummy_data:
dummy_data = dummy_data if isinstance(dummy_data, (tuple, list)) else list(dummy_data)
proxy = dpModel().begin(dsn)
try:
for e in dummy_data:
fields = []
params = []
for k, v in e.items():
fields.append(k)
params.append(v)
proxy.execute("""
INSERT INTO {table_name}
({fields}) VALUES ({params})
ON DUPLICATE KEY UPDATE
{updates}"""
.replace('{table_name}', table.__table_name__)
.replace('{fields}', ','.join(['`%s`' % ee for ee in fields]))
.replace('{params}', ','.join(['%s' for ee in fields]))
.replace('{updates}', ','.join(['`%s` = VALUES(`%s`)' % (ee, ee) for ee in fields])),
params)
proxy.commit()
logging.info('Table data insertion succeed : %s :: %s' % (dsn, table.__table_name__))
except Exception as e:
succed = False
proxy.rollback()
logging.exception(e)
logging.error('Table data insertion failed : %s :: %s' % (dsn, table.__table_name__))
return succed