jD0QzLPG

1import sqlite3
2
3
4def ensure_connection(func):
5    def inner(*args, **kwargs):
6        with sqlite3.connect('likem.db') as conn:
7            res=func(*args, conn = conn, **kwargs)
8        return res
9    return inner
10
11@ensure_connection
12def init_db(conn, forse: bool=False):  
13
14    c = conn.cursor()
15
16    if forse:
17        c.execute('DROP TABLE IF EXISTS user_like')
18        c.execute('DROP TABLE IF EXISTS autor_info')
19        c.execute('DROP TABLE IF EXISTS user_chat_id')
20
21    c.execute('''
22            CREATE TABLE IF NOT EXISTS user_like (
23                    id              INTEGER PRIMARY KEY,
24                    user_id         INTEGER NOT NULL,
25                    message_id      INTEGER NOT NULL,
26                    like_value      INTEGER, 
27                    dislike_value   INTEGER,
28                    datatime        DATE
29                    )''')
30    conn.commit()
31
32    c.execute('''
33                CREATE TABLE IF NOT EXISTS autor_info (
34                        id              INTEGER PRIMARY KEY,
35                        autor_id        VARCHAR(100),
36                        message_id      INTEGER,
37                        datatime        DATE)''')
38    conn.commit()
39
40    c.execute('''
41                CREATE TABLE IF NOT EXISTS user_chat_id (
42                        id              INTEGER PRIMARY KEY,
43                        user_id         INTEGER NOT NULL,
44                        chat_id         INTEGER)''')
45    conn.commit()
46
47@ensure_connection
48def add_autor_id(conn, autor_id, message_id, data_time):
49    c = conn.cursor()
50    c.execute('INSERT INTO autor_info (autor_id, message_id, datatime) VALUES (?,?,?)', (autor_id, message_id, data_time))
51    conn.commit()
52
53
54@ensure_connection
55def add_user_chat_id(conn, user_id, chat_id):
56    c = conn.cursor()
57    c.execute('INSERT INTO user_chat_id (user_id, chat_id) VALUES (?,?)', (user_id, chat_id))
58    conn.commit()
59
60
61@ensure_connection
62def get_user_chat_id(conn):
63    c = conn.cursor()
64    res = c.execute('SELECT chat_id FROM user_chat_id').fetchall()
65    return res
66
67
68@ensure_connection
69def get_autor_id(conn, message_id):
70    c = conn.cursor()
71    res = c.execute('SELECT autor_id FROM autor_info WHERE message_id=?', (message_id,)).fetchone()
72    return res
73
74
75@ensure_connection
76def add_user_id_and_message_id(conn, user_id, message_id, like, data_time):
77    c = conn.cursor()
78    if like:
79        c.execute('INSERT INTO user_like (user_id, message_id, like_value, dislike_value, datatime) VALUES (?,?,1,0,?)', (user_id, message_id, data_time))
80    else:
81        c.execute('INSERT INTO user_like (user_id, message_id, like_value, dislike_value,datatime) VALUES (?,?,0,1,?)', (user_id, message_id, data_time))
82    conn.commit()
83
84
85@ensure_connection
86def verify_user(conn, user_id, message_id):
87    c = conn.cursor()
88    res = c.execute(
89        'SELECT user_id, message_id FROM user_like WHERE ((like_value=1 OR dislike_value=1) AND (user_id=? AND message_id=?))',
90        (user_id, message_id))
91    if res.fetchall() != []: return True
92    return False
93
94
95@ensure_connection
96def get_summ(conn, message_id):
97    c = conn.cursor()
98    summ_like = c.execute('SELECT message_id, SUM(like_value) FROM user_like WHERE message_id=?',
99                          (message_id,)).fetchone()
100    summ_dislike = c.execute('SELECT message_id, SUM(dislike_value) FROM user_like WHERE message_id=?',
101                             (message_id,)).fetchone()
102    summ_result = [summ_like[1], summ_dislike[1]]
103    for i in range(len(summ_result)):
104        if summ_result[i] == None: summ_result[i] = 0
105        else: continue
106    return summ_result