ZMSQL - TBufDataset SQL enhanced in-memory database

[tatamata]

Since it seems that zmsql users will have to choose for themselves whether they want to compile TZMQueryDataset based on current TBufDataset or based on TZMBufDataset,
I put the decision into compiler directive:  {$DEFINE TZMBufDataset}.
Use "$DEFINE ZMBufDataset" compiler directive to base TZMQueryDataset on TZMBufDataset
or use "$OFF DEFINE ZMBufDataset" compiler directive to base TZMQueryDataset on TBufDataset.
The option {$DEFINE ZMBufDataset} is set in zmsql package (actually in pl_zmsql package, since I use CodeTyphon) under: Options/Compiler Options/Other/Conditionals/Custom Options/Defines.
if you switch it on, TZMBufDataset is ancestor, if you switch it off, TBufDataset is ancestor.

So, now you can check it easy for yourself, which option suites you well on your Lazarus/fpc installation.

Please, find attached current zmsql 0.1.14 pre-release version and do your tests.

If someone wants to help, these are the issues I would like to be solved before official 0.1.14 release:
- implementation of autoincrement fields ---> it seems that TBufDataset intentionally does not support autoincrement fields. In ZMQueryDataset, this should be implemented, maybe similar to way how it is implemented in TDbf. If someone knows how to do it, this would be nice...
- to detect whyZMQueryDataset does not work correctly with recent TBufDataset as ancestor (at least on my CodeTyphon v. 4.60.)

Other points of interest:
- How to implement left and right outer joins in JanSQL engine? Though you can somehow resolve this limitation, by using non-standard "ASSIGN TO variable" statement to perform sub-queries and then include resultset to main query, it would be nice if outer joins are implemented in the JanSQL engine...
- How to make another component, similar to ZMQueryDataset, that would perform SQL queries on TDataset instances, in-memory, rather than on .csv files? Imagine a component that performes in-memory SQL queries where "tables" are not files, but rather TDataset instances....I think this could have interesting usage...

[tatamata]

zmsql package version 0.1.14 is uploaded to Lazarus ccr:
http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

For those who use CodeTyphon, the reowrked pl_zmsql package is attached.

[tatamata]

Hello, everybody. Please, find attached draft zmsql package of incoming version 0.1.15. New version brings:
*implemented autoincrement fields (ftAutoInc).
*Improved visibility of TDataset methods and properties.
*zmquerydataset works again with TBufDataset as ancestor (as in CodeTyphon v.4.70).

In meantime, if someone can investigate why persistent fields created with following method bring different kind of errors, such as: "Class "TStringField" not found"?
Namely, persistent fields seem to be correctly created, they are present in .lfm and are visible in object inspector, but during run-time, different errors occur...
It seems that persistent fields created through editor don't have such problems?
The idea was to enable automatic creation of all persistent fields, from previously defined fielddefs, by setting property PersistentFieldsCreated to True. Obviously, something is not OK with the following code:

Code: [Select]

procedure TZMQueryDataSet.DoCreatePersistentFieldsFromFieldDefs;
var
  NewField: TField;
  FieldDef: TFieldDef;
  i: integer;

  function FieldNameToPascalIdentifer(const AName: string): string;
   var
     i : integer;
   begin
     Result := '';
     // FieldName is an ansistring
     for i := 1 to Length(AName) do
       if AName[i] in ['0'..'9','a'..'z','A'..'Z','_'] then
         Result := Result + AName[i];
     if (Length(Result) > 0) and (not (Result[1] in ['0'..'9'])) then
         Exit;
     if Assigned(FieldDef.FieldClass) then
     begin
       Result := FieldDef.FieldClass.ClassName + Result;
       if Copy(Result, 1, 1) = 'T' then
         Result := Copy(Result, 2, Length(Result) - 1);
     end
     else
       Result := 'Field' + Result;
   end;


  function CreateFieldName(Owner: TComponent; const AName: string): string;
  var
    j:integer;
     begin
       for j := 0 to Owner.ComponentCount - 1 do
       begin
         if CompareText(Owner.Components[j].Name, AName) = 0 then
         begin
           Result := FormEditingHook.CreateUniqueComponentName(NewField);
           exit;
         end;
       end;
       Result := AName;
     end;

 begin
  //This procedure creates PERSISTENT Fields from predefined FieldDefs.
  for I:=0 to fielddefs.Count-1 do
  with Fielddefs.Items[I] do begin
    FieldDef := Fielddefs.Items[I];
    if DataType<>ftUnknown then
      begin
      //Create new field and set it's unique name.
      NewField:=CreateField(self.Owner); //self.Owner ---> this makes created field to be persistent and visible in object inspector.
      NewField.Name := CreateFieldName(self.Owner, self.Name + FieldNameToPascalIdentifer(NewField.FieldName));
      end;
      //Set initial properties of the field.
      NewField.FieldKind:=fkData;
      NewField.SetFieldType(FieldDef.DataType);
      NewField.Size:=FieldDef.Size;
    end;
    self.BindFields(True);
end;                                                     

[taazz]

Its the streaming mechanism that knows nothing about those classes. I do not know where or how and if you find it please point it out to me too, but TStringfield and friends must be registered in order for the streaming to work correctly (ee registerClass). I have no idea how the streaming mecahnism does it at this point though.

[tatamata]

Hi, taaz, thanks.
So I put in zmquery an initialization section, (as suggested to me by Zeljan and you):

Code: [Select]

initialization

RegisterClasses ( [{ ftUnknown} Tfield,
    { ftString} TStringField,
    { ftSmallint} TSmallIntField,
    { ftInteger} TLongintField,
    { ftWord} TWordField,
    { ftBoolean} TBooleanField,
    { ftFloat} TFloatField,
    { ftCurrency} TCurrencyField,
    { ftBCD} TBCDField,
    { ftDate} TDateField,
    { ftTime} TTimeField,
    { ftDateTime} TDateTimeField,
    { ftBytes} TBytesField,
    { ftVarBytes} TVarBytesField,
    { ftAutoInc} TAutoIncField,
    { ftBlob} TBlobField,
    { ftMemo} TMemoField,
    { ftGraphic} TGraphicField,
    { ftFmtMemo} TBlobField,
    { ftParadoxOle} TBlobField,
    { ftDBaseOle} TBlobField,
    { ftTypedBinary} TBlobField,
    { ftFixedChar} TStringField,
    { ftWideString} TWideStringField,
    { ftLargeint} TLargeIntField,
    { ftOraBlob} TBlobField,
    { ftOraClob} TMemoField,
    { ftVariant} TVariantField,
    { ftGuid} TGuidField,
    { ftFMTBcd} TFMTBCDField,
    { ftFixedWideString} TWideStringField,
    { ftWideMemo} TWideMemoField             ]);     Now there are no errors due to not found classes anymore,
but now it is complainig about duplicate names of persistent fields. It seems that during loading it enters twice in DoCreatePersistentFieldsFromFieldDefs;

Continuing tommorow...

[tatamata]

OK, it seems that persistent fields objects (classes) are now created during run, but there are no underlying database fields (Fields.Count=0).
So, if LoadFromTable or QueryExecute is executed, error "Operation cannot be performed on inactive dataset" apears. Setting Active=True raise error too...

If CreateDatasete is executed before LoadFromTable or QueryExecute, then error like this appears: "Duplicate fieldname:ProductsId".

So, I don't get it, it seems that persistent field object is created but underlying Field is not. The loaded field object has the fieldname so prevents creating dynamic field by CreateDataset method....

Does anybody has an idea how to solve this?

[tatamata]

Hello, everybody...it seems that the problem with persistent fields is not in ZMQueryDataset, but in TBufDataset.
I tried creating persistent fields manually in editor and the same errors appear like when I do it all at once in code.
An the errors are: "Missing (compatible) underlying dataset, can not open.", "Operation cannot be performed on an inactive dataset".

Going through methods involved, I can't see what could be the cause in code, I don't see the major difference in what TBufDatase.CreateDataset does when creating dynamic fields...It's CreateFields use the sam looping through fielddefs and executing CreateField(AOWner)....

If someone want to check this, the current draft component and the test project are attached. The test projects show two master/detail datasets in both cases: Left side is how it behaves when dynamic fields, right side when persistent fields.

And the code is:

procedure TZMQueryDataSet.SetPersistentFieldsCreated(AValue: Boolean);
 { TODO : To solve problems with persistent fields }
begin
  if FPersistentFieldsCreated=AValue then exit;
  if AValue=True then
    begin
      try
        //In design-time only, because, in run-time persistent fields should be streamed from .lfm?
        if (csDesigning in ComponentState)
            and not (csLoading in ComponentState)
            and not (csReading in ComponentState)
          then begin
            ShowMessage('I am going to create persisten fields from fielddefs.');
            CreatePersistentFieldsFromFieldDefs;
          end;
        {FPersistentFieldsCreated:=AValue; //Removed to CreatePersistentFieldsFromFieldDefs procedure.}
        { TODO : Setting FPersistentFieldsCreated to True is temporarily disabled, because if PersistentFieldsCreated is True, then persistent fields will be loaded twice (once from stream and second time here) when project loading in design-time.... }
         FPersistentFieldsCreated:=False; //POOR SOLUTION
      except
        FPersistentFieldsCreated:=False;
        self.Active:=False;
      end;
    end;
  if AValue=False then  { TODO : To reconsider what to do on SetPersistentFieldsCreated=False. Currently set to do nothing. }
    FPersistentFieldsCreated:=AValue;
end;                                       

procedure TZMQueryDataSet.CreatePersistentFieldsFromFieldDefs;
//This procedure is used to create PERSISTENT Fields from FieldDefs.
{var strMsg:String;}
begin
  with self do begin
    FSourceData:=sdInternal;
    FFieldCount:=self.FieldDefs.Count;
    try
      Close;
      // Create PERSISTENT fields from FieldDefs
      { TODO : To investigate BindFields(False) and DefaultFields in ZMBufDataset and TBufDataset.
In Delphi, BindFields(False) disconnects  fields object from underlying fields, but it seems that currently this does not work here?
Also, DefaultFields should be False in case of persistent fields and True in case of dynamic fields. However, it seems that sometimes it is False even if only dynamic fields are created.}
      {
      if InspectFields=ifDoNothing {This means that there are already created corresponding persistent fields.} then begin
        ShowMessage('InspectFields=ifDoNothing');
        if self.DefaultFields=False {DefaultFields=False means Persistent Fields exist} then begin
          ShowMessage('self.DefaultFields=False');
          Exit;
        end;
      end;
      }
      Fields.Clear;
      DoCreatePersistentFieldsFromFieldDefs;
      BindFields(True); //Connect persistent Fields objects to underlying Fields.
      //If everything goes ok, set the property accordingly.
      FPersistentFieldsCreated:=True;
      //Deal mutually exclusive property
      FDynamicFieldsCreated:=False;
    except
      ShowMessage('I can not create persistent fields!');
      FPersistentFieldsCreated:=False;
      self.Active:=False;
    end;
  end;
end;                             

procedure TZMQueryDataSet.DoCreatePersistentFieldsFromFieldDefs;
var
  NewField: TField;
  FieldDef: TFieldDef;
  i: integer;

  function FieldNameToPascalIdentifer(const AName: string): string;
   var
     i : integer;
   begin
     Result := '';
     // FieldName is an ansistring
     for i := 1 to Length(AName) do
       if AName in ['0'..'9','a'..'z','A'..'Z','_'] then
         Result := Result + AName;
     if (Length(Result) > 0) and (not (Result[1] in ['0'..'9'])) then
         Exit;
     if Assigned(FieldDef.FieldClass) then
     begin
       Result := FieldDef.FieldClass.ClassName + Result;
       if Copy(Result, 1, 1) = 'T' then
         Result := Copy(Result, 2, Length(Result) - 1);
     end
     else
       Result := 'Field' + Result;
   end;


  function CreateFieldName(Owner: TComponent; const AName: string): string;
  var
    j:integer;
     begin
       for j := 0 to Owner.ComponentCount - 1 do
       begin
         if CompareText(Owner.Components[j].Name, AName) = 0 then
         begin
           Result := FormEditingHook.CreateUniqueComponentName(NewField);
           exit;
         end;
       end;
       Result := AName;
     end;

 begin

   //This procedure creates PERSISTENT Fields from predefined FieldDefs.
  for I:=0 to fielddefs.Count-1 do
  with Fielddefs.Items do begin
    FieldDef := Fielddefs.Items;
    if DataType<>ftUnknown then
      begin
      //Create new field and set it's unique name.
      NewField:=CreateField(self.Owner); //self.Owner ---> this makes created field to be persistent and visible in object inspector.
      NewField.Name := CreateFieldName(self.Owner, self.Name + FieldNameToPascalIdentifer(NewField.FieldName));
      end;
      //Set initial properties of the field.
      NewField.FieldKind:=fkData;
      NewField.SetFieldType(FieldDef.DataType);
      NewField.Size:=FieldDef.Size;
    end;
    self.BindFields(True);

end;   

[HeDiBo]

if FPersistentFieldsCreated=AValue then exit;

I think your trouble stems from the fact that this condition is never true. And if you make it true by setting the property, the call to this function comes too early (namely when streaming in components).

I made it work by setting PersistentFieldsCreated to False at design time and setting them to True when the Load data button is clicked:

Code: [Select]

procedure TForm1.Button2Click(Sender: TObject);
begin
  DataSource_Products_Per.Enabled:=True;
  DataSource_Boms_Per.Enabled:=True;
  Products_Per.PeristentFieldsCreated := True;
  Boms_Per.PeristentFieldsCreated := True;

  Products_Per.LoadFromTable;
  //Products_Per.QueryExecute;
  Boms_Per.LoadFromTable;
  //Boms_Per.QueryExecute;
end;

So, I think you must find a way to set this property after all components are streamed in.

Good luck 

[tatamata]

if FPersistentFieldsCreated=AValue then exit;

I think your trouble stems from the fact that this condition is never true. And if you make it true by setting the property, the call to this function comes too early (namely when streaming in components).

I made it work by setting PersistentFieldsCreated to False at design time and setting them to True when the Load data button is clicked:

Code: [Select]

procedure TForm1.Button2Click(Sender: TObject);
begin
  DataSource_Products_Per.Enabled:=True;
  DataSource_Boms_Per.Enabled:=True;
  Products_Per.PeristentFieldsCreated := True;
  Boms_Per.PeristentFieldsCreated := True;

  Products_Per.LoadFromTable;
  //Products_Per.QueryExecute;
  Boms_Per.LoadFromTable;
  //Boms_Per.QueryExecute;
end;

So, I think you must find a way to set this property after all components are streamed in.

Good luck 

Dear HeDIBO, thank you for your effort and testing.

However, I could not reproduce what you are suggesting.
It does not work on my computer, with CodeTyphon 4.70. The same errors appear...

Could somebody else try this solution, as HeDIBO proposed?

Thanks.

[HeDiBo]

Did you try this with the PeristentFieldsCreated property set to False at design time?
What kind of error do you receive?

[tatamata]

Did you try this with the PeristentFieldsCreated property set to False at design time?
What kind of error do you receive?

Yes.
The error is:
Project project1 raised exception class 'EdatabaseError' with message: Missing (compatible) underlying dataset, can not open

Regards

[HeDiBo]

The error is:
Project project1 raised exception class 'EdatabaseError' with message: Missing (compatible) underlying dataset, can not open

Could it be I'm still on CT 4.6 but anyhow using TBufDataset i.s.o. TZMBufDataset?

[HeDiBo]

I don't have CT 4.7 yet. The difference must be in TBufDataset. The version for CT 4.6 does work! (But may have other problems, I don't know).

I tried to go to TZMBufDataset but that fails now because the property MaxIndexesCount is missing.

So, if you want to keep the possibility of TZMBufDataset open, that needs some work.

[HeDiBo]

The folllowing piece of code from TBufDataset is the only place where the database error "Missing (compatible) underlying dataset, can not open." is generated anywhere in fpc.

Since I do not have an error, you may be able to deduce why in your source the error does occur.

Code: [Select]

procedure TCustomBufDataset.InternalOpen;

var IndexNr : integer;
    i : integer;

begin
  FAutoIncField:=nil;
  if not Assigned(FDatasetReader) and (FileName<>'') then
    begin
    FFileStream := TFileStream.Create(FileName,fmOpenRead);
    FDatasetReader := GetPacketReader(dfAny, FFileStream);
    end;
  if assigned(FDatasetReader) then
    begin
    FReadFromFile := True;
    IntLoadFielddefsFromFile;
    end;

  // This is to check if the dataset is actually created (By calling CreateDataset,
  // reading from a stream in some other way implemented by a descendent)
  // If there are less fields then FieldDefs we know for sure that the dataset
  // is not (correctly) created.

  // If there are constant expressions in the select statement (for PostgreSQL)
  // they are of type ftUnknown (in FieldDefs), and are not created (in Fields).
  // So Fields.Count < FieldDefs.Count in this case
  // See mantis #22030

  //  if Fields.Count<FieldDefs.Count then
  if Fields.Count = 0 then
    DatabaseError(SErrNoDataset);              // <<<<<<< the "missing" error

  // If there is a field with FieldNo=0 then the fields are not found to the
  // FieldDefs which is a sign that there is no dataset created. (Calculated and
  // lookup fields have FieldNo=-1)
  for i := 0 to Fields.Count-1 do
    if Fields[i].FieldNo=0 then
      DatabaseError(SErrNoDataset)             // <<<<<<< the "missing" error
    else if (FAutoIncValue>-1) and (Fields[i] is TAutoIncField) and not assigned(FAutoIncField) then
      FAutoIncField := TAutoIncField(Fields[i]);

Because I don't have your bug, I cannot be of further assistence I'm afreaid. Hope you find it!

[tatamata]

I don't have CT 4.7 yet. The difference must be in TBufDataset. The version for CT 4.6 does work! (But may have other problems, I don't know).

I tried to go to TZMBufDataset but that fails now because the property MaxIndexesCount is missing.

So, if you want to keep the possibility of TZMBufDataset open, that needs some work.

Hi!. Please find attached the current (work in progress) ZMSQL package. I set {$DEFINE ZMBufDataset} and this version of ZMBufDataset is actually BufDataset from CodeTyphon v 4.70.
So, you might test it. At least we will know whether the problem is inside BufDataset.
Thanks.