For most transparent support, I represent them as text, CHAR (38) NOT NULL. Others prefer a binary representation. Since I spent 5 years in a rapid response team, three of those as the only 24x7 on call support for a key enterprise product, I am very much concerned about minimizing the support (my) workload, even if it consumes a bit more storage resources. At about 2 to 3 dollars a gigabyte, storage is cheap. If I can store it in a standard, recognizable text form, I will.

I am more concerned with transparency of the back end DBMS than with saving 20 bytes per record. If you are aiming at a palm system, then the concerns would be reversed.

A GUID is not a "sortable" field in the sense that order has any meaning, so it is not appropriate for every situation. However, it is guaranteed unique until the year 3040. So for distributed systems (which is what it was designed for) it is ideal as a primary key. A clustered index on a GUID would probably not provide any benefit.

The table schema in my infamous benchmarking program uses GUID + Integer for the primary key. The theory behind this is that the physical store should retain the entire history of any business entity. Hence, the GUID represents the entity, and the sequence number allows rapid ordering of changes to the entity over time. When the "current" row is required, select by GUID and ((void_stamp is null) or (void stamp > now)).

GUID is generally generated by OS level calls (Windows or UNIX) or by one of about a hundred Java classes, depending on whose libraries you like to use.

My own WIP framework actually has a "master object table" that cross references known GUID's to the tables that they physically reside in and the class structures that instantiate them, allowing you to have nothing but the GUID and get to the data you need with two queries. From this, it is probably becoming apparent that the framework itself hides much of the DBMS overheads from the end user, so speed and efficiency are important to me but only within the context that it does not reduce supportability.

My testing has established that a GUID represented as CHAR(38) NOT NULL averages about 3 I/O (30 ms) per lookup on a table with 4,000,000 rows.

By the way, I get a 30 percent boost to performance by dereferencing the query parameters by integer (sequence). This section is easier to read. This code work pretty much as-is for all TQuery descendents I have tested, at least so far as the GUID stuff is concerned.

For the GUID discussion, this snip contains all you need as an example of how I use them. A "Memento" is a pattern also called a "Value Object", that has only values - for our purposes the memento fields correspond to table columns in the design.

Code snip ...

{**
* @created 2004-01-09-22.09.47.620
* @author RACE Code Generator (Auto-create) Version 0.0.0.0
*
* @todo Verify code for method SetParams
*
*}
procedure TContact_001_IBOPersistor.SetParamsWithName (_Memento: Tmemento; _params: TIB_Row);
var
E: Exception;
begin
if not (_Memento is TContact_001_Memento) then
begin
E := EInvalidCast.CreateFmt ('Memento %s cannot be saved as %s', [ _Memento.className, TContact_001_Memento.classname ]);
// Log.LogException (E, self, module_name, 'SetParams');
raise E;
end;

with _params, (_memento as TContact_001_Memento) do
begin
try
BeginUpdate;
try
ParamByName ('CREATE_STAMP_USER_OID').AsString := GUIDToString (FCreate_Stamp.User_OID);
ParamByName ('CREATE_STAMP_USER_SID').AsInteger := FCreate_Stamp.User_SID;
ParamByName ('CREATE_STAMP_INTER_OID').AsString := GUIDToString (FCreate_Stamp.Intermediary_OID);
ParamByName ('CREATE_STAMP_INTER_SID').AsInteger := FCreate_Stamp.Intermediary_SID;
// *** TimeStamp not supported by some libraries "as-shipped"
// ParamByName ('CREATE_STAMP_TIME').AsSQLTimeStamp := FCreate_Stamp.time;
ParamByName ('CREATE_STAMP_TIME').AsDateTime := SQLTimeStampToDateTime (FCreate_Stamp.time);
ParamByName ('CREATE_STAMP_METH').AsString := FCreate_Stamp.MethodName;
ParamByName ('CREATE_STAMP_PGM').AsString := FCreate_Stamp.Programname;

// ParamByName ('EFFECTIVE_TS').AsSQLTimeStamp := DateTimeToSQLTimeStamp (FEffective_TS);
ParamByName ('EFFECTIVE_TS').AsDateTime := FEffective_TS;
// ParamByName ('END_TS').AsSQLTimeStamp := DateTimeToSQLTimeStamp (FEnd_TS);
ParamByName ('END_TS').AsDateTime := FEnd_TS;
ParamByName ('OID').AsString := GUIDToString (FOID);
ParamByName ('SID').AsInteger := FSID;
ParamByName ('VOID_STAMP_USER_OID').AsString := GUIDToString (FVoid_Stamp.User_OID);
ParamByName ('VOID_STAMP_USER_SID').AsInteger := FVoid_Stamp.User_SID;
ParamByName ('VOID_STAMP_INTER_OID').AsString := GUIDToString (FVoid_Stamp.Intermediary_OID);
ParamByName ('VOID_STAMP_INTER_SID').AsInteger := FVoid_Stamp.Intermediary_SID;
// ParamByName ('VOID_STAMP_TIME').AsSQLTimeStamp := FVoid_Stamp.time;
ParamByName ('VOID_STAMP_TIME').AsDateTime := SQLTimeStampToDateTime (FVoid_Stamp.time);
ParamByName ('VOID_STAMP_METH').AsString := FVoid_Stamp.MethodName;
ParamByName ('VOID_STAMP_PGM').AsString := FVoid_Stamp.Programname;
ParamByName ('APARTMENT').AsString := FApartment;
ParamByName ('CITY').AsString := FCity;
ParamByName ('COUNTRY').AsString := FCountry;
ParamByName ('EMAIL').AsString := Femail;
ParamByName ('EMAIL_SERVER').AsString := Femail_server;
ParamByName ('FIRST_NAME').AsString := FFirst_Name;
ParamByName ('LAST_NAME').AsString := FLast_Name;
ParamByName ('POSTALCODE').AsString := FPostalCode;
ParamByName ('STREET').AsString := FStreet;
finally
EndUpdate (true);
end;
except
on E:Exception do begin
// Log.LogException (E, self, module_name, 'SetParams');
raise;
end;
end;
end;
end;

----- Original Message -----
From: akestion
To: firebird-support@yahoogroups.com
Sent: Wednesday, April 07, 2004 7:15 AM
Subject: [firebird-support] (unknown)


Me again,

Can somebody gives me some advises about GUID (Global Unique
IDentifiers)and Firebird ?

This to enable me to work on some data in disconnected mod.
How to use it efficiently, an exemple would be welcome.

Thank you.


[Non-text portions of this message have been removed]