postgresql - 使用格式化的 SQL + XML 标记数据库

Question

我正在尝试使用 liquibase 并创建了一些示例格式化的 sql 文件和一个包含格式化的 sql 文件的主更新日志 XML 文件。

一切正常，更新和回滚，但我必须提供一个数字，以便 liquibase 知道要回滚多少步。

我尝试添加一个带有 tagDatabase 标签的变更集，但似乎没有用。知道可能是什么问题。

db.changelog_1.0.sql

--liquibase formatted sql
--changeset NIKHIL:changelog_1.0.sql
--comment create Test_1 table

CREATE TABLE TEST_1 (   
    ID VARCHAR (40) NOT NULL,
    ENV VARCHAR (32) NOT NULL,
    TS TIMESTAMPTZ NOT NULL,
    DATAOBJECT VARCHAR (32) NOT NULL,
    CONSTRAINT TEST_1_PK PRIMARY KEY (env,id)
);

--rollback DROP TABLE TEST_1;

db.changelog_1.1.sql

--liquibase formatted sql
--changeset NIKHIL:changelog_1.1.sql
--comment create Test_2 table

CREATE TABLE TEST_2 (   
    ID VARCHAR (40) NOT NULL,
    ENV VARCHAR (32) NOT NULL,
    TS TIMESTAMPTZ NOT NULL,
    DATAOBJECT VARCHAR (32) NOT NULL,
    CONSTRAINT TEST_2_PK PRIMARY KEY (env,id)
);

--rollback drop table TEST_2;

db.changelog.master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd 
            http://www.liquibase.org/xml/ns/dbchangelog 
            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.7.xsd">
        <include relativeToChangelogFile="true" file="db.changelog_1.0.sql"/>
        <include relativeToChangelogFile="true" file="db.changelog_1.1.sql"/>
</databaseChangeLog>

更新命令

liquibase --driver=org.postgresql.Driver --classpath="C:\Development\postgresql-42.2.6.jar" --changeLogFile="C:\Development\DBObjects\Changelog\db.changelog.master.xml" --url="jdbc:postgresql://127.0.0.1:5430/postgres" --username=postgres --password=PWD update

回滚命令

liquibase --driver=org.postgresql.Driver --classpath="C:\Development\postgresql-42.2.6.jar" --changeLogFile="C:\Development\DBObjects\Changelog\db.changelog.master.xml" --url="jdbc:postgresql://127.0.0.1:5430/postgres" --username=postgres --password=PWD rollbackCount 10

db.changelog.master.xml

<?xml version="1.0" encoding="UTF-8"?>
    <databaseChangeLog
            xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
            xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
                http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd 
                http://www.liquibase.org/xml/ns/dbchangelog 
                http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.7.xsd">
            <include relativeToChangelogFile="true" file="db.changelog_1.0.sql"/>
            <include relativeToChangelogFile="true" file="db.changelog_1.1.sql"/>
            <changeSet author="NIKHIL" id="tag_1.0.0">
               <tagDatabase tag="1.0.0" />
            <changeSet>
    </databaseChangeLog>

错误

Starting Liquibase at Do., 15 Aug. 2019 13:57:19 MESZ (version 3.7.0 built at 2019-07-16 02:26:39)
Unexpected error running Liquibase: cvc-complex-type.2.4.a: Invalid content was found starting with element '{"http://www.liquibase.org/xml/ns/dbchangelog":changeSet}'. One of '{"http://www.liquibase.org/xml/ns/dbchangelog":modifySql}' is expected.
liquibase.exception.ChangeLogParseException: Error parsing line 14 column 14 of C:/Development/GitHub/DBObjects/Changelog/db.changelog.master.xml: cvc-complex-type.2.4.a: Invalid content was found starting with element '{"http://www.liquibase.org/xml/ns/dbchangelog":changeSet}'. One of '{"http://www.liquibase.org/xml/ns/dbchangelog":modifySql}' is expected.
        at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChangeLogSAXParser.java:120)
        at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChangeLogParser.java:15)
        at liquibase.Liquibase.getDatabaseChangeLog(Liquibase.java:217)
        at liquibase.Liquibase.update(Liquibase.java:190)
        at liquibase.Liquibase.update(Liquibase.java:179)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1206)
        at liquibase.integration.commandline.Main.run(Main.java:192)
        at liquibase.integration.commandline.Main.main(Main.java:130)
Caused by: org.xml.sax.SAXParseException: cvc-complex-type.2.4.a: Invalid content was found starting with element '{"http://www.liquibase.org/xml/ns/dbchangelog":changeSet}'. One of '{"http://www.liquibase.org/xml/ns/dbchangelog":modifySql}' is expected.
        at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:204)
        at java.xml/com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.error(ErrorHandlerWrapper.java:135)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:396)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:327)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:284)
        at java.xml/com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator$XSIErrorReporter.reportError(XMLSchemaValidator.java:511)
        at java.xml/com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.reportSchemaError(XMLSchemaValidator.java:3587)
        at java.xml/com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.handleStartElement(XMLSchemaValidator.java:1971)
        at java.xml/com.sun.org.apache.xerces.internal.impl.xs.XMLSchemaValidator.startElement(XMLSchemaValidator.java:829)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:374)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2708)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:605)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.next(XMLNSDocumentScannerImpl.java:112)
        at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:534)
        at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:888)
        at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:824)
        at java.xml/com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
        at java.xml/com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1216)
        at java.xml/com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:635)
        at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChangeLogSAXParser.java:112)
        ... 7 common frames omitted

Answer 1

看起来您忘记添加 /关闭你的<changeSet> :

<changeSet author="NIKHIL" id="tag_1.0.0">
    <tagDatabase tag="1.0.0" />
</changeSet>  

我相信，这就是您获得 SAXParseException 的原因.

此外，rollbackCount计算要回滚的 changeSets 的数量。如果指定 rollbackCount 1 ，它将回滚最后执行的 changeSet。如果指定为 10，则将回滚最后 10 个 changeSet。

如果你想回滚到某个标签，那么你可能想要使用 rollback <tag>命令。

看看这个 rollback article在 liquibase 文档中。

rollback <tag> - Rolls back the database to the state it was in when the tag was applied.

rollbackCount <value> - Rolls back the last <value> change sets.