TiledViz
Loading...
Searching...
No Matches
tvdb.py
1#!/bin/env python3
2
3import sys,os
4import re
5import datetime
6import json
7import hashlib, binascii
8import random
9#import crypt
10
11from sqlalchemy.engine.url import make_url
12from sqlalchemy import create_engine
13#from sqlalchemy import pool
14from sqlalchemy.pool import QueuePool
15from sqlalchemy import MetaData
16
17# # from sqlalchemy.orm import scoped_session, sessionmaker
18# # from sqlalchemy.ext.declarative import declarative_base
19from sqlalchemy.orm import relationship, joinedload, subqueryload, Session
20
21from psycopg2 import IntegrityError as psIntegrityError
22
23from sqlalchemy.exc import IntegrityError
24
25def SQLconnector(args):
26 global metadata,conn,engine,pool,session
27 engine = create_engine(make_url("postgresql://"+args.login+":"+os.getenv("passwordDB")+"@"+args.host+":"+args.port+"/"+args.databasename),
28 poolclass= QueuePool
29 )
30 #convert_unicode= True,
31 # def status(self):
32 # return "Pool size: %d Connections in pool: %d "\
33 # "Current Overflow: %d Current Checked out "\
34 # "connections: %d" % (self.size(),
35 # self.checkedin(),
36 # self.overflow(),
37 # self.checkedout())
38 metadata = MetaData()
39 metadata.reflect(engine)
40
41 conn=engine.connect()
42
43 # conn = conn.execution_options(
44 # isolation_level="READ COMMITTED"
45 # )
46 # #Valid values for isolation_level include:
47 # READ COMMITTED
48 # READ UNCOMMITTED
49 # REPEATABLE READ
50 # SERIALIZABLE
51 # AUTOCOMMIT
52
53 pool=QueuePool(conn,
54 pool_size= 10,
55 max_overflow= 10)
56 # ,
57 # pool_timeout= 30,
58 # pool_recycle= 1800
59
60 session = Session(engine)
61 # pre-connect so this part isn't profiled (if we choose)
62 session.connection()
63 return(metadata,conn,engine,pool,session)
64
65def insert_table(table_name,values):
66 table_ = metadata.tables[table_name]
67 now = datetime.datetime.now()
68
69 try:
70 if ("creation_date" in table_.c and "creation_date" in values):
71 values["creation_date"]=now
72 myinsert = table_.insert().returning(table_.c.id).values(values)
73 except (KeyError, AttributeError):
74 myinsert = table_.insert().returning(table_.c[values.copy().popitem()[0]]).values(values)
75
76 uniq=True
77 try:
78 connexe=conn.execute(myinsert)
79 id_val=connexe.fetchall()[0][0]
80 connexe.close()
81 print("Add "+table_name+" :",id_val)
82 except (psIntegrityError, IntegrityError):
83 uniq=False
84 print('Warning IntegrityError: in '+table_name+' values '+values["name"]+' already exist.')
85 print('We will use previous value.')
86 id_val=session.query(table_).filter(table_.c.name==values["name"]).one()[0]
87 print("Reuse "+table_name+" :",id_val)
88
89 return id_val,uniq
90
91def print_table(table_name):
92 table_ = metadata.tables[table_name]
93 select_st = table_.select()
94 res = conn.execute(select_st)
95 print(res.keys())
96 for _row in res:
97 print(_row)
98
99def encode_tileset(thistileset):
100 nbtiles=len(thistileset.tiles)
101 tile=thistileset.tiles[int(nbtiles/2)]
102 try:
103 tile.source["name"]
104 hasname=True
105 except:
106 hasname=False
107 try:
108 tile.source["variable"]
109 hasvariable=True
110 except:
111 hasvariable=False
112
113 tiledata=[]
114 try:
115 listiles=sorted((tile for tile in thistileset.tiles),key=lambda x: int(x.source["connection"]));
116 except:
117 listiles=sorted((tile for tile in thistileset.tiles),key=lambda x: int(x.id));
118 for tile in listiles:
119 if(hasname):
120 name=tile.source["name"]
121 else:
122 name=tile.title
123 if (hasvariable):
124 var=tile.source["variable"]
125 else:
126 var=name
127 # If connection ? Test tileset connection ok ??
128 tiledata.append({"title":tile.title,
129 "url":tile.source["url"],
130 "comment":tile.comment,
131 "tags":tile.tags,
132 "name":name,
133 "variable":var,
134 "pos_px_x":tile.pos_px_x,
135 "pos_px_y":tile.pos_px_y,
136 "IdLocation" : tile.IdLocation,
137 "dbid":tile.id
138 })
139 return tiledata
140
141def decode_tileset(thistileset):
142 # If connection ? Test tileset connection ok ??
143 try:
144 tile=thistileset.tiles[0]
145 except:
146 return ""
147
148 try:
149 tile.source["name"]
150 hasname=True
151 except:
152 hasname=False
153 try:
154 tile.source["variable"]
155 hasvariable=True
156 except:
157 hasvariable=False
158
159 try:
160 listiles=sorted((tile for tile in thistileset.tiles),key=lambda x: int(x.source["connection"]));
161 except:
162 listiles=sorted((tile for tile in thistileset.tiles),key=lambda x: int(x.id));
163 if(hasname):
164 if (hasvariable):
165 json_tiles={"nodes":
166 [{"title":tile.title,
167 "url" :tile.source["url"],
168 "comment" :tile.comment,
169 "tags" :tile.tags,
170 "variable" :tile.source["variable"],
171 "pos_px_x" :tile.pos_px_x,
172 "pos_px_y" :tile.pos_px_y,
173 "IdLocation" : tile.IdLocation,
174 "name" :tile.source["name"],
175 "connection" :tile.source["connection"],
176 } for tile in listiles ]}
177 else:
178 json_tiles={"nodes":
179 [{"title":tile.title,
180 "url" :tile.source["url"],
181 "comment" :tile.comment,
182 "tags" :tile.tags,
183 "variable" : "",
184 "pos_px_x" :tile.pos_px_x,
185 "pos_px_y" :tile.pos_px_y,
186 "IdLocation" : tile.IdLocation,
187 "name" :tile.source["name"],
188 "connection" :tile.source["connection"],
189 } for tile in listiles ]}
190 elif (hasvariable):
191 json_tiles={"nodes":
192 [{"title":tile.title,
193 "url" :tile.source["url"],
194 "comment" :tile.comment,
195 "tags" :tile.tags,
196 "variable" :tile.source["variable"],
197 "pos_px_x" :tile.pos_px_x,
198 "pos_px_y" :tile.pos_px_y,
199 "IdLocation" : tile.IdLocation,
200 "name" : "",
201 "connection" :tile.source["connection"],
202 } for tile in listiles ]}
203 else:
204 json_tiles={"nodes":
205 [{"title":tile.title,
206 "url" :tile.source["url"],
207 "comment" :tile.comment,
208 "tags" :tile.tags,
209 "variable" : "",
210 "pos_px_x" :tile.pos_px_x,
211 "pos_px_y" :tile.pos_px_y,
212 "IdLocation" : tile.IdLocation,
213 "name" : "",
214 "connection" :tile.source["connection"],
215 } for tile in listiles ]}
216
217 try:
218 json_tiles_text=json.JSONEncoder().encode(json_tiles)
219 except:
220 json_tiles_text=""
221 return json_tiles_text
222
223def encode_session(sessionNAME,Session):
224 ThisSession=session.query(Session).filter(Session.name == sessionNAME).first()
225 ListAllTileSet_ThisSession=ThisSession.tile_sets
226 JsonSession=json.JSONEncoder().encode(
227 {"info": {"SessionName" : sessionNAME,
228 "ProjectName" : ThisSession.project.name,
229 "Users" : list(set([ThisSession.project.user.name]+
230 [SessionUser.name for SessionUser in ThisSession.users]))},
231 "tilesets": [ {"name":thistileset.name,
232 "Dataset_path":thistileset.Dataset_path,
233 "tiles": [ {"id" : tile.id,
234 "title" : tile.title,
235 "comment": tile.comment,
236 "source": tile.source,
237 "tags": tile.tags
238 } for tile in thistileset.tiles ] }
239 for thistileset in ListAllTileSet_ThisSession ]
240 })
241
242 return JsonSession
243
244def passrandom(nbchar):
245 #ALPHABET = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/@_.-"
246 ALPHABET = "B6P8VbhZoGp9JYd0.uLCsAT4DXF1xqIUSyQMniNgje5_~3crvlHR-7W2f@kEtmazwKO$"
247 mystring=''.join(random.choice(ALPHABET) for i in range(nbchar)).encode('utf-8')
248 #mystring = os.urandom(nbchar)
249 #mystring=crypt.mksalt(crypt.METHOD_SHA512).encode('utf-8')
250 return mystring
251
252def passprotected(password):
253 mysalt=passrandom(20)
254 passhash = hashlib.pbkdf2_hmac('sha512',password.encode('utf-8'),mysalt,100000)
255 hexpass = binascii.hexlify(passhash)
256 return hexpass.decode("utf-8","ignore"),mysalt.decode("utf-8","ignore")
257
258
259def testpassprotected(Users,user,password,savedhash,savedsalt):
260 mysalt=savedsalt.encode('utf-8')
261 passhash = hashlib.pbkdf2_hmac('sha512',password.encode('utf-8'),mysalt,100000)
262 hexpass = binascii.hexlify(passhash)
263 thisUser=session.query(Users).filter_by(name=user).one()
264 readHash=thisUser.password
265 if (hexpass.decode("utf-8","ignore")==readHash):
266 now = datetime.datetime.now()
267 thisUser.dateverified=now
268 session.commit()
269 return True
270 else:
271 print('mysalt= %s\n passhash = %s\n hexpass = %s\n hexpass.decode("utf-8","ignore") = %s\n readHash = %s' % (mysalt, passhash, hexpass, hexpass.decode("utf-8","ignore"), readHash))
272 return False