85 lines
2.1 KiB
Python
85 lines
2.1 KiB
Python
"""Create the necessary tables in an SQLite database"""
|
|
|
|
|
|
import sqlite3
|
|
|
|
|
|
def connect_db():
|
|
conn = sqlite3.connect('database/database.sqlite')
|
|
conn.execute("PRAGMA foreign_keys = 1")
|
|
return conn
|
|
|
|
|
|
def create_user_table():
|
|
query = """
|
|
create table if not exists users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT,
|
|
email TEXT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password TEXT NOT NULL,
|
|
created_on TEXT default CURRENT_TIMESTAMP,
|
|
active BOOLEAN default TRUE
|
|
)
|
|
"""
|
|
|
|
try:
|
|
connection = connect_db()
|
|
connection.execute(query)
|
|
connection.commit()
|
|
except Exception as e:
|
|
print("Error creating users table", e)
|
|
return False
|
|
|
|
return True
|
|
|
|
|
|
def create_keys_table():
|
|
query = """
|
|
create table if not exists keys (
|
|
user_id INTEGER,
|
|
encryption_key TEXT,
|
|
encryption_key_salt TEXT,
|
|
FOREIGN KEY (user_id) REFERENCES users (id)
|
|
ON DELETE CASCADE
|
|
)
|
|
"""
|
|
try:
|
|
connection = connect_db()
|
|
connection.execute(query)
|
|
connection.commit()
|
|
except Exception as e:
|
|
print("Error creating keys table", e)
|
|
return False
|
|
|
|
return True
|
|
|
|
|
|
def create_secrets_table():
|
|
query = """
|
|
create table if not exists secrets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
data TEXT NOT NULL,
|
|
added_on TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
modified_on TEXT,
|
|
active BOOLEAN DEFAULT TRUE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
)
|
|
"""
|
|
try:
|
|
connection = connect_db()
|
|
connection.execute(query)
|
|
connection.commit()
|
|
except Exception as e:
|
|
print("Error creating secrets tabe", e)
|
|
return False
|
|
|
|
return True
|
|
|
|
|
|
if __name__ == '__main__':
|
|
create_user_table()
|
|
create_keys_table()
|
|
create_secrets_table() |