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
- 指尖路徑:
終
