{"id":1415,"date":"2024-08-21T18:01:26","date_gmt":"2024-08-21T16:01:26","guid":{"rendered":"https:\/\/spgoo.org\/?page_id=1415"},"modified":"2024-08-22T14:54:16","modified_gmt":"2024-08-22T12:54:16","slug":"bdd-mysql","status":"publish","type":"page","link":"https:\/\/spgoo.org\/?page_id=1415","title":{"rendered":"BDD – Mysql"},"content":{"rendered":"\n

Illustration de l’utilisation de Mysql pour le stockage des donn\u00e9es issues de l’apport des utilisateurs dans Emolgine.<\/p>\n\n\n\n

Deux tables ont \u00e9t\u00e9 g\u00e9n\u00e9r\u00e9es ayant une structure voisine. La table emolgine_requetes et emolgine_annotation.<\/p>\n\n\n\n

emolgine_requetes <\/strong>: objectif est de stocker la liste des requ\u00eates ex\u00e9cut\u00e9es dans Neo4J<\/span> par utilisateur. Chaque requ\u00eate est associ\u00e9e \u00e0 un calcul de hash (sha256) stock\u00e9 dans l’attribut qui permet de cumuler pour un m\u00eame utilisateur une seule entr\u00e9e et de pouvoir stocker \u00e0 chaque ex\u00e9cution dans l’attribut performances un tableau (date — perf).<\/p>\n\n\n\n

Structure de cette table :<\/p>\n\n\n\n

+---------------+---------------------+------+-----+---------+---------------+\n| Field        | Type                | Null | Key | Default | Extra          |\n+---------------+---------------------+------+-----+---------+---------------+\n| id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |\n| user         | varchar(255)        | NO   |     | NULL    |                |\n| checksum     | varchar(255)        | NO   |     | NULL    |                |\n| requete      | longtext            | NO   |     | NULL    |                |\n| performances | longtext            | YES  |     | NULL    |                |\n| accessibilite| varchar(20)         | YES  |     | NULL    |                |\n+---------------+---------------------+------+-----+---------+---------------+<\/code><\/pre>\n\n\n\n
Exemple de contenu <\/p>\n\n\n\n
| 54 | useremolgine | 1ef2d5941c5b7757bdd4fd7766066abc843f95f9f0bc31aaaedc3ea8721c340d | MATCH (n:Molecule) RETURN n LIMIT 10 | [{\"date\": \"2024-08-21 09:05:55\", \"perf\": 0.008063077926635742}, {\"date\": \"2024-08-21 09:06:10\", \"perf\": \"0.016541004180908\"}] | perso |\n| 55 | useremolgine | ca593a99549ed11ed8487bd5aee98c4d3902473e1bfc01cb3b168bf1ab7018cf | MATCH r=(mol:Molecule)-[:FIT]->(:Cluster_mol_site)-[:CFIT]->(:Site)-[:FROM]->(:Protein) WHERE mol.id STARTS WITH 'ABC' RETURN r LIMIT 200 | [{\"date\": \"2024-08-21 09:06:36\", \"perf\": 0.02539992332458496}, {\"date\": \"2024-08-21 09:07:39\", \"perf\": \"0.025347948074341\"}, {\"date\": \"2024-08-21 09:52:16\", \"perf\": \"0.025094985961914\"}] | perso |\n| 56 | useremolgine | add52ca3f196593a76174c5ab564cc48bfc681d9ffcf8fbca0d4dc6ed7a82375 | MATCH (n1)-[r]->(n2) RETURN r, n1, n2 LIMIT 25 | [{\"date\":\"2024-08-21 09:52:31\",\"perf\":0.02987384796142578}] | perso |\n| 57 | ystroppa | ca593a99549ed11ed8487bd5aee98c4d3902473e1bfc01cb3b168bf1ab7018cf | MATCH r=(mol:Molecule)-[:FIT]->(:Cluster_mol_site)-[:CFIT]->(:Site)-[:FROM]->(:Protein) WHERE mol.id STARTS WITH 'ABC' RETURN r LIMIT 200 | [{\"date\":\"2024-08-21 13:21:16\",\"perf\":0.02583479881286621}]\n<\/code><\/pre>\n\n\n\n
Calcul du hash pour la requ\u00eate : pour le traitement de la requ\u00eate, on supprime tous les espaces et on la transforme en majuscules pour calculer le code de hashage que l’on va stocker dans la table.<\/p>\n\n\n\n
hash('sha256', strtoupper(preg_replace('\/\\s+\/','',$requete)))<\/strong><\/code><\/pre>\n\n\n\n
Illustration de la manipulation de ce type de table en SQL<\/p>\n\n\n\n
Lecture<\/p>\n\n\n\n
function requete_get($user,$checksum) {\n   global $wpdb,$table_name;\n   $data=$wpdb->get_row($wpdb->prepare(\"SELECT id FROM $table_name WHERE user like '$user' and checksum like '$checksum'\"));\n   ecrire_log(\"verification presence de la requete \".json_encode($data));\n   return $data;\n}<\/code><\/pre>\n\n\n\n
Modification :<\/p>\n\n\n\n
A l’aide de l’instruction JSON_ARRAY_APPEND on peut ajouter directement dans la structure ARRAY la nouvelle entr\u00e9e sous forme de documentation JSON sous la forme {“date”:value, “perf”:value};<\/p>\n\n\n\n
function requete_update_perf($id,$performance, $datetime) {\n   global $wpdb,$table_name;\n   ecrire_log(\"requete_update_perf \".\" \".$id.\" \".$performance.\" \".$datetime);\n   $retour=$wpdb->query($wpdb->prepare(\"UPDATE $table_name SET performances=JSON_ARRAY_APPEND<\/strong>(performances, '$',JSON_OBJECT<\/strong>('date','$datetime','perf','$performance')) WHERE id like $id\"));\n   ecrire_log(\"retour mise a jour \".$retour);\n}<\/code><\/pre>\n