{"id":968,"date":"2020-02-08T13:14:00","date_gmt":"2020-02-08T12:14:00","guid":{"rendered":"http:\/\/wollef.org\/?p=968"},"modified":"2020-02-08T13:14:00","modified_gmt":"2020-02-08T12:14:00","slug":"le-logiciel-raspberry-car-tache-de-stockage-dans-la-db-locale","status":"publish","type":"post","link":"https:\/\/wollef.org\/blog\/le-logiciel-raspberry-car-tache-de-stockage-dans-la-db-locale\/","title":{"rendered":"Le Logiciel Raspberry Car \u2013 T\u00e2che de stockage dans la DB locale (3\/5)"},"content":{"rendered":"\n<p>En utilisant les\ntechniques de la partie pr\u00e9c\u00e9dente, le code de la tache de stockage dans la DB\nlocale effectuant le travail suivant&nbsp;:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><em>Initialiser la connexion DB (si on la\nr\u00e9utilise entre les appels)<\/em><\/li><li>On boucle<ul><li>Poser le verrou\nGPS&nbsp;: je bloque la m\u00e9moire pour la derni\u00e8re position GPS<\/li><\/ul><ul><li>Poser le verrou OBD&nbsp;: je bloque la m\u00e9moire pour la\nderni\u00e8re info OBD<ul><li>Action s\u00e9curis\u00e9e : r\u00e9cup\u00e9rer les derni\u00e8res\ninformations mise de c\u00f4t\u00e9 par le programme GPS et OBD<\/li><\/ul><\/li><\/ul><ul><li>D\u00e9bloquer le verrou\nGPS <em>(cela a dur\u00e9 quelque millisecondes)<\/em><\/li><\/ul><ul><li>D\u00e9bloquer le verrou OBD <em>(cela a dur\u00e9 quelque\nmillisecondes)<\/em><\/li><\/ul><ul><li>S\u2019il y a une ou\nplusieurs mesures \u00e0 enregistrer<ul><li><em>Initialiser\nla connexion DB (si on ne la r\u00e9utilise pas entre les appels)<\/em><\/li><\/ul><ul><li>Poser le verrou\nDB&nbsp;: je bloque l\u2019acc\u00e8s \u00e0 la base SQL<ul><li>Action s\u00e9curis\u00e9e&nbsp;: Il les enregistre dans\nla base SQL<\/li><\/ul><\/li><\/ul><ul><li>D\u00e9bloquer le verrou DB <em>(cela a dur\u00e9 presque 1 seconde,\ncar on \u00e9crit sur le disque)<\/em><\/li><\/ul><ul><li><em>Fermer\nla connexion DB (si on ne la r\u00e9utilise pas entre les appels)<\/em><\/li><\/ul><\/li><\/ul><ul><li>Attendre 60 secondes<\/li><\/ul><\/li><\/ul>\n\n\n\n<p>Ressemble \u00e0<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>def store_pos_in_db(delay):\n\n    # la declaration de partage des variables memoris\u00e9\u00e9 par le thread GPS\n    global lastGpsPositionMeasured;\n    global lastGpsLongitude;\n    global lastGpsLattitude;\n    global lastGpsSpeed\n    global lastGpsElevation;\n    global lastGpsMeasureTime;\n    global lastGpsTime;\n\n    # la declaration de partage des variables memoris\u00e9\u00e9 par le thread OBD\n    global lastObdMeasured;\n    global lastObdMeasureTime;\n    global lastObdSpeed\n    global lastObdFuelLevel\n    global lastObdRpm\n\n    # initialisation des variables GPS\n    lastGpsPositionMeasured = False;\n    lastGpsLongitude = 0;\n    lastGpsLattitude =0;\n    lastGpsSpeed =0;\n    lastGpsElevation =0;\n    lastGpsMeasureTime =0;\n    lastGpsTime = 0\n\n    # initialisation OBD\n    lastObdMeasured = False;\n    lastObdMeasureTime = 0;\n    lastObdSpeed = 0;\n    lastObdFuelLevel= 0;\n    lastObdRpm= 0;\n\n    # deux mode possibles :\n    # - un ou on garde la connexion DB active entre chaque attente\n    # - un ou on la referme a chaque fois\n    if MODE_SHARED_DB_CONNECTION == True:\n        permanentDBConnectionB = sqlite3.connect(CAR_DB, isolation_level=None);\n\n    # on boucle en permanence\n    while True:\n        # on essaye d'ecrire vers la DB\n        print(\"DB: Try to store position\");\n\n        # on bloque le verrou sur les variables partag\u00e9es\n        threadGpsLock.acquire()\n        threadObdLock.acquire()\n\n        # on memorise l'heure courante (que l'on va memoriser : la derni\u00e8re mesure est faite depuis quelques secondes\n        statustime=int(datetime.timestamp(datetime.now()))\n\n        # un affichage pour le debug\n        print (C_DB+ \"--------------------------------------------\", ENDC)\n        print (C_DB+ \"DB: db.time  :\", datetime.utcfromtimestamp(statustime).strftime('%Y-%m-%d %H:%M:%S') , ENDC)\n        print (C_DB+ \"--------------------------------------------\", ENDC)\n        print (C_DB+ \"DB: g.status :\", lastGpsPositionMeasured, ENDC)\n        print (C_DB+ \"DB: g.time   :\", datetime.utcfromtimestamp(lastGpsMeasureTime).strftime('%Y-%m-%d %H:%M:%S'), ENDC)\n        print (C_DB+ \"DB: g.lat.   :\", lastGpsLattitude, ENDC)\n        print (C_DB+ \"DB: g.long   :\", lastGpsLongitude, ENDC)\n        print (C_DB+ \"DB: g.speed  :\", lastGpsSpeed, ENDC)\n        print (C_DB+ \"DB: g.elev   :\", lastGpsElevation, ENDC)\n        print (C_DB+ \"DB: g.gtime  :\", datetime.utcfromtimestamp(lastGpsTime).strftime('%Y-%m-%d %H:%M:%S'), ENDC)\n        print (C_DB+ \"--------------------------------------------\", ENDC)\n        print (C_DB+ \"DB: o.status :\", lastObdMeasured, ENDC)\n        print (C_DB+ \"DB: o.time   :\", datetime.utcfromtimestamp(lastObdMeasureTime).strftime('%Y-%m-%d %H:%M:%S'), ENDC)\n        print (C_DB+ \"DB: o.speed  :\", lastObdSpeed, ENDC)\n        print (C_DB+ \"DB: o.rpm    :\", lastObdRpm, ENDC)\n        print (C_DB+ \"DB: o.fuel   :\", lastObdFuelLevel, ENDC)\n        print (C_DB+ \"--------------------------------------------\", ENDC)\n\n        # si un des threads a une nouvelle donn\u00e9e\n        if lastGpsPositionMeasured == True or lastObdMeasured == True :\n\n            # on bloque l'acc\u00e8s \u00e0 la DB, pour que le threqd SF ne trqvqille pqs en meme temps\n            threadDbLock.acquire()\n\n            # si on est dans le mode DB en permanence active, on utilise la connection, sinon on l'ouvre\n            if MODE_SHARED_DB_CONNECTION == True:\n                conn = permanentDBConnectionB\n            else :\n                print(C_SF+\"DB : Connect to DB\", ENDC);\n                conn = sqlite3.connect(CAR_DB, isolation_level=None);\n\n            print(\"DB : Store position\");\n            # on cree le texte de la requete pour inserer dans la base\n            sql = ''' INSERT INTO car_status(statusdate, gps_longitude , gps_latitude, sent, gps_speed, gps_elevation, obd_speed, obd_fuellevel, obd_rpm, gps_measure_time, obd_measure_time, gps_time)\n                  VALUES(?,?,?,?,?,?,?,?,?,?,?,?) '''\n\n            # on ouvre le curseur vers la DB, et on lui demande d'executer la requete avec ces param\u00e8tres\n            cur = conn.cursor()\n            cur.execute(sql, (statustime, lastGpsLongitude, lastGpsLattitude, 0, lastGpsSpeed, lastGpsElevation, lastObdSpeed,lastObdFuelLevel,lastObdRpm,lastGpsMeasureTime,lastObdMeasureTime,lastGpsTime))\n\n            # si on est dans le mode o\u00f9 on ouvre et ferme la connection \u00e0 chaqiue fois, on la referme\n            if MODE_SHARED_DB_CONNECTION == False:\n                conn.close()\n                print(\"DB : Connection closed\");\n            # on libere le verrou d'acc\u00e8s \u00e0 la DB (le thread SF peut maintenant lire\n            threadDbLock.release()\n\n        # on libere les verrous d'acc\u00e8s aux variables pour que les thread OBD et GPS puissent y \u00e9crire\n        threadGpsLock.release()\n        threadObdLock.release()\n\n        time.sleep(delay)\n\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>En utilisant les techniques de la partie pr\u00e9c\u00e9dente, le code de la tache de stockage dans la DB locale effectuant le travail suivant&nbsp;: Initialiser la connexion DB (si on la r\u00e9utilise entre les appels) On boucle Poser le verrou GPS&nbsp;: je bloque la m\u00e9moire pour la derni\u00e8re position GPS Poser le verrou OBD&nbsp;: je bloque <a class=\"read-more\" href=\"https:\/\/wollef.org\/blog\/le-logiciel-raspberry-car-tache-de-stockage-dans-la-db-locale\/\">Continue Reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,1],"tags":[53],"class_list":["post-968","post","type-post","status-publish","format-standard","hentry","category-raspberry-cars","category-non-classe","tag-2020-raspberry-cars"],"_links":{"self":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts\/968","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/comments?post=968"}],"version-history":[{"count":0,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"wp:attachment":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/tags?post=968"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}