def __init__(self,mode="ignore",infile=None):
if infile is None:
infile = openfilecreate('Choose a project file for importing','XML files (*.xml)|*.xml',
style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST)
if infile is None:
return None
(conn, cursor) = openconnection()
#
# get the highest project number so far in the database, add 1, and assign to the
# imported project
#......................................................................
# ask for confirmation before causing a major desaster
dialog = DialogImport(None,_U("import data"),\
_U("This will modify your databases\nIf You are sure, specify what to do with duplicate data")+\
_U("\nElse press CANCEL"))
ret = dialog.ShowModal()
if ret == wx.ID_OK:
mode = "overwrite"
elif ret == wx.ID_IGNORE:
mode = "ignore"
else:
return
logTrack("ImportDB in mode %s"%mode)
# create a dom and import in it the xml project file
self.document = xml.dom.minidom.parse(infile)
# get the elements from the DOM
dbs = self.document.getElementsByTagName('EinsteinDataBase')
for db in dbs:
tables = db.getElementsByTagName("table")
tabledict = {}
for table in tables:
tablename = str(table.getAttribute('name'))
tablelist = []
rows = table.getElementsByTagName("row")
for row in rows:
sqlist = []
sqldict = {}
newdict = {}
nrow = row.getAttribute('n')
elements = row.getElementsByTagName("element")
rowName = None #characteristic name of the row
rowPar1 = None
rowPar2 = None
for element in elements:
fieldname = element.getAttribute('name').lower()
fieldnameCAPS = str(element.getAttribute('name'))
fieldnameCapitalLetters = element.getAttribute('name')
eltype = element.getAttribute('type')
elauto = element.getAttribute('auto')
elvalue = element.getAttribute('value')
if tablename == "dbfluid" and fieldname == "fluidname":
rowName = elvalue
elif tablename == "dbfuel" and fieldname == "fuelname":
rowName = elvalue
elif tablename == "auditor" and fieldname == "name":
rowName = elvalue
elif tablename == "dbelectricitymix":
if fieldname == "country":
rowName = elvalue
elif fieldname == "year":
rowPar1 = elvalue
elif tablename == "dbboiler":
if fieldname == "boilermanufacturer":
rowName = elvalue
elif fieldname == "boilermodel":
rowPar1 = elvalue
elif tablename == "dbchp":
if fieldname == "chpequip":
rowName = elvalue
elif tablename == "dbheatpump":
if fieldname == "hpmanufacturer":
rowName = elvalue
elif fieldname == "hpmodel":
rowPar1 = elvalue
elif tablename == "dbsolarthermal":
if fieldname == "stmanufacturer":
rowName = elvalue
elif fieldname == "stmodel":
rowPar1 = elvalue
if elauto != 'auto_increment':
newdict.update({fieldnameCapitalLetters:elvalue})
# substitute invalid chars in char fields and enclose in ''
if eltype.startswith('char') or \
eltype.startswith('varchar') or \
eltype.startswith('text'):
elvalue = elvalue.encode("utf-8")
if eltype.startswith('date'):
elvalue = "'" + self.subsIllegal(elvalue) + "'"
# substitute auto-increment value with NULL
if elauto == 'auto_increment':
# main key field
oldKey = int(elvalue)
elvalue = 'NULL'
# sqlist.append("%s=%s" % (fieldname,elvalue))
sqldict.update({fieldnameCAPS:elvalue})
#......................................................................
# before inserting new entry, check if entry with the same name exists
# (only for questionnaire, dbfluid, dbfuel
ignoreRow = False
existingRows = []
if tablename == "dbfluid":
existingRows = Status.DB.dbfluid.FluidName[check(rowName)]
elif tablename == "dbfuel":
existingRows = Status.DB.dbfuel.FuelName[check(rowName)]
elif tablename == "auditor":
existingRows = Status.DB.auditor.Name[check(rowName)]
elif tablename == "dbelectricitymix":
if rowPar1 is None:
existingRows = Status.DB.dbelectricitymix.Country[check(rowName)]
else:
existingRows = Status.DB.dbelectricitymix.Country[check(rowName)].Year[rowPar1]
elif tablename == "dbboiler":
if rowPar1 is None:
existingRows = Status.DB.dbboiler.BoilerManufacturer[check(rowName)]
else:
existingRows = Status.DB.dbboiler.BoilerManufacturer[check(rowName)].BoilerModel[check(rowPar1)]
elif tablename == "dbchp":
existingRows = Status.DB.dbchp.CHPequip[check(rowName)]
elif tablename == "dbheatpump":
if rowPar1 is None:
existingRows = Status.DB.dbheatpump.HPManufacturer[check(rowName)]
else:
existingRows = Status.DB.dbheatpump.HPManufacturer[check(rowName)].HPModel[check(rowPar1)]
elif tablename == "dbsolarthermal":
if rowPar1 is None:
existingRows = Status.DB.dbsolarthermal.STManufacturer[check(rowName)]
else:
existingRows = Status.DB.dbsolarthermal.STManufacturer[check(rowName)].STModel[check(rowPar1)]
else:
existingRows = []
if len(existingRows) > 0:
if rowPar2 is None:
par2 = ""
else:
par2 = " " + str(rowPar2)
if rowPar1 is None:
par1 = ""
else:
par1 = " "+str(rowPar1)
if mode == "overwrite":
existingRows[0].update(newdict)
logWarning("%s: %s%s%s"%(tablename,check(rowName),par1,par2)+\
_U(" already in database. Data from imported file will be updated"))
else:
logWarning("%s: %s%s%s"%(tablename,check(rowName),par1,par2)+\
_U(" already in database. Data from imported file will be ignored"))
else:
# create sql sentence and update database
# sql = 'INSERT INTO %s SET ' % (tablename,) + ', '.join(sqlist)
# cursor.execute(sql)
table = Table(Status.DB,tablename)
table.insert(sqldict)
# get last inserted
cursor.execute('SELECT LAST_INSERT_ID() AS last')
field = cursor.fetchone()
conn.close()
def __init__(self,infile=None):
self.pid = None
self.newpid = None
if infile is None:
infile = openfilecreate('Choose a project file for importing','XML files (*.xml)|*.xml',
style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST)
if infile is None:
return None
(conn, cursor) = openconnection()
#
# get the highest project number so far in the database, add 1, and assign to the
# imported project
#NOTE: a dummy row is added and later on deleted in order to obtain the auto-increment
# status of the table. there should be a more elegant way to to this ...
# => to be changed in the future ...
dummyID = Status.DB.questionnaire.insert({"Name":"dummy"})
Status.SQL.commit()
cursor.execute('SELECT MAX(Questionnaire_id) AS n FROM questionnaire')
nrows = cursor.rowcount
if nrows <= 0:
self.newpid = 1
else:
field = cursor.fetchone()
# new pid for this project
self.newpid = int(field['n']) + 1
logDebug("ExportData (importProject): dummyID = %s newpid = %s"%(dummyID,self.newpid))
dummyRows = Status.DB.questionnaire.Questionnaire_ID[dummyID]
if len(dummyRows) > 0:
dummyRows[0].delete()
# create a dom and import in it the xml project file
self.document = xml.dom.minidom.parse(infile)
# get the elements from the DOM
self.projectdict = {}
projects = self.document.getElementsByTagName('EinsteinProject')
for project in projects:
self.pid = project.getAttribute('pid')
tables = project.getElementsByTagName("table")
tabledict = {}
for table in tables:
tablename = str(table.getAttribute('name'))
tablelist = []
rows = table.getElementsByTagName("row")
for row in rows:
sqlist = []
sqldict = {}
nrow = row.getAttribute('n')
elements = row.getElementsByTagName("element")
rowName = None #characteristic name of the row
for element in elements:
fieldname = element.getAttribute('name').lower()
fieldnameCAPS = str(element.getAttribute('name'))
eltype = element.getAttribute('type')
elauto = element.getAttribute('auto')
elvalue = element.getAttribute('value')
if tablename == "questionnaire" and fieldname == "name":
rowName = elvalue
existingRows = Status.DB.questionnaire.Name[check(rowName)]
if len(existingRows) > 0:
showWarning(_U("Project with name %s already exists in database\nProject renamed to: IMPORTED PROJECT")%rowName)
elvalue = 'IMPORTED PROJECT'
elif tablename == "dbfluid" and fieldname == "fluidname":
rowName = elvalue
elif tablename == "dbfuel" and fieldname == "fuelname":
rowName = elvalue
elif tablename == "auditor" and fieldname == "name":
rowName = elvalue
# substitute new pid in id field
if fieldname == 'projectid' or \
fieldname == 'questionnaire_id':
elvalue = self.newpid
# substitute invalid chars in char fields and enclose in ''
if eltype.startswith('char') or \
eltype.startswith('varchar') or \
eltype.startswith('text'):
elvalue = elvalue.encode("utf-8")
if eltype.startswith('date'):
# elvalue = "'" + self.subsIllegal(elvalue) + "'"
elvalue = self.subsIllegal(elvalue)
# substitute auto-increment value with NULL
if elauto == 'auto_increment':
# main key field
oldKey = int(str(elvalue))
elvalue = 'NULL'
# sqlist.append("%s=%s" % (fieldname,elvalue))
sqldict.update({fieldnameCAPS:elvalue})
#......................................................................
# before inserting new entry, check if entry with the same name exists
# (only for questionnaire, dbfluid, dbfuel
ignoreRow = False
if tablename == "dbfluid":
existingRows = Status.DB.dbfluid.FluidName[check(rowName)]
if len(existingRows) > 0:
showWarning(_U("Fluid %s already in database. Data from imported file will be ignored")%rowName)
ignoreRow = True
newID = existingRows[0].DBFluid_ID
elif tablename == "dbfuel":
existingRows = Status.DB.dbfuel.FuelName[check(rowName)]
if len(existingRows) > 0:
showWarning(_U("Fuel %s already in database. Data from imported file will be ignored")%rowName)
ignoreRow = True
newID = existingRows[0].DBFuel_ID
elif tablename == "auditor":
existingRows = Status.DB.auditor.Name[check(rowName)]
if len(existingRows) > 0:
showWarning(_U("Auditor %s already in database. Data from imported file will be ignored")%rowName)
ignoreRow = True
newID = existingRows[0].Auditor_ID
else:
existingRows = []
if ignoreRow == False:
# create sql sentence and update database
#substituted the following block by
# sql = 'INSERT INTO %s SET ' % (tablename,) + ', '.join(sqlist)
# print sql
# cursor.execute(sql)
table = Table(Status.DB,tablename)
newKey = table.insert(sqldict)
# get last inserted
cursor.execute('SELECT LAST_INSERT_ID() AS last')
field = cursor.fetchone()
# 2009-04-06: line eliminated: gave 0 as result !!! substituted by direct assignment
# of newKey (see above)
# newKey = int(field['last'])
else:
newKey = newID
tablelist.append((oldKey,newKey))
tabledict[tablename] = tablelist
self.projectdict[self.pid] = tabledict
conn.close()
#####HS2008-07-14: here restoring links added
for PId in self.projectdict.keys():
Status.prj.restoreLinks(self.newpid,self.projectdict[PId])