EKDScode/log_db.sql

-- 管理员日常操作日志表
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE AR_BONUS_ADMIN_HANDLE';
    EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE AR_BONUS_ADMIN_HANDLE
(
  ID            VARCHAR2(32) DEFAULT SYS_GUID()  NOT NULL   PRIMARY KEY,
  ADMIN_ID      VARCHAR2(32)                     NOT NULL,
  ADMIN_NAME    VARCHAR2(16)                     NOT NULL,
  IP            VARCHAR2(16)                     NOT NULL,
  REQUEST_ROUTE VARCHAR2(32)                     NOT NULL,
  OPT_CONTENT   VARCHAR2(4000)                   NOT NULL,
  KEY_LOG       NUMBER(1) DEFAULT 0              NOT NULL,
  OPT_OBJ_ID    VARCHAR2(32),
  OPT_OBJ_NAME  VARCHAR2(16),
  REMARK        VARCHAR2(255),
  DEVICE_TYPE   VARCHAR2(255),
  DEVICE_SYSTEM VARCHAR2(255),
  DEVICE_VERSION VARCHAR2(255),
  DEVICE_NET    VARCHAR2(255),
  DEVICE_UUID   VARCHAR2(255),
  USER_AGENT    VARCHAR2(1000),
  PERIOD_NUM    NUMBER(10) DEFAULT 0             NOT NULL,
  P_MONTH       DATE                             NOT NULL,
  CREATED_AT    NUMBER(10) DEFAULT 0             NOT NULL
)
PARTITION BY RANGE(P_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);

COMMENT ON TABLE AR_BONUS_ADMIN_HANDLE IS '管理员日常操作日志表';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.ADMIN_ID IS '操作人ID';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.ADMIN_NAME IS '操作人';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.IP IS '操作IP';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.REQUEST_ROUTE IS '请求路由';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.OPT_CONTENT IS '操作内容';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.KEY_LOG IS '关键日志';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.OPT_OBJ_ID IS '操作对象ID';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.OPT_OBJ_NAME IS '操作对象名称';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.REMARK IS '备注';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.DEVICE_TYPE IS '设备类型';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.DEVICE_SYSTEM IS '设备操作系统';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.DEVICE_VERSION IS '设备操作系统版本';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.DEVICE_NET IS '设备使用网络';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.DEVICE_UUID IS '设备UUID';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.USER_AGENT IS '浏览器标识';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.PERIOD_NUM IS '浏览器标识';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.P_MONTH IS '用于表分区的创建日期';
COMMENT ON COLUMN AR_BONUS_ADMIN_HANDLE.CREATED_AT IS '创建时间';


-- 管理员登录日志表
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE "AR_BONUS_ADMIN_LOGIN"';
    EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE "AR_BONUS_ADMIN_LOGIN"
(
  ID            VARCHAR2(32) DEFAULT SYS_GUID()  NOT NULL   PRIMARY KEY,
  ADMIN_ID      VARCHAR2(32)                     NOT NULL,
  ADMIN_NAME    VARCHAR2(16)                     NOT NULL,
  IP            VARCHAR2(16)                     NOT NULL,
  REMARK        VARCHAR2(255),
  DEVICE_TYPE   VARCHAR2(255),
  DEVICE_SYSTEM VARCHAR2(255),
  DEVICE_VERSION VARCHAR2(255),
  DEVICE_NET    VARCHAR2(255),
  DEVICE_UUID   VARCHAR2(255),
  USER_AGENT    VARCHAR2(1000),
  PERIOD_NUM    NUMBER(10) DEFAULT 0             NOT NULL,
  P_MONTH       DATE                             NOT NULL,
  CREATED_AT    NUMBER(10) DEFAULT 0             NOT NULL
)
PARTITION BY RANGE(P_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);

COMMENT ON TABLE "AR_BONUS_ADMIN_LOGIN" IS '管理员登录日志表';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".ADMIN_ID IS '操作人ID';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".ADMIN_NAME IS '操作人';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".IP IS '操作IP';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".REMARK IS '备注';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".DEVICE_TYPE IS '设备类型';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".DEVICE_SYSTEM IS '设备操作系统';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".DEVICE_VERSION IS '设备操作系统版本';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".DEVICE_NET IS '设备使用网络';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".DEVICE_UUID IS '设备UUID';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".USER_AGENT IS '浏览器标识';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".PERIOD_NUM IS '期数';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".P_MONTH IS '分区表索引日期';
COMMENT ON COLUMN "AR_BONUS_ADMIN_LOGIN".CREATED_AT IS '创建时间';

-- 结算系统接口异步通知日志表
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE "AR_BONUS_API_NOTICE"';
    EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE "AR_BONUS_API_NOTICE"
(
  ID            VARCHAR2(32) DEFAULT SYS_GUID()  NOT NULL   PRIMARY KEY,
  URL           VARCHAR2(255)                    NOT NULL,
  METHOD        VARCHAR2(255)                    NOT NULL,
  DATA          CLOB                             NOT NULL,
  ROUTE         VARCHAR2(255)                    NOT NULL,
  STATUS        NUMBER(10)                       NOT NULL,
  RESPONSE      CLOB                             NOT NULL,
  REMARK        VARCHAR2(255) ,
  P_MONTH       DATE                             NOT NULL,
  CREATED_AT    NUMBER(10) DEFAULT 0             NOT NULL
)
PARTITION BY RANGE(P_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);

COMMENT ON TABLE "AR_BONUS_API_NOTICE" IS '结算系统接口异步通知日志表';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".ID IS 'ID';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".URL IS '通知的URL';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".METHOD IS '通知方法';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".DATA IS '通知数据';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".ROUTE IS '本地路由';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".STATUS IS '通知状态';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".RESPONSE IS '通知结果';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".REMARK IS '备注';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".P_MONTH IS '分区表索引日期';
COMMENT ON COLUMN "AR_BONUS_API_NOTICE".CREATED_AT IS '通知时间';

-- 结算系统异步任务日志表
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE "AR_BONUS_ASYNC"';
    EXCEPTION WHEN OTHERS THEN NULL;
END;
CREATE TABLE "AR_BONUS_ASYNC"
(
  ID            VARCHAR2(32) DEFAULT SYS_GUID()   NOT NULL   PRIMARY KEY,
  TYPE          NUMBER(10)                        NOT NULL,
  ROUTE         VARCHAR2(255)                     NOT NULL,
  TITLE         VARCHAR2(255)                     NOT NULL,
  DETAIL        CLOB                              NOT NULL,
  STATUS        NUMBER(1) DEFAULT 1               NOT NULL,
  P_MONTH       DATE                              NOT NULL,
  CREATED_AT    NUMBER(10) DEFAULT 0              NOT NULL
)
PARTITION BY RANGE(P_MONTH)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P0 VALUES LESS THAN (TO_DATE('2018-03-01','YYYY-MM-DD'))
);

COMMENT ON TABLE "AR_BONUS_ASYNC" IS '结算系统异步任务日志表';
COMMENT ON COLUMN "AR_BONUS_ASYNC".ID IS 'ID';
COMMENT ON COLUMN "AR_BONUS_ASYNC".TYPE IS '任务类型';
COMMENT ON COLUMN "AR_BONUS_ASYNC".ROUTE IS '路由';
COMMENT ON COLUMN "AR_BONUS_ASYNC".TITLE IS '标题';
COMMENT ON COLUMN "AR_BONUS_ASYNC".DETAIL IS '内容';
COMMENT ON COLUMN "AR_BONUS_ASYNC".STATUS IS '成功状态';
COMMENT ON COLUMN "AR_BONUS_ASYNC".P_MONTH IS '表分区索引日期';
COMMENT ON COLUMN "AR_BONUS_ASYNC".CREATED_AT IS '创建时间';

COMMIT ;