Author: mattm
Date: 2016-10-12 22:43:03 +0200 (Wed, 12 Oct 2016)
New Revision: 26391

Modified:
   trunk/openvas-manager/ChangeLog
   trunk/openvas-manager/tools/openvas-migrate-to-postgres.in
Log:
        * tools/openvas-migrate-to-postgres.in (cleanup_sqlite_db): Remove stray
        task preferences.  The C migrator migrate_167_to_168 is leaving one of
        these behind.  Remove escalator_condition_data_trash, which
        migrate_56_to_57 leaves behind.
        (TABLES_135): Generate from TABLES_134.
        (migrate_150_to_151, migrate_153_to_154, migrate_154_to_155)
        (migrate_166_to_167): Create tables that were introduced at this time.
        (TABLES_151): Order to match create order.
        (TABLES_153_NO_USERS): New variable.
        (TABLES_154): Remove tables removed in migrator.
        (create_migrate_clean): Clean before migrating, because migrating 
depends
        on a clean db.

Modified: trunk/openvas-manager/ChangeLog
===================================================================
--- trunk/openvas-manager/ChangeLog     2016-10-12 15:03:27 UTC (rev 26390)
+++ trunk/openvas-manager/ChangeLog     2016-10-12 20:43:03 UTC (rev 26391)
@@ -1,5 +1,20 @@
 2016-10-12  Matthew Mundell <matthew.mund...@greenbone.net>
 
+       * tools/openvas-migrate-to-postgres.in (cleanup_sqlite_db): Remove stray
+       task preferences.  The C migrator migrate_167_to_168 is leaving one of
+       these behind.  Remove escalator_condition_data_trash, which
+       migrate_56_to_57 leaves behind.
+       (TABLES_135): Generate from TABLES_134.
+       (migrate_150_to_151, migrate_153_to_154, migrate_154_to_155)
+       (migrate_166_to_167): Create tables that were introduced at this time.
+       (TABLES_151): Order to match create order.
+       (TABLES_153_NO_USERS): New variable.
+       (TABLES_154): Remove tables removed in migrator.
+       (create_migrate_clean): Clean before migrating, because migrating 
depends
+       on a clean db.
+
+2016-10-12  Matthew Mundell <matthew.mund...@greenbone.net>
+
        * tools/openvas-migrate-to-postgres.in: Convert tabs to spaces.
 
 2016-10-12  Matthew Mundell <matthew.mund...@greenbone.net>

Modified: trunk/openvas-manager/tools/openvas-migrate-to-postgres.in
===================================================================
--- trunk/openvas-manager/tools/openvas-migrate-to-postgres.in  2016-10-12 
15:03:27 UTC (rev 26390)
+++ trunk/openvas-manager/tools/openvas-migrate-to-postgres.in  2016-10-12 
20:43:03 UTC (rev 26391)
@@ -972,6 +972,10 @@
   sqlite "DELETE FROM task_alerts WHERE alert_location = 1 AND alert NOT IN 
(SELECT id FROM alerts_trash);"
   test_sql_exit "Failed to clean up task_alerts (trash alerts)"
 
+  # Remove stray task_preferences.
+  sqlite "DELETE FROM task_preferences WHERE task NOT IN (SELECT id FROM 
alerts);"
+  test_sql_exit "Failed to clean up task_preferences"
+
   # Remove stray report_format_params_trash.
   sqlite "DELETE FROM report_format_params_trash WHERE report_format NOT IN 
(SELECT id FROM report_formats_trash);"
   test_sql_exit "Failed to clean up report_format_params_trash"
@@ -1022,6 +1026,11 @@
         UPDATE report_hosts SET end_time = strftime ('%s', substr (end_time, 
21) || '-' || (select number from months where name = substr (end_time, 5, 3)) 
|| '-' || replace (substr (end_time, 9, 2), ' ', '0') || 'T' || substr 
(end_time, 12, 9)) WHERE end_time GLOB '??? ??? ?? ??:??:?? ????';
         COMMIT;" | $SQLITE3 -batch $SQLITE_DB
   test_sql_exit "Failed to make end_time in report_hosts consistent"
+
+  # There was an error in the C code migrate_56_to_57 which left this
+  # table lying around.
+  sqlite "DROP TABLE IF EXISTS escalator_condition_data_trash;"
+  test_sql_exit "Failed to remove escalator_condition_data_trash"
 }
 
 copy_data () {
@@ -1126,7 +1135,7 @@
   pg "DROP TABLE report_results;"
 }
 
-TABLES_135="users agents agents_trash configs config_preferences meta 
nvt_preferences nvt_selectors lsc_credentials lsc_credentials_trash port_lists 
port_ranges targets schedules slaves tasks reports report_hosts report_formats 
report_format_params report_format_param_options results task_files 
configs_trash config_preferences_trash report_formats_trash 
report_format_params_trash report_format_param_options_trash schedules_trash 
slaves_trash targets_trash report_host_details task_preferences 
port_lists_trash port_ranges_trash alerts_trash alert_event_data_trash 
alert_method_data_trash alerts alert_condition_data alert_event_data 
alert_method_data task_alerts alert_condition_data_trash groups group_users 
filters filters_trash port_names settings roles role_users overrides 
overrides_trash notes notes_trash groups_trash roles_trash tags_trash 
group_users_trash role_users_trash scanners scanners_trash permissions_trash 
permissions tags"
+TABLES_135=`echo $TABLES_134 | sed -e "s/ report_results//g"`
 
 migrate_135_to_136 () {
   : # Schema stayed the same.
@@ -1202,8 +1211,6 @@
   pg "ALTER TABLE report_counts ADD COLUMN min_qod INTEGER;"
 }
 
-# FIX tables were added to rest?
-
 TABLES_147=$TABLES_146
 
 migrate_147_to_148 () {
@@ -1225,11 +1232,76 @@
 TABLES_150=$TABLES_149
 
 migrate_150_to_151 () {
-  : # Schema stayed the same.
+  # Create new tables.
+
+  pg "CREATE TABLE IF NOT EXISTS hosts                                         
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        uuid text UNIQUE NOT NULL,                                             
\
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,                
\
+        name text NOT NULL,                                                    
\
+        comment text,                                                          
\
+        creation_time integer,                                                 
\
+        modification_time integer);"
+
+  pg "CREATE TABLE IF NOT EXISTS host_identifiers                              
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        uuid text UNIQUE NOT NULL,                                             
\
+        host integer REFERENCES hosts (id) ON DELETE RESTRICT,                 
\
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,                
\
+        name text NOT NULL,                                                    
\
+        comment text,                                                          
\
+        value text NOT NULL,                                                   
\
+        source_type text NOT NULL,                                             
\
+        source_id text NOT NULL,                                               
\
+        source_data text NOT NULL,                                             
\
+        creation_time integer,                                                 
\
+        modification_time integer);"
+
+  pg "CREATE TABLE IF NOT EXISTS oss                                           
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        uuid text UNIQUE NOT NULL,                                             
\
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,                
\
+        name text NOT NULL,                                                    
\
+        comment text,                                                          
\
+        creation_time integer,                                                 
\
+        modification_time integer);"
+
+  pg "CREATE TABLE IF NOT EXISTS host_oss                                      
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        uuid text UNIQUE NOT NULL,                                             
\
+        host integer REFERENCES hosts (id) ON DELETE RESTRICT,                 
\
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,                
\
+        name text NOT NULL,                                                    
\
+        comment text,                                                          
\
+        os integer REFERENCES oss (id) ON DELETE RESTRICT,                     
\
+        source_type text NOT NULL,                                             
\
+        source_id text NOT NULL,                                               
\
+        source_data text NOT NULL,                                             
\
+        creation_time integer,                                                 
\
+        modification_time integer);"
+
+  pg "CREATE TABLE IF NOT EXISTS host_max_severities                           
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        host integer REFERENCES hosts (id) ON DELETE RESTRICT,                 
\
+        severity real,                                                         
\
+        source_type text NOT NULL,                                             
\
+        source_id text NOT NULL,                                               
\
+        creation_time integer);"
+
+  pg "CREATE TABLE IF NOT EXISTS host_details                                  
\
+       (id SERIAL PRIMARY KEY,                                                 
\
+        host integer REFERENCES hosts (id) ON DELETE RESTRICT,                 
\
+        source_type text NOT NULL,                                             
\
+        source_id text NOT NULL,                                               
\
+        detail_source_type text,                                               
\
+        detail_source_name text,                                               
\
+        detail_source_description text,                                        
\
+        name text,                                                             
\
+        value text);"
 }
 
 # FIX host_details added between 150 and 151  (at 151 they have to be there, 
at 150 coping will fail if they're not there (if missing from sqlite then 
ignore))
-TABLES_151="$TABLES_150 hosts oss host_identifiers host_details 
host_max_severities host_oss."
+TABLES_151="$TABLES_150 hosts host_identifiers oss host_oss 
host_max_severities host_details"
 
 migrate_151_to_152 () {
   : # Schema stayed the same.
@@ -1246,9 +1318,44 @@
 migrate_153_to_154 () {
   pg "DROP TABLE lsc_credentials;"
   pg "DROP TABLE lsc_credentials_trash;"
+
+  # Create new tables.
+
+  pg "CREATE TABLE credentials                                         \
+       (id SERIAL PRIMARY KEY,                                         \
+        uuid text UNIQUE NOT NULL,                                     \
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,        \
+        name text NOT NULL,                                            \
+        comment text,                                                  \
+        creation_time integer,                                         \
+        modification_time integer,                                     \
+        type text);"
+
+  pg "CREATE TABLE credentials_trash                                   \
+       (id SERIAL PRIMARY KEY,                                         \
+        uuid text UNIQUE NOT NULL,                                     \
+        owner integer REFERENCES users (id) ON DELETE RESTRICT,        \
+        name text NOT NULL,                                            \
+        comment text,                                                  \
+        creation_time integer,                                         \
+        modification_time integer,                                     \
+        type text);"
+
+  pg "CREATE TABLE credentials_data                                        \
+       (id SERIAL PRIMARY KEY,                                             \
+        credential INTEGER REFERENCES credentials (id) ON DELETE RESTRICT, \
+        type TEXT,                                                         \
+        value TEXT);"
+
+  pg "CREATE TABLE credentials_trash_data                                      
  \
+       (id SERIAL PRIMARY KEY,                                                 
  \
+        credential INTEGER REFERENCES credentials_trash (id) ON DELETE 
RESTRICT, \
+        type TEXT,                                                             
  \
+        value TEXT);"
 }
 
-TABLES_154="$TABLES_153 credentials credentials_trash credentials_data 
credentials_trash_data"
+TABLES_153_NO_USERS=`echo $TABLES_153 | sed -e "s/^users //g"`
+TABLES_154=`echo "users credentials credentials_trash credentials_data 
credentials_trash_data $TABLES_153_NO_USERS" | sed -e "s/\( lsc_credentials\| 
lsc_credentials_trash\)//g"`
 
 migrate_154_to_155 () {
   pg "ALTER TABLE reports ADD COLUMN flags INTEGER;"
@@ -1271,6 +1378,23 @@
   pg "ALTER TABLE targets_trash DROP COLUMN esxi_lsc_credential;"
   pg "ALTER TABLE targets_trash DROP COLUMN esxi_location;"
   pg "ALTER TABLE targets_trash RENAME COLUMN port_range TO port_list;"
+
+  # Create new tables.
+
+  pg "CREATE TABLE IF NOT EXISTS targets_login_data        \
+       (id SERIAL PRIMARY KEY,                             \
+        target INTEGER REFERENCES targets (id),            \
+        type TEXT,                                         \
+        credential INTEGER REFERENCES credentials (id),    \
+        port INTEGER);"
+
+  pg "CREATE TABLE IF NOT EXISTS targets_trash_login_data  \
+       (id SERIAL PRIMARY KEY,                             \
+        target INTEGER REFERENCES targets_trash (id),      \
+        type TEXT,                                         \
+        credential INTEGER,                                \
+        port INTEGER,                                      \
+        credential_location INTEGER);"
 }
 
 TABLES_156="$TABLES_155 targets_login_data targets_trash_login_data"
@@ -1355,7 +1479,10 @@
 TABLES_166=$TABLES_165
 
 migrate_166_to_167 () {
-  : # Schema stayed the same.
+  # Create new table.
+
+  pg "CREATE TABLE IF NOT EXISTS resources_predefined                   \
+       (id SERIAL PRIMARY KEY, resource_type text, resource INTEGER);"
 }
 
 TABLES_167="$TABLES_166 resources_predefined"
@@ -1825,8 +1952,8 @@
 create_migrate_clean () {
   log_info "Creating Postgres database (version $1)."
   create_tables_133
+  cleanup_sqlite_db
   migrate 133 $1
-  cleanup_sqlite_db
 }
 
 ## Create a Postgres database based on the SQLite one.

_______________________________________________
Openvas-commits mailing list
Openvas-commits@wald.intevation.org
https://lists.wald.intevation.org/cgi-bin/mailman/listinfo/openvas-commits

Reply via email to