Transcript CREATE TABLE t_Experiment ( ID BIGINT NOT NULL

SimDB as a TAP service
various TIG members
(IVOA.IVOATheorySimDB)
www.g-vo.org
Overview
• History of SimDB
• SimDB is a TAP service
– protocol
– metadata
• Object relational mapping
• TAP as information integration protocol
www.g-vo.org
History
• Simple Numeric Access Protocol (SNAP)
– Cambridge 2006
– Victoria 2006
• follow DAL v2 S*AP (ala SSAP)
– data model
– queryData
– getData (accessURL)
• LARGE simulations (“3+1D”/ “cosmological”/...)
– too large for accessURL
– getData more complex: cut-outs etc
• Separated in 2
– Simulation database (SimDB)
– Simulation Data Access Protocol (SimDAP)
www.g-vo.org
SimDB
• SimDB is a protocol for a database
containing metadata about simulations
– like a Simulation registry (DON’T MENTION
REGISTRY)
• SimDB’s data model (SimDB/DM)
prescribes the database’s data model
• Should support discovery of simulations,
i.e. queryData of SNAP
– query expressions
– result format
www.g-vo.org
SimDB/DM
relatively
complex
(see tomorrow)
www.g-vo.org
SimDB/TAP
• Claim: such a omplex model requires flexible
query language
– Choose ADQL
• Consequently(?) SimDB is a TAP service
• Special: Predefined data model
• Restrictions on required functionality
– sync-only ADQL queries mandated
– no upload
– ... (discuss tomorrow, TIG session)
• TAP 0.5, p10, comment about “inheriting
services”
www.g-vo.org
SimDB/TAP_SCHEMA
• Requires relational model for SimDB/DM
– “Object-Relational mapping”
– Impedance mismatch
– some “standard” approaches
• Expressed as
– VODataService
– TAP_SCHEMA inserts
– “empty VOTable”
• VOTable serialisation of the TAP_SCHEMA
tables
www.g-vo.org
OR mapping
•
•
•
•
•
class → table
attribute → column
reference → foreign key
composition → foreign key to container
inheritance
www.g-vo.org
class → table
CREATE TABLE t_Experiment (
ID BIGINT NOT NULL, -- Surrogate key
publisherDID VARCHAR(128) NOT NULL,
...
)
ALTER TABLE t_Experiment ADD CONSTRAINT
pk_t_Experiment_ID PRIMARY KEY(ID);
www.g-vo.org
attribute → column
CREATE TABLE t_InputParameter (
ID BIGINT NOT NULL,
name VARCHAR(128) NOT NULL,
ucd varchar(128,
datatype varchar(32) NOT NULL,
multiplicity varchar(10),
...
)
• Note: data type mapping required
www.g-vo.org
reference → foreign key
CREATE TABLE t_Experiment (
ID BIGINT NOT NULL,
protocolID BIGINT NOT NULL,
...
)
ALTER TABLE t_Experiment ADD CONSTRAINT
fk_t_Experiment_protocol FOREIGN KEY
(protocolId) REFERENCES t_Protocol(ID);
www.g-vo.org
composition → foreign key to
container
CREATE TABLE t_Column (
...
containerId BIGINT NOT NULL, -- Table
...
);
ALTER TABLE t_Column ADD CONSTRAINT fk_t_Column_container
FOREIGN KEY (containerId) REFERENCES t_Table(ID);
www.g-vo.org
inheritance
• Requirement:
Each class, abstract and
concrete, can be queried
on
– select * from Simulation
– select * from Experiment
– Select * from Resource
• Obtains all attributes,
including inherited ones.
www.g-vo.org
inheritance
Some “standard” approaches
1. 1 table per class,
•
•
no inherited attributes
primary key = foreign key to base table
2. 1 table per hierarchy
•
•
gather columns from subclasses
DTYPE column
3. 1 table per concrete (= non-abstract)
class, all attributes
www.g-vo.org
inheritance following 1
CREATE TABLE t_Resource (
ID BIGINT IDENTITY NOT NULL,
DTYPE VARCHAR(32) , ...
);
CREATE TABLE t_WebService (
ID BIGINT NOT NULL, ...
);
ALTER TABLE t_WebService ADD CONSTRAINT fk_t_WebService_extends
FOREIGN KEY (ID) REFERENCES t_Resource(ID);
www.g-vo.org
Add views, one per class
CREATE VIEW Resource
AS
SELECT ID
,
DTYPE
,
name
,
description
,
referenceURL
,
created
,
updated
,
status
FROM t_Resource
www.g-vo.org
CREATE VIEW WebService
AS
SELECT b.*
,
t.baseURL
,
t.type
,
t.registryId
FROM t_WebService t
,
Resource b
WHERE b.ID = t.ID
Comments
(discussed tomorrow)
• Implementations in progress
• Heterogeneity and quantities cause problems for ADQL
– Alberto’s proposal might help: fix units for quantities
(defined by vocabulary)
– May need simpler, dedicated protocols
• How to register SimDB/TAP?
• Extensions to DM allowed?
• SimDB/REST+XML as well?
• VOExplorer-like approach to browsing a SimDB
www.g-vo.org
Conclusions
• Approach might be used for
– SIAP, SSA, Registry
• Not for
– STC, Characterisation
• Is (??) already used/planned for
– Atomic/molecular lines, Source catalogue
• Complexity of SimDB caused be heterogeneity
– simpler for other cases
• Finally, need participation in SimDB effort.
www.g-vo.org