Creating your own database tables

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

Related tables:

TableDescription

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:

FieldDescription

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');

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:

FieldDescription

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');

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:

FieldDescription

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');

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

Code samples below are based on Scripting.

Select a query

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

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

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));