-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbinit.py
63 lines (55 loc) · 1.94 KB
/
dbinit.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import os
import sys
import psycopg2 as dbapi2
INIT_STATEMENTS = [
'DROP TABLE IF EXISTS public.categories CASCADE',
'DROP TABLE IF EXISTS public.items CASCADE',
'DROP TABLE IF EXISTS public.posts CASCADE',
'DROP TABLE IF EXISTS public.users CASCADE',
'''CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(32) NOT NULL UNIQUE,
password varchar(200) NOT NULL,
email varchar(254) NOT NULL UNIQUE,
phone_number varchar(11) NOT NULL ,
profile_pic varchar(120) NOT NULL);
''',
'''CREATE TABLE categories (
id serial PRIMARY KEY,
category_name varchar(32) NOT NULL UNIQUE );
''',
'''CREATE TABLE items (
id serial PRIMARY KEY,
category_id serial NOT NULL,
name varchar(32) NOT NULL,
description varchar(500) NOT NULL,
image varchar(120) NOT NULL DEFAULT 'default.jpg',
color varchar(20),
situation varchar(20),
CONSTRAINT CONSTRAINT1 FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE );
''',
'''CREATE TABLE posts (
id serial PRIMARY KEY,
user_id serial NOT NULL,
item_id serial NOT NULL,
post_date date NOT NULL,
is_active boolean DEFAULT TRUE,
is_sold boolean DEFAULT FALSE,
tag1 varchar(15),
tag2 varchar(15),
CONSTRAINT CONSTRAINT1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
CONSTRAINT CONSTRAINT2 FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE CASCADE);
''',
]
def initialize(url):
with dbapi2.connect(url) as connection:
cursor = connection.cursor()
for statement in INIT_STATEMENTS:
cursor.execute(statement)
cursor.close()
if __name__ == "__main__":
url = os.getenv("DATABASE_URL")
if url is None:
print("Usage: DATABASE_URL=url python dbinit.py", file=sys.stderr)
sys.exit(1)
initialize(url)