Coverage for lib/stockage/lib_pg_safia_sys.py: 48%
1534 statements
« prev ^ index » next coverage.py v7.9.1, created at 2026-02-18 02:40 +0100
« prev ^ index » next coverage.py v7.9.1, created at 2026-02-18 02:40 +0100
1import datetime
2import json
3import sys
5try :
6 import psycopg2.extras
7 from psycopg2 import connect, sql
8 from psycopg2.extras import RealDictCursor, RealDictRow, Json as psycopg2Json
9except ImportError as e:
10 print("ImportError : " + str(e))
11 print("Please install psycopg2 and psycopg2-binary")
12# sys.exit(1)
14from lib.lib_util import replace_non_alpha_with_underscore
16class LibPGSafiaSys():
17 def __init__(self, PG_HOST = None, PG_USER = None, PG_PASSWORD = None, PG_DB = None, PG_PORT = None):
18 import logging
19 self.logger = logging.getLogger()
20 try:
21 # super().__init__()
23 if PG_HOST != None:
24 self.client = connect(
25 dbname=PG_DB, user=PG_USER, password=PG_PASSWORD, host=PG_HOST, port=PG_PORT
26 )
27 else :
28 self.client = None
29 self.user_table = "mtruser.user"
30 self.user_payment_status = "mtruser.user_payment_status"
31 self.projects_table = "mtruser.project"
32 self.groups_table = "mtrright.group"
33 self.log_query_table = "mtrlog.log_query"
34 self.datou_table = "mtrdatou.datou"
36 if self.client != None:
37 # - [ ] TODO VR 18-8-23 : je ne comprends pas pourquoi ca plantait pas avant, il faudrait regarder les précédentes versions
38 self.client.autocommit = True # => avec cela les tests passent
40# self.client.set_session(readonly=False, autocommit=True)
41# import psycopg2
42# self.client.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
44 self.get_admin_situation()
45 self.PG_HOST = PG_HOST
46 self.PG_USER = PG_USER
47 self.PG_PASSWORD = PG_PASSWORD
48 self.PG_DB = PG_DB
49 self.PG_PORT = PG_PORT
50 except Exception as e:
51 print(str(e))
52 self.client = None
54 def reconnect(self):
55 self.client = connect(
56 dbname=self.PG_DB, user=self.PG_USER, password=self.PG_PASSWORD, host=self.PG_HOST, port=self.PG_PORT
57 )
59 def __del__(self):
60 print("Calling destructor")
61 self.logger.info("Calling destructor")
62 try :
63 self.get_admin_situation()
64 self.client.commit()
65 self.get_admin_situation()
66 except Exception as e:
67 print(str(e))
68 print("Exception while deleting stuff !")
70# cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
72 def kill_all_lock_transaction(self, list_ids):
73 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
74 for id in list_ids:
75 terminate_lock = "SELECT pg_terminate_backend(%s)"
76 cursor.execute(terminate_lock, (id,))
78 def get_admin_situation(self, exclude_table = ["pg_namespace", "pg_index", "pg_class", "pg_authid", "pg_database"],
79 verbose = False):
80 query_get_lock = """
81SELECT
82 relname,
83 locktype,
84 page,
85 virtualtransaction,
86 pid,
87 mode,
88 granted
89FROM
90 pg_locks l
91LEFT JOIN
92 pg_stat_all_tables t ON l.relation = t.relid
93WHERE
94 relname IS NOT NULL;
95 """
97 if self.client == None:
98 return [], []
100 list_locks = []
101 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
102 cursor.execute(query_get_lock)
103 list_locks = cursor.fetchall()
105 query_get_running = """
106 SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc;
107 """
108 list_running = []
109 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
110 cursor.execute(query_get_running)
111 list_running = cursor.fetchall()
113 list_locks_kept = []
114 for l in list_locks:
115 if l["relname"] not in exclude_table:
116 list_locks_kept.append(l)
118 list_running_kept = []
119 for l in list_running:
120 if l["query"] != "":
121 list_running_kept.append(l)
122 self.logger.info(" list_locks_kept : " + str(len(list_locks_kept)))
123 self.logger.info(" list_running_kept : " + str(len(list_running_kept)))
124 print(str(" list_locks_kept : " + str(len(list_locks_kept))))
125 print(str(" list_running_kept : " + str(len(list_running_kept))))
126 if verbose:
127 self.logger.info(" list_locks_kept : " + str((list_locks_kept)))
128 self.logger.info(" list_running_kept : " + str((list_running_kept)))
129 print(str(" list_locks_kept : " + str((list_locks_kept))))
130 print(str(" list_running_kept : " + str((list_running_kept))))
135 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
136 cursor.execute("SHOW TRANSACTION ISOLATION LEVEL;")
137 status = cursor.fetchall()
138 print(str(status))
140 # select pg_terminate_backend(47675);
141 return list_locks_kept, list_running_kept
143 # TODO VR 1-5-24 : je ne sais pas d'ou cela est-il fait !
144 def create_statement(self, migration):
145 with self.client.cursor() as cursor:
146 cursor.execute(migration)
147 self.client.commit()
149 def select_user(self, id):
150 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
151 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.user_table.split('.')))
152 cursor.execute(query, (id,))
153 one_fetch = cursor.fetchone()
154 return one_fetch
156 def update_user(self, id,
157 first_name = None,
158 last_name = None,
159 phone = None,
160 email = None,
161 misc_info = None):
162 list_set = []
163 list_data = ()
164 if first_name != None:
165 list_set.append(" firstname=%s ")
166 list_data += (first_name,)
167 if last_name != None:
168 list_set.append(" lastname=%s ")
169 list_data += (last_name,)
170 if phone != None:
171 list_set.append(" phone=%s ")
172 list_data += (phone,)
173 if email != None:
174 list_set.append(" mail=%s ")
175 list_data += (email,)
176 if misc_info != None:
177 list_set.append(" miscinfo=%s ")
178 list_data += (json.dumps(misc_info),)
180 if len(list_set) == 0:
181 return 0
183 info_to_update = ', '.join(list_set)
185 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
186 query = sql.SQL("UPDATE {} SET " + info_to_update + " WHERE id=%s RETURNING *").format(sql.Identifier(*self.user_table.split('.')))
187 list_data += (id,)
188 cursor.execute(query, list_data)
189 one_fetch = cursor.fetchone()
190 return one_fetch
192 def get_user_from_mail(self, mail):
193 if mail == None:
194 return None
195 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
196 query = sql.SQL("SELECT * FROM {} WHERE mail=%s").format(sql.Identifier(*self.user_table.split('.')))
197 print(query.as_string(self.client))
198 cursor.execute(query, (mail,))
199 all_fetch = cursor.fetchone()
200 if all_fetch == None:
201 return None
202 return all_fetch
204 def get_user_info_from_id(self, user_id):
205 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
206 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.user_table.split('.')))
207 # print(query)
208 cursor.execute(query, (user_id,))
209 one_fetch = cursor.fetchone()
210 if one_fetch == None:
211 return None
212 return one_fetch
214 def get_user_from_phone(self, phone):
215 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
216 query = sql.SQL("SELECT * FROM {} WHERE phone=%s").format(sql.Identifier(*self.user_table.split('.')))
217 print(query.as_string(self.client))
218 cursor.execute(query, (phone,))
219 all_fetch = cursor.fetchone()
220 if all_fetch == None:
221 return None
222 return all_fetch
224 def topup_money(self, user_id, amount): # pragma no cover payment
225 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
226 insert_query = sql.SQL("""INSERT INTO {} (user_id, amount)
227 VALUES (%s, %s)
228 ON CONFLICT (user_id)
229 DO UPDATE SET
230 amount = mtruser.user_payment_status.amount + EXCLUDED.amount,
231 updated_at = NOW()
232 RETURNING amount""").format(sql.Identifier(*self.user_payment_status.split('.')))
233 val = (user_id, amount)
234 cursor.execute(insert_query, val)
235 self.client.commit()
236 one_fetch = cursor.fetchone()
237 # TODO VR would prefer to do this in lss
238 cursor.close()
239 if one_fetch == None:
240 return 0
241 else :
242 return one_fetch["amount"]
244 def get_current_amount(self, user_id):
245 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
246 query = sql.SQL("SELECT * FROM {} WHERE user_id=%s").format(sql.Identifier(*self.user_payment_status.split('.')))
247 cursor.execute(query, (user_id,))
248 one_fetch = cursor.fetchone()
249 # TODO VR would prefer to do this in lss
250 if one_fetch == None:
251 return 0
252 else :
253 return one_fetch["amount"]
255 def setup_user(self, email = "anonymous@opio.fr", auth_type = "otp_apia"):
256 print(" email : " + str(email))
258 # mtruser I owuld like it in database mtr OR ?
260 with self.client.cursor() as cursor:
261# insert_query = "INSERT INTO \"user\" (mail, auth_type, schema_user_documents) VALUES (%s, %s, %s)"
262 schema_name = replace_non_alpha_with_underscore(email) + "_documents"
263# ret = cursor.execute(insert_query, (email, auth_type, schema_name))
265 complete_insert_query = sql.SQL("""
266 INSERT INTO {} (mail, auth_type, schema_user_documents) VALUES (%s, %s, %s)
267 RETURNING id""").format(sql.Identifier(*self.user_table.split('.')))
268# SELECT currval('user_id_seq');
270 print(complete_insert_query.as_string(self.client))
272 cursor.execute(complete_insert_query, (email, auth_type, schema_name))
273 all_fetch = cursor.fetchone()
274 if len(all_fetch) == 1:
275 ret = all_fetch[0]
276 else :
277 ret = None
278 print("ERROR exiting (or not)")
280 create_schema_document_user = sql.SQL("CREATE SCHEMA IF NOT EXISTS {}").format(sql.Identifier(schema_name))
282 cursor.execute(create_schema_document_user)
283 self.client.commit()
285 print(" ret setup_user : " + str(ret))
287 return ret
289 def get_projects_from_owner(self, user_id = 31, project_id = None): # info@opio.fr could be public project
290 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
291 if project_id != None:
292 query = "SELECT * FROM mtruser.project WHERE owner_id=%s AND id=%s"
293 cursor.execute(query, (user_id, project_id))
294 else:
295 query = "SELECT * FROM mtruser.project WHERE owner_id=%s"
296 cursor.execute(query, (user_id,))
297 all_fetch = cursor.fetchall()
298 return all_fetch
300 def get_project_with_description_and_user(self, user_id, description):
301 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
303 select_query = sql.SQL("SELECT * FROM {} WHERE owner_id=%s AND description=%s").format(sql.Identifier(*self.projects_table.split('.')))
304 cursor.execute(select_query, (user_id, description))
305 res = cursor.fetchone()
306 if res != None :
307 print(" One Project found for this user with the same description : " + str(res))
308 return res
309 else :
310 return None
312 def create_project(self, user_id, schema, description, otp):
314# with self.client.cursor(cursor_factory=DictCursor) as cursor:
315 with self.client.cursor() as cursor:
316 # VR todo on pourrait rajouter les initiales dans le noms des tables
317 table_name = schema + "." + "dataset_" + otp
318 match_page_sections_function_name = "match_" + otp
319 if len(table_name) > 63:
320 print(str(len(table_name)))
321 print("We truncate the table name, and what about the matching function name ? ")
322 table_name = table_name[:63]
324 select_query = sql.SQL("SELECT * FROM {} WHERE table_documents=%s").format(sql.Identifier(*self.projects_table.split('.')))
325 cursor.execute(select_query, (table_name,))
326 res = cursor.fetchone()
327 if res != None:
328 if len(res) == 1:
329 return {"id" : res[0]}
331 insert_query = sql.SQL("INSERT INTO {} (owner_id, description, table_documents, function_search_documents) VALUES (%s, %s, %s, %s) RETURNING ID").format(sql.Identifier(*self.projects_table.split('.')))
333 print(" insert_query : " + str(insert_query))
335 cursor.execute(insert_query, (str(user_id), description, table_name, match_page_sections_function_name))
337# print(" insert_query : " + str(insert_query))
339# cursor.execute(insert_query, (str(user_id), description, table_name, match_page_sections_function_name))
341 all_fetch = cursor.fetchone()
342 #for row in all_fetch:
343 # print(row)
345 print("NEED TO CREATE THE DOCUMENT TABLE NOW !")
346 from lib.stockage.lib_pg_dataset_pg import build_query_create_documents
347 hash40 = "idx_" + otp[:35]
348 docs = build_query_create_documents(table_name, match_page_sections_function_name, hash40)
350 print(docs)
351 cursor.execute(docs)
352 self.client.commit()
353 cursor.close()
355 print(" ret : " + str(all_fetch))
357 if len(all_fetch) == 1:
358 return {"id" : all_fetch[0]}
360# data = [dict(row) for row in data]
361 def get_project_info(self, project_id):
362 if project_id == None or project_id == "None":
363 return {}
364 import logging
365 logger = logging.getLogger()
366 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
367 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.')))
368 logger.info(" query : " + query.as_string(self.client))
369 cursor.execute(query, (project_id,))
370 logger.info(" before fetchall ")
371 info_project = cursor.fetchall()
372 cursor.close()
373 if len(info_project) == 1:
374 return dict(info_project[0])
375 else:
376 print("Unexpected result ")
377 return {}
379 def get_meta_info_project(self, table_documents,
380 group_by = None ) : #"id"): # TODO wip plop
381 import logging
382 logger = logging.getLogger()
383 logger.info(" in get_meta_info_project ")
384# return {"number_documents" : 28}
385 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
386 table_parts = table_documents.split('.')
387 if group_by != None and group_by != None:
388 # Note: group_by is a column name which cannot be parameterized, but it should be validated/sanitized by caller
389 query = sql.SQL("SELECT count(*) FROM (SELECT count(*) AS number_documents, max(created_at) as latest_update FROM {} GROUP BY {}) as temp").format(
390 sql.Identifier(*table_parts),
391 sql.Identifier(group_by)
392 )
393 else:
394 query = sql.SQL("SELECT count(*) AS number_documents, max(created_at) as latest_update FROM {}").format(
395 sql.Identifier(*table_parts)
396 )
397 logger.info(" query : " + query.as_string(self.client))
398 cursor.execute(query)
399 logger.info(" before fetchall ")
400 nb_docs = cursor.fetchall()
401 cursor.close()
402 self.client.commit() # Pour test des nrt 19-6-23 2:30
403 if len(nb_docs) == 1:
404 return dict(nb_docs[0])
405 else:
406 print("Unexpected result ")
407 return {}
409 def truncate(self, table_documents, kill_all = False, verbose = False):
410 only_table = table_documents.split(".")[1] if len(table_documents.split(".")) == 2 else ""
411 locks, rt = self.get_admin_situation(verbose=verbose)
412 self.logger.info(" locks : " + str(locks))
413 locks_with_table = list(filter(lambda x : x["relname"] == only_table, locks))
414 rt_with_table = list(filter(lambda x : table_documents in x["query"], rt))
415 self.logger.info(" locks_with_table : " + str(locks_with_table))
417 # 32779
418 if kill_all:
419 list_ids = list(map(lambda x : x["pid"], rt_with_table))
420 self.kill_all_lock_transaction(list_ids)
421 # TODO VR : would be better to check again but I don't know what's need to be done
422 rt_with_table = []
423 locks_with_table = []
424 elif len(locks_with_table) > 0 or len(rt_with_table) > 0:
425 print("We expect problem and should wait ! ")
426 self.logger.info("We expect problem and should wait")
427 self.logger.info("RETURNING")
428 return "NO_TRUNCATE"
429 import time
430 waittime = 1
431 while len(locks_with_table) > 0 or len(rt_with_table) > 0:
432 time.sleep(waittime)
433 waittime *= 2
434 locks, rt = self.get_admin_situation(verbose=verbose)
435 locks_with_table = list(filter(lambda x : x["relname"] == only_table, locks))
436 rt_with_table = list(filter(lambda x : table_documents in x["query"], rt))
437 print("STILL")
438 self.logger.info("STIHL")
439# time.sleep(10)
440 self.get_admin_situation(verbose=verbose)
441 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
442 table_parts = table_documents.split('.')
443 query = sql.SQL("TRUNCATE {}").format(sql.Identifier(*table_parts))
444 print("About to truncate " + query.as_string(self.client))
445 self.logger.info("Truncate")
446 cursor.execute(query) # got AccessExclusiveLock
447 self.client.commit()
448 cursor.close()
449 self.logger.info("End of truncate !")
450 self.get_admin_situation(verbose=verbose)
451 return "TRUNCATE"
453 def add_cost_to_project(self, project_id : int, add_cost : float) -> dict:
454 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
456 query = sql.SQL("UPDATE {} SET cost = cost + %s WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.')))
457 print(query.as_string(self.client))
458 cursor.execute(query, (add_cost, project_id))
459 cursor.close()
461 return {}
463 def update_project_info(self, project_id, info_json):
464 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
466 list_set = []
467 list_data = ()
468 if "shortname" in info_json:
469 list_set.append(" shortlink=%s ")
470 list_data += (info_json["shortname"],)
471# list_data.append(info_json["shortname"])
473 if "name" in info_json:
474 list_set.append(" description=%s ")
475 list_data += (info_json["name"],)
477 if "configuration" in info_json:
478 if "key" in info_json and info_json["key"] != "" and info_json["key"] != "None":
479# list_set.append(" configuration=%s ")
480# list_data.append(info_json["configuration"])
481 value_to_insert = info_json["configuration"]
482 list_keys = info_json["key"].split("/")
483# if len(list_keys) == 1: # TODO VR 28-7-24 : we should refactor this to jsonb
484# key_to_insert = list_keys[0]
485# value_to_insert = info_json["configuration"]
486# list_set.append("configuration = jsonb_set(configuration, '{" + key_to_insert + "}', to_jsonb('" + value_to_insert + "'), true)")
488 key_as_csv = ",".join(list_keys)
490 print(" value_to_insert type : " + str(type(value_to_insert)))
491 if type(value_to_insert) == str:
492 value_to_insert = "to_jsonb('" + value_to_insert + "'::text)"
493 elif type(value_to_insert) == dict:
494 import json
495 # TODO make plus propre
496# list_data += (json.dumps(value_to_insert),)
497# value_to_insert = "to_jsonb(" + json.dumps(value_to_insert) + "::jsonb, true)"
498# list_data += (json.dumps(value_to_insert),)
499# value_to_insert = "to_jsonb(" + str("%s") + "::jsonb)"
500# value_to_insert = "to_jsonb(" + str("%s") + ")"
501 value_to_insert = "$$" + json.dumps(value_to_insert) + "$$::jsonb"
504 to_insert = "configuration = jsonb_set(configuration, '{" + key_as_csv + "}', " + value_to_insert + ",true)"
505 list_set.append(to_insert)
506 print("We will need to update the configuration afer refactoring to jsonb, now in test 28-7-24")
507 else:
508 list_set.append( "configuration=%s ")
509 import json
510 list_data += (json.dumps(info_json["configuration"]),)
512 if len(list_set) == 0:
513 return "NOTHING DONE"
515 set_clause = " AND ".join(list_set)
516 query = sql.SQL("UPDATE {} SET " + set_clause + " WHERE id=%s").format(sql.Identifier(*self.projects_table.split('.')))
517 list_data += (project_id,)
519 print(query.as_string(self.client))
520 # Or
521 # just
522 # testing
523 # what
524 # will
525 # be
526 # sent
527 # to
528 # the
529 # server
532 if len(list_data) == 1: # Only project_id
533 cursor.execute(query, list_data)
534 return "SAVED"
535 else:
536 print(cursor.mogrify(query, list_data).decode('utf8'))
537 cursor.execute(query, list_data)
538# cursor.insert_many(query, list_data)
539 cursor.close()
540 return "SAVED"
542 def get_project_by_shortname(self, shortlink):
543 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
544 query = "select id FROM mtruser.project where shortlink=%s;"
545 cursor.execute(query, (shortlink, ))
546 res = cursor.fetchall()
547 return res
549 def set_valid_bill(self, bill_id):
550 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
551 query = "UPDATE mtruser.mtr_factures SET validated=true WHERE id=%s"
552 cursor.execute(query, (bill_id,))
553 self.client.commit()
554 return True
556 def get_last_fact_id_month(self):
557 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
558# query = "SELECT id FROM mtruser.mtr_factures WHERE validated=true ORDER BY created_at DESC LIMIT 1;"
559# query = "SELECT max(id) FROM mtruser.mtr_factures;"
560 query = "SELECT id, month FROM mtruser.mtr_factures order by id desc limit 1;"
561 cursor.execute(query)
562 res = cursor.fetchone()
564 res_max_id = res["id"] if "id" in res else None
565 res_month_argmax = res["month"] if "month" in res else None
567 query2 = "select last_value from mtruser_mtr_facture_id_seq;"
569 cursor.execute(query2)
570 res2 = cursor.fetchone()
571 res_last_value = res2["last_value"] if "last_value" in res2 else None
573 return res_max_id, res_last_value, res_month_argmax
575 # TODO VR 20-4-25 : to be deleted since not used and not as good !
576 def reset_facture_info(self, new_max_id):
577 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
578 query = "SELECT setval('mtruser_mtr_facture_id_seq', %s, false)"
579 cursor.execute(query, (new_max_id,))
580 self.client.commit()
582 query = "DELETE FROM mtruser.mtr_factures WHERE id>%s"
583 cursor.execute(query, (new_max_id,))
584 self.client.commit()
586 return True
588 def insert_new_bill(self, bill_id, filename, project_id, month_string_for_data, price_ht, price_ttc, nb_page_total):
589 # TODO VR 20-4-25
590 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
591 query = f"""INSERT INTO mtruser.mtr_factures (filename, project_id, month, price_ht, price_ttc, nb_pages) VALUES
592 ('{filename}', {project_id}, '{month_string_for_data}', '{price_ht}', '{price_ttc}', '{nb_page_total}') RETURNING id;"""
593 cursor.execute(query)
594 self.client.commit()
596 all_fetch = cursor.fetchone()
597 if len(all_fetch) == 1:
598 query_id = all_fetch["id"]
599 else :
600 query_id = None
601 print("ERROR exiting (or not)")
603 if query_id != bill_id:
604 print("Inconsistent, please check !")
606 return query_id
608 def get_facture_from_project(self, project_id = None, is_validated = None):
609 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
610 list_conditions = []
611 list_params = []
612 if project_id != None:
613 list_conditions.append(" project_id=%s ")
614 list_params.append(project_id)
616 if is_validated != None:
617 list_conditions.append(" validated=%s ")
618 list_params.append(is_validated)
619 condition = " WHERE " + " AND ".join(list_conditions) if len(list_conditions) > 0 else ""
620 query = "SELECT * FROM mtruser.mtr_factures " + condition
621 cursor.execute(query, tuple(list_params))
622 res = cursor.fetchall()
623 return res
625 def select_project_id_from_facture(self, path_facture):
626 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
627 query = "SELECT project_id FROM mtruser.mtr_factures WHERE filename=%s"
628 cursor.execute(query, (path_facture,))
629 res = cursor.fetchone()
630 if res == None or "project_id" not in res:
631 return None
632 return res["project_id"]
634 def get_bill_validate(self, date_start):
635 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
636 query = "SET lc_time = 'fr_FR.UTF-8';SELECT validated, count(*), string_agg(distinct month, ',') as months FROM mtruser.mtr_factures WHERE to_date(month, 'TMMonth YYYY') >= %s GROUP BY validated"
637 # query = "SELECT validated, count(*), string_agg(distinct month, ',') as months FROM mtruser.mtr_factures WHERE created_at >= %s GROUP BY validated"
638 cursor.execute(query, (date_start,))
639 res = cursor.fetchall()
640 map_validated_count = {}
641 map_validated_months = {}
642 for r in res:
643 map_validated_count[r["validated"]] = r["count"]
644 map_validated_months[r["validated"]] = r["months"]
645 if True not in map_validated_count:
646 map_validated_count[True] = 0
647 if False not in map_validated_count:
648 map_validated_count[False] = 0
649 if True not in map_validated_months:
650 map_validated_months[True] = ""
651 if False not in map_validated_months:
652 map_validated_months[False] = ""
653 return map_validated_count, map_validated_months
657 def get_min_bill_id_after_or_equal_date(self, date_start):
658 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
659 query = "SELECT min(id) FROM mtruser.mtr_factures WHERE to_date(month, 'TMMonth YYYY') >= %s"
660 cursor.execute(query, (date_start,))
661 res = cursor.fetchone()
662 if res == None or "min" not in res:
663 return None
664 return res["min"]
666 def reset_facture(self, bill_id, nb_bill_expected = 0, verbose = False):
667 # Check should have been done previously
668 # Est-ce suffisnat ?
669# client_for_rollback = self.client
670# client_for_rollback.autocommit = False
671 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
672 query = "WITH deleted AS (DELETE FROM mtruser.mtr_factures WHERE id>=%s AND validated=false RETURNING *) SELECT count(*) FROM deleted"
673 cursor.execute(query, (bill_id,))
674 res = cursor.fetchone()
675 print(" res : " + str(res))
676 if res == None or "count" not in res or res["count"] != nb_bill_expected:
677 print("Didn't manage to make rollback works : " + str(res) + " nb_bill_expected : " + str(nb_bill_expected))
678 raise Exception("Inconsistent data, nothing deleted neither done !")
679# self.client.rollback()
680# return False
681 else:
682 self.client.commit()
684 query = "SELECT setval('mtruser_mtr_facture_id_seq', %s)"
685 res = cursor.execute(query, (bill_id - 1,))
686 print(" res : " + str(res))
687 self.client.commit()
689 return True
691 def insert_query_log(self, project_id, user_id, endpoint, request_method,
692 request_body = "", ip_address = "0.0.0.0",
693 elapsed_time = 0):
694 import logging
695 logger = logging.getLogger()
696 logger.info("inside insert_query_log from lpgss")
697 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
699 complete_insert_query = sql.SQL("""
700 INSERT INTO {} (
701 project_id,
702 user_id,
703 endpoint,
704 request_method,
705 request_body,
706 ip_address,
707 elapsed_time) VALUES (%s, %s, %s, %s, %s, %s, %s)
708 RETURNING id""").format(sql.Identifier(*self.log_query_table.split('.')))
710 print(complete_insert_query.as_string(self.client))
711 logger.info(complete_insert_query.as_string(self.client))
713 cursor.execute(complete_insert_query, (project_id, user_id, endpoint, request_method, request_body, ip_address, elapsed_time))
714 all_fetch = cursor.fetchone()
715 if len(all_fetch) == 1:
716 query_id = all_fetch["id"]
717 else :
718 query_id = None
719 print("ERROR exiting (or not)")
721 self.client.commit()
722 cursor.close()
723 return query_id
725 def update_query_end(self, query_id, response_status = 200, response_body = "",
726 elapsed_time = 0, cost = 0, detailed_time = {},
727 verbose = False):
728 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
730 detailed_time_str = str(detailed_time)[:1023]
731 complete_update_query = sql.SQL("""
732 UPDATE {}
733 SET
734 response_status = %s,
735 response_body = %s,
736 elapsed_time = %s,
737 cost = %s,
738 detailed_time = %s,
739 finished_at=NOW()
740 WHERE id=%s""").format(sql.Identifier(*self.log_query_table.split('.')))
742 if verbose:
743 print(complete_update_query.as_string(self.client))
745 try :
746 cursor.execute(complete_update_query, (response_status, str(response_body), elapsed_time, cost, detailed_time_str, query_id))
747 self.client.commit()
748 except Exception as e:
749 print("Error in update_query_end: " + str(e))
750 self.client.rollback()
751 finally:
752 cursor.close()
754 def get_queries(self, project_id):
755 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
756 select_query = sql.SQL("SELECT * FROM {} WHERE project_id=%s ORDER BY id DESC LIMIT 100").format(sql.Identifier(*self.log_query_table.split('.')))
757 cursor.execute(select_query, (project_id,))
758 all_fetch = cursor.fetchall()
759 return all_fetch
761 def check_table_exists(self, schema_and_table_name):
762 table_name = schema_and_table_name
763 table_schema = ""
764 if schema_and_table_name == None:
765 return False
766 if "." in schema_and_table_name:
767 table_name = schema_and_table_name.split(".")[1] if len(schema_and_table_name.split(".")) == 2 else ""
768 table_schema = schema_and_table_name.split(".")[0] if len(schema_and_table_name.split(".")) == 2 else ""
769 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
770 query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = %s and table_schema = %s);"
771 cursor.execute(query, (table_name, table_schema,))
772 exists = cursor.fetchone()["exists"]
773 return exists
775 def get_documents(self, table_name, document_id = None, chunk_id = None,
776 limit = 100, offset = 0,
777 order_by_recent = False):
778 if table_name == None:
779 return []
780 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
781 table_parts = table_name.split('.')
782 params = []
784 if chunk_id != None:
785 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*table_parts))
786 params.append(str(document_id) + "_" + str(chunk_id))
787 elif document_id != None:
788 query = sql.SQL("SELECT * FROM {} WHERE document_id=%s").format(sql.Identifier(*table_parts))
789 params.append(document_id)
790 else:
791 query = sql.SQL("SELECT * FROM {}").format(sql.Identifier(*table_parts))
793 if order_by_recent:
794 query = sql.SQL(query.as_string(self.client) + " ORDER BY created_at DESC")
795 if limit != None and limit != 0:
796 query = sql.SQL(query.as_string(self.client) + " LIMIT %s OFFSET %s")
797 params.extend([limit, offset])
799 print(query.as_string(self.client))
800 cursor.execute(query, tuple(params) if params else None)
801 list_res = cursor.fetchall()
802 from lib.lib_util import get_id_order_document
803 try :
804 list_res_ordered = list(sorted(list_res, key=lambda x : get_id_order_document(x["id"])))
805 except Exception as e:
806 print(str(e))
807 print("ERROR treated as warning we don't re-order the list")
808 list_res_ordered = list_res
809 return list_res_ordered
811 def get_descs(self, table_name, document_id = None, limit = 0, offset = 0):
812# if limit > 0:
813# print(" Limit not yet managed in get_descs ")
814# return []
816 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
817 table_parts = table_name.split('.')
818 params = []
820 if document_id != None:
821 query = sql.SQL("SELECT * FROM {} WHERE id=%s").format(sql.Identifier(*table_parts))
822 params.append(document_id)
823 else:
824 query = sql.SQL("SELECT * FROM {}").format(sql.Identifier(*table_parts))
826 if limit > 0:
827 query = sql.SQL(query.as_string(self.client) + " LIMIT %s OFFSET %s")
828 params.extend([limit, offset])
830 print(" query desc : " + query.as_string(self.client))
831 cursor.execute(query, tuple(params) if params else None)
832 all_result = cursor.fetchall()
833 return all_result
835# TO TEST
836 # TODO VR avoid sql injection ! => done je crois 21-6-23
837 def update_document(self, table_name, document_id, new_values):
838 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
839 tuple_join = ()
840 list_clause = []
841 for col, val in new_values.items():
842 tuple_join += (str(val),)
843 if col == "source": # to avoid sql injection (pg do the rest)
844 list_clause.append("source = %s")
845 if col == "source_id":
846 list_clause.append("source_id = %s")
847 if col == "content":
848 list_clause.append("content = %s")
849 if col == "document_id":
850 list_clause.append("document_id = %s")
851 if col == "author":
852 list_clause.append("author = %s")
853 if col == "url":
854 list_clause.append("url = %s")
856 set_clause = ', '.join(list_clause)
857 query = f"UPDATE {table_name} SET {set_clause} WHERE id = '{document_id}'"
858 self.logger.info(query)
859 self.logger.info(tuple_join)
860 cursor.execute(query, tuple_join)
861 self.client.commit()
863 def delete_document(self, table_name, document_id):
864 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
865 cursor.execute(f"DELETE FROM {table_name} WHERE document_id = '{document_id}'")
866 self.client.commit()
868 # - [ ] TODO VR : rename_document est tout pourri en fait car on ne modifie pas les id, en changeant leur nom
869 def rename_document(self, table_name, document_id, rename):
871 list_chunks = self.get_documents(table_name, document_id=document_id, chunk_id=None)
873 list_old_new_id = []
874 for l in list_chunks:
875 old_id = l["id"]
876 new_id = l["id"].replace(document_id, rename)
877 list_old_new_id.append({"old_id":old_id, "new_id":new_id})
879 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
880 for ch in list_old_new_id:
881 new_id = ch["new_id"]
882 old_id = ch["old_id"]
883 cursor.execute(f"UPDATE {table_name} SET id='{new_id}' WHERE id = '{old_id}'")
884 self.client.commit()
886 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
887 cursor.execute(f"UPDATE {table_name} SET document_id='{rename}' WHERE document_id = '{document_id}'")
888 self.client.commit()
890 def get_configuration_option(self):
891 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
892 select_query = """
893 SELECT layer_type, string_agg(configuration, ',') as options, string_agg(class_name, ',') as list_class_name, string_agg(module_name, ',') as list_module_name
894 FROM mtradmin.brick_layer_custom GROUP BY layer_type;
895 """
896 cursor.execute(select_query)
897 all_fetch = cursor.fetchall()
898 self.client.commit()
899 return all_fetch
901 def get_configuration_option_one(self, configuration):
902 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
903 select_query = f"""
904 SELECT *
905 FROM mtradmin.brick_layer_custom WHERE configuration = '{configuration}';
906 """
907 cursor.execute(select_query)
908 all_fetch = cursor.fetchall()
909 self.client.commit()
910 return all_fetch
912 def build_insert_query_configuration_aux(self, list_data):
913 base_query = """
914 INSERT INTO mtradmin.configuration (layer_type, configuration, user_id, project_id)
915 VALUES %s
916 ON CONFLICT (layer_type, user_id, project_id)
917 DO UPDATE SET
918 configuration = EXCLUDED.configuration,
919 modified_at = NOW();
920 """
921 formatted_data = ', '.join(['(%s, %s, %s, %s)' % tuple(map(lambda x: "'%s'" % x if isinstance(x, str) else x, data)) for data in list_data])
922 final_query = base_query % formatted_data
923 return final_query
925 def update_configuration(self, list_data):
926 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
927 insert_query = self.build_insert_query_configuration_aux(list_data)
928 print(" insert_query : " + str(insert_query))
929 cursor.execute(insert_query)
930 self.client.commit()
932 def load_configuration(self, user_id, project_id):
933 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
934 list_conditions = []
935 params = []
936 if str(user_id).isdigit():
937 list_conditions.append(" user_id=%s ")
938 params.append(user_id)
939 if str(project_id).isdigit():
940 list_conditions.append(" project_id=%s ")
941 params.append(project_id)
942 string_condition = " WHERE" + " AND ".join(list_conditions) if len(list_conditions) > 0 else ""
943 select_configuration = "SELECT * FROM mtradmin.configuration " + string_condition
944 print(" select_configuration : " + str(select_configuration))
945 cursor.execute(select_configuration, tuple(params) if params else None)
946 all_result = cursor.fetchall()
947 return all_result
949 def load_conf_project(self, project_id,
950 key = None):
951 current_conf_json = {}
952 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
953# query = "SELECT configuration FROM mtruser.project where project_id=%s;"
954# cursor.execute(query, (project_id))
955 if key != None and key != "":
956 list_keys = key.split("/")
957 # Note: key is used for column path extraction, should be validated by caller
958 key_with_arrow = "->" + "->".join(list(map(lambda x : "'" + x + "'", list_keys))) + " as " + key.replace("/", "_")
959 else :
960 key_with_arrow = ""
961 if project_id != 'all':
962 print("TODO REFACTO remove load_sub_json in external function and voila")
963 key_with_arrow = ""
965 if project_id == 'all':
966 query = "SELECT id as project_id, configuration" + key_with_arrow + " FROM mtruser.project"
967 cursor.execute(query)
968 current_conf = cursor.fetchall()
969# if res != None and "configuration" in res and res["configuration"] != None:
970# current_conf = res["configuration"]
971 #current_conf = {"all" : current_conf}
972 else :
973 query = "SELECT id as project_id, configuration" + key_with_arrow + " FROM mtruser.project WHERE id=%s"
974 cursor.execute(query, (project_id,))
975 res = cursor.fetchone()
976 if res != None and "configuration" in res and res["configuration"] != None:
977 current_conf = res["configuration"]
978 else:
979 current_conf = "{}"
981 print(" current_conf_json : " + str(current_conf)[:100])
982 if type(current_conf) == type({}) or project_id == 'all':
983 current_conf_json = current_conf
984 else :
985 print("DEPRECATED 10-10 to delete 11-11")
986 try :
987 current_conf_json = json.loads(current_conf)
988 except Exception as e:
989 print(str(e))
990 print("load_conf_project failed set to {} ")
991 current_conf_json = {}
992 return current_conf_json
994 def update_configuration_project(self, project_id, data_json, replace = False):
996# import json
997# current_conf_as_json = json.loads(current_conf)
999# current_conf.update(data_json)
1000 # VR 18-11-23 : je prefere supprimer les anciennes configuration par exemple des datous qui ont été supprimé => test à mettre à jour !
1001 # D'apères les tests il y a un truc ou on a pas sauver ce qu'il fallait, dans certains cas configuration est egale a 'data': '{"store_in_project": "true", "project_id": "1"}'}
1002 # Je ne sqis pas lequel est bien utilisé
1003 if replace:
1004 current_conf = data_json
1005 else :
1006 current_conf = dict(self.load_conf_project(project_id))
1007 if current_conf == None:
1008 current_conf = {}
1009 # fix un bug mais fais clairement planter les tests !
1010# current_conf.update(data_json)
1012 from pydantic.utils import deep_update
1014 current_conf = deep_update(current_conf, data_json)
1015 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1016 query = "UPDATE mtruser.project SET configuration=%s where id=%s;"
1017 import json
1018 cursor.execute(query, (json.dumps(current_conf), project_id))
1021 def get_datou_step_template(self):
1022 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1023 select_query = """
1024 SELECT *
1025 FROM mtrdatou.datou_step_template;
1026 """
1027 cursor.execute(select_query)
1028 all_fetch = cursor.fetchall()
1029 return all_fetch
1031 def get_datou_user(self, user_id, list_datou_ids = []):
1032 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1033 params = []
1034 if user_id != None:
1035 select_query = sql.SQL("SELECT * FROM {} WHERE (public OR owner_id = %s)").format(sql.Identifier(*self.datou_table.split('.')))
1036 params.append(user_id)
1037 else:
1038 select_query = sql.SQL("SELECT * FROM {} WHERE public").format(sql.Identifier(*self.datou_table.split('.')))
1040 if len(list_datou_ids) > 0:
1041 placeholders = ','.join(['%s'] * len(list_datou_ids))
1042 select_query = sql.SQL(select_query.as_string(self.client) + " AND id IN (" + placeholders + ")")
1043 params.extend(list_datou_ids)
1045 cursor.execute(select_query, tuple(params) if params else None)
1046 all_fetch = cursor.fetchall()
1047 return all_fetch
1049 def get_datou_step_user(self, user_id, list_datou_ids = [], verbose = False):
1050 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1051 params = []
1052 if user_id != None:
1053 condition_right = " md.public OR md.owner_id = %s "
1054 params.append(user_id)
1055 else:
1056 condition_right = " md.public "
1058 select_query = sql.SQL("""
1059 SELECT mds.*, mddst.name
1060 FROM mtrdatou.datou_step mds, {} md,
1061 mtrdatou.datou_step_template mddst
1062 WHERE mddst.id=mds.datou_step_type_id
1063 AND mds.mtr_datou_id=md.id
1064 AND (""" + condition_right + """)""").format(sql.Identifier(*self.datou_table.split('.')))
1066 if len(list_datou_ids) > 0:
1067 placeholders = ','.join(['%s'] * len(list_datou_ids))
1068 select_query = sql.SQL(select_query.as_string(self.client) + " AND md.id IN (" + placeholders + ")")
1069 params.extend(list_datou_ids)
1071 if verbose:
1072 print(select_query.as_string(self.client))
1073 cursor.execute(select_query, tuple(params) if params else None)
1074 all_fetch = cursor.fetchall()
1075 return all_fetch
1077 # TODO optimize with loop inside cursor
1078 def update_datou_param_json(self, update_datou_json): # pragma no cover TO REMOVE 17-7-23, don't want to complexify interface jquery to "spare" ids !
1079 for d in update_datou_json:
1080 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1081 update_query = """
1082 UPDATE mtrdatou.datou_step set param_json=%s
1083 WHERE id='%s'
1084 """
1085 import json
1086 tuple = (json.dumps(d["param_json"]), d["id"])
1087 print(update_query)
1088 cursor.execute(update_query, tuple)
1089 self.client.commit()
1090 cursor.close()
1092 def update_datou(self, user_id, datou_name, datou_id_to_update, datou_is_public = None):
1093 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1094 list_id_col_name = []
1095 list_id_col_val = []
1096 list_update_command = []
1097 tuple_value = ()
1098 suffix_ret_id_or_not = " RETURNING ID;"
1099 if datou_id_to_update != None:
1100 list_id_col_name.append("id")
1101 list_id_col_val.append("%s")
1102 tuple_value += (datou_id_to_update,)
1103# suffix_ret_id_or_not = ";" # Commenting simplify the code at the end
1105 list_id_col_name.append("filename")
1106 list_id_col_val.append("%s")
1107 tuple_value += (datou_name,)
1108 list_update_command.append(" filename = EXCLUDED.filename ")
1110 list_id_col_name.append("owner_id")
1111 list_id_col_val.append("%s")
1112 tuple_value += (user_id,)
1114 if datou_is_public != None:
1115 list_id_col_name.append("public")
1116 list_id_col_val.append("%s")
1117 tuple_value += (datou_is_public,)
1118 list_update_command.append(" public = EXCLUDED.public ")
1120 id_col_name = ", ".join(list_id_col_name)
1121 id_col_val = ", ".join(list_id_col_val)
1122 set_update = " SET " + ", ".join(list_update_command) if len(list_update_command) > 0 else ""
1124 insert_query = sql.SQL("""INSERT INTO {} ( """ + (id_col_name) + """ )
1125 VALUES ( """ + (id_col_val) + """ )
1126 ON CONFLICT (id) DO UPDATE
1127-- ON CONFLICT ON CONSTRAINT unique_userid_filename DO UPDATE
1128 """ + set_update + suffix_ret_id_or_not).format(sql.Identifier(*self.datou_table.split('.')))
1129 print(insert_query.as_string(self.client))
1131 cursor.execute(insert_query, tuple_value)
1132 one_result = cursor.fetchone()
1133 datou_id_to_return = one_result["id"]
1134 cursor.close()
1135 self.client.commit()
1137 return datou_id_to_return
1140 # TODO VR : comment faut-il mieux gérer les step_type_id, depuis l'interface ou les rechercher ?
1141 # TODO VR : doit-on dupliquer cet info du coup
1143# id SERIAL PRIMARY KEY,
1144# mtr_datou_id INTEGER,
1145# datou_step_type_id INTEGER,
1146# param_json JSON,
1147# created_at TIMESTAMP,
1148# modified_at TIMESTAMP,
1149# order_step INTEGER NOT NULL,
1151 def update_datou_step(self, list_steps):
1152 if len(list_steps) == 0:
1153 return []
1154 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1155 # TODO VR for empty datou now ?
1156 # TODO VR uses datou_step_id when exists (need to make two type of query)
1157 # TODO nettoyer
1158 delete_query = "DELETE FROM mtrdatou.datou_step WHERE mtr_datou_id=%s"
1159 cursor.execute(delete_query, (list_steps[0]["datou_id"],))
1161# (id_col_name, id_col_val) = ("id, ", "%s, ") if datou_id_to_update is not None else ("", "")
1162 insert_query = """INSERT INTO mtrdatou.datou_step (mtr_datou_id, datou_step_type_id, param_json, order_step)
1163 VALUES (%s, %s, %s, %s)
1164 RETURNING ID;
1165 """
1166 list_data = []
1167 for l in list_steps:
1168 import json
1169 # json.dumps( ??
1170 param_json_str = json.dumps(l["param_json"]) if type(l["param_json"]) == type({}) else l["param_json"]
1171 order_step = l["order"] if "order" in l else l["order_step"] # TODO VR backward compatibility while chaning filename to name
1172 tuple_value = (l["datou_id"], int(l["datou_step_type_id"]), param_json_str, int(order_step))
1174 list_data.append(tuple_value)
1176 print(insert_query)
1177 cursor.executemany(insert_query, list_data)
1178 try :
1179 all_results = cursor.fetchall()
1180 self.client.commit() # au cas ou le comportement est que le curseur est détruit au commit (mais rend le commit inutile du coup)
1181 except Exception as e:
1182 print(str(e))
1183 print("MAis ce n'est sans doute pas bloquant vu que je supprime tout")
1184 all_results = []
1185 cursor.close()
1187 return all_results
1189 # TODO VR : NEED REFACTO ON DELETE CASCADE
1190 def delete_datou(self, mtr_datou_id, user_id):
1191 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1192 delete_query = sql.SQL("DELETE FROM {} WHERE id=%s AND owner_id=%s").format(sql.Identifier(*self.datou_table.split('.')))
1193 print(delete_query.as_string(self.client))
1194 cursor.execute(delete_query, (mtr_datou_id, user_id))
1195 cursor.close()
1196 self.client.commit()
1197# return ""
1199 def create_group(self, user_id, name = "self_group"):
1200 query = "INSERT INTO mtrright.group (owner_id, name) VALUES (%s, %s) ON CONFLICT (owner_id, name) DO NOTHING RETURNING id;"
1201 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1202 cursor.execute(query, (user_id, name))
1203 self.client.commit()
1204 one_result = cursor.fetchone()
1205 return one_result
1207 # TODO VR 13-7 : est-ce bien utile d'avoir ces guillemets là ?
1208 def is_owner(self, table_name_object, user_id, project_id):
1209 table_parts = table_name_object.split('.')
1210 query = sql.SQL("SELECT id FROM {} WHERE owner_id=%s AND id=%s").format(sql.Identifier(*table_parts))
1211 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1212 cursor.execute(query, (user_id, project_id))
1213 one_result = cursor.fetchone()
1214 if one_result == None:
1215 return False
1216 else :
1217 return True
1219 def get_role_on_object(self, table_name_group_object, user_id, object_id = None):
1220 table_parts = table_name_group_object.split('.')
1221 params = [user_id]
1223 if object_id != None:
1224 query = sql.SQL("""SELECT mtgr.role, gu.role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail
1225 FROM {} mtgr,
1226 mtrright.group_user gu,
1227 mtrright.group g,
1228 mtruser.user mtu
1229 WHERE mtu.id=g.owner_id
1230 AND g.id=gu.group_id
1231 AND gu.user_id=%s
1232 AND g.id=mtgr.group_id
1233 AND mtgr.object_id=%s""").format(sql.Identifier(*table_parts))
1234 params.append(object_id)
1235 else:
1236 query = sql.SQL("""SELECT mtgr.role, gu.role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail
1237 FROM {} mtgr,
1238 mtrright.group_user gu,
1239 mtrright.group g,
1240 mtruser.user mtu
1241 WHERE mtu.id=g.owner_id
1242 AND g.id=gu.group_id
1243 AND gu.user_id=%s
1244 AND g.id=mtgr.group_id""").format(sql.Identifier(*table_parts))
1246 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1247 cursor.execute(query, tuple(params))
1248 all_fetch = cursor.fetchall()
1249 return all_fetch
1251 # - [ ] TODO VR : peut-on merger cette fonction avec get_role_on_object (ou le object_id est optionnel)
1252 # - [ ] TODO VR : le user_role ne devrait sans doute pas exister (sauf pour administrer les groupes grrrr !)
1253 # - [ ] TODO VR : ne devrait-on pas récupérer ici les email ? Bon on va voir si ce sont les memes
1254 # - [ ] TODO VR : on peut vouloir faire un distinct user_id, mais en fait cela pose la question de savoir comment faire les changements de droits ! :-) => il y a plein de cas !
1255 def get_all_role_on_object(self, table_name_group_object, object_id):
1256 table_parts = table_name_group_object.split('.')
1257 query = sql.SQL("""SELECT mtgr.role as group_role, gu.role as user_role, gu.user_id, mtgr.object_id, g.name, g.id, g.owner_id, mtu.mail
1258 FROM {} mtgr,
1259 mtrright.group_user gu,
1260 mtrright.group g,
1261 mtruser.user mtu
1262 WHERE mtu.id=gu.user_id
1263 AND g.id=gu.group_id
1264 AND g.id=mtgr.group_id
1265 AND mtgr.object_id=%s""").format(sql.Identifier(*table_parts))
1266 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1267 cursor.execute(query, (object_id,))
1268 all_fetch = cursor.fetchall()
1269 return all_fetch
1271 def get_all_member_groups(self, list_group):
1272 placeholders = ','.join(['%s'] * len(list_group))
1273 query = """SELECT gu.role as user_role, gu.user_id, g.name, g.id as group_id, g.owner_id, mtu.mail, mtu.id
1274 FROM mtrright.group_user gu,
1275 mtrright.group g,
1276 mtruser.user mtu
1277 WHERE mtu.id=gu.user_id
1278 AND g.id=gu.group_id
1279 AND g.id IN (""" + placeholders + """)"""
1280 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1281 cursor.execute(query, tuple(list_group))
1282 all_fetch = cursor.fetchall()
1283 return all_fetch
1285 def get_email_from_user_ids(self, list_user_ids):
1286 if len(list_user_ids) == 0:
1287 return []
1289 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1290 placeholders = ','.join(['%s'] * len(list_user_ids))
1291 query = sql.SQL("SELECT id, mail FROM {} WHERE id IN (" + placeholders + ")").format(sql.Identifier(*self.user_table.split('.')))
1292 cursor.execute(query, tuple(list_user_ids))
1293 all_fetch = cursor.fetchall()
1294 return all_fetch
1296 def get_all_users(self):
1297 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1298 query = sql.SQL("SELECT * FROM {} ORDER BY modified_at DESC").format(sql.Identifier(*self.user_table.split('.')))
1299 cursor.execute(query)
1300 all_fetch = cursor.fetchall()
1301 return all_fetch
1303 def get_group_by_owner(self, user_id, name=None):
1304 params = [user_id]
1305 if name != None:
1306 query = "SELECT * FROM mtrright.group WHERE owner_id=%s AND name=%s"
1307 params.append(name)
1308 else:
1309 query = "SELECT * FROM mtrright.group WHERE owner_id=%s"
1311 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1312 cursor.execute(query, tuple(params))
1313 all_fetch = cursor.fetchall()
1314 return all_fetch
1316 def create_group(self, user_id, name):
1317 query = "INSERT INTO mtrright.group (owner_id, name) VALUES (%s, %s) RETURNING ID;"
1319 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1320 cursor.execute(query, (user_id, name))
1321 one_fetch = cursor.fetchone()
1322 return one_fetch
1324 def get_projects(self, list_project_ids):
1325 print(" TODO project or maybe duplicate => YES INDEED ! but could be refacto ! ")
1326 return []
1328 def create_invitation(self, user_id, mail, action, param_json, link, token):
1329 # - [x] TODO add valid_until => one day !
1330 # - [x] TODO used_at => using updated_at
1331 # - [x] TODO add token
1332 query = """INSERT INTO mtrright.invitation (owner_id, mail, action, param_json, link, token)
1333 VALUES (%s, %s, %s, %s, %s, %s) RETURNING id;"""
1334 import json
1335 one_val = (user_id, mail, action, json.dumps(param_json), link, token)
1336 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1337 cursor.execute(query, one_val)
1338 self.client.commit()
1339 all_fetch = cursor.fetchone()
1340 return all_fetch
1342 def get_invitation_info_from_token(self, token):
1343 query = "SELECT * FROM mtrright.invitation WHERE token=%s"
1344 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1345 cursor.execute(query, (token,))
1346 all_fetch = cursor.fetchone()
1347 return all_fetch
1350#
1351# log_send_mail
1352# send_at
1353# valid_until
1355 pass
1357 def add_role_project_to_user(self, group_id, project_id, role):
1358 query = """INSERT INTO mtrright.group_project (group_id, object_id, role)
1359 VALUES (%s, %s, %s) on CONFLICT(group_id, object_id, role) DO NOTHING;"""
1360 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1361 cursor.execute(query, (group_id, project_id, role))
1362 self.client.commit()
1364 def remove_role_to_user(self, group_id, project_id, role = None):
1365 params = [group_id, project_id]
1366 if role != None:
1367 query = """DELETE FROM mtrright.group_project WHERE group_id=%s AND object_id=%s AND role=%s"""
1368 params.append(role)
1369 else:
1370 query = """DELETE FROM mtrright.group_project WHERE group_id=%s AND object_id=%s"""
1371 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1372 cursor.execute(query, tuple(params))
1373 self.client.commit()
1375 def add_role_to_user_group_buggue(self):#, group_id, user_id, role):
1376 query = """INSERT INTO mtrright.group_user (group_id, user_id, role)
1377 VALUES (49, 9, 'admin') on duplicate(group_id, user_id, role) DO NOTHING;"""
1378 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1379 cursor.execute(query) #, (group_id, user_id, role))
1380 self.client.commit()
1382 def add_role_to_user_group(self, group_id, user_id, role):
1383 query = """INSERT INTO mtrright.group_user (group_id, user_id, role)
1384 VALUES (%s, %s, %s) on CONFLICT(group_id, user_id, role) DO NOTHING;"""
1385 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1386 cursor.execute(query, (group_id, user_id, role))
1387 self.client.commit()
1389 def remove_role_group_to_user(self, group_id, user_id):
1390 select = "SELECT * FROM mtrright.group_user WHERE group_id=%s AND user_id=%s"
1391 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1392 cursor.execute(select, (group_id, user_id))
1393 all_fetch = cursor.fetchall()
1394 if len(all_fetch) == 0:
1395 print("Internal error we could not find the group or user")
1396 else :
1397 query = """DELETE FROM mtrright.group_user WHERE group_id=%s AND user_id=%s"""
1398 cursor.execute(query, (group_id, user_id))
1399 self.client.commit()
1401 def update_invitation_used(self, id, user_id):
1402 query = "UPDATE mtrright.invitation SET user_id=%s, used_at=NOW() WHERE id=%s"
1403 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1404 cursor.execute(query, (user_id, id))
1405 self.client.commit()
1407# TODO log_activation
1408 pass
1410# import psycopg2
1412 # needs to be replaced by correctly configured psycopg2.extras.Json
1413 # https://www.psycopg.org/docs/extras.html#json-adaptation
1414 # https://www.psycopg.org/docs/extras.html#jsonb-adaptation
1415 # needs to be replaced by correclty configured upsert_audit_info
1416 une_var = [
1417{"variable": "project_id", "type": "text"},
1418{"variable": "audit_info", "type": "json"},
1419{"variable": "mtr_datou_id", "type": "text"},
1420{"variable": "mtr_user_id", "type": "text"},
1421{"variable": "launched_at", "type": "text"}
1422]
1423 def save_audit_info(self, project_id,
1424 safia_doc_id,
1425 audit_info,
1426 hash_id_treatment,
1427 mtr_datou_id,
1428 mtr_user_id,
1429 launched_at):
1430 if mtr_datou_id < 0:
1431 return None
1432 # JE veux insérer ces données dans la table mtraudit.mtr_datou_exec
1433 insert_sql = """
1434 INSERT INTO mtraudit.mtr_datou_exec (
1435 mtr_datou_id,
1436 launched_at,
1437 user_id,
1438 project_id,
1439 safia_doc_id,
1440 hash_id_treatment,
1441 audit_info
1442 ) VALUES (%s, %s, %s, %s, %s, %s, %s)
1443 """
1444 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1445 res = cursor.execute(insert_sql, (
1446 mtr_datou_id,
1447 launched_at,
1448 mtr_user_id,
1449 project_id,
1450 safia_doc_id,
1451 hash_id_treatment,
1452 json.dumps(audit_info, default=str)
1453# psycopg2Json(audit_info) c'est assez iunteressant pour gérer tout cela !
1454 ))
1455 self.client.commit()
1456 return res
1458 def upsert_audit_info(self, hash_id_treatment,
1459 list_json_update = [
1460{"variable": "project_id", "type": "text"},
1461{"variable": "safia_doc_id", "type": "text"},
1462{"variable": "audit_info", "type": "json"},
1463{"variable": "mtr_datou_id", "type": "text"},
1464{"variable": "user_id", "type": "text"},
1465{"variable": "launched_at", "type": "text"},
1466{"variable": "id_file", "type": "text"}
1467],
1468 input_values = {},
1469 verbose = False):
1470 list_variables = ["hash_id_treatment"]
1471 list_values = ["%s"]
1472 list_updates = []
1473 tuple_values = (hash_id_treatment,)
1475 for one_value in list_json_update:
1476 variable = one_value["variable"]
1477 if variable not in input_values or input_values[variable] == None:
1478 sys.stdout.write("ç")
1479 continue
1481 value = input_values[variable]
1482 type_variable = one_value["type"] if "type" in one_value else "text"
1484 list_variables.append(variable)
1485 list_values.append("%s")
1486 if type_variable == "json":
1487 try :
1488 new_val = json.dumps(value, default=str)
1489 except Exception as e:
1490 print(str(e))
1491 print("ERROR in upsert_audit_info, will try to save it anyway : hash_id_treatment " + str(hash_id_treatment))
1492 from lib.lib_util import remove_circular_refs
1493 value = remove_circular_refs(value)
1494 new_val = json.dumps(value, default=str)
1495# new_val = str(value)
1496 try :
1497 tuple_values += (new_val,)
1498 except Exception as e:
1499 print(str(e))
1500 print("ERROR in upsert_audit_info cat tuple")
1501 tuple_values += ("",)
1502# tuple_values += (psycopg2Json)
1503# tuple_values += (psycopg2.extras.Json(value),)
1504 elif type_variable == "text":
1505 tuple_values += (value,)
1506 else:
1507 print("Unsupported type : " + str(type_variable))
1508 continue
1509 list_updates.append(f"{variable} = EXCLUDED.{variable}")
1511 if len(list_variables) == 0:
1512 return None
1513 command_sql = f"""
1514 INSERT INTO mtraudit.mtr_datou_exec ({', '.join(list_variables)})
1515 VALUES ({', '.join(list_values)})
1516 """
1517 if len(list_updates) > 0:
1518 command_sql += f"""
1519 ON CONFLICT(hash_id_treatment) DO UPDATE SET
1520 {', '.join(list_updates)};
1521 """
1522 else:
1523 command_sql += " ON CONFLICT(hash_id_treatment) DO NOTHING;"
1524 if verbose:
1525 print(command_sql)
1526 print(" length data to insert : " + str(len(str(tuple_values))))
1527 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1528 cursor.execute(command_sql, tuple_values)
1529 self.client.commit()
1531 # just for test, needs to be deprecated soon ! 8-5-24
1532 # deprecated on 26-9-24
1533 def update_audit_info(self, hash_id_treatment, id_file):
1534 update_sql = """
1535 INSERT INTO mtraudit.mtr_datou_exec (hash_id_treatment, id_file)
1536 VALUES(%s, %s)
1537 ON CONFLICT(hash_id_treatment) DO UPDATE SET id_file=EXCLUDED.id_file;
1538 """
1539 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1540 cursor.execute(update_sql, (hash_id_treatment, id_file))
1541 self.client.commit()
1543 def load_data_audit(self, hash_id_treatment = None,
1544 user_id = None,
1545 col_csv = "id,id_file,mtr_datou_id,created_at,launched_at,modified_at,deleted_at,valid_until,user_id,project_id,safia_doc_id,hash_id_treatment,cdn,input_data,nb_page,nb_modif,input_file,audit_resume,info_anon,info_customer,info_lab,info_cdn,info_back,info_date,info_suivi,info_action,info_usage_integration,info_ml,info_init,info_extract,info_consolidate,info_result,info_exec,info_page,info_stat",
1546 output_type="dict",
1547 limit = None,
1548 offset = 0,
1549 condition = []): # or pd
1550 list_conditions = []
1551 params = []
1552 if hash_id_treatment != None:
1553 list_conditions.append(" hash_id_treatment = %s")
1554 params.append(hash_id_treatment)
1555 col_csv = "*"
1556 else:
1557 if col_csv == None:
1558 col_csv = "id,mtr_datou_id,created_at,launched_at,modified_at,deleted_at,valid_until,user_id,project_id,safia_doc_id,hash_id_treatment,cdn"
1560 for one_cond in condition:
1561 type_condition = one_cond["type"] if "type" in one_cond else "EQUAL"
1562 variable = one_cond["variable"]
1563 # Note: variable is a column name - should be validated/sanitized by caller to prevent injection
1564 if "value" in one_cond:
1565 value = one_cond["value"]
1566 data_type = one_cond["data_type"] if "data_type" in one_cond else "text"
1567 cast = ""
1568 if data_type == "int":
1569 cast = "::integer"
1571 rel = "=" # default EQUAL
1572 if type_condition == "NOTEQUAL":
1573 rel = "!="
1574 elif type_condition == "GREATER":
1575 rel = ">"
1577 if value == "formula":
1578 # WARNING: Formula is potentially unsafe - should be validated by caller
1579 formula = one_cond["formula"]
1580 list_conditions.append(f"{variable}{cast} {rel} {formula}")
1581 else:
1582 list_conditions.append(f"{variable}{cast} {rel} %s")
1583 params.append(value)
1584 else:
1585 list_conditions.append(f"{variable} IS NOT NULL")
1587 cond_sql = ""
1588 if len(list_conditions) > 0:
1589 cond_sql = " WHERE " + " AND ".join(list_conditions)
1591 order_by = ""
1592 if col_csv != "count(*)" and col_csv != "sum(nb_page)":
1593 order_by = " ORDER BY id DESC "
1595 limit_offset = ""
1596 if limit != None and limit != 0:
1597 limit_offset = " LIMIT %s OFFSET %s"
1598 params.extend([limit, offset])
1600 # WARNING: col_csv should be validated/sanitized by caller to prevent injection
1601 query = f"""SELECT {col_csv} FROM mtraudit.mtr_datou_exec {cond_sql} {order_by} {limit_offset}"""
1602 print(" in load_data_audit " + str(query))
1603 if output_type == "pd":
1604 import pandas.io.sql as psql
1605 if params:
1606 dataframe = psql.read_sql(query, self.client, params=tuple(params))
1607 else:
1608 dataframe = psql.read_sql(query, self.client)
1609 return dataframe
1610 elif output_type == "dict":
1611 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1612 cursor.execute(query, tuple(params) if params else None)
1613 if hash_id_treatment != None:
1614 all_fetch = cursor.fetchone()
1615 else :
1616 all_fetch = cursor.fetchall()
1618 return all_fetch
1619 else :
1620 print("Unsupported output_type : " + str(output_type))
1621 return None
1623 def duplicate_data_audit(self, hash_id_treatment = "default_nothing_happen",
1624 new_hash_id_treatment = "None"):
1625 if new_hash_id_treatment == None:
1626 from auth.lib_auth import create_id
1627 new_hash_id_treatment = create_id()
1629 query_duplicate = """ INSERT INTO mtraudit.mtr_datou_exec (info_date, id_file, nb_page, mtr_datou_id, created_at, launched_at, modified_at, deleted_at, valid_until, user_id, project_id, safia_doc_id, hash_id_treatment, cdn, audit_info)
1630 SELECT info_date, id_file, nb_page, mtr_datou_id, created_at, launched_at, modified_at, deleted_at, valid_until, user_id, project_id, safia_doc_id, %s, cdn, audit_info
1631 FROM mtraudit.mtr_datou_exec
1632 WHERE hash_id_treatment=%s"""
1633 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1634 cursor.execute(query_duplicate, (new_hash_id_treatment, hash_id_treatment))
1635 self.client.commit()
1636 return new_hash_id_treatment
1637# return cursor.fetchall()
1639 def delete_data_manual(self, id = None, hash_id_treatment = None):
1640 query = "DELETE FROM mtraudit.mtr_manual WHERE id=%s;"
1641 if id != None and str(id).isdigit():
1642 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1643 cursor.execute(query, (id,))
1644 self.client.commit()
1646 def update_data_manual(self, id = None, hash_id_treatment = None, new_hit = None):
1647 new_hit_fetch = None
1648 query_find_id_new_hit = "SELECT id FROM mtraudit.mtr_datou_exec WHERE hash_id_treatment=%s"
1649 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1650 cursor.execute(query_find_id_new_hit, (new_hit,))
1651 new_hit_fetch = cursor.fetchone()
1652 if new_hit_fetch != None and id != None and str(id).isdigit():
1653 query = "UPDATE mtraudit.mtr_manual SET mtr_datou_exec=%s where id=%s;"
1654 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1655 cursor.execute(query, (new_hit_fetch, id))
1656 self.client.commit()
1658 def select_datou_exec_from_id_file_and_mtr_datou_id(self, id_file, mtr_datou_id = 51):
1659 query_find_id_new_hit = "SELECT id, hash_id_treatment as hit FROM mtraudit.mtr_datou_exec WHERE id_file=%s AND mtr_datou_id=%s"
1660 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1661 cursor.execute(query_find_id_new_hit, (id_file, mtr_datou_id))
1662 all_fetch = cursor.fetchall()
1663 if len(all_fetch) == 0:
1664 print(" No result for id_file : " + str(id_file) + " and mtr_datou_id : " + str(mtr_datou_id))
1665 return None
1666 elif len(all_fetch) >= 1:
1667 print(" Warning, more than one result for id_file : " + str(id_file) + " and mtr_datou_id : " + str(mtr_datou_id))
1668 return all_fetch
1670 def save_data_manual(self, hash_id_treatment, user_id, manual_result):
1671 self.table_mtr_manual = "mtraudit.mtr_manual"
1672 all_result = self.load_data_audit(hash_id_treatment=hash_id_treatment,
1673 col_csv="id,hash_id_treatment")
1674 if (type(all_result) == dict or type(all_result) == RealDictRow) and "id" in all_result:
1675 id_mde = all_result["id"]
1676 elif len(all_result) != 1:
1677 print("Unexpected result, return None, the rest should fail : ")
1678 id_mde = None
1679 else:
1680 id_mde = all_result[0]["id"] if "id" in all_result[0] else None
1682 if id_mde != None:
1683 insert_query = sql.SQL("INSERT INTO {} (mtr_exec_id, user_id, manual_input_info) VALUES (%s, %s, %s)").format(sql.Identifier(*self.table_mtr_manual.split('.')))
1685 val = (id_mde, user_id, json.dumps(manual_result, default=str))
1686 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1687 cursor.execute(insert_query, val)
1688 res = self.client.commit()
1689# return res
1690 # one_fetch = cursor.fetchone()
1692 else :
1693 print(" None id_mde, not saving anything ! ")
1695# return 0
1697 def update_data_manual(self, hash_id_treatment = None,
1698 new_hash_id_treatment = None,
1699 id = None):
1701 query = "SELECT id FROM mtraudit.mtr_datou_Exec WHERE hash_id_treatment=%s"
1702 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1703 cursor.execute(query, (new_hash_id_treatment,))
1704 new_hash_id_treatment_fetch = cursor.fetchone()#.fetchall()
1706 query = "UPDATE mtraudit.mtr_manual SET mtr_exec_id=%s WHERE id=%s"
1707 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1708 cursor.execute(query, (new_hash_id_treatment_fetch["id"], id))
1710 def load_data_manual(self, ids_manual = None, hash_id_treatment = None,
1711 limit = 0, offset = 0, list_cols_as_csv = "mm.id,mm.mtr_exec_id,mm.created_at,mm.modified_at,mm.user_id,mm.manual_input_info,mm.auto_info"):
1712 list_cond = []
1713 params = []
1714 list_tables = ["mtraudit.mtr_manual mm"]
1715 order_by = " ORDER BY id DESC"
1716 list_col_as_list = list(map(lambda x : x.strip(" "), list_cols_as_csv.split(",")))
1717 if hash_id_treatment != None:
1718 print(" Not yet supported !")
1719 list_cond.append("mde.hash_id_treatment=%s")
1720 params.append(hash_id_treatment)
1721 list_cond.append("mde.id=mm.mtr_exec_id")
1722 list_tables.append("mtraudit.mtr_datou_exec mde")
1723 # list_col_as_list = list(map(lambda x : "mm." + x, list_col_as_list))
1724 order_by = " ORDER BY mm.id"
1725 elif ids_manual != None: # JE ne vois pas comment gérer ce cas, mais du coup la logique d'alias de table nécessite de hardcoder mtr_manual comme mm
1726 # WARNING: ids_manual should be validated by caller to prevent SQL injection
1727 list_cond.append(f"mm.ids_manual IN ({ids_manual})")
1728 else:
1729 print("Not yet supported, but maybe should use the limit then !")
1731 cond_sql = ""
1732 if len(list_cond) > 0:
1733 cond_sql = "WHERE " + " AND ".join(list_cond)
1734 list_table_csv = ", ".join(list_tables)
1736 limit_cond = ""
1737 if limit != None and int(limit) > 0:
1738 limit_cond = " LIMIT %s OFFSET %s"
1739 params.extend([limit, offset])
1741 # WARNING: list_cols_as_csv should be validated by caller to prevent SQL injection
1742 list_col = ",".join(list_col_as_list)
1743 query = f"""SELECT {list_col} FROM {list_table_csv} {cond_sql} {order_by} {limit_cond}"""
1744 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1745 cursor.execute(query, tuple(params) if params else None)
1746 all_fetch = cursor.fetchall()
1748 return all_fetch
1752 def load_data_comms(self, hash_id_treatment = None,
1753 mtr_datou_id_extract = 40):
1755 if hash_id_treatment == None:
1756 print("hash_id_treatment none not supported, but maybe should use the limit then !")
1757 return {}
1759 query = """SELECT comms->comm_ids as commentaire, comm_ids as id_doc, comm_ids::int+1 as nb_doc
1760FROM
1761(
1762SELECT json_object_keys(comms) as comm_ids, comms, hit
1763FROM
1764(
1765SELECT (json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json)->'Commentaires' as comms, mde.hash_id_treatment as hit
1766FROM mtraudit.mtr_datou_exec mde WHERE mde.hash_id_treatment=%s
1767AND mde.mtr_datou_id=%s
1768)
1769as comms_json
1770)
1771as comm_json"""
1772 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1773 cursor.execute(query, (hash_id_treatment, mtr_datou_id_extract))
1774 all_fetch = cursor.fetchall()
1776 return all_fetch
1779 def get_treatment_done(self, last_month, year_last_month,
1780 mtr_datou_id = 40,
1781 project_id = [],
1782 excluded_project_id = [22, 121, 135, 99, 93, 94, 95, 96, 97, 268, 92]):
1784 list_conditions = []
1785 params = []
1786 if len(excluded_project_id) > 0:
1787 placeholders = ','.join(['%s'] * len(excluded_project_id))
1788 list_conditions.append(f" project_id NOT IN ({placeholders})")
1789 params.extend(excluded_project_id)
1790 if len(project_id) > 0:
1791 placeholders = ','.join(['%s'] * len(project_id))
1792 list_conditions.append(f" project_id IN ({placeholders})")
1793 params.extend(project_id)
1794 if last_month != None:
1795# list_conditions.append(" EXTRACT(MONTH FROM valid_until) <= 6 ") # + str(last_month))
1796 # list_conditions.append(" EXTRACT(MONTH FROM valid_until) = %s")
1797 list_conditions.append(" EXTRACT(MONTH FROM created_at) = %s")
1798 params.append(last_month)
1799 if year_last_month != None:
1800 list_conditions.append(" EXTRACT(YEAR FROM created_at) = %s")
1801 params.append(year_last_month)
1802 if mtr_datou_id != None:
1803 list_conditions.append(" mtr_datou_id = %s")
1804 params.append(mtr_datou_id)
1806 cond_sql = ""
1807 if len(list_conditions) > 0:
1808 cond_sql = " WHERE " + " AND ".join(list_conditions)
1810 query = f"""SELECT created_at, nb_page, project_id, info_date, mtr_datou_id, info_consolidate, id_file, hash_id_treatment FROM mtraudit.mtr_datou_exec {cond_sql}"""
1812 print(str(query))
1814 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1815 cursor.execute(query, tuple(params) if params else None)
1816 all_fetch = cursor.fetchall()
1818 return all_fetch
1821 def load_auto_val(self, type_doc = "document_type", verbose = False, limit = 1000000
1822 , project_id = None):
1823 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else ""
1825 query = """select json_extract_path_text(df_complet_as_json, '""" + type_doc + """', id_row::text) as auto_val, id_row,
1826nb_page, query_type, user_id, created_at, col_name, manual_val, corrected_at, lgth_audit, id_file, datou_step, hit
1827from (
1828select
1829generate_series(0,nb_page-1) as id_row,
1830nb_page as nb_page, 'only_auto' as query_type, mde.user_id as user_id,
1831mde.created_at as created_at,
1832'""" + type_doc + """' as col_name,
1833'None' as manual_val,
1834'None' as corrected_at,
1835length(mde.audit_info::text) as lgth_audit,
1836mde.id_file as id_file,
1837mde.audit_info->'io_exec'->'9'->'datou_step' as datou_step,
1838json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json as df_complet_as_json,
1839mde.hash_id_treatment as hit from mtraudit.mtr_datou_exec mde where mde.project_id
1840not in (22, 121, 135) and mde.mtr_datou_id=40
1841""" + condition_project_id + """
1842order by mde.id desc
1843limit """ + str(limit) + """
1844)
1845as temp;
1846"""
1848 if verbose:
1849 print(query)
1851 import pandas.io.sql as psql
1852 dataframe = psql.read_sql(query, self.client)
1853 return dataframe
1855 def load_manual_correct_val(self, type_doc = "document_type", verbose = False, limit = 1000000, project_id = None):
1857 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else ""
1859 query = """select id, id_row, hit, col_name, manual_val, created_at, corrected_at, user_id, id_file, lgth_audit,
1860json_extract_path_text(saxia_auto_res, id_row) as auto_val
1861from (select id, hit, json_extract_path_text(actions, 'id') as id_row,
1862 json_extract_path_text(actions, 'col_name') as col_name, corrected_at, created_at,
1863json_extract_path_text(actions, 'manual_value') as manual_val,
1864user_id, lgth_audit, id_file, saxia_auto_all->'""" + type_doc + """' as saxia_auto_res
1865from (
1866select mm.id as id, mde.hash_id_treatment as hit, mm.user_id as user_id, mde.created_at as created_at, mm.created_at as corrected_at,
1867json_array_elements(mm.manual_input_info->'list_actions') as actions,
1868length(mde.audit_info::text) as lgth_audit, mde.id_file as id_file,
1869(json_extract_path_text(mde.audit_info->'io_exec'->'9'->'output', 'df_complet_as_json')::json)
1870as saxia_auto_all, mde.audit_info->'io_exec'->'9'->'datou_step' as datou_step,
1871mde.hash_id_treatment from mtraudit.mtr_manual mm, mtraudit.mtr_datou_exec mde
1872where mm.mtr_exec_id=mde.id and mde.project_id not in (22, 121, 135)
1873""" + condition_project_id + """
1874 and mde.mtr_datou_id=40
1875 order by mde.id desc
1876 limit """ + str(limit) + """
1877) as temp) as temp2 where col_name='""" + type_doc + """' order by created_at;
1880"""
1883 if verbose:
1884 print(query)
1886 import pandas.io.sql as psql
1887 dataframe = psql.read_sql(query, self.client)
1888 return dataframe
1890 def load_df_cons(self, verbose = False, limit = 0, project_id = None,
1891 mtr_datou_id = 44, with_out_folder = True):
1892 condition_project_id = " and mde.project_id in (" + ",".join(list(map(str, project_id))) + ")" if project_id != None and project_id != 0 and (type(project_id) == list and len(project_id) > 0) else ""
1894 list_col_output = ["""json_extract_path_text(mde.info_consolidate, 'df_cons')::json as df_cons,
1895 mde.hash_id_treatment, mde.id_file, mde.project_id, mde.id, json_extract_path_text(mde.info_consolidate, 'hash_id_treatment_input') as mhit"""]
1896 list_table = ["mtraudit.mtr_datou_exec mde"]
1897 list_condition = [""]
1898 if with_out_folder:
1899 list_col_output.append("json_extract_path_text(mdeh.info_back, 'out_folder') as out_folder")
1900 list_table.append("mtraudit.mtr_datou_exec mdeh")
1901 list_condition.append(" mdeh.mtr_datou_id = 40 and mdeh.hash_id_treatment = json_extract_path_text(mde.info_consolidate, 'hash_id_treatment_input')")
1902 print("TO ADAPT")
1903 else:
1904 print("NOT DONE, with_out_folder by default")
1906 if (type(project_id) == str and project_id.isdigit()) or type(project_id) == int:
1907 condition_project_id = " and mde.project_id = " + str(project_id) + " "
1909 limit_cond = f" LIMIT {limit}" if limit != None and int(limit) > 0 else ""
1911 query = f"""SELECT {', '.join(list_col_output)}
1912FROM {', '.join(list_table)}
1913WHERE mde.mtr_datou_id=44
1914{' AND '.join(list_condition)}
1915{condition_project_id}
1916{limit_cond}
1917 """
1919 if verbose:
1920 print(query)
1921 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1922 cursor.execute(query)
1923 all_fetch = cursor.fetchall()
1925 return all_fetch
1926 #import pandas.io.sql as psql
1927 #dataframe = psql.read_sql(query, self.client)
1928 #return dataframe
1930 def load_output_folder_from_mhit(self, list_mhits = [], verbose = False):
1931 if len(list_mhits) == 0:
1932 return []
1933 query = f"""SELECT hash_id_treatment as mhit, json_extract_path_text(info_back, 'out_folder') as out_folder
1934 from mtraudit.mtr_datou_exec where hash_id_treatment in ({",".join(list(map(lambda x : "'" + x + "'", list_mhits)))})"""
1936 if verbose:
1937 print(query)
1938 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1939 cursor.execute(query)
1940 all_fetch = cursor.fetchall()
1942 return all_fetch
1944 def running_job(self, project_id = None, verbose = False,
1945 mtr_datou_id_upload = 51,
1946 mtr_datou_id_anon = 25,
1947 mtr_datou_id_extract = 40,
1948 mtr_datou_id_prediag = 55,
1949 mtr_datou_id_consolidate = 44,
1950 nb_day = 0):
1952 params = []
1953 list_conditions = ["current_date-created_at::date<=%s"]
1954 params.append(nb_day)
1955 list_conditions.append("id_file IS NOT NULL")
1956 if project_id != None and str(project_id) != "0":
1957 list_conditions.append("project_id=%s")
1958 params.append(project_id)
1960 if len(list_conditions) > 0:
1961 cond_sql = " WHERE " + " AND ".join(list_conditions)
1962 else :
1963 cond_sql = ""
1965 query = f"""
1966 SELECT id_file, info_lab, info_date, created_at, nb_page, project_id, mtr_datou_id, info_back, hash_id_treatment FROM mtraudit.mtr_datou_exec {cond_sql}
1967 """
1969 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
1970 cursor.execute(query, tuple(params))
1971 all_fetch = cursor.fetchall()
1973 list_uploaded = []
1974 list_treated = []
1975 list_anon = []
1976 list_prediag = []
1977 list_consolidate = []
1978 for d in all_fetch:
1979 if str(d["mtr_datou_id"]) == str(mtr_datou_id_upload):
1980 list_uploaded.append(d)
1981 if str(d["mtr_datou_id"]) == str(mtr_datou_id_extract):
1982 list_treated.append(d)
1983 if str(d["mtr_datou_id"]) == str(mtr_datou_id_anon):
1984 list_anon.append(d)
1985 if str(d["mtr_datou_id"]) == str(mtr_datou_id_prediag):
1986 list_prediag.append(d)
1987 if str(d["mtr_datou_id"]) == str(mtr_datou_id_consolidate):
1988 list_consolidate.append(d)
1990 list_running = []
1991 for d in list_uploaded:
1992 found_treated = False
1993 for t in list_treated:
1994 if d["id_file"] == t["id_file"] and d["project_id"] == t["project_id"]:
1995 found_treated = True
1996 break
1998 found_prediag = False
1999 for a in list_prediag:
2000 if d["id_file"] == a["id_file"] and d["project_id"] == a["project_id"]:
2001 found_prediag = True
2002 break
2004 found_anon = False
2005 for n in list_anon:
2006 if d["id_file"] == n["id_file"] and d["project_id"] == n["project_id"]:
2007 found_anon = True
2008 break
2010 if found_prediag:
2011 d["prediag"] = a
2013 if found_anon:
2014 d["anon"] = n
2016 if not found_treated:
2017 list_running.append(d)
2019 if len(list_running) > 0:
2020 # print("could Build link pdf or anon in suivi running")
2021 # {'in_folder': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15',
2022 # 'work_folder_images': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15',
2023 # 'out_folder': 'static/onedrive/workarea_anon/output_wfrom_api_d_2210_Oct_15',
2024 # 'work_folder': '/Users/moilerat/Documents/Fotonower/Safia/prompt/python/server',
2025 # 'out_file': 'id_4_nb_1_d_20240612_e813c6b0fd8327e25b11697930b84d15d0c03ff6239f10734e4458b040873942.docx'}
2026 for data in list_running:
2027 # print("TODO")
2028 data["link_pdf"] = ""
2029 #del data["info_back"]
2031 return {"running_or_failed": list_running,
2032 "zprediag" : list_prediag,
2033 "uploaded": list_uploaded,
2034 "treated": list_treated,
2035 "zanon" : list_anon,
2036 "zconsolidate" : list_consolidate}
2040 def record_validation_code(self, user_id, code, ipv4):
2041 query = "INSERT INTO mtruser.code_double_factor (code, user_id, created_at, ip_used) VALUES (%s, %s, now(), %s)"
2042 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2043 cursor.execute(query, (code, user_id, ipv4))
2044 self.client.commit()
2046 def check_validation_code(self, user_id, code):
2048 query = """
2049 SELECT id, user_id, created_at, validated_at FROM mtruser.code_double_factor WHERE code=%s AND user_id=%s AND created_at + interval '1 hour' > now() AND validated_at IS NULL
2050 """
2052 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2053 cursor.execute(query, (code, user_id))
2054 all_fetch = cursor.fetchall()
2056 if len(all_fetch) == 0:
2057 return False
2058 else :
2059 print(" We should check valide")
2060 print(" all_fetch validation code : " + str(all_fetch))
2062 query_update = "UPDATE mtruser.code_double_factor SET validated_at=now() WHERE id=%s"
2063 cursor.execute(query_update, (all_fetch[0]["id"],))
2065 return True
2067 def record_ip_2fa(self, user_id, ip_validated, white_list_ip = False, soft = True):
2069 if white_list_ip:
2070 valid_until = "NOW() + interval '1 year'"
2071 else:
2072 valid_until = "NOW()"
2074 if soft:
2075 action_on_conflict = "DO NOTHING "
2076 action_on_conflict = f"DO UPDATE SET user_id={user_id}"
2077 else :
2078 action_on_conflict = f"DO UPDATE SET valid_until={valid_until}"
2080 query = f"""insert into mtruser.validated_ip (user_id, ipv4, valid_until)
2081 values ({user_id}, '{ip_validated}', {valid_until})
2082 ON CONFLICT(user_id, ipv4) {action_on_conflict}
2083 RETURNING id;"""
2085 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2086 cursor.execute(query)
2087 res = cursor.fetchone()
2088 self.client.commit()
2089 if 'id' in res:
2090 return res['id']
2091 else :
2092 return None
2094 # VR on dirait qu'on utilise pas cela !
2095 def retrieve_ips_2fa(self, user_id, ip_search = None, only_valid = True):
2096 params = [user_id]
2097 query = "SELECT * FROM mtruser.validated_ip WHERE user_id=%s"
2098 if ip_search != None:
2099 query += " AND ipv4=%s"
2100 params.append(ip_search)
2102 if only_valid:
2103 query += " AND valid_until > NOW()"
2105 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2106 cursor.execute(query, tuple(params))
2107 all_fetch = cursor.fetchall()
2108 return all_fetch
2110 def deactivate_all_ips_2fa(self, user_id):
2111 query = "UPDATE mtruser.validated_ip SET valid_until=NOW() WHERE user_id=%s"
2112 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2113 cursor.execute(query, (user_id,))
2115 def record_connection_ip_info(self, id_ipv4, type, info):
2117# UPDATE mtruser.project SET configuration = jsonb_set(configuration, '{custom_display,saxia_suivi,quick}', '{"saxia.config.project": {"project_id": 91}}', true) WHERE id = 91;
2119 import datetime
2120 info["created_at"] = datetime.datetime.now().isoformat()
2122 info_json_as_str = json.dumps(info)
2124 # Note: type is used as a JSON key path - should be validated by caller to prevent injection
2125 query = """UPDATE mtruser.validated_ip
2126 SET misc_info = jsonb_set(
2127 misc_info,
2128 '{""" + type + """}',
2129 COALESCE(misc_info->'""" + type + """', '[]'::jsonb) || %s::jsonb
2130)
2131WHERE id = %s"""
2133 print(query)
2134 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2135 cursor.execute(query, (info_json_as_str, id_ipv4))
2136 self.client.commit()
2138 def get_usage_connection_ip_info(self, mtr_user_id = None, ipv4 = None, type = None,
2139 nb_day = 0, limit = 0, offset = 0):
2141 cond_json_key = "misc_info"
2142 params = []
2143 if type != None:
2144 # Note: type is used as a JSON key path - should be validated by caller
2145 cond_json_key += "->'" + str(type) + "'"
2146 data_col = " jsonb_array_elements(coalesce(" + cond_json_key + ", '[]'::jsonb))"
2147 else :
2148 print(" TODO VR 4/2/25 to use jsonb_object_keys Je ne sais pas si on va gérer cela car il faut un jsonb_object_keys ou un truc imbriqué ! ")
2149 data_col = " " + cond_json_key + " "
2151 list_condition = []
2152 if mtr_user_id != None:
2153 list_condition.append("user_id=%s")
2154 params.append(mtr_user_id)
2155 if ipv4 != None:
2156 list_condition.append("ipv4=%s")
2157 params.append(ipv4)
2159 condition = ""
2160 if len(list_condition) > 0:
2161 condition = " WHERE " + " AND ".join(list_condition)
2163 # Note: type is used in query construction - should be validated by caller
2164 query = """
2165 SELECT ipv4, user_id, '""" + str(type) + """' as type, """ + data_col + """ as elem FROM mtruser.validated_ip
2166 """ + condition + """ """
2168 if nb_day != None and str(nb_day).isdigit() and int(nb_day) > 0:
2169 # Note: nb_day should be validated as integer by caller
2170 query = "SELECT * FROM ( " + query + " ) as temp WHERE (elem->>'created_at')::date + interval '" + str(nb_day) + " day' > now() ORDER BY (elem->>'created_at')::timestamp"
2172# list_condition.append("elem->'created_at'::date >= current_date - interval '" + str(nb_day) + " day'")
2174 print(query)
2175 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2176 cursor.execute(query, tuple(params) if params else None)
2177 all_fetch = cursor.fetchall()
2178 return all_fetch
2180 return {}
2182 def audit_or_delete_user(self, user_id, also_delete = False):
2184 all_info = {"list_deleted" : [], "list_kept" : []}
2185 if str(user_id).isdigit():
2186 all_project_owned = self.get_projects_from_owner(user_id)
2187 info_user = self.select_user(user_id)
2188 info_user2 = self.get_user_info_from_id(user_id)
2190 all_info["audit"] = {"info_user" : info_user,
2191 "info_user2" : info_user2,
2192 "projects" : all_project_owned}
2194 schema_user_documents = info_user["schema_user_documents"] if info_user != None and "schema_user_documents" in info_user else ""
2195 if also_delete:
2196 query_delete_schema = sql.SQL("DROP SCHEMA IF EXISTS {} CASCADE").format(sql.Identifier(schema_user_documents))
2197 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2198 cursor.execute(query_delete_schema)
2199 self.client.commit()
2201 for pid in all_project_owned:
2202 info = self.get_project_info(pid["id"])
2204 if str(user_id) == str(info["owner_id"]):
2205 all_info["list_deleted"].append(info["id"])
2206 if also_delete:
2207 query_delete_project = "DELETE FROM mtruser.project WHERE id=%s"
2208 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2209 cursor.execute(query_delete_project, (info["id"],))
2210 self.client.commit()
2211 else :
2212 all_info["list_kept"].append(info)
2214 if also_delete:
2215 query = "DELETE FROM mtruser.user WHERE id=%s"
2216 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2217 cursor.execute(query, (user_id,))
2218 self.client.commit()
2220 query = "SELECT * FROM mtruser.flask_user WHERE mtr_user_id=%s"
2221 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2222 cursor.execute(query, (user_id,))
2223 self.client.commit()
2224 list_data_fu = cursor.fetchall()
2225 all_info["list_fu"] = list_data_fu
2226 if len(list_data_fu) > 1:
2227 all_info["error"].append("Multiple login account not expected : internal error, please report to admin !")
2229 if len(list_data_fu) == 1 and also_delete:
2230 id = list_data_fu[0]["id"]
2231 query = "DELETE FROM mtruser.flask_user WHERE id=%s"
2232 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2233 cursor.execute(query, (id,))
2234 self.client.commit()
2236 return all_info
2237 else :
2238 return "Nothing DONE"
2240 def get_token(self, mtr_user_id, token = None, project_id = None): # token and project unused and no limit !
2241 query = """SELECT id, valid_until, mtr_user_id, description, token, configuration, default_project_id, created_at FROM mtruser.mtr_token WHERE mtr_user_id = %s AND valid_until > NOW() """
2242 query = """
2243 SELECT id, user_id, client_id, access_token as token, 'auto' as description,
2244 TO_TIMESTAMP(issued_at) + interval '1 second' * expires_in as valid_until
2245 FROM mtruser.oauth2_token WHERE user_id = %s
2246 """ # and access_token_revoked_at is null ;"""
2248# CAST(TO_TIMESTAMP(issued_at) as date) + interval '1 second' * expires_in as valid_untile
2250# AND token = %s
2251 params = (mtr_user_id,)
2253 if project_id is not None:
2254 query += " AND default_project_id = %s"
2255 params += (project_id,)
2257 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2258 cursor.execute(query, params)
2259 res = cursor.fetchall()
2260 if res:
2261 return res
2262 else:
2263 return None
2265 def create_token(self, mtr_user_id, description, token, configuration = {}, project_id = None):
2267 list_columns = ["mtr_user_id", "description", "token", "configuration"]
2268 table_name = "mtruser.mtr_token"
2269 list_values_format = ["%s", "%s", "%s", "%s"]
2270 tuple_values = (mtr_user_id, description, token, json.dumps(configuration))
2272 from werkzeug.security import gen_salt
2273 access_token = gen_salt(42)
2274 table_name = "mtruser.oauth2_token"
2275 list_columns = ["user_id", "client_id", "access_token",
2276 "refresh_token", "issued_at", "expires_in",
2277 "access_token_revoked_at", "refresh_token_revoked_at",
2278 "token_type"]
2279 list_values_format = ["%s", "%s", "%s", "%s", "%s", "%s",
2280 "%s", "%s", "%s"]
2282# strftime('%Y-%m-%d %H:%M:%S')
2283 issued_at = datetime.datetime.now() #.time()
2284 tuple_values = (mtr_user_id, "B9VTPyioeANFgaotf4tFwdwz", access_token, "", issued_at.timestamp(), "31530000", 0 , 0, "Bearer")
2286 if project_id is not None:
2287 list_columns.append("default_project_id")
2288 configuration["project_id"] = project_id
2289 list_values_format.append("%s")
2290 tuple_values += (project_id,)
2292 query = f"INSERT INTO {table_name} (" + ", ".join(list_columns) + ") VALUES (" + ", ".join(list_values_format) + ") RETURNING id;"
2294 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2295 cursor.execute(query, tuple_values)
2296 res = cursor.fetchone()
2297 self.client.commit()
2299 if res and 'id' in res:
2300 return res['id']
2301 else:
2302 return None
2304 def update_token(self, token, configuration = None, project_id = None, mtr_user_id = None, set_not_valid=True, description=None):
2305 # en fait je ne veux pas de description !
2306 list_columns = []
2307 tuple_values = ()
2308 if configuration is not None:
2309 list_columns.append("configuration = %s")
2310 tuple_values += (json.dumps(configuration),)
2311 if project_id is not None:
2312 list_columns.append("default_project_id = %s")
2313 tuple_values += (project_id,)
2314 if mtr_user_id is not None:
2315 list_columns.append("mtr_user_id = %s")
2316 tuple_values += (mtr_user_id,)
2317 if set_not_valid:
2318 list_columns.append("valid_until = NOW()")
2319 if description is not None:
2320 list_columns.append("description = %s")
2321 tuple_values += (description,)
2322 if len(list_columns) == 0:
2323 print("Nothing to update, returning False")
2324 return False
2325 list_columns.append("modified_at = NOW()")
2326 query = f"UPDATE mtruser.mtr_token SET {', '.join(list_columns)} WHERE token = %s;"
2327 tuple_values += (token,)
2328 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2329 cursor.execute(query, tuple_values)
2330 self.client.commit()
2331 return True
2333# get_token_if_is_valid
2334 def check_bearer_token(self, token):
2335 query = """SELECT id, mtr_user_id, description, configuration, default_project_id, created_at FROM mtruser.mtr_token WHERE token = %s AND valid_until > NOW();
2336 """
2337 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2338 cursor.execute(query, (token,))
2339 res = cursor.fetchone()
2340 if res:
2341 return res
2342 else:
2343 return None
2345 def list_autorized_client(self, mtr_user_id):
2346 query = """SELECT * FROM mtruser.client WHERE mtr_user_id={mtr_user_id};
2347 """
2348 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2349 cursor.execute(query)
2350 res = cursor.fetchone()
2351 if res:
2352 return res
2353 else:
2354 return None
2356 def list_token_access_usage(self, mtr_user_id):
2357 pass
2359 def list_grant_access_to_client_app(self, mtr_user_id):
2360 query = """SELECT * FROM mtruser.grant WHERE mtr_user_id={mtr_user_id};
2361 """
2362 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2363 cursor.execute(query)
2364 res = cursor.fetchone()
2365 if res:
2366 return res
2367 else:
2368 return None
2370 def revoke_client(self, mtr_user_id, client_id):
2371 pass
2377 def list_client_app_to_uld(self, client_name):
2378 pass
2380 def create_client_app_to_uld(self, client_name):
2381 pass
2383 # OAuth2 Client management methods
2384 def get_oauth_clients_by_user(self, user_id):
2385 """
2386 Retrieve all OAuth2 clients for a specific user
2388 Args:
2389 user_id: The user ID to filter clients
2391 Returns:
2392 List of dictionaries containing client information
2393 id | user_id | client_id | client_secret | client_id_issued_at | client_secret_expires_at | client_metadata
2395 """
2396 query = """
2397 SELECT id, user_id, client_id, client_secret, client_id_issued_at, client_secret_expires_at, client_metadata
2398 FROM mtruser.oauth2_client
2399 WHERE user_id = %s
2400 ORDER BY client_id_issued_at DESC
2401 """
2403 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2404 cursor.execute(query, (user_id,))
2405 res = cursor.fetchall()
2406 if res:
2407 return res
2408 else:
2409 return None
2411 def create_oauth_client(self, client_id, client_secret, user_id, client_name,
2412 redirect_uris, grant_types, response_types,
2413 scope = None, client_uri = None, client_metadata = {}):
2414 """
2415 Create a new OAuth2 client in the database
2417 Args:
2418 client_id: Generated unique client identifier
2419 client_secret: Generated secret for the client
2420 user_id: The user who owns this client
2421 client_metadata: JSON string of additional metadata
2422 redirect_uris: Space-separated string of redirect URIs
2423 grant_types: Space-separated string of grant types
2424 response_types: Space-separated string of response types
2425 client_uri: Optional URL of the client application
2426 scope: OAuth scopes for the client
2427 client_name: Human-readable name for the client
2429 Returns:
2430 The ID of the newly created client
2431 """
2432 query = """
2433 INSERT INTO mtruser.oauth2_client
2434 (client_id, client_secret, user_id, client_metadata, client_id_issued_at, client_secret_expires_at)
2435 VALUES (%s, %s, %s, %s, EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')),
2436 EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP + interval '10000 hour') AT TIME ZONE 'UTC') )
2437 RETURNING id
2438 """
2440 if isinstance(redirect_uris, str) and redirect_uris.strip() != "":
2441 redirect_uris = [uri.strip() for uri in redirect_uris.split()]
2442 else:
2443 redirect_uris = []
2444 if isinstance(redirect_uris, list):
2445 if "redirect_uris" in client_metadata:
2446 redirect_uris = list(set(client_metadata["redirect_uris"] + redirect_uris))
2447 client_metadata["redirect_uris"] = redirect_uris
2449 if isinstance(grant_types, str) and grant_types.strip() != "":
2450 grant_types = [gt.strip() for gt in grant_types.split()]
2451 else:
2452 grant_types = []
2453 if isinstance(grant_types, list):
2454 if "grant_types" in client_metadata:
2455 grant_types = list(set(client_metadata["grant_types"] + grant_types))
2456 client_metadata["grant_types"] = grant_types
2458 if isinstance(response_types, str) and response_types.strip() != "":
2459 response_types = [rt.strip() for rt in response_types.split()]
2460 else:
2461 response_types = []
2462 if isinstance(response_types, list):
2463 if "response_types" in client_metadata:
2464 response_types = list(set(client_metadata["response_types"] + response_types))
2465 client_metadata["response_types"] = response_types
2466 if client_uri != None:
2467 client_metadata["client_uri"] = client_uri
2468 if scope != None:
2469 client_metadata["scope"] = scope
2471 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2472 cursor.execute(query, (client_id, client_secret, user_id, json.dumps(client_metadata)))
2473 result = cursor.fetchone()
2474 self.client.commit()
2475 return result['id'] if result and 'id' in result else None
2477 def delete_oauth_client(self, client_id, user_id):
2478 """
2479 Delete an OAuth2 client from the database
2481 Args:
2482 client_id: The client identifier to delete
2483 user_id: The user who owns this client
2484 Returns:
2485 True if deletion was successful, False otherwise
2486 """
2487 query = """
2488 DELETE FROM mtruser.oauth2_client
2489 WHERE client_id = %s AND user_id = %s
2490 """
2492 with self.client.cursor(cursor_factory=RealDictCursor) as cursor:
2493 cursor.execute(query, (client_id, user_id))
2494 self.client.commit()
2495 return cursor.rowcount > 0