w6hYBFdt

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