def setup_named_shortlinks():
parsed = read_spreadsheet("Shortlinks")
next(parsed) # discard headers
all_files = []
for line in parsed:
short_link, full_name, url, discoverable, *_ = line
data = requests.get(url).text
file = ServerFile(
short_link, full_name, url, data, None, int(discoverable == "TRUE")
)
all_files.append(file)
with connect_db() as db:
db("DROP TABLE IF EXISTS links")
db(
"""CREATE TABLE links (
short_link varchar(128),
full_name varchar(128),
url varchar(1024),
data LONGBLOB,
discoverable BOOLEAN)"""
)
db(
"INSERT INTO links VALUES (%s, %s, %s, %s, %s)",
[[x[:4] + x[5:]] for x in all_files],
)
def setup_stored_files():
# refresh stored files
parsed = read_spreadsheet("Saved Files")
next(parsed) # discard headers
stored_files = []
for line in parsed:
file_name, url, *_ = line
data = requests.get(url).text
stored_files.append([file_name, data])
with connect_db() as db:
db("DROP TABLE IF EXISTS stored_files")
db("CREATE TABLE stored_files (file_name varchar(128), file_contents LONGBLOB)"
)
db("INSERT INTO stored_files VALUES (%s, %s)", stored_files)
def setup_shortlink_paths():
parsed = read_spreadsheet("Shortlink Paths")
next(parsed) # discard headers
paths = []
for candidate_path, requested_path, *_ in parsed:
if requested_path.startswith("/"):
requested_path = requested_path[1:]
if requested_path and not requested_path.endswith("/"):
requested_path += "/"
paths.append([candidate_path, requested_path])
with connect_db() as db:
db("DROP TABLE IF EXISTS linkPaths")
db("CREATE TABLE linkPaths (candidate_path varchar(256), requested_path varchar(256))"
)
db("INSERT INTO linkPaths VALUES (%s, %s)", paths)
def setup_ok_server_interface():
CACHE.clear()
parsed = read_spreadsheet("OK Config Paths")
_, _, _, _, semester = next(parsed)
lookups = []
for line in parsed:
pattern, template, *_ = line
lookups.append([pattern, template])
with connect_db() as db:
db("DROP TABLE IF EXISTS ok_lookups")
db("CREATE TABLE ok_lookups (email varchar(128), template varchar(128))"
)
db("INSERT INTO ok_lookups VALUES (%s, %s)", lookups)
db("DROP TABLE IF EXISTS ok_data")
db("CREATE TABLE ok_data (name varchar(128), value varchar(128))")
db("INSERT INTO ok_data VALUES (%s, %s)", ["semester", semester])
def setup_preloaded_tables():
# refresh SQL preloaded tables
parsed = read_spreadsheet("Preloaded SQL Tables")
next(parsed) # discard headers
init_sql = []
for line in parsed:
url, *_ = line
resp = requests.get(url)
if resp.status_code == 200:
init_sql.append(resp.text)
with connect_db() as db:
joined_sql = "\n\n".join(init_sql)
joined_sql = re.sub(
r"create\s+table(?!\s+if\b)",
"CREATE TABLE IF NOT EXISTS ",
joined_sql,
flags=re.IGNORECASE,
)
encoded = b64encode(bytes(joined_sql, "utf-8"))
db("DROP TABLE IF EXISTS preloaded_tables")
db("CREATE TABLE preloaded_tables (data LONGBLOB)")
db("INSERT INTO preloaded_tables VALUES (%s)", [encoded])