gnue/gnuef/src/drivers/mysql DBdriver.py

[Jade Meskill]

CVSROOT:        /home/cvs
Module name:    gnue
Changes by:     Jade Meskill <address@hidden>   00/12/19 14:49:54

Modified files:
        gnuef/src/drivers/mysql: DBdriver.py 

Log message:
        MySQL driver is now fully functional

CVSWeb URLs:
http://subversions.gnu.org/cgi-bin/cvsweb/gnue/gnuef/src/drivers/mysql/DBdriver.py.diff?r1=1.3&r2=1.4

Patches:
Index: gnue/gnuef/src/drivers/mysql/DBdriver.py
diff -u gnue/gnuef/src/drivers/mysql/DBdriver.py:1.3 
gnue/gnuef/src/drivers/mysql/DBdriver.py:1.4
--- gnue/gnuef/src/drivers/mysql/DBdriver.py:1.3        Fri Dec 15 19:32:11 2000
+++ gnue/gnuef/src/drivers/mysql/DBdriver.py    Tue Dec 19 14:49:54 2000
@@ -22,78 +22,99 @@
       print "MySQL database driver initializing"
     self.connection = None
     self.cursor = None
+    self.uniqueKey = None
+    self.oldMaskData = {}
+    self.maskCount = 1
 
   def connect(self, dbhost, dbname, dbuser, dbpasswd):
     if GFOptions.DEBUG:
       print "connecting"
     self.connection = MySQLdb.connect(db=dbname, host=dbhost, user=dbuser, 
passwd=dbpasswd)
+    self.connection.cursorclass = MySQLdb.DictCursor
     self.cursor = self.connection.cursor()
     
   def disconnect(self):
     self.connection.close()
 
   def commit(self):
-    self.cursor.commit()
     pass
     
   def rollback(self):
-    self.cursor.rollback()
+    pass
+
+  def insert(self, table, mask):
+    for fieldname in mask.keys():
+      if mask[fieldname] == None or len(mask[fieldname]) == 0:
+        del mask[fieldname]
 
-  def insert(self,table,mask):
     command = self.constructSQL('insert', table, mask)
+    print command
     self.cursor.execute(command)
+    mask[self.uniqueKey] = self.getMaskCount()
+    print mask
     return mask
   
-  def delete(self,table,mask):
+  def delete(self, table, mask):
     command = self.constructSQL('delete', table, mask)
     self.cursor.execute(command)
 
-  def update(self,table,mask):
+  def update(self, table, mask):
     command = self.constructSQL('update', table, mask)
     self.cursor.execute(command)
 
-  def getFieldList(self,table):
+  def getFieldList(self, table):
     l = {}
     cr = self.connection.cursor()
-    cr.execute('SELECT * FROM %s LIMIT 1' % table)
-    for d in cr.description:
-      l[d[0]] = 'text'
+    cr.execute('DESCRIBE %s' % table)
+    rs = cr.fetchallDict()
+    self.getUniqueKey(table)
+    for r in rs:
+      l[r["Field"]] = "text"
     return l
+
+    # Primary key fetching code, will possibly be used in later implementation
+    #  if r["Key"] == "PRI":
+    #    print "PK = %s" % (r["Field"])
+    #    self.primaryKey[r["Field"]] = ''
+    #for d in cr.description:
+    #  l[d[0]] = 'text'
 
-  def query(self,table,mask):
+
+  def query(self, table, mask, order_by = None):
     command = self.constructSQL('query',table, mask)
+    print command
     self.cursor.execute(command)
-    resultset = self.cursor.fetchall()
-    rs = []
-    dsc = {}
-    for d in self.cursor.description:
-      dsc[len(dsc)] = d[0]
-    for rec in resultset:
-      nrc = {}
-      for r in rec:
-        nrc[dsc[len(nrc)]] = r
-      print nrc
-      rs.append(nrc)
+    rs = self.cursor.fetchallDict()
+    self.oldMaskData = {}
+    self.maskCount = 1
+    for r in rs:
+      r[self.uniqueKey] = self.getMaskCount()
+      self.oldMaskData[int(r[self.uniqueKey])] = self.copyDict(r)
     return rs
-  
+
   def constructSQL(self, style, table, mask = None):
     sql = None
     fields = None
     qualifier = None
 
+    print style
+
     if style == 'query':
       action = "SELECT "
       location = "FROM %s " % (table)
-      fields = " oid AS oid_%s, * " % (table)
+      fields = " * "
       
       if type(mask) == type({}):
-        fields = " %s " % (string.join(mask.keys(), ','))
+        fields = "%s" % (string.join(mask.keys(), ','))
+        fields = string.replace(fields, "%s," % (self.uniqueKey), '')
+        fields = "%s " % (fields)
         for fieldname in mask.keys():
-          if mask[fieldname] != None and len(mask[fieldname]):
-            if qualifier == None:
-              qualifier = "WHERE %s LIKE  '%s'" % (fieldname,mask[fieldname])
-            else:
-              qualifier = "%s AND %s LIKE  '%s'" % (qualifier, 
fieldname,mask[fieldname])
+          if fieldname != self.uniqueKey:
+            if mask[fieldname] != None and len(mask[fieldname]):
+              if qualifier == None:
+                qualifier = "WHERE %s LIKE '%s'" % (fieldname,mask[fieldname])
+              else:
+                qualifier = "%s AND %s LIKE  '%s'" % (qualifier, 
fieldname,mask[fieldname])
 
       sql = action + fields + location + (qualifier and qualifier or "")
 
@@ -105,11 +126,12 @@
       if type(mask) == type({}):
         fields = "(%s) " % (string.join(mask.keys(), ','))
         for fieldname in mask.keys():  
-          if mask[fieldname] != None and len(mask[fieldname]):
-            if values == None:
-              values = "VALUES ('%s'" % (mask[fieldname])
-            else:
-              values = "%s, '%s'" % (values, mask[fieldname])
+          if fieldname != self.uniqueKey:
+            if mask[fieldname] != None and len(mask[fieldname]):
+              if values == None:
+                values = "VALUES ('%s'" % (mask[fieldname])
+              else:
+                values = "%s, '%s'" % (values, mask[fieldname])
         values = "%s)" % (values)
         
       sql = action + location + fields + values + (qualifier and qualifier or 
"")
@@ -117,12 +139,51 @@
     elif style == 'update':
       action = "UPDATE "
       location = "%s " % (table)
-      values = None
+      values = " SET "
+      qualifier = " WHERE "
 
+      if type(mask) == type({}):
+        for fieldname in mask.keys():
+          if fieldname != self.uniqueKey:
+            if mask[fieldname] != None and len(mask[fieldname]):
+              values = "%s %s='%s'," % (values, fieldname, mask[fieldname])
+              qualifier = "%s %s='%s' AND" % (qualifier, fieldname, 
self.oldMaskData[int(mask[self.uniqueKey])][fieldname])
+        values = values[:-1]
+        qualifier = qualifier[:-3]
+        
+      sql = action + location + values + (qualifier and qualifier or "")
+
+    elif style == 'delete':
+      action = "DELETE FROM "
+      location = "%s " % (table)
+      values = ""
+      qualifier = " WHERE "
+
+      if type(mask) == type({}):
+        for fieldname in mask.keys():
+          if fieldname != self.uniqueKey:
+            if mask[fieldname] != None and len(mask[fieldname]):
+              qualifier = "%s %s='%s' AND" % (qualifier, fieldname, 
self.oldMaskData[int(mask[self.uniqueKey])][fieldname])
+        qualifier = qualifier[:-3]
+        
+      sql = action + location + values + (qualifier and qualifier or "")
+
     else:
       print "constructSQL: unsupport SQL statement type"
 
-    if GFOptions.DEBUG > 50:
-      print sql
-
     return sql
+
+  def getUniqueKey(self, table):
+    self.uniqueKey = "uniqueKey_%s" % (table)
+    return self.uniqueKey
+
+  def getMaskCount(self):
+    self.maskCount = self.maskCount + 1
+    return self.maskCount - 1
+
+  def copyDict(self, dict):
+    nd = {}
+    for key in dict.keys():
+      nd[key] = dict[key]
+
+    return nd