oracle 建表语句_H2试镜记录扮演ORACLE

在利用H2推广实施单元测试的过程中，遇到和解决了一些问题，在此记录一下。

项目背景

金融行业传统的业务系统，重度使用ORACLE 数据库，包括很多业务逻辑是采用存储过程(SP)实现的。目前正在进行去SP化，采用的是SSM的技术栈。

Oracle数据库->H2数据库

1 数据库删表出错

原因：由于早期DDL 编写不规范，在drop 表时没有加上if exists 的判断drop table if exists users;
导致DDL导入执行时出错。
结果办法：
考虑到与线上DDL一致性，只能采取修改如下配置的方式进行：spring.datasource.continue-on-error=true
在初始化数据库时，遇到错误是否继续，默认false

2 多个DDL/DCL/DDL文件导入

在我们的项目中，数据结构是如下的约定的
02-table/.sql //建表
03-interface/.sql //数据库接口
04-grant/.sql //授权
99-init/.sql //初始化数据
因此，需要按照顺序依次进行导入。

因此，需要修改Spring的配置文件如下：

spring.datasource.schema=classpath:/db/02table/schema*.sql
spring.datasource.data=classpath:/db/99init/data.sql,classpath:/db/99init/data2.sql

笔者采用了以下项目作为demo
https://github.com/xhuanlee/spring-boot-junit-example.git
假设分别有两个*.sql文件在02-table和99init目录，执行结果如下:

2018-07-28 21:09:19.801  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [D:\repo\spring-boot-junit-example-master\target\test-classes\db\02table\schema.sql] in 19 ms.
2018-07-28 21:09:19.801  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from file [D:\repo\spring-boot-junit-example-master\target\test-classes\db\02table\schema2.sql]
2018-07-28 21:09:19.802  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from file [D:\repo\spring-boot-junit-example-master\target\test-classes\db\02table\schema2.sql] in 1 ms.
2018-07-28 21:09:19.804  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [db/99init/data.sql]
2018-07-28 21:09:19.808  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [db/99init/data.sql] in 3 ms.
2018-07-28 21:09:19.808  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from class path resource [db/99init/data2.sql]
2018-07-28 21:09:19.809  INFO 21256 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from class path resource [db/99init/data2.sql] in 1 ms.

DML(data manipulation language)：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language)：
DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL(Data Control Language)：
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant,deny,revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

3 sequence

曾经遇到过一个奇葩的Oracle sequence

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 MAXVALUE   999999999......(这里有28个9)
 NOCACHE
 NOCYCLE;

问题：根据Oracle 官方文档，oracle数据库sequence最大支持28位。
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
而H2数据库的定义中sequence是一个long型，
Long numbers are between -9223372036854775808 and 9223372036854775807.
很明显，这个28个9的sequence是无法导入h2的
解决办法：和开发同学协商是否有必要用28个9。不行的化，退而求其次，在测试环境下用不同的sql。

4 Web Console

在与开发同学沟通的过程中，有开发同学指出，在他们的开发过程中，一般都是通过pl/sql等连接ORACLE数据库进行数据表的相关开发工作，这部分DDL/DML的操作也是在开发的最后才作为版本的内容提交进代码库。
如果改用H2内存数据库进行开发/测试，则希望提供一个console，便于查看数据库结构和数据库中的内容。
H2是提供了web console 以及TCP连接的。

conncct_h2.JPG

h2_webconsole.JPG

如果是Srpingboot的话，需要在application.properties文件中增加两个配置项目

spring.h2.console.enabled=true
spring.h2.console.path=/console
server.port=8888

然后就可以通过http://localhost:8888/console来访问H2的数据库了。
当然笔者并不认为这是一个好的解决方案。由于最终的线上数据库还是Oracle,笔者还是建议开发人员通过PL/SQL连接ORACLE进行数据库相关的开发工作。需要变化的是，像随时提交代码一样，提交数据库的代码到代码库中。也就是将Oracle数据库导出成的DDL/DML的文件，然后再通过H2编写和执行单元测试用例。

多层嵌套临时表

在H2推广的过程中发现，某些开发人员编写了特别复杂的SQL,尤其是重度使用临时表进行嵌套。当嵌套层数超过4层时，H2的不能支持了，虽然H2在其官方文档中表示其支持临时表。
作为临时的解决方案，也只能是把这种类型的用例请出单元测试的范围了。