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'
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 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:
What kind of change were made.
|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 ):
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'