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


过程

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

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



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



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




posted @   LinForest_zZ  阅读(295)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· spring官宣接入deepseek,真的太香了~
点击右上角即可分享
微信分享提示