#include "rrd_tool.h"
+#include "unused.h"
#include <dbi/dbi.h>
#include <time.h>
};
/* the prototypes */
-int _sql_setparam(struct sql_table_helper* th,char* key, char* value);
-int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t *value);
-char* _find_next_separator(char* start,char separator);
-char* _find_next_separator_twice(char*start,char separator);
-char _hexcharhelper(char c);
-int _inline_unescape (char* string);
-double rrd_fetch_dbi_double(dbi_result *result,int idx);
-long rrd_fetch_dbi_long(dbi_result *result,int idx);
+static void _sql_close(struct sql_table_helper* th);
+static int _sql_setparam(struct sql_table_helper* th,char* key, char* value);
+static int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t *value,int ordered);
+static char* _find_next_separator(char* start,char separator);
+static char* _find_next_separator_twice(char*start,char separator);
+static char _hexcharhelper(char c);
+static int _inline_unescape (char* string);
+static double rrd_fetch_dbi_double(dbi_result *result,int idx);
+static long rrd_fetch_dbi_long(dbi_result *result,int idx);
/* the real code */
/* helpers to get correctly converted values from DB*/
-long rrd_fetch_dbi_long(dbi_result *result,int idx) {
+static long rrd_fetch_dbi_long(dbi_result *result,int idx) {
char *ptmp="";
- switch (dbi_result_get_field_type_idx(result,idx)) {
- case DBI_TYPE_STRING:
- ptmp=(char*)dbi_result_get_string_idx(result,idx);
- return atoi(ptmp);
- default:
- return dbi_result_get_longlong_idx(result,idx);
+ long value=DNAN;
+ /* get the attributes for this filed */
+ unsigned int attr=dbi_result_get_field_attribs_idx(result,idx);
+ unsigned int type=dbi_result_get_field_type_idx(result,idx);
+ /* return NAN if NULL */
+ if(dbi_result_field_is_null_idx(result,idx)) { return DNAN; }
+ /* do some conversions */
+ switch (type) {
+ case DBI_TYPE_STRING:
+ ptmp=(char*)dbi_result_get_string_idx(result,idx);
+ value=atoi(ptmp);
+ break;
+ case DBI_TYPE_INTEGER:
+ if (attr & DBI_INTEGER_SIZE1) { value=dbi_result_get_char_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE2) { value=dbi_result_get_short_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE3) { value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE4) { value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE8) { value=dbi_result_get_longlong_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported attribute flags %i for type INTEGER\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_DECIMAL:
+ if (attr & DBI_DECIMAL_SIZE4) { value=floor(dbi_result_get_float_idx(result,idx));
+ } else if (attr & DBI_DECIMAL_SIZE8) { value=floor(dbi_result_get_double_idx(result,idx));
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported attribute flags %i for type DECIMAL\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_BINARY:
+ attr=dbi_result_get_field_length_idx(result,idx);
+ ptmp=(char*)dbi_result_get_binary_copy_idx(result,idx);
+ ptmp[attr-1]=0;
+ /* check for "known" libdbi error */
+ if (strncmp("ERROR",ptmp,5)==0) {
+ if (!getenv("RRD_NO_LIBDBI_BUG_WARNING")) {
+ fprintf(stderr,"rrdtool_fetch_libDBI: you have possibly triggered a bug in libDBI by using a (TINY,MEDIUM,LONG) TEXT field with mysql\n this may trigger a core dump in at least one version of libdbi\n if you are not touched by this bug and you find this message annoying\n please set the environment-variable RRD_NO_LIBDBI_BUG_WARNING to ignore this message\n");
+ }
+ }
+ /* convert to number */
+ value=atoi(ptmp);
+ /* free pointer */
+ free(ptmp);
+ break;
+ case DBI_TYPE_DATETIME:
+ value=dbi_result_get_datetime_idx(result,idx);
+ break;
+ default:
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported type: %i with attribute %i\n",time(NULL),idx,type,attr ); }
+ value=DNAN;
+ break;
}
+ return value;
}
-double rrd_fetch_dbi_double(dbi_result *result,int idx) {
+static double rrd_fetch_dbi_double(dbi_result *result,int idx) {
char *ptmp="";
+ double value=DNAN;
+ /* get the attributes for this filed */
+ unsigned int attr=dbi_result_get_field_attribs_idx(result,idx);
+ unsigned int type=dbi_result_get_field_type_idx(result,idx);
/* return NAN if NULL */
if(dbi_result_field_is_null_idx(result,idx)) { return DNAN; }
/* do some conversions */
- switch (dbi_result_get_field_type_idx(result,idx)) {
- case DBI_TYPE_STRING:
- ptmp=(char*)dbi_result_get_string_idx(result,idx);
- return strtod(ptmp,NULL);
- default:
- return dbi_result_get_double_idx(result,idx);
+ switch (type) {
+ case DBI_TYPE_STRING:
+ ptmp=(char*)dbi_result_get_string_idx(result,idx);
+ value=strtod(ptmp,NULL);
+ break;
+ case DBI_TYPE_INTEGER:
+ if (attr & DBI_INTEGER_SIZE1) { value=dbi_result_get_char_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE2) { value=dbi_result_get_short_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE3) { value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE4) { value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE8) { value=dbi_result_get_longlong_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported attribute flags %i for type INTEGER\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_DECIMAL:
+ if (attr & DBI_DECIMAL_SIZE4) { value=dbi_result_get_float_idx(result,idx);
+ } else if (attr & DBI_DECIMAL_SIZE8) { value=dbi_result_get_double_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported attribute flags %i for type DECIMAL\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_BINARY:
+ attr=dbi_result_get_field_length_idx(result,idx);
+ ptmp=(char*)dbi_result_get_binary_copy_idx(result,idx);
+ ptmp[attr-1]=0;
+ /* check for "known" libdbi error */
+ if (strncmp("ERROR",ptmp,5)==0) {
+ if (!getenv("RRD_NO_LIBDBI_BUG_WARNING")) {
+ fprintf(stderr,"rrdtool_fetch_libDBI: you have possibly triggered a bug in libDBI by using a (TINY,MEDIUM,LONG) TEXT field with mysql\n this may trigger a core dump in at least one version of libdbi\n if you are not touched by this bug and you find this message annoying\n please set the environment-variable RRD_NO_LIBDBI_BUG_WARNING to ignore this message\n");
+ }
+ }
+ /* convert to number */
+ value=strtod(ptmp,NULL);
+ /* free pointer */
+ free(ptmp);
+ break;
+ case DBI_TYPE_DATETIME:
+ value=dbi_result_get_datetime_idx(result,idx);
+ break;
+ default:
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i unsupported type: %i with attribute %i\n",time(NULL),idx,type,attr ); }
+ value=DNAN;
+ break;
}
+ return value;
}
-int _sql_close(struct sql_table_helper* th) {
+static void _sql_close(struct sql_table_helper* th) {
/* close only if connected */
if (th->conn) {
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: close connection\n",time(NULL) ); }
/* shutdown dbi */
dbi_conn_close(th->conn);
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: shutting down libdbi\n",time(NULL) ); }
dbi_shutdown();
/* and assign empty */
th->conn=NULL;
+ th->connected=0;
}
}
-int _sql_setparam(struct sql_table_helper* th,char* key, char* value) {
+static int _sql_setparam(struct sql_table_helper* th,char* key, char* value) {
char* dbi_errstr=NULL;
+ dbi_driver driver;
/* if not connected */
if (! th->conn) {
/* initialize some stuff */
th->result=NULL;
th->connected=0;
/* initialize db */
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: initialize libDBI\n",time(NULL) ); }
dbi_initialize(NULL);
- th->conn=dbi_conn_new(th->dbdriver);
+ /* load the driver */
+ driver=dbi_driver_open(th->dbdriver);
+ if (! driver) {
+ rrd_set_error( "libdbi - no such driver: %s (possibly a dynamic link problem of the driver being linked without -ldbi)",th->dbdriver);
+ return -1;
+ }
+ /* and connect to driver */
+ th->conn=dbi_conn_open(driver);
/* and handle errors */
if (! th->conn) {
- dbi_conn_error(th->conn,(const char**)&dbi_errstr);
- rrd_set_error( "libdbi - no such driver: %s (possibly a dynamic link problem of the driver being linked without -ldbi)",dbi_errstr);
+ rrd_set_error( "libdbi - could not open connection to driver %s",th->dbdriver);
dbi_shutdown();
- return -1;
+ return -1;
}
}
if (th->connected) {
_sql_close(th);
return -1;
}
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: setting option %s to %s\n",time(NULL),key,value ); }
if (dbi_conn_set_option(th->conn,key,value)) {
dbi_conn_error(th->conn,(const char**)&dbi_errstr);
rrd_set_error( "libdbi: problems setting %s to %s - %s",key,value,dbi_errstr);
return 0;
}
-int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t *value) {
+static int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t *value,int ordered) {
char* dbi_errstr=NULL;
char sql[10240];
time_t startt=0,endt=0;
}
if (! th->connected) {
/* and now connect */
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: connect to DB\n",time(NULL) ); }
if (dbi_conn_connect(th->conn) <0) {
dbi_conn_error(th->conn,(const char**)&dbi_errstr);
rrd_set_error( "libdbi: problems connecting to db with connect string %s - error: %s",th->filename,dbi_errstr);
if (! th->result) {
/* return if table_next is NULL */
if (th->table_next==NULL) {
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: reached last table to connect to\n",time(NULL) ); }
/* but first close connection */
_sql_close(th);
/* and return with end of data */
th->table_next=_find_next_separator(th->table_start,'+');
_inline_unescape(th->table_start);
/* and prepare FULL SQL Statement */
- snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM %s WHERE %s GROUP BY rrd_time",
- th->timestamp,th->value,th->table_start,th->where);
+ if (ordered) {
+ snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM %s WHERE %s ORDER BY %s",
+ th->timestamp,th->value,th->table_start,th->where,th->timestamp);
+ } else {
+ snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM %s WHERE %s",
+ th->timestamp,th->value,th->table_start,th->where);
+ }
/* and execute sql */
if (getenv("RRDDEBUGSQL")) { startt=time(NULL); fprintf(stderr,"RRDDEBUGSQL: %li: executing %s\n",startt,sql); }
th->result=dbi_conn_query(th->conn,sql);
dbi_result_free(th->result);
th->result=NULL;
/* and call recursively - this will open the next table or close connection as a whole*/
- return _sql_fetchrow(th,timestamp,value);
+ return _sql_fetchrow(th,timestamp,value,ordered);
}
/* and return with flag for one value */
*timestamp=rrd_fetch_dbi_long(th->result,1);
return 1;
}
-char* _find_next_separator(char* start,char separator) {
+static char* _find_next_separator(char* start,char separator) {
char* found=strchr(start,separator);
/* have we found it */
if (found) {
return NULL;
}
-char* _find_next_separator_twice(char*start,char separator) {
+static char* _find_next_separator_twice(char*start,char separator) {
char *found=start;
/* find next separator in string*/
while (found) {
return NULL;
}
-char _hexcharhelper(char c) {
+static char _hexcharhelper(char c) {
switch (c) {
case '0': return 0 ; break;
case '1': return 1 ; break;
return -1;
}
-int _inline_unescape (char* string) {
+static int _inline_unescape (char* string) {
char *src=string;
char *dst=string;
char c,h1,h2;
} else {
/* try to calculate hex value from the next 2 values*/
h1=_hexcharhelper(*src);
- if (h1<0) { rrd_set_error( "string escape error at: %s\n",string);return(1); }
+ if (h1 == (char)-1) {
+ rrd_set_error("string escape error at: %s\n",string);
+ return(1);
+ }
h2=_hexcharhelper(*(src+1));
- if (h2<0) { rrd_set_error( "string escape error at: %s\n",string);return(1); }
+ if (h1 == (char)-1) {
+ rrd_set_error("string escape error at: %s\n",string);
+ return(1);
+ }
c=h2+(h1<<4);
/* increase src pointer by 2 skiping 2 chars */
src+=2;
int
rrd_fetch_fn_libdbi(
- char *filename, /* name of the rrd */
- enum cf_en cf_idx, /* which consolidation function ?*/
+ const char *filename, /* name of the rrd */
+ enum cf_en UNUSED(cf_idx), /* consolidation function */
time_t *start,
time_t *end, /* which time frame do you want ?
* will be changed to represent reality */
/* the settings for the "works" of rrd */
int fillmissing=0;
unsigned long minstepsize=300;
+ /* by default assume unixtimestamp */
+ int isunixtime=1;
+ long gmt_offset=0;
/* the result-set */
long r_timestamp,l_timestamp,d_timestamp;
double r_value,l_value,d_value;
char where[10240];
table_help.conn=NULL;
table_help.where=where;
+ table_help.filename=filename;
/* some loop variables */
int i=0;
rrd_set_error( "formatstring wrong - %s",tmpptr);
return -1;
}
+ /* if we have leading '*', then we have a TIMEDATE Field*/
+ if (table_help.timestamp[0]=='*') {
+ struct tm tm;
+ time_t t=time(NULL);
+ localtime_r(&t,&tm);
+ gmt_offset=tm.tm_gmtoff;
+ isunixtime=0; table_help.timestamp++;
+ }
/* hex-unescape the value */
if(_inline_unescape(table_help.timestamp)) { return -1; }
if (strcmp(sqlargs,"derive")==0) { /* the derive option with the default allowed max delta */
derive=600;
} else if (strcmp(sqlargs,"prediction")==0) {
- rrd_set_error("argument prediction is no longer supported in a VDEF - use new generic CDEF-functions instead");
+ rrd_set_error("argument prediction is no longer supported in a DEF - use new generic CDEF-functions instead");
return -1;
} else if (strcmp(sqlargs,"sigma")==0) {
- rrd_set_error("argument sigma is no longer supported in a VDEF - use new generic CDEF-functions instead");
+ rrd_set_error("argument sigma is no longer supported in a DEF - use new generic CDEF-functions instead");
return -1;
} else if (*sqlargs==0) { /* ignore empty */
} else { /* else add to where string */
i=atoi(tmpptr);if (i>0) { minstepsize=i; }
} else if (strcmp(libdbiargs,"rrdfillmissing")==0) { /* allow override for minstepsize */
i=atoi(tmpptr);if (i>0) { fillmissing=i; }
- } else if (strcmp(libdbiargs,"rrdderivemaxstep")==0) { /* allow override for minstepsize */
- i=atoi(tmpptr);if (i>0) { derive=i; }
+ } else if (strcmp(libdbiargs,"rrdderivemaxstep")==0) { /* allow override for derived max delta */
+ i=atoi(tmpptr);if (i>0) { if (derive) { derive=i; }}
} else { /* store in libdbi, as these are parameters */
- if (_sql_setparam(&table_help,libdbiargs,tmpptr)) {
- _sql_close(&table_help);
- return -1;
- }
+ if (_sql_setparam(&table_help,libdbiargs,tmpptr)) { return -1; }
}
/* and continue loop with next pointer */
libdbiargs=nextptr;
/* and append the SQL WHERE Clause for the timeframe calculated above (adding AND if required) */
if (where[0]) {strcat(where," AND ");}
i=strlen(where);
- snprintf(where+i,sizeof(where)-1-i,"%li < %s AND %s < %li",*start,table_help.timestamp,table_help.timestamp,*end);
+ if (isunixtime) {
+ snprintf(where+i,sizeof(where)-1-i,"%li < %s AND %s < %li",*start,table_help.timestamp,table_help.timestamp,*end);
+ } else {
+ char tsstart[64];strftime(tsstart,sizeof(tsstart),"%Y-%m-%d %H:%M:%S",localtime(start));
+ char tsend[64];strftime(tsend,sizeof(tsend),"%Y-%m-%d %H:%M:%S",localtime(end));
+ snprintf(where+i,sizeof(where)-1-i,"'%s' < %s AND %s < '%s'",tsstart,table_help.timestamp,table_help.timestamp,tsend);
+ }
/* and now calculate the number of rows in the resultset... */
rows=((*end)-(*start))/(*step)+2;
}
/* allocate memory for resultset (with the following columns: min,avg,max,count,sigma) */
- i=rows * sizeof(rrd_value_t)*(*ds_cnt);
+ i=(rows+1) * sizeof(rrd_value_t)*(*ds_cnt);
if (((*data) = malloc(i))==NULL){
/* and return error */
rrd_set_error("malloc failed for %i bytes",i);
/* and assign undefined values for last - in case of derived calculation */
l_value=DNAN;l_timestamp=0;
/* here goes the real work processing all data */
- while((r_status=_sql_fetchrow(&table_help,&r_timestamp,&r_value))>0) {
+ while((r_status=_sql_fetchrow(&table_help,&r_timestamp,&r_value,derive))>0) {
/* processing of value */
/* calculate index for the timestamp */
+ r_timestamp-=gmt_offset;
idx=(r_timestamp-(*start))/(*step);
/* some out of bounds checks on idx */
if (idx<0) { idx=0;}
(*data)[idx*(*ds_cnt)+1]=r_value; /* AVG */
(*data)[idx*(*ds_cnt)+2]=r_value; /* MAX */
(*data)[idx*(*ds_cnt)+3]=1; /* COUNT */
- (*data)[idx*(*ds_cnt)+4]=r_value; /* SIGMA */
+ (*data)[idx*(*ds_cnt)+4]=r_value*r_value; /* SIGMA */
} else {
/* MIN */
if ((*data)[idx*(*ds_cnt)+0]>r_value) { (*data)[idx*(*ds_cnt)+0]=r_value; }
}
(*data)[idx*(*ds_cnt)+4]=r_value;
/* now the average */
- (*data)[idx*(*ds_cnt)+1]/=i;
+ (*data)[idx*(*ds_cnt)+1]/=count;
+ }
+ }
+
+ /* Fill in missing values */
+ fillmissing/=(*step);/* Convert from seconds to steps */
+ if (fillmissing>0) {
+ int copy_left=fillmissing;
+ for(idx=1;idx<rows;idx++) {
+ long count=(*data)[idx*(*ds_cnt)+3];
+ if (count==0) {
+ /* No data this bin */
+ if (copy_left>0) {
+ /* But we can copy from previous */
+ int idx_p=idx-1;
+ (*data)[idx*(*ds_cnt)+0]=(*data)[idx_p*(*ds_cnt)+0];
+ (*data)[idx*(*ds_cnt)+1]=(*data)[idx_p*(*ds_cnt)+1];
+ (*data)[idx*(*ds_cnt)+2]=(*data)[idx_p*(*ds_cnt)+2];
+ (*data)[idx*(*ds_cnt)+3]=(*data)[idx_p*(*ds_cnt)+3];
+ (*data)[idx*(*ds_cnt)+4]=(*data)[idx_p*(*ds_cnt)+4];
+ copy_left--;
+ }
+ }else{
+ copy_left=fillmissing;
+ }
}
}