1 // Copyright (c) 2017, Tobias Mueller tm(at)tm3d.de
2 // All rights reserved.
4 // Redistribution and use in source and binary forms, with or without
5 // modification, are permitted provided that the following conditions are
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
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.
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.
34 #include "mySensorDB.h"
39 mySensorDB::mySensorDB(std::string configfile, owLog* log) {
43 readConfigFile(configfile);
44 if (log->last()<OWLOG_ERROR) {
45 my = mysql_init(NULL);
47 log->set(OWLOG_ERROR,"ERROR: Could not initialize Mysql");
52 int mySensorDB::readConfigFile(std::string configfile) {
53 std::ifstream dbc(configfile);
55 log->set(OWLOG_ERROR,"Error reading database config file %s",configfile.c_str());
60 while (std::getline(dbc, line)) {
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;
73 log->set(OWLOG_ERROR,"ERROR: Database config file isn't correct. (not 6 lines)\n");
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) {
91 log->set(OWLOG_ERROR,"ERROR mysql_real_connect(): %u (%s)\n",mysql_errno (my), mysql_error (my));
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;";
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));
128 int mySensorDB::createDeviceTable(owDevice *dev) {
129 if (dev->config->valid) {
132 s="SELECT sid FROM "+mypre+"sensors WHERE sid='";
133 sprintf(hs,"%016llX",(unsigned long long)dev->getNum().num);
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));
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");
146 if (mysql_num_rows(result)==0) {
147 log->set(OWLOG_INFO,"Sensor not in Database\n");
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) {
153 cols+=std::string(hs)+"_"+std::to_string(i+1);
154 cols+="` double DEFAULT NULL ";
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));
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));
177 //int num_fields=mysql_num_fields(result)
179 //while ((row = mysql_fetch_row(result))) {
180 // for(int i = 0; i < num_fields; i++) {
181 // printf("%s ", row[i] ? row[i] : "NULL");
185 mysql_free_result(result);
190 int mySensorDB::insertValues(owDevice *dev) {
191 if (dev->config->valid) {
193 sprintf(hs,"%016llX",(unsigned long long)dev->getNum().num);
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();
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();
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));