- Fixed field declaration for PostgreSQL 7.4

- New tables: log_abuse, object_abuse and service_pattern
- Added general service patterns in the service 'ANY'
- Added new issue types.

diff --git a/src/database/create.sql b/src/database/create.sql
index 33909f0..99f9561 100644 (file)
--- a/src/database/create.sql
+++ b/src/database/create.sql
@@ -12,7 +12,13 @@
 -- DBA create the database and give access permissions.
 --
 --  $Log: create.sql,v $
---  Revision 1.18  2003-12-03 08:06:57  arjen
+--  Revision 1.19  2005-06-04 07:09:47  arjen
+--  - Fixed field declaration for PostgreSQL 7.4
+--  - New tables: log_abuse, object_abuse and service_pattern
+--  - Added general service patterns in the service 'ANY'
+--  - Added new issue types.
+--
+--  Revision 1.18  2003/12/03 08:06:57  arjen
 --  Changed the type of log_adv_daemon_email.delay and log_adv_daemon_email.xdelay
 --  from time to interval. These delays can be more than 24 hours.
 --
@@ -155,7 +161,7 @@ CREATE TABLE "db_value"
 
 
 COPY "db_value" FROM stdin;
-db_version     44
+db_version     48
 gcm_daemon_version     5
 log_processing 0
 last_notification      0
@@ -214,6 +220,13 @@ CREATE INDEX log_processed ON log USING btree (processed);
 SELECT setval ('"log_logid_seq"', 1, false);
 
 
+CREATE TABLE log_abuse
+(
+        logid      bigint,
+       objectid   bigint,
+        source     inet
+);
+
 --
 --
 
@@ -384,7 +397,7 @@ CREATE TABLE notification_check
      checkname               TEXT,
      description             TEXT,
      time_between_executions INTERVAL, 
-     last_execution          DATETIME,
+     last_execution          timestamp,
      execution_counter       BIGINT,
      notificationcounter     BIGINT,
      decreasinglist          BOOLEAN default false,
@@ -487,6 +500,19 @@ SELECT setval ('"object_objectid_seq"', 1, false);
 --
 --
 
+CREATE TABLE "object_abuse"
+(
+       objectid   bigint,
+        source     inet,
+        nr_abuses  integer,
+        status     text,
+
+        primary key (objectid, source)
+);
+
+--
+--
+
 CREATE TABLE "object_issue"
 (
        "objectid" bigint,
@@ -696,6 +722,22 @@ CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
 
 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
 
+CREATE TABLE service_pattern
+(
+      service   text,
+      rank      int,
+      pattern   text,
+      action    text,
+      argument  text,
+
+      primary key (service, rank)
+);
+
+INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log');
+INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected');
+INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected');
+INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected');
+
 --
 
 CREATE TABLE "status"
@@ -755,12 +797,17 @@ CREATE TABLE "type_of_issue"
 
 
 COPY "type_of_issue" FROM stdin;
-1      manual entry    4       A manual entry of a notification        t
-2      parameter created       3       A new parameter was created     t
-3      property modified       3       The STATIC property of a parameter was modified t
-4      parameter removed       3       A parameter was removed t
-5      service unknown 5       Service in log entry is unknown t
-6      service not used        5       Service in log entry is not used        t
+1      manual entry    4       A manual entry of a notification        t       t       1       \N      \N
+2      parameter created       3       A new parameter was created     t       t       1       \N      \N
+3      property modified       3       The STATIC property of a parameter was modified t       t       1       \N      \N
+4      parameter removed       3       A parameter was removed t       t       1       \N      \N
+5      service unknown 5       Service in log entry is unknown t       t       1       \N      \N
+6      service not used        5       Service in log entry is not used        t       t       1       \N      \N
+7      abuses exceeded 5       Abuse treshold exceeded from an IP address      t       t       1       \N      \N
+8      unmatched log   5       Log entries could not be matched        t       t       1       \N      \N
+9      Error detected  5       An Error is reported in the log t       t       1       \N      \N
+9      Failure detected        5       A Failure is reported in the log        t       t       1       \N      \N
+10     Warning detected        3       A Warning is reported in the log        t       t       1       \N      \N
 \.
 
 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
@@ -806,13 +853,16 @@ GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
+GRANT SELECT ON log_adv_daemon_email TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
+GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
+GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
@@ -830,6 +880,9 @@ GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
 GRANT INSERT ON object TO GROUP admin;
 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
 GRANT DELETE ON object TO GROUP admin;
+GRANT INSERT ON object_abuse TO GROUP admin;
+GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin;
+GRANT DELETE ON object_abuse TO GROUP admin;
 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
 GRANT INSERT ON usr TO GROUP admin;
 GRANT DELETE ON usr TO GROUP admin;

diff --git a/src/database/destroy.sql b/src/database/destroy.sql
index 3b7f389..d466a9f 100644 (file)
--- a/src/database/destroy.sql
+++ b/src/database/destroy.sql
@@ -31,6 +31,7 @@ DROP TABLE "log";
 
 DROP SEQUENCE "log_advid_seq";
 
+DROP TABLE log_abuse;
 DROP TABLE log_adv_kernel_network;
 DROP TABLE log_adv_daemon_email;
 DROP TABLE log_adv_daemon;
@@ -56,6 +57,8 @@ DROP SEQUENCE "object_objectid_seq";
 
 DROP TABLE "object";
 
+DROP TABLE "object_abuse";
+
 DROP TABLE "object_issue";
 
 DROP TABLE "object_priority";
@@ -82,6 +85,8 @@ DROP TABLE "property";
 
 DROP TABLE "service";
 
+DROP TABLE service_pattern
+
 DROP TABLE "status";
 DROP TABLE supported_os;
 

diff --git a/src/gcm_daemon/classes/gnucomo_db_version.php b/src/gcm_daemon/classes/gnucomo_db_version.php
index f4a4d42..9ce5cfb 100644 (file)
--- a/src/gcm_daemon/classes/gnucomo_db_version.php
+++ b/src/gcm_daemon/classes/gnucomo_db_version.php
@@ -14,8 +14,8 @@
    {
      case 1:
        //DROP the rule that prohibits updates (change of spec).
-       $local_sql = "DROP RULE log_update";
-       $dbms->query($local_sql); //DISABLED FOR NEW ROLE LOG-tABLE  ???
+       //$local_sql = "DROP RULE log_update";
+       //$dbms->query($local_sql); //DISABLED FOR NEW ROLE LOG-tABLE  ???
 
      case 2:
        //In the log table processed should be false by default
@@ -339,7 +339,7 @@ case 36:
      $local_sql  = "CREATE TABLE notification_check (";
      $local_sql .= "checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,";
      $local_sql .= "checkname TEXT, description TEXT, time_between_executions INTERVAL, ";
-     $local_sql .= "last_execution DATETIME, execution_counter BIGINT,";
+     $local_sql .= "last_execution timestamp, execution_counter BIGINT,";
      $local_sql .= "notificationcounter BIGINT)";
      $dbms->query($local_sql);
 
@@ -540,6 +540,78 @@ case 43:
      $local_sql = "CREATE INDEX log_adv_email_status      ON log_adv_daemon_email (status)";
      $dbms->query($local_sql);
 
+   case 44:
+
+     $local_sql = "CREATE TABLE object_abuse
+                   (
+                           objectid   bigint,
+                           source     inet,
+                           nr_abuses  integer,
+                           status     text,
+
+                           primary key (objectid, source)
+                   );";
+     $dbms->query($local_sql);
+     $dbms->query("GRANT SELECT ON log_adv_daemon_email TO GROUP view, GROUP ops, GROUP admin");
+     $dbms->query("GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin");
+     $dbms->query("GRANT INSERT ON object_abuse TO GROUP admin");
+     $dbms->query("GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin");
+     $dbms->query("GRANT DELETE ON object_abuse TO GROUP admin");
+     $dbms->query("GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin");
+
+     $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
+     $local_sql .= " VALUES ('abuses exceeded',5,'Abuse treshold exceeded from an IP address', true) ";
+     $dbms->query($local_sql);
+
+   case 45:
+
+     $local_sql = "CREATE TABLE service_pattern
+                   (
+                         service   text,
+                         rank      int,
+                         pattern   text,
+                         action    text,
+                         argument  text,
+
+                         primary key (service, rank)
+                   )";
+     $dbms->query($local_sql);
+     $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log')";
+     $dbms->query($local_sql);
+     $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
+     $local_sql .= " VALUES ('unmatched log',5,'Log entries could not be matched', true) ";
+     $dbms->query($local_sql);
+
+   case 46:
+
+     $local_sql = "CREATE TABLE log_abuse
+                   (
+                           logid      bigint,
+                          objectid   bigint,
+                           source     inet
+                   )";
+     $dbms->query($local_sql);
+
+   case 47:
+
+     $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected')";
+     $dbms->query($local_sql);
+     $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
+     $local_sql .= " VALUES ('Error detected',5,'An Error is reported in the log', true) ";
+     $dbms->query($local_sql);
+
+     $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected')";
+     $dbms->query($local_sql);
+     $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
+     $local_sql .= " VALUES ('Failure detected',5,'A Failure is reported in the log', true) ";
+     $dbms->query($local_sql);
+
+     $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected')";
+     $dbms->query($local_sql);
+     $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
+     $local_sql .= " VALUES ('Warning detected',3,'A Warning is reported in the log', true) ";
+     $dbms->query($local_sql);
+
 
 //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN
 /*