gnue/common README.databases src/dbdrivers/gadf...

[Jan Ischebeck]

CVSROOT:        /cvsroot/gnue
Module name:    gnue
Changes by:     Jan Ischebeck <address@hidden>  02/09/24 09:35:06

Modified files:
        common         : README.databases 
        common/src/dbdrivers/gadfly: DBdriver.py 
        common/src/dbdrivers/sqlite: DBdriver.py 

Log message:
        * add gadfly and sqlite to README.databases
        * add schema support to sqlite db

CVSWeb URLs:
http://savannah.gnu.org/cgi-bin/viewcvs/gnue/gnue/common/README.databases.diff?tr1=1.10&tr2=1.11&r1=text&r2=text
http://savannah.gnu.org/cgi-bin/viewcvs/gnue/gnue/common/src/dbdrivers/gadfly/DBdriver.py.diff?tr1=1.2&tr2=1.3&r1=text&r2=text
http://savannah.gnu.org/cgi-bin/viewcvs/gnue/gnue/common/src/dbdrivers/sqlite/DBdriver.py.diff?tr1=1.1&tr2=1.2&r1=text&r2=text

Patches:
Index: gnue/common/README.databases
diff -c gnue/common/README.databases:1.10 gnue/common/README.databases:1.11
*** gnue/common/README.databases:1.10   Wed Sep 11 14:02:39 2002
--- gnue/common/README.databases        Tue Sep 24 09:35:06 2002
***************
*** 16,21 ****
--- 16,23 ----
   * Informix [via Kinfxdb]
   * OpenIngres 1.2, CA Ingres 6.4, CA Ingres II [via ingmod]
   * Sybase ASE 11.0.3/11.9.2 [via Sybase-Python]
+  * SQLite 2.7 [via PySQLite]
+  * Gadfly 1.0
  
  Quick chart of supported middleware:
   * GNUe Appserver [via GNURPC]
***************
*** 454,459 ****
--- 456,541 ----
  
  
  
+ SQLite
+ ======
+ 
+ *** PySQLite [http://pysqlite.sourceforge.net/]
+ 
+     POSIX Support: YES
+     Win32 Support: YES
+ 
+     Platforms Tested:  
+        Debian GNU/Linux 3.0
+ 
+     Description: 
+        SQLite is a powerful embedded relational database management system
+        in a compact C library, developed by D. Richard Hipp. The library is 
+        self-contained ...  itself is uncopyrighted, and free to use for any
+        purpose. 
+ 
+        SQLite is not a client library used to connect to a big database 
server.
+        SQLite is the server. The SQLite library reads and writes directly to 
+        and from the database files on disk.
+ 
+        PySQLite is a Python extension for SQLite that conforms to the Python
+        Database API Specification 2.0. The source is released under the 
+        Python license.
+ 
+ 
+     Example connections.conf entry:
+ 
+        [myconn]
+        provider=sqlite         # Use the SQLite adapter
+        dbname=/usr/db/testdb   # The filename for the SQLite database
+ 
+     Notes:
+ 
+       1. The database engine stores all data in string format. Many 
+          SQL statements won't work. Comparison of date types won't work
+          correctly, etc.
+ 
+ 
+ 
+ Gadfly
+ ======
+ 
+ *** Gadfly [http://gadfly.sourceforge.net/]
+ 
+     POSIX Support: YES
+     Win32 Support: YES
+ 
+     Platforms Tested:  
+        Debian GNU/Linux 3.0
+ 
+     Description: 
+        Gadfly is a simple relational database system implemented in Python
+        based on the SQL Structured Query Language. 
+ 
+        Gadfly is not a client library used to connect to a big database 
server.
+        Gadfly is the server. The Gadfly library reads and writes directly to 
+        and from the database files on disk.
+ 
+ 
+     Example connections.conf entry:
+ 
+        [myconn]
+        provider=gadfly         # Use the gadfly adapter
+        dbname=testdb           # The filename for the gadfly database
+                                # (without extension)
+        directory=/usr/db       # The directory where the gadfly database
+                                # is stored
+ 
+     Notes:
+ 
+       1. The database engine stores all data in string format. Many 
+          SQL statements won't work. Comparison of date types won't work
+          correctly, etc.
+ 
+       2. The database engine doesn't support the sql condition "LIKE", it
+          will replaced by the condition "=" (equal) for the moment.
+ 
+ 
+ 
  
  MIDDLEWARE ADAPTERS
  ===================
***************
*** 468,474 ****
      Win32 Support: YES
  
      Platforms Tested:  
!        ??? (We need your help!)
  
      Description: 
         GNUe's own Enterprise Application Server, currently under heavy
--- 550,557 ----
      Win32 Support: YES
  
      Platforms Tested:  
!        Debian GNU/Linux 3.0
!        MS Windows 98
  
      Description: 
         GNUe's own Enterprise Application Server, currently under heavy
Index: gnue/common/src/dbdrivers/gadfly/DBdriver.py
diff -c gnue/common/src/dbdrivers/gadfly/DBdriver.py:1.2 
gnue/common/src/dbdrivers/gadfly/DBdriver.py:1.3
*** gnue/common/src/dbdrivers/gadfly/DBdriver.py:1.2    Thu Sep 19 08:48:06 2002
--- gnue/common/src/dbdrivers/gadfly/DBdriver.py        Tue Sep 24 09:35:06 2002
***************
*** 162,168 ****
  
    # Return a list of the types of Schema objects this driver provides
    def getSchemaTypes(self):
!     return [('table','Table',1)]
  
    # Return a list of Schema objects
    def getSchemaList(self, type=None):
--- 162,168 ----
  
    # Return a list of the types of Schema objects this driver provides
    def getSchemaTypes(self):
!     return [('view','View',1), ('table','Table',1)]
  
    # Return a list of Schema objects
    def getSchemaList(self, type=None):
***************
*** 187,193 ****
  
    # Find a schema object with specified name
    def getSchemaByName(self, name, type=None):
!     statement = "SELECT '%s' from __table_names__" % (name)
  
      cursor = self._dataConnection.cursor()
      GDebug.printMesg(1,"** Executing: %s **" % statement)
--- 187,193 ----
  
    # Find a schema object with specified name
    def getSchemaByName(self, name, type=None):
!     statement = "SELECT * from __table_names__ WHERE TABLE_NAME='%s'" % (name)
  
      cursor = self._dataConnection.cursor()
      GDebug.printMesg(1,"** Executing: %s **" % statement)
***************
*** 195,203 ****
  
      rs = cursor.fetchone()
      if rs:
!       schema = GDataObjects.Schema(attrs={'id':name, 'name':name,
!                            'type':'table'},
!                            getChildSchema=self.__getFieldSchema)
      else:
        schema = None
  
--- 195,203 ----
  
      rs = cursor.fetchone()
      if rs:
!       schema = GDataObjects.Schema(attrs={'id':rs[1], 'name':rs[1], \
!                                 'type':rs[0] == 1 and 'view' or 'table',},
!                                        getChildSchema=self.__getFieldSchema)
      else:
        schema = None
  
Index: gnue/common/src/dbdrivers/sqlite/DBdriver.py
diff -c gnue/common/src/dbdrivers/sqlite/DBdriver.py:1.1 
gnue/common/src/dbdrivers/sqlite/DBdriver.py:1.2
*** gnue/common/src/dbdrivers/sqlite/DBdriver.py:1.1    Thu Aug  8 17:16:26 2002
--- gnue/common/src/dbdrivers/sqlite/DBdriver.py        Tue Sep 24 09:35:06 2002
***************
*** 32,42 ****
  #     dbname=    This is the SQLite database to use (required)
  #
  
- #### THIS IS AN UNTESTED DRIVER ####
- ####      Any volunteers?       ####
  
! 
! from string import lower
  import sys
  from gnue.common import GDebug, GDataObjects, GConnections
  from gnue.common.dbdrivers._dbsig.DBdriver \
--- 32,39 ----
  #     dbname=    This is the SQLite database to use (required)
  #
  
  
! from string import lower,find,rfind,split,strip
  import sys
  from gnue.common import GDebug, GDataObjects, GConnections
  from gnue.common.dbdrivers._dbsig.DBdriver \
***************
*** 89,94 ****
--- 86,228 ----
    def getLoginFields(self):
      return []
  
+   #
+   # Schema (metadata) functions
+   #
+ 
+   # Return a list of the types of Schema objects this driver provides
+   def getSchemaTypes(self):
+     return [('view','View',1), ('table','Table',1)]
+ 
+   # Return a list of Schema objects
+   def getSchemaList(self, type=None):
+     
+     if type!=None:
+       where=" WHERE type='%s'" % type
+     else:
+       where=""
+ 
+     statement = "SELECT type,name,tbl_name,sql FROM sqlite_master "+\
+                 where+" UNION ALL "+\
+                 "SELECT type,name,tbl_name,sql FROM sqlite_temp_master "+\
+                 where+" ORDER BY name;"
+ 
+     cursor = self._dataConnection.cursor()
+     GDebug.printMesg(1,"** Executing: %s **" % statement)
+     cursor.execute(statement)    
+ 
+     list = []
+     for rs in cursor.fetchall():
+       if rs[0] in ('table','view'):
+         list.append(GDataObjects.Schema(attrs={'id':rs[1], 'name':rs[1], \
+                                                'type':rs[0],},
+                                         getChildSchema=self.__getFieldSchema))
+ 
+     cursor.close()
+     print list
+     return list
+ 
+ 
+   # Find a schema object with specified name
+   def getSchemaByName(self, name, type=None):
+     
+     if type!=None:
+       where=" AND type='%s'" % type
+     else:
+       where=""
+ 
+     statement = ("SELECT type,name,tbl_name,sql FROM sqlite_master "+\
+                  "WHERE name='%s'"+where+" UNION ALL "+\
+                  "SELECT type,name,tbl_name,sql FROM sqlite_temp_master "+\
+                  "WHERE name='%s' "+where+" ORDER BY name;") % (name,name)
+ 
+     cursor = self._dataConnection.cursor()
+     GDebug.printMesg(1,"** Executing: %s **" % statement)
+     cursor.execute(statement)
+ 
+     rs = cursor.fetchone()
+     if rs and rs[0] in ('table','view'):
+       schema = GDataObjects.Schema(attrs={'id':rs[1], 'name':rs[1], \
+                                           'type':rs[0],},
+                                    getChildSchema=self.__getFieldSchema)
+     else:
+       schema = None
+ 
+     cursor.close()
+     return schema
+ 
+ 
+   # Get fields for a table
+   def __getFieldSchema(self, parent):
+ 
+     if parent.type=='view':
+       print "Views are not supported at the moment"
+       return None
+ 
+     statement = ("SELECT type,name,tbl_name,sql FROM sqlite_master "+\
+                  "WHERE type='%s' and name='%s' UNION ALL "+\
+                  "SELECT type,name,tbl_name,sql FROM sqlite_temp_master "+\
+                  "WHERE type='%s' "+\
+                  "and name='%s' ORDER BY name;") % (parent.type,parent.id,\
+                                                     parent.type,parent.id)
+ 
+     cursor = self._dataConnection.cursor()
+     GDebug.printMesg(1,"** Executing: %s **" % statement)
+     cursor.execute(statement)
+     columns = cursor.description
+ 
+     # Because sqlite don't store column definitions, but computes it
+     # every time anew from the 'create table' statement, we have to
+     # parse that statement to get the data
+ 
+     # get sql definition of table
+     rs = cursor.fetchone()
+     cursor.close()
+     if rs:
+       sql=rs[3]
+     else:
+       return None
+ 
+     # parse the sql definition
+     GDebug.printMesg(3,"** Table definition: %s **" % sql)
+ 
+     sql=sql[find(sql,'(')+1:rfind(sql,')')]
+     fields = split(sql,',')
+     list = []
+     for field in fields:
+ 
+       fls=split(strip(field),' ',2)
+ 
+       if not fls[0] in ('Constraint','Primary'):
+         
+         try:
+           nativetype= fls[1][:find(fls[1],'(')]
+ 
+           size=int(fls[1][find(fls[1],'(')+1:-1])
+         except:
+           nativetype = fls[1]
+           size=None
+         
+         attrs={'id': "%s.%s" % (parent.id, fls[0]), 'name': fls[0],
+                'type':'field', 'nativetype': nativetype,
+                'required':fls[2]=="NOT NULL"}
+         
+         if size!=None:
+           attrs['length'] = size
+         
+         if nativetype in ('int','integer','bigint','mediumint',
+                            'smallint','tinyint','float','real',
+                            'double','decimal'):
+           attrs['datatype']='number'
+         elif nativetype[0] in ('date','time','timestamp','datetime'):
+           attrs['datatype']='date'
+         else:
+           attrs['datatype']='text'
+ 
+         list.append(GDataObjects.Schema(attrs=attrs))
+ 
+     return list
+ 
  
  class SQLite_DataObject_Object(SQLite_DataObject, \
        DBSIG_DataObject_Object):
***************
*** 110,115 ****
--- 244,250 ----
  
    def _buildQuery(self, conditions={}):
      return DBSIG_DataObject_SQL._buildQuery(self, conditions)
+ 
  
  
  #