Creating your own database tables

The application comes with a general purpose table structure for storing its own “table values”.

Related tables:

Table	Description
OKM_DB_METADATA_TYPE	Used to set table metadata definition.
OKM_DB_METADATA_VALUE	Used to store tables rows.
OKM_DB_METADATA_SEQUENCE	Used to create sequences, normally used into tables.

Create a new table definition

Table OKM_DB_METADATA_TYPE fields description:

Field	Description
DMT_ID	Primary key of OKM_DB_METADATA_TYPE table.
DMT_REAL_COLUM	Set the real column name on OKM_DB_METADATA_VALUE table.
DMT_TABLE	Set the table name.
DMT_TYPE	Set the column type of the table. Available column types: - text. - boolean. - integer. - long. - file.

Example

• Table named country.
• Column named country_id of type integer goes in col00.
• Column named country_names of type text goes in col01.

DELETE FROM OKM_DB_METADATA_TYPE WHERE DMT_TABLE='country';
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('country', 'col00', 'integer', 'country_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('country', 'col01', 'text', 'country_name');

Note

Oracle and PostgreSQL databases inserts needs to set DMT_ID column value.

Oracle:

DELETE FROM OKM_DB_METADATA_TYPE WHERE DMT_TABLE='country';
INSERT INTO OKM_DB_METADATA_TYPE (DMT_ID, DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country', 'col00', 'integer', 'country_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_ID, DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country', 'col01', 'text', 'country_name');

PostgreSQL:

DELETE FROM OKM_DB_METADATA_TYPE WHERE DMT_TABLE='country';
INSERT INTO OKM_DB_METADATA_TYPE (DMT_ID, DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (nextval('hibernate_sequence'), 'country', 'col00', 'integer', 'country_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_ID, DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (nextval('hibernate_sequence'), 'country', 'col01', 'text', 'country_name');

Create a new table values

Table OKM_DB_METADATA_VALUE fields description:

Field	Description
DMV_ID	Primary key of OKM_DB_METADATA_VALUE table.
DMV_COL00	Set the column 0 value.
DMV_COL01	Set the column 1 value.
DMV_COL02	Set the column 2 value.
DMV_COL03	Set the column 3 value.
DMV_COL04	Set the column 4 value.
DMV_COL05	Set the column 5 value.
DMV_COL06	Set the column 6 value.
DMV_COL07	Set the column 7 value.
DMV_COL08	Set the column 8 value.
DMV_COL09	Set the column 9 value.
DMV_COL10	Set the column 10 value.
DMV_COL11	Set the column 11 value.
DMV_COL12	Set the column 12 value.
DMV_COL13	Set the column 13 value.
DMV_COL15	Set the column 14 value.
DMV_TABLE	Set the table name.

Example

• Table named country.
• Column named country_id of type integer goes in col00.
• Column named country_names of type text goes in col01.

DELETE FROM OKM_DB_METADATA_VALUE WHERE DMV_TABLE='country';
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('country', '001', 'Australia');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('country', '002', 'Canada');

Note

Oracle and PostgreSQL databases inserts needs to set DMV_ID column value.

Oracle:

DELETE FROM OKM_DB_METADATA_VALUE WHERE DMV_TABLE='country';
INSERT INTO OKM_DB_METADATA_VALUE (DMV_ID, DMV_TABLE, DMV_COL00, DMV_COL01) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country', '001', 'Australia');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_ID, DMV_TABLE, DMV_COL00, DMV_COL01) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country', '002', 'Canada');

PostgreSQL:

DELETE FROM OKM_DB_METADATA_VALUE WHERE DMV_TABLE='country';
INSERT INTO OKM_DB_METADATA_VALUE (DMV_ID, DMV_TABLE, DMV_COL00, DMV_COL01) VALUES (nextval('hibernate_sequence'), 'country', '001', 'Australia');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_ID, DMV_TABLE, DMV_COL00, DMV_COL01) VALUES (nextval('hibernate_sequence'), 'country', '002', 'Canada');

Create a new sequence

Table OKM_DB_METADATA_SEQUENCE fields description:

Field	Description
DMS_ID	Primary key of OKM_DB_METADATA_SEQUENCE table.
DMS_COLUM	Set the column name.
DMS_TABLE	Set the table name.
DMS_TABLE	Set the next sequence value.

Example

• Table named country.
• Column country_id

INSERT INTO OKM_DB_METADATA_SEQUENCE (DMS_TABLE, DMS_COLUMN, DMS_VALUE) VALUES ('country','country_id','1');

Note

Oracle and PostgreSQL databases inserts needs to set DMV_ID column value.

Oracle:

INSERT INTO OKM_DB_METADATA_SEQUENCE (DMS_ID, DMS_TABLE, DMS_COLUMN, DMS_VALUE) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country','country_id','1');

PostgreSQL:

INSERT INTO OKM_DB_METADATA_SEQUENCE (DMS_ID, DMS_TABLE, DMS_COLUMN, DMS_VALUE) VALUES (nextval('hibernate_sequence'), 'country','country_id','1');

Code samples

Caution

Code samples below are based on Scripting.

Select a query

Tip

Queries can be used the virtual column names adding character $ at the begining, for example $country_name.

“DatabaseMetadataUtils” class helps on building query with method “buildQuery(table, filter, order)”.

import com.openkm.dao.DatabaseMetadataDAO;
import com.openkm.dao.bean.DatabaseMetadataValue;
import com.openkm.util.DatabaseMetadataUtils;

for (DatabaseMetadataValue dmv : DatabaseMetadataDAO.executeValueQuery(DatabaseMetadataUtils.buildQuery("country", "", "$country_name"))) {
    System.out.println(dmv);
}

for (DatabaseMetadataValue dmv : DatabaseMetadataDAO.executeValueQuery("from DatabaseMetadataValue dmv where dmv.table='country' order by dmv.col01")) {
    System.out.println(dmv);
}

Update query

Tip

Method “executeValueQueryUnique” of “DatabaseMetadataDAO2 class returns only an unique record.

We must be sure the query only will retrieve a single result, otherwise will be raised an error.

import com.openkm.dao.DatabaseMetadataDAO;
import com.openkm.dao.bean.DatabaseMetadataValue;
import com.openkm.util.DatabaseMetadataUtils;

DatabaseMetadataValue dmv = DatabaseMetadataDAO.executeValueQueryUnique(DatabaseMetadataUtils.buildQuery("country", "$country_id='001'", "$country_name"));
System.out.println(dmv);
dmv.setCol01("France");
DatabaseMetadataDAO.updateValue(dmv);
System.out.println(dmv);

Delete query

import com.openkm.dao.DatabaseMetadataDAO;
import com.openkm.dao.bean.DatabaseMetadataValue;
import com.openkm.util.DatabaseMetadataUtils;

DatabaseMetadataValue dmv =DatabaseMetadataDAO.executeValueQueryUnique(DatabaseMetadataUtils.buildQuery("country", "$country_id='001'", "$country_name"));
DatabaseMetadataDAO.deleteValue(dmv.getId());

Get next sequence value

Note

When the method “getNextSequenceValue is executed and the sequence not exists, it will be automatically created, starting with value 1.

import com.openkm.dao.DatabaseMetadataDAO;

Long id = new Long( DatabaseMetadataDAO.getNextSequenceValue("country", "country_id"));
System.out.println(String.format("%03d", id));