postgres_sys_ddl.sql

create table "Praise"
(
);

alter table "Praise"
    owner to postgres;

create table "Random"
(
);

alter table "Random"
    owner to postgres;

create table "_Visit"
(
);

alter table "_Visit"
    owner to postgres;

create table "Method"
(
);

alter table "Method"
    owner to postgres;

create table "Document"
(
    id              bigint,
    debug           smallint,
    "userId"        bigint,
    "testAccountId" bigint,
    version         smallint,
    name            varchar(100),
    type            varchar(5),
    url             varchar(250),
    request         text,
    apijson         text,
    sqlauto         text,
    standard        text,
    header          text,
    date            timestamp,
    detail          text
);

alter table "Document"
    owner to postgres;

create table apijson_privacy
(
    id             bigint         not null
        primary key,
    certified      smallint       not null,
    phone          varchar(64)    not null,
    balance        numeric(10, 2) not null,
    _password      varchar(20)    not null,
    "_payPassword" varchar(32)    not null
);

comment on table apijson_privacy is '用户隐私信息表。\n对安全要求高，不想泄漏真实名称。对外名称为 Privacy';

comment on column apijson_privacy.id is '唯一标识';

comment on column apijson_privacy.certified is '已认证';

comment on column apijson_privacy.phone is '手机号，仅支持 11 位数的。不支持 +86 这种国家地区开头的。如果要支持就改为 VARCHAR(14)';

comment on column apijson_privacy.balance is '余额';

comment on column apijson_privacy._password is '登录密码';

comment on column apijson_privacy."_payPassword" is '支付密码';

alter table apijson_privacy
    owner to postgres;

create index "phone_UNIQUE"
    on apijson_privacy (phone);

create table apijson_user
(
    id              bigint   not null
        primary key,
    sex             smallint not null,
    name            varchar(20),
    tag             varchar(45),
    head            varchar(300),
    "contactIdList" jsonb,
    "pictureList"   jsonb,
    date            timestamp(6)
);

comment on table apijson_user is '用户公开信息表。对安全要求高，不想泄漏真实名称。对外名称为 User';

comment on column apijson_user.id is '唯一标识';

comment on column apijson_user.sex is '性别：
0-男
1-女';

comment on column apijson_user.name is '名称';

comment on column apijson_user.tag is '标签';

comment on column apijson_user.head is '头像url';

comment on column apijson_user."contactIdList" is '联系人id列表';

comment on column apijson_user."pictureList" is '照片列表';

comment on column apijson_user.date is '创建日期';

alter table apijson_user
    owner to postgres;

create table "Comment"
(
    id         bigint           not null
        primary key,
    "toId"     bigint default 0 not null,
    "userId"   bigint           not null,
    "momentId" bigint           not null,
    date       timestamp(6),
    content    varchar(1000)    not null
);

comment on table "Comment" is '评论';

comment on column "Comment".id is '唯一标识';

comment on column "Comment"."toId" is '被回复的id';

comment on column "Comment"."userId" is '评论人id';

comment on column "Comment"."momentId" is '动态id';

comment on column "Comment".date is '创建日期';

comment on column "Comment".content is '内容';

alter table "Comment"
    owner to postgres;

create table "Moment"
(
    id                 bigint not null
        primary key,
    "userId"           bigint not null,
    date               timestamp(6),
    content            varchar(300),
    "praiseUserIdList" jsonb  not null,
    "pictureList"      jsonb  not null
);

comment on table "Moment" is '动态';

comment on column "Moment".id is '唯一标识';

comment on column "Moment"."userId" is '用户id';

comment on column "Moment".date is '创建日期';

comment on column "Moment".content is '内容';

comment on column "Moment"."praiseUserIdList" is '点赞的用户id列表';

comment on column "Moment"."pictureList" is '图片列表';

alter table "Moment"
    owner to postgres;

create table "TestRecord"
(
    id              bigint                                 not null
        primary key,
    "userId"        bigint                                 not null,
    "documentId"    bigint                                 not null,
    response        text                                   not null,
    date            timestamp(6) default CURRENT_TIMESTAMP not null,
    compare         text,
    standard        text,
    "randomId"      bigint       default 0,
    headless        smallint     default 0                 not null,
    "reportId"      bigint       default 0                 not null,
    "testAccountId" bigint       default 0                 not null,
    duration        bigint       default 0                 not null,
    "minDuration"   bigint       default 0                 not null,
    "maxDuration"   bigint       default 0                 not null,
    host            varchar(200)
);

comment on column "TestRecord".id is '唯一标识';

comment on column "TestRecord"."userId" is '用户id';

comment on column "TestRecord"."documentId" is '测试用例文档id';

comment on column "TestRecord".response is '接口返回结果JSON';

comment on column "TestRecord".date is '创建日期';

comment on column "TestRecord".compare is '对比结果';

comment on column "TestRecord".standard is 'response 的校验标准，是一个 JSON 格式的 AST ，描述了正确 Response 的结构、里面的字段名称、类型、长度、取值范围 等属性。';

comment on column "TestRecord"."randomId" is '随机配置 id';

comment on column "TestRecord".headless is '是否为无 UI 的 Headless 模式：0-否 1-是';

comment on column "TestRecord"."reportId" is '测试报告 ID';

comment on column "TestRecord"."testAccountId" is '测试账号 id';

alter table "TestRecord"
    owner to postgres;

create table "Function"
(
    id           bigint                                 not null
        primary key,
    language     varchar,
    name         varchar(30)                            not null,
    "returnType" varchar(45)  default 'Object'::character varying,
    arguments    varchar(100),
    demo         text                                   not null,
    detail       varchar(1000),
    date         timestamp(6) default CURRENT_TIMESTAMP not null,
    "userId"     bigint       default 0,
    version      integer      default 0,
    tag          varchar,
    methods      varchar,
    return       integer
);

comment on column "Function".language is '语言：Java(java), JavaScript(js), Lua(lua), Python(py), Ruby(ruby), PHP(php) 等，NULL 默认为 Java，JDK 1.6-11 默认支持 JavaScript，JDK 12+ 需要额外依赖 Nashron/Rhiro 等 js 引擎库，其它的语言需要依赖对应的引擎库，并在 ScriptEngineManager 中注册';

comment on column "Function".name is '方法名';

comment on column "Function"."returnType" is '返回类型';

comment on column "Function".arguments is '参数列表，每个参数的类型都是 String。
用 , 分割的字符串 比 [JSONArray] 更好，例如 array,item ，更直观，还方便拼接函数。';

comment on column "Function".demo is '可用的示例。';

comment on column "Function".detail is '详细描述';

comment on column "Function".date is '创建时间';

comment on column "Function"."userId" is '用户id';

comment on column "Function".version is '允许的最低版本号，只限于GET,HEAD外的操作方法。\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".tag is '允许的标签.\nnull - 允许全部\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".methods is '允许的操作方法。\nnull - 允许全部\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".return is '返回值示例';

alter table "Function"
    owner to postgres;

create table "Request"
(
    id        bigint,
    debug     smallint,
    version   smallint,
    method    varchar(10),
    tag       varchar(30),
    structure json,
    detail    varchar(10000),
    date      timestamp
);

alter table "Request"
    owner to postgres;

create table "Script"
(
    id              bigint,
    "userId"        bigint,
    "testAccountId" bigint,
    "documentId"    bigint,
    simple          smallint,
    ahead           smallint,
    title           varchar(100),
    name            varchar(100),
    script          text,
    date            timestamp,
    detail          varchar(1000)
);

alter table "Script"
    owner to postgres;

create table "Access"
(
    id     integer                                                                                  not null
        primary key,
    schema varchar(100) default NULL::character varying,
    debug  integer      default 0                                                                   not null,
    name   varchar(50)  default '实际表名，例如 apijson_user'::character varying                     not null,
    alias  text,
    get    text         default '["UNKNOWN", "LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,
    head   text         default '["UNKNOWN", "LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,
    gets   text         default '["LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text            not null,
    heads  text         default '["LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text            not null,
    post   text         default '["OWNER", "ADMIN"]'::text                                          not null,
    put    text         default '["OWNER", "ADMIN"]'::text                                          not null,
    delete text         default '["OWNER", "ADMIN"]'::text                                          not null,
    date   text         default CURRENT_TIMESTAMP                                                   not null,
    detail text
);

comment on column "Access".id is '唯一标识';

comment on column "Access".debug is '是否为调试表，只允许在开发环境使用，测试和线上环境禁用';

comment on column "Access".alias is '外部调用的表别名，例如 User';

comment on column "Access".get is '允许 get 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]
用 JSON 类型不能设置默认值，反正权限对应的需求是明确的，也不需要自动转 JSONArray。
TODO: 直接 LOGIN,CONTACT,CIRCLE,OWNER 更简单，反正是开发内部用，不需要复杂查询。';

comment on column "Access".head is '允许 head 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".gets is '允许 gets 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".heads is '允许 heads 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".post is '允许 post 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".put is '允许 put 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".delete is '允许 delete 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".date is '创建时间';

alter table "Access"
    owner to postgres;

create table "Verify"
(
    id     bigint,
    type   integer,
    phone  bigint,
    verify integer,
    date   timestamp
);

alter table "Verify"
    owner to postgres;