Files
KnifeCMS.Java/sql/knifecms-postgresql.sql
2013-08-15 21:56:13 +08:00

588 lines
17 KiB
PL/PgSQL

--KnifeCMS 执行脚本 for Postgresql
DROP TABLE IF EXISTS k_grab;
CREATE TABLE IF NOT EXISTS k_grab (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_title varchar(200) DEFAULT NULL,
k_url varchar(255) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DROP TABLE IF EXISTS k_grab_news;
CREATE TABLE IF NOT EXISTS k_grab_news (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_title varchar(200) DEFAULT NULL,
k_source varchar(100) DEFAULT NULL,
k_author varchar(45) DEFAULT NULL,
k_url varchar(200) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_filetype varchar(45) DEFAULT NULL,
k_content text
);
DROP TABLE IF EXISTS k_document;
CREATE TABLE IF NOT EXISTS k_document (
id serial NOT NULL PRIMARY KEY,
k_title varchar(200) DEFAULT NULL,
k_source varchar(100) DEFAULT NULL,
k_author varchar(45) DEFAULT NULL,
k_risktype varchar(32) DEFAULT NULL,
k_documenttype varchar(32) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_filetype varchar(45) DEFAULT NULL,
k_fileurl varchar(300) DEFAULT NULL,
k_isfee int NOT NULL
);
DROP TABLE IF EXISTS k_documenttype;
CREATE TABLE IF NOT EXISTS k_documenttype (
id serial NOT NULL PRIMARY KEY,
k_type varchar(100) DEFAULT NULL
);
DROP TABLE IF EXISTS k_english;
CREATE TABLE IF NOT EXISTS k_english (
id serial NOT NULL PRIMARY KEY,
k_en varchar(200) DEFAULT NULL,
k_cn varchar(200) DEFAULT NULL,
k_means text,
k_hot int DEFAULT 0
);
DROP TABLE IF EXISTS k_history;
CREATE TABLE IF NOT EXISTS k_history (
id serial NOT NULL PRIMARY KEY,
k_usermail varchar(100) DEFAULT NULL,
k_ip varchar(50) DEFAULT NULL,
k_os varchar(50) DEFAULT NULL,
k_browser varchar(50) DEFAULT NULL,
k_url varchar(100) DEFAULT NULL,
k_accessdate date DEFAULT '1970-01-01',
k_accesstime time DEFAULT '00:00:00',
k_endtime time DEFAULT '00:00:00',
k_type int DEFAULT 0
) ;
DROP TABLE IF EXISTS k_comment;
CREATE TABLE IF NOT EXISTS k_comment (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_user varchar(16) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_news_id varchar(16) DEFAULT NULL,
k_comment text,
k_display int DEFAULT 0
);
DROP TABLE IF EXISTS k_custom;
CREATE TABLE IF NOT EXISTS k_custom (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_site varchar(32) DEFAULT NULL,
k_name varchar(100) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_url varchar(200) DEFAULT NULL,
k_status int DEFAULT 0,
k_description text
);
DROP TABLE IF EXISTS k_fields;
CREATE TABLE IF NOT EXISTS k_fields (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(100) DEFAULT NULL,
k_formid varchar(16) DEFAULT NULL,
k_newsid varchar(16) DEFAULT NULL,
k_order varchar(16) DEFAULT NULL,
k_value text
);
DROP TABLE IF EXISTS k_filter;
CREATE TABLE IF NOT EXISTS k_filter (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(32) DEFAULT NULL
);
DROP TABLE IF EXISTS k_flow;
CREATE TABLE IF NOT EXISTS k_flow (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_site varchar(32) DEFAULT NULL,
k_name varchar(100) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_url varchar(200) DEFAULT NULL,
k_status char(1) DEFAULT 0,
k_description text,
k_type int
);
DROP TABLE IF EXISTS k_form;
CREATE TABLE IF NOT EXISTS k_form (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(200) DEFAULT NULL,
k_content text
);
DROP TABLE IF EXISTS k_userhistory;
CREATE TABLE IF NOT EXISTS k_userhistory (
id serial NOT NULL PRIMARY KEY,
k_usermail varchar(100) DEFAULT NULL,
k_ip varchar(50) DEFAULT NULL,
k_os varchar(50) DEFAULT NULL,
k_browser varchar(50) DEFAULT NULL,
k_url varchar(100) DEFAULT NULL,
k_accessdate date DEFAULT '1970-01-01',
k_accesstime time DEFAULT '00:00:00',
k_endtime time DEFAULT '00:00:00'
) ;
DROP TABLE IF EXISTS k_logs;
CREATE TABLE IF NOT EXISTS k_logs (
id varchar(16) NOT NULL PRIMARY KEY,
k_content VARCHAR(255) NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
DROP TABLE IF EXISTS k_news;
CREATE TABLE IF NOT EXISTS k_news (
id varchar(36) NOT NULL DEFAULT '' PRIMARY KEY,
k_source varchar(100) DEFAULT NULL,
k_title varchar(100) NOT NULL DEFAULT '',
k_file varchar(200) DEFAULT NULL,
k_content text NOT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_username varchar(32) DEFAULT NULL,
k_type varchar(16) DEFAULT NULL,
k_link varchar(16) DEFAULT NULL,
k_commend varchar(6) DEFAULT NULL,
k_template varchar(32) DEFAULT NULL,
k_order int DEFAULT 0,
k_display int DEFAULT 0,
k_tofront int DEFAULT 0,
k_count int DEFAULT 0,
k_keywords varchar(100) DEFAULT NULL,
k_treenews varchar(16) DEFAULT NULL,
k_docfile varchar(200) DEFAULT NULL,
k_isfee int DEFAULT 0,
k_type2 varchar(255) DEFAULT NULL,
k_doctype varchar(16) DEFAULT NULL,
k_summary text,
k_rights int DEFAULT 0,
k_author varchar(100) DEFAULT NULL,
k_score int DEFAULT 0,
k_search int DEFAULT 0,
k_ispub int DEFAULT 0,
k_offdate timestamp NULL,
k_xmldata text
);
DROP TABLE IF EXISTS k_content;
CREATE TABLE IF NOT EXISTS k_content (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_newsid varchar(32) DEFAULT NULL,
k_page int(11) DEFAULT '0',
k_content text
);
DROP TABLE IF EXISTS k_order;
CREATE TABLE IF NOT EXISTS k_order (
id VARCHAR(16) NOT NULL PRIMARY KEY,
k_user VARCHAR(16) NOT NULL,
k_title varchar(150) NOT NULL,
k_goods VARCHAR(200) NOT NULL,
k_money int DEFAULT 0,
k_date date DEFAULT NULL,
k_status int NOT NULL DEFAULT '0'
);
DROP TABLE IF EXISTS k_relative;
CREATE TABLE IF NOT EXISTS k_relative (
id VARCHAR(16) NOT NULL PRIMARY KEY,
k_news_id VARCHAR(16) NOT NULL,
k_rels VARCHAR(255) NULL
);
DROP TABLE IF EXISTS k_pic;
CREATE TABLE IF NOT EXISTS k_pic (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_title varchar(100) DEFAULT NULL,
k_url varchar(100) DEFAULT NULL,
k_newsid varchar(255) DEFAULT NULL,
k_order varchar(16) DEFAULT NULL,
k_userid varchar(16) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_pic_type varchar(16) DEFAULT NULL,
k_display int DEFAULT '0'
);
DROP TABLE IF EXISTS k_pic_type;
CREATE TABLE k_pic_type (
id varchar(32) NOT NULL PRIMARY KEY,
k_name varchar(100) DEFAULT NULL,
k_parent varchar(16) DEFAULT '0',
k_site varchar(16) DEFAULT NULL,
k_userid int DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_order varchar(16) DEFAULT NULL,
k_description varchar(255) DEFAULT NULL
);
DROP TABLE IF EXISTS k_video;
CREATE TABLE IF NOT EXISTS k_video (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_title varchar(100) DEFAULT NULL,
k_url varchar(100) DEFAULT NULL,
k_newsid varchar(255) DEFAULT NULL,
k_order varchar(16) DEFAULT NULL,
k_userid varchar(16) DEFAULT NULL,
k_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
k_display int DEFAULT '0'
);
DROP TABLE IF EXISTS k_record;
CREATE TABLE IF NOT EXISTS k_record (
id varchar(16) NOT NULL PRIMARY KEY,
k_user_id varchar(16) NOT NULL,
k_date timestamp with time zone,
k_operation varchar(200) NOT NULL,
k_news_id varchar(16) NOT NULL
);
DROP TABLE IF EXISTS k_role;
CREATE TABLE IF NOT EXISTS k_role (
id varchar(16) NOT NULL PRIMARY KEY,
k_name varchar(100) NOT NULL,
k_username varchar(200) NOT NULL
);
DROP TABLE IF EXISTS k_subscribe;
CREATE TABLE IF NOT EXISTS k_subscribe (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(100) DEFAULT NULL,
k_form varchar(100) DEFAULT NULL,
k_user varchar(100) DEFAULT NULL,
k_stype CHAR(1) NULL,
k_mail VARCHAR(150) NULL,
k_phone VARCHAR(50) NULL
);
DROP TABLE IF EXISTS k_supplier;
CREATE TABLE IF NOT EXISTS k_supplier (
id VARCHAR(16) NOT NULL PRIMARY KEY,
k_user VARCHAR(32) NULL,
k_name VARCHAR(200) NULL,
k_sid VARCHAR(20) NULL,
k_sname VARCHAR(200) NULL,
k_card VARCHAR(20) NULL
);
DROP TABLE IF EXISTS k_bill;
CREATE TABLE IF NOT EXISTS k_bill (
id VARCHAR(16) NOT NULL PRIMARY KEY,
k_suser VARCHAR(32) NULL,
k_file VARCHAR(200) NULL,
k_date date NULL
);
DROP TABLE IF EXISTS k_theme;
CREATE TABLE IF NOT EXISTS k_theme (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(50) DEFAULT NULL,
k_description varchar(200) DEFAULT NULL,
k_css text,
k_status int DEFAULT 0
);
DROP TABLE IF EXISTS k_thumbnail;
CREATE TABLE IF NOT EXISTS k_thumbnail (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_link varchar(16) DEFAULT NULL,
k_url varchar(16) DEFAULT NULL
);
DROP TABLE IF EXISTS k_tree;
CREATE TABLE IF NOT EXISTS k_tree (
id varchar(32) NOT NULL PRIMARY KEY,
k_name varchar(100) DEFAULT NULL,
k_tree_type varchar(32) NOT NULL,
k_value text,
k_show int DEFAULT 0
);
DROP TABLE IF EXISTS k_type;
CREATE TABLE IF NOT EXISTS k_type (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(100) NOT NULL DEFAULT '',
k_parent varchar(16) DEFAULT '0',
k_value varchar(255) DEFAULT NULL,
k_order int DEFAULT 0,
k_index_template varchar(100) DEFAULT NULL,
k_type_template varchar(100) DEFAULT NULL,
k_news_template varchar(100) DEFAULT NULL,
k_form varchar(100) DEFAULT NULL,
k_admin varchar(200) DEFAULT NULL,
k_audit varchar(200) DEFAULT NULL,
k_display int DEFAULT 1,
k_show int DEFAULT 0,
k_ismember int DEFAULT 0,
k_url varchar(200) DEFAULT NULL,
k_target int DEFAULT 0,
k_pagenum varchar(16) DEFAULT NULL,
k_pagesize int default 10,
k_orderby varchar(100) DEFAULT NULL,
k_search int DEFAULT 1,
k_tree varchar(32) DEFAULT NULL,
k_tree_id varchar(32) DEFAULT '',
k_site varchar(16) DEFAULT NULL,
k_needpub int default 0,
k_html int default 0,
k_html_rule int default 0,
k_html_dir int default 0
);
DROP TABLE IF EXISTS k_type2;
CREATE TABLE k_type2 (
id VARCHAR(16) NOT NULL PRIMARY KEY,
k_name VARCHAR(255) NOT NULL
);
DROP TABLE IF EXISTS k_site;
CREATE TABLE IF NOT EXISTS k_site (
id varchar(32) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(100) DEFAULT NULL,
k_domain varchar(200) DEFAULT NULL,
k_admin varchar(200) DEFAULT NULL,
k_lang varchar(8) DEFAULT NULL,
k_html int DEFAULT 0,
k_html_rule int DEFAULT 0,
k_html_dir int DEFAULT 0,
k_mailserver varchar(200) DEFAULT NULL,
k_mailuser varchar(100) DEFAULT NULL,
k_mailpass varchar(100) DEFAULT NULL,
k_isthumb int DEFAULT 0,
k_order varchar(16) DEFAULT NULL,
k_pub_dir varchar(100) DEFAULT NULL,
k_template varchar(100) DEFAULT NULL,
k_big5 int DEFAULT 0,
k_index varchar(100) DEFAULT NULL,
k_theme varchar(32) DEFAULT NULL,
k_css_file varchar(100) DEFAULT NULL
);
DROP TABLE IF EXISTS k_sys;
CREATE TABLE IF NOT EXISTS k_sys (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_sso int DEFAULT 0
);
DROP TABLE IF EXISTS k_user;
CREATE TABLE IF NOT EXISTS k_user (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_username varchar(32) NOT NULL DEFAULT '',
k_password varchar(32) NOT NULL DEFAULT '',
k_email varchar(32) DEFAULT NULL,
k_qq varchar(32) DEFAULT NULL,
k_birthday date DEFAULT NULL,
k_sysj date DEFAULT NULL,
k_tel varchar(32) DEFAULT NULL,
k_fax varchar(32) DEFAULT NULL,
k_addr varchar(100) DEFAULT NULL,
k_intro varchar(200) DEFAULT NULL,
k_popedom int DEFAULT 0,
k_document_show int DEFAULT 0
);
DROP TABLE IF EXISTS k_version;
CREATE TABLE IF NOT EXISTS k_version (
id varchar(16) NOT NULL PRIMARY KEY,
k_number int,
k_date date,
k_content text
);
DROP TABLE IF EXISTS k_vote;
CREATE TABLE IF NOT EXISTS k_vote (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_title varchar(32) NOT NULL DEFAULT ''
);
DROP TABLE IF EXISTS k_voteitem;
CREATE TABLE IF NOT EXISTS k_voteitem (
id varchar(16) NOT NULL DEFAULT '' PRIMARY KEY,
k_name varchar(32) NOT NULL DEFAULT '',
k_vote_id int DEFAULT NULL
);
DROP TABLE IF EXISTS k_meeting;
CREATE TABLE IF NOT EXISTS k_meeting (
id serial NOT NULL PRIMARY KEY,
k_title varchar(100) NOT NULL,
k_host varchar(100) DEFAULT NULL,
k_starttime date DEFAULT NULL,
k_endtime date DEFAULT NULL,
k_isclosed int NOT NULL DEFAULT '0',
k_meetingid varchar(100) DEFAULT NULL
);
DROP TABLE IF EXISTS k_organclass;
CREATE TABLE IF NOT EXISTS k_organclass (
id serial NOT NULL PRIMARY KEY,
k_oid int DEFAULT NULL,
k_class varchar(45) DEFAULT NULL
);
DROP TABLE IF EXISTS k_organization;
CREATE TABLE IF NOT EXISTS k_organization (
id serial NOT NULL PRIMARY KEY,
k_type varchar(45) NOT NULL,
k_classid int DEFAULT NULL
);
DROP TABLE IF EXISTS k_risktype;
CREATE TABLE IF NOT EXISTS k_risktype (
id serial NOT NULL PRIMARY KEY,
k_type varchar(100) DEFAULT NULL
);
DROP TABLE IF EXISTS k_userfav;
CREATE TABLE IF NOT EXISTS k_userfav (
id serial NOT NULL PRIMARY KEY,
k_userid int NOT NULL,
k_docid varchar(16) NOT NULL,
k_type int DEFAULT 0,
k_favdate date DEFAULT NULL
);
DROP TABLE IF EXISTS k_userinfo;
CREATE TABLE IF NOT EXISTS k_userinfo (
id serial NOT NULL PRIMARY KEY,
k_idcard varchar(20) DEFAULT NULL,
k_acount varchar(45) DEFAULT NULL,
k_password varchar(45) DEFAULT NULL,
k_realname varchar(45) DEFAULT NULL,
k_birthday date DEFAULT NULL,
k_sex int DEFAULT 0,
k_education varchar(100) DEFAULT NULL,
k_professional varchar(100) NULL,
k_email varchar(200) DEFAULT NULL,
k_phone varchar(45) DEFAULT NULL,
k_mobile varchar(45) DEFAULT NULL,
k_organization varchar(80) DEFAULT NULL,
k_department varchar(80) DEFAULT NULL,
k_position varchar(80) DEFAULT NULL,
k_location varchar(200) DEFAULT NULL,
k_zipcode varchar(20) DEFAULT NULL,
k_isfee int DEFAULT 0,
k_isadmin int DEFAULT 0,
k_adminnum int DEFAULT 0,
k_usednum int DEFAULT 0,
k_score bigint DEFAULT 0,
k_mac varchar(500) DEFAULT '',
k_active int DEFAULT 0,
k_hobby VARCHAR(200) NULL,
k_salary VARCHAR(100) NULL,
k_attend VARCHAR(200) NULL,
k_exhibition VARCHAR(200) NULL,
k_receive VARCHAR(100) DEFAULT NULL,
k_ismarry int DEFAULT 0,
k_spouse VARCHAR(200) NULL,
k_family VARCHAR(200) NULL,
k_workarea VARCHAR(200) NULL,
k_unit VARCHAR(200) NULL,
k_homearea VARCHAR(200) NULL,
k_officephone VARCHAR(45) NULL,
k_fax VARCHAR(45) NULL,
k_rejection VARCHAR(200) NULL,
k_bestway VARCHAR(200) NULL,
k_incomingway VARCHAR(200) NULL,
k_regdate date DEFAULT NULL,
k_identity int DEFAULT 0,
k_avatar VARCHAR(100) NULL,
k_count int DEFAULT 0,
k_type VARCHAR(255) NULL
);
DROP TABLE IF EXISTS k_usertype;
CREATE TABLE IF NOT EXISTS k_usertype (
id serial NOT NULL PRIMARY KEY,
k_name varchar(200) DEFAULT NULL,
k_rights int NOT NULL DEFAULT '0',
k_parent int DEFAULT 0,
k_onscore int DEFAULT 0,
k_macnum int DEFAULT 0,
k_order int DEFAULT 0
);
insert into k_user (id, k_username, k_password, k_email, k_qq, k_birthday, k_tel, k_fax, k_addr, k_intro, k_popedom)
values ('10935283-5777490', 'admin', 'admin123', null, '710633139', null, null, null, null, null, '3');
insert into k_site (id, k_name, k_domain, k_admin, k_lang, k_html, k_html_rule, k_html_dir, k_mailserver, k_mailuser, k_mailpass, k_pub_dir, k_template)
values ('1', 'KnifeCMS', 'www.knifecms.com', null, 'zh-cn', '1', '0', '0', null, null, null, 'default', 'default');
commit;
CREATE OR REPLACE FUNCTION f_add_news()
RETURNS trigger AS
$BODY$begin
NEW.k_order := (SELECT COUNT(*) FROM k_news Where k_type = NEW.k_type)+1;
return NEW;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION f_add_news()
OWNER TO postgres;
CREATE TRIGGER t_add_news BEFORE INSERT ON k_news FOR EACH ROW EXECUTE PROCEDURE f_add_news();
CREATE OR REPLACE FUNCTION f_add_pic()
RETURNS trigger AS
$BODY$begin
NEW.k_order := (SELECT COUNT(*) FROM k_pic Where k_newsid = NEW.k_newsid)+1;
return NEW;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION f_add_news()
OWNER TO postgres;
CREATE TRIGGER t_add_pic BEFORE INSERT ON k_pic FOR EACH ROW EXECUTE PROCEDURE f_add_pic();
CREATE OR REPLACE FUNCTION f_add_type()
RETURNS trigger AS
$BODY$begin
NEW.k_order := (SELECT COUNT(*) FROM k_type Where k_parent = NEW.k_parent and k_tree_id=NEW.k_tree_id)+1;
IF NEW.k_type_template='' or NEW.k_type_template is Null THEN
NEW.k_type_template := 'list.htm';
END IF;
IF NEW.k_news_template='' or NEW.k_news_template is Null THEN
NEW.k_news_template := 'news.htm';
END IF;
IF NEW.k_site='' or NEW.k_site is Null THEN
NEW.k_site := '1';
END IF;
return NEW;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION f_add_type()
OWNER TO postgres;
CREATE TRIGGER t_add_type BEFORE INSERT ON k_type FOR EACH ROW EXECUTE PROCEDURE f_add_type();
CREATE OR REPLACE FUNCTION f_add_site()
RETURNS trigger AS
$BODY$begin
NEW.k_order := (SELECT COUNT(*) FROM k_site)+1;
IF NEW.k_pub_dir='' or NEW.k_pub_dir is Null THEN
NEW.k_pub_dir := NEW.k_domain;
END IF;
IF NEW.k_template='' or NEW.k_template is Null THEN
NEW.k_template := NEW.k_domain;
END IF;
return NEW;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION f_add_site()
OWNER TO postgres;
CREATE TRIGGER t_add_site BEFORE INSERT ON k_site FOR EACH ROW EXECUTE PROCEDURE f_add_site();