liquibase
- 引自官网
LiquiBase是一个用于数据库重构和迁移的开源工具，通过日志文件的形式记录数据库的变更，然后执行日志文件中的修改，将数据库更新或回滚到一致的状态。
它的目标是提供一种数据库类型无关的解决方案，通过执行schema类型的文件来达到迁移。其有点主要有以下：
- 支持几乎所有主流的数据库，如MySQL, PostgreSQL, Oracle, Sql Server, DB2等；
- 支持多开发者的协作维护；
- 日志文件支持多种格式，如XML, YAML, JSON, SQL等；
- 支持多种运行方式，如命令行、Spring集成、Maven插件、Gradle插件等。
- 在多数据源项目中,sql显然不如yml.xml等自如
- 版本号由开发人员来维护，使用 author + id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| <dependencies>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.6.3</version>
<configuration>
<propertyFileWillOverride>true</propertyFileWillOverride>
<propertyFile>src/main/resources/liquibase/liquibase.properties</propertyFile>
</configuration>
<executions>
<execution>
<phase>process-resources</phase>
<goals>
<goal>update</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
|
不配置默认会查找’classpath:/db/changelog/db.changelog-master.yaml’文件
- 新建xml,作为databaseChangeLog主节点
- 可以直接在里面写changeSet.也可以在外面写好,再导入
- 如果一开始没用，后期想引入 liquibase，可以把以前的数据库导出成 sql，然后引入 sql 文件。方式如下：
1
2
3
4
5
6
7
| <databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<include file="liquibase/reservation_daily.sql" relativeToChangelogFile="false"/>
</databaseChangeLog>
|
- path （在 include 标签里是 file）：指定要加载的文件或文件夹位置
- relativeToChangelogFile ：文件位置的路径是否相对于 root changelog 是相对路径，默认 false，即相对于 classpath 是相对路径。
配置类(这里可选,liquibase配置支持yml.properties,Config类配置,首推配置文件)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
@Configuration
public class LiquibaseConfig {
@Bean
public SpringLiquibase liquibase(DataSource dataSource) {
SpringLiquibase liquibase = new SpringLiquibase();
liquibase.setDataSource(dataSource);
liquibase.setChangeLog("classpath:liquibase/master.xml");
liquibase.setContexts("development,test,production");
liquibase.setShouldRun(true);
return liquibase;
}
}
|
完整示例及说明:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| <databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<changeSet id="table-tag-1" author="小五">
<tagDatabase tag="version_1.1"/>
</changeSet>
<changeSet id="1" author="小五">
<createTable tableName="role" remarks="role表">
<column name="rolename" type="varchar(64)"/>
<column name="password" type="varchar(64)"/>
</createTable>
</changeSet>
<changeSet id="table-tag-2" author="小五">
<tagDatabase tag="version_1.2"/>
</changeSet>
<changeSet id="2" author="小五">
<createTable tableName="user" remarks="user表">
<column name="username" type="varchar(64)"/>
<column name="password" type="varchar(64)"/>
<column name="state" type="int(11)"/>
<column name="identity" type="int(11)"/>
</createTable>
<sql>
INSERT INTO "user"("username","password","state","identity") values ('admin','123',1,999);
</sql>
<rollback>
<delete tableName="user">
<where>
username='admin'
</where>
</delete>
<dropTable tableName="user"/>
</rollback>
</changeSet>
</databaseChangeLog>
|
版本回滚(maven的方式)
回滚指定次数
1
2
| #
mvn liquibase:rollback -Dliquibase.rollbackCount=次数
|
回滚到指定tag
1
| mvn liquibase:rollback -Dliquibase.rollbackTag=tag名称
|
输出回滚语句
1
2
3
| #
mvn liquibase:rollbackSQL -Dliquibase.rollbackCount=次数
mvn liquibase:rollbackSQL -Dliquibase.rollbackTag=tag名称
|
输出变更记录
其他命令
1
2
3
4
5
6
7
8
9
10
11
12
13
| changelogSync : 将changelog中未套用至db的change logs标识成已同步
changelogSyncSQL : 同changelogSync，但只产生sql，而不执行同步到db
generateChangeLog : 将目前数据库的结构(默认不包含数据)生成 xml
diff : 比对两个数据库间的差异
status : 显示目前change set有那些change log会被套用到db
update : 将changeLog.xml中的数据变动changeset脚本转化为sql语句，直接在数据库中执行
updateSQL : 将changeLog.xml中的数据变动changeset脚本转化为sql语句，并输出到对应的文件中
|
一些属性说明:
Change Types
1
2
3
4
| 1. <constraints nullable="false" referencedTableName="security_role" referencedColumnNames="id" deleteCascade="false" foreignKeyName="role_id"/>
约束限制属性,非空,外键参考表,外键参考列,递归删除(数据库的关联删除),外键字段
2. createTable 建表
3. ...看文档即可,有些多
|
1
| liquibase --driver=org.postgresql.Driver --classpath=postgresql-42.2.14.jar --changeLogFile=./dbchangelog.xml --url="jdbc:postgresql://127.0.0.1:5432/xxx" --username=postgres --password=postgres generateChangeLog
|
Liquibase规范&注意事项
1
2
3
4
5
6
7
| ChangeSet id建议使用[版本]-[日期]-[序号]，如 V1-20200101-001；
ChangeSet必须填写author；
已经执行过的ChangeSet严禁修改；
谨慎升级Liquibase版本
项目如需脚本更新,定要设置tag和rollback以便回滚
建议对database以及data分别处理
待补充
|
结合项目发现的弊端
1
| 项目升级导致的数据迁移,用xml,yml等操作没有灵活的标签,这样一来还得用,那支持跨库多数据源的方式又没了,有待研究
|
所有liquibase数据类型的完整列表,以及它们如何转换为不同的数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
| [boolean]
MySQLDatabase: BIT(1)
SQLiteDatabase: BOOLEAN
H2Database: BOOLEAN
PostgresDatabase: BOOLEAN
UnsupportedDatabase: BOOLEAN
DB2Database: SMALLINT
MSSQLDatabase: [bit]
OracleDatabase: NUMBER(1)
HsqlDatabase: BOOLEAN
FirebirdDatabase: SMALLINT
DerbyDatabase: SMALLINT
InformixDatabase: BOOLEAN
SybaseDatabase: BIT
SybaseASADatabase: BIT
[tinyint]
MySQLDatabase: TINYINT
SQLiteDatabase: TINYINT
H2Database: TINYINT
PostgresDatabase: SMALLINT
UnsupportedDatabase: TINYINT
DB2Database: SMALLINT
MSSQLDatabase: [tinyint]
OracleDatabase: NUMBER(3)
HsqlDatabase: TINYINT
FirebirdDatabase: SMALLINT
DerbyDatabase: SMALLINT
InformixDatabase: TINYINT
SybaseDatabase: TINYINT
SybaseASADatabase: TINYINT
[int]
MySQLDatabase: INT
SQLiteDatabase: INTEGER
H2Database: INT
PostgresDatabase: INT
UnsupportedDatabase: INT
DB2Database: INTEGER
MSSQLDatabase: [int]
OracleDatabase: INTEGER
HsqlDatabase: INT
FirebirdDatabase: INT
DerbyDatabase: INTEGER
InformixDatabase: INT
SybaseDatabase: INT
SybaseASADatabase: INT
[mediumint]
MySQLDatabase: MEDIUMINT
SQLiteDatabase: MEDIUMINT
H2Database: MEDIUMINT
PostgresDatabase: MEDIUMINT
UnsupportedDatabase: MEDIUMINT
DB2Database: MEDIUMINT
MSSQLDatabase: [int]
OracleDatabase: MEDIUMINT
HsqlDatabase: MEDIUMINT
FirebirdDatabase: MEDIUMINT
DerbyDatabase: MEDIUMINT
InformixDatabase: MEDIUMINT
SybaseDatabase: MEDIUMINT
SybaseASADatabase: MEDIUMINT
[bigint]
MySQLDatabase: BIGINT
SQLiteDatabase: BIGINT
H2Database: BIGINT
PostgresDatabase: BIGINT
UnsupportedDatabase: BIGINT
DB2Database: BIGINT
MSSQLDatabase: [bigint]
OracleDatabase: NUMBER(38, 0)
HsqlDatabase: BIGINT
FirebirdDatabase: BIGINT
DerbyDatabase: BIGINT
InformixDatabase: INT8
SybaseDatabase: BIGINT
SybaseASADatabase: BIGINT
[float]
MySQLDatabase: FLOAT
SQLiteDatabase: FLOAT
H2Database: FLOAT
PostgresDatabase: FLOAT
UnsupportedDatabase: FLOAT
DB2Database: FLOAT
MSSQLDatabase: [float](53)
OracleDatabase: FLOAT
HsqlDatabase: FLOAT
FirebirdDatabase: FLOAT
DerbyDatabase: FLOAT
InformixDatabase: FLOAT
SybaseDatabase: FLOAT
SybaseASADatabase: FLOAT
[double]
MySQLDatabase: DOUBLE
SQLiteDatabase: DOUBLE
H2Database: DOUBLE
PostgresDatabase: DOUBLE PRECISION
UnsupportedDatabase: DOUBLE
DB2Database: DOUBLE
MSSQLDatabase: [float](53)
OracleDatabase: FLOAT(24)
HsqlDatabase: DOUBLE
FirebirdDatabase: DOUBLE PRECISION
DerbyDatabase: DOUBLE
InformixDatabase: DOUBLE PRECISION
SybaseDatabase: DOUBLE
SybaseASADatabase: DOUBLE
[decimal]
MySQLDatabase: DECIMAL
SQLiteDatabase: DECIMAL
H2Database: DECIMAL
PostgresDatabase: DECIMAL
UnsupportedDatabase: DECIMAL
DB2Database: DECIMAL
MSSQLDatabase: [decimal](18, 0)
OracleDatabase: DECIMAL
HsqlDatabase: DECIMAL
FirebirdDatabase: DECIMAL
DerbyDatabase: DECIMAL
InformixDatabase: DECIMAL
SybaseDatabase: DECIMAL
SybaseASADatabase: DECIMAL
[number]
MySQLDatabase: numeric
SQLiteDatabase: NUMBER
H2Database: NUMBER
PostgresDatabase: numeric
UnsupportedDatabase: NUMBER
DB2Database: numeric
MSSQLDatabase: [numeric](18, 0)
OracleDatabase: NUMBER
HsqlDatabase: numeric
FirebirdDatabase: numeric
DerbyDatabase: numeric
InformixDatabase: numeric
SybaseDatabase: numeric
SybaseASADatabase: numeric
[blob]
MySQLDatabase: LONGBLOB
SQLiteDatabase: BLOB
H2Database: BLOB
PostgresDatabase: BYTEA
UnsupportedDatabase: BLOB
DB2Database: BLOB
MSSQLDatabase: [varbinary](MAX)
OracleDatabase: BLOB
HsqlDatabase: BLOB
FirebirdDatabase: BLOB
DerbyDatabase: BLOB
InformixDatabase: BLOB
SybaseDatabase: IMAGE
SybaseASADatabase: LONG BINARY
[function]
MySQLDatabase: FUNCTION
SQLiteDatabase: FUNCTION
H2Database: FUNCTION
PostgresDatabase: FUNCTION
UnsupportedDatabase: FUNCTION
DB2Database: FUNCTION
MSSQLDatabase: [function]
OracleDatabase: FUNCTION
HsqlDatabase: FUNCTION
FirebirdDatabase: FUNCTION
DerbyDatabase: FUNCTION
InformixDatabase: FUNCTION
SybaseDatabase: FUNCTION
SybaseASADatabase: FUNCTION
[UNKNOWN]
MySQLDatabase: UNKNOWN
SQLiteDatabase: UNKNOWN
H2Database: UNKNOWN
PostgresDatabase: UNKNOWN
UnsupportedDatabase: UNKNOWN
DB2Database: UNKNOWN
MSSQLDatabase: [UNKNOWN]
OracleDatabase: UNKNOWN
HsqlDatabase: UNKNOWN
FirebirdDatabase: UNKNOWN
DerbyDatabase: UNKNOWN
InformixDatabase: UNKNOWN
SybaseDatabase: UNKNOWN
SybaseASADatabase: UNKNOWN
[datetime]
MySQLDatabase: datetime
SQLiteDatabase: TEXT
H2Database: TIMESTAMP
PostgresDatabase: TIMESTAMP WITHOUT TIME ZONE
UnsupportedDatabase: datetime
DB2Database: TIMESTAMP
MSSQLDatabase: [datetime]
OracleDatabase: TIMESTAMP
HsqlDatabase: TIMESTAMP
FirebirdDatabase: TIMESTAMP
DerbyDatabase: TIMESTAMP
InformixDatabase: DATETIME YEAR TO FRACTION(5)
SybaseDatabase: datetime
SybaseASADatabase: datetime
[time]
MySQLDatabase: time
SQLiteDatabase: time
H2Database: time
PostgresDatabase: TIME WITHOUT TIME ZONE
UnsupportedDatabase: time
DB2Database: time
MSSQLDatabase: [time](7)
OracleDatabase: DATE
HsqlDatabase: time
FirebirdDatabase: time
DerbyDatabase: time
InformixDatabase: INTERVAL HOUR TO FRACTION(5)
SybaseDatabase: time
SybaseASADatabase: time
[timestamp]
MySQLDatabase: timestamp
SQLiteDatabase: TEXT
H2Database: TIMESTAMP
PostgresDatabase: TIMESTAMP WITHOUT TIME ZONE
UnsupportedDatabase: timestamp
DB2Database: timestamp
MSSQLDatabase: [datetime]
OracleDatabase: TIMESTAMP
HsqlDatabase: TIMESTAMP
FirebirdDatabase: TIMESTAMP
DerbyDatabase: TIMESTAMP
InformixDatabase: DATETIME YEAR TO FRACTION(5)
SybaseDatabase: datetime
SybaseASADatabase: timestamp
[date]
MySQLDatabase: date
SQLiteDatabase: date
H2Database: date
PostgresDatabase: date
UnsupportedDatabase: date
DB2Database: date
MSSQLDatabase: [date]
OracleDatabase: date
HsqlDatabase: date
FirebirdDatabase: date
DerbyDatabase: date
InformixDatabase: date
SybaseDatabase: date
SybaseASADatabase: date
[char]
MySQLDatabase: CHAR
SQLiteDatabase: CHAR
H2Database: CHAR
PostgresDatabase: CHAR
UnsupportedDatabase: CHAR
DB2Database: CHAR
MSSQLDatabase: [char](1)
OracleDatabase: CHAR
HsqlDatabase: CHAR
FirebirdDatabase: CHAR
DerbyDatabase: CHAR
InformixDatabase: CHAR
SybaseDatabase: CHAR
SybaseASADatabase: CHAR
[varchar]
MySQLDatabase: VARCHAR
SQLiteDatabase: VARCHAR
H2Database: VARCHAR
PostgresDatabase: VARCHAR
UnsupportedDatabase: VARCHAR
DB2Database: VARCHAR
MSSQLDatabase: [varchar](1)
OracleDatabase: VARCHAR2
HsqlDatabase: VARCHAR
FirebirdDatabase: VARCHAR
DerbyDatabase: VARCHAR
InformixDatabase: VARCHAR
SybaseDatabase: VARCHAR
SybaseASADatabase: VARCHAR
[nchar]
MySQLDatabase: NCHAR
SQLiteDatabase: NCHAR
H2Database: NCHAR
PostgresDatabase: NCHAR
UnsupportedDatabase: NCHAR
DB2Database: NCHAR
MSSQLDatabase: [nchar](1)
OracleDatabase: NCHAR
HsqlDatabase: CHAR
FirebirdDatabase: NCHAR
DerbyDatabase: NCHAR
InformixDatabase: NCHAR
SybaseDatabase: NCHAR
SybaseASADatabase: NCHAR
[nvarchar]
MySQLDatabase: NVARCHAR
SQLiteDatabase: NVARCHAR
H2Database: NVARCHAR
PostgresDatabase: VARCHAR
UnsupportedDatabase: NVARCHAR
DB2Database: NVARCHAR
MSSQLDatabase: [nvarchar](1)
OracleDatabase: NVARCHAR2
HsqlDatabase: VARCHAR
FirebirdDatabase: NVARCHAR
DerbyDatabase: VARCHAR
InformixDatabase: NVARCHAR
SybaseDatabase: NVARCHAR
SybaseASADatabase: NVARCHAR
[clob]
MySQLDatabase: LONGTEXT
SQLiteDatabase: TEXT
H2Database: CLOB
PostgresDatabase: TEXT
UnsupportedDatabase: CLOB
DB2Database: CLOB
MSSQLDatabase: [varchar](MAX)
OracleDatabase: CLOB
HsqlDatabase: CLOB
FirebirdDatabase: BLOB SUB_TYPE TEXT
DerbyDatabase: CLOB
InformixDatabase: CLOB
SybaseDatabase: TEXT
SybaseASADatabase: LONG VARCHAR
[currency]
MySQLDatabase: DECIMAL
SQLiteDatabase: REAL
H2Database: DECIMAL
PostgresDatabase: DECIMAL
UnsupportedDatabase: DECIMAL
DB2Database: DECIMAL(19, 4)
MSSQLDatabase: [money]
OracleDatabase: NUMBER(15, 2)
HsqlDatabase: DECIMAL
FirebirdDatabase: DECIMAL(18, 4)
DerbyDatabase: DECIMAL
InformixDatabase: MONEY
SybaseDatabase: MONEY
SybaseASADatabase: MONEY
[uuid]
MySQLDatabase: char(36)
SQLiteDatabase: TEXT
H2Database: UUID
PostgresDatabase: UUID
UnsupportedDatabase: char(36)
DB2Database: char(36)
MSSQLDatabase: [uniqueidentifier]
OracleDatabase: RAW(16)
HsqlDatabase: char(36)
FirebirdDatabase: char(36)
DerbyDatabase: char(36)
InformixDatabase: char(36)
SybaseDatabase: UNIQUEIDENTIFIER
SybaseASADatabase: UNIQUEIDENTIFIER
|
liquibase demo仓库地址
