dmsql.pas

是一个delphi的流程制作软件

  // Delete all of the child nodes first.
  oTreeNode.DeleteChildren;
  // Get the node's SQL_DB property.
  db := PdmoObject(oTreeNode.Data)^.SQL_DB;
  for lcv := 1 to db.Tables.Count do begin
    db.Tables.Item(lcv, null).Triggers.Refresh(true);
    for lcv2 := 1 to db.Tables.Item(lcv, null).Triggers.Count do begin
      new(dmoObject);
      dmoObject.SQL_DMO := PdmoObject(oTreeNode.Data)^.SQL_DMO;
      dmoObject.SQL_DB := db;
      dmoObject.SQL_OBJ := db.Tables.Item(lcv, null).Triggers.Item(lcv2, null);
      dmoObject.lConnected := PdmoObject(oTreeNode.Data)^.lConnected;
      oTreeNode.Owner.AddChildObject(oTreeNode, db.Tables.Item(lcv, null).Triggers.Item(lcv2, null).name, dmoObject).StateIndex := 4;
    end;
  end;
  oTreeNode.expanded := true;
end;

{
Load the standard objects for a database.
}
procedure Tbo_SQL.LoadObjects(oTreeNode : TTreeNode);
begin
  if oTreeNode.text = 'tables' then begin
    LoadTables(oTreeNode);
  end else if oTreeNode.text = 'views' then begin
    LoadViews(oTreeNode);
  end else if oTreeNode.text = 'system tables' then begin
    LoadSystemTables(oTreeNode);
  end else if oTreeNode.text = 'stored procedures' then begin
    LoadStoredProcedures(oTreeNode);
  end else if oTreeNode.text = 'triggers' then begin
    LoadTriggers(oTreeNode);
  end;
end;

{
If the selected object is a persisten container (like a table),
return the rows that are associated with it.
}
procedure Tbo_SQL.GetData(oTreeNode : TTreeNode);
begin
  if (oTreeNode = nil) or
     (oTreeNode.level < 3) or

     ((oTreeNode.level >= 3) and
     ((oTreeNode.parent.text <> 'tables') xor
      (oTreeNode.parent.text <> 'views') xor
      (oTreeNode.parent.text <> 'system tables'))) then begin
    if db_Objects.connected then db_Objects.connected := false;
    exit;
  end;

  try
    with db_Objects do begin
      connected := false;
      // *** Note, I purposely named the ODBC DSN driver alias the same
      // as the database name.  This was a convenience factor.
      params.values['DATABASE NAME'] := PdmoObject(oTreeNode.data)^.SQL_DB.name;
      params.values['ODBC DSN'] := PdmoObject(oTreeNode.data)^.SQL_DB.name;
      connected := true;
    end;
    with q_Data do begin
      close;
      Tablename := PdmoObject(oTreeNode.data)^.SQL_OBJ.name;
      open;
    end;
  except
    q_Data.close;
  end;
end;

{
Dynamically list the details of the persistant storage containter.

In the case of tables and views, this will be columns.
}
procedure Tbo_SQL.GetDetails(oTreeNode : TTreeNode; oListView : TListView);
var
  oListItem : TListItem;
  oColumn : TListColumn;
  lcv : integer;
  obj : variant;
begin
  // Clear it out.
  oListView.Items.Clear;
  oListView.Columns.Clear;

  // Top most node doesn't have a parent.
  if (oTreeNode.level < 3) or
     (oTreeNode.parent.text <> 'tables') and
     (oTreeNode.parent.text <> 'system tables') then exit;

  // Create the columns.
  oColumn := oListView.Columns.Add;
  oColumn.Caption := 'Name';
  oColumn.Width := 120;
  oColumn := oListView.Columns.Add;
  oColumn.Caption := 'Type';
  oColumn.Width := 65;
  oColumn := oListView.Columns.Add;
  oColumn.Caption := 'Length';
  oColumn.Width := 50;
  oColumn := oListView.Columns.Add;
  oColumn.Caption := 'Prec';
  oColumn.Width := 50;
  oColumn := oListView.Columns.Add;
  oColumn.Caption := 'Null';
  oColumn.Width := 50;

  // Fill the columns.
  obj := PdmoObject(oTreeNode.Data)^.SQL_OBJ;
  for lcv := 1 to obj.Columns.Count do begin
    oListItem := oListView.Items.Add;
    oListItem.Caption := obj.Columns.item(lcv).Name;
    oListItem.SubItems.Add(obj.Columns.item(lcv).PhysicalDatatype);
    oListItem.SubItems.Add(vartostr(obj.Columns.item(lcv).Length));
    oListItem.SubItems.Add(vartostr(obj.Columns.item(lcv).NumericPrecision));
    oListItem.SubItems.Add(vartostr(obj.Columns.item(lcv).AllowNulls+1));
  end;
end;

(*
Format the dropped fields as specified.

TdadLayout = (dad_Field,                           {Field,                   }
              dad_aField,                          {@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_Alias_Field_Equals_aField_and);  {Alias.Field = @Field and,}

*)
procedure Tbo_SQL.FormatDADLayout(Source : TObject; dadLayout : TdadLayout; oMemo : TMemo);
var
  lcv : integer;
  iLastPos : integer;
  s : string;
  iTerminator : integer; {0=none, 1=',', 2='and'}

  function GetDatatypeFormat : string;
  begin
    with Source as TlistView do begin
      if (Items[lcv].SubItems[0] = 'char') or
         (Items[lcv].SubItems[0] = 'nchar') or
         (Items[lcv].SubItems[0] = 'varchar') or
         (Items[lcv].SubItems[0] = 'nvarchar') or
         (Items[lcv].SubItems[0] = 'sysname') or
         (Items[lcv].SubItems[0] = 'text') or
         (Items[lcv].SubItems[0] = 'ntext') then begin
        result := Items[lcv].SubItems[0] + '(' + Items[lcv].SubItems[1] + ')';
      end else if (Items[lcv].SubItems[0] = 'float') or
                  (Items[lcv].SubItems[0] = 'decimal') or
                  (Items[lcv].SubItems[0] = 'money') or
                  (Items[lcv].SubItems[0] = 'numeric') or
                  (Items[lcv].SubItems[0] = 'real') or
                  (Items[lcv].SubItems[0] = 'smallmoney') then begin
        result := Items[lcv].SubItems[0] + '(' +
                  Items[lcv].SubItems[1] + ',' +
                  Items[lcv].SubItems[2] + ')';
      end else begin
        // Default.
        result := Items[lcv].SubItems[0];
      end;
    end;
  end;

  {
  Determine whether to replace selected text or insert new lines.
  }
  procedure InsertMemoText(sText : string);
  begin
    if oMemo.SelText <> '' then begin
      oMemo.SelText := sText;
      dec(iLastPos);
    end else begin
      oMemo.Lines.Insert(iLastPos, sText);
    end;
  end;

begin
  iLastPos := oMemo.perform(EM_LINEFROMCHAR, -1, 0);
  // Take into account that person may start on blank page.
  if (iLastPos = 0) and (oMemo.Lines.Count = 0) then iLastPos := -1;

  iTerminator := 0;

  if Source is TListView then begin
    with Source as TListView do begin
      for lcv := 0 to Items.Count - 1 do begin
        // If not a selected item then continue.
        if not Items[lcv].selected then continue;
        inc(iLastPos);

        case dadLayout of
        dad_Field: begin
                     InsertMemoText(Items[lcv].Caption + ',');
                     iTerminator := 1;
                   end;
        dad_Alias: begin
                     InsertMemoText(Items[lcv].SubItems[5]);
                     iTerminator := 0;
                   end;
        dad_aField: begin
                      InsertMemoText('@' + Items[lcv].Caption);
                      iTerminator := 0;
                    end;
        dad_aField2: begin
                       InsertMemoText('@' + Items[lcv].Caption + ',');
                       iTerminator := 1;
                    end;
        dad_Alias_Field: begin
                           InsertMemoText(Items[lcv].SubItems[5] + '.' +
                                          Items[lcv].Caption + ',');
                           iTerminator := 1;
                         end;
        dad_aField_Equals_Field: begin
                                   InsertMemoText('@' + Items[lcv].Caption + ' = ' +
                                                  Items[lcv].Caption + ',');
                                   iTerminator := 1;
                                 end;
        dad_aField_DataType: begin
                               InsertMemoText('@' + Items[lcv].Caption + ' ' +
                                              GetDatatypeFormat + ',');
                               iTerminator := 1;
                             end;
        dad_Field_Equals_aField_and: begin
                                       InsertMemoText(Items[lcv].Caption + ' = ' +
                                                      '@' + Items[lcv].Caption + ' and');
                                       iTerminator := 2;
                                     end;
        dad_Field_Equals_aField: begin
                                   InsertMemoText(Items[lcv].Caption + ' = ' +
                                                  '@' + Items[lcv].Caption + ',');
                                   iTerminator := 1;
                                 end;
        dad_Alias_Field_Equals_aField_and: begin
                                             InsertMemoText(Items[lcv].SubItems[5] + '.' +
                                                            Items[lcv].Caption + ' = ' +
                                                            '@' + Items[lcv].Caption + ' and');
                                             iTerminator := 2;
                                           end;
        end;
      end;
    end;
  end else if Source is TTreeView then begin
    inc(iLastPos);

    case dadLayout of
    dad_Alias: begin
                 InsertMemoText(TTreeView(Source).selected.text);
                 iTerminator := 0;
               end;
    dad_SP_Alias: begin
                    InsertMemoText('sp' + TTreeView(Source).selected.text + '$');
                    iTerminator := 0;
                  end;
    dad_SP_Alias_Get: begin
                        InsertMemoText('sp' + TTreeView(Source).selected.text + '$get');
                        iTerminator := 0;
                      end;
    dad_SP_Alias_Ins: begin
                        InsertMemoText('sp' + TTreeView(Source).selected.text + '$ins');
                        iTerminator := 0;
                      end;
    dad_SP_Alias_Upd: begin
                        InsertMemoText('sp' + TTreeView(Source).selected.text + '$upd');
                        iTerminator := 0;
                      end;
    dad_SP_Alias_Del: begin
                        InsertMemoText('sp' + TTreeView(Source).selected.text + '$del');
                        iTerminator := 0;
                      end;
    end;
  end;
  // Clear up the last terminator.
  s := oMemo.Lines.Strings[iLastPos];
  case iTerminator of
  0:;
  1: Delete(s, Length(s), 1);
  2: Delete(s, Length(s)-2, 3);
  end;
  oMemo.Lines.Strings[iLastPos] := s;
end;

{
Provide ability to save the SQL scripts to a file.
}
procedure Tbo_SQL.SaveMemoToFile(sMemoText : string);
var
  MyFile : TextFile;
  SD : TSaveDialog;
begin
  SD := TSaveDialog.Create(nil);
  SD.DefaultExt := 'SQL';
  SD.FileName := 'script';
  SD.Options := [];
  SD.Options := [ofOverwritePrompt];
  SD.Title := 'Save SQL Script';
  SD.Filter := 'Script files (*.SQL)|*.SQL';
  SD.InitialDir := SQLPath;
  try
    // Get the name of the text file.
    if SD.execute then begin
      // Reset the initial dir.
      SD.InitialDir := ExtractFilePath(SD.FileName);
      SQLPath := SD.InitialDir;
      // Assign the file.
      AssignFile(MyFile, SD.FileName);
      // Read/Write file access;
      FileMode := 2;
      // Create and Open the file.
      Rewrite(MyFile);
      WriteLn(MyFile, sMemoText);
      // Close the file.
      CloseFile(MyFile);
    end;
  finally
    SD.free;
  end;
end;

{
Provide ability to load the SQL scripts from a file.
}
procedure Tbo_SQL.LoadMemoFromFile(oMemo : TMemo);
var
  MyFile : TextFile;
  OD : TOpenDialog;
  sLine : string;
begin
  OD := TSaveDialog.Create(nil);
  OD.DefaultExt := 'SQL';
  OD.FileName := 'script';
  OD.Options := [];
  OD.Title := 'Load SQL Script';
  OD.Filter := 'Script files (*.SQL)|*.SQL; All Files (*.*)|*.*';
  OD.InitialDir := SQLPath;
  try
    // Get the name of the text file.
    if OD.execute then begin
      // Reset the initial dir.
      OD.InitialDir := ExtractFilePath(OD.FileName);
      SQLPath := OD.InitialDir;
      // Assign the file.
      AssignFile(MyFile, OD.FileName);
      // Readonly file access;
      FileMode := 0;
      // Open the file.
      Reset(MyFile);
      // spin thru and load.
      while not EOF(MyFile) do begin
        ReadLn(MyFile, sLine);
        oMemo.Lines.Add(sLine);
      end;
      // Close the file.
      CloseFile(MyFile);
    end;
  finally
    OD.free;
  end;
end;


{
Retrieve user defined list of macros and put in list view.
}
procedure Tbo_SQL.LoadMacros(oListView : TListView);
var
  oListItem : TListItem;
  oColumn : TListColumn;
  MyIni : TIniFile;