DataTranADO

From RiskWiki

Jump to: navigation, search

DataTranADO - Data transfer library for moving data between a central DB and many remote DB's

Language: Delphi 7 - 2007

DataTranADO - Data transfer library for moving data between a central DB and many remote DB's with support for synchronising across DB's when DB's cannot use an ADO connection. This library is the heart of the CRIS remote connection module. It provides a method for many remote desktop systems to synchronise across remote networks - even across dial up lines, where an ADO Connector can not be established between the databases.


Because of its approach, the library can be used to synchronise data between differing versions or even types of databases, provided the databases at each end can use ADO connectors. It essentially converts all actions to SQL statements.


Registers TDataTranADODataSet on the Palette.


uses
  Windows, Messages, SysUtils, Classes, DB, ADODB, Dialogs, Variants, JclStrings;

type
  { Data transfer scripting --> Parsing options.
  Options to only evaluate user variables only (ignore executing stored procedures and resolving table field values) }
  erExpressionRestrictions = (eetAll, eetUserVarsOnly);

  { Which fields from a table get included in a SQL update or insert clause:
  1. All fields (such as an insert)
  2. Writeable only (such as a remote user only uploading changes to fields which they have publication access to)
  3. Writeable and exclude keys (such as a set clause which doesn't require the key fields)
  4. ** No longer in use **
     Description:
     All except exclude fields which are exclusive to the interactive remote user and the key fields (such as an update sent
     from central to a remote user which must exclude fields which the remote user has exclusive access to - don't want to
     overwrite pending updates which have not yet been transferred - and the key fields)
     Problem:
     Central was only sending updates for fields to inspectors which central and licensees have publication access to OR the field is for multi-use.
     Changes made by one inspector were being transferred to central BUT central was then not transferring the change to other inspectors.
     This change only applies to central where it has special rules; it must act as an information brocker.     
  5. All fields and exclude keys (applicable to central acting as an information broker)  }
  isfIncludeSQLFields = (isfAll, isfWriteableOnly, isfWriteableAndExcludeKeys, isfExcludeExclusiveToRemoteUserAndKeys, isfAllAndExcludeKeys);

  // Data Transfer scripting keywords -> VAR[], FIELD[], EXEC[]
  dtKeywordType = (dtVar, dtField, dtSPExec);

  TEvalExpression = class(TObject)
  private
    FADOQuery : TADOQuery;
  protected
  public
    constructor Create(AOwner: TComponent; AADOConnection: TADOConnection); reintroduce;
    destructor Destroy; override;

    function Eval(const AExpression : String; out AErrorFlag : Boolean;
      out AErrorMessage : String) : Boolean;
  end;

  TDataSharingRules = class(TObject)
  private
    FADOConnection : TADOConnection;
    FDT_CentralBrokerFlag: Boolean;
    FDT_UserNo: String;
    FDT_UserType: String;
    FDT_SharedTableName: String;
    FScript: String;
    FSourceDataSet: TDataSet;
    procedure SetDT_CentralBrokerFlag(const Value: Boolean);
    procedure SetDT_SharedTableName(const Value: String);
    procedure SetDT_UserNo(const Value: String);
    procedure SetDT_UserType(const Value: String);
    procedure SetScript(const Value: String);

    function Peek(const AScript : String; APosition : Integer; const AKeyword : String) : Boolean;
    procedure SetSourceDataSet(const Value: TDataSet);
    procedure SetADOConnection(const Value: TADOConnection);
  protected
    function GetKeyValue(const AScript : String; APosition : Integer; out ASubstitutedLength : Integer; AKeyWordType : dtKeywordType) : String;
    function SubstituteKeyVariable(const AVariableName : String) : String;
    function SubstituteKeyField(const AFieldName : String) : String;
    function ExecDTStoredProc(const AScript : String) : Boolean;
    function ExtractStoredProcNameFromScript(const AScript : String) : String;
  public
    property ADOConnection : TADOConnection read FADOConnection write SetADOConnection;
    property DT_SharedTableName : String read FDT_SharedTableName write SetDT_SharedTableName;

    property DT_UserNo : String read FDT_UserNo write SetDT_UserNo;
    property DT_UserType : String read FDT_UserType write SetDT_UserType;
    property DT_CentralBrokerFlag : Boolean read FDT_CentralBrokerFlag write SetDT_CentralBrokerFlag;
    property Script : String read FScript write SetScript;
    property SourceDataSet : TDataSet read FSourceDataSet write SetSourceDataSet;
    procedure Clear;

    function GetDTExpression(out AUserVarsOnly_InUse : Boolean; AExpressionRestrictions : erExpressionRestrictions = eetAll) : String;
    function EvaluateUser(const AEvalExpression : String; out AErrorEncountered : Boolean) : Boolean;

    function GetPrimaryKeyFieldsList : String;
    procedure ReadDataSharingProperties(out ASharedTable, AMastertListTable, APublisher : Boolean;
      out AAfterScrollPublicationScript : String; out AOriginator, APublishAllFields : Boolean);
    function ApplyRecordLocking : Boolean;
    procedure GetSharedFieldRules(APublishFieldsList, AReadOnlyFieldsList : TStringList;
      AEvalUserVarsOnly : Boolean; out AFieldScriptUseUserVarsOnly : Boolean);
    procedure AssignTableSubscribers(AUserNoList, AUserTypeList : TStringList);
    procedure LogTransferRecord(AUpdateKind : TUpdateKind; const AKeyFieldNames, AKeyFieldValues : String; const ARestrictTransferFieldNames : String = '');
    function GetDTStoredProc_ExecExpresssion(const AScript : String;
      ASourceDataSet: TDataSet; ATransferRecordsDataSet : TDataSet) : String;
  end;

  TTransferData = class(TObject)
  private
    FADOConnection: TADOConnection;

    FDT_CentralBrokerFlag: Boolean;
    FDT_UserType: String;
    FDT_UserNo: String;

    FSavedShortDateFormat : String;
    FSavedLongDateFormat : String;
    FDT_TransferToRemoteUserType: String;
    FDT_TransferToRemoteUserNo: String;
    FDataSharingRules: TDataSharingRules;
    FSQLWhereKeyClause : String;
    procedure SetDT_CentralBrokerFlag(const Value: Boolean);
    procedure SetDT_UserNo(const Value: String);
    procedure SetDT_UserType(const Value: String);
    procedure SetADOConnection(const Value: TADOConnection);
    procedure SetDT_TransferToRemoteUserNo(const Value: String);
    procedure SetDT_TransferToRemoteUserType(const Value: String);
    procedure SetDataSharingRules(const Value: TDataSharingRules);
  protected
    procedure AssignFieldNames(const ASharedTableName : String; ADataSet : TDataSet;
      AFieldNameList : TStringList; AIncludeSQLFields : isfIncludeSQLFields; ATransferRestrictedFieldList : String = '');
    procedure AssignFieldValues(ADataSet : TDataSet; AFieldNameList, AFieldValueList : TStringList);
    function GetSQLValidStringValueFromField(AField : TField) : String;
    function GetSQLValidStringValueFromString(const AFieldAsString : String) : String;

    function GetDeleteAllSQL(const ASharedTableName : String) : String;
    function GetInsertAllSQL(const ASharedTableName : String) : String;
    function GetInsertSQL(const ASharedTableName : String; ADataSet : TDataSet; AIncludeSQLFields : isfIncludeSQLFields) : String;
    function GetUpdateSQL(const ASharedTableName : String; ADataSet : TDataSet; AIncludeSQLFields : isfIncludeSQLFields;
      ATransferRestrictedFieldList : String) : String;

    function GetUploadSQL(const ASharedTableName : String; ASourceDataSet: TDataSet; ATransferRecordsDataSet : TDataSet) : String;
    function GetSQLWhereClauseFromKeyFields(const ASharedTableName : String; ADataSet : TDataSet) : String;
    function GetSQLWhereClauseFromTransferRecordFields(const AKeyFieldNames, AKeyFieldValues : String) : String;
    function GetSetSQLClause(AFieldNameList : TStringList; ADataSet : TDataSet) : String;

    function GetUploadTimeStamp(const ASharedTableName : String; ADataSet : TDataSet) : String;
    function GetRecordLocking(const ASharedTableName : String; ADataSet : TDataSet) : String;
    function GetTransferRecordAsUploadedState(ARecordNumber, APostedState : Integer) : String;
    procedure ResetPartUploadedTransferRecords;
    function GetExecStoredProcDistributeChanges(const ASharedTableName: String;
      ASourceDataSet: TDataSet; ATransferRecordsDataSet : TDataSet) : String;

    function GetRunSQLFieldName(const ASharedTableName : String) : String;

    procedure ExecuteLocalSQL(const ASQLText : String);

    // System Date-Time variable management
    procedure SetStandardTransferDateTimeFormat;
    procedure ResetDateTimeFormat;

    // TDataSharingRules object to read data sharing properties from the database <data transfer> system tables.
    property DataSharingRules : TDataSharingRules read FDataSharingRules write SetDataSharingRules;
  public
    constructor Create;
    destructor Destroy; override;

    property ADOConnection : TADOConnection read FADOConnection write SetADOConnection;
    property DT_UserNo : String read FDT_UserNo write SetDT_UserNo;
    property DT_UserType : String read FDT_UserType write SetDT_UserType;
    property DT_CentralBrokerFlag : Boolean read FDT_CentralBrokerFlag write SetDT_CentralBrokerFlag;
    property DT_TransferToRemoteUserNo : String read FDT_TransferToRemoteUserNo write SetDT_TransferToRemoteUserNo;
    property DT_TransferToRemoteUserType : String read FDT_TransferToRemoteUserType write SetDT_TransferToRemoteUserType;

    // Transaction Management
    function BeginDBTransaction : Boolean;
    function CommitDBTransaction : Boolean;
    function RollbackDBTransaction : Boolean;

    // Date-Time System Checking
    function GetLocalSystemDateTime(out LocalDateTime : TDateTime) : Boolean;
    function ValidateLocalDateTime(CentralDateTime : TDateTime) : Boolean; // Run on remote systems

    // Data Transfer
    procedure Transfer_Import(out AErrorMessage : String; out AErrorFlag : Boolean; Const AImportString : AnsiString);
    function  Transfer_ProcessUpload(out AErrorMessage : String; out AErrorFlag : Boolean) : AnsiString;
    function Transfer_ProcessDBUpgradeUpload(out AErrorMessage : String; out AErrorFlag : Boolean) : AnsiString; // Run on central system only
    procedure Transfer_PostUploaded(out AErrorMessage : String; out AErrorFlag : Boolean);
    procedure Transfer_UnPostUploaded(out AErrorMessage : String; out AErrorFlag : Boolean);

    function  RecordCentralTransferHistory(AIgnoreDateSynchFlag, ATransferIncompleteFlag, AErrorFlag : Boolean;
      const AErrorDescript, ASecurityStatus, ATransferDetails : String) : Boolean; // Run on central system only
  end;

  TDataTranADODataSet = class(TADODataSet)
  private
    FDataTran_SharedTable : Boolean;
    FDataTran_PublishAllFields: Boolean;
    FDataTran_Publisher: Boolean;
    FAfterScrollPublicationScript : String;
    FEvaluateFieldRulesAfterScroll : Boolean;
    FDataTran_Originator: Boolean;
    FDataTran_PublishFieldsList: TStringList;
    FDataSharingRules: TDataSharingRules;
    FDataTran_ReadOnlyFieldsList: TStringList;
    FDT_CentralBrokerFlag: Boolean;
    FDT_UserNo: String;
    FDT_UserType: String;
    FDT_SharedTableName: String;
    FDataTran_MastertListTable: Boolean;
    FSavedShortDateFormat : String;
    FSavedLongDateFormat : String;
    FDataTran_RestrictTransferFields: Boolean;
    FDataTran_RestrictTransferFieldNames: String;
    procedure SetDataTran_SharedTable(const Value: Boolean);
    procedure SetDataTran_Originator(const Value: Boolean);
    procedure SetDataTran_PublishAllFields(const Value: Boolean);
    procedure SetDataTran_Publisher(const Value: Boolean);
    procedure SetDataTran_PublishFieldsList(const Value: TStringList);
    procedure SetDataSharingRules(const Value: TDataSharingRules);
    procedure SetDataTran_ReadOnlyFieldsList(const Value: TStringList);
    procedure SetDT_CentralBrokerFlag(const Value: Boolean);
    procedure SetDT_SharedTableName(const Value: String);
    procedure SetDT_UserNo(const Value: String);
    procedure SetDT_UserType(const Value: String);
    procedure SetDataTran_MastertListTable(const Value: Boolean);
    procedure SetAfterScrollPublicationScript(const Value: String);
    procedure SetDataTran_RestrictTransferFieldNames(
      const Value: String);
    procedure SetDataTran_RestrictTransferFields(const Value: Boolean);
    { Private declarations }
  protected
    { Protected declarations }
    procedure DoBeforeOpen; override;
    procedure DoOnNewRecord; override;
    procedure DoAfterPost; override;
    procedure DoBeforeDelete; override;
    procedure DoBeforeEdit; override;
    procedure DoAfterOpen; override;
    procedure DoAfterScroll; override;
    procedure AssignRecordKeyProperties(out AKeyFieldList, AKeyFieldValues : String);
    procedure SetStandardTransferDateTimeFormat;
    procedure ResetDateTimeFormat;        
  public
    { Public declarations }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    procedure ReReadSharedFieldRules;

    // Is the table shared?
    property DataTran_SharedTable : Boolean read FDataTran_SharedTable write SetDataTran_SharedTable;
    // Is the table a master list?
    property DataTran_MastertListTable : Boolean read FDataTran_MastertListTable write SetDataTran_MastertListTable;
    // Can I write to the table?
    property DataTran_Publisher : Boolean read FDataTran_Publisher write SetDataTran_Publisher;
    // If the condition under which I can write to a table depends upon field values when I scroll to different records then what is the condition script??
    // If this variable is blank then do not evaluate if I can publish after scrolling records.
    property AfterScrollPublicationScript : String read FAfterScrollPublicationScript write SetAfterScrollPublicationScript;
    // Can I insert new records?
    property DataTran_Originator : Boolean read FDataTran_Originator write SetDataTran_Originator;
    // Does publish all fields apply to this table?
    property DataTran_PublishAllFields : Boolean read FDataTran_PublishAllFields write SetDataTran_PublishAllFields;
    // If publish all fields does not apply then which fields can I edit?
    property DataTran_PublishFieldsList : TStringList read FDataTran_PublishFieldsList write SetDataTran_PublishFieldsList;
    // If publish all fields does not apply then which fields are read only?
    property DataTran_ReadOnlyFieldsList : TStringList read FDataTran_ReadOnlyFieldsList write SetDataTran_ReadOnlyFieldsList;

    // TDataSharingRules object to read data sharing properties from the database <data transfer> system tables.
    property DataSharingRules : TDataSharingRules read FDataSharingRules write SetDataSharingRules;

    property DataTran_RestrictTransferFields : Boolean read FDataTran_RestrictTransferFields write SetDataTran_RestrictTransferFields;
    property DataTran_RestrictTransferFieldNames : String read FDataTran_RestrictTransferFieldNames write SetDataTran_RestrictTransferFieldNames;
  published
    { Published declarations }
    // My user number, EG: Licensee number, inspector region ID
    property DT_UserNo : String read FDT_UserNo write SetDT_UserNo;
    // My user type, EG: CEN, INS, LIC
    property DT_UserType : String read FDT_UserType write SetDT_UserType;
    // Am I the central broker?
    property DT_CentralBrokerFlag : Boolean read FDT_CentralBrokerFlag write SetDT_CentralBrokerFlag;
    // The table name used to read shared table name properties
    property DT_SharedTableName : String read FDT_SharedTableName write SetDT_SharedTableName;
  end;

const
  CKeyword_VAR = 'VAR';
  CKeyword_FIELD = 'FIELD';
  CKeywordRunSP_EXEC = 'EXEC';

  CRemoteTimeStamp = 'remote_time_stamp';
  CCentralTimeStamp = 'central_time_stamp';
  CLicenseeTimeStamp = 'licensee_time_stamp';
  CInspectorTimeStamp = 'inspector_time_stamp';

  CNotUploadedState = 0;
  CPartUploadedState = 1;
  CPostUploadedState = 2;

  CInspectorUserType = 'INS';
  CLicenseeUserType = 'LIC';

  CDateTimeToleranceValue = 1/12; // 2 hours tolerance above and below (a 4 hour window)

BackLinks



CopyRight Bishop Phillips Consulting Pty Ltd 1997-2012 ( DataTranADO )
Personal tools