oceanbase

1
set ob_enable_index_direct_select=1;
2
alter system set merger_check_interval = '10s' tenant sys;
3
alter system set merger_check_interval = '10s' tenant all_user;
4
alter system set merger_check_interval = '10s' tenant all_meta;
5
DROP TABLE if exists trade_sequence_000;
6
DROP TABLE if exists BUSINESS_ACTION_000;
7
DROP TABLE if exists BUSINESS_ACTIVITY_000;
8
DROP TABLE if exists NOTIFY_000 ;
9
DROP TABLE if exists NOTIFY_HISTORY_000 ;
10
DROP TABLE if exists TIMEOUT_000 ;
11
DROP TABLE if exists TIMEOUT_HISTORY_000 ;
12
DROP TABLE if exists TIMEOUT_LONG_000 ;
13
DROP TABLE if exists TIMEOUT_REMIND_000;
14
DROP TABLE if exists TRADE_BASE_000 ;
15
DROP TABLE if exists TRADE_CHARGE_000;
16
DROP TABLE if exists TRADE_EXT_000 ;
17
DROP TABLE if exists TRADE_FUND_BILL_000 ;
18
DROP TABLE if exists TRADE_GOODS_INFO_000 ;
19
DROP TABLE if exists TRADE_LOGISTICS_000;
20
DROP TABLE if exists TRADE_MESSAGE_BOARD_000 ;
21
DROP TABLE if exists TRADE_NOTE_000 ;
22
DROP TABLE if exists TRADE_OUTTRADE_000 ;
23
DROP TABLE if exists TRADE_PAYMENT_CLAUSE_000 ;
24
DROP TABLE if exists TRADE_PAYMENT_ORDER_000 ;
25
DROP TABLE if exists TRADE_PROCESS_000 ;
26
DROP TABLE if exists TRADE_REFUND_000 ;
27
DROP TABLE if exists TRADE_VOUCHER_000 ;
28
DROP TABLE if exists TRADE_RECEIVE_ADDRESS_000;
29
DROP TABLE if exists TRADE_COOPERATE_000;
30
DROP TABLE if exists TRADE_SETTLE_ORDER_000;
31
DROP TABLE if exists TRADE_PARTICIPANT_000;
32
DROP TABLE if exists BUSINESS_ACTIVITY_ASYNC_000;
33
DROP TABLE if exists EXTERFACE_INVOKE_000;
34
CREATE TABLE if not exists trade_sequence_000
35
(
36
name varchar(256),
37
value int,
38
min_value int,
39
max_value int,
40
step int,
41
gmt_create TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
42
gmt_modified TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
43
primary key (name)
44
) DEFAULT CHARSET = 'utf8';
45
CREATE TABLE if not exists BUSINESS_ACTION_000 
46
(
47
ACTION_ID     int           NOT NULL,
48
TX_ID         varchar(128)   NOT NULL,
49
NAME          varchar(64)    NOT NULL,
50
STATE         CHAR(1)         NOT NULL,
51
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
52
GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
53
CONTEXT       varchar(4000),
54
primary key (ACTION_ID)
55
) DEFAULT CHARSET = 'utf8';
56
CREATE TABLE if not exists BUSINESS_ACTIVITY_000 
57
(
58
TX_ID                varchar(128)   NOT NULL,
59
STATE               	VARCHAR(1)         NOT NULL,
60
ACCOUNT_TRANS_STATE 	VARCHAR(1)         NOT NULL,
61
GMT_CREATE           TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
62
GMT_MODIFIED         TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
63
PROPAGATION         	VARCHAR(1),
64
CONTEXT              varchar(2048),
65
primary key (TX_ID)
66
) DEFAULT CHARSET = 'utf8';
67
CREATE TABLE if not exists NOTIFY_000 
68
(
69
ID                varchar(34)    NOT NULL,
70
PLUGIN            varchar(50)    NOT NULL,
71
PARAMETER         varchar(4000)  NOT NULL,
72
GROUP_NO          int            NOT NULL,
73
STATUS           	VARCHAR(1)         NOT NULL,
74
GMT_CREATE        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
75
GMT_MODIFY        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
76
IW_USER_ID        varchar(16),
77
CHANNEL_ENDPOINT  varchar(200),
78
BUSINESS_ORDER    varchar(128),
79
BUSINESS_TYPE     varchar(50),
80
PLUGIN_HOST       varchar(15),
81
GMT_LOCAL_CREATE  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
82
primary key (id)
83
)DEFAULT CHARSET = 'utf8';
84
CREATE INDEX NOTIFY_BUSINESS_ORDER1_IND ON NOTIFY_000 (BUSINESS_ORDER);
85
CREATE INDEX NOTIFY_PLUGIN_IND ON NOTIFY_000 (PLUGIN, CHANNEL_ENDPOINT);
86
CREATE TABLE if not exists NOTIFY_HISTORY_000 
87
(
88
ID                varchar(34)    NOT NULL,
89
PLUGIN            varchar(50)    NOT NULL,
90
PARAMETER         varchar(4000)  NOT NULL,
91
GROUP_NO          int            NOT NULL,
92
STATUS           	VARCHAR(1)         NOT NULL,
93
GMT_CREATE        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
94
GMT_MODIFY        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
95
IW_USER_ID        varchar(16),
96
CHANNEL_ENDPOINT  varchar(200),
97
BUSINESS_ORDER    varchar(128),
98
BUSINESS_TYPE     varchar(50),
99
PLUGIN_HOST       varchar(15),
100
GMT_LOCAL_CREATE  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
101
PRIMARY KEY (ID)
102
)DEFAULT CHARSET = 'utf8';
103
CREATE INDEX NOTIFY_HISTORY_000_BORDER_IND ON NOTIFY_HISTORY_000 (BUSINESS_ORDER);
104
CREATE INDEX NOTIFY_HISTORY_IG_IND ON NOTIFY_HISTORY_000 (IW_USER_ID, GROUP_NO);
105
CREATE TABLE if not exists TIMEOUT_000 
106
(
107
JOB_ID        varchar(32)   NOT NULL,
108
TRADE_NO      varchar(64),
109
USER_ID       varchar(128),
110
PAYTYPE       varchar(4),
111
END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
112
ACTION        varchar(30),
113
PARAMETER     varchar(100),
114
STATUS       	VARCHAR(1),
115
ACTION_TYPE  	VARCHAR(1),
116
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
117
PRIOR_LEVEL   int default 1,
118
GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
119
GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
120
PRIMARY KEY (JOB_ID)
121
) DEFAULT CHARSET = 'utf8';
122
CREATE INDEX IND_BEYOND_TIMEOUT_ETIME ON TIMEOUT_000 (END_TIME);
123
CREATE INDEX TIMEOUT_000_PL_IND ON TIMEOUT_000 (PRIOR_LEVEL);
124
CREATE INDEX TIMEOUT_TNO_IND ON TIMEOUT_000 (TRADE_NO);
125
CREATE TABLE if not exists TIMEOUT_HISTORY_000 
126
(
127
JOB_ID        varchar(32)   NOT NULL,
128
TRADE_NO      varchar(64)   NOT NULL,
129
USER_ID       varchar(128),
130
PAYTYPE       varchar(4),
131
END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
132
ACTION        varchar(30),
133
PARAMETER     varchar(100),
134
STATUS       	VARCHAR(1),
135
ACTION_TYPE  	VARCHAR(1)        NOT NULL,
136
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
137
PRIOR_LEVEL   int,
138
GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
139
GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
140
PRIMARY KEY (JOB_ID, TRADE_NO, ACTION_TYPE)
141
) DEFAULT CHARSET = 'utf8';
142
CREATE TABLE if not exists TIMEOUT_LONG_000 
143
(
144
JOB_ID        varchar(32)   NOT NULL,
145
TRADE_NO      varchar(64),
146
USER_ID       varchar(128),
147
PAYTYPE       varchar(4),
148
END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
149
ACTION        varchar(30),
150
PARAMETER     varchar(100),
151
STATUS       	VARCHAR(1),
152
ACTION_TYPE  	VARCHAR(1),
153
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
154
PRIOR_LEVEL   int          ,
155
GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
156
GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
157
PRIMARY KEY (JOB_ID)
158
) DEFAULT CHARSET = 'utf8';
159
CREATE INDEX BYD_TIMEOUT_LONG_ENDTIME_IND ON TIMEOUT_LONG_000 (END_TIME);
160
CREATE INDEX TIMEOUT_LONG_000_PL_IND ON TIMEOUT_LONG_000 (PRIOR_LEVEL);
161
CREATE INDEX TIMEOUT_LONG_TNO_IND ON TIMEOUT_LONG_000 (TRADE_NO);
162
CREATE TABLE if not exists TIMEOUT_REMIND_000 
163
(
164
JOB_ID        varchar(32)   NOT NULL,
165
TRADE_NO      varchar(64),
166
USER_ID       varchar(128),
167
PAYTYPE       varchar(4),
168
END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
169
ACTION        varchar(30),
170
PARAMETER     varchar(100),
171
STATUS       	VARCHAR(1),
172
ACTION_TYPE  	VARCHAR(1),
173
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
174
PRIOR_LEVEL   int          ,
175
GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
176
GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
177
PRIMARY KEY (JOB_ID)
178
) DEFAULT CHARSET = 'utf8';
179
CREATE INDEX BY_TIMEOUT_REMIND_ETIME_IND ON TIMEOUT_REMIND_000 (END_TIME);
180
CREATE INDEX TIMEOUT_REMIND_000_PL_IND ON TIMEOUT_REMIND_000 (PRIOR_LEVEL);
181
CREATE INDEX TIMEOUT_REMIND_TNO_IND ON TIMEOUT_REMIND_000 (TRADE_NO);
182
CREATE TABLE if not exists TRADE_BASE_000 
183
(
184
ID                     int           NOT NULL,
185
TRADE_NO               varchar(64)    NOT NULL,
186
OUT_TRADE_NO           varchar(64),
187
SELLER_ACCOUNT         varchar(100)   NOT NULL,
188
SELLER_LOGIN_EMAIL     varchar(100)   NOT NULL,
189
BUYER_ACCOUNT          varchar(100)   NOT NULL,
190
BUYER_LOGIN_EMAIL      varchar(100)   NOT NULL,
191
SELLER_TYPE            int,
192
BUYER_TYPE             int,
193
TRADE_FROM             varchar(100),
194
TRADE_EMAIL            varchar(100),
195
OPERATOR_ROLE          VARCHAR(1)     NOT NULL,
196
TRADE_STATUS           varchar(50)    NOT NULL,
197
TOTAL_FEE              int            default 0 NOT NULL,
198
SERVICE_FEE_RATIO      varchar(64),
199
SERVICE_FEE            int            default 0 NOT NULL,
200
CURRENCY               varchar(3),
201
SELLER_ACTION          varchar(200),
202
BUYER_ACTION           varchar(200),
203
GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00"                ,
204
SELLER_USER_ID         varchar(16),
205
BUYER_USER_ID          varchar(16),
206
ADDITIONAL_TRD_STATUS  varchar(50),
207
TRADE_TYPE             varchar(4)     NOT NULL,
208
SELLER_FULLNAME        varchar(256)   default '',
209
BUYER_FULLNAME         varchar(256)   default '',
210
SELLER_NICK            varchar(100)   default '' NOT NULL,
211
BUYER_NICK             varchar(100)   default '' NOT NULL,
212
GOODS_TITLE            varchar(256),
213
GMT_LAST_MODIFIED_DT   TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
214
STOP_TIMEOUT           varchar(4) 	default '0',
215
GATHERING_TYPE         varchar(4),
216
BUYER_MARKER           VARCHAR(1),
217
SELLER_MARKER          VARCHAR(1),
218
BUYER_MARKER_MEMO      varchar(1000),
219
SELLER_MARKER_MEMO     varchar(1000),
220
CHANNEL                varchar(32),
221
PRODUCT                varchar(20),
222
PAY_CHANNEL            varchar(32),
223
OTHER_SERVICE_FEE      int,
224
RELATION_PRO           varchar(64),
225
PLATFORM varchar(100),
226
CHANNEL_PARTNER varchar(100),
227
GOODS_TYPE      VARCHAR(4),
228
GOODS_SUB_TYPE  VARCHAR(4),
229
PRIMARY KEY (TRADE_NO)
230
)DEFAULT CHARSET = 'utf8';
231
CREATE INDEX BYD_TRADE_BASE_TEMAIL_GMTC_IND ON TRADE_BASE_000 
232
(TRADE_EMAIL, GMT_CREATE);
233
CREATE INDEX IND_BEYOND_TRADE_BASE_BUYGMT1 ON TRADE_BASE_000 
234
(BUYER_ACCOUNT, GMT_CREATE);
235
CREATE INDEX IND_BEYOND_TRADE_BASE_SALGMT1 ON TRADE_BASE_000 
236
(SELLER_ACCOUNT, GMT_CREATE);
237
CREATE TABLE if not exists TRADE_CHARGE_000 
238
(
239
ID                int           NOT NULL,
240
TRADE_NO          varchar(64)   NOT NULL,
241
GMT_CHARGE        TIMESTAMP(6) default "2012-01-01 12:00:00"    NOT NULL,
242
GMT_PAY           TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
243
CHARGE_OUT_DATE  	VARCHAR(8),
244
GMT_MODIFIED      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
245
PLAN_CHARGE       int,
246
ACTUAL_CHARGE     int,
247
RATE_ID           int,
248
RATE              int,
249
RATE_ORIGIN      	VARCHAR(1),
250
PRODUCT_NAME      varchar(32),
251
CHARGE_TYPE       varchar(16),
252
SERVICE_EVENT_ID  int,
253
PAYMENT_BILL_ID   int,
254
SALES_ID          int,
255
PARTNER_ID        varchar(30),
256
DEBIT_ACCOUNT     varchar(20),
257
CREDIT_ACCOUNT    varchar(20),
258
BILL_STATUS      	VARCHAR(1),
259
SERVICE_AMOUNT    int,
260
TRADE_FROM        varchar(100),
261
DEBIT_USER_ID     varchar(16),
262
SERVICT_TYPE      varchar(8),
263
SUB_TRADE_NO      varchar(64),
264
primary key (TRADE_NO, SERVICT_TYPE,	CHARGE_TYPE, SUB_TRADE_NO)
265
)DEFAULT CHARSET = 'utf8';
266
CREATE INDEX TRADE_CHARGE_000_GMT_IND ON TRADE_CHARGE_000
267
(GMT_MODIFIED);
268
CREATE TABLE if not exists TRADE_EXT_000 
269
(
270
TRADE_NO               varchar(64)    NOT NULL,
271
EXT_INFO               varchar(1000),
272
TRADE_AGENT            varchar(30),
273
GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
274
FP_TIME_OUT_RULE       varchar(1000),
275
FOREX_RATE_ID          int,
276
FOREX_CURRENCY         varchar(8),
277
FOREX_TOTAL_FEE        int,
278
GOODS_INNER_SELL_RATE  int,
279
PREPARE_COUPON_FEE     int,
280
PREPARE_COUPON_LIST    varchar(64),
281
FOREX_CERT_NO          varchar(128),
282
GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
283
AUTH_DEBIT_TYPE        varchar(1),
284
SUPPORT_CREDIT_CARD    varchar(256),
285
SPECIFIED_PAY_CHANNEL  varchar(128),
286
CHARGE_CHANNEL_INFO    varchar(256),
287
PAY_CHANNELS           varchar(900),
288
PRIMARY KEY (TRADE_NO)
289
)DEFAULT CHARSET = 'utf8';
290
CREATE INDEX TRADE_EXT_000_GMT_IND ON TRADE_EXT_000 (GMT_MODIFIED);
291
CREATE TABLE if not exists TRADE_FUND_BILL_000 
292
(
293
BILL_NO             varchar(128)   NOT NULL,
294
TRADE_NO            varchar(64)    NOT NULL,
295
PAY_ACCOUNT_NO      varchar(20)    NOT NULL,
296
RECEIVE_ACCOUNT_NO  varchar(20)    NOT NULL,
297
CURRENCY            varchar(3)    default '156' NOT NULL,
298
AMOUNT              int           NOT NULL,
299
TYPE               	VARCHAR(2)         NOT NULL,
300
STATUS             	VARCHAR(1)         NOT NULL,
301
CHANNEL            	VARCHAR(2)         NOT NULL,
302
GMT_PAY             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
303
IW_TRANS_LOG_ID     int,
304
GMT_TRADE_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
305
GMT_CREATE          TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
306
GMT_MODIFIED        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
307
MEMO                varchar(1000),
308
BANK_PAY_ONLINE_ID  int,
309
BANK_AMOUNT         int,
310
BANK_TYPE           varchar(32),
311
FREEZE_TYPE         varchar(5),
312
OUT_ORDER_NO        varchar(64),
313
PARTNER_ID          varchar(30),
314
DEPOSIT_ID          varchar(16),
315
BILL_EXT            varchar(200),
316
PAY_ORDER_NO        varchar(64),
317
primary key (TRADE_NO,BILL_NO)
318
)DEFAULT CHARSET = 'utf8';
319
CREATE INDEX BYD_TRADE_FUND_BILL_PF_IND ON TRADE_FUND_BILL_000 
320
(PAY_ACCOUNT_NO, GMT_TRADE_CREATE);
321
CREATE INDEX BYD_TRADE_FUND_BILL_RF_IND ON TRADE_FUND_BILL_000 
322
(RECEIVE_ACCOUNT_NO, GMT_TRADE_CREATE);
323
CREATE INDEX TRADE_FUND_BILL_PTG_IND ON TRADE_FUND_BILL_000 
324
(PARTNER_ID, GMT_TRADE_CREATE);
325
CREATE INDEX TRADE_FUND_BILL_000_GMT_IND ON TRADE_FUND_BILL_000
326
(GMT_MODIFIED);
327
CREATE TABLE if not exists TRADE_GOODS_INFO_000 
328
(
329
ID              int           NOT NULL,
330
TRADE_NO        varchar(64)    NOT NULL,
331
GOODS_ID        varchar(32)    default ' ' NOT NULL,
332
GOODS_TITLE     varchar(256),
333
GOODS_BID       int         default 0 NOT NULL,
334
GOODS_QUANTITY  int         default 1 NOT NULL,
335
DETAIL_URL      varchar(400),
336
GOODS_MEMO      varchar(1000),
337
REFER_URL       varchar(400),
338
PARTNER         varchar(100),
339
GMT_CREATE      TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
340
OTHER_FEE       int,
341
CATEGORY        varchar(24),
342
PROMOTION_DESC  varchar(256),
343
PIC_URL         varchar(400),
344
GOODS_TYPE      VARCHAR(4),
345
GOODS_SUB_TYPE  VARCHAR(4),
346
primary key (TRADE_NO, ID)
347
)DEFAULT CHARSET = 'utf8';
348
CREATE TABLE if not exists TRADE_LOGISTICS_000
349
(
350
ID                    int          NOT NULL,
351
TRADE_NO              VARCHAR(64)   NOT NULL,
352
LOGISTICS_ID          VARCHAR(20),
353
LOGISTICS_NAME        VARCHAR(128),
354
INVOICE_NO            VARCHAR(64),
355
SIGN_VOUCHER_NO       VARCHAR(64),
356
TRANSPORT_TYPE        int,
357
TRANSPORT_FEE         int    default 0 NOT NULL, 
358
OTHER_FEE             int    default 0,
359
ADDITIONAL_SERVICE    VARCHAR(300),
360
LOGISTICS_STATUS      VARCHAR(100),
361
SEND_FULLNAME         VARCHAR(256),
362
SEND_PHONE            VARCHAR(30),
363
SEND_MOBILE_PHONE     VARCHAR(30),
364
SEND_ADDRESS          VARCHAR(256),
365
SEND_POST             VARCHAR(20),
366
RECEIVE_FULLNAME      VARCHAR(256),
367
RECEIVE_PHONE         VARCHAR(30),
368
RECEIVE_MOBILE_PHONE  VARCHAR(30),
369
RECEIVE_ADDRESS       VARCHAR(256),
370
RECEIVE_POST          VARCHAR(20),
371
LOGISTICS_MEMO        VARCHAR(600),
372
TRADE_OR_REFUND_FLAG  int         default 1 NOT NULL,
373
GMT_CREATE            TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
374
GMT_APPLY             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
375
GMT_FETCH             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
376
GMT_TRANSPORT         TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
377
GMT_SIGN              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
378
GMT_MODIFIED          TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
379
SEND_IM_TYPE          VARCHAR(10),
380
SEND_IM_ID            VARCHAR(100),
381
RECEIVE_IM_TYPE       VARCHAR(10),
382
RECEIVE_IM_ID         VARCHAR(100),
383
TRANSPORT_PAYMENT     int,
384
TRANSPORT_MODIFIABLE  int,
385
RECEIVE_ADDRESS_CODE  VARCHAR(8),
386
SEND_ADDRESS_CODE     VARCHAR(8),
387
TRANSPORT_TYPE_SEND   int,
388
LOGISTICS_NO          VARCHAR(100),
389
TRANS_GOODS_INFO      VARCHAR(400),
390
REC_ADDRESS_NO        VARCHAR(100),
391
FLAG_MAIN             int,
392
primary key (TRADE_NO, TRADE_OR_REFUND_FLAG, ID)
393
)DEFAULT CHARSET = 'utf8';
394
CREATE INDEX TRADE_LOGISTICS_000_GMT_IND ON TRADE_LOGISTICS_000
395
(GMT_MODIFIED);
396
CREATE TABLE if not exists TRADE_MESSAGE_BOARD_000 
397
(
398
ID           int           NOT NULL,
399
TRADE_NO     varchar(64)    NOT NULL,
400
MESSAGE      varchar(2000)  NOT NULL,
401
POSTER       varchar(100)  default ' ' NOT NULL,
402
POSTER_ROLE  int,
403
OPERATOR_IP  varchar(15),
404
MSG_TYPE     int    default 0,
405
IS_READ     	int   default 0 NOT NULL,
406
GMT_CREATE   TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
407
PRIMARY KEY (TRADE_NO, ID)
408
) DEFAULT CHARSET = 'utf8';
409
CREATE TABLE if not exists TRADE_NOTE_000 
410
(
411
ID              int          NOT NULL,
412
TRADE_NO        varchar(64)   NOT NULL,
413
TRADE_STATUS    varchar(64)   default ' ',
414
BUYER_EMAIL     varchar(100)  default ' ' NOT NULL,
415
SELLER_EMAIL    varchar(100)  default ' ' NOT NULL,
416
OPERATOR_EMAIL  varchar(100)  default ' ' NOT NULL,
417
OPERATOR_IP     varchar(15),
418
GMT_CREATE      TIMESTAMP(6) default "2012-01-01 12:00:00",
419
OPERATOR_MEMO   varchar(256),
420
NOTE_TYPE       varchar(100),
421
REMOTE_MAC      varchar(64),
422
primary key (TRADE_NO, ID, GMT_CREATE)
423
)DEFAULT CHARSET = 'utf8';
424
CREATE TABLE if not exists TRADE_OUTTRADE_000 
425
(
426
PARTNER_ID    varchar(200)  NOT NULL,
427
OUT_TRADE_NO  varchar(64)   NOT NULL,
428
TRADE_FROM    varchar(100)  NOT NULL,
429
TRADE_NO      varchar(64)   NOT NULL,
430
GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00"       NOT NULL,
431
primary key (OUT_TRADE_NO, PARTNER_ID, TRADE_FROM)
432
) DEFAULT CHARSET = 'utf8';
433
CREATE TABLE if not exists TRADE_PAYMENT_CLAUSE_000 
434
(
435
ID                     int           NOT NULL,
436
OUT_RELATION_ID        varchar(64),
437
SERIAL_NO              int,
438
TRADE_NO               varchar(64)    NOT NULL,
439
GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
440
GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
441
PAYMENT_TYPE           varchar(64)    NOT NULL,
442
STATUS                 varchar(50),
443
PAY_ACCOUNT_NO         varchar(20),
444
PAY_USER_ID            varchar(16),
445
RECEIVE_ACCOUNT_NO     varchar(20),
446
RECEIVE_USER_ID        varchar(16),
447
AMOUNT                 int,
448
CURRENCY               varchar(64),
449
PAYMENT_RESTRICT_RULE  varchar(1000),
450
DESCRIPTION            varchar(1000),
451
MEMO                   varchar(1000),
452
CONTEXT                varchar(1000),
453
primary key (TRADE_NO, OUT_RELATION_ID)
454
)DEFAULT CHARSET = 'utf8';
455
CREATE INDEX TRADE_PCLAUSE_000_GMT_IND ON TRADE_PAYMENT_CLAUSE_000 
456
(GMT_MODIFIED);
457
CREATE TABLE if not exists TRADE_PAYMENT_ORDER_000 
458
(
459
ORDER_NO            varchar(64)    NOT NULL,
460
TRADE_NO            varchar(64)    NOT NULL,
461
BIZ_TYPE            varchar(20)    NOT NULL,
462
OUT_ORDER_NO        varchar(64),
463
GMT_MODIFIED        TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
464
STATUS              varchar(2)     NOT NULL,
465
PAY_ACCOUNT_NO      varchar(20)    NOT NULL,
466
RECEIVE_ACCOUNT_NO  varchar(20)    NOT NULL,
467
AMOUNT              int           NOT NULL,
468
RETURN_AMOUNT       int,
469
CURRENCY            varchar(3)     NOT NULL,
470
FREEZE_TYPE         varchar(5),
471
EXT_INFO            varchar(1000),
472
MEMO                varchar(1000),
473
PAYMENT_ID          varchar(64),
474
PAYMENT_CONTEXT     varchar(1000),
475
GMT_PAY             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
476
GMT_CREATE          TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
477
FREEZE_AMOUNT       int,
478
SUB_TRANS_CODE      varchar(40)    NOT NULL,
479
primary key (TRADE_NO, ORDER_NO)
480
)DEFAULT CHARSET = 'utf8';
481
CREATE INDEX TRADE_PAYMENT_ORD_000_GM_IND ON TRADE_PAYMENT_ORDER_000
482
(GMT_MODIFIED);
483
CREATE TABLE if not exists TRADE_PROCESS_000 
484
(
485
ID                        int           NOT NULL,
486
TRADE_NO                  varchar(64)   default ' ' NOT NULL,
487
OUT_TRADE_NO              varchar(64),
488
RECEIVE_FEE               int           default 0 NOT NULL,
489
SEND_FEE                  int           default 0 NOT NULL,
490
ADJUST_FEE                int           default 0 NOT NULL,
491
MEMO                      varchar(600),
492
OPERATE_TIME              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
493
OPERATE_NAME              varchar(256),
494
FLAG_REFUND               varchar(4)    default '0' NOT NULL,
495
FLAG_DEFERPAY             varchar(4)    default '0' NOT NULL,
496
FLAG_PAYMENT              varchar(8)    default '0' NOT NULL,
497
FLAG_TRADE_LOCKED         varchar(4)    default '0' NOT NULL,
498
FLAG_SELLER_MDF           varchar(4)    default '0' NOT NULL,
499
GMT_CREATE                TIMESTAMP(6) default "2012-01-01 12:00:00"                ,
500
GMT_MODIFIED              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
501
GMT_RECEIVE_PAY           TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
502
GMT_SEND_PAY              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
503
GMT_SEND_GOODS            TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
504
TIME_OUT_RULE             varchar(1000),
505
GMT_OUT_TRADE_NO_CREATED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
506
COUPON_CLEAR_FEE          int,
507
COUPON_ADJUST_FEE         int,
508
REFUND_FEE                int,
509
GMT_REFUND_PAY            TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
510
GMT_FINISH                TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
511
ACCOUNT_TX_ID             varchar(40),
512
ADJUST_TIMEOUT            int,
513
GMT_PRE_AUTH              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
514
PRE_AUTH_FEE              int,
515
ACTUAL_ADJUST_FEE         int,
516
EXPIRE_TIME               TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
517
SEND_BACK_FEE             int,
518
PRIMARY KEY (TRADE_NO)
519
)DEFAULT CHARSET = 'utf8';
520
CREATE INDEX IND_BYD_TRD_PROCESS_GRPAY ON TRADE_PROCESS_000 
521
(GMT_RECEIVE_PAY, RECEIVE_FEE);
522
CREATE INDEX TRADE_PROCESS_GMT_IND ON TRADE_PROCESS_000
523
(GMT_MODIFIED);
524
CREATE TABLE if not exists TRADE_REFUND_000 
525
(
526
ID                  int           NOT NULL,
527
TRADE_NO            varchar(64)    NOT NULL,
528
OUT_TRADE_NO        varchar(64),
529
RETURN_STATUS       varchar(100),
530
MEMO                varchar(2000),
531
RETURN_FEE          int,
532
GMT_ADMIN_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
533
ADMIN_STATUS        varchar(100),
534
RETURN_TYPE         varchar(20)    NOT NULL,
535
RETURN_GOODS        int,
536
GOODS_STATUS        int,
537
CLOSE_CAUSE         varchar(600),
538
REQUIRE_VOUCHER     int,
539
GMT_CREATE          TIMESTAMP(6) default "2012-01-01 12:00:00"                NOT NULL,
540
GMT_MODIFIED        TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
541
SELLER_NOT_RECEIVE  int,
542
REFUND_REASON       varchar(400),
543
APPEAL_NO           varchar(50),
544
GMT_DW_SYNC         TIMESTAMP(6) default "2012-01-01 12:00:00"        ,
545
BUY_USER_ID         varchar(16)    NOT NULL,
546
BUY_ACCOUNT         varchar(20)    NOT NULL,
547
SELL_USER_ID        varchar(16)    NOT NULL,
548
SELL_ACCOUNT        varchar(20)    NOT NULL,
549
TRADE_FROM          varchar(100),
550
TRADE_EMAIL         varchar(100),
551
IS_INPOUR_REFUND   	VARCHAR(1),
552
REFUND_FLOW_TYPE   	VARCHAR(1),
553
GMT_LAST_APPLY      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
554
REFUND_FOREX_FEE    int,
555
PRIMARY KEY (TRADE_NO, ID)
556
)DEFAULT CHARSET = 'utf8';
557
CREATE INDEX TRADE_REFUND_BG_IND ON TRADE_REFUND_000 
558
(BUY_ACCOUNT, GMT_CREATE);
559
CREATE INDEX TRADE_REFUND_SG_IND ON TRADE_REFUND_000 
560
(SELL_ACCOUNT, GMT_CREATE);
561
CREATE INDEX TRADE_REFUND_TG_IND ON TRADE_REFUND_000 
562
(TRADE_EMAIL, GMT_CREATE);
563
CREATE INDEX TRADE_REFUND_000_GMT_IND ON TRADE_REFUND_000 (GMT_MODIFIED);
564
CREATE TABLE if not exists TRADE_VOUCHER_000 
565
(
566
ID                     int           NOT NULL,
567
TRADE_NO               varchar(64)   default ' '  NOT NULL,
568
REFUND_ID              int           NOT NULL,
569
SELLER_OR_BUYER        VARCHAR(1)    default 'S' NOT NULL,
570
SELLER_UPLOAD_VOUCHER  varchar(4000),
571
BUYER_UPLOAD_VOUCHER   varchar(4000),
572
UPLOAD_FLAG            int           default 0 NOT NULL,
573
GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
574
GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
575
GMT_CHECK              TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
576
LAST_OPERATOR          varchar(32),
577
CHECK_MEMO             varchar(2000),
578
MEMEO                  varchar(4000),
579
GMT_TIMEOUT_START      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
580
PRIMARY KEY (TRADE_NO, ID)
581
) DEFAULT CHARSET = 'utf8';
582
CREATE INDEX TRADE_VOUCHER_000_MT_IND ON TRADE_VOUCHER_000
583
(GMT_MODIFIED);
584
CREATE TABLE if not exists TRADE_RECEIVE_ADDRESS_000
585
(
586
TRADE_NO          VARCHAR(64)   NOT NULL,
587
ADDRESS_NO        VARCHAR(100)  NOT NULL,
588
RECEIVE_NAME      VARCHAR(256),
589
ADDRESS_CODE      VARCHAR(8),
590
ADDRESS           VARCHAR(256),
591
MOBILE_PHONE      VARCHAR(30),
592
PHONE             VARCHAR(30),
593
RECEIVE_POST      VARCHAR(20),
594
VIRTUAL_REC_INFO  VARCHAR(400),
595
GMT_CREATE        DATETIME                NOT NULL,
596
GMT_MODIFIED      DATETIME,
597
PRIMARY KEY (TRADE_NO, ADDRESS_NO)
598
)DEFAULT CHARSET = 'utf8';
599
CREATE TABLE if not exists TRADE_COOPERATE_000
600
(
601
TRADE_NO         VARCHAR(64),
602
COOPERATE_NO     VARCHAR(64),
603
COOPERATOR_FROM  VARCHAR(32),
604
GMT_CREATE       TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
605
GMT_MODIFIED     TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
606
PRIMARY key (TRADE_NO, COOPERATE_NO, COOPERATOR_FROM)
607
)DEFAULT CHARSET = 'utf8';
608
CREATE INDEX TRADE_COOPERATE_000_MT_IND ON TRADE_COOPERATE_000
609
(GMT_MODIFIED);
610
CREATE TABLE if not exists TRADE_SETTLE_ORDER_000
611
(
612
ORDER_NO            VARCHAR(64)    NOT NULL,
613
ORG_ORDER_NO        VARCHAR(64),
614
TRADE_NO            VARCHAR(64)    NOT NULL,
615
OUT_REQUEST_NO      VARCHAR(64),
616
BIZ_PRODUCT         VARCHAR(32),
617
SETTLE_ACTION_TYPE  VARCHAR(32),
618
AMOUNT              int           NOT NULL,
619
STATUS              VARCHAR(50)    NOT NULL,
620
EXTINFO             VARCHAR(1024),
621
MEMO                VARCHAR(512),
622
SETTLE_ID           VARCHAR(64),
623
SETTLE_CONTEXT      VARCHAR(1024),
624
GMT_CREATE          TIMESTAMP(6)         NOT NULL,
625
GMT_MODIFIED        TIMESTAMP(6),
626
SETTLE_TYPE         VARCHAR(32),
627
PRIMARY KEY (TRADE_NO, ORDER_NO, GMT_CREATE)
628
)DEFAULT CHARSET = 'utf8';
629
CREATE TABLE if not exists TRADE_PARTICIPANT_000
630
(
631
TRADE_NO          VARCHAR(64)   NOT NULL,
632
USER_ID           VARCHAR(16)   NOT NULL,
633
USER_NAME         VARCHAR(256),
634
USER_NICK         VARCHAR(100),
635
PARTICIPANT_ROLE  VARCHAR(64)   NOT NULL,
636
MEMO              VARCHAR(200),
637
GMT_CREATE        TIMESTAMP(6) default "2012-01-01 12:00:00"              NOT NULL,
638
GMT_MODIFIED      TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
639
PRIMARY KEY (TRADE_NO, USER_ID, PARTICIPANT_ROLE)
640
)DEFAULT CHARSET = 'utf8';
641
CREATE TABLE if not exists BUSINESS_ACTIVITY_ASYNC_000
642
(
643
TX_ID                VARCHAR(128)   NOT NULL,
644
STATE                VARCHAR(1)         NOT NULL,
645
ACCOUNT_TRANS_STATE  VARCHAR(1)         NOT NULL,
646
GMT_CREATE           TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
647
GMT_MODIFIED         TIMESTAMP(6) default "2012-01-01 12:00:00"        NOT NULL,
648
PROPAGATION          VARCHAR(1),
649
CONTEXT              VARCHAR(2048),
650
PRIMARY KEY (TX_ID)
651
)DEFAULT CHARSET = 'utf8';
652
CREATE TABLE if not exists EXTERFACE_INVOKE_000
653
(
654
ID           int         NOT NULL,
655
PARTNER_ID   VARCHAR(32)   NOT NULL,
656
TARGET       VARCHAR(64)   NOT NULL,
657
EXTERFACE    VARCHAR(128)  NOT NULL,
658
NOTIFY_URL   VARCHAR(200),
659
RETURN_URL   VARCHAR(200),
660
SIGN_TYPE    VARCHAR(10),
661
CHARSET      VARCHAR(20),
662
GMT_INVOKE   TIMESTAMP(6) default "2012-01-01 12:00:00"       NOT NULL,
663
GMT_FINISH   TIMESTAMP(6) default "2012-01-01 12:00:00"      NOT NULL,
664
TARGET_TYPE  VARCHAR(20)   NOT NULL,
665
GMT_NOTIFY   TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
666
GMT_RETURN   TIMESTAMP(6) default "2012-01-01 12:00:00" NULL,
667
PRIMARY KEY(TARGET_TYPE, TARGET, PARTNER_ID)
668
)DEFAULT CHARSET = 'utf8';
669
CREATE INDEX EXTERFACE_INVOKE_000_ID_IND ON EXTERFACE_INVOKE_000(ID);
670
merge happens
671
insert into notify_history_000(id,plugin,parameter,group_no,status,gmt_create,gmt_modify) values('1111','1','1bdc',-2,'1','2014-09-19 12:00:00','2014-09-19 11:11:11');
672
insert into notify_history_000(id,plugin,parameter,group_no,status,gmt_create,gmt_modify) values('2111','1','1bdc',-2,'1','2014-09-19 12:00:00','2014-09-19 11:11:11');
673
insert into notify_history_000(id,plugin,parameter,group_no,status,gmt_create,gmt_modify) values('3111','1','1bdc',-2,'1','2014-09-19 12:00:00','2014-09-19 11:11:11');
674
update  notify_history_000 set group_no=-1 , iw_user_id = '1111' , status ='N';
675
select id from notify_history_000 where group_no = -1 and iw_user_id = '1111';
676
id
677
1111
678
2111
679
3111
680
select id, plugin, plugin_host, parameter, group_no, status, gmt_create, gmt_local_create, gmt_modify, iw_user_id, channel_endpoint, business_order, business_type from notify_000 where (id = '1111') for update;
681
id	plugin	plugin_host	parameter	group_no	status	gmt_create	gmt_local_create	gmt_modify	iw_user_id	channel_endpoint	business_order	business_type
682
select id, plugin, plugin_host, parameter, group_no, status, gmt_create, gmt_local_create, gmt_modify, iw_user_id, channel_endpoint, business_order, business_type from notify_000 where ((id = '1111') AND (status IN ('N', 'C')));
683
id	plugin	plugin_host	parameter	group_no	status	gmt_create	gmt_local_create	gmt_modify	iw_user_id	channel_endpoint	business_order	business_type
684
select id, plugin, plugin_host, parameter, group_no, status, gmt_create, gmt_local_create, gmt_modify, iw_user_id, channel_endpoint, business_order, business_type from notify_000 where ((id = '1111') AND (status IN ('N', 'C'))) for update;
685
id	plugin	plugin_host	parameter	group_no	status	gmt_create	gmt_local_create	gmt_modify	iw_user_id	channel_endpoint	business_order	business_type
686
select /* QUERY_TIMEOUT(15000000) */ id from notify_000 where  gmt_create <= current_timestamp() and gmt_create >= date_sub(current_timestamp(), INTERVAL 6 MINUTE) and gmt_modify < now() and status in ('N', 'C') and group_no != -1;
687
id
688
select /* QUERY_TIMEOUT(15000000) */ id from notify_000 where  gmt_create <= current_timestamp() and gmt_create >= date_sub(current_timestamp(), INTERVAL 6 MINUTE) and gmt_modify < now() and status in ('N', 'C') and group_no = -1;
689
id
690
select /* QUERY_TIMEOUT(15000000) */ id, business_order, business_type from notify_000 where gmt_create <= current_timestamp() AND gmt_create >= date_sub(current_timestamp(), INTERVAL 6 MINUTE) AND ((status = 'N') AND (gmt_modify < current_timestamp())) limit 1;
691
id	business_order	business_type
692
select id, plugin, plugin_host, parameter, group_no, status, gmt_create, gmt_local_create, gmt_modify, iw_user_id, channel_endpoint, business_order, business_type from notify_000 where (iw_user_id ='1111') and (gmt_create < current_timestamp()) and (gmt_create >= current_timestamp());
693
id	plugin	plugin_host	parameter	group_no	status	gmt_create	gmt_local_create	gmt_modify	iw_user_id	channel_endpoint	business_order	business_type
694
delete from notify_history_000 where ((group_no = (- 1)) AND (iw_user_id = '1111'));
695