PostgreSQL UUID/GUID Data Type Support

Code patches written by our users to solve certain "problems" that were not solved, yet.

Moderators: gto, cipto_kh, EgonHugeist, mdaems

Post Reply
User avatar
royirwan
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 04.10.2005, 02:51
Location: Bogor - Indonesia
Contact:

PostgreSQL UUID/GUID Data Type Support

Post by royirwan »

Hi all,
I need to implement PostgreSQL UUID/GUID data type support in Zeos. I already checked up to latest snapshot (rev 387) n it's not implemented yet. So, I have to patch ZeosDBO for this purpose. And if you need the patch, here is the quick n dirty diffs:

Code: Select all

--- dbc/ZDbcPostgreSqlUtils.pas	(v 6.6.2-rc)
+++ dbc/ZDbcPostgreSqlUtils.pas	(pg uuid patched)
@@ -283,6 +283,8 @@
     Result := stAsciiStream
   else if (TypeName <> '') and (TypeName[1] = '_') then // ARRAY TYPES
     Result := stAsciiStream
+  else if (TypeName = 'uuid') then
+    Result := stString
   else
     Result := stUnknown;
 end;
@@ -328,6 +330,7 @@
     1042: Result := stString; { bpchar }
     22,30: Result := stAsciiStream; { int2vector/oidvector. no '_aclitem' }
     651, 1000..1028: Result := stAsciiStream;
+    2950: Result := stString; { uuid }
     else
       Result := stUnknown;
   end;

Code: Select all

--- dbc/ZDbcPostgreSqlResultSet.pas	(v 6.6.2-rc)
+++ dbc/ZDbcPostgreSqlResultSet.pas	(pg uuid patched)
@@ -183,6 +183,7 @@
   Connection := Statement.GetConnection as IZPostgreSQLConnection;
 
   case TypeOid of
+    2950: ColumnInfo.Precision := 38; { uuid }
     790: ColumnInfo.Currency := True; { money }
     19: ColumnInfo.Precision := 32; { name }
     1186: ColumnInfo.Precision := 32; { interval }
With this patch, uuid treated as normal string field with 38 in size.
No uuid/guid generation algorithm involved here, because I think the uuid/guid generation is not ZeosDBO's responsibility.

Rgds,
Roy Irwan
User avatar
royirwan
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 04.10.2005, 02:51
Location: Bogor - Indonesia
Contact:

Post by royirwan »

Hi again,
If you want to implement PostgreSQL UUID column as TGuidField instead of normal string field (TStringField), here is the quick n dirty patch:

Code: Select all

Index: component\ZAbstractRODataset.pas
===================================================================
--- component\ZAbstractRODataset.pas	(v 6.6.2-rc)
+++ component\ZAbstractRODataset.pas	(pg uuid patch)
@@ -1000,7 +1000,7 @@
             Statement.SetLong(I + 1, StrToInt64(Param.AsString));
           ftCurrency:
             Statement.SetBigDecimal(I + 1, Param.AsCurrency);
-          ftString:
+          ftString, ftGuid:
             Statement.SetString(I + 1, Param.AsString);
           ftBytes:
             Statement.SetString(I + 1, Param.AsString);
@@ -1441,7 +1441,7 @@
       begin
         FieldType := ConvertDbcToDatasetType(GetColumnType(I));
 
-        if FieldType in [ftString, ftWidestring, ftBytes] then
+        if FieldType in [ftString, ftWidestring, ftBytes, ftGuid] then
           Size := GetPrecision(I)
         else Size := 0;
 
@@ -2972,7 +2972,7 @@
               Statement.SetFloat(I + 1, ParamValue.AsFloat);
             ftLargeInt:
               Statement.SetInt(I + 1, ParamValue.AsInteger);
-            ftString:
+            ftString, ftGuid:
               Statement.SetString(I + 1, ParamValue.AsString);
             ftBytes:
               Statement.SetString(I + 1, ParamValue.AsString);
@@ -3020,7 +3020,7 @@
     ftFloat, ftBCD, ftDateTime, ftDateTime, ftDateTime, ftBytes, ftVarBytes,
     ftInteger, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftUnknown,
     ftString, ftString, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
-    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftString, ftTimeStamp, ftFMTBcd
+    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftGuid, ftTimeStamp, ftFMTBcd
     {$IFDEF FPC2_1UP}, ftString, ftBlob{$ENDIF});
 
 {$ELSE}
@@ -3031,7 +3031,7 @@
     ftFloat, ftBCD, ftDateTime, ftDateTime, ftDateTime, ftBytes, ftVarBytes,
     ftInteger, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftUnknown,
     ftString, ftString, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
-    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftString, ftTimeStamp, ftFMTBcd,
+    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftGuid, ftTimeStamp, ftFMTBcd,
     ftFixedWideChar,ftWideMemo,ftOraTimeStamp,ftOraInterval);
  {$ELSE}
  const
@@ -3040,7 +3040,7 @@
     ftFloat, ftBCD, ftDateTime, ftDateTime, ftDateTime, ftBytes, ftVarBytes,
     ftInteger, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftBlob, ftUnknown,
     ftString, ftString, ftLargeInt, ftADT, ftArray, ftReference, ftDataSet,
-    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftString{$IFNDEF VER130}, ftTimestamp, ftFMTBcd{$ENDIF});
+    ftBlob, ftBlob, ftVariant, ftInterface, ftInterface, ftGuid{$IFNDEF VER130}, ftTimestamp, ftFMTBcd{$ENDIF});
  {$ENDIF}
 {$ENDIF}
 
Index: component\ZDatasetUtils.pas
===================================================================
--- component\ZDatasetUtils.pas	(v 6.6.2-rc)
+++ component\ZDatasetUtils.pas	(pg uuid patch)
@@ -314,6 +314,8 @@
       Result := ftBlob;
     stUnicodeString, stUnicodeStream:
       Result := ftWideString;
+    stGuid:
+      Result := ftGuid;
     else
       Result := ftUnknown;
   end;
@@ -355,6 +357,8 @@
       Result := stBinaryStream;
     ftWideString:
       Result := stUnicodeString;//!!!I do not know if it is a stUnicodeString or stUnicodeStream
+    ftGuid:
+      Result := stGuid;
     else
       Result := stUnknown;
   end;
@@ -433,7 +437,7 @@
         RowAccessor.SetLong(FieldIndex, ResultSet.GetLong(ColumnIndex));
       ftCurrency:
         RowAccessor.SetBigDecimal(FieldIndex, ResultSet.GetBigDecimal(ColumnIndex));
-      ftString:
+      ftString, ftGuid:
         RowAccessor.SetPChar(FieldIndex, ResultSet.GetPChar(ColumnIndex));
       ftWidestring:
         RowAccessor.SetUnicodeString(FieldIndex, ResultSet.GetUnicodeString(ColumnIndex));
@@ -503,7 +507,7 @@
       ftCurrency:
         ResultSet.UpdateBigDecimal(ColumnIndex,
           RowAccessor.GetBigDecimal(FieldIndex, WasNull));
-      ftString:
+      ftString, ftGuid:
         ResultSet.UpdatePChar(ColumnIndex, RowAccessor.GetPChar(FieldIndex, WasNull));
       ftWidestring:
         ResultSet.UpdateUnicodeString(ColumnIndex,
@@ -644,7 +648,7 @@
   begin
     ColumnIndex := TField(FieldRefs[I]).FieldNo;
     case TField(FieldRefs[I]).DataType of
-      ftString:
+      ftString, ftGuid:
         DefVarManager.SetAsString(ResultValues[I],
           ResultSet.GetString(ColumnIndex));
       ftBoolean:
@@ -698,7 +702,7 @@
   begin
     ColumnIndex := FieldIndices[I];
     case TField(FieldRefs[I]).DataType of
-      ftString:
+      ftString, ftGuid:
         DefVarManager.SetAsString(ResultValues[I],
           RowAccessor.GetString(ColumnIndex, WasNull));
       ftBoolean:

Index: dbc\ZDbcCache.pas
===================================================================
--- dbc\ZDbcCache.pas	(v 6.6.2-rc)
+++ dbc\ZDbcCache.pas	(pg uuid patch)
@@ -319,7 +319,7 @@
       Result := SizeOf(Double);
     stBigDecimal:
       Result := SizeOf(Extended);
-    stString:
+    stString, stGuid:
       Result := ColumnInfo.Precision + 1;
     stUnicodeString:
       Result := 2 * ColumnInfo.Precision + 2;
@@ -587,7 +587,7 @@
         Result := CompareBool(PWordBool(ValuePtr1)^, PWordBool(ValuePtr2)^);
       stDate, stTime, stTimestamp:
         Result := CompareFloat(PDateTime(ValuePtr1)^, PDateTime(ValuePtr2)^);
-      stString:
+      stString, stGuid:
         Result := AnsiStrComp(PChar(ValuePtr1), PChar(ValuePtr2));
       stUnicodeString:
       {$IFNDEF VER130BELOW}
@@ -885,7 +885,7 @@
   if FBuffer.Columns[FColumnOffsets[ColumnIndex - 1]] = 0 then
   begin
     case FColumnTypes[ColumnIndex - 1] of
-      stString:
+      stString, stGuid:
         Result := @FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1];
       else
       begin
@@ -932,7 +932,7 @@
       stFloat: Result := FloatToSQLStr(GetFloat(ColumnIndex, IsNull));
       stDouble: Result := FloatToSQLStr(GetDouble(ColumnIndex, IsNull));
       stBigDecimal: Result := FloatToSQLStr(GetBigDecimal(ColumnIndex, IsNull));
-      stString:
+      stString, stGuid:
         Result := PChar(@FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1]);
       stUnicodeString, stUnicodeStream: Result := GetUnicodeString(ColumnIndex, IsNull);
       stBytes: Result := BytesToStr(GetBytes(ColumnIndex, IsNull));
@@ -1641,7 +1641,7 @@
           Result.VType := vtDateTime;
           Result.VDateTime := PDateTime(ValuePtr)^;
         end;
-      stString:
+      stString, stGuid:
         begin
           Result.VType := vtString;
           Result.VString := PChar(ValuePtr);
@@ -2008,7 +2008,7 @@
   CheckColumnConvertion(ColumnIndex, stString);
 {$ENDIF}
   case FColumnTypes[ColumnIndex - 1] of
-    stString:
+    stString, stGuid:
       begin
         if Value <> nil then
         begin
@@ -2056,7 +2056,7 @@
     stFloat: SetFloat(ColumnIndex, SQLStrToFloatDef(Value, 0));
     stDouble: SetDouble(ColumnIndex, SQLStrToFloatDef(Value, 0));
     stBigDecimal: SetBigDecimal(ColumnIndex, SQLStrToFloatDef(Value, 0));
-    stString:
+    stString, stGuid:
       begin
         FBuffer.Columns[FColumnOffsets[ColumnIndex - 1]] := 0;
         StrPLCopy(@FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1], Value,

Index: dbc\ZDbcCachedResultSet.pas
===================================================================
--- dbc\ZDbcCachedResultSet.pas	(v 6.6.2-rc)
+++ dbc\ZDbcCachedResultSet.pas	(pg uuid patch)
@@ -1755,7 +1755,7 @@
         stFloat: RowAccessor.SetFloat(I, ResultSet.GetFloat(I));
         stDouble: RowAccessor.SetDouble(I, ResultSet.GetDouble(I));
         stBigDecimal: RowAccessor.SetBigDecimal(I, ResultSet.GetBigDecimal(I));
-        stString: RowAccessor.SetPChar(I, ResultSet.GetPChar(I));
+        stString, stGuid: RowAccessor.SetPChar(I, ResultSet.GetPChar(I));
         stUnicodeString: RowAccessor.SetUnicodeString(I, ResultSet.GetUnicodeString(I));
         stBytes: RowAccessor.SetBytes(I, ResultSet.GetBytes(I));
         stDate: RowAccessor.SetDate(I, ResultSet.GetDate(I));

				 
Index: dbc\ZDbcDbLibUtils.pas
===================================================================
--- dbc\ZDbcDbLibUtils.pas	(v 6.6.2-rc)
+++ dbc\ZDbcDbLibUtils.pas	(pg uuid patch)
@@ -268,7 +268,7 @@
         else Result := '0';
       stByte, stShort, stInteger, stLong, stFloat, stDouble, stBigDecimal:
         Result := SoftVarManager.GetAsString(Value);
-      stString:
+      stString, stGuid:
         Result := AnsiQuotedStr(SoftVarManager.GetAsString(Value), '''');
       stBytes:
         begin

Index: dbc\ZDbcGenericResolver.pas
===================================================================
--- dbc\ZDbcGenericResolver.pas	(v 6.6.2-rc)
+++ dbc\ZDbcGenericResolver.pas	(pg uuid patch)
@@ -548,7 +548,7 @@
       stBigDecimal:
         Statement.SetBigDecimal(I + 1,
           RowAccessor.GetBigDecimal(ColumnIndex, WasNull));
-      stString:
+      stString, stGuid:
         Statement.SetString(I + 1, RowAccessor.GetString(ColumnIndex, WasNull));
       stUnicodeString:
         Statement.SetUnicodeString(I + 1,
@@ -827,7 +827,7 @@
               stBigDecimal:
                 RowAccessor.SetBigDecimal(Current.ColumnIndex,
                   ResultSet.GetBigDecimal(I));
-              stString, stAsciiStream:
+              stString, stAsciiStream, stGuid:
                 RowAccessor.SetString(Current.ColumnIndex,
                   ResultSet.GetString(I));
               stUnicodeString:

Index: dbc\ZDbcIntfs.pas
===================================================================
--- dbc\ZDbcIntfs.pas	(v 6.6.2-rc)
+++ dbc\ZDbcIntfs.pas	(pg uuid patch)
@@ -98,7 +98,7 @@
   {** Defines supported SQL types. }
   TZSQLType = (stUnknown, stBoolean, stByte, stShort, stInteger, stLong, stFloat,
     stDouble, stBigDecimal, stString, stUnicodeString, stBytes, stDate, stTime,
-    stTimestamp, stAsciiStream, stUnicodeStream, stBinaryStream);
+    stTimestamp, stAsciiStream, stUnicodeStream, stBinaryStream, stGuid);
 
   {** Defines a transaction isolation level. }
   TZTransactIsolationLevel = (tiNone, tiReadUncommitted, tiReadCommitted,

Index: dbc\ZDbcPostgreSqlResultSet.pas
===================================================================
--- dbc\ZDbcPostgreSqlResultSet.pas	(v 6.6.2-rc)
+++ dbc\ZDbcPostgreSqlResultSet.pas	(pg uuid patch)
@@ -183,6 +183,7 @@
   Connection := Statement.GetConnection as IZPostgreSQLConnection;
 
   case TypeOid of
+    2950: ColumnInfo.Precision := 38; { uuid }
     790: ColumnInfo.Currency := True; { money }
     19: ColumnInfo.Precision := 32; { name }
     1186: ColumnInfo.Precision := 32; { interval }
		 
Index: dbc\ZDbcPostgreSqlStatement.pas
===================================================================
--- dbc\ZDbcPostgreSqlStatement.pas	(v 6.6.2-rc)
+++ dbc\ZDbcPostgreSqlStatement.pas	(pg uuid patch)
@@ -350,7 +350,7 @@
         else Result := 'FALSE';
       stByte, stShort, stInteger, stLong, stBigDecimal, stFloat, stDouble:
         Result := SoftVarManager.GetAsString(Value);
-      stString, stBytes:
+      stString, stGuid, stBytes:
         Result := EncodeString(FCharactersetCode,SoftVarManager.GetAsString(Value));
       stDate:
         Result := Format('''%s''::date',

Index: dbc\ZDbcPostgreSqlUtils.pas
===================================================================
--- dbc\ZDbcPostgreSqlUtils.pas	(v 6.6.2-rc)
+++ dbc\ZDbcPostgreSqlUtils.pas	(pg uuid patch)
@@ -283,6 +283,8 @@
     Result := stAsciiStream
   else if (TypeName <> '') and (TypeName[1] = '_') then // ARRAY TYPES
     Result := stAsciiStream
+  else if (TypeName = 'uuid') then
+    Result := stGuid //stString
   else
     Result := stUnknown;
 end;
@@ -328,6 +330,7 @@
     1042: Result := stString; { bpchar }
     22,30: Result := stAsciiStream; { int2vector/oidvector. no '_aclitem' }
     651, 1000..1028: Result := stAsciiStream;
+    2950: Result := stGuid; { uuid }
     else
       Result := stUnknown;
   end;

	 
Index: dbc\ZDbcResultSet.pas
===================================================================
--- dbc\ZDbcResultSet.pas	(v 6.6.2-rc)
+++ dbc\ZDbcResultSet.pas	(pg uuid patch)
@@ -1019,7 +1019,7 @@
         Result.VType := vtDateTime;
         Result.VDateTime := GetTimestamp(ColumnIndex);
       end;
-    stString, stBytes, stAsciiStream, stBinaryStream:
+    stString, stGuid, stBytes, stAsciiStream, stBinaryStream:
       begin
         Result.VType := vtString;
         Result.VString := GetString(ColumnIndex);

Index: dbc\ZDbcUtils.pas
===================================================================
--- dbc\ZDbcUtils.pas	(v 6.6.2-rc)
+++ dbc\ZDbcUtils.pas	(pg uuid patch)
@@ -298,7 +298,7 @@
     stLong, stFloat, stDouble, stBigDecimal:
       Result := InitialType in [stBoolean, stByte, stShort, stInteger,
         stLong, stFloat, stDouble, stBigDecimal, stString, stUnicodeString];
-    stString, stUnicodeString:
+    stString, stUnicodeString, stGuid:
       Result := True;
     stBytes:
       Result := InitialType in [stString, stUnicodeString, stBytes,
@@ -340,6 +340,8 @@
       Result := 'BigDecimal';
     stString:
       Result := 'String';
+    stGuid:
+      Result := 'UUID';
     stUnicodeString:
       Result := 'UnicodeString';
     stBytes:

Index: component\ZSqlProcessor.pas
===================================================================
--- component\ZSqlProcessor.pas	(v 6.6.2-rc)
+++ component\ZSqlProcessor.pas	(pg uuid patch)
@@ -487,7 +487,7 @@
             Statement.SetLong(I + 1, StrToInt64(Param.AsString));
           ftCurrency:
             Statement.SetBigDecimal(I + 1, Param.AsCurrency);
-          ftString:
+          ftString, ftGuid:
             Statement.SetString(I + 1, Param.AsString);
           ftBytes:
             Statement.SetString(I + 1, Param.AsString);

Index: component\ZSqlUpdate.pas
===================================================================
--- component\ZSqlUpdate.pas	(v 6.6.2-rc)
+++ component\ZSqlUpdate.pas	(pg uuid patch)
@@ -566,7 +566,7 @@
         stBigDecimal:
           Statement.SetBigDecimal(I + 1,
             RowAccessor.GetBigDecimal(ColumnIndex, WasNull));
-        stString, stUnicodeString:
+        stString, stGuid, stUnicodeString:
           Statement.SetString(I + 1, RowAccessor.GetString(ColumnIndex, WasNull));
         stBytes:
           Statement.SetBytes(I + 1, RowAccessor.GetBytes(ColumnIndex, WasNull));
@@ -622,7 +622,7 @@
             Statement.SetFloat(I + 1, ParamValue.AsFloat);
           ftLargeInt:
             Statement.SetInt(I + 1, ParamValue.AsInteger);
-          ftString:
+          ftString, ftGuid:
             Statement.SetString(I + 1, ParamValue.AsString);
           ftBytes:
             Statement.SetString(I + 1, ParamValue.AsString);
@@ -719,7 +719,7 @@
             stFloat: RefreshRowAccessor.SetFloat(RefreshColumnIndex, RefreshResultSet.GetFloat(I));
             stDouble: RefreshRowAccessor.SetDouble(RefreshColumnIndex, RefreshResultSet.GetDouble(I));
             stBigDecimal: RefreshRowAccessor.SetBigDecimal(RefreshColumnIndex, RefreshResultSet.GetBigDecimal(I));
-            stString: RefreshRowAccessor.SetPChar(RefreshColumnIndex, RefreshResultSet.GetPChar(I));
+            stString, stGuid: RefreshRowAccessor.SetPChar(RefreshColumnIndex, RefreshResultSet.GetPChar(I));
             stUnicodeString: RefreshRowAccessor.SetUnicodeString(RefreshColumnIndex, RefreshResultSet.GetUnicodeString(I));
             stBytes: RefreshRowAccessor.SetBytes(RefreshColumnIndex, RefreshResultSet.GetBytes(I));
             stDate: RefreshRowAccessor.SetDate(RefreshColumnIndex, RefreshResultSet.GetDate(I));

Index: component\ZStoredProcedure.pas
===================================================================
--- component\ZStoredProcedure.pas	(v 6.6.2-rc)
+++ component\ZStoredProcedure.pas	(pg uuid patch)
@@ -174,7 +174,7 @@
           Statement.SetDouble(I+1, Param.AsFloat);
         ftLargeInt:
           Statement.SetLong(I+1, StrToInt64(Param.AsString));
-        ftString , ftFixedChar:
+        ftString, ftGuid, ftFixedChar:
           Statement.SetString(I+1, Param.AsString);
         ftBytes:
           Statement.SetString(I+1, Param.AsString);
@@ -250,7 +250,7 @@
   {$ELSE}
           Param.AsInteger := FCallableStatement.GetLong(I + 1);
   {$ENDIF}
-        ftString:
+        ftString, ftGuid:
           Param.AsString := FCallableStatement.GetString(I + 1);
         ftBytes:
           Param.AsString := FCallableStatement.GetString(I + 1);
Beware, I only test the patch with Delphi 2007 n PostgreSQL 8.3.

Regards,
Roy Irwan
Post Reply