Query Utils

From OpenKM Documentation
Jump to: navigation, search

Here are some queries that could be useful for migration.

Save OKM_CONFIG table values

Creates update script with actual OKM_CONFIG table values to be restored in new OpenKM database;

SELECT  'UPDATE OKM_CONFIG SET CFG_VALUE=''' ||  CFG_VALUE || ''' WHERE CFG_KEY=''' || CFG_KEY || '''' FROM OKM_CONFIG;

Tested: OpenKM (6.x) Database (MySQL).

Looking for OKM_ACTIVITY document history

Looking for creation of some document named /okm_root:/employees/8526. employe_name.pdf

SELECT * FROM OKM_ACTIVITY WHERE ACT_ACTION="CREATE_DOCUMENT" AND ACT_PARAMS LIKE "%/employees/8526%";

Results example( each UUID indicates a different document ):

| 10177807 | 2012-08-31 17:33:48 | user1 | CREATE_DOCUMENT | c2a7f11c-3bab-4bed-bfd1-3e52ff46d38c | application/pdf, 1074025, /okm:root/employees/8526. employe_name.pdf  |
| 10346330 | 2012-09-03 11:32:20 | user1 | CREATE_DOCUMENT | 65c2f13e-9b3c-41ae-b163-eb9ff7dc31c6 | application/pdf, 1108017, /okm:root/employees/8526.. employe_name.pdf |
| 10347837 | 2012-09-03 11:53:23 | user1 | CREATE_DOCUMENT | d2f89737-dd44-4d4a-98af-179b904935dd | application/pdf, 1111746, /okm:root/employees/8526. employe_name.pdf  |

Looking for when has been deleted of some document named /okm_root:/employees/8526. employe_name.pdf

SELECT * FROM OKM_ACTIVITY WHERE ACT_ACTION="DELETE_DOCUMENT" AND ACT_PARAMS LIKE "%/employees/8526%";

Looking for all the activity log of the document

SELECT * FROM OKM_ACTIVITY WHERE ACT_PARAMS LIKE "%/employees/8526%";


Tested: OpenKM (5.x) Database (MySQL).