def fetch_columns(driver: Driver, db: Database, table_name: str): if driver is Driver.SQLITE: c = db.execute_sql(f"PRAGMA table_info({table_name});") res = c.fetchall() return [i[1] for i in res] else: c = db.execute_sql(f"SELECT column_name " f"FROM INFORMATION_SCHEMA.COLUMNS " f"WHERE table_name = '{table_name}';") res = c.fetchall() return [i[0] for i in res]
def drop_index_if_exists(driver: Driver, db: Database, table_name: str, index_name: str): if driver is Driver.MYSQL: # mysql needs DROP ... ON indexes = fetch_indexes(driver, db, table_name) if index_name in indexes: db.execute_sql(f"DROP INDEX `{index_name}` ON `{table_name}`;") return else: db.execute_sql(f'DROP INDEX IF EXISTS "{index_name}";') return
def down_grade(driver: Driver, db: Database): assert driver is not Driver.SQLITE # sqlite doesn't support drop column columns = fetch_columns(driver, db, "dbbardata") if 'open_interest' in columns: db.execute_sql("ALTER TABLE dbbardata " " DROP COLUMN open_interest;") columns = fetch_columns(driver, db, "dbtickdata") if 'open_interest' in columns: db.execute_sql("ALTER TABLE dbtickdata " " DROP COLUMN open_interest;") print("down grade succeed!")
def upgrade(driver: Driver, db: Database): columns = fetch_columns(driver, db, "dbbardata") if 'open_interest' not in columns: db.execute_sql("ALTER TABLE dbbardata " " ADD COLUMN open_interest FLOAT DEFAULT 0;") columns = fetch_columns(driver, db, "dbtickdata") if 'open_interest' not in columns: db.execute_sql("ALTER TABLE dbtickdata " " ADD COLUMN open_interest FLOAT DEFAULT 0;") print("upgrade succeed!")
def create_index_if_not_exists(driver: Driver, db: Database, table_name: str, index_name: str, *args): indexes = fetch_indexes(driver, db, table_name) if index_name not in indexes: if driver is Driver.POSTGRESQL: # psql does'nt supports `` keys = ",".join([f'"{key}"' for key in args]) db.execute_sql(f'CREATE UNIQUE INDEX "{index_name}" ' f' ON "{table_name}" ({keys});') else: # mysql needs ``, others supports `` keys = ",".join([f'`{key}`' for key in args]) db.execute_sql(f'CREATE UNIQUE INDEX `{index_name}` ' f' ON `{table_name}` ({keys});')
def fetch_indexes(driver: Driver, db: Database, table_name: str): if driver is Driver.SQLITE: c = db.execute_sql(f"PRAGMA index_list({table_name});") res = c.fetchall() res = [i[1] for i in res] return res elif driver is Driver.POSTGRESQL: c = db.execute_sql(f"SELECT indexname " f" FROM pg_indexes" f" WHERE tablename = '{table_name}';") return [i[0] for i in c.fetchall()] else: c = db.execute_sql(f"SELECT DISTINCT INDEX_NAME" f" FROM INFORMATION_SCHEMA.STATISTICS" f" WHERE TABLE_NAME = '{table_name}';") return [i[0] for i in c.fetchall()]