kidV2UQ0

1import sqlite3
2import sqlite3 as mdb
3from sqlite3 import Cursor
4
5
6class Database:
7
8    def __int__(self):
9        pass
10
11
12def connectToDb() -> sqlite3.Connection:
13    db = mdb.connect("users.db")
14    cursor = db.cursor()
15    db.execute("pragma foreign_keys")
16    createTableUsersRooms(db)
17    createTableTasks(db)
18    createTableUsers(db)
19    createTablePoll(db)
20    createTableUsersRooms(db)
21    print("tables created")
22    return db
23
24
25def chk_conn(self):
26    try:
27        self.cursor()
28        return True
29    except Exception as ex:
30        return False
31
32
33myconn = mdb.connect("users.db")
34if chk_conn(myconn):
35    print("Database connected")
36else:
37    print("Database connection failed")
38
39
40def createTableUsers(db: mdb.Connection):
41    cursor = db.cursor()
42    cursor.execute('''
43        CREATE TABLE IF NOT EXISTS users (
44            user_id integer PRIMARY KEY,
45            login text NOT NULL UNIQUE,
46            password text NOT NULL
47            )
48        ''')
49
50
51def createTableRooms(db: mdb.Connection):
52    cursor = db.cursor()
53    cursor.execute('''
54        CREATE TABLE IF NOT EXISTS rooms (
55            room_id integer PRIMARY KEY,
56            password text NOT NULL,
57            owner_id integer NOT NULL,
58            FOREIGN KEY (owner_id) REFERENCES users (id)
59            )
60        ''')
61
62
63def createTableUsersRooms(db: mdb.Connection):
64    cursor = db.cursor()
65    cursor.execute('''
66        CREATE TABLE IF NOT EXISTS users_rooms (
67            id integer PRIMARY KEY,
68            room_id integer NOT NULL,
69            user_id integer NOT NULL,
70            FOREIGN KEY (user_id) REFERENCES users (id),
71            FOREIGN KEY (room_id) REFERENCES rooms (id),
72            UNIQUE(room_id, user_id)
73           ) 
74        ''')
75
76
77def createTablePoll(db: mdb.Connection):
78    cursor = db.cursor()
79    cursor.execute('''
80        CREATE TABLE IF NOT EXISTS poll (
81            id integer PRIMARY KEY,
82            subject_id integer NOT NULL,
83            task_id integer NOT NULL,
84            user_id integer NOT NULL,
85            vote float NOT NULL,
86            FOREIGN KEY (task_id) REFERENCES tasks (id),
87            FOREIGN KEY (user_id) REFERENCES users (id)
88                    ) 
89                ''')
90
91
92def createTableTasks(db: mdb.Connection):
93    cursor = db.cursor()
94    cursor.execute('''
95        CREATE TABLE IF NOT EXISTS tasks (
96            id integer PRIMARY KEY,
97            room_id integer NOT NULL,
98            subject text NOT NULL,
99            FOREIGN KEY (room_id) references rooms (id)
100            ) 
101        ''')
102