为不同数据库生成脚本的最佳选择 [英] Best choice to generate scripts for different databases

问题描述

我想知道如何从特定模板到不同数据库(例如Oracle，MSSQL，Sybase)生成脚本(DDL，DML).

I am wondering on how to generate scripts (DDL, DML) from an specific template to different databases, like Oracle, MSSQL, Sybase.

Liquibase似乎合适，但是有什么框架或方法可以实现吗?

Liquibase seems to fit, but is there any framework or way to do it?

推荐答案

以下示例为H2数据库生成SQL. XML变更集使liquibase可以生成特定于数据库的SQL.

The following example generates SQL for the H2 database. The XML changeset enables liquibase to generate database specific SQL.

mkdir lib
curl http://search.maven.org/remotecontent?filepath=org/liquibase/liquibase-core/3.0.8/liquibase-core-3.0.8.jar -o lib/liquibase.jar
curl http://search.maven.org/remotecontent?filepath=com/h2database/h2/1.3.174/h2-1.3.174.jar -o lib/h2.jar

这是一个作弊行为.您还可以下载并安装zip软件包.

This is a cheat. You could also download and install the zip package.

此变更日志将创建一个表.以下格式是XML，其他受支持的格式是YAML和SQL.

This changelog creates a single table. The following format is XML, other supported formats are YAML and SQL.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="mark" id="1">
        <createTable tableName="EMPLOYEE">
            <column name="EMPNO" type="INT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="EMP_PK"/>
            </column>
            <column name="NAME" type="VARCHAR(10)"/>
            <column name="JOB" type="VARCHAR(9)"/>
            <column name="BOSS" type="INT"/>
            <column name="HIREDATE" type="VARCHAR(12)"/>
            <column name="SALARY" type="DECIMAL(7,2)"/>
            <column name="COMM" type="DECIMAL(7,2)"/>
            <column name="DEPTNO" type="INT"/>
        </createTable>
    </changeSet>

</databaseChangeLog>

运行

Liquibase可以按以下方式运行以生成SQL

Running

Liquibase can be run as follows to generate the SQL

java -jar ./lib/liquibase.jar \
     --classpath=lib/h2.jar \
     --url=jdbc:h2:db/scottTiger \
     --driver=org.h2.Driver \
     --username=user \
     --password=pass \
     --changeLogFile=sample.xml \
     updateSQL

更新"命令会将SQL应用于数据库.

The "update" command would apply the SQL against the database.

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: sample.xml
-- Ran at: 22/12/13 22:30
-- Against: USER@jdbc:h2:db/scottTiger
-- Liquibase version: 3.0.8
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM PUBLIC.DATABASECHANGELOGLOCK;

INSERT INTO PUBLIC.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

-- Lock Database
-- Create Database Change Log Table
CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20));

-- Changeset sample.xml::1::mark
CREATE TABLE PUBLIC.EMPLOYEE (EMPNO INT NOT NULL, NAME VARCHAR(10), JOB VARCHAR(9), BOSS INT, HIREDATE VARCHAR(12), SALARY DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO INT, CONSTRAINT EMP_PK PRIMARY KEY (EMPNO));

INSERT INTO PUBLIC.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE) VALUES ('1', 'mark', 'sample.xml', NOW(), 1, '7:4700326f252366e9cfe598fded5037c8', 'createTable', '', 'EXECUTED', '3.0.8');

Liquibase创建一个名为"DATABASECHANGELOG"的特殊表来跟踪所有更改.仔细查看，您会发现"EMPLOYEE"表的表创建语句.

Liquibase creates a special table called "DATABASECHANGELOG" to track all changes. Look carefully and you'll find the table create statement for the "EMPLOYEE" table.

此示例适用于H2.支持其他数据库.

This example if for H2. Other databases are supported.

这篇关于为不同数据库生成脚本的最佳选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！