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