def Process(self, inputFile):
rowsImported = 0
rowsToInsert = []
for event, elem in xml.etree.cElementTree.iterparse(inputFile):
if elem.tag != "ROW":
continue
row = self._GetRowFromElement(elem)
rowsToInsert.append(row)
rowsImported += 1
commit = (self.commitPoint \
and rowsImported % self.commitPoint == 0)
if commit or len(rowsToInsert) == self.cursor.arraysize:
self.cursor.executemany(None, rowsToInsert)
rowsToInsert = []
if commit:
self.connection.commit()
if self.reportPoint and rowsImported % self.reportPoint == 0:
cx_Logging.Trace("%d rows imported.", rowsImported)
elem.clear()
if rowsToInsert:
self.cursor.executemany(None, rowsToInsert)
if self.commitPoint is None or rowsImported % self.commitPoint != 0:
self.connection.commit()
if self.reportPoint is None or rowsImported % self.reportPoint != 0:
cx_Logging.Trace("%d rows imported.", rowsImported)
def Remove(self):
cx_Logging.Trace("Removing database...")
self.Stop("abort")
if self.service is not None:
oraDim = os.path.join(self.binDir, "oradim")
command = "%s -DELETE -SID %s" % (oraDim, self.sid)
cx_Utils.ExecuteOSCommands(command)
entries = [s.strip() for s in file(self.diskConfigFileName)]
entries.append(self.adminDir)
entries.append(self.parameterFileName)
entries.append(self.storedParameterFileName)
entries.append(self.passwordFileName)
dirsToCheck = {}
for entry in entries:
if not os.path.exists(entry):
continue
dirsToCheck[os.path.dirname(entry)] = None
cx_ShellUtils.Remove(entry)
for dir in dirsToCheck:
if not os.path.exists(dir):
continue
if not os.listdir(dir):
cx_Logging.Trace("removing directory %s...", dir)
os.rmdir(dir)
cx_Logging.Trace("Database %s removed.", self.sid)
def ReportProgress(self, numRows):
"""Report progress on the import."""
if self.inFileSize is not None:
percent = (self.inFile.tell() / self.inFileSize) * 100
cx_Logging.Trace(" %d rows imported (%.0f%% of file).", numRows,
percent)
else:
cx_Logging.Trace(" %d rows imported.", numRows)
def Start(self):
if self.hasService:
self.service.Start()
if self.IsAvailable():
cx_Logging.Trace("Database %s already started.", self.sid)
else:
cx_Logging.Trace("Starting database %s...", self.sid)
self.RunInSqlplus("startup.sql", "startup")
cx_Logging.Trace("Database %s started.", self.sid)
def _LogCommand(self, command):
separator = "-" * 66
message = command.GetLogMessage()
cx_Logging.Trace("%s", separator)
cx_Logging.Trace("%s", message)
cx_Logging.Trace("%s", separator)
if sys.stdout.isatty() and not sys.stderr.isatty():
now = datetime.datetime.today()
print(now.strftime("%Y/%m/%d %H:%M:%S"), message)
sys.stdout.flush()
def __TraceReturn(self, frame, returnValue):
if self.traceTimeStack:
elapsedTime = time.time() - self.traceTimeStack.pop()
else:
elapsedTime = 0
self.prefix = " " * len(self.traceTimeStack)
code = frame.f_code
cx_Logging.Trace('%sFile "%s", line %d', self.prefix, code.co_filename,
frame.f_lineno)
returnValue = self.__ValueForOutput(returnValue)
cx_Logging.Trace("%s%s() returning %s in %.3f seconds", self.prefix,
code.co_name, returnValue, elapsedTime)
def main():
"""
Method that performs the work of the script.
"""
options = get_options()
connection = cx_OracleUtils.Connect(options.schema)
cx_Logging.Trace("Connected to %s@%s", connection.username, connection.dsn)
cx_Logging.Trace("Listening on pipe %s...", options.pipeName)
# display the messages
for message in cx_OracleDebugger.MessageGenerator(connection,
options.pipeName):
cx_Logging.Trace("%s", message)
def Stop(self, mode = ""):
aborting = (mode.lower() == "abort")
isAvailable = self.IsAvailable()
if isAvailable and not aborting:
self.WriteDiskConfigFile()
if self.hasService:
self.service.Stop()
elif isAvailable:
cx_Logging.Trace("Stopping database %s...", self.sid)
self.RunInSqlplus("shutdown.sql", "shutdown %s" % mode)
cx_Logging.Trace("Database %s stopped.", self.sid)
else:
cx_Logging.Trace("Database %s already stopped.", self.sid)
def __TraceCall(self, frame, unusedArg):
code = frame.f_code
cx_Logging.Trace('%sFile "%s", line %d', self.prefix, code.co_filename,
frame.f_lineno)
args = [self.__FormatValue(frame, n) \
for n in code.co_varnames[:code.co_argcount]]
if code.co_flags & 4:
args.append(
self.__FormatValue(frame, code.co_varnames[len(args)], "*"))
if code.co_flags & 8:
args.append(
self.__FormatValue(frame, code.co_varnames[len(args)], "**"))
cx_Logging.Trace("%s%s(%s)", self.prefix, code.co_name,
", ".join(args))
self.traceTimeStack.append(time.time())
self.prefix = " " * len(self.traceTimeStack)
def Process(self, processor):
connection = processor.connection
cursor = connection.cursor()
cursor.execute("select user from dual")
user, = cursor.fetchone()
parser = cx_OracleParser.SimpleParser()
sql = open(self.fileName).read()
connectStatementClass = parser.parser.processor.ConnectStatement
try:
for statement in parser.IterParse(sql, user):
if isinstance(statement, connectStatementClass):
connection = cx_OracleEx.Connection(
statement.user, statement.password
or connection.password, statement.dsn
or connection.dsn)
cursor = connection.cursor()
cx_Logging.Trace("%s", statement.GetLogMessage(cursor))
parser.parser.processor.owner = statement.user
else:
try:
statement.Process(cursor)
except cx_Exceptions.BaseException as error:
lineNumber = statement.lineNumber
if isinstance(error, cx_OracleEx.DatabaseException) \
and error.dbErrorOffset is not None:
offset = error.dbErrorOffset
lineNumber += statement.sql[:offset].count("\n")
cx_Logging.Error("Error at line %s", lineNumber)
if not processor.onErrorContinue:
raise
cx_Logging.Error("%s", error.message)
except cx_OracleParser.ParsingFailed as value:
cx_Logging.Error("Parsing failed at line %s (%s...)",
value.arguments["lineNumber"],
value.arguments["remainingString"][:100])
def StartTracing(self, domains, maxLength=1000, traceLines=False):
tracer = Tracer(self, domains, maxLength, traceLines)
cx_Logging.Trace("starting tracing (traceLines=%r) of domains %r",
traceLines, domains)
if traceLines:
sys.settrace(tracer)
else:
sys.setprofile(tracer)
def __ExportTableHeader(self, tableName):
"""Export the table header to the file."""
cx_Logging.Trace("%sExporting table %s...", self.prefix, tableName)
self.cursor.execute("select * from " + tableName)
columns = [(r[0], self.__StringRepOfType(r[1], r[2])) \
for r in self.cursor.description]
pickle.dump(tableName, self.outFile, BINARY)
pickle.dump(columns, self.outFile, BINARY)
def LogMessages(connection, pipeName):
"""Log messages using the cx_Logging module."""
cx_Logging.Debug("logging messages from pipe %s", pipeName)
debugConnection = cx_Oracle.Connection(connection.username,
connection.password,
connection.tnsentry,
threaded=True)
for message in MessageGenerator(debugConnection, pipeName):
cx_Logging.Trace("%s", message)
def __ExportTableRows(self, rowsToSkip, rowLimit):
"""Export the rows in the table to the file."""
numRows = 0
format = self.prefix + " %d rows exported."
cursor = self.cursor
outFile = self.outFile
reportPoint = self.reportPoint
for row in cursor:
numRows += 1
if numRows > rowLimit:
numRows -= 1
break
elif numRows > rowsToSkip:
pickle.dump(row, outFile, BINARY)
if reportPoint is not None and numRows % reportPoint == 0:
cx_Logging.Trace(format, numRows)
if reportPoint is None or numRows == 0 or numRows % reportPoint != 0:
cx_Logging.Trace(format, numRows)
pickle.dump(None, outFile, BINARY)
def RecompileInvalidObjects(connection,
includeSchemas,
excludeSchemas=[],
raiseError=True,
logPrefix="",
connectAsOwner=False):
"""Recompile all invalid objects in the schemas requested."""
# determine whether or not to use dba views or not
if len(includeSchemas) == 1 and not excludeSchemas \
and connection.username.upper() == includeSchemas[0]:
singleSchema = True
viewPrefix = "all"
else:
singleSchema = False
viewPrefix = "dba"
# prepare a cursor to determine if object is still invalid
invalidCursor = connection.cursor()
invalidCursor.prepare("""
select count(*)
from %s_objects
where owner = :owner
and object_name = :name
and object_type = :type
and status = 'INVALID'""" % viewPrefix)
invalidCursor.setinputsizes(owner=connection.STRING,
name=connection.STRING,
type=connection.STRING)
# prepare a cursor to determine the errors for stored source
errorsCursor = PrepareErrorsCursor(connection, viewPrefix)
# fetch all of the invalid objects
numErrors = 0
numCompiled = 0
compileCursor = connection.cursor()
cursor = connection.cursor()
cursor.arraysize = 25
cursor.execute("""
select
owner,
object_name,
object_type
from %s_objects
where status = 'INVALID'
and object_type != 'UNDEFINED'
order by owner""" % viewPrefix)
for owner, name, type in cursor.fetchall():
# ignore if this schema should be ignored
if includeSchemas and owner not in includeSchemas:
continue
if excludeSchemas and owner in excludeSchemas:
continue
# ignore if prior compiles have made this object valid
invalidCursor.execute(None, owner=owner, name=name, type=type)
invalid, = invalidCursor.fetchone()
if not invalid:
continue
# perform compile
numCompiled += 1
if singleSchema:
compileName = name
else:
compileName = "%s.%s" % (owner, name)
cx_Logging.Trace("%sCompiling %s (%s)...", logPrefix, compileName,
type)
parts = type.lower().split()
statement = "alter " + parts[0] + " " + compileName + " compile"
if len(parts) > 1:
statement += " " + parts[1]
if connectAsOwner and connection.username.upper() != owner:
connection = cx_OracleEx.Connection(owner, connection.password,
connection.dsn)
compileCursor = connection.cursor()
compileCursor.execute(statement)
try:
CheckForErrors(errorsCursor,
owner,
name,
type,
"has",
logPrefix=logPrefix)
except:
if raiseError:
raise
numErrors += 1
# all done
if numErrors:
cx_Logging.Trace("%sAll objects compiled: %s error(s).", logPrefix,
numErrors)
elif numCompiled:
cx_Logging.Trace("%sAll objects compiled successfully.", logPrefix)
else:
cx_Logging.Trace("%sNo invalid objects to compile.", logPrefix)
"""
import cx_Logging
import cx_LoggingOptions
import cx_OptionParser
import cx_OracleDebugger
import cx_OracleUtils
# parse command line
parser = cx_OptionParser.OptionParser()
parser.AddOption("--pipe-name",
default="DbDebugger",
metavar="NAME",
prompt="Pipe name",
help="use this pipe name for communication with the database")
parser.AddOption(cx_OracleUtils.SchemaOption())
cx_LoggingOptions.AddOptions(parser)
options = parser.Parse()
cx_LoggingOptions.ProcessOptions(options)
# connect to the database
connection = cx_OracleUtils.Connect(options.schema)
cx_Logging.Trace("Connected to %s@%s", connection.username,
connection.tnsentry)
cx_Logging.Trace("Listening on pipe %s...", options.pipeName)
# display the messages
for message in cx_OracleDebugger.MessageGenerator(connection,
options.pipeName):
cx_Logging.Trace("%s", message)
def __TraceLine(self, frame, unusedArg):
code = frame.f_code
cx_Logging.Trace('%sFile "%s", line %d', self.prefix, code.co_filename,
frame.f_lineno)
# write the data to the XML file
if options.fileName == "-":
outputFile = sys.stdout
else:
outputFile = file(options.fileName, "w")
writer = cx_XML.Writer(outputFile, numSpaces=4)
writer.StartTag("ROWSET")
for row in cursor:
writer.StartTag("ROW")
for name, value in zip(names, row):
if value is None:
continue
if isinstance(value, cx_Oracle.DATETIME):
dateValue = datetime.datetime(value.year, value.month, value.day,
value.hour, value.minute,
value.second)
value = dateValue.strftime(options.dateFormat)
else:
value = str(value)
writer.WriteTagWithValue(name, value)
writer.EndTag()
if options.reportPoint is not None \
and cursor.rowcount % options.reportPoint == 0:
cx_Logging.Trace("%s rows exported.", cursor.rowcount)
writer.EndTag()
if options.reportPoint is None or cursor.rowcount % options.reportPoint != 0:
cx_Logging.Trace("%s rows exported.", cursor.rowcount)
cx_Logging.Trace("Done.")
def StopTracing(self):
"""Stop tracing."""
cx_Logging.Trace("stopping tracing")
sys.setprofile(None)
sys.settrace(None)
if options.arraySize:
importer.cursor.arraysize = options.arraySize
importer.OpenFile(options.fileName)
if options.reportPoint:
importer.reportPoint = options.reportPoint
if options.commitPoint:
importer.commitPoint = options.commitPoint
if options.reportPoint is None:
importer.reportPoint = options.commitPoint
# set the list of tables for import
if options.includeTables:
options.includeTables = [
s.upper() for v in options.includeTables for s in v.split(",")
]
if options.excludeTables:
options.excludeTables = [
s.upper() for v in options.excludeTables for s in v.split(",")
]
# import all of the data
for tableName, columnNames in importer:
checkName = tableName.upper()
if options.includeTables and checkName not in options.includeTables \
or options.excludeTables and checkName in options.excludeTables:
cx_Logging.Trace("Skipping table %s...", tableName)
importer.SkipTable()
else:
cx_Logging.Trace("Importing table %s...", tableName)
importer.ImportTable()
def __TraceLine(self, frame, unusedArg):
"""Trace the execution of a single line."""
code = frame.f_code
cx_Logging.Trace('%sFile "%s", line %d', self.prefix, code.co_filename,
frame.f_lineno)
if options.sqlInFile:
sql = file(sql).read()
cursor.execute(sql)
# define function to return an evaluated string (to support tabs, newlines)
def EvalString(value):
return value.replace("\\t", "\t").replace("\\n", "\n")
# dump the results to the output file
fieldSeparator = EvalString(options.fieldSep)
recordSeparator = EvalString(options.recordSep)
stringEncloser = EvalString(options.stringEncloser)
escapeCharacter = EvalString(options.escapeChar)
writer = csv.writer(outFile,
delimiter=fieldSeparator,
quotechar=stringEncloser,
escapechar=escapeCharacter,
lineterminator=recordSeparator)
for row in cursor:
writer.writerow(row)
if options.reportPoint and cursor.rowcount % options.reportPoint == 0:
cx_Logging.Trace("%s rows dumped.", cursor.rowcount)
# report the total number of rows dumped
if not options.reportPoint or cursor.rowcount == 0 or \
cursor.rowcount % options.reportPoint != 0:
cx_Logging.Trace("%s rows dumped.", cursor.rowcount)
cx_Logging.Trace("Done.")
statement += " and (%s)" % " or ".join(additionalWhereClauses)
updateCursor = destConnection.cursor()
updateCursor.bindarraysize = sourceCursor.arraysize
updateCursor.prepare(statement)
vars = []
updateVars = []
for name in updateColumns:
colPos, colType, isLob = sourceColumns[name]
sourceVar = sourceVars[colPos]
targetVar = updateCursor.var(colType, sourceVar.maxlength)
updateVars.append((sourceVar, targetVar, isLob))
vars.append(targetVar)
updateCursor.setinputsizes(*vars)
# tell user what is happening
cx_Logging.Trace("Copying data...")
cx_Logging.Trace(" Source: %s", sourceSQL)
cx_Logging.Trace(" Destination: %s", destinationTable)
# skip rows that are not of interest
while options.skip:
cx_Logging.Trace(" Rows left to skip: %s", options.skip)
rowsToFetch = min(sourceCursor.arraysize, options.skip)
options.skip -= sourceCursor.fetchraw(rowsToFetch)
# initialize counters used in performing the copy
insertedRows = 0
modifiedRows = 0
unmodifiedRows = 0
insertPos = 0
updatePos = 0
def Process(self, cursor):
self.Execute(cursor)
message = self.GetLogMessage(cursor)
if message is not None:
cx_Logging.Trace("%s", message)
