Alert.png The wiki is deprecated and due to be decommissioned by the end of September 2022.
The content is being migrated to other supports, new updates will be ignored and lost.
If needed you can get in touch with EGI SDIS team using operations @ egi.eu.

Difference between revisions of "GOCDB/Release4/DB Schema"

From EGIWiki
Jump to navigation Jump to search
m
Line 5: Line 5:
IMPORTANT NOTE: this DB schema is designed following a PROM model.  
IMPORTANT NOTE: this DB schema is designed following a PROM model.  
Understanding how this schema works mean understanding general line of the underlying model.  
Understanding how this schema works mean understanding general line of the underlying model.  
You may want to check the page describing [[PROM]] first.
You may want to check the page describing [[GOCDB/PROM]] first.


=== object types ===
=== object types ===
Line 211: Line 211:
* in a similar way, if site A belongs to one or many group (a country, a cloud, an NGI...) links are added accordingly between object '999' and objects corresponding to these groups.
* in a similar way, if site A belongs to one or many group (a country, a cloud, an NGI...) links are added accordingly between object '999' and objects corresponding to these groups.


[[GOCDB4_Architecture|back to GOCDB4 Architecture page]]
[[GOCDB/Release4/Architecture|back to GOCDB4 Architecture page]]

Revision as of 11:34, 23 December 2010

This page is part of the GOCDB4 Architecture Documentation

GOCDB4 object and database schema

IMPORTANT NOTE: this DB schema is designed following a PROM model. Understanding how this schema works mean understanding general line of the underlying model. You may want to check the page describing GOCDB/PROM first.

object types

Object Type Main class/table description
Site GOCDB_SITES  Grid sites
Service endpoint GOCDB_SERVICE_ENDPOINTS Grid service endpoints
Downtime GOCDB_DOWNTIMES Grid downtimes
Router  GOCDB_ROUTERS Network routers used to access sites
User GOCDB_USERS  Registered users, operators and contact persons
Technical Role GOCDB_RESPONSIBILITIES User Roles, linked to a GOCDB entity
Permission  GOCDB_RESPONSIBILITIES  Basic set of allowed permissions associated to roles
Political Role GOCDB_DESCRIPTORS  Use roles labels, linked or not to a GOCDB technical role
Political Role Type  GOCDB_DESCRIPTORS  Various types of political roles
Political Role Request GOCDB_DESCRIPTORS internal - part of the user management system
Political Role Denial  GOCDB_DESCRIPTORS  internal - part of the user management system
Domain  GOCDB_DESCRIPTORS domains of the machines hosted at a site
Group  GOCDB_GROUPS  Actual groups of sites (NGI, Country, ROC,...)
Group type  GOCDB_DESCRIPTORS  Site group types
Timezone  GOCDB_DESCRIPTORS  Valid timezones
Certification Status GOCDB_DESCRIPTORS Site certification statuses
Service type GOCDB_DESCRIPTORS  Grid service types
VO  GOCDB_DESCRIPTORS  Official VOs retrieved from CIC DB
egee_objects  GOCDB_EGEE_OBJECTS  internal - part of the object management system
god_object GOCDB_GOD_OBJECT  internal - part of the object management system

link types

linkType parent object child object description/use
1 site timezone effective timezone at site location
2 site certification_status  certification status of a given site
3 group  site  group(s) the child site belongs to
4  service_type  service_endpoint  type/flavour of the child endpoint
5  service_type  service_type  linking service flavour to service type
6  site service_endpoint  parent site providing the child endpoint
7  service_endpoint  service_endpoint  child endpoint is an alias for parent endpoint
8 service_endpoint VO  parent service_endpoint supports child VO
9  user  site  homesite of a given user
10 user  role  specific role(s) of a given user
11  role  site  site the parent specific role applies on
12  role  group  group the parent specific role applies on
13 role  role  child role is of the generic type of the parent role
14  downtime  user user who created the downtime
15  downtime  service_endpoint  endpoint(s) impacted by given downtime
16  group group_type  type of a given group
17  group  group  child group is a subgroup of parent group
18  site  router network router(s) used to access given site
19  user  generic_contact  contact details of a user
20  site generic_contact  main contact details for a site
 21 site security_contact  security contact details for a site
22  site  emergency_contact  emergency contact details for a site
23  site  lcgalert_contact  LCG alert contact details for a site
24 group  generic_contact  main contact details for a group

data tables

 ------------------------------------------
|GOCDB_SITES                               |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- short_name            VARCHAR(100)      |
|- official_name         VARCHAR(2000)     |
|- home_url              VARCHAR(500)      |
|- GIIS_url              VARCHAR(500)      |
|- operatinghours_start  DATE/TIME         |
|- operatinghours_end    DATE/TIME         |
|- location              VARCHAR(100)      |
|- description           VARCHAR(2000)     |
 ------------------------------------------
 ------------------------------------------
|GOCDB_SERVICE_ENDPOINTS                   |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- endpoint              VARCHAR(500)      |
|- host_IP               VARCHAR(20)       |
|- host_DN               VARCHAR(100)      |
|- description           VARCHAR(2000)     |
|- production_level      VARCHAR(20)       |
 ------------------------------------------
 ------------------------------------------
|GOCDB_USERS                               |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- forename              VARCHAR(100)      |
|- surname               VARCHAR(100)      |
|- certificate DN        VARCHAR(100)      |
|- workinghours_start    DATE/TIME         |
|- workinghours_end      DATE/TIME         |
 ------------------------------------------
 ------------------------------------------
|GOCDB_CONTACTS                            |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- email                 VARCHAR(100)      |
|- phone                 VARCHAR(100)      |
 ------------------------------------------
 ------------------------------------------
|GOCDB_DOWNTIMES                           |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- classification        VARCHAR(20)       |
|- severity              VARCHAR(20)       |
|- description           VARCHAR(2000)     |
|- declaration_timestamp TIMESTAMP         |
|- start_timestamp       TIMESTAMP         |
|- end_timestamp         TIMESTAMP         |
 ------------------------------------------
 ------------------------------------------
|GOCDB_ROUTERS                             |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- IP                    VARCHAR(100)      |
|- FQDN                  VARCHAR(100)      |
|- IsPingable            BOOLEAN           |
 ------------------------------------------
 ------------------------------------------
|GOCDB_RESPONSIBILITIES                    |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- description           VARCHAR(100)      |
|- permission_mask       NUMBER            |
 ------------------------------------------
 ------------------------------------------
|GOCDB_DESCRIPTORS                         |
|------------------------------------------|
|- CobjectId             NUMBER            |
|- CgridId               NUMBER            |
|- name                  VARCHAR(100)      |
|- description           VARCHAR(2000)     |
 ------------------------------------------

examples

Representation of "site A and associated details"

  • a site A entry in the GOCDB_SITES table. it is assigned an object id (e.g. '999'). Associated fields are filled.
  • an entry in the TObjects core table, associating object id '999' to the object type that corresponds to "site".
  • a certified entry in the GOCDB_DESCRIPTORS table, with an object id (e.g. '888')
  • an entry in the TObjects core table, associating object id '888' to the object type that corresponds to "certification_status".
  • an entry in the Tlinks core table, of type 2 (site to certification_status), linking object '999' to object '888'
  • in a similar way, if site A belongs to one or many group (a country, a cloud, an NGI...) links are added accordingly between object '999' and objects corresponding to these groups.

back to GOCDB4 Architecture page