master

分支 (4)

标签 (2)

管理

管理

nt

nt2

dev

master

xci-core-1.2.18

v20200408_1450

xci-parent
/
sql
/
sqlserver.sql

create table sys_app
(
    id bigint not null
        constraint pk_app_id
            primary key,
    name nvarchar(100) not null
        constraint uk_app_name
            unique,
    secret nvarchar(100) not null,
    status tinyint not null,
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统应用程序', 'SCHEMA', 'dbo', 'TABLE', 'sys_app'
go

exec sp_addextendedproperty 'MS_Description', '应用主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_app', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '应用名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_app', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '密钥', 'SCHEMA', 'dbo', 'TABLE', 'sys_app', 'COLUMN', 'secret'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_app', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_app', 'COLUMN', 'remark'
go

create table sys_data_map
(
    object_name nvarchar(100) not null,
    object_id bigint not null,
    target_name nvarchar(100) not null,
    target_id bigint not null
)
go

exec sp_addextendedproperty 'MS_Description', '系统对象数据关联', 'SCHEMA', 'dbo', 'TABLE', 'sys_data_map'
go

exec sp_addextendedproperty 'MS_Description', '对象名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_data_map', 'COLUMN', 'object_name'
go

exec sp_addextendedproperty 'MS_Description', '对象主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_data_map', 'COLUMN', 'object_id'
go

exec sp_addextendedproperty 'MS_Description', '目标名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_data_map', 'COLUMN', 'target_name'
go

exec sp_addextendedproperty 'MS_Description', '目标主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_data_map', 'COLUMN', 'target_id'
go

create index ix_data_map_object
    on sys_data_map (object_name, object_id, target_name, target_id)
go

create table sys_dept
(
    id bigint not null
        constraint pk_dept_id
            primary key,
    parent_id bigint not null,
    name nvarchar(100) not null,
    code nvarchar(100) not null
        constraint uk_dept_code
            unique,
    spell nvarchar(100),
    path nvarchar(100),
    status tinyint not null,
    remark nvarchar(500),
    constraint uk_dept_name
        unique (parent_id, name)
)
go

exec sp_addextendedproperty 'MS_Description', '系统机构', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept'
go

exec sp_addextendedproperty 'MS_Description', '机构主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '机构上级主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'parent_id'
go

exec sp_addextendedproperty 'MS_Description', '机构名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '机构编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '机构名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '排序路径', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'path'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_dept', 'COLUMN', 'remark'
go

create table sys_dic
(
    id bigint not null
        constraint pk_dic_id
            primary key,
    code nvarchar(100) not null,
    name nvarchar(100) not null,
    value nvarchar(500) not null,
    spell nvarchar(100),
    path int,
    status tinyint not null,
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统字典', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic'
go

exec sp_addextendedproperty 'MS_Description', '字典主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '字典类型编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '字典名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '字典值', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'value'
go

exec sp_addextendedproperty 'MS_Description', '字典名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '排序行号', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'path'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic', 'COLUMN', 'remark'
go

create index ix_dic_category_code
    on sys_dic (code)
go

create table sys_dic_category
(
    id bigint not null
        constraint pk_dic_category_id
            primary key,
    code nvarchar(100) not null
        constraint uk_dic_category_code
            unique,
    name nvarchar(100) not null
)
go

exec sp_addextendedproperty 'MS_Description', '系统字典类型', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic_category'
go

exec sp_addextendedproperty 'MS_Description', '字典类型主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic_category', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '字典类型编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic_category', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '字典类型名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_dic_category', 'COLUMN', 'name'
go

create table sys_file
(
    id bigint not null
        constraint pk_file_id
            primary key,
    name nvarchar(100) not null,
    url nvarchar(200),
    file_size bigint not null,
    record_id bigint not null,
    content_type nvarchar(100),
    md5 nvarchar(100),
    operate_user_id bigint,
    operate_user_name nvarchar(100),
    operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '系统文件', 'SCHEMA', 'dbo', 'TABLE', 'sys_file'
go

exec sp_addextendedproperty 'MS_Description', '文件主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '文件名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '文件路径', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'url'
go

exec sp_addextendedproperty 'MS_Description', '文件大小', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'file_size'
go

exec sp_addextendedproperty 'MS_Description', '记录主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'record_id'
go

exec sp_addextendedproperty 'MS_Description', '文档类型', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'content_type'
go

exec sp_addextendedproperty 'MS_Description', '文件指纹', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'md5'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人姓名', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_file', 'COLUMN', 'operate_date_time'
go

create index ix_file_record_id
    on sys_file (record_id)
go

create table sys_history
(
    id bigint not null
        constraint pk_history_id
            primary key,
    operate_type tinyint not null,
    table_name nvarchar(100) not null,
    primary_key nvarchar(100) not null,
    before_data nvarchar(max),
    after_data nvarchar(max),
    message nvarchar(max),
    operate_user_id bigint,
    operate_user_name nvarchar(100),
    operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '系统历史记录', 'SCHEMA', 'dbo', 'TABLE', 'sys_history'
go

exec sp_addextendedproperty 'MS_Description', '历史记录主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '操作类型(1:新增;2:修改;3:删除)', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'operate_type'
go

exec sp_addextendedproperty 'MS_Description', '表名', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'table_name'
go

exec sp_addextendedproperty 'MS_Description', '主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'primary_key'
go

exec sp_addextendedproperty 'MS_Description', '操作前数据', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'before_data'
go

exec sp_addextendedproperty 'MS_Description', '操作后数据', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'after_data'
go

exec sp_addextendedproperty 'MS_Description', '消息', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'message'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人姓名', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_history', 'COLUMN', 'operate_date_time'
go

create index ix_history_primary_key
    on sys_history (primary_key)
go

create table sys_log
(
    id bigint not null
        constraint pk_log_id
            primary key,
    app_id nvarchar(100) not null,
    operate_type tinyint not null,
    module nvarchar(100),
    method nvarchar(200),
    param_data nvarchar(max),
    ip nvarchar(100),
    browser nvarchar(100),
    url nvarchar(500),
    cost_time int,
    result smallint not null,
    message nvarchar(max),
    details nvarchar(max),
    operate_user_id bigint,
    operate_user_name nvarchar(100),
    operate_date_time datetime
)
go

exec sp_addextendedproperty 'MS_Description', '系统日志', 'SCHEMA', 'dbo', 'TABLE', 'sys_log'
go

exec sp_addextendedproperty 'MS_Description', '日志主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '应用标识', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'app_id'
go

exec sp_addextendedproperty 'MS_Description', '操作类型(1:操作;2:登录;3:异常)', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'operate_type'
go

exec sp_addextendedproperty 'MS_Description', '模块', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'module'
go

exec sp_addextendedproperty 'MS_Description', '方法', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'method'
go

exec sp_addextendedproperty 'MS_Description', '参数', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'param_data'
go

exec sp_addextendedproperty 'MS_Description', 'IP地址', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'ip'
go

exec sp_addextendedproperty 'MS_Description', '浏览器', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'browser'
go

exec sp_addextendedproperty 'MS_Description', '请求地址', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'url'
go

exec sp_addextendedproperty 'MS_Description', '耗时', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'cost_time'
go

exec sp_addextendedproperty 'MS_Description', '执行结果(0:失败;1:成功)', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'result'
go

exec sp_addextendedproperty 'MS_Description', '信息', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'message'
go

exec sp_addextendedproperty 'MS_Description', '详细信息', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'details'
go

exec sp_addextendedproperty 'MS_Description', '操作人主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'operate_user_id'
go

exec sp_addextendedproperty 'MS_Description', '操作人姓名', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'operate_user_name'
go

exec sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_log', 'COLUMN', 'operate_date_time'
go

create table sys_module
(
    id                bigint                                 not null
        constraint pk_module_id
            primary key,
    parent_id         bigint                                 not null,
    name              nvarchar(100)                          not null,
    code              nvarchar(100)                          not null
        constraint uk_module_code
            unique,
    spell             nvarchar(100),
    param_data        nvarchar(1000),
    web_url           nvarchar(200),
    web_cls           nvarchar(100),
    win_provider      nvarchar(200),
    win_large_icon    nvarchar(100),
    win_small_icon    nvarchar(100),
    web_flag          tinyint                                not null,
    win_flag          tinyint                                not null,
    expand_flag       tinyint                                not null,
    public_flag       tinyint                                not null,
    menu_flag         tinyint                                not null,
    dialog_flag       tinyint                                not null,
    preloading_flag   tinyint
        constraint DF_sys_module_preloading_flag default 0   not null,
    vector_graph_flag tinyint
        constraint DF_sys_module_vector_graph_flag default 0 not null,
    path              nvarchar(100),
    status            tinyint                                not null,
    remark            nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统模块', 'SCHEMA', 'dbo', 'TABLE', 'sys_module'
go

exec sp_addextendedproperty 'MS_Description', '模块主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '模块上级主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'parent_id'
go

exec sp_addextendedproperty 'MS_Description', '模块名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '模块编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '模块名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '模块参数', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'param_data'
go

exec sp_addextendedproperty 'MS_Description', 'Web地址', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'web_url'
go

exec sp_addextendedproperty 'MS_Description', 'Web图标', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'web_cls'
go

exec sp_addextendedproperty 'MS_Description', 'WinFrom窗口类', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'win_provider'
go

exec sp_addextendedproperty 'MS_Description', 'WinFrom大图标', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'win_large_icon'
go

exec sp_addextendedproperty 'MS_Description', 'WinFrom小图标', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'win_small_icon'
go

exec sp_addextendedproperty 'MS_Description', '是否Web菜单', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'web_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否WinForm菜单', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'win_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否展开', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'expand_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否公开', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'public_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否菜单', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'menu_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否显示为对话框', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'dialog_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否预加载', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'preloading_flag'
go

exec sp_addextendedproperty 'MS_Description', '是否矢量图', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'vector_graph_flag'
go

exec sp_addextendedproperty 'MS_Description', '排序路径', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'path'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_module', 'COLUMN', 'remark'
go


create table sys_module_map
(
    object_id bigint not null,
    module_id bigint not null
)
go

exec sp_addextendedproperty 'MS_Description', '系统对象模块关联', 'SCHEMA', 'dbo', 'TABLE', 'sys_module_map'
go

exec sp_addextendedproperty 'MS_Description', '对象主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_module_map', 'COLUMN', 'object_id'
go

exec sp_addextendedproperty 'MS_Description', '模块主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_module_map', 'COLUMN', 'module_id'
go

create index ix_ommap_o_m_id
    on sys_module_map (object_id, module_id)
go

create table sys_param
(
    id bigint not null
        constraint pk_param_id
            primary key,
    name nvarchar(100) not null,
    code nvarchar(100) not null
        constraint uk_param_code
            unique,
    value nvarchar(2000) not null,
    spell nvarchar(100),
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统参数', 'SCHEMA', 'dbo', 'TABLE', 'sys_param'
go

exec sp_addextendedproperty 'MS_Description', '参数主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '参数名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '参数编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '参数值', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'value'
go

exec sp_addextendedproperty 'MS_Description', '参数名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_param', 'COLUMN', 'remark'
go

create table sys_report
(
    id bigint not null
        constraint pk_report_id
            primary key,
    code nvarchar(100) not null
        constraint uk_report_code
            unique,
    name nvarchar(100) not null,
    url nvarchar(200),
    path nvarchar(500),
    md5 nvarchar(100),
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统报表', 'SCHEMA', 'dbo', 'TABLE', 'sys_report'
go

exec sp_addextendedproperty 'MS_Description', '报表主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '报表编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '报表名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '接口地址', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'url'
go

exec sp_addextendedproperty 'MS_Description', '报表文件路径', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'path'
go

exec sp_addextendedproperty 'MS_Description', '文件指纹', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'md5'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_report', 'COLUMN', 'remark'
go

create table sys_role
(
    id bigint not null
        constraint pk_role_id
            primary key,
    name nvarchar(100) not null,
    code nvarchar(100) not null
        constraint uk_role_code
            unique,
    spell nvarchar(100),
    dept_id bigint not null,
    dept_name nvarchar(100) not null,
    status tinyint not null,
    remark nvarchar(500),
    constraint uk_role_name
        unique (dept_id, name)
)
go

exec sp_addextendedproperty 'MS_Description', '系统角色', 'SCHEMA', 'dbo', 'TABLE', 'sys_role'
go

exec sp_addextendedproperty 'MS_Description', '角色主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '角色名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '角色编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '角色名称简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '机构主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'dept_id'
go

exec sp_addextendedproperty 'MS_Description', '机构名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'dept_name'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_role', 'COLUMN', 'remark'
go

create table sys_role_map
(
    user_id bigint not null,
    role_id bigint not null
)
go

exec sp_addextendedproperty 'MS_Description', '系统用户角色关联', 'SCHEMA', 'dbo', 'TABLE', 'sys_role_map'
go

exec sp_addextendedproperty 'MS_Description', '用户主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_role_map', 'COLUMN', 'user_id'
go

exec sp_addextendedproperty 'MS_Description', '角色主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_role_map', 'COLUMN', 'role_id'
go

create index ix_urmap_u_r_id
    on sys_role_map (user_id, role_id)
go

create table sys_seq
(
    id bigint not null
        constraint pk_seq_id
            primary key,
    name nvarchar(100) not null,
    code nvarchar(100) not null
        constraint uk_seq_code
            unique,
    start_with int not null,
    current_value int not null,
    increment_by int not null,
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统序列', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq'
go

exec sp_addextendedproperty 'MS_Description', '序列主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '序列名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '序列编码', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'code'
go

exec sp_addextendedproperty 'MS_Description', '开始值', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'start_with'
go

exec sp_addextendedproperty 'MS_Description', '当前值', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'current_value'
go

exec sp_addextendedproperty 'MS_Description', '步长', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'increment_by'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_seq', 'COLUMN', 'remark'
go

create table sys_user
(
    id bigint not null
        constraint pk_user_id
            primary key,
    name nvarchar(100) not null,
    spell nvarchar(100),
    account nvarchar(100) not null
        constraint uk_user_account
            unique,
    password nvarchar(100) not null,
    salt nvarchar(100) not null,
    mobile nvarchar(100),
    email nvarchar(100),
    dept_id bigint not null,
    dept_name nvarchar(100) not null,
    login_number int not null,
    admin tinyint not null,
    first_visit_time datetime,
    last_visit_time datetime,
    allow_start_time datetime,
    allow_end_time datetime,
    status tinyint not null,
    remark nvarchar(500)
)
go

exec sp_addextendedproperty 'MS_Description', '系统用户', 'SCHEMA', 'dbo', 'TABLE', 'sys_user'
go

exec sp_addextendedproperty 'MS_Description', '用户主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'id'
go

exec sp_addextendedproperty 'MS_Description', '用户姓名', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'name'
go

exec sp_addextendedproperty 'MS_Description', '用户姓名简拼', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'spell'
go

exec sp_addextendedproperty 'MS_Description', '账号', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'account'
go

exec sp_addextendedproperty 'MS_Description', '密码', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'password'
go

exec sp_addextendedproperty 'MS_Description', '密码盐', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'salt'
go

exec sp_addextendedproperty 'MS_Description', '手机号码', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'mobile'
go

exec sp_addextendedproperty 'MS_Description', '电子邮件', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'email'
go

exec sp_addextendedproperty 'MS_Description', '机构主键', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'dept_id'
go

exec sp_addextendedproperty 'MS_Description', '机构名称', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'dept_name'
go

exec sp_addextendedproperty 'MS_Description', '登录次数', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'login_number'
go

exec sp_addextendedproperty 'MS_Description', '管理员(0:否;1:是)', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'admin'
go

exec sp_addextendedproperty 'MS_Description', '首次登录时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'first_visit_time'
go

exec sp_addextendedproperty 'MS_Description', '最后登录时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'last_visit_time'
go

exec sp_addextendedproperty 'MS_Description', '有效期开始时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'allow_start_time'
go

exec sp_addextendedproperty 'MS_Description', '有效期结束时间', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'allow_end_time'
go

exec sp_addextendedproperty 'MS_Description', '状态(0:禁用;1:启用)', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'status'
go

exec sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', 'dbo', 'TABLE', 'sys_user', 'COLUMN', 'remark'
go