Fedora Database Tables And SQL

This section briefly outlines the current table structure of Fedora Commons.
The Fedora Commons Database currently (as of 3.0b2) consists of seven tables, shown in the screenshot below. For the diagrams the tool SchemaSpy (http://schemaspy.sourceforge.net/) was used.

Pidgen

This table contains two columns: the namespace of the digital object and the currently highest assigned id.
 namespace | highestid 
-----------+-----------
 demo      |        36
 test      |        12
 escidoc   |    111203

Doregistry

This table contains general info about the digital objects, namely the PID (dopid), version of the digital object (systemversion), the owner id, object state (active, etc.) and the assigned label.
dopid                | systemversion | ownerid     | objectstate | label                                                              
-----------------------------+---------------+-------------+-------------+------------------------------------------
 demo:XML_TO_HTMLDOC |             1 | fedoraAdmin | A           | Generated Content Model
 demo:12             |             1 | fedoraAdmin | A           | Service Definition Object for Document Transform
 demo:13             |             1 | fedoraAdmin | A           | Service Deployment Object implementing ...
 demo:FO_TO_PDFDOC   |             1 | fedoraAdmin | A           | Generated Content Model
 demo:TEI_TO_PDFDOC  |             1 | fedoraAdmin | A           | Generated Content Model
 demo:21             |             1 | fedoraAdmin | A           | Sample Document Object (FO to PDF)
 demo:26             |             1 | fedoraAdmin | A           | Sample Document Object (TEI to PDF)
 demo:19             |             1 | fedoraAdmin | A           | Service Definition Object for PDFs
 demo:22             |             1 | fedoraAdmin | A           | Service Definition Object for FOs
 demo:20             |             1 | fedoraAdmin | A           | Service Deployment Object implementing the ...

Modeldeploymentmap

This table contains the relationships between the fundamental object types, see here
           cmodel            |        sdef         |         sdep          
-----------------------------+---------------------+-----------------------
 demo:XML_TO_HTMLDOC         | demo:12             | demo:13
 demo:FO_TO_PDFDOC           | demo:19             | demo:20
 demo:TEI_TO_PDFDOC          | demo:19             | demo:20
 demo:TEI_TO_PDFDOC          | demo:22             | demo:25
 demo:DualResImageCollection | demo:Collection     | demo:CollectionImpl
 demo:DualResImage           | demo:DualResolution | demo:DualResImageImpl
 demo:UVA_STD_IMAGE          | demo:27             | demo:28
 demo:CMImage                | demo:1              | demo:2
 demo:UVA_STD_IMAGE_1        | demo:1              | demo:2
 demo:UVA_STD_IMAGE_2        | demo:1              | demo:3
 demo:CMSID                  | demo:1              | demo:4
 demo:UVA_MRSID_IMAGE        | demo:1              | demo:4
 demo:UVA_MRSID_IMAGE_1      | demo:8              | demo:9

doFields

This table contains the Dublin Core entries of the digital objects.
   pid   |                                                              label                                                              | state |   ownerid   |     cdate     |     mdate     |    dcmdate    |                                                            dctitle                                                            |      dccreator      |                                    dcsubject                                    |                                                            dcdescription                                                             | dcpublisher | dccontributor | dcdate | dctype |   dcformat    | dcidentifier | dcsource | dclanguage | dcrelation | dccoverage |    dcrights     
---------+---------------------------------------------------------------------------------------------------------------------------------+-------+-------------+---------------+---------------+---------------+-------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+-------------+---------------+--------+--------+---------------+--------------+----------+------------+------------+------------+-----------------
 demo:12 | service definition object for document transform                                                                                | a     | fedoraadmin | 1214561433233 | 1214561433233 | 1214561433308 |  service definition object for document transform .                                                                           |                     |                                                                                 |                                                                                                                                      |             |               |        |        |               |  demo:12 .   |          |            |            |            | 
 demo:13 | service deployment object implementing the document transform bdef contract using the fedora local saxon transformation service | a     | fedoraadmin | 1214561433534 | 1214561433534 | 1214561433636 |  service deployment object implementing the document transform contract using the fedora local saxon transformation service . |                     |                                                                                 |                                                                                                                                      |             |               |        |        |               |  demo:13 .   |          |            |            |            | 
 demo:11 | exibit intro: architectural drawings, pavillion iii, iva image collection - university of virginia                              | a     | fedoraadmin | 1214561448657 | 1214561448657 | 1214561448718 |  exhibit intro: architectural drawings, pavilion iii, university of virginia .                                                |  thomas jefferson . |  pavilion iii lawn university of virginia jefferson jeffersonian architecture . |  exhibition introduction image for the collection of architectural drawings of pavillion iii on the lawn at university of virginia . |  iva .      |               |        |        |  image/jpeg . |  demo:11 .   |          |            |            |            |  unrestricted .

objectPaths

This table stores the filesystem path of each object, along with its PID and a surrogate key.
tokendbid |        token            |  path                                                   
-----------+-----------------------------+------------------------------------------------------------
1         | demo:XML_TO_HTMLDOC     | /f3b2-bin/data/objects/2008/0627/12/10/demo_XML_TO_HTMLDOC
2         | demo:12                 | /f3b2-bin/data/objects/2008/0627/12/10/demo_12
3         | demo:13                 | /f3b2-bin/data/objects/2008/0627/12/10/demo_13
4         | demo:FO_TO_PDFDOC       | /f3b2-bin/data/objects/2008/0627/12/10/demo_FO_TO_PDFDOC
5         | demo:TEI_TO_PDFDOC      | /f3b2-bin/data/objects/2008/0627/12/10/demo_TEI_TO_PDFDOC

datastreamPaths

This table contains the names and paths of the datastream associated with the digital objects stored in objectPaths. The primary key tokendbid relates to the corresponding row in objectPaths with the same tokenbid, it is a 1:1 relation.
tokendbid  |             token                              |  path 
-----------+------------------------------------------------+-----------------------------------------------------------------------------------

         1 | changeme:2+ds2008036990wo+dsv2008036990wo      | /f3b2-bin/data/objects/datastreams/2008/0704/14/1/changeme_2+ds2008036990wo+dsv2008036990wo
         2 | changeme:2+ds2008036990full+dsv2008036990full  | /f3b2-bin/data/objectsdatastreams/2008/0704/14/12/changeme_2+ds2008036990full+dsv2008036990full

SQL

When a digital object is ingested, the following SQL statements are executed. The digital object has one DC datastream (with field search index enabled), two managed datastreams, and a relationship.
-- Get PID for object
1  UPDATE pidGen SET highestID = $1 WHERE namespace = $2

-- Check if object already exists
2  SELECT doPID FROM doRegistry WHERE doPID='changeme:27'

-- Register digital object
3  INSERT INTO doRegistry (doPID,  ownerId, label) VALUES ('changeme:27', 'fedoraAdmin', 'Sample Document...')

-- Perform XACML requests (see fedora.server.security.RessourceAttributeFinder)
4  SELECT path FROM objectPaths WHERE token='changeme:27'
5  SELECT path FROM objectPaths WHERE token='changeme:27'
6  SELECT path FROM objectPaths WHERE token='changeme:27'
7  SELECT path FROM objectPaths WHERE token='changeme:27'
8  SELECT path FROM objectPaths WHERE token='changeme:27'
9  SELECT path FROM objectPaths WHERE token='changeme:27'
10 SELECT path FROM objectPaths WHERE token='changeme:27'
11 SELECT path FROM objectPaths WHERE token='changeme:27'
12 SELECT path FROM objectPaths WHERE token='changeme:27'

-- Check if datastream exists and insert
13 SELECT path FROM datastreamPaths WHERE token='changeme:27+ds2008036990wo+dsv2008036990wo'
14 UPDATE datastreamPaths SET path = $1 WHERE token = $2
15 INSERT INTO datastreamPaths (token, path) VALUES ($1, $2)

-- The same for the second datastream
17 SELECT path FROM datastreamPaths WHERE token='changeme:27+ds2008036990full+dsv2008036990full'
18 UPDATE datastreamPaths SET path = $1 WHERE token = $2
19 INSERT INTO datastreamPaths (token, path) VALUES ($1, $2)

-- Insert object relationships
16 INSERT INTO modelDeploymentMap (cModel, sDef, sDep) VALUES ('changeme:25' ,'changeme:22', 'changeme:21')

-- Insert path of object, first select, then try to update. If update fails, insert.
20 SELECT path FROM objectPaths WHERE token='changeme:27'
21 UPDATE objectPaths SET path = $1 WHERE token = $2
22 INSERT INTO objectPaths (token, path) VALUES ($1, $2)

-- Select and increment version of digital object
23 SELECT systemVersion FROM doRegistry WHERE doPID='changeme:27'
24 UPDATE doRegistry SET systemVersion=1 WHERE doPID='changeme:27'

-- Get another reader instance, it checks the object upon creation
25 SELECT path FROM objectPaths WHERE token='changeme:27'

-- Delete record from dcdates and insert new for all dates in DC datastream
26 DELETE FROM dcDates WHERE pid='changeme:27'
27 INSERT INTO dcDates (pid, dcDate) values ('changeme:27', 1212451200000)

-- Insert entry for DC indexing. Try update first, if it fails, insert.
28 UPDATE doFields SET label = $1, state = $2, ownerId = $3, cDate = $4, mDate = $5, dcmDate = $6, dcTitle=$7...
29 INSERT INTO doFields (pid, label, state, ownerId, cDate, mDate, dcmDate, dcTitle, dcCreator, dcSubject,...)

Add new attachment

In order to upload a new attachment to this page, please use the following box to find the file, then click on “Upload”.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
png
fedora_tables_screenshot.png 233.238 kB 2 Tue Jul 08 20:54:54 CEST 2008 KST Tables Screenshot
« This page (revision-1) was last changed on 08-Jul-2008 20:48 by KST [RSS]