3 /* This 'function' updates the gnucomo database to the correct version it will make changes to the
4 * database. It is called within an if-statement when needed.
5 * INPUT : NONE (everything is available within the class)
6 * OUTPUT : Success TRUE/FALSE
10 //Do while statement since all updateshave to be executed starting at the
11 //latest version. It's a walk through.
13 switch ($active_version)
16 //DROP the rule that prohibits updates (change of spec).
17 $local_sql = "DROP RULE log_update";
18 // $dbms->query($local_sql); DISABLED FOR NEW ROLE LOG-tABLE
21 //In the log table processed should be false by default
22 $local_sql = "UPDATE log SET processed = false";
23 $dbms->query($local_sql);
26 //In the log_adv a column is added that indicates where the detailed
27 //data has been written to
28 $local_sql = "ALTER TABLE log_adv ADD COLUMN detailed_table VARCHAR(75)";
29 $dbms->query($local_sql);
32 //Create a log_adv_kernel_network table that recognizes the log-records
33 //that have come from the kernel-network interface (typically iptables).
34 $local_sql = "CREATE TABLE log_adv_kernel_network (";
35 $local_sql .= "device_in VARCHAR(15), device_out VARCHAR(15), hw_address MACADDR, ";
36 $local_sql .= "source_ip INET, destination_ip INET, packet_length BIGINT, ";
37 $local_sql .= "tos_bit VARCHAR(5), ";
38 $local_sql .= "prec_bit VARCHAR(5), ttl INT, header_id BIGINT, source_port INT, ";
39 $local_sql .= "destination_port INT, body_length INT, protocol VARCHAR(5), ";
40 $local_sql .= "body_len INT";
41 $local_sql .= ") INHERITS (log_adv)";
42 $dbms->query($local_sql);
45 //Add the operating system to the object-table
46 //COLUMN os (VARCHAR - 25)
47 //COLUMN os_version (VARCHAR - 15)
48 $local_sql = "ALTER TABLE object ADD COLUMN os TEXT";
49 $dbms->query($local_sql);
51 $local_sql = "ALTER TABLE object ADD COLUMN os_version TEXT";
52 $dbms->query($local_sql);
54 $local_sql = "CREATE INDEX os ON object (os)";
55 $dbms->query($local_sql);
57 $local_sql = "CREATE INDEX os_version ON object (os, os_version)";
58 $dbms->query($local_sql);
61 //Create a table with supported operating systems. Only after a implementation of a
62 //log-processing application a new OS will be added. This ensures the correctness of
64 $local_sql = "CREATE TABLE supported_os (os_name TEXT, remarks TEXT)";
65 $dbms->query($local_sql);
67 $local_sql = "CREATE UNIQUE INDEX spp_os ON supported_os (os_name)";
68 $dbms->query($local_sql);
71 //Create a column in the log table that indicates if the daemon did recognize the
72 //log-record. This makes it easier to trace which log-records still have to be added
74 $local_sql = "ALTER TABLE log ADD COLUMN recognized BOOLEAN";
75 $dbms->query($local_sql);
77 $local_sql = "ALTER TABLE log ALTER COLUMN recognized SET DEFAULT FALSE";
78 $dbms->query($local_sql);
81 //Set the default value of processed to FALSE
82 $local_sql = "ALTER TABLE log ALTER COLUMN processed SET DEFAULT FALSE";
83 $dbms->query($local_sql);
86 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN window TEXT";
87 $dbms->query($local_sql);
89 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN urgp INT";
90 $dbms->query($local_sql);
92 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN syn BOOLEAN";
93 $dbms->query($local_sql);
95 $local_sql = "ALTER TABLE log_adv_kernel_network ALTER COLUMN syn SET DEFAULT FALSE";
96 $dbms->query($local_sql);
100 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN type INT";
101 $dbms->query($local_sql);
103 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN code INT";
104 $dbms->query($local_sql);
106 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN sequence_number INT";
107 $dbms->query($local_sql);
110 //Recognize some more kernel_network fields
111 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN res varchar(5)";
112 $dbms->query($local_sql);
114 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN rst BOOLEAN";
115 $dbms->query($local_sql);
117 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN df BOOLEAN";
118 $dbms->query($local_sql);
121 //Add the gcm_daemon_version to the system
122 $local_sql = "INSERT INTO db_value VALUES ('gcm_daemon_version', '1')";
123 $dbms->query($local_sql);
126 //Create the table object_system_user
127 $local_sql = "CREATE TABLE object_system_user (objectid BIGINT, system_username TEXT, can_login BOOLEAN, can_be_root BOOLEAN)";
128 $dbms->query($local_sql);
130 $local_sql = "CREATE UNIQUE INDEX obsyus_id_name ON object_system_user (objectid, system_username)";
131 $dbms->query($local_sql);
134 $local_sql = "CREATE INDEX obsyus_id ON object_system_user (objectid)";
135 $dbms->query($local_sql);
138 $local_sql = "CREATE INDEX obsyus_name ON object_system_user (system_username)";
139 $dbms->query($local_sql);
142 //Make type_of_issue ready for automatic detection
143 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN automated_check BOOLEAN";
144 $dbms->query($local_sql);
146 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN alert_level INT";
147 $dbms->query($local_sql);
149 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN last_run TIMESTAMP";
150 $dbms->query($local_sql);
152 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN recheck_interval TIMESTAMP";
153 $dbms->query($local_sql);
156 $local_sql = "INSERT INTO db_value (setting, setting_value) VALUES ('log_processing', '0')";
157 $dbms->query($local_sql);
160 $local_sql = "ALTER TABLE log_adv_kernel_network DROP COLUMN hw_address";
161 $dbms->query($local_sql);
164 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN hw_address TEXT";
165 $dbms->query($local_sql);
167 $local_sql = "DROP TABLE object_system_user";
168 $dbms->query($local_sql);
171 $local_sql = "UPDATE action SET actionname = 'Investigation completed', statuscode = 'PEN', description = 'Investigation has been done.' ";
172 $local_sql .= "WHERE actionid = '9'";
173 $dbms->query($local_sql);
176 $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
177 $local_sql .= " values ('parameter created',3,'A new parameter was created')";
178 $dbms->query($local_sql);
180 $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
181 $local_sql .= " values ('property modified',3,'The STATIC property of a parameter was modified')";
182 $dbms->query($local_sql);
184 $local_sql = "insert into type_of_issue (name, suggested_priority, description)";
185 $local_sql .= " values ('parameter removed',3,'A parameter was removed') ";
186 $dbms->query($local_sql);
189 $local_sql = "CREATE TABLE parameter_notification (notificationid bigint, paramid bigint, primary key (notificationid, paramid))";
190 $dbms->query($local_sql);
192 $local_sql = "CREATE TABLE parameter_class (name text, property_name text, description text, property_type text, ";
193 $local_sql .= "min float, max float, notify boolean, primary key (name, property_name));";
194 $dbms->query($local_sql);
196 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, notify)";
197 $local_sql .= "VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't')";
198 $dbms->query($local_sql);
202 $local_sql = "UPDATE action SET statuscode = LOWER(statuscode)";
203 $dbms->query($local_sql);
206 //Create a log_adv_kernel_network table that recognizes the log-records
207 //that have come from the kernel-network interface (typically iptables).
208 $local_sql = "CREATE TABLE log_adv_daemon (";
209 $local_sql .= "service TEXT, event TEXT";
210 $local_sql .= ") INHERITS (log_adv)";
211 $dbms->query($local_sql);
214 $local_sql = "CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service)";
215 $dbms->query($local_sql);
220 //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN
221 $local_sql = "ALTER TABLE log DROP COLUMN recognized";
222 $dbms->query($local_sql);
224 $local_sql = "ALTER TABLE logng DROP COLUMN recognized";
225 $dbms->query($local_sql);
229 $local_sql = "UPDATE db_value SET setting_value = ".$db_version." WHERE setting = 'db_version'";
231 $dbms->query($local_sql);