· 6 years ago · Dec 23, 2019, 09:26 AM
1from websocket_server import WebsocketServer
2import sqlite3
3import json
4from datetime import date
5from itertools import zip_longest
6
7class data:
8
9 def __init__(self):
10 self.conn = sqlite3.connect('data.db', check_same_thread=False)
11 self.c = self.conn.cursor()
12 print("Create data base")
13 self.init_tables()
14
15 def init_tables(self):
16 self.c.execute("PRAGMA foreign_keys")
17 self.c.execute('''CREATE TABLE if not exists user (
18 user_id integer PRIMARY KEY,
19 user_name text UNIQUE,
20 user_pass text)''')
21
22 self.c.execute('''CREATE TABLE if not exists table_ (
23 table_id integer,
24 user_id integer,
25 table_name text,
26 table_date text,
27 last_update text,
28 OX_name text,
29 OY_name text,
30 type text,
31 PRIMARY KEY (table_id),
32 FOREIGN KEY (user_id) REFERENCES user (user_id))''')
33
34 self.c.execute('''CREATE TABLE if not exists table_data (
35 table_id integer,
36 OX integer,
37 OY integer,
38 note text)''')
39
40 self.conn.commit()
41
42 def get_user_id(self, user):
43 return self.c.execute('SELECT user_id from user WHERE user_name = ?', [user]).fetchone()[0]
44
45 def grouper(self, n, iterable, fillvalue=None):
46 args = [iter(iterable)] * n
47 return zip_longest(*args, fillvalue=fillvalue)
48
49 def add_table(self, user, table_name, data, OX_name, OY_name, type_):
50 user_id = self.get_user_id(user)
51 self.c.execute('INSERT INTO table_ (table_id, user_id, table_name, table_date, last_update, OX_name, OY_name, type) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)', [user_id, table_name, date.today(), date.today(), OX_name, OY_name, type_])
52 data = list(self.grouper(2, data))
53 table_id = self.c.execute("SELECT count(*) from table_").fetchone()[0]
54
55 for x, y in data:
56 self.c.execute('INSERT INTO table_data VALUES (?, ?, ?, ?)', [table_id, x, y, ""])
57
58 self.conn.commit()
59
60 def add_user(self, name, passw):
61 # (user_name, user_pass)
62 try:
63 self.c.execute('INSERT INTO user VALUES (NULL, ?, ?)', [name, passw,])
64 except Exception as e:
65 print("User already exist")
66 return False
67 self.conn.commit()
68 return True
69
70 def check_user(self, name, passw):
71 res = None
72 try:
73 res = self.c.execute('SELECT user_pass from user WHERE user_name = (?)', [name]).fetchone()[0]
74 except Exception as e:
75 print("No such user")
76 return False
77 print(res)
78 return passw == res
79
80 def get_tabels(self, user):
81 user_id = self.get_user_id(user)
82 table_info = self.c.execute('SELECT table_id, table_name, table_date, last_update, OX_name, OY_name, type from table_ WHERE user_id = ?', [user_id]).fetchall()
83 table_data = []
84 for table_id in [a[0] for a in table_info]:
85 table_data.append(self.c.execute('SELECT OX, OY, note from table_data WHERE table_id = ?', [table_id]).fetchall())
86
87 return list(zip(table_info, table_data))
88
89 def add_note(self, table_id, X, note):
90 self.c.execute('UPDATE table_data set note = ? WHERE table_id = ? and OX = ?', [note, table_id, X]);
91 self.c.execute('UPDATE table_ set last_update = ? WHERE table_id = ?', [table_id, date.today()]);
92
93class server:
94 PORT=8080
95 server = None
96 clients_ = dict()
97
98 def __init__(self):
99 self.data = data()
100 self.run_server()
101
102 def run_server(self):
103 server = WebsocketServer(self.PORT)
104 server.set_fn_new_client(self.new_client)
105 server.set_fn_client_left(self.client_left)
106 server.set_fn_message_received(self.message_received)
107 server.run_forever()
108
109 def new_client(self, client, server):
110 print("New client")
111
112 def client_left(self, client, server):
113 print("Client leave", client['id'])
114 pass
115
116 def message_received(self, client, server, message):
117 data = json.loads(message)
118 print("Client id : ", client['id'], data)
119 server.send_message(client, self.treat_message(data, client['id']))
120
121 def check_name(self, name):
122 if len(name) > 13:
123 return [False, "Too Long, user name must be at list less then 13 chars"]
124 if len(name) < 4:
125 return [False, "Too short, user name must be at list more then 3 chars"]
126 return [True]
127
128 def check_pass(self, passw):
129 if len(passw) > 13:
130 return [False, "Too Long, user pass must be at list less then 13 chars"]
131 if len(passw) < 4:
132 return [False, "Too short, user pass must be at list more then 3 chars"]
133 return [True]
134
135 def treat_message(self, data, client_id):
136 if (data["mode"] == "Sign in"):
137 checks = [self.check_name(data["name"]), self.check_pass(data["pass"])]
138 for check in checks:
139 if not check[0]:
140 return json.dumps({"mode" : "bad auth", "error" : check[1]})
141
142 if self.data.check_user(data["name"], data["pass"]):
143 self.clients_[client_id] = data["name"];
144 return json.dumps({"mode" : "auth succes", "name" : data["name"]})
145 else:
146 return json.dumps({"mode" : "bad auth", "error" : "Bad auth, try again"})
147
148 elif (data["mode"] == "Sign up"):
149 checks = [self.check_name(data["name"]), self.check_pass(data["pass"])]
150 for check in checks:
151 if not check[0]:
152 return json.dumps({"mode" : "bad user creating", "error" : check[1]})
153
154 if self.data.add_user(data["name"], data["pass"]):
155 self.clients_[client_id] = data["name"];
156 return json.dumps({"mode" : "creating user succes", "name" : data["name"]})
157 else:
158 return json.dumps({"mode" : "bad user creating", "error" : "User is already exists, try another UserName"})
159
160 elif (data["mode"] == "Create table"):
161 self.data.add_table(self.clients_[client_id], data["table name"], data["data"], data["Ox"], data["Oy"], data["type"]);
162 return json.dumps({"mode" : "creating table succes"})
163
164 elif (data["mode"] == "Get tables"):
165 return json.dumps({"mode" : "user tables", "data" : self.data.get_tabels(self.clients_[client_id])})
166 elif (data["mode"] == "New note"):
167 self.data.add_note(data["table_id"], data["X"], data["note"]);
168 return json.dumps({"mode" : "Note successfully created"})
169 else:
170 print("error");
171
172
173if __name__ == '__main__':
174 S = server()