Michael Van Canneyt wrote: ...
I would propose to introduce a enumerated
TSQLTransactionStyle =(tsConcurrent,tsReadCommit, etc.);
Then add a TransactionStyle to TSQLTransaction; This must be mapped by the TSQLConnection when creating the handle.
...
Indeed, this would be more elegant than my solution, but this would require finding some common functionality between various databases (Firebird, PostgreSQL, MySQL, and thinking about others in the future). I.e. something that would be both flexible and yet (at least relatively) portable to all TSQLConnection descendants. I'll look into this tomorrow. Although I may need some help about what transaction modes can be expressed in PostgreSQL and MySQL. I'll see tomorrow what I can do (and where I will need some help :).
1. After reading docs of PostgreSQL and MySQL (I know possibilities of Firebird by heart :), I must admit that I don't see a way to define TSQLTransactionStyle in a way that would be both really flexible and portable to all 3 databases (Firebird, PostgreSQL, MySQL). Every database has an idea of at least two transaction modes like
a) Concurrent (aka "Snapshot") b) Read committed
... but that's all. Many details seem to be different, especially "read committed" mode has many diffeent variants and possibilities (whether one multirow select can be only partially affected by committing some other transaction ? In Firebird yes, it's always possible, in PostgreSQL you can avoid this. What should happen when uncommitted data is pending ? In Firebird -- many choices: read committed record version, wait for committing or rollback, return immediately with error... PostgreSQL seems to always return committed record version. Anyway -- these are just examples).
I'm waiting for your opinion on this point. How exactly would you propose TSQLTransactionStyle to be defined, and how it should behave when some modes cannot be implemented in some database ?
2. To clear field for our discussion, I'm attaching all my simple corrections to fcl/db/ as a patch to this email. This patch does *not* mess with anything related to changing transaction modes (neither "your" way of TSQLTransactionStyle nor "my" initial way of exposing TSQLTransaction.SQLHandle). In other words, this is the patch that you want to apply now, no matter what is your answer to point 1 above :)
Summary of changes in this patch:
-- Default TIBTransaction.IsolationLevel is changed to ilConcurrent. This makes TIBTransaction more consistent with TIBConnection behaviour, and Delphi's IBX, and Firebird C API (as I explained in one of previous letters).
-- In units Interbase and IBConnection, SQLVar[...].AliasName is used everywhere, instead of SQLVar[...].SQLName. As I explained in previous letter "[fpc-devel] Fix to IBConnection field names", using AliasName is just better than SQLName. Consider e.g. `select count(*) from ...', that has one field with SQLName = '' but AliasName = 'COUNT'. Also, developer writing SQL statememt has full control over what alias name will be assigned for the field.
-- Fix for Sqldb unit that used in one place FieldByName while FindField + manual check was required. This is described in detail in comments inside patch.
-- TIBDatabase should allow setting Transaction from non-nil to nil (TIBDatabase must anyway be prepared everywhere to handle the case when Transaction = nil), and also TIBDatabase should automatically set it's Transaction property to nil when Transaction instance is destroyed (using FreeNotification). I rearranged TIBDatabase.SetTransaction to make it (in my opinion) cleaner.
Michalis.
cvs diff: Diffing .
cvs diff: Diffing dbase
cvs diff: Diffing interbase
Index: interbase/interbase.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/interbase/interbase.pp,v
retrieving revision 1.16
diff -u -r1.16 interbase.pp
--- interbase/interbase.pp 17 Mar 2005 09:02:17 -0000 1.16
+++ interbase/interbase.pp 20 Mar 2005 03:13:15 -0000
@@ -52,6 +52,8 @@
procedure SetDBDialect;
procedure SetTransaction(Value : TIBTransaction);
protected
+ procedure Notification(AComponent: TComponent;
+ Operation: TOperation); override;
function GetHandle : pointer; virtual;
{ This procedure makes connection to Interbase server internally.
Is visible only by descendants, in application programming
@@ -180,7 +182,7 @@
property AccessMode: TAccessMode
read FAccessMode write FAccessMode default amReadWrite;
property IsolationLevel: TIsolationLevel
- read FIsolationLevel write FIsolationLevel default ilReadCommitted;
+ read FIsolationLevel write FIsolationLevel default ilConcurrent;
property LockResolution: TLockResolution
read FLockResolution write FLockResolution default lrWait;
property TableReservation: TTableReservation
@@ -383,22 +385,24 @@
procedure TIBDatabase.SetTransaction(Value : TIBTransaction);
begin
- if FTransaction = nil then
+ if Value <> FTransaction then
begin
+ if FTransaction <> nil then
+ begin
+ if FTransaction.Active then
+ raise EInterBaseError.Create(
+ 'Cannot assign transaction while old transaction active!');
+ FTransaction.RemoveFreeNotification(Self);
+ end;
+
FTransaction := Value;
- if Assigned(FTransaction) then
- FTransaction.Database := Self;
- exit;
- end;
- if (Value <> FTransaction) and (Value <> nil) then
- if (not FTransaction.Active) then
+ if FTransaction <> nil then
begin
- FTransaction := Value;
FTransaction.Database := Self;
- end
- else
- raise EInterBaseError.Create('Cannot assign transaction while old transaction active!');
+ FTransaction.FreeNotification(Self);
+ end;
+ end;
end;
function TIBDatabase.GetHandle: pointer;
@@ -468,6 +472,14 @@
inherited Destroy;
end;
+procedure TIBDatabase.Notification(AComponent: TComponent;
+ Operation: TOperation);
+begin
+ inherited;
+ if (AComponent = FTransaction) and (Operation = opRemove) then
+ FTransaction := nil;
+end;
+
{ TIBTransaction }
procedure TIBTransaction.SetActive(Value : boolean);
@@ -573,7 +585,7 @@
constructor TIBTransaction.Create(AOwner : TComponent);
begin
inherited Create(AOwner);
- FIsolationLevel := ilReadCommitted;
+ FIsolationLevel := ilConcurrent;
end;
destructor TIBTransaction.Destroy;
@@ -990,7 +1002,7 @@
for x := 0 to FSQLDA^.SQLD - 1 do
begin
{$R-}
- if (Field.FieldName = FSQLDA^.SQLVar[x].SQLName) then
+ if (Field.FieldName = FSQLDA^.SQLVar[x].AliasName) then
begin
Result := not PFieldDataPrefix(CurrBuff)^.IsNull;
@@ -1138,7 +1150,7 @@
begin
TranslateFldType(FSQLDA^.SQLVar[x].SQLType, FSQLDA^.SQLVar[x].SQLLen, lenset,
TransType, TransLen);
- TFieldDef.Create(FieldDefs, FSQLDA^.SQLVar[x].SQLName, TransType,
+ TFieldDef.Create(FieldDefs, FSQLDA^.SQLVar[x].AliasName, TransType,
TransLen, False, (x + 1));
end;
{$R+}
cvs diff: Diffing memds
cvs diff: Diffing mysql
cvs diff: Diffing odbc
cvs diff: Diffing sdf
cvs diff: Diffing sqldb
Index: sqldb/sqldb.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/sqldb/sqldb.pp,v
retrieving revision 1.14
diff -u -r1.14 sqldb.pp
--- sqldb/sqldb.pp 14 Feb 2005 17:13:12 -0000 1.14
+++ sqldb/sqldb.pp 20 Mar 2005 03:13:16 -0000
@@ -618,8 +618,21 @@
begin
// Todo: If there is more then one field in the key, that must be parsed
s := indexdefs[tel].fields;
- F := fieldbyname(s);
- F.ProviderFlags := F.ProviderFlags + [pfInKey];
+ F := FindField(s);
+
+ { Using FindField and checking is F<>nil
+ (instead of using FieldByName) is *not* only a workaround
+ for this "Todo: If there is more then one field ..." above.
+
+ Even if S consists of only one field, there still is
+ a chance that this field is not available in our dataset.
+ Consider e.g. when I do `select Xyz from SomeTable'
+ where SomeTable is defined as
+ `(Foo integer primary key, Xyz integer)'.
+ Then S above will be 'Foo', but there is no field 'Foo'
+ in my dataset ! }
+ if F <> nil then
+ F.ProviderFlags := F.ProviderFlags + [pfInKey];
end;
end;
end;
cvs diff: Diffing sqldb/interbase
Index: sqldb/interbase/ibconnection.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/sqldb/interbase/ibconnection.pp,v
retrieving revision 1.12
diff -u -r1.12 ibconnection.pp
--- sqldb/interbase/ibconnection.pp 14 Feb 2005 17:13:12 -0000 1.12
+++ sqldb/interbase/ibconnection.pp 20 Mar 2005 03:13:16 -0000
@@ -488,7 +488,7 @@
begin
TranslateFldType(SQLDA^.SQLVar[x].SQLType, SQLDA^.SQLVar[x].SQLLen, SQLDA^.SQLVar[x].SQLScale,
lenset, TransType, TransLen);
- FD := TFieldDef.Create(FieldDefs, SQLDA^.SQLVar[x].SQLName, TransType,
+ FD := TFieldDef.Create(FieldDefs, SQLDA^.SQLVar[x].AliasName, TransType,
TransLen, False, (x + 1));
if TransType = ftBCD then FD.precision := SQLDA^.SQLVar[x].SQLLen;
FD.DisplayName := SQLDA^.SQLVar[x].AliasName;
cvs diff: Diffing sqldb/mysql
cvs diff: Diffing sqldb/postgres
cvs diff: Diffing sqlite
cvs diff: Diffing tests
_______________________________________________ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
