(*
   fenixsql
   author Alessandro Batisti
   abatisti@tiscali.it
   http://fblib.altervista.org
   
   contributor Anton Baranov
   anton.baranov@red-soft.biz
   http://sourceforge.net/projects/rdbman/

   file: fsbrowser.pas


  * This program 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.
  *
  * This program 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.

*)

unit rdbmain;

interface

uses
  Windows, SysUtils, Classes, Controls, ExtCtrls, Forms,
  Dialogs, ActnList, ImgList, Menus, ComCtrls, ToolWin,
  SynEditHighlighter, SynHighlighterSQL, SynEdit,
  DB, Grids, DBGrids, DBClient,
  ZConnection, ZDataset, ZDbcIntfs,
  ZAbstractDataset, ZAbstractTable, ZAbstractRODataset, ZAbstractConnection,
  fsdbconnections, FsConfig, fieldinfo, IBExtract, IBDatabase,
  Midaslib;

type
  TRpIBXProtocol = (rpIBXTCP, rpIBXNetBeui, rpIBXIpx, rpIBXLocal);

  TColDesc = record
    intType, Subtype: smallint;
  end;
  TNodeDesc = class         //store information for each node
  private
    FNodeType: Integer;
    FObjName: string;
    FObjDesc: string;
  public
    constructor Create(ANodeType: Integer; AObjName, AObjDesc: string);
    property NodeType: Integer read FNodeType write FNodeType;
    property ObjName: string read FObjName write FObjName;
    property ObjDesc: string read FObjDesc write FObjDesc;
  end;

  TMainForm = class(TForm)
    MainMenu1: TMainMenu;
    File1: TMenuItem;
    Open1: TMenuItem;
    Close1: TMenuItem;
    Exit1: TMenuItem;
    Help1: TMenuItem;
    ActionList1: TActionList;
    ImageList1: TImageList;
    ConnectAction: TAction;
    ExctractMetadata1: TMenuItem;
    MakeBackup1: TMenuItem;
    CreateDatabase1: TMenuItem;
    N1: TMenuItem;
    Exit2: TMenuItem;
    Environment1: TMenuItem;
    Isql1: TMenuItem;
    N2: TMenuItem;
    Servicemanager1: TMenuItem;
    Usermanager1: TMenuItem;
    Options1: TMenuItem;
    FontColor1: TMenuItem;
    Newscript1: TMenuItem;
    Openscript1: TMenuItem;
    Savescript1: TMenuItem;
    N3: TMenuItem;
    ExecuteSQLstatement1: TMenuItem;
    ExecuteSQLScript1: TMenuItem;
    N4: TMenuItem;
    Commit1: TMenuItem;
    RollBack1: TMenuItem;
    N5: TMenuItem;
    Previous1: TMenuItem;
    Next1: TMenuItem;
    ClearHistory1: TMenuItem;
    About1: TMenuItem;
    ToolBar1: TToolBar;
    ConnectToolButton: TToolButton;
    DisConnectToolButton: TToolButton;
    ToolButton3: TToolButton;
    RefreshToolButton: TToolButton;
    ToolButton5: TToolButton;
    ZConnection1: TZConnection;
    MainTreeView: TTreeView;
    Splitter1: TSplitter;
    TablePageControl: TPageControl;
    TableDataTabSheet: TTabSheet;
    StatusBar1: TStatusBar;
    TableDataDBGrid: TDBGrid;
    ZTableDataSource: TDataSource;
    ZTable1: TZTable;
    TableFieldTabSheet: TTabSheet;
    FieldDataSource: TDataSource;
    FieldsClientDataSet: TClientDataSet;
    TableFieldDBGrid: TDBGrid;
    FieldsClientDataSetCOLUMN_NAME: TStringField;
    FieldsClientDataSetTABLE_CAT: TStringField;
    FieldsClientDataSetTABLE_SCHEM: TStringField;
    FieldsClientDataSetTABLE_NAME: TStringField;
    FieldsClientDataSetDATA_TYPE: TStringField;
    FieldsClientDataSetTYPE_NAME: TStringField;
    FieldsClientDataSetCOLUMN_SIZE: TStringField;
    FieldsClientDataSetBUFFER_LENGTH: TStringField;
    FieldsClientDataSetDECIMAL_DIGITS: TStringField;
    FieldsClientDataSetNUM_PREC_RADIX: TStringField;
    FieldsClientDataSetNULLABLE: TStringField;
    FieldsClientDataSetREMARKS: TStringField;
    FieldsClientDataSetCOLUMN_DEF: TStringField;
    FieldsClientDataSetSQL_DATA_TYPE: TStringField;
    FieldsClientDataSetSQL_DATETIME_SUB: TStringField;
    FieldsClientDataSetCHAR_OCTET_LENGTH: TStringField;
    FieldsClientDataSetORDINAL_POSITION: TStringField;
    FieldsClientDataSetIS_NULLABLE: TStringField;
    FieldsClientDataSetAUTO_INCREMENT: TStringField;
    FieldsClientDataSetCASE_SENSITIVE: TStringField;
    FieldsClientDataSetSEARCHABLE: TStringField;
    FieldsClientDataSetWRITABLE: TStringField;
    FieldsClientDataSetDEFINITELYWRITABLE: TStringField;
    FieldsClientDataSetREADONLY: TStringField;
    DisconnectAction: TAction;
    FieldsClientDataSetDATA_SUBTYPE: TStringField;
    DDLTabSheet: TTabSheet;
    DDLSynEdit: TSynEdit;
    DDLSynSQLSyn: TSynSQLSyn;
    FieldsClientDataSetTYPE_ORIGINNAME: TStringField;
    FieldsClientDataSetLOWER_BOUND: TStringField;
    FieldsClientDataSetUPPER_BOUND: TStringField;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure TreeViewEvents(ANode: TTreeNode);
    procedure TablePageControlChange(Sender: TObject);
    procedure MainTreeViewChange(Sender: TObject; Node: TTreeNode);
    procedure aDbConnectionsExecute(Sender: TObject);
    procedure DisconnectActionExecute(Sender: TObject);
  private
    FParamValue: array of string;
    FColDesc: array of TColDesc;
    FHistory: TStringList;
    FHistoryIdx: Integer;
    FCurrentAlias: string;
    {Database Objects List}
    FDomainList: TStringList;
    FTableList: TStringList;
    FViewList: TStringList;
    FProcList: TStringList;
    FTriggerList: TStringList;
    FGeneratorList: TStringList;
    FUdfList: TStringList;
    FExceptionList: TStringList;
    FRoleList: TStringList;
    FSysTableList: TStringList;
    FSysTriggerList: TStringList;
    FExecutedDDLStm: Boolean;
  public
    MetaData: IZDatabaseMetadata;
    procedure ClearTvDB;
    procedure LoadTvDb;
    procedure LoadLists;
    procedure LoadTableData(table: string);
    procedure LoadTableFields(table: string);
    procedure LoadTableDDL(table: string; NP: TRpIBXProtocol);
  end;

var
  MainForm: TMainForm;

implementation

{$R *.dfm}

//uses
// IBDatabase, IBExtract;
const
  (*  tree view node name  *)
  CDOMAINS = 'Domains';
  CTABLES = 'Tables';
  CVIEWS = 'Views';
  CPROCEDURES = 'Procedures';
  CTRIGGERS = 'Triggers';
  CGENERATORS = ' Generators';
  CUDFS = 'UDFs';
  CEXCEPTIONS = 'Exceptions';
  CROLES = 'Roles';
  CSYSTEMTABLES = 'System Tables';
  CSYSTEMTRIGGERS = 'System Triggers';
  CDEP = 'Dependencies';
  CPRIV = 'Privileges';

  (* tree view item type *)
  CNT_DATABASE =   0;
  CNT_DOMAINS =    1;
  CNT_DOMAIN =     2;
  CNT_TABLES =     3;
  CNT_TABLE =      4;
  CNT_VIEWS =      5;
  CNT_VIEW =       6;
  CNT_PROCEDURES = 7;
  CNT_PROCEDURE =  8;
  CNT_TRIGGERS =   9;
  CNT_TRIGGER =    10;
  CNT_GENERATORS = 11;
  CNT_GENERATOR =  12;
  CNT_UDFS =       13;
  CNT_UDF =        14;
  CNT_EXCEPTIONS = 15;
  CNT_EXCEPTION =  16;
  CNT_ROLES =           17;
  CNT_ROLE =            18;
  CNT_DEP =             19;
  CNT_PRIV =            20;
  CNT_SYSTABLES =       21;
  CNT_SYSTABLE  =       22;
  CNT_SYSTRIGGERS =     23;
  CNT_SYSTRIGGER =      24;
  CNT_TRIGGERSINTABLE = 25;

  (* Tree Views Bitmaps *)
  BMP_NONE = -1;
  BMP_DOMAIN = 0;
  BMP_TABLE = 1;
  BMP_VIEW = 2;
  BMP_PROCEDURE = 3;
  BMP_TRIGGER = 4;
  BMP_GENERATOR = 5;
  BMP_UDF = 6;
  BMP_SYSTEMTABLE = 8;
  BMP_DEP = 10;
  BMP_EXCEPTION = 13;
  BMP_ROLE = 14;
  BMP_SYSTEMTRIGGER = 15;
  BMP_DATABASE = 16;
  BMP_PRIV = 17;
  {$IFDEF UNIX}
  ROW_H = 5;
  {$ELSE}
  ROW_H = 2;
  {$ENDIF}

  DEFAULT_HEIGHT = 500;
  DEFAULT_WIDTH = 640;

{ TNodeDesc }

//------------------------------------------------------------------------------

constructor TNodeDesc.Create(ANodeType: Integer; AObjName, AObjDesc: string);
begin
  FNodeType := ANodeType;
  FObjName := AObjName;
  FObjDesc := AObjDesc;
end;

{ TMainForm }

//------------------------------------------------------------------------------

procedure TMainForm.LoadLists;
begin
  ZConnection1.GetTableNames('TABLE', FTableList);
end;

//------------------------------------------------------------------------------

procedure TMainForm.ClearTvDB;
var
  i: integer;
begin
  MainTreeView.Items.BeginUpdate;
  try
      for i := 0 to MainTreeView.Items.Count -1 do
        if Assigned(MainTreeView.Items[i].Data) then
        begin
          TNodeDesc(MainTreeView.Items[i].Data).Free;
          MainTreeView.Items[i].Data := nil;
        end;

      MainTreeView.Items.Clear;
  finally
    MainTreeView.Items.EndUpdate;
  end;
end;

//------------------------------------------------------------------------------

procedure TMainForm.LoadTvDb;
var
  nDb, nObj, nItem, nDepend, nPriv, nTrigTable, nTrigTableItem: TTreeNode;
  i, it: integer;
begin
  try
    Screen.Cursor := crSQLWait;

    LoadLists;
    ClearTvDb;

    MainTreeView.Items.BeginUpdate;

    nDb := MainTreeView.Items.Add(nil, FCurrentAlias);
    nDb.Data := TNodeDesc.Create(CNT_DATABASE, '', 'Database alias::' + FCurrentAlias);
    ndb.SelectedIndex := BMP_DATABASE;
    nDb.ImageIndex := BMP_DATABASE;

    // Add Tables
    nObj := MainTreeView.Items.AddChild(nDb, Format(CTABLES + ' (%d)', [FTableList.Count]));
    nObj.SelectedIndex := BMP_TABLE;
    nObj.ImageIndex := BMP_TABLE;
    nObj.Data := TNodeDesc.Create(CNT_TABLES, '', 'TABLES');

    for i := 0 to FTableList.Count - 1 do
    begin
      nItem := MainTreeView.Items.AddChild(nObj, FTableList.Strings[i]);
      nItem.ImageIndex := BMP_TABLE;
      nItem.SelectedIndex := BMP_TABLE;
      nItem.Data := TNodeDesc.Create(CNT_TABLE, FTableList.Strings[i],
        'TABLE::' + FTableList.Strings[i]);
    end;

  finally
    Screen.Cursor := crDefault;
    MainTreeView.Items.EndUpdate;
  end;
end;

//------------------------------------------------------------------------------

procedure TMainForm.LoadTableData(table: string);
begin
  TableDataDBGrid.enabled := False;
  ZTable1.Active := False;

  ZTable1.TableName := table;

  ZTable1.Active := True;
  TableDataDBGrid.enabled := True;
end;

//------------------------------------------------------------------------------

procedure TMainForm.LoadTableFields(table: string);
begin
  TableFieldDBGrid.enabled := False;
  FieldsClientDataSet.Active := False;
  FieldsClientDataSet.Close;
  FieldsClientDataSet.CreateDataSet;
  FieldsClientDataSet.DisableControls;

  Filldatasetwithfieldsinfo(ZConnection1, True, FieldsClientDataSet, table, '');

  FieldsClientDataSet.EnableControls;
  FieldsClientDataSet.Active := True;
  TableFieldDBGrid.enabled := True;
end;

//------------------------------------------------------------------------------

procedure TMainForm.LoadTableDDL(table: string; NP: TRpIBXProtocol);
var
  dbase: TIBDatabase;
  Tran1: TIBTransaction;
  ibextract: TIBExtract;
begin
  dbase := nil;
  try
    dbase := TIBDatabase.Create(nil);
    ibextract := TIBExtract.Create(nil);
    Tran1 := TIBTransaction.create(nil);
    //dbase := IBDatabase1;
    //ibextract := IBExtract1;

    with dbase do begin
      try
        LoginPrompt := false;
        Params.Clear;
        Params.Add(Format('user_name=%s',[ZConnection1.User]));
        Params.Add(Format('password=%s', [ZConnection1.Password]));
        case NP of
          rpIBXTCP:     DatabaseName := Format('%s:%s',  [ZConnection1.HostName, ZConnection1.Database]);
          rpIBXNetBeui: DatabaseName := Format('\\%s\%s',[ZConnection1.HostName, ZConnection1.Database]);
          rpIBXIpx:     DatabaseName := Format('%s@%s',  [ZConnection1.HostName, ZConnection1.Database]);
          else          DatabaseName := ZConnection1.Database;
        end;
        Connected := true;

        DefaultTransaction := Tran1;
        Tran1.DefaultDatabase := dbase;

        ibextract.Database := dbase;
        ibextract.ExtractObject(eoTable, table);

        DDLSynEdit.Text := ibextract.Items.Text;
      except
        on E: Exception do
          ShowMessage(E.Message);
      end;
    end;
  finally
    Tran1.Active := False;
    if dbase.Connected then
      dbase.Connected := false;
    ibextract.Free;
    dbase.free;
    tran1.free;
  end;
end;

//------------------------------------------------------------------------------

procedure TMainForm.TreeViewEvents(ANode: TTreeNode);
var
  temp: string;
  obj_Name: string;
begin
  if not ZConnection1.Connected then
    exit;
  if (ANode = nil) or (ANode.Data = nil) then
    exit;

  Caption := TNodeDesc(ANode.Data).ObjDesc;
  obj_name := TNodeDesc(ANode.Data).ObjName;

  case TNodeDesc(ANode.Data).Nodetype of
    CNT_DATABASE:
      begin
      end;
    CNT_DOMAINS:
      begin
      end;
    CNT_DOMAIN:
      begin
      end;
    CNT_TABLES:
      begin
      end;
    CNT_TABLE,CNT_SYSTABLE:
      begin
        if TablePageControl.ActivePage = TableDataTabSheet then
        begin
          LoadTableData(ANode.Text);
          exit;
        end;
        if TablePageControl.ActivePage = TableFieldTabSheet then
        begin
          LoadTableFields(ANode.Text);
          exit;
        end;
        if TablePageControl.ActivePage = DDLTabSheet then
        begin
          LoadTableDDL(ANode.Text, rpIBXLocal);
          exit;
        end;
      end;
    CNT_VIEWS:
      begin
      end;
    CNT_VIEW:
      begin
      end;
    CNT_PROCEDURES:
      begin
      end;
    CNT_PROCEDURE:
      begin
      end;
    CNT_TRIGGERS:
      begin
      end;
    CNT_TRIGGER,CNT_SYSTRIGGER:
      begin
      end;
    CNT_GENERATORS:
      begin
      end;
    CNT_GENERATOR:
      begin
      end;
    CNT_UDFS:
      begin
      end;
    CNT_UDF:
      begin
      end;
    CNT_EXCEPTIONS:
      begin
      end;
    CNT_EXCEPTION:
      begin
      end;
    CNT_ROLES:
      begin
      end;
    CNT_ROLE:
      begin
      end;
    CNT_PRIV:
      begin
      end;
    CNT_DEP:
      begin
      end;
    CNT_SYSTABLES:
      begin
      end;
    CNT_SYSTRIGGERS:
      begin
      end;
    CNT_TRIGGERSINTABLE:
      begin
      end;
    else  // else case
  end; // end case
end;

//------------------------------------------------------------------------------

procedure TMainForm.aDbConnectionsExecute(Sender: TObject);
var
  Alias: string;
begin
  if ZConnection1.Connected then
    exit;

  Alias := FCurrentAlias;
  if not DbConnection(ZConnection1, Alias) then Exit;

  try
    FCurrentAlias := Alias;
    LoadtvDb;
    fsconfig.last_alias_connected := Alias;
    fsConfig.WriteConfigFile;
  except
     on E: Exception do
        ShowMessage(E.Message);
  end;
end;

//------------------------------------------------------------------------------

procedure TMainForm.DisconnectActionExecute(Sender: TObject);
begin
  if not ZConnection1.Connected then
    exit;

  FieldsClientDataSet.Active := False;
  FieldsClientDataSet.Close;
  TableFieldDBGrid.enabled := False;

  ZTable1.Active := False;
  TableDataDBGrid.enabled := False;

  DDLSynEdit.Clear;

  ClearTvDb;

  ZConnection1.Disconnect;
end;

//------------------------------------------------------------------------------

procedure TMainForm.MainTreeViewChange(Sender: TObject; Node: TTreeNode);
begin
  TreeViewEvents(Node);
end;

//------------------------------------------------------------------------------

procedure TMainForm.TablePageControlChange(Sender: TObject);
begin
  TreeViewEvents(MainTreeView.Selected);
end;

//------------------------------------------------------------------------------

procedure TMainForm.FormCreate(Sender: TObject);
begin
  FHIstory := TStringList.Create;
  FDomainList := TStringList.Create;
  FTableList := TStringList.Create;
  FViewList := TStringList.Create;
  FProcList := TStringList.Create;
  FTriggerList := TStringList.Create;
  FGeneratorList := TStringList.Create;
  FUdfList := TStringList.Create;
  FExceptionList := TStringList.Create;
  FRoleList := TStringList.Create;
  FSysTableList := TStringList.Create;
  FSysTriggerList := TStringList.Create;

  Caption := APP_TITLE + ' ' + APP_VERSION;

  InitFileConfig;
  //ReadAliasNames;
  ReadConfigFile;

  Self.Top := (Screen.Height - DEFAULT_HEIGHT) div 2;
  Self.Left := (Screen.Width - DEFAULT_WIDTH) div 2;
  Self.Height := DEFAULT_HEIGHT;
  Self.Width := DEFAULT_WIDTH;

  FsConfig.LoadFormPos(Self);

end;

//------------------------------------------------------------------------------

procedure TMainForm.FormDestroy(Sender: TObject);
begin
  ZConnection1.Connected := False;

  FHistory.Free;
  FDomainList.Free;
  FTableList.Free;
  FViewList.Free;
  FProcList.Free;
  FTriggerList.Free;
  FGeneratorList.Free;
  FUdfList.Free;
  FExceptionList.Free;
  FRoleList.Free;
  FSysTableList.Free;
  FSysTriggerList.Free;

end;

end.
