脚本管理工具-dbDeploy

young 549 2021-10-18

环境搭建

1.基本环境

Java、Maven、数据库

2.初始化控制表

创建CHANGELOG表

create table CHANGELOG
(
  CHANGE_NUMBER NUMBER(22) not null,
  COMPLETE_DT   TIMESTAMP(6) not null,
  APPLIED_BY    VARCHAR2(100) not null,
  DESCRIPTION   VARCHAR2(500) not null
)
/

alter table CHANGELOG add constraint PK_CHANGELOG primary key (CHANGE_NUMBER) 
/

3.sql文件创建脚本(与src平级)

dbdeploy1.png

createNewDDLChangeFile.bat

mvn dbdeploy:change-script -Ddbchangefile.name=DDL

createNewDMLChangeFile.bat

mvn dbdeploy:change-script -Ddbchangefile.name=DML

生成的文件在sql目录中
dbdeploy2.png

4.执行脚本 update.bat (与src平级)

mvn dbdeploy:update -Ddb.url=jdbc:oracle:thin:@ip:port:dis -Ddb.usr=xxx -Ddb.pwd=xxx > dblog.log

5. pom文件

<?xml version="1.0" encoding="GBK"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<parent>
    <groupId>xxx</groupId>
    <artifactId>yyyy</artifactId>
    <version>x.y.z</version>
  </parent>
  <groupId>xxx.db</groupId>
  <artifactId>db</artifactId>

	<properties>
		<db.url>jdbc:oracle:thin:@127.0.0.1:TEST</db.url>
		<db.usr>${env.DBDEPLOY_USR}</db.usr>
		<db.pwd>${env.DBDEPLOY_PWD}</db.pwd>
	</properties>

	<build>
		<plugins>
			<plugin>
				<groupId>com.dbdeploy</groupId>
				<artifactId>maven-dbdeploy-plugin</artifactId>
				<version>3.0M3</version>
				<configuration>
					<scriptdirectory>./src/main/sql</scriptdirectory>
					<name>${dbchangefile.name}</name>
					<encoding>GBK</encoding>
					<lineEnding>lf</lineEnding>
					<outputfile>./target/apply.sql</outputfile>
					<undoOutputfile>./target/undo.sql</undoOutputfile>
					<driver>oracle.jdbc.OracleDriver</driver>
					<url>${db.url}</url>
					<userid>${db.usr}</userid>
					<password>${db.pwd}</password>
					<dbms>ora</dbms>
					<delimiter>/</delimiter>
					<delimiterType>row</delimiterType>
				</configuration>
				<dependencies>
                    <!-- 根据需要进行依赖修改 -->
					<dependency>
					  <groupId>com.oracle</groupId>
					  <artifactId>ojdbc6</artifactId>
					  <version>11.2.0.1.0</version>
					</dependency>
					<dependency>
					  <groupId>com.oracle</groupId>
					  <artifactId>orai18n</artifactId>
					  <version>11.2.0.1.0</version>
					</dependency>
				</dependencies>
				<executions>
					<execution>
						<id>update-db</id>
						<phase>verify</phase>
						<goals>
							<goal>update</goal>
						</goals>
						<configuration>
							<scriptdirectory>${basedir}/target/sql-all</scriptdirectory>
						</configuration>
					</execution>
				</executions>
			</plugin>
			<plugin>
				<artifactId>maven-resources-plugin</artifactId>
				<version>2.5</version>
				<executions>
					<execution>
						<id>copy-sql-to-source-dir</id>
						<phase>process-resources</phase>
						<goals>
							<goal>copy-resources</goal>
						</goals>
						<configuration>
							<outputDirectory>${basedir}/target/sql-all</outputDirectory>
							<resources>
								<resource>
									<directory>src/main/sql</directory>
								</resource>
							</resources>
						</configuration>
					</execution>
				</executions>
			</plugin>
		</plugins>
	</build>

	<profiles>
		<profile>
			<id>script</id>
			<build>
				<plugins>
					<plugin>
						<groupId>com.dbdeploy</groupId>
						<artifactId>maven-dbdeploy-plugin</artifactId>
						<executions>
							<execution>
								<id>create-script-file</id>
								<phase>verify</phase>
								<goals>
									<goal>db-scripts</goal>
								</goals>
								<configuration>
									<scriptdirectory>${basedir}/target/sql-all</scriptdirectory>
								</configuration>
							</execution>
						</executions>
					</plugin>
				</plugins>
			</build>
		</profile>
	</profiles>
</project>

脚本规范

1.创建脚本文件

执行createNewDDLChangeFile.bat创建DDL（数据定义语言，用来维护数据库对象）脚本文件，
在src/main/sql目录下会创建对应的DDL的sql文件，每个DDL语句一个脚本文件。

执行createNewDMLChangeFile.bat创建DML（用于增删改表中数据，DML是伴随事务控制的）脚本文件，
在src/main/sql目录下会创建对应的DML的sql文件

2.编写脚本

用正斜杠/分割DML语句，形如：

insert into fw_permission (PERMISSION_ID, NAME, DESCRIPTION)
values ('function_financeManagement_liqsettle', '扣款明细导出', '')
/
insert into fw_permission_management (NODE_ID, PARENT_NODE_ID, PERMISSION_ID, NODE_NAME, DISPLAY_ORDER, NOTES)
values ('110062', '100004', 'function_financeManagement_liqsettle', '扣款明细导出', 350, '')
/

用正斜杠/分割DDL语句，形如：

create table CO_SEND
(
SEND_ID VARCHAR2(20) not null,
MOBILE VARCHAR2(24)
)
/
comment on column CO_SEND.SEND_ID
is '发送ID'
/
comment on column CO_SEND.MOBILE
is '手机号码'
/

替换;为/的方法：查找正则表达式;\r\n，替换为\r\n/\r\n

3.UNDO语句

UNDO语句编写方法如下：

CREATE TABLE FOO (
FOO_ID INTEGER NOT NULL
,FOO_VALUE VARCHAR(30)
)
/
ALTER TABLE FOO ADD CONSTRAINT PK_FOO PRIMARY KEY (FOO_ID)
/
--//@UNDO
DROP TABLE FOO
/

4.创建方法

创建方法脚本时，语句中的分号保留，在语句结尾的下一行增加/

create or replace function F_DICT(DICTIONARY_ID_IN in VARCHAR2,ITEM_ID_IN in VARCHAR2)
  return VARCHAR2 DETERMINISTIC is ITEM_NAME VARCHAR2(50);
i NUMBER;
BEGIN
  select
    COUNT(1) INTO i
  from CO_DICTIONARY_ITEM t
  where t.DICTIONARY_ID = DICTIONARY_ID_IN
    and t.ITEM_ID = ITEM_ID_IN;
  CASE
    WHEN i = 0
      THEN ITEM_NAME:=ITEM_ID_IN;
    ELSE
      SELECT t.ITEM_NAME INTO ITEM_NAME
      FROM CO_DICTIONARY_ITEM t
      WHERE t.DICTIONARY_ID = DICTIONARY_ID_IN
        AND t.ITEM_ID = ITEM_ID_IN;
    END CASE;
  return(ITEM_NAME);
END F_DICT;
/

执行

修改update.bat中的相关参数为自己需要的数据，执行update.bat，执行之后变会执行src/sql下未执行过的sql，同时改变changelog表，输入执行日志在指定文件中

db