目前按照自己的IWP框架开发出的 cms 项目 iwp-press 基础表结构如下:
-- 用户,权限,角色
DROP TABLE IF EXISTS `iwp_users`;
CREATE TABLE `iwp_users` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT '用户主键标识' ,
`login` varchar(60) NOT NULL default '' COMMENT '用户名',
`password` char(32) NOT NULL default '' COMMENT '密码',
`nicename` varchar(50) NOT NULL default '' COMMENT '昵称',
`email` varchar(100) NOT NULL default '' COMMENT '邮箱',
`url` varchar(100) NOT NULL default '' COMMENT '主页地址',
`registered` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '注册时间',
`activation_key` varchar(60) NOT NULL default '' COMMENT '激活key',
`status` int(10) unsigned NOT NULL COMMENT '帐号状态',
PRIMARY KEY (`id`),
KEY `user_login_key` (`login`),
KEY `user_nicename` (`nicename`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '系统用户表';
DROP TABLE IF EXISTS `iwp_acl_roles`;
CREATE TABLE IF NOT EXISTS `iwp_acl_roles` (
`role_id` int(10) unsigned NOT NULL auto_increment COMMENT '角色主键标识',
`rolename` varchar(32) NOT NULL default '' COMMENT '角色名',
`description` varchar(240) NOT NULL default '' COMMENT '角色描述',
`created_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '创建时间',
`updated_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (`role_id`),
UNIQUE KEY `rolename` (`rolename`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'ACL 角色表';
DROP TABLE IF EXISTS `iwp_acl_actions`;
CREATE TABLE IF NOT EXISTS `iwp_acl_actions` (
`action_id` int(10) unsigned NOT NULL auto_increment COMMENT '资源主键标识',
`uuid` varchar(64) NOT NULL COMMENT '资源地址编码',
`access` int(4) NOT NULL default 0 COMMENT '访问标识',
`description` varchar(240) NOT NULL default '' COMMENT '资源描述',
`created_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '创建时间',
`updated_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '更新时间',
PRIMARY KEY (`action_id`),
UNIQUE KEY `uuid` (`uuid`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'ACL 资源表';
DROP TABLE IF EXISTS `iwp_acl_roles_has_actions`;
CREATE TABLE `iwp_acl_roles_has_actions` (
`action_id` int(10) unsigned NOT NULL default '0' COMMENT '资源主键标识',
`role_id` int(10) unsigned NOT NULL default '0' COMMENT '角色主键标识',
PRIMARY KEY (`action_id`,`role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'ACL 角色-资源关联表';
DROP TABLE IF EXISTS `iwp_acl_users_has_roles`;
CREATE TABLE IF NOT EXISTS `iwp_acl_users_has_roles` (
`user_id` int(10) unsigned NOT NULL COMMENT '用户主键标识',
`role_id` int(10) unsigned NOT NULL COMMENT '角色主键标识' ,
PRIMARY KEY (`user_id`,`role_id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'ACL 用户-角色关联表';
-- 站点配置信息
DROP TABLE IF EXISTS `iwp_sitemeta`;
CREATE TABLE `iwp_sitemeta` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT '选项主键标识',
`name` varchar(64) NOT NULL default '' COMMENT '选项名称',
`val` longtext NOT NULL default '' COMMENT '选项值',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = '站点配置选项表';
-- 另一种 分类结构
DROP TABLE IF EXISTS `iwp_sysclasses`;
CREATE TABLE `iwp_sysclasses` (
`id` int(10) unsigned NOT NULL auto_increment,
`parent_id` int(10) unsigned NOT NULL,
`name` varchar(64) collate utf8_unicode_ci NOT NULL,
`left_value` int(10) unsigned NOT NULL,
`right_value` int(10) unsigned NOT NULL,
`deep` tinyint(8) unsigned NOT NULL default '0',
`created_at` datetime default NULL,
`updated_at` datetime default NULL,
`data` text ,
`orderby` tinyint(8) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `deep` (`deep`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='先根遍历算法结构的层级分类表';
-- 友情链接
DROP TABLE IF EXISTS `iwp_links`;
CREATE TABLE `iwp_links` (
`link_id` int(10) unsigned NOT NULL auto_increment,
`link_url` varchar(255) NOT NULL default '',
`link_name` varchar(255) NOT NULL default '',
`link_image` varchar(255) NOT NULL default '',
`link_target` varchar(25) NOT NULL default '',
`link_description` varchar(255) NOT NULL default '',
`link_visible` int(10) unsigned NOT NULL,
PRIMARY KEY (`link_id`),
KEY `link_visible` (`link_visible`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='友情链接表';
-- 文章相关
DROP TABLE IF EXISTS `iwp_posts`;
CREATE TABLE `iwp_posts` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT '文章的编号',
`post_class_id` int(10) unsigned NOT NULL COMMENT '文章隶属的分类',
`author` int(10) unsigned NOT NULL default '0' COMMENT '文章的作者标识',
`created_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '文章的发布日期',
`content` longtext NOT NULL COMMENT '文章的内容',
`title` varchar(200) NOT NULL default '' COMMENT '文章的标题',
`excerpt` text NOT NULL COMMENT '文章的可选摘要',
`status` varchar(20) NOT NULL default 'publish' COMMENT '文章的状态',
`comment_status` varchar(20) NOT NULL default 'open' COMMENT '是否允许对文章进行评论',
`post_password` varchar(20) NOT NULL default '' COMMENT '文章的密码',
`updated_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '文章的修改时间',
`comment_count` int(10) unsigned NOT NULL default '0' COMMENT '文章评论的数量',
`post_type` int(10) NOT NULL COMMENT '文章内容的类型: 比如 文章,产品,资讯等等',
PRIMARY KEY (`id`),
KEY `post_name` (`title`),
KEY `post_class_id` (`post_class_id`),
KEY `post_status_date` (`status`,`created_at`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='存储文章信息的数据表';
DROP TABLE IF EXISTS `iwp_postmeta`;
CREATE TABLE `iwp_postmeta` (
`meta_id` int(10) unsigned NOT NULL auto_increment COMMENT '元信息的编号',
`post_id` int(10) unsigned NOT NULL default '0' COMMENT '文章的编号',
`meta_key` varchar(255) default NULL COMMENT 'meta key名',
`meta_value` longtext COMMENT 'meta 值',
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='存储文章元信息的数据表';
DROP TABLE IF EXISTS `iwp_comments`;
CREATE TABLE `iwp_comments` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT '评论的编号',
`post_id` int(10) unsigned NOT NULL default '0' COMMENT '所评论文章的编号',
`author` varchar(60) NOT NULL COMMENT '发布评论的作者名字',
`author_email` varchar(100) NOT NULL default '' COMMENT '发布评论者的邮箱',
`author_url` varchar(200) NOT NULL default '' COMMENT '发布评论者的url',
`author_ip` varchar(100) NOT NULL default '' COMMENT '发布评论者的ip',
`created_at` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '发布日期',
`content` text NOT NULL COMMENT '评论内容',
`approved` varchar(20) NOT NULL default '1' COMMENT '评论的状态: 0 待审批; 1 已审批; 2 垃圾评论',
`agent` varchar(255) NOT NULL default '' COMMENT '评论者的信息: 浏览器&OS 信息',
`comment_type` int(10) NOT NULL default '0' COMMENT '评论的类型',
PRIMARY KEY (`id`),
KEY `comment_approved` (`approved`),
KEY `comment_post_id` (`post_id`),
KEY `comment_date` (`created_at`),
KEY `comment_type` (`comment_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='存储文章评论的数据表';