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 or Mail ).

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).

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.

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 folders what the name starts with 'test':

select * from okm_node_base nb 
  inner join okm_node_folder 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

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 ).

SQL example queries

Query TO get all the versions of a document named 'test.pdf':

select * 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' 

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

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'

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 * from okm_node_base nb 
  inner join okm_node_category nc on nc.nct_node = nb.nbs_uuid
  where nb.nbs_name = 'test'

Node properties

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 )

SQL example queries

Query to get all the metadata associated with a document named 'test.pdf':

select nd.nbs_uuid, nbs_name, npg_group, npg_name, npg_value
  from okm_node_document nd   
  inner join okm_node_base nb on nd.nbs_uuid = nb.nbs_uuid
  inner join okm_node_property np on np.npg_node = nd.nbs_uuid
  where nb.nbs_name = 'test.pdf'