src/mySensorDB.cpp

   1 // Copyright (c) 2017, Tobias Mueller tm(at)tm3d.de
   2 // All rights reserved.
   3 //
   4 // Redistribution and use in source and binary forms, with or without
   5 // modification, are permitted provided that the following conditions are
   6 // met:
   7 //
   8 //  * Redistributions of source code must retain the above copyright
   9 //    notice, this list of conditions and the following disclaimer.
  10 //  * Redistributions in binary form must reproduce the above copyright
  11 //    notice, this list of conditions and the following disclaimer in the
  12 //    documentation and/or other materials provided with the
  13 //    distribution.
  14 //  * All advertising materials mentioning features or use of this
  15 //    software must display the following acknowledgement: This product
  16 //    includes software developed by tm3d.de and its contributors.
  17 //  * Neither the name of tm3d.de nor the names of its contributors may
  18 //    be used to endorse or promote products derived from this software
  19 //    without specific prior written permission.
  20 //
  21 // THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  22 // "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  23 // LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  24 // A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  25 // OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  26 // SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  27 // LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  28 // DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  29 // THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  30 // (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  31 // OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  32
  33
  34 #include "mySensorDB.h"
  35 #include <iostream>
  36 #include <algorithm>
  37 #include <fstream>
  38 #include <sstream>
  39 mySensorDB::mySensorDB(std::string configfile, owLog* log) {
  40         this->log=log;
  41         log->clear();
  42         my=0;
  43         readConfigFile(configfile);
  44         if (log->last()<OWLOG_ERROR) {
  45                 my = mysql_init(NULL);
  46                 if (my == NULL) {
  47                         log->set(OWLOG_ERROR,"ERROR: Could not initialize Mysql");
  48                 }
  49         }
  50 }
  51
  52 int mySensorDB::readConfigFile(std::string configfile) {
  53         std::ifstream dbc(configfile);
  54         if (!dbc) {
  55                 log->set(OWLOG_ERROR,"Error reading database config file %s",configfile.c_str());
  56                 return -1;
  57         }
  58         int lc=0;
  59         std::string line;
  60         while (std::getline(dbc, line)) {
  61                 switch (lc) {
  62                         case 0:myserver=line;break;
  63                         case 1:myport=line;break;
  64                         case 2:myuser=line;break;
  65                         case 3:mypass=line;break;
  66                         case 4:mydb=line;break;
  67                         case 5:mypre=line;break;
  68                 }
  69                 lc++;
  70                 if (lc==6) break;
  71         }
  72         if (lc<6) {
  73                 log->set(OWLOG_ERROR,"ERROR: Database config file isn't correct. (not 6 lines)\n");
  74                 return -2;
  75         }
  76         return 0;
  77 }
  78
  79
  80 int mySensorDB::connect() {
  81         if( mysql_real_connect (
  82                         my,   /* Zeiger auf MYSQL-Handler */
  83                         myserver.c_str(), /* Host-Name */
  84                         myuser.c_str(), /* User-Name */
  85                         mypass.c_str(), /* Passwort für user_name */
  86                         mydb.c_str(),  /* Name der Datenbank */
  87                         atol(myport.c_str()),     /* Port (default=0) */
  88                         NULL,  /* Socket (default=NULL) */
  89                         0      /* keine Flags */  )  == NULL) {
  90
  91                 log->set(OWLOG_ERROR,"ERROR mysql_real_connect(): %u (%s)\n",mysql_errno (my), mysql_error (my));
  92                 return -1;
  93         }
  94         printf("DB ok\n");
  95         return 0;
  96 }
  97
  98 int mySensorDB::createSensorTable() {
  99         std::string query="CREATE TABLE IF NOT EXISTS `"+mypre+"sensors` ("
 100                                                         "  `sid` varchar(20) NOT NULL COMMENT 'owID + number of 1 to 4' ,"
 101                                                          " `sdescription` varchar(128) DEFAULT NULL,"
 102                                                          " `schip` varchar(120) DEFAULT NULL COMMENT 'Chip of mesasurement',"
 103                                                          " `smaxval` double DEFAULT NULL COMMENT 'Maximal value',"
 104                                                         "  `sminval` double DEFAULT NULL COMMENT 'Minimal value',"
 105                                                          " `slocation` varchar(60) DEFAULT NULL COMMENT 'Position of Sensor, if needed',"
 106                                                          " `stype` varchar(40) DEFAULT NULL COMMENT 'Name of pysical property',"
 107                                                          " `sunit` varchar(20) DEFAULT NULL COMMENT 'Physical unit',"
 108                                                          " `sprecision` double DEFAULT NULL COMMENT '+- precission',"
 109                                                          " `sprecisionlong` text DEFAULT NULL COMMENT 'a table of specific precission values',"
 110                                                          " `sfirstuse` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
 111                                                          " `scalib_m` double DEFAULT '0' COMMENT 'an optional addition value',"
 112                                                          " `scalib_n` double DEFAULT '1' COMMENT 'an optimal multip. value', "
 113                                                          " `shidden` tinyint(1) DEFAULT '0' COMMENT 'for automatic presentation',"
 114                                                          " `sformelcode` tinyint(1) DEFAULT '0' COMMENT 'code of the formel',"
 115                                                          " `stypecode` tinyint(1) DEFAULT '0' COMMENT 'code of the property',"
 116                                                          " PRIMARY KEY (`sid`)"
 117                                                         ") DEFAULT CHARSET=utf8;";
 118
 119         if (mysql_query(my,query.c_str()        ))      {
 120                 log->set(OWLOG_ERROR, "ERROR mysql_query: %u (%s)\n",mysql_errno (my), mysql_error (my));
 121                 return -1;
 122         }
 123         return 0;
 124 }
 125
 126
 127
 128 int mySensorDB::createDeviceTable(owDevice *dev) {
 129         if (dev->config->valid) {
 130                 std::string s;
 131                 char hs[50];
 132                 s="SELECT sid FROM "+mypre+"sensors WHERE sid='";
 133                 sprintf(hs,"%016llX",(unsigned long long)dev->getNum().num);
 134                 s=s+hs+"_1';";
 135                 if (mysql_query(my,s.c_str())) {
 136                         //printf("ERROR communicating Database\n");
 137                         log->set(OWLOG_ERROR,"ERROR mysql_query: %u (%s)\n",mysql_errno (my), mysql_error (my));
 138                         return -1;
 139                 }
 140                 MYSQL_RES *result = mysql_store_result(my);
 141                 if (result == NULL)  {
 142                         //finish_with_error(my);
 143                         log->set(OWLOG_ERROR,"ERROR get result from Database\n");
 144                         return -1;
 145                 }
 146                 if (mysql_num_rows(result)==0) {
 147                         log->set(OWLOG_INFO,"Sensor not in Database\n");
 148                         std::string cols;
 149                         cols="CREATE TABLE IF NOT EXISTS `"+mypre+"OW"+std::string(hs)+"` (`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ";
 150                         for (size_t i=0;i<4;i++)  {
 151                                 if (dev->config->getPropertyID(i)!=0) {
 152                                         cols+=", `";
 153                                         cols+=std::string(hs)+"_"+std::to_string(i+1);
 154                                         cols+="` double DEFAULT NULL ";
 155                                         std::string query;
 156                                         query="INSERT INTO "+mypre+"sensors (sid,schip,stype,sunit,sformelcode,stypecode) VALUES ('"+std::string(hs)+"_"+std::to_string(i+1)+"' ";
 157                                         query+=", '"+dev->config->getSensorChip(i)+"' , '";
 158                                         query+=dev->config->getQuantity(i)+"' , '";
 159                                         query+=dev->config->getUnit(i)+"' ," ;
 160                                         query+=std::to_string(dev->config->getFormulaID(i))+" ,";
 161                                         query+=std::to_string(dev->config->getPropertyID(i))+");";
 162                                         //printf("%s\n",query.c_str());
 163                                         if (mysql_query(my,query.c_str())) {
 164                                                 log->set(OWLOG_ERROR, "ERROR mysql_query: %u (%s)\n",mysql_errno (my), mysql_error (my));
 165                                                 return -1;
 166                                         }
 167                                 }
 168                         }
 169                         cols+=", PRIMARY KEY (`time`) ) DEFAULT CHARSET=utf8;";
 170                         //printf("%s\n",cols.c_str());
 171                         if (mysql_query(my,cols.c_str())) {
 172                         log->set(OWLOG_ERROR, "ERROR mysql_query: %u (%s)\n",mysql_errno (my), mysql_error (my));
 173                                 return -1;
 174                         }
 175
 176                 }
 177                                 //int num_fields=mysql_num_fields(result)
 178                                 //MYSQL_ROW row;
 179                                 //while ((row = mysql_fetch_row(result))) {
 180                                 //  for(int i = 0; i < num_fields; i++) {
 181                                 //              printf("%s ", row[i] ? row[i] : "NULL");
 182                                 //      }
 183                                 //      printf("\n");
 184                                 //}
 185                 mysql_free_result(result);
 186         }
 187         return 0;
 188 }
 189
 190 int mySensorDB::insertValues(owDevice *dev) {
 191         if (dev->config->valid) {
 192                 char hs[50];
 193                 sprintf(hs,"%016llX",(unsigned long long)dev->getNum().num);
 194                 std::string query;
 195                 query="INSERT INTO `"+mypre+"OW"+std::string(hs)+"` (";
 196                 for (size_t i=0;i<4;i++) if (dev->config->getPropertyID(i)!=0) query+=std::string(hs)+"_"+std::to_string(i+1)+", ";
 197                 query.pop_back();query.pop_back();
 198                 query+=") VALUES (";
 199                 for (size_t i=0;i<4;i++) if (dev->config->getPropertyID(i)!=0) query+=std::to_string(dev->values[i])+", ";
 200                 query.pop_back();query.pop_back();
 201                 query+=");";
 202                 //printf("%s\n",query.c_str());
 203                 if (mysql_query(my,query.c_str())) {
 204                         log->set(OWLOG_ERROR, "ERROR mysql_query: %u (%s)\n",mysql_errno (my), mysql_error (my));
 205                         return -1;
 206                 }
 207         }
 208
 209         return 0;
 210 }