Update of /cvsroot/perfparse/_perfparse/db_tools
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv3149/db_tools
Modified Files:
convert.c
Log Message:
convert service_bin table. Eleminate DATETIME database columns. Converted to INT for Unix Timestamps.
Index: convert.c
===================================================================
RCS file: /cvsroot/perfparse/_perfparse/db_tools/convert.c,v
retrieving revision 1.31
retrieving revision 1.32
diff -C2 -d -r1.31 -r1.32
*** convert.c 14 Apr 2007 21:59:54 -0000 1.31
--- convert.c 15 Apr 2007 23:39:52 -0000 1.32
***************
*** 47,51 ****
// debug
! #define DEBUG
void checkTables ();
--- 47,51 ----
// debug
! // #define DEBUG
void checkTables ();
***************
*** 77,80 ****
--- 77,81 ----
hostsCache=g_hash_table_new(g_str_hash,g_str_equal);
servicesCache=g_hash_table_new(g_str_hash,g_str_equal);
+ metricCache=g_hash_table_new(g_str_hash,g_str_equal);
}
***************
*** 783,786 ****
--- 784,857 ----
printw(_("\ndone\n")); refresh();
+
+
+ // Step 6
+ iStep++;
+ printw(_("(Step %d of %d)\n"),iStep,iNumSteps);
+ printw(_("Copying data of perfdata_service_bin table.\n"));
+ printw(_("Since this table can be really huge i will copy the data in chunks to avoid table locks.\n"));
+ printw(_("Every chunk will consist of the data collected on one day starting with the 1st entry.\n"));
+ printw(_("Everytime when data of 30 days are inserted i will make a 15 sec. break to give the database a chance to finish outstanding transactions.\n"));
+
+
+ refresh();
+
+ // get date of oldest entry
+ g_string_printf(gsODBSql, "select UNIX_TIMESTAMP(MIN(ctime)) from perfdata_service_bin");
+
+ int iOldestEntry; // oldest Entry not yet copied
+ query_result_old=dbtool_mysql_query(gsODBSql->str, &db_mysql_old, query_result_old);
+ if((result_row_old=mysql_fetch_row(query_result_old))) {
+ iOldestEntry=atoi(result_row_old[0]);
+ } else {
+ iOldestEntry=time(NULL);
+ }
+
+ int donedays=0;
+ while (iOldestEntry < time(NULL)) {
+ g_string_printf(gsODBSql, "select host_name, service_description,metric,"
+ "UNIX_TIMESTAMP(ctime),value,warn,critical,"
+ "state from perfdata_service_bin where ctime "
+ "between FROM_UNIXTIME(%d) and FROM_UNIXTIME(%d)",
+ iOldestEntry,iOldestEntry+24*60*60);
+ query_result_old=dbtool_mysql_query(gsODBSql->str, &db_mysql_old,query_result_old);
+ iODBnumrows=dbtool_rows(query_result_old);
+
+ printw(_("Copying data from date: %s Affected rows: %d\n"),
+ ctime(&iOldestEntry),iODBnumrows);
+ getyx(stdscr,y,x);
+ i=0;
+ while ((result_row_old=mysql_fetch_row(query_result_old))) {
+ i++;
+ int Mid;
+ move(y,x);
+ piODBlengths=mysql_fetch_lengths(query_result_old);
+
+ printw(_("Copying entry %d/%d"),i,iODBnumrows); refresh();
+ Mid=get_metricId_from_text(result_row_old[0],result_row_old[1],result_row_old[2],&db_mysql_new);
+ if(Mid!=FALSE) { // stale data
+ // TODO: check if ctime is changed everywhere from DATETIME to int (unix timestamp)
+ g_string_printf(gsNDBSql, "insert ignore into perfdata_service_bin"
+ "(metric_id,ctime,value,warn,critical,state) "
+ "values (%d,%s,%s,%s,%s,%s)",
+ Mid,result_row_old[3],result_row_old[4],
+ result_row_old[5],result_row_old[6],
+ result_row_old[7]);
+
+ // printw("debug: Sql Statement: %s\n",gsNDBSql->str);refresh();
+ dbtool_mysql_query(gsNDBSql->str, &db_mysql_new, query_result_new);
+ }
+ }
+ iOldestEntry+=3600*24;
+ donedays++;
+ if(donedays%30==0) {
+ printw(_("Sleeping 15 Sec."));
+ sleep(15);
+ }
+ move(x,y-2);
+ }
+ move(x,y);
+ printw(_("\ndone\n")); refresh();
+
//TODO: End comment versioncheck
|
