[Perfparse-cvs] _perfparse/db_tools convert.c,1.31,1.32

[Florian G. <re...@us...>]

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