• GLUE 2.0 – Reference Realizations to SQL Schema
• SQL repository
• tedia2sql bugs (old?)

GLUE 2.0 Relational Database Schema Proposal ( v.41)

Issues/Problems

• check ExecutionEnvironment in ComputingResource
• can the aggregation directives in the diagram be replaced ?
• indexes need to be defined for entities
• when inserting a manager entry the associated (first created) environment needs the managerID (chase one own's tail problem)
• Benchmark: check contraint needs to be comma seperated

Schema details

Tedia2sql allows to generate database specific SQL statements including (concatinated) primary keys, foreign keys and views from the diagram.

The Aggregation arrow between two entities indicate a foreign key relationship. The end of the arrow determines on where the foreign key is active. <br/> e.g. !EntryType ---<> !ValueTable will create a foreign key on !ValueTable referring to the id of the !EntryType.

The present rendering of the GLUE 2.0 uses the following concepts:

• Primary key concepts to ensure uniqueness and *fast data access*
• Foreign key relationships between tables to ensure *data integrity*, some with *on delete* constraints which ensure that data is deleted properly.
• CHECK constraints for closed enumeration types
• An *AttributeType table* containing all multi-valued attribute names from all entities with a numerical id field. These are provided as INSERT statements.
• An *EntryTypes table* containing all table names with a numerical id field which are used in this schema. These are provided as INSERT statements.
• Multi-valued attributes of an entity from the GLUE schema are stored in a table named like the entity, additionally ending with ‘_MVA’ (MultiValuedAttribute). The attribute type is determined by referring to an entry in the AttributeType table.
• The Extention concept is realized by adding the key as a *new entry into the AttributeType* table and the value into the multi-valued table of the related entity. Consequently, the attributeTypeID of the sub table entry must be the (numerical) id of the previously added AttributeType table entry
• Table views are used to get information more easily. However, they should not be used excessively (e.g. as sub queries) since they may have great impact on database performance. Table view names must have ‘V_’ as a prefix.

The present schema has been developed regardless of versions of underlying database systems but in respect on compliance with the SQL92 standard. However, data integrity concepts such as foreign key relationships are supported by most database implementations.

The *ndpointShare_LNK table serves as a look-up table to map Share entries to Endpoint entries. It is only allowed to insert value pairs which combination reflects a relationship between ComputingShare and ComputingEndpoint or StorageShare and StorageEndpoint.

The information from the Benchmark table which is used for ExecutionEnviroment and ComputingManager may be fetched also from its views (V_CManagerBenchmark and V_ExecEnvBenchmark).

(only valid for database system supporting ‘on cascade’ directive:) Some ‘on cascade’ directives may fail if the parentEntry hasn’t been removed first manually. An example is the ComputingService; the deletion of an entry here will *remove ALL* depended entries in other tables. However, it will fail if there are still entries in the ComputingActivities table.

String lengths

The maximum length of string columns are 255 characters. Below you find an exempt from the most common used attributes and their string length.

Glue 2.0 schema document -> relational DB Mapping issues

Multi Valued Attributes

The schema for this dedicated multi-valued attributes (example is the Endpoint):

-- Endpoint table defintion
create table Endpoint (
...
);

-- Endpoint_MVA table definition
create table ComputingActivity_MVA (
  endpointID           varchar (255) not null,
  attributeType       integer,    -- Error, RequestedApplicationEnvironment, ExecutionNode, OtherMessages
  value                     varchar (255) not null
) ;

Data Insert Order

Computing

1. UserDomain / AdminDomain
2. UserDomain.Location, UserContact, AdminDomain.Location,AdminContact
3. ComputingService
4. ComputingService.Location, ComputingService.Contact, ComputingManager
5. ApplicationEnvironment, Endpoint, ExecutionEnvironment
6. ComputingEndpoint, ApplicationHandle
7. CServiceSService - provided that data from step 1-3 fo the storage part have been inserted, AppEnvExecEnv
8. ComputingShare, Benchmark
9. ComputingActivity, CShareExecEnv, EndpointShare_LNK, ComputingMappingPolicy

Storage

1. UserDomain / AdminDomain
2. UserDomain.Location, UserContact, AdminDomain.Location,AdminContact
3. StorageService
4. StorageService.Location, StorageService.Contact, StorageAccessProtocol
5. StorageManager, StorageServiceCapacity, StorageEndpoint
6. StorageResource
7. StorageShare
8. StorageShareCapacity, SShareSResource, StorageMappingPolicy

SQL Search Examples

SELECT url, type FROM Contact 
WHERE parentID = ( SELECT id FROM AdminDomain WHERE name = 'CERN');
+---------------------+---------+
| url                 | type    |
+---------------------+---------+
| mailto:support@cern.ch | general |
+---------------------+---------+

-- The trusted CAs of a specific !AdminDomain
SELECT value, name FROM ValueTable, AttributeTypes
WHERE AttributeTypes.name = 'TrustedCA'
AND ValueTable.typeid = AttributeTypes.id
AND ValueTable.parentid = (SELECT id FROM AdminDomain WHERE uniqueID = 'urn:admindomain:ad1.cern.ch' ) ;
+--------------------------------------------------+-----------+
| value                                            | name      |
+--------------------------------------------------+-----------+
| /C=PL/O=GRID/CN=Polish Grid CA                   | TrustedCA |
| /C=US/O=Globus/CN=Globus Certification Authority | TrustedCA |
+--------------------------------------------------+-----------+

-- All Endpoints which are classified as a 'production' service
SELECT state,url,implementationName, specificationName, specificationVersion from Endpoint, Service 
WHERE Service.qualityLevel = 'production' 
  AND Endpoint.serviceID = Service.id;
+---------+---------------------------------------------------------------+--------------------+-------------------+----------------------+
| state   | url                                                           | implementationName | specificationName | specificationVersion |
+---------+---------------------------------------------------------------+--------------------+-------------------+----------------------+
| running | https://egee-rb-02.cnaf.infn.it:7443/glite_wms_wmproxy_server | glite WMS          | gLite WMProxy     | 1.5                  |
| running | http://lcg-bdii:2170                                          | glite-bdii         | glue              | 2.0                  |
+---------+---------------------------------------------------------------+--------------------+-------------------+----------------------+

-- The State and Endpoint of all Webservices from one Domain.
SELECT Service.uniqueID, Endpoint.State, ValueTable.value as 'Endpoint.WSDL'
FROM ValueTable, Endpoint, Service
WHERE typeid = (SELECT id from AttributeTypes WHERE name ='WSDL')
  AND parentType = (SELECT id from EntryTypes WHERE name = 'Endpoint')
  AND Endpoint.id = ValueTable.parentID
  AND Endpoint.serviceID = Service.id
  AND Service.domainID = (SELECT id from AdminDomain WHERE name ='CERN');
+-------------------------+---------+--------------------------------------------------------------+
| uniqueID                | State   | Endpoint.WSDL                                                |
+-------------------------+---------+--------------------------------------------------------------+
| egee-rb-02.cnaf.infn.it | running | http://trinity.datamat.it/projects/EGEE/WMProxy/WMProxy.wsdl |
+-------------------------+---------+--------------------------------------------------------------+

AdminDomain Table

+----+-----------------------------+---------+--------------------------------------------+-------------+
| id | uniqueID                    | name    | description                                | distributed |
+----+-----------------------------+---------+--------------------------------------------+-------------+
|  1 | urn:admindomain:ad1.cern.ch | CERN    | European Organization for Nuclear Research |           0 |
|  2 | urn:admindomain:t1.infn.it  | INFN-T1 | This is the Italian T1 of EGEE Grid        |           0 |
+----+-----------------------------+---------+--------------------------------------------+-------------+

Service Table

+----+-------------------------+---------------+------------+------------------------------------+--------------+-------------------------------+------------+
| id | uniqueID                | name          | capability | statusPage                         | qualityLevel | complexity                    | locationID |
+----+-------------------------+---------------+------------+------------------------------------+--------------+-------------------------------+------------+
|  1 | egee-rb-02.cnaf.infn.it | org.glite.wms | NULL       | http://gridice4.cnaf.infn.it:50080 | production   | endpoint=1,share=0,resource=0 |       NULL |
|  2 | egee-rb-02.cern.ch      | org.glite.wms | NULL       | none                               | production   | endpoint=1,share=0,resource=0 |       NULL |
+----+-------------------------+---------------+------------+------------------------------------+--------------+-------------------------------+------------+

Endpoint Table

+----+-------------------+-----------+------+---------------------------------------------------------------+------------------+-------------------+----------------------+----------------------+--------------------+-----------------------+---------+----------------------+-------------------+------------+---------------+-------------+--------------------+--------------------------+
| id | uniqueID          | serviceID | name | url                                                           | category         | specificationName | specificationVersion | implementationVendor | implementationName | implementationVersion | state   | stateInfo            | semantics         | startTime  | downTimeStart | downTimeEnd | downTimeInfo       | issuerCA                 |
+----+-------------------+-----------+------+---------------------------------------------------------------+------------------+-------------------+----------------------+----------------------+--------------------+-----------------------+---------+----------------------+-------------------+------------+---------------+-------------+--------------------+--------------------------+
|  1 | urn:infn:cnaf:wms |         1 | NULL | https://egee-rb-02.cnaf.infn.it:7443/glite_wms_wmproxy_server | categoryendpoint | gLite WMProxy     | 1.5                  | EGEE                 | glite WMS          | 3.1                   | running | everything all right | Semastics-webpage | 1191196800 |    1193875200 |  1193961600 | Release update     | /C=IT/O=INFN/CN=INFN CA/ |
|  2 | urn:cern:bdii     |         2 | NULL | http://lcg-bdii:2170                                          | categoryendpoint | glue              | 2.0                  | EGEE                 | glite-bdii         | 4.6                   | running |                      |                   |          0 |             0 |           0 | No Downtime at all | /C=CH/O=CERN/CN=CERN CA/ |
+----+-------------------+-----------+------+---------------------------------------------------------------+------------------+-------------------+----------------------+----------------------+--------------------+-----------------------+---------+----------------------+-------------------+------------+---------------+-------------+--------------------+--------------------------+

Contact Table

+----+------------------------------+----------+------------+------------------------------+---------+
| id | localID                      | parentID | parentType | url                          | type    |
+----+------------------------------+----------+------------+------------------------------+---------+
|  1 | loc_1                        |        1 |          1 | mailto:chef@cern.ch          | general |
|  2 | mailto:t1-admin@cnaf.infn.it |        2 |          1 | mailto:t1-admin@cnaf.infn.it | general |
+----+------------------------------+----------+------------+------------------------------+---------+

Location

+----+---------------+----------+------------+---------------------+----------------------------------------------+-----------+----------+
| id | localID       | parentID | parentType | name                | address                                      | longitude | latitude |
+----+---------------+----------+------------+---------------------+----------------------------------------------+-----------+----------+
|  1 | loc_1         |        1 |          1 | Geneva, Switzerland | CERN, 1207 Geneva, Switzerland               |   6.04518 |  46.2337 |
|  2 | loc_2         |        1 |          1 | Geneva, Switzerland | CERN, 1207 Geneva, Switzerland               |   6.05488 |  46.2311 |
|  3 | bologna-italy |        1 |          1 | Bologna, Italy      | Viale Berti Pichat 6/2, Bologna, Italy (TBV) |   11.3417 |  44.4948 |
|  4 | bologna-italy |        1 |          4 | Bologna, Italy      | Viale Berti Pichat 6/2, Bologna, Italy (TBV) |   11.3417 |  44.4948 |
+----+---------------+----------+------------+---------------------+----------------------------------------------+-----------+----------+

AttributeTypes Table

+----+------------------+
| id | name             |
+----+------------------+
|  1 | WWW              |
|  2 | OtherInfo        |
|  3 | Owner            |
|  4 | WSDL             |
|  5 | TrustedCA        |
|  6 | SupportedProfile |
+----+------------------+

EntryTypes Table

+----+-------------+
| id | name        |
+----+-------------+
|  1 | AdminDomain |
|  2 | Location    |
|  3 | Contact     |
|  4 | Service     |
|  5 | Endpoint    |
+----+-------------+

ValueTable Table

+----------+------------+--------+--------------------------------------------------------------+
| parentID | parentType | typeID | value                                                        |
+----------+------------+--------+--------------------------------------------------------------+
|        1 |          1 |      1 | www.mypage.com                                               |
|        1 |          1 |      1 | www.mysecondpage.com                                         |
|        1 |          1 |      2 | This is the CERN domain                                      |
|        2 |          1 |      1 | http://www.cnaf.infn.it                                      |
|        2 |          1 |      2 | This is the Italian T1                                       |
|        2 |          1 |      3 | INFN                                                         |
|        1 |          3 |      2 | My first input for CERN Contact.                             |
|        1 |          3 |      2 | My second input for CERN Contact.                            |
|        3 |          3 |      2 | working hours: 8-18                                          |
|        1 |          5 |      4 | http://trinity.datamat.it/projects/EGEE/WMProxy/WMProxy.wsdl |
|        1 |          5 |      6 | WS-I 1.0                                                     |
|        1 |          5 |      6 | AnotherProfile                                               |
|        1 |          5 |      5 | /C=PL/O=GRID/CN=Polish Grid CA                               |
|        1 |          5 |      5 | /C=US/O=Globus/CN=Globus Certification Authority             |
+----------+------------+--------+--------------------------------------------------------------+

Attachments:

Glue20_small.png [GLUE2Relational/Glue20_small.png] Glue20Schema.sql [GLUE2Relational/Glue20Schema.sql] Glue20Schema.png [GLUE2Relational/Glue20Schema.png] Glue20.dia [GLUE2Relational/Glue20.dia]