DataGear 變更部署資料庫為SQL Server填坑指南（含轉寫後的SQL server程式碼及SQL server配置檔案）

• 1. 引言
• 2. 配置資料庫連結
• 3. 引入資料庫驅動
• 4. 手動初始化資料庫
• 5. 改寫SQL
• 6. 其他
• 7. 參考

1. 引言

DataGear預設使用Derby資料庫作為系統的後設資料庫，至於待分析的業務庫則支援MySQL、SQL Server等。
Derby資料庫足以支援系統的資料集、圖表、看板等物件的管理，且不需要安裝資料庫服務，但由於目前DataGear不支援單點登入整合，因此專案上為了便於使用者認證資訊的統一管理，以及DataGear資料庫的備份等，將Derby變更為SQL Server，與待分析的業務資料庫一致。
在變更操作過程中，參考了DataGear官方說明，操作時遇到一些坑，記錄下來分享與大家。
不少內容是引用官方文件的，直接抄來了。

gitee地址：
https://gitee.com/datagear/datagear

2. 配置資料庫連結

在config/application.properties新增datasource.driverClassName、datasource.url、datasource.username、datasource.password配置項，配置新資料庫連線。

• MySQL

datasource.driverClassName=com.mysql.cj.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/datagear
datasource.username=datagear
datasource.password=datagear

• SQL Server

datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
datasource.url=jdbc:sqlserver://192.168.1.30:1433;SelectMethod=cursor;DatabaseName=datagear
datasource.username=datagear
datasource.password=datagear

3. 引入資料庫驅動

在根目錄下新建lib包，將JDBC驅動（sqljdbc4-4.0.jar）放入。
修改startup.bat為

java -Xbootclasspath/a:lib/sqljdbc4-4.0.jar -jar datagear-2.4.0.jar

4. 手動初始化資料庫

此時執行startup，datagear將會按照配置的連結訪問資料庫，如果資料庫配置以及驅動無誤，則會進入下一步。
接著datagear檢測到沒有資料庫，此時會自動建立資料庫，使用的SQL是打包在jar檔案中的，原始碼庫路徑為datagear-management/src/main/resources/org/datagear/management/ddl/datagear.sql。此SQL為Derby資料庫，因此將執行失敗。
擴充套件：可以用SQL server等資料庫指令碼替換了原始碼sql並打包，分發到專案上，專案上直接執行即可建立資料庫，類似於Activity等的部署。
接著需要先改寫Derby建庫指令碼為SQL server，改寫後執行SQL，然後設定版本號，然後執行startup.bat即可。
坑：如果是新環境，DATAGEAR_VERSION（版本）表中的記錄為空，此時按照官方說明執行SQL設定版本號將不起效。

UPDATE DATAGEAR_VERSION SET VERSION_VALUE='當前版本號'

或許Derby支援資料不存在則insert的update騷操作，對於SQL server需要insert下。
此坑造成的效果時，啟動時dategear查詢到當前已有資料庫，但版本為null，因此需要升級，且從頭升級，於是執行打包的SQL。

5. 改寫SQL

DataGear的（升級）SQL指令碼非常漂亮，每個版本不論是否有SQL更新，都寫有註釋，供手工和自動執行時參考。

-----------------------------------------
--version[1.6.1], DO NOT EDIT THIS LINE!
-----------------------------------------

如果是新環境，則需要從頭執行到尾，如果是升級，則找到起始版本執行後邊的SQL即可。

以下列舉Derby轉SQL server的幾個常見操作。

• TIMESTAMP，巨坑，SQL server也有該資料型別，但意義不一樣，改為datetime
• CURRENT_TIMESTAMP，當前時間，修改為(getdate())，獲取當前時間
• 函式按照SQLserver語法改寫，變數填寫@即可。函式實現Derby不支援的replace、取餘操作，datagear採用了java擴充套件，SQL server直接用系統函式、操作符即可，不需要.net擴充套件
• DATAGEAR_REPLACEREGEX，按官方說明，不必理睬即可，該正規表示式替換新版本中已廢棄
• RENAME COLUMN ，重新命名列，按照SQL server語法替換
• RENAME TABLE ，重新命名錶，按照SQL server語法替換
• 表中加欄位，ADD COLUMN 去掉column
• ALTER COLUMN DS_TYPE SET NOT NULL;，修改列非空，SQL server需要找到原來的型別，一併修改。
• VARCHAR(10000)，SQL server支援到8K，修改為VARCHAR(8000)

修改後的SQL Server版SQL為：

-----------------------------------------
--version[1.0.0], DO NOT EDIT THIS LINE!
-----------------------------------------

if not exists (select * from sysobjects where name='DATAGEAR_VERSION' and xtype='U')
CREATE TABLE DATAGEAR_VERSION 
(
	VERSION_MAJOR VARCHAR(50),
	VERSION_MINOR VARCHAR(50),
	VERSION_REVISION VARCHAR(50),
	VERSION_BUILD VARCHAR(50)
);
go
if not exists (select * from sysobjects where name='DATAGEAR_USER' and xtype='U')
CREATE TABLE DATAGEAR_USER
(
	USER_ID VARCHAR(50) NOT NULL,
	USER_NAME VARCHAR(50) NOT NULL,
	USER_PASSWORD VARCHAR(200) NOT NULL,
	USER_REAL_NAME VARCHAR(100),
	USER_EMAIL VARCHAR(200),
	USER_IS_ADMIN VARCHAR(20),
	USER_CREATE_TIME DATETIME,
	PRIMARY KEY (USER_ID),
	UNIQUE (USER_NAME)
);
go
--alter table DATAGEAR_USER alter column USER_CREATE_TIME datetime;
--無法更改列 'USER_CREATE_TIME'，因為它是 'timestamp'。
--alter table DATAGEAR_USER drop column USER_CREATE_TIME;
--alter table DATAGEAR_USER add USER_CREATE_TIME DATETIME;


--the password is 'admin'
INSERT INTO DATAGEAR_USER VALUES('admin', 'admin', '4c6d8d058a4db956660f0ee51fcb515f93471a086fc676bfb71ba2ceece5bf4702c61cefab3fa54b', '', '', 'true', default);

if not exists (select * from sysobjects where name='DATAGEAR_SCHEMA' and xtype='U')
CREATE TABLE DATAGEAR_SCHEMA
(
	SCHEMA_ID VARCHAR(50) NOT NULL,
	SCHEMA_TITLE VARCHAR(100) NOT NULL,
	SCHEMA_URL VARCHAR(200) NOT NULL,
	SCHEMA_USER VARCHAR(200),
	SCHEMA_PASSWORD VARCHAR(200),
	SCHEMA_CREATE_USER_ID VARCHAR(50),
	SCHEMA_CREATE_TIME DATETIME,
	SCHEMA_SHARED VARCHAR(20),
	DRIVER_ENTITY_ID VARCHAR(100),
	PRIMARY KEY (SCHEMA_ID)
);
--alter table DATAGEAR_SCHEMA drop column SCHEMA_CREATE_TIME;
--alter table DATAGEAR_SCHEMA add SCHEMA_CREATE_TIME DATETIME;

-----------------------------------------
--version[1.1.0], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[1.1.1], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[1.2.0], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[1.3.0], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[1.4.0], DO NOT EDIT THIS LINE!
-----------------------------------------

if not exists (select * from sysobjects where name='DATAGEAR_ROLE' and xtype='U')
CREATE TABLE DATAGEAR_ROLE
(
	ROLE_ID VARCHAR(50) NOT NULL,
	ROLE_NAME VARCHAR(100) NOT NULL,
	ROLE_DESCRIPTION VARCHAR(200),
	ROLE_ENABLED VARCHAR(10) NOT NULL,
	ROLE_CREATE_TIME DATETIME,
	PRIMARY KEY (ROLE_ID)
);
--alter table DATAGEAR_ROLE drop column ROLE_CREATE_TIME;
--alter table DATAGEAR_ROLE add ROLE_CREATE_TIME DATETIME;

if not exists (select * from sysobjects where name='DATAGEAR_ROLE_USER' and xtype='U')
CREATE TABLE DATAGEAR_ROLE_USER
(
	RU_ID VARCHAR(50) NOT NULL,
	RU_ROLE_ID VARCHAR(50) NOT NULL,
	RU_USER_ID VARCHAR(50) NOT NULL,
	PRIMARY KEY (RU_ID)
);

ALTER TABLE DATAGEAR_ROLE_USER ADD FOREIGN KEY (RU_ROLE_ID) REFERENCES DATAGEAR_ROLE (ROLE_ID) ON DELETE CASCADE;

ALTER TABLE DATAGEAR_ROLE_USER ADD FOREIGN KEY (RU_USER_ID) REFERENCES DATAGEAR_USER (USER_ID) ON DELETE CASCADE;

ALTER TABLE DATAGEAR_ROLE_USER ADD CONSTRAINT UK_RU_ROLE_USER_ID UNIQUE (RU_ROLE_ID, RU_USER_ID);

if not exists (select * from sysobjects where name='DATAGEAR_AUTHORIZATION' and xtype='U')
CREATE TABLE DATAGEAR_AUTHORIZATION
(
	AUTH_ID VARCHAR(50) NOT NULL,
	AUTH_RESOURCE VARCHAR(200) NOT NULL,
	AUTH_RESOURCE_TYPE VARCHAR(50) NOT NULL,
	AUTH_PRINCIPAL VARCHAR(200) NOT NULL,
	AUTH_PRINCIPAL_TYPE VARCHAR(50) NOT NULL,
	AUTH_PERMISSION SMALLINT NOT NULL,
	AUTH_ENABLED VARCHAR(10) NOT NULL,
	AUTH_CREATE_TIME DATETIME,
	AUTH_CREATE_USER_ID VARCHAR(50),
	PRIMARY KEY (AUTH_ID)
);
--alter table DATAGEAR_AUTHORIZATION drop column AUTH_CREATE_TIME;
--alter table DATAGEAR_AUTHORIZATION add AUTH_CREATE_TIME DATETIME;
go
--自定義REPLACE函式
--srcStr    原始字串
--oldStr    要替換的舊子串
--newStr    要替換的新子串
CREATE FUNCTION DATAGEAR_REPLACE(@srcStr VARCHAR(500), @oldStr VARCHAR(100), @newStr VARCHAR(100)) RETURNS VARCHAR(500)
as
begin
--PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.datagear.management.util.DerbyFunctionSupport.replace';
	return replace(@srcStr,@oldStr,@newStr);
end;
go

if not exists (select * from sysobjects where name='DATAGEAR_SQL_HISTORY' and xtype='U')
CREATE TABLE DATAGEAR_SQL_HISTORY
(
	SQLHIS_ID VARCHAR(50) NOT NULL,
	SQLHIS_SQL VARCHAR(5000) NOT NULL,
	SQLHIS_SCHEMA_ID VARCHAR(50) NOT NULL,
	SQLHIS_USER_ID VARCHAR(50) NOT NULL,
	SQLHIS_CREATE_TIME DATETIME  DEFAULT GETDATE(),
	PRIMARY KEY (SQLHIS_ID)
);
--alter table DATAGEAR_SQL_HISTORY drop column SQLHIS_CREATE_TIME;
--alter table DATAGEAR_SQL_HISTORY add SQLHIS_CREATE_TIME DATETIME;

ALTER TABLE DATAGEAR_SQL_HISTORY ADD FOREIGN KEY (SQLHIS_SCHEMA_ID) REFERENCES DATAGEAR_SCHEMA (SCHEMA_ID) ON DELETE CASCADE;


-----------------------------------------
--version[1.5.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--SQL資料集
if not exists (select * from sysobjects where name='DATAGEAR_SQL_DATA_SET' and xtype='U')
CREATE TABLE DATAGEAR_SQL_DATA_SET
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_NAME VARCHAR(100) NOT NULL,
	DS_SCHEMA_ID VARCHAR(50) NOT NULL,
	DS_SQL VARCHAR(1000) NOT NULL,
	DS_CREATE_USER_ID VARCHAR(50),
	DS_CREATE_TIME DATETIME,
	PRIMARY KEY (DS_ID)
);
-- alter table DATAGEAR_DATA_SET drop column DS_CREATE_TIME;
-- alter table DATAGEAR_DATA_SET add DS_CREATE_TIME DATETIME;

--該欄位後需要刪除，約束將限制其刪除
--ALTER TABLE DATAGEAR_SQL_DATA_SET ADD FOREIGN KEY (DS_SCHEMA_ID) REFERENCES DATAGEAR_SCHEMA (SCHEMA_ID);

--資料集屬性
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_PROP' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_PROP
(
	PROP_DS_ID VARCHAR(50) NOT NULL,
	PROP_NAME VARCHAR(100) NOT NULL,
	PROP_TYPE VARCHAR(50) NOT NULL,
	PROP_LABEL VARCHAR(100),
	PROP_ORDER INTEGER
);

ALTER TABLE DATAGEAR_DATA_SET_PROP ADD FOREIGN KEY (PROP_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;

ALTER TABLE DATAGEAR_DATA_SET_PROP ADD CONSTRAINT UK_DS_PROP_DS_ID_NAME UNIQUE (PROP_DS_ID, PROP_NAME);

--資料集引數
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_PAR' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_PAR
(
	PAR_DS_ID VARCHAR(50) NOT NULL,
	PAR_NAME VARCHAR(100) NOT NULL,
	PAR_TYPE VARCHAR(100) NOT NULL,
	PAR_REQUIRED VARCHAR(10),
	PAR_DEFAULT_VALUE VARCHAR(200),
	PAR_ORDER INTEGER
);

ALTER TABLE DATAGEAR_DATA_SET_PAR ADD FOREIGN KEY (PAR_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE; 

ALTER TABLE DATAGEAR_DATA_SET_PAR ADD CONSTRAINT UK_DS_PAR_DS_ID_NAME UNIQUE (PAR_DS_ID, PAR_NAME);

--資料集輸出
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_EXPT' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_EXPT
(
	EXPT_DS_ID VARCHAR(50) NOT NULL,
	EXPT_NAME VARCHAR(100) NOT NULL,
	EXPT_TYPE VARCHAR(50) NOT NULL,
	EXPT_ORDER INTEGER
);

ALTER TABLE DATAGEAR_DATA_SET_EXPT ADD FOREIGN KEY (EXPT_DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;

ALTER TABLE DATAGEAR_DATA_SET_EXPT ADD CONSTRAINT UK_DS_EXPT_DS_ID_NAME UNIQUE (EXPT_DS_ID, EXPT_NAME);

--圖表
if not exists (select * from sysobjects where name='DATAGEAR_HTML_CHART_WIDGET' and xtype='U')
CREATE TABLE DATAGEAR_HTML_CHART_WIDGET
(
	HCW_ID VARCHAR(50) NOT NULL,
	HCW_NAME VARCHAR(100) NOT NULL,
	HCW_PLUGIN_ID VARCHAR(100) NOT NULL,
	HCW_UPDATE_INTERVAL INTEGER,
	HCW_CREATE_USER_ID VARCHAR(50),
	HCW_CREATE_TIME DATETIME,
	PRIMARY KEY (HCW_ID)
);
-- alter table DATAGEAR_HTML_CHART_WIDGET drop column HCW_CREATE_TIME;
-- alter table DATAGEAR_HTML_CHART_WIDGET add HCW_CREATE_TIME DATETIME;

--圖表-資料集資訊
if not exists (select * from sysobjects where name='DATAGEAR_HCW_DS' and xtype='U')
CREATE TABLE DATAGEAR_HCW_DS
(
	HCW_ID VARCHAR(50) NOT NULL,
	DS_ID VARCHAR(50) NOT NULL,
	DS_PROPERTY_SIGNS VARCHAR(500),
	DS_ORDER INTEGER
);

ALTER TABLE DATAGEAR_HCW_DS ADD FOREIGN KEY (HCW_ID) REFERENCES DATAGEAR_HTML_CHART_WIDGET (HCW_ID) ON DELETE CASCADE;

ALTER TABLE DATAGEAR_HCW_DS ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_SQL_DATA_SET (DS_ID) ON DELETE CASCADE;

--看板
if not exists (select * from sysobjects where name='DATAGEAR_HTML_DASHBOARD' and xtype='U')
CREATE TABLE DATAGEAR_HTML_DASHBOARD
(
	HD_ID VARCHAR(50) NOT NULL,
	HD_NAME VARCHAR(100) NOT NULL,
	HD_TEMPLATE VARCHAR(100) NOT NULL,
	HD_TEMPLATE_ENCODING VARCHAR(50),
	HD_CREATE_USER_ID VARCHAR(50),
	HD_CREATE_TIME DATETIME,
	PRIMARY KEY (HD_ID)
);
-- alter table DATAGEAR_HTML_DASHBOARD drop column HD_CREATE_TIME;
-- alter table DATAGEAR_HTML_DASHBOARD add HD_CREATE_TIME DATETIME;

-----------------------------------------
--version[1.6.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--ALTER TABLE DATAGEAR_HTML_DASHBOARD ALTER COLUMN HD_TEMPLATE SET DATA TYPE VARCHAR(500);

-----------------------------------------
--version[1.6.1], DO NOT EDIT THIS LINE!
-----------------------------------------

-----------------------------------------
--version[1.7.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--自定義正則REPLACE函式，目前僅用於下面修改資料標記
--srcStr  原始字串
--oldStr  要替換的正規表示式
--newStr  要替換的新子串
--CREATE FUNCTION DATAGEAR_REPLACEREGEX(srcStr VARCHAR(500), oldStr VARCHAR(100), newStr VARCHAR(100)) RETURNS VARCHAR(500)
--PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.datagear.management.util.DerbyFunctionSupport.replaceRegex';
 
--擴充SQL欄位
ALTER TABLE DATAGEAR_SQL_DATA_SET ALTER COLUMN DS_SQL VARCHAR(8000);


-----------------------------------------
--version[1.8.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--預設值欄位改為描述欄位
--RENAME COLUMN DATAGEAR_DATA_SET_PAR.PAR_DEFAULT_VALUE TO PAR_DESC;
exec sp_rename 'DATAGEAR_DATA_SET_PAR.PAR_DEFAULT_VALUE','PAR_DESC' 

--移除資料集輸出項表
DROP TABLE DATAGEAR_DATA_SET_EXPT;

--擴充圖表資料集資料標記列
ALTER TABLE DATAGEAR_HCW_DS ALTER COLUMN DS_PROPERTY_SIGNS VARCHAR(1000);

--新增圖表資料集別名列
ALTER TABLE DATAGEAR_HCW_DS ADD DS_ALIAS VARCHAR(100);

--新增圖表資料集引數值列
ALTER TABLE DATAGEAR_HCW_DS ADD DS_PARAM_VALUES VARCHAR(1000);


-----------------------------------------
--version[1.8.1], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[1.9.0], DO NOT EDIT THIS LINE!
-----------------------------------------
 
-----------------------------------------
--version[1.10.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--新增資料集引數輸入框型別
ALTER TABLE DATAGEAR_DATA_SET_PAR ADD PAR_INPUT_TYPE VARCHAR(50);

--新增資料集引數輸入框載荷
ALTER TABLE DATAGEAR_DATA_SET_PAR ADD PAR_INPUT_PAYLOAD VARCHAR(1000);


-----------------------------------------
--version[1.10.1], DO NOT EDIT THIS LINE!
-----------------------------------------

-----------------------------------------
--version[1.11.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--改為通用資料集
--RENAME TABLE DATAGEAR_SQL_DATA_SET TO DATAGEAR_DATA_SET;
exec sp_rename 'DATAGEAR_SQL_DATA_SET','DATAGEAR_DATA_SET';

--新建SQL資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_SQL' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_SQL
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_SCHEMA_ID VARCHAR(50) NOT NULL,
	DS_SQL VARCHAR(8000) NOT NULL,
	PRIMARY KEY (DS_ID)
);

--遷移SQL資料集資料
INSERT INTO DATAGEAR_DATA_SET_SQL (DS_ID, DS_SCHEMA_ID, DS_SQL) SELECT DS_ID, DS_SCHEMA_ID, DS_SQL FROM DATAGEAR_DATA_SET;

--新增約束
ALTER TABLE DATAGEAR_DATA_SET_SQL ADD FOREIGN KEY (DS_SCHEMA_ID) REFERENCES DATAGEAR_SCHEMA (SCHEMA_ID);

--引入表 'DATAGEAR_DATA_SET_SQL' 可能會導致迴圈或多重級聯路徑。請指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 約束
ALTER TABLE DATAGEAR_DATA_SET_SQL ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;
--ALTER TABLE DATAGEAR_DATA_SET_SQL ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE NO ACTION;

--移除通用資料集中的SQL資料集列
--需要先移除外來鍵約束，然後刪除列，Derby可能直接可以刪除
--ALTER TABLE DATAGEAR_DATA_SET DROP CONSTRAINT [FK__DATAGEAR___DS_SC__04459E07];
ALTER TABLE DATAGEAR_DATA_SET DROP COLUMN DS_SCHEMA_ID;

ALTER TABLE DATAGEAR_DATA_SET DROP COLUMN DS_SQL;

--為通用資料集新增型別欄位，並全部初始化為'SQL'
ALTER TABLE DATAGEAR_DATA_SET ADD DS_TYPE VARCHAR(50);

UPDATE DATAGEAR_DATA_SET SET DS_TYPE = 'SQL';

ALTER TABLE DATAGEAR_DATA_SET ALTER column DS_TYPE VARCHAR(50) NOT NULL;

--2020-08-10
--JSON值資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_JSON_VALUE' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_JSON_VALUE
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_VALUE VARCHAR(8000) NOT NULL,
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_JSON_VALUE ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;

--2020-08-12
--JSON檔案資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_JSON_FILE' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_JSON_FILE
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_FILE_NAME VARCHAR(100) NOT NULL,
	DS_DISPLAY_NAME VARCHAR(100) NOT NULL,
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;


-----------------------------------------
--version[1.11.1], DO NOT EDIT THIS LINE!
-----------------------------------------


-----------------------------------------
--version[1.12.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--2020-08-28
--JSON檔案資料集新增編碼欄位
ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD DS_FILE_ENCODING VARCHAR(50);

--2020-08-28
--Excel檔案資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_EXCEL' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_EXCEL
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_FILE_NAME VARCHAR(100) NOT NULL,
	DS_DISPLAY_NAME VARCHAR(100) NOT NULL,
	DS_SHEET_INDEX INTEGER,
	DS_NAME_ROW INTEGER,
	DS_DATA_ROW_EXP VARCHAR(100),
	DS_DATA_COLUMN_EXP VARCHAR(100),
	DS_FORCE_XLS VARCHAR(10),
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;

--2020-08-31
--CSV值資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_CSV_VALUE' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_CSV_VALUE
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_VALUE VARCHAR(8000) NOT NULL,
	DS_NAME_ROW INTEGER,
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_CSV_VALUE ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;

--2020-08-31
--CSV檔案資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_CSV_FILE' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_CSV_FILE
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_FILE_NAME VARCHAR(100) NOT NULL,
	DS_DISPLAY_NAME VARCHAR(100) NOT NULL,
	DS_FILE_ENCODING VARCHAR(50),
	DS_NAME_ROW INTEGER,
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;

--2020-09-03
--JSON檔案資料集新增資料JSON路徑欄位
ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD DS_DATA_JSON_PATH VARCHAR(200);

--2020-09-05
--HTTP資料集
if not exists (select * from sysobjects where name='DATAGEAR_DATA_SET_HTTP' and xtype='U')
CREATE TABLE DATAGEAR_DATA_SET_HTTP
(
	DS_ID VARCHAR(50) NOT NULL,
	DS_URI VARCHAR(1000) NOT NULL,
	DS_HEADER_CONTENT VARCHAR(5000),
	DS_RQT_METHOD VARCHAR(50),
	DS_RQT_CONTENT_TYPE VARCHAR(100),
	DS_RQT_CONTENT_CHARSET VARCHAR(100),
	DS_RQT_CONTENT varchar(8000),
	DS_RPS_CONTENT_TYPE VARCHAR(100),
	DS_RPS_DATA_JSON_PATH VARCHAR(200),
	PRIMARY KEY (DS_ID)
);

ALTER TABLE DATAGEAR_DATA_SET_HTTP ADD FOREIGN KEY (DS_ID) REFERENCES DATAGEAR_DATA_SET (DS_ID) ON DELETE CASCADE;


-----------------------------------------
--version[1.13.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--2020-09-22
--資料分析專案
if not exists (select * from sysobjects where name='DATAGEAR_ANALYSIS_PROJECT' and xtype='U')
CREATE TABLE DATAGEAR_ANALYSIS_PROJECT
(
	AP_ID VARCHAR(50) NOT NULL,
	AP_NAME VARCHAR(100) NOT NULL,
	AP_DESC VARCHAR(500),
	AP_CREATE_USER_ID VARCHAR(50),
	AP_CREATE_TIME DATETIME,
	PRIMARY KEY (AP_ID)
);
-- alter table DATAGEAR_ANALYSIS_PROJECT drop column AP_CREATE_TIME;
-- alter table DATAGEAR_ANALYSIS_PROJECT add AP_CREATE_TIME DATETIME;

ALTER TABLE DATAGEAR_DATA_SET ADD  DS_AP_ID VARCHAR(50);

ALTER TABLE DATAGEAR_HTML_CHART_WIDGET ADD  HCW_AP_ID VARCHAR(50);

ALTER TABLE DATAGEAR_HTML_DASHBOARD ADD  HD_AP_ID VARCHAR(50);

--2020-09-25
--新增外來鍵
ALTER TABLE DATAGEAR_DATA_SET ADD FOREIGN KEY (DS_AP_ID) REFERENCES DATAGEAR_ANALYSIS_PROJECT (AP_ID);

ALTER TABLE DATAGEAR_HTML_CHART_WIDGET ADD FOREIGN KEY (HCW_AP_ID) REFERENCES DATAGEAR_ANALYSIS_PROJECT (AP_ID);

ALTER TABLE DATAGEAR_HTML_DASHBOARD ADD FOREIGN KEY (HD_AP_ID) REFERENCES DATAGEAR_ANALYSIS_PROJECT (AP_ID);

--2020-09-25
--新增索引
CREATE INDEX DATAGEAR_DATA_SET_CREATE_USER_ID ON DATAGEAR_DATA_SET(DS_CREATE_USER_ID);

CREATE INDEX DATAGEAR_HTML_CHART_WIDGET_CREATE_USER_ID ON DATAGEAR_HTML_CHART_WIDGET(HCW_CREATE_USER_ID);

CREATE INDEX DATAGEAR_HTML_DASHBOARD_CREATE_USER_ID ON DATAGEAR_HTML_DASHBOARD(HD_CREATE_USER_ID);

--2020-09-26
--資料集資源目錄
if not exists (select * from sysobjects where name='DATAGEAR_DSR_DIRECTORY' and xtype='U')
CREATE TABLE DATAGEAR_DSR_DIRECTORY
(
	DD_ID VARCHAR(50) NOT NULL,
	DD_DIRECTORY VARCHAR(250) NOT NULL,
	DD_DESC VARCHAR(500),
	DD_CREATE_USER_ID VARCHAR(50),
	DD_CREATE_TIME DATETIME,
	PRIMARY KEY (DD_ID)
);
-- alter table DATAGEAR_DSR_DIRECTORY drop column DD_CREATE_TIME;
-- alter table DATAGEAR_DSR_DIRECTORY add DD_CREATE_TIME DATETIME;

--2020-09-26
--為檔案類資料集新增服務端檔案相關欄位

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD  DS_FILE_SOURCE_TYPE VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD  DS_DSRD_ID VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD  DS_DSRD_FILE_NAME VARCHAR(500);

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ADD FOREIGN KEY (DS_DSRD_ID) REFERENCES DATAGEAR_DSR_DIRECTORY (DD_ID);

--舊資料都應改為UPLOAD型別
UPDATE DATAGEAR_DATA_SET_JSON_FILE SET DS_FILE_SOURCE_TYPE = 'UPLOAD';

ALTER TABLE DATAGEAR_DATA_SET_JSON_FILE ALTER COLUMN DS_FILE_SOURCE_TYPE VARCHAR(50) NOT NULL;

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ADD  DS_FILE_SOURCE_TYPE VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ADD  DS_DSRD_ID VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ADD  DS_DSRD_FILE_NAME VARCHAR(500);

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ADD FOREIGN KEY (DS_DSRD_ID) REFERENCES DATAGEAR_DSR_DIRECTORY (DD_ID);

--舊資料都應改為UPLOAD型別
UPDATE DATAGEAR_DATA_SET_EXCEL SET DS_FILE_SOURCE_TYPE = 'UPLOAD';

ALTER TABLE DATAGEAR_DATA_SET_EXCEL ALTER column DS_FILE_SOURCE_TYPE VARCHAR(50) NOT NULL;

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ADD  DS_FILE_SOURCE_TYPE VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ADD  DS_DSRD_ID VARCHAR(50);

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ADD  DS_DSRD_FILE_NAME VARCHAR(500);

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ADD FOREIGN KEY (DS_DSRD_ID) REFERENCES DATAGEAR_DSR_DIRECTORY (DD_ID);

--舊資料都應改為UPLOAD型別
UPDATE DATAGEAR_DATA_SET_CSV_FILE SET DS_FILE_SOURCE_TYPE = 'UPLOAD';

ALTER TABLE DATAGEAR_DATA_SET_CSV_FILE ALTER COLUMN DS_FILE_SOURCE_TYPE VARCHAR(50) NOT NULL;


-----------------------------------------
--version[1.13.1], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[2.0.0], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[2.1.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--2020-12-04
--擴容列長度
ALTER TABLE DATAGEAR_SCHEMA ALTER column SCHEMA_URL  VARCHAR(1000);

--2020-12-09
--新增內建角色
INSERT INTO DATAGEAR_ROLE VALUES('ROLE_REGISTRY', '註冊使用者', '系統新新增和註冊的使用者都會自動新增至此角色', 'true', default);

INSERT INTO DATAGEAR_ROLE VALUES('ROLE_DATA_ADMIN', '資料管理員', '可以管理資料來源、資料集、圖表、看板', 'true', default);

INSERT INTO DATAGEAR_ROLE VALUES('ROLE_DATA_ANALYST', '資料分析員', '僅可檢視資料來源、資料集、圖表、看板，展示圖表和看板', 'true', default);

--2020-12-10
--將全部已註冊使用者關聯至[註冊使用者]角色
--此語句可多次執行，因為已排除了可能的重複項
INSERT INTO DATAGEAR_ROLE_USER
SELECT
	'RUREG' + USR.USER_ID,
	'ROLE_REGISTRY',
	USR.USER_ID
FROM DATAGEAR_USER USR
WHERE
	USR.USER_ID NOT IN (SELECT RU_USER_ID FROM DATAGEAR_ROLE_USER WHERE RU_ROLE_ID = 'ROLE_REGISTRY');

--將全部已註冊使用者關聯至[資料管理員]角色，這樣才符合之前版本的設計概念
--此語句可多次執行，因為已排除了可能的重複項
INSERT INTO DATAGEAR_ROLE_USER
SELECT
	'RUDA' + USR.USER_ID,
	'ROLE_DATA_ADMIN',
	USR.USER_ID
FROM DATAGEAR_USER USR
WHERE
	USR.USER_ID NOT IN (SELECT RU_USER_ID FROM DATAGEAR_ROLE_USER WHERE RU_ROLE_ID = 'ROLE_DATA_ADMIN');


-----------------------------------------
--version[2.1.1], DO NOT EDIT THIS LINE!
-----------------------------------------



-----------------------------------------
--version[2.2.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--2021-01-17
--簡化DATAGEAR_VERSION表結構
ALTER TABLE DATAGEAR_VERSION ADD VERSION_VALUE VARCHAR(100);
go
UPDATE DATAGEAR_VERSION SET VERSION_VALUE = (SELECT VERSION_MAJOR + '.' + VERSION_MINOR + '.' + VERSION_REVISION FROM DATAGEAR_VERSION);
go
ALTER TABLE DATAGEAR_VERSION DROP COLUMN VERSION_MAJOR;
go
ALTER TABLE DATAGEAR_VERSION DROP COLUMN VERSION_MINOR;
go
ALTER TABLE DATAGEAR_VERSION DROP COLUMN VERSION_REVISION;
go
ALTER TABLE DATAGEAR_VERSION DROP COLUMN VERSION_BUILD;
go

-----------------------------------------
--version[2.3.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--新增圖表資料集附件列
ALTER TABLE DATAGEAR_HCW_DS ADD DS_ATTACHMENT VARCHAR(20);
go
--自定義整數取餘數函式
--valueNum    數值
--divNum      除數
CREATE FUNCTION DATAGEAR_FUNC_MODINT(@valueNum INTEGER, @divNum INTEGER) RETURNS INTEGER
as
begin
	--PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.datagear.management.util.DerbyFunctionSupport.modInt';
	return @valueNum % @divNum;
end;
go
--自定義字串取長度函式
--valueStr    字串
CREATE FUNCTION DATAGEAR_FUNC_LENGTH(@valueStr VARCHAR(1000)) RETURNS INTEGER
as
begin
--	PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.datagear.management.util.DerbyFunctionSupport.lengthStr';
	return len(@valueStr);
end;
go

-----------------------------------------
--version[2.4.0], DO NOT EDIT THIS LINE!
-----------------------------------------

--新增資料集屬性預設值列
ALTER TABLE DATAGEAR_DATA_SET_PROP ADD PROP_DFT_VALUE VARCHAR(100);


UPDATE DATAGEAR_VERSION SET VERSION_VALUE='2.4.0';
insert into DATAGEAR_VERSION(VERSION_VALUE)values('2.4.0');


--select 'DROP TABLE '+name + ';' from sysobjects where name like'DATAGEAR%' and xtype='U' 
--select * from DATAGEAR_VERSION;

--select 'ALTER TABLE ' + b.name + ' NOCHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name like 'DATAGEAR%'; 
--select 'ALTER TABLE ' + b.name + ' CHECK CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name like 'DATAGEAR%'; 
--select 'ALTER TABLE ' + b.name + ' DROP CONSTRAINT ' + a.name +';'  from sysobjects a ,sysobjects b where a.xtype ='f' and a.parent_obj = b.id and b.name like 'DATAGEAR%'; 

--drop function dbo.DATAGEAR_FUNC_LENGTH;
--drop function dbo.DATAGEAR_FUNC_MODINT;
--drop function dbo.DATAGEAR_REPLACE;

6. 其他

Datagear的資料庫命名規範過多次，不少表、欄位名稱、型別等做了修改，一些欄位刪除了，轉換時由於兩個drop column沒有執行，且該列有非空約束，造成新增時失敗。
但datagear提示“操作失敗，記錄已被引用，無法刪除或修改標識（違反資料庫完整性約束）”。把外來鍵約束禁用，後又刪除都無濟於事。最終下載原始碼把環境跑起來才發現是欄位非空驗證不通過。

Datagear原始碼中並未有SQL server的包，因此在pom中配置SQLserver的dependency後執行。

7. 參考

DataGear 變更部署資料庫操作指南

8. 附：SQL server 配置

\config\application.properties

#--UTF-8 file--

#是否禁用匿名使用者功能，禁用後，匿名使用者將不能使用系統功能
#可選值：true 表示禁用；false 表示不禁用
disableAnonymous=false

#是否禁用註冊功能
#可選值：true 表示禁用；false 表示不禁用
disableRegister=true

#預設角色，可選值：ROLE_DATA_ADMIN、ROLE_DATA_ANALYST
#ROLE_DATA_ADMIN 資料管理員，可以管理資料來源、資料集、圖表、看板
#ROLE_DATA_ANALYST 資料分析員，僅可檢視資料來源、資料集、圖表、看板，展示圖表和看板
#預設角色：註冊使用者
#defaultRole.register=ROLE_DATA_ADMIN
#預設角色：管理員新增使用者
#defaultRole.add=ROLE_DATA_ADMIN
#預設角色：匿名使用者
#defaultRole.anonymous=ROLE_DATA_ADMIN

#日誌級別
#ERROR, WARN, INFO, DEBUG, TRACE
logging.level.org.datagear=INFO

#日誌儲存路徑
logging.file.name=logs/datagear.log

#服務埠號
server.port=50401

#資料來源配置
datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
datasource.url=jdbc:sqlserver://192.168.1.30:1433;SelectMethod=cursor;DatabaseName=datagear
datasource.username=datagear
datasource.password=datagear