如何比较两个数据库结构？ [英] How to compare two database structures?

问题描述

我正在使用 SQLite / Hibernate 。想法是每次启动应用程序时检查数据库结构是否是最新的。我在DB文件夹中有我现有的数据库，每次应用程序启动时，我都会在DB / structure文件夹中创建最新的数据库。

我想比较它们，如果我的现有数据库是旧的，请将数据复制到最新的数据库。摆脱旧的数据库，并在它的地方移动新的数据库。

到目前为止，我已经尝试过 SchemaCrawler ，但是我收到了错误并且无法弄清楚。

更新：

我将SchemaCrawler连接到两个数据库：

  public Sc​​hemaConroller（）{
 
 SchemaCrawlerOptions options = new SchemaCrawlerOptions（）; 
 options.setSchemaInfoLevel（SchemaInfoLevelBuilder.standard（））; 
 
目录catalog1 = null; 
尝试{
 Connection conn = getConnection（）; 
 catalog1 = SchemaCrawlerUtility.getCatalog（conn，options）; 
 for（Schema schema：catalog1.getSchemas（））{
 System.out.println（schema）; （table table：catalog1.getTables（schema））{
 System.out.println（o  - >+ table +size：+ table.getColumns（）。size（ ））; 
 / * for（Column column：table.getColumns（））{
 System.out.println（o  - >+ column）; 
 
 
 
} catch（Exception e）{
 // TODO自动生成的catch块
 e.printStackTrace（）; 
} 
 
目录catalog2 = null; 
尝试{
 Connection conn = getConnection2（）; 
 catalog2 = SchemaCrawlerUtility.getCatalog（conn，options）; 
 for（Schema schema：catalog2.getSchemas（））{
 System.out.println（schema）; （table table：catalog2.getTables（schema））{
 System.out.println（o  - >+ table +size：+ table.getColumns（）。size（ ））; 
 / * for（Column column：table.getColumns（））{
 System.out.println（o  - >+ column）; 
 
 
 
} catch（Exception e）{
 // TODO自动生成的catch块
 e.printStackTrace（）; 
} 
 
 if（catalog1.equals（catalog2））{
 System.out.println（>>>>>>>> >>>>><<<<<<<<<<<<<<<< 
} else {
 System.out.println（>>>>>>>>>>> DATABASE IS OUT OF DATE<< <<<<<<<<<<<<）; 
 
 
 $ / code $ / pre 
 
但我总是得到肯定的答案， code> catalog1 == catalog2   - 我总是得到否定的。如何正确比较数据结构？
解决方案
 Alyona， 请看看 schemacrawler-diff ，了解如何开始通过编程比较数据库结构。请注意，这段代码不是生产准备好的，也不支持，所以您将根据您的需要开发自己的比较数据库的方法。
 
 
 
 Sualeh Fatehi，  SchemaCrawler  
 
I'm using SQLite/Hibernate. Idea is to check each time app starts whether database structure is up to date. I have my existing database in "DB" folder and each time app start I'm creating up to date database in "DB/structure" folder. 

I want to compare them and if my existing database is old, copy data to up to date database. Get rid of old database and move fresh one in it's place. 

So far I've tried SchemaCrawler, but I was getting errors with it and couldn't figure it out.

UPDATE:

I connected with SchemaCrawler to both databases:
public SchemaConroller() {

        SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        options.setSchemaInfoLevel(SchemaInfoLevelBuilder.standard());

        Catalog catalog1=null;
        try {
            Connection conn = getConnection();
            catalog1 = SchemaCrawlerUtility.getCatalog(conn, options);
            for (Schema schema : catalog1.getSchemas()) {
                System.out.println(schema);
                for (Table table : catalog1.getTables(schema)) {
                    System.out.println("o--> " + table + "    size: "+table.getColumns().size());
                    /*for (Column column : table.getColumns()) {
                        System.out.println("     o--> " + column);
                    }*/
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        Catalog catalog2=null;
        try {
            Connection conn = getConnection2();
            catalog2 = SchemaCrawlerUtility.getCatalog(conn, options);
            for (Schema schema : catalog2.getSchemas()) {
                System.out.println(schema);
                for (Table table : catalog2.getTables(schema)) {
                    System.out.println("o--> " + table + "    size: "+table.getColumns().size());
                    /*for (Column column : table.getColumns()) {
                        System.out.println("     o--> " + column);
                    }*/
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        if(catalog1.equals(catalog2)){
            System.out.println(">>>>>>>>>>>>DATABASE IS UP TO DATE<<<<<<<<<<<<<<");
        }else{
            System.out.println(">>>>>>>>>>>>DATABASE IS OUT OF DATE<<<<<<<<<<<<<<");
        }
    }
But I always get positive answer, if I try catalog1 == catalog2 - I always get negative. How to properly compare data structure?
解决方案
Alyona,

Please take a look at schemacrawler-diff for ideas on how to get started with programatically comparing database structures. Please note that this code is not production ready, nor is it supported, so you will to develop your own method of comparing databases, based on your needs.

Sualeh Fatehi, SchemaCrawler

                        
这篇关于如何比较两个数据库结构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！