Database Model Management

Many organizations need to have detailed control over the database schema of their server products.
It's not an option to generate schema from a JPA model, because the schema has a long history from before JPA. There must be good control over upgrade to newer versions.

I decided to make make a tool to ease management of the Database Model. The tool may replace tools like ERStudio.



The plus sides of ERStudio :

• Logical model. We generate physical models for different db types from the same logical model.
• Type templates. Column templates can be defined, with name, type, documentation. If the template is changed (typically length of a varchar), the change takes effect for all tables that use the template
• Grouping. Entities (tables) are grouped into sets. (User management, alarm handling, etc)
• Documentation. Model, groups, tables, templates and columns have a documentation tag. 

The minus sides of ERStudio :

• Sequences are poorly handled by the tool.
• Tool has steep learning curve, and many dialogs/wizards with lots of options
• Cumbersome to generate DDL
• A license server must be set up
• Expensive license

Db Model Tool

A modeling tool for managing database models.

The tool is an Eclipse plugin.
It has

• Editor for db model language files. 
• Code generator, that generates ddl files

Current Features

• A domain specific language (DSL) used to define
• logical model
• mappings to physical (db type specific) schema
• type templates
• Arrange the model entities into groups (subsystems)
• Attach documentation tags 
• tables, sequences, constraints (indices, cross-refs)
• conditionally include / exclude model elements in DDL
• Generates DDLs for each physical mapping
• Generates documentation reports
• Eclipse plugin with editor for the DSL, supports
• syntax highlighting
• syntax and semantic error indication
• refactoring support (rename an item, will rename all references)
• Automatic regeneration of DDLs when saving the model.

Future Features

• Version support:
• Delta scripts to change schema to next version
• Generate delta script by diff of two model versions
• Delta scripts can also do data manipulation
• Diagram View

Db Model Language

The Db Model language (dbm) is used to

• logical model
• mappings to physical (db type specific) schema
• type templates
• Arrange the model entities into groups (subsystems)
• Attach documentation tags 
• tables, sequences, constraints (indices, cross-refs)
• conditionally include / exclude model elements in DDL

Db Model language files have extension .dbm

Elements of dbm language

types

The types section is used to define all table column types used in the model. 

Example
"

types (

int

long

varchar

boolean

blob

)

"

mapping

A mapping element is used to define how logical model to db type (oracle, postgres) translation should be done.

Example
"

mapping oracle (

int : 'number(10,0)'

long : 'number(20,0)'

varchar : 'varchar2($arg1)'

boolean : 'number(1,0)'

blob : 'blob'

)

mapping postgres (

int : 'int4'

long : 'int8'

varchar : ' varchar ($arg1)'

boolean : 'boolean'

blob : 'bytea'

)

"

aliases

Aliases is used to associate column names with datatypes, typically used for columns that should be defined the same in multiple tables.

Example
"

aliases (

id int not null

name varchar(100)

description varchar( 1000 )

)

"

table

Table element is used to define a database table.

Example

"

table alarm (

id,

description,

active boolean,

cause varchar(100),

primary key alarm_pk (id)

)

"

In this example, the id and description columns have not a specified type.
Instead the type with same name as defined in aliases, is used.

sequence

sequence element is used to define a sequence in the database.

Example
"

sequence gen_seq (

cache = 10

increment = 1

start = 100

)

"

index

index element is used to define an index constraint in the database.

Example

"

unique index alarm_active_ndx on alarm ( active id )

index alarm_cause_ndx on alarm ( cause )

"

reference

reference element is used to define a cross reference constraint in the database.

Example
"

reference alarm_actor_xr: alarm.actor_id -> actor.id

reference alarm_state_xr: alarm.description -> state.description cascade delete

"

def

def is used to define properties in the db model

Example

"

def xref

"

if

if is used to conditionally create database objects (tables, sequences, constraints ...)

Example

"

if xref {

reference alarm_actor_xr: alarm.actor_id -> actor.id

reference alarm_state_xr: alarm.desc -> state.desc cascade delete

}

"

groups

groups element is used to group tables into subsystems. A group has a name and a set of tables.

Example

"

groups (

performance : perf_data perf_param ^groups

alarm : alarm state

)

"

Note that the performance group contains a table with name groups. But 'groups' is defined as a keyword in the dbm language. The way to resolve such conflicts, is to prefix groups table name with a circumflex: ^




Under Construction...

TEXT INSIDE SCROLL BOX HERppa kkekehehhe hfhfhfhf evggvjjbdehbe ece ce ce ce euhuhu dcdcdcdcdc dcdcdccd dc d dc dINSIDE SCROLL BOX HERppa kkekehehhe hfhfhfhf evggvjjbdehbe ece ce ce ce euhuhu dcdcdcdcdc dcdcdccd dc d dc dINSIDE SCROLL BOX HERppa kkekehehhe hfhfhfhf evggvjjbdehbe ece ce ce ce euhuhu dcdcdcdcdc dcdcdccd dc d dc dc dc dc dcdcdccdcd rggtgt gtt gtg tggtggttgE

Her er