Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Compare 2 Firebird structures(500 pts) and list differences (750 pts)

I would like some source to query a firebird database structure, and compare it with a "master" database structure
This is so that I can check 60 databases and see which ones may have missed an upgrade which updated the structure
I know this can be done by querying the RDB$ tables in the structure
The actual RDB$ names may not match exactly.
e.g. tables may have been created in a different order. I don't mind, as long as the tables are there.
For this reason you will have to do queries on the table name at the client, and use the reference given by that for further comparisons
For simplicity, all I require for 500 pts is to know if the databases are different (missing/different tables/domains/procedures/views)
for a total of 750 points, i would like to know the differences

Ideally the results I would like are so I can tell

at the table level
     table missing: "mytable2" does not exist                - if the table is missing from the client database
     extra table exists: "mytable3" exists                      - this table is not in the master's database, but for some reason is in a client's

if the table exists, then check the fields and return results if they differ
     field differs: "mytable1.Home_Phone" is different   - if the client's field is varchar(10) but should be varchar(12) for example
     field missing: "mytable1.Mobile_Phone is missing   - if the field does not exist in the client's database but is in the master's
     extra field: "maytable1.Fax_Number"                    - if the field is not in the master's database, but for some reason is i a client's
     index/constaint missing:
     index/constaint differs:
     extra index/constaint:

stored procedure checks
     stored procedure missing: "myprocedure1"
     stored procedure diffferent: "myprocedure 2"
     extra stored procedure

views checks
     view missing: "myprocedure1"
     view diffferent: "myprocedure2"
     extra view

domains
     domain missing:
     domain different:
     extra domain:

generators
     generator missing:
     generator different:
     extra generator:

triggers
     trigger missing:
     trigger different:
     extra trigger:

I lack the time to do this along with the other workload I have
If someone has something like this in Delphi, or can code it up for me, it would really be great.
0
TheRealLoki
Asked:
TheRealLoki
  • 9
  • 5
1 Solution
 
esoftbgCommented:
I can code it for you, but I need may be 24 hours, because I can not start coding it immediately ....
So if anybady does not provide an example early, I will do it as soon as possible for me.
0
 
esoftbgCommented:
Please download an example about Compareing Tables from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21406743.zip        Compare 2 Firebird structures(500 pts) and list differences (750 pts)

I will contionue tomorrow ....
0
 
TheRealLokiSenior DeveloperAuthor Commented:
great stuff so far. If you can get the rest of it done, it's a safe bet the points are yours
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
esoftbgCommented:
Unfortunately I have some mistake and first must repair it ....
0
 
esoftbgCommented:
Please download the example again.
I think this time it works fine about Tables and Stored Procedures.
I will continue with views, generators and triggers this evening ....
0
 
esoftbgCommented:
Please download the example again.
I made it about all the requirements in the question.

Emil
0
 
TheRealLokiSenior DeveloperAuthor Commented:
great work Emil!
any chance of doing the views and domains also?
0
 
esoftbgCommented:
it works with views, but I never have been used domains, so if it is possible, post a simple example of a domain, or create a domain into a DataBase, and send it as a zipped file to: esoftbg_e_e@yahoo.com
Tis way I will be able to test the Database with a domain.
0
 
TheRealLokiSenior DeveloperAuthor Commented:
CREATE DOMAIN DMN_APPOINTMENTACTIONTYPE AS CHAR(1) CHARACTER SET WIN1251
NOT NULL
CHECK ((value = 'M') or (value = 'E'));
0
 
esoftbgCommented:
Thank you !
(You use CHARACTER SET WIN1251, so I guess you are a Russian or Bulgarian)
0
 
TheRealLokiSenior DeveloperAuthor Commented:
no, it was simply the default when i made this domain (the utility i use is russian).
it is unimportant as the value is restricting the input. I'm from New Zealand
0
 
esoftbgCommented:
O.k.
Please download the example again.
I think all is fine !
Emil
0
 
esoftbgCommented:
Here is the source code (1202 lines):

//................................................................................................................................

unit Unit1_Q_21406743;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
  Forms, Dialogs, StdCtrls, Buttons, Grids, ComCtrls, ExtCtrls, DB,
  DBGrids, IBDatabase, IBExtract, IBCustomDataSet, IBQuery;

type
  TGet_Mode = (gmItems, gmDetails);
  TLISTRec = record
    STRLST: TStringList;
    Exists: Boolean;
  end;
  TLBxArr = array of TLISTRec;
  TDBMode = (dbmMaster, dbmClient);
  TForm_Comparison = class(TForm)
      IBDB_Master: TIBDatabase;
      IBTR_Master: TIBTransaction;
      PageControl: TPageControl;
      tbsh_Tables: TTabSheet;
      tbsh_Procedures: TTabSheet;
      Edit_Master: TEdit;
      sgr_Client_List: TStringGrid;
      IBDB_Client: TIBDatabase;
      IBTR_Client: TIBTransaction;
      IBExtract: TIBExtract;
      Label1: TLabel;
      bbt_Do_Comparison_Procedures: TBitBtn;
      Memo_Procedures: TMemo;
      tbsh_Views: TTabSheet;
      bbt_Do_Comparison_Views: TBitBtn;
      Memo_Views: TMemo;
      tbsh_Generators: TTabSheet;
      bbt_Do_Comparison_Generators: TBitBtn;
      Memo_Generators: TMemo;
      tbsh_Triggers: TTabSheet;
      bbt_Do_Comparison_Triggers: TBitBtn;
      Memo_Triggers: TMemo;
      tbsh_Domains: TTabSheet;
      bbt_Do_Comparison_Domains: TBitBtn;
      Memo_Domains: TMemo;
      bbt_Do_Comparison_Tables: TBitBtn;
      Memo: TMemo;
      procedure FormCreate(Sender: TObject);
      procedure bbt_Do_Comparison_TablesClick(Sender: TObject);
      procedure FormDestroy(Sender: TObject);
      procedure bbt_Do_Comparison_ProceduresClick(Sender: TObject);
      procedure bbt_Do_Comparison_ViewsClick(Sender: TObject);
      procedure bbt_Do_Comparison_GeneratorsClick(Sender: TObject);
      procedure bbt_Do_Comparison_TriggersClick(Sender: TObject);
      procedure bbt_Do_Comparison_DomainsClick(Sender: TObject);
    private   { Private declarations }
      Total:    Integer;
      SLT:      TStringList;
      SL:       TStringList;
      LIST_M:   TLBxArr;
      LIST_C:   array of TLBxArr;
      SH:       string;
    public    { Public declarations }
      function  Connect_Database(IBDB: TIBDatabase; DB_Name: string): Boolean;
      function  LBx_M_Add(IBDB: TIBDatabase; IBTR: TIBTransaction; DB_Name, Table_Name: string; Get_Mode: TGet_Mode; Cnt: Integer): Integer;
      function  LBx_C_Add(IBDB: TIBDatabase; IBTR: TIBTransaction; DB_Name, Table_Name: string; Get_Mode: TGet_Mode; C: Integer; Cnt: Integer): Integer;
      procedure Get_M_Tables(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Table_Name: string);
      procedure Get_C_Tables(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Table_Name: string; C: Integer);
      procedure Extract_Names(lbx_FB: TStringList; SL: TStringList; SCheck: string);
      function  Get_Master(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Extract_Name: string; EOT: TExtractObjectTypes; Get_Mode: TGet_Mode): Boolean;
      function  Get_Client(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Extract_Name: string; EOT: TExtractObjectTypes; Get_Mode: TGet_Mode; C: Integer): Boolean;
  end;

var
  Form_Comparison: TForm_Comparison;

implementation

{$R *.dfm}

procedure TForm_Comparison.FormCreate(Sender: TObject);
begin
  Total := 0;
  SH := '................................................................................................................'
      + '................................................................................................................';
  SLT := TStringList.Create;
  SL := TStringList.Create;
  sgr_Client_List.Cells[0,0] := 'FireBird DataBases to be Compared';
  sgr_Client_List.Cells[0,1] := 'FB1.FDB';
  sgr_Client_List.Cells[0,2] := 'FB2.FDB';
  sgr_Client_List.Cells[0,3] := 'FB3.FDB';
  SetLength(LIST_C, 0);
  SetLength(LIST_M, 0);
end;

procedure TForm_Comparison.FormDestroy(Sender: TObject);
begin
  IBDB_Master.CloseDataSets;
  IBDB_Master.Connected := False;
  IBDB_Client.CloseDataSets;
  IBDB_Client.Connected := False;
  SetLength(LIST_M, 0);
  SetLength(LIST_C, 0);
  SLT.Free;
  SL.Free;
end;

function  TForm_Comparison.Connect_Database(IBDB: TIBDatabase; DB_Name: string): Boolean;
var
  B:      Boolean;
begin
  B := False;
  try
    try
      if IBDB.Connected then
        IBDB.Connected := False;
      IBDB.DatabaseName := DB_Name;
      IBDB.LoginPrompt := False;
      IBDB.Params.Values['lc_ctype']  := 'WIN1252';
      IBDB.Params.Values['user_name'] := 'SYSDBA';
      IBDB.Params.Values['password']  := 'masterkey';
      IBDB.SQLDialect := 1;

      IBDB.Connected := True;
      B := True;
    except
      B := False;
    end;
  finally
    Result := B;
  end;
end;

function  TForm_Comparison.LBx_M_Add(IBDB: TIBDatabase; IBTR: TIBTransaction; DB_Name, Table_Name: string; Get_Mode: TGet_Mode; Cnt: Integer): Integer;
var
  I:      Integer;
  J:      Integer;
  L:      Integer;
  PJ:     Integer;
begin
  L := -1;
  try
    try
      L := Length(LIST_M);
      SetLength(LIST_M, L+1);
      LIST_M[L].STRLST := TStringList.Create;
      LIST_M[L].STRLST.Add(DB_Name);
      LIST_M[L].STRLST.Add(Table_Name);
      LIST_M[L].Exists := False;
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      if (Get_Mode=gmDetails) then
      begin
        IBExtract.ExtractObject(eoTable, Table_Name, [etTable]);
        SLT.Assign(IBExtract.Items);
        PJ := 0;
        J := 2147483647;
        for I := 0 to SLT.Count-2 do
        begin
          if (PJ=0) then
            PJ := Pos('(', SLT[I])
          else
          if (PJ>0) then
          begin
            J := I;
            PJ := -1;
          end;
          if (I>J) then
          begin
            LIST_M[L].STRLST.Add(Trim(SLT[I]));
          end;
        end;
      end;
    except
      L := -1;
    end;
  finally
    Inc(Total);
    Result := L;
  end;
end;

function  TForm_Comparison.LBx_C_Add(IBDB: TIBDatabase; IBTR: TIBTransaction; DB_Name, Table_Name: string; Get_Mode: TGet_Mode; C: Integer; Cnt: Integer): Integer;
var
  I:      Integer;
  J:      Integer;
  L:      Integer;
  PJ:     Integer;
begin
  L := -1;
  try
    try
      L := Length(LIST_C[C-1]);
      SetLength(LIST_C[C-1], L+1);
      LIST_C[C-1][L].STRLST := TStringList.Create;
      LIST_C[C-1][L].STRLST.Add(DB_Name);
      LIST_C[C-1][L].STRLST.Add(Table_Name);
      LIST_C[C-1][L].Exists := False;
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      IBExtract.ExtractObject(eoTable, Table_Name, [etTable]);
      SLT.Assign(IBExtract.Items);
      PJ := 0;
      J := 2147483647;
      for I := 0 to SLT.Count-2 do
      begin
        if (PJ=0) then
          PJ := Pos('(', SLT[I])
        else
        if (PJ>0) then
        begin
          J := I;
          PJ := -1;
        end;
        if (I>J) then
        begin
          LIST_C[C-1][L].STRLST.Add(Trim(SLT[I]));
        end;
      end;
    except
      L := -1;
    end;
  finally
    Inc(Total);
    Result := L;
  end;
end;

procedure TForm_Comparison.Get_M_Tables(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Table_Name: string);
var
  K:      Integer;
  S:      string;
  lbx_FB: TStringList;
begin
  S := '';
  lbx_FB := TStringList.Create;
  try
    if (Table_Name='') then
    begin
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      IBExtract.ExtractObject(eoTable, Table_Name, [etTable]);
      SL.Assign(IBExtract.Items);
      Extract_Names(lbx_FB, SL, 'CREATE TABLE');
      for K := 0 to lbx_FB.Count-1 do
      begin
        if (LBx_M_Add(IBDB, IBTR, DB_Name, lbx_FB[K], gmDetails, lbx_FB.Count)>=0) then
          Get_M_Tables(IBDB, IBTR, DBMode, DB_Name, lbx_FB[K]);
      end;
    end;
  finally
    lbx_FB.Free;
  end;
end;

procedure TForm_Comparison.Get_C_Tables(IBDB: TIBDatabase; IBTR: TIBTransaction; {lbx_FB: TListBox; mmo_FB: TMemo;} DBMode: TDBMode; DB_Name, Table_Name: string; C: Integer);
var
  K:      Integer;
  S:      string;
  lbx_FB: TStringList;
begin
  S := '';
  lbx_FB := TStringList.Create;
  try
    if (Table_Name='') then
    begin
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      IBExtract.ExtractObject(eoTable, Table_Name, [etTable]);
      SL.Assign(IBExtract.Items);
      Extract_Names(lbx_FB, SL, 'CREATE TABLE');
      for K := 0 to lbx_FB.Count-1 do
      begin
        if (LBx_C_Add(IBDB, IBTR, DB_Name, lbx_FB[K], gmDetails, C, lbx_FB.Count)>=0) then
          Get_C_Tables(IBDB, IBTR, DBMode, DB_Name, lbx_FB[K], C);
      end;
    end;
  finally
    lbx_FB.Free;
  end;
end;

procedure TForm_Comparison.Extract_Names(lbx_FB: TStringList; SL: TStringList; SCheck: string);
var
  I:      Integer;
//  P:      Integer;
  S:      string;
begin
  if (SL.Count>0) then
  begin
    lbx_FB.Clear;
    for I := 0 to SL.Count-1 do
    begin
      if (Pos(SCheck, SL[I])>0) then
      begin
        S := SL[I];
        Delete(S, 1, Length(SCheck)+1);
        {
        P := Pos('(', S);
        if (P>0) then
          Delete(S, P, 1);
        }
        lbx_FB.Add(S);
      end;
    end;
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_TablesClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  CF:     Integer;
  MF:     Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo.Clear;
    if FileExists(SMaster) then
    begin
      Get_M_Tables(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, '');
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo.Lines.Add(SH);
    Memo.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo.Lines.Add('        Table '+LIST_M[M].STRLST[1]);
      for MF := 2 to LIST_M[M].STRLST.Count-1 do
      begin
        Memo.Lines.Add('                Field '+LIST_M[M].STRLST[MF]);
      end;
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo.Lines.Add(SH);
          Memo.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_C_Tables(IBDB_Client, IBTR_Client, dbmClient, SClient, '', K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo.Lines.Add(SH);
            Memo.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo.Lines.Add(SH);
            Memo.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for MF := 2 to LIST_M[M].STRLST.Count-1 do
                Rel_M[MF] := -1;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                  for CF := 2 to LIST_C[K-1][C].STRLST.Count-1 do
                  begin
                    for MF := 2 to LIST_M[M].STRLST.Count-1 do
                      if (LIST_C[K-1][C].STRLST[CF]=LIST_M[M].STRLST[MF]) then
                        Rel_M[MF] := 0;
                  end;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo.Lines.Add('        Table '+LIST_M[M].STRLST[1]+T);
              if LIST_M[M].Exists then
              for MF := 2 to LIST_M[M].STRLST.Count-1 do
              begin
                if (Rel_M[MF]=-1) then
                  T := ' is missing in the ' + SClient
                else
                  T := ' exists in the ' + SClient;
                Memo.Lines.Add('                Field '+LIST_M[M].STRLST[MF]+T);
              end;
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra Table in the ' + SClient;
                Memo.Lines.Add('        Table '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

function  TForm_Comparison.Get_Master(IBDB: TIBDatabase; IBTR: TIBTransaction; DBMode: TDBMode; DB_Name, Extract_Name: string; EOT: TExtractObjectTypes; Get_Mode: TGet_Mode): Boolean;
var
  I:      Integer;
  K:      Integer;
  S:      string;
  lbx_FB: TStringList;
begin
  I := 32;
  try
    S := '';
    lbx_FB := TStringList.Create;
    try
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      IBExtract.ExtractObject(EOT, '', []);
      SL.Assign(IBExtract.Items);
      Extract_Names(lbx_FB, SL, Extract_Name);
      for K := 0 to lbx_FB.Count-1 do
      begin
        I := 0;
        if not (LBx_M_Add(IBDB, IBTR, DB_Name, lbx_FB[K], gmItems, lbx_FB.Count)>=0) then
          Inc(I);
      end;
    finally
      lbx_FB.Free;
    end;
  finally
    Result := (I=0);
  end;
end;

function  TForm_Comparison.Get_Client(IBDB: TIBDatabase; IBTR: TIBTransaction; {lbx_FB: TListBox; mmo_FB: TMemo;} DBMode: TDBMode; DB_Name, Extract_Name: string; EOT: TExtractObjectTypes; Get_Mode: TGet_Mode; C: Integer): Boolean;
var
  I:      Integer;
  K:      Integer;
  S:      string;
  lbx_FB: TStringList;
begin
  I := 32;
  try
    S := '';
    lbx_FB := TStringList.Create;
    try
      IBExtract.Database := IBDB;
      IBExtract.Transaction := IBTR;
      IBExtract.ExtractObject(EOT, '', []);
      SL.Assign(IBExtract.Items);
      Extract_Names(lbx_FB, SL, Extract_Name);
      I := 0;
      for K := 0 to lbx_FB.Count-1 do
      begin
        if not (LBx_C_Add(IBDB, IBTR, DB_Name, lbx_FB[K], gmItems, C, lbx_FB.Count)>=0) then
          Inc(I);
      end;
    finally
      lbx_FB.Free;
    end;
  finally
    Result := (I=0);
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_ProceduresClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo_Procedures.Clear;
    if FileExists(SMaster) then
    begin
      Get_Master(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, 'CREATE PROCEDURE', eoProcedure, gmItems);
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo_Procedures.Lines.Add(SH);
    Memo_Procedures.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo_Procedures.Lines.Add('        Procedure '+LIST_M[M].STRLST[1]);
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo_Procedures.Lines.Add(SH);
          Memo_Procedures.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_Client(IBDB_Client, IBTR_Client, dbmClient, SClient, 'CREATE PROCEDURE', eoProcedure, gmItems, K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo_Procedures.Lines.Add(SH);
            Memo_Procedures.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo_Procedures.Lines.Add(SH);
            Memo_Procedures.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo_Procedures.Lines.Add('        Procedure '+LIST_M[M].STRLST[1]+T);
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra Procedure in the ' + SClient;
                Memo_Procedures.Lines.Add('        Procedure '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo_Procedures.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_ViewsClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo_Views.Clear;
    if FileExists(SMaster) then
    begin
      Get_Master(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, 'CREATE VIEW', eoView, gmItems);
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo_Views.Lines.Add(SH);
    Memo_Views.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo_Views.Lines.Add('        View '+LIST_M[M].STRLST[1]);
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo_Views.Lines.Add(SH);
          Memo_Views.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_Client(IBDB_Client, IBTR_Client, dbmClient, SClient, 'CREATE VIEW', eoView, gmItems, K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo_Views.Lines.Add(SH);
            Memo_Views.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo_Views.Lines.Add(SH);
            Memo_Views.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo_Views.Lines.Add('        View '+LIST_M[M].STRLST[1]+T);
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra View in the ' + SClient;
                Memo_Views.Lines.Add('        View '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo_Views.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_GeneratorsClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo_Generators.Clear;
    if FileExists(SMaster) then
    begin
      Get_Master(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, 'CREATE GENERATOR', eoGenerator, gmItems);
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo_Generators.Lines.Add(SH);
    Memo_Generators.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo_Generators.Lines.Add('        Generator '+LIST_M[M].STRLST[1]);
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo_Generators.Lines.Add(SH);
          Memo_Generators.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_Client(IBDB_Client, IBTR_Client, dbmClient, SClient, 'CREATE GENERATOR', eoGenerator, gmItems, K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo_Generators.Lines.Add(SH);
            Memo_Generators.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo_Generators.Lines.Add(SH);
            Memo_Generators.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo_Generators.Lines.Add('        Generator '+LIST_M[M].STRLST[1]+T);
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra Generator in the ' + SClient;
                Memo_Generators.Lines.Add('        Generator '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo_Generators.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_TriggersClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo_Triggers.Clear;
    if FileExists(SMaster) then
    begin
      Get_Master(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, 'CREATE TRIGGER', eoTrigger, gmItems);
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo_Triggers.Lines.Add(SH);
    Memo_Triggers.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo_Triggers.Lines.Add('        Trigger '+LIST_M[M].STRLST[1]);
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo_Triggers.Lines.Add(SH);
          Memo_Triggers.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_Client(IBDB_Client, IBTR_Client, dbmClient, SClient, 'CREATE TRIGGER', eoTrigger, gmItems, K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo_Triggers.Lines.Add(SH);
            Memo_Triggers.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo_Triggers.Lines.Add(SH);
            Memo_Triggers.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo_Triggers.Lines.Add('        Trigger '+LIST_M[M].STRLST[1]+T);
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra Trigger in the ' + SClient;
                Memo_Triggers.Lines.Add('        Trigger '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo_Triggers.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

procedure TForm_Comparison.bbt_Do_Comparison_DomainsClick(Sender: TObject);
var
  C:      Integer;
  IC:     Integer;
  K:      Integer;
  M:      Integer;
  LC:     Integer;
  CSize:  Integer;
  MSize:  Integer;
  MaxM:   Integer;
  Rel_M:  array of Shortint;
  Rel_C:  array of Shortint;
  SClient:string;
  SMaster:string;
  T:      string;
begin
  Total := 0;
  SMaster := Edit_Master.Text;
  if Connect_Database(IBDB_Master, SMaster) then
  try
    IC := ComponentCount;
    for K := IC-1 downto 0 do
    begin
      if (Components[K].ClassType=TListBox) then
      begin
        (Components[K] as TListBox).Free;
      end;
    end;
    SetLength(LIST_C, 0);
    SetLength(LIST_M, 0);
    Memo_Domains.Clear;
    if FileExists(SMaster) then
    begin
      Get_Master(IBDB_Master, IBTR_Master, dbmMaster, Edit_Master.Text, 'CREATE DOMAIN', eoDomain, gmItems);
      MSize := Length(LIST_M);
      if (MSize>0) then
      begin
        MaxM := LIST_M[0].STRLST.Count;
        for M := 1 to Length(LIST_M)-1 do
          if (MaxM<LIST_M[M].STRLST.Count) then
            MaxM := LIST_M[M].STRLST.Count;
          SetLength(Rel_M, MaxM);
      end;
    end;
    MSize := Length(LIST_M);
    Memo_Domains.Lines.Add(SH);
    Memo_Domains.Lines.Add('Master DB '+SMaster);
    for M := 0 to MSize-1 do
    begin
      Memo_Domains.Lines.Add('        Domain '+LIST_M[M].STRLST[1]);
    end;

    for K := 1 to sgr_Client_List.RowCount-1 do
    begin
      SClient := Trim(sgr_Client_List.Cells[0,K]);
      if (SClient<>'') then
      begin
        if not FileExists(SClient) then
        begin
          Memo_Domains.Lines.Add(SH);
          Memo_Domains.Lines.Add('Database '+SClient+' does not exist');
        end
        else
        begin
          if Connect_Database(IBDB_Client, SClient) then
          begin
            LC := Length(LIST_C);
            SetLength(LIST_C, LC+1);
            Get_Client(IBDB_Client, IBTR_Client, dbmClient, SClient, 'CREATE DOMAIN', eoDomain, gmItems, K);
          end;
          if (Length(LIST_C)=0) then
          begin
            Memo_Domains.Lines.Add(SH);
            Memo_Domains.Lines.Add('Database '+SClient+' does not contain tables');
          end
          else
          begin
            Memo_Domains.Lines.Add(SH);
            Memo_Domains.Lines.Add('Database '+SClient);

            CSize := Length(LIST_C[K-1]);

            for M := 0 to MSize-1 do
            begin
              LIST_M[M].Exists := False;
              for C := 0 to CSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                begin
                  LIST_M[M].Exists := True;
                end;
              end;
            end;
            for M := 0 to MSize-1 do
            begin
              if not LIST_M[M].Exists then
                T := ' is missing in the ' + SClient
              else
              begin
                T := ' exists in the ' + SClient;
              end;
              Memo_Domains.Lines.Add('        Domain '+LIST_M[M].STRLST[1]+T);
            end;

            for C := 0 to CSize-1 do
            begin
              LIST_C[K-1][C].Exists := False;
              for M := 0 to MSize-1 do
              begin
                if (LIST_C[K-1][C].STRLST[1]=LIST_M[M].STRLST[1]) then
                  LIST_C[K-1][C].Exists := True;
              end;
            end;
            for C := 0 to CSize-1 do
            begin
              if not LIST_C[K-1][C].Exists then
              begin
                T := ' is an extra Trigger in the ' + SClient;
                Memo_Domains.Lines.Add('        Domain '+LIST_C[K-1][C].STRLST[1]+T);
              end
              else
                T := '';
            end;

          end;
        end;
      end;
    end;
    Memo_Domains.Lines.Add(SH);
  finally
    SetLength(Rel_C, 0);
    SetLength(Rel_M, 0);
  end;
end;

end.
0
 
TheRealLokiSenior DeveloperAuthor Commented:
your extra points can be picked up by adding a comment to
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21416823.html
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now