dbdm.pas

DBDesigner 4 is a database design system that integrates database design, modelling, creation and ma

unit DBDM;

//----------------------------------------------------------------------------------------------------------------------
//
// This file is part of fabFORCE DBDesigner4.
// Copyright (C) 2002 Michael G. Zinner, www.fabFORCE.net
//
// DBDesigner4 is free software; you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
//
// DBDesigner4 is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.

// You should have received a copy of the GNU General Public License
// along with DBDesigner4; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
//
//----------------------------------------------------------------------------------------------------------------------
//
// Unit DBDM.pas
// -------------
// Version 1.0, 13.01.2003, Mike
// Description
//   Contains general functions which are used to work with databases
//
// Changes:
//   Version 1.0, 13.01.2003, Mike
//     initial version
//
//----------------------------------------------------------------------------------------------------------------------

interface

uses
  SysUtils, Classes, QTypes, DBXpress, FMTBcd, DBClient, Provider, SqlExpr,
  DB, IniFiles, QForms, QControls, Qt, QDialogs, Contnrs;

type
  // Class storing a Database-Connection
  TDBConn = class(TPersistent)
    constructor Create;
    destructor Destroy; override;

    procedure Assign(Source: TPersistent); override;
  public
    Name,                     // Name of the Datebase - Connection
    Description,              // Description of the DBConn
    DriverName,               // Parameters ...
    GetDriverFunc,
    LibraryName,
    VendorLib: string;
    TableScope: TTableScopes;
    Params: TStringList;
  end;

  // Class to store a Database-Hosts
  TDBHost = class(TPersistent)
    constructor Create;
    destructor Destroy;  override;
  public
    Caption,                  // Caption of the Datebase - Host
    HostName,                 // Name or IP of the Host
    User_Name: string;        // Default User name for connection
  end;

  TDMDB = class(TDataModule)
    SQLConn: TSQLConnection;
    OutputDataSrc: TDataSource;
    OutputQry: TSQLQuery;
    OutputDataSetProvider: TDataSetProvider;
    OutputClientDataSet: TClientDataSet;
    SchemaSQLQuery: TSQLQuery;

    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);

    //Get all DatabaseConnections from the ini-File
    procedure GetDBConns;
    procedure ReadDBConnFromIniFile(theIni: TMemIniFile; dbconnName: string; theDBConn: TDBConn);
    procedure StoreDBConns;
    //Connects to a DBConn
    function ConnectToDB(DBConn: Pointer; theSQLConn: TSQLConnection = nil): Boolean;
    //Disconnect from DB
    procedure DisconnectFromDB;
    //Get a new DBConn and initialize with default values
    function GetNewDBConn(name: string; AddToDBConns: Boolean = True; DatabaseType: string = ''): TDBConn;

    //Let the User select a DBConnection
    function GetUserSelectedDBConn(defDBConn: string): TDBConn;

    //General OnButtonClick f黵 DBConn Buttons
    procedure GetDBConnButtonClick(Sender: TObject; defDBConn: string = '');

    //List all DB-Tables in a Stringlist
    function GetDBTables(var tablelist: TStringList; theSQLConn: TSQLConnection = nil; theDBConn: TDBConn = nil): Boolean;

    //Execute a SQL Command
    procedure ExecSQL(s: string);

    procedure LoadSettingsFromIniFile;
    procedure SaveSettingsToIniFile;

    //Returns the Records of a Query as INSERTs
    function GetRecordsAsInserts(SQLCmd: string; theQry: TClientDataSet; limit: integer = 0): string;
    function GetRecordsAsInsertsTSQLQuery(SQLCmd: string; theQry: TSQLQuery; limit: integer = 0): string;

    //Returns first command from SQL-Script and removes this command from the list
    function GetFirstSQLCmdFromScript(var cmds: String): String;

    function ExecuteSQLCmdScript(cmds: String): integer;
  private
    { Private declarations }
    RunFirstTime: Boolean;
  public
    { Public declarations }
    DatabaseTypes: TStringList;
    DefaultDatabaseType: string;

    DBConnections: TObjectList;
    CurrentDBConn: TDBConn;
  end;

const
  QEventType_SetQueryStatusLbl = QEventType(Integer(QEventType_ClxUser) + 200);
  QEventType_CloseAllClientDatasets = QEventType(Integer(QEventType_ClxUser) + 201);

var
  DMDB: TDMDB;

implementation

{$R *.xfm}

uses DBConnSelect, MainDM;

procedure TDMDB.DataModuleCreate(Sender: TObject);
begin
  RunFirstTime:=False;

  //Create StringLists
  DatabaseTypes:=TStringList.Create;

  CurrentDBConn:=nil;
  DBConnections:=TObjectList.Create;

  LoadSettingsFromIniFile;

  if(Not(RunFirstTime))or(FileExists(DMMain.SettingsPath+'DBConn.ini'))then
    GetDBConns;
end;

procedure TDMDB.DataModuleDestroy(Sender: TObject);
begin
  SaveSettingsToIniFile;

  //Free Stringlists
  DatabaseTypes.Free;

  //Store current DBConns
  StoreDBConns;

  //Free DBConns
  DBConnections.Free;
end;

procedure TDMDB.GetDBConns;
var theIni: TMemIniFile;
  IniFileDBConns: TStringList;
  i: integer;
  dbconnName: string;
  theDBConn: TDBConn;
begin
  //Read IniFile
  theIni:=TMemIniFile.Create(DMMain.SettingsPath+'DBConn.ini');
  try
    IniFileDBConns:=TStringList.Create;
    try

      theIni.ReadSectionValues('DBConn', IniFileDBConns);

      //Make a Node for each DBConn
      for i:=0 to IniFileDBConns.Count-1 do
      begin
        dbconnName:=IniFileDBConns.Values['DBConnName'+IntToStr(i+1)];

        if(dbconnName<>'')then
        begin
          theDBConn:=TDBConn.Create;
          try
            theDBConn.Name:=dbconnName;

            ReadDBConnFromIniFile(theIni, dbconnName, theDBConn);

            DBConnections.Add(theDBConn);
          except
            theDBConn.Free;
          end;
        end;
      end;
    finally
      IniFileDBConns.Free;
    end;
  finally
    theIni.Free;
  end;
end;

procedure TDMDB.ReadDBConnFromIniFile(theIni: TMemIniFile; dbconnName: string; theDBConn: TDBConn);
var theDBConnParams: TStringList;
  ospostfix: string;
begin
  theDBConnParams:=TStringList.Create;
  try
    theIni.ReadSectionValues(dbconnName, theDBConnParams);


    {$IFDEF LINUX}
    ospostfix:='Linux';
    {$ELSE}
    ospostfix:='';
    {$ENDIF}

    with(theDBConnParams)do
    begin
      theDBConn.Name:=dbconnName;
      theDBConn.Description:=Values['Description'];
      theDBConn.DriverName:=Values['DriverName'];
      theDBConn.GetDriverFunc:=Values['GetDriverFunc'];

      theDBConn.LibraryName:=Values['LibraryName'+ospostfix];

      theDBConn.VendorLib:=Values['VendorLib'+ospostfix];
      if(Values['TableScope']<>'')then
      begin
        theDBConn.TableScope:=[];
        if(Pos('tsTable', Values['TableScope'])<>0)then
          theDBConn.TableScope:=theDBConn.TableScope + [tsTable];
        if(Pos('tsView', Values['TableScope'])<>0)then
          theDBConn.TableScope:=theDBConn.TableScope + [tsView];
        if(Pos('tsSysTable', Values['TableScope'])<>0)then
          theDBConn.TableScope:=theDBConn.TableScope + [tsSysTable];
        if(Pos('tsSynonym', Values['TableScope'])<>0)then
          theDBConn.TableScope:=theDBConn.TableScope + [tsSynonym];
      end
      else
        theDBConn.TableScope:=[tsTable, tsView];

      //Delete Native Params
      if(IndexOfName('Description')<>-1)then
        Delete(IndexOfName('Description'));
      if(IndexOfName('DriverName')<>-1)then
        Delete(IndexOfName('DriverName'));
      if(IndexOfName('GetDriverFunc')<>-1)then
        Delete(IndexOfName('GetDriverFunc'));
      if(IndexOfName('LibraryName')<>-1)then
        Delete(IndexOfName('LibraryName'));
      if(IndexOfName('LibraryName'+ospostfix)<>-1)then
        Delete(IndexOfName('LibraryName'+ospostfix));
      if(IndexOfName('VendorLib')<>-1)then
        Delete(IndexOfName('VendorLib'));
      if(IndexOfName('VendorLib'+ospostfix)<>-1)then
        Delete(IndexOfName('VendorLib'+ospostfix));
      if(IndexOfName('TableScope')<>-1)then
        Delete(IndexOfName('TableScope'));
    end;

    theDBConn.Params.Assign(theDBConnParams);
  finally
    theDBConnParams.Free;
  end;
end;

procedure TDMDB.StoreDBConns;
var theIni: TMemIniFile;
  i, j: integer;
  dbconnName, s, ospostfix: string;
  theDBConn: TDBConn;
begin
  {$IFDEF LINUX}
  ospostfix:='Linux';
  {$ELSE}
  ospostfix:='';
  {$ENDIF}

  //delete old file
  DeleteFile(DMMain.SettingsPath+'DBConn.ini');

  //Save IniFile
  theIni:=TMemIniFile.Create(DMMain.SettingsPath+'DBConn.ini');
  try
    //Make a Node for each DBConn
    for i:=0 to DBConnections.Count-1 do
    begin
      theDBConn:=TDBConn(DBConnections[i]);
      dbconnName:=theDBConn.Name;
      theIni.WriteString('DBConn', 'DBConnName'+IntToStr(i+1), dbconnName);

      if(dbconnName<>'')then
      begin
        theIni.WriteString(dbconnName, 'Description', theDBConn.Description);
        theIni.WriteString(dbconnName, 'DriverName', theDBConn.DriverName);
        theIni.WriteString(dbconnName, 'GetDriverFunc', theDBConn.GetDriverFunc);
        theIni.WriteString(dbconnName, 'LibraryName'+ospostfix, theDBConn.LibraryName);
        theIni.WriteString(dbconnName, 'VendorLib'+ospostfix, theDBConn.VendorLib);

        s:='';
        if(theDBConn.TableScope<>[])then
        begin
          s:='[';
          if(tsTable in theDBConn.TableScope)then
            s:=s + 'tsTable ,';
          if(tsView in theDBConn.TableScope)then
            s:=s + 'tsView ,';
          if(tsSysTable in theDBConn.TableScope)then
            s:=s + 'tsSysTable ,';
          if(tsSynonym in theDBConn.TableScope)then
            s:=s + 'tsSynonym ,';

          s:=Copy(s, 1, length(s)-2)+']';
        end
        else
          s:='[tsTable, tsView]';

        theIni.WriteString(dbconnName, 'TableScope', s);

        for j:=0 to theDBConn.Params.Count-1 do
          if(theDBConn.Params.Names[j]<>'Password')then
            theIni.WriteString(dbconnName, theDBConn.Params.Names[j], theDBConn.Params.Values[theDBConn.Params.Names[j]]);

      end;
    end;

    theIni.UpdateFile;
  finally
    theIni.Free;
  end;
end;

function TDMDB.ConnectToDB(DBConn: Pointer; theSQLConn: TSQLConnection = nil): Boolean;
begin
  Result:=False;

  if(CurrentDBConn<>nil)then
    DisconnectFromDB;

  if(theSQLConn=nil)then
    theSQLConn:=SQLConn;

  //Assign the params
  with(theSQLConn)do
  begin
    DriverName:=TDBConn(DBConn).DriverName;
    GetDriverFunc:=TDBConn(DBConn).GetDriverFunc;
    LibraryName:=TDBConn(DBConn).LibraryName;
    VendorLib:=TDBConn(DBConn).VendorLib;
    TableScope:=TDBConn(DBConn).TableScope;
    Params.Assign(TDBConn(DBConn).Params);

    //Open DB
    try
      Open;

      if(Connected)then
        Result:=True;
    except
      on x: Exception do
      begin
        if(theSQLConn=SQLConn)then
          CurrentDBConn:=nil;

        raise;
      end;
    end;

    //If given SQLConnection is the Std. DBConn
    if(theSQLConn=SQLConn)then
    begin
      if(SQLConn.Connected)then
      begin
        CurrentDBConn:=DBConn;

        QApplication_postEvent(Application.MainForm.Handle, QCustomEvent_create(QEventType_SetQueryStatusLbl, nil));
      end
      else
        CurrentDBConn:=nil;
    end;
  end;
end;

procedure TDMDB.DisconnectFromDB;
begin
  if(SQLConn.Connected)then
    SQLConn.Close;

  if(OutputClientDataSet.Active)then
    OutputClientDataSet.Close;

  CurrentDBConn:=nil;

  QApplication_postEvent(Application.MainForm.Handle, QCustomEvent_create(QEventType_SetQueryStatusLbl, nil));
  QApplication_postEvent(Application.MainForm.Handle, QCustomEvent_create(QEventType_CloseAllClientDatasets, nil));
end;

function TDMDB.GetNewDBConn(name: string; AddToDBConns: Boolean = True; DatabaseType: string = ''): TDBConn;
var theDBConn: TDBConn;
  theIni: TMemIniFile;
begin
  theDBConn:=TDBConn.Create;

  //Copy settings from defaults
  //Read IniFile
  theIni:=TMemIniFile.Create(DMMain.SettingsPath+'DBConn_DefaultSettings.ini');
  try
    if(DatabaseType='')then
      DatabaseType:=DMDB.DefaultDatabaseType;

    DMDB.ReadDBConnFromIniFile(theIni, DatabaseType, theDBConn);

    theDBConn.Name:=name;

    if(AddToDBConns)then
      DBConnections.Add(theDBConn);
  finally
    theIni.Free;
  end;

  GetNewDBConn:=theDBConn;
end;

function TDMDB.GetUserSelectedDBConn(defDBConn: string): TDBConn;
var i, s: integer;
begin
  GetUserSelectedDBConn:=nil;

  s:=-1;
  for i:=0 to DBConnections.Count-1 do
    if(TDBConn(DBConnections[i]).name=defDBConn)then
    begin
      s:=i;
      break;
    end;

  DBConnSelectForm:=TDBConnSelectForm.Create(self);
  try
    if(s>-1)then
      DBConnSelectForm.SetData(DBConnections, TDBConn(DBConnections[s]))
    else
      DBConnSelectForm.SetData(DBConnections, nil);
    if(DBConnSelectForm.ShowModal=mrOK)then
    begin
      GetUserSelectedDBConn:=DBConnSelectForm.SelDBConn;
    end;
  finally
    DBConnSelectForm.Free;
  end;
end;

procedure TDMDB.GetDBConnButtonClick(Sender: TObject; defDBConn: string = '');
var SelDBConn: TDBConn;
begin
  if(Sender.ClassNameIs('TSpeedButton'))then
    DMDB.DisconnectFromDB;

  //do until a successful connection is established or the user selects abort
  while(1=1)do
  begin
    //Let the User choose connection if there is no open connection
    if(DMDB.CurrentDBConn=nil)then
      SelDBConn:=DMDB.GetUserSelectedDBConn(defDBConn)
    else
      SelDBConn:=DMDB.CurrentDBConn;

    if(SelDBConn<>nil)then
    begin
      //Try to connect to the DB
      try
        DMDB.ConnectToDB(SelDBConn);
      except
        on x: Exception do
        begin
          MessageDlg(DMMain.GetTranslatedMessage('Connection to database failed.'+#13#10#13#10+'%s', 121,
            x.Message), mtError, [mbOK], 0);

          continue;
        end;
      end;

      break;
    end
    else
      break;