Difference between revisions of "Query Utils"
From OpenKM Documentation
(Created page with 'Here are some queries that could be usefult for migration. == Save OKM_CONFIG values == <source lang="sql"> select 'UPDATE OKM_CONFIG SET CFG_VALUE=''' || CFG_VALUE || ''' WHE…') |
|||
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | Here are some queries that could be | + | Here are some queries that could be useful for migration. |
| − | == Save OKM_CONFIG values == | + | == Save OKM_CONFIG table values == |
| + | Creates update script with actual OKM_CONFIG table values to be restored in new OpenKM database; | ||
<source lang="sql"> | <source lang="sql"> | ||
select 'UPDATE OKM_CONFIG SET CFG_VALUE=''' || CFG_VALUE || ''' WHERE CFG_KEY=''' || CFG_KEY || '''' from OKM_CONFIG; | select 'UPDATE OKM_CONFIG SET CFG_VALUE=''' || CFG_VALUE || ''' WHERE CFG_KEY=''' || CFG_KEY || '''' from OKM_CONFIG; | ||
</source> | </source> | ||
| + | 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 | ||
| + | <source lang="sql"> | ||
| + | select * from OKM_ACTIVITY where ACT_ACTION="CREATE_DOCUMENT" and ACT_PARAMS LIKE "%/employees/8526%"; | ||
| + | </source> | ||
| + | |||
| + | Results example( each UUID indicates a different document ): | ||
| + | <source lang="text"> | ||
| + | | 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 | | ||
| + | </source> | ||
| + | |||
| + | Looking for when has been deleted of some document named /okm_root:/employees/8526. employe_name.pdf | ||
| + | <source lang="sql"> | ||
| + | select * from OKM_ACTIVITY where ACT_ACTION="DELETE_DOCUMENT" and ACT_PARAMS LIKE "%/employees/8526%"; | ||
| + | </source> | ||
| + | |||
| + | Looking for all the activity log of the document | ||
| + | <source lang="sql"> | ||
| + | select * from OKM_ACTIVITY where ACT_PARAMS LIKE "%/employees/8526%"; | ||
| + | </source> | ||
| + | |||
| + | |||
| + | |||
| + | Tested: OpenKM (5.x) Database (MySQL). | ||
[[Category: Migration Guide]] | [[Category: Migration Guide]] | ||
Latest revision as of 16:41, 11 September 2012
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).
