Database description

Node types

These tables store the information about a node in OpenKM.

The table named OKM_NODE_BASE store the common fields ( name, author, creation date, among others ) for all the node types ( Document, Folder, Mail or Record ).

The field named NBS_UUID is the unique node ID. NBS_UUID FIELD is the foreight key between parent table ( OKM_NODE_BASE ) and childs ( OKM_NODE_DOCUMENT, OKM_NODE_FOLDER, OKM_NODE_MAIL, OKM_NODE_RECORD ).

There are other useful tables:

  • OKM_NODE_DOCUMENT: Store information about documents.
  • OKM_NODE_FOLDER: Store information about folders.
  • OKM_NODE_MAIL: Store information about mails.
  • OKM_NODE_RECORD: Store information about records.

SQL example queries

Query used to retrieve the information about every document:

select * from okm_node_base nb 
  inner join okm_node_document nd on nd.nbs_uuid = nb.nbs_uuid

Query  to get records what the name starts with 'test':

select * from okm_node_base nb 
  inner join okm_node_record nr on nr.nbs_uuid = nb.nbs_uuid
  where nb.nbs_name like 'test%'

To find a Document by uuid could use this query:

select * from okm_node_base nb 
  inner join okm_node_document nd on nd.nbs_uuid = nb.nbs_uuid
  where nb.nbs_uuid = 'fc35ce80-57f2-4026-ae77-7a11bca042ed'

Mark documents to being indexed again

The column named NDC_TEXT_EXTRACTED in the table OKM_NODE_DOCUMENT it takes control of what documents has been processed into the Text extraction queue and what are still pending to be procesed ( value 'T' means processed and 'F' means pending ). If you want to reindex the whole repository again or some files you might execute a query like this one:

update OKM_NODE_DOCUMENT set NDC_TEXT_EXTRACTED='F'

Node version and properties

These tables store information about node version history:

The table named OKM_NODE_VERSION store version information about every version of each node.

  • The field NV_ID is the id of a version.
  • The value of the field named NV_PARENT is the unique document ID ( the same value as NBS_UUID ).

The table named OKM_NODE_DOCUMENT_VERSION store the information of all versions of a document.

  • The field named NDV_UUID is the unique document version ID.
  • The value of the field named NDV_PARENT is the unique document ID ( the same value as NBS_UUID ).

In the table named OKM_NODE_PROPERTY are stored the metadata of a node ( Document, Folder, Mail, Record ).

  • The value of the field named NPG_NODE value is the unique document ID ( the same value as NBS_UUID ).
  • The field named NPG_GROUP is the metadata group name ( for example okg:consulting )
  • The field named NPG_NAME is the metadata field name ( for example okp:consulting.comment )

The table named OKM_NODE_PROPERTY_VERSION store the information of all versions of the properties. 

  • The field named NPV_VERSION is the number of version of this property ( the same value as NV_ID )

SQL example queries

Query to get all changes of a document named 'test.pdf':

select nv.*, ndv.* from okm_node_document nd 
  inner join okm_node_version nv on nv.nv_parent = nd.nbs_uuid
  inner join okm_node_base nb on nd.nbs_uuid = nb.nbs_uuid
  inner join okm_node_document_version ndv on ndv.ndv_parent = nd.nbs_uuid
  where nb.nbs_name = 'test.pdf'

This query prints the following info:

ColumnTypeDescription

NV_ACTION

DOCUMENT_CREATED

FOLDER_CREATED

MAIL_CREATED

RECORD_CREATED

DOCUMENT_CHANGED

DOCUMENT_IMPORTED

FOLDER_IMPORTED

RECORD_IMPORTED

MAIL_IMPORTED

PROPERTY_GROUP_ADDED

PROPERTY_GROUP_REMOVED

PROPERTY_GROUP_CHANGED

What kind of change were made.

NV_NAME String  Version number.
NDV_UUID String  Document content version id.

Query to get all the metadata values from a document named 'test.pdf' and their changes:

select npv.* from okm_node_document nd 
  inner join okm_node_version nv on nv.nv_parent = nd.nbs_uuid
  inner join okm_node_base nb on nd.nbs_uuid = nb.nbs_uuid
  inner join okm_node_property_version npv on npv.npv_version = nv.nv_id
  where nb.nbs_name = 'test.pdf' 
  order by npv.npv_version desc

This query will print all metadata values for each version. Usually, the actual version of these metadata values are the ones having the biggest npv_version value.

The next query is useful to know where all the versions of a file named 'test.pdf' are stored into the datastore:

Only applicable to the default datastore configuration ( case all the versions of the documents are stored in the file system ).

Take a look at Datastore for more information.

select ndv_uuid from okm_node_document nd 
  inner join okm_node_document_version nv on nv.ndv_parent = nd.nbs_uuid
  inner join okm_node_base nb on nd.nbs_uuid = nb.nbs_uuid
  where nb.nbs_name = 'test.pdf'

For example 'f0d7f0a1-b337-4917-b592-56e163b81cde'. This means your file is located in the physical drive in the path ( based in first eight characters of this id ):

/home/user/tomcat_home/repository/datastore/f0/d7/f0/a1/f0d7f0a1-b337-4917-b592-56e163b81cde

Node categories and keywords

The value of the field named NCT_NODE is the unique document ID ( the same value as NBS_UUID ).

The value of the field named NKW_NODE is the unique document ID ( the same value as NBS_UUID ).

SQL example queries

Query to extract all keywords associated with a node named 'test.pdf':

select * from okm_node_base nb 
  inner join okm_node_keyword nk on nk.nkw_node = nb.nbs_uuid
  where nb.nbs_name = 'test.pdf'

Query to get all the categories for a node named 'test.pdf':

select nd.nbs_uuid, nbs_name, npg_group, npg_name, npg_value
from okm_node_base nb inner join okm_node_category nc on nc.nct_node = nb.nbs_uuid where nb.nbs_name = 'test'