derby数据库试用

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider','BUILTIN');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication','true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.coupon','pwdcoupon'); --设置用户名为coupon,密码为pwdcoupon
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers','coupon');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode','noAccess')


create table BUSS_LOG
(
   BUSS_LOG_ID          BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1) ,
   TERMINAL_INFO_ID     int,
   RFID_ID              varchar(128),
   OPERATE_TIME         TIMESTAMP,
   OPERATE_TYPE         varchar(10) ,
   UPLOAD_RESULT        varchar(1) ,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (BUSS_LOG_ID)
);
create table CARD
(
   ID                   BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   DISTRIBUTOR          varchar(50),
   OPER                 varchar(50),
   CARDID               varchar(50),
   CARDSER              varchar(50),
   MOBILE               varchar(20),
   SEX                  varchar(10),
   AGE                  varchar(10),
   OCCUPATION           varchar(50),
   DISPATCHTIME         varchar(20),
   ACTIVETIME           varchar(20),
   ENABLEDATE           varchar(20),
   DISABLEDATE          varchar(20),
   CARDTYPE             varchar(20),
   STATUS               varchar(10),
   MODIFY_TIME          TIMESTAMP ,
   DATA_STATUS          int,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (ID)
);
create table COMPANY
(
   COMPANY_ID           BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   COMPANY_NAME         varchar(200),
   COMPANY_TEL          varchar(32),
   COMPANY_ADDRESS      varchar(128),
   SP_INTRODUCE         varchar(1000),
   COUPON_LOG           varchar(256),
   SHOW_IMG1            varchar(256),
   SHOW_IMG2            varchar(256),
   SHOW_IMG3            varchar(256),
   SHOW_IMG4            varchar(256),
   SHOW_IMG5            varchar(256),
   MODIFY_TIME          TIMESTAMP,
   DATA_STATUS          int,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (COMPANY_ID)
);


create table COUPON
(
   TEMPLATE_SERIES_NO   varchar(128) not null,
   TEMPLATE_NAME        varchar(256),
   COMPANY_ID           bigint,
   VALID_BEG_TIME       varchar(32),
   VALID_END_TIME       varchar(32),
   IMG_PATH             varchar(200),
   CATEGORY_ID          varchar(20),
   CIRCULATION          int,
   TICKET_INFO          varchar(1000),
   IS_TO_VIP            int,
   CATEGORY_NAME        varchar(100),
   OUT_QUANTITY         int,
   USED_QUANTITY        int,
   REMAIN_QUANTITY      int,
   CREATE_TIME          TIMESTAMP,
   MODIFY_TIME          TIMESTAMP,
   CHECK_CODE           varchar(256),
   DATA_STATUS          int,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (TEMPLATE_SERIES_NO)
);
create table PRINT_LOG
(
   PRINT_LOG_ID         BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   TERMINAL_INFO_ID     int ,
   RFID_ID              varchar(32) ,
   PRINT_TIME           timestamp,
   COUPON_INFO          varchar(256),
   COUPON_ID            varchar(256) ,
   COUPON_IMAGE_PATH    varchar(256) ,
   TERMINAL_MAC_ADDRESS varchar(48) ,
   PRINT_RESUTL         varchar(1) ,
   UPLOAD_RESULT        varchar(1) ,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (PRINT_LOG_ID)
);
create table RECOMMEND
(
   RECOMMEND_ID         BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   COMPANY_ID           BIGINT not null,
   COMPANY_LOCATION     varchar(10),
   COMPANY_TYPE         varchar(10),
   COMPANY_CATEGORY     varchar(10),
   ORDERNO              BIGINT,
   DATA_STATUS          int,
   MODIFY_TIME          timestamp,
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (RECOMMEND_ID)
);


create table SYN_LOG
(
   SYN_LOG_ID           BIGINT not null  GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   TERMINAL_INFO_ID     int,
   STRAT_TIME           timestamp,
   END_TIME             timestamp,
   SYN_RESUTL           varchar(1),
   SYN_MEMO             varchar(256),
   UPLOAD_RESULT        varchar(1),
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (SYN_LOG_ID)
);
create table SYS_PARAM
(
   PARAM_CODE           varchar(200) not null,
   PARAM_VALUE          varchar(200),
   PARAM_INFO           varchar(200),
   primary key (PARAM_CODE)
);


CREATE TABLE TRANSACTION_LOG (
  TRANSACTION_ID     BIGINT not null  GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
  TERMINAL_INFO_ID   int,
  TRANSACTION_PHONE  int,
  FEE                varchar(8),
  TRANSACTION_TIME   timestamp ,
  RESULT_CODE        varchar(2),
  COUPON_INFO        varchar(256),
  MENO               varchar(256),
  TERMINAL_MAC_ADDRESS varchar(48),
  DB_CREATE_TIME     TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
  PRIMARY KEY (TRANSACTION_ID)
);
create table WARN_LOG
(
   WARN_ID              BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
   TERMINAL_INFO_ID     int not null,
   TERMINAL_MAC_ADDRESS varchar(48),
   WARNING_LEVEL_CODE   int not null,
   WARNING_SOURCE       varchar(32) ,
   WARNING_CODE         varchar(8) not null ,
   MEMO                 varchar(1024) not null,
   WARNING_TIME         timestamp ,
   CPURATE              varchar(8),
   RAMSIZE              varchar(8),
   RAM_USAGE            varchar(8),
   DISK_VOLUME          varchar(256),
   DISK_FREESPACE       varchar(256),
   PROCESS_CPU_INFO     varchar(256),
   PROCESS_RAM_INFO     varchar(256),
   VIDEO_SPACE          varchar(32),
   LOG_SPACE            varchar(32),
   DAEMONRUNTIME        varchar(256),
   CLIENTRUNTIME        varchar(256),
   UPLOAD_RESULT        varchar(1),
   DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
   primary key (WARN_ID)
);
create table MOVIE_PRINT_LOG(
PRINT_LOG_ID         BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
TERMINAL_INFO_ID     int ,
    ORDERID varchar(50),
    MOBILE     varchar(13),   
    PASSWD       varchar(6),
    PRINT_TIME           timestamp,
    CINEMA_NAME          varchar(200),
    MOVIE_NAME           varchar(200),
    SHOWROOM_NAME   varchar(50),
    SHOWTIME varchar(20),
    SEAT varchar(20),
    PRINT_RESUTL         varchar(1),
    UPLOAD_RESULT        varchar(1),
    DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
    primary key (PRINT_LOG_ID)
);


create table USER_PAY_LOG(
POINT_LOG_ID         BIGINT not null GENERATED BY DEFAULT  AS IDENTITY (START WITH 1, INCREMENT BY 1),
TERMINAL_INFO_ID     int ,
    COUPON_ID   varchar(256),
    BONUS_POINT       varchar(20),
    MOBILE     varchar(20),
    UPDATE_RESULT        varchar(1),
    UPDATE_TIME          TIMESTAMP ,
    DB_CREATE_TIME       TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP,
    ORDER_NO               varchar(20),
    primary key (POINT_LOG_ID)
);


ALTER TABLE coupon ADD COLUMN useway varchar(8);