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 trade_sequence_000;
11
DROP TABLE if exists BUSINESS_ACTION_000; 
12
DROP TABLE if exists BUSINESS_ACTIVITY_000; 
13
DROP TABLE if exists NOTIFY_000 ;
14
DROP TABLE if exists NOTIFY_HISTORY_000 ;
15
DROP TABLE if exists TIMEOUT_000 ;
16
DROP TABLE if exists TIMEOUT_HISTORY_000 ;
17
DROP TABLE if exists TIMEOUT_LONG_000 ;
18
DROP TABLE if exists TIMEOUT_REMIND_000; 
19
DROP TABLE if exists TRADE_BASE_000 ;
20
DROP TABLE if exists TRADE_CHARGE_000; 
21
DROP TABLE if exists TRADE_EXT_000 ;
22
DROP TABLE if exists TRADE_FUND_BILL_000 ;
23
DROP TABLE if exists TRADE_GOODS_INFO_000 ;
24
DROP TABLE if exists TRADE_LOGISTICS_000;
25
DROP TABLE if exists TRADE_MESSAGE_BOARD_000 ;
26
DROP TABLE if exists TRADE_NOTE_000 ;
27
DROP TABLE if exists TRADE_OUTTRADE_000 ;
28
DROP TABLE if exists TRADE_PAYMENT_CLAUSE_000 ;
29
DROP TABLE if exists TRADE_PAYMENT_ORDER_000 ;
30
DROP TABLE if exists TRADE_PROCESS_000 ;
31
DROP TABLE if exists TRADE_REFUND_000 ;
32
DROP TABLE if exists TRADE_VOUCHER_000 ;
33
DROP TABLE if exists TRADE_RECEIVE_ADDRESS_000;
34
DROP TABLE if exists TRADE_COOPERATE_000;
35
DROP TABLE if exists TRADE_SETTLE_ORDER_000;
36
DROP TABLE if exists TRADE_PARTICIPANT_000;
37
DROP TABLE if exists BUSINESS_ACTIVITY_ASYNC_000;
38
DROP TABLE if exists EXTERFACE_INVOKE_000;
39
--enable_warnings
40

41
CREATE TABLE if not exists trade_sequence_000
42
(
43
 name varchar(256),
44
 value int,
45
 min_value int,
46
 max_value int,
47
 step int,
48
 gmt_create TIMESTAMP(6) default "2012-01-01 12:00:00",
49
 gmt_modified TIMESTAMP(6) default "2012-01-01 12:00:00",
50
 primary key (name)
51
) DEFAULT CHARSET = 'utf8';
52

53

54
CREATE TABLE if not exists BUSINESS_ACTION_000 
55
(
56
  ACTION_ID     int           NOT NULL,
57
  TX_ID         varchar(128)   NOT NULL,
58
  NAME          varchar(64)    NOT NULL,
59
  STATE         CHAR(1)         NOT NULL,
60
  GMT_CREATE    TIMESTAMP         NOT NULL,
61
  GMT_MODIFIED  TIMESTAMP         NOT NULL,
62
  CONTEXT       varchar(4000),
63
  primary key (ACTION_ID)
64
) DEFAULT CHARSET = 'utf8';
65

66
CREATE TABLE if not exists BUSINESS_ACTIVITY_000 
67
(
68
  TX_ID                varchar(128)   NOT NULL,
69
  STATE               	VARCHAR(1)         NOT NULL,
70
  ACCOUNT_TRANS_STATE 	VARCHAR(1)         NOT NULL,
71
  GMT_CREATE           TIMESTAMP         NOT NULL,
72
  GMT_MODIFIED         TIMESTAMP         NOT NULL,
73
  PROPAGATION         	VARCHAR(1),
74
  CONTEXT              varchar(2048),
75
  primary key (TX_ID)
76
) DEFAULT CHARSET = 'utf8';
77

78
CREATE TABLE if not exists NOTIFY_000 
79
(
80
  ID                varchar(34)    NOT NULL,
81
  PLUGIN            varchar(50)    NOT NULL,
82
  PARAMETER         varchar(4000)  NOT NULL,
83
  GROUP_NO          int            NOT NULL,
84
  STATUS           	VARCHAR(1)         NOT NULL,
85
  GMT_CREATE        TIMESTAMP         NOT NULL,
86
  GMT_MODIFY        TIMESTAMP         NOT NULL,
87
  IW_USER_ID        varchar(16),
88
  CHANNEL_ENDPOINT  varchar(200),
89
  BUSINESS_ORDER    varchar(128),
90
  BUSINESS_TYPE     varchar(50),
91
  PLUGIN_HOST       varchar(15),
92
  GMT_LOCAL_CREATE  TIMESTAMP(6) default "2012-01-01 12:00:00",
93
  primary key (id)
94
)DEFAULT CHARSET = 'utf8';
95

96
CREATE INDEX NOTIFY_BUSINESS_ORDER1_IND ON NOTIFY_000 (BUSINESS_ORDER);
97
CREATE INDEX NOTIFY_PLUGIN_IND ON NOTIFY_000 (PLUGIN, CHANNEL_ENDPOINT);
98

99
CREATE TABLE if not exists NOTIFY_HISTORY_000 
100
(
101
  ID                varchar(34)    NOT NULL,
102
  PLUGIN            varchar(50)    NOT NULL,
103
  PARAMETER         varchar(4000)  NOT NULL,
104
  GROUP_NO          int            NOT NULL,
105
  STATUS           	VARCHAR(1)         NOT NULL,
106
  GMT_CREATE        TIMESTAMP         NOT NULL,
107
  GMT_MODIFY        TIMESTAMP         NOT NULL,
108
  IW_USER_ID        varchar(16),
109
  CHANNEL_ENDPOINT  varchar(200),
110
  BUSINESS_ORDER    varchar(128),
111
  BUSINESS_TYPE     varchar(50),
112
  PLUGIN_HOST       varchar(15),
113
  GMT_LOCAL_CREATE  TIMESTAMP(6) default "2012-01-01 12:00:00",
114
  PRIMARY KEY (ID)
115
)DEFAULT CHARSET = 'utf8';
116
CREATE INDEX NOTIFY_HISTORY_000_BORDER_IND ON NOTIFY_HISTORY_000 (BUSINESS_ORDER);
117
CREATE INDEX NOTIFY_HISTORY_IG_IND ON NOTIFY_HISTORY_000 (IW_USER_ID, GROUP_NO);
118

119
CREATE TABLE if not exists TIMEOUT_000 
120
(
121
  JOB_ID        varchar(32)   NOT NULL,
122
  TRADE_NO      varchar(64),
123
  USER_ID       varchar(128),
124
  PAYTYPE       varchar(4),
125
  END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00",
126
  ACTION        varchar(30),
127
  PARAMETER     varchar(100),
128
  STATUS       	VARCHAR(1),
129
  ACTION_TYPE  	VARCHAR(1),
130
  GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00",
131
  PRIOR_LEVEL   int default 1,
132
  GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00",
133
  GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00",
134
  PRIMARY KEY (JOB_ID)
135
) DEFAULT CHARSET = 'utf8';
136

137
CREATE INDEX IND_BEYOND_TIMEOUT_ETIME ON TIMEOUT_000 (END_TIME);
138
CREATE INDEX TIMEOUT_000_PL_IND ON TIMEOUT_000 (PRIOR_LEVEL);
139
CREATE INDEX TIMEOUT_TNO_IND ON TIMEOUT_000 (TRADE_NO);
140

141
CREATE TABLE if not exists TIMEOUT_HISTORY_000 
142
(
143
  JOB_ID        varchar(32)   NOT NULL,
144
  TRADE_NO      varchar(64)   NOT NULL,
145
  USER_ID       varchar(128),
146
  PAYTYPE       varchar(4),
147
  END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00",
148
  ACTION        varchar(30),
149
  PARAMETER     varchar(100),
150
  STATUS       	VARCHAR(1),
151
  ACTION_TYPE  	VARCHAR(1)        NOT NULL,
152
  GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00",
153
  PRIOR_LEVEL   int,
154
  GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00",
155
  GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00",
156
  PRIMARY KEY (JOB_ID, TRADE_NO, ACTION_TYPE)
157
) DEFAULT CHARSET = 'utf8';
158

159
CREATE TABLE if not exists TIMEOUT_LONG_000 
160
(
161
  JOB_ID        varchar(32)   NOT NULL,
162
  TRADE_NO      varchar(64),
163
  USER_ID       varchar(128),
164
  PAYTYPE       varchar(4),
165
  END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00",
166
  ACTION        varchar(30),
167
  PARAMETER     varchar(100),
168
  STATUS       	VARCHAR(1),
169
  ACTION_TYPE  	VARCHAR(1),
170
  GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00",
171
  PRIOR_LEVEL   int          ,
172
  GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00",
173
  GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00",
174
  PRIMARY KEY (JOB_ID)
175
) DEFAULT CHARSET = 'utf8';
176
CREATE INDEX BYD_TIMEOUT_LONG_ENDTIME_IND ON TIMEOUT_LONG_000 (END_TIME);
177
CREATE INDEX TIMEOUT_LONG_000_PL_IND ON TIMEOUT_LONG_000 (PRIOR_LEVEL);
178
CREATE INDEX TIMEOUT_LONG_TNO_IND ON TIMEOUT_LONG_000 (TRADE_NO);
179

180

181
CREATE TABLE if not exists TIMEOUT_REMIND_000 
182
(
183
  JOB_ID        varchar(32)   NOT NULL,
184
  TRADE_NO      varchar(64),
185
  USER_ID       varchar(128),
186
  PAYTYPE       varchar(4),
187
  END_TIME      TIMESTAMP(6) default "2012-01-01 12:00:00",
188
  ACTION        varchar(30),
189
  PARAMETER     varchar(100),
190
  STATUS       	VARCHAR(1),
191
  ACTION_TYPE  	VARCHAR(1),
192
  GMT_CREATE    TIMESTAMP(6) default "2012-01-01 12:00:00",
193
  PRIOR_LEVEL   int          ,
194
  GMT_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00",
195
  GMT_OCCUR     TIMESTAMP(6) default "2012-01-01 12:00:00",
196
  PRIMARY KEY (JOB_ID)
197
) DEFAULT CHARSET = 'utf8';
198

199

200
CREATE INDEX BY_TIMEOUT_REMIND_ETIME_IND ON TIMEOUT_REMIND_000 (END_TIME);
201

202
CREATE INDEX TIMEOUT_REMIND_000_PL_IND ON TIMEOUT_REMIND_000 (PRIOR_LEVEL);
203

204
CREATE INDEX TIMEOUT_REMIND_TNO_IND ON TIMEOUT_REMIND_000 (TRADE_NO);
205

206
CREATE TABLE if not exists TRADE_BASE_000 
207
(
208
 ID                     int           NOT NULL,
209
  TRADE_NO               varchar(64)    NOT NULL,
210
  OUT_TRADE_NO           varchar(64),
211
  SELLER_ACCOUNT         varchar(100)   NOT NULL,
212
  SELLER_LOGIN_EMAIL     varchar(100)   NOT NULL,
213
  BUYER_ACCOUNT          varchar(100)   NOT NULL,
214
  BUYER_LOGIN_EMAIL      varchar(100)   NOT NULL,
215
  SELLER_TYPE            int,
216
  BUYER_TYPE             int,
217
  TRADE_FROM             varchar(100),
218
  TRADE_EMAIL            varchar(100),
219
  OPERATOR_ROLE          VARCHAR(1)     NOT NULL,
220
  TRADE_STATUS           varchar(50)    NOT NULL,
221
  TOTAL_FEE              int            default 0 NOT NULL,
222
  SERVICE_FEE_RATIO      varchar(64),
223
  SERVICE_FEE            int            default 0 NOT NULL,
224
  CURRENCY               varchar(3),
225
  SELLER_ACTION          varchar(200),
226
  BUYER_ACTION           varchar(200),
227
  GMT_CREATE             TIMESTAMP                 ,
228
  SELLER_USER_ID         varchar(16),
229
  BUYER_USER_ID          varchar(16),
230
  ADDITIONAL_TRD_STATUS  varchar(50),
231
  TRADE_TYPE             varchar(4)     NOT NULL,
232
  SELLER_FULLNAME        varchar(256)   default '',
233
  BUYER_FULLNAME         varchar(256)   default '',
234
  SELLER_NICK            varchar(100)   default '' NOT NULL,
235
  BUYER_NICK             varchar(100)   default '' NOT NULL,
236
  GOODS_TITLE            varchar(256),
237
  GMT_LAST_MODIFIED_DT   TIMESTAMP(6) default "2012-01-01 12:00:00",
238
  STOP_TIMEOUT           varchar(4) 	default '0',
239
  GATHERING_TYPE         varchar(4),
240
  BUYER_MARKER           VARCHAR(1),
241
  SELLER_MARKER          VARCHAR(1),
242
  BUYER_MARKER_MEMO      varchar(1000),
243
  SELLER_MARKER_MEMO     varchar(1000),
244
  CHANNEL                varchar(32),
245
  PRODUCT                varchar(20),
246
  PAY_CHANNEL            varchar(32),
247
  OTHER_SERVICE_FEE      int,
248
  RELATION_PRO           varchar(64),
249
  PLATFORM varchar(100),
250
  CHANNEL_PARTNER varchar(100),
251
  GOODS_TYPE      VARCHAR(4),
252
  GOODS_SUB_TYPE  VARCHAR(4),
253
  PRIMARY KEY (TRADE_NO)
254
)DEFAULT CHARSET = 'utf8';
255

256
CREATE INDEX BYD_TRADE_BASE_TEMAIL_GMTC_IND ON TRADE_BASE_000 
257
(TRADE_EMAIL, GMT_CREATE);
258

259
CREATE INDEX IND_BEYOND_TRADE_BASE_BUYGMT1 ON TRADE_BASE_000 
260
(BUYER_ACCOUNT, GMT_CREATE);
261

262
CREATE INDEX IND_BEYOND_TRADE_BASE_SALGMT1 ON TRADE_BASE_000 
263
(SELLER_ACCOUNT, GMT_CREATE);
264

265
CREATE TABLE if not exists TRADE_CHARGE_000 
266
(
267
  ID                int           NOT NULL,
268
  TRADE_NO          varchar(64)   NOT NULL,
269
  GMT_CHARGE        TIMESTAMP     NOT NULL,
270
  GMT_PAY           TIMESTAMP(6) default "2012-01-01 12:00:00",
271
  CHARGE_OUT_DATE  	VARCHAR(8),
272
  GMT_MODIFIED      TIMESTAMP(6) default "2012-01-01 12:00:00",
273
  PLAN_CHARGE       int,
274
  ACTUAL_CHARGE     int,
275
  RATE_ID           int,
276
  RATE              int,
277
  RATE_ORIGIN      	VARCHAR(1),
278
  PRODUCT_NAME      varchar(32),
279
  CHARGE_TYPE       varchar(16),
280
  SERVICE_EVENT_ID  int,
281
  PAYMENT_BILL_ID   int,
282
  SALES_ID          int,
283
  PARTNER_ID        varchar(30),
284
  DEBIT_ACCOUNT     varchar(20),
285
  CREDIT_ACCOUNT    varchar(20),
286
  BILL_STATUS      	VARCHAR(1),
287
  SERVICE_AMOUNT    int,
288
  TRADE_FROM        varchar(100),
289
  DEBIT_USER_ID     varchar(16),
290
  SERVICT_TYPE      varchar(8),
291
  SUB_TRADE_NO      varchar(64),
292
  primary key (TRADE_NO, SERVICT_TYPE,	CHARGE_TYPE, SUB_TRADE_NO)
293
)DEFAULT CHARSET = 'utf8';
294

295
CREATE INDEX TRADE_CHARGE_000_GMT_IND ON TRADE_CHARGE_000
296
(GMT_MODIFIED);
297

298
CREATE TABLE if not exists TRADE_EXT_000 
299
(
300
  TRADE_NO               varchar(64)    NOT NULL,
301
  EXT_INFO               varchar(1000),
302
  TRADE_AGENT            varchar(30),
303
  GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00",
304
  FP_TIME_OUT_RULE       varchar(1000),
305
  FOREX_RATE_ID          int,
306
  FOREX_CURRENCY         varchar(8),
307
  FOREX_TOTAL_FEE        int,
308
  GOODS_INNER_SELL_RATE  int,
309
  PREPARE_COUPON_FEE     int,
310
  PREPARE_COUPON_LIST    varchar(64),
311
  FOREX_CERT_NO          varchar(128),
312
  GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00",
313
  AUTH_DEBIT_TYPE        varchar(1),
314
  SUPPORT_CREDIT_CARD    varchar(256),
315
  SPECIFIED_PAY_CHANNEL  varchar(128),
316
  CHARGE_CHANNEL_INFO    varchar(256),
317
  PAY_CHANNELS           varchar(900),
318
  PRIMARY KEY (TRADE_NO)
319
)DEFAULT CHARSET = 'utf8';
320
CREATE INDEX TRADE_EXT_000_GMT_IND ON TRADE_EXT_000 (GMT_MODIFIED);
321

322
CREATE TABLE if not exists TRADE_FUND_BILL_000 
323
(
324
  BILL_NO             varchar(128)   NOT NULL,
325
  TRADE_NO            varchar(64)    NOT NULL,
326
  PAY_ACCOUNT_NO      varchar(20)    NOT NULL,
327
  RECEIVE_ACCOUNT_NO  varchar(20)    NOT NULL,
328
  CURRENCY            varchar(3)    default '156' NOT NULL,
329
  AMOUNT              int           NOT NULL,
330
  TYPE               	VARCHAR(2)         NOT NULL,
331
  STATUS             	VARCHAR(1)         NOT NULL,
332
  CHANNEL            	VARCHAR(2)         NOT NULL,
333
  GMT_PAY             TIMESTAMP(6) default "2012-01-01 12:00:00",
334
  IW_TRANS_LOG_ID     int,
335
  GMT_TRADE_CREATE    TIMESTAMP         NOT NULL,
336
  GMT_CREATE          TIMESTAMP         NOT NULL,
337
  GMT_MODIFIED        TIMESTAMP         NOT NULL,
338
  MEMO                varchar(1000),
339
  BANK_PAY_ONLINE_ID  int,
340
  BANK_AMOUNT         int,
341
  BANK_TYPE           varchar(32),
342
  FREEZE_TYPE         varchar(5),
343
  OUT_ORDER_NO        varchar(64),
344
  PARTNER_ID          varchar(30),
345
  DEPOSIT_ID          varchar(16),
346
  BILL_EXT            varchar(200),
347
  PAY_ORDER_NO        varchar(64),
348
  primary key (TRADE_NO,BILL_NO)
349
)DEFAULT CHARSET = 'utf8';
350

351
CREATE INDEX BYD_TRADE_FUND_BILL_PF_IND ON TRADE_FUND_BILL_000(PAY_ACCOUNT_NO, GMT_TRADE_CREATE);
352

353
CREATE INDEX BYD_TRADE_FUND_BILL_RF_IND ON TRADE_FUND_BILL_000(RECEIVE_ACCOUNT_NO, GMT_TRADE_CREATE);
354

355
CREATE INDEX TRADE_FUND_BILL_PTG_IND ON TRADE_FUND_BILL_000 (PARTNER_ID, GMT_TRADE_CREATE);
356

357
CREATE INDEX TRADE_FUND_BILL_000_GMT_IND ON TRADE_FUND_BILL_000(GMT_MODIFIED);
358

359
CREATE TABLE if not exists TRADE_GOODS_INFO_000 
360
(
361
  ID              int           NOT NULL,
362
  TRADE_NO        varchar(64)    NOT NULL,
363
  GOODS_ID        varchar(32)    default ' ' NOT NULL,
364
  GOODS_TITLE     varchar(256),
365
  GOODS_BID       int         default 0 NOT NULL,
366
  GOODS_QUANTITY  int         default 1 NOT NULL,
367
  DETAIL_URL      varchar(400),
368
  GOODS_MEMO      varchar(1000),
369
  REFER_URL       varchar(400),
370
  PARTNER         varchar(100),
371
  GMT_CREATE      TIMESTAMP         NOT NULL,
372
  OTHER_FEE       int,
373
  CATEGORY        varchar(24),
374
  PROMOTION_DESC  varchar(256),
375
  PIC_URL         varchar(400),
376
  GOODS_TYPE      VARCHAR(4),
377
  GOODS_SUB_TYPE  VARCHAR(4),
378
  primary key (TRADE_NO, ID)
379
)DEFAULT CHARSET = 'utf8';
380

381
CREATE TABLE if not exists TRADE_LOGISTICS_000
382
(
383
  ID                    int          NOT NULL,
384
  TRADE_NO              VARCHAR(64)   NOT NULL,
385
  LOGISTICS_ID          VARCHAR(20),
386
  LOGISTICS_NAME        VARCHAR(128),
387
  INVOICE_NO            VARCHAR(64),
388
  SIGN_VOUCHER_NO       VARCHAR(64),
389
  TRANSPORT_TYPE        int,
390
  TRANSPORT_FEE         int    default 0 NOT NULL, 
391
  OTHER_FEE             int    default 0,
392
  ADDITIONAL_SERVICE    VARCHAR(300),
393
  LOGISTICS_STATUS      VARCHAR(100),
394
  SEND_FULLNAME         VARCHAR(256),
395
  SEND_PHONE            VARCHAR(30),
396
  SEND_MOBILE_PHONE     VARCHAR(30),
397
  SEND_ADDRESS          VARCHAR(256),
398
  SEND_POST             VARCHAR(20),
399
  RECEIVE_FULLNAME      VARCHAR(256),
400
  RECEIVE_PHONE         VARCHAR(30),
401
  RECEIVE_MOBILE_PHONE  VARCHAR(30),
402
  RECEIVE_ADDRESS       VARCHAR(256),
403
  RECEIVE_POST          VARCHAR(20),
404
  LOGISTICS_MEMO        VARCHAR(600),
405
  TRADE_OR_REFUND_FLAG  int         default 1 NOT NULL,
406
  GMT_CREATE            TIMESTAMP(6) default "2012-01-01 12:00:00",
407
  GMT_APPLY             TIMESTAMP(6) default "2012-01-01 12:00:00",
408
  GMT_FETCH             TIMESTAMP(6) default "2012-01-01 12:00:00",
409
  GMT_TRANSPORT         TIMESTAMP(6) default "2012-01-01 12:00:00",
410
  GMT_SIGN              TIMESTAMP(6) default "2012-01-01 12:00:00",
411
  GMT_MODIFIED          TIMESTAMP(6) default "2012-01-01 12:00:00",
412
  SEND_IM_TYPE          VARCHAR(10),
413
  SEND_IM_ID            VARCHAR(100),
414
  RECEIVE_IM_TYPE       VARCHAR(10),
415
  RECEIVE_IM_ID         VARCHAR(100),
416
  TRANSPORT_PAYMENT     int,
417
  TRANSPORT_MODIFIABLE  int,
418
  RECEIVE_ADDRESS_CODE  VARCHAR(8),
419
  SEND_ADDRESS_CODE     VARCHAR(8),
420
  TRANSPORT_TYPE_SEND   int,
421
  LOGISTICS_NO          VARCHAR(100),
422
  TRANS_GOODS_INFO      VARCHAR(400),
423
  REC_ADDRESS_NO        VARCHAR(100),
424
  FLAG_MAIN             int,
425
  primary key (TRADE_NO, TRADE_OR_REFUND_FLAG, ID)
426
)DEFAULT CHARSET = 'utf8';
427
CREATE INDEX TRADE_LOGISTICS_000_GMT_IND ON TRADE_LOGISTICS_000
428
(GMT_MODIFIED);
429

430

431
#这个表修改了IS_READ,原来使用的是CHAR（1）
432
CREATE TABLE if not exists TRADE_MESSAGE_BOARD_000 
433
(
434
  ID           int           NOT NULL,
435
  TRADE_NO     varchar(64)    NOT NULL,
436
  MESSAGE      varchar(2000)  NOT NULL,
437
  POSTER       varchar(100)  default ' ' NOT NULL,
438
  POSTER_ROLE  int,
439
  OPERATOR_IP  varchar(15),
440
  MSG_TYPE     int    default 0,
441
  IS_READ     	int   default 0 NOT NULL,
442
  GMT_CREATE   TIMESTAMP(6) default "2012-01-01 12:00:00",
443
  PRIMARY KEY (TRADE_NO, ID)
444
) DEFAULT CHARSET = 'utf8';
445

446
CREATE TABLE if not exists TRADE_NOTE_000 
447
(
448
  ID              int          NOT NULL,
449
  TRADE_NO        varchar(64)   NOT NULL,
450
  TRADE_STATUS    varchar(64)   default ' ',
451
  BUYER_EMAIL     varchar(100)  default ' ' NOT NULL,
452
  SELLER_EMAIL    varchar(100)  default ' ' NOT NULL,
453
  OPERATOR_EMAIL  varchar(100)  default ' ' NOT NULL,
454
  OPERATOR_IP     varchar(15),
455
  GMT_CREATE      TIMESTAMP(6) default "2012-01-01 12:00:00",
456
  OPERATOR_MEMO   varchar(256),
457
  NOTE_TYPE       varchar(100),
458
  REMOTE_MAC      varchar(64),
459
  primary key (TRADE_NO, ID, GMT_CREATE)
460
)DEFAULT CHARSET = 'utf8';
461

462
CREATE TABLE if not exists TRADE_OUTTRADE_000 
463
(
464
  PARTNER_ID    varchar(200)  NOT NULL,
465
  OUT_TRADE_NO  varchar(64)   NOT NULL,
466
  TRADE_FROM    varchar(100)  NOT NULL,
467
  TRADE_NO      varchar(64)   NOT NULL,
468
  GMT_CREATE    TIMESTAMP        NOT NULL,
469
  primary key (OUT_TRADE_NO, PARTNER_ID, TRADE_FROM)
470
) DEFAULT CHARSET = 'utf8';
471

472
CREATE TABLE if not exists TRADE_PAYMENT_CLAUSE_000 
473
(
474
  ID                     int           NOT NULL,
475
  OUT_RELATION_ID        varchar(64),
476
  SERIAL_NO              int,
477
  TRADE_NO               varchar(64)    NOT NULL,
478
  GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00",
479
  GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00",
480
  PAYMENT_TYPE           varchar(64)    NOT NULL,
481
  STATUS                 varchar(50),
482
  PAY_ACCOUNT_NO         varchar(20),
483
  PAY_USER_ID            varchar(16),
484
  RECEIVE_ACCOUNT_NO     varchar(20),
485
  RECEIVE_USER_ID        varchar(16),
486
  AMOUNT                 int,
487
  CURRENCY               varchar(64),
488
  PAYMENT_RESTRICT_RULE  varchar(1000),
489
  DESCRIPTION            varchar(1000),
490
  MEMO                   varchar(1000),
491
  CONTEXT                varchar(1000),
492
  primary key (TRADE_NO, OUT_RELATION_ID)
493
)DEFAULT CHARSET = 'utf8';
494
CREATE INDEX TRADE_PCLAUSE_000_GMT_IND ON TRADE_PAYMENT_CLAUSE_000 
495
(GMT_MODIFIED);
496

497
CREATE TABLE if not exists TRADE_PAYMENT_ORDER_000 
498
(
499
  ORDER_NO            varchar(64)    NOT NULL,
500
  TRADE_NO            varchar(64)    NOT NULL,
501
  BIZ_TYPE            varchar(20)    NOT NULL,
502
  OUT_ORDER_NO        varchar(64),
503
  GMT_MODIFIED        TIMESTAMP         NOT NULL,
504
  STATUS              varchar(2)     NOT NULL,
505
  PAY_ACCOUNT_NO      varchar(20)    NOT NULL,
506
  RECEIVE_ACCOUNT_NO  varchar(20)    NOT NULL,
507
  AMOUNT              int           NOT NULL,
508
  RETURN_AMOUNT       int,
509
  CURRENCY            varchar(3)     NOT NULL,
510
  FREEZE_TYPE         varchar(5),
511
  EXT_INFO            varchar(1000),
512
  MEMO                varchar(1000),
513
  PAYMENT_ID          varchar(64),
514
  PAYMENT_CONTEXT     varchar(1000),
515
  GMT_PAY             TIMESTAMP(6) default "2012-01-01 12:00:00",
516
  GMT_CREATE          TIMESTAMP         NOT NULL,
517
  FREEZE_AMOUNT       int,
518
  SUB_TRANS_CODE      varchar(40)    NOT NULL,
519
  primary key (TRADE_NO, ORDER_NO)
520
  )DEFAULT CHARSET = 'utf8';
521

522
CREATE INDEX TRADE_PAYMENT_ORD_000_GM_IND ON TRADE_PAYMENT_ORDER_000
523
(GMT_MODIFIED);
524

525
CREATE TABLE if not exists TRADE_PROCESS_000 
526
(
527
  ID                        int           NOT NULL,
528
  TRADE_NO                  varchar(64)   default ' ' NOT NULL,
529
  OUT_TRADE_NO              varchar(64),
530
  RECEIVE_FEE               int           default 0 NOT NULL,
531
  SEND_FEE                  int           default 0 NOT NULL,
532
  ADJUST_FEE                int           default 0 NOT NULL,
533
  MEMO                      varchar(600),
534
  OPERATE_TIME              TIMESTAMP(6) default "2012-01-01 12:00:00",
535
  OPERATE_NAME              varchar(256),
536
  FLAG_REFUND               varchar(4)    default '0' NOT NULL,
537
  FLAG_DEFERPAY             varchar(4)    default '0' NOT NULL,
538
  FLAG_PAYMENT              varchar(8)    default '0' NOT NULL,
539
  FLAG_TRADE_LOCKED         varchar(4)    default '0' NOT NULL,
540
  FLAG_SELLER_MDF           varchar(4)    default '0' NOT NULL,
541
  GMT_CREATE                TIMESTAMP                 ,
542
  GMT_MODIFIED              TIMESTAMP(6) default "2012-01-01 12:00:00",
543
  GMT_RECEIVE_PAY           TIMESTAMP(6) default "2012-01-01 12:00:00",
544
  GMT_SEND_PAY              TIMESTAMP(6) default "2012-01-01 12:00:00",
545
  GMT_SEND_GOODS            TIMESTAMP(6) default "2012-01-01 12:00:00",
546
  TIME_OUT_RULE             varchar(1000),
547
  GMT_OUT_TRADE_NO_CREATED  TIMESTAMP(6) default "2012-01-01 12:00:00",
548
  COUPON_CLEAR_FEE          int,
549
  COUPON_ADJUST_FEE         int,
550
  REFUND_FEE                int,
551
  GMT_REFUND_PAY            TIMESTAMP(6) default "2012-01-01 12:00:00",
552
  GMT_FINISH                TIMESTAMP(6) default "2012-01-01 12:00:00",
553
  ACCOUNT_TX_ID             varchar(40),
554
  ADJUST_TIMEOUT            int,
555
  GMT_PRE_AUTH              TIMESTAMP(6) default "2012-01-01 12:00:00",
556
  PRE_AUTH_FEE              int,
557
  ACTUAL_ADJUST_FEE         int,
558
  EXPIRE_TIME               TIMESTAMP(6) default "2012-01-01 12:00:00",
559
  SEND_BACK_FEE             int,
560
  PRIMARY KEY (TRADE_NO)
561
 )DEFAULT CHARSET = 'utf8';
562

563
CREATE INDEX IND_BYD_TRD_PROCESS_GRPAY ON TRADE_PROCESS_000 
564
(GMT_RECEIVE_PAY, RECEIVE_FEE);
565

566
CREATE INDEX TRADE_PROCESS_GMT_IND ON TRADE_PROCESS_000
567
(GMT_MODIFIED);
568

569
CREATE TABLE if not exists TRADE_REFUND_000 
570
(
571
  ID                  int           NOT NULL,
572
  TRADE_NO            varchar(64)    NOT NULL,
573
  OUT_TRADE_NO        varchar(64),
574
  RETURN_STATUS       varchar(100),
575
  MEMO                varchar(2000),
576
  RETURN_FEE          int,
577
  GMT_ADMIN_MODIFIED  TIMESTAMP(6) default "2012-01-01 12:00:00",
578
  ADMIN_STATUS        varchar(100),
579
  RETURN_TYPE         varchar(20)    NOT NULL,
580
  RETURN_GOODS        int,
581
  GOODS_STATUS        int,
582
  CLOSE_CAUSE         varchar(600),
583
  REQUIRE_VOUCHER     int,
584
  GMT_CREATE          TIMESTAMP                 NOT NULL,
585
  GMT_MODIFIED        TIMESTAMP(6) default "2012-01-01 12:00:00",
586
  SELLER_NOT_RECEIVE  int,
587
  REFUND_REASON       varchar(400),
588
  APPEAL_NO           varchar(50),
589
  GMT_DW_SYNC         TIMESTAMP         ,
590
  BUY_USER_ID         varchar(16)    NOT NULL,
591
  BUY_ACCOUNT         varchar(20)    NOT NULL,
592
  SELL_USER_ID        varchar(16)    NOT NULL,
593
  SELL_ACCOUNT        varchar(20)    NOT NULL,
594
  TRADE_FROM          varchar(100),
595
  TRADE_EMAIL         varchar(100),
596
  IS_INPOUR_REFUND   	VARCHAR(1),
597
  REFUND_FLOW_TYPE   	VARCHAR(1),
598
  GMT_LAST_APPLY      TIMESTAMP(6) default "2012-01-01 12:00:00",
599
  REFUND_FOREX_FEE    int,
600
  PRIMARY KEY (TRADE_NO, ID)
601
)DEFAULT CHARSET = 'utf8';
602
CREATE INDEX TRADE_REFUND_BG_IND ON TRADE_REFUND_000 
603
(BUY_ACCOUNT, GMT_CREATE);
604

605
CREATE INDEX TRADE_REFUND_SG_IND ON TRADE_REFUND_000 
606
(SELL_ACCOUNT, GMT_CREATE);
607

608
CREATE INDEX TRADE_REFUND_TG_IND ON TRADE_REFUND_000 
609
(TRADE_EMAIL, GMT_CREATE);
610

611
CREATE INDEX TRADE_REFUND_000_GMT_IND ON TRADE_REFUND_000 (GMT_MODIFIED);
612

613
CREATE TABLE if not exists TRADE_VOUCHER_000 
614
(
615
  ID                     int           NOT NULL,
616
  TRADE_NO               varchar(64)   default ' '  NOT NULL,
617
  REFUND_ID              int           NOT NULL,
618
  SELLER_OR_BUYER        VARCHAR(1)    default 'S' NOT NULL,
619
  SELLER_UPLOAD_VOUCHER  varchar(4000),
620
  BUYER_UPLOAD_VOUCHER   varchar(4000),
621
  UPLOAD_FLAG            int           default 0 NOT NULL,
622
  GMT_CREATE             TIMESTAMP(6) default "2012-01-01 12:00:00",
623
  GMT_MODIFIED           TIMESTAMP(6) default "2012-01-01 12:00:00",
624
  GMT_CHECK              TIMESTAMP(6) default "2012-01-01 12:00:00",
625
  LAST_OPERATOR          varchar(32),
626
  CHECK_MEMO             varchar(2000),
627
  MEMEO                  varchar(4000),
628
  GMT_TIMEOUT_START      TIMESTAMP(6) default "2012-01-01 12:00:00",
629
  PRIMARY KEY (TRADE_NO, ID)
630
) DEFAULT CHARSET = 'utf8';
631

632
CREATE INDEX TRADE_VOUCHER_000_MT_IND ON TRADE_VOUCHER_000
633
(GMT_MODIFIED);
634

635
CREATE TABLE if not exists TRADE_RECEIVE_ADDRESS_000
636
(
637
TRADE_NO          VARCHAR(64)   NOT NULL,
638
ADDRESS_NO        VARCHAR(100)  NOT NULL,
639
RECEIVE_NAME      VARCHAR(256),
640
ADDRESS_CODE      VARCHAR(8),
641
ADDRESS           VARCHAR(256),
642
MOBILE_PHONE      VARCHAR(30),
643
PHONE             VARCHAR(30),
644
RECEIVE_POST      VARCHAR(20),
645
VIRTUAL_REC_INFO  VARCHAR(400),
646
GMT_CREATE        DATETIME                NOT NULL,
647
GMT_MODIFIED      DATETIME,
648
PRIMARY KEY (TRADE_NO, ADDRESS_NO)
649
)DEFAULT CHARSET = 'utf8';
650

651

652
CREATE TABLE if not exists TRADE_COOPERATE_000
653
(
654
TRADE_NO         VARCHAR(64),
655
COOPERATE_NO     VARCHAR(64),
656
COOPERATOR_FROM  VARCHAR(32),
657
GMT_CREATE       TIMESTAMP(6) default "2012-01-01 12:00:00",
658
GMT_MODIFIED     TIMESTAMP(6) default "2012-01-01 12:00:00",
659
PRIMARY key (TRADE_NO, COOPERATE_NO, COOPERATOR_FROM)
660
)DEFAULT CHARSET = 'utf8';
661

662
CREATE INDEX TRADE_COOPERATE_000_MT_IND ON TRADE_COOPERATE_000
663
(GMT_MODIFIED);
664

665
CREATE TABLE if not exists TRADE_SETTLE_ORDER_000
666
(
667
ORDER_NO            VARCHAR(64)    NOT NULL,
668
ORG_ORDER_NO        VARCHAR(64),
669
TRADE_NO            VARCHAR(64)    NOT NULL,
670
OUT_REQUEST_NO      VARCHAR(64),
671
BIZ_PRODUCT         VARCHAR(32),
672
SETTLE_ACTION_TYPE  VARCHAR(32),
673
AMOUNT              int           NOT NULL,
674
STATUS              VARCHAR(50)    NOT NULL,
675
EXTINFO             VARCHAR(1024),
676
MEMO                VARCHAR(512),
677
SETTLE_ID           VARCHAR(64),
678
SETTLE_CONTEXT      VARCHAR(1024),
679
GMT_CREATE          TIMESTAMP(6)         NOT NULL,
680
GMT_MODIFIED        TIMESTAMP(6),
681
SETTLE_TYPE         VARCHAR(32),
682
PRIMARY KEY (TRADE_NO, ORDER_NO, GMT_CREATE)
683
)DEFAULT CHARSET = 'utf8';
684

685

686
CREATE TABLE if not exists TRADE_PARTICIPANT_000
687
(
688
TRADE_NO          VARCHAR(64)   NOT NULL,
689
USER_ID           VARCHAR(16)   NOT NULL,
690
USER_NAME         VARCHAR(256),
691
USER_NICK         VARCHAR(100),
692
PARTICIPANT_ROLE  VARCHAR(64)   NOT NULL,
693
MEMO              VARCHAR(200),
694
GMT_CREATE        TIMESTAMP               NOT NULL,
695
GMT_MODIFIED      TIMESTAMP(6) default "2012-01-01 12:00:00",
696
PRIMARY KEY (TRADE_NO, USER_ID, PARTICIPANT_ROLE)
697
)DEFAULT CHARSET = 'utf8';
698

699
CREATE TABLE if not exists BUSINESS_ACTIVITY_ASYNC_000
700
(
701
TX_ID                VARCHAR(128)   NOT NULL,
702
STATE                VARCHAR(1)         NOT NULL,
703
ACCOUNT_TRANS_STATE  VARCHAR(1)         NOT NULL,
704
GMT_CREATE           TIMESTAMP         NOT NULL,
705
GMT_MODIFIED         TIMESTAMP         NOT NULL,
706
PROPAGATION          VARCHAR(1),
707
CONTEXT              VARCHAR(2048),
708
PRIMARY KEY (TX_ID)
709
)DEFAULT CHARSET = 'utf8';
710

711
CREATE TABLE if not exists EXTERFACE_INVOKE_000
712
(
713
  ID           int         NOT NULL,
714
  PARTNER_ID   VARCHAR(32)   NOT NULL,
715
  TARGET       VARCHAR(64)   NOT NULL,
716
  EXTERFACE    VARCHAR(128)  NOT NULL,
717
  NOTIFY_URL   VARCHAR(200),
718
  RETURN_URL   VARCHAR(200),
719
  SIGN_TYPE    VARCHAR(10),
720
  CHARSET      VARCHAR(20),
721
  GMT_INVOKE   TIMESTAMP        NOT NULL,
722
  GMT_FINISH   TIMESTAMP       NOT NULL,
723
  TARGET_TYPE  VARCHAR(20)   NOT NULL,
724
  GMT_NOTIFY   TIMESTAMP(6) default "2012-01-01 12:00:00",
725
  GMT_RETURN   TIMESTAMP(6) default "2012-01-01 12:00:00",
726
  PRIMARY KEY(TARGET_TYPE, TARGET, PARTNER_ID)
727
)DEFAULT CHARSET = 'utf8';
728

729
CREATE INDEX EXTERFACE_INVOKE_000_ID_IND ON EXTERFACE_INVOKE_000(ID);
730

731
--disable_warnings
732
DROP TABLE if exists trade_sequence_000;
733
DROP TABLE if exists BUSINESS_ACTION_000; 
734
DROP TABLE if exists BUSINESS_ACTIVITY_000; 
735
DROP TABLE if exists NOTIFY_000 ;
736
DROP TABLE if exists NOTIFY_HISTORY_000 ;
737
DROP TABLE if exists TIMEOUT_000 ;
738
DROP TABLE if exists TIMEOUT_HISTORY_000 ;
739
DROP TABLE if exists TIMEOUT_LONG_000 ;
740
DROP TABLE if exists TIMEOUT_REMIND_000; 
741
DROP TABLE if exists TRADE_BASE_000 ;
742
DROP TABLE if exists TRADE_CHARGE_000; 
743
DROP TABLE if exists TRADE_EXT_000 ;
744
DROP TABLE if exists TRADE_FUND_BILL_000 ;
745
DROP TABLE if exists TRADE_GOODS_INFO_000 ;
746
DROP TABLE if exists TRADE_LOGISTICS_000;
747
DROP TABLE if exists TRADE_MESSAGE_BOARD_000 ;
748
DROP TABLE if exists TRADE_NOTE_000 ;
749
DROP TABLE if exists TRADE_OUTTRADE_000 ;
750
DROP TABLE if exists TRADE_PAYMENT_CLAUSE_000 ;
751
DROP TABLE if exists TRADE_PAYMENT_ORDER_000 ;
752
DROP TABLE if exists TRADE_PROCESS_000 ;
753
DROP TABLE if exists TRADE_REFUND_000 ;
754
DROP TABLE if exists TRADE_VOUCHER_000 ;
755
DROP TABLE if exists TRADE_RECEIVE_ADDRESS_000;
756
DROP TABLE if exists TRADE_COOPERATE_000;
757
DROP TABLE if exists TRADE_SETTLE_ORDER_000;
758
DROP TABLE if exists TRADE_PARTICIPANT_000;
759
DROP TABLE if exists BUSINESS_ACTIVITY_ASYNC_000;
760
DROP TABLE if exists EXTERFACE_INVOKE_000;
761
--enable_warnings
762