def xmldata2sql(self, node):
tablename = node.attributes['name'].value
conditions = {
'DEF_timestamp': 'NOW()',
'REF|ApplicationData|application': '%s'
}
fieldvalues = {}
values = []
fields = []
sqlinserts = []
for n in node.childNodes:
if n.nodeName == 'field':
d = n.attributes
fname = self.getAttribute(d, 'name')
if conditions.has_key(fname):
fvalue = conditions[fname]
elif n.firstChild is not None:
fvalue = "'%s'" % (sqldb.escape(n.firstChild.data), )
else:
fvalue = "''"
fieldvalues[fname] = fvalue
if fname <> 'DEF_id':
fields.append(sqldb.addbackquotes(fname))
values.append(fvalue)
sqlinsert = "INSERT into `%s` (%s) VALUES (%s)" % (
tablename, ', '.join(fields), ', '.join(values))
self.curtable = tablename
self.curinsert = sqlinsert
self.curfieldvalues = fieldvalues
def xmldata2sql(self, node):
tablename = node.attributes['name'].value
conditions = {
'DEF_timestamp': 'NOW()',
'REF|ApplicationData|application': '%s'
}
fieldvalues={}
values=[]
fields=[]
sqlinserts = []
for n in node.childNodes:
if n.nodeName == 'field':
d = n.attributes
fname = self.getAttribute(d, 'name')
if conditions.has_key(fname):
fvalue = conditions[fname]
elif n.firstChild is not None:
fvalue = "'%s'" % (sqldb.escape(n.firstChild.data),)
else:
fvalue = "''"
fieldvalues[fname]=fvalue
if fname <> 'DEF_id':
fields.append(sqldb.addbackquotes(fname))
values.append(fvalue)
sqlinsert = "INSERT into `%s` (%s) VALUES (%s)" % (tablename, ', '.join(fields), ', '.join(values))
self.curtable = tablename
self.curinsert = sqlinsert
self.curfieldvalues = fieldvalues
class ImportExport:
"""ImportExport: An object class to import/export Leginon II applications from
database to another via a XML file
"""
def __init__(self):
self.db = sqldb.sqlDB()
self.warning = ""
self.information = ""
def setDBparam(self, **kwargs):
self.db = sqldb.sqlDB(**kwargs)
def getMessageLog(self):
return { 'warning': self.warning,
'information': self.information
}
def importApplication(self, filename):
try:
xmlapp = XMLApplicationImport(filename)
except IOError,e:
raise ApplicationImportError(e)
return
# Create SQL tables
sqldef = xmlapp.getSQLDefinitionQueries()
for q in sqldef:
self.db.execute(q)
# Check if application exists
application = xmlapp.getFieldValues()['ApplicationData']
sqlwhere=[]
for k,v in application.items():
if not re.findall('^DEF_',k):
sqlwhere.append('%s=%s' % (sqldb.addbackquotes(k),v))
where = ' 1 '
if len(sqlwhere):
where = ' AND '.join(sqlwhere)
checkquery = "Select `DEF_id`, name, version from ApplicationData where %s" % (where,)
check = self.db.selectone(checkquery)
check = False
# Insert New Application data
if check:
raise ApplicationImportError("Application %s (version %d) exists" % (check['name'], check['version']))
else:
q = xmlapp.getSQLApplicationQuery()
applicationId = self.db.insert(q)
queries = xmlapp.getSQLDataQueries()
for q in queries:
try:
q = q % (applicationId,)
except:
pass
self.db.insert(q)
checkquery = "Select `DEF_id`, name, version from ApplicationData where %s" % (where,)
check = self.db.selectone(checkquery)
self.information = "Application %s-%s inserted sucessfully" % (check['name'], check['version'])
def getSQLTableDefinitionXML(self, table):
"""
Convert any SQL Table definition into XML. For example:
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| DEF_id | int(16) | | PRI | NULL | auto_increment |
| DEF_timestamp | timestamp(14) | YES | MUL | NULL | |
| name | text | | | | |
| version | int(11) | | | 0 | |
+---------------+---------------+------+-----+---------+----------------+
=>
<sqltable name="ApplicationData" >
<field name="DEF_id" type="int(16)" null="NOT NULL" extra="auto_increment" />
<field name="DEF_timestamp" type="timestamp(14)" />
<field name="name" type="text" null="NOT NULL" />
<field name="version" type="int(11)" default="DEFAULT '0'" null="NOT NULL" />
<key>PRIMARY KEY (`DEF_id`)</key>
<key>KEY `DEF_timestamp` (`DEF_timestamp`)</key>
</sqltable>
"""
schema_create = ' <sqltable name="%s" >%s' % (table, self.crlf)
rows = self.db.selectall("SHOW FIELDS FROM `%s`" % (table, ))
for row in rows:
schema_create += ' <field %s' % (self.crlf, )
schema_create += ' name="%s" %s' % (row['Field'], self.crlf)
schema_create += ' type="%s" %s' % (row['Type'], self.crlf)
if row['Default']:
schema_create += ' default="DEFAULT \'%s\'"%s' % (
row['Default'], self.crlf)
if not row['Null']:
schema_create += ' null="NOT NULL"%s' % (self.crlf, )
if row['Extra']:
schema_create += ' extra="%s"%s' % (row['Extra'],
self.crlf)
schema_create += " />%s" % (self.crlf, )
keys = self.db.selectall("SHOW KEYS FROM `%s`" % (table, ))
keys_dict = {}
for key in keys:
kname = key['Key_name']
if kname != 'PRIMARY':
kname = sqldb.addbackquotes(kname)
if key['Non_unique'] == 0 and kname != 'PRIMARY':
kname = "UNIQUE|%s" % (kname, )
if not keys_dict.has_key(kname):
keys_dict[kname] = []
keys_dict[kname].append(sqldb.addbackquotes(key['Column_name']))
for key, value in keys_dict.items():
schema_create += ' <key>'
value_str = ', '.join(value)
if key == 'PRIMARY':
schema_create += 'PRIMARY KEY (%s)' % (value_str, )
elif re.findall('^UNIQUE\|', key):
k = re.sub('^UNIQUE\|', '', key)
schema_create += 'UNIQUE %s (%s)' % (k, value_str)
else:
schema_create += 'KEY %s (%s)' % (key, value_str)
schema_create += '</key>%s' % (self.crlf, )
schema_create += ' </sqltable>%s' % (self.crlf, )
return schema_create
def getSQLTableDefinitionXML(self,table):
"""
Convert any SQL Table definition into XML. For example:
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| DEF_id | int(16) | | PRI | NULL | auto_increment |
| DEF_timestamp | timestamp(14) | YES | MUL | NULL | |
| name | text | | | | |
| version | int(11) | | | 0 | |
+---------------+---------------+------+-----+---------+----------------+
=>
<sqltable name="ApplicationData" >
<field name="DEF_id" type="int(16)" null="NOT NULL" extra="auto_increment" />
<field name="DEF_timestamp" type="timestamp(14)" />
<field name="name" type="text" null="NOT NULL" />
<field name="version" type="int(11)" default="DEFAULT '0'" null="NOT NULL" />
<key>PRIMARY KEY (`DEF_id`)</key>
<key>KEY `DEF_timestamp` (`DEF_timestamp`)</key>
</sqltable>
"""
schema_create = ' <sqltable name="%s" >%s' % (table,self.crlf)
rows = self.db.selectall("SHOW FIELDS FROM `%s`" % (table,))
for row in rows:
schema_create += ' <field %s' % (self.crlf,)
schema_create += ' name="%s" %s' % (row['Field'], self.crlf)
schema_create += ' type="%s" %s' % (row['Type'], self.crlf)
if row['Default']:
schema_create += ' default="DEFAULT \'%s\'"%s' % (row['Default'], self.crlf)
if not row['Null']:
schema_create += ' null="NOT NULL"%s' % (self.crlf,)
if row['Extra']:
schema_create += ' extra="%s"%s' % (row['Extra'], self.crlf)
schema_create += " />%s" % (self.crlf,)
keys = self.db.selectall("SHOW KEYS FROM `%s`" % (table,))
keys_dict={}
for key in keys:
kname = key['Key_name']
if kname!='PRIMARY':
kname = sqldb.addbackquotes(kname)
if key['Non_unique']==0 and kname!='PRIMARY':
kname = "UNIQUE|%s" % (kname,)
if not keys_dict.has_key(kname):
keys_dict[kname]=[]
keys_dict[kname].append(sqldb.addbackquotes(key['Column_name']))
for key,value in keys_dict.items():
schema_create += ' <key>'
value_str = ', '.join(value)
if key=='PRIMARY':
schema_create += 'PRIMARY KEY (%s)' % (value_str,)
elif re.findall('^UNIQUE\|',key):
k = re.sub('^UNIQUE\|', '', key)
schema_create += 'UNIQUE %s (%s)' % (k, value_str)
else:
schema_create += 'KEY %s (%s)' % (key, value_str)
schema_create += '</key>%s' % (self.crlf,)
schema_create += ' </sqltable>%s' % (self.crlf,)
return schema_create
