empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Leszek Piotrowicz (JIRA)" <empire-db-...@incubator.apache.org>
Subject [jira] Created: (EMPIREDB-80) use of DdlUtils to synchronize database schema
Date Mon, 17 May 2010 10:34:43 GMT
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.


Mime
View raw message