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 "Rainer Döbele (JIRA)" <em...@incubator.apache.org> on 2018/10/19 10:47:00 UTC
[jira] [Closed] (EMPIREDB-80) use of DdlUtils to synchronize
database schema
[ https://issues.apache.org/jira/browse/EMPIREDB-80?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rainer Döbele closed EMPIREDB-80.
---------------------------------
Resolution: Won't Fix
{color:#333333}too old{color}
> 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 was sent by Atlassian JIRA
(v7.6.3#76005)