· 6 years ago · Sep 28, 2019, 02:34 AM
1/*****************************
2 Type : Search result for fts view
3
4 Wut 2018-08-8
5
6 ***************************/
7drop type if exists "dbSys".fnfts_search_tbinfo_type cascade;
8create type "dbSys".fnfts_search_tbinfo_type as (
9 sch text
10 ,nam text
11 ,fieldname text
12 ,ftstbl text
13 ,viewregistrationid int
14 ,fts boolean
15 ,schper text
16 ,namper text
17 ,tblper text -- format('"%s".%s', b1.sch, b1.nam ) tblper
18 ,tblvw text -- format('"%s".%s', b.sch, b.nam ) tblvw
19 ,tblnovw text --format('"%s".%s', b.sch, movstr_right( b.nam,'vw') ) tblnovw
20 ,ftsfk_tables text
21 ,ftsfk_columns text
22 ,ftsfk_views text
23 ,ftsfk_column_names text
24 ,can_search text
25 ,matview boolean
26 ,mvaccid_share int
27 ,viewregisid int
28 ,tk_viewregisid_main int
29 ,tk_viewregis_tbl text
30 ,nam_create_view text
31 ,tk_assg boolean
32 ,tk_ftsassg boolean
33 , tk_tkassg_ida int[]
34
35);
36
37/************************************
38 * Function : Explain how it search
39 * Wut, 2018-09-09
40 '
41 select "dbSys".fnfts_debug_info( 0, debug, deep,userid,accid,textsc,tbl,tblorg,tb.matview );
42 ************************************/
43--drop function if exists "dbSys".fnfts_debug_info(
44drop function if exists "dbSys".fnfts_debug_info(
45 inout lineno int
46 ,inout step numeric
47 ,inout debug boolean
48
49 ,inout deep int
50 ---------------------------
51 ,inout userid int
52 ,inout accid int
53 ,inout textsc text
54 ,inout tbl text
55 ,inout tborg text
56 ,inout matview boolean
57 ,in _ida int[]
58 ,in _title text -- Keyword : 'Enter', 'Exit','FK', 'Child'
59 ,in _tcustom text -- description
60 ,in _explain boolean
61
62
63 );
64create or replace function "dbSys".fnfts_debug_info(
65 inout lineno int
66 ,inout step numeric -- offset, select to_char(1.0, '0.0' )
67 ,inout debug boolean
68 ,inout deep int
69 ---------------------------
70 ,inout userid int
71 ,inout accid int
72 ,inout textsc text
73 ,inout tbl text
74 ,inout tborg text
75 ,inout matview boolean
76 ,in _ida int[] = null
77 ,in _title text = null -- 'Enter', 'Exit','FK', 'Child'
78 ,in _tcustom text =null
79 ,in _explain boolean = false
80
81
82 ) returns record as $$
83 declare ok boolean;
84begin
85 if not debug then
86 return;
87 end if;
88 /*
89 raise notice 'Line#%,%step % ,userid %,accid %,textsc %,tbl %,tblorg %,matview %'
90 ,lineno
91 ,repeat(' ',2*deep),step
92 , userid
93 , accid
94 , textsc
95 , tbl
96 , tborg
97 , matview
98 ;
99 */
100 ok:= case
101 when not _explain then case
102 when _ida is null then false
103 else true
104 end
105 else true
106 end;
107 --if ok then
108 if true then
109 raise notice '#%-%-% % tbl=%,_ids=%,%'
110 , lpad( lineno::text, 4,'0')
111 ,deep
112 , to_char(step, '0.0' ) -- step
113 ,repeat(' ',2 * (deep ))
114 ,tbl
115 ,array_to_string(_ida,',')
116 ,concat_ws(':',_title,_tcustom)
117 ;
118 end if;
119 if _title = 'Exit' and ok then
120 raise notice '#%-%-% % %'
121 , lpad( 0::text, 4,'0')
122 ,deep
123 , to_char(0, '0.0' ) -- step
124 ,repeat(' ',2 * (deep ))
125 ,repeat('-',50)
126 ;
127
128 end if;
129 return;
130end $$ language plpgsql;
131
132
133
134 /**************************
135 * Function : get fts view information
136
137 Test :
138 select a.* from "dbSys".fnfts_search_get_ftsinfo( 48,12,'"dbLegal".trcasetranassetVW12' ) a;
139
140
141 * Wut, 2018-08-08
142 **********************************/
143drop function if exists "dbSys".fnfts_search_get_ftsinfo(
144 userid int
145 , accid int
146 , in tbl text
147 ,out tb "dbSys".fnfts_search_tbinfo_type
148
149) cascade;
150
151create or replace function "dbSys".fnfts_search_get_ftsinfo(
152 userid int
153 , accid int
154 , in tbl text
155
156 ,out tb "dbSys".fnfts_search_tbinfo_type
157
158) returns "dbSys".fnfts_search_tbinfo_type as $$
159declare sql text; tblvw99 text; tb_tblnovw text;
160begin
161
162
163 -- -* Get view infor
164 execute $x$
165 select a.sch
166 ,lower(a.nam) nam
167 -- , ltrim( lower(nam),'tb' ) fieldname
168 ,a1.ftsfn fieldname
169 ,a1.ftstbl
170
171 ,b.id viewregistrationid
172 ,b.fts
173 ,b1.sch schper
174 ,b1.nam namper
175 ,format('"%s".%s', b1.sch, b1.nam ) tblper
176 ,format('"%s".%s', b.sch, b.nam ) tblvw
177 ,format('"%s".%s', b.sch, movstr_right( b.nam,'vw') ) tblnovw
178 ,b.ftsfk_tables
179 ,b.ftsfk_columns
180 ,b.ftsfk_views
181 ,b.ftsfk_column_names
182 ,b1.fts is null can_search
183 ,b.matview
184 ,b.mvaccid_share
185 -- from "dbSys".getTableSchName( tbl ) a
186 from "dbSys".getTableSchName( $1 ) a
187
188 left join "dbSys".tbviewregis a1 on a1.sch = a.sch and a1.nam = lower(a.nam)
189 left join "dbSys".tbviewregistration b on b.id = a1.viewregistrationid
190 left join "dbSys".tbtblregis b1 on b1.id = b.tblregis_id
191 $x$ using tbl
192 into tb;
193
194end $$ language plpgsql stable ;
195
196 /**************************
197 * Function : get view information
198
199 Test :
200 select a.* from "dbSys".fnfts_search_create_fts( 48,12,'"dbLegal".trcasetranassetVW12' ) a;
201
202
203 * Wut, 2018-08-08
204 **********************************/
205drop function if exists "dbSys".fnfts_search_create_fts(
206 userid int
207 , accid int
208 , in tbl text
209
210 ,out tb "dbSys".fnfts_search_tbinfo_type
211
212) cascade;
213
214create or replace function "dbSys".fnfts_search_create_fts(
215 userid int
216 , accid int
217 , in tbl text
218
219 ,out tb "dbSys".fnfts_search_tbinfo_type
220
221) returns "dbSys".fnfts_search_tbinfo_type as $$
222declare sql text; tblvw99 text; tb_tblnovw text;
223begin
224 --* Creat view and enable search , optional
225 --if enable_search and tb is not null then
226 tb_tblnovw := movstr_right(tbl,'vw');
227 if not emptystr( tb_tblnovw) then
228 --* create view if none
229 if not "dbSys".havetable( tbl ) then
230 -- raise notice ' 0378:create view %', tb_tblnovw;
231 --perform "dbSys".tvcreateview( userid,accid, quote_literal( movstr_right(tbl,'vw') ) );
232 sql = format('select "dbSys".tvcreateview( %s,%s,%L )', userid, accid, tb_tblnovw );
233 begin
234 execute sql into tblvw99 ;
235 exception
236 when others then raise '0382:%,tblnovw=%,tbl %, sql=%',sqlerrm,tb_tblnovw,tbl, sql;
237 end;
238 -- raise '0376:tblnovw %, have view % = %', movstr_right(tbl,'vw'),tblvw99, "dbSys".havetable( tblvw99 );
239 if not "dbSys".havetable( tblvw99 ) then
240 raise exception '0386: Can not create view, sql=%', sql;
241 end if;
242 end if;
243 -- raise notice '0228:tb_tblnovw %', tb_tblnovw;
244 --* Enable search
245 perform "dbSys".recreate_views( _accid:= accid , _tbl :=tb_tblnovw, _fts:=true );
246 end if;
247
248 -- -* Get view infor
249 select a.* from "dbSys".fnfts_search_get_ftsinfo(userid, accid, tbl) a
250
251 into tb;
252
253end $$ language plpgsql ;
254
255
256/********************************
257 * Function : Search FTS file
258 * Wut,2018-09-11
259 *
260 Usage :
261 select * from "dbSys".fnfts_search( 41,4, '"dbContact".tbcontactvw4' ,'เชียงใหม่');
262
263 **********************************/
264
265
266create or replace function "dbSys".fnfts_searchdata(
267 in userid int
268 ,in accid int
269 , in ssorg text
270 ,in tb "dbSys".fnfts_search_tbinfo_type
271-- ,in tblpar text
272 ,in child boolean
273-- ,in expl boolea
274
275 ----------------------
276-- ,inout parsearched text
277 ,out ida int[]
278 ,out ids text
279
280
281) returns record as $$
282declare sql text; ss text;
283begin
284
285 ss:= ssorg;
286 sql :=format(
287 'select a.id from %s a where %s '
288 , tb.ftstbl ,ss
289 );
290 --raise '0067:sql=%',sql;
291 sql:= format('
292 select array_agg(distinct a.id), array_to_string(array_agg(distinct a.id),%L) from (
293 %s
294 ) a
295 ', ','
296 ,sql
297 ) ;
298
299 begin
300 execute sql into ida , ids ;
301 exception
302 when others then raise exception '0411:%,sql=%, tb.ftstb=%, child %',sqlerrm, sql, tb.ftstbl ,child;
303
304 end ;
305 -- raise '0287: sql=%', sql;
306 -- perform "dbSys".fnfts_debug_info( 0674, 0.2::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _title:='Search'::text,_tcustom:=format('sql=%s',sql) );
307
308 --*
309 /*
310 -- if child then
311
312 if tblpar is not null then
313 parsearched := array_append ( parsearched , tblpar );
314 end if;
315 */
316
317 --* validate ,if it's right for the user
318 if ida is not null then
319 if not child then
320 -- select array_agg(a.id) from "dbContact".tbcontact_userdata(userid,accid,'"dbContact.tbcontact' ) a where a.id in ida into idav ;
321 execute format ('select array_agg(a.id) from %s_userdata(
322 1 -- %s
323 ,%s
324 ,%L ) a --A
325 where a.id in (%s)' --B
326 ,userid, accid, tb.tblnovw, tb.tblnovw --A
327 -- ,1, accid, tb.tblnovw, tb.tblnovw --A
328 , ids --B
329 )
330 into ida ;
331 -- into idav ;
332
333 /*
334 --if array_length(idav,1) = 0 then -- select array_length(null::int[],1)
335 if array_length(idav,1) = 0 or array_length(idav::int[],1) is null then -- select array_length(null::int[],1)
336
337 ida = null;
338 elseif array_length(idav,1) < array_length(ida,1) then
339 raise notice '0588: some found record is not for user "%"', userid;
340 end if;
341 */
342 ida := idav;
343 ids := array_to_string(ida,',');
344 end if;
345
346
347 end if; -- if ida is not null then -----
348
349
350end $$ language plpgsql stable ;
351
352
353 /***************************************************************************************************
354 Function : Full Text Search
355
356 Description: Search data source (view) directly and return set of record id. Support search all child and foreign data. Search stop once it
357 found matched data. It keep hit data to each datasource for prioritize when make child search.
358 Input :
359 userid
360 accid
361 tbl - datasource or view .
362 texts : input search text.
363 textc : input search text that double space is trimmed. This is used in searching and recursive call.
364 child : flag telling the whether datasource is child.
365 warn : Raise exception if no search is not successful due to no correspodent search table, warning by default.
366 Output :
367 ida :array of found record id
368 ids :text of found record id , spearated by comman
369 sql :search sql
370 Wut, 2018-04-07
371 ------------------------------------------------------------------------------
372 Test Case : dbContact.tbAddrVW_DATA3_fts.sql
373 select * from "dbSys".fnfts_search( 41,4,'"dbContact".tbaddrvw4', '332');
374 select unnest( ida) from "dbSys".fnfts_search( 41,4,'"dbContact".tbaddrvw4', 'อุดมสุข| อุดมสุข');
375 select unnest( ida) from "dbSys".fnfts_search( 41,4,'"dbContact".tbaddrvw4', 'อุดมสุข เชียงใหม่ | Sukhumvit ');
376 Test Case : dbLegal.trcaseassetVW_DATA4_fts.sql
377 select unnest( ida ) from "dbSys".fnfts_search( 41,4,'"dbContact".tbcontactvw4', 'contact11', warn:=false ) ;
378 select unnest( ida ) from "dbSys".fnfts_search( 41,4,'"dbLegal".trcaseassetpartvw4', 'contact11', warn:=false)
379 select unnest( ida ) from "dbSys".fnfts_search( 41,4,'"dbLegal".trcaseassetvw4', 'contact11', warn:=false) ;
380
381 select unnest( ida ) from "dbSys".fnfts_search( 48,12,'"dbContact".tbpostcodevw12', 'เชียงใหม่' ) ;
382
383 ******************************************************************************************/
384drop function if exists "dbSys".fnfts_search(
385 in userid int
386 ,in accid int
387 ,in tbl text --"dbContact".tbaddrvw4
388 ,in texts text
389 ,in textsc text
390 ,in child boolean
391 ,in warn boolean
392
393 ,out ida int[]
394 ,out ids text
395 ,out sql text
396
397);
398
399drop function if exists "dbSys".fnfts_search(
400 in userid int
401 ,in accid int
402 ,in tbl text --"dbContact".tbaddrvw4
403 ,in texts text
404 ,in textsc text
405 ,in child boolean
406 ,in warn boolean
407 ,in deep int
408 ,out ida int[]
409 ,out ids text
410 ,out sql text
411
412);
413drop function if exists "dbSys".fnfts_search(
414 in userid int
415 ,in accid int
416 ,in tbl text --"dbContact".tbaddrvw4
417 ,in texts text
418 ,in textsc text
419 ,in child boolean
420 ,in warn boolean
421 ,in deep int
422 ,in tblsearched text[]
423
424
425);
426drop function if exists "dbSys".fnfts_search(
427 in userid int
428 ,in accid int
429 ,in tbl text --"dbContact".tbaddrvw4
430 ,in texts text
431 ,in textsc text
432 ,in child boolean
433 ,in warn boolean
434 ,in deep int
435 ,in tblsearched text[]
436 ,in debug boolean
437
438
439);
440
441drop function if exists "dbSys".fnfts_search(
442 in userid int
443 ,in accid int
444 ,in tbl text --"dbContact".tbaddrvw4
445 ,in texts text
446 ,in textsc text
447 ,in child boolean
448 ,in warn boolean
449 ,in deep int
450 ,inout tblsearched text[] -- searched and not fou
451 ,in debug boolean
452
453
454) ;
455
456drop function if exists "dbSys".fnfts_search(
457 in userid int
458 ,in accid int
459 ,in tbl text --"dbContact".tbaddrvw4
460 ,in texts text
461 ,in textsc text
462 ,in child boolean
463 ,in warn boolean
464 ,in deep int
465 ,in debug boolean
466 ,inout parsearched text[]
467 ,inout tblsearched_notfound text[] -- searched and not found, NO LONGER implement
468 ,inout tblpar text
469 ,inout tblorg text
470
471) ;
472
473drop function if exists "dbSys".fnfts_search(
474 in userid int
475 ,in accid int
476 ,in tbl text --"dbContact".tbaddrvw4
477 ,in texts text
478 ,in textsc text
479 ,in child boolean
480 ,in warn boolean
481 ,in deep int
482 ,in debug boolean
483 ,inout parsearched text[]
484 ,inout tblsearched_notfound text[]
485 ,inout tblpar text
486 ,inout tblorg text
487 ,inout expl boolean
488
489 ,out ida int[]
490 ,out ids text
491 ,out sql text
492 ,out tblFound text
493
494);
495
496drop function if exists "dbSys".fnfts_search(
497 in userid int
498 ,in accid int
499 ,in tbl text --"dbContact".tbaddrvw4
500 ,in texts text
501 ,in textsc text
502 ,in child boolean
503 ,in warn boolean
504 ,in deep int
505 ,in debug boolean
506 ,in search_parsearched boolean
507 ,inout parsearched text[]
508 ,inout tblsearched_notfound text[]
509 ,inout tblpar text
510 ,inout tblorg text
511 ,inout expl boolean
512
513 ,out ida int[]
514 ,out ids text
515 ,out sql text
516 ,out tblFound text
517
518);
519DROP FUNCTION if exists "dbSys".fnfts_search(integer, integer, text, text, text, boolean, boolean, integer, boolean, boolean, boolean, text[], text[], text, text, boolean);
520DROP FUNCTION if exists "dbSys".fnfts_search(integer, integer, text, text, text, boolean, boolean, integer, boolean, boolean, boolean, boolean, text[], text[], text, text, boolean);
521
522create or replace function "dbSys".fnfts_search(
523 in userid int
524 ,in accid int
525 ,in tbl text --"dbContact".tbaddrvw4
526 ,in texts text
527 ,in textsc text = null
528 ,in child boolean = null
529 ,in warn boolean = false
530 ,in deep int= null
531 ,in debug boolean = false
532 ,in search_parsearched boolean = true -- Keep search even have searched it parent,
533 ,in _ilike boolean = true
534 ,in tbl_only boolean = false
535
536 ,inout parsearched text[] = null
537 ,inout tblsearched_notfound text[] = null -- searched and not found, NO LONGER implement
538 ,inout tblpar text = null
539 ,inout tblorg text = null
540 ,inout expl boolean = false
541
542
543 ,out ida int[]
544 ,out ids text
545 ,out sql text
546 ,out tblFound text
547
548) returns record as $$
549declare
550 sora text[]; s text; ss text; ssorg text;
551 ok boolean; dt timestamp_wtz; sd date ;sd_attr record;
552 -- tb record;
553 tb "dbSys".fnfts_search_tbinfo_type;
554 tbchild "dbSys".fnfts_search_tbinfo_type;
555 tbsave "dbSys".fnfts_search_tbinfo_type;
556
557 s_and text;
558 sora_and text[]; ss_or text;
559 crec record;
560 sqlrun text;
561 elstart timestamp_wtz;
562 fk record;
563 -- _warn boolean = warn;
564 idav int[];
565 Searched_and_Notfound boolean;
566 searched_by_par boolean ;
567 ida_found int[];
568 idaf int[]; idsf text;
569 i int ;
570 idat int[];
571 -- tblvw99 text;
572 ida_fk int[];
573 ids_fk text;
574 element_have_and boolean;
575-- _mvaccid_share boolean;
576 find record;
577
578begin
579
580
581 --* deep count and check
582 deep = case
583 when deep is null then 0
584 else deep +1
585 end;
586
587 if deep = 30 then
588 raise exception '0337:Limit deep=%,tbl % ',deep,tbl;
589 end if;
590
591
592 --* Get view infor
593 select
594 a.sch
595 ,lower(a.nam) nam
596 -- , ltrim( lower(nam),'tb' ) fieldname
597 ,a1.ftsfn fieldname
598 ,a1.ftstbl
599 ,b.id viewregistrationid
600 ,b.fts
601 ,b1.sch schper
602 ,b1.nam namper
603 ,format('"%s".%s', b1.sch, b1.nam ) tblper
604 ,format('"%s".%s', b.sch, b.nam ) tblvw
605 ,format('"%s".%s', b.sch, movstr_right( b.nam,'vw') ) tblnovw
606 ,b.ftsfk_tables
607 ,b.ftsfk_columns
608 ,b.ftsfk_views
609 ,b.ftsfk_column_names
610 ,b1.fts is null can_search
611 ,b.matview
612 ,b.mvaccid_share
613 ,a1.id viewregisid
614 ,a1.tk_viewregisid_main
615 ,case when a1.tk_viewregisid_main is not null and a11.sch is not null then format('"%s".%s', a11.sch, a11.nam ) else null::text end tk_viewregis_tbl
616 ,a1.nam_create_view
617 ,a1.tk_assg
618 ,case when a1.tk_ftsassg is null then false else a1.tk_ftsassg end tk_ftsassg
619 ,a1.tk_tkassg_ida
620 from "dbSys".getTableSchName( tbl ) a
621 left join "dbSys".tbviewregis a1 on a1.sch = a.sch and a1.nam = lower(a.nam)
622 left join "dbSys".tbviewregis a11 on a11.id = a1.tk_viewregisid_main
623 left join "dbSys".tbviewregistration b on b.id = a1.viewregistrationid
624 left join "dbSys".tbtblregis b1 on b1.id = b.tblregis_id
625 into tb;
626-- raise '0623';
627-- raise '0623: tb.tk_tkassg_ida %', tb.tk_tkassg_ida;
628--
629 begin
630 select tb.* into tbsave;
631 exception
632 when others then raise '0622: %, %',sqlerrm, 'assigning tbsave';
633 end;
634
635-- raise '0616: tk_viewregisid_main %,tb.viewregisid %, tb.tk_viewregis_tbl %, tb.tk_tkassg_ida %',tb.tk_viewregisid_main, tb.viewregisid, tb.tk_viewregis_tbl, tb.tk_tkassg_ida;
636 -- raise '0636: tk_ftsassg %, tb.tk_assg %',tb. tk_ftsassg, tb.tk_assg;
637
638-- raise '0638:tbl %, tk_ftsassg %, tb.tk_assg %', tbl, tb. tk_ftsassg, tb.tk_assg;
639
640 --* FTS assignment
641 if tb.tk_viewregisid_main is not null
642 and tb. tk_ftsassg
643 and not (position( 'last:' in lower(texts))>0 or position( 'date:' in lower(texts))>0 )
644 then
645
646 --* create view if it's gone
647 if tb.tk_viewregis_tbl is null then
648 -- raise '0625 nam_create_view %,format() %', tb.nam_create_view, format('"%s".%s',tb.sch , tb.nam_create_view);
649 -- perform "dbSys".tvcreateview(userid,accid,'"dbLegal".trcasetrn040',dropit:=true );
650
651 begin
652 perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
653 exception
654 when others then raise '0638:%,tb.nam_create_view=%,tbl %, ',sqlerrm,tb.nam_create_view,tbl l;
655 end;
656
657 --* Make new calling using original view
658 tb.tk_viewregis_tbl = tbl;
659 -- raise '0635: tb.tk_viewregis_tbl %', tb.tk_viewregis_tbl;
660 end if;
661 -- raise '0658: tb.tk_viewregis_tbl %', tb.tk_viewregis_tbl;
662 select a.ida, a.ids, a.sql ,a.tblsearched_notfound , a.parsearched
663 from "dbSys".fnfts_search(
664 userid
665 , accid
666 , tb.tk_viewregis_tbl -- tbl
667 , texts
668 , textsc , child , warn, deep ,debug, search_parsearched,_ilike
669 , tbl_only
670 , parsearched
671 ,tblsearched_notfound -- searched and not found, NO LONGER implement
672 ,tblpar
673 ,tblorg
674 ,expl
675 ) a
676 into ida, ids, sql ,tblsearched_notfound, parsearched
677 ;
678 begin
679 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, ida );
680 -- raise '0644: tblvw %, tbsave.viewregisid %, ida % ', tb.tblnovw, tbsave.viewregisid, ida ;
681 exception
682 when others then raise '1370:%, tbsave.viewregistrationid %, tb.nam_create_view %',sqlerrm, tbsave.viewregistrationid, tb.nam_create_view;
683 end;
684 /*
685 begin
686 perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
687 exception
688 when others then raise '0685:%, tbsave.viewregistrationid %, tb.nam_create_view %',sqlerrm, tbsave.viewregistrationid, tb.nam_create_view;
689
690 end ;
691 */
692 return;
693
694 end if;
695
696
697-- raise '0692:tbl %, tk_ftsassg %, tb.tk_assg %', tbl, tb. tk_ftsassg, tb.tk_assg;
698
699 --* Task Assignment
700 if tbsave.tk_assg then
701
702 --raise '0681: tb.tk_tkassg_ida %, tb.ftstbl %, tbl %', tb.tk_tkassg_ida, tb.ftstbl, tbl;
703 -- raise '0681: tb.ftstbl %, tbl %', tbsave.ftstbl, tbl ;
704 --* recreate if have searched data
705 if tb.tk_tkassg_ida is not null then
706 /*
707 begin
708 perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
709 exception
710 when others then raise '0747:%m tb.nam_create_view %',sqlerrm, tb.nam_create_view;
711 end;
712 */
713 end if;
714 --* user this assign view to search
715 --tb.ftstbl = tbl;
716
717
718 end if;
719 -- raise '0613:tbsave.viewregisid %', tbsave.viewregisid;
720
721 --* table name must be lower case
722 tbl = format('"%s".%s',tb.sch,tb.nam );
723
724-- raise '0609: tbl %', tbl;
725 --*
726
727 --_mvaccid_share=tb.mvaccid_share;
728 -- select a.* from "dbSys".fnfts_search_get_ftsinfo(userid, accid, tbl) a into tb;
729
730-- tbl:= format('"%s".%s',tb.sch, tb.nam ); -- lower tablename
731
732 -- raise '0309:tbl %, tb.sch %, tb.nam %, tb.ftstbl %', tbl, tb.sch,tb.nam, tb.ftstbl;
733
734 --*
735 if tblorg is null then
736 tblorg := tbl;
737 end if;
738
739 --raise '0324 texts %', texts;
740
741
742 -- select ltrimf('tbaddr','tb', '' ); -- addr
743 -- select ltrimf('tbbaddr','tb'); -- badd
744 -- raise '0019: tb.fieldname %,texts %, texts is not null %',tb.fieldname, texts, texts is not null;
745
746 --* Remove double space
747 if textsc is null then
748 if texts is not null then
749 -- raise '0461: tb.fieldname %,texts %, texts is not null %',tb.fieldname, texts, texts is not null;
750
751 -- raise '0744: tk_ftsassg %, tb.tk_assg %',tb. tk_ftsassg, tb.tk_assg;
752
753 --*
754 if position( 'last:' in lower(texts))>0 or position( 'date:' in lower(texts))>0 then
755 --* Task assignment: refresh data to orginal
756 if tbsave.tk_assg and ( not tbsave.tk_ftsassg ) then
757 -- select array_agg(id) from "dbLegal".trcasetrn040VW179_51451 where id = any(ida)
758
759 -- raise '0748:tbsave. tk_tkassg_ida %, tbsave.viewregisid %',tbsave. tk_tkassg_ida, tbsave.viewregisid;
760 if tbsave. tk_tkassg_ida is not null then
761 -- raise '0750: tbsave.viewregisid %', tbsave.viewregisid;
762 --* clear existing ida
763 begin
764 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, null );
765 exception
766 when others then raise '0998:%, tbsave.viewregistrationid %',sqlerrm, tbsave.viewregistrationid;
767 end;
768 --perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid, child_only:=true );
769 -- perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view));
770 end if;
771 end if;
772 end if;
773
774 --* Get last data
775 -- if texts = 'last:20' then
776 if position( 'last:' in lower(texts))>0 or position( 'date:' in lower(texts))>0 then
777
778 --*
779 select split_part( lower(texts),':',2 ) no_item into find ;
780 -- raise '0743:find.no_item %', find.no_item;
781
782 -- select * from "dbLegal".trcasetran -- upddt
783 if "dbSys".havecolumn( tb.tblper, 'upddt' )
784 -- and "dbSys".havecolumn( tbl, 'upddt' )
785 then s= 'upddt';
786 -- elseif "dbSys".havecolumn( tb.tblper, 'upd' ) and "dbSys".havecolumn( tbl, 'upd' ) then s= 'upd';
787 -- elseif "dbSys".havecolumn( tb.tblper, 'trandate' ) and "dbSys".havecolumn( tbl, 'trandate' ) then s= 'trandate';
788
789 else s = 'id';
790 end if;
791
792 --*
793 -- raise '0782: tk_ftsassg %, tb.tk_assg %',tb. tk_ftsassg, tb.tk_assg;
794 -- raise '0782: tk_ftsassg %, tb.tk_assg %',tbsave. tk_ftsassg, tbsave.tk_assg;
795
796 if position( 'last:' in lower(texts))>0 then
797 sql = format('
798 select array_agg(a.id ) from (
799 select a.id from %s a
800 left join %s b on b.id = a.id -- A-1
801 where a.acc= $1
802 order by b.%s desc --A
803 limit %s --B
804 ) a
805'
806 --,case when not tb.tk_assg then tb.tblper else tbl end
807 ,case when tb.tk_ftsassg then tb.tblper else tbl end
808
809 , tb.tblper --A-1
810 , s --A
811 , find.no_item --B
812
813 ) ;
814 elseif position( 'date:' in lower(texts))>0 then
815 select split_part( texts,':',2 ) date_part into find;
816 select split_part( find.date_part,'-',1 ) date1 , split_part( find.date_part,'-',2 ) date2 into find;
817
818 -- raise '0796: % - % ', find.date1, find.date2;
819
820 sql = format('
821 select array_agg(a.id ) from(
822 select a.id from %s a
823 left join %s b on b.id = a.id -- A-2
824
825 where a.acc= $1
826
827 and b.%s between %L and %L --A-1
828 order by b.%s desc --A
829 -- limit 100 --B
830 ) a
831'
832 -- ,case when not tb.tk_assg then tb.tblper else tbl end
833 ,case when tb.tk_ftsassg then tb.tblper else tbl end
834
835 , tb.tblper --A-2
836 ,s, ddmmyyyy_sl_2date( find.date1)::text , ddmmyyyy_sl_2date( find.date2 )::text --A-1
837
838 , s --A
839 -- , find.no_item --B
840
841 ) ;
842
843
844
845 end if;
846
847 -- raise '0767: ,sql=%',sql;
848
849 --*
850 begin
851 execute sql using accid into ida ;
852 exception
853 when others then raise '0796:%, sql=%',sqlerrm,sql;
854 end;
855
856 -- raise '0663: deep %, ida %,sql=%', deep, ida,sql;
857
858 if deep = 0 and
859
860 ida is not null and array_length(ida,1)>0 then
861 --if true then
862 begin
863 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, ida );
864 exception
865 when others then raise '0666:%',sqlerrm;
866 end;
867 -- raise '0728, ida %', ida ;
868
869 --* Both Task and FTS Assignment
870 -- if tbsave.tk_ftsassg then
871 if true then
872 -- if true then
873
874 --* drop child
875 perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid, child_only:=true );
876
877 --* then recreate parent
878 begin
879
880 perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
881 -- perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view) );
882
883 -- raise '0813';
884
885 exception
886 when others then raise '0747:%, tbl %, tb.nam_create_view %, tb.tk_assg %, tb.tk_ftsassg %',sqlerrm,tbl, tb.nam_create_view,tb.tk_assg, tb.tk_ftsassg;
887 end;
888 end if;
889
890 end if;
891
892 return;
893
894 end if;
895
896 textsc := strtrim_lr( texts );
897 else
898
899 end if;
900 end if;
901 -- raise '%0258: tb.ftstbl %, "dbSys".haveTable(tb.ftstbl) =%, tblnovw=%, textsc %',repeat(' ',deep*2), tb.ftstbl, "dbSys".haveTable(tb.ftstbl) ,tb.tblnovw, textsc;
902
903 --*
904 if not "dbSys".haveTable(tb.ftstbl) then
905
906 -- -* Get view infor
907 -- raise '0692: tb.fts %', tb.fts;
908 --* FTS View : Create and get info
909 if tb.fts then
910 begin
911 select a.* from "dbSys".fnfts_search_create_fts(userid, accid, tbl ) a
912 into tb;
913 exception
914 when others then raise '0708:%, tbl=%, tb.fts=% ',sqlerrm,tbl, tb.fts;
915 end ;
916 end if;
917
918 -- raise notice '% 0348: tbl %, tb.ftstbl %, "dbSys".haveTable(tb.ftstbl) =%, tblper :=%',repeat(' ',deep*2), tb.ftstbl,tbl, "dbSys".haveTable(tb.ftstbl) , format('"%s".%s',tb.schper, tb.namper);
919
920 end if;
921
922-- raise '0704: tb.ftstbl %', tb.ftstbl;
923 --* verify fts data does exisst --------------------
924 ok = case
925 when tb.ftstbl is null then false
926 when tb.fts is null then false
927 when not tb.fts then false
928 else true
929 end ;
930 --if ( tb.ftstbl is null) or (tb.fts is null or not tb.fts) then
931 if not ok then
932 s:= format( 'No FTS file for >%s<, tb.ftstbl is null, tb.schper %s, tb.namper %s', tbl, tb.schper, tb.namper );
933 if warn then
934
935 -- raise exception '0490: No FTS file for >%<, tb.ftstbl is null, tb.schper %, tb.namper % ', tbl, tb.schper, tb.namper;
936 raise exception '0490:%',s;
937
938 else
939
940 -- raise '0490: No FTS file for >%<, tb.ftstbl is null, tb.schper %, tb.namper % ', tbl, tb.schper, tb.namper;
941 perform "dbSys".fnfts_debug_info( 0490, 0::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=true
942 , _ida:=null, _title:='Return'::text,_tcustom:=s );
943
944 return ;
945 end if;
946 end if;
947 -----------------------------------------------------------------------------------------------
948 elstart:=TIMEOFDAY();
949 perform "dbSys".fnfts_debug_info( 0526, 0.0::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _title:='Enter' ::text );
950 -- raise '0119';
951 --* 1.OR ----------------------------
952 if position('|' in textsc )>0 then
953 -- raise '0722';
954 sora = string_to_array(textsc,'|' );
955
956 -- ss := format('a.tsv @@ to_tsquery(%L)', array_to_string( sora ,' | '));
957 -- 'อุดมสุข เชียงใหม่ | Sukhumvit '
958 element_have_and = false;
959 i=0;
960 foreach s in array sora loop
961 i:=i+1;
962 s = trim(s);
963 if position( ' ' in s ) >0 then
964 element_have_and = true;
965 exit;
966 end if;
967 end loop;
968 if not element_have_and then
969 i=0;
970 foreach s in array sora loop
971 i:=i+1;
972 s = trim(s);
973 sora[i] = "dbSys".fnfts_str2match( s );
974 end loop;
975 ss := format('a.tsv @@ to_tsquery(%L)', array_to_string( sora ,' | '));
976 ss_or := ss;
977 else
978 ss_or := null;
979 foreach s_and in array sora loop
980 begin
981 select a.ida, a.ids, a.sql ,a.tblsearched_notfound
982 from "dbSys".fnfts_search( userid, accid
983 , tbl
984 , null, s_and , child , warn, deep ,debug,search_parsearched,_ilike,tbl_only,parsearched, tblsearched_notfound ,tblpar ,tblorg
985 ) a
986 --into ida, ids, sql ,tblsearched_notfound ;
987 into idaf, idsf, sql ,tblsearched_notfound ;
988 --raise notice '0232:textsc %,s %, idaf %, idsf %, ida.length %, ida is null %',textsc,s_and, idaf, idsf, array_length(ida,1), ida is null ;
989 ida := array_int_add( ida,idaf ) ;
990 --ids := array_to_string(ida,',');
991 exception
992 when others then raise ' 0795:%,search tbl=%,child %', sqlerrm,tbl,child;
993 end;
994
995 end loop;
996 ids:= array_to_string(ida, ',');
997 return;
998 end if;
999
1000
1001 --* 2.AND --------------------
1002 else
1003 -- raise '0260 tbl %, textc "%"', tbl, textsc;
1004 if position (' ' in textsc ) > 0 then
1005 sora_and = string_to_array( textsc ,' ' );
1006 -- SELECT to_tsvector('The quick brown fox jumped over the lazy dog') @@ to_tsquery('fox & dog');
1007 -- ss := format('a.tsv @@ to_tsquery(%L)', array_to_string( sora_and ,' & '));
1008 i=0;
1009 foreach s in array sora_and loop
1010 i:=i+1;
1011 sora_and[i] = "dbSys".fnfts_str2match( s );
1012 end loop;
1013 ss := format('a.tsv @@ to_tsquery(%L)', array_to_string( sora_and ,' & '));
1014
1015 ss_or := ss;
1016 -- raise '0793:ss %',ss;
1017 --* 3.like ----------------------------
1018 else
1019 ss:= "dbSys".fnfts_ilike( textsc, tb.fieldname, _ilike ); -- a.xxx like 'yyyy%'
1020 ss_or := ss;
1021 end if;
1022 end if;
1023
1024 -- raise '0288 ss %,tb.ftstbl %', ss,tb.ftstbl;
1025
1026 --* 0.search data ---------------------------------------------------------------------------------------------------------------------------------
1027 perform "dbSys".fnfts_debug_info( 0674, 0.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _title:='Search'::text );
1028 ssorg = ss_or;
1029 if ssorg is not null then
1030 select a.ida, a.ids from "dbSys".fnfts_searchdata(
1031 userid
1032 ,accid
1033 , ssorg
1034 ,tb
1035 -- ,tblpar
1036 ,child
1037 -- ,expl
1038 -------------------
1039 -- ,parsearched
1040
1041 ) a
1042 into ida, ids;
1043 perform "dbSys".fnfts_debug_info( 0674, 0.2::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _title:='Search'::text,_tcustom:=format('sql=%s',sql) );
1044 -- raise '0575:ida %, tb %, ssorg %', ida, tb, ssorg;
1045
1046 --*
1047 if tbl_only then
1048 return;
1049 end if;
1050
1051 --* share a/c
1052 if ida is null then
1053 --if deep =1 then
1054 if accid <> 4 then
1055 -- raise '1119" length %, schper % namper %, ', array_length( ida, 1),tb.schper, tb.namper ;
1056 --if ida is null or array_length( ida, 1 ) =0 then
1057 -- if false then
1058 if tb.namper in ('tbcontact', 'tbpostcode') then
1059 /*
1060 select a.*
1061 ,format('"%s".%svw%s', a.fk_table_schema, a.fk_table_name, a.mvaccid_share) tbl_share
1062 from "dbSys".getForeigningKey_share( format('"%s".%s',tb.schper,tb.namper) ) a
1063 into fk;
1064 */
1065 -- fk.mvaccid_share "dbContact".tbcontact, tbl_share 4, tbl_share "dbContact".tbcontactvw4
1066
1067 select 4::int mvaccid_share, '"dbContact".tbcontactvw4'::text tbl_share into fk;
1068
1069 raise notice '0911: fk.mvaccid_share %, tbl_share %, tbl_share %', format('"%s".%s',tb.schper,tb.namper), fk.mvaccid_share, fk.tbl_share;
1070 if fk.mvaccid_share is not null then
1071
1072 if diff( fk.mvaccid_share, accid ) then
1073 -- raise notice '0935: tb.sch %, nam %, fk.tbl_share %, texts %, fk.mvaccid_share %', tb.sch, tb.nam,fk.tbl_share , texts , fk.mvaccid_share ;
1074 -- select unnest(ida) FROM "dbSys".fnfts_search(1,4,'"dbContact".tbcontactVW4','ศาลจังหวัดเชียงใหม่',_ilike:='1')
1075 --* search
1076 begin
1077 select a.ida, a.ids, a.sql ,a.tblsearched_notfound , a.parsearched
1078 from "dbSys".fnfts_search(
1079 1 -- userid
1080 ,fk.mvaccid_share -- accid
1081 ,fk.tbl_share -- tbl --"dbContact".tbaddrvw4
1082 , texts
1083 , textsc , child , warn, deep ,debug, search_parsearched,_ilike
1084 , true
1085 , parsearched,tblsearched_notfound ,tbl, tblorg
1086
1087 ) a
1088 into ida, ids, sql ,tblsearched_notfound, parsearched;
1089 -- raise '0618:isa %',ida;
1090 exception
1091 when others then raise '0948:%, fk.tbl_share %, texts %',sqlerrm, fk.tbl_share , texts;
1092 end;
1093
1094 return;
1095 end if;
1096 end if;
1097 end if;
1098
1099 end if;
1100 end if;
1101
1102 --* Task assignment
1103 if tbsave.tk_assg and ( not tbsave.tk_ftsassg ) then
1104 -- select array_agg(id) from "dbLegal".trcasetrn040VW179_51451 where id = any(ida)
1105
1106 -- raise '0965:ida %, tbsave.viewregisid %',ida, tbsave.viewregisid;
1107 if tbsave. tk_tkassg_ida is not null then
1108 -- raise '0994: tbsave.viewregisid %', tbsave.viewregisid;
1109 --* clear existing ida
1110 begin
1111 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, null );
1112 exception
1113 when others then raise '0998:%, tbsave.viewregistrationid %',sqlerrm, tbsave.viewregistrationid;
1114 end;
1115 --perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid, child_only:=true );
1116 -- perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view));
1117 end if;
1118 --* intersect the result ida with all task assignment ida
1119 execute format( 'select array_agg(id) from %s where id = any($1)', tbl ) using ida
1120 into ida;
1121 ids = array_to_string(ida,',');
1122
1123 -- raise '1009:ida %',ida;
1124 --* then save back
1125 /*
1126 begin
1127 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, ida );
1128 exception
1129 when others then raise '0964:%, tbsave.viewregistrationid %',sqlerrm, tbsave.viewregistrationid;
1130 end;
1131 perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid, child_only:=true );
1132 */
1133 -- raise '0946:ida %, tbsave.viewregisid %',ida, tbsave.viewregisid;
1134 end if;
1135
1136 --*
1137 if tblpar is not null then
1138 parsearched := array_append ( parsearched , tblpar );
1139 end if;
1140
1141 -- raise '0714: tb.sch %, tb.nam %, ida %, textsc %', tb.sch,tb.nam, ida , textsc;
1142
1143 perform "dbSys".fnfts_debug_info( 0845, 1.0::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl,_ida:=ida, _title:='FK'::text );
1144
1145 --* 1.Search foreign key, if not found ------------------------------------------------------------------------------------------------------------------------
1146 --if ida is null then
1147 if emptystr(ids ) then
1148 -- if false then
1149 --if ida is null and false then
1150
1151 -- raise '0832: tb.ftsfk_tables %', tb.ftsfk_tables;
1152
1153 if tb.ftsfk_tables is not null then
1154 -- raise '%', tb.ftsfk_tables;
1155 select string_to_array( tb.ftsfk_tables,',') tbls
1156 , string_to_array(tb.ftsfk_columns,',') cols
1157 , string_to_array(tb.ftsfk_views,',') tblvws
1158 , string_to_array(tb.ftsfk_column_names,',') col_names
1159 , 0::int c
1160 , null::text tb
1161 , null::text col_val
1162 , null::text tblvw9
1163 into fk;
1164
1165 -- if '"dbLegal".trcasetranasset' = any ( fk.tbls) then
1166 -- raise '0255:tbl %, fk.tbls %',tbl, fk.tbls;
1167 --end if;
1168
1169 -- raise ' 0492: FK Search,tbl=%, fk.col_names %, tb.ftsfk_tables %, fk.tbls %, fk.tblvws %',tbl, fk.col_names, tb.ftsfk_tables, fk.tbls, fk.tblvws;
1170 --raise '0253:%',
1171 foreach fk.tb in array fk.tbls loop
1172 fk.c := fk.c +1;
1173 fk.tblvw9 := fk.tblvws[fk.c]||accid ;
1174
1175 -- Searched_and_Notfound = false;
1176 Searched_and_Notfound =case
1177 when fk.tblvw9 = any( tblsearched_notfound) then true
1178 else false
1179 end;
1180
1181 --* is it already searched by parent ?
1182 if not search_parsearched then
1183 searched_by_par = case
1184 when fk.tblvw9 = any( parsearched ) then true
1185 else false
1186 end;
1187 else
1188 searched_by_par=false ;
1189 end if;
1190 --if tbl = '"dbLegal".tblawoffcasetrvw12' then
1191 --if fk.tblvws[fk.c] = '"dbLegal".tbcasestatusvw' then
1192
1193 --if fk.tbls[fk.c] = '"dbLegal".tbcasestatus' then
1194 --if fk.tbls[fk.c] = '"dbPM".tbproject' then
1195 --if tbl = '"dbPM".tbproject' then
1196 -- if tbl = '"dbContact".tbcontcontact' then
1197
1198 -- "dbLegal".tbcasestatus
1199 -- raise '0270: tbl %, fk.tblvw9 %,tblorg %, parsearched %, searched_by_par %, Searched_and_Notfound %', tbl, fk.tblvw9, tblorg,parsearched, searched_by_par, Searched_and_Notfound;
1200 --end if;
1201
1202 --if fk.tb = '"dbLegal".tbcasestatus' then
1203 -- if position( 'tbcasestatus' in fk.tb )>0 then
1204 -- raise notice '0271: fk.tb %,fk.tblvw9 % = any( tblsearched_notfound % ) =%, fk.tbls[fk.c] %, Searched_and_Notfound % , searched_by_par %, parsearched=% ',fk.tb, fk.tblvw9, tblsearched_notfound, fk.tblvw9 = any( tblsearched_notfound), fk.tbls[fk.c], Searched_and_Notfound , searched_by_par, parsearched ;
1205 --end if;
1206
1207
1208 if not emptystr( fk.tblvws[fk.c] )
1209 and not Searched_and_Notfound
1210 and not searched_by_par
1211 then -- select 'a' = any(null)
1212
1213 --* debug
1214 /*
1215 if fk.tblvws[fk.c] = '"dbLegal".tbcasestatusvw' then
1216 raise '0582:fk.tblvws[fk.c] %, tblsearched_notfound % tblawofficevw = any( tblsearched_notfound)=%', fk.tblvws[fk.c] ,array_to_string(tblsearched_notfound,','), '"dbLegal".tblawofficevw'= any( tblsearched_notfound);
1217 end if;*/
1218
1219 --execute format('select %s from "%s".%s where id = %s', fk.col_names[fk.c] , tb.schper, tb.namper, ) into field_val ;
1220 --
1221 -- raise notice '% 0467: FK Search: views99= %, fk.col_names[ fk.c] %,ida=%, parsearched=%,texts %, taxsc %',repeat(' ',deep*2), fk.tblvws[fk.c] || accid, fk.col_names[ fk.c],ida,parsearched ,texts, textsc;
1222
1223 perform "dbSys".fnfts_debug_info( 0845, 1.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl,_ida:=ida, _title:='FK'::text ,_tcustom:=format('col_names=%s',fk.col_names[fk.c ]) );
1224 -- perform "dbSys".fnfts_debug_info( 0845, 1.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_ida:=ida, _title:='FK'::text );
1225
1226 -- raise notice '0789: tbl %, textsc %', fk.tblvws[fk.c] || accid, textsc;
1227
1228 --* recursive call by search FK
1229 begin
1230 select a.ida, a.ids, a.sql ,a.tblsearched_notfound, a.parsearched
1231 from "dbSys".fnfts_search( userid, accid
1232 , fk.tblvws[fk.c] || accid
1233 --, null
1234 , texts
1235 , textsc
1236 , null , warn, deep ,debug,search_parsearched, _ilike,tbl_only,parsearched,tblsearched_notfound ,tbl ,tblorg ) a
1237 --into ida, ids, sql ,tblsearched_notfound;
1238 into idaf, idsf, sql ,tblsearched_notfound, parsearched ;
1239 exception
1240 when others then raise ' 0418:%,search foreign %,tbl=%,child %, textsc %, fk.tblvws[fk.c] %, accid %', sqlerrm, fk.tblvws[fk.c] ,tbl,child, textsc , fk.tblvws[fk.c], accid;
1241 end;
1242 perform "dbSys".fnfts_debug_info( 0857, 1.2::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _ida:=idaf, _title:='FK' ::text,_tcustom:='ida=idaf' );
1243
1244
1245 --if tbl = '"dbLegal".trcasetranorgvw179' then
1246 -- raise '0756:fk table %, ida %, idaf %, parsearched %', fk.tblvws[fk.c] || accid, ida, idaf,parsearched ;
1247 --end if;
1248 -- perform "dbSys".fnfts_debug_info( 0790, 1.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_ida:=ida, _title:='FK'::text ,_tcutom:='AFter recursive call' ::text );
1249 -- perform "dbSys".fnfts_debug_info( 0790, 1.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_ida:=ida );
1250
1251 --* if found
1252 --if ida is not null then
1253 --if idaf is not null then
1254 if not emptystr(idsf) then
1255 ida_found:= idaf;
1256 sqlrun := format(
1257 'select array_agg(distinct a.%s) ida ---A
1258 , array_to_string(array_agg(distinct a.%s),%L) ids -- B
1259 from "%s".%s a -- C
1260 where a.%s in ( %s ) --D
1261 and case
1262 when a.acc = %s then true -- E
1263 else case
1264 when %s is null then false --E1
1265 else equal( a.acc, %s ) --F
1266 end
1267 end
1268 -- group by a.%s --G
1269
1270 '
1271 ,'id' -- crec.pid_name --A
1272 ,'id',',' -- crec.pid_name, --- B
1273 , tb.schper, tb.namper --C
1274 -- , fk.col_names[fk.c] , ids --D
1275 , fk.col_names[fk.c] , idsf --D
1276 , accid -- E
1277 , fmval( tb.mvaccid_share ) --E1
1278 , fmval( tb.mvaccid_share ) --F
1279 , 'NA' --G crec.pid_name
1280 );
1281 -- raise '0995:sqlrun=%',sqlrun;
1282 begin
1283 -- execute sqlrun into ida, ids;
1284 execute sqlrun into idaf, idsf;
1285 exception
1286 -- when others then raise exception '0448:%,get data after search foreign, sqlrun=%',sqlerrm, sqlrun;
1287 when others then raise exception '0448:%,get data after search foreign,idaf=%, idsf=%,idaf is not null=% sqlrun=%',sqlerrm,idaf, idsf, idaf is not null,sqlrun;
1288 end ;
1289 -- perform "dbSys".fnfts_debug_info( 0857, 1.3::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview, _explain:=expl,_ida:=idaf, _title:='FK' ::text,_tcustom:= format( 'ida=idaf,sqlrun=%s',sqlrun ) );
1290 perform "dbSys".fnfts_debug_info( 0857, 1.3::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview, _explain:=expl,_ida:=idaf, _title:='FK' ::text,_tcustom:= format( 'ida=idaf' ) );
1291
1292 -- if tbl = '"dbLegal".trcasetranorgvw179' then
1293 -- raise '1257:fk table %, ida %, idaf %, parsearched %, sql=%', fk.tblvws[fk.c] || accid, ida, idaf,parsearched, sql ;
1294 -- end if;
1295 --if ida is not null then
1296 --if idaf is not null then
1297 if not emptystr( idsf ) then
1298
1299 -- raise notice '% 0794:found fk, nam %, ida %,idaf %, idsf %,sqlrun=%',repeat(' ',deep*2), fk.tblvws[fk.c] || accid, ida,idaf,idsf, sqlrun ;
1300 sql:=sqlrun;
1301
1302 ida := array_int_add( ida,idaf );
1303 ids := array_to_string(ida,',');
1304 -- exit;
1305 else
1306 -- * debug
1307 if debug then
1308 -- raise notice '1040: NOT found data from %.%, ,ida_found %, sqlrun=%',tb.schper, tb.namper, ida_found, sqlrun;
1309 end if;
1310 end if;
1311
1312 end if;
1313 --perform "dbSys".fnfts_debug_info( 0857, 1.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_ida:=ida::int[],_title:='FK' ::text );
1314 perform "dbSys".fnfts_debug_info( 0857, 1.4::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _ida:=ida::int[]
1315 --, _title:='FK' ::text
1316 ,_title := case
1317 when idsf is not null then -- found
1318 concat_ws('=','idsf', idsf )
1319 else --not found
1320
1321 --'NOT found' || concat_ws('idsf=',idsf) || concat_ws('sqlrun=',sqlrun )
1322 format('NOT found!, ida_found=%s, sqlrun=%s',ida_found,sqlrun)
1323 end
1324 );
1325
1326 else
1327 /*
1328 if fk.tbls[fk.c] = '"dbLegal".tbcasestatus' then
1329 -- "dbLegal".tbcasestatus
1330 raise '0359: tbl %, fk.tblvw9 %, parsearched %, searched_by_par %', tbl, fk.tblvw9, parsearched, searched_by_par;
1331 end if;
1332*/
1333
1334 -- exit;
1335
1336
1337 end if;
1338 end loop;
1339 -- raise '1299: tbl %, idsf %, ida %',tbl,idsf, ida ≈;
1340 end if;
1341 end if;
1342
1343 --* save result search by fk
1344 ida_fk := ida;
1345 ids_fk := ids;
1346 -- raise '1306: ida %',ida;
1347 perform "dbSys".fnfts_debug_info( 0845, 2.0 ::numeric, debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview, _explain:=expl,_ida :=ida,_title:='Child'::text );
1348
1349 --- * 2.search child , if not found ---------------------------------------------------------------------------------------------------------
1350 --if ida is null then
1351 -- if true then
1352 if emptystr(ids ) then
1353 -- if false then
1354 -- raise '1017: tb.sch %, tb.nam %, ida %, textsc %', tb.sch,tb.nam, ida , textsc;
1355
1356 for crec in execute $x$
1357 select c1.fts, c11.sch, c11.nam , c12.pid_name, c12.sch schper, c12.nam namper
1358 , c1.ftshits
1359 , format('"%s".%s',c1.sch,c1.nam) tblvw
1360 , format('"%s".%s',c1.sch,c1.nam)||a.acc tblvw9
1361 , c11.ftstbl
1362 from "dbSys".tbviewregis a
1363 left join "dbSys".tbviewregistration b on b.id = a.viewregistrationid
1364 left join "dbSys".tbtblregis c on c.tblregisid_par = b.tblregis_id and c.child
1365 left join "dbSys".tbviewregistration c1 on c1.tblregis_id = c.id
1366 left join "dbSys".tbviewregis c11 on c11.viewregistrationid = c1.id and c11.acc = a.acc
1367 left join "dbSys".tbtblregis c12 on c12.id = c1.tblregis_id
1368 where a.sch = $1 and a.nam = $2 and a.acc = $3
1369 --and c1.fts is not null
1370 and c1.fts=true
1371 and c.kind is null
1372 and c1.sch not in ('dbSysWKF')
1373 and case
1374 when a.tk_assg then c11.tk_assg and c11.uid = a.uid
1375 else c11.tk_assg is null or not c11.tk_assg
1376 end
1377 order by c1.ftshits desc
1378 $x$ using tb.sch, tb.nam, accid
1379
1380 loop
1381 --if crec.fts is null then
1382
1383 -- if true then
1384 --if crec.fts is not null then
1385 if crec.fts then
1386
1387 if true then
1388
1389 if crec.ftstbl is not null then
1390 if not "dbSys".havetable(crec.ftstbl) then
1391 -- if position('trcasetrandcvw4' in crec.tblvw9 )>0 then
1392 -- raise notice '0662:crec.ftstbl %, crec.tblvw9 % ', crec.ftstbl, crec.tblvw9 ;
1393 -- end if;
1394 -- select "dbSys".recreate_views( _accid:= accid , _tbl :='"dbLegal".trcaseasset', _fts:=true );
1395
1396 select a.* from "dbSys".fnfts_search_create_fts(userid, accid, crec.tblvw9 ) a into tbchild ;
1397
1398 if tbchild.nam is not null then
1399 crec.fts :=true ;
1400 end if;
1401 end if;
1402 end if;
1403 end if;
1404 end if;
1405
1406 --*
1407 if crec.fts is not null then
1408 -- if true then
1409
1410 --* recursive call by search child
1411 Searched_and_Notfound= case
1412 when ( crec.tblvw9 = any( tblsearched_notfound) ) then true
1413 else false
1414 end;
1415
1416 --* is it already searched by parent ?, always check if search child
1417 --if not search_parsearched then
1418 searched_by_par = case
1419 when crec.tblvw9 = any( parsearched ) then true
1420 else false
1421 end ;
1422 -- else
1423 -- searched_by_par=false ;
1424 -- end if;
1425
1426 -- raise '0666:tbl %', tbl;
1427
1428 -- raise '0671:crec.tblvw9 %, tblorg %, parsearched %, crec.ftstbl %, searched_by_par %, tblsearched_notfound %, Searched_and_Notfound %',crec.tblvw9, tblorg, parsearched, crec.ftstbl, searched_by_par, tblsearched_notfound, Searched_and_Notfound;
1429
1430 if not emptystr( crec.nam )
1431 and not Searched_and_Notfound
1432 and not searched_by_par
1433 and diff( crec.tblvw9,tblorg )
1434 then
1435 --if crec.nam = 'tbcontcontactvw12' then
1436 -- raise '1140';
1437 --end if;
1438 begin
1439 select a.ida, a.ids, a.sql ,a.tblsearched_notfound
1440 from "dbSys".fnfts_search( userid, accid
1441 --, format('"%s".%s', crec.sch, crec.nam )
1442 , crec.tblvw9
1443 , texts --null
1444 , textsc
1445 , true , warn, deep ,debug,search_parsearched,_ilike,tbl_only, parsearched, tblsearched_notfound ,tbl ,tblorg
1446 ) a
1447 --into ida, ids, sql ,tblsearched_notfound ;
1448 into idaf, idsf, sql ,tblsearched_notfound ;
1449
1450 exception
1451 when others then raise ' 0548:%,search child %,tbl=%,child %', sqlerrm, format('"%s".%s', crec.sch, crec.nam ) ,tbl,child;
1452 end;
1453 perform "dbSys".fnfts_debug_info( 0845, 2.1 ::numeric, debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview, _explain:=expl,_ida :=idaf,_title:='Child'::text, _tcustom:=format('ida=idaf') );
1454
1455 --* Get parent , if found
1456 --if ida is not null then
1457 --if idaf is not null then
1458 if not emptystr( idsf ) then
1459
1460
1461 --if crec.tblvw9 = '"dbLegal".trcasetrannovw12' then
1462 -- if crec.tblvw9 = '"dbLegal".tbcontcontactvw12' then
1463 --if crec.nam = 'tbcontcontactvw12' then
1464 -- raise '0390: tbl %,crec.tblvw9 %, parsearched %', tbl,crec.tblvw9, parsearched ;
1465 -- end if;
1466
1467 -- raise notice '% 0258-3: tbl %,ida %, idaf %',repeat(' ',deep*2),tbl,ida, idaf;
1468
1469 sqlrun := format(
1470 'select distinct a.%s id
1471 --, array_to_string(array_agg(distinct a.%s),%L)
1472 from "%s".%s a
1473 where a.id in ( %s ) --D
1474 group by a.%s
1475 '
1476 ,crec.pid_name
1477 ,crec.pid_name,','
1478 , crec.schper, crec.namper
1479 --, ids --D
1480 , idsf --D
1481
1482 , crec.pid_name
1483 );
1484 sqlrun := format('
1485 select array_agg( a.id ), string_agg(a.id::text , %L ) from -- A
1486 ( %s --B
1487 ) a
1488 ' , ',' --A
1489 , sqlrun --B
1490 );
1491 --raise '0787:tbl %, ida %, crec.tblvw9 %,parsearched =%, sqlrun=%', tbl,ida, crec.tblvw9,parsearched, sqlrun ;
1492
1493 begin
1494 --execute sqlrun into ida, ids ;
1495 execute sqlrun into idaf, idsf ;
1496 exception
1497 when others then raise exception '0498:%,get data after search child,sqlrun=%',sqlerrm, sqlrun;
1498
1499 end ;
1500 /*
1501 if tblorg = '"dbLegal".trcasetrn040vw194' then
1502 raise notice '% 0258-4: tbl %,ida %,sqlrun=%',repeat(' ',deep*2),tbl,ida, sqlrun ;
1503 end if;
1504 */
1505 --if ida is not null then
1506 --if idaf is not null then
1507 if not emptystr(idsf) then
1508 -- raise notice '% 0942:found child, nam %, ida %,idaf %,sqlrun=%',repeat(' ',deep*2), format('"%s".%s', crec.sch, crec.nam ), ida,idaf, sqlrun;
1509 /*
1510 ida := ida || idaf; --- array_append(ida, idaf);
1511 ids := concat_ws(',',ids , idsf);
1512
1513 */
1514
1515 --* add element
1516 -- raise notice '0940: ida %, idaf %', ida, idaf ;
1517 /*
1518 foreach i in array idaf loop
1519 ok := case
1520 --when i != any( ida ) then true
1521 when not ( i = any( ida ) ) then true
1522
1523 else ida is null
1524 end;
1525 --if not ( i = any( ida )) then -- select 1 != any( '{2,3}'::int[] )
1526 if ok then
1527 ida := ida || i; -- select null::int[] || 1 = {1}
1528 ids := concat_ws(',',ids , i);
1529 -- raise notice '0515: ida %,i %', ida, i ;
1530 end if;
1531 end loop;
1532 */
1533 ida := array_int_add( ida,idaf );
1534 ids := array_to_string(ida,',');
1535
1536
1537 sql := sqlrun;
1538
1539 -- exit;
1540
1541 --else
1542 -- raise exception '0409:Can not get record, sqlrun=%',sqlrun;
1543 end if;
1544 end if;
1545 --else
1546 -- exit;
1547 end if ; -- not emptystr()
1548 end if; -- crec.fts is not null
1549 end loop;
1550 -- else
1551 end if;
1552 --*
1553 --if tbl = '"dbHR".tborgvw179' then
1554 -- raise '0974: ida %, ids ≈%, ida_fk %, ids_fk %', ida, ids, ida_fk , ids_fk;
1555 --end if;
1556 -- raise notice '0974: ida %, ids %', ida, ids;
1557 perform "dbSys".fnfts_debug_info( 1032, 3.0::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview, _explain:=expl,_ida:=ida, _title:='Child'::text , _tcustom:='Finish'::text );
1558
1559 --* 3.Hierarchy Upline
1560 --if emptystr(ids ) then
1561 -- raise '1537:';
1562 --end if;
1563 --ids:=ids || ids_fk;
1564 --*
1565 ida := array_int_add( ida_fk,ida );
1566 ids := array_to_string(ida,',');
1567 perform "dbSys".fnfts_debug_info( 1062, 3.1::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _ida:=ida, _title:='Merge'::text , _tcustom:='FK + Child'::text );
1568
1569 --if ida is not null then
1570 if not emptystr(ids ) then
1571 tblFound := tbl;
1572 -- raise '% 0569: FOUND! "%" at %,ida "%" , deep %, sql=%',repeat(' ',deep*2),textsc, tbl ,ida,deep, sql ;
1573 begin
1574 execute $x$
1575 update "dbSys".tbviewregistration set
1576 ftshits = ftshits + 1
1577 , ftstime = case
1578 when ftstime is null then clock_timestamp() - $2
1579 else ftstime + (clock_timestamp() - $2)
1580 end
1581 where id = $1
1582 $x$ using tb.viewregistrationid , elstart ;
1583 exception
1584 when others then -- leave it;
1585 end;
1586 perform "dbSys".fnfts_debug_info( 1217, 3.2::numeric , debug, deep,userid,accid,textsc,tbl,tblorg, tb.matview,_explain:=expl, _ida:=ida, _title:=' ** FOUND! **'::text , _tcustom:=null ::text );
1587
1588
1589 -- if tbsave.viewregistrationid = 948 then
1590 -- raise '1378: deep %, tbl %, tbsave.viewregisid %,ida %',deep, tbl, tbsave.viewregisid, ida ;
1591 -- end if;
1592 --- if tbsave.nam = 'trcasetrn040vw179_772' then
1593 -- raise '01542: deep %, tbsave.viewregisid %',deep, tbsave.viewregisid;
1594 -- end if;
1595 --*
1596 if deep = 0 then
1597 -- if deep = 1 then
1598
1599 -- if true then
1600
1601 -- raise '1378: tbl %, tbsave.viewregisid %,ida %, tbsave.tk_ftsassg %', tbl, tbsave.viewregisid, ida, tbsave.tk_ftsassg ;
1602
1603 --if true then
1604 if tbsave.tk_assg or tbsave.tk_ftsassg then
1605
1606 begin
1607 perform "dbSys".tbftsuser_update_ida( userid,accid,tbsave.viewregistrationid, tbsave.viewregisid, ida );
1608 exception
1609 when others then raise '1573:%, tbsave.viewregistrationid %',sqlerrm, tbsave.viewregistrationid;
1610 end;
1611 --*
1612 -- raise '1387: tbsave.viewregisid %,ida %', tbsave.viewregisid, ida ;
1613 -- perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid );
1614 --* FTS Assignment
1615 if tbsave.tk_ftsassg then
1616 -- if true then
1617 -- perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
1618 --* drop child
1619 -- raise '1564';
1620 /*
1621 perform "dbSys".tbviewregis_task_delete( userid,accid, tbsave.viewregisid, child_only:=true );
1622
1623 --* then recreate parent
1624 begin
1625 perform "dbSys".tvcreateview(userid,accid,format('"%s".%s',tb.sch , tb.nam_create_view), dropit:=true );
1626 exception
1627 when others then raise '0747:%, tbl %, tb.nam_create_view %, tb.tk_assg %, tb.tk_ftsassg %',sqlerrm,tbl, tb.nam_create_view,tb.tk_assg, tb.tk_ftsassg;
1628 end;
1629 */
1630 end if;
1631 end if;
1632
1633 end if;
1634 -- select * from "dbSys".tbviewregistration where id = 208;
1635
1636
1637 else
1638 -- if fk.tblvw9 != any( tblsearched_notfound) then
1639 -- if tbl not in array tblsearched_notfound then
1640 -- if not ( tbl = any ( tblsearched_notfound )) then -- ???
1641 if true then
1642 tblsearched_notfound := array_append ( tblsearched_notfound , tbl );
1643
1644 end if;
1645 end if;
1646
1647 end if; --
1648 perform "dbSys".fnfts_debug_info( 1090, 4.0::numeric , debug, deep,userid,accid,textsc,tbl,tblorg,tb.matview,_explain:=expl,_ida:=ida, _title:='Exit'::text );
1649
1650 --* deep count and check
1651 deep = deep - 1;
1652
1653end $$ language plpgsql ; --** MUST be volatile ** see also: tbCaseAssetVW_DATA4_fts.sql , secnario :Search case for the contact while no fts