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