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