# -*- coding: utf-8 -*- # This file provides classes for exporting and importing database information # into/from XML from jToolkit.data import database from jToolkit.data import dates from jToolkit import prefs from jToolkit import errors import os import sys from jToolkit import serviceerrors from elementtree import ElementTree from cStringIO import StringIO from xml.dom import minidom import zipfile def toupper(value): return value.upper() def ConvertValue(value, typename=None): if value == 'None' or value is None: return None if typename == "string": return value if typename == "integer": return int(value) if typename == "float": return float(value) if typename == "datetime": return dates.parsedate(value, '%m/%d/%y %H:%M:%S') if value.startswith("-"): noneg = value[1:] else: noneg = value if noneg.isdigit(): return int(value) if noneg.lower() == "1.#qnan" or noneg.lower() == "1.#inf" or noneg.lower() == "1.#ind": return None pfloat = noneg.split('.') if len(pfloat) == 2 and pfloat[0].isdigit() and pfloat[1].isdigit(): return float(value) try: date = dates.parsedate(value, '%m/%d/%y %H:%M:%S') return date except dates.ParseError, dates.TypeError: pass return value def FindDateFromPeriod(number, units): """This function subtracts a period from the current date and returns it""" currentdate = dates.currentdate() if units == 'minutes': specDate = currentdate - dates.datetimedelta(minutes = number) elif units == 'hours': specDate = currentdate - dates.datetimedelta(hours = number) elif units == 'days': specDate = currentdate - dates.datetimedelta(days = number) elif units == 'months': years = number / 12 months = number % 12 if currentdate.month - months < 1: years = years + 1 months = months - 12 specDate = currentdate.replace(year = currentdate.year - years, month = currentdate.month - months) elif units == 'years' or units == "": specDate = currentdate.replace(year = currentdate.year - number) else: raise ValueError("Invalid units specification: %d %s" % (number, units)) return specDate def unzipAndParse(filename): """Takes a zipped filename, returns an XML Tree""" zipFile = zipfile.ZipFile(filename, compression=zipfile.ZIP_DEFLATED) contents = zipFile.read(os.path.basename(filename[:-4])) zipFile.close() return ElementTree.parse(StringIO(contents)) xmlns_dt = "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns_rs = "urn:schemas-microsoft-com:rowset" xmlns_s = "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns_z = "#RowsetSchema" typemap = {'int':'number','float':'double','dateTime':'datetime','string':'string'} class TableExporter: def __init__(self, errorhandler=None): """constructs the jHistExporter...""" if errorhandler is None: self.errorhandler = errors.ConsoleErrorHandler() else: self.errorhandler = errorhandler #Get the config self.defaultprefsfile, self.prefsfilename = self.getprefsfilenames() self.InitialiseConfig() self.CreateErrorHandlers() self.LoadDatabaseConfig() # set up the list of changed files self.ChangedFiles = [] def InitialiseConfig(self): self.config = prefs.PrefsParser() self.errorhandler.logtrace("Reading default preferences from "+self.defaultprefsfile) self.config.parsefile(self.defaultprefsfile) if os.path.exists(self.prefsfilename): self.errorhandler.logtrace("Reading preferences from "+self.prefsfilename) self.config.parsefile(self.prefsfilename) def CreateErrorHandlers(self): errorhandlers = [self.errorhandler] if hasattr(self.config, "mailerrors"): if not hasattr(self, "transfer"): self.errorhandler.logerror("Mail error reporting not supported without Transfer server") else: self.mailerrorhandler = serviceerrors.ForwardMailErrorHandler(self.config.mailerrors, transferAgent=self.transfer) errorhandlers.append(self.mailerrorhandler) if hasattr(self.config, "fileerrors"): self.fileerrorhandler = errors.ErrorHandler(self.config.fileerrors) errorhandlers.append(self.fileerrorhandler) self.errorhandler = errors.TeeErrorHandler(*errorhandlers) def LoadDatabaseConfig(self): DatabaseConfig = self.GetDatabaseConfigInfo() #Set the date formatting text # FIXME: Replace this with dbdateformatfn if DatabaseConfig.DBTYPE == 'access': self.dateformatstr = "format(logtime, 'YYYYMMDD')" self.datetimeformatstr = "format(logtime, 'YYYYMMDD_HH')" elif DatabaseConfig.DBTYPE == 'sqlserver': self.dateformatstr = "convert(char(8), logtime, 112)" self.datetimeformatstr = "convert(char(8), logtime, 112)+'_'+convert(char(2), logtime, 8)" elif DatabaseConfig.DBTYPE in ['oracle','postgres']: self.dateformatstr = "to_char(logtime, 'YYYYMMDD')" self.datetimeformatstr = "to_char(logtime, 'YYYYMMDD_HH24')" elif DatabaseConfig.DBTYPE == 'mysql': self.dateformatstr = "DATE_FORMAT(logtime, '%Y%m%d')" self.datetimeformatstr = "DATE_FORMAT(logtime, '%Y%m%d_%H')" try: self.db = database.dbwrapper(DatabaseConfig, errorhandler=self.errorhandler, generalcache=False) except: self.errorhandler.logerror(self.errorhandler.traceback_str()) sys.exit(1) def getprefsfilenames(self): """Returns the default prefs file name, and the prefs file name""" raise NotImplementedError("TableExporter.getprefsfilenames()") def GetDatabaseConfigInfo(self): raise NotImplementedError("TableExporter.GetDatabaseConfigInfo()") def GetExportDirectory(self): raise NotImplementedError("TableExporter.GetExportDirectory()") def CreateColumnSchema(self, description, schemaParent): """Returns the column schema from the description returned by db.allrows""" # Start column definition ColumnSchema = ElementTree.SubElement(schemaParent,'s:Schema') ColumnSchema.set('id','RowsetSchema') Columns = ElementTree.SubElement(ColumnSchema, 's:ElementType') Columns.set('name','row') Columns.set('content','eltOnly') # description is an array of tuples # each tuple describes a column for colnum, coldesc in enumerate(description): colName = str(coldesc[0]).upper() colNullable = coldesc[6] > 0 colDatatype = coldesc[1] colDatalength = coldesc[3] colPrecision = coldesc[4] colScale = coldesc[5] # Principal description ColDesc = ElementTree.SubElement(Columns, 's:AttributeType') ColDesc.set('name',colName) ColDesc.set('rs:number',str(colnum+1)) if colNullable: ColDesc.set('rs:nullable','true') ColDesc.set('rs:writeunknown','true') # Data description ColData = ElementTree.SubElement(ColDesc, 's:datatype') if colDatatype == self.db.driver.STRING: ColData.set('dt:type','string') ColData.set('rs:dbtype','str') elif colDatatype == self.db.driver.NUMBER: if colDatalength == 4: ColData.set('dt:type','int') elif colDatalength == 8: ColData.set('dt:type','float') else: self.errorhandler.logerror('Unhandled numerical tye of length %d' % colDatalength) ColData.set('rs:precision',str(colPrecision)) ColData.set('rs:fixedlength','true') elif colDatatype == self.db.driver.DATETIME: ColData.set('dt:type','dateTime') ColData.set('rs:dbtype','timestamp') ColData.set('rs:scale',str(colScale)) ColData.set('rs:precision',str(colPrecision)) ColData.set('rs:fixedlength','true') ColData.set('dt:maxlength',str(colDatalength)) if not colNullable: ColData.set('rs:maybenull','false') # This should conclude the description of columns return ColumnSchema def WriteData(self, SQLStatement, filename, compress=False): # Create root element XMLTree = ElementTree.Element('xml') # Set namespaces XMLTree.set('xmlns:s','uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882') XMLTree.set('xmlns:dt','uuid:C2F41010-65B3-11d1-A29F-00AA00C14882') XMLTree.set('xmlns:rs','urn:schemas-microsoft-com:rowset') XMLTree.set('xmlns:z','#RowsetSchema') # Get rows and descriptions rows, description = self.db.allrows(SQLStatement,1) colnames = [str(coldesc[0]) for coldesc in description] self.CreateColumnSchema(description, XMLTree) # Actual row data gets written here Data = ElementTree.SubElement(XMLTree, 'rs:data') for row in rows: Row = ElementTree.SubElement(Data, 'z:row') for colnum, column in enumerate(colnames): Row.set(column.upper(), str(row[colnum])) filePrefix = self.GetExportDirectory() fullname = os.path.join(filePrefix, filename) tree = ElementTree.ElementTree(XMLTree) if compress: # If the name ends with .zip, we want to chop this off the filename # Otherwise, we want to add it on to the fullname if fullname[-4:] == '.zip': filename = filename[:-4] else: fullname += '.zip' self.errorhandler.logtrace("writing %s as %s" % (fullname, filename)) zipFile = zipfile.ZipFile(fullname, 'w', zipfile.ZIP_DEFLATED) XML = StringIO() tree.write(XML) zipFile.writestr(str(filename), XML.getvalue()) zipFile.close() else: fp = open(str(fullname),'w') UglyXML = StringIO() tree.write(UglyXML) prettyXML = minidom.parseString(UglyXML.getvalue()) fp.write(prettyXML.toprettyxml()) fp.close() def ReadNumRows(self, filename, compressed=False): if compressed: tree = unzipAndParse(filename) else: tree = ElementTree.parse(open(filename)) treeRoot = tree.getroot() rowset = treeRoot.find(str(ElementTree.QName(xmlns_rs,"data"))) if not rowset: return 0 rows = rowset.findall(str(ElementTree.QName(xmlns_z,"row"))) return len(rows) class TableImporter: def __init__(self, config, errorhandler=None): """constructs the jHistImportManager...""" if errorhandler is None: self.errorhandler = errors.ConsoleErrorHandler() else: self.errorhandler = errorhandler #Get the config self.config = config DatabaseConfig = self.GetDatabaseConfigInfo() #Set the date formatting text if DatabaseConfig.DBTYPE == 'access': self.dateformatstr = "format(logtime, 'YYYYMMDD')" self.datetimeformatstr = "format(logtime, 'YYYYMMDD_HH')" elif DatabaseConfig.DBTYPE == 'sqlserver': self.dateformatstr = "convert(char(8), logtime, 112)" self.datetimeformatstr = "convert(char(8), logtime, 112)+'_'+convert(char(2), logtime, 8)" elif DatabaseConfig.DBTYPE in ['oracle','postgres']: self.dateformatstr = "to_char(logtime, 'YYYYMMDD')" self.datetimeformatstr = "to_char(logtime, 'YYYYMMDD_HH24')" self.db = database.dbwrapper(DatabaseConfig, errorhandler=self.errorhandler, generalcache=False) # set up the list of changed files self.instdir = self.GetStorageDirectory() def GetDatabaseConfigInfo(self): raise NotImplementedError("TableImporter.GetDatabaseConfigInfo()") def GetStorageDirectory(self): raise NotImplementedError("TableImporter.GetStorageDirectory()") def CheckTable(self, tableName, tableXML, IDColumns, checkMod=True): #First, check for existence of table columnSchema = tableXML.find(str(ElementTree.QName(xmlns_s,"Schema"))) columns = self.CheckTableStructure(tableName, columnSchema, checkMod) if not columns: self.errorhandler.logtrace("Creating table %s" % tableName) columns = self.CreateTableFromSchema(tableName, columnSchema) rowSet = tableXML.find(str(ElementTree.QName(xmlns_rs,"data"))) # self.errorhandler.logtrace("checking table %s" % tableName) for row in rowSet.findall("{%s}row" % xmlns_z): IDValues = dict([(ID, row.get(ID.upper())) for ID in IDColumns]) IDConditions = ["%s = %s" % (ID, self.db.dbrepr(ConvertValue(IDValues[ID], columns[ID.lower()]))) for ID in IDColumns] rowSQL = "SELECT * FROM %s WHERE " % tableName rowSQL += " AND ".join(IDConditions) rowdict = self.db.singlerowdict(rowSQL, toupper) self.CheckAndModifyRow(tableName, row, IDColumns, rowdict, columns, checkMod) def CheckAndModifyRow(self, table, XMLEquiv, IDColumns, rowdict, coltypedict, checkMod=True): """Checks to see if rows are the same. If not, modifies the row accordingly. If the rowdict is not given, adds the row.""" # TODO: Add formattedtype stuff for dates cols = XMLEquiv.keys() if not rowdict[IDColumns[0].upper()]: values = {} for col in cols: values[col.lower()] = ConvertValue(XMLEquiv.get(col), coltypedict[col.lower()]) self.db.insert(table, values) elif checkMod: modified = 0 for col in cols: if str(rowdict[col]) != XMLEquiv.get(col): modified = 1 break if modified: values = {} for col in cols: if col.lower() not in IDColumns: values[col.lower()] = ConvertValue(XMLEquiv.get(col), coltypedict[col.lower()]) self.db.update(table, IDColumns, [ConvertValue(XMLEquiv.get(ID.upper()), coltypedict[ID.lower()]) for ID in IDColumns], values) def RecreateTable(self, tableName, tableXML, whereClause=''): self.db.execute("DELETE FROM "+tableName + whereClause) rowSet = tableXML.find(str(ElementTree.QName(xmlns_rs,"data"))) for row in rowSet.findall("{%s}row" % xmlns_z): cols = row.keys() values = {} for col in cols: values[col.lower()] = ConvertValue(row.get(col)) try: self.db.insert(tableName, values) except Exception, e: self.errorhandler.logerror("Error inserting row into table %s: %s" % (tableName, e)) def CreateTableFromSchema(self, tableName, columnSchema, primaryKeys=None): """Creates an event manager table""" columns = {} if primaryKeys is None: primaryKeys = [] primaryKeys = [key.upper() for key in primaryKeys] types = self.db.dbtypenames elements = columnSchema.find(str(ElementTree.QName(xmlns_s,"ElementType"))) SQL = "CREATE TABLE "+tableName+" (" for attribute in elements.findall("{%s}AttributeType" % xmlns_s): name = attribute.get("name") datatypeElem = attribute.find(str(ElementTree.QName(xmlns_s,"datatype"))) genericdatatype = typemap[datatypeElem.get(ElementTree.QName(xmlns_dt,"type"))] datatype = types[genericdatatype] SQL += "%s %s" % (name, datatype) columns[name.lower()] = genericdatatype if name in primaryKeys: SQL += " NOT NULL" SQL += ", " if primaryKeys != []: SQL += "PRIMARY KEY (%s), " % ','.join(primaryKeys) SQL = SQL[:-2] + ")" self.db.execute(SQL) return columns def CheckTableStructure(self, tableName, columnSchema, checkMod): # First, get ourselves a rowset backtypemap = dict([(v, k) for k, v in self.db.dbtypenames.items()]) SQL = "SELECT * FROM "+tableName+" WHERE 0 = 1" try: rows, description = self.db.allrows(SQL,1) except: return None # In this case, don't check full structure if not checkMod: return dict([(coldesc[0].lower(), backtypemap[coldesc[1].lower()]) for coldesc in description]) # colnames are the names of the columns from the database # The second value in the tuple denotes a column which is there colnames = dict([(str(coldesc[0].lower()),False) for coldesc in description]) # descnames are the column names in the schema descnames = {} types = self.db.dbtypenames elements = columnSchema.find(str(ElementTree.QName(xmlns_s,"ElementType"))) for attribute in elements.findall("{%s}AttributeType" % xmlns_s): name = attribute.get("name").lower() datatypeElem = attribute.find(str(ElementTree.QName(xmlns_s,"datatype"))) datatype = types[typemap[datatypeElem.get(ElementTree.QName(xmlns_dt,"type"))]] if not colnames.has_key(name): descnames[name] = (False, datatype) else: descnames[name] = (True, datatype) colnames[name] = True # Drop unused columns for column in colnames.keys(): if colnames[column] == False: self.errorhandler.logtrace("Dropping unused column %s from table %s" % (column,tableName)) SQL = "ALTER TABLE "+tableName+" DROP COLUMN "+column self.db.execute(SQL) # Add new columns for column in descnames.keys(): if descnames[column][0] == False: self.errorhandler.logtrace("Adding new column %s to table %s" % (column, tableName)) SQL = "ALTER TABLE %s ADD %s %s" % (tableName, column, descnames[column][1]) self.db.execute(SQL) return dict([(key.lower(), backtypemap[descnames[key][1].lower()]) for key in descnames.keys()])