|
NCBI Home IEB Home C Toolkit docs C++ Toolkit source browser C Toolkit source browser (2) |
NCBI C Toolkit Cross ReferenceC/biostruc/PubStruct_proc.scr |
source navigation diff markup identifier search freetext search file search |
1 /*
2 * $Id: PubStruct_proc.scr,v 6.18 2000/09/14 22:24:39 kimelman Exp $
3 *
4 * $Log: PubStruct_proc.scr,v $
5 * Revision 6.18 2000/09/14 22:24:39 kimelman
6 * bugfixes
7 *
8 * Revision 6.17 2000/09/13 16:59:38 kimelman
9 * enable retrival of dead mmdbs
10 *
11 * Revision 6.16 1999/06/15 20:37:48 kimelman
12 * id_get_asn_prop: synced to OS
13 *
14 * Revision 6.15 1999/05/03 14:44:35 kimelman
15 * spaces
16 *
17 * Revision 6.14 1999/03/16 16:56:28 kimelman
18 * new ID fixes
19 *
20 * Revision 6.13 1998/11/06 18:59:06 kimelman
21 * PubStruct loading transaction granularity changed
22 *
23 * Revision 6.12 1998/10/28 23:14:45 kimelman
24 * id_get_asn synced to OS
25 *
26 * Revision 6.11 1998/10/20 15:58:31 kimelman
27 * synchronization bugfix
28 *
29 * Revision 6.10 1998/08/08 04:49:45 kimelman
30 * bugfix: state type and processing for negative values
31 *
32 * Revision 6.9 1998/08/05 17:51:02 kimelman
33 * --enforce mode & /tmp downpath
34 *
35 * Revision 6.8 1998/07/14 20:24:49 kimelman
36 * FT schema & smart load
37 *
38 * Revision 6.7 1998/06/12 17:45:00 kimelman
39 * timestamps history fixed, vacuum cleaning debugged
40 *
41 * Revision 6.6 1998/06/05 18:19:23 kimelman
42 * atextract styled
43 *
44 * Revision 6.5 1998/06/05 17:59:18 kimelman
45 * structure takeover bug fixed
46 */
47 /************************************************************************/
48 /***** Documentation By Armadillo Tools *****/
49 /***** Release 3.0.0 Source Version *****/
50 /***** Copyright Panttaja Consulting Group, Inc. 1991-1994 *****/
51 /************************************************************************/
52 /***** FILE: PubStruct_proc.scr *****/
53 /***** SOURCE DB: PubStruct *****/
54 /***** SERVERNAME: BACH10 *****/
55 /***** CLASS: procedures *****/
56 /***** REPORT TYPE: sch *****/
57 /***** DATE OF RUN: Fri Jun 5 14:10:09 1998 *****/
58 /************************************************************************/
59
60 /************************************************************************/
61 PRINT '/***** SELECT DATABASE PubStruct *****/'
62 /************************************************************************/
63 go
64 USE PubStruct
65 go
66
67 /************************************************************************/
68 PRINT '/***** PROCEDURES *****/'
69 /************************************************************************/
70 go
71 IF EXISTS (SELECT * FROM sysobjects
72 WHERE name = 'pdb2mmdb'
73 AND uid = user_id('dbo')
74 AND type = 'P')
75 BEGIN
76 DROP PROCEDURE dbo.pdb2mmdb
77 END
78 go
79 /************************************************************************/
80 PRINT '/***** PROCEDURE pdb2mmdb *****/'
81 /************************************************************************/
82 go
83
84 create proc
85 pdb2mmdb(@pdb_id char(4))
86 as
87 select gi=p.mmdb_id from pdb p where p.pdb_id = @pdb_id and exists
88 (select * from Struct s where s.mmdb_id = p.mmdb_id and state = 0 and suppressed = 0 )
89 go
90 /***** Grant and Revoke permissions pdb2mmdb *****/
91 go
92 GRANT EXECUTE ON pdb2mmdb TO public
93 go
94
95
96 IF EXISTS (SELECT * FROM sysobjects
97 WHERE name = 'mmdb2acc'
98 AND uid = user_id('dbo')
99 AND type = 'P')
100 BEGIN
101 DROP PROCEDURE dbo.mmdb2acc
102 END
103 go
104 /************************************************************************/
105 PRINT '/***** PROCEDURE mmdb2acc *****/'
106 /************************************************************************/
107 go
108
109 create proc
110 mmdb2acc (
111 @mmdb int, /* mmdb */
112 @state int,
113 @acc int output,
114 )
115 as
116 declare @stat int
117 declare @suppress int
118 select @stat = @state
119 if @stat < 0
120 begin
121 select @stat = max(state)
122 from Struct
123 where mmdb_id = @gi and suppressed = 0 and state <= -@stat-1
124 end
125 select @suppress = min(suppressed) from Struct where mmdb_id = @gi and state = @stat and datalength(blob)
126
127 select @acc=acc
128 from Struct
129 where mmdb_id = @gi and suppressed = @suppress and state = @stat
130 if @@rowcount = 0 /* looks to be removed */
131 select @acc = -1
132 go
133
134
135 IF EXISTS (SELECT * FROM sysobjects
136 WHERE name = 'id_find_gi'
137 AND uid = user_id('dbo')
138 AND type = 'P')
139 BEGIN
140 DROP PROCEDURE dbo.id_find_gi
141 END
142 go
143 /************************************************************************/
144 PRINT '/***** PROCEDURE id_find_gi *****/'
145 /************************************************************************/
146 go
147
148 create proc
149 id_find_gi (
150 @gi int, /* mmdb */
151 @state int = 0
152 )
153 as
154 declare @acc int
155 declare @live tinyint
156
157 exec mmdb2acc @gi,@state, @acc=@acc output
158 if exists ( select * from Struct where acc = @acc and suppressed = 0 )
159 select @live = 100
160 else
161 select @live = 125
162
163 select sat=convert(smallint,10),sat_key=@acc,gi_state=@live,0,0,"01/01/1900"
164 from Struct
165 where acc = @acc
166 go
167 /***** Grant and Revoke permissions id_find_gi *****/
168 go
169 GRANT EXECUTE ON id_find_gi TO public
170 go
171
172 IF EXISTS (SELECT * FROM sysobjects
173 WHERE name = 'id_get_asnprop'
174 AND uid = user_id('dbo')
175 AND type = 'P')
176 BEGIN
177 DROP PROCEDURE dbo.id_get_asnprop
178 END
179 go
180 /************************************************************************/
181 PRINT '/***** PROCEDURE id_get_asnprop *****/'
182 /************************************************************************/
183 go
184
185 create proc
186 id_get_asnprop ( @sat_key int)
187 as
188 declare @state tinyint
189 select @state=suppressed from Struct where acc = @sat_key
190 if @@rowcount > 0
191 begin
192 if @state > 0
193 select @state=125
194 else
195 select @state=100
196 select state=@state,confidential, suppress=suppressed, override=convert(tinyint,0),
197 length=datalength(blob), owner=convert(smallint,0), "unknown","N/A"
198 from Struct
199 where acc = @sat_key
200 end
201 go
202 /***** Grant and Revoke permissions id_get_asnprop *****/
203 go
204 GRANT EXECUTE ON id_get_asnprop TO public
205 go
206 IF EXISTS (SELECT * FROM sysobjects
207 WHERE name = 'id_get_asnblob'
208 AND uid = user_id('dbo')
209 AND type = 'P')
210 BEGIN
211 DROP PROCEDURE dbo.id_get_asnblob
212 END
213 go
214 /************************************************************************/
215 PRINT '/***** PROCEDURE id_get_asnblob *****/'
216 /************************************************************************/
217 go
218
219 create proc
220 id_get_asnblob ( @sat_key int)
221 as
222 select asn=blob
223 from Struct
224 where @sat_key = acc
225 go
226 /***** Grant and Revoke permissions id_get_asnblob *****/
227 go
228 GRANT EXECUTE ON id_get_asnblob TO public
229 go
230 IF EXISTS (SELECT * FROM sysobjects
231 WHERE name = 'id_get_gi_content'
232 AND uid = user_id('dbo')
233 AND type = 'P')
234 BEGIN
235 DROP PROCEDURE dbo.id_get_gi_content
236 END
237 go
238 /************************************************************************/
239 PRINT '/***** PROCEDURE id_get_gi_content *****/'
240 /************************************************************************/
241 go
242
243 create proc
244 id_get_gi_content(
245 @sat_key int,
246 @sat smallint=10
247 )
248 as
249 declare @mod_date datetime
250 declare @date_entrez datetime
251 declare @create_date datetime
252 declare @gi int
253 declare @pdb_id char(4)
254 declare @mmdb_id int
255
256 if(@sat != 10)
257 return 100
258
259 select @mmdb_id= mmdb_id
260 from Struct
261 where acc = @sat_key
262
263 select @pdb_id=pdb_id
264 from pdb
265 where mmdb_id = @mmdb_id
266
267 select @mod_date=min(date)
268 from Struct s
269 where s.mmdb_id = @mmdb_id
270
271 select @mod_date=isnull(min(date),@mod_date)
272 from Struct s
273 where s.mmdb_id = @mmdb_id and s.state = 0
274
275 select @date_entrez=min(date)
276 from Struct s
277 where s.mmdb_id = @mmdb_id and s.state = 0
278
279 select @create_date=min(date)
280 from Struct
281 where mmdb_id in (select mmdb_id from pdb where pdb_id = @pdb_id)
282
283 select @create_date=isnull(min(date),@create_date)
284 from Struct s
285 where s.state = 0 and
286 s.mmdb_id in (select mmdb_id from pdb where pdb_id = @pdb_id)
287
288 select gi =@mmdb_id,
289 create_date=@create_date,
290 mod_date =@mod_date,
291 entrez_date=@date_entrez
292
293 return 0
294
295 go
296 /***** Grant and Revoke permissions id_get_gi_content *****/
297 go
298 GRANT EXECUTE ON id_get_gi_content TO public
299 go
300
301 IF EXISTS (SELECT * FROM sysobjects
302 WHERE name = 'id_get_asn'
303 AND uid = user_id('dbo')
304 AND type = 'P')
305 BEGIN
306 DROP PROCEDURE dbo.id_get_asn
307 END
308 go
309 /************************************************************************/
310 PRINT '/***** PROCEDURE id_get_asn *****/'
311 /************************************************************************/
312 go
313
314 create proc
315 id_get_asn (
316 @gi int=0,
317 @sat_key int=0,
318 @sat smallint=0,
319 @maxplex int=0,
320 @outfmt int=0
321 )
322 as
323 if(@sat != 10)
324 return 100
325
326 if(@outfmt !=100 and @outfmt != 0)
327 return 100
328
329 if(@sat_key=0)
330 begin
331 declare @acc int
332
333 exec mmdb2acc @gi,0, @acc=@acc output
334
335 select @sat_key = acc from Struct s where acc = @acc
336 if(@@rowcount = 0)
337 return 100
338 end
339
340 exec id_get_asnprop @sat_key
341 exec id_get_asnblob @sat_key
342
343 if(@outfmt=100)
344 exec id_get_gi_content @sat_key,@sat
345
346 return 0
347
348 go
349 /***** Grant and Revoke permissions id_get_asn *****/
350 go
351 GRANT EXECUTE ON id_get_asn TO public
352 go
353
354 IF EXISTS (SELECT * FROM sysobjects
355 WHERE name = 'rm_struct'
356 AND uid = user_id('dbo')
357 AND type = 'P')
358 BEGIN
359 DROP PROCEDURE dbo.rm_struct
360 END
361 go
362 /************************************************************************/
363 PRINT '/***** PROCEDURE rm_struct *****/'
364 /************************************************************************/
365 go
366
367 create proc
368 rm_struct( @acc int)
369 as
370 declare @mmdb int
371 declare @state_in tinyint
372 declare @suppr tinyint
373
374 if (select count(*) from Struct
375 where acc = @acc ) <= 0
376 begin
377 raiserror 18001 "Structure unavailable"
378 rollback transaction
379 return -1
380 end
381
382 select @mmdb = mmdb_id, @state_in = state, @suppr = suppressed
383 from Struct
384 where acc = @acc
385 if ( @suppr <> 0 )
386 return 0
387
388 /* suppress all blobs with the same mmdb_id and requied state */
389 update Struct
390 set suppressed = suppressed + 1
391 where mmdb_id = @mmdb and state = @state_in
392 go
393 /***** Grant and Revoke permissions rm_struct *****/
394 go
395 GRANT EXECUTE ON rm_struct TO public
396 go
397 IF EXISTS (SELECT * FROM sysobjects
398 WHERE name = 'push_struct'
399 AND uid = user_id('dbo')
400 AND type = 'P')
401 BEGIN
402 DROP PROCEDURE dbo.push_struct
403 END
404 go
405 /************************************************************************/
406 PRINT '/***** PROCEDURE push_struct *****/'
407 /************************************************************************/
408 go
409
410 create proc
411 push_struct( @acc int, @state_out tinyint=0,@date datetime=null,@suppr tinyint = 0)
412 as
413 declare @mmdb_id int
414 declare @state_in tinyint
415 declare @pdb_id char(4)
416
417 if (select count(*) from Struct
418 where acc = @acc ) <= 0
419 begin
420 raiserror 18001 "Structure unavailable"
421 rollback transaction
422 return -1
423 end
424 select @mmdb_id = s.mmdb_id, @state_in = s.state, @pdb_id=p.pdb_id
425 from Struct s, pdb p
426 where acc = @acc and p.mmdb_id = s.mmdb_id
427 /* suppress all blobs with the same mmdb_id and requied state */
428 update Struct
429 set suppressed = suppressed + 1
430 where mmdb_id=@mmdb_id and state = @state_out
431 if @state_out = 0 and @suppr = 0
432 begin
433 /* suppress all other mmdb's related to the same pdb_id */
434 update Struct
435 set suppressed = suppressed + 1
436 where mmdb_id in ( select mmdb_id from pdb where mmdb_id != @mmdb_id and pdb_id = @pdb_id)
437 end
438 /* change structure status */
439 update Struct
440 set state = @state_out, date = isnull(@date,getdate()), suppressed = @suppr
441 where acc = @acc
442
443 go
444 /***** Grant and Revoke permissions push_struct *****/
445 go
446 GRANT EXECUTE ON push_struct TO public
447 go
448
449 IF EXISTS (SELECT * FROM sysobjects
450 WHERE name = 'new_entry'
451 AND uid = user_id('dbo')
452 AND type = 'P')
453 BEGIN
454 DROP PROCEDURE dbo.new_entry
455 END
456 go
457 /************************************************************************/
458 PRINT '/***** PROCEDURE new_entry *****/'
459 /************************************************************************/
460 go
461
462 create proc
463 new_entry(@acc int = null output)
464 as
465 begin transaction new_entry_1
466 update acc set ticket=ticket+1 where kind = 1
467 select @acc=ticket-1 from acc where kind = 1
468 commit transaction new_entry_1
469 go
470 /***** Grant and Revoke permissions new_struct *****/
471 go
472
473 IF EXISTS (SELECT * FROM sysobjects
474 WHERE name = 'new_struct'
475 AND uid = user_id('dbo')
476 AND type = 'P')
477 BEGIN
478 DROP PROCEDURE dbo.new_struct
479 END
480 go
481 /************************************************************************/
482 PRINT '/***** PROCEDURE new_struct *****/'
483 /************************************************************************/
484 go
485
486 create proc
487 new_struct(@state_out tinyint=1)
488 as
489 declare @acc int
490
491 exec new_entry @acc=@acc output
492
493 begin transaction new_struct_1
494 insert into Struct(acc ,mmdb_id , state , date , suppressed, confidential, blob)
495 values(@acc,0 ,@state_out,getdate(), 0 , 0 , null)
496 update Struct set blob = null where acc = @acc
497 select @acc
498 commit transaction new_struct_1
499 go
500 GRANT EXECUTE ON new_struct TO public
501 go
502
503
504
505 IF EXISTS (SELECT * FROM sysobjects
506 WHERE name = 'new_struct1'
507 AND uid = user_id('dbo')
508 AND type = 'P')
509 BEGIN
510 DROP PROCEDURE dbo.new_struct1
511 END
512 go
513 /************************************************************************/
514 PRINT '/***** PROCEDURE new_struct1 *****/'
515 /************************************************************************/
516 go
517
518 create proc
519 new_struct1(
520 @acc int,
521 @mmdb int,
522 @date datetime = null,
523 @pdb_id char(4) = null
524 )
525 as
526 declare @state_out tinyint
527 /* QA */
528
529 if ( select count(*) from pdb where mmdb_id = @mmdb ) = 0
530 begin
531 begin transaction new_struct1_QA
532 insert into pdb(mmdb_id,pdb_id) values(@mmdb,isnull(@pdb_id,str(@mmdb)))
533 commit transaction new_struct1_QA
534 end
535 else if ( select count(*) from pdb where mmdb_id = @mmdb and pdb_id = @pdb_id ) != 1
536 begin
537 declare @msg char
538 select @msg = 'pdb identifier ['+@pdb_id+'changed for given mmdb ('+str(@mmdb)+')'
539 raiserror 18001 @msg
540 return -1
541 end
542
543 begin transaction
544
545 update Struct
546 set mmdb_id = @mmdb
547 where acc = @acc
548
549 select @state_out = state
550 from Struct
551 where acc = @acc
552
553 exec push_struct @acc,@state_out
554
555 commit transaction
556
557 /* check for initial loading */
558 if ( (select count(*) from Struct where mmdb_id = @mmdb ) = 1 and @date is not null )
559 begin
560 exec new_entry @acc=@acc output
561 begin transaction
562 insert into Struct(acc ,mmdb_id , state , date , suppressed, confidential, blob)
563 values(@acc,@mmdb , 0 , @date , 1 , 0 , null)
564 commit transaction
565 end
566 go
567 /***** Grant and Revoke permissions new_struct1 *****/
568 go
569 GRANT EXECUTE ON new_struct1 TO public
570 go
571 IF EXISTS (SELECT * FROM sysobjects
572 WHERE name = 'get_props'
573 AND uid = user_id('dbo')
574 AND type = 'P')
575 BEGIN
576 DROP PROCEDURE dbo.get_props
577 END
578 go
579 /************************************************************************/
580 PRINT '/***** PROCEDURE get_props *****/'
581 /************************************************************************/
582 go
583
584 create proc
585 get_props ( @acc int)
586 as
587 select acc,mmdb_id,state,date,suppressed,confidential,datalength(blob)
588 from Struct
589 where acc = @acc
590
591 go
592 IF EXISTS (SELECT * FROM sysobjects
593 WHERE name = 'vacuum_cleaning'
594 AND uid = user_id('dbo')
595 AND type = 'P')
596 BEGIN
597 DROP PROCEDURE dbo.vacuum_cleaning
598 END
599 go
600 /************************************************************************/
601 PRINT '/***** PROCEDURE vacuum_cleaning *****/'
602 /************************************************************************/
603 go
604
605 create proc
606 vacuum_cleaning ( @days int = 0 )
607 as
608 declare @acc int
609 declare @obv_date datetime
610 declare @stamp datetime
611
612 /* set the obvilion date */
613 select @obv_date = dateadd(day,-@days,getdate())
614 select @stamp = getdate()
615
616 delete from Struct
617 where suppressed > 0 and state > 0 and date < @obv_date
618
619 delete Struct
620 from Struct s
621 where suppressed > 0 and state = 0 and date < @obv_date and
622 datalength(blob) > 0
623
624 update statistics Struct
625 go
|
This page was automatically generated by the
LXR engine.
Visit the LXR main site for more information. |