You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by "Leszek Piotrowicz (JIRA)" <em...@incubator.apache.org> on 2010/05/17 12:34:43 UTC
[jira] Created: (EMPIREDB-80) use of DdlUtils to synchronize
database schema
use of DdlUtils to synchronize database schema
----------------------------------------------
Key: EMPIREDB-80
URL: https://issues.apache.org/jira/browse/EMPIREDB-80
Project: Empire-DB
Issue Type: New Feature
Components: Core
Reporter: Leszek Piotrowicz
Priority: Minor
Empire-db has supplementary ddl sql creation. Generated sql may be then used to create the whole database or table from scratch.
However most often changes in database schema are incremental, in example new columns are added to existing tables or columns
are indexed for better search performance. empire-db does not support such situations.
There is project called DdlUtils (also from apache foundation) which may do a lot broader set of database changes, not only
creation from scratch but also alteration of an existing database schema to a new schema - ie:
- adding/removing table/column
- change of column type precision, change of column type
- adding/removing index
- adding/removing foreign key
It does not handle table or column rename but it is difficult to do it automatically anyway. If necessary data from
existing tables are copied to helper tables to avoid data loss.
I have written a function which "translates" empire-db DBDatabase to ddlutils Database (with tables, indexes and foreign keys).
Then the resulting database model may be used directly by ddlutils to create or alter table schema, write sql etc.
Some additional notes:
The translation function are to be put in DBDatabase, it is one monolithic function, for readability it should be split into smaller functions
How to create sql/alter schema using DdlUtils is desribed in: <a href="http://db.apache.org/ddlutils/api-usage.html">http://db.apache.org/ddlutils/api-usage.html</a>
EmpireDb type system does not always match DdlUtils type system - in example INTEGER is translated to either INTEGER or BIGINT based on column size
DdlUtils does not support views so they are not translated
DdlUtils does not support H2 database (however there is a patch contributed by H2 author). It is not known when H2 support will be oficially available in DdlUtils (work progress seems rather slow in this project)
<pre>
public Database createDatabase() {
// transform database structure from empiredb to ddlutils
Database db = new Database();
db.setName("model");
// add tables
for (DBTable tableEmp : getTables()) {
Table table = new Table();
List<DBColumn> primaryKeyColumnsEmp = tableEmp.getPrimaryKey() != null ? Arrays.asList(tableEmp.getPrimaryKey().getColumns()) : Collections.EMPTY_LIST;
table.setName(tableEmp.getName());
// add table columns
for (DBColumn colEmp1 : tableEmp.getColumns()) {
DBTableColumn colEmp = (DBTableColumn) colEmp1; // cast to access column default value
Column col = new Column();
col.setName(colEmp.getName());
col.setPrimaryKey(primaryKeyColumnsEmp.contains(colEmp));
col.setRequired(colEmp.isRequired());
int size = 0;
int scale = 0;
switch (colEmp.getDataType()) {
case AUTOINC:
col.setTypeCode(Types.INTEGER);
break;
case BLOB:
col.setTypeCode(Types.BLOB);
break;
case BOOL:
col.setTypeCode(Types.BOOLEAN);
break;
case CHAR:
col.setTypeCode(Types.CHAR);
size = (int) colEmp.getSize();
if (size > 0) {
col.setSizeAndScale(size, 0);
}
break;
case CLOB:
col.setTypeCode(Types.CLOB);
break;
case DATE:
col.setTypeCode(Types.DATE);
break;
case DATETIME:
col.setTypeCode(Types.TIMESTAMP);
break;
case DECIMAL:
col.setTypeCode(Types.DECIMAL);
size = (int) colEmp.getSize();
scale = (int) ((colEmp.getSize() - size) * 10 + 0.5);
if (size > 0) {
col.setSizeAndScale(size, scale);
}
break;
case DOUBLE:
col.setTypeCode(Types.DOUBLE);
break;
case INTEGER:
size = (int) colEmp.getSize();
if (size > 4) {
col.setTypeCode(Types.BIGINT);
} else {
col.setTypeCode(Types.INTEGER);
}
break;
case TEXT:
col.setTypeCode(Types.VARCHAR);
size = (int) colEmp.getSize();
if (size > 0) {
col.setSizeAndScale(size, 0);
}
break;
default:
throw new RuntimeException("unknown column type");
}
col.setAutoIncrement(colEmp.getDataType() == DataType.AUTOINC);
if (colEmp.getDefaultValue() != null) {
col.setDefaultValue(colEmp.getDefaultValue().toString());
}
table.addColumn(col);
}
// add table index
for (DBIndex idxEmp : tableEmp.getIndexes()) {
Index idx = null;
if (idxEmp.getType() == DBIndex.STANDARD) {
idx = new NonUniqueIndex();
} else if (idxEmp.getType() == DBIndex.UNIQUE) {
idx = new UniqueIndex();
}
if (idx != null) {
idx.setName(idxEmp.getName());
for (DBColumn idxEmpCol : idxEmp.getColumns()) {
idx.addColumn(new IndexColumn(idxEmpCol.getName()));
}
table.addIndex(idx);
}
}
db.addTable(table);
}
// add foreign keys
for (DBRelation relEmp : getRelations()) {
ForeignKey rel = new ForeignKey();
rel.setName(relEmp.getName());
Table srcTable = null;
Table dstTable = null;
for (DBReference refEmp : relEmp.getReferences()) {
if (srcTable == null) {
srcTable = db.findTable(((DBTable) refEmp.getSourceColumn().getRowSet()).getName());
}
if (dstTable == null) {
dstTable = db.findTable(((DBTable) refEmp.getTargetColumn().getRowSet()).getName());
}
rel.addReference(new Reference(srcTable.findColumn(refEmp.getSourceColumn().getName()), dstTable.findColumn(refEmp.getTargetColumn().getName())));
}
rel.setForeignTable(dstTable);
srcTable.addForeignKey(rel);
}
// resolve internal model references, validate model
db.initialize();
return db;
}
</pre>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (EMPIREDB-80) use of DdlUtils to synchronize
database schema
Posted by "Leszek Piotrowicz (JIRA)" <em...@incubator.apache.org>.
[ https://issues.apache.org/jira/browse/EMPIREDB-80?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Leszek Piotrowicz updated EMPIREDB-80:
--------------------------------------
Attachment: aaa.txt
Code with grant license to ASF
> use of DdlUtils to synchronize database schema
> ----------------------------------------------
>
> Key: EMPIREDB-80
> URL: https://issues.apache.org/jira/browse/EMPIREDB-80
> Project: Empire-DB
> Issue Type: New Feature
> Components: Core
> Reporter: Leszek Piotrowicz
> Priority: Minor
> Attachments: aaa.txt
>
>
> Empire-db has supplementary ddl sql creation. Generated sql may be then used to create the whole database or table from scratch.
> However most often changes in database schema are incremental, in example new columns are added to existing tables or columns
> are indexed for better search performance. empire-db does not support such situations.
> There is project called DdlUtils (also from apache foundation) which may do a lot broader set of database changes, not only
> creation from scratch but also alteration of an existing database schema to a new schema - ie:
> - adding/removing table/column
> - change of column type precision, change of column type
> - adding/removing index
> - adding/removing foreign key
> It does not handle table or column rename but it is difficult to do it automatically anyway. If necessary data from
> existing tables are copied to helper tables to avoid data loss.
> I have written a function which "translates" empire-db DBDatabase to ddlutils Database (with tables, indexes and foreign keys).
> Then the resulting database model may be used directly by ddlutils to create or alter table schema, write sql etc.
> Some additional notes:
> The translation function are to be put in DBDatabase, it is one monolithic function, for readability it should be split into smaller functions
> How to create sql/alter schema using DdlUtils is desribed in: <a href="http://db.apache.org/ddlutils/api-usage.html">http://db.apache.org/ddlutils/api-usage.html</a>
> EmpireDb type system does not always match DdlUtils type system - in example INTEGER is translated to either INTEGER or BIGINT based on column size
> DdlUtils does not support views so they are not translated
> DdlUtils does not support H2 database (however there is a patch contributed by H2 author). It is not known when H2 support will be oficially available in DdlUtils (work progress seems rather slow in this project)
> <pre>
> public Database createDatabase() {
> // transform database structure from empiredb to ddlutils
> Database db = new Database();
> db.setName("model");
> // add tables
> for (DBTable tableEmp : getTables()) {
> Table table = new Table();
> List<DBColumn> primaryKeyColumnsEmp = tableEmp.getPrimaryKey() != null ? Arrays.asList(tableEmp.getPrimaryKey().getColumns()) : Collections.EMPTY_LIST;
> table.setName(tableEmp.getName());
> // add table columns
> for (DBColumn colEmp1 : tableEmp.getColumns()) {
> DBTableColumn colEmp = (DBTableColumn) colEmp1; // cast to access column default value
> Column col = new Column();
> col.setName(colEmp.getName());
> col.setPrimaryKey(primaryKeyColumnsEmp.contains(colEmp));
> col.setRequired(colEmp.isRequired());
> int size = 0;
> int scale = 0;
> switch (colEmp.getDataType()) {
> case AUTOINC:
> col.setTypeCode(Types.INTEGER);
> break;
> case BLOB:
> col.setTypeCode(Types.BLOB);
> break;
> case BOOL:
> col.setTypeCode(Types.BOOLEAN);
> break;
> case CHAR:
> col.setTypeCode(Types.CHAR);
> size = (int) colEmp.getSize();
> if (size > 0) {
> col.setSizeAndScale(size, 0);
> }
> break;
> case CLOB:
> col.setTypeCode(Types.CLOB);
> break;
> case DATE:
> col.setTypeCode(Types.DATE);
> break;
> case DATETIME:
> col.setTypeCode(Types.TIMESTAMP);
> break;
> case DECIMAL:
> col.setTypeCode(Types.DECIMAL);
> size = (int) colEmp.getSize();
> scale = (int) ((colEmp.getSize() - size) * 10 + 0.5);
> if (size > 0) {
> col.setSizeAndScale(size, scale);
> }
> break;
> case DOUBLE:
> col.setTypeCode(Types.DOUBLE);
> break;
> case INTEGER:
> size = (int) colEmp.getSize();
> if (size > 4) {
> col.setTypeCode(Types.BIGINT);
> } else {
> col.setTypeCode(Types.INTEGER);
> }
> break;
> case TEXT:
> col.setTypeCode(Types.VARCHAR);
> size = (int) colEmp.getSize();
> if (size > 0) {
> col.setSizeAndScale(size, 0);
> }
> break;
> default:
> throw new RuntimeException("unknown column type");
> }
> col.setAutoIncrement(colEmp.getDataType() == DataType.AUTOINC);
> if (colEmp.getDefaultValue() != null) {
> col.setDefaultValue(colEmp.getDefaultValue().toString());
> }
> table.addColumn(col);
> }
> // add table index
> for (DBIndex idxEmp : tableEmp.getIndexes()) {
> Index idx = null;
> if (idxEmp.getType() == DBIndex.STANDARD) {
> idx = new NonUniqueIndex();
> } else if (idxEmp.getType() == DBIndex.UNIQUE) {
> idx = new UniqueIndex();
> }
> if (idx != null) {
> idx.setName(idxEmp.getName());
> for (DBColumn idxEmpCol : idxEmp.getColumns()) {
> idx.addColumn(new IndexColumn(idxEmpCol.getName()));
> }
> table.addIndex(idx);
> }
> }
> db.addTable(table);
> }
> // add foreign keys
> for (DBRelation relEmp : getRelations()) {
> ForeignKey rel = new ForeignKey();
> rel.setName(relEmp.getName());
> Table srcTable = null;
> Table dstTable = null;
> for (DBReference refEmp : relEmp.getReferences()) {
> if (srcTable == null) {
> srcTable = db.findTable(((DBTable) refEmp.getSourceColumn().getRowSet()).getName());
> }
> if (dstTable == null) {
> dstTable = db.findTable(((DBTable) refEmp.getTargetColumn().getRowSet()).getName());
> }
> rel.addReference(new Reference(srcTable.findColumn(refEmp.getSourceColumn().getName()), dstTable.findColumn(refEmp.getTargetColumn().getName())));
> }
> rel.setForeignTable(dstTable);
> srcTable.addForeignKey(rel);
> }
> // resolve internal model references, validate model
> db.initialize();
> return db;
> }
> </pre>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.