118 lines
3.9 KiB
Python
118 lines
3.9 KiB
Python
import psycopg2
|
|
from psycopg2 import sql
|
|
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
|
|
|
|
def createDatabase(dbuser, dbpass, dbhost, dbname):
|
|
try:
|
|
conn = psycopg2.connect(
|
|
host=dbhost,
|
|
user=dbuser,
|
|
password=dbpass,
|
|
database="postgres"
|
|
)
|
|
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (dbname,))
|
|
exists = cur.fetchone()
|
|
|
|
if not exists:
|
|
cur.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(dbname)))
|
|
print(f"Database '{dbname}' created successfully")
|
|
else:
|
|
print(f"Database '{dbname}' already exists")
|
|
|
|
cur.close()
|
|
conn.close()
|
|
|
|
except Exception as e:
|
|
print(f"Error creating database: {e}")
|
|
raise
|
|
|
|
def createTables(dbuser, dbpass, dbhost, dbname):
|
|
try:
|
|
conn = psycopg2.connect(
|
|
host=dbhost,
|
|
user=dbuser,
|
|
password=dbpass,
|
|
database=dbname
|
|
)
|
|
cur = conn.cursor()
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS groups (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) UNIQUE NOT NULL,
|
|
parent UUID REFERENCES groups(id),
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
print("Table 'groups' created or already exists")
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
email VARCHAR(255),
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
group_id UUID REFERENCES groups(id)
|
|
)
|
|
""")
|
|
print("Table 'users' created or already exists")
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS userData (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id uuid REFERENCES users(id),
|
|
service_id VARCHAR(255) NOT NULL,
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
property VARCHAR(255) NOT NULL,
|
|
value VARCHAR(255) NOT NULL
|
|
)
|
|
""")
|
|
print("Table 'userData' created or already exists")
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS groupData (
|
|
id SERIAL PRIMARY KEY,
|
|
group_id uuid REFERENCES groups(id),
|
|
service_id VARCHAR(255) NOT NULL,
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
property VARCHAR(255) NOT NULL,
|
|
value VARCHAR(255) NOT NULL
|
|
)
|
|
""")
|
|
print("Table 'userData' created or already exists")
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id uuid REFERENCES users(id),
|
|
action VARCHAR(255),
|
|
details VARCHAR(255),
|
|
user_ip VARCHAR(255),
|
|
user_agent VARCHAR(255),
|
|
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
print("Table 'logs' created or already exists")
|
|
|
|
cur.execute("""
|
|
CREATE TABLE IF NOT EXISTS userTokens (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
owner_id uuid REFERENCES users(id),
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
expiration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP + INTERVAL '30 days'
|
|
)
|
|
""")
|
|
print("Table 'userTokens' created or already exists")
|
|
|
|
conn.commit()
|
|
cur.close()
|
|
conn.close()
|
|
|
|
except Exception as e:
|
|
print(f"Error creating tables: {e}")
|
|
raise |