Difference between revisions of "Database Metadata"

From OpenKM Documentation
Jump to: navigation, search
(→‎Database Query syntax)
(→‎Sequences)
 
(20 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
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.
+
{{TOCright}} __TOC__
  
−
Our costumer want us to create a contact management. For this, we are going to create the metadata structure:
+
When you create an extension, the need for a database to store data is very common. 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.
 +
 
 +
Actually there are several data types available:
 +
 
 +
* text
 +
* boolean
 +
* integer
 +
* long
 +
 
 +
Our customer wants us to create a contact management feature. For this, we are going to create the metadata structure:
  
 
<source lang="sql">
 
<source lang="sql">
Line 19: Line 28:
 
{{Warning|Actually a meta-table can contain no more than 15 columns.}}
 
{{Warning|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:
+
And if you go to '''Administration''' -> '''Database Query''' you can see this new empty table:
  
 
<center>[[File:Database metadata 01.png|650px]]</center>
 
<center>[[File:Database metadata 01.png|650px]]</center>
Line 74: Line 83:
  
 
== Use from Java ==
 
== 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:
+
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 by making use of these static methods:
  
 
<source lang="java">
 
<source lang="java">
Line 84: Line 93:
 
</source>
 
</source>
  
−
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:
+
Each one of these methods will return a Hibernate query with the $xxx columns already replaced by its real-column counterpart. And this Hibernate query can be executed, for example. by:
  
 
<source lang="java">
 
<source lang="java">
Line 91: Line 100:
  
 
The returned list, in this case, will be a list of DatabaseMetadataValue objects.
 
The returned list, in this case, will be a list of DatabaseMetadataValue objects.
 +
 +
 +
The example has sql to register metadata bean structure, bean and class example.
 +
 +
<source lang="sql">
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col00', 'text', 'uuid_id');
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col01', 'text', 'type');
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col02', 'text', 'name');
 +
</source>
 +
 +
<source lang="java">
 +
public class ExtendedSecurity extends DatabaseMetadataCommon implements IsSerializable {
 +
    public static final String TYPE_USER = "user";
 +
    public static final String TYPE_ROLE = "role";
 +
 +
    // Metadata Virtual Name mapping
 +
    public static final String MV_TABLE_NAME = "security";
 +
    public static final String MV_COLUMN_NAME_UUID = "uuid_id";
 +
    public static final String MV_COLUMN_NAME_TYPE = "type";
 +
    public static final String MV_COLUMN_NAME_NAME = "name";
 +
 +
    private String uuid;
 +
    private String type;
 +
    private String name;
 +
 +
    @Override
 +
    public void loadFromMap(Map<String, String> map) {
 +
        super.loadFromMap(map);
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_UUID)) {
 +
            setUuid(map.get(MV_COLUMN_NAME_UUID));
 +
        }
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_TYPE)) {
 +
            setType(map.get(MV_COLUMN_NAME_TYPE));
 +
        }
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_NAME)) {
 +
            setName(map.get(MV_COLUMN_NAME_NAME));
 +
        }
 +
    }
 +
 +
    @Override
 +
    public Map<String, String> restoreToMap() {
 +
        Map<String,String> map = super.restoreToMap();
 +
 +
        if (uuid != null) {
 +
            map.put(MV_COLUMN_NAME_UUID, getUuid());
 +
        }
 +
 +
        if (type != null) {
 +
            map.put(MV_COLUMN_NAME_TYPE, getType());
 +
        }
 +
 +
        if (name != null) {
 +
            map.put(MV_COLUMN_NAME_NAME, getName());
 +
        }
 +
 +
        return map;
 +
    }
 +
 +
    public String getUuid() {
 +
        return uuid;
 +
    }
 +
 +
    public void setUuid(String uuid) {
 +
        this.uuid = uuid;
 +
    }
 +
 +
    public String getType() {
 +
        return type;
 +
    }
 +
 +
    public void setType(String type) {
 +
        this.type = type;
 +
    }
 +
 +
    public String getName() {
 +
        return name;
 +
    }
 +
 +
    public void setName(String name) {
 +
        this.name = name;
 +
    }
 +
}
 +
</source>
 +
 +
<source lang="java">
 +
public class Example {   
 +
public test() {
 +
    // get sequence
 +
    Double seq = new Double(DatabaseMetadataDAO.getNextSequenceValue("table_name", "col_name"));
 +
        // create value
 +
        final ExtendedSecurity security = new ExtendedSecurity();
 +
        security.setUuid("some uuid");
 +
        security.setName("some name");
 +
        security.setType(ExtendedSecurity.TYPE_ROLE);
 +
        security.setRealTable(ExtendedSecurity.MV_TABLE_NAME);
 +
        Double newValue = new Double(DatabaseMetadataDAO.createValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap())));
 +
 +
        // Execute query
 +
        String filter = "$" + ExtendedSecurity.MV_COLUMN_NAME_UUID + "='some uuid'";
 +
        DatabaseMetadataDAO.executeValueQuery(DatabaseMetadataUtils.buildQuery(ExtendedSecurity.MV_TABLE_NAME, filter, "")));
 +
 +
        // Update value
 +
        security.setName("some name changed");
 +
        DatabaseMetadataDAO.updateValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()));
 +
 +
        // Delete value
 +
        DatabaseMetadataDAO.deleteValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()).getId());
 +
    }
 +
}
 +
</source>
 +
 +
Can be done joins between tables for it should be used method executeMultiValueQuery:
 +
<source lang="java">
 +
List<String> tables = new ArrayList<String>();
 +
tables.add(GWTCountry.MV_TABLE_NAME);
 +
tables.add(GWTState.MV_TABLE_NAME);
 +
 +
String query = "from DatabaseMetadataValue dmv1, DatabaseMetadataValue dmv2 ";
 +
query += "where dmv1.table='" + GWTCountry.MV_TABLE_NAME + "' ";
 +
query += "and dmv2.table='" + GWTState.MV_TABLE_NAME + "' ";
 +
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "='SPAIN' ";
 +
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "=" + "dmv2.$" + GWTState.MV_COLUMN_COUNTRY_NAME;
 +
 +
DatabaseMetadataDAO.executeMultiValueQuery(DatabaseMetadataUtils.replaceVirtual(tables, query));
 +
</source>
 +
 +
== Use from Java (GWT-frontend) ==
 +
The class OKMDatabaseMetadataService has the interface definition to accessing metadata services.
 +
 +
The example has sql to register metadata bean structure, bean and class example.
 +
 +
<source lang="sql">
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col00', 'text', 'uuid_id');
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col01', 'text', 'type');
 +
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col02', 'text', 'name');
 +
</source>
 +
 +
<source lang="java">
 +
public class GWTExtendedSecurity extends DatabaseMetadataCommon implements IsSerializable {
 +
    public static final String TYPE_USER = "user";
 +
    public static final String TYPE_ROLE = "role";
 +
 +
    // Metadata Virtual Name mapping
 +
    public static final String MV_TABLE_NAME = "security";
 +
    public static final String MV_COLUMN_NAME_UUID = "uuid_id";
 +
    public static final String MV_COLUMN_NAME_TYPE = "type";
 +
    public static final String MV_COLUMN_NAME_NAME = "name";
 +
 +
    private String uuid;
 +
    private String type;
 +
    private String name;
 +
 +
    @Override
 +
    public void loadFromMap(Map<String, String> map) {
 +
        super.loadFromMap(map);
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_UUID)) {
 +
            setUuid(map.get(MV_COLUMN_NAME_UUID));
 +
        }
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_TYPE)) {
 +
            setType(map.get(MV_COLUMN_NAME_TYPE));
 +
        }
 +
 +
        if (map.containsKey(MV_COLUMN_NAME_NAME)) {
 +
            setName(map.get(MV_COLUMN_NAME_NAME));
 +
        }
 +
    }
 +
 +
    @Override
 +
    public Map<String, String> restoreToMap() {
 +
        Map<String,String> map = super.restoreToMap();
 +
 +
        if (uuid != null) {
 +
            map.put(MV_COLUMN_NAME_UUID, getUuid());
 +
        }
 +
 +
        if (type != null) {
 +
            map.put(MV_COLUMN_NAME_TYPE, getType());
 +
        }
 +
 +
        if (name != null) {
 +
            map.put(MV_COLUMN_NAME_NAME, getName());
 +
        }
 +
 +
        return map;
 +
    }
 +
 +
    public String getUuid() {
 +
        return uuid;
 +
    }
 +
 +
    public void setUuid(String uuid) {
 +
        this.uuid = uuid;
 +
    }
 +
 +
    public String getType() {
 +
        return type;
 +
    }
 +
 +
    public void setType(String type) {
 +
        this.type = type;
 +
    }
 +
 +
    public String getName() {
 +
        return name;
 +
    }
 +
 +
    public void setName(String name) {
 +
        this.name = name;
 +
    }
 +
}
 +
</source>
 +
 +
<source lang="java">
 +
public class Example {
 +
    private final OKMDatabaseMetadataServiceAsync metadataService = (OKMDatabaseMetadataServiceAsync) GWT.create(OKMDatabaseMetadataService.class);
 +
   
 +
    public test() {
 +
        // get sequence
 +
        metadataService.getNextSequenceValue("table_name", "col_name" , new AsyncCallback<Double>() {
 +
            @Override
 +
            public void onSuccess(Double result) {
 +
                int value = result.intValue();
 +
            }
 +
 +
            @Override
 +
            public void onFailure(Throwable caught) {
 +
            }
 +
        });
 +
 +
        // create value
 +
        final GWTExtendedSecurity security = new GWTExtendedSecurity();
 +
        security.setUuid("some uuid");
 +
        security.setName("some name");
 +
        security.setType(GWTExtendedSecurity.TYPE_ROLE);
 +
        security.setRealTable(GWTExtendedSecurity.MV_TABLE_NAME);
 +
        metadataService.createValue(security.restoreToMap(), new AsyncCallback<Double>() {
 +
            @Override
 +
            public void onSuccess(Double result) {
 +
                // created
 +
            }
 +
 +
            @Override
 +
            public void onFailure(Throwable caught) {
 +
            }
 +
        });
 +
 +
        // Execute query
 +
String filter = "$" + GWTExtendedSecurity.MV_COLUMN_NAME_UUID + "='some uuid'";
 +
        metadataService.executeValueQuery(GWTExtendedSecurity.MV_TABLE_NAME, filter, "", new AsyncCallback<List<Map<String,String>>>() {
 +
            @Override
 +
            public void onSuccess(List<Map<String, String>> result) {
 +
                if (result.size() > 0) {
 +
                    GWTExtendedSecurity security = new GWTExtendedSecurity();
 +
                    secutiry.loadFromMap(result.get(0));
 +
                }
 +
            }
 +
 +
            @Override
 +
            public void onFailure(Throwable caught) {
 +
                GeneralComunicator.showError("executeValueQuery", caught);
 +
            }
 +
        });
 +
 +
        // Update value
 +
        security.setName("some name changed");
 +
        metadataService.updateValue(security.restoreToMap(), new AsyncCallback<Object>() {
 +
            @Override
 +
            public void onSuccess(Object result) {
 +
            }
 +
 +
            @Override
 +
            public void onFailure(Throwable caught) {
 +
            }
 +
        });
 +
 +
        // Delete value
 +
        metadataService.deleteValue(security.restoreToMap(), new AsyncCallback<Object>() {
 +
            @Override
 +
            public void onSuccess(Object result) {
 +
            }
 +
 +
            @Override
 +
            public void onFailure(Throwable caught) {
 +
            }
 +
        });
 +
    }
 +
}
 +
</source>
 +
 +
Can be done joins between tables for it should be used method executeMultiValueQuery:
 +
<source lang="java">
 +
List<String> tables = new ArrayList<String>();
 +
tables.add(GWTCountry.MV_TABLE_NAME);
 +
tables.add(GWTState.MV_TABLE_NAME);
 +
 +
String query = "from DatabaseMetadataValue dmv1, DatabaseMetadataValue dmv2 ";
 +
query += "where dmv1.table='" + GWTCountry.MV_TABLE_NAME + "' ";
 +
query += "and dmv2.table='" + GWTState.MV_TABLE_NAME + "' ";
 +
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "='SPAIN' ";
 +
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "=" + "dmv2.$" + GWTState.MV_COLUMN_COUNTRY_NAME;
 +
 +
metadataService.executeMultiValueQuery(tables, query, new AsyncCallback<List<List<Map<String,String>>>>() {
 +
    @Override
 +
    public void onSuccess(List<List<Map<String, String>>> result) {
 +
        if (!result.isEmpty()) {
 +
            List<Map<String, String>> dmvRow = result.get(0);
 +
 +
            if (!dmvRow.isEmpty() && dmvRow.size() == 2) {
 +
                GWTState state = new GWTState();
 +
                state.loadFromMap(dmvRow.get(1));
 +
            }
 +
        }
 +
    }
 +
 +
    @Override
 +
    public void onFailure(Throwable caught) {
 +
    }
 +
});
 +
</source>
 +
 +
== Sequences ==
 +
To work with sequences there's the class getNextSequenceValue(String table, String column). Table indicates metadata table name and column indicates some metadata column name.
 +
 +
Sequences are stored int OKM_DB_METADATA_SEQUENCE table. Any new sequence start by default with value 1. When getNextSequenceValue method is executed if sequence not exists is automatically created.
 +
 +
<source lang="java">
 +
/**
 +
* getNewContactID
 +
*/
 +
public static long getNewContactID() throws DatabaseException {
 +
    return DatabaseMetadataDAO.getNextSequenceValue("contact", con_id);
 +
}
 +
</source>
 +
 
[[Category: Extension Guide]]
 
[[Category: Extension Guide]]

Latest revision as of 12:02, 12 December 2012

When you create an extension, the need for a database to store data is very common. 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.

Actually there are several data types available:

  • text
  • boolean
  • integer
  • long

Our customer wants us to create a contact management feature. 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

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

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

Database metadata 01.png

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:

Database metadata 02.png

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 by 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 methods will return a Hibernate query with the $xxx columns already replaced 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.


The example has sql to register metadata bean structure, bean and class example. 

INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col00', 'text', 'uuid_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col01', 'text', 'type');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col02', 'text', 'name');

public class ExtendedSecurity extends DatabaseMetadataCommon implements IsSerializable {
    public static final String TYPE_USER = "user";
    public static final String TYPE_ROLE = "role";

    // Metadata Virtual Name mapping 
    public static final String MV_TABLE_NAME = "security";
    public static final String MV_COLUMN_NAME_UUID = "uuid_id";
    public static final String MV_COLUMN_NAME_TYPE = "type";
    public static final String MV_COLUMN_NAME_NAME = "name";

    private String uuid;
    private String type;
    private String name;

    @Override
    public void loadFromMap(Map<String, String> map) {
        super.loadFromMap(map);

        if (map.containsKey(MV_COLUMN_NAME_UUID)) {
            setUuid(map.get(MV_COLUMN_NAME_UUID));
        }

        if (map.containsKey(MV_COLUMN_NAME_TYPE)) {
            setType(map.get(MV_COLUMN_NAME_TYPE));
        }

        if (map.containsKey(MV_COLUMN_NAME_NAME)) {
            setName(map.get(MV_COLUMN_NAME_NAME));
        }
    }

    @Override
    public Map<String, String> restoreToMap() {
        Map<String,String> map = super.restoreToMap();

        if (uuid != null) {
            map.put(MV_COLUMN_NAME_UUID, getUuid());
        }

        if (type != null) {
            map.put(MV_COLUMN_NAME_TYPE, getType());
        }

        if (name != null) {
            map.put(MV_COLUMN_NAME_NAME, getName());
        }

        return map;
    }

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

public class Example {    
public test() {
    // get sequence
    Double seq = new Double(DatabaseMetadataDAO.getNextSequenceValue("table_name", "col_name"));
        // create value
        final ExtendedSecurity security = new ExtendedSecurity();
        security.setUuid("some uuid");
        security.setName("some name");
        security.setType(ExtendedSecurity.TYPE_ROLE);
        security.setRealTable(ExtendedSecurity.MV_TABLE_NAME);
        Double newValue = new Double(DatabaseMetadataDAO.createValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap())));

        // Execute query
        String filter = "$" + ExtendedSecurity.MV_COLUMN_NAME_UUID + "='some uuid'";
        DatabaseMetadataDAO.executeValueQuery(DatabaseMetadataUtils.buildQuery(ExtendedSecurity.MV_TABLE_NAME, filter, "")));

        // Update value
        security.setName("some name changed");
        DatabaseMetadataDAO.updateValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()));

        // Delete value
        DatabaseMetadataDAO.deleteValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()).getId());
    }
}

Can be done joins between tables for it should be used method executeMultiValueQuery: 

List<String> tables = new ArrayList<String>();
tables.add(GWTCountry.MV_TABLE_NAME);
tables.add(GWTState.MV_TABLE_NAME);

String query = "from DatabaseMetadataValue dmv1, DatabaseMetadataValue dmv2 ";
query += "where dmv1.table='" + GWTCountry.MV_TABLE_NAME + "' ";
query += "and dmv2.table='" + GWTState.MV_TABLE_NAME + "' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "='SPAIN' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "=" + "dmv2.$" + GWTState.MV_COLUMN_COUNTRY_NAME;

DatabaseMetadataDAO.executeMultiValueQuery(DatabaseMetadataUtils.replaceVirtual(tables, query));

Use from Java (GWT-frontend)

The class OKMDatabaseMetadataService has the interface definition to accessing metadata services.

The example has sql to register metadata bean structure, bean and class example. 

INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col00', 'text', 'uuid_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col01', 'text', 'type');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col02', 'text', 'name');

public class GWTExtendedSecurity extends DatabaseMetadataCommon implements IsSerializable {
    public static final String TYPE_USER = "user";
    public static final String TYPE_ROLE = "role";

    // Metadata Virtual Name mapping 
    public static final String MV_TABLE_NAME = "security";
    public static final String MV_COLUMN_NAME_UUID = "uuid_id";
    public static final String MV_COLUMN_NAME_TYPE = "type";
    public static final String MV_COLUMN_NAME_NAME = "name";

    private String uuid;
    private String type;
    private String name;

    @Override
    public void loadFromMap(Map<String, String> map) {
        super.loadFromMap(map);

        if (map.containsKey(MV_COLUMN_NAME_UUID)) {
            setUuid(map.get(MV_COLUMN_NAME_UUID));
        }

        if (map.containsKey(MV_COLUMN_NAME_TYPE)) {
            setType(map.get(MV_COLUMN_NAME_TYPE));
        }

        if (map.containsKey(MV_COLUMN_NAME_NAME)) {
            setName(map.get(MV_COLUMN_NAME_NAME));
        }
    }

    @Override
    public Map<String, String> restoreToMap() {
        Map<String,String> map = super.restoreToMap();

        if (uuid != null) {
            map.put(MV_COLUMN_NAME_UUID, getUuid());
        }

        if (type != null) {
            map.put(MV_COLUMN_NAME_TYPE, getType());
        }

        if (name != null) {
            map.put(MV_COLUMN_NAME_NAME, getName());
        }

        return map;
    }

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

public class Example {
    private final OKMDatabaseMetadataServiceAsync metadataService = (OKMDatabaseMetadataServiceAsync) GWT.create(OKMDatabaseMetadataService.class);
    
    public test() {
        // get sequence
        metadataService.getNextSequenceValue("table_name", "col_name" , new AsyncCallback<Double>() {
            @Override
            public void onSuccess(Double result) {
                int value = result.intValue();
            }

            @Override
            public void onFailure(Throwable caught) {
            }
        });

        // create value
        final GWTExtendedSecurity security = new GWTExtendedSecurity();
        security.setUuid("some uuid");
        security.setName("some name");
        security.setType(GWTExtendedSecurity.TYPE_ROLE);
        security.setRealTable(GWTExtendedSecurity.MV_TABLE_NAME);
        metadataService.createValue(security.restoreToMap(), new AsyncCallback<Double>() {
            @Override
            public void onSuccess(Double result) {
                // created
            }

            @Override
            public void onFailure(Throwable caught) {
            }
        });

        // Execute query
	String filter = "$" + GWTExtendedSecurity.MV_COLUMN_NAME_UUID + "='some uuid'";
        metadataService.executeValueQuery(GWTExtendedSecurity.MV_TABLE_NAME, filter, "", new AsyncCallback<List<Map<String,String>>>() {
            @Override
            public void onSuccess(List<Map<String, String>> result) {
                if (result.size() > 0) {
                    GWTExtendedSecurity security = new GWTExtendedSecurity();
                    secutiry.loadFromMap(result.get(0));
                }
            }

            @Override
            public void onFailure(Throwable caught) {
                GeneralComunicator.showError("executeValueQuery", caught);
            }
        });

        // Update value
        security.setName("some name changed");
        metadataService.updateValue(security.restoreToMap(), new AsyncCallback<Object>() {
            @Override
            public void onSuccess(Object result) {
            }

            @Override
            public void onFailure(Throwable caught) {
            }
        });

        // Delete value
        metadataService.deleteValue(security.restoreToMap(), new AsyncCallback<Object>() {
            @Override
            public void onSuccess(Object result) {
            }

            @Override
            public void onFailure(Throwable caught) {
            }
        });
    }
}

Can be done joins between tables for it should be used method executeMultiValueQuery: 

List<String> tables = new ArrayList<String>();
tables.add(GWTCountry.MV_TABLE_NAME);
tables.add(GWTState.MV_TABLE_NAME);

String query = "from DatabaseMetadataValue dmv1, DatabaseMetadataValue dmv2 ";
query += "where dmv1.table='" + GWTCountry.MV_TABLE_NAME + "' ";
query += "and dmv2.table='" + GWTState.MV_TABLE_NAME + "' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "='SPAIN' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "=" + "dmv2.$" + GWTState.MV_COLUMN_COUNTRY_NAME;

metadataService.executeMultiValueQuery(tables, query, new AsyncCallback<List<List<Map<String,String>>>>() {
    @Override
    public void onSuccess(List<List<Map<String, String>>> result) {
        if (!result.isEmpty()) {
            List<Map<String, String>> dmvRow = result.get(0);

            if (!dmvRow.isEmpty() && dmvRow.size() == 2) {
                GWTState state = new GWTState();
                state.loadFromMap(dmvRow.get(1));
            }
        } 
    }

    @Override
    public void onFailure(Throwable caught) {
    }
});

Sequences

To work with sequences there's the class getNextSequenceValue(String table, String column). Table indicates metadata table name and column indicates some metadata column name.

Sequences are stored int OKM_DB_METADATA_SEQUENCE table. Any new sequence start by default with value 1. When getNextSequenceValue method is executed if sequence not exists is automatically created. 

/**
 * getNewContactID
 */
public static long getNewContactID() throws DatabaseException {
    return DatabaseMetadataDAO.getNextSequenceValue("contact", con_id);
}
Retrieved from "https://www.openkm.com/wiki/index.php?title=Database_Metadata&oldid=8300"