|
/*
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 ,
CODE TEXT,
CREDIT_CODE TEXT,
TYPE TEXT,
INDUSTRY TEXT,
OPERATING_PERIOD TEXT,
DAY_OF_APPROVAL TEXT,
MANAGER TEXT,
ADDR TEXT,
SCOPE_OF_BUSINESS TEXT);
ALTER TABLE ORG_REG_INFO ADD PRIMARY KEY (NAME);
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,
user_id text,
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：论文，2：专利, 3 专家 4 机构 5 资源 6 文章';
COMMENT ON COLUMN feedback.content IS '内容';
COMMENT ON COLUMN feedback.param IS '参数：如 论文ID....';
COMMENT ON COLUMN feedback.state IS '状态，0：初始状态 ，其他由不同大类业务确定';
COMMENT ON COLUMN feedback.user_id IS '返馈人ID';
create sequence professor_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE professor ADD COLUMN share_id int8 DEFAULT nextval('professor_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN professor.share_id IS '共享ID';
create sequence organization_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE organization ADD COLUMN share_id int8 DEFAULT nextval('organization_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN organization.share_id IS '共享ID';
create sequence article_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE article ADD COLUMN share_id int8 DEFAULT nextval('article_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN article.share_id IS '共享ID';
create sequence resource_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE resource ADD COLUMN share_id int8 DEFAULT nextval('resource_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN resource.share_id IS '共享ID';
create sequence ppaper_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE ppaper ADD COLUMN share_id int8 DEFAULT nextval('ppaper_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN ppaper.share_id IS '共享ID';
create sequence ppatent_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
ALTER TABLE ppatent ADD COLUMN share_id int8 DEFAULT nextval('ppatent_share_id_seq'::regclass) NOT NULL;
COMMENT ON COLUMN ppatent.share_id IS '共享ID';
update organization set org_type = null;
ALTER TABLE organization ADD COLUMN linkman text;
COMMENT ON COLUMN organization.linkman IS '联系人';
ALTER TABLE article ADD COLUMN sort_num int8 default 0 not null;
COMMENT ON COLUMN article.sort_num IS '排序字段，人工权重';
ALTER TABLE resource ADD COLUMN sort_num int8 default 0 not null;
COMMENT ON COLUMN resource.sort_num IS '排序字段，人工权重';
--begin v1.9.1
ALTER TABLE professor ADD COLUMN page_views int8 default 0 not null;
COMMENT ON COLUMN professor.page_views IS '浏览量';
CREATE TABLE art_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE art_key_word IS '文章关键字';
COMMENT ON COLUMN art_key_word.id IS '文章ID';
COMMENT ON COLUMN art_key_word.kw IS '关键字';
CREATE TABLE res_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE res_key_word IS '资源关键字';
COMMENT ON COLUMN res_key_word.id IS '资源ID';
COMMENT ON COLUMN res_key_word.kw IS '关键字';
CREATE TABLE pro_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pro_key_word IS '用户关键字';
COMMENT ON COLUMN pro_key_word.id IS '用户ID';
COMMENT ON COLUMN pro_key_word.kw IS '关键字';
CREATE TABLE org_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE org_key_word IS '企业关键字';
COMMENT ON COLUMN org_key_word.id IS '企业ID';
COMMENT ON COLUMN org_key_word.kw IS '关键字';
CREATE TABLE pap_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pap_key_word IS '论文关键字';
COMMENT ON COLUMN pap_key_word.id IS '论文ID';
COMMENT ON COLUMN pap_key_word.kw IS '关键字';
CREATE TABLE pat_key_word (id char(32) NOT NULL,kw text not null);
COMMENT ON TABLE pat_key_word IS '专利关键字';
COMMENT ON COLUMN pat_key_word.id IS '专利ID';
COMMENT ON COLUMN pat_key_word.kw IS '关键字';
CREATE TABLE ARTICLE_ORG (ARTICLE_ID CHAR(32) NOT NULL,ORG_ID CHAR(32) NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE ARTICLE_ORG ADD UNIQUE (ARTICLE_ID,ORG_ID);
COMMENT ON COLUMN ARTICLE_ORG.ARTICLE_ID IS '文章ID';
COMMENT ON COLUMN ARTICLE_ORG.ORG_ID IS '企业ID';
alter table leave_word add column category char(1) not null default '1';
comment on column leave_word.category is '留言分类 1： 文章 2：论文 3：专利';
ALTER TABLE leave_word RENAME article_id TO obj_id;
comment on column leave_word.obj_id is '留言对象ID';
CREATE TABLE paper_agree (
op_id char(32) NOT NULL,
paper_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE paper_agree IS '论文点赞表';
COMMENT ON COLUMN paper_agree.op_id IS '点赞人的ID';
COMMENT ON COLUMN paper_agree.paper_id IS '被点赞的论文ID';
COMMENT ON COLUMN paper_agree.create_time IS '点赞时间';
CREATE TABLE patent_agree (
op_id char(32) NOT NULL,
patent_id char(32) NOT NULL,
create_time char(14) NOT NULL
);
COMMENT ON TABLE patent_agree IS '专利点赞表';
COMMENT ON COLUMN patent_agree.op_id IS '点赞人的ID';
COMMENT ON COLUMN patent_agree.patent_id IS '被点赞的专利ID';
COMMENT ON COLUMN patent_agree.create_time IS '点赞时间';
ALTER TABLE patent_agree ADD PRIMARY KEY (op_id,patent_id);
ALTER TABLE paper_agree ADD PRIMARY KEY (op_id,paper_id);
ALTER TABLE organization ALTER COLUMN is_join SET DEFAULT '0';
UPDATE organization set is_join='0' WHERE is_join is null;
ALTER TABLE organization ALTER COLUMN is_join SET NOT NULL;
drop view view_user_count;
drop view view_user_pictable;
drop view view_allcustomer;
drop view view_customer;
drop view view_my_customer;
ALTER TABLE professor ALTER COLUMN phone TYPE varchar(50);
CREATE OR REPLACE VIEW view_allcustomer AS
SELECT professor.id,professor.email,professor.phone,luser.mobile_phone,luser.create_time,luser.active_time,
luser.send_mail_status,luser.invite_code,luser.inviter_id,luser.user_type,professor.subject,professor.industry,
professor.department,professor.office,professor.org_id,professor.title,professor.name,professor.address,
professor.authentication,professor.auth_type,professor.auth_status,professor.sort_first,professor.auth_status_expert,
organization.name AS orgname
FROM ((luser RIGHT JOIN professor ON ((luser.id = professor.id)))
LEFT JOIN organization ON ((professor.org_id = organization.id)));;
CREATE OR REPLACE VIEW view_customer AS
SELECT professor.id,professor.email,professor.phone,luser.mobile_phone,luser.create_time,luser.active_time,luser.send_mail_status,
luser.invite_code,luser.inviter_id,luser.user_type,professor.subject,professor.industry,professor.department,professor.office,
professor.org_id,professor.title,professor.name,professor.address,professor.authentication,professor.auth_type,professor.auth_status,
professor.sort_first,professor.auth_status_expert,organization.name AS orgname
FROM ((luser
RIGHT JOIN professor ON ((luser.id = professor.id)))
LEFT JOIN organization ON ((professor.org_id = organization.id)))
WHERE (((luser.inviter_id IS NULL) OR (luser.active_time IS NULL)) AND (NOT (EXISTS ( SELECT customer_power.professor_id
FROM customer_power
WHERE ((customer_power.professor_id = luser.id) AND ((customer_power.pow_type = '0'::bpchar) OR (customer_power.pow_type = '1'::bpchar)))))));;
CREATE OR REPLACE VIEW view_my_customer AS
SELECT customer_power.power_id,professor.id,professor.email,professor.subject,professor.industry,professor.department,professor.office,professor.org_id,
professor.title,professor.name,professor.address,professor.authentication,professor.auth_type,professor.auth_status,professor.sort_first,professor.auth_status_expert,
organization.name AS orgname,customer_power.descp,customer_power.create_time,customer_power.pow_type,customer_power.modify_time,customer_power.cuser_id,
luser.mobile_phone AS phone,cuser.name AS cuser_name,luser.invite_code
FROM ((((customer_power
JOIN professor ON ((customer_power.professor_id = professor.id)))
JOIN organization ON ((professor.org_id = organization.id)))
JOIN luser ON ((professor.id = luser.id)))
JOIN cuser ON ((customer_power.cuser_id = cuser.id)));;
CREATE OR REPLACE VIEW view_user_count AS
SELECT to_date(cc.create_time, 'yyyymmdd'::text) AS create_time,
COALESCE(znum.num, (0)::bigint) AS znum,
COALESCE(jnum.jnum, (0)::bigint) AS jnum,
dn.ljnum,
dn.lznum
FROM (((( SELECT to_char(generate_series((date_trunc('month'::text, now()) - '1 mon'::interval), now(), '1 day'::interval), 'yyyymmdd'::text) AS create_time) cc
LEFT JOIN ( SELECT num.create_time,count(num.id) AS num
FROM ( SELECT luser.id,
"substring"((luser.create_time)::text, 1, 8) AS create_time
FROM luser
WHERE (to_date((luser.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (now() - '30 days'::interval))) num
GROUP BY num.create_time) znum ON ((cc.create_time = znum.create_time)))
LEFT JOIN ( SELECT num1.create_time,
count(num1.id) AS jnum
FROM ( SELECT luser.id,
"substring"((luser.active_time)::text, 1, 8) AS create_time
FROM luser
WHERE (to_date((luser.active_time)::text, 'YYYYMMDDHH24MISS'::text) >= (now() - '30 days'::interval))) num1
GROUP BY num1.create_time) jnum ON ((cc.create_time = jnum.create_time)))
LEFT JOIN ( SELECT d1.create_time,
0 AS num,
0 AS jnum,
d1.ljnum,
d1.lznum
FROM ( SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))
UNION
SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time
UNION
SELECT lj1.create_time,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
0 AS ljnum,
count(luser.id) AS lznum
FROM luser
WHERE ("substring"((luser.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))
UNION
SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
count(luser.id) AS ljnum,
0 AS lznum
FROM luser
WHERE ("substring"((luser.active_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))) lj1
GROUP BY lj1.create_time) d1) dn ON ((cc.create_time = dn.create_time)));;
CREATE OR REPLACE VIEW view_user_pictable AS
SELECT dn.create_time,
dn.cuser_id,
sum(dn.znum) AS znum, sum(dn.jnum) AS jnum,
sum(dn.ljnum) AS ljnum,
sum(dn.lznum) AS lznum
FROM ( SELECT to_date(u.create_time, 'YYYYMMDD'::text) AS create_time,
u.cuser_id,
0 AS znum,
0 AS jnum,
0 AS ljnum,
0 AS lznum
FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser
UNION
SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
cuser.id AS cuser_id
FROM cuser) u
UNION
SELECT to_date(n1.create_time, 'YYYYMMDD'::text) AS create_time,
n1.cuser_id,
n1.znum,
0 AS jnum,
(0)::numeric AS ljnum,
(0)::numeric AS lznum
FROM ( SELECT "substring"((view_my_customer.create_time)::text, 1, 8) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS znum
FROM view_my_customer
WHERE (to_date((view_my_customer.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (('now'::text)::date - '6 days'::interval))
GROUP BY "substring"((view_my_customer.create_time)::text, 1, 8), view_my_customer.cuser_id) n1
UNION
SELECT to_date(j1.create_time, 'YYYYMMDD'::text) AS create_time,
j1.cuser_id,
0 AS znum,
j1.jnum,
(0)::numeric AS ljnum,
(0)::numeric AS lznum
FROM ( SELECT "substring"((view_my_customer.create_time)::text, 1, 8) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS jnum
FROM view_my_customer
WHERE ((to_date((view_my_customer.create_time)::text, 'YYYYMMDDHH24MISS'::text) >= (('now'::text)::date - '6 days'::interval)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY "substring"((view_my_customer.create_time)::text, 1, 8), view_my_customer.cuser_id) j1
UNION
SELECT to_date(d1.create_time, 'YYYYMMDD'::text) AS create_time,
d1.cuser_id,
0 AS znum,
0 AS jnum,
d1.ljnum,
d1.lznum
FROM ( SELECT lj1.create_time,
lj1.cuser_id,
sum(lj1.ljnum) AS ljnum,
sum(lj1.lznum) AS lznum
FROM ( SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char(now(), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char(now(), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj1
GROUP BY lj1.create_time, lj1.cuser_id
UNION
SELECT lj2.create_time,
lj2.cuser_id,
sum(lj2.ljnum) AS ljnum,
sum(lj2.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '1 day'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj2
GROUP BY lj2.create_time, lj2.cuser_id
UNION
SELECT lj3.create_time,
lj3.cuser_id,
sum(lj3.ljnum) AS ljnum,
sum(lj3.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '2 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj3
GROUP BY lj3.create_time, lj3.cuser_id
UNION
SELECT lj4.create_time,
lj4.cuser_id,
sum(lj4.ljnum) AS ljnum,
sum(lj4.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '3 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj4
GROUP BY lj4.create_time, lj4.cuser_id
UNION
SELECT lj5.create_time,
lj5.cuser_id,
sum(lj5.ljnum) AS ljnum,
sum(lj5.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '4 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj5
GROUP BY lj5.create_time, lj5.cuser_id
UNION
SELECT lj6.create_time,
lj6.cuser_id,
sum(lj6.ljnum) AS ljnum,
sum(lj6.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '5 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj6
GROUP BY lj6.create_time, lj6.cuser_id
UNION
SELECT lj7.create_time,
lj7.cuser_id,
sum(lj7.ljnum) AS ljnum,
sum(lj7.lznum) AS lznum
FROM ( SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
0 AS ljnum,
count(view_my_customer.power_id) AS lznum
FROM view_my_customer
WHERE ("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text))
GROUP BY view_my_customer.cuser_id
UNION
SELECT to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text) AS create_time,
view_my_customer.cuser_id,
count(view_my_customer.power_id) AS ljnum,
0 AS lznum
FROM view_my_customer
WHERE (("substring"((view_my_customer.create_time)::text, 1, 8) <= to_char((('now'::text)::date - '6 days'::interval), 'YYYYMMDD'::text)) AND (view_my_customer.pow_type = '1'::bpchar))
GROUP BY view_my_customer.cuser_id) lj7
GROUP BY lj7.create_time, lj7.cuser_id) d1) dn
GROUP BY dn.create_time, dn.cuser_id
ORDER BY dn.create_time;;
--- modify begin at version 1.9.2
-- ALTER TABLE ARTICLE ADD COLUMN COL_NUM int4 not null default 0;
-- COMMENT ON COLUMN article.col_num IS '栏目号 <=0:没有栏目号，1：个人自建 2：企业自发 其它：由业务定义';
-- update article set col_num=1 where professor_id is not null;
-- update article set col_num=2 where org_id is not null;
ALTER TABLE organization ADD COLUMN col_mgr char(1) NOT NULL DEFAULT '0';
COMMENT ON COLUMN organization.col_mgr IS '是否是管理者 0：不是 1:是';
CREATE TABLE discover_banner (
id bigserial PRIMARY KEY NOT NULL, -- 编号id
title CHARACTER VARYING(50) NOT NULL, -- 外显标题
banner SMALLINT NOT NULL, -- 栏目
begin_time CHARACTER(14), -- 上架时间
end_time CHARACTER(14), -- 下架时间
clicks BIGINT NOT NULL DEFAULT 0, -- 点击量
descp TEXT, -- 备注
article_id CHARACTER(32) NOT NULL, -- 文章ID
modify_time CHARACTER(14) NOT NULL, -- 修改时间
modifier CHARACTER VARYING(32), -- 最后修改人ID
create_time CHARACTER(14) NOT NULL, -- 创建时间
creator CHARACTER VARYING(32) NOT NULL, -- 创建人ID
location SMALLINT
);
COMMENT ON COLUMN discover_banner.id IS '编号id';
COMMENT ON COLUMN discover_banner.title IS '外显标题';
COMMENT ON COLUMN discover_banner.banner IS '栏目';
COMMENT ON COLUMN discover_banner.begin_time IS '上架时间';
COMMENT ON COLUMN discover_banner.end_time IS '下架时间';
COMMENT ON COLUMN discover_banner.clicks IS '点击量';
COMMENT ON COLUMN discover_banner.descp IS '备注';
COMMENT ON COLUMN discover_banner.article_id IS '文章ID';
COMMENT ON COLUMN discover_banner.modify_time IS '修改时间';
COMMENT ON COLUMN discover_banner.modifier IS '最后修改人ID';
COMMENT ON COLUMN discover_banner.create_time IS '创建时间';
COMMENT ON COLUMN discover_banner.creator IS '创建人ID';
COMMENT ON COLUMN discover_banner.location IS '显示位置';
--- modify begin at version 2.0
drop table DEMAND_SUB_OR_INDUS;
drop table DEMAND;
CREATE TABLE DEMAND (
ID CHAR(32) NOT NULL,
TITLE TEXT NOT NULL,
PROVINCE TEXT NOT NULL,
CITY TEXT,
COST CHAR(1) NOT NULL,
DURATION CHAR(1) NOT NULL,
INVALID_DAY CHAR(8) NOT NULL,
CONTACT_NUM TEXT NOT NULL,
STATE CHAR(1) NOT NULL,
ORG_ID CHAR(32) NOT NULL,
MODIFIER CHAR(32) NOT NULL,
CREATOR CHAR(32) NOT NULL,
DESCP TEXT NOT NULL,
PAGE_VIEWS INT8 NOT NULL,
SHARE_ID bigserial NOT NULL,
CREATE_TIME CHAR(14) NOT NULL,
MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE DEMAND ADD PRIMARY KEY (ID);
COMMENT ON TABLE DEMAND IS '需求';
COMMENT ON COLUMN DEMAND.ID IS '需求ID';
COMMENT ON COLUMN DEMAND.TITLE IS '主题';
COMMENT ON COLUMN DEMAND.PROVINCE IS '省份';
COMMENT ON COLUMN DEMAND.CITY IS '城市';
COMMENT ON COLUMN DEMAND.COST IS '费用预算';
COMMENT ON COLUMN DEMAND.DURATION IS '预计时长';
COMMENT ON COLUMN DEMAND.INVALID_DAY IS '有效期';
COMMENT ON COLUMN DEMAND.CONTACT_NUM IS '联系电话';
COMMENT ON COLUMN DEMAND.STATE IS '状态';
COMMENT ON COLUMN DEMAND.ORG_ID IS '发布人发布时机构ID';
COMMENT ON COLUMN DEMAND.MODIFIER IS '最后修改人';
COMMENT ON COLUMN DEMAND.CREATOR IS '发布人';
COMMENT ON COLUMN DEMAND.DESCP IS '内容';
COMMENT ON COLUMN DEMAND.PAGE_VIEWS IS '浏览量';
COMMENT ON COLUMN DEMAND.SHARE_ID IS '分享ID';
COMMENT ON COLUMN DEMAND.CREATE_TIME IS '发布时间';
COMMENT ON COLUMN DEMAND.MODIFY_TIME IS '最后修改时间，=完成时间 =关闭时间';
ALTER TABLE organization ADD COLUMN res_mgr char(1) NOT NULL DEFAULT '0';
COMMENT ON COLUMN organization.res_mgr IS '是否是资源管理者 0：不是 1:是';
--- modify begin at version 2.1
CREATE TABLE WEB_MSG_IDX (
OWNER TEXT NOT NULL,
ACTOR TEXT NOT NULL,
LAST_TIME BIGINT NOT NULL,
NUM_OF_OWNER INTEGER NOT NULL,
NUM_OF_ACTOR INTEGER NOT NULL,
LAST_CNT TEXT NOT NULL,
SHOW_OF_OWNER CHAR(1) NOT NULL,
SHOW_OF_ACTOR CHAR(1) NOT NULL);
ALTER TABLE WEB_MSG_IDX ADD PRIMARY KEY (OWNER,ACTOR);
CREATE TABLE WEB_MSG_CNT (CNT TEXT NOT NULL,SEND_TIME TEXT NOT NULL,SENDER TEXT NOT NULL,RECIVER TEXT NOT NULL,READED CHAR(1) NOT NULL);
ALTER TABLE WEB_MSG_CNT ADD PRIMARY KEY (SENDER,RECIVER,SEND_TIME);
COMMENT ON TABLE WEB_MSG_IDX is '消息会话表';
COMMENT ON COLUMN WEB_MSG_IDX.OWNER IS '会话所有人ID(两个人中ID小的为会话所有人)';
COMMENT ON COLUMN WEB_MSG_IDX.ACTOR IS '会话参与人ID(两个人中ID大的为会话参与人)';
COMMENT ON COLUMN WEB_MSG_IDX.LAST_TIME IS '会话最后活动时间';
COMMENT ON COLUMN WEB_MSG_IDX.NUM_OF_OWNER IS '会话所有人未读消息数';
COMMENT ON COLUMN WEB_MSG_IDX.NUM_OF_ACTOR IS '会话参与人未读消息数';
COMMENT ON COLUMN WEB_MSG_IDX.LAST_CNT IS '会话最后内容';
COMMENT ON COLUMN WEB_MSG_IDX.SHOW_OF_OWNER IS '是否存在与所有的人会话列表中';
COMMENT ON COLUMN WEB_MSG_IDX.SHOW_OF_ACTOR IS '是否存在与参与的人会话列表中';
COMMENT ON TABLE WEB_MSG_CNT IS '消息内容表';
COMMENT ON COLUMN WEB_MSG_CNT.SENDER is '消息发送人ID';
COMMENT ON COLUMN WEB_MSG_CNT.RECIVER is '消息接收人ID';
COMMENT ON COLUMN WEB_MSG_CNT.CNT is '消息内容';
COMMENT ON COLUMN WEB_MSG_CNT.SEND_TIME is '消息发送时间';
COMMENT ON COLUMN WEB_MSG_CNT.READED is '消息是否已读';
--modify begin at version2.20
CREATE TABLE LEAVE_MSG (ID CHAR(32) NOT NULL,CNT TEXT NOT NULL,REF_ID TEXT NOT NULL,REF_TYPE CHAR(1) NOT NULL,
TOP TEXT NOT NULL,PARENT TEXT,SENDER TEXT NOT NULL,RECIVER TEXT,STATE CHAR(1) NOT NULL,AGREE_COUNT BIGINT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL,MODIFY_TIME CHAR(14) NOT NULL,TIME_DESC TEXT NOT NULL);
ALTER TABLE LEAVE_MSG ADD PRIMARY KEY (ID);
COMMENT ON TABLE LEAVE_MSG is '留言表（新）';
COMMENT ON COLUMN LEAVE_MSG.ID is '留言ID';
COMMENT ON COLUMN LEAVE_MSG.CNT is '留言内容';
COMMENT ON COLUMN LEAVE_MSG.REF_ID is '留言对象ID';
COMMENT ON COLUMN LEAVE_MSG.REF_TYPE is '留言对象分类 1：文章 2：论文 3：专利 4:回答';
COMMENT ON COLUMN LEAVE_MSG.TOP is '顶级留言（只针对对象不针对留言）';
COMMENT ON COLUMN LEAVE_MSG.PARENT is '回复的留言ID';
COMMENT ON COLUMN LEAVE_MSG.SENDER is '留言人ID';
COMMENT ON COLUMN LEAVE_MSG.RECIVER is '被回复的留言人ID';
COMMENT ON COLUMN LEAVE_MSG.STATE is '状态 1：发布中';
COMMENT ON COLUMN LEAVE_MSG.AGREE_COUNT is '被点赞数量';
CREATE TABLE LEAVE_MSG_AGREE_REC (ID TEXT NOT NULL,UID TEXT NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE LEAVE_MSG_AGREE_REC ADD PRIMARY KEY (ID,UID);
COMMENT ON TABLE LEAVE_MSG_AGREE_REC is '留言点赞记录表';
COMMENT ON COLUMN LEAVE_MSG_AGREE_REC.ID is '留言ID';
COMMENT ON COLUMN LEAVE_MSG_AGREE_REC.UID is '点赞人ID';
CREATE TABLE QUESTION (ID CHAR(32) NOT NULL,TITLE TEXT NOT NULL,
CNT TEXT,IMG TEXT,KEYS TEXT NOT NULL,
UID TEXT NOT NULL,LAST_REPLY_TIME CHAR(14),PAGE_VIEWS BIGINT NOT NULL,
REPLY_COUNT BIGINT NOT NULL,STATE CHAR(1) NOT NULL,CREATE_TIME CHAR(14) NOT NULL,
TIME_DESC TEXT NOT NULL,MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE QUESTION ADD PRIMARY KEY (ID);
COMMENT ON TABLE QUESTION is '提问表';
COMMENT ON COLUMN QUESTION.ID is '提问ID';
COMMENT ON COLUMN QUESTION.TITLE is '提问标题';
COMMENT ON COLUMN QUESTION.CNT is '提问描述';
COMMENT ON COLUMN QUESTION.IMG is '提问图片（英文逗号分隔）';
COMMENT ON COLUMN QUESTION.KEYS is '提问关键词（英文逗号分隔）';
COMMENT ON COLUMN QUESTION.UID is '提问人ID';
COMMENT ON COLUMN QUESTION.LAST_REPLY_TIME is '最后回答时间';
COMMENT ON COLUMN QUESTION.REPLY_COUNT is '回答次数';
COMMENT ON COLUMN QUESTION.STATE is '状态 1：发布中';
CREATE TABLE ANSWER (ID CHAR(32) NOT NULL,QID TEXT NOT NULL,UID TEXT NOT NULL,STATE CHAR(1) NOT NULL,
CNT TEXT NOT NULL,AGREE BIGINT NOT NULL,BALLOT BIGINT NOT NULL,CREATE_TIME CHAR(14) NOT NULL,
TIME_DESC TEXT NOT NULL,MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE ANSWER ADD PRIMARY KEY (ID);
ALTER TABLE ANSWER ADD UNIQUE (QID,UID);
COMMENT ON TABLE ANSWER is '回答表';
COMMENT ON COLUMN ANSWER.ID is '回答ID';
COMMENT ON COLUMN ANSWER.QID is '提问ID';
COMMENT ON COLUMN ANSWER.CNT is '回答内容';
COMMENT ON COLUMN ANSWER.UID is '回答人ID';
COMMENT ON COLUMN ANSWER.AGREE is '赞同票数';
COMMENT ON COLUMN ANSWER.BALLOT is '总投票数';
COMMENT ON COLUMN ANSWER.STATE is '状态 1：发布中';
CREATE TABLE ANSWER_AGREE_REC (UID TEXT NOT NULL,AID TEXT NOT NULL,FLAG CHAR(1) NOT NULL);
ALTER TABLE ANSWER_AGREE_REC ADD PRIMARY KEY (UID,AID);
COMMENT ON TABLE ANSWER_AGREE_REC is '回答投票表';
COMMENT ON COLUMN ANSWER_AGREE_REC.AID is '回答ID';
COMMENT ON COLUMN ANSWER_AGREE_REC.UID is '投票人ID';
COMMENT ON COLUMN ANSWER_AGREE_REC.FLAG is '1：赞同票 0：反对票';
CREATE TABLE QET_KEY_WORD (ID TEXT NOT NULL,KW TEXT NOT NULL);
ALTER TABLE QET_KEY_WORD ADD PRIMARY KEY (ID,KW);
CREATE TABLE NOTIFY_MSG_IDX (RECIVER TEXT NOT NULL,LAST_CNT TEXT NOT NULL,LAST_TIME TEXT NOT NULL,UN_READ INTEGER NOT NULL);
ALTER TABLE NOTIFY_MSG_IDX ADD PRIMARY KEY (RECIVER);
COMMENT ON TABLE NOTIFY_MSG_IDX is '通知索引表';
COMMENT ON COLUMN NOTIFY_MSG_IDX.RECIVER is '接收人ID';
COMMENT ON COLUMN NOTIFY_MSG_IDX.LAST_CNT is '最后通知内容';
COMMENT ON COLUMN NOTIFY_MSG_IDX.LAST_TIME is '最后通知时间';
COMMENT ON COLUMN NOTIFY_MSG_IDX.UN_READ is '未读通知数';
CREATE TABLE NOTIFY_MSG_CNT (ID CHAR(32) NOT NULL,CNT TEXT NOT NULL,RECIVER TEXT NOT NULL,READED CHAR(1) NOT NULL,
PID TEXT NOT NULL,UID TEXT NOT NULL,CREATE_TIME TEXT NOT NULL,OP_TYPE INTEGER NOT NULL,TIME_DESC TEXT NOT NULL);
ALTER TABLE NOTIFY_MSG_CNT ADD PRIMARY KEY (ID);
COMMENT ON TABLE NOTIFY_MSG_CNT is '通知内容表';
COMMENT ON COLUMN NOTIFY_MSG_CNT.ID is '通知ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.CNT is '通知内容';
COMMENT ON COLUMN NOTIFY_MSG_CNT.RECIVER is '接收人ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.READED is '是否已读，1：已读 0:未读';
COMMENT ON COLUMN NOTIFY_MSG_CNT.PID is '参数ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.UID is '通知触发人ID';
COMMENT ON COLUMN NOTIFY_MSG_CNT.UID is '通知类型(详见文档)';
CREATE TABLE QUESTION_INVITE_REC (UID TEXT NOT NULL,QID TEXT NOT NULL,PID TEXT NOT NULL,CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE QUESTION_INVITE_REC ADD PRIMARY KEY (UID,QID,PID);
COMMENT ON TABLE QUESTION_INVITE_REC is '邀请记录表';
COMMENT ON COLUMN QUESTION_INVITE_REC.UID is '邀请人ID';
COMMENT ON COLUMN QUESTION_INVITE_REC.PID is '被邀请人ID';
COMMENT ON COLUMN QUESTION_INVITE_REC.QID is '提问ID';
--modify begin at version2.30
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='3';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='4';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='25';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='26';
UPDATE GROWTH_LOG SET OPERATE='2' WHERE OPERATE='27';
ALTER TABLE PROFESSOR ADD COLUMN SEX text, ADD COLUMN BIRTHDAY text;
create sequence ware_share_id_seq increment by 1 minvalue 100000 no maxvalue start with 100000;
CREATE TABLE WARE (
ID CHAR(32) NOT NULL,
NAME TEXT NOT NULL,
KEYWORDS TEXT,
CNT TEXT,
COOPERATION TEXT,
DESCP TEXT,
CATEGORY CHAR(1) NOT NULL,
OWNER TEXT,
IMAGES TEXT,
STATE CHAR(1) NOT NULL,
SHARE_ID INT8 DEFAULT nextval('ware_share_id_seq'::regclass) NOT NULL,
PAGE_VIEWS INT8 NOT NULL,
SORT_FIRST INT8 NOT NULL,
CREATE_TIME CHAR(14) NOT NULL,
MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE WARE ADD PRIMARY KEY (ID);
COMMENT ON TABLE WARE is '服务表';
COMMENT ON COLUMN WARE.NAME is '服务名称';
COMMENT ON COLUMN WARE.KEYWORDS is '服务关键词（逗号分隔）';
COMMENT ON COLUMN WARE.CNT is '服务内容';
COMMENT ON COLUMN WARE.COOPERATION is '合作备注';
COMMENT ON COLUMN WARE.DESCP is '详细描述';
COMMENT ON COLUMN WARE.CATEGORY is '类型： 1：个人 2：企业';
COMMENT ON COLUMN WARE.OWNER is '发布人ID professorId orgId';
COMMENT ON COLUMN WARE.IMAGES is '图片列表（逗号分隔）';
COMMENT ON COLUMN WARE.STATE is '状态 0：删除的 1： 发布中的，2：草稿状态的';
COMMENT ON COLUMN WARE.SHARE_ID is '分享ID';
COMMENT ON COLUMN WARE.CREATE_TIME is '创建时间';
COMMENT ON COLUMN WARE.MODIFY_TIME is '最后修改时间';
CREATE TABLE WRE_KEY_WORD(
ID TEXT NOT NULL,
KW TEXT NOT NULL
);
COMMENT ON TABLE WRE_KEY_WORD is '服务关键词';
CREATE TABLE WARE_PRO(
ID TEXT NOT NULL,
PROFESSOR TEXT NOT NULL
);
COMMENT ON TABLE WARE_PRO is '服务联系人表';
CREATE TABLE WARE_RES(
ID TEXT NOT NULL,
RESOURCE TEXT NOT NULL
);
COMMENT ON TABLE WARE is '服务关联资源表';
CREATE TABLE ARTICLE_WARE(
ID TEXT NOT NULL,
WARE TEXT NOT NULL
);
COMMENT ON TABLE WARE is '文章关联服务表';
ALTER TABLE PPATENT ADD COLUMN COOPERATION text;
COMMENT ON COLUMN PPATENT.COOPERATION is '合作备注';
--modify begin at version2.40
CREATE TABLE PLATFORM_USER (
ID CHAR(32) NOT NULL,
EMAIL TEXT NOT NULL,
PHONE TEXT,
PASSWD TEXT NOT NULL,
STATE CHAR(1) NOT NULL,
CREATE_TIME CHAR(14) NOT NULL,
MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE PLATFORM_USER ADD PRIMARY KEY (ID);
ALTER TABLE PLATFORM_USER ADD UNIQUE (EMAIL);
ALTER TABLE PLATFORM_USER ADD UNIQUE (PHONE);
COMMENT ON TABLE PLATFORM_USER is '平台用户表';
COMMENT ON COLUMN PLATFORM_USER.EMAIL is '登录邮箱';
COMMENT ON COLUMN PLATFORM_USER.PHONE is '登录手机号（未户用）';
COMMENT ON COLUMN PLATFORM_USER.PASSWD is '登录密码';
COMMENT ON COLUMN PLATFORM_USER.PHONE is '状态 1：启用 0：未启用';
CREATE TABLE PLATFORM_INFO (
ID CHAR(32) NOT NULL,
NAME TEXT NOT NULL,
LOGO TEXT,
LINKMAN TEXT,
LINKPHONE TEXT,
LINKEMAIL TEXT,
PROVINCE TEXT,
CITY TEXT,
ADDR TEXT,
URL TEXT,
INDUSTRY TEXT,
DESCP TEXT,
CREATE_TIME CHAR(14) NOT NULL,
MODIFY_TIME CHAR(14) NOT NULL);
ALTER TABLE PLATFORM_INFO ADD PRIMARY KEY (ID);
ALTER TABLE PLATFORM_INFO ADD UNIQUE (name);
COMMENT ON TABLE PLATFORM_INFO is '平台信息表';
COMMENT ON COLUMN PLATFORM_INFO.NAME is '平台名称';
COMMENT ON COLUMN PLATFORM_INFO.LOGO is '平台LOGO';
COMMENT ON COLUMN PLATFORM_INFO.LINKMAN is '联系人';
COMMENT ON COLUMN PLATFORM_INFO.LINKPHONE is '联系电话';
COMMENT ON COLUMN PLATFORM_INFO.LINKEMAIL is '联系邮箱';
COMMENT ON COLUMN PLATFORM_INFO.PROVINCE is '省';
COMMENT ON COLUMN PLATFORM_INFO.CITY is '市';
COMMENT ON COLUMN PLATFORM_INFO.ADDR is '地址';
COMMENT ON COLUMN PLATFORM_INFO.URL is '官网';
COMMENT ON COLUMN PLATFORM_INFO.INDUSTRY is '面向行业';
COMMENT ON COLUMN PLATFORM_INFO.DESCP is '平台介绍';
CREATE TABLE REFERENCED_ARTICLE (
PID TEXT NOT NULL,
AID TEXT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE REFERENCED_ARTICLE ADD PRIMARY KEY (PID,AID);
COMMENT ON TABLE REFERENCED_ARTICLE is '企业动态表';
COMMENT ON COLUMN REFERENCED_ARTICLE.PID is '平台ID';
COMMENT ON COLUMN REFERENCED_ARTICLE.AID is '文章ID';
COMMENT ON COLUMN REFERENCED_ARTICLE.CREATE_TIME is '关联时间';
CREATE TABLE RESIDENT_ORG (
PID TEXT NOT NULL,
OID TEXT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE RESIDENT_ORG ADD PRIMARY KEY (PID,OID);
COMMENT ON TABLE RESIDENT_ORG is '平台入驻企业表';
COMMENT ON COLUMN RESIDENT_ORG.PID is '平台ID';
COMMENT ON COLUMN RESIDENT_ORG.OID is '企业ID';
COMMENT ON COLUMN RESIDENT_ORG.CREATE_TIME is '入驻时间';
CREATE TABLE BUTTED_ORG (
PID TEXT NOT NULL,
OID TEXT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE BUTTED_ORG ADD PRIMARY KEY (PID,OID);
COMMENT ON TABLE BUTTED_ORG is '对接的平台机构表';
COMMENT ON COLUMN BUTTED_ORG.PID is '平台ID';
COMMENT ON COLUMN BUTTED_ORG.OID is '对接的机构ID';
COMMENT ON COLUMN BUTTED_ORG.CREATE_TIME is '对接时间';
CREATE TABLE BUTTED_PROFESSOR (
PID TEXT NOT NULL,
UID TEXT NOT NULL,
CREATE_TIME CHAR(14) NOT NULL);
ALTER TABLE BUTTED_PROFESSOR ADD PRIMARY KEY (PID,UID);
COMMENT ON TABLE BUTTED_PROFESSOR is '对接的平台专家表';
COMMENT ON COLUMN BUTTED_PROFESSOR.PID is '平台ID';
COMMENT ON COLUMN BUTTED_PROFESSOR.UID is '对接的专家ID';
COMMENT ON COLUMN BUTTED_PROFESSOR.CREATE_TIME is '对接时间';
--ALTER TABLE RESOURCE ADD COLUMN CATEGORY TEXT DEFAULT '0';
--COMMENT ON COLUMN resource.CATEGORY IS '资源分类';
--ALTER TABLE RESOURCE ALTER COLUMN CATEGORY SET NOT NULL;
ALTER TABLE DEMAND ADD COLUMN ORG_NAME TEXT DEFAULT '1';
update DEMAND set ORG_NAME =(SELECT NAME FROM ORGANIZATION WHERE ID = DEMAND.ORG_ID);
ALTER TABLE DEMAND DROP COLUMN ORG_ID;
ALTER TABLE DEMAND ALTER COLUMN ORG_NAME SET NOT NULL;
ALTER TABLE DEMAND ADD COLUMN SOURCE TEXT DEFAULT 'ekexiuWeb';
ALTER TABLE DEMAND ALTER COLUMN SOURCE SET NOT NULL;
ALTER TABLE public.article ADD owner_id CHAR(32) NULL;
UPDATE public.article set owner_id = professor_id WHERE article_type = '1'
UPDATE public.article SET owner_id = org_id WHERE article_type = '2'
ALTER TABLE public.article DROP professor_id;
ALTER TABLE public.article DROP org_id
|
