File: phrasematch.sql

package info (click to toggle)
virtuoso-opensource 6.1.6+dfsg2-4
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster, sid, stretch
  • size: 260,992 kB
  • ctags: 125,220
  • sloc: ansic: 652,748; sql: 458,419; xml: 282,834; java: 61,031; sh: 40,031; cpp: 36,890; cs: 25,240; php: 12,692; yacc: 9,523; lex: 7,018; makefile: 6,157; jsp: 4,484; awk: 1,643; perl: 1,013; ruby: 1,003; python: 326
file content (633 lines) | stat: -rw-r--r-- 24,864 bytes parent folder | download | duplicates (2)
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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
--
--  $Id$
--
--  This file is part of the OpenLink Software Virtuoso Open-Source (VOS)
--  project.
--
--  Copyright (C) 1998-2012 OpenLink Software
--
--  This project is free software; you can redistribute it and/or modify it
--  under the terms of the GNU General Public License as published by the
--  Free Software Foundation; only version 2 of the License, dated June 1991.
--
--  This program is distributed in the hope that it will be useful, but
--  WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
--  General Public License for more details.
--
--  You should have received a copy of the GNU General Public License along
--  with this program; if not, write to the Free Software Foundation, Inc.,
--  51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
--

create procedure AP_EXEC_NO_ERROR (in expr varchar)
{
  declare state, message, meta, result any;
  exec(expr, state, message, vector(), 0, meta, result);
}
;

create table DB.DBA.SYS_ANN_PHRASE_CLASS
(
  APC_ID integer not null primary key,
  APC_NAME varchar(255),		-- unique name for use in API/UI
  APC_OWNER_UID integer,		-- references SYS_USERS (U_ID), NULL if the record writeable for any reader
  APC_READER_GID integer,		-- references SYS_USERS (U_ID), NULL if the record is readable for public
  APC_CALLBACK varchar,
  APC_APP_ENV any
  )
alter index SYS_ANN_PHRASE_CLASS on DB.DBA.SYS_ANN_PHRASE_CLASS partition cluster replicated
create unique index SYS_ANN_PHRASE_CLASS_APC_NAME on DB.DBA.SYS_ANN_PHRASE_CLASS (APC_NAME) partition cluster replicated
;

create table DB.DBA.SYS_ANN_PHRASE_SET
(
  APS_ID integer not null primary key,
  APS_NAME varchar(255),		-- unique name for use in API/UI
  APS_OWNER_UID integer,		-- references SYS_USERS (U_ID), NULL if the record writeable for any reader
  APS_READER_GID integer,		-- references SYS_USERS (U_ID), NULL if the record is readable for public
  APS_APC_ID integer not null,		-- references SYS_ANN_PHRASE_CLASS (APC_ID)
  APS_LANG_NAME varchar not null,	-- name of language handler that is used to split texts of phrases
  APS_APP_ENV any,
  APS_SIZE any,				-- approximate number of phrases in set (actual or estimate for future)
  APS_LOAD_AT_BOOT integer not null	-- flags whether phrases should be loaded at boot time.
  )
alter index SYS_ANN_PHRASE_SET on DB.DBA.SYS_ANN_PHRASE_SET partition cluster replicated
create unique index SYS_ANN_PHRASE_SET_APS_NAME on DB.DBA.SYS_ANN_PHRASE_SET (APS_NAME) partition cluster replicated
;

--#IF VER=5
alter table DB.DBA.SYS_ANN_PHRASE_SET add APS_LOAD_AT_BOOT integer not null
;
--#ENDIF

create table DB.DBA.SYS_ANN_PHRASE
(
  AP_APS_ID integer not null,		-- references SYS_ANN_PHRASE_SET (APS_ID),
  AP_CHKSUM integer,			-- phrase checksum
  AP_TEXT varchar,			-- original text
  AP_LINK_DATA any,			-- Associated data about links etc.
  AP_LINK_DATA_LONG long varchar,	-- Same as AP_LINK_DATA but for long content, one of two is always NULL
  primary key (AP_APS_ID, AP_CHKSUM, AP_TEXT)
  )
alter index SYS_ANN_PHRASE on DB.DBA.SYS_ANN_PHRASE partition cluster replicated
;

--#IF VER=5
alter table DB.DBA.SYS_ANN_PHRASE add AP_LINK_DATA_LONG long varchar
;
--#ENDIF

create table DB.DBA.SYS_ANN_AD_ACCOUNT (
  AAA_ID integer not null primary key,
  AAA_NAME varchar(255),		-- unique name for use in API/UI
  AAA_OWNER_UID integer,		-- references SYS_USERS (U_ID), NULL if the record writeable for any reader
  AAA_READER_GID integer,		-- references SYS_USERS (U_ID), NULL if the record is readable for public
  AAA_DETAILS long xml,			-- any details, e.g., in RDF
  AAA_APP_ENV any
  )
alter index SYS_ANN_AD_ACCOUNT on DB.DBA.SYS_ANN_AD_ACCOUNT partition cluster replicated
create unique index SYS_ANN_AD_ACCOUNT_AAA_NAME on DB.DBA.SYS_ANN_AD_ACCOUNT (AAA_NAME) partition cluster replicated
;

create table DB.DBA.SYS_ANN_LINK (
  AL_ID integer primary key,
  AL_OWNER_UID integer,			-- references SYS_USERS (U_ID), NULL if the record writeable for any reader; always readable for public
  AL_URI varchar,			-- URI template for A HREF
  AL_TEXT varchar,			-- text template for body of <A>
  AL_NOTE varchar,			-- taxt after the link (or around it)
  AL_TAGS any,				-- tags to add or remove
  AL_CALLBACK varchar,
  AL_APP_ENV any
  )
alter index SYS_ANN_LINK on DB.DBA.SYS_ANN_LINK partition cluster replicated
;

create table DB.DBA.SYS_ANN_AD_RULE (
  AAR_AAA_ID integer not null,		-- advertizer who pays for the ad
  AAR_APS_ID integer not null,		-- phrase set
  AAR_AP_CHKSUM integer not null,	-- phrase checksum
  AAR_TEXT varchar not null,		-- original text
  AAR_AL_ID integer not null,		-- references SYS_ANN_LINK (AL_ID)
  AAR_APP_ENV any,
  primary key (AAR_AAA_ID, AAR_APS_ID, AAR_AP_CHKSUM, AAR_TEXT, AAR_AL_ID)
  )
alter index SYS_ANN_AD_RULE on DB.DBA.SYS_ANN_AD_RULE partition cluster replicated
;

--#IF VER=5
delete from SYS_TRIGGERS where T_NAME in (
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_CLASS_I'),
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_CLASS_U'),
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_CLASS_BD'),
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_SET_I'),
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_SET_U'),
  fix_identifier_case ('DBA.SYS_ANN_PHRASE_SET_BD') )
;

delete from SYS_PROCEDURES where P_NAME in (
  fix_identifier_case ('DB.DBA.AP_EXEC_NO_ERROR'),
  fix_identifier_case ('DB.DBA.ANN_BOOT'),
  fix_identifier_case ('DB.DBA.ANN_ZAP'),
  fix_identifier_case ('DB.DBA.ANN_AUTHENTICATE'),
  fix_identifier_case ('DB.DBA.ANN_GETID'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_CLASS_ADD'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_CLASS_ADD_INT'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_CLASS_DEL'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_SET_ADD'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_SET_ADD_INT'),
  fix_identifier_case ('DB.DBA.ANN_PHRASE_SET_DEL'),
  fix_identifier_case ('DB.DBA.ANN_LINK_ADD'),
  fix_identifier_case ('DB.DBA.ANN_LINK_MODIFY'),
  fix_identifier_case ('DB.DBA.ANN_LINK_DEL'),
  fix_identifier_case ('DB.DBA.ANN_AD_RULE_ADD'),
  fix_identifier_case ('DB.DBA.ANN_AD_RULE_DEL') )
;
--#ENDIF

create trigger SYS_ANN_PHRASE_CLASS_I after insert on DB.DBA.SYS_ANN_PHRASE_CLASS referencing new as N
{
  ap_class_status (N.APC_ID, 1, 1);
}
;

create trigger SYS_ANN_PHRASE_CLASS_U after update on DB.DBA.SYS_ANN_PHRASE_CLASS referencing new as N
{
  ap_class_status (N.APC_ID, 1, 1);
}
;

create trigger SYS_ANN_PHRASE_CLASS_BD before delete on DB.DBA.SYS_ANN_PHRASE_CLASS referencing old as O
{
  if (exists (select top 1 1 from DB.DBA.SYS_ANN_PHRASE_SET where APS_APC_ID = O.APC_ID))
    signal ('42000', sprintf ('Integrity violation: can not delete annotation phrase class "%s" that is referenced by a phrase set', O.APC_NAME));
  ap_class_status (O.APC_ID, 0);
}
;

create trigger SYS_ANN_PHRASE_SET_I after insert on DB.DBA.SYS_ANN_PHRASE_SET referencing new as N
{
  ap_set_status (N.APS_ID, 1, 1);
}
;

create trigger SYS_ANN_PHRASE_SET_U after update on DB.DBA.SYS_ANN_PHRASE_SET referencing new as N
{
  ap_set_status (N.APS_ID, 1, 1);
}
;

create trigger SYS_ANN_PHRASE_SET_BD before delete on DB.DBA.SYS_ANN_PHRASE_SET referencing old as O
{
  ap_set_status (O.APS_ID, 0);
}
;

create procedure DB.DBA.ANN_BOOT()
{
  ap_global_init ();
  for select APC_ID from DB.DBA.SYS_ANN_PHRASE_CLASS do
    {
      ap_class_status (APC_ID, 1, 0);
    }
  for select APS_ID, APS_LOAD_AT_BOOT from DB.DBA.SYS_ANN_PHRASE_SET do
    {
      ap_set_status (APS_ID, case (APS_LOAD_AT_BOOT) when 0 then 1 else 2 end, 0);
    }
}
;

create function ANN_AUTHENTICATE (in id any, in what char (1), in access char, in auth_uname any, in auth_pwd varchar, inout auth_uid integer := null) returns integer
{
  declare uid, owner_uid, reader_gid, res_id integer;
  declare pwd varchar;
whenever not found goto obj_nf;
  if ('C' = what)
    {
      if (isinteger (id))
        select APC_ID, APC_OWNER_UID, APC_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_PHRASE_CLASS where APC_ID = id;
      else
        select APC_ID, APC_OWNER_UID, APC_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_PHRASE_CLASS where APC_NAME = id;
    }
  else if ('S' = what)
    {
      if (isinteger (id))
        select APS_ID, APS_OWNER_UID, APS_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_PHRASE_SET where APS_ID = id;
      else
        select APS_ID, APS_OWNER_UID, APS_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_PHRASE_SET where APS_NAME = id;
    }
  else if ('A' = what)
    {
      if (isinteger (id))
        select AAA_ID, AAA_OWNER_UID, AAA_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_AD_ACCOUNT where AAA_ID = id;
      else
        select AAA_ID, AAA_OWNER_UID, AAA_READER_GID into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_AD_ACCOUNT where AAA_NAME = id;
    }
  else if ('L' = what)
    {
      if (isinteger (id))
        select AL_ID, AL_OWNER_UID, null into res_id, owner_uid, reader_gid from DB.DBA.SYS_ANN_LINK where AL_ID = id;
      else
        return -7;
    }
  else return -14;
whenever not found goto user_nf;
  if (auth_uid is null)
    {
      if (isinteger (auth_uname))
        select U_PASSWORD, U_ID into pwd, uid from DB.DBA.SYS_USERS where U_ID = auth_uname and (0 = U_ACCOUNT_DISABLED) and U_SQL_ENABLE;
      else
        select U_PASSWORD, U_ID into pwd, uid from DB.DBA.SYS_USERS where U_NAME = auth_uname;
      if (isstring (pwd))
	{
	  if ((pwd[0] = 0 and not pwd_magic_calc (auth_uname, auth_pwd) = pwd) or (pwd[0] <> 0 and pwd <> auth_pwd))
	    {
	      -- dbg_obj_princ ('ANN_AUTHENTICATE: the password of ', auth_uname, ' is not ', auth_pwd);
	      return -12;
	    }
	}
      auth_uid := uid;
    }
  else
    uid := auth_uid;
  -- dbg_obj_princ ('ANN_AUTHENTICATE: res_id=', res_id, ', owner_uid=', owner_uid, ', reader_gid=', reader_gid, ' auth_uid=', auth_uid, ' access=', access);
  if (0 = uid)
    return res_id;
  if (owner_uid is not null)
    {
      if (owner_uid = uid)
        return res_id;
      if ('W' = access)
        return -12;
    }
  if (reader_gid is not null)
    {
      if (exists (select top 1 1 from DB.DBA.SYS_USER_GROUP where UG_UID = uid and UG_GID = reader_gid))
        return res_id;
      return -12;
    }
  return res_id;
obj_nf:
  return -1;
user_nf:
  -- dbg_obj_princ ('ANN_AUTHENTICATE: no user ', auth_uname);
  return -12;
}
;

create function ANN_GETID (in what varchar )
{
  if ('C' = what)
    {
      return coalesce (
        (select top 1 (t.APC_ID-1) from DB.DBA.SYS_ANN_PHRASE_CLASS as t where t.APC_ID > 1 and not exists (select top 1 1 from DB.DBA.SYS_ANN_PHRASE_CLASS as i where i.APC_ID = t.APC_ID-1)),
        (select max (u.APC_ID+1) from DB.DBA.SYS_ANN_PHRASE_CLASS as u),
	1 );
    }
  if ('S' = what)
    {
      return coalesce (
        (select top 1 (t.APS_ID-1) from DB.DBA.SYS_ANN_PHRASE_SET as t where t.APS_ID > 1 and not exists (select top 1 1 from DB.DBA.SYS_ANN_PHRASE_SET as i where i.APS_ID = t.APS_ID-1)),
        (select max (u.APS_ID+1) from DB.DBA.SYS_ANN_PHRASE_SET as u),
	1 );
    }
  if ('A' = what)
    {
      return coalesce (
        (select top 1 (t.AAA_ID-1) from DB.DBA.SYS_ANN_AD_ACCOUNT as t where t.AAA_ID > 1 and not exists (select top 1 1 from DB.DBA.SYS_ANN_AD_ACCOUNT as i where i.AAA_ID = t.AAA_ID-1)),
        (select max (u.AAA_ID+1) from DB.DBA.SYS_ANN_AD_ACCOUNT as u),
	1 );
    }
  if ('L' = what)
    {
      return coalesce (
        (select top 1 (t.AL_ID-1) from DB.DBA.SYS_ANN_LINK as t where t.AL_ID > 1 and not exists (select top 1 1 from DB.DBA.SYS_ANN_LINK as i where i.AL_ID = t.AL_ID-1)),
        (select max (u.AL_ID+1) from DB.DBA.SYS_ANN_LINK as u),
	1 );
    }
  return -14;
}
;

--!AWK PUBLIC
create function ANN_PHRASE_CLASS_ADD (in _name varchar, in _owner_uid integer, in _reader_gid integer, in _callback varchar, in _app_env any, in mode varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare auth_uid, _id integer;
  mode := lower (mode);
  auth_uid := NULL;
  _id := ANN_AUTHENTICATE (_name, 'C', 'W', auth_uname, auth_pwd, auth_uid);
  if (_id < -1)
    return _id;
  if (_id = -1)
    _id := ANN_GETID ('C');
  else
    {
      if ('into' = mode)
        signal ('23000', sprintf ('Uniqueness violation: Annotation phrase class ''%s'' exists', _name));
      if ('soft' = mode)
        return _id;
    }
  return ANN_PHRASE_CLASS_ADD_INT (_id, _name, _owner_uid, _reader_gid, _callback, _app_env);
}
;

create function ANN_PHRASE_CLASS_ADD_INT (in _id integer, in _name varchar, in _owner_uid integer, in _reader_gid integer, in _callback varchar, in _app_env any)
{
  insert replacing DB.DBA.SYS_ANN_PHRASE_CLASS (APC_ID, APC_NAME, APC_OWNER_UID, APC_READER_GID, APC_CALLBACK, APC_APP_ENV)
  values (_id, _name, _owner_uid, _reader_gid, _callback, _app_env);
  return _id;
}
;

--!AWK PUBLIC
create function ANN_PHRASE_CLASS_DEL (in _name varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare auth_uid, _id integer;
  auth_uid := NULL;
  _id := ANN_AUTHENTICATE (_name, 'C', 'W', auth_uname, auth_pwd, auth_uid);
  if (_id < 0)
    return _id;
  if (exists (select top 1 1 from DB.DBA.SYS_ANN_PHRASE_SET where APS_APC_ID = _id))
    {
      declare use_sample_name varchar;
      use_sample_name := coalesce ((select top 1 APS_NAME from DB.DBA.SYS_ANN_PHRASE_SET where ANN_AUTHENTICATE (APS_NAME, 'S', 'R', auth_uname, auth_pwd, auth_uid)));
      if (use_sample_name is null)
        signal ('23000', sprintf ('Integrity violation: annotation phrase class ''%s'' is used in definition of some annotation phrase set', _name));
      else
        signal ('23000', sprintf ('Integrity violation: annotation phrase class ''%s'' is used in definition of annotation phrase set ''%s''', _name, use_sample_name));
    }
  delete from DB.DBA.SYS_ANN_PHRASE_CLASS where APC_ID = _id;
  return _id;
}
;

--!AWK PUBLIC
create function ANN_PHRASE_SET_ADD (in _name varchar, in _owner_uid integer, in _reader_gid integer, in _apc_name varchar, in _lang_name varchar, in _app_env any, in _size integer, in _load_at_boot integer, in mode varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare auth_uid, _id, _apc_id integer;
  mode := lower (mode);
  auth_uid := NULL;
  _apc_id := ANN_AUTHENTICATE (_apc_name, 'C', 'R', auth_uname, auth_pwd, auth_uid);
  if (_apc_id < 0)
    return _apc_id;
  _id := ANN_AUTHENTICATE (_name, 'S', 'W', auth_uname, auth_pwd, auth_uid);
  if (_id < -1)
    return _id;
  if (_id = -1)
    _id := ANN_GETID ('S');
  else
    {
      declare old_lang_name varchar;
      if ('into' = mode)
        signal ('23000', sprintf ('Uniqueness violation: Annotation phrase set ''%s'' exists', _name));
      old_lang_name := coalesce ((select APS_LANG_NAME from DB.DBA.SYS_ANN_PHRASE_SET where APS_ID = _id));
      if (_lang_name <> old_lang_name)
        signal ('23000', sprintf ('Integrity violation: Annotation phrase set ''%s'' uses language ''%s'' that can not be replaced with ''%s''', _name, old_lang_name, _lang_name));
      if ('soft' = mode)
        return _id;
    }
  return ANN_PHRASE_SET_ADD_INT (_id, _name, _owner_uid, _reader_gid, _apc_id, _lang_name, _app_env, _size, _load_at_boot);
}
;

create function ANN_PHRASE_SET_ADD_INT (in _id integer, in _name varchar, in _owner_uid integer, in _reader_gid integer, in _apc_id integer, in _lang_name varchar, in _app_env any, in _size integer, in _load_at_boot integer)
{
  insert replacing DB.DBA.SYS_ANN_PHRASE_SET (APS_ID, APS_NAME, APS_OWNER_UID, APS_READER_GID, APS_APC_ID, APS_LANG_NAME, APS_APP_ENV, APS_SIZE, APS_LOAD_AT_BOOT)
  values (_id, _name, _owner_uid, _reader_gid, _apc_id, _lang_name, _app_env, _size, _load_at_boot);
  return _id;
}
;

--!AWK PUBLIC
create function ANN_PHRASE_SET_DEL (in _name varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare auth_uid, _id integer;
  auth_uid := NULL;
  _id := ANN_AUTHENTICATE (_name, 'S', 'W', auth_uname, auth_pwd, auth_uid);
  if (_id < 0)
    return _id;
  if (exists (select top 1 1 from DB.DBA.SYS_ANN_AD_RULE where AAR_APS_ID = _id))
    {
      declare use_sample_name varchar;
      use_sample_name := coalesce ((select top 1 AAA_NAME from DB.DBA.SYS_ANN_AD_RULE join DB.DBA.SYS_ANN_AD_ACCOUNT on (AAR_AAA_ID = AAA_ID) where AAR_APS_ID = _id and ANN_AUTHENTICATE (AAA_ID, 'A', 'R', auth_uname, auth_pwd, auth_uid)));
      if (use_sample_name is null)
        signal ('23000', sprintf ('Integrity violation: annotation phrase set ''%s'' is used by some advertizer', _name));
      else
        signal ('23000', sprintf ('Integrity violation: annotation phrase set ''%s'' is used by advertizer ''%s''', _name, use_sample_name));
    }
  delete from DB.DBA.SYS_ANN_PHRASE where AP_APS_ID = _id;
  delete from DB.DBA.SYS_ANN_PHRASE_SET where APS_ID = _id;
  return _id;
}
;

--!AWK PUBLIC
create function ANN_LINK_ADD (in _owner_uid integer, in _uri varchar, in _text varchar, in _note varchar, in _tags any, in _callback varchar, in _app_env any) returns integer
{
  declare _id integer;
  _id := ANN_GETID ('L');
  insert into DB.DBA.SYS_ANN_LINK (AL_ID, AL_OWNER_UID, AL_URI, AL_TEXT, AL_NOTE, AL_TAGS, AL_CALLBACK, AL_APP_ENV)
  values (_id, _owner_uid, _uri, _text, _note, _tags, _callback, _app_env);
  return _id;
}
;

--!AWK PUBLIC
create function ANN_LINK_MODIFY (in _id integer, in _owner_uid integer, in _uri varchar, in _text varchar, in _note varchar, in _tags any, in _callback varchar, in _app_env any, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare _res integer;
  declare auth_uid integer;
  auth_uid := NULL;
  _res := ANN_AUTHENTICATE (_id, 'L', 'W', auth_uname, auth_pwd, auth_uid);
  if (_res < 0)
    return _res;
  insert replacing DB.DBA.SYS_ANN_LINK (AL_ID, AL_OWNER_UID, AL_URI, AL_TEXT, AL_NOTE, AL_TAGS, AL_CALLBACK, AL_APP_ENV)
  values (_id, _owner_uid, _uri, _text, _note, _tags, _callback, _app_env);
  return _id;
}
;

--!AWK PUBLIC
create function ANN_LINK_DEL (in _id integer, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare _res integer;
  declare auth_uid integer;
  auth_uid := NULL;
  _res := ANN_AUTHENTICATE (_id, 'L', 'W', auth_uname, auth_pwd, auth_uid);
  if (_res < 0)
    return _res;
  if (exists (select top 1 1 from DB.DBA.SYS_ANN_AD_RULE where AAR_AL_ID = _id))
    {
      declare use_sample_name varchar;
      use_sample_name := coalesce ((select top 1 AAA_NAME from DB.DBA.SYS_ANN_AD_RULE join DB.DBA.SYS_ANN_AD_ACCOUNT on (AAR_AAA_ID = AAA_ID) where AAR_AL_ID = _id and ANN_AUTHENTICATE (AAA_ID, 'A', 'R', auth_uname, auth_pwd, auth_uid)));
      if (use_sample_name is null)
        signal ('23000', sprintf ('Integrity violation: advertizing link %d is used by some advertizer', _id));
      else
        signal ('23000', sprintf ('Integrity violation: advertizing link %d is used by advertizer ''%s''', _id, use_sample_name));
    }
  delete from DB.DBA.SYS_ANN_LINK where AL_ID = _id;
  return _id;
}
;

--!AWK PUBLIC
create function ANN_AD_RULE_ADD (in aaa_name varchar, in aps_name varchar, in _text varchar, in _al_id integer, in _app_env any, in _lang_name varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare _aaa_id, _aps_id, _ap_chksum integer;
  declare auth_uid integer;
  declare _aps_lang_name varchar;
  declare old_data, old_al any;
  declare old_al_idx integer;
  auth_uid := NULL;
  _aaa_id := ANN_AUTHENTICATE (aaa_name, 'A', 'W', auth_uname, auth_pwd, auth_uid); -- 'W' because we're going to charge the account.
  if (_aaa_id < 0)
    return _aaa_id;
  _aps_id := ANN_AUTHENTICATE (aps_name, 'S', 'W', auth_uname, auth_pwd, auth_uid);
  if (_aps_id < 0)
    return _aps_id;
  if (not exists (select top 1 1 from DB.DBA.SYS_ANN_LINK where AL_ID = _al_id))
    return -1;
  _aps_lang_name := coalesce ((select APS_LANG_NAME from DB.DBA.SYS_ANN_PHRASE_SET where APS_ID = _aps_id));
  if (_lang_name <> _aps_lang_name)
    signal ('23000', sprintf ('Integrity violation: language ''%s'' in advertizing rule conflicts with language ''%s'' of phrase set ''%s''', _lang_name, _aps_lang_name, aps_name));
  _ap_chksum := ap_phrase_chksum (_text, _lang_name);
  old_data := coalesce ((select AP_LINK_DATA from DB.DBA.SYS_ANN_PHRASE where AP_APS_ID = _aps_id and AP_CHKSUM=_ap_chksum and AP_TEXT = _text), vector ());
  if (not isarray (old_data))
    return -9;
  if (mod (length (old_data), 2))
    return -9;
  old_al_idx := position ('AL', old_data, 1, 2);
  if (0 = old_al_idx)
    old_data := vector_concat (vector ('AL', vector (_al_id, _aaa_id, _app_env)), old_data);
  else
    {
      old_al := aref_set_0 (old_data, old_al_idx);
      if (mod (length (old_al), 3))
	return -9;
      if (0 <> position (_al_id, old_al, 1, 3))
        {
          old_data [old_al_idx] := old_al;
          return -8;
        }
      old_data [old_al_idx] := vector_concat (old_al, vector (_al_id, _aaa_id, _app_env));
    }
  ap_add_phrases (_aps_id, vector (vector (_text, old_data)));
  insert replacing DB.DBA.SYS_ANN_AD_RULE (AAR_AAA_ID, AAR_APS_ID, AAR_AP_CHKSUM, AAR_TEXT, AAR_AL_ID, AAR_APP_ENV)
  values (_aaa_id, _aps_id, _ap_chksum, _text, _al_id, _app_env);
  commit work;
  return 0;
}
;

--!AWK PUBLIC
create function ANN_AD_RULE_DEL (in aaa_name varchar, in aps_name varchar, in _text varchar, in _al_id integer, in _lang_name varchar, in auth_uname varchar, in auth_pwd varchar) returns integer
{
  declare _aaa_id, _aps_id, _ap_chksum integer;
  declare auth_uid integer;
  declare _aps_lang_name varchar;
  declare old_data, old_al, pos_to_del any;
  declare old_al_idx, res integer;
  auth_uid := NULL;
  _aaa_id := ANN_AUTHENTICATE (aaa_name, 'A', 'W', auth_uname, auth_pwd, auth_uid); -- 'W' because we're going to remove an ad that can be worth.
  if (_aaa_id < 0)
    return _aaa_id;
  _aps_id := ANN_AUTHENTICATE (aps_name, 'S', 'W', auth_uname, auth_pwd, auth_uid);
  if (_aps_id < 0)
    return _aps_id;
  if (not exists (select top 1 1 from DB.DBA.SYS_ANN_LINK where AL_ID = _al_id))
    return -1;
  _aps_lang_name := coalesce ((select APS_LANG_NAME from DB.DBA.SYS_ANN_PHRASE_SET where APS_ID = _aps_id));
  if (_lang_name <> _aps_lang_name)
    signal ('23000', sprintf ('Integrity violation: language ''%s'' in advertizing rule conflicts with language ''%s'' of phrase set ''%s''', _lang_name, _aps_lang_name, aps_name));
  _ap_chksum := ap_phrase_chksum (_text, _lang_name);
whenever not found goto phrase_nf;
  select AP_LINK_DATA into old_data from DB.DBA.SYS_ANN_PHRASE where AP_APS_ID = _aps_id and AP_CHKSUM=_ap_chksum and AP_TEXT = _text;
  if (not isarray (old_data))
    {
      res := -9;
      goto phrase_done;
    }
  if (mod (length (old_data), 2))
    {
      res := -9;
      goto phrase_done;
    }
  old_al_idx := position ('AL', old_data, 1, 2);
  if (0 = old_al_idx)
    {
      res := -1;
      goto phrase_done;
    }
  old_al := aref_set_0 (old_data, old_al_idx);
  if (mod (length (old_al), 3))
    {
      res := -1;
      goto phrase_done;
    }
  pos_to_del := position (_al_id, old_al, 1, 3);
  if (0 = pos_to_del)
    {
      old_data [old_al_idx] := old_al;
      res := -1;
      goto phrase_done;
    }
  if (length (old_al) > 3)
    {
      old_al := vector_concat (subseq (old_al, 0, pos_to_del - 1), subseq (old_al, pos_to_del + 2));
      old_data [old_al_idx] := old_al;
    }
  else
    {
      if (length (old_data) > 2)
        old_data := vector_concat (subseq (old_data, 0, old_al_idx - 1), subseq (old_data, old_al_idx + 1));
      else
        {
	  ap_add_phrases (_aps_id, vector (vector (_text)));
	  res := 0;
	  goto phrase_done;
        }
    }
  ap_add_phrases (_aps_id, vector (vector (_text, old_data)));
  res := 0;
  goto phrase_done;

phrase_nf:
  res := -1;

phrase_done:
  delete from DB.DBA.SYS_ANN_AD_RULE where (AAR_AAA_ID = _aaa_id) and (AAR_APS_ID = _aps_id) and (AAR_AP_CHKSUM = _ap_chksum) and (AAR_TEXT = _text) and (AAR_AL_ID = _al_id);
  commit work;
  return res;
}
;

create procedure DB.DBA.ANN_ZAP ()
{
  declare _aps_id, _prev_aps_id integer;
  declare _text, _prev_text varchar;
  whenever not found goto phrase_nf;
  _prev_aps_id := -1;
  _prev_text := '';

next_phrase:
  select AP_APS_ID, AP_TEXT into _aps_id, _text from DB.DBA.SYS_ANN_PHRASE;
  if ((_aps_id = _prev_aps_id) and (_text = _prev_text))
    signal ('OBLOM', sprintf ('Unable to remove phrase ''%s'' of set %d', _text, _aps_id));
  ap_add_phrases (_aps_id, vector (vector (_text)));
  _prev_aps_id := _aps_id;
  _prev_text := _text;
  goto next_phrase;

phrase_nf:
  delete from DB.DBA.SYS_ANN_AD_RULE;
  delete from DB.DBA.SYS_ANN_LINK;
  delete from DB.DBA.SYS_ANN_AD_ACCOUNT;
  delete from DB.DBA.SYS_ANN_PHRASE_SET;
  delete from DB.DBA.SYS_ANN_PHRASE_CLASS;
  commit work;
}
;

AP_EXEC_NO_ERROR ('DB.DBA.ANN_BOOT()')
;