/*++++++++++++++ .IDENTIFICATION metamore.sql .LANGUAGE ISQL .AUTHOR Sybase Administrator .ENVIRONMENT .KEYWORDS .VERSION 1.0 26-Oct-1995: RDESIS .VERSION 1.1 23-Dec-1995: New kwds_seq table. .VERSION 2.0 26-Apr-1997: META data-base .VERSION 2.1 09-Apr-2008: sum_statrm procedure .COMMENTS This procedure allows to add keywords into the Meta Data-base ---------------*/ /*============= Remove a catalogue from the Tables ============ * Usage: rm_cat catname ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "METAstat_rm") drop table METAstat_rm go Select * into METAstat_rm from METAstat where catid=0 go Select * from METAstat_rm go Delete METAstat_rm go IF EXISTS (select name from sysobjects where name = "METAhit_rm") drop table METAhit_rm go Select * into METAhit_rm from METAhit where catid=0 go Select * from METAhit_rm go Delete METAhit_rm go print "====Install procedure: rm_cat" IF EXISTS (select name from sysobjects where name = "rm_cat") drop PROCEDURE rm_cat go CREATE PROCEDURE rm_cat @name varchar(30) AS declare @catid int select @catid = catid from METAcat where name = @name IF (@catid is NULL) print "++++No such catalogue: %1!", @name ELSE BEGIN print "----Tables in catalog '%1!' to drop:", @catid Select dbaid, name, dbname from METAtab where catid = @catid IF ( @catid < 1000) BEGIN print "****Can't remove catalogue #%1!", @catid END ELSE BEGIN print "****Save METAstat in METAstat_rm" Select * from METAstat where catid = @catid Insert into METAstat_rm Select * from METAstat where catid = @catid print "****Save METAhit in METAhit_rm" Select * from METAhit where catid = @catid Insert into METAhit_rm Select * from METAhit where catid = @catid Select distinct "Use viz", dbaid from METAtab where catid = @catid Select "Drop table", dbname,";" from METAtab where catid = @catid delete from METAstat where catid = @catid delete from METAhit where catid = @catid delete from METAdig where catid = @catid delete from METAmor where catid = @catid delete from METAnot where catid = @catid delete from METAkwd where catid = @catid delete from METAcro where catid = @catid delete from METAqin where catid = @catid delete from METAcol where catid = @catid delete from METAcell00 where catid = @catid delete from METAcell02 where catid = @catid delete from METAcell03 where catid = @catid delete from METAcell09 where catid = @catid delete from METAcell0a where catid = @catid delete from METAcell0b where catid = @catid delete from METAcell0c where catid = @catid delete from METAcell0d where catid = @catid delete from METAcell0e where catid = @catid delete from METAtab where catid = @catid delete from METAcat where catid = @catid END END go /*============= Remove a catalogue from the Tables ============ * Usage: rm_catid catid ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "rm_catid") drop PROCEDURE rm_catid go CREATE PROCEDURE rm_catid @catid int AS print "----Tables in catalog '%1!' to drop:", @catid Select dbaid, name, dbname from METAtab where catid = @catid IF ( @catid < 1000) BEGIN print "****Can't remove catalogue #%1!", @catid END ELSE BEGIN print "****Save METAstat in METAstat_rm" Select * from METAstat where catid = @catid Insert into METAstat_rm Select * from METAstat where catid = @catid print "****Save METAhit in METAhit_rm" Select * from METAhit where catid = @catid Insert into METAhit_rm Select * from METAhit where catid = @catid Select distinct "Use viz", dbaid from METAtab where catid = @catid Select "Drop table", dbname,";" from METAtab where catid = @catid delete from METAstat where catid = @catid delete from METAhit where catid = @catid delete from METAdig where catid = @catid delete from METAmor where catid = @catid delete from METAnot where catid = @catid delete from METAkwd where catid = @catid delete from METAcro where catid = @catid delete from METAqin where catid = @catid delete from METAcol where catid = @catid delete from METAcell00 where catid = @catid delete from METAcell02 where catid = @catid delete from METAcell03 where catid = @catid delete from METAcell09 where catid = @catid delete from METAcell0a where catid = @catid delete from METAcell0b where catid = @catid delete from METAcell0c where catid = @catid delete from METAcell0d where catid = @catid delete from METAcell0e where catid = @catid delete from METAtab where catid = @catid delete from METAcat where catid = @catid END go /* Adds to METAstat the statistics stored in METAstat_rm */ print "====Install procedure: sum_statrm e.g. sum_statrm 2241" IF EXISTS (select name from sysobjects where name = "sum_statrm") drop PROCEDURE sum_statrm go CREATE PROCEDURE sum_statrm @catid int AS declare @n int select @n = count(*) from METAstat_rm if (@n != 5) print "****METAstat_rm does not contain 5 elements" ELSE BEGIN print "----Merge METAstat/METAhit for catalog '%1!'", @catid Update METAstat_rm set catid=@catid Update METAhit_rm set catid=@catid Select tbl="METAstat ", * from METAstat where catid=@catid Select tbl="METAstat_rm", * from METAstat_rm where catid=@catid Select tbl="METAhit ", * from METAhit where catid=@catid Select tbl="METAhit_rm ", * from METAhit_rm where catid=@catid Select * into #s from METAstat_rm Insert into #s select * from METAstat where catid=@catid Select catid, phase, tstart=MIN(tstart), tlocal=MAX(tlocal), tother=MAX(tother), nlocal=SUM(nlocal), nother=SUM(nother) into #s1 from #s group by catid, phase Delete METAstat where catid=@catid Insert into METAstat select * from #s1 Select tbl="METAstat ", * from METAstat where catid=@catid Select * into #h from METAhit_rm Insert into #h select * from METAhit where catid=@catid Select catid, hstid, btime, etime=max(etime), Ncalls=sum(Ncalls) into #h1 from #h group by catid, hstid, btime Delete METAhit where catid=@catid Insert into METAhit select * from #h1 Select tbl="METAhit ", * from METAhit where catid=@catid END go /* update METAstat set METAstat.nlocal = METAstat.nlocal+METAstat_rm.nlocal, METAstat.nother = METAstat.nother+METAstat_rm.nother from METAstat, METAstat_rm where METAstat.catid=METAstat_rm.catid and METAstat.phase=METAstat_rm.phase and METAstat.catid=@catid */ /*============= Remove the Index Coordinates ================== * Usage: rm_cells catid ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "rm_cells") drop PROCEDURE rm_cells go CREATE PROCEDURE rm_cells @catid int AS print "----CooIndex in catalog '%1!' to drop:", @catid delete from METAcell00 where catid = @catid delete from METAcell02 where catid = @catid delete from METAcell03 where catid = @catid delete from METAcell09 where catid = @catid delete from METAcell0a where catid = @catid delete from METAcell0b where catid = @catid delete from METAcell0c where catid = @catid delete from METAcell0d where catid = @catid delete from METAcell0e where catid = @catid go /*============= Change the catid of a Catalog ============ * Usage: mv_catid old_catid new_catid ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "mv_catid") drop PROCEDURE mv_catid go CREATE PROCEDURE mv_catid @catid int, @canew int AS print "----Convert catid=%1! into catid=%2!", @catid, @canew Select * into #cat from METAcat where catid = @catid Update #cat set catid = @canew where catid = @catid Select * into #tab from METAtab where catid = @catid Update #tab set catid = @canew where catid = @catid IF ( @catid < 1000) BEGIN print "****Can't move to catalogue #%1!", @catid END ELSE BEGIN Insert into METAcat Select * from #cat Insert into METAtab Select * from #tab Update METAcell0e set catid=@canew where catid = @catid Update METAcell0d set catid=@canew where catid = @catid Update METAcell0c set catid=@canew where catid = @catid Update METAcell0b set catid=@canew where catid = @catid Update METAcell0a set catid=@canew where catid = @catid Update METAcell09 set catid=@canew where catid = @catid Update METAcell03 set catid=@canew where catid = @catid Update METAcell02 set catid=@canew where catid = @catid Update METAcell00 set catid=@canew where catid = @catid Update METAcol set catid=@canew where catid = @catid Update METAqin set catid=@canew where catid = @catid Update METAcro set catid=@canew where catid = @catid Update METAkwd set catid=@canew where catid = @catid Update METAnot set catid=@canew where catid = @catid Update METAmor set catid=@canew where catid = @catid Update METAstat set catid=@canew where catid = @catid Delete from METAtab where catid = @catid Delete from METAcat where catid = @catid END Drop table #cat Drop table #tab go /*============= Add an item in METAstat Table ================== * (add only if not yet existing) * Usage: add_stat "name" ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "add_stat") drop PROCEDURE add_stat go CREATE PROCEDURE add_stat @name varchar(30) AS declare @catid int declare @n int declare @t int select @t = datediff(ss, "Jan 1, 2000", getdate()) select @catid = catid from METAcat where name = @name select @n = count(*) from METAcat, METAstat where METAcat.catid=METAstat.catid and METAcat.name = @name IF (@n = 0) BEGIN INSERT into METAstat VALUES (@catid, 0, @t, @t, @t, 0, 0) INSERT into METAstat VALUES (@catid, 1, @t, @t, @t, 0, 0) INSERT into METAstat VALUES (@catid, 3, @t, @t, @t, 0, 0) INSERT into METAstat VALUES (@catid, 4, @t, @t, @t, 0, 0) INSERT into METAstat VALUES (@catid, 5, @t, @t, @t, 0, 0) END ELSE BEGIN print "----Catalog '%1!' already in METAstat:", @name select * from METAstat where catid = @catid SELECT phase=0, catid, tlocal=MAX(tlocal), tother=MAX(tother), nlocal=SUM(nlocal), nother=SUM(nother) into #METAstat from METAstat group by catid having catid = @catid UPDATE METAstat set tlocal = #METAstat.tlocal, tother=#METAstat.tother, nlocal = #METAstat.nlocal, nother=#METAstat.nother from METAstat, #METAstat where METAstat.catid = #METAstat.catid AND METAstat.phase = #METAstat.phase UPDATE METAstat set tstart = @t, nlocal=0, nother=0 where catid = @catid and phase > 0 select * from METAstat where catid = @catid END go print "====Install procedure: release_now" IF EXISTS (select name from sysobjects where name = "release_now") drop PROCEDURE release_now go CREATE PROCEDURE release_now @name varchar(30) AS declare @catid int declare @t int select @t = datediff(ss, "Jan 1, 2000", getdate()) - 7200 select @catid = catid from METAcat where name = @name print "----Release catid: %1!", @catid UPDATE METAtab set release = @t where catid = @catid UPDATE METAcat set status = status & 239 where catid = @catid go IF EXISTS (select name from sysobjects where name = "release_at") drop PROCEDURE release_at go CREATE PROCEDURE release_at @name varchar(30), @when datetime AS declare @catid int declare @t int select @t = datediff(ss, "Jan 1, 2000", @when) select @catid = catid from METAcat where name = @name UPDATE METAtab set release = @t where catid = @catid UPDATE METAcat set status = status & 239 where catid = @catid go /*============= Add a new Keyword in the Definitions =========== * (add only if not yet existing) * Usage: add_kwdef "name", [0|200], "explanation" ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "add_kwdef") drop PROCEDURE add_kwdef go CREATE PROCEDURE add_kwdef @name varchar(30), @kwdno int = 0, @explain varchar(255) = "" AS /* Usage: add_kwdef "name", [0|200], "explanation" */ declare @kno int declare @auto_explain varchar(255) /* Does the kw already exist ? */ select @kno = (select kwdid from METAkwdef where name = @name) /* If not, add it NOW */ IF (@kno is NULL) BEGIN IF (@kwdno >= 200) BEGIN select @auto_explain = "Mission", @kno = MIN(kwdid)-1 from METAkwdef where kwdno = @kwdno IF (@kno is NULL) select @kno = -@kwdno IF (@kno > -200) select @kno = -200 END ELSE BEGIN select @auto_explain = "Astronomical characteristic", @kno = MAX(kwdid)+1 from METAkwdef where kwdno = @kwdno END if (@explain != "") select @auto_explain = @explain print "----Adding METAkwdef '%1!' (%2!)",@name, @kno Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (@kwdno, @kno, @name, @auto_explain) END go /*============= Add a kslot in METAcat Table ================== * Usage: add_kslot catid, kslot ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "add_kslot") drop PROCEDURE add_kslot go CREATE PROCEDURE add_kslot @catal varchar(50), @kslot int AS /* Usage: add_kslot "cat_name", kslot * (Set the Kohonen kslot value) */ declare @catid int select @catid = (select catid from METAcat where name = @catal) if (@catid is NULL) BEGIN print "****add_kslot: Unknown catalog: %1!", @catal return END select catid, old_kslot=kslot From METAcat where catid = @catid Update METAcat Set kslot=@kslot Where catid=@catid select catid, new_kslot=kslot From METAcat where catid = @catid go /*============= Add a new Acronym into the Catalog ============ * Usage: add_acro "catalog", "acronym" ----------------------------------------------------------------*/ IF EXISTS (select name from sysobjects where name = "add_acro") drop PROCEDURE add_acro go CREATE PROCEDURE add_acro @catal varchar(50), @name varchar(20) AS /* Usage: add_acro "cat_name", "acronym" * (Add an Acronym to a catalog) */ declare @catid int select @catid = (select catid from METAcat where name = @catal) if (@catid is NULL) BEGIN print "****add_acro: Unknown catalog: %1!", @catal return END Insert into METAcro (name, catid) VALUES (@name, @catid) go /*============= Add a new Keyword to a Catalog =================*/ /* (add only if not yet existing) */ /* Usage: add_kwd "cat_name", "kwd_name", [0|200] (Add a keyword def) add_adc "cat_name", "kwd_name" (Add a keyword from ADC list) add_wl "cat_name", "wl_name" (Add wl keyword) add_mission "cat_name", "mission_name" (Add a mission) add_optical "cat_name" (Add "optical" if no wavelength) set_catrole "cat_name", "kwd_name", num (Importance of Catalogue) ===============================================================*/ IF EXISTS (select name from sysobjects where name = "add_kwd") BEGIN drop PROCEDURE add_kwd drop PROCEDURE add_mission drop PROCEDURE add_wl drop PROCEDURE add_adc drop PROCEDURE add_optical drop PROCEDURE set_catrole END drop PROCEDURE add_kwd go CREATE PROCEDURE add_kwd @catal varchar(50), @name varchar(30), @mission int = 0 AS /* Usage: add_kwd "cat_name", "kwd_name", [0|200] * (Add a keyword to a Catalogue, 200 = Mission) */ declare @catid int declare @kwdid int select @catid = (select catid from METAcat where name = @catal) if (@catid is NULL) BEGIN print "****add_kwd: Unknown catalog: %1!", @catal return END select @kwdid = (select kwdid from METAkwdef where name = @name) IF (@kwdid is NULL) BEGIN print "****add_kwd: Unknown keyword: '%1!'", @name return END print "----Add keyword '%1!'(%2!) in catalogue '%3!'(%4!)", @name, @kwdid, @catal, @catid Insert into METAkwd (catid, kwdid, catrole) VALUES (@catid, @kwdid, 255) go /*---- For a mission, we add into METAkwdef if necessary ----*/ CREATE PROCEDURE add_mission @catal varchar(50), @name varchar(30) AS /* Usage: add_mission "cat_name", "mission_name" (Add a Mission keyword to a Catalogue) */ declare @mission varchar(30) select @mission = (select name from METAkwdef where name=@name and kwdno = 200) IF (@mission is NULL) BEGIN print "****Mission_Name '%1!' undefined??? (%2!)", @name, @catal /* print "++++Adding new Mission: %1!", @name */ /* execute add_kwdef @name=@name, @kwdno=200, @explain="(?)" */ print "****Add Mission with: " print " execute add_kwdef @name='%1!', @kwdno=200, @explain='...'" END execute add_kwd @catal=@catal, @name=@name, @mission=200 go /*---- For Wavelength: just pick up the numeric kwdno.. ----*/ CREATE PROCEDURE add_wl @catal varchar(50), @name varchar(30) AS /* Usage: add_wl "cat_name", "wl_name" (Add Wavelength Keyword to a Catalogue) */ declare @kwdno int select @kwdno = (select kwdno from METAkwdef where name = @name and kwdno>=100 and kwdno<200) IF (@kwdno is NULL) print "****add_wl: Invalid Wavelength Keyword: %1!", @name ELSE execute add_kwd @catal=@catal, @name=@name, @mission = @kwdno go /*---- Add 'optical' Keyword, if no wavelength-related kw exist ----*/ CREATE PROCEDURE add_optical @catal varchar(50) AS /* Usage: add_optical "cat_name" (Add "optical" keyword to a Catalogue if no wavelength) */ declare @n int declare @catid int select @n = -1 select @catid = (select catid from METAcat where name = @catal) IF (@catid is NULL) print "****add_optical: Unknown catalog: %1!", @catal ELSE select @n = count(*) from METAkwd where catid = @catid and kwdid > -200 and kwdid <= -100 /* Verify there are ASTRONOMICAL parameters */ IF (@n = 0) select @n = count(*) from METAkwd, METAkwdef where METAkwd.kwdid = METAkwdef.kwdid and kwdno = 0 and catid = @catid and METAkwdef.explain NOT LIKE "%[Aa]stro%" ELSE print "----wavelength-dependent exists for catalog: %1!", @catal IF (@n = 0) BEGIN print "====Adding 'optical' keyword for catalog: %1!", @catal Insert into METAkwd (catid, kwdid) VALUES (@catid, -140) END go /*---- Add keyword from ADC set: just map to METAkwdef...*/ /* Method: store mapped names in temporary array #kwd */ /******* BEWARE: with this STUPID Sybase, apparently INCOMPATIBLE ******* with the sp_primarykey / sp_foreignkey ********/ CREATE PROCEDURE add_adc @catal varchar(50), @name varchar(50) AS /* Usage: add_adc "cat_name", "kwd_name" (Add a keyword from ADC list) */ declare @catid int declare @nt int declare @kwname varchar(30) select @catid = catid from METAcat where name = @catal IF (@catid is NULL) BEGIN print "****add_adc: Unknown catalog: %1!", @catal return END select catid=@catid, name=map into #adc from ADCkwdef where adc = @name select @nt = count(*) from #adc IF (@nt = 0) BEGIN print "****ADC_Keyword '%1!' undefined??? (%2!)", @name, @catal drop table #adc return END IF (@nt = 1) BEGIN select @kwname = (select name from #adc) IF (@kwname = "") BEGIN print "++++Keyword '%1!' ignored (%2!)", @name, @catal drop table #adc return END END /* ---Remove keywords already there ---*/ Delete from METAkwd where catid=@catid and kwdid in (Select kwdid from #adc, METAkwdef where METAkwdef.name = #adc.name) /* ---Update now the METAkwd table ---*/ Insert into METAkwd (catrole,catid, kwdid) select catrole=255,catid, kwdid from #adc,METAkwdef where METAkwdef.name = #adc.name drop table #adc go /*---- Change the role of a Catalogue for a Keyword......*/ /* 0=basic, 255=not relevant */ CREATE PROCEDURE set_catrole @catal varchar(50), @name varchar(50), @catrole int AS /* Usage: set_catrole "cat_name", "kwd_name", num (Set the Importance of Catalogue in the Keyword) */ declare @catid int declare @kwdid int declare @n int select @catid = (select catid from METAcat where name = @catal) IF (@catid is NULL) print "****set_catrole: Unknown catalog: %1!", @catal select @kwdid = (select kwdid from METAkwdef where name = @name) if (@kwdid is NULL) print "****set_catrole: Unknown keyword: '%1!' (%2!)", @name,@catal select @n = (select count(*) from METAkwd where catid = @catid and kwdid = @kwdid) if (@n = 0) print "****set_catrole: keyword '%1!' not attached to catalog '%2!'", @name,@catal Update METAkwd Set catrole = @catrole where catid=@catid and kwdid=@kwdid go /*============================================================== * Insert Basic Values *==============================================================*/ set nocount on print "....Removing all existing keywords in METAkwd, METAkwdef, ADCkwdef" delete METAkwd go delete METAkwdef go delete ADCkwdef go delete METAcro go /*======== Insert Basic Keywords ==============*/ set nocount on print "....Adding basic Keywords" Insert into METAkwcat (kwdno_min, kwdno_max, name) VALUES (0, 0, "Astronomy") go Insert into METAkwcat (kwdno_min, kwdno_max, name) VALUES (1, 99, "---Unused--") go Insert into METAkwcat (kwdno_min, kwdno_max, name) VALUES (100, 199, "Wavelength") go Insert into METAkwcat (kwdno_min, kwdno_max, name) VALUES (200, 200, "Mission") go Insert into METAkwcat (kwdno_min, kwdno_max, name) VALUES (201, 255, "---Unused--") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES ( 0, 0, "_META_", "Definitions used for the description of the data") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES ( 0, 1, "Atomic_Data", "Physical data related to atoms") go Insert into METAkwd (catid, kwdid, catrole) VALUES (0, 0, 0) go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (100, -100, "no_wavelength", "Wavelength irrelevant") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (110, -110, "Radio", "Radio Wavelength ({lambda} > 0.1mm)") go /* 113, "Radio(m)" */ /* 115, "Radio(cm)" */ /* 117, "Radio(mm)" */ /* 118, "Radio(submm)" */ /* ---- No Yet Defined ----- Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (120, -120, "FIR", "Far Infra-red ({lambda} in range 30-100{mu}m)") ----------------------------*/ Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (130, -130, "IR", "Infra-red ({lambda} in range 0.8-100{mu}m)") go /* ---- No Yet Defined ----- Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (135, -135, "NIR", "Near Infra-red ({lambda} in range 0.8-22{mu}m)") ----------------------------*/ Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (140, -140, "optical", "Optical wavelength ({lambda} in range 0.35-1{mu}m)") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (150, -150, "UV", "Ultra-violet ({lambda} in range 90-350nm)") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (160, -160, "EUV", "Extreme Ultra-violet ({lambda} in range 10-100nm)") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (170, -170, "X-ray", "X-ray wavelength ({lambda} in range 0.2-10nm, Energy 0.1-6keV)") go Insert into METAkwdef (kwdno, kwdid, name, explain) VALUES (190, -190, "Gamma-ray", "Gamma rays wavelength (Energy > 6keV)") go add_kwd "META", "_META_" go add_wl "META", "no_wavelength" go /*===========================================================================* * Modifications in METAtables *===========================================================================*/ /* "offset" is written "off7" ... */ update METAcol set name = "offset" where name = "off7" and catid=0 go /* Set flags = PK (0x0400 = 1024) for PKs -- Note: METAgrp has all in primary key!! */ update METAcol set flags = flags | 1024 where catid = 0 and (name = "catid" or name = "kwdid" or name = "grpid" or name = "tabid" or name = "colid") go update METAcol set flags = flags | 1024 where catid = 0 and colid < 4 and name = "morid" go update METAcol set flags = flags | 1024 where catid = 0 and colid = 1 and name = "famid" go update METAcol set flags = flags | 1024 where catid = 0 and colid = 1 and name = "ucdid" go update METAcol set flags = flags | 1024 where catid = 0 and tabid>3 and name = "type" go update METAcol set flags = flags | 1024 where catid = 0 and tabid=20 and name = "hstid" go update METAcol set flags = flags | 1024 where catid = 0 and tabid>=31 and tabid<33 and name = "photid" go update METAcol set flags = flags | 1024 where catid = 0 and tabid=32 and name = "fltrid" go /* Also PK for RA and Dec */ update METAcol set flags = flags | 1024 where catid = 0 and ( name = "RA2000" or name = "DE2000") go /* METAdic */ update METAcol set flags = flags | 1024 where catid = 0 and tabid=26 and (name="type" or name="name") go /* Set flags = FK (0x0800 = 2048) for (not METAcat) */ update METAcol set flags = flags | 2048 where catid = 0 and name = "catid" and tabid != 1 go update METAcol set flags = flags | 2048 where catid = 0 and name = "photid" /* and tabid != 31 */ go update METAcol set flags = flags | 2048 where catid = 0 and name = "famid" and colid > 1 go update METAcol set flags = flags | 2048 where catid = 0 and name = "ucdid" and colid > 1 go /* The standard "FK --> PK" link is better replaced by dedicated link */ update METAcol set morid=13 where catid = 0 and name = "famid" and colid > 1 go update METAcol set morid=16 where catid = 0 and name = "ucdid" and colid > 1 go update METAcol set morid=21 where catid=0 and name = "hstid" and colid > 1 go update METAcol set morid=31 where catid=0 and tabid>=31 and name = "GCPD" go /* A column to link to catalog from METAobj, flags=0xA011 = -24559 */ select * into #col20 from METAcol where catid=0 and tabid=23 and name = "texto" go update #col20 set name="Catalog", dbname="Catalog", famid=0, type=4, dbtype=20, length=8, morid=20, fmt="Catalog", flags=-24559, explain="Go to the catalog home page" go update METAcol set colid=colid+1 where catid=0 and tabid=23 and name = "texto" go Insert into METAcol select * from #col20 go /* Foreign keys */ update METAcol set flags = flags | 2048 where catid = 0 and name = "hstid" and ((flags&1024) = 0) go /* Set morid */ update METAcol set morid= 1 where catid = 0 and name = "morid" and colid > 4 go update METAcol set morid= 2 where catid = 0 and name = "notid" and colid > 4 go update METAcol set morid= 3 where catid = 0 and name = "flags" and colid > 4 go update METAcol set morid= 4 where catid = 0 and tabid<=3 and name like "%type" go update METAcol set morid= 8 where catid = 0 and tabid=28 and name = "label" go update METAcol set morid= 9 where catid = 0 and tabid=30 and name = "symbol" go update METAcol set morid=10 where catid = 0 and tabid=28 and name = "name" go /* standard "FK --> PK" link better replaced by dedicated link for catid !*/ update METAcol set morid=12 where catid = 0 and name = "catid" go update METAcol set morid=12 where catid = 0 and name = "source" and morid=0 go /* Special link to Dictionary in METAdig (tabid=22) update METAcol set morid=22 where catid = 0 and tabid=22 and name = "texto" and morid=0 go /* standard "FK --> PK" link better replaced by dedicated link for catid !*/ update METAcol set morid=14 where catid = 0 and name = "tabid" go /* Explain a column */ update METAcol set morid=15 where catid = 0 and tabid!=3 and name="colid" go /* Set the VO_OSELECT flag for all columns used are Primay or Foreign Key, or name can be used to generate LINKS */ Update METAcol set flags = flags|8192 where catid = 0 and (name like "%id" or (flags&3072) != 0) go /* SET sofTeX(244) for METAnot text */ declare @tabid int select @tabid = tabid from METAtab where name = "METAnot" UPDATE METAcol set type = 244 where catid=0 AND tabid=@tabid AND name = "text" go /* declare @tabid int */ /* select @tabid = tabid from METAtab where name = "METAfam" */ UPDATE METAcol /* set type = 244 where catid=0 AND tabid=@tabid AND name = "explain" */ set type = 244 where catid=0 AND name = "explain" go /* ---texto the soft is defined in the METAmor, not here -- otherwise, the \sed{\begin{soft}...} generates problems */ declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" UPDATE METAcol set type=180, morid= 5 /* TeX */ where catid=0 AND tabid=@tabid AND name = "texto" go UPDATE METAcol set type = 244 where catid=0 AND name = "comment" go /* METAobj has no catid link, and is not displayed ... */ /*------------------------------------------------------ declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" UPDATE METAcol set flags = flags - 1 where catid=0 AND tabid=@tabid AND name = "catid" and (flags&1) != 0 go ------------------------------------------------------*/ declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" UPDATE METAcol set flags = flags - 2048 where catid=0 AND tabid=@tabid AND name = "catid" and (flags&2048) != 0 go /* ---------- No, use 12 !! declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" UPDATE METAcol set morid= 6 where catid=0 AND tabid=@tabid AND name = "catid" ------------- */ go declare @tabid int select @tabid = tabid from METAtab where name = "METAladin" UPDATE METAcol set morid= 7 where catid=0 AND tabid=@tabid AND name = "name" go /* Set the proper units and flags for positions */ print "" print "====Adding the Units for META definitions" go UPDATE METAcol set type = 201, flags = flags | 35, unit = """h:m:s""", dbunit = "mas" where catid=0 AND name like "RA%" go UPDATE METAcol set type = 9, flags = flags | 35, unit = """d:m:s""", dbunit = "mas" where catid=0 AND name like "DE%" go UPDATE METAcol set unit="yr", dbunit="yr" where catid=0 and (name="equinox" or name="epoch") go /* UPDATE METAcol set unit="1000", dbunit="1000" where catid=0 and name="krows" go */ /* ------------------ METAobj is NOT considered as having Position declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" Update METAtab set equinox = 2000, cooframe = 5 where catid=0 AND tabid = @tabid go ------------------------------------------------------------------ */ declare @tabid int select @tabid = tabid from METAtab where name = "ReadMeObj" Update METAtab set equinox = 2000, cooframe = 5 where catid=0 AND tabid = @tabid go declare @tabid int select @tabid = tabid from METAtab where name = "ReadMeObj" UPDATE METAcol set type=180, morid= 5 /* TeX */ where catid=0 AND tabid=@tabid AND name = "texto" go /* In METAobj, RA2000 and DE2000 are NOT main positions */ declare @tabid int select @tabid = tabid from METAtab where name = "METAobj" update METAcol set flags = flags&32735 where tabid=@tabid and name like "%2000" go /* Change Explanation of some Keywords which are NOT astronomical */ Drop table METAnot_astro go Create table METAnot_astro (name varchar(30)) go Insert into METAnot_astro VALUES ("Ages") go Insert into METAnot_astro VALUES ("Atomic_Data") go Insert into METAnot_astro VALUES ("Diameters") go Insert into METAnot_astro VALUES ("Earth") go Insert into METAnot_astro VALUES ("Ephemerides") go Insert into METAnot_astro VALUES ("Magnetic_fields") go Insert into METAnot_astro VALUES ("Masses") go Insert into METAnot_astro VALUES ("Models") go Insert into METAnot_astro VALUES ("Orbits") go Insert into METAnot_astro VALUES ("Planets+Asteroids") go Insert into METAnot_astro VALUES ("Sun") go Insert into METAnot_astro VALUES ("_META_") go Update METAkwdef Set explain = "Physical studies" where name in (Select name from METAnot_astro) go /* Update notes in the META tables */ Update METAcol set morid= tabid where catid=0 and tabid >= 240 and name = "cellh" go /*... Move METAcellxx to viz3 Update METAtab set dbaid = 3 where catid=0 and name like "METAcell0[239abcde]" */ go