dbeerdm.pas

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

unit DBEERDM;

//----------------------------------------------------------------------------------------------------------------------
//
// 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 DBEERDM.pas
// ----------------
// Version 2.1, 03.05.2003, Mike
// Description
//   Contains the reverse engineering and database syncronisation functions
//
// Changes:
//   Version 2.1, 03.05.2003, Mike
//     introduced GetidDatatype
//   Version 2.0, 18.04.2003, Mike
//     Changed all Records to TObjects.
//   Version 1.3, 07.04.2003, Mike
//     Fixed bug when rev eng ORCL DB, "schema"."tablename", removed all quotes
//   Version 1.2, 01.04.2003, Mike
//     Fixed bug in ReverseEngineering Functions, IndexParams were
//       not assigned and caused an access violation when saving
//     DBSync: BIGINT(20) = BIGINT
//             Ignore LengthParam when the index is FULLTEXT
//   Version 1.1, 20.03.2003, Mike
//     added support for IndexColumn length parameter
//     added support for PrevTableName and generate SQL RENAME command
//   Version 1.0, 13.03.2003, Mike
//     initial version
//
//----------------------------------------------------------------------------------------------------------------------

interface

uses
  SysUtils, Classes, QTypes, SqlExpr, DB, QForms, QStdCtrls, QDialogs,
  EERModel, DBXpress, RegExpr;

type
  CreateTableSyntax = (MySQL, SQLite, Oracle, MSSQL);

  TDMDBEER = class(TDataModule)

    //Do the Reverse Engeneering
    procedure EERReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0);
    procedure EERMySQLReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0);
    procedure EERORCLReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0; ImportSchema: Boolean = False; PutDefaultValuesInQuotes: Boolean = False);
    procedure EERSQLiteReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0);
    procedure EERMSSQLReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0; CollapseTables: Boolean = False);
    procedure EERMySQLReverseEngineer2(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0);
    procedure BuildTableFromCreateStatement(theTable: TEERTable; theCreateStatement: string; Syntax: CreateTableSyntax);

    procedure EERReverseEngineerMakeRelations(theModel: Pointer; theTables: TList; BuildRelUsingPrimKey: Boolean);
    procedure EERReverseEngineerCreateStdInserts(theModel: Pointer; theTables: TList; limit: integer;
      StatusLbl: TLabel = nil; ImportSchema: Boolean = False);

    procedure EERMySQLSyncDB(theModel: Pointer; DBConn: Pointer; Log: TStrings;
      KeepExTbls, StdInsertsOnCreate, StdInsertsSync: Boolean);
    procedure EERMySQLSyncStdInserts(EERTable: Pointer; var Log: TStrings);
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);

    function RemoveCommentsFromSQLCmd(cmd: string): string;
    function GetColumnCountFromSQLCmd(cmd: string): integer;
    procedure GetColumnFromSQLCmd(cmd: string; i: integer; var col: TEERColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  DMDBEER: TDMDBEER;

implementation

uses MainDM, DBDM, EERDM;

{$R *.xfm}



procedure TDMDBEER.DataModuleCreate(Sender: TObject);
begin
  //
end;

procedure TDMDBEER.DataModuleDestroy(Sender: TObject);
begin
  //
end;

procedure TDMDBEER.EERReverseEngineer(theModel: Pointer; DBConn: Pointer; theTables: TStringList; XCount: integer; BuildRelations: Boolean; BuildRelUsingPrimKey: Boolean; DatatypeSubst: TStringList; StatusLbl: TLabel = nil; CreateStdInserts: Boolean = False; limitStdIns: integer = 0);
var DbTables: TList;
  i, j, xpos, ypos, xanz, defwidth, defheight, iddatatype: integer;
  DatatypeName, prevIndex, tablename, indexname: string;
  EERModel: TEERModel;
  theTable, tmpTbl: TEERTable;
  theColumn: TEERColumn;
  theIndex: TEERIndex;
  tblAtPos: Boolean;
  fldColumnName :TField; // Addition by Vadim
  //s: string;
  theQuoteChar: String;
begin
  EERModel:=theModel;

  defwidth:=220;
  defheight:=160;
  xanz:=XCount;
  xpos:=0;
  ypos:=0;

  //GetTables
  //Col 0: RECNO
  //1: CATALOG_NAME
  //2: SCHEMA_NAME
  //3: TABLE_NAME
  //4: TABLE_TYPE

  if(StatusLbl<>nil)then
  begin
    StatusLbl.Caption:=DMMain.GetTranslatedMessage('Fetching Tables', 147);
    StatusLbl.Refresh;
    Application.ProcessMessages;
  end;

  DbTables:=TList.Create;
  try
    DMDB.SchemaSQLQuery.SetSchemaInfo(stTables, '', '');
    DMDB.SchemaSQLQuery.Open;
    theQuoteChar:=DMDB.SchemaSQLQuery.GetQuoteChar;

    while(Not(DMDB.SchemaSQLQuery.EOF))do
    begin
      tablename:=DMDB.SchemaSQLQuery.Fields[3].AsString;

      //get only selected tables
      if(theTables.IndexOf(tablename)<>-1)then
      begin
        //Place at temporary position and reposition after Columns and
        //indices have been added
        theTable:=EERModel.NewTable(EERModel.EERModel_Width-250, 0, False);


        if(Pos(theQuoteChar, tablename)>0)then
          theTable.ObjName:=DMMain.ReplaceText(tablename, theQuoteChar, '')
        else
          theTable.ObjName:=tablename;


        //theTable.RefreshObj;

        DbTables.Add(theTable);
      end;

      DMDB.SchemaSQLQuery.Next;
    end;
    DMDB.SchemaSQLQuery.Close;

    //Get the columns
    for i:=0 to DbTables.Count-1 do
    begin
      if(StatusLbl<>nil)then
      begin
        StatusLbl.Caption:=DMMain.GetTranslatedMessage('Fetching Table Columns/Indices (%s)', 148,
          TEERTable(DbTables[i]).ObjName);
        StatusLbl.Refresh;
        Application.ProcessMessages;
      end;

      //Get Columns
      //Col 0: RECNO
      //1: CATALOG_NAME
      //2: SCHEMA_NAME
      //3: TABLE_NAME
      //4: COLUMN_NAME
      //5: COLUMN_POSITION
      //6: COLUMN_TYPE
      //7: COLUMN_DATATYPE
      //8: COLUMN_TYPENAME
      //9: COLUMN_SUBTYPE
      //10: COLUMN_LENGTH
      //11: COLUMN_PRECISION
      //12: COLUMN_SCALE
      //13: COLUMN_NULLABLE

      DMDB.SchemaSQLQuery.SetSchemaInfo(stColumns, TEERTable(DbTables[i]).ObjName, '');
      DMDB.SchemaSQLQuery.Open;

      while(Not(DMDB.SchemaSQLQuery.EOF))do
      begin
        //new(theColumn);
        theColumn:=TEERColumn.Create(TEERTable(DbTables[i]));
        TEERTable(DbTables[i]).Columns.Add(theColumn);

        DatatypeName:=DMDB.SchemaSQLQuery.Fields[8].AsString;

        theColumn.ColName:=DMDB.SchemaSQLQuery.Fields[4].AsString;
        theColumn.Obj_id:=DMMain.GetNextGlobalID;
        theColumn.Pos:=TEERTable(DbTables[i]).Columns.Count;
        theColumn.idDatatype:=TEERDatatype(EERModel.GetDataTypeByNameSubst(DatatypeName, DatatypeSubst)).id;
        theColumn.DatatypeParams:='';
        theColumn.Width:=DMDB.SchemaSQLQuery.Fields[10].AsInteger;
        theColumn.Prec:=DMDB.SchemaSQLQuery.Fields[11].AsInteger;
        theColumn.PrimaryKey:=False;
        theColumn.NotNull:=(DMDB.SchemaSQLQuery.Fields[13].AsString='1');
        theColumn.AutoInc:=False;
        theColumn.IsForeignKey:=False;

        if(CompareText(DMDB.SchemaSQLQuery.Fields[8].AsString,
          'varchar')=0)then
          //The openodbc Driver returns Columns Params at Pos 10
          if(DMDB.CurrentDBConn.DriverName='openodbc')then
            theColumn.DatatypeParams:='('+DMDB.SchemaSQLQuery.Fields[10].AsString+')'
          else
            theColumn.DatatypeParams:='('+DMDB.SchemaSQLQuery.Fields[11].AsString+')';

        {if(CompareText(SchemaSQLQuery.Fields[8].AsString,
          'float')=0)then
          theColumn.DatatypeParams:='('+SchemaSQLQuery.Fields[12].AsString+', '+SchemaSQLQuery.Fields[11].AsString+')';}

        DMDB.SchemaSQLQuery.Next;
      end;
      DMDB.SchemaSQLQuery.Close;

      TEERTable(DbTables[i]).RefreshObj;

      //Get Indices
      //Col 0: RECNO
      //1: CATALOG_NAME
      //2: SCHEMA_NAME
      //3: TABLE_NAME
      //4: INDEX_NAME
      //5: COLUMN_NAME
      //6: COLUMN_POSITION
      //7: PKEY_NAME
      //8: INDEX_TYPE
      //9: SORT_ORDER
      //10: FILTER

      prevIndex:='';

      DMDB.SchemaSQLQuery.SetSchemaInfo(stIndexes, TEERTable(DbTables[i]).ObjName, '');
      DMDB.SchemaSQLQuery.Open;

      {s:='';
      for j:=0 to DMMain.SchemaSQLQuery.FieldCount-1 do
        s:=s+IntToStr(j)+': '+DMMain.SchemaSQLQuery.Fields[j].DisplayName+#13#10;
      ShowMessage(s);}

      theIndex:=nil;

      // Addition by Vadim
      fldColumnName := DMDB.SchemaSQLQuery.FindField('COLUMN_NAME');
      if Assigned(fldColumnName) then
        while(Not(DMDB.SchemaSQLQuery.EOF))do
        begin
          indexname:=DMDB.SchemaSQLQuery.Fields[4].AsString;

          //Don't add the same Index a second time
          if(prevIndex<>indexname)then
          begin
            //new(theIndex);
            theIndex:=TEERIndex.Create(TEERTable(DbTables[i]));
            theIndex.Obj_id:=DMMain.GetNextGlobalID;
            theIndex.IndexName:=indexname;
            if(CompareText(Copy(indexname, 1, 7), 'PRIMARY')=0)then
              theIndex.IndexKind:=ik_PRIMARY
            else
              theIndex.IndexKind:=ik_INDEX;
            TEERTable(DbTables[i]).Indices.Add(theIndex);
            theIndex.Pos:=TEERTable(DbTables[i]).Indices.Count-1;

          end;

          {//The openodbc Driver returns Column Name at Pos 6
          if(DMDB.CurrentDBConn.DriverName='openodbc')then
            theColumn:=TEERTable(DbTables[i]).GetColumnByName(DMDB.SchemaSQLQuery.Fields[6].AsString)
          else
            theColumn:=TEERTable(DbTables[i]).GetColumnByName(DMDB.SchemaSQLQuery.Fields[5].AsString);}

          // Addition by Vadim
          theColumn:=TEERTable(DbTables[i]).GetColumnByName(fldColumnName.AsString);

          if(CompareText(Copy(indexname, 1, 7), 'PRIMARY')=0)then
            theColumn.PrimaryKey:=True;

          theIndex.Columns.Add(IntToStr(theColumn.Obj_id));


          prevIndex:=indexname;
          DMDB.SchemaSQLQuery.Next;
        end;
        DMDB.SchemaSQLQuery.Close;

        TEERTable(DbTables[i]).RefreshObj;
      end;

    //Order table positions
    for j:=0 to EERModel.ComponentCount-1 do
    begin
      if(EERModel.Components[j].ClassNameIs('TEERTable'))then
      begin
        theTable:=TEERTable(EERModel.Components[j]);

        theTable.Obj_X:=80+xpos*defwidth;
        theTable.Obj_Y:=40+ypos*defheight;

        //Remove quotations
        theTable.ObjName:=DMMain.ReplaceText(theTable.ObjName, theQuoteChar, '');
        {theTable.ObjName:=DMMain.ReplaceText(theTable.ObjName, '"', '');
        theTable.ObjName:=DMMain.ReplaceText(theTable.ObjName, '''', '');
        theTable.ObjName:=DMMain.ReplaceText(theTable.ObjName, '`', '');
        theTable.ObjName:=DMMain.ReplaceText(theTable.ObjName, '