Database description

Node types

These tables store the information about a node in OpenKM.

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

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

There are other useful tables:

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

SQL example queries

Query used to retrieve 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 whose 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, 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'

List of child nodes with standard security:

SELECT NB.NBS_UUID FROM OKM_NODE_BASE NB left outer join OKM_NODE_USER_PERMISSION UP ON NB.NBS_UUID=UP.NUP_NODE left outer join OKM_NODE_ROLE_PERMISSION RP on NB.NBS_UUID=RP.NRP_NODE 
WHERE NB.NBS_PARENT='586939f7-988a-430a-b3d1-4f8e75bd92e4' AND ((UP.NUP_USER='okmAdmin' and (UP.NUP_PERMISSION & 1)=1) or ((RP.NRP_ROLE in ('ROLE_USER')) 
and (RP.NRP_PERMISSION & 1)=1)) group by NB.NBS_UUID

Mark documents to be indexed again

The column named NDC_TEXT_EXTRACTED in the table OKM_NODE_DOCUMENT indicates which documents have been processed by the text extraction queue and which are still pending (value 'T' means processed and 'F' means pending). If you want to reindex the whole repository or some files, you might execute a query like this:

update OKM_NODE_DOCUMENT set NDC_TEXT_EXTRACTED='F'

Node version

These tables store information about node version history:

The table named OKM_NODE_VERSION stores version information for 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 ).

SQL example queries

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

select nv.* 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
 where nb.nbs_name = 'test.pdf';

This query prints the main info like this:

Query to get changes for a document named 'test.pdf' when its binary was updated:

select nv.* 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
 where nb.nbs_name = 'test.pdf' and nv.nv_action = 'DOCUMENT_CHANGED';

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

select nv.nv_docver 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
 where nb.nbs_name = 'test.pdf'

Node metadata properties

Each metadata group uses two tables to store information; take the following as a sample:

Sample:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE property-groups PUBLIC "-//OpenKM//DTD Property Groups 3.10//EN"
                                 "http://www.openkm.com/dtd/property-groups-3.10.dtd">
<property-groups>
  <property-group label="Consulting" name="okg:consulting">
    <input label="Input label" name="okp:consulting.input1" />
    <separator label="Separator label" name="okp:consulting.separator" /> 
    <input label="Input label" name="okp:consulting.input2" />
  </property-group>
</property-groups>

After registering the previous metadata definition, two tables will be created with these columns:

• OKM_PGRP_CUR_CONSULTING (used for current metadata values)
  • RGT_UUID: ID of the node which has this metadata
  • RGT_PRO_INPUT1: Value of property okp:consulting.input1 
  • RGT_PRO_INPUT2: Value of property okp:consulting.input2
  • RGT_PRO_SEPARATOR: Value of property okp:consulting.separator
• OKM_PGRP_HIS_CONSULTING (used for the history of the metadata values)
  • RGT_UUID: ID of the node which has this metadata
  • RGT_VERSION: ID of the related version
  • RGT_PRO_INPUT1: Value of property okp:consulting.input1
  • RGT_PRO_INPUT2: Value of property okp:consulting.input2
  • RGT_PRO_SEPARATOR: Value of property okp:consulting.separator

Query to get all the metadata values from the document named 'test.pdf' with UUID 'e0518d0e-8312-4df9-991c-263f16b061bf' and their changes:

SELECT * FROM OKM_PGRP_HIS_CONSULTING where RGT_UUID = 'e0518d0e-8312-4df9-991c-263f16b061bf' order by RGT_VERSION desc;

Query to get changes for a document named 'test.pdf' when its metadata properties were updated:

select nv.* 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
 where nb.nbs_name = 'test.pdf' and nv.nv_action = 'PROPERTY_GROUP_CHANGED';

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 nk.* 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 nb.nbs_uuid, nb.nbs_name, nc.nct_category
 from OKM_NODE_BASE nb 
 inner join OKM_NODE_CATEGORY nc on nc.nct_node = nb.nbs_uuid
 where nb.nbs_name = 'test'