sql.vm
6.38 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
## 通用变量定义
#if ($importEnable)
#set ($functionNames = ['查询', '创建', '更新', '删除', '导出', '导入'])
#set ($functionOps = ['query', 'create', 'update', 'delete', 'export', 'import'])
#else
#set ($functionNames = ['查询', '创建', '更新', '删除', '导出'])
#set ($functionOps = ['query', 'create', 'update', 'delete', 'export'])
#end
##
## 宏定义：生成按钮 SQL（通用部分）
#macro(insertButtonSql $parentIdVar)
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, ${parentIdVar},
'', '', '', 0
);
#end
#end
##
## ======================= MySQL / OceanBase =======================
#if ($dbType.name() == 'MYSQL' || $dbType.name() == 'OCEAN_BASE')
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'${table.classComment}管理', '', 2, 0, ${table.parentMenuId},
'${simpleClassName_strikeCase}', '', '${table.moduleName}/${table.businessName}/index', 0, '${table.className}'
);
-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();
-- 按钮 SQL
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, @parentId,
'', '', '', 0
);
#end
##
## ======================= Oracle / 达梦 DM / 神通 OSCAR =======================
#elseif ($dbType.name() == 'ORACLE' || $dbType.name() == 'DM' || $dbType.name() == 'OSCAR')
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'${table.classComment}管理', '', 2, 0, ${table.parentMenuId},
'${simpleClassName_strikeCase}', '', '${table.moduleName}/${table.businessName}/index', 0, '${table.className}'
);
-- 按钮父菜单ID
-- 说明：Oracle/达梦/神通 使用序列获取上一个插入的 ID
#if ($dbType.name() == 'OSCAR')
-- 注意：神通需要 system_menu 的 id 默认使用 system_menu_seq 序列，参见 sql/tools/README.md 的神通部分
#end
DECLARE
v_parent_id NUMBER;
BEGIN
SELECT system_menu_seq.CURRVAL INTO v_parent_id FROM DUAL;
-- 按钮 SQL
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, v_parent_id,
'', '', '', 0
);
#end
END;
/
##
## ======================= PostgreSQL / 人大金仓 KingbaseES =======================
#elseif ($dbType.name() == 'POSTGRE_SQL' || $dbType.name() == 'KINGBASE_ES')
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'${table.classComment}管理', '', 2, 0, ${table.parentMenuId},
'${simpleClassName_strikeCase}', '', '${table.moduleName}/${table.businessName}/index', 0, '${table.className}'
);
-- 按钮父菜单ID
-- 说明：PostgreSQL/KingbaseES 使用 lastval() 获取上一个插入的序列值
DO $$
DECLARE
v_parent_id BIGINT;
BEGIN
v_parent_id := lastval();
-- 按钮 SQL
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, v_parent_id,
'', '', '', 0
);
#end
END $$;
##
## ======================= SQL Server =======================
#elseif ($dbType.name() == 'SQL_SERVER' || $dbType.name() == 'SQL_SERVER2005')
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'${table.classComment}管理', '', 2, 0, ${table.parentMenuId},
'${simpleClassName_strikeCase}', '', '${table.moduleName}/${table.businessName}/index', 0, '${table.className}'
);
-- 按钮父菜单ID
-- 说明：SQL Server 使用 SCOPE_IDENTITY() 获取上一个插入的自增 ID
DECLARE @parentId BIGINT;
SET @parentId = SCOPE_IDENTITY();
-- 按钮 SQL
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, @parentId,
'', '', '', 0
);
#end
##
## ======================= 不支持的数据库类型 =======================
#else
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 注意：当前数据库类型 ${dbType.name()} 暂不支持自动生成菜单 SQL
-- 请参考以下 MySQL 语法，手动修改为您的数据库对应的语法
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 菜单 SQL
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status, component_name
)
VALUES (
'${table.classComment}管理', '', 2, 0, ${table.parentMenuId},
'${simpleClassName_strikeCase}', '', '${table.moduleName}/${table.businessName}/index', 0, '${table.className}'
);
-- 按钮父菜单ID
-- TODO: 请根据您的数据库类型，修改获取上一个插入 ID 的方式
-- MySQL: SELECT @parentId := LAST_INSERT_ID();
-- Oracle: SELECT system_menu_seq.CURRVAL INTO v_parent_id FROM DUAL;
-- PostgreSQL: SELECT lastval() INTO v_parent_id;
-- SQL Server: SET @parentId = SCOPE_IDENTITY();
SELECT @parentId := LAST_INSERT_ID();
-- 按钮 SQL
#foreach ($functionName in $functionNames)
#set ($index = $foreach.count - 1)
INSERT INTO system_menu(
name, permission, type, sort, parent_id,
path, icon, component, status
)
VALUES (
'${table.classComment}${functionName}', '${permissionPrefix}:${functionOps.get($index)}', 3, $foreach.count, @parentId,
'', '', '', 0
);
#end
#end