def setup_enums(): glc.execute_db_command( """CREATE TYPE course_type AS ENUM ('MAIN', 'REC', 'LAB', 'L/L', 'WRK')""" ) glc.execute_db_command( """CREATE TYPE course_status AS ENUM ('Open', 'Closed', 'Cancelled')""" )
def setup_authors(): glc.execute_db_command("""CREATE TABLE authors ( author_id serial UNIQUE PRIMARY KEY, first_name varchar(50) NOT NULL, middle_name varchar(250), last_name varchar(50) )""")
def setup_courses(): glc.execute_db_command("""CREATE TABLE courses ( crn integer UNIQUE PRIMARY KEY, dept char(3) NOT NULL, dept_num varchar(4) NOT NULL, title text NOT NULL, term_year varchar(15) NOT NULL, crs_type course_type NOT NULL, credits real DEFAULT 0.0, status course_status NOT NULL, mt_day varchar(3), mt_start_time time, mt_end_time time, loc_building varchar(10), loc_room varchar(6), sec_enrolled smallint, sec_enroll_cap smallint, total_enrolled smallint, total_enroll_cap smallint, comments text, prof varchar(50), prereqs text, descrip text, url varchar(512) UNIQUE )""")
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)
def main(conn, cursor): glc.execute_db_command("""DROP SCHEMA public CASCADE""") glc.execute_db_command("""CREATE SCHEMA public""") glc.execute_db_command("""GRANT ALL ON SCHEMA public TO postgres""") glc.execute_db_command("""GRANT ALL ON SCHEMA public TO public""") glc.execute_db_command( """COMMENT ON SCHEMA public IS 'standard public schema'""")
def remove_article_dups(connection=None, cursor=None): glc.execute_db_command("""DELETE FROM articles WHERE article_id IN ( SELECT article_id FROM ( SELECT article_id, ROW_NUMBER() OVER ( PARTITION BY title, publish_date, source_id ORDER BY article_id ) AS rnum FROM articles ) t WHERE t.rnum > 1) )""")
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 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 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 setup_articles(): glc.execute_db_command("""CREATE TABLE articles ( article_id serial UNIQUE PRIMARY KEY, title varchar(512) NOT NULL, url varchar(512), publish_date DATE NOT NULL, content text NOT NULL, main_img_url varchar(1024), source_id integer NOT NULL REFERENCES sources on DELETE RESTRICT, is_fake boolean, fake_type varchar(25) NOT NULL DEFAULT 'NO_CLASS' )""")
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 add_table_to_db(table_name, *cols, connection=None, cursor=None, VERBOSE=True): 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 setup_constraints(): glc.execute_db_command( """ALTER TABLE articles ADD CONSTRAINT check_fake_types CHECK (fake_type IN ('bs', 'conspiracy', 'satire', 'hate', 'fake', 'state', 'junksci', 'bias', 'NO_CLASS'))""" ) glc.execute_db_command( """ALTER TABLE articles ADD CONSTRAINT unique_articles UNIQUE (title, publish_date, source_id)""" ) glc.execute_db_command( """ALTER TABLE authors ADD CONSTRAINT unique_authors UNIQUE (first_name, last_name)""" )
def setup_linking_tables(): glc.execute_db_command( """CREATE TABLE article_authors ( article_id integer NOT NULL REFERENCES articles ON DELETE CASCADE, author_id integer NOT NULL REFERENCES authors ON DELETE CASCADE )""", None) glc.execute_db_command( """CREATE TABLE source_authors ( source_id integer NOT NULL REFERENCES sources ON DELETE CASCADE, author_id integer NOT NULL REFERENCES authors ON DELETE CASCADE )""", None) glc.execute_db_command("""CREATE TABLE article_tags ( article_id integer NOT NULL REFERENCES articles ON DELETE CASCADE, tag_id integer NOT NULL REFERENCES tags ON DELETE CASCADE )""")
def setup_indexes(): glc.execute_db_command("""CREATE INDEX tag_name_skey ON tags (tag_name)""") glc.execute_db_command( """CREATE INDEX source_name_skey ON sources (name)""") glc.execute_db_command( """CREATE INDEX author_fname_skey ON authors (first_name)""") glc.execute_db_command( """CREATE INDEX author_lname_skey ON authors (last_name)""") glc.execute_db_command( """CREATE INDEX author_fullname_skey ON authors (first_name, last_name)""" ) glc.execute_db_command( """CREATE INDEX article_title_skey ON articles (title)""") glc.execute_db_command( """CREATE INDEX author_source_skey ON articles (source_id)""") glc.execute_db_command( """CREATE INDEX ar_au_article_skey ON article_authors (article_id)""") glc.execute_db_command( """CREATE INDEX ar_au_author_skey ON article_authors (author_id)""") glc.execute_db_command( """CREATE INDEX s_au_source_skey ON source_authors (source_id)""") glc.execute_db_command( """CREATE INDEX s_au_author_skey ON source_authors (author_id)""") glc.execute_db_command( """CREATE INDEX ar_t_article_skey ON article_tags (article_id)""") glc.execute_db_command( """CREATE INDEX ar_t_tag_skey ON article_tags (tag_id)""") glc.execute_db_command( """CREATE UNIQUE INDEX ar_au_unique_skey ON article_authors (article_id, author_id)""" ) glc.execute_db_command( """CREATE UNIQUE INDEX s_au_unique_skey ON source_authors (source_id, author_id)""" ) glc.execute_db_command( """CREATE UNIQUE INDEX ar_t_unique_skey ON article_tags (article_id, tag_id)""" ) glc.execute_db_command("""CREATE INDEX token_skey ON tokens (token)""")
def setup_tokens(): glc.execute_db_command("""CREATE TABLE tokens ( token_id serial UNIQUE PRIMARY KEY, token text UNIQUE )""")
def setup_tags(): glc.execute_db_command("""CREATE TABLE tags ( tag_id serial UNIQUE PRIMARY KEY, tag_name varchar(255) UNIQUE )""")
def clean_zero_content_articles(): glc.execute_db_command( """DELETE FROM articles WHERE content SIMILAR TO '( |\t|\r|\n)*';""")
def setup_sources(): glc.execute_db_command("""CREATE TABLE sources ( source_id serial UNIQUE PRIMARY KEY, name varchar(255) NOT NULL, base_url varchar(512) UNIQUE NOT NULL )""")