from playhouse.migrate import PostgresqlMigrator, migrate
from redash.models import db
from redash import models
if __name__ == '__main__':
db.connect_db()
migrator = PostgresqlMigrator(db.database)
with db.database.transaction():
column = models.User.api_key
column.null = True
migrate(
migrator.add_column('users', 'api_key', models.User.api_key),
)
for user in models.User.select(models.User.id, models.User.api_key):
user.save(only=user.dirty_fields)
migrate(
migrator.add_not_null('users', 'api_key')
)
db.close_db(None)
from playhouse.migrate import PostgresqlMigrator, migrate
from redash.models import db
from redash import models
if __name__ == '__main__':
db.connect_db()
migrator = PostgresqlMigrator(db.database)
with db.database.transaction():
column = models.User.api_key
column.null = True
migrate(migrator.add_column('users', 'api_key', models.User.api_key), )
for user in models.User.select(models.User.id, models.User.api_key):
user.save(only=user.dirty_fields)
migrate(migrator.add_not_null('users', 'api_key'))
db.close_db(None)
class Migrate(object):
def __init__(self, rules, func):
self.rules = rules
self.func = func
self.actions = []
self.raw_actions = []
self.m = PostgresqlMigrator(database)
def run(self):
conn = database.obj.connection()
for rule in self.rules:
with conn.cursor() as cur:
if not rule(cur):
return
self.func(self)
self.apply()
def apply(self):
print('Applying {} actions'.format(len(self.actions)))
migrate(*self.actions)
print('Executing {} raw queries'.format(len(self.raw_actions)))
conn = database.obj.connection()
for query, args in self.raw_actions:
with conn.cursor() as cur:
cur.execute(query, args)
conn.commit()
def add_columns(self, table, *fields):
for field in fields:
self.actions.append(
self.m.add_column(table._meta.db_table, field.name, field))
def rename_column(self, table, field, new_name):
self.actions.append(
self.m.rename_column(table._meta.db_table, field.name, new_name))
def drop_not_nulls(self, table, *fields):
for field in fields:
self.actions.append(
self.m.drop_not_null(table._meta.db_table, field.name))
def add_not_nulls(self, table, *fields):
for field in fields:
self.actions.append(
self.m.add_not_null(table._meta.db_table, field.name))
def execute(self, query, params=None):
self.raw_actions.append((query, params or []))
def backfill_column(self,
table,
old_columns,
new_columns,
pkeys=None,
cast_funcs=None):
total = table.select().count()
if not pkeys:
pkeys = [table._meta.primary_key]
q = table.select(*(pkeys + old_columns)).tuples()
idx = 0
modified = 0
start = time.time()
with database.transaction() as txn:
for values in q:
idx += 1
if idx % 10000 == 0:
print('[%ss] Backfilling %s %s/%s (wrote %s)' %
(time.time() - start, str(table), idx, total,
modified))
if modified % 1000:
txn.commit()
obj = {
new_column.name:
cast_funcs[new_column](values[i + len(pkeys)])
if cast_funcs and new_column in cast_funcs else values[i] +
len(pkeys)
for i, new_column in enumerate(new_columns)
}
if not any(obj.values()):
continue
modified += 1
table.update(
**{
new_column.name: values[i + len(pkeys)]
for i, new_column in enumerate(new_columns)
}).where(
reduce(operator.and_,
[(iz == values[i])
for i, iz in enumerate(pkeys)])).execute()
txn.commit()
print('DONE, %s scanned %s written' % (idx, modified))
@staticmethod
def missing(table, field):
def rule(cursor):
cursor.execute(COLUMN_EXISTS_SQL, (table._meta.db_table, field))
if len(cursor.fetchall()) == 0:
return True
return False
return rule
@staticmethod
def nullable(table, field):
def rule(cursor):
cursor.execute(GET_NULLABLE_SQL, (table._meta.db_table, field))
return cursor.fetchone()[0] == 'YES'
return rule
@staticmethod
def non_nullable(table, field):
def rule(cursor):
cursor.execute(GET_NULLABLE_SQL, (table._meta.db_table, field))
return cursor.fetchone()[0] == 'NO'
return rule
@classmethod
def only_if(cls, check, table, *fields):
def deco(func):
rules = [check(table, i) for i in fields]
cls(rules, func).run()
return deco
@classmethod
def always(cls):
def deco(func):
cls([lambda c: True], func).run()
return deco