Difference between revisions of "Database Metadata"

Revision as of 13:46, 1 September 2011

When you creates an extension is very common the need of a database to store data. You can create tables, but also need to create a bean with Hibernate XML mapping or annotations, a DAO, etc. The other way is creating meta-tables. These virtual tables are part of the OpenKM 5.1 Database Metadata feature. Let's see an example.

Our costumer want us to create a contact management. For this, we are going to create the metadata structure:

INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('contact', 'col00', 'integer', 'con_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('contact', 'col01', 'text', 'con_name');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('contact', 'col02', 'text', 'con_mail');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('contact', 'col03', 'text', 'con_phone');

In this sample, the meta-table "contact" contains 4 columns:

• COL 0 -> con_id
• COL 1 -> con_name
• COL 2 -> con_mail
• COL 3 -> con_phone

Actually a meta-table can contain no more than 15 columns.

An if you go to Administration -> Database Query you can see this new empty table:

Let's insert some data:

INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01, DMV_COL02, DMV_COL03) VALUES ('contact', '1', 'Tai Lung', 'tlung@openkm.com', '555112233');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01, DMV_COL02, DMV_COL03) VALUES ('contact', '2', 'Po Ping', 'pping@openkm.com', '555223344');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01, DMV_COL02, DMV_COL03) VALUES ('contact', '3', 'Master Shifu', 'mshifu@openkm.com', '555334455');

This is the executed query again:

As you can see, now the inserted data is shown under its correct column. But this is not all, you can also filter these results using this syntax:

SELECT|contact|$con_name='Po Ping'

Which will display only results with virtual column "con_name" has the value "Po Ping". Not the $ symbol to refer to a virtual column. You can learn more on this in the next section.

Database Query syntax

The syntax used in the Database Query is defined as:

SENTENCE|TABLES|QUERY

Where TABLES is a list of meta-tables separated by a comma.

SELECT|TABLE
SELECT|TABLE|FILTER

where TABLE is an unique meta-table.

UPDATE|TABLE
UPDATE|TABLE|VALUES
UPDATE|TABLE|VALUES|FILTER

where TABLE is an unique meta-table.

DELETE|TABLE
DELETE|TABLE|FILTER

where TABLE is an unique meta-table.

This is a sample JOIN query using metadata sintax:

SENTENCE|expediente,municipio|from DatabaseMetadataValue expe, DatabaseMetadataValue mun
where expe.table='expediente' and mun.table='municipio' and expe.$exp_mun_id=mun.$mun_id

Use from Java

Obviously Database metadata can also be used from Java. This way you can implement your own extensions which make use of this feature. This can be achieved making use of these static methods:

String DatabaseMetadataUtils.buildQuery(String table, String filter, String order)

String DatabaseMetadataUtils.buildUpdate(String table, String values, String filter)

String DatabaseMetadataUtils.buildDelete(String table, String filter)

Each one of these method will return a Hibernate query with the $xxx columns already replace by its real-column counterpart. And this Hibernate query can be executed, for example. by:

List<Object> LegacyDAO.executeQuery(String query)

The returned list, in this case, will be a list of DatabaseMetadataValue objects.