· 8 years ago · Mar 25, 2018, 03:38 AM
1/*********************************
2 Table : View registratin - design
3***********************************/
4-- * Table to hold registration data
5drop table if exists "dbSys".tbViewRegistration cascade ;
6
7drop sequence if exists "dbSys".tbViewRegistration_Seq;
8create SEQUENCE "dbSys".tbViewRegistration_Seq;
9
10CREATE TABLE IF NOT EXISTS "dbSys".tbViewRegistration (
11 ID int not null default nextval('"dbSys".tbViewRegistration_seq') primary key
12 ,Sch text
13 ,Nam text
14 ,lastid int null
15
16 ,lastins timestamp_wtz null
17 ,lastupd timestamp_wtz null
18 ,lastdel timestamp_wtz null
19
20 ,delete_system_data boolean not null default true
21 ,edit_system_data boolean not null default true -- true = to make it compatible
22 ,edit_system_field text null
23 -- ,init_create_func text null
24 ,init_data boolean null ,init_data_uid int null ,init_data_acc int null, init_data_func_name text null
25 ,init_sysorg boolean null default false -- Keep system original data
26 ,init_master boolean null
27 ,init_count text null -- Field name of parent counter for this view
28 ,init_proc text null
29 ,init_proc_creater text null
30 ,init_proc_sqlexe text null
31
32 ,init_proc_orgid_init text null -- = rec._sql_org_init
33 ,init_proc_orgid_init_drop text null -- = rec._sql_org_init_drop
34
35 ,init_nam_table text null -- persistend using to create the init procedure
36 ,init_sync_sysdata boolean null
37 ,init_sync_acc int null
38 ,init_merge boolean not null default false -- Force init event having data when drop view
39 ,init_unique_fn text null -- = 'acc,title' -- unique field name
40 ,init_unique_exc_updatefn text null -- = 'description' -- update when uniquue violation
41
42 ,OrderNo_Updatable boolean not null default false
43 ,OrderNo_FieldName text not null default 'OrderNo'
44 -- ,pers_sch text ,pers_nam text
45 ,tblregis_id int null references "dbSys".tbtblregis(id) on delete cascade
46 ,tblregis_default boolean null -- view have this persistent as default
47 ,haveOrgID boolean null -- have 'orgid' in persistent
48 ,fnCheckPost text null
49 ,privSysRole text null
50 -- ,fnOrderNo text null
51 ,init_registration boolean null
52
53 ,matview boolean null
54 ,mvOp text null -- check mvop in ( 'Ins','Upd',' Del' )
55 ,mvAccID_share int
56 ,mvOrderBy text
57 ,mvpk text
58 ,mvStrict boolean -- Check data is in scope of view when insert
59 ,mvAncestor text
60 ,mvAncPK text-- = 'contactid' -- Data field for ancestor PK
61 --,mvtbl text null
62 ,mvSyncParent boolean null
63 ,mvIndInh boolean null
64 ,mvupd_byanc boolean null
65 ,mvForeDS text null -- 'dbContact".tbaddrvw'
66 ,mvForeFK text null -- 'tvalue' > 'id'
67 ,mvForePK text null
68
69);
70create unique index tbViewRegistration_sch_nam on "dbSys".tbViewRegistration( sch, nam );
71create index tbViewRegistration_tblregis_id on "dbSys".tbViewRegistration( tblregis_id );
72create unique index tbViewRegistration_sch_nam_tblregis_default on "dbSys".tbViewRegistration( sch, nam, tblregis_default ) where tblregis_default is true ;
73
74
75/******************************
76 * Table : Data field of view
77 * Wut, 2018-03-10
78
79 select * from "dbSys".getTableColumns( '"dbContact".tbcontactvw4');
80
81
82
83**********************************/
84drop table if exists "dbSys".tbViewRegistrationDF cascade ;
85
86drop sequence if exists "dbSys".tbViewRegistrationDF_Seq;
87create SEQUENCE "dbSys".tbViewRegistrationDF_Seq;
88
89create TABLE IF NOT EXISTS "dbSys".tbViewRegistrationDF (
90 ID int not null default nextval('"dbSys".tbViewRegistrationDF_seq') primary key
91 ,fno int not null
92 ,fname text not null
93 ,ftype text not null
94 ,fnullable boolean null
95 ,fprimary boolean null
96 ,fhas_default boolean null
97 ,fdef_value text null
98 ,fcol_comment text null
99 ,fcol_title text null
100 ,pid int not null references "dbSys".tbviewregistration on delete cascade
101
102);
103create unique index tbViewRegistrationDF_pid_fname on "dbSys".tbViewRegistrationDF( pid,fname );
104
105-- select * from "dbSys".getTableColumns( '"dbContact".tbcontactvw4');
106
107
108
109/**********************************
110 Trigger :Create data of data field for certain view
111 Wut, 2018-03-10
112
113 select rtrim('xvwxxxvw','vw'); --'xvwxxx
114 select * from "dbSys".getTableColumns( '"dbContact".tbcontactvw4');
115
116 select * from "dbSys".tbViewRegistrationDF ;
117
118**********************************/
119create or replace function "dbSys".tbViewRegistration_create_datafield () returns trigger as $$
120declare tbl text; sql text;
121begin
122 -- select format('"%".%s',a.sch, a.nam) from "dbSys".tbtblregis where id = new.tblregis_id into tbl;
123 /*
124 begin
125 select format('"%s".%s',new.sch, rtrim( lower(new.nam), 'vw' ) ) into tbl;
126
127 if tbl is not null then
128 begin
129 sql:= format('select "dbSys".tvcreateview( %s,%s,%L)', 41,4, tbl );
130 execute sql into tbl;
131 exception
132 when others then raise notice '0136:Can not create view, sql=%', sql;
133 end;
134 if "dbSys".havetable(tbl) then
135 begin
136 insert into "dbSys".tbViewRegistrationDF (
137 fno --int not null
138 ,fname-- text not null
139 ,ftype-- text not null
140 ,fprimary --boolean null
141 , fnullable
142 ,fhas_default -- boolean null
143 ,fdef_value-- text null
144 ,fcol_comment-- text null
145 ,fcol_title-- text null
146
147 ,pid -- int not null references "dbSys".tbviewregistration
148 )
149 select a.*, new.id from "dbSys".getTableColumns ( tbl ) a
150 ;
151 exception
152 when others then
153 raise notice '0151:%, can not add data field for "%"',sqlerrm, tbl;
154 end;
155 end if;
156 end if;
157 exception
158 when others then raise notice '0157:%',sqlerrm;
159 end ;
160 */
161
162 -- select format('"%".%s',a.sch, a.nam) from "dbSys".tbtblregis where id = new.tblregis_id into tbl;
163
164 select format('"%s".%s',new.sch, rtrim( lower(new.nam), 'vw' ) ) into tbl;
165
166 if tbl is not null then
167
168 begin
169 sql:= format('select "dbSys".tvcreateview( %s,%s,%L)', 41,4, tbl );
170 execute sql into tbl;
171
172 if "dbSys".havetable(tbl) then
173
174 insert into "dbSys".tbViewRegistrationDF (
175 fno --int not null
176 ,fname-- text not null
177 ,ftype-- text not null
178 ,fprimary --boolean null
179 , fnullable
180 ,fhas_default -- boolean null
181 ,fdef_value-- text null
182 ,fcol_comment-- text null
183 ,fcol_title-- text null
184
185 ,pid -- int not null references "dbSys".tbviewregistration
186 )
187 select a.*, new.id from "dbSys".getTableColumns ( tbl ) a
188 ;
189
190
191 end if;
192 exception
193 when others then -- skip
194 end;
195 end if;
196
197
198 return new;
199end $$ language plpgsql;
200/*
201drop trigger if exists tbViewRegistration_create_datafield on "dbSys".tbviewregistration cascade;
202create trigger tbViewRegistration_create_datafield
203 after insert on "dbSys".tbviewregistration
204 for each row execute procedure "dbSys".tbViewRegistration_create_datafield ();
205
206 */
207
208
209
210/************************************
211 Table : View registration for openning
212
213 select * from "dbSys".tbViewRegis
214**************************************/
215drop table if exists "dbSys".tbViewRegis cascade ;
216
217drop sequence if exists "dbSys".tbViewRegis_Seq;
218create SEQUENCE "dbSys".tbViewRegis_Seq;
219
220CREATE UNLOGGED TABLE IF NOT EXISTS "dbSys".tbViewRegis (
221 ID int not null default nextval('"dbSys".tbViewRegis_seq') primary key
222 ,Sch text not null
223 ,Nam text not null
224 ,acc int not null
225 -- ,del_sysdata boolean not null default true
226 ,DataLimit int null
227 ,DataCount int null
228 ,viewregistrationid int references "dbSys".tbviewregistration on delete cascade
229 ,mvTbl text null
230
231
232 ,unique( sch,nam,acc )
233);
234alter SEQUENCE "dbSys".tbViewRegis_Seq owned by "dbSys".tbViewRegis.ID;
235
236/**********************************
237 Trigger : Delete view
238
239 --select * from "dbSys".tbViewRegis where nam= 'tbourtvw12';
240
241**********************************/
242create or replace function "dbSys".tbViewRegis_DeleteView () returns trigger as $$
243begin
244
245
246 execute format('drop view if exists %I.%s cascade', old.sch, old.nam);
247
248 if old.mvtbl is not null then
249
250 -- raise '102xx old.mvtbl"%",old.name"%" ', old.mvtbl,old.nam;
251 --* Delete mv if it's owner
252 if position(lower(old.nam) in lower( old.mvtbl))>0 then -- tbcourtvw12 in "dbLegal".tbcourtvw12_mv
253
254 execute format('drop table if exists %s cascade', old.mvtbl );
255 end if;
256 end if;
257 -- raise '1395 havetable"%"', "dbSys".havetable('"dbLegal".tbcourtvw4_mv');
258 return old;
259end $$ language plpgsql;
260
261drop trigger if exists tbviewregis_deleteview on "dbSys".tbviewregis cascade;
262create trigger tbviewregis_deleteview before delete on "dbSys".tbviewregis
263 for each row execute procedure "dbSys".tbViewRegis_DeleteView ();
264
265
266
267/***************************
268 * Table : Keep trigger created by the view
269 *
270 * Wut,2017-09-30
271 *
272 *
273 * *************************/
274drop table if exists "dbSys".tbVwRegisRG cascade ;
275CREATE UNLOGGED TABLE IF NOT EXISTS "dbSys".tbVwRegisRG (
276--CREATE TABLE IF NOT EXISTS "dbSys".tbVwRegisRG (
277 id serial primary key
278 -- ,sch text null
279 -- ,nam text null
280 ,tgname text null
281 ,dataset text null
282 ,vwname text null
283 ,istable boolean null
284 ,pid int not null references "dbSys".tbviewregis on delete cascade
285 ,unique(pid, tgname)
286);
287
288
289/**********************************
290 Trigger : Delete trigger create by this view, but binding with other dataset
291
292 Wut, 2017-09-30
293
294 select * from "dbSys".tbVwRegisRG;
295
296**********************************/
297
298create or replace function "dbSys".tbVwRegisRG_DropTG () returns trigger as $$
299begin
300-- raise exception '139 vwname"%"',old.viewname;
301 if old.vwname is not null then
302 execute format('drop view if exists %s', old.vwname ) ;
303 end if;
304
305 if old.istable then
306 execute format ('drop trigger if exists %s on %s', old.tgname, old.dataset);
307
308 end if;
309 return old;
310end $$ language plpgsql;
311
312drop trigger if exists tbVwRegisRG_DropTG on "dbSys".tbVwRegisRG cascade;
313create trigger tbVwRegisRG_DropTG after delete on "dbSys".tbVwRegisRG
314 for each row execute procedure "dbSys".tbVwRegisRG_DropTG ();