PowerDesigner: 逆向工程: 利用sql脚本文件逆生成模型

MarkTime: missed

LogTime: 2024-11-09 15:15:08

开发完需要依据模块现有表结构, 生成pdm, 帮助后来者通过直观展示的模块涉及表、表间关系来理解模块逻辑

版本说明

Power Designer: Sybase PowerDesigner 16.5
Oracle: Oracle Version 11g
PL/SQL Developer: PLSQL Developer 14

前置准备

用于构建模型的sql脚本文件

/*==============================================================*/
/* Table: 驿站表: T_STATION                                     */
/*==============================================================*/
-- Create table
DECLARE
  AN_FLAG NUMBER;
BEGIN
  SELECT COUNT(1) INTO AN_FLAG FROM USER_TABLES WHERE TABLE_NAME='T_STATION';
  IF AN_FLAG=0 THEN
    EXECUTE IMMEDIATE ' 
      CREATE TABLE T_STATION
      (
        id            VARCHAR2(32) not null,
        name          VARCHAR2(250),
        type          VARCHAR2(4),
        area_code     VARCHAR2(64),
        address       VARCHAR2(300),
        lon           NUMBER(10,6),
        lat           NUMBER(10,6),
        link_man      VARCHAR2(100),
        link_man_tel  VARCHAR2(20),
        begin_time    VARCHAR2(20),
        end_time      VARCHAR2(20),
        cur_status    VARCHAR2(2),
        descption     VARCHAR2(4000),
        attach_id     VARCHAR2(1000),
        creator       VARCHAR2(32),
        create_time   DATE,
        updater       VARCHAR2(32),
        update_time   DATE,
        constraint PK_T_STATION_ID primary key (ID)
      )';
    END IF;
END;
/
-- Add comments to the table 
COMMENT ON TABLE T_STATION IS '驿站表';
-- Add comments to the columns 
COMMENT ON COLUMN T_STATION.id IS '主键';
COMMENT ON COLUMN T_STATION.name IS '驿站名称';
COMMENT ON COLUMN T_STATION.type IS '驿站类型: 字典值: CITIZEN_STATION_TYPE';
COMMENT ON COLUMN T_STATION.area_code IS '所属社区';
COMMENT ON COLUMN T_STATION.address IS '地址';
COMMENT ON COLUMN T_STATION.lon IS '经度';
COMMENT ON COLUMN T_STATION.lat IS '纬度';
COMMENT ON COLUMN T_STATION.link_man IS '联系人';
COMMENT ON COLUMN T_STATION.link_man_tel IS '联系电话';
COMMENT ON COLUMN T_STATION.begin_time IS '开放开始时间';
COMMENT ON COLUMN T_STATION.end_time IS '开放结束时间';
COMMENT ON COLUMN T_STATION.cur_status IS '服务状态: 0关闭; 1正常';
COMMENT ON COLUMN T_STATION.descption IS '简介';
COMMENT ON COLUMN T_STATION.attach_id IS '附件id';
COMMENT ON COLUMN T_STATION.creator IS '创建人';
COMMENT ON COLUMN T_STATION.create_time IS '创建时间';
COMMENT ON COLUMN T_STATION.updater IS '更新人';
COMMENT ON COLUMN T_STATION.update_time IS '更新时间';


/*==============================================================*/
/* Table: 驿站活动表: T_STATION_ACT                             */
/*==============================================================*/
-- Create table
DECLARE
  AN_FLAG NUMBER;
BEGIN
  SELECT COUNT(1) INTO AN_FLAG FROM USER_TABLES WHERE TABLE_NAME='T_STATION_ACT';
  IF AN_FLAG=0 THEN
    EXECUTE IMMEDIATE ' 
    create table T_STATION_ACT
    (
      id             VARCHAR2(32) not null,
      station_id     VARCHAR2(32),
      name       VARCHAR2(200),
      begin_time     DATE,
      end_time       DATE,
      content        VARCHAR2(4000),
      attach_id      VARCHAR2(2000),
      creator        VARCHAR2(32),
      create_time    DATE,
      updater        VARCHAR2(32),
      update_time    DATE,
      release_time   DATE,
      status         VARCHAR2(2),
      constraint PK_T_STATION_ACT_ID primary key (ID)
       )';
    END IF;
END;
/
-- Add comments to the table 
comment on table T_STATION_ACT IS '驿站活动表';
-- Add comments to the columns 
COMMENT ON COLUMN T_STATION_ACT.id IS '主键';
COMMENT ON COLUMN T_STATION_ACT.station_id IS '驿站id';
COMMENT ON COLUMN T_STATION_ACT.name IS '活动名称';
COMMENT ON COLUMN T_STATION_ACT.begin_time IS '活动开始时间';
COMMENT ON COLUMN T_STATION_ACT.end_time IS '活动结束时间';
COMMENT ON COLUMN T_STATION_ACT.content IS '活动内容';
COMMENT ON COLUMN T_STATION_ACT.attach_id IS '附件';
COMMENT ON COLUMN T_STATION_ACT.creator IS '创建人';
COMMENT ON COLUMN T_STATION_ACT.create_time IS '创建时间';
COMMENT ON COLUMN T_STATION_ACT.updater IS '更新人';
COMMENT ON COLUMN T_STATION_ACT.update_time IS '更新时间';
COMMENT ON COLUMN T_STATION_ACT.release_time IS '发布时间';
COMMENT ON COLUMN T_STATION_ACT.status IS '活动状态: 1未开始;2进行中;3已结束';
COMMENT ON COLUMN T_STATION_ACT.reservation_id IS '预约事项id';

逆向所需的vbs脚本文件

作用: 将表结构的comment注释赋予对应的表/列/视图

pd的安装目录下有文件夹 VB Script, 可以考虑保存此VBS文件直接放置到自己本地里
- D:\IsDev\Sybase\PowerDesigner16\VB Scripts\A_LinForest_FormatToChinese.vbs (自己的PD安装目录)

alidationMode   =   True 
InteractiveMode   =   im_Batch
Dim   mdl   '   the   current   model
'   get   the   current   active   model 
Set   mdl   =   ActiveModel 
If   (mdl   Is   Nothing)   Then 
      MsgBox   "There   is   no   current   Model " 
ElseIf   Not   mdl.IsKindOf(PdPDM.cls_Model)   Then 
      MsgBox   "The   current   model   is   not   an   Physical   Data   model. " 
Else 
      ProcessFolder   mdl 
End   If
Private   sub   ProcessFolder(folder) 
On Error Resume Next
      Dim   Tab   'running     table 
      for   each   Tab   in   folder.tables 
            if   not   tab.isShortcut   then 
                  tab.name   =   tab.comment
                  Dim   col   '   running   column 
                  for   each   col   in   tab.columns 
                  if col.comment="" then
                  else
                        col.name=   col.comment 
                  end if
                  next 
            end   if 
      next
      Dim   view   'running   view 
      for   each   view   in   folder.Views 
            if   not   view.isShortcut   then 
                  view.name   =   view.comment 
            end   if 
      next
      '   go   into   the   sub-packages 
      Dim   f   '   running   folder 
      For   Each   f   In   folder.Packages 
            if   not   f.IsShortcut   then 
                  ProcessFolder   f 
            end   if 
      Next 
end   sub

过程

先创建一个空model项目
- * DBMS 需要根据使用的数据库版本进行选择

利用已经准备好的sql脚本, 来逆向生成物理模型
- 指尖路径:
  - -> Database -> Update Model from Database(Ctrl + R)
  - -> Add Files
  - -> 确定
- 如果选择的文件内部有变动, 需要Clear All之后, 再Add Files来重新导入加载

上一步点击确定之后, sql脚本没问题的话就可以看到转换完成的模型了
- 聚焦表Table/字段Column && 双击 => 查看对应表Table/字段Column的详情
- 聚焦表Table && 单击长按不松开 => 拖拽调整排版
- ctrl + 鼠标滚轮滚动 => 缩放
- 可以使用实线标识两个表字段间的关系

此时的模型是直接用英文来标识字段的, 如果为了更加直观(以表结构的comment内容来展示), 需要借助vbs
- 指尖路径:
  - -> Tools -> Execute Commands -> Edit/Run Script...
  - -> 选择已存在vbs文件/直接复制前置准备-逆向vbsvbs至脚本输入框
  - -> Run运行脚本
  - -> Close

终

posted @ 2024-11-11 00:33 LinForest_zZ 阅读(1142) 评论(0) 收藏举报

刷新页面返回顶部

linForest

PowerDesigner: 逆向工程: 利用sql脚本文件逆生成模型

版本说明

前置准备

用于构建模型的sql脚本文件

逆向所需的vbs脚本文件

过程

终

公告