oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: bin.lb
5
# owner group: SQL2
6
# tags: ddl
7
# description:
8
#
9
--disable_warnings
10
drop table if exists fin_influx_extension;
11
drop table if exists fin_influx_inst;
12
drop table if exists fin_settle_serial_map;
13
drop table if exists fin_influx_terminal;
14
drop table if exists fin_info_transaction;
15
drop table if exists fin_influx_terminal_unique;
16
drop table if exists fin_request_no_unique;
17
drop table if exists fin_settle_serial_unique;
18
drop table if exists fin_influx_transaction;
19
drop table if exists idx1_fin_influx_transaction;
20
drop table if exists fin_influx_payer;
21
drop table if exists idx1_fin_influx_payer;
22
drop table if exists idx2_fin_influx_payer;
23
--enable_warnings
24

25
## table 1
26
create table fin_influx_extension(INFLUX_ID varchar(64) primary key, EXTENSION1 varchar(32), EXTENSION2 varchar(32), REQ_EXTENSION varchar(4000), RES_EXTENSION varchar(4000), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
27
insert into fin_influx_extension(INFLUX_ID, REQ_EXTENSION, RES_EXTENSION, GMT_CREATE, GMT_MODIFIED) values('0001','req_1', 'res_1', '2012-12-20 12:00', '2013-1-20 13:00');
28
select INFLUX_ID,EXTENSION1,EXTENSION2,REQ_EXTENSION,RES_EXTENSION,GMT_CREATE,GMT_MODIFIED from fin_influx_extension where INFLUX_ID = '0001';
29

30
## table 2
31
create table fin_influx_inst(INFLUX_ID varchar(64) primary key, INST_ID varchar(32), INST_SERIAL_NO varchar(32), INST_REF_NO varchar(32), RESP_AMOUNT int, RESP_CURRENCY char(3), INST_RESULT_CODE  varchar(16), INST_RESULT_DESCRIPTION varchar(256), GMT_SETTLE datetime(6), GMT_RESP datetime(6), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
32
insert into fin_influx_inst values('0001', '00001', 'serial_no', 'ref_no', 100, '123', 'result_code', 'result_des', '2013-1-20 13:00','2013-1-20 13:00','2013-1-20 13:00','2013-1-20 13:00');
33

34
SELECT INFLUX_ID, INST_ID, INST_SERIAL_NO, INST_REF_NO, RESP_AMOUNT, RESP_CURRENCY, INST_RESULT_CODE, INST_RESULT_DESCRIPTION, GMT_SETTLE, GMT_RESP, GMT_CREATE, GMT_MODIFIED FROM fin_influx_inst  WHERE INFLUX_ID = '0001';
35

36
## table 3 fin_settle_serial_map
37
create table fin_settle_serial_map(INFLUX_ID varchar(64) primary key, SETTLE_SERIAL_NO varchar(64), INST_ID varchar(32), FINANCE_EXCHANGE_CODE varchar(32), GMT_CREATE datetime(6), GMT_MODIFIED datetime(6));
38
SELECT FINANCE_EXCHANGE_CODE, SETTLE_SERIAL_NO, INST_ID, INFLUX_ID, GMT_CREATE, GMT_MODIFIED FROM fin_settle_serial_map WHERE   SETTLE_SERIAL_NO = '0001' AND FINANCE_EXCHANGE_CODE = '0001';
39
SELECT FINANCE_EXCHANGE_CODE, SETTLE_SERIAL_NO, INST_ID, INFLUX_ID, GMT_CREATE, GMT_MODIFIED FROM fin_settle_serial_map WHERE   SETTLE_SERIAL_NO = '0001';
40
SELECT INFLUX_ID FROM fin_settle_serial_map  WHERE FINANCE_EXCHANGE_CODE IN ('EXCHANGECODES') AND SETTLE_SERIAL_NO = '0001';
41

42
## table 4 fin_influx_terminal
43
create table fin_influx_terminal(influx_id	varchar(64)  primary key, inst_id	varchar(32), inst_merchant_no	varchar(32), inst_terminal_no	varchar(32), term_batch_no	varchar(6), term_trace_no	varchar(6), rrn	varchar(12), auth_code	varchar(6), gmt_create	datetime, gmt_modified	datetime, finance_exchange_code	varchar(32), inst_account_no	varchar(32), exchange_amount	int, exchange_currency	char(3), extension	varchar(4000));
44
select influx_id, inst_id, inst_merchant_no, inst_terminal_no, term_batch_no, term_trace_no, rrn, auth_code, gmt_create, gmt_modified, finance_exchange_code, inst_account_no, exchange_amount, exchange_currency, extension from fin_influx_terminal  where influx_id='0001' and term_trace_no = '123';
45

46
## table 5 fin_info_transaction 
47
create table fin_info_transaction( 
48
info_id	varchar(64) primary key, 
49
inst_id	varchar(32),
50
business_code	varchar(16),
51
sub_business_code	varchar(16),
52
exchange_type	varchar(32),
53
finance_exchange_code	varchar(32),
54
exchange_status	varchar(8),
55
request_info	varchar(4000),
56
response_info	varchar(4000),
57
gmt_send	datetime,
58
gmt_res	datetime,
59
gmt_create	datetime,
60
gmt_modified	datetime,
61
result_code	varchar(16),
62
result_description	varchar(256),
63
inst_result_code	varchar(16),
64
inst_result_description	varchar(256)
65
);
66
# select using primary key
67

68
## table 6 fin_influx_terminal_unique
69
create table fin_influx_terminal_unique(
70
inst_id	varchar(32),
71
inst_merchant_no	varchar(32),
72
inst_terminal_no	varchar(32),
73
term_batch_no	varchar(6),
74
term_trace_no	varchar(6),
75
gmt_create	datetime,
76
gmt_modified	datetime,
77
primary key(inst_id, term_batch_no, inst_merchant_no, term_trace_no, inst_terminal_no)
78
);
79
#selects to add 
80

81

82
## table 7 fin_request_no_unique
83
create table fin_request_no_unique(
84
request_identify	varchar(32),
85
request_biz_no	varchar(64),
86
gmt_create	datetime,
87
gmt_modified	datetime,
88
influx_id	varchar(64),
89
primary key(request_identify, request_biz_no)
90
);
91
# selects to add
92

93

94
## table 8 fin_settle_serial_unique
95
create table fin_settle_serial_unique(
96
finance_exchange_code	varchar(32),
97
settle_serial_no	varchar(64),
98
gmt_create	datetime,
99
gmt_modified	datetime,
100
primary key(finance_exchange_code, settle_serial_no)
101
);
102

103
## table 9 fin_influx_transaction
104
create table fin_influx_transaction(
105
influx_id	varchar(64) primary key,
106
org_influx_id	varchar(64),
107
inst_id	varchar(32),
108
business_code	varchar(16),
109
sub_business_code	varchar(16),
110
exchange_type	varchar(32),
111
finance_exchange_code	varchar(32),
112
settle_serial_no	varchar(64),
113
payer_account_no	varchar(32),
114
exchange_amount	int,
115
exchange_currency	char(3),
116
account_amount	int,
117
account_currency	char(3),
118
settle_amount	int,
119
settle_currency	char(3),
120
settle_status	varchar(8),
121
exchange_status	varchar(8),
122
result_code	varchar(16),
123
result_description	varchar(256),
124
recover_flag	char(1),
125
recon_flag	char(1),
126
negative_flag	char(1),
127
negative_exchange_type	varchar(16),
128
request_identify	varchar(32),
129
request_biz_no	varchar(64),
130
pay_unique_no	varchar(64),
131
pay_channel_api	varchar(32),
132
inst_channel_api	varchar(32),
133
clear_channel	varchar(32),
134
biz_identity	varchar(32),
135
gmt_submit	datetime,
136
gmt_resp	datetime,
137
gmt_settle	datetime,
138
gmt_create	datetime,
139
gmt_modified	datetime
140
);
141
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction where influx_id = '0001';
142

143
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction  where finance_exchange_code = 'ss' and settle_serial_no = '00001';
144
 
145
select influx_id, org_influx_id, inst_id, business_code, sub_business_code, exchange_type, finance_exchange_code, settle_serial_no, payer_account_no, exchange_amount, exchange_currency, account_amount, account_currency, settle_amount, settle_currency, settle_status, exchange_status, result_code, result_description, recover_flag, recon_flag, negative_flag, negative_exchange_type, request_identify, request_biz_no, pay_unique_no, pay_channel_api, inst_channel_api, clear_channel, biz_identity, gmt_submit, gmt_resp, gmt_settle, gmt_create, gmt_modified from fin_influx_transaction   where inst_channel_api = 'api' and settle_serial_no = '00001';
146

147
### table 10 idx1_fin_influx_transaction
148

149
create table idx1_fin_influx_transaction(
150
settle_serial_no	varchar(64),
151
finance_exchange_code	varchar(32),
152
influx_id	varchar(64),
153
inst_channel_api	varchar(32),
154
primary key(settle_serial_no,finance_exchange_code,influx_id)
155
);
156

157
select * from idx1_fin_influx_transaction where finance_exchange_code = 'sss' and settle_serial_no = 'ss';
158
select * from idx1_fin_influx_transaction where inst_channel_api = 'ss' and settle_serial_no = 'ss';
159

160
### table 11 fin_influx_payer
161

162
create table fin_influx_payer(
163

164
influx_id	varchar(64) ,
165
payer_account_no	varchar(32),
166
payer_name	varchar(32),
167
inst_account_no	varchar(32),
168
inst_account_name	varchar(128),
169

170
card_type	varchar(16),
171
card_index	varchar(32),
172
issuer	varchar(32),
173
agreement_no	varchar(100),
174
certificate_type	varchar(64),
175
certificate_no	varchar(32),
176
mobile_phone	varchar(16),
177
pay_tool	varchar(16),
178
bill_no	varchar(32),
179
bill_type	varchar(8),
180
gmt_create	datetime,
181
gmt_modified	datetime,
182
primary key(inst_account_no, gmt_create, influx_id)
183
);
184
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where influx_id='0001';
185
   
186
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where bill_no='sss';
187
   
188
select influx_id, payer_account_no, payer_name, inst_account_no, inst_account_name, card_type, card_index, issuer, agreement_no, certificate_type, certificate_no, mobile_phone, pay_tool, bill_no, bill_type, gmt_create, gmt_modified from fin_influx_payer where inst_account_no='0001' and gmt_create>='2012-10-1 23:00:00' and gmt_create<='2012-12-23 23:00:00';
189

190
## table 12 idx1_fin_influx_payer
191
create table idx1_fin_influx_payer (
192
bill_no	varchar(32),
193
inst_account_no	varchar(32),
194
gmt_create	datetime,
195
influx_id	varchar(64),
196
virtual_col1	int,
197
primary key(bill_no,inst_account_no,gmt_create,influx_id)
198
);
199
# bill_no+inst_account_no+gmt_create+influx_id+influx_id
200

201
## table 13 idx2_fin_influx_payer
202
create table idx2_fin_influx_payer(
203
influx_id	varchar(64),
204
inst_account_no	varchar(32),
205
gmt_create	datetime,
206
virtual_col1	int,
207
a               int,
208
primary key(influx_id,inst_account_no,gmt_create,virtual_col1)
209
);
210
# influx_id+inst_account_no+gmt_create+inlux_id主键 ，满足fin_influx_payer的第3种where influx_id=?查询
211

212
drop table fin_influx_extension;
213
drop table fin_influx_inst;
214
drop table fin_settle_serial_map;
215
drop table fin_influx_terminal;
216
drop table fin_info_transaction;
217
drop table fin_influx_terminal_unique;
218
drop table fin_request_no_unique;
219
drop table fin_settle_serial_unique;
220
drop table fin_influx_transaction;
221
drop table idx1_fin_influx_transaction;
222
drop table fin_influx_payer;
223
drop table idx1_fin_influx_payer;
224
drop table idx2_fin_influx_payer;
225