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
Beispiel #2
0
	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
Beispiel #5
0
	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