unit Unit5;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, csvdataset, dbf, FileUtil, ZDataset,
  ZConnection, ZMQueryDataSet, ZMConnection, LResources, Controls,
  {
   dbf_common,
  }
  Dialogs;

type


  //Type that holds information about Dbf tables
  TMightyQueryDbfTables=array[1..9] of TDbf;


  { TDataModule1 }

  TDataModule1 = class(TDataModule)
    AutoincsAUTOINC_ID: TAutoIncField;
    AutoincsFIELDS_AUTOINC: TLongintField;
    AutoincsFILTERS_AUTOINC: TLongintField;
    AutoincsPROJECTS_AUTOINC: TLongintField;
    AutoincsQUERIESPARAMETERS_AUTOINC: TLongintField;
    AutoincsQUERIES_AUTOINC: TLongintField;
    AutoincsSQLCONNECTORS_AUTOINC: TLongintField;
    AutoincsSQLTABLES_AUTOINC: TLongintField;
    AutoincsTABLES_AUTOINC: TLongintField;
    CSVTableData: TCSVDataset;
    DataSourceAutoincs: TDataSource;
    DatasourceCSVTableData: TDataSource;
    DatasourceFields: TDataSource;
    DatasourceFilters: TDataSource;
    DatasourceProjects: TDataSource;
    DatasourceQueries: TDataSource;
    DatasourceQueriesParameters: TDataSource;
    DatasourceQueryResult: TDataSource;
    DatasourceSQLConnectors: TDataSource;
    DatasourceSQLTableData: TDataSource;
    DatasourceSQLTables: TDataSource;
    DatasourceTables: TDataSource;
    Autoincs: TDbf;
    Fields: TDbf;
    FieldsFIELDCOMMENT: TStringField;
    FieldsFIELDNAME: TStringField;
    FieldsFIELD_ID: TAutoIncField;
    FieldsTABLE_ID: TLongintField;
    Filters: TDbf;
    FiltersFILTERCOMMENT: TStringField;
    FiltersFILTERNAME: TStringField;
    FiltersFILTERTEXT: TStringField;
    FiltersFILTER_ID: TAutoIncField;
    FiltersPROJECT_ID: TLongintField;
    Projects: TDbf;
    ProjectsPROJECTCOMMENT: TStringField;
    ProjectsPROJECTNAME: TStringField;
    ProjectsPROJECTPATH: TStringField;
    ProjectsPROJECT_ID: TAutoIncField;
    Queries: TDbf;
    QueriesParameters: TDbf;
    QueriesParametersQUERYCOMMENT: TStringField;
    QueriesParametersQUERYPARAMETERNAME: TStringField;
    QueriesParametersQUERYPARAMETERVALUE: TStringField;
    QueriesParametersQUERYPARAMETER_ID: TAutoIncField;
    QueriesParametersQUERY_ID: TLongintField;
    QueriesPROJECT_ID: TLongintField;
    QueriesQUERYCOMMENT: TStringField;
    QueriesQUERYNAME: TStringField;
    QueriesQUERYTEXT: TMemoField;
    QueriesQUERY_ID: TAutoIncField;
    SQLConnectors: TDbf;
    SQLConnectorsCATALOG: TStringField;
    SQLConnectorsDATABASE: TStringField;
    SQLConnectorsHOSTNAME: TStringField;
    SQLConnectorsPASSWORD: TStringField;
    SQLConnectorsPORT: TStringField;
    SQLConnectorsPROJECT_ID: TLongintField;
    SQLConnectorsPROTOCOL: TStringField;
    SQLConnectorsSQLCONNECTORNAME: TStringField;
    SQLConnectorsSQLCONNECTOR_ID: TAutoIncField;
    SQLConnectorsTOBEIMPORTED: TBooleanField;
    SQLConnectorsUSER: TStringField;
    SQLTables: TDbf;
    SQLTablesIMPORTEDNAME: TStringField;
    SQLTablesSQLCONNECTOR_ID: TLongintField;
    SQLTablesSQLTABLECOMMENT: TStringField;
    SQLTablesSQLTABLENAME: TStringField;
    SQLTablesSQLTABLE_ID: TAutoIncField;
    SQLTablesTOBEIMPORTED: TBooleanField;
    Tables: TDbf;
    TablesPROJECT_ID: TLongintField;
    TablesTABLECOMMENT: TStringField;
    TablesTABLENAME: TStringField;
    TablesTABLE_ID: TAutoIncField;
    ZConnection1: TZConnection;
    ZMConnection1: TZMConnection;
    ZMQueryDataSetActionQuery: TZMQueryDataSet;
    ZMQueryDatasetCSVTableData: TZMQueryDataSet;
    ZMQueryDatasetQueryResult: TZMQueryDataSet;
    ZMQueryDatasetSQLTableData: TZMQueryDataSet;
    ZTable1: TZTable;
    procedure DataModuleCreate(Sender: TObject);
    procedure FieldsAfterOpen(DataSet: TDataSet);
    procedure FieldsAfterPost(DataSet: TDataSet);
    procedure FieldsBeforePost(DataSet: TDataSet);
    procedure FieldsNewRecord(DataSet: TDataSet);
    procedure FiltersBeforePost(DataSet: TDataSet);
    procedure FiltersNewRecord(DataSet: TDataSet);
    procedure ProjectsAfterInsert(DataSet: TDataSet);
    procedure ProjectsAfterPost(DataSet: TDataSet);
    procedure ProjectsAfterScroll(DataSet: TDataSet);
    procedure ProjectsBeforeDelete(DataSet: TDataSet);
    procedure ProjectsNewRecord(DataSet: TDataSet);
    procedure QueriesAfterScroll(DataSet: TDataSet);
    procedure QueriesBeforeDelete(DataSet: TDataSet);
    procedure QueriesBeforePost(DataSet: TDataSet);
    procedure QueriesNewRecord(DataSet: TDataSet);
    procedure QueriesParametersBeforePost(DataSet: TDataSet);
    procedure QueriesParametersNewRecord(DataSet: TDataSet);
    procedure SQLConnectorsBeforeDelete(DataSet: TDataSet);
    procedure SQLConnectorsBeforePost(DataSet: TDataSet);
    procedure SQLConnectorsNewRecord(DataSet: TDataSet);
    procedure SQLTablesBeforePost(DataSet: TDataSet);
    procedure SQLTablesNewRecord(DataSet: TDataSet);
    procedure TablesAfterOpen(DataSet: TDataSet);
    procedure TablesAfterPost(DataSet: TDataSet);
    procedure TablesBeforeDelete(DataSet: TDataSet);
    procedure TablesNewRecord(DataSet: TDataSet);
    procedure PackDbfTables;
    procedure EmptyPackDbfTables;
    procedure InitiateFreshDbfTables;
  private
    { private declarations }
  public
    { public declarations }
    vDbfTables:TMightyQueryDbfTables;
  end;

var
  DataModule1: TDataModule1;

implementation

uses Unit1;

{ TDataModule1 }

procedure TDataModule1.PackDbfTables;
var
  I:Integer;
  vExclusive: boolean;
begin
  for I:=Low(VDbfTables) to High(VDbfTables) do begin
    vExclusive:= vDbfTables[I].Exclusive;
    vDbfTables[I].Exclusive:=True;
    try
      vDbfTables[I].PackTable;
      vDbfTables[I].RegenerateIndexes;
      vDbfTables[I].Refresh;
      //ShowMessage('Packed table: '+ vDbfTables[I].TableName);
    except
      on E:Exception do begin
        ShowMessage(E.Message +' Could not pack table: '+ vDbfTables[I].TableName);
      end;
    end;
    vDbfTables[I].Exclusive:=vExclusive;
  end;
end;

procedure TDataModule1.EmptyPackDbfTables;
var
  I:Integer;
  vExclusive: boolean;
begin
  if MessageDlg('This action will delete all tables.'
  +LineEnding+'Are you sure that you want to delete all tables?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      for I:=Low(VDbfTables) to High(VDbfTables) do begin
        vExclusive:= vDbfTables[I].Exclusive;
        vDbfTables[I].Exclusive:=True;
        try
          vDbfTables[I].EmptyTable;
          vDbfTables[I].PackTable;
          vDbfTables[I].RegenerateIndexes;
          vDbfTables[I].Refresh;
          //ShowMessage('Emptied and packed table: '+ vDbfTables[I].TableName);
        except
          on E:Exception do begin
            ShowMessage(E.Message +' Could not empty and pack table: '+ vDbfTables[I].TableName);
          end;
        end;
        vDbfTables[I].Exclusive:=vExclusive;
      end;
    end;
end;

procedure TDataModule1.InitiateFreshDbfTables;
var
  I:Integer;
  vExclusive: boolean;
begin
  if MessageDlg('This action will delete all tables and reinitalize fresh, clean database.'
  +LineEnding+'Are you sure that you want to delete all existing tables?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      for I:=Low(VDbfTables) to High(VDbfTables) do begin
        vExclusive:= vDbfTables[I].Exclusive;
        vDbfTables[I].Exclusive:=True;

        try
          vDbfTables[I].EmptyTable;
          vDbfTables[I].PackTable;
          vDbfTables[I].RegenerateIndexes;
          vDbfTables[I].Refresh;
          //ShowMessage('Emptied and packed table: '+ vDbfTables[I].TableName);
        except
          on E:Exception do begin
            ShowMessage(E.Message +' Could not empty and pack table: '+ vDbfTables[I].TableName);
          end;
        end;

        try
          vDbfTables[I].First;
          vDbfTables[I].Append;
          vDbfTables[I].Post;
          vDbfTables[I].Refresh;
          //ShowMessage('Initialized table: '+ vDbfTables[I].TableName);
        except
          on E:Exception do begin
            ShowMessage(E.Message +' Could not initialize table: '+ vDbfTables[I].TableName);
          end;
        end;

        vDbfTables[I].Exclusive:=vExclusive;
      end;

      for I:=Low(VDbfTables) to High(VDbfTables) do begin
        vDbfTables[I].Active:= False;
        vDbfTables[I].FilePath:='Data';
        try
          vDbfTables[I].Exclusive:=True;
          vDbfTables[I].Active:=True;
        except
          on E:Exception do begin
            if vDbfTables[I].Active then ShowMessage(E.Message
              +'. Error while activating table: '+ vDbfTables[I].TableName)
            else ShowMessage(E.Message
              +'. Could not activate table: '+ vDbfTables[I].TableName);
          end;
        end;
      end;

      //I don't know why, but application must be closed and reopen in order to changes become visible...
      ShowMessage ('Please close and reopen the application!');

    end;
end;

procedure TDataModule1.FieldsNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('FIELDS_AUTOINC').AsInteger:= FieldByName('FIELDS_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('FIELD_ID').AsInteger:= DataModule1.Autoincs.FieldByName('FIELDS_AUTOINC').AsInteger;

  DataSet.FieldByName('Table_id').AsInteger:=Tables.FieldByName('Table_id').AsInteger;
end;

procedure TDataModule1.FiltersBeforePost(DataSet: TDataSet);
begin

end;

procedure TDataModule1.FiltersNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('FILTERS_AUTOINC').AsInteger:= FieldByName('FILTERS_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('FILTER_ID').AsInteger:= DataModule1.Autoincs.FieldByName('FILTERS_AUTOINC').AsInteger;

  DataSet.FieldByName('Project_id').AsInteger:=Projects.FieldByName('Project_id').AsInteger;
end;

procedure TDataModule1.ProjectsAfterInsert(DataSet: TDataSet);
begin

end;

procedure TDataModule1.ProjectsAfterPost(DataSet: TDataSet);
begin
  FormMain.SetZMSQLProject;
end;

procedure TDataModule1.FieldsAfterOpen(DataSet: TDataSet);
begin
  FormMain.UpdateAutocompleteList;
end;

procedure TDataModule1.DataModuleCreate(Sender: TObject);
var i:Integer;
begin

  {$IFDEF EnforceZMSQL}
  DatasourceCSVTableData.DataSet:=ZMQueryDatasetCSVTableData;
  {$ELSE}
  DatasourceCSVTableData.DataSet:=CSVTableData;
  {$ENDIF}

  //Set paths for DBF Tables.
  vDbfTables[1]:=Autoincs;
  vDbfTables[2]:=Projects;
  vDbfTables[3]:=Tables;
  vDbfTables[4]:=Fields;
  vDbfTables[5]:=SQLConnectors;
  vDbfTables[6]:=SQLTables;
  vDbfTables[7]:=Queries;
  vDbfTables[8]:=QueriesParameters;
  vDbfTables[9]:=Filters;

  for I:=Low(VDbfTables) to High(VDbfTables) do begin
    vDbfTables[I].Active:= False;
    vDbfTables[I].FilePath:='Data';
    try

      //Set dbf options
       vDbfTables[I].OpenMode:=omAutoCreate;
       vDbfTables[I].StoreDefs:=True;
       vDbfTables[I].TableLevel:=7;
       vDbfTables[I].Storage:=stoFile;
       vDbfTables[I].UseAutoInc:=True;

      if vDbfTables[I].Storage=stoFile then begin
        //Open DBF file
        vDbfTables[I].Exclusive:=True;
        vDbfTables[I].Active:=True;
      end;
    except
      on E:Exception do begin
        if vDbfTables[I].Active then ShowMessage(E.Message
          +'. Error while activating table: '+ vDbfTables[I].TableName)
        else ShowMessage(E.Message
          +'. Could not activate table: '+ vDbfTables[I].TableName);
      end;
    end;
  end;

end;


procedure TDataModule1.FieldsAfterPost(DataSet: TDataSet);
begin
  FormMain.UpdateAutocompleteList;
end;

procedure TDataModule1.FieldsBeforePost(DataSet: TDataSet);
begin

end;

procedure TDataModule1.ProjectsAfterScroll(DataSet: TDataSet);
begin
  FormMain.SetZMSQLProject;
end;

procedure TDataModule1.ProjectsBeforeDelete(DataSet: TDataSet);
begin
  with Datamodule1 do begin
    try
      Tables.DisableControls;
      Fields.DisableControls;
      SQLConnectors.DisableControls;
      SQLTables.DisableControls;
      Queries.DisableControls;
      QueriesParameters.DisableControls;

      //Enforce referential delete for table "Tables".
      Tables.First;
      while not Tables.Eof do begin
        //Enforce referential delete for table "Fields".
        Fields.First;
        while not Fields.EOF do begin
          Fields.Delete;
        end;
        Fields.Refresh;
        Tables.Delete;
      end;
      Tables.Refresh;

      //Enforce referential delete for table "Filters".
      Filters.First;
      while not Filters.Eof do begin
        Filters.Delete;
      end;
      Filters.Refresh;

      //Enforce referential delete for table "SQLConnectors".
      SQLConnectors.First;
      while not SQLConnectors.Eof do begin
        //Enforce referential delete for table "SQLTables".
        SQLTables.First;
        while not SQLTables.EOF do begin
          SQLTables.Delete;
        end;
        SQLTables.Refresh;
        SQLConnectors.Delete;
      end;

      //Enforce referential delete for table "Queries".
      Queries.First;
      while not Queries.Eof do begin
        //Enforce referential delete for table "QueriesParameters".
        QueriesParameters.First;
        while not QueriesParameters.EOF do begin
          QueriesParameters.Delete;
        end;
        QueriesParameters.Refresh;
        Queries.Delete;
      end;
      Queries.Refresh;

    finally
      Tables.EnableControls;
      Fields.EnableControls;
      SQLConnectors.EnableControls;
      SQLTables.EnableControls;
      Queries.EnableControls;
      QueriesParameters.EnableControls;
    end;
  end;
end;

procedure TDataModule1.ProjectsNewRecord(DataSet: TDataSet);
begin

  if Dataset.State in [dsInsert] then begin
      //Hack to compensate fact that Autoinc is not working anymore for TDBF
      with DataModule1.Autoincs do begin
           First;
           Edit;
           FieldByName('PROJECTS_AUTOINC').AsInteger:= FieldByName('PROJECTS_AUTOINC').AsInteger + 1;
           Post;
           Refresh;
      end;
  end;

  Dataset.FieldByName('PROJECT_ID').AsInteger:= DataModule1.Autoincs.FieldByName('PROJECTS_AUTOINC').AsInteger;

  Dataset.FieldByName('PROJECTNAME').AsString:='Project'+Dataset.FieldByName('PROJECT_ID').AsString;

end;

procedure TDataModule1.QueriesAfterScroll(DataSet: TDataSet);
begin
  if Queries.Active=True then
    FormMain.SynEditSQLEditor.Text:=Datamodule1.Queries.FieldByName('QUERYTEXT').AsString;
end;

procedure TDataModule1.QueriesBeforeDelete(DataSet: TDataSet);
begin
  try
    QueriesParameters.DisableControls;
    //Enforce referential delete for table "QueriesParameters".
    QueriesParameters.First;
    while not QueriesParameters.EOF do begin
      QueriesParameters.Delete;
    end;
    QueriesParameters.Refresh;
  finally
    QueriesParameters.EnableControls;
  end;
end;

procedure TDataModule1.QueriesBeforePost(DataSet: TDataSet);
begin
  //Get SQL from SynEditSQLEditor
  Queries.FieldByName('QUERYTEXT').AsString:=FormMain.SynEditSQLEditor.Text;
end;

procedure TDataModule1.QueriesNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('QUERIES_AUTOINC').AsInteger:= FieldByName('QUERIES_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('QUERY_ID').AsInteger:= DataModule1.Autoincs.FieldByName('QUERIES_AUTOINC').AsInteger;

  DataSet.FieldByName('Project_id').AsInteger:=Projects.FieldByName('Project_id').AsInteger;
  Dataset.FieldByName('QUERYNAME').AsString:='Query' + DataModule1.Autoincs.FieldByName('QUERIES_AUTOINC').AsString;
end;

procedure TDataModule1.QueriesParametersBeforePost(DataSet: TDataSet);
begin

end;

procedure TDataModule1.QueriesParametersNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('QUERIESPARAMETERS_AUTOINC').AsInteger:= FieldByName('QUERIESPARAMETERS_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('QUERYPARAMETER_ID').AsInteger:= DataModule1.Autoincs.FieldByName('QUERIESPARAMETERS_AUTOINC').AsInteger;

  DataSet.FieldByName('Query_id').AsInteger:=Queries.FieldByName('Query_id').AsInteger;
end;

procedure TDataModule1.SQLConnectorsBeforeDelete(DataSet: TDataSet);
begin
  try
    SQLTables.DisableControls;
    //Enforce referential delete for table "SQLTables".
    SQLTables.First;
    while not SQLTables.EOF do begin
      SQLTables.Delete;
    end;
    SQLTables.Refresh;
  finally
    SQLTables.EnableControls;
  end;
end;

procedure TDataModule1.SQLConnectorsBeforePost(DataSet: TDataSet);
begin

end;

procedure TDataModule1.SQLConnectorsNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('SQLCONNECTORS_AUTOINC').AsInteger:= FieldByName('SQLCONNECTORS_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('SQLCONNECTOR_ID').AsInteger:= DataModule1.Autoincs.FieldByName('SQLCONNECTORS_AUTOINC').AsInteger;

  Dataset.FieldByName('PROJECT_ID').AsInteger:=Projects.FieldByName('PROJECT_ID').AsInteger;
  Dataset.FieldByName('TOBEIMPORTED').AsBoolean:=True;
  Dataset.FieldByName('SQLCONNECTORNAME').AsString:='SQLConnector' + DataModule1.Autoincs.FieldByName('SQLCONNECTORS_AUTOINC').AsString;
end;

procedure TDataModule1.SQLTablesBeforePost(DataSet: TDataSet);
begin

end;

procedure TDataModule1.SQLTablesNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('SQLTABLES_AUTOINC').AsInteger:= FieldByName('SQLTABLES_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('SQLTABLE_ID').AsInteger:= DataModule1.Autoincs.FieldByName('SQLTABLES_AUTOINC').AsInteger;

  DataSet.FieldByName('SQLConnector_id').AsInteger:=SQLConnectors.FieldByName('SQLConnector_id').AsInteger;
  Dataset.FieldByName('TOBEIMPORTED').AsBoolean:=True;
end;

procedure TDataModule1.TablesAfterOpen(DataSet: TDataSet);
begin
  FormMain.UpdateTablesList;
end;

procedure TDataModule1.TablesAfterPost(DataSet: TDataSet);
begin
  FormMain.UpdateTablesList;
end;

procedure TDataModule1.TablesBeforeDelete(DataSet: TDataSet);
begin
  try
    Fields.DisableControls;
    //Enforce referential delete for table "Fields".
    Fields.First;
    while not Fields.EOF do begin
      Fields.Delete;
    end;
    Fields.Refresh;
  finally
    Fields.EnableControls;
  end;
end;

procedure TDataModule1.TablesNewRecord(DataSet: TDataSet);
begin
  if Dataset.State in [dsInsert] then begin
    //Hack to compensate fact that Autoinc is not working anymore for TDBF
    with DataModule1.Autoincs do begin
         First;
         Edit;
         FieldByName('TABLES_AUTOINC').AsInteger:= FieldByName('TABLES_AUTOINC').AsInteger + 1;
         Post;
         Refresh;
    end;
  end;

  Dataset.FieldByName('TABLE_ID').AsInteger:= DataModule1.Autoincs.FieldByName('TABLES_AUTOINC').AsInteger;

  DataSet.FieldByName('Project_id').AsInteger:=Projects.FieldByName('Project_id').AsInteger;
end;

initialization
  {$I unit5.ctrs}

end.

