initial check in firmware files

1 files changed, 404 insertions, 0 deletions

diff --git a/software/michi_funcs/db_funcs.cpp b/software/michi_funcs/db_funcs.cpp
new file mode 100644
index 0000000..c1a2f56
--- /dev/null
+++ b/software/michi_funcs/db_funcs.cpp
@@ -0,0 +1,404 @@
+/*
+How to use db_funcs:
+1. in main.cpp #include "db_funcs.h"
+2. in main.cpp, in init()
+	while (network_connected != 1) { vTaskDelay(2000 / portTICK_PERIOD_MS); }
+	strcpy(dbnam ,(char *)"c10mm");
+	strcpy(pg_server_ip ,(char *)"192.168.178.100");
+	
+2.a	pqConnection = new TpgConnection(); // Verbindungsobject erzeugen
+2.b	if ( pqConnection->db_connect( pg_server_ip, srvpo, dbnam, PGUser, PGPassword, PGCharset) == 0) //verbinden	
+2.c	  query1 = new TResultSet( pqConnection->PGconn );
+2.d	  query1->db_query("select type, state from items limit 10;");
+2.4	  for (int iRow = 0; iRow < query1->NumRows-1; iRow++)
+	  {
+	     std::cout << query1->Zeile[iRow].Spalte[0] << " | ";
+	     std::cout << query1->Zeile[iRow].Spalte[1] << " | ";
+	     printf("\n");
+	  }	
+	
+3.	  query1->db_query("select bla bla;"); //naechste Abfrage
+	  //oder neues Abfrage-Object erstellen: query2 = new TResultSet( pqConnection->PGconn );
+	  
+4. in main.cpp, main_loop() 
+    db_loop(); //db_loop regelmaessig aufrufen, damit NOTIFY Nachrichten verarbeitet werden.
+    
+    
+5. Auf Notification reagieren: Vor der mail() Schleife die NOTIFIY Nachrichten registrieren    
+	db_query("listen door_open"); //register listen handler for NOTIFY door_open <door_no>
+	db_query("listen door_close"); //register listen handler for NOTIFY door_close <door_no>
+    
+*/
+#include <sys/types.h>
+#include <stdio.h>
+#include <string.h>
+#include "freertos/FreeRTOS.h"
+#include "freertos/task.h"
+#include "esp_netif.h"
+#include "esp_eth.h"
+#include "esp_log.h"
+#include "SimplePgSQL.h"
+
+//For ResultSet:
+#include "db_funcs.h"
+#include <vector>
+#include <iostream>
+#include <string>
+
+static int pg_flags = 0;
+
+// PGSQL
+#define PGBufferSize				16384
+#define PGCharset					"utf-8"
+#define PGUser						"micha"
+#define PGPassword					"micha"
+const TickType_t delay1ms = 1 / portTICK_PERIOD_MS;
+const TickType_t delay10ms = 10 / portTICK_PERIOD_MS;
+const TickType_t delay100ms = 100 / portTICK_PERIOD_MS;
+
+//static PGconnection *PGconn = NULL;
+static unsigned char PGbuffer[PGBufferSize];
+static char lastNotify[40];
+
+static const char *PGTAG = "SCALADIS-PG";
+
+static unsigned char pg_lastError_string[80];
+
+//TResultSet ResultSetObject(1);
+
+TpgConnection::TpgConnection()
+{
+   PGconn = NULL;
+}
+
+
+int TpgConnection::db_connect(const char *pg_server_ip, int pg_port, const char *s_db_name, const char *s_db_user, const char *s_db_passwd, const char *s_db_charset)
+{
+//CONNECTION_OK, CONNECTION_BAD, CONNECTION_NEEDED, /* setDbLogin() needed */
+//CONNECTION_AWAITING_RESPONSE, /* Waiting for a response from the postmaster.        */
+//	CONNECTION_AUTH_OK
+   int loop_fuse = 0; 
+   ESP_LOGI(PGTAG,"db_connect Enter");
+   PGconn = new PGconnection(pg_flags, PGbuffer, PGBufferSize); 
+   if (PGconn == NULL) { ESP_LOGE(PGTAG, "error creating PGconn object"); } 
+   
+   last_pgError = PGconn->setDbLogin(pg_server_ip, pg_port, s_db_name, s_db_user, s_db_passwd, s_db_charset);
+   
+   while ( (last_pgError != CONNECTION_OK) || (last_pgError != CONNECTION_BAD)
+    || (last_pgError != CONNECTION_NEEDED)  )
+   {
+       last_pgError = PGconn->status();
+       vTaskDelay(delay10ms); 
+       loop_fuse++;
+       if (loop_fuse > 100) { ESP_LOGE(PGTAG, "PGsetDbLogin break after 10 attempts. "); break; }
+   }     
+   
+   //ResultSetObject.Zeile.resize(1);
+      
+   switch (last_pgError) {
+	case CONNECTION_NEEDED:  { ESP_LOGE(PGTAG,"connection needed: %s", PGconn->getMessage()); return -1; }
+	case CONNECTION_OK:      { ESP_LOGI(PGTAG,"db_connect sucessful (OK) to db %s", s_db_name); return 0; }
+	case CONNECTION_BAD:     { ESP_LOGE(PGTAG,"ERROR: %s", PGconn->getMessage()); return -1; }
+	case CONNECTION_AWAITING_RESPONSE: { ESP_LOGI(PGTAG,"ERROR AWAITING_RESPONSE: %s", PGconn->getMessage()); return -1; }
+	case CONNECTION_AUTH_OK: { ESP_LOGI(PGTAG,"db_connect sucessful (AUTH_OK) to db %s", s_db_name); return 0; }
+   }
+  
+   
+   ESP_LOGI(PGTAG,"ResultSet created");
+   
+   ESP_LOGI(PGTAG,"db_connect EXIT. last_pgError=%d", last_pgError);
+   return 0;
+}
+
+int TpgConnection::db_disconnect(void)
+{
+   ESP_LOGI(PGTAG,"db_disconnect ENTER");
+   if (PGconn == NULL) { ESP_LOGE(PGTAG, "error destroy PGconn object. PGconn = NULL"); return -1;  }
+   PGconn->close();
+   delete PGconn;
+   PGconn = NULL;
+   ESP_LOGI(PGTAG,"db_disconnect EXIT"); 
+   return 0;
+}
+
+void TpgConnection::db_loop(void)
+{
+//     bool readyToQuery = false;
+     char *msg = NULL;
+     last_pgError = PGconn->status();
+     if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) 
+     {
+          ESP_LOGE(PGTAG, "ERROR: %s", PGconn->getMessage() );
+     } else
+     {
+        last_pgError = PGconn->getData();
+        if (last_pgError < 0) 
+        {
+            ESP_LOGE(PGTAG, "ERROR: %s", PGconn->getMessage() );
+        } else
+           {
+              if (last_pgError > 0)
+              {
+                //some data arrive
+                msg = PGconn->getMessage();
+                if (msg != NULL)
+                {
+                   sprintf( lastNotify, msg); //copy message, to save the NOTIFY
+                   ESP_LOGI(PGTAG,"NOTIFY %s", lastNotify);
+                }
+              }
+           }
+     } 
+}
+
+// ********************************************************************************************
+
+TResSetRows::TResSetRows() 
+{
+    Spalte.resize(1); 
+    //ESP_LOGE(PGTAG, "--------------------------> TResSetRows constructor called");
+}
+
+
+TResSetRows::~TResSetRows() 
+{
+    Spalte.resize(0);
+    //ESP_LOGE(PGTAG, "--------------------------> TResSetRows destructor called");
+}
+
+TResultSet::TResultSet(PGconnection *PGconnFromCon)
+{
+   PGconn = PGconnFromCon;
+   if (PGconnFromCon == NULL)
+   {
+      ESP_LOGE(PGTAG, "ERROR"); 
+      ESP_LOGE(PGTAG, "TResultSet::TResultSet(PGconnection *PGconnFromCon) -> PGconnFromCon ist NULL!!!!!!");
+      ESP_LOGE(PGTAG, ""); 
+   }
+   Zeile.resize(1);
+   NumRows = 0;
+   //ESP_LOGE(PGTAG, "--------------------------> TResultSet constructor called"); 
+}
+
+TResultSet::~TResultSet()
+{
+  //Speicher aufraeumen
+  ESP_LOGI(PGTAG,"\nTResultSet Destructor called"); //fflush (stdout);
+}
+
+int TResultSet::db_query(const char *sql)
+{
+   int last_pgError;
+   bool queryEnded;
+   
+//   ESP_LOGI(PGTAG,"db_query ENTER: %s", sql); fflush (stdout);
+    NumRows = 0;//reset NumRows.
+   
+   //sprintf(lbuf, "SELECT name,gname FROM accounts WHERE id=(cast(x'%s' AS int));", info);
+   last_pgError = PGconn->execute(sql);
+   if (last_pgError < 0)
+   {
+      printf("ERROR: TResultSet::db_query: PGexecute: %s", PGconn->getMessage());
+      return last_pgError;
+   } else
+   {
+        vTaskDelay(1);
+        queryEnded = false;
+        while (queryEnded == false)
+        {
+            last_pgError = PGconn->getData();
+	      if (last_pgError & PG_RSTAT_HAVE_COLUMNS) { process_db_col(); }
+	      if (last_pgError & PG_RSTAT_HAVE_ROW)     { process_db_row(); }
+	      if (last_pgError & PG_RSTAT_HAVE_SUMMARY) { process_db_summary();  }
+	      if (last_pgError & PG_RSTAT_HAVE_MESSAGE) { process_db_haveMsg();  }
+	      if (last_pgError & PG_RSTAT_READY)        { process_db_ready();  }
+	      if (last_pgError & PG_RSTAT_COMMAND_SENT) { process_db_cmd_send(); }
+	      if (last_pgError & PG_RSTAT_HAVE_ERROR)   { process_db_have_error(); 
+	        printf("\n Error from Postgres: ");
+	        if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); }
+	        fflush (stdout);
+	        queryEnded = true;  }
+	      if (last_pgError & PG_RSTAT_HAVE_NOTICE)  { process_db_have_notice(); }   
+	           
+	     if (last_pgError < 0) 
+	     {
+	        queryEnded = true;  
+	        printf("Get Data Error: %d\n", last_pgError );
+	        if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); }
+	        fflush (stdout);
+	     }  
+	     if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) 
+     	     {
+     	        queryEnded = true;
+     	        //seem to be the normal end. No Error Message. 
+                //ESP_LOGE(PGTAG, "ERROR: CONNECTION BAD or NEEDED:" );
+                //if ( PGconn->getMessage() != NULL ) { ESP_LOGE(PGTAG, "----> %s", PGconn->getMessage() ); }
+             }
+             if (last_pgError == 0) { queryEnded = true; //no more data }      
+        }
+
+
+       } //while (queryEnded == false)
+   }           
+//   ESP_LOGI(PGTAG,"db_query EXIT"); fflush (stdout);
+   return 0;  
+}
+
+int TResultSet::db_exec(const char *sql)
+{
+   int last_pgError;
+   bool queryEnded;
+   
+//   ESP_LOGI(PGTAG,"db_query ENTER: %s", sql); fflush (stdout);
+    NumRows = 0;//reset NumRows.
+   
+   last_pgError = PGconn->execute(sql);//call old version from 17.02.2022
+   if (last_pgError < 0)
+   {
+      return last_pgError;
+   } else
+   {
+        vTaskDelay(1);
+        queryEnded = false;
+        while (queryEnded == false)
+        {
+            last_pgError = PGconn->getData();    
+	      if (last_pgError & PG_RSTAT_HAVE_COLUMNS) { process_db_col();  }
+	      if (last_pgError & PG_RSTAT_HAVE_ROW)     { process_db_row(); }
+	      if (last_pgError & PG_RSTAT_HAVE_SUMMARY) { process_db_summary(); }
+	      if (last_pgError & PG_RSTAT_HAVE_MESSAGE) { process_db_haveMsg();  }
+	      if (last_pgError & PG_RSTAT_READY)        { process_db_ready();  }
+	      if (last_pgError & PG_RSTAT_COMMAND_SENT) { process_db_cmd_send();  }
+	      if (last_pgError & PG_RSTAT_HAVE_ERROR)   { 
+	        process_db_have_error(); 
+	        printf("\n Error from Postgres: ");
+	        if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); }
+	        fflush (stdout);
+	        queryEnded = true;  }
+	      if (last_pgError & PG_RSTAT_HAVE_NOTICE)  { process_db_have_notice(); }   
+	           
+	     if (last_pgError < 0) 
+	     {
+	        queryEnded = true;  
+	        printf("Insert/Update Error: %d\n", last_pgError );
+	        if ( PGconn->getMessage() != NULL ) { printf("---> %s\n", PGconn->getMessage() ); }
+	        fflush (stdout);
+	     }  
+	     if (last_pgError == CONNECTION_BAD || last_pgError == CONNECTION_NEEDED) 
+     	     {
+     	        queryEnded = true;
+     	        //seem to be the normal end. No Error Message. 
+                //ESP_LOGE(PGTAG, "ERROR: CONNECTION BAD or NEEDED:" );
+                //if ( PGconn->getMessage() != NULL ) { ESP_LOGE(PGTAG, "----> %s", PGconn->getMessage() ); }
+             }
+             if (last_pgError == 0) { queryEnded = true; //no more data }      
+        }
+
+
+       } //while (queryEnded == false)
+   }     
+   fflush (stdout);
+   return 0; 
+}
+
+
+void TResultSet::process_db_col(void)
+{
+        int nCols;
+        int iCurrCol;
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_COLS");
+	printf("Spalten gefunden: \n" );
+	fflush (stdout);	
+	
+	//iRow = 1; // erste Zeile hat die Spaltennamen!
+	CurrentRow = 1;
+	nCols = PGconn->nfields();
+	this->NumCols = nCols;
+	Zeile[0].numCols = nCols;
+		
+	Zeile.resize( 1 ); //erste Zeile
+	Zeile[0].Spalte.resize(nCols);
+//	printf("Set Result buffer auf Spalten: %d\n", nCols );
+	fflush (stdout);
+	//alloc 1 row only, for the column names. More is added later.
+	for (iCurrCol = 0; iCurrCol < nCols; iCurrCol++) 
+	{
+	   if (iCurrCol) printf(" | ");
+	   this->Zeile[0].Spalte[iCurrCol] = PGconn->getColumn(iCurrCol);
+//           std::cout <<  Zeile[0].Spalte[iCurrCol] ;		
+//           fflush (stdout);
+        }
+
+	printf("\n==========\n");
+        fflush(stdout);	
+}
+
+void TResultSet::process_db_row(void)
+{
+        //char *msg;
+        int iRow; 
+        int iCurrField;
+        std::string sFeld;
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_ROW");
+        this->CurrentRow++;
+        NumRows++;
+        iRow = this->CurrentRow;
+        this->Zeile.resize( iRow ); //naechste Zeile
+        this->Zeile[iRow-1].Spalte.resize( this->Zeile[0].numCols );
+        
+//        printf("Row %d:", iRow );
+        for (iCurrField = 0; iCurrField < PGconn->nfields(); iCurrField++) 
+        {
+              //printf(" | ");
+              //msg = PGconn->getValue( iCurrField );
+              //copy the string, to save the result
+              if (!PGconn->getValue( iCurrField ) ) 
+              { Zeile[iRow-1].Spalte[iCurrField].copy( (char *)"NULL", 0, 4); } 
+              else 
+              {
+                 sFeld = PGconn->getValue( iCurrField );
+                  Zeile[iRow-1].Spalte[iCurrField] = sFeld; //assign string to string
+              }
+              //printf(" %s", msg);fflush(stdout);
+//              std::cout <<  Zeile[iRow-1].Spalte[iCurrField] << " | " ;
+        } 	
+//        std::cout << "\n ";
+}
+
+
+
+
+
+void TResultSet::process_db_summary(void)
+{
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_SUMMARY");
+}
+
+void TResultSet::process_db_haveMsg(void)
+{
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_MESSAGE");
+}
+
+void TResultSet::process_db_ready(void)
+{
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_READY");
+}
+
+void TResultSet::process_db_cmd_send(void)
+{
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_COMMAND_SENT");
+}
+
+void TResultSet::process_db_have_error(void)
+{
+//	ESP_LOGE(PGTAG,"---> PG_RSTAT_HAVE_ERROR");
+}
+
+void TResultSet::process_db_have_notice(void)
+{
+//	ESP_LOGI(PGTAG,"---> PG_RSTAT_HAVE_NOTICE");
+}
+
+
+
+