|
/*
init at 20170501
*/
DROP TABLE IF EXISTS accessory;
CREATE TABLE accessory (
accessory_id char(32) NOT NULL,
accessory_src varchar(255) NOT NULL,
operation_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
create_time char(14) NOT NULL,
accessory_name varchar(255)
);
COMMENT ON TABLE accessory IS '资源交易附件表（目前没用）';
COMMENT ON COLUMN accessory.accessory_id IS '附件ID';
COMMENT ON COLUMN accessory.accessory_src IS '附件路径';
COMMENT ON COLUMN accessory.operation_id IS '资源交易ID';
COMMENT ON COLUMN accessory.professor_id IS '用户ID';
COMMENT ON COLUMN accessory.create_time IS '创建时间';
COMMENT ON COLUMN accessory.accessory_name IS '附件名称';
-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS article;
CREATE TABLE article (
article_id char(32) NOT NULL,
professor_id char(32),
article_title text,
article_content text,
subject text,
industry text,
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL,
article_img varchar(255),
org_id char(32),
article_type char(1),
article_agree int4 NOT NULL,
page_views int4,
status char(1),
publish_time char(14)
);
COMMENT ON TABLE article IS '科研文章表';
COMMENT ON COLUMN article.article_id IS '文章ID';
COMMENT ON COLUMN article.professor_id IS '发布人ID';
COMMENT ON COLUMN article.article_title IS '文章标题';
COMMENT ON COLUMN article.article_content IS '文章内容';
COMMENT ON COLUMN article.subject IS '关键词';
COMMENT ON COLUMN article.industry IS '应用行业';
COMMENT ON COLUMN article.create_time IS '创建时间';
COMMENT ON COLUMN article.modify_time IS '修改时间';
COMMENT ON COLUMN article.article_img IS '文章封面图片';
COMMENT ON COLUMN article.org_id IS '企业ID';
COMMENT ON COLUMN article.article_type IS '文章类型(1-个人文章，2-企业文章)';
COMMENT ON COLUMN article.article_agree IS '文章被点赞数量';
COMMENT ON COLUMN article.page_views IS '浏览量';
COMMENT ON COLUMN article.status IS '文章状态(0-草稿，1-发布，2-定时发布，3-删除，4-关闭)';
COMMENT ON COLUMN article.publish_time IS '文章发布时间';
-- ----------------------------
-- Table structure for article_agree
-- ----------------------------
DROP TABLE IF EXISTS article_agree;
CREATE TABLE article_agree (
operate_id char(32) NOT NULL,
article_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE article_agree IS '文章点赞表';
COMMENT ON COLUMN article_agree.operate_id IS '点赞人的ID';
COMMENT ON COLUMN article_agree.article_id IS '被点赞的文章ID';
COMMENT ON COLUMN article_agree.create_time IS '点赞时间';
-- ----------------------------
-- Table structure for assess
-- ----------------------------
DROP TABLE IF EXISTS assess;
CREATE TABLE assess (
assess_id char(32) NOT NULL,
star_level int2 NOT NULL,
descp text,
operation_id char(32) NOT NULL,
resource_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE assess IS '交易评价表（暂时没用）';
COMMENT ON COLUMN assess.assess_id IS '评价ID';
COMMENT ON COLUMN assess.star_level IS '评价星级';
COMMENT ON COLUMN assess.descp IS '详细评价';
COMMENT ON COLUMN assess.operation_id IS '交易ID';
COMMENT ON COLUMN assess.resource_id IS '资源ID';
COMMENT ON COLUMN assess.professor_id IS '用户ID';
COMMENT ON COLUMN assess.create_time IS '创建时间';
-- ----------------------------
-- Table structure for auth_apply
-- ----------------------------
DROP TABLE IF EXISTS auth_apply;
CREATE TABLE auth_apply (
auth_apply_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
solve_status int2 DEFAULT 0 NOT NULL,
create_time char(14) NOT NULL,
apply_type int2 NOT NULL
);
COMMENT ON TABLE auth_apply IS '认证申请表';
COMMENT ON COLUMN auth_apply.auth_apply_id IS '认证申请ID';
COMMENT ON COLUMN auth_apply.professor_id IS '申请人ID（可以是个人或企业用户）';
COMMENT ON COLUMN auth_apply.solve_status IS '解决状态(-1-认证失败，1-待处理，2-处理中，3-认证通过)';
COMMENT ON COLUMN auth_apply.create_time IS '申请时间';
COMMENT ON COLUMN auth_apply.apply_type IS '申请认证类型(1-实名认证,2-专家认证,3-企业认证)';
DROP TABLE IF EXISTS auth_image;
CREATE TABLE auth_image (
auth_image_id char(32) NOT NULL,
auth_apply_id char(32) NOT NULL,
auth_src varchar(255) NOT NULL
);
COMMENT ON TABLE auth_image IS '认证申请图片表';
COMMENT ON COLUMN auth_image.auth_image_id IS '认证图片ID';
COMMENT ON COLUMN auth_image.auth_apply_id IS '认证申请ID';
COMMENT ON COLUMN auth_image.auth_src IS '认证图片路径';
DROP TABLE IF EXISTS complain;
CREATE TABLE complain (
complain_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
complain_contant varchar(255) NOT NULL,
appellee char(32),
create_time char(14) NOT NULL,
complain_type int2
);
COMMENT ON TABLE complain IS '投诉表';
COMMENT ON COLUMN complain.complain_id IS '投诉ID';
COMMENT ON COLUMN complain.professor_id IS '投诉人ID';
COMMENT ON COLUMN complain.complain_contant IS '投诉内容';
COMMENT ON COLUMN complain.appellee IS '被投诉ID';
COMMENT ON COLUMN complain.create_time IS '投诉时间';
COMMENT ON COLUMN complain.complain_type IS '投诉类型(0-建议，1-投诉)';
-- ----------------------------
-- Table structure for consult
-- ----------------------------
DROP TABLE IF EXISTS consult;
CREATE TABLE consult (
consult_id char(32) NOT NULL,
consult_type varchar(255) NOT NULL,
consult_title varchar(255) NOT NULL,
consult_contant text NOT NULL,
create_time char(14) NOT NULL,
professor_id char(32),
consultant_id char(32) NOT NULL,
consult_status int2,
finish_time char(14),
assess_status int2,
assess_star int2,
assess_contant text,
assess_time char(14),
thanks_status int2,
thanks_money numeric(24,2),
thanks_time char(14),
revovery_time char(14),
read_status int2 NOT NULL,
demand_id char(32)
);
COMMENT ON TABLE consult IS '咨询表';
COMMENT ON COLUMN consult.consult_id IS '咨询ID';
COMMENT ON COLUMN consult.consult_type IS '咨询类型';
COMMENT ON COLUMN consult.consult_title IS '咨询主题';
COMMENT ON COLUMN consult.consult_contant IS '咨询内容';
COMMENT ON COLUMN consult.create_time IS '创建时间';
COMMENT ON COLUMN consult.professor_id IS '专家ID';
COMMENT ON COLUMN consult.consultant_id IS '咨询者ID';
COMMENT ON COLUMN consult.consult_status IS '咨询状态(0-进行中,1-已完成,2-待回复,3-已谢绝)';
COMMENT ON COLUMN consult.finish_time IS '完成时间';
COMMENT ON COLUMN consult.assess_status IS '评价状态(0-待评价,1-已评价)';
COMMENT ON COLUMN consult.assess_star IS '评价星级';
COMMENT ON COLUMN consult.assess_contant IS '评价内容';
COMMENT ON COLUMN consult.assess_time IS '评价时间';
COMMENT ON COLUMN consult.thanks_status IS '感谢状态(0-未感谢，1-以感谢)';
COMMENT ON COLUMN consult.thanks_money IS '感谢金额';
COMMENT ON COLUMN consult.thanks_time IS '感谢时间';
COMMENT ON COLUMN consult.revovery_time IS '最后回复时间';
COMMENT ON COLUMN consult.read_status IS '查看状态(0-未查看，1-已查看)';
COMMENT ON COLUMN consult.demand_id IS '需求ID';
-- ----------------------------
-- Table structure for consult_reject
-- ----------------------------
DROP TABLE IF EXISTS consult_reject;
CREATE TABLE consult_reject (
reject_id char(32) NOT NULL,
rejector char(32) NOT NULL,
consult_id char(32) NOT NULL,
sort int4 NOT NULL,
reject_reason varchar(255) NOT NULL
);
COMMENT ON TABLE consult_reject IS '咨询谢绝表（谢绝理由）';
COMMENT ON COLUMN consult_reject.reject_id IS '拒绝id';
COMMENT ON COLUMN consult_reject.rejector IS '拒绝者';
COMMENT ON COLUMN consult_reject.consult_id IS '咨询ID';
COMMENT ON COLUMN consult_reject.sort IS '排序字段';
COMMENT ON COLUMN consult_reject.reject_reason IS '谢绝理由';
-- ----------------------------
-- Table structure for data_dict
-- ----------------------------
DROP TABLE IF EXISTS data_dict;
CREATE TABLE data_dict (
dict_code varchar(32) NOT NULL,
actived char(1) NOT NULL,
code varchar(32) NOT NULL,
caption varchar(255) NOT NULL,
parent_code varchar(32),
descp text
);
COMMENT ON TABLE data_dict IS '数据字典';
COMMENT ON COLUMN data_dict.dict_code IS '属性分类（学术领域-SUBJECT，应用行业-INDUSTRY，省份-PROVINCE，城市-ADDRESS，研究方向-RESEARCH_AREA）';
COMMENT ON COLUMN data_dict.actived IS '激活状态';
COMMENT ON COLUMN data_dict.code IS '唯一ID';
COMMENT ON COLUMN data_dict.caption IS '属性名称';
COMMENT ON COLUMN data_dict.parent_code IS '父ID';
COMMENT ON COLUMN data_dict.descp IS '详细描述';
-- ----------------------------
-- Table structure for demand
-- ----------------------------
DROP TABLE IF EXISTS demand;
CREATE TABLE demand (
demand_id char(32) NOT NULL,
demander char(32) NOT NULL,
demand_aim char(1) NOT NULL,
demand_type char(1) NOT NULL,
demand_title varchar(255) NOT NULL,
demand_content text,
demand_status char(1) NOT NULL,
order_key int8 NOT NULL,
create_time char(14) NOT NULL,
close_time char(14),
org_id char(32)
);
COMMENT ON TABLE demand IS '需求表';
COMMENT ON COLUMN demand.demand_id IS '需求ID';
COMMENT ON COLUMN demand.demander IS '需求者';
COMMENT ON COLUMN demand.demand_aim IS '需求目的(1-技术,2-资源,3-其他)';
COMMENT ON COLUMN demand.demand_type IS '需求类型(1-个人,2-企业)';
COMMENT ON COLUMN demand.demand_title IS '需求主题';
COMMENT ON COLUMN demand.demand_content IS '需求内容';
COMMENT ON COLUMN demand.demand_status IS '需求状态(0-关闭,1-发布中)';
COMMENT ON COLUMN demand.order_key IS '排序字段';
COMMENT ON COLUMN demand.create_time IS '发布时间';
COMMENT ON COLUMN demand.close_time IS '关闭时间';
COMMENT ON COLUMN demand.org_id IS '企业ID';
-- ----------------------------
-- Table structure for demand_sub_or_indus
-- ----------------------------
DROP TABLE IF EXISTS demand_sub_or_indus;
CREATE TABLE demand_sub_or_indus (
id char(32) NOT NULL,
sort int4 NOT NULL,
sub_or_indus varchar(255) NOT NULL,
demand_id char(32) NOT NULL
);
COMMENT ON TABLE demand_sub_or_indus IS '需求的学术领域或应用行业';
COMMENT ON COLUMN demand_sub_or_indus.id IS '主键ID';
COMMENT ON COLUMN demand_sub_or_indus.sort IS '排序字段';
COMMENT ON COLUMN demand_sub_or_indus.sub_or_indus IS '学术领域或应用行业';
COMMENT ON COLUMN demand_sub_or_indus.demand_id IS '需求ID';
-- ----------------------------
-- Table structure for dict_city
-- ----------------------------
DROP TABLE IF EXISTS dict_city;
CREATE TABLE dict_city (
dict_code varchar(32) NOT NULL,
caption varchar(255) NOT NULL,
parent_code varchar(32)
);
COMMENT ON TABLE dict_city IS '省份-城市二级数据字典';
COMMENT ON COLUMN dict_city.dict_code IS '城市代码';
COMMENT ON COLUMN dict_city.caption IS '城市名称';
COMMENT ON COLUMN dict_city.parent_code IS '上级代码';
-- ----------------------------
-- Table structure for dict_meta
-- ----------------------------
DROP TABLE IF EXISTS dict_meta;
CREATE TABLE dict_meta (
readonly char(1) NOT NULL,
code varchar(32) NOT NULL,
name varchar(255) NOT NULL,
tree char(1) NOT NULL,
descp text,
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL
);
-- ----------------------------
-- Table structure for growth_log
-- ----------------------------
DROP TABLE IF EXISTS growth_log;
CREATE TABLE growth_log (
id char(32) NOT NULL,
professor_id char(32) NOT NULL,
operate varchar(4),
score int4,
sign_in_days int4,
create_time char(14) NOT NULL,
invite_pro char(32)
);
COMMENT ON TABLE growth_log IS '用户积分成长记录表';
COMMENT ON COLUMN growth_log.id IS '主键ID';
COMMENT ON COLUMN growth_log.professor_id IS '用户ID';
COMMENT ON COLUMN growth_log.operate IS '操作代码（详细见积分规则）';
COMMENT ON COLUMN growth_log.score IS '分值';
COMMENT ON COLUMN growth_log.sign_in_days IS '签到天数';
COMMENT ON COLUMN growth_log.create_time IS '创建时间';
COMMENT ON COLUMN growth_log.invite_pro IS '邀请的用户';
-- ----------------------------
-- Table structure for honor
-- ----------------------------
DROP TABLE IF EXISTS honor;
CREATE TABLE honor (
id char(32) NOT NULL,
year char(4),
name text NOT NULL,
professor_id char(32) NOT NULL,
descp text
);
COMMENT ON TABLE honor IS '获得奖项';
COMMENT ON COLUMN honor.year IS '获奖年份';
COMMENT ON COLUMN honor.name IS '获奖名称';
COMMENT ON COLUMN honor.professor_id IS '用户ID';
COMMENT ON COLUMN honor.descp IS '详细描述';
-- ----------------------------
-- Table structure for image
-- ----------------------------
DROP TABLE IF EXISTS image;
CREATE TABLE image (
image_id char(32) NOT NULL,
image_src varchar(255) NOT NULL,
resource_id char(32) NOT NULL
);
COMMENT ON TABLE image IS '资源图片表';
COMMENT ON COLUMN image.image_id IS '图片ID';
COMMENT ON COLUMN image.image_src IS '图片路径';
COMMENT ON COLUMN image.resource_id IS '资源ID';
-- ----------------------------
-- Table structure for leave_word
-- ----------------------------
DROP TABLE IF EXISTS leave_word;
CREATE TABLE leave_word (
id char(32) NOT NULL,
content text NOT NULL,
sender char(32) NOT NULL,
article_id char(32) NOT NULL,
order_key int8 NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE leave_word IS '文章留言表';
COMMENT ON COLUMN leave_word.content IS '留言内容';
COMMENT ON COLUMN leave_word.sender IS '留言人ID';
COMMENT ON COLUMN leave_word.article_id IS '文章ID';
COMMENT ON COLUMN leave_word.order_key IS '排序字段';
COMMENT ON COLUMN leave_word.create_time IS '留言时间';
-- ----------------------------
-- Table structure for luser
-- ----------------------------
DROP TABLE IF EXISTS luser;
CREATE TABLE luser (
id char(32) NOT NULL,
email varchar(50),
mobile_phone char(11),
passwd char(32) NOT NULL,
user_type char(1) NOT NULL,
create_time char(14),
invite_code varchar(32),
send_mail_status int2 DEFAULT 0,
inviter_id char(32),
active_time char(14)
);
COMMENT ON TABLE luser IS '个人用户表';
COMMENT ON COLUMN luser.id IS '个人用户ID';
COMMENT ON COLUMN luser.email IS '注册邮箱';
COMMENT ON COLUMN luser.mobile_phone IS '注册手机';
COMMENT ON COLUMN luser.passwd IS '密码';
COMMENT ON COLUMN luser.user_type IS '用户类型(0-个人用户，1-企业用户)';
COMMENT ON COLUMN luser.create_time IS '创建时间';
COMMENT ON COLUMN luser.invite_code IS '邀请码';
COMMENT ON COLUMN luser.send_mail_status IS '是否发送邀请邮件(0-未发送,1-已发送)';
COMMENT ON COLUMN luser.inviter_id IS '邀请人ID';
COMMENT ON COLUMN luser.active_time IS '激活时间';
-- ----------------------------
-- Table structure for messages
-- ----------------------------
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
message_id char(32) NOT NULL,
operation_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
leave_word text,
create_time char(14) NOT NULL
);
COMMENT ON TABLE messages IS '交易留言信息（目前没用）';
COMMENT ON COLUMN messages.message_id IS '信息ID';
COMMENT ON COLUMN messages.operation_id IS '交易ID';
COMMENT ON COLUMN messages.professor_id IS '留言用户ID';
COMMENT ON COLUMN messages.leave_word IS '留言内容';
COMMENT ON COLUMN messages.create_time IS '创建时间';
-- ----------------------------
-- Table structure for operate_service_file
-- ----------------------------
DROP TABLE IF EXISTS operate_service_file;
CREATE TABLE operate_service_file (
service_file_id char(32) NOT NULL,
service_log_id char(32) NOT NULL,
file_src varchar(255) NOT NULL,
file_name varchar(255) NOT NULL
);
COMMENT ON TABLE operate_service_file IS '服务记录附件表';
COMMENT ON COLUMN operate_service_file.service_file_id IS '服务附件ID';
COMMENT ON COLUMN operate_service_file.service_log_id IS '服务记录ID';
COMMENT ON COLUMN operate_service_file.file_src IS '文件路径';
COMMENT ON COLUMN operate_service_file.file_name IS '文件名称';
-- ----------------------------
-- Table structure for operate_service_log
-- ----------------------------
DROP TABLE IF EXISTS operate_service_log;
CREATE TABLE operate_service_log (
service_log_id char(32) NOT NULL,
service_user char(32) NOT NULL,
servant varchar(255),
service_content varchar(255),
create_time char(14) NOT NULL
);
COMMENT ON TABLE operate_service_log IS '后台服务记录表';
COMMENT ON COLUMN operate_service_log.service_log_id IS '服务记录ID';
COMMENT ON COLUMN operate_service_log.service_user IS '服务用户';
COMMENT ON COLUMN operate_service_log.servant IS '服务者';
COMMENT ON COLUMN operate_service_log.service_content IS '服务内容';
COMMENT ON COLUMN operate_service_log.create_time IS '服务时间';
-- ----------------------------
-- Table structure for operation
-- ----------------------------
DROP TABLE IF EXISTS operation;
CREATE TABLE operation (
operation_id char(32) NOT NULL,
operation_status int2 NOT NULL,
demand_id char(32) NOT NULL,
detail_demand text,
deliver_time char(8),
resource_id char(32) NOT NULL,
deal_price numeric(24,2),
deal_time char(14),
pay_method varchar(255),
reply_notes varchar(255),
create_time char(14) NOT NULL,
applysquare varchar(255)
);
COMMENT ON TABLE operation IS '资源交易表（目前没用）';
COMMENT ON COLUMN operation.operation_id IS '交易ID';
COMMENT ON COLUMN operation.operation_status IS '交易状态';
COMMENT ON COLUMN operation.demand_id IS '需求方ID（需要资源的用户ID）';
COMMENT ON COLUMN operation.detail_demand IS '详细需求';
COMMENT ON COLUMN operation.deliver_time IS '交付时间';
COMMENT ON COLUMN operation.resource_id IS '资源ID';
COMMENT ON COLUMN operation.deal_price IS '成交价格';
COMMENT ON COLUMN operation.deal_time IS '成交时间';
COMMENT ON COLUMN operation.pay_method IS '支付方式';
COMMENT ON COLUMN operation.reply_notes IS '回复备注';
COMMENT ON COLUMN operation.create_time IS '创建时间';
COMMENT ON COLUMN operation.applysquare IS '申请方';
-- ----------------------------
-- Table structure for operation_status
-- ----------------------------
DROP TABLE IF EXISTS operation_status;
CREATE TABLE operation_status (
status_id int2 NOT NULL,
status_name varchar(255) NOT NULL
);
COMMENT ON TABLE operation_status IS '交易状态（目前没用）';
COMMENT ON COLUMN operation_status.status_id IS '交易状态ID';
COMMENT ON COLUMN operation_status.status_name IS '交易状态名称';
-- ----------------------------
-- Table structure for org_res_staff
-- ----------------------------
DROP TABLE IF EXISTS org_res_staff;
CREATE TABLE org_res_staff (
resource_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE org_res_staff IS '企业资源负责人';
COMMENT ON COLUMN org_res_staff.resource_id IS '资源ID';
COMMENT ON COLUMN org_res_staff.professor_id IS '用户ID';
COMMENT ON COLUMN org_res_staff.create_time IS '创建时间';
-- ----------------------------
-- Table structure for org_retrieve
-- ----------------------------
DROP TABLE IF EXISTS org_retrieve;
CREATE TABLE org_retrieve (
id char(32) NOT NULL,
name varchar(255),
org_id char(32),
email varchar(50),
passwd char(32),
solve_status varchar(2) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE org_retrieve IS '企业账号找回申请表';
COMMENT ON COLUMN org_retrieve.name IS '企业名称';
COMMENT ON COLUMN org_retrieve.org_id IS '企业ID';
COMMENT ON COLUMN org_retrieve.email IS '企业邮箱';
COMMENT ON COLUMN org_retrieve.passwd IS '密码';
COMMENT ON COLUMN org_retrieve.solve_status IS '解决状态(-1-认证失败，1-待处理，2-处理中，3-认证通过)';
COMMENT ON COLUMN org_retrieve.create_time IS '创建时间';
-- ----------------------------
-- Table structure for org_retrieve_img
-- ----------------------------
DROP TABLE IF EXISTS org_retrieve_img;
CREATE TABLE org_retrieve_img (
id char(32) NOT NULL,
org_retrieve_id char(32) NOT NULL,
src varchar(255) NOT NULL
);
COMMENT ON TABLE org_retrieve_img IS '企业账号找回申请图片表';
COMMENT ON COLUMN org_retrieve_img.org_retrieve_id IS '企业账号找回申请ID';
COMMENT ON COLUMN org_retrieve_img.src IS '图片路径';
-- ----------------------------
-- Table structure for org_subject
-- ----------------------------
DROP TABLE IF EXISTS org_subject;
CREATE TABLE org_subject (
organization_id char(32) NOT NULL,
subject_id int2 NOT NULL
);
-- ----------------------------
-- Table structure for org_user
-- ----------------------------
DROP TABLE IF EXISTS org_user;
CREATE TABLE org_user (
id char(32) NOT NULL,
email varchar(50),
passwd char(32) NOT NULL,
user_type char(1),
inviter_id char(32),
create_time char(14) NOT NULL
);
COMMENT ON TABLE org_user IS '企业用户表';
COMMENT ON COLUMN org_user.id IS '企业用户ID';
COMMENT ON COLUMN org_user.email IS '注册邮箱';
COMMENT ON COLUMN org_user.passwd IS '密码';
COMMENT ON COLUMN org_user.user_type IS '用户类型(0-个人用户，1-企业用户)';
COMMENT ON COLUMN org_user.inviter_id IS '邀请人ID（目前没用）';
COMMENT ON COLUMN org_user.create_time IS '创建时间';
-- ----------------------------
-- Table structure for organization
-- ----------------------------
DROP TABLE IF EXISTS organization;
CREATE TABLE organization (
org_attr varchar(32),
id char(32) NOT NULL,
name varchar(100) COLLATE zh_CN NOT NULL,
org_type varchar(32),
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL,
descp text,
org_url varchar(255),
found_time char(8),
province varchar(255),
city varchar(255),
subject text,
industry text,
org_size char(1),
qualification text,
is_join char(1),
auth_status varchar(2),
for_short varchar(20)
);
COMMENT ON TABLE organization IS '企业信息表';
COMMENT ON COLUMN organization.org_attr IS '字段类型待修改，逗号分隔，多个属性';
COMMENT ON COLUMN organization.name IS '企业名称';
COMMENT ON COLUMN organization.org_type IS '企业类型(2-上市企业，3-国有企业，4-合资企业，5-私人企业，6-外资企业，7-初创企业)';
COMMENT ON COLUMN organization.create_time IS '创建时间';
COMMENT ON COLUMN organization.modify_time IS '修改时间';
COMMENT ON COLUMN organization.descp IS '企业简介';
COMMENT ON COLUMN organization.org_url IS '企业官网';
COMMENT ON COLUMN organization.found_time IS '创立时间';
COMMENT ON COLUMN organization.province IS '所在省份';
COMMENT ON COLUMN organization.city IS '所在城市';
COMMENT ON COLUMN organization.subject IS '学术领域';
COMMENT ON COLUMN organization.industry IS '应用行业';
COMMENT ON COLUMN organization.org_size IS '企业规模(1:50人以内，2:50-100人，3:100-200人，4:200-500人，5:500-1000人，6:1000人以上)';
COMMENT ON COLUMN organization.qualification IS '企业资质';
COMMENT ON COLUMN organization.is_join IS '是否入驻(0-未入驻，1-已入驻)';
COMMENT ON COLUMN organization.auth_status IS '企业认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN organization.for_short IS '企业简称';
-- ----------------------------
-- Table structure for paper
-- ----------------------------
DROP TABLE IF EXISTS paper;
CREATE TABLE paper (
professor_id char(32) NOT NULL,
id char(32) NOT NULL,
year char(4),
name text NOT NULL,
url text,
descp text
);
COMMENT ON TABLE paper IS '专家发表的书刊杂志记录表';
COMMENT ON COLUMN paper.professor_id IS '用户ID';
COMMENT ON COLUMN paper.year IS '年份';
COMMENT ON COLUMN paper.name IS '书刊杂志名';
COMMENT ON COLUMN paper.url IS '链接';
COMMENT ON COLUMN paper.descp IS '详细描述';
-- ----------------------------
-- Table structure for part_time_job
-- ----------------------------
DROP TABLE IF EXISTS part_time_job;
CREATE TABLE part_time_job (
id char(32) NOT NULL,
start_month char(6),
stop_month char(6),
professor_id char(32) NOT NULL,
company varchar(255) NOT NULL,
title varchar(255) NOT NULL,
descp text,
department varchar(255)
);
COMMENT ON TABLE part_time_job IS '工作经历表';
COMMENT ON COLUMN part_time_job.start_month IS '入职时间（年月）';
COMMENT ON COLUMN part_time_job.stop_month IS '离职时间（年月）';
COMMENT ON COLUMN part_time_job.professor_id IS '用户ID';
COMMENT ON COLUMN part_time_job.company IS '所在公司';
COMMENT ON COLUMN part_time_job.title IS '职务';
COMMENT ON COLUMN part_time_job.descp IS '详细描述';
COMMENT ON COLUMN part_time_job.department IS '所在部门';
-- ----------------------------
-- Table structure for patent
-- ----------------------------
DROP TABLE IF EXISTS patent;
CREATE TABLE patent (
id char(32) NOT NULL,
year char(4),
name text NOT NULL,
professor_id char(32) NOT NULL,
url varchar(255),
descp text
);
COMMENT ON TABLE patent IS '专利表';
COMMENT ON COLUMN patent.year IS '年份';
COMMENT ON COLUMN patent.name IS '专利名';
COMMENT ON COLUMN patent.professor_id IS '用户ID';
COMMENT ON COLUMN patent.url IS '链接';
COMMENT ON COLUMN patent.descp IS '详细描述';
-- ----------------------------
-- Table structure for pay_history
-- ----------------------------
DROP TABLE IF EXISTS pay_history;
CREATE TABLE pay_history (
pay_history_id char(32) NOT NULL,
operation_id char(32) NOT NULL,
paid_money numeric(24,2),
create_time char(14) NOT NULL,
remark text,
isonline int2,
return_money numeric(24,2),
pay_type int2,
pay_status int2,
professor_id char(32) NOT NULL
);
COMMENT ON TABLE pay_history IS '支付历史记录（暂时没用）';
-- ----------------------------
-- Table structure for professor
-- ----------------------------
DROP TABLE IF EXISTS professor;
CREATE TABLE professor (
office text,
subject text,
industry text,
department varchar(255),
org_id char(32),
title varchar(255),
id char(32) NOT NULL,
name varchar(255) NOT NULL,
descp text,
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL,
address varchar(255),
star_level numeric(4,2) DEFAULT 0 NOT NULL,
consult_count int2 DEFAULT 0 NOT NULL,
province varchar(255),
star_avg numeric(4,2) DEFAULT 0 NOT NULL,
authentication int2,
auth_type int2 DEFAULT 0 NOT NULL,
auth_status int2 DEFAULT 0 NOT NULL,
phone varchar(20),
email varchar(50),
sort_first int2 DEFAULT 0 NOT NULL,
auth_status_expert int2 DEFAULT 0,
org_auth char(1),
score_value int4,
growth_value int4,
score_percent varchar(10),
id_card char(18),
cuser_id char(32),
professor_state int2 DEFAULT 0 NOT NULL
);
COMMENT ON TABLE professor IS '个人用户信息表';
COMMENT ON COLUMN professor.office IS '职位';
COMMENT ON COLUMN professor.subject IS '学术领域';
COMMENT ON COLUMN professor.industry IS '应用行业';
COMMENT ON COLUMN professor.department IS '所属部门';
COMMENT ON COLUMN professor.org_id IS '所属企业ID';
COMMENT ON COLUMN professor.title IS '职称';
COMMENT ON COLUMN professor.id IS '用户ID';
COMMENT ON COLUMN professor.name IS '姓名';
COMMENT ON COLUMN professor.descp IS '个人简介';
COMMENT ON COLUMN professor.create_time IS '创建时间';
COMMENT ON COLUMN professor.modify_time IS '修改时间';
COMMENT ON COLUMN professor.address IS '所在城市';
COMMENT ON COLUMN professor.star_level IS '星级';
COMMENT ON COLUMN professor.consult_count IS '接受的咨询次数（完成状态）';
COMMENT ON COLUMN professor.province IS '所在省份';
COMMENT ON COLUMN professor.star_avg IS '平均星级';
COMMENT ON COLUMN professor.authentication IS '用户身份(0-普通用户,1-科研工作者,2-企业高管,3-在校生,4-技术人员,5-HR/猎头,6-销售人员,7-投资方,8-咨询顾问)';
COMMENT ON COLUMN professor.auth_type IS '用户类型(0-普通用户,1-专家用户)';
COMMENT ON COLUMN professor.auth_status IS '实名认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN professor.phone IS '联系电话';
COMMENT ON COLUMN professor.email IS '联系邮箱';
COMMENT ON COLUMN professor.sort_first IS '第一排序字段(运营人员可修改)';
COMMENT ON COLUMN professor.auth_status_expert IS '专家认证状态(-1-认证失败,0-未认证,1-待认证,2-认证中,3-已认证)';
COMMENT ON COLUMN professor.org_auth IS '企业认证状态(0-未认证，1-已认证)';
COMMENT ON COLUMN professor.score_value IS '积分值';
COMMENT ON COLUMN professor.growth_value IS '成长值';
COMMENT ON COLUMN professor.score_percent IS '积分超过的用户百分比';
COMMENT ON COLUMN professor.id_card IS '身份证号';
COMMENT ON COLUMN professor.cuser_id IS '录入员id（运营人员id）';
COMMENT ON COLUMN professor.professor_state IS '专家录入审核状态（0=审核通过2=未审核1=审核失败）';
-- ----------------------------
-- Table structure for professor_edu_bg
-- ----------------------------
DROP TABLE IF EXISTS professor_edu_bg;
CREATE TABLE professor_edu_bg (
id char(32) NOT NULL,
year char(4),
student char(1) NOT NULL,
degree varchar(255),
professor_id char(32) NOT NULL,
school varchar(255) NOT NULL,
college varchar(255),
major varchar(255),
descp text
);
COMMENT ON TABLE professor_edu_bg IS '用户教育背景';
COMMENT ON COLUMN professor_edu_bg.year IS '年份';
-- ----------------------------
-- Table structure for project
-- ----------------------------
DROP TABLE IF EXISTS project;
CREATE TABLE project (
id char(32) NOT NULL,
start_month char(6),
stop_month char(6),
professor_id char(32) NOT NULL,
name text NOT NULL,
descp text
);
COMMENT ON TABLE project IS '项目经验表';
-- ----------------------------
-- Table structure for research_area
-- ----------------------------
DROP TABLE IF EXISTS research_area;
CREATE TABLE research_area (
sort_num int4 NOT NULL,
descp text,
count int4 NOT NULL,
professor_id char(32) NOT NULL,
caption varchar(255) NOT NULL
);
COMMENT ON TABLE research_area IS '研究方向表';
COMMENT ON COLUMN research_area.count IS '被赞次数';
COMMENT ON COLUMN research_area.professor_id IS '用户ID';
COMMENT ON COLUMN research_area.caption IS '研究方向';
-- ----------------------------
-- Table structure for research_area_log
-- ----------------------------
DROP TABLE IF EXISTS research_area_log;
CREATE TABLE research_area_log (
oprete_professor_id char(32) NOT NULL,
create_time char(14) NOT NULL,
professor_id char(32) NOT NULL,
caption varchar(255) NOT NULL
);
COMMENT ON TABLE research_area_log IS '研究方向点赞记录表';
COMMENT ON COLUMN research_area_log.oprete_professor_id IS '点赞用户ID';
COMMENT ON COLUMN research_area_log.create_time IS '点赞时间';
COMMENT ON COLUMN research_area_log.professor_id IS '被赞用户ID';
COMMENT ON COLUMN research_area_log.caption IS '被赞的研究方向';
-- ----------------------------
-- Table structure for resource
-- ----------------------------
DROP TABLE IF EXISTS resource;
CREATE TABLE resource (
resource_id char(32) NOT NULL,
resource_name varchar(255) COLLATE zh_CN NOT NULL,
supported_services varchar(255) NOT NULL,
descp text,
professor_id char(32) NOT NULL,
ascription varchar(255),
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL,
hope_pay_method varchar(255),
cooperation_notes text,
subject text,
industry text
);
COMMENT ON TABLE resource IS '资源表';
COMMENT ON COLUMN resource.resource_id IS '资源ID';
COMMENT ON COLUMN resource.resource_name IS '资源名称';
COMMENT ON COLUMN resource.supported_services IS '应用用途';
COMMENT ON COLUMN resource.descp IS '详细描述';
COMMENT ON COLUMN resource.professor_id IS '专家id';
COMMENT ON COLUMN resource.ascription IS '暂时没有用（所属人）';
COMMENT ON COLUMN resource.create_time IS '创建时间';
COMMENT ON COLUMN resource.modify_time IS '修改时间';
COMMENT ON COLUMN resource.hope_pay_method IS '期望付款方式（暂时没有用）';
COMMENT ON COLUMN resource.cooperation_notes IS '合作备注';
COMMENT ON COLUMN resource.subject IS '关键词';
COMMENT ON COLUMN resource.industry IS '暂时没有用（应用行业）';
COMMENT ON COLUMN resource.page_views IS '浏览量';
COMMENT ON COLUMN resource.comp IS '所在单位';
COMMENT ON COLUMN resource.resource_type IS '资源类型(1-个人资源，2-企业资源)';
-- ----------------------------
-- Table structure for resource_tmp
-- ----------------------------
DROP TABLE IF EXISTS resource_tmp;
CREATE TABLE resource_tmp (
id varchar(255) NOT NULL,
name text NOT NULL,
domain text,
comp text,
spec text,
img text,
parameter text,
major_func text,
cost float8,
enabled_time text,
communication text,
org_name text,
corporation text,
linkman text,
tel text,
email text,
addr text,
zip_code text,
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL
);
COMMENT ON COLUMN resource_tmp.name IS '资源名称(resource_name)';
COMMENT ON COLUMN resource_tmp.domain IS '关键词(subject)';
COMMENT ON COLUMN resource_tmp.comp IS '所在单位(comp)';
COMMENT ON COLUMN resource_tmp.spec IS '厂商及型号规格(spec)';
COMMENT ON COLUMN resource_tmp.img IS '图片路径';
COMMENT ON COLUMN resource_tmp.parameter IS '性能参数(paremeter)';
COMMENT ON COLUMN resource_tmp.major_func IS '应用用途(supported_services、desc)';
COMMENT ON COLUMN resource_tmp.cost IS '仪器原值（万元）';
COMMENT ON COLUMN resource_tmp.enabled_time IS '启用时间';
COMMENT ON COLUMN resource_tmp.communication IS '联系方式';
COMMENT ON COLUMN resource_tmp.org_name IS '机构名称';
COMMENT ON COLUMN resource_tmp.corporation IS '负责人';
COMMENT ON COLUMN resource_tmp.linkman IS '联系人 ';
COMMENT ON COLUMN resource_tmp.tel IS '电话';
COMMENT ON COLUMN resource_tmp.email IS '邮箱';
COMMENT ON COLUMN resource_tmp.addr IS '地址';
COMMENT ON COLUMN resource_tmp.zip_code IS '邮编';
-- ----------------------------
-- Table structure for resource_tmp_log
-- ----------------------------
DROP TABLE IF EXISTS resource_tmp_log;
CREATE TABLE resource_tmp_log (
owner_id char(32) NOT NULL,
resource_id char(32) NOT NULL,
resource_tmp_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE resource_tmp_log IS '资源导入记录表';
COMMENT ON COLUMN resource_tmp_log.owner_id IS '操作的用户ID（个人或企业）';
COMMENT ON COLUMN resource_tmp_log.resource_id IS '资源ID';
COMMENT ON COLUMN resource_tmp_log.resource_tmp_id IS '临时资源ID';
COMMENT ON COLUMN resource_tmp_log.create_time IS '导入时间';
-- ----------------------------
-- Table structure for tidings
-- ----------------------------
DROP TABLE IF EXISTS tidings;
CREATE TABLE tidings (
tidings_id char(32) NOT NULL,
tidings_contant text NOT NULL,
sender_id char(32) NOT NULL,
consult_id char(32) NOT NULL,
create_time char(14) NOT NULL,
read_status int2 NOT NULL
);
COMMENT ON COLUMN tidings.tidings_id IS '咨询消息ID';
COMMENT ON COLUMN tidings.tidings_contant IS '消息内容';
COMMENT ON COLUMN tidings.sender_id IS '发送者ID';
COMMENT ON COLUMN tidings.consult_id IS '咨询ID';
COMMENT ON COLUMN tidings.create_time IS '创建时间';
COMMENT ON COLUMN tidings.read_status IS '查看状态（0-未查看，1-已查看）';
-- ----------------------------
-- Table structure for user_open_id
-- ----------------------------
DROP TABLE IF EXISTS user_open_id;
CREATE TABLE user_open_id (
userid char(32) NOT NULL,
oauth_type text NOT NULL,
openid text NOT NULL,
create_time char(14) NOT NULL,
modify_time char(14) NOT NULL
);
-- ----------------------------
-- Table structure for watch
-- ----------------------------
DROP TABLE IF EXISTS watch;
CREATE TABLE watch (
professor_id char(32) NOT NULL,
watch_object char(32) NOT NULL,
create_time char(14) NOT NULL,
watch_type int2 NOT NULL
);
COMMENT ON TABLE watch IS '关注表';
COMMENT ON COLUMN watch.professor_id IS '专家ID';
COMMENT ON COLUMN watch.watch_object IS '关注对象ID';
COMMENT ON COLUMN watch.create_time IS '关注时间';
COMMENT ON COLUMN watch.watch_type IS '关注类型(专家-1，资源-2，3-文章)';
ALTER TABLE accessory ADD PRIMARY KEY (accessory_id);
-- ----------------------------
-- Primary Key structure for table article
-- ----------------------------
ALTER TABLE article ADD PRIMARY KEY (article_id);
-- ----------------------------
-- Primary Key structure for table article_agree
-- ----------------------------
ALTER TABLE article_agree ADD PRIMARY KEY (operate_id, article_id);
-- ----------------------------
-- Primary Key structure for table assess
-- ----------------------------
ALTER TABLE assess ADD PRIMARY KEY (assess_id);
-- ----------------------------
-- Primary Key structure for table auth_apply
-- ----------------------------
ALTER TABLE auth_apply ADD PRIMARY KEY (auth_apply_id);
-- ----------------------------
-- Primary Key structure for table auth_image
-- ----------------------------
ALTER TABLE auth_image ADD PRIMARY KEY (auth_image_id);
-- ----------------------------
-- Primary Key structure for table complain
-- ----------------------------
ALTER TABLE complain ADD PRIMARY KEY (complain_id);
-- ----------------------------
-- Primary Key structure for table consult
-- ----------------------------
ALTER TABLE consult ADD PRIMARY KEY (consult_id);
-- ----------------------------
-- Primary Key structure for table consult_reject
-- ----------------------------
ALTER TABLE consult_reject ADD PRIMARY KEY (reject_id);
-- ----------------------------
-- Uniques structure for table data_dict
-- ----------------------------
ALTER TABLE data_dict ADD UNIQUE (dict_code, caption);
-- ----------------------------
-- Primary Key structure for table data_dict
-- ----------------------------
ALTER TABLE data_dict ADD PRIMARY KEY (code, dict_code);
-- ----------------------------
-- Primary Key structure for table demand
-- ----------------------------
ALTER TABLE demand ADD PRIMARY KEY (demand_id);
-- ----------------------------
-- Primary Key structure for table demand_sub_or_indus
-- ----------------------------
ALTER TABLE demand_sub_or_indus ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table dict_city
-- ----------------------------
ALTER TABLE dict_city ADD PRIMARY KEY (dict_code);
-- ----------------------------
-- Primary Key structure for table dict_meta
-- ----------------------------
ALTER TABLE dict_meta ADD PRIMARY KEY (code);
-- ----------------------------
-- Primary Key structure for table growth_log
-- ----------------------------
ALTER TABLE growth_log ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table honor
-- ----------------------------
ALTER TABLE honor ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table image
-- ----------------------------
ALTER TABLE image ADD PRIMARY KEY (image_id);
-- ----------------------------
-- Primary Key structure for table leave_word
-- ----------------------------
ALTER TABLE leave_word ADD PRIMARY KEY (id);
-- ----------------------------
-- Uniques structure for table luser
-- ----------------------------
ALTER TABLE luser ADD UNIQUE (email);
ALTER TABLE luser ADD UNIQUE (mobile_phone);
-- ----------------------------
-- Primary Key structure for table luser
-- ----------------------------
ALTER TABLE luser ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table messages
-- ----------------------------
ALTER TABLE messages ADD PRIMARY KEY (message_id);
-- ----------------------------
-- Primary Key structure for table operate_service_file
-- ----------------------------
ALTER TABLE operate_service_file ADD PRIMARY KEY (service_file_id);
-- ----------------------------
-- Primary Key structure for table operate_service_log
-- ----------------------------
ALTER TABLE operate_service_log ADD PRIMARY KEY (service_log_id);
-- ----------------------------
-- Primary Key structure for table operation
-- ----------------------------
ALTER TABLE operation ADD PRIMARY KEY (operation_id);
-- ----------------------------
-- Primary Key structure for table operation_status
-- ----------------------------
ALTER TABLE operation_status ADD PRIMARY KEY (status_id);
-- ----------------------------
-- Uniques structure for table org_res_staff
-- ----------------------------
ALTER TABLE org_res_staff ADD UNIQUE (resource_id, professor_id);
-- ----------------------------
-- Primary Key structure for table org_retrieve
-- ----------------------------
ALTER TABLE org_retrieve ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table org_retrieve_img
-- ----------------------------
ALTER TABLE org_retrieve_img ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table org_subject
-- ----------------------------
ALTER TABLE org_subject ADD PRIMARY KEY (organization_id, subject_id);
-- ----------------------------
-- Uniques structure for table org_user
-- ----------------------------
ALTER TABLE org_user ADD UNIQUE (email);
-- ----------------------------
-- Primary Key structure for table org_user
-- ----------------------------
ALTER TABLE org_user ADD PRIMARY KEY (id);
-- ----------------------------
-- Indexes structure for table organization
-- ----------------------------
CREATE UNIQUE INDEX organization_name_key ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key1 ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key2 ON organization USING btree (name COLLATE zh_CN);
CREATE UNIQUE INDEX organization_name_key3 ON organization USING btree (name COLLATE zh_CN);
-- ----------------------------
-- Uniques structure for table organization
-- ----------------------------
ALTER TABLE organization ADD UNIQUE (name);
-- ----------------------------
-- Primary Key structure for table organization
-- ----------------------------
ALTER TABLE organization ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table paper
-- ----------------------------
ALTER TABLE paper ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table part_time_job
-- ----------------------------
ALTER TABLE part_time_job ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table patent
-- ----------------------------
ALTER TABLE patent ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table pay_history
-- ----------------------------
ALTER TABLE pay_history ADD PRIMARY KEY (pay_history_id);
-- ----------------------------
-- Primary Key structure for table professor
-- ----------------------------
ALTER TABLE professor ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table professor_edu_bg
-- ----------------------------
ALTER TABLE professor_edu_bg ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table project
-- ----------------------------
ALTER TABLE project ADD PRIMARY KEY (id);
-- ----------------------------
-- Primary Key structure for table research_area
-- ----------------------------
ALTER TABLE research_area ADD PRIMARY KEY (professor_id, caption);
-- ----------------------------
-- Primary Key structure for table resource
-- ----------------------------
ALTER TABLE resource ADD PRIMARY KEY (resource_id);
-- ----------------------------
-- Primary Key structure for table resource_tmp
-- ----------------------------
ALTER TABLE resource_tmp ADD PRIMARY KEY (id);
-- ----------------------------
-- Uniques structure for table resource_tmp_log
-- ----------------------------
ALTER TABLE resource_tmp_log ADD UNIQUE (resource_id, resource_tmp_id);
-- ----------------------------
-- Primary Key structure for table tidings
-- ----------------------------
ALTER TABLE tidings ADD PRIMARY KEY (tidings_id);
-- ----------------------------
-- Uniques structure for table user_open_id
-- ----------------------------
ALTER TABLE user_open_id ADD UNIQUE (userid, oauth_type);
ALTER TABLE user_open_id ADD UNIQUE (oauth_type, openid);
-- ----------------------------
-- Primary Key structure for table user_open_id
-- ----------------------------
ALTER TABLE user_open_id ADD PRIMARY KEY (oauth_type, openid, userid);
-- ----------------------------
-- Uniques structure for table watch
-- ----------------------------
ALTER TABLE watch ADD UNIQUE (professor_id, watch_object, watch_type);
-- ----------------------------
-- Primary Key structure for table watch
-- ----------------------------
ALTER TABLE watch ADD PRIMARY KEY (professor_id, watch_object);
/*
* modify 20170607
*
*
*
*/
ALTER TABLE resource ADD COLUMN org_name VARCHAR (255);
ALTER TABLE resource ADD COLUMN spec VARCHAR (255);
COMMENT ON COLUMN resource.spec IS '厂商及型号规格';
ALTER TABLE resource ADD COLUMN PARAMETER TEXT;
COMMENT ON COLUMN resource. PARAMETER IS '性能参数';
ALTER TABLE resource ADD COLUMN publish_time CHAR (14);
COMMENT ON COLUMN resource.publish_time IS '发布时间';
ALTER TABLE resource ADD COLUMN status CHAR (1);
COMMENT ON COLUMN resource.status IS '资源状态(0-草稿，1-发布，3-删除，4-关闭)';
ALTER TABLE image ADD COLUMN sort INTEGER NULL;
UPDATE image SET sort = 1;
ALTER TABLE image ALTER COLUMN sort SET NOT NULL;
UPDATE image SET image_src = REPLACE ( image_src, '/kexiu/www/html/images/resource','201704');
ALTER TABLE resource ADD COLUMN page_views INTEGER;
COMMENT ON COLUMN resource.page_views IS '浏览量';
UPDATE resource SET page_views = 0, status = '1', publish_time = modify_time;
ALTER TABLE resource ADD COLUMN comp VARCHAR (255);
COMMENT ON COLUMN resource.comp IS '所在单位';
ALTER TABLE resource ADD COLUMN resource_type CHAR (1);
COMMENT ON COLUMN resource.resource_type IS '资源类型(1-个人资源，2-企业资源)';
ALTER TABLE resource ALTER COLUMN professor_id DROP NOT NULL;
ALTER TABLE resource ADD COLUMN org_id CHAR (32);
CREATE TABLE ORG_RES_STAFF (
RESOURCE_ID CHAR (32) NOT NULL,
PROFESSOR_ID CHAR (32) NOT NULL,
CREATE_TIME CHAR (14) NOT NULL
);
ALTER TABLE ORG_RES_STAFF ADD UNIQUE (RESOURCE_ID, PROFESSOR_ID);
CREATE TABLE RESOURCE_TMP_LOG (
OWNER_ID CHAR (32) NOT NULL,
RESOURCE_ID CHAR (32) NOT NULL,
RESOURCE_TMP_ID CHAR (32) NOT NULL,
CREATE_TIME CHAR (14) NOT NULL
);
ALTER TABLE RESOURCE_TMP_LOG ADD UNIQUE (
RESOURCE_ID,
RESOURCE_TMP_ID
);
ALTER TABLE article ADD COLUMN page_views INT4;
COMMENT ON COLUMN article.page_views IS '浏览量';
ALTER TABLE article ADD COLUMN status CHAR (1);
COMMENT ON COLUMN article.status IS '文章状态(0-草稿，1-发布，2-删除，3关闭)';
ALTER TABLE article ADD COLUMN publish_time CHAR(14);
COMMENT ON COLUMN article.publish_time IS '文章发布时间';
UPDATE article SET page_views = 0,status = '1',publish_time = modify_time;
-- ----------------------------
-- Table structure for article_pro
-- ----------------------------
DROP TABLE IF EXISTS article_pro;
CREATE TABLE article_pro (
article_id char(32) NOT NULL,
professor_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE article_pro IS '文章相关专家';
COMMENT ON COLUMN article_pro.article_id IS '文章ID';
COMMENT ON COLUMN article_pro.professor_id IS '用户ID';
COMMENT ON COLUMN article_pro.create_time IS '创建时间';
DROP TABLE IF EXISTS article_res;
CREATE TABLE article_res (
article_id char(32) NOT NULL,
resource_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE article_res IS '文章相关资源';
COMMENT ON COLUMN article_res.article_id IS '文章ID';
COMMENT ON COLUMN article_res.resource_id IS '资源ID';
COMMENT ON COLUMN article_res.create_time IS '创建时间';
-- ----------------------------
-- Uniques structure for table article_res
-- ----------------------------
ALTER TABLE article_res ADD PRIMARY KEY (article_id, resource_id);
-- ----------------------------
-- Uniques structure for table article_pro
-- ----------------------------
ALTER TABLE article_pro ADD PRIMARY KEY (article_id, professor_id);
update resource set resource_type='1';
-----begin version 1.9------------------------
ALTER TABLE organization ADD COLUMN addr text COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.addr IS '企为地址';
ALTER TABLE organization ADD COLUMN email text;
COMMENT ON COLUMN organization.email IS '联系邮箱';
ALTER TABLE organization ADD COLUMN contact_num text;
COMMENT ON COLUMN organization.contact_num IS '联系电话';
ALTER TABLE organization ADD COLUMN field_of_customer text COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.field_of_customer IS '客户领域';
ALTER TABLE organization ADD COLUMN field_of_supplier text COLLATE "zh_CN.utf8";
COMMENT ON COLUMN organization.field_of_supplier IS '供应商领域';
ALTER TABLE organization ADD COLUMN sort_num int8 default 0 not null;
COMMENT ON COLUMN organization.sort_num IS '排序值';
ALTER TABLE organization ADD COLUMN page_views int8 default 0 not null;
COMMENT ON COLUMN organization.page_views IS '浏览量';
COMMENT ON COLUMN watch.watch_type IS '关注类型(专家-1，资源-2，3-文章,4-专利 ，5-论文,6-企业)';
CREATE TABLE ppatent (
id char(32) NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
page_views int8 DEFAULT 0 NOT NULL,
code text NOT NULL,
req_code text NOT NULL,
req_person text COLLATE "zh_CN.utf8",
req_day char(8) NOT NULL,
pub_day char(8) NOT NULL,
summary text COLLATE "zh_CN.utf8" NOT NULL,
ref_param text ,
authors text COLLATE "zh_CN.utf8" NOT NULL,
sort_num int8 DEFAULT 0 NOT NULL,
create_time char(14) NOT NULL,
keywords text COLLATE "zh_CN.utf8"
);
COMMENT ON COLUMN ppatent.name IS '名称';
COMMENT ON COLUMN ppatent.page_views IS '浏览量';
COMMENT ON COLUMN ppatent.code IS '专利号';
COMMENT ON COLUMN ppatent.req_code IS '申请号';
COMMENT ON COLUMN ppatent.req_person IS '申请人';
COMMENT ON COLUMN ppatent.summary IS '摘要';
COMMENT ON COLUMN ppatent.keywords IS '关键词';
COMMENT ON COLUMN ppatent.authors IS '作者列表，以英文逗号分隔';
COMMENT ON COLUMN ppatent.sort_num IS '排序字段，人工权重';
CREATE UNIQUE INDEX ppatent_pkey ON ppatent USING btree (id);
ALTER TABLE ppatent ADD PRIMARY KEY (id);
CREATE TABLE patent_author (
patent_id char(32) NOT NULL,
professor_id char(32) DEFAULT '################################'::bpchar NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
ass_time char(14) DEFAULT '00010101010101'::bpchar NOT NULL);
COMMENT ON COLUMN patent_author.patent_id IS '专利ID';
COMMENT ON COLUMN patent_author.professor_id IS '专家id(=32个#时，还没有关专家)';
COMMENT ON COLUMN patent_author.name IS '作者姓名';
COMMENT ON COLUMN patent_author.ass_time IS '关联专家时间';
CREATE UNIQUE INDEX patent_author_pkey ON patent_author USING btree (patent_id,name COLLATE "zh_CN.utf8");
ALTER TABLE patent_author ADD PRIMARY KEY (patent_id,name);
CREATE TABLE ppaper (
id char(32) NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
page_views int8 DEFAULT 0 NOT NULL,
cn4periodical text,
en4periodical text,
periodicaltype text,
pub_day text,
summary text COLLATE "zh_CN.utf8" NOT NULL,
keywords text COLLATE "zh_CN.utf8",
authors text COLLATE "zh_CN.utf8" NOT NULL,
sort_num int8 DEFAULT 0 NOT NULL,
create_time char(14) NOT NULL,
ref_param text);
COMMENT ON COLUMN ppaper.name IS '名称';
COMMENT ON COLUMN ppaper.page_views IS '浏览量';
COMMENT ON COLUMN ppaper.cn4periodical IS '期刊中文名';
COMMENT ON COLUMN ppaper.en4periodical IS '期刊英文名';
COMMENT ON COLUMN ppaper.pub_day IS '发表期数';
COMMENT ON COLUMN ppaper.summary IS '摘要';
COMMENT ON COLUMN ppaper.keywords IS '关键词';
COMMENT ON COLUMN ppaper.authors IS '作者列表，以英文逗号分隔';
COMMENT ON COLUMN ppaper.sort_num IS '排序字段，人工权重';
CREATE UNIQUE INDEX ppaper_pkey ON ppaper USING btree (id);
ALTER TABLE ppaper ADD PRIMARY KEY (id);
CREATE TABLE paper_author (
paper_id char(32) NOT NULL,
professor_id char(32) DEFAULT '################################'::bpchar NOT NULL,
name text COLLATE "zh_CN.utf8" NOT NULL,
ass_time char(14) DEFAULT '00010101010101'::bpchar NOT NULL);
COMMENT ON COLUMN paper_author.paper_id IS '专利ID';
COMMENT ON COLUMN paper_author.professor_id IS '专家id(=32个#时，还没有关专家)';
COMMENT ON COLUMN paper_author.name IS '作者姓名';
COMMENT ON COLUMN paper_author.ass_time IS '关联专家时间';
CREATE UNIQUE INDEX paper_author_pkey ON paper_author USING btree (paper_id,name COLLATE "zh_CN.utf8");
ALTER TABLE paper_author ADD PRIMARY KEY (paper_id,name);
CREATE TABLE ORG_REG_INFO (
NAME TEXT COLLATE "zh_CN.utf8" NOT NULL,
NUM TEXT NOT NULL,
CODE TEXT NOT NULL,
CREDIT_CODE TEXT,
"TYPE" TEXT NOT NULL,
INDUSTRY TEXT NOT NULL,
OPERATING_PERIOD TEXT NOT NULL,
DAY_OF_APPROVAL TEXT NOT NULL,
MANAGER TEXT NOT NULL,
ADDR TEXT NOT NULL,
SCOPE_OF_BUSINESS TEXT NOT NULL);
ALTER TABLE ORG_REG_INFO ADD PRIMARY KEY (CODE);
COMMENT ON COLUMN ORG_REG_INFO.NAME IS '注册名称';
COMMENT ON COLUMN ORG_REG_INFO.NUM IS '工商注册号';
COMMENT ON COLUMN ORG_REG_INFO.CODE IS '组织机构代码';
COMMENT ON COLUMN ORG_REG_INFO.CREDIT_CODE IS '统一信用代码';
COMMENT ON COLUMN ORG_REG_INFO."TYPE" IS '企业类型';
COMMENT ON COLUMN ORG_REG_INFO.INDUSTRY IS '行业';
COMMENT ON COLUMN ORG_REG_INFO.OPERATING_PERIOD IS '营业期限';
COMMENT ON COLUMN ORG_REG_INFO.DAY_OF_APPROVAL IS '核准日期';
COMMENT ON COLUMN ORG_REG_INFO.MANAGER IS '登记机关';
COMMENT ON COLUMN ORG_REG_INFO.ADDR IS '注册地址';
COMMENT ON COLUMN ORG_REG_INFO.SCOPE_OF_BUSINESS IS '经营范围';
CREATE TABLE feedback (
id bigserial NOT NULL,
title text,
category int4 NOT NULL,
"schema" int4 NOT NULL,
param text NOT NULL,
content text NOT NULL,
create_time char(14) NOT NULL,
state char(1) NOT NULL,
PRIMARY KEY (id)
);
COMMENT ON TABLE feedback IS '反馈信息';
COMMENT ON COLUMN feedback.id IS '反馈信息ID';
COMMENT ON COLUMN feedback.title IS '标题';
COMMENT ON COLUMN feedback.category IS '大类 1：纠错';
COMMENT ON COLUMN feedback."schema" IS '小类类 1.1：论文纠错，1.2：专利纠错';
COMMENT ON COLUMN feedback.content IS '内容';
COMMENT ON COLUMN feedback.param IS '参数：如 论文ID....';
COMMENT ON COLUMN feedback.state IS '状态，0：初始状态 ，其他由不同大类业务确定';
update organization set org_type = null;
|
