117 lines
2.4 KiB
Python
117 lines
2.4 KiB
Python
|
import docopt, os, sqlite3
|
||
|
|
|
||
|
|
usage = \
|
||
|
|
'''
|
||
|
|
Usage:
|
||
|
|
create_databases.py PATH
|
||
|
|
|
||
|
|
|
||
|
|
'''
|
||
|
|
|
||
|
|
|
||
|
|
args = docopt.docopt(usage)
|
||
|
|
|
||
|
|
|
||
|
|
path = args["PATH"]
|
||
|
|
|
||
|
|
def create_private_db(path):
|
||
|
|
private_path = os.path.join(path, "private")
|
||
|
|
os.makedirs(private_path)
|
||
|
|
|
||
|
|
private_db_file = os.path.join(private_path, "users.db")
|
||
|
|
db = sqlite3.connect(private_db_file)
|
||
|
|
|
||
|
|
cursor = db.cursor()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE users(" \
|
||
|
|
"uid integer PRIMARY KEY AUTOINCREMENT, "\
|
||
|
|
"username text UNIQUE, "\
|
||
|
|
"passwd_hash text, "\
|
||
|
|
"email text, "\
|
||
|
|
"first_name text, "\
|
||
|
|
"last_name text, " \
|
||
|
|
"is_confirmed bool)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE globals(" \
|
||
|
|
"confirmation_id_offset integer " \
|
||
|
|
")")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE email_queue(" \
|
||
|
|
"email_id integer PRIMARY KEY AUTOINCREMENT, " \
|
||
|
|
"sender text, " \
|
||
|
|
"recipient text, " \
|
||
|
|
"message text, " \
|
||
|
|
"in_transaction bool)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE confirmations_awaiting(" \
|
||
|
|
"user_id integer, " \
|
||
|
|
"convirmation_id integer PRIMARY KEY AUTOINCREMENT");
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE INDEX username ON users(username)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
db.close()
|
||
|
|
|
||
|
|
def create_public_db(path):
|
||
|
|
public_path = os.path.join(path, "pulic")
|
||
|
|
os.makedirs(public_path)
|
||
|
|
|
||
|
|
public_db_file = os.path.join(public_path, "data")
|
||
|
|
db = sqlite3.connect(public_db_file)
|
||
|
|
|
||
|
|
cursor = db.cursor()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE location_settings("\
|
||
|
|
"username text PRIMARY KEY, "\
|
||
|
|
"radius decimal, "\
|
||
|
|
"use_gps bool, "\
|
||
|
|
"country text)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE specimen_settings("\
|
||
|
|
"username text PRIMARY KEY, "\
|
||
|
|
"author text, "\
|
||
|
|
"location_uri text) ")
|
||
|
|
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE locations("\
|
||
|
|
"username text, "\
|
||
|
|
"latitude decimal, "\
|
||
|
|
"longitude decimal, "\
|
||
|
|
"name text, "\
|
||
|
|
"radius decimal, "\
|
||
|
|
"description text, "\
|
||
|
|
"country text, "\
|
||
|
|
"identifier text, "\
|
||
|
|
"address text, "\
|
||
|
|
"image_uri text)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE TABLE specimen("\
|
||
|
|
"username text, "\
|
||
|
|
"genus text, "\
|
||
|
|
"species text, "\
|
||
|
|
"subspecies text, "\
|
||
|
|
"sex text, "\
|
||
|
|
"nickname text, "\
|
||
|
|
"author text, "\
|
||
|
|
"image_uri text, "\
|
||
|
|
"location_uri text, "\
|
||
|
|
"identifier text, "\
|
||
|
|
"latitude decimal, "\
|
||
|
|
"longitude decimal)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
cursor.execute("CREATE INDEX username_loc on locations(username)")
|
||
|
|
cursor.execute("CREATE INDEX username_spec on specimen(username)")
|
||
|
|
db.commit()
|
||
|
|
|
||
|
|
|
||
|
|
create_private_db(path)
|
||
|
|
create_public_db(path)
|