#!/usr/bin/python # This script will copy the additional controls information # from one SQLite translation table file into another. These # are some columns added to a couple of tables and one additional # table. These were added by Nerses G. to build on the original # SQLite file generated by the tt_csv2db.py script. In order to # preserve that information while still being able to automatically # copy updates from Fernando's SpreadSheet, this script is used. # # To use this, give it the name of the new and the old and tt.db # files respectively: # # > tt_copy_controls.py tt_new.db tt_old.db # # The copy will only proceed if the tt_new.db file does not # already have the "Detector_Hierarchy" table defined. If it # already exists there, then it is assumed that the copy has # already been done and so will not overwrite anything. # Similarly, if tt_old.db does not contain the "Detector_Hierarchy" # table, then the script will stop without modifying the # "Crate" and "Channel" tables to include the additional # columns. import sqlite3 as lite import sys import os import socket import itertools import re from time import gmtime, localtime, strftime if len(sys.argv) != 3: print "\nYou must supply both and new and an old SQLite file!" print "\nUsage:" print " tt_copy_controls.py tt_new.db tt_old.db\n" sys.exit(0); # Open connections to both files and stop if either won't open tt_new = sys.argv[1] tt_old = sys.argv[2] con_new = lite.connect(tt_new) con_old = lite.connect(tt_old) print '' with con_new: with con_old: # Specify that next cursor should be "dictionary" # (i.e. python's hash map) so columns can be indexed # by name con_new.row_factory = lite.Row con_old.row_factory = lite.Row # Create Cursors cur_new = con_new.cursor() cur_old = con_old.cursor() #======================================================================== # This first part just checks the format of the input and output SQLite # files to verify that the needed table and columns exist in the old # DB (the one being copied from) but do not exist in the new one (the one # we're copying to.) We exit if anything seems wrong to ensure we don't # overwrite existing information print '-------------------------------------------------------------------' print ' Updating controls values in %s using values from %s' % (tt_new, tt_old) print '' # Check if table already exits in tt_old, but no in tt_new sql = "SELECT name FROM sqlite_master WHERE type='table' AND name='Detector_Hierarchy'"; cur_new.execute(sql) cur_old.execute(sql) rows_new = cur_new.fetchall() rows_old = cur_old.fetchall() table_exists_new = len(rows_new)>0 table_exists_old = len(rows_old)>0 print '%s : Detector_Hierarchy table exists = %s' % (tt_new, table_exists_new ) print '%s : Detector_Hierarchy table exists = %s' % (tt_old, table_exists_old ) if table_exists_new or not table_exists_old: print '\nFiles do not contain correct format ("Detector_Hierarchy" should exist ONLY in the old tt.db file)\n' sys.exit(-2) # Check if "host" and "IP" columns exist in "Crate" table sql = "SELECT sql FROM sqlite_master WHERE tbl_name='Crate' AND type='table'" cur_new.execute(sql) cur_old.execute(sql) (row_new) = cur_new.fetchone() (row_old) = cur_old.fetchone() has_host_new = False has_host_old = False has_IP_new = False has_IP_old = False if "host TEXT" in row_new[0] : has_host_new = True; if "host TEXT" in row_old[0] : has_host_old = True; if "IP TEXT" in row_new[0] : has_IP_new = True; if "IP TEXT" in row_old[0] : has_IP_old = True; if has_host_new or has_IP_new or (not has_host_old) or (not has_IP_old): print '\nCrate table should have host and IP columns ONLY in the old tt.db!' print 'has_host_new=%d has_IP_new=%d has_host_old=%d has_IP_old=%d' % (has_host_new,has_IP_new,has_host_old,has_IP_old) print 'old: %s' % row_old[0] print 'new: %s' % row_new[0] sys.exit(-3) # Check if "enable" column exists in "Channel" table sql = "SELECT sql FROM sqlite_master WHERE tbl_name='Channel' AND type='table'" cur_new.execute(sql) cur_old.execute(sql) (row_new) = cur_new.fetchone() (row_old) = cur_old.fetchone() has_enable_new = False has_enable_old = False if "enable INTEGER" in row_new[0] : has_enable_new = True; if "enable INTEGER" in row_old[0] : has_enable_old = True; if has_enable_new or not has_enable_old: print '\nChannel table should have "enable" column ONLY in the old tt.db!\n' sys.exit(-4) print 'Table definitions seem OK. Proceeding ...' #======================================================================== # Modify the existing Crate and Channel tables in the new file # to contain the new columns. Also, add Detector_Hiearchy table sql = "ALTER TABLE Crate ADD COLUMN host TEXT" cur_new.execute(sql) sql = "ALTER TABLE Crate ADD COLUMN IP TEXT" cur_new.execute(sql) sql = "ALTER TABLE Channel ADD COLUMN enable INTEGER DEFAULT 1" cur_new.execute(sql) # Add Detector_Hiearchy sql = "SELECT sql FROM sqlite_master WHERE tbl_name='Detector_Hierarchy' AND type='table'" cur_old.execute(sql) (row_old) = cur_old.fetchone() sql_Detector_Hierarchy = row_old[0] print '\nCreating Detector_Hierarchy table:' print ' %s' % sql_Detector_Hierarchy cur_new.execute(sql_Detector_Hierarchy) #======================================================================== # Copy entire Detector_Hierarchy table. This can be done in a single # SQL statement! The trick is to attach the old and new databases using # the SQLite "ATTACH DATABASE" command. cur_new.execute("ATTACH DATABASE '%s' AS 'OLD'" % tt_old) sql = "INSERT INTO Detector_Hierarchy (id, parent_id, name, type, chanid, mtime) SELECT id, parent_id, name, type, chanid, mtime FROM OLD.Detector_Hierarchy" print '\nCopying Detector_Hierarchy entries...' cur_new.execute(sql) cur_new.execute("SELECT count(*) AS cnt FROM Detector_Hierarchy") (row_new) = cur_new.fetchone() print ' %d rows copied' % int(row_new[0]) #======================================================================== # Copy host and IP information from from Crate table print '\nUpdating Crate table ...' cur_new.execute("SELECT name,host,IP FROM OLD.Crate") rows_old = cur_new.fetchall() Nrows_updated_host = 0 Nrows_updated_IP = 0 for row in rows_old: host = row['host'] IP = row['IP'] name = row['name'] if host != None: Nrows_updated_host += 1 cur_new.execute("UPDATE Crate SET host='%s' WHERE name='%s'" % (host,name)) if IP != None: Nrows_updated_IP += 1 cur_new.execute("UPDATE Crate SET IP='%s' WHERE name='%s'" % (IP,name)) print ' updated %d "host" values' % Nrows_updated_host print ' updated %d "IP" values' % Nrows_updated_IP #======================================================================== # Copy "enable" values from Channel table print '\nUpdating Channel table ...' sql = "SELECT OLD.Channel.name AS name, OLD.Channel.enable AS enable FROM OLD.Channel,main.Channel" sql += " WHERE OLD.Channel.chanid=main.Channel.chanid AND OLD.Channel.enable!=main.Channel.enable" cur_new.execute(sql) rows_old = cur_new.fetchall() Nrows_updated_enable = 0 for row in rows_old: enable = row['enable'] name = row['name'] if enable != None: Nrows_updated_enable += 1 cur_new.execute("UPDATE Channel SET enable='%s' WHERE name='%s'" % (enable,name)) print ' updated %d "enable" values' % Nrows_updated_enable if Nrows_updated_enable==0: print " (zero values updated means all values were already correct)" #======================================================================== # Copy slot values from Module table for certain module types # # For most of these modules, the slot numbering starts from 0. # In principle, this could be corrected in the tt_csv2db.py script. # We do this here, however, in order to catch any additional changes # that were made (and omit any that were not changed) by Nerses. print '\nUpdating slots in Module table ...' mod_types = ['A1535SN', 'A1550N', 'A1550P', 'ISEG', 'MPV8008L', 'MPV8030L'] for mod_type in mod_types: sql = "SELECT OLD.Module.moduleid AS moduleid,OLD.Module.slot,main.Module.slot AS bad_slot FROM OLD.Module,main.Module" sql += " WHERE OLD.Module.type='%s' AND OLD.Module.moduleid=main.Module.moduleid" % mod_type sql += " AND OLD.Module.slot!=main.Module.slot" cur_new.execute(sql) rows = cur_new.fetchall() for row in rows: sql = "UPDATE Module SET slot=%s WHERE moduleid=%s" % (row['slot'], row['moduleid']) cur_new.execute(sql) #print '%s : (slot was %s for module type %s)' % (sql, row['bad_slot'], mod_type) if len(rows)>0: print ' Changed %3d slot values for module type %s' % (len(rows), mod_type) #======================================================================== # Copy entries from Channel table for certain module types # print '\nCopying missing Channel entries for HV,LV crates ...' # Get list of columns in Channel table cur_new.execute("SELECT * FROM Channel LIMIT 1") row = cur_new.fetchone() chan_columns = row.keys() cols = '' for col in chan_columns: if col != 'chanid': cols += ',%s' % col cols = cols[1:] # remove first ',' # Loop over control module types, getting all Channel entries for each for mod_type in mod_types: sql = "SELECT * FROM OLD.Channel,OLD.Module WHERE OLD.Module.moduleid=OLD.Channel.moduleid AND OLD.Module.type='%s'" % mod_type cur_new.execute(sql) rows = cur_new.fetchall() Ninserts = 0 for row in rows: # Check if this entry is already in the new table sql = "SELECT * FROM main.Channel WHERE moduleid=%s AND name='%s'" % (row['chanid'], row['name']) cur_new.execute(sql) if cur_new.rowcount<1: sql = "INSERT INTO main.Channel (%s) SELECT %s FROM OLD.Channel WHERE moduleid=%s AND name='%s'" % (cols, cols, row['moduleid'], row['name']) cur_new.execute(sql) Ninserts += 1 if Ninserts>0: print ' Inserted %d channels for module type %s' % (Ninserts, mod_type) #print columns #for k,v in row: #print '%s = %s' % (k,v) #sys.exit(0) # chanid = row['chanid'] # moduleid = row['moduleid'] # name = row['name'] # channel = row['channel'] # system = row['system'] # # chanid INTEGER PRIMARY KEY, moduleid INTEGER, name TEXT, channel INT, system TEXT, col_name TEXT, enable INTEGER DEFAULT 1) print '\nUpdate complete.' print '-------------------------------------------------------------------'