H4fnzaJK

1class Database(object):
2    def __int__(self):
3        return self
4
5    connection = sqlite3.connect("users.db", timeout=10)
6    connection.execute("PRAGMA foreign_keys = ON;")
7    cursor = connection.cursor()
8    connection.isolation_level = ''
9    print("database connected")
10
11    cursor.execute('''
12        CREATE TABLE IF NOT EXISTS users (
13            id integer PRIMARY KEY,
14            login text NOT NULL UNIQUE,
15            password text NOT NULL
16            )
17        ''')
18
19    cursor.execute('''
20        CREATE TABLE IF NOT EXISTS rooms (
21            id integer PRIMARY KEY,
22            password text NOT NULL,
23            owner_id integer NOT NULL,
24            FOREIGN KEY (owner_id) REFERENCES users (id)
25            )
26        ''')
27
28    cursor.execute('''
29        CREATE TABLE IF NOT EXISTS users_rooms (
30            id integer PRIMARY KEY,
31            room_id integer NOT NULL,
32            user_id integer NOT NULL,
33            FOREIGN KEY (user_id) REFERENCES users (id),
34            FOREIGN KEY (room_id) REFERENCES rooms (id),
35            UNIQUE(room_id, user_id)
36           ) 
37        ''')
38
39    cursor.execute('''
40        CREATE TABLE IF NOT EXISTS poll (
41            id integer PRIMARY KEY,
42            subject_id integer NOT NULL,
43            task_id integer NOT NULL,
44            user_id integer NOT NULL,
45            vote float NOT NULL,
46            FOREIGN KEY (task_id) REFERENCES tasks (id),
47            FOREIGN KEY (user_id) REFERENCES users (id)
48                    ) 
49                ''')
50
51    cursor.execute('''
52        CREATE TABLE IF NOT EXISTS tasks (
53            id integer PRIMARY KEY,
54            room_id integer NOT NULL,
55            subject text NOT NULL,
56            FOREIGN KEY (room_id) references rooms (id)
57            ) 
58        ''')
59