Home / Blog

---

Blog Docs 2 - Database Setup

---

CREATE TABLE blog_cat (
cat int NOT NULL DEFAULT 0 PRIMARY KEY,
name varchar(99) NOT NULL DEFAULT '',
info varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE blog_file (
id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
mid int NOT NULL DEFAULT 0,
sizeb int NOT NULL DEFAULT 0,
filename varchar(255) NOT NULL DEFAULT '',
contents varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE blog_log (
lid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid int NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT now(),
ip int unsigned NOT NULL DEFAULT 0,
tid int NOT NULL DEFAULT 0,
log_type int NOT NULL DEFAULT 1,
KEY tid (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE blog_msg (
mid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
tid int NOT NULL DEFAULT 0,
uid int NOT NULL DEFAULT 0,
to_mid int NOT NULL DEFAULT 0,
ip int unsigned NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
updated timestamp NOT NULL DEFAULT now(),
rank int NOT NULL DEFAULT 0,
txt text NOT NULL,
KEY blog_msg_tid_idx (tid),
KEY blog_msg_pid_idx (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- msg text details of a topic
-- when mid == tid, it is the text for topic
-- to_mid: 0 default -1 blocked -2 spam -8 new, else eg -123 reply to mid=123 need auth, or 1234 reply to mid=1234 auth approved

CREATE TABLE blog_related (
tid1 int NOT NULL DEFAULT 0,
tid2 int NOT NULL DEFAULT 0,
sort int NOT NULL DEFAULT 0,
PRIMARY KEY (tid1,tid2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE blog_tag (
tid int NOT NULL DEFAULT 0,
tag int NOT NULL DEFAULT 0,
sort int NOT NULL DEFAULT 0,
PRIMARY KEY (tid,tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE blog_topic (
tid int NOT NULL DEFAULT 0 PRIMARY KEY,
cat int NOT NULL DEFAULT 0,
status smallint NOT NULL DEFAULT 1,
hit int NOT NULL DEFAULT 1,
uid int NOT NULL DEFAULT 0,
created timestamp NOT NULL DEFAULT now(),
updated timestamp NOT NULL DEFAULT now(),
updated_uid int NOT NULL DEFAULT 0,
msg int NOT NULL DEFAULT 0,
rank int NOT NULL DEFAULT 0,
lock_uid int NOT NULL DEFAULT 0,
url varchar(255) NOT NULL DEFAULT '',
meta mediumtext NOT NULL,
KEY blog_topic_pid_idx (uid),
KEY blog_topic_cat_idx (cat),
KEY blog_topic_status_idx (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You only need to create above tables, and the following sample data needed for blog_cat:

INSERT INTO blog_cat(cat,name,info) VALUES
(11,'blog_styles','[","blog","forum","ticket","photo","music","video","miniblog","statckOverFlow","wiki"]'),
(12,'blog_access','["Account Closed","Private","Staff Only","Friends Only","Read Only","Read Post Free"]'),
(13,'blog_status','{"1":{"0":"Deleted","1":"Private","2":"Public","9":"Spam"},"3":{"0":"Deleted","1":"New","2":"Replied","3":"OnHold","4":"Closed","8":"Template","9":"Spam"}}'),
(14,'blog_ranks','{"1":"Low","2":"Medium","3":"High","4":"Urgent","5":"Emergency","6":"Critical","50":"Good","98":"Cat Top","99":"Global Top"}'),
(15,'blog_att_ftype','[["No Attachment","],["Images","png;gif;jpg;jpeg"],["Common File Types","jpg;gif;png;doc;docx;xls;xlsx;ppt;pdf;txt;html;htm;pdf;zip;rar;mp3;mp4;avi;mov;js"],["All File Types except","exe;bat"]]'),
(16,'blog_log_type','["NA","topic","cat","tag","alert","spam","6","rankUp","rankDown"]'),
(17,'blog_settings',''),
(18,'Common tags','PHP;MySQL;HTML;CSS;JS'),
(19,'FAQ','a'),
(20,'unsorted tag','name=Tag Name: url=tag-displayed-name. info=Some other display name'),
(21,'nested tag','eg tag 2101 is kid of 21'),
(1000,'blog_friend',''),
(1001,'blog_staff','{"1":[1,2],"181130":[102,108]}'),
(210100,'x','unsorted tags starts from here, or you can make it starts from 2500 etc'),

-- 1811* is a list of sample categories, replace with your own:

(1811, 'tutplus.com','The base cat of this site'),
(181101, 'code', ''),
(181133, 'web-design', ''),
(18110101, 'javascript', 'JavaScript'),
(18110156, 'php', 'PHP'),
(18113344, 'react', ''),
(18113366, 'icon-design','');

-- blog_cat.name need to be unique

---

Back « Blog Docs 1 - Basic and quick setup
Next » Blog Docs 3 - Advanced Setup

Comments