File: nntpf_web_svc.sql

package info (click to toggle)
virtuoso-opensource 7.2.5.1%2Bdfsg1-0.3
  • links: PTS, VCS
  • area: main
  • in suites: bookworm
  • size: 285,240 kB
  • sloc: ansic: 641,220; sql: 490,413; xml: 269,570; java: 83,893; javascript: 79,900; cpp: 36,927; sh: 31,653; cs: 25,702; php: 12,690; yacc: 10,227; lex: 7,601; makefile: 7,129; jsp: 4,523; awk: 1,697; perl: 1,013; ruby: 1,003; python: 326
file content (197 lines) | stat: -rw-r--r-- 6,408 bytes parent folder | download | duplicates (7)
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
nntpf_exec_no_error(
'create table NNTPF_PING_REG
(
 NPR_HOST_ID int references ODS.DBA.SVC_HOST (SH_ID) on update cascade on delete cascade,
 NPR_NG_GROUP int references DB.DBA.NEWS_GROUPS (NG_GROUP) on update cascade on delete cascade,
 primary key (NPR_NG_GROUP, NPR_HOST_ID)
)');

nntpf_exec_no_error(
'create table NNTPF_PING_LOG
(
 NPL_HOST_ID int references ODS.DBA.SVC_HOST (SH_ID) on update cascade on delete cascade,
 NPL_NG_GROUP int references DB.DBA.NEWS_GROUPS (NG_GROUP) on update cascade on delete cascade,
 NPL_P_TITLE varchar default null,
 NPL_P_URL varchar default null,
 NPL_STAT int default 0, -- 1 sent, 2 error, 0 pending
 NPL_TS timestamp,
 NPL_SENT datetime,
 NPL_ERROR long varchar,
 NPL_SEQ integer identity,
 primary key (NPL_NG_GROUP, NPL_HOST_ID, NPL_STAT, NPL_SEQ)
)');

create procedure NNTPF_PING
  (
  in _ng_group int,
  in _post_title varchar := null,
  in _post_url varchar := null,
  in svc_name varchar := null
  )
{

  if (svc_name is null)
    {
      for select NPR_HOST_ID, NG_GROUP from NNTPF_PING_REG, DB.DBA.NEWS_GROUPS where NG_GROUP = _ng_group do
  {
    if (not exists (select 1 from NNTPF_PING_LOG where NPL_NG_GROUP = NG_GROUP and NPL_HOST_ID = NPR_HOST_ID and NPL_STAT = 0))
      insert into NNTPF_PING_LOG (NPL_NG_GROUP, NPL_HOST_ID, NPL_STAT, NPL_P_TITLE, NPL_P_URL)
    values (NG_GROUP, NPR_HOST_ID, 0, _post_title, _post_url);
  }
    }
  else
    {
      declare s_id, _wai_id int;
      s_id := (select SH_ID from ODS.DBA.SVC_HOST where SH_NAME = svc_name);
      _ng_group := (select NG_GROUP from DB.DBA.NEWS_GROUPS where NG_GROUP = _ng_group);
      if (s_id is not null and _ng_group is not null and
          not exists (select 1 from NNTPF_PING_LOG where NPL_NG_GROUP = _ng_group and NPL_HOST_ID = s_id and NPL_STAT = 0)
         )
           insert into NNTPF_PING_LOG (NPL_NG_GROUP, NPL_HOST_ID, NPL_STAT, NPL_P_TITLE, NPL_P_URL)
                  values (_ng_group, s_id, 0, _post_title, _post_url);

    }
};
create procedure NNTPF_SVC_PROCESS_PINGS ()
{
  declare _host_id, _ng_group, dedl, seq int;
  declare nam, use_pings, _url, _title,_ng_url varchar;

  declare cr cursor for select NPL_HOST_ID, NPL_NG_GROUP, NG_DESC,  concat('/nntpf/nntpf_nthread_view.vspx?group=',cast(NG_GROUP as varchar)),NPL_P_TITLE, NPL_P_URL, NPL_SEQ from
      NNTPF_PING_LOG, DB.DBA.NEWS_GROUPS where NG_GROUP = NPL_NG_GROUP and NPL_STAT = 0;

  dedl := 0;

  declare exit handler for sqlstate '40001'
    {
      rollback work;
      close cr;
      dedl := dedl + 1;
      if (dedl < 5)
  goto again;
    };

again:
  whenever not found goto ret;
  open cr (prefetch 1);

  while (1)
  {

      
      fetch cr into _host_id, _ng_group, nam, _ng_url, _title, _url, seq;

      commit work;
      

      for select SH_URL, SH_PROTO, SH_METHOD from ODS.DBA.SVC_HOST where SH_ID = _host_id do
      {

      if (isstring (SH_PROTO) and SH_PROTO <> '' and _ng_url is not null)
        {
         declare url, rc varchar;
         rc := null;

         if (length (_url) = 0)
          url := DB.DBA.WA_LINK (1, _ng_url);
         else
         url := DB.DBA.WA_LINK (1, _url);

        if (length (_title))
          nam := _title;
        

        {
          declare exit handler for sqlstate '*' {
            rollback work;
            update NNTPF_PING_LOG set NPL_ERROR = __SQL_MESSAGE, NPL_STAT = 2, NPL_SENT = now ()
                 where NPL_NG_GROUP = _ng_group and NPL_HOST_ID = _host_id and NPL_STAT = 0 and NPL_SEQ = seq;
            commit work;
            goto next;
          };
        
          commit work;
--          dbg_printf ('[%s] [%s] [%s] [%s] [%s]', SH_PROTO, SH_URL, SH_METHOD, url, nam);

          if (SH_PROTO = 'soap')
            {
              rc := DB.DBA.SOAP_CLIENT (url=>SH_URL,
              operation=>'ping',
              parameters=>vector ('weblogname',nam,'weblogurl',url),
              soap_action=>'/weblogUpdates'
              );
            }
          else if (SH_PROTO = 'xml-rpc')
            {
              if (SH_METHOD = 'weblogUpdates.ping')
          {
            rc := DB.DBA.XMLRPC_CALL (SH_URL, 'weblogUpdates.ping', vector (nam, url));
          }
              else
          {
              
            rc := DB.DBA.XMLRPC_CALL (SH_URL, 'weblogUpdates.extendedPing',
            vector (nam, url, url, url || 'gems/rss.xml'));
          }
            }
          else if (SH_PROTO = 'REST')
            {
              declare hf, ping_url any;
              ping_url := sprintf ('%s%U', SH_URL, url);
              http_get (ping_url, hf);


              if (isarray (hf) and length (hf) and hf[0] not like 'HTTP/1._ 200 %')
          {
            rc := xml_tree (sprintf ('<response><flerror>1</flerror><message>%V</message></response>', hf[0]));
          }
            }
        }
        
        if (isarray(rc))
        {
          declare xt any;
          declare err, msg any;
          xt := xml_tree_doc (rc);
          err := cast (xpath_eval ('//flerror/text()', xml_cut(xt), 1) as varchar);
          msg := cast (xpath_eval ('//message/text()', xml_cut(xt), 1) as varchar);
          if (err <> '0')
          {
            update NNTPF_PING_LOG set NPL_ERROR = msg, NPL_STAT = 2, NPL_SENT = now ()
               where NPL_NG_GROUP = _ng_group and NPL_HOST_ID = _host_id and NPL_STAT = 0 and NPL_SEQ = seq;
            commit work;
            goto next;
          }
        }
        }
      }
      update NNTPF_PING_LOG set NPL_STAT = 1, NPL_SENT = now () , NPL_ERROR=null where NPL_NG_GROUP = _ng_group and NPL_HOST_ID = _host_id and NPL_STAT = 0 and NPL_SEQ = seq;
      commit work;
      next:;
  }
  ret:
  close cr;
  return;
};


 
create trigger WEBSVC_PING_I_NNFE_THR after insert on DB.DBA.NNFE_THR referencing new as N
{

declare _ng_name varchar;

declare exit handler for not found {_ng_name:='';};
select NG_NAME into _ng_name from DB.DBA.NEWS_GROUPS where NG_GROUP=N.FTHR_GROUP;

NNTPF_PING(
           N.FTHR_GROUP,
           concat('Newsgroup ',cast(_ng_name as varchar),' changed on ',left(datestring(N.FTHR_DATE),19)),
           DB.DBA.WA_LINK (1,concat('/nntpf/nntpf_nthread_view.vspx?group=',cast(N.FTHR_GROUP as varchar)))
          );


};

insert soft "DB"."DBA"."SYS_SCHEDULED_EVENT" (SE_INTERVAL, SE_LAST_COMPLETED, SE_NAME, SE_SQL, SE_START)
  values (10, NULL, 'NNTPF NOTIFICATIONS', 'DB.DBA.NNTPF_SVC_PROCESS_PINGS()', now());