def heroes(self):
glc.execute_db_command("""CREATE TABLE IF NOT EXISTS heroes (
id bigint UNIQUE PRIMARY KEY,
name varchar(100) NOT NULL,
localized_name varchar(50) UNIQUE NOT NULL,
url_full_portrait text NOT NULL,
url_small_portrait text NOT NULL,
url_large_portrait text NOT NULL,
url_vertical_portrait text NOT NULL
)""")
def items(self):
glc.execute_db_command("""CREATE TABLE IF NOT EXISTS items (
id bigint UNIQUE PRIMARY KEY,
name varchar(100) UNIQUE NOT NULL,
localized_name varchar(50) NOT NULL,
cost integer NOT NULL,
recipe boolean NOT NULL,
secret_shop boolean NOT NULL,
side_shop boolean NOT NULL,
url_image text NOT NULL
)""")
def add_table_to_db(table_name, *cols, connection = None, cursor = None, VERBOSE = False):
cols_string = ""
for col in cols:
cols_string = cols_string + col + ", "
cols_string = cols_string[0:-2]
glc.execute_db_command("""CREATE TABLE %s (%s)""", (table_name, cols_string), connection = connection, cursor = cursor)
if VERBOSE:
print("Added %d table with columns: ")
for col in cols:
print(" %s" % col)
def regions(self):
glc.execute_db_command("""CREATE TABLE IF NOT EXISTS regions (
id bigint UNIQUE PRIMARY KEY,
name varchar(50) NOT NULL
)""")
def leaver(self):
glc.execute_db_command("""CREATE TABLE IF NOT EXISTS leaver (
id bigint UNIQUE PRIMARY KEY,
name varchar(50) NOT NULL,
description text NOT NULL
)""")
def abilities(self):
glc.execute_db_command("""CREATE TABLE IF NOT EXISTS abilities (
id bigint UNIQUE PRIMARY KEY,
name varchar(100) NOT NULL
)""")
def drop_constraint_from_table(table, cons_name, connection = None, cursor = None, VERBOSE = False):
glc.execute_db_command("""ALTER TABLE %s DROP CONSTRAINT %s""", (table, cons_name), connection = connection, cursor = cursor)
if VERBOSE:
print("Dropped %s constraint from %s table" % (cons_name, table))
def drop_column_default(table, col_name, connection = None, cursor = None, VERBOSE = False):
glc.execute_db_command("""ALTER TABLE %s ALTER COLUMN DROP DEFAULT""", (table, col_name), connection = connection, cursor = cursor)
if VERBOSE:
print("Dropped default for %s in %s table.")
def set_column_default(table, col_name, default_val, connection = None, cursor = None, VERBOSE = False):
glc.execute_db_command("""ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s""", (table, col_name, default_val), connection = connection, cursor = cursor)
if VERBOSE:
print("Set default for %s in %s table to %s." % (col_name, table, default_val))
def drop_column_from_table(table, col_name, connection = None, cursor = None, VERBOSE = False):
glc.execute_db_command("""ALTER TABLE %s DROP COLUMN %s""", (table, col_name), connection = connection, cursor = cursor)
if VERBOSE:
print("Dropped column %s from %s table." % (col_name, table))
def add_column_to_table(table, col_name, col_type, connection = None, cursor = None, VERBOSE = False):
glc.execute_db_command("""ALTER TABLE %s ADD COLUMN %s %s""", (table, col_name, col_type))
if VERBOSE:
print("Added column %s to %s table [type:%s]." % (table, col_name, col_type))
def close_all_db_connections(dbname = os.environ.get("DBNAME"), connection = None, cursor = None):
glc.execute_db_command("""SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = %s AND pid <> pg_backend_pid();""", (dbname,), connection = connection, cursor = cursor)