Re: [RFC/gdl2] Postgres Adaptor

[David Ayers]

David Ayers wrote:

> Thanks.  Patch is coming up...

Here it is:

   * EOControl/EONSAddOns.h/m:
   ([NSString -parsedFirstVersionSubstring]): New method.
   * EOAdaptors/Postgres95/Postgres95Adaptor.h/m: Added include of
   pg_config.h to access PG_VERSION.  Added databaseVersion to list
   of meaningful connectionDictionary keys.
   (postgresClientVersion): New function.
   * EOAdaptors/Postgres95/Postgres95Channel.h/m:  Added instance
   variable to hold the version of the database server.
   (pgResultDictionary): New static function for debuging.
   ([Postgres95Channel -_readServerVersion]): New method to set Server
   Version.
   ([Postgres95Channel -openChannel]): Call _readServerVersion method.
   ([Postgres95Channel -describeTableNames]): Adapt select statement
   according to database version.
   * Postgres95/Postgres95SQLExpression.m:
   ([Postgres95SQLExpression +dropTableStatementsForEntityGroup:]):
   Adapt select statement according to database version supplied in
   connectionDictionary of the entites model.

I think my NSString catagory -parsedFirstVersionSubstring is a bit clumsy.
I'm open to alternatives.  Otherwise I'll commit this soon.

Cheers,
David

Index: EOAdaptors/Postgres95/Postgres95Adaptor.h
===================================================================
RCS file: 
/cvsroot/gnustep/gnustep/dev-libs/gdl2/EOAdaptors/Postgres95/Postgres95Adaptor.h,v
retrieving revision 1.2
diff -u -r1.2 Postgres95Adaptor.h
--- EOAdaptors/Postgres95/Postgres95Adaptor.h   31 Mar 2003 00:24:15 -0000      
1.2
+++ EOAdaptors/Postgres95/Postgres95Adaptor.h   23 Jun 2003 15:50:25 -0000
@@ -39,6 +39,7 @@
 #include <stdio.h>
 #include <libpq-fe.h>
 #include <libpq/libpq-fs.h>
+#include <pg_config.h>
 #undef Assert
 
 @class NSMutableArray;
@@ -50,6 +51,8 @@
        (default getenv(PGHOST) or localhost)
     databaseName - the name of the database to use 
        (default getenv(PGDATABASE))
+    databaseVersion - the Version of the database
+       (default parsed from #define PG_VERSION)
     options - additional options sent to the POSTGRES95 backend
        (default getenv(PGOPTIONS))
     port - port to communicate with POSTGRES95 backend
@@ -63,6 +66,9 @@
           real user id of the user running the program and that user id
          is interpreted by the server (AFAIK)
 */
+
+extern int
+postgresClientVersion();
 
 @interface Postgres95Adaptor : EOAdaptor
 {
Index: EOAdaptors/Postgres95/Postgres95Adaptor.m
===================================================================
RCS file: 
/cvsroot/gnustep/gnustep/dev-libs/gdl2/EOAdaptors/Postgres95/Postgres95Adaptor.m,v
retrieving revision 1.7
diff -u -r1.7 Postgres95Adaptor.m
--- EOAdaptors/Postgres95/Postgres95Adaptor.m   31 Mar 2003 00:24:15 -0000      
1.7
+++ EOAdaptors/Postgres95/Postgres95Adaptor.m   23 Jun 2003 15:50:25 -0000
@@ -56,6 +56,7 @@
 #include <Foundation/Foundation.h>
 #endif
 
+#include <EOControl/EONSAddOns.h>
 #include <EOControl/EODebug.h>
 
 #include <EOAccess/EOAccess.h>
@@ -74,6 +75,18 @@
 NSString *Postgres95Exception = @"Postgres95Exception";
 static int pgConnTotalAllocated = 0;
 static int pgConnCurrentAllocated = 0;
+
+int
+postgresClientVersion()
+{
+  static int version = 0;
+  if (version == 0)
+    {
+      NSString *versionString = [NSString stringWithCString: PG_VERSION];
+      version = [versionString parsedFirstVersionSubstring];
+    }
+  return version;
+}
 
 
 @implementation Postgres95Adaptor
Index: EOAdaptors/Postgres95/Postgres95Channel.h
===================================================================
RCS file: 
/cvsroot/gnustep/gnustep/dev-libs/gdl2/EOAdaptors/Postgres95/Postgres95Channel.h,v
retrieving revision 1.2
diff -u -r1.2 Postgres95Channel.h
--- EOAdaptors/Postgres95/Postgres95Channel.h   31 Mar 2003 00:24:15 -0000      
1.2
+++ EOAdaptors/Postgres95/Postgres95Channel.h   23 Jun 2003 15:50:25 -0000
@@ -52,6 +52,7 @@
   BOOL _isFetchInProgress;
   BOOL _fetchBlobsOid;
   NSArray *_pkAttributeArray;
+  int _pgVersion;
 
   struct {
     unsigned int postgres95InsertedRowOid:1;
@@ -66,6 +67,7 @@
 
 - (void)_cancelResults;
 - (void)_describeResults;
+- (void)_readServerVersion;
 
 /* Private methods */
 - (char*)_readBinaryDataRow: (Oid)oid length: (int*)length zone: (NSZone*)zone;
Index: EOAdaptors/Postgres95/Postgres95Channel.m
===================================================================
RCS file: 
/cvsroot/gnustep/gnustep/dev-libs/gdl2/EOAdaptors/Postgres95/Postgres95Channel.m,v
retrieving revision 1.9
diff -u -r1.9 Postgres95Channel.m
--- EOAdaptors/Postgres95/Postgres95Channel.m   22 Jun 2003 16:36:40 -0000      
1.9
+++ EOAdaptors/Postgres95/Postgres95Channel.m   23 Jun 2003 15:50:25 -0000
@@ -57,6 +57,7 @@
 #include <EOControl/EONull.h>
 #include <EOControl/EOQualifier.h>
 #include <EOControl/EOFetchSpecification.h>
+#include <EOControl/EONSAddOns.h>
 #include <EOControl/EODebug.h>
 
 #include <EOAccess/EOAttribute.h>
@@ -77,6 +78,77 @@
   __dummy_function_used_for_linking();
 }
 
+#define NSS_SWF NSString stringWithFormat
+static NSDictionary *
+pgResultDictionary(PGresult *pgResult)
+{
+  int nfields, ntuples;
+  int i, j;
+  NSMutableArray *fields;
+  NSMutableArray *tuples;
+  ExecStatusType statusType;
+
+  nfields = PQnfields(pgResult);
+  ntuples = PQntuples(pgResult);
+
+  fields = [NSMutableArray arrayWithCapacity: nfields];
+  tuples = [NSMutableArray arrayWithCapacity: ntuples];
+
+  for (i = 1; i <= nfields; i++)
+    {
+      char *fname;
+      fname = PQfname(pgResult, i);
+      [fields addObject: [NSDictionary dictionaryWithObjectsAndKeys:
+       [NSS_SWF:@"%s",  fname], @"PQfname",
+       [NSS_SWF:@"%d",  PQfnumber(pgResult, fname)], @"PQfnumber",
+       [NSS_SWF:@"%ud", PQftype(pgResult, i)], @"PQftype",
+       [NSS_SWF:@"%d",  PQfsize(pgResult, i)], @"PQfsize",
+       [NSS_SWF:@"%d",  PQfmod(pgResult, i)], @"PQfmod",
+       nil]];
+    }
+
+  for (i = 1; i <= ntuples; i++)
+    {
+      NSMutableDictionary *tuple;
+      tuple = [NSMutableDictionary dictionaryWithCapacity: nfields];
+      for (j = 1; j <= nfields; j++)
+       {
+         NSString *tupleInfo;
+         NSString *tupleKey;
+         tupleKey = [NSS_SWF:@"%s", PQfname(pgResult, j)];
+         if (PQgetisnull(pgResult, i, j))
+           {
+             tupleInfo = @"NULL";
+           }
+         else
+           {
+             NSString *fmt;
+             fmt = [NSS_SWF: @"%%%ds", PQgetlength(pgResult, i, j)];
+             tupleInfo = [NSS_SWF: fmt, PQgetvalue(pgResult, i, j)];
+           }
+         [tuple setObject: tupleInfo forKey: tupleKey];
+       }
+      [tuples addObject: tuple];
+    }
+
+  statusType = PQresultStatus(pgResult);
+
+  return [NSDictionary dictionaryWithObjectsAndKeys:
+    [NSS_SWF:@"%d",  statusType], @"PQresultStatus",
+    [NSS_SWF:@"%s",  PQresStatus(statusType)], @"PQresStatus",
+    [NSS_SWF:@"%s",  PQresultErrorMessage(pgResult)], @"PQresultErrorMessage",
+    [NSS_SWF:@"%d",  ntuples], @"PQntuples",
+    [NSS_SWF:@"%d",  nfields], @"PQnfields",
+    [NSS_SWF:@"%d",  PQbinaryTuples(pgResult)], @"PQbinaryTuples",
+    [NSS_SWF:@"%s",  PQcmdStatus(pgResult)], @"PQcmdStatus",
+    [NSS_SWF:@"%s",  PQoidStatus(pgResult)], @"PQoidStatus",
+    [NSS_SWF:@"%d",  PQoidValue(pgResult)], @"PQoidValue",
+    [NSS_SWF:@"%s",  PQcmdTuples(pgResult)], @"PQcmdTuples",
+    tuples, @"tuples",
+    fields, @"fields",
+    nil];
+}
+
 @implementation Postgres95Channel
 
 - (id) initWithAdaptorContext: (EOAdaptorContext *)adaptorContext
@@ -129,7 +201,10 @@
   _pgConn = [(Postgres95Adaptor *)[[self adaptorContext] adaptor] newPGconn];
 
   if (_pgConn)
-    [self _describeDatabaseTypes];
+    {
+      [self _readServerVersion];
+      [self _describeDatabaseTypes];
+    }
 }
 
 - (void)closeChannel
@@ -1171,6 +1246,28 @@
   _pgResult = NULL;
 }
 
+- (void)_readServerVersion
+{
+  NSString *version;
+
+  _pgResult = PQexec(_pgConn, 
+                    "SELECT version()");
+
+  if (_pgResult == NULL || PQresultStatus(_pgResult) != PGRES_TUPLES_OK)
+    {
+      _pgResult = NULL;
+      [NSException raise: Postgres95Exception
+                  format: @"cannot read type name informations from database. "
+                  @"bad response from server"];
+    }
+
+  version = [NSString stringWithCString: PQgetvalue(_pgResult, 0, 0)];
+  _pgVersion = [version parsedFirstVersionSubstring];
+
+  PQclear(_pgResult);
+  _pgResult = NULL;
+}
+
 - (NSArray *)attributesToFetch
 {
   return _attributes;
@@ -1320,11 +1417,22 @@
 {
   int i, count;
   NSMutableArray *results = [NSMutableArray array];
+  char *tableSelect;
+
+  if (_pgVersion < 70300)
+    {
+      tableSelect = "SELECT tablename FROM pg_tables WHERE tableowner != 
'postgres' OR tablename NOT LIKE 'pg_%'";
+    }
+  else
+    {
+      tableSelect = "SELECT tablename FROM pg_tables WHERE 
pg_tables.schemaname = 'public'";
+    }
+
 
   NSAssert(_pgConn, @"Channel not opened");
 
-  _pgResult = PQexec(_pgConn,
-                    "SELECT tablename FROM pg_tables WHERE 
pg_tables.schemaname = 'public'");
+  _pgResult = PQexec(_pgConn, tableSelect);
+                    
 
   if (_pgResult == NULL
       || PQresultStatus(_pgResult) != PGRES_TUPLES_OK)
Index: EOAdaptors/Postgres95/Postgres95SQLExpression.m
===================================================================
RCS file: 
/cvsroot/gnustep/gnustep/dev-libs/gdl2/EOAdaptors/Postgres95/Postgres95SQLExpression.m,v
retrieving revision 1.7
diff -u -r1.7 Postgres95SQLExpression.m
--- EOAdaptors/Postgres95/Postgres95SQLExpression.m     22 May 2003 14:06:35 
-0000      1.7
+++ EOAdaptors/Postgres95/Postgres95SQLExpression.m     23 Jun 2003 15:50:25 
-0000
@@ -44,10 +44,12 @@
 #endif
 
 #include <EOControl/EONull.h>
+#include <EOControl/EONSAddOns.h>
 #include <EOControl/EODebug.h>
 
 #include <EOAccess/EOAttribute.h>
 #include <EOAccess/EOEntity.h>
+#include <EOAccess/EOModel.h>
 #include <EOAccess/EOSchemaGeneration.h>
 
 #include <Postgres95EOAdaptor/Postgres95SQLExpression.h>
@@ -305,18 +307,30 @@
 
 + (NSArray *)dropTableStatementsForEntityGroup:(NSArray *)entityGroup
 {
-  // We redefine this method to add the CASCADE: it is needed to delete
-  // associated foreign key constraints when dropping a table.
-  // Q: shouldn't we move this into EOSQLExpression.m?
-  NSArray *newArray = nil;
+  EOEntity *entity;
+  NSArray  *newArray;
+  int       version;
 
   EOFLOGClassFnStartOrCond(@"EOSQLExpression");
 
-  newArray = [NSArray arrayWithObject:
-                       [self expressionForString:
-                               [NSString stringWithFormat: @"DROP TABLE %@ 
CASCADE",
-                                         [[entityGroup objectAtIndex: 0]
-                                           externalName]]]];
+  entity = [entityGroup objectAtIndex: 0];
+  version = [[[[entity model] connectionDictionary]
+             objectForKey: @"databaseVersion"] parsedFirstVersionSubstring];
+  if (version == 0)
+    {
+      version = postgresClientVersion();
+    }
+
+  if (version < 70300)
+    {
+      newArray = [super dropTableStatementsForEntityGroup: entityGroup];
+    }
+  else
+    {
+      newArray = [NSArray arrayWithObject: [self expressionForString:
+       [NSString stringWithFormat: @"DROP TABLE %@ CASCADE",
+                 [entity externalName]]]];
+    }
 
   EOFLOGClassFnStopOrCond(@"EOSQLExpression");
 
Index: EOControl/EONSAddOns.h
===================================================================
RCS file: /cvsroot/gnustep/gnustep/dev-libs/gdl2/EOControl/EONSAddOns.h,v
retrieving revision 1.3
diff -u -r1.3 EONSAddOns.h
--- EOControl/EONSAddOns.h      31 Mar 2003 00:24:15 -0000      1.3
+++ EOControl/EONSAddOns.h      23 Jun 2003 15:50:25 -0000
@@ -28,7 +28,7 @@
 #define __EONSAddOns_h__
 
 #ifndef NeXT_Foundation_LIBRARY
-#include <Foundation/NSObject.h>
+#include <Foundation/NSArray.h>
 #include <Foundation/NSString.h>
 #else
 #include <Foundation/Foundation.h>
@@ -36,7 +36,6 @@
 
 #include <EOControl/EODefines.h>
 
address@hidden NSArray;
 @class NSLock;
 @class NSRecursiveLock;
 
@@ -88,6 +87,10 @@
 
 @interface NSString (YorYes)
 - (BOOL)isYorYES;
address@hidden
+
address@hidden NSString (VersionParsing)
+- (int)parsedFirstVersionSubstring;
 @end
 
 #endif /* __EONSAddOns_h__ */
Index: EOControl/EONSAddOns.m
===================================================================
RCS file: /cvsroot/gnustep/gnustep/dev-libs/gdl2/EOControl/EONSAddOns.m,v
retrieving revision 1.6
diff -u -r1.6 EONSAddOns.m
--- EOControl/EONSAddOns.m      2 May 2003 17:00:23 -0000       1.6
+++ EOControl/EONSAddOns.m      23 Jun 2003 15:50:25 -0000
@@ -38,6 +38,8 @@
 #ifndef NeXT_Foundation_LIBRARY
 #include <Foundation/NSString.h>
 #include <Foundation/NSArray.h>
+#include <Foundation/NSScanner.h>
+#include <Foundation/NSCharacterSet.h>
 #include <Foundation/NSLock.h>
 #include <Foundation/NSUserDefaults.h>
 #include <Foundation/NSNotification.h>
@@ -507,6 +509,39 @@
 }
 
 @end
+
address@hidden NSString (VersionParsing)
+- (int)parsedFirstVersionSubstring
+{
+  NSString       *shortVersion;
+  NSScanner      *scanner;
+  NSCharacterSet *characterSet;
+  NSArray        *versionComponents;
+  NSString       *component;
+  int             count, i;
+  int             version = 0;
+  int             factor[] = { 10000, 100, 1 };
+
+  scanner = [NSScanner scannerWithString: self];
+  characterSet 
+    = [NSCharacterSet characterSetWithCharactersInString: @"0123456789."];
+
+  [scanner setCharactersToBeSkipped: [characterSet invertedSet]];
+  [scanner scanCharactersFromSet: characterSet intoString: &shortVersion];
+
+  versionComponents = [shortVersion componentsSeparatedByString:@"."];
+  count = [versionComponents count];
+
+  for (i = 0; (i < count) && (i < 3); i++)
+    {
+      component = [versionComponents objectAtIndex: i];
+      version += [component intValue] * factor[i];
+    }
+
+  return version;
+}
address@hidden
+
 
 @interface GDL2GlobalLockVendor (private)
 + (void) _setupLocks: (NSNotification *)notif;