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