# 4.0.3.rc2 後續 DB 規範 (sql 內容部分)
## 修改紀錄
|**版本**|**時間**|**修改範圍**|**修改人**|
|:--:|:--:|:--:|:--:|
| 4.0.4 | 2023/12/25 | 建立文件 | Edward |
| 4.0.4 | 2023/12/29 | 新增 sp 規範 | Edward |
| 4.0.4 | 2024/01/02 | 修改欄位新增方式 | Edward |
| 4.0.5 | 2024/01/22 | 新增廠商規範 | Edward |
| 4.0.5 | 2024/01/29 | 調整新增頁面範例 | Edward |
| 5.1.6 | 2024/04/18 | gameInfo變動 | Edward |
| 6.0.0 | 2024/04/22 | partition變動 | Edward |
## 超連結
#### [A. 主目錄](https://hackmd.io/@lkiK_sKXSz-7nlCQuYbvvQ/rJNKiQFkR)
#### [B. 檔案命名與位置](https://hackmd.io/@lkiK_sKXSz-7nlCQuYbvvQ/HJlOQXFyC)
#### [C. git branch 與上版](https://hackmd.io/@lkiK_sKXSz-7nlCQuYbvvQ/Bk5TimK1C)
#### [D. sql 檔案內容](https://hackmd.io/@lkiK_sKXSz-7nlCQuYbvvQ/Sy-_smFyA)
## 目錄
#### A. 大方向
#### B. 模塊or頁面新增
#### C. 全品牌統一資料
#### D. 欄位新增或修改
#### E. 遊戲新增
#### F. 金額幣別問題
#### G. sp 新增模板
#### H. 外接廠商部分
## A. 大方向
所有 sql 檔案在寫的時候，請盡量以可以重複執行的寫法去寫，減少執行 flyway 執行時噴錯的可能
common 內容皆為全品牌通用，後續不再做品牌區分，只做版本區分，所以在這部分添加內容時，請注意以下兩點:
1. 是否能全品牌通用 ?
2. 在升版時會不會影響到舊有資料 ? 會的話要補處理 sql
3. 關於遊戲部分一律放到 brand
brand 則是依照品牌去做調整，新增時請注意以下幾點
1. 禁止調整一二級注單，依不同外部廠商而新增的有關 table 以外的 schema
2. 若為全品牌統一資料，請用 update 去做狀態，是否支援的調整，禁止直接清空 table
請在 sql 檔內提供一定的說明，減輕 DBA 的工作量，schema 在新增或調整時，請盡量將各欄位註記寫清楚
## B. 模塊or頁面新增
### 1. 新增模塊:
1. rp_power 的 pid = 0 時 status 請預設為 0，並依照品牌需求在 init 去做 status UPDATE，並且單個檔案只放此部分，頁面請另外放。
```sql=
=== common ===
INSERT INTO game_manage.rp_power (pid, name, code, action, mark, sort, icon, status, isagent)
SELECT *
FROM (SELECT '0', '网咖系统', 'm0r12', '#', '', '12' AS sort, 'pre-ico-a', 0 AS status, '1' AS isagent) t
WHERE NOT EXISTS (
SELECT 1
FROM game_manage.rp_power
WHERE pid = 0 AND name = '网咖系统'
);
```
2. brand 部分請特別注意 where 條件，條件請盡量詳細避免有多筆資料受到調整。
3. 禁用 id 為條件，雖然預期 rp_power 與 rp_api 全品牌應該相同，但避免問題還是盡量避免
```sql=
=== brand ===
SELECT id INTO @tmpPid
FROM game_manage.rp_power
WHERE name = '网咖系统';
UPDATE game_manage.rp_power SET status = 1 WHERE name = '网咖系统' AND id = @tmpPid;
```
### 2. 新增頁面與功能:
1. id 改成 auto
2. WHERE NOT EXISTS 條件多加 pid，避免多頁面要吃同一個 api 而沒新增到
3. 以下為體育交收報表為例
```sql=
=== common ===
-- 取得模塊 pid
SET @powerId = '';
SELECT id INTO @powerId FROM `game_manage`.`rp_power` WHERE `name` = '财务管理' AND `pid` = 0;
INSERT INTO `game_manage`.`rp_power` ( pid, name, code, action, mark, sort, icon, status, isagent)
SELECT pid, name, code, action, mark, sort, icon, status, isagent
FROM (SELECT @powerId AS pid, '体育交收报表' AS name, 'm529r7' AS code, '/deliveryReportSport' AS action, '' AS mark, 11 AS sort, 'fa-puzzle-piece' AS icon, 0 AS status, 0 AS isagent) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_power` WHERE `action` = A.`action` AND name = A.name AND `pid` = @powerId);
-- 取得頁面 pid
SET @pageId = '';
SELECT id INTO @pageId FROM `game_manage`.`rp_power` WHERE `name` = '体育交收报表' AND `pid` = @powerId;
-- 新增報表生成功能
INSERT INTO `game_manage`.`rp_power` (pid, name, code, action, mark, sort, icon, status, isagent)
SELECT pid, name, code, action, mark, sort, icon, status, isagent
FROM (SELECT @pageId AS pid, '体育交收报表生成' AS name, CONCAT('m',@pageId,'r1') AS code, '#' AS action, '' AS mark, 1 AS sort, '' AS icon, 0 AS status, 0 AS isagent) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_power` WHERE `action` = A.`action` AND name = A.name AND `pid` = @pageId);
-- 取得報表生成 pid
SET @createId = '';
SELECT id INTO @createId FROM `game_manage`.`rp_power` WHERE `name` = '体育交收报表生成' AND `pid` = @pageId;
-- 新增報表導出功能
INSERT INTO `game_manage`.`rp_power` (pid, name, code, action, mark, sort, icon, status, isagent)
SELECT pid, name, code, action, mark, sort, icon, status, isagent
FROM (SELECT @pageId AS pid, '体育交收报表导出' AS name, CONCAT('m',@pageId,'r4') AS code, '#' AS action, '' AS mark, 4 AS sort, '' AS icon, 0 AS status, 0 AS isagent) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_power` WHERE `action` = A.`action` AND name = A.name AND `pid` = @pageId);
-- 取得報表導出 pid
SET @exportId = '';
SELECT id INTO @exportId FROM `game_manage`.`rp_power` WHERE `name` = '体育交收报表导出' AND `pid` = @pageId;
-- 新增頁面所需 api
INSERT INTO `game_manage`.`rp_api` (rp_power_id, action, method)
SELECT `rp_power_id`,`action`,`method`
FROM (SELECT @pageId AS `rp_power_id`, '/deliveryReportSport' AS `action`, 'GET' AS `method`) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_api` WHERE `action` = A.`action` AND `method` = A.`method` AND `rp_power_id` = @pageId);
INSERT INTO `game_manage`.`rp_api` (rp_power_id, action, method)
SELECT `rp_power_id`,`action`,`method`
FROM (SELECT @pageId AS `rp_power_id`, '/deliveryReportSport/initData' AS `action`, 'GET' AS `method`) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_api` WHERE `action` = A.`action` AND `method` = A.`method` AND `rp_power_id` = @pageId);
~~~ 中間省略 ~~~
-- 新增報表生成所需 api
INSERT INTO `game_manage`.`rp_api` (rp_power_id, action, method)
SELECT `rp_power_id`,`action`,`method`
FROM (SELECT @createId AS `rp_power_id`, '/deliveryReportSport/saveLevels' AS `action`, 'POST' AS `method`) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_api` WHERE `action` = A.`action` AND `method` = A.`method` AND `rp_power_id` = @createId);
-- 新增報表導出所需 api
INSERT INTO `game_manage`.`rp_api` (rp_power_id, action, method)
SELECT `rp_power_id`,`action`,`method`
FROM (SELECT @exportId AS `rp_power_id`, '/deliveryReportSport/exportData' AS `action`, 'GET' AS `method`) A
WHERE NOT EXISTS (SELECT 1 FROM `game_manage`.`rp_api` WHERE `action` = A.`action` AND `method` = A.`method` AND `rp_power_id` = @exportId);
```
```sql=
=== brand ===
SELECT id INTO @tmpPid
FROM game_manage.rp_power
WHERE name = '网咖系统';
UPDATE game_manage.rp_power SET status = 1 WHERE name = '代理账号' AND pid = @tmpPid;
```
## C. 全品牌統一資料
1. 類似於模塊與頁面部分，凡是有能夠用 isSupport 或是 status 等做品牌間啟用或不啟用的參數，一律在 migrate create table 與 insert data，預設為關閉，並在 init 做 update
2. 目前已確定此作法 table
1. KYDB_NEW.theme
2. KYDB_NEW.festival_skin
3. game_manage.rp_currency
4. KYDB_NEW.Sys_Language
```sql=
=== common ===
== schema ==
USE KYDB_NEW;
CREATE TABLE IF NOT EXISTS`Sys_Language` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`en_name` varchar(100) NOT NULL,
`abbreviation` varchar(20) NOT NULL,
`status` tinyint(1) NOT NULL COMMENT "是否開啟 0:關 1:開",
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
== 固定資料 ==
INSERT INTO Sys_Language (id, name, en_name, abbreviation, status) VALUES (1, '中文(简体)', 'simplifiedChinese', 'zh_cn', 0);
INSERT INTO Sys_Language (id, name, en_name, abbreviation, status) VALUES (2, '中文(繁体)', 'traditionalChinese', 'zh_tw', 0);
INSERT INTO Sys_Language (id, name, en_name, abbreviation, status) VALUES (3, '英语', 'english', 'en_us', 0);
INSERT INTO Sys_Language (id, name, en_name, abbreviation, status) VALUES (4, '越南语', 'vietnamese', 'vi_vn', 0);
INSERT INTO Sys_Language (id, name, en_name, abbreviation, status) VALUES (5, '日文', 'japanese', 'ja_jp', 0);
```
```sql=
=== brand ===
UPDATE KYDB_NEW.Sys_Language SET `status` = 1 WHERE name = '中文(简体)';
```
## D. 欄位新增或修改
1. 欄位新增時請建立 default，並確定是否全品牌通用該 default ，若無法的話請補充修改 sql 在 common 內，請盡量用 sp 去確認該欄位是否存在，不存在用新增，存在則用修改
:::warning
目前與 Jimmy 討論過後， RD 在做提交檔案依然用 sp 包，但請另外註記 直接執行的 sql ，後續 DBA 提交給外測以外的環境會將 sp 部分移除只留下 ALTER
:::
```sql=
=== common ===
DROP PROCEDURE IF EXISTS `KYDB_NEW`.`addKYDBAgentColumn`;
DELIMITER $$
CREATE PROCEDURE `KYDB_NEW`.`addKYDBAgentColumn`()
BEGIN
IF not EXISTS (SELECT column_name FROM information_schema.columns WHERE table_schema = 'KYDB_NEW' and table_name = 'agent' and column_name = 'gameCurrencyControl ')
THEN
ALTER TABLE `KYDB_NEW`.`agent` add `gameCurrencyControl` int(11) NULL DEFAULT 1 COMMENT '游玩币别显示开关 (1 = 開, 2 = 關)';
ALTER TABLE `KYDB_NEW`.`agent` add `gamePlayedListControl` int(11) NULL DEFAULT 1 COMMENT '游玩记录快捷开关 (1 = 開, 2 = 關)';
ELSE
ALTER TABLE `KYDB_NEW`.`agent` CHANGE `gameCurrencyControl` `gameCurrencyControl` int(11) NULL DEFAULT 1 COMMENT '游玩币别显示开关 (1 = 開, 2 = 關)';
ALTER TABLE `KYDB_NEW`.`agent` CHANGE `gamePlayedListControl` `gamePlayedListControl` int(11) NULL DEFAULT 1 COMMENT '游玩记录快捷开关 (1 = 開, 2 = 關)';
END IF;
END;
$$
DELIMITER ;
call KYDB_NEW.addKYDBAgentColumn();
DROP PROCEDURE IF EXISTS `KYDB_NEW`.`addKYDBAgentColumn`;
-- ALTER TABLE `KYDB_NEW`.`agent` add `gameCurrencyControl` int(11) NULL DEFAULT 1 COMMENT '游玩币别显示开关 (1 = 開, 2 = 關)', add `gamePlayedListControl` int(11) NULL DEFAULT 1 COMMENT '游玩记录快捷开关 (1 = 開, 2 = 關)';
```
2. 欄位做更動時也確認是否會影響到原資料判別，記得補上相對應的改動，或是盡量保留原值，新需求以補充的方式做新增，以下範例為原資料不須改動
```sql=
=== 原 cloumn: ===
KYDB_NEW.`agent`.`payType` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '是否自动转帐(0:自动转帐 1:代理转帐)';
=== common ===
ALTER TABLE `KYDB_NEW`.`agent`
MODIFY COLUMN `payType` tinyint(1) NOT NULL DEFAULT 0 COMMENT '游戏内转帐类型 0: 否 1: 平台通知平台转帐 2:代理自行转帐 3:代理自行转帐(特殊)' AFTER `formalUrl`;
```
3. 後續欄位的添加與調整不要再用 sp 去包，避免產生多個重複性檔案，再上版時出現噴錯才能知道是否有未發現問題
## E. 遊戲新增
1. 遊戲相關部分一律放入 brand ，包含 GameInfo，GameRoomInfo，一、二級注單 schema 與 Sys_cron_partition(通用部分會放在 migrate，遊戲一二級注單部分會放在 brand)。一二級注單的 schema 請盡可能保持全品牌統一，如有任何調整請通知 DBA 在範例部分做調整。以下為 black 為範例，以下內容皆為 brand
2. GameInfo: category = 1 為內部遊戲， > 1 為外部遊戲對照廠商，對照表清參照下列網址，若所需廠商不在表內，請連絡後台組 Jeremy 做添加
https://docs.google.com/spreadsheets/d/1-WMUGppguyJMdmm4XqyaAtMSpCX5n50dTan0DVTnQXA/edit?gid=0#gid=0
```sql=
INSERT INTO `KYDB_NEW`.`GameInfo` (GameID, GameName, GameLogo, GameType, GameStatus, GameParameter, DESKey, GameURL, HttpURL, CreateUser, CreateTime, LastEditUser, LastEditTime, IsDelete, GameChannelID, GameIPWhitelis, Sort, ASEKey, FormalLink, DemoLink, DemoOutLink, Setting, gType, desensitization, category, ptkEffect) VALUES
(600, '21点', null, 0, 0, 'black', null, 'ky34.com', null, 'System', '2023-06-07 15:19:15', 'System', null, 0, null, null, 0, null, null, null, null, null, '1', '21D', 1, 0),
```
3. GameRoomInfo
```sql=
INSERT INTO `KYDB_NEW`.`GameRoomInfo` (ServerID, ServerName, ServerDLLName, KindID, NodeID, SortID, GameID, TableCount, ServerType, ServerPort, DataBaseName, DataBaseAddr, ServiceAddr, VideoAddr, CellScore, RevenueRatio, RestrictScore, MinEnterScore, MaxEnterScore, MaxPlayer, MatchInning, ServerRule, ServerNote, CreateDateTime, ModifyDateTime, PlazaID, Saturation, AndroidMinLeaveScore, AndroidMaxLeaveScore, ChairCount, TableFee, TakeMoney, OnLine, OfflineTimesKick, ShowPlayerName, desensitization) VALUES
(6001, '21点新手房', 'lh.dll', 600, 0, 100, 600, 1000, null, null, null, null, null, null, 300, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 3, 1, '21点新手房'),
(6002, '21点初级房', 'lh.dll', 600, 0, 100, 600, 1000, null, null, null, null, null, null, 1000, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 3, 1, '21点初级房'),
(6003, '21点中级房', 'lh.dll', 600, 0, 100, 600, 1000, null, null, null, null, null, null, 3000, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 3, 1, '21点中级房'),
(6004, '21点高级房', 'lh.dll', 600, 0, 100, 600, 1000, null, null, null, null, null, null, 10000, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 3, 1, '21点高级房');
```
4. 一級注單
```sql=
USE `game_record`;
CREATE TABLE IF NOT EXISTS `gamerecord_gflower` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`big_data` longtext NOT NULL COMMENT '游戏记录数据',
`ip` varchar(50) DEFAULT NULL COMMENT '游戏服务器ip',
`createtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
KEY `index_id_createtime` (`id`,`createtime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
5. 一般二級注單: 若有欄位調整，請通知 DBA 修改新增模板
```sql=
USE `detail_record`;
CREATE TABLE IF NOT EXISTS `XXXXX_gameRecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'PK ID',
`GameID` bigint(20) DEFAULT NULL COMMENT '房间流水号',
`Accounts` varchar(190) DEFAULT NULL COMMENT '代理ID_玩家账号',
`ServerID` int(11) DEFAULT NULL COMMENT '服务器ID',
`RoomType` int(11) DEFAULT NULL COMMENT '房间类型',
`KindID` int(11) DEFAULT NULL COMMENT '游戏ID',
`TableID` bigint(20) DEFAULT NULL COMMENT '桌号',
`ChairID` int(11) DEFAULT NULL COMMENT '座位号',
`UserCount` int(11) DEFAULT NULL COMMENT '人数',
`HandCard` varchar(10000) DEFAULT NULL COMMENT '手牌',
`CellScore` bigint(20) DEFAULT NULL COMMENT '有效投注额',
`AllBet` bigint(20) DEFAULT NULL COMMENT '总投注',
`Profit` bigint(20) DEFAULT NULL COMMENT '输赢金额',
`CurScore` bigint(20) DEFAULT NULL COMMENT '玩家身上剩余筹码',
`TakeScore` bigint(20) DEFAULT NULL COMMENT '玩家携带金额',
`Revenue` bigint(20) DEFAULT NULL COMMENT '抽水',
`GameStartTime` datetime DEFAULT NULL COMMENT '游戏开始时间',
`GameEndTime` timestamp NULL DEFAULT NULL COMMENT '游戏结束时间',
`CardValue` varchar(1000) DEFAULT NULL COMMENT '此局开奖资讯(兼容旧版牌型)',
`OpValue` bigint(20) DEFAULT NULL COMMENT '原始数据中的Id OpValue',
`ChannelID` int(11) DEFAULT NULL COMMENT '代理ID',
`LineCode` varchar(50) DEFAULT NULL COMMENT '站台',
`GameUserNO` varchar(50) DEFAULT NULL COMMENT '游戏局号',
`language` varchar(50) NOT NULL COMMENT '语言',
`currency` varchar(50) NOT NULL COMMENT '币别',
`CreateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
UNIQUE KEY `uk_no_endtime` (`GameUserNO`,`GameEndTime`),
KEY `index_id_GameEndTime` (`id`,`GameEndTime`) USING BTREE,
KEY `index_gameuserno` (`GameUserNO`) USING BTREE,
KEY `index_gameendtime` (`GameEndTime`) USING BTREE,
KEY `index_gameendtime_kindId` (`GameEndTime`,`KindID`) USING BTREE,
KEY `index_gameendtime_channel` (`GameEndTime`,`ChannelID`) USING BTREE,
KEY `index_gameendtime_account` (`GameEndTime`,`Accounts`) USING BTREE,
KEY `index_gameendtime_kindId_serverId` (`GameEndTime`,`KindID`,`ServerID`) USING BTREE,
KEY `index_accounts` (`Accounts`) USING BTREE,
KEY `index_gamestarttime_account` (`GameStartTime`,`Accounts`) USING BTREE,
KEY index_gameendtime_currency (GameEndTime,currency) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
6. 魚機二級注單: 若有欄位調整，請通知 DBA 修改新增模板
```sql=
USE `detail_record`;
CREATE TABLE IF NOT EXISTS `XXXX_gameRecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`GameID` bigint(20) DEFAULT NULL,
`Accounts` varchar(190) DEFAULT NULL,
`ServerID` int(11) DEFAULT NULL,
`RoomType` int(11) DEFAULT NULL,
`KindID` int(11) DEFAULT NULL,
`TableID` bigint(20) DEFAULT NULL,
`ChairID` int(11) DEFAULT NULL,
`UserCount` int(11) DEFAULT NULL,
`HandCard` varchar(10000) DEFAULT NULL,
`CellScore` bigint(20) DEFAULT NULL,
`AllBet` bigint(20) DEFAULT NULL,
`Profit` bigint(20) DEFAULT NULL,
`KillProfit` bigint(20) DEFAULT NULL,
`DiveProfit` bigint(20) DEFAULT NULL,
`NormalProfit` bigint(20) DEFAULT NULL,
`CurScore` bigint(20) DEFAULT NULL,
`TakeScore` bigint(20) DEFAULT NULL,
`Revenue` bigint(20) DEFAULT NULL,
`GameStartTime` datetime DEFAULT NULL,
`GameEndTime` timestamp NULL DEFAULT NULL,
`CardValue` varchar(1000) DEFAULT NULL,
`OpValue` bigint(20) DEFAULT NULL,
`ChannelID` int(11) DEFAULT NULL,
`LineCode` varchar(50) DEFAULT NULL,
`GameUserNO` varchar(50) DEFAULT NULL,
`language` varchar(50) NOT NULL COMMENT '语言',
`currency` varchar(50) NOT NULL COMMENT '币别',
`CreateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
UNIQUE KEY `uk_no_endtime` (`GameUserNO`,`GameEndTime`),
KEY `index_id_GameEndTime` (`id`,`GameEndTime`) USING BTREE,
KEY `index_gameuserno` (`GameUserNO`) USING BTREE,
KEY `index_gameendtime` (`GameEndTime`) USING BTREE,
KEY `index_gameendtime_kindId` (`GameEndTime`,`KindID`) USING BTREE,
KEY `index_gameendtime_channel` (`GameEndTime`,`ChannelID`) USING BTREE,
KEY `index_gameendtime_account` (`GameEndTime`,`Accounts`) USING BTREE,
KEY `index_gameendtime_kindId_serverId` (`GameEndTime`,`KindID`,`ServerID`) USING BTREE,
KEY `index_accounts` (`Accounts`) USING BTREE,
KEY `index_gamestarttime_account` (`GameStartTime`,`Accounts`) USING BTREE,
KEY index_gameendtime_currency (GameEndTime,currency) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
7. Sys_cron_partition: 一級注單每月一切，二級注單每日一切
```sql=
USE `KYDB_NEW`;
INSERT INTO `Sys_cron_partition`(`PartitionDatabase`, `PartitionTable`, `PartitionType`, `PartitionColumns`, `PartitionFunction`, `PartitionStatus`, `CreateTime`) VALUES
('game_record', 'gamerecord_bjl', 'month', 'createtime', 'UNIX_TIMESTAMP', 1, '2023-08-16 18:33:50'),
('detail_record', 'black_gameRecord', 'day', 'GameEndTime', 'UNIX_TIMESTAMP', 1, '2023-08-16 18:33:50');
CALL `KYDB_NEW`.sp_cron_partition_single_table('game_record','gamerecord_bjl');
```
:::success
每次添加需要定期切分 partition 的 table 時都會 CALL KYDB_NEW.sp_cron_partition_single_table();
但理論上不須全部掃過，故提供一個新的 sp ，它可對單個已經寫在 `KYDB_NEW`.`Sys_cron_partition` 內的 table 去切一次 partition ，可省下大量的更新時間，使用方式為 CALL KYDB_NEW.sp_cron_partition_single_table(newDatabase, newTable);
該 sp 上線版本為 6.0.0.1.29。
:::
## F. 金額幣別問題
目前 DB 有關分數與幣別部分，可簡單分為下列幾種
1. 該 table 沒有 currency，而內容所有分數幣別皆為 CNY
2. 該 table 有 currency，而內容所有分數皆為該 currency
3. 該 table 有 currency，而內容所有分數幣別皆為 CNY
4. 該 table 有 currency，而內容部分欄位為 CNY，部分欄位依照 currency
以上第一二種類型可不做特別說明
但屬於三四種的，請在個別欄位的註記寫清楚該欄位用途與幣別
## G. sp 新增模板
1. 一律統一將 sp 命名為 sp_XXXXXXX (view 與 trigger 一樣)
2. 不論新增或是更新，前面都要有 drop 的語句
3. sp 使用者部分因各環境皆不同，若有限制會需要應對環境調整，故後續接不對 sp 新增使用者限制
4. 下列為新增 sp 模板，歡迎套用
```sql=
USE `KYStatis`;
DROP PROCEDURE IF EXISTS `sp_getDayRangeBaseData`;
DELIMITER $$
CREATE PROCEDURE `sp_getDayRangeBaseData` (
IN startTime DATE,
IN endTime DATE,
IN agents LONGTEXT
)
BEGIN
SELECT
SUM(WinGold * exchangeRate) AS dayWinGold,
SUM(LostGold * exchangeRate) AS dayLostGold,
SUM(CellScore * exchangeRate) AS dayValidBet,
SUM(Revenue * exchangeRate) AS dayDeductGold,
SUM(ActiveUsers) AS dayBetGames,
SUM((WinGold + LostGold - Revenue) * exchangeRate) AS dayProfit
FROM
statis_record_agent_all
WHERE
StatisDate BETWEEN startTime AND endTime
AND FIND_IN_SET(ChannelID, agents);
END $$
DELIMITER ;
```
## H. 外接廠商部分
1. 外接廠商包含 GameInfo、company、 companyGameInfo、二級注單、partition 部分皆歸類在 brand。
2. 部分跟特定廠商有關的 table 也歸屬在 brand 內(例如 timerTask.xpg_dailyCheckRecord)
3. 以下為模板
```sql=
USE timerTask ;
CREATE TABLE IF NOT EXISTS `xpg_dailyCheckRecord` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vendor_id` int(11) NOT NULL,
`date` date NOT NULL COMMENT '查询核对日期',
`status` int(1) NOT NULL COMMENT '1: 成功(注单总数相等) 0:失败(注单总数不相等)',
`create_time` datetime NOT NULL COMMENT '完成核对時間',
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
Sign in with Wallet
Connect another wallet