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
)""")
