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 the 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 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'

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 takes control of what documents have been processed into the Text extraction queue and what are still pending to be processed ( 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

These tables store information about node version history:

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

SQL example queries

Query to get all changes of 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 of 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 next 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 will be created two tables 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 of a document named 'test.pdf' when its metadata properties 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 = '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'