Collaboration diagram for AtlTriggerDBCopy.OracleExporter:

Public Member Functions
def	__init__ (self, connection)

def	getTables (self, exceptTables)

def	executeQuery (self, query, data={})

def	tableSize (self, tableName)

def	columnNames (self, tableName)

def	getIndexes (self)

def	getPrimaryKeys (self)

def	getForeignKeys (self)

def	sqliteType (self, oraType)

def	tableCreationCommand (self, tableName, primaryKeys, foreignKeys, indexes)

def	extractSchema (self, creationFileName)

def	copyTable (self, tableName, sqliteInserter, size)

Public Attributes
	connection

	primaryKeys

	foreignKeys

	indexes

	allTables

	ignoreTablesCreate

	ignoreTablesFill

Detailed Description

Definition at line 102 of file AtlTriggerDBCopy.py.

Constructor & Destructor Documentation

◆ init()

def AtlTriggerDBCopy.OracleExporter.__init__	(	self,
		connection
	)

Definition at line 104 of file AtlTriggerDBCopy.py.

     def __init__(self, connection):
         self.connection = connection
         self.primaryKeys = None
         self.foreignKeys = None
         self.indexes = None
         self.allTables = None
         self.ignoreTablesCreate = [ 'TT_WRITELOCK', 'HLT_SMT_TO_HRE', 'HLT_PARAMETER', 'HLT_RELEASE', 'TRIGGER_LOG',
                                     'DBCOPY_SOURCE_DATABASE', 'HLT_RULE_SET', 'TEMP', 'HLT_RULE_PARAMETER', 'HLT_RULE_COMPONENT',
                                     'HLT_SETUP', 'TT_USERS', 'HLT_RULE', "HLT_HRC_TO_HRP", "HLT_HRE_TO_HRS",
                                     "HLT_HRS_TO_HRU", "HLT_HRU_TO_HRC", "HLT_PRESCALE_SET_ALIAS", "HLT_PRESCALE_SET_COLL",
                                     "PRESCALE_SET_ALIAS", "TRIGGER_ALIAS", "L1_PRESCALE_SET_ALIAS", "L1_CALO_SIN_COS",
                                     "L1_CI_TO_CSC", "L1_JET_INPUT", "L1_MUON_THRESHOLD_SET"]
         self.ignoreTablesFill = self.ignoreTablesCreate + [ ]
         print("Tables to ignore creation: %r" % len(self.ignoreTablesCreate))
         print("Tables to ignore filling: %r" % len(self.ignoreTablesFill))
  
  

Member Function Documentation

◆ columnNames()

def AtlTriggerDBCopy.OracleExporter.columnNames	(	self,
		tableName
	)

Definition at line 148 of file AtlTriggerDBCopy.py.

     def columnNames(self,tableName):
         query_ColumnNames = "SELECT column_name, data_type FROM sys.all_tab_columns WHERE owner = :SCHEMA AND table_name = :TABLE_NAME ORDER BY column_id"
         data = { "SCHEMA" : self.connection.schema, "TABLE_NAME" : tableName }
         result = self.executeQuery( query = query_ColumnNames, data = data )
         colNames, colTypes = zip(*result)
         return colNames, colTypes
  

◆ copyTable()

def AtlTriggerDBCopy.OracleExporter.copyTable	(	self,
		tableName,
		sqliteInserter,
		size
	)

Definition at line 248 of file AtlTriggerDBCopy.py.

     def copyTable(self, tableName, sqliteInserter, size):
         # build insert statement
         schema = self.connection.schema
         colNames,_ = self.columnNames(tableName)
         colNameList = ",".join(colNames)
         bindVarList = ",".join(len(colNames)*["?"])
         insertStatement = f"INSERT INTO {tableName} ({colNameList}) VALUES ({bindVarList})"
         # select entries from oracle
         selectQuery = f"SELECT {colNameList} FROM {schema}.{tableName}"
         #print(selectQuery)
         result = self.executeQuery( selectQuery )
         useBulkInsert = True
         if useBulkInsert:
             sqliteInserter.insertBulk(insertStatement, result)
         else:
             c = 0
             for data in result:
                 if c%10000==0:
                     print("%i / %i" % (c, size))
                 c+=1
                 sqliteInserter.insert(insertStatement, data)
                 if c==size:
                     break # just doing some timing measurements
         sqliteInserter.commit()
  
  

◆ executeQuery()

def AtlTriggerDBCopy.OracleExporter.executeQuery	(	self,
		query,
		data = `{}`
	)

Definition at line 135 of file AtlTriggerDBCopy.py.

     def executeQuery(self, query, data = {}):
         cursor = self.connection.cursor()
         cursor.arraysize = 1000
         cursor.execute( query, **data )
         return cursor        
  

◆ extractSchema()

def AtlTriggerDBCopy.OracleExporter.extractSchema	(	self,
		creationFileName
	)

Definition at line 239 of file AtlTriggerDBCopy.py.

     def extractSchema(self, creationFileName):
         fk = self.getForeignKeys()
         pk = self.getPrimaryKeys()
         indexes = self.getIndexes()
         with open(creationFileName, "w") as fh:
             print("Creating schema file for sqlite: %s" % creationFileName)
             for tableName in self.getTables(exceptTables = self.ignoreTablesCreate):
                 print(self.tableCreationCommand( tableName, pk[tableName], fk.get(tableName, list()), indexes.get(tableName, list())), file = fh)
  

◆ getForeignKeys()

def AtlTriggerDBCopy.OracleExporter.getForeignKeys ( self )

Definition at line 190 of file AtlTriggerDBCopy.py.

     def getForeignKeys(self):
         if self.foreignKeys is not None:
             return self.foreignKeys
         print("retrieving foreign key constraints from Oracle")
         query_ForeignKeys = """
         SELECT c.table_name, c.constraint_name, a.column_name, c.r_constraint_name, r.table_name, r.column_name 
         FROM sys.all_constraints c
         INNER JOIN
         sys.all_cons_columns a
         on (c.constraint_type='R' and c.constraint_name = a.constraint_name and c.owner = :SCHEMA and a.owner = :SCHEMA)
         INNER JOIN
         sys.all_cons_columns r
         on (c.r_constraint_name = r.constraint_name and r.owner = :SCHEMA)
         """        
         data = { "SCHEMA" : self.connection.schema }
         fk = {}
         result = self.executeQuery( query = query_ForeignKeys, data = data )
         for table, _, fkcol, _, rtable, rcol in result:
             if table not in fk:
                 fk[table] = []
             fk[table] += [ { "col" : fkcol, "rtab" : rtable, "rcol" : rcol } ]
         self.foreignKeys = fk
         return self.foreignKeys
  

◆ getIndexes()

def AtlTriggerDBCopy.OracleExporter.getIndexes ( self )

Definition at line 155 of file AtlTriggerDBCopy.py.

     def getIndexes(self):
         if self.indexes is not None:
             return self.indexes
         self.indexes = {}
         print("retrieving indexes from Oracle")
         query_Indexes = """
         SELECT table_name, index_name, column_name FROM sys.all_ind_columns WHERE table_owner = :SCHEMA
         """
         data = { "SCHEMA" : self.connection.schema }
         result = self.executeQuery( query = query_Indexes, data = data )
         for table_name, index_name, column_name in result:
             if table_name not in self.indexes:
                 self.indexes[table_name] = {}
             self.indexes[table_name][index_name] = column_name
         return self.indexes
  

◆ getPrimaryKeys()

def AtlTriggerDBCopy.OracleExporter.getPrimaryKeys ( self )

Definition at line 171 of file AtlTriggerDBCopy.py.

     def getPrimaryKeys(self):
         if self.primaryKeys is not None:
             return self.primaryKeys
         print("retrieving primary key constraints from Oracle")
         query_PrimaryKeys = """
         SELECT table_name, column_name, position FROM sys.all_cons_columns WHERE owner = :SCHEMA AND constraint_name in 
         (SELECT constraint_name FROM sys.all_constraints WHERE owner = :SCHEMA AND constraint_type='P') 
         ORDER BY table_name, position
         """
         data = { "SCHEMA" : self.connection.schema }
         pk = {}
         result = self.executeQuery( query = query_PrimaryKeys, data = data )
         for table_name, column_name, _ in result:
             if table_name not in pk:
                 pk[table_name] = []
             pk[table_name] += [ column_name ]
         self.primaryKeys = pk
         return self.primaryKeys
  

◆ getTables()

def AtlTriggerDBCopy.OracleExporter.getTables	(	self,
		exceptTables
	)

Definition at line 121 of file AtlTriggerDBCopy.py.

     def getTables(self, exceptTables):
         if self.allTables is None:
             query_ListAllTables = "SELECT table_name FROM all_tables WHERE owner=:SCHEMA"
             data = { "SCHEMA" : self.connection.schema }
             result = self.executeQuery(query_ListAllTables, data)
             self.allTables = [x[0] for x in result]
             print("All tables: %i" % len(self.allTables))
         tables = deepcopy(self.allTables)
         if exceptTables is not None:
             for x in exceptTables:
                 if x in tables:
                     tables.remove(x)
         return tables
  

◆ sqliteType()

def AtlTriggerDBCopy.OracleExporter.sqliteType	(	self,
		oraType
	)

Definition at line 214 of file AtlTriggerDBCopy.py.

     def sqliteType(self, oraType):
         if oraType.startswith("TIMESTAMP"):
             return "TEXT"
         d = { "VARCHAR2" : "TEXT",
               "CHAR" : "TEXT",
               "NUMBER" : "INTEGER",
               "CLOB" : "TEXT"
           }
         return d[oraType]
  

◆ tableCreationCommand()

def AtlTriggerDBCopy.OracleExporter.tableCreationCommand	(	self,
		tableName,
		primaryKeys,
		foreignKeys,
		indexes
	)

Definition at line 224 of file AtlTriggerDBCopy.py.

     def tableCreationCommand(self, tableName, primaryKeys, foreignKeys, indexes):
         colNames, colTypes = self.columnNames(tableName)
         lines = []
         for colName, colType in zip(colNames,colTypes):
             lines.append( "%s %s" % (colName, self.sqliteType(colType)) )
         lines.append( "PRIMARY KEY (%s)" % ",".join(primaryKeys) )
         for fk in foreignKeys:
             lines.append( "FOREIGN KEY (%s) REFERENCES %s (%s)" % (fk["col"], fk["rtab"], fk["rcol"]) )
         creationCommand = f"CREATE TABLE IF NOT EXISTS {tableName} (\n    "
         creationCommand += ",\n    ".join(lines)
         creationCommand += "\n);\n"
         for index_name in indexes:
             creationCommand += "CREATE INDEX %s on %s(%s);\n" % (index_name, tableName, indexes[index_name])
         return creationCommand
  

◆ tableSize()

def AtlTriggerDBCopy.OracleExporter.tableSize	(	self,
		tableName
	)

Definition at line 141 of file AtlTriggerDBCopy.py.

     def tableSize(self,tableName):
         query_TableSize = "SELECT COUNT(*) FROM {schema}.{tableName}"
         qdict = { "schema" : self.connection.schema, "tableName" : tableName }
         result = self.executeQuery( query_TableSize.format(**qdict) )
         row = result.fetchone()
         return row[0]
  

Member Data Documentation

◆ allTables

AtlTriggerDBCopy.OracleExporter.allTables

Definition at line 109 of file AtlTriggerDBCopy.py.

◆ connection

AtlTriggerDBCopy.OracleExporter.connection

Definition at line 105 of file AtlTriggerDBCopy.py.

◆ foreignKeys

AtlTriggerDBCopy.OracleExporter.foreignKeys

Definition at line 107 of file AtlTriggerDBCopy.py.

◆ ignoreTablesCreate

AtlTriggerDBCopy.OracleExporter.ignoreTablesCreate

Definition at line 110 of file AtlTriggerDBCopy.py.

◆ ignoreTablesFill

AtlTriggerDBCopy.OracleExporter.ignoreTablesFill

Definition at line 116 of file AtlTriggerDBCopy.py.

◆ indexes

AtlTriggerDBCopy.OracleExporter.indexes

Definition at line 108 of file AtlTriggerDBCopy.py.

◆ primaryKeys

AtlTriggerDBCopy.OracleExporter.primaryKeys

Definition at line 106 of file AtlTriggerDBCopy.py.

The documentation for this class was generated from the following file:

AtlTriggerDBCopy.py

Public Member Functions

Public Attributes