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()]
