|
NCBI Home IEB Home C Toolkit docs C++ Toolkit source browser C Toolkit source browser (2) |
NCBI C Toolkit Cross ReferenceC/biostruc/Entrez.scr |
source navigation diff markup identifier search freetext search file search |
1 /*
2 * $Id: Entrez.scr,v 6.16 2000/09/13 16:59:38 kimelman Exp $
3 *
4 * This file contains the additions to PubStruct Database on Public
5 * Entrez Servers
6 *
7 * $Log: Entrez.scr,v $
8 * Revision 6.16 2000/09/13 16:59:38 kimelman
9 * enable retrival of dead mmdbs
10 *
11 * Revision 6.15 1999/10/22 17:50:35 kimelman
12 * synced to OS
13 *
14 * Revision 6.14 1999/07/01 21:40:20 kimelman
15 * qa_checks extended
16 *
17 * Revision 6.13 1999/06/15 20:37:48 kimelman
18 * id_get_asn_prop: synced to OS
19 *
20 * Revision 6.12 1999/06/09 02:01:51 kimelman
21 * typo
22 *
23 * Revision 6.11 1999/06/09 01:59:31 kimelman
24 * e2index input order fixed
25 *
26 * Revision 6.10 1999/06/08 03:54:20 kimelman
27 * entrez indexes ordering fixed
28 *
29 * Revision 6.9 1999/05/11 21:41:23 kimelman
30 * 1. qa_check proc added
31 * 2. this script now removes all procs not required on public/retrieval site
32 *
33 * Revision 6.8 1999/05/03 14:47:26 kimelman
34 * spaces
35 *
36 * Revision 6.7 1999/04/22 01:51:51 kimelman
37 * Move Entrez indexing procs to _active DB only
38 * create list2index mode for selected reindexing
39 *
40 * Revision 6.6 1999/03/16 16:56:28 kimelman
41 * new ID fixes
42 *
43 * Revision 6.5 1998/10/26 20:53:43 kimelman
44 * bugfix: timerounding to minutes before comparision and print format fioxes
45 *
46 * Revision 6.4 1998/10/05 17:47:03 kimelman
47 * Fix for dates problem
48 *
49 * Revision 6.3 1998/07/27 19:35:57 kimelman
50 * fresh & full lists 2 index reordered in according to 'entrez' modification dates
51 *
52 * Revision 6.2 1998/07/22 22:06:09 kimelman
53 * Enterez removed list : satkey sat date ==> mmdb
54 *
55 * Revision 6.1 1998/07/14 20:24:42 kimelman
56 * FT schema & smart load
57 *
58 * Log: PubStruct_proc.scr,v
59 * Revision 6.7 1998/06/12 17:45:00 kimelman
60 * timestamps history fixed, vacuum cleaning debugged
61 *
62 * Revision 6.6 1998/06/05 18:19:23 kimelman
63 * atextract styled
64 *
65 * Revision 6.5 1998/06/05 17:59:18 kimelman
66 * structure takeover bug fixed
67 */
68 /************************************************************************/
69 PRINT '/***** SELECT DATABASE PubStruct *****/'
70 /************************************************************************/
71 go
72 USE PubStruct
73 go
74
75
76 IF EXISTS (SELECT * FROM sysobjects
77 WHERE name = 'mmdb2acc'
78 AND uid = user_id('dbo')
79 AND type = 'P')
80 BEGIN
81 DROP PROCEDURE dbo.mmdb2acc
82 END
83 go
84 /************************************************************************/
85 PRINT '/***** PROCEDURE mmdb2acc *****/'
86 /************************************************************************/
87 go
88
89 create proc
90 mmdb2acc (
91 @mmdb int, /* mmdb */
92 @state int,
93 @acc int output
94 )
95 as
96 declare @stat int
97 declare @suppress int
98 select @stat = @state
99 if @stat < 0
100 begin
101 select @stat = max(state)
102 from Struct
103 where mmdb_id = @gi and suppressed = 0 and state <= -@stat-1
104 end
105 select @suppress = min(suppressed) from Struct where mmdb_id = @gi and state = @stat and datalength(blob)
106
107 select @acc=acc
108 from Struct
109 where mmdb_id = @gi and suppressed = @suppress and state = @stat
110 go
111
112
113 IF EXISTS (SELECT * FROM sysobjects
114 WHERE name = 'id_find_gi'
115 AND uid = user_id('dbo')
116 AND type = 'P')
117 BEGIN
118 DROP PROCEDURE dbo.id_find_gi
119 END
120 go
121 /************************************************************************/
122 PRINT '/***** PROCEDURE id_find_gi *****/'
123 /************************************************************************/
124 go
125
126 create proc
127 id_find_gi (
128 @gi int, /* mmdb */
129 @state tinyint = 0
130 )
131 as
132 declare @acc int
133
134 select @acc=acc
135 from Struct
136 where mmdb_id = @gi and state = @state and suppressed = 0
137
138 if @@rowcount = 0 /* looks to be removed */
139 select @acc = -1
140
141 if @state = 0
142 begin
143 declare @acc1 int
144
145 select @acc=acc
146 from Struct s, EntrezControl..SatKeyFlags i
147 where s.mmdb_id = @gi and s.state = 0 and s.acc = i.sat_key and i.sat = 10 and i. dumped4entrez = 1
148
149 if @@rowcount = 0 /* looks to be removed */
150 select @acc = -1
151 end
152 if @acc = -1
153 exec mmdb2acc @gi,@state, @acc=@acc output
154 if @acc > 0
155 begin
156 if exists ( select * from Struct where acc = @acc and suppressed = 0 )
157 select sat=convert(smallint,10),sat_key=@acc,gi_state=convert(tinyint,100),0,0,"01/01/1900"
158 else
159 select sat=convert(smallint,10),sat_key=@acc,gi_state=convert(tinyint,125),0,0,"01/01/1900"
160 end
161 go
162 /***** Grant and Revoke permissions id_find_gi *****/
163 GRANT EXECUTE ON id_find_gi TO public
164 go
165 IF EXISTS (SELECT * FROM sysobjects
166 WHERE name = 'id_get_asnprop'
167 AND uid = user_id('dbo')
168 AND type = 'P')
169 BEGIN
170 DROP PROCEDURE dbo.id_get_asnprop
171 END
172 go
173 /************************************************************************/
174 PRINT '/***** PROCEDURE id_get_asnprop *****/'
175 /************************************************************************/
176 go
177
178 create proc
179 id_get_asnprop ( @sat_key int)
180 as
181 declare @state tinyint
182
183 select @state = suppressed from Struct where acc = @sat_key
184 if @state > 0
185 select @state = 125
186 else
187 select @state = 100
188 select state=@state,confidential, suppress=convert(tinyint,0), override=convert(tinyint,0),
189 length=datalength(blob), owner=convert(smallint,0),"unknown","N/A",class=convert(tinyint,0)
190 from Struct
191 where acc = @sat_key
192 go
193 /***** Grant and Revoke permissions id_get_asnprop *****/
194 go
195 GRANT EXECUTE ON id_get_asnprop TO public
196 go
197 IF EXISTS (SELECT * FROM sysobjects
198 WHERE name = 'id_get_asnblob'
199 AND uid = user_id('dbo')
200 AND type = 'P')
201 BEGIN
202 DROP PROCEDURE dbo.id_get_asnblob
203 END
204 go
205 /************************************************************************/
206 PRINT '/***** PROCEDURE id_get_asnblob *****/'
207 /************************************************************************/
208 go
209
210 create proc
211 id_get_asnblob ( @sat_key int)
212 as
213 select asn=blob
214 from Struct
215 where @sat_key = acc
216
217 go
218 /***** Grant and Revoke permissions id_get_asnblob *****/
219 go
220 GRANT EXECUTE ON id_get_asnblob TO public
221 go
222 IF EXISTS (SELECT * FROM sysobjects
223 WHERE name = 'id_get_gi_content'
224 AND uid = user_id('dbo')
225 AND type = 'P')
226 BEGIN
227 DROP PROCEDURE dbo.id_get_gi_content
228 END
229 go
230 /************************************************************************/
231 PRINT '/***** PROCEDURE id_get_gi_content *****/'
232 /************************************************************************/
233 go
234
235 create proc
236 id_get_gi_content (
237 @sat_key int,
238 @sat smallint=10
239 )
240 as
241 declare @mod_date datetime
242 declare @date_entrez datetime
243 declare @create_date datetime
244 declare @gi int
245 declare @pdb_id char(4)
246 declare @mmdb_id int
247
248 if(@sat != 10)
249 return 100
250
251
252 select @mmdb_id= mmdb_id
253 from Struct
254 where acc = @sat_key
255
256 select @pdb_id=pdb_id
257 from pdb
258 where mmdb_id = @mmdb_id
259
260 /* create date */
261 select @create_date=min(date)
262 from Struct
263 where state = 0 and mmdb_id in (select mmdb_id from pdb where pdb_id = @pdb_id)
264
265 select @create_date=isnull(min(i.date_entrez),@create_date)
266 from Struct s, EntrezControl..SatKeyFlags i
267 where s.state = 0 and s.mmdb_id in (select mmdb_id from pdb where pdb_id = @pdb_id)
268 and s.acc = i.sat_key and i.sat = 10
269
270 /* Modificatio Date */
271 select @mod_date=min(date)
272 from Struct s
273 where s.state = 0 and s.mmdb_id = @mmdb_id
274
275 select @mod_date=isnull(min(i.date_entrez),@mod_date)
276 from Struct s, EntrezControl..SatKeyFlags i
277 where s.state = 0 and s.mmdb_id = @mmdb_id
278 and s.acc = i.sat_key and i.sat = 10
279
280 /* Entrez Publication Date */
281 select @date_entrez=min(i.date_entrez)
282 from Struct s, EntrezControl..SatKeyFlags i
283 where s.mmdb_id = @mmdb_id and s.state = 0 and s.acc = i.sat_key and i.sat = 10
284 and i.date_entrez is not null
285
286 select gi =@mmdb_id,
287 create_date=@create_date,
288 mod_date =@mod_date,
289 entrez_date=@date_entrez
290
291 return 0
292
293 go
294 /***** Grant and Revoke permissions id_get_gi_content *****/
295 go
296 GRANT EXECUTE ON id_get_gi_content TO public
297 go
298 IF EXISTS (SELECT * FROM sysobjects
299 WHERE name = 'id_get_asn'
300 AND uid = user_id('dbo')
301 AND type = 'P')
302 BEGIN
303 DROP PROCEDURE dbo.id_get_asn
304 END
305 go
306 /************************************************************************/
307 PRINT '/***** PROCEDURE id_get_asn *****/'
308 /************************************************************************/
309 go
310
311 create proc
312 id_get_asn(
313 @gi int=0,
314 @sat_key int=0,
315 @sat smallint=0,
316 @maxplex int=0,
317 @outfmt int=0
318 )
319 as
320
321 if(@sat != 10)
322 return 100
323
324 if(@outfmt !=100 and @outfmt != 0)
325 return 100
326
327 if(@sat_key=0)
328 begin
329 select @sat_key = acc
330 from Struct s, EntrezControl..SatKeyFlags i
331 where mmdb_id = @gi and state = 0 and s.acc = i.sat_key and i.sat = 10 and i.dumped4entrez =1
332 if(@@rowcount = 0)
333 begin
334 declare @acc int
335 exec mmdb2acc @gi,0, @acc=@acc output
336
337 select @sat_key = acc from Struct s where acc = @acc
338 if(@@rowcount = 0)
339 return 100
340 end
341 end
342
343 exec id_get_asnprop @sat_key
344 exec id_get_asnblob @sat_key
345
346 if(@outfmt=100)
347 exec id_get_gi_content @sat_key,@sat
348
349 return 0
350
351 go
352 /***** Grant and Revoke permissions id_get_asn *****/
353 go
354 GRANT EXECUTE ON id_get_asn TO public
355 go
356
357 IF EXISTS (SELECT * FROM sysobjects
358 WHERE name = 'list2index'
359 AND uid = user_id('dbo')
360 AND type = 'P')
361 BEGIN
362 DROP PROCEDURE dbo.list2index
363 END
364 go
365 /************************************************************************/
366 PRINT '/***** PROCEDURE list2index *****/'
367 /************************************************************************/
368 go
369
370 create proc
371 list2index(@full int = 0,@mmdb_id int = 0)
372 as
373 begin
374 if (@full = 1)
375 begin
376 select 'fresh ' , s.acc, '10',
377 mod_date=datediff(minute,'Jan 1 1900', isnull(min(i1.date_entrez),min(s1.date)))
378 from Struct s, Struct s1, EntrezControl..SatKeyFlags i, EntrezControl..SatKeyFlags i1
379 where s.state = 0 and s.suppressed = 0 and i.sat_key = s.acc and i.sat=10 and i.date_entrez is null
380 and s.mmdb_id = s1.mmdb_id
381 and s1.state = 0 and i1.sat_key = s1.acc and i1.sat=10
382 and s.mmdb_id > 0
383 group by s.acc,s.mmdb_id
384 order by mod_date,s.mmdb_id
385 end
386 else if(@full = 2) /* enforced reindex cases */
387 begin
388 select 'update ' , s.acc, '10',
389 mod_date=datediff(minute,'Jan 1 1900', isnull(min(i1.date_entrez),min(s1.date)))
390 from Struct s, Struct s1, EntrezControl..SatKeyFlags i1
391 where s.state = 0 and s.suppressed = 0
392 and s.mmdb_id = s1.mmdb_id
393 and s1.state = 0 and i1.sat_key = s1.acc and i1.sat=10
394 and s.mmdb_id = @mmdb_id
395 and s.mmdb_id > 0
396 group by s.acc
397 end
398 else if(@full = -1)
399 begin
400 select 'removed ', mmdb_id
401 from Struct s, EntrezControl..SatKeyFlags i
402 where state = 0 and suppressed > 0 and i.sat_key = s.acc and i.sat=10 and i.dumped4entrez = 1
403 and not exists ( select * from Struct b
404 where b.mmdb_id = s.mmdb_id and b.state = 0 and b.suppressed = 0 )
405 order by mmdb_id
406 end
407 else if(@full = 0)
408 begin
409 select 'data ' , s.acc, '10',
410 mod_date=datediff(minute,'Jan 1 1900', isnull(min(i.date_entrez),min(s1.date)))
411 from Struct s, Struct s1, EntrezControl..SatKeyFlags i
412 where s1.state = 0 and s.mmdb_id = s1.mmdb_id and s.state = 0 and s.suppressed = 0 and
413 i.sat_key = s1.acc and i.sat=10
414 and s.mmdb_id > 0
415 group by s.acc,s.mmdb_id
416 order by mod_date,s.mmdb_id
417 end
418 end
419 go
420 GRANT EXECUTE ON list2index TO public
421 go
422
423 IF EXISTS (SELECT * FROM sysobjects
424 WHERE name = 'vacuum_entrez'
425 AND uid = user_id('dbo')
426 AND type = 'P')
427 BEGIN
428 DROP PROCEDURE dbo.vacuum_entrez
429 END
430 go
431 /************************************************************************/
432 PRINT '/***** PROCEDURE vacuum_entrez *****/'
433 /************************************************************************/
434 go
435
436 create proc
437 vacuum_entrez (@days int = 0)
438 as
439 declare @acc int
440 declare @obv_date datetime
441 declare @stamp datetime
442
443 /* set the obvilion date */
444 select @obv_date = dateadd(day,-@days,getdate())
445 select @stamp = getdate()
446
447 select acc
448 from Struct s, EntrezControl..SatKeyFlags i(1)
449 where s.state = 0 and i.sat_key = s.acc and i.sat = 10 and s.suppressed >0
450 and date < @obv_date and datalength(blob) > 0 and i.dumped4entrez = 0
451 go
452 GRANT EXECUTE ON vacuum_entrez TO anyone
453 go
454
455 IF EXISTS (SELECT * FROM sysobjects
456 WHERE name = 'qa_checks'
457 AND uid = user_id('dbo')
458 AND type = 'P')
459 BEGIN
460 DROP PROCEDURE dbo.qa_checks
461 END
462 go
463 /************************************************************************/
464 PRINT '/***** PROCEDURE qa_checks *****/'
465 /************************************************************************/
466 go
467
468 create proc
469 qa_checks
470 as
471 select mmdb_id,state from Struct
472 where suppressed = 0
473 group by mmdb_id,state
474 having count(*) > 1
475
476 select acc,state from Struct
477 where mmdb_id <=0
478
479 select mmdb_id from Struct s
480 where not exists ( select * from pdb p where p.mmdb_id = s.mmdb_id )
481
482 select mmdb_id,state from Struct s, EntrezControl..SatKeyFlags e
483 where sat = 10 and sat_key = acc
484 and dumped4entrez = 1
485 group by mmdb_id,state
486 having count(*) > 1
487
488 select acc from Struct s
489 where not exists (select * from EntrezControl..SatKeyFlags e
490 where sat = 10 and sat_key = s.acc )
491
492 select sat_key from EntrezControl..SatKeyFlags
493 where sat=10 and dumped4entrez=1 and date_entrez is null
494
495 go
496 GRANT EXECUTE ON qa_checks TO anyone
497 go
498 /************************************************************************/
499 PRINT '/***** DO QA checks *****/'
500 /************************************************************************/
501 go
502 execute qa_checks
503 go
504
505 /************************************************************************/
506 PRINT '/***** PROCEDURE post_index *****/'
507 /************************************************************************/
508 go
509 IF EXISTS (SELECT * FROM sysobjects
510 WHERE name = 'post_index'
511 AND uid = user_id('dbo')
512 AND type = 'P')
513 BEGIN
514 DROP PROCEDURE dbo.post_index
515 END
516 go
517
518 /************************************************************************/
519 PRINT '/***** PROCEDURE post_remove *****/'
520 /************************************************************************/
521 go
522 IF EXISTS (SELECT * FROM sysobjects
523 WHERE name = 'post_remove'
524 AND uid = user_id('dbo')
525 AND type = 'P')
526 BEGIN
527 DROP PROCEDURE dbo.post_remove
528 END
529 go
530
531 /************************************************************************/
532 PRINT '/***** TRIGGER at_insert *****/'
533 /************************************************************************/
534 go
535
536 IF EXISTS (SELECT * FROM sysobjects
537 WHERE name = 'at_insert'
538 AND uid = user_id('dbo')
539 AND type = 'TR')
540 BEGIN
541 DROP trigger dbo.at_insert
542 END
543 go
544
545 /************************************************************************/
546 PRINT '/***** TRIGGER at_delete *****/'
547 /************************************************************************/
548 go
549
550 IF EXISTS (SELECT * FROM sysobjects
551 WHERE name = 'at_delete'
552 AND uid = user_id('dbo')
553 AND type = 'TR')
554 BEGIN
555 DROP trigger dbo.at_delete
556 END
557 go
558
559 /************************************************************************/
560 PRINT '/***** PROCEDURE fill_satinfo *****/'
561 /************************************************************************/
562 go
563
564 IF EXISTS (SELECT * FROM sysobjects
565 WHERE uid = user_id('dbo')
566 AND type = 'P'
567 AND name = 'fill_satinfo')
568 BEGIN
569 DROP PROCEDURE dbo.fill_satinfo
570 END
571 go
572
573 /************************************************************************/
574 PRINT '/***** PROCEDURE rm_struct *****/'
575 /************************************************************************/
576 go
577
578 IF EXISTS (SELECT * FROM sysobjects
579 WHERE uid = user_id('dbo')
580 AND type = 'P'
581 AND name = 'rm_struct')
582 BEGIN
583 DROP PROCEDURE dbo.rm_struct
584 END
585 go
586
587
588 /************************************************************************/
589 PRINT '/***** PROCEDURE push_struct *****/'
590 /************************************************************************/
591 go
592
593 IF EXISTS (SELECT * FROM sysobjects
594 WHERE uid = user_id('dbo')
595 AND type = 'P'
596 AND name = 'push_struct')
597 BEGIN
598 DROP PROCEDURE dbo.push_struct
599 END
600 go
601
602
603 /************************************************************************/
604 PRINT '/***** PROCEDURE new_entry *****/'
605 /************************************************************************/
606 go
607
608 IF EXISTS (SELECT * FROM sysobjects
609 WHERE uid = user_id('dbo')
610 AND type = 'P'
611 AND name = 'new_entry')
612 BEGIN
613 DROP PROCEDURE dbo.new_entry
614 END
615 go
616
617
618 /************************************************************************/
619 PRINT '/***** PROCEDURE new_struct *****/'
620 /************************************************************************/
621 go
622
623 IF EXISTS (SELECT * FROM sysobjects
624 WHERE uid = user_id('dbo')
625 AND type = 'P'
626 AND name = 'new_struct')
627 BEGIN
628 DROP PROCEDURE dbo.new_struct
629 END
630 go
631
632
633 /************************************************************************/
634 PRINT '/***** PROCEDURE new_struct1 *****/'
635 /************************************************************************/
636 go
637
638 IF EXISTS (SELECT * FROM sysobjects
639 WHERE uid = user_id('dbo')
640 AND type = 'P'
641 AND name = 'new_struct1')
642 BEGIN
643 DROP PROCEDURE dbo.new_struct1
644 END
645 go
646
647
648 /************************************************************************/
649 PRINT '/***** PROCEDURE get_props *****/'
650 /************************************************************************/
651 go
652
653 IF EXISTS (SELECT * FROM sysobjects
654 WHERE uid = user_id('dbo')
655 AND type = 'P'
656 AND name = 'get_props')
657 BEGIN
658 DROP PROCEDURE dbo.get_props
659 END
660 go
661
662
663 /************************************************************************/
664 PRINT '/***** PROCEDURE vacuum_cleaning *****/'
665 /************************************************************************/
666 go
667
668 IF EXISTS (SELECT * FROM sysobjects
669 WHERE uid = user_id('dbo')
670 AND type = 'P'
671 AND name = 'vacuum_cleaning')
672 BEGIN
673 DROP PROCEDURE dbo.vacuum_cleaning
674 END
675 go
|
This page was automatically generated by the
LXR engine.
Visit the LXR main site for more information. |