From: arjen Date: Thu, 13 Feb 2003 08:39:12 +0000 (+0000) Subject: Added log, notification and parameter counters to the 'object' table. X-Git-Tag: V0_0_5~26 X-Git-Url: http://www.andromeda.nl/gitweb/?p=gnucomo.git;a=commitdiff_plain;h=1094412697ffd91c93a6fb13a0d11b4d45fe8291 Added log, notification and parameter counters to the 'object' table. Counting these things at the time a user interface needs them is too slow. Other programs, like gcm_daemon en gcm_input should prepare these counters for quick retrieval. --- diff --git a/src/gcm_daemon/classes/gnucomo_db_version.php b/src/gcm_daemon/classes/gnucomo_db_version.php index 08f386f..dccdfef 100644 --- a/src/gcm_daemon/classes/gnucomo_db_version.php +++ b/src/gcm_daemon/classes/gnucomo_db_version.php @@ -213,9 +213,30 @@ case 23: $local_sql = "CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service)"; $dbms->query($local_sql); + + case 24: + $local_sql = "ALTER TABLE object ADD COLUMN log_count BIGINT"; + $dbms->query($local_sql); + $local_sql = "ALTER TABLE object ADD COLUMN notification_count BIGINT"; + $dbms->query($local_sql); + $local_sql = "ALTER TABLE object ADD COLUMN parameter_count BIGINT"; + $dbms->query($local_sql); +case 25: + $local_sql = "UPDATE object SET log_count = '0', " + . "notification_count = '0', " + . "parameter_count = '0' "; + $dbms->query($local_sql); +case 26: + $local_sql = "UPDATE type_of_issue SET name = TRIM(name)"; + $dbms->query($local_sql); + +case 27: + $local_sql = "INSERT INTO db_value VALUES ('last_notification', '0')"; + $dbms->query($local_sql); + /* //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN $local_sql = "ALTER TABLE log DROP COLUMN recognized"; @@ -224,6 +245,7 @@ $local_sql = "ALTER TABLE logng DROP COLUMN recognized"; $dbms->query($local_sql); */ + } $local_sql = "UPDATE db_value SET setting_value = ".$db_version." WHERE setting = 'db_version'"; diff --git a/src/gcm_daemon/gcm_daemon.php b/src/gcm_daemon/gcm_daemon.php index bc0547a..dc97a0f 100755 --- a/src/gcm_daemon/gcm_daemon.php +++ b/src/gcm_daemon/gcm_daemon.php @@ -20,7 +20,7 @@ */ -// $Id: gcm_daemon.php,v 1.5 2003-02-08 07:38:44 arjen Exp $ +// $Id: gcm_daemon.php,v 1.6 2003-02-13 08:39:12 arjen Exp $ ini_set('include_path', '.:./classes:../phpclasses'); @@ -37,7 +37,7 @@ require_once "gnucomo.process_log.php"; $project_name = "gnucomo"; //name of the entire project $app_name = "gcm_daemon"; //name of the application running $developrelease = "TRUE"; //Indicates if special debug settings are needed -$db_version = 24; //The db_version indicates what the level of +$db_version = 28; //The db_version indicates what the level of //the database should be. If the database is //old an update will be generated. $gcmd_version = 3; //This value indicates the active version of the gcm_daemon, @@ -75,6 +75,7 @@ if ($dbms->have_db_connection() == "FALSE") { //The database connection has been made. $dbms_working = copy_db_class($dbms); } + //Verify if the database is up-to-date by checking the versionnumber $local_sql = "SELECT setting_value FROM db_value WHERE setting = 'db_version' "; $dbms->query($local_sql); @@ -94,7 +95,7 @@ if ($dbms->fetch_row() == "TRUE") { //If there is a new gcm_daemon_version the logrecords that couldn't be understood can be //reprocessed. For this reason processed is now changed to false again for not recognized //records. -$local_sql = " SELECT setting_value FROM db_value WHERE setting = 'gcm_daemon_version'"; +$local_sql = "SELECT setting_value FROM db_value WHERE setting = 'gcm_daemon_version'"; $dbms->query($local_sql); if ($dbms->fetch_row() == "TRUE") { @@ -114,10 +115,12 @@ if ($dbms->fetch_row() == "TRUE") { //Now we loop the tasks that we have to do. + do { //At this place we start processing new log-lines process_log (); + notificationstats(); $keep_running = 'FALSE'; @@ -138,24 +141,38 @@ function process_log () { global $dbms; global $dbms_working; -/* - //Start a transaction for the processing/ - $local_sql_working = "BEGIN TRANSACTION"; - $dbms_working->query($local_sql_working); -*/ //Find records in log that still have to be processed. - $local_sql = " SELECT setting_value FROM db_value WHERE setting = 'log_processing'"; + $local_sql = "SELECT setting_value FROM db_value WHERE setting = 'log_processing'"; $dbms->query($local_sql); if ($dbms->fetch_row() == "TRUE") { $last_log = $dbms->db_result_row[0]; } - - + + //Query the log-table $local_sql = "SELECT * FROM log WHERE logid > CAST(".$last_log." AS BIGINT) order by logid"; $dbms->query($local_sql); + //Update the log-statistics in the object-table + $local_statistics_db = copy_db_class($dbms); + $local_findobject_db = copy_db_class($dbms); + + //Make totals + $local_upper_row = $dbms->num_rows() + $last_log + 1; + $local_sql = "SELECT COUNT(logid), objectid from log WHERE logid > CAST(". $last_log . + " AS BIGINT) AND logid < CAST (" . $local_upper_row . " AS BIGINT) GROUP BY objectid"; + $local_statistics_db->query ($local_sql); + + //Loop the objects + for ($i = 1; $i <= $local_statistics_db->num_rows(); $i++) { + $local_object_row = $local_statistics_db->fetch_row(); + $local_sql = "UPDATE object SET log_count = log_count + " . + $local_statistics_db->db_result_row[0] . " WHERE objectid = '" . + $local_statistics_db->db_result_row[1] . "'"; + $local_findobject_db->query($local_sql); + } + $local_counter = 0; if ($dbms->num_rows() > 0) { @@ -164,12 +181,11 @@ function process_log () { $dbms_changes = copy_db_class($dbms); if ($dbms_changes->have_db_connection() == 'TRUE') { - $local_sql = 0; + $local_sql = 0 ; + $local_sql_statistics = ""; $local_object_os = ""; $local_object_os_version = ""; - //Walk through all the log-records. - while ($local_counter < $dbms->num_rows()) { $local_return_row = $dbms->fetch_row(); @@ -177,15 +193,12 @@ function process_log () { //Work on active rows $local_log_id = $dbms->db_result_row[0]; - //If this is the first time get information about the object - if ($local_object_os == "") { - //No OS has been given yet, so let's find the objectid. - $local_findobject_db = copy_db_class($dbms); - - $local_sql_findobject = "SELECT os, os_version FROM object WHERE objectid = '".$dbms->db_result_row[0]."'"; - $local_findobject_db->query($local_sql_findobject); - $local_findobject_result = $local_findobject_db->fetch_row(); - if ($local_findobject_result == 'TRUE') { + $local_sql_findobject = "SELECT os, os_version FROM object WHERE objectid = '".$dbms->db_result_row[1]."'"; + $local_findobject_db->query($local_sql_findobject); + $local_findobject_result = $local_findobject_db->fetch_row(); + if ($local_findobject_result == 'TRUE') { + + //Now work on the OS again $local_object_os = $local_findobject_db->db_result_row[0]; if ($local_object_os == "") { $local_object_os = "Linux"; @@ -193,10 +206,6 @@ function process_log () { } else { $local_object_os_version = $local_findobject_db->db_result_row[1]; } - } else { - syslog (LOG_INFO, "Couldn't find object for log-records"); - die("Couldn't match log-records to any existing object"); - } } switch (strtolower($local_object_os)) { @@ -219,20 +228,72 @@ function process_log () { } $local_counter++; } - + + //Register that the logrecords have been processed. $local_sql = "UPDATE db_value SET setting_value = '".$local_log_id."' where setting = 'log_processing'"; $dbms->query($local_sql); + //Update the statistics for the object-table + + } else { syslog (LOG_INFO, "Couldn't clone database connection."); die ("Couldn't reconnect to the database.\n"); } - } else { - die ("done"); } + } -?> +function notificationstats () { + +/* This routine will determine how many new notifications have arrived and will + * update the statistics in the object-table to keep the performance acceptable + * INPUT : NONE + * OUTPUT : NONE + */ + + global $dbms; + + //Find records in log that still have to be processed. + $local_sql = "SELECT setting_value FROM db_value WHERE setting = 'last_notification'"; + $dbms->query($local_sql); + $local_dbms = copy_db_class($dbms); + + //Determine the last notification + if ($dbms->fetch_row() == "TRUE") { + $last_notification = $dbms->db_result_row[0]; + } + + //Determine how many records we are going to analyse. + $local_sql = "SELECT MAX(notificationid) FROM notification " . + "WHERE notificationid > CAST ('" . $last_notification . "' AS BIGINT)"; + $dbms->query($local_sql); + + //Only process data if there are new notifications + if ($dbms->fetch_row() == "TRUE") { + $local_upper = $dbms->db_result_row[0] + 1; + $local_sql = "SELECT COUNT(objectid), objectid FROM notification " . + "WHERE notificationid > CAST ('" . $last_notification ."' AS BIGINT) " . + "AND notificationid < CAST ('" . $local_upper . + "' AS BIGINT) GROUP BY objectid"; + $dbms->query($local_sql); + + + for ($i=0; $i < $dbms->num_rows(); $i++) { + $dbms->fetch_row(); + + $local_sql = "UPDATE object SET notification_count = notification_count + " . $dbms->db_result_row[0] . + " WHERE objectid = '" . $dbms->db_result_row[1] . "'"; + $local_dbms->query($local_sql); + } + } + + $local_sql = "UPDATE db_value SET setting_value = '" . $local_upper . + "' WHERE setting = 'last_notification'"; + $dbms->query($local_sql); + +} +?>