透过liquibase将PostgreSQL数据库导入到H2数据库

通过liquibase将PostgreSQL数据库导入到H2数据库
1.背景
项目中使用的数据库是PostgreSQL，在做测试时，想使用H2代替。
2.问题
2.1 保留字：在PostgreSQL中使用了几个H2的保留字，例如 "end", "offset", "foreign"，这些保留字是不能作为表的字段名。
2.2 字段类型：在PostgreSQL中double precision类型需要被替换成H2的float。
3.方法
liquibase提供一个ant任务：generateChangeLog，可以将PostgreSQL数据库中表，约束和数据都导出到一个xml文件中。然后通过liquibase根据导出的xml文件，在H2中生成数据库。
4.结果
生成xml文件，内容是liquibase的ChangeLog。

在使用generateChangeLog时，需要做一些扩展。
1.generateChangeLog任务

<generateChangeLog            outputFile="xxxxx\\structure.xml"            driver="org.postgresql.Driver"            url="jdbc:postgresql://localhost:5432/db"            username="name"            password="pass"            classpathref="classpath"    loglevel="debug"databasename="code">import java.util.ArrayList;import java.util.Arrays;import java.util.List;import liquibase.database.core.PostgresDatabase;import liquibase.database.typeconversion.TypeConverterFactory;import liquibase.serializer.ChangeLogSerializerFactory;import liquibase.snapshot.DatabaseSnapshotGeneratorFactory;public class ExtendPostgresDatabase    extends PostgresDatabase{    public static final List<String> reservedWords = Arrays.asList( new String[] { "end", "offset", "foreign" } );    public static final List<String> ignoreTables = new ArrayList<String>();    public static final List<String> ignoreIndexes = new ArrayList<String>();    public static final List<String> ignorePrimaryKeys = new ArrayList<String>();    public static final List<String> ignoreForeignKeys = new ArrayList<String>();    public ExtendPostgresDatabase()    {        TypeConverterFactory.getInstance().register( new Postgres2H2TypeConverter() );//注册类型转换器，将Postgres的数据类型转换成H2        ChangeLogSerializerFactory.getInstance().register( new ExtendXMLChangeLogSerializer() );//注册ChangeLog文件生成器        DatabaseSnapshotGeneratorFactory.getInstance().register( new ExtendPostgresDatabaseSnapshotGenerator() );//注册数据库快照    }    @Override    public boolean isReservedWord( String word )    {        boolean reservedWord = super.isReservedWord( word );        if ( reservedWord )        {            return reservedWord;        }        return reservedWords.contains( word.toLowerCase() );    }}

3.扩展PostgresDatabaseSnapshotGenerator类

import java.util.Set;import liquibase.database.Database;import liquibase.database.structure.ForeignKey;import liquibase.database.structure.Index;import liquibase.database.structure.PrimaryKey;import liquibase.database.structure.Table;import liquibase.diff.DiffStatusListener;import liquibase.exception.DatabaseException;import liquibase.snapshot.DatabaseSnapshot;import liquibase.snapshot.jvm.PostgresDatabaseSnapshotGenerator;public class ExtendPostgresDatabaseSnapshotGenerator    extends PostgresDatabaseSnapshotGenerator{    @Override    public DatabaseSnapshot createSnapshot( Database database, String requestedSchema, Set<DiffStatusListener> listeners )        throws DatabaseException    {        DatabaseSnapshot snapshot = super.createSnapshot( database, requestedSchema, listeners );        //过滤掉不希望被导出的数据库元素        for ( String key : ExtendPostgresDatabase.ignoreIndexes )        {            Index index = snapshot.getIndex( key );            snapshot.getIndexes().remove( index );        }        for ( String key : ExtendPostgresDatabase.ignoreTables )        {            Table table = snapshot.getTable( key );            snapshot.getTables().remove( table );        }        for ( String key : ExtendPostgresDatabase.ignorePrimaryKeys )        {            PrimaryKey primaryKey = snapshot.getPrimaryKey( key );            snapshot.getPrimaryKeys().remove( primaryKey );        }        for ( String key : ExtendPostgresDatabase.ignoreForeignKeys )        {            ForeignKey foreignKey = snapshot.getForeignKey( key );            snapshot.getForeignKeys().remove( foreignKey );        }        return snapshot;    }    @Override    public boolean supports( Database database )    {        return database instanceof ExtendPostgresDatabase;    }    @Override    public int getPriority( Database database )    {        return super.getPriority( database ) + 1;    }}

4.扩展XMLChangeLogSerializer类

import liquibase.change.ColumnConfig;import liquibase.serializer.core.xml.XMLChangeLogSerializer;import org.w3c.dom.Element;public class ExtendXMLChangeLogSerializer    extends XMLChangeLogSerializer{    @Override    public String[] getValidFileExtensions()    {        return new String[] { "ext" };//配合generateChangeLog任务中指定的changeLogFile的后缀名    }    public Element createNode( ColumnConfig columnConfig )    {        //如果列的名字是保留字，需要加上转义符，否则在导入H2时会失败。                Element createNode = super.createNode( columnConfig );        String tagName = createNode.getTagName();        if ( tagName.equalsIgnoreCase( "column" ) )        {            String name = createNode.getAttribute( "name" );            if(ExtendPostgresDatabase.reservedWords.contains(name.toLowerCase()))            {                createNode.setAttribute( "name", """ + name + """ );            }        }        return createNode;    }}

5.扩展H2TypeConverter类

import liquibase.database.Database;import liquibase.database.structure.Column;import liquibase.database.typeconversion.core.H2TypeConverter;public class Postgres2H2TypeConverter    extends H2TypeConverter{    @Override    public int getPriority()    {         return super.getPriority()+1;    }    @Override    public boolean supports( Database database )    {        return database instanceof ExtendPostgresDatabase;    }    @Override    public String convertToDatabaseTypeString( Column referenceColumn, Database database )    {        //将PostgreSQL的类型转换成H2类型        if ( "LONGVARCHAR".equals( referenceColumn.getTypeName().toUpperCase() ) )        {            return "LONGVARCHAR";        }        if ( "FLOAT4".equals( referenceColumn.getTypeName().toUpperCase() ) )        {            return "FLOAT";        }        return super.convertToDatabaseTypeString( referenceColumn, database );    }}

查看更多 下一篇