dmsql.pas

是一个delphi的流程制作软件

unit dmSQL;
{DISCLAIMER
--------------------
This source code is the property of Core Software.  You may use and distrubute
this source code at your own risk.  Warantee as to the to the completeness,
robustness, timliness, or any issue relevant to this source code whatsoever is
not expressed or implied in any way.

FREE INFORMATION
--------------------
Most companies disallow any religious content to be published in articles or
submitted material which is made public.  This is illegal.  I have a constitutional
right to practice my religion, which includes bringing the news of Jesus Christ
to the world.  It is not my intention to invade anyone's privacy, insult, or offend
any person - religious or not.

Jesus Loves You!


Core Software
CTO, Jason 'Wedge' Perry
534 Denver Ave
Chesapeake, VA 23322
[e-mail]
AOL Instant Messager : GuiOOP
ICQ Pager Address : 37953032
}

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBTables, Db, ComCtrls, stdCtrls, Menus, IniFiles, ComObj, SQLDMO_TLB,
  ImgList;

{
Possible drag-and-drop layouts.                      DEFINITION
}
type
  TdadLayout = (dad_Field,                           {Field,                   }
                dad_Alias,                           {Alias                    }
                dad_SP_Alias,                        {spAlias$                 }
                dad_SP_Alias_Get,                    {spAlias$get              }
                dad_SP_Alias_Ins,                    {spAlias$ins              }
                dad_SP_Alias_Upd,                    {spAlias$upd              }
                dad_SP_Alias_Del,                    {spAlias$del              }
                dad_aField,                          {@Field                   }
                dad_aField2,                         {@Field,                  }
                dad_Alias_Field,                     {Alias.Field,             }
                dad_aField_Equals_Field,             {@Field = Field,          }
                dad_aField_Datatype,                 {@Field DataType,         }
                dad_Field_Equals_aField_and,         {Field = @Field and,      }
                dad_Field_Equals_aField,             {Field = @Field,          }
                dad_Alias_Field_Equals_aField_and);  {Alias.Field = @Field and,}

type
  PdmoObject = ^TdmoObject;
  TdmoObject = record
    SQL_DMO    : _SQLServer;
    SQL_DB     : _Database;
    SQL_OBJ    : oleVariant;
    lConnected : boolean;
  end;

type
  Tbo_SQL = class(TDataModule)
    ds_Data: TDataSource;
    q_Data: TTable;
    imgObjects: TImageList;
    db_Objects: TDatabase;
  private
    procedure ParseTemplate(oTreeNode: TTreeNode; oStrings : TStrings; sText : string; dadLayout : TdadLayout);
    procedure FormatTemplateText(oTreeNode : TTreeNode; oStrings : TStrings; dadLayout : TDadLayout);
  public
    SQLPath : string;  // Default SQL script save path.
    procedure Connect(oTreeNode : TTreeNode);
    procedure ManageTreeView(oTreeView : TTreeView);
    procedure RefreshBranch(oTreeView : TTreeView);
    function Execute(oSourceMemo, oResultMemo : TMemo; oTreeView : TTreeView; lClearResultsFirst : boolean; sDelimiter : string; lExecute : boolean) : boolean;

    procedure LoadServers(oTreeView : TTreeView);
    procedure LoadDatabases(oTreeNode : TTreeNode);
    procedure LoadObjects(oTreeNode : TTreeNode);
    procedure LoadTables(oTreeNode : TTreeNode);
    procedure LoadSystemTables(oTreeNode : TTreeNode);
    procedure LoadViews(oTreeNode : TTreeNode);
    procedure LoadStoredProcedures(oTreeNode : TTreeNode);
    procedure LoadTriggers(oTreeNode : TTreeNode);
    procedure GetData(oTreeNode : TTreeNode);
    procedure GetDetails(oTreeNode : TTreeNode; oListView : TListView);
    procedure FormatDADLayout(Source : TObject; dadLayout : TdadLayout; oMemo : TMemo);
    procedure SaveMemoToFile(sMemoText : string);
    procedure LoadMemoFromFile(oMemo : TMemo);
    procedure LoadMacros(oListView : TListView);
    procedure GetSQLScriptText(oMemo : TMemo; oTreeNode : TTreeNode);

    procedure BuildFromTemplate(oTreeNode: TTreeNode; oStrings : TStrings; dadLayout : TDadLayout);
  end;

var
  bo_SQL: Tbo_SQL;

implementation

{$R *.DFM}

{
Entry point to filling a new treeview component.  All treeview
actions (like OnChange) should be passed to this method.
}
procedure Tbo_SQL.ManageTreeView(oTreeView : TTreeView);
begin
  // Prevent any nil nodes from running this.
  if oTreeView.selected = nil then exit;

  case oTreeView.selected.level of
  0: begin
       bo_SQL.Connect(oTreeView.selected);
       if oTreeView.selected.Count = 0 then LoadDatabases(oTreeView.Selected)
     end;
  2: if oTreeView.selected.count = 0 then LoadObjects(oTreeView.selected);
  end;
end;

{
Method to refresh the selected branch on the treeview.
}
procedure Tbo_SQL.RefreshBranch(oTreeView : TTreeView);
begin
  if oTreeView.selected <> nil then begin
    case oTreeView.selected.level of
    0: LoadDatabases(oTreeView.Selected);
    2: LoadObjects(oTreeView.selected);
    3: LoadObjects(oTreeView.selected.parent);
    end;
  end;
end;

{
Dynamically determines the available servers at a workstation.

A treeview component is passed in and it load it up.
}
procedure Tbo_SQL.LoadServers(oTreeView : TTreeView);
var
  lcv : integer;
  oAliasList : TStrings;
  dmoObject : PdmoObject;
begin
  oAliasList := TStringList.Create;
  try
    // Since servers are being determined, will clear and reload the treeview.
    oTreeView.Items.clear;
    Session.GetAliasNames(oAliasList);

    // Spin thru and fill aliases.
    for lcv := 0 to oAliasList.Count - 1 do begin
      if Session.GetAliasDriverName(oAliasList[lcv]) <> 'SQL Server' then continue
      else begin
        new(dmoObject);
        dmoObject.SQL_DMO := CoSQLServer.Create;//createOLEObject('SQLDMO.SQLServer');
        // Name it.
        dmoObject.SQL_DMO.Name := '.';//oAliasList[lcv];
        // Use NT Authentication (as opposed to SQL Server Authentication).
        dmoObject.SQL_DMO.LoginSecure := true;
        // Set a reasonable timeout.
        dmoObject.SQL_DMO.LoginTimeout := 3;
        // Autoreconnect if connection is lost.
        dmoObject.SQL_DMO.AutoReconnect := true;
        // Assign application name so server knows who i am.
        dmoObject.SQL_DMO.ApplicationName := 'SQL Script Builder';
        // Not connected yet.
        dmoObject.lConnected := false;
        // Login. (Uncomment if not loginSecure.
        //dmoObject.SQL_DMO.Login := 'sa';
        // Password.
        //dmoObject.SQL_DMO.Password := 'sa';
        // Add the object (not connected yet).
        oTreeView.Items.AddObject(oTreeView.Selected, oAliasList[lcv], dmoObject).StateIndex := 1;
      end;
    end;
  finally
    oAliasList.free;
  end;
end;

{
Method to connect to a server.
}
procedure Tbo_SQL.Connect(oTreeNode : TTreeNode);
var
  dmo : _SQLServer;
begin
  if oTreeNode.Level = 0 then begin
    // If the connection hasn't been made yet, make it.
    dmo := PdmoObject(oTreeNode.data)^.SQL_DMO;
    if not PdmoObject(oTreeNode.data)^.lConnected then begin
      dmo.Connect(dmo.name, dmo.Login, dmo.Password);
    end;

    // Test the connection.
    if not dmo.VerifyConnection(SQLDMOConn_ReconnectIfDead) then begin
      raise Exception.Create('An error has occurred while attempting to' + #10#13 +
                             'connect to the SQL OLE Server.' + #10#13#10#13 +
                             'Be sure to load the SQL Server 7.x tools' + #10#13 +
                             'before attempting to use SQL Script Builder.' + #10#13 +
                             '(msg:dmSQL.pas/SetSQLObjectsDatabase)');
    end else begin
      // Set the connection flag.
      PdmoObject(oTreeNode.data)^.lConnected := true;
    end;
  end;
end;

{
Dynamically loads a selected server alias with the available databases.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadDatabases(oTreeNode : TTreeNode);
var
  oNewNode : TTreeNode;
  lcv : integer;
  dmoObject : PdmoObject;
  dmo : _SQLServer;
begin
  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  dmo := PdmoObject(oTreeNode.Data)^.SQL_DMO;
  dmo.Databases.Refresh(true);
  for lcv := 1 to dmo.Databases.Count do begin
    new(dmoObject);
    dmoObject.SQL_DMO := dmo;
    dmoObject.SQL_DB := dmo.Databases.Item(lcv, null);
    dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
    oNewNode := oTreeNode.Owner.AddChildObject(oTreeNode, dmo.Databases.Item(lcv, null).name, dmoObject);
    oNewNode.StateIndex := 2;

    // Now add the basic object nodes.
    oTreeNode.Owner.AddChildObject(oNewNode, 'tables', dmoObject).StateIndex := 3;
    oTreeNode.Owner.AddChildObject(oNewNode, 'views', dmoObject).StateIndex := 3;
    oTreeNode.Owner.AddChildObject(oNewNode, 'system tables', dmoObject).StateIndex := 3;
    oTreeNode.Owner.AddChildObject(oNewNode, 'stored procedures', dmoObject).StateIndex := 3;
    oTreeNode.Owner.AddChildObject(oNewNode, 'triggers', dmoObject).StateIndex := 3;
  end;
  oTreeNode.expanded := true;
end;

{
Dynamically loads a selected database branch with the available tables.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadTables(oTreeNode : TTreeNode);
var
  lcv : integer;
  db : _Database;
  dmoObject : PdmoObject;
begin
  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  // Get the node's SQL_DB property.
  db := PdmoObject(oTreeNode.Data)^.SQL_DB;
  db.Tables.Refresh(true);
  for lcv := 1 to db.Tables.Count do begin
    // Table list (non-system).
    if not db.Tables.Item(lcv, null).SystemObject then begin
      new(dmoObject);
      dmoObject.SQL_DMO := PdmoObject(oTreeNode.Data)^.SQL_DMO;
      dmoObject.SQL_DB := db;
      dmoObject.SQL_OBJ := db.Tables.Item(lcv, null);
      dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
      oTreeNode.Owner.AddChildObject(oTreeNode, db.Tables.Item(lcv, null).name, dmoObject).StateIndex := 4;
    end;
  end;
  oTreeNode.expanded := true;
end;

{
Dynamically loads a selected database branch with the available system tables.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadSystemTables(oTreeNode : TTreeNode);
var
  lcv : integer;
  db : _Database;
  dmoObject : PdmoObject;
begin
  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  // Get the node's SQL_DB property.
  db := PdmoObject(oTreeNode.Data)^.SQL_DB;
  db.Tables.Refresh(true);
  for lcv := 1 to db.Tables.Count do begin
    // Tables list (system).
    if db.Tables.Item(lcv, null).SystemObject then begin
      new(dmoObject);
      dmoObject.SQL_DMO := PdmoObject(oTreeNode.Data)^.SQL_DMO;
      dmoObject.SQL_DB := db;
      dmoObject.SQL_OBJ := db.Tables.Item(lcv, null);
      dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
      oTreeNode.Owner.AddChildObject(oTreeNode, db.Tables.Item(lcv, null).name, dmoObject).StateIndex := 4;
    end;
  end;
  oTreeNode.expanded := true;
end;

{
Dynamically loads a selected database branch with the available views.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadViews(oTreeNode : TTreeNode);
var
  lcv : integer;
  db : _Database;
  dmoObject : PdmoObject;
begin
  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  // Get the node's SQL_DB property.
  db := PdmoObject(oTreeNode.Data)^.SQL_DB;
  db.Views.Refresh(true);
  for lcv := 1 to db.Views.Count do begin
    new(dmoObject);
    dmoObject.SQL_DMO := PdmoObject(oTreeNode.Data)^.SQL_DMO;
    dmoObject.SQL_DB := db;
    dmoObject.SQL_OBJ := db.Views.Item(lcv, null);
    dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
    oTreeNode.Owner.AddChildObject(oTreeNode, db.Views.Item(lcv, null).name, dmoObject).StateIndex := 4;
  end;
  oTreeNode.expanded := true;
end;

{
Dynamically loads a selected database branch with the available stored procedures.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadStoredProcedures(oTreeNode : TTreeNode);
var
  lcv : integer;
  db : _Database;
  dmoObject : PdmoObject;
begin
  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  // Get the node's SQL_DB property.
  db := PdmoObject(oTreeNode.Data)^.SQL_DB;
  db.StoredProcedures.Refresh(true);
  for lcv := 1 to db.StoredProcedures.Count do begin
    new(dmoObject);
    dmoObject.SQL_DMO := PdmoObject(oTreeNode.Data)^.SQL_DMO;
    dmoObject.SQL_DB := db;
    dmoObject.SQL_OBJ := db.StoredProcedures.Item(lcv, null);
    dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
    oTreeNode.Owner.AddChildObject(oTreeNode, db.StoredProcedures.Item(lcv, null).name, dmoObject).StateIndex := 4;
  end;
  oTreeNode.expanded := true;
end;

{
Dynamically loads a selected database branch with the available triggers.

A ttreenode object is passed in which will load/refresh the branch.
}
procedure Tbo_SQL.LoadTriggers(oTreeNode : TTreeNode);
var
  lcv, lcv2 : integer;
  db : _Database;
  dmoObject : PdmoObject;
begin