Database Schema Management

An OSGi service inserts and updates database tables, columns and keys. The database and schema must exists.

Purpose

Usually relational database schema is managed by heavyweight orm frameworks like hibernate. DBSchema is an alternative, if mapping is unneeded and only a lightweight solution to manage relational database objects is required in OSGi environments.

Maven

<dependency>
  <groupId>org.sodeac</groupId>
  <artifactId>org.sodeac.dbschema.api</artifactId>
  <version>1.0.0</version>
</dependency>
<dependency>
  <groupId>org.sodeac</groupId>
  <artifactId>org.sodeac.dbschema.provider</artifactId>
  <version>1.0.2</version>
</dependency>

Install to local m2-Repository (+ H2 schema driver)

mvn org.apache.maven.plugins:maven-dependency-plugin:2.8:get -Dartifact="org.sodeac:org.sodeac.dbschema.api:1.0.0"
mvn org.apache.maven.plugins:maven-dependency-plugin:2.8:get -Dartifact="org.sodeac:org.sodeac.dbschema.provider:1.0.2"
mvn org.apache.maven.plugins:maven-dependency-plugin:2.8:get -Dartifact="org.sodeac:org.sodeac.dbschema.driver.h2:1.0.0"

Install to Apache Karaf / Apache ServiceMix (+ H2 schema driver)

bundle:install -s mvn:org.sodeac/org.sodeac.dbschema.api/1.0.0
bundle:install -s mvn:org.sodeac/org.sodeac.dbschema.provider/1.0.2
bundle:install -s mvn:org.sodeac/org.sodeac.dbschema.driver.base/1.0.0
bundle:install -s mvn:org.sodeac/org.sodeac.dbschema.driver.h2/1.0.0

OSGi-Dependencies

Bundle-RequiredExecutionEnvironment: JavaSE-1.8

org.osgi.framework;version="[1.8,2)"
org.osgi.service.component;version="[1.3,2)"
org.osgi.service.log;version="[1.3,2)"

Purpose

Getting Started

Inject IDatabaseSchemaProcessor into your component.

@Reference
protected volatile IDatabaseSchemaProcessor databaseSchemaProcessor = null;

Usage: create simple schema with java fluent api

SchemaSpec spec = new SchemaSpec("business");
spec.setDbmsSchemaName(connection.getSchema());

spec.addTable("company")		
.addColumn("id", IColumnType.ColumnType.CHAR.toString(),false,36)
	.setPrimaryKey()
	.endColumnDefinition()
.addColumn("company_name", IColumnType.ColumnType.VARCHAR.toString(),false,256)
	.endColumnDefinition()
.addColumn("established_since", IColumnType.ColumnType.DATE.toString(),true)
	.endColumnDefinition()
;

spec.addTable("employee")	
.addColumn("id", IColumnType.ColumnType.CHAR.toString(),false,36)
	.setPrimaryKey()
	.endColumnDefinition()
.addColumn("company_id", IColumnType.ColumnType.CHAR.toString(),true,36)
	.setForeignKey("fk1_employee", "company","id")
	.endColumnDefinition()
.addColumn("employee_name", IColumnType.ColumnType.VARCHAR.toString(),false,256)
	.endColumnDefinition()
.addColumn("birthday", IColumnType.ColumnType.DATE.toString(),false)
	.endColumnDefinition()
.addColumn("date_of_joining", IColumnType.ColumnType.DATE.toString(),false)
	.endColumnDefinition()
.addColumn("date_of_leaving", IColumnType.ColumnType.DATE.toString(),true)
	.endColumnDefinition()
;

schemaProcessor.checkSchemaSpec(spec, connection);

Supported dbm systems

DBMS	Provider-Bundle
H2	org.sodeac:org.sodeac.dbschema.driver.h2
PostgreSQL	org.sodeac:org.sodeac.dbschema.driver.postgresql
Oracle	org.sodeac:org.sodeac.dbschema.driver.oracle

More database managment systems can be supported by providing an OSGi service implements IDatabaseSchemaDriver .

Supported column types

Type	Key	Provider-Bundle
char	IColumnType.ColumnType.CHAR.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
varchar	IColumnType.ColumnType.VARCHAR.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
clob	IColumnType.ColumnType.CLOB.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
boolean	ColumnType.BOOLEAN.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
smallint	ColumnType.SMALLINT.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
integer	ColumnType.INTEGER.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
bigint	ColumnType.BIGINT.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
real	ColumnType.REAL.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
double	ColumnType.DOUBLE.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
timestamp	ColumnType.TIMESTAMP.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
date	ColumnType.DATE.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
time	ColumnType.TIME.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
binary	ColumnType.BINARY.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2
blob	ColumnType.BLOB.toString()	org.sodeac:org.sodeac.dbschema.provider:1.0.2

More column types can be supported by providing an OSGi service implements IColumnType .

Limits

• only single column primary keys are supported
• primary key specification updates are ignored in already existing database tables
• tablespaces specification updates are ignored in already existing database tables or keys/indices
• column types updates are limited by limits of used dbms
• removing tables, columns and indices from schema specification are ignored if objects already exist in dbms
• only one foreign key specification is allowed for one tablecolumn
• no support to specify functions, procedures, trigger, sequences and views

License

Eclipse Public License 2.0