AtlTriggerDBCopy.OracleExporter Class Reference

Collaboration diagram for AtlTriggerDBCopy.OracleExporter:

Public Member Functions
	__init__ (self, connection)
	getTables (self, exceptTables)
	executeQuery (self, query, data={})
	tableSize (self, tableName)
	columnNames (self, tableName)
	getIndexes (self)
	getPrimaryKeys (self)
	getForeignKeys (self)
	sqliteType (self, oraType)
	tableCreationCommand (self, tableName, primaryKeys, foreignKeys, indexes)
	extractSchema (self, creationFileName)
	copyTable (self, tableName, sqliteInserter, size)

Public Attributes
	connection = connection
	primaryKeys = None
	foreignKeys = None
dict	indexes = None
list	allTables = None
list	ignoreTablesCreate
list	ignoreTablesFill = self.ignoreTablesCreate + [ ]

Detailed Description

Definition at line 102 of file AtlTriggerDBCopy.py.

Constructor & Destructor Documentation

__init__()

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()

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()

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()

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()

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()

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()

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()

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()

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()

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()

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()

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 = None

Definition at line 109 of file AtlTriggerDBCopy.py.

connection

AtlTriggerDBCopy.OracleExporter.connection = connection

Definition at line 105 of file AtlTriggerDBCopy.py.

foreignKeys

AtlTriggerDBCopy.OracleExporter.foreignKeys = None

Definition at line 107 of file AtlTriggerDBCopy.py.

ignoreTablesCreate

AtlTriggerDBCopy.OracleExporter.ignoreTablesCreate

Initial value:

=  [ '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"]

Definition at line 110 of file AtlTriggerDBCopy.py.

ignoreTablesFill

AtlTriggerDBCopy.OracleExporter.ignoreTablesFill = self.ignoreTablesCreate + [ ]

Definition at line 116 of file AtlTriggerDBCopy.py.

indexes

dict AtlTriggerDBCopy.OracleExporter.indexes = None

Definition at line 108 of file AtlTriggerDBCopy.py.

primaryKeys

AtlTriggerDBCopy.OracleExporter.primaryKeys = None

Definition at line 106 of file AtlTriggerDBCopy.py.

---

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

• AtlTriggerDBCopy.py