Пример #1
0
static char *get_id(char *key, int *level){
	char *ret=NULL, *tmp;
	ret = SQLexec("id", key);
	if( !(tmp = SQLexec("level", key)) ) *level = 0;
	else *level = atoi(tmp);
	free(tmp);
	return ret;
}
Пример #2
0
/********************************************************************************************************************************
 * 		int SaveDataPaketContainerToDatabase(datapaketcontainer* dpc):
 * 			save dpc to database
 ********************************************************************************************************************************/
int SaveDataPaketContainerToDatabase(datapaketcontainer* dpc){
	//PGresult *res;
	int i;
	char st[CMDBUFFER_SIZE];
	PGresult *res;
	char dtStr[CMDBUFFER_SIZE];
	char adrStr[CMDBUFFER_SIZE];
  char log[LOG_BUF_LEN];
	int success = 0;

	if(SQLTableExists(LDB_TBL_NAME_DATA)){
		lielas_createDataTbl();
		if(SQLTableExists(LDB_TBL_NAME_DATA)){
			return -1;
		}
	}

  snprintf(log, LOG_BUF_LEN, "saving values to database");
	lielas_log((unsigned char*) log, LOG_LEVEL_DEBUG);
  
	// create column string
	//snprintf(column, BUFFER_SIZE, "%s.%s.%s", dpc->d->address, dpc->m->address, dpc->c->address);

	for( i = 0; i < dpc->datapakets; i++){

		//create column if not existing
		snprintf(adrStr, CMDBUFFER_SIZE, "%s.%s.%s", dpc->dp[i]->d->mac, dpc->dp[i]->m->address, dpc->dp[i]->c->address);
		if(SQLRowExists(LDB_TBL_NAME_DATA, adrStr)){
			snprintf(st, CMDBUFFER_SIZE, "ALTER TABLE %s.%s ADD COLUMN \"%s\" text", LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA , adrStr);
			res = SQLexec(st);
			PQclear(res);
		}

		// create datetime string
		strftime(dtStr, CMDBUFFER_SIZE, "%Y-%m-%d %H:%M:%S", dpc->dp[i]->dt);
    
		//check, if datetime-row exists
		if(SQLCellExists(LDB_TBL_NAME_DATA, "datetime", dtStr)){
			//creat new row
			snprintf(st, CMDBUFFER_SIZE, "INSERT INTO %s.%s ( datetime, \"%s\" ) VALUES ( '%s', '%s')", 
                LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA ,adrStr, dtStr, dpc->dp[i]->value);
			res = SQLexec(st);
			PQclear(res);
			success += 1;
		}else{
			snprintf(st, CMDBUFFER_SIZE, "UPDATE %s.%s SET \"%s\"='%s' WHERE datetime='%s'", 
                LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA ,adrStr, dpc->dp[i]->value, dtStr );
			res = SQLexec(st);
			PQclear(res);
			success += 1;
		}
	}

  snprintf(log, LOG_BUF_LEN, "%i values successfully saved", success);
	lielas_log((unsigned char*) log, LOG_LEVEL_DEBUG);

	return 0;
}
Пример #3
0
/********************************************************************************************************************************
 * 		int getDeviceData(Ldevice *d): get device data and save it to database
 ********************************************************************************************************************************/
int getDeviceData(Ldevice *d, datapaketcontainer *dpc){
	int i;
	time_t rawtime;
	struct tm *now;
	struct tm timeout;
	struct tm dt;
	unsigned char buf[DATABUFFER_SIZE] = { 0 };
	char datetimestr[CMDBUFFER_SIZE];
	char st[CMDBUFFER_SIZE];
	char cmd[CMDBUFFER_SIZE];
	char adrStr[CMDBUFFER_SIZE];
  char log[LOG_BUF_LEN];
	coap_buf *cb;
	int cnr;
	datapaket *dp;
	PGresult *res;
  int pos = 0;
  int eof = 0;
  int datasets = 0;
  

	dpc->datapakets = 0;
	dpc->dec = 0;
	dpc->d = d;


  lielas_log((unsigned char*) "get device data", LOG_LEVEL_DEBUG);

	cb = coap_create_buf();
	if( cb == NULL){
		printf("Error: cannot create coap_buf\n");
		return -1;
	}
	cb->buf = (char*)buf;
  cb->bufSize = DATABUFFER_SIZE;
  
	time(&rawtime);
	now = gmtime(&rawtime);
	memcpy(&timeout, now, sizeof(struct tm));
	timeout.tm_sec += MAX_DATA_SEND_TIME;
	mktime(&timeout);



	// get last value in database and create coap get command
	snprintf(adrStr, DATABUFFER_SIZE, "%s.1.1", d->mac);
	if(SQLTableExists(LDB_TBL_NAME_DATA) == 0){  
      
    //Table exists, check if column exists
    if(SQLColumnExists(LDB_TBL_NAME_DATA, adrStr)){ //column does not exist, get all data
			snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
			coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
      
    }else{ // column exists, get new data
      snprintf(st, DATABUFFER_SIZE, 
                  "SELECT datetime \"%s\" FROM %s.%s WHERE \"%s\" NOT LIKE '' ORDER BY datetime DESC LIMIT 1", 
                  adrStr, LDB_TBL_SCHEMA, LDB_TBL_NAME_DATA , adrStr);
      res = SQLexec(st);
      if(PQntuples(res) == 1){
        strcpy(datetimestr, PQgetvalue(res, 0, 0));
        for(i = 0; i < strlen(datetimestr); i++){
          if(datetimestr[i] == '-'){
            datetimestr[i] = '.';
          }
        }
        for(i = 0; i < strlen(datetimestr); i++){
          if(datetimestr[i] == ' '){
            datetimestr[i] = '-';
          }
        }
        snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, datetimestr);
        coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
        snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
        lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
      }else{
        snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
        coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
        snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
        lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
      }
      PQclear(res);
    }
	}else{
		lielas_createDataTbl();
		if(SQLTableExists(LDB_TBL_NAME_DATA)){
			return -1;
		}
		snprintf(cmd, DATABUFFER_SIZE, "coap://[%s]:5683/database?datetime=%s", d->address, GET_FIRST_VALUE_DATE);
		coap_send_cmd(cmd, cb, MYCOAP_METHOD_GET, NULL);
    snprintf(log, LOG_BUF_LEN, "sended get data cmd: %s", cmd);
    lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
	}

	if(cb->status != COAP_STATUS_CONTENT){
    lielas_log((unsigned char*) "Status error getting data", LOG_LEVEL_DEBUG);
		return -1;
	}


  //parse data
  while(pos < cb->len && !eof){

    //parse date
    lwp_compdt_to_struct_tm((unsigned char*)&cb->buf[pos], &dt);
    mktime(&dt);
    
    
    snprintf(log, LOG_BUF_LEN, "found datetime: %x:%x:%x:%x ... ", (unsigned char)cb->buf[pos], (unsigned char)cb->buf[pos+1], (unsigned char)cb->buf[pos+2], (unsigned char)cb->buf[pos+3]);
    strftime(&log[strlen(log)], LOG_BUF_LEN, "%d.%m.%Y %H:%M:%S",&dt);
    lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
    
    pos += 4;
      
    //test date
    if(testDatetime(&dt)){
      
      
      //parse values
      for(cnr = 1; cnr <= d->modul[1]->channels; cnr++){
				dp =CreateDatapaket();
				if(dp == NULL)
					break;
				dp->d = d;
				dp->m = d->modul[1];
				dp->c = d->modul[1]->channel[cnr];
        
        convertValueWithExponent(&cb->buf[pos], dp->value, VALUEBUFFER_SIZE, dp->c->exponent);
        pos += 2;
        
				memcpy(dp->dt, &dt, sizeof(struct tm));
				dpc->dp[dpc->datapakets] = dp;
				dpc->datapakets += 1;
        
        
        snprintf(log, LOG_BUF_LEN, "found value: %x:%x ... %s", (unsigned char)cb->buf[pos-2], (unsigned char)cb->buf[pos-1], dp->value);
        lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
        
        datasets += 1;
        if(datasets >= MAX_VALUES_IN_PAKET)
          break;
      }
    }else{
      lielas_log((unsigned char*) "error parsing log data, invalid datetime ", LOG_LEVEL_WARN);
      eof = 1;
      break;
    }
 
  }
  snprintf(log, LOG_BUF_LEN, "received %i values", datasets);
  lielas_log((unsigned char*)log, LOG_LEVEL_DEBUG);
  
	return 0;
}