自己动手实现 多用户论坛系统 tiny_forum
crud 以及sql如下: 感觉 自己写的coreapp-mini 其实还是蛮好用的...
?
-- 分类-- 表的结构 `tiny_forum_category`DROP TABLE IF EXISTS `tiny_forum_category`;CREATE TABLE IF NOT EXISTS `tiny_forum_category` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(64) NOT NULL , `logo` varchar(64) NOT NULL , `uid` int(10) unsigned NOT NULL, `created_at` int(10) unsigned NOT NULL default 1, `updated_at` int(10) unsigned NOT NULL default 0, PRIMARY KEY (`id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- 表的结构 `tiny_forum_category_info`DROP TABLE IF EXISTS `tiny_forum_category_info`;CREATE TABLE IF NOT EXISTS `tiny_forum_category_info` ( `id` int(10) unsigned NOT NULL auto_increment, `cat_id` int(10) unsigned NOT NULL, `num_post` int(8) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cat_id` (`cat_id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- 主题-- 表的结构 `tiny_forum_post`DROP TABLE IF EXISTS `tiny_forum_post`;CREATE TABLE IF NOT EXISTS `tiny_forum_post` ( `id` int(10) unsigned NOT NULL auto_increment, `title` varchar(64) NOT NULL , `cat_id` int(10) unsigned NOT NULL, `uid` int(10) unsigned NOT NULL, `created_at` int(10) unsigned NOT NULL default 1, `updated_at` int(10) unsigned NOT NULL default 0, PRIMARY KEY (`id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- 表的结构 `tiny_forum_post_content`DROP TABLE IF EXISTS `tiny_forum_post_content`;CREATE TABLE IF NOT EXISTS `tiny_forum_post_content` ( `id` int(10) unsigned NOT NULL auto_increment, `post_id` int(10) unsigned NOT NULL, `content` text collate utf8_unicode_ci, PRIMARY KEY (`id`), UNIQUE KEY `post_id` (`post_id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- 表的结构 `tiny_forum_post_info`DROP TABLE IF EXISTS `tiny_forum_post_info`;CREATE TABLE IF NOT EXISTS `tiny_forum_post_info` ( `id` int(10) unsigned NOT NULL auto_increment, `post_id` int(10) unsigned NOT NULL, `num_comment` int(6) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `post_id` (`post_id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- 评论 -- 表的结构 `tiny_forum_post_comment`DROP TABLE IF EXISTS `tiny_forum_post_comment`;CREATE TABLE IF NOT EXISTS `tiny_forum_post_comment` ( `id` int(10) unsigned NOT NULL auto_increment, `post_id` int(10) unsigned NOT NULL, `content` text collate utf8_unicode_ci, `uid` int(10) unsigned NOT NULL, `created_at` int(10) unsigned NOT NULL default 1, PRIMARY KEY (`id`))DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;---- 添加索引ALTER TABLE `tiny_forum_category` ADD INDEX `inx_tiny_fc_uid` (`uid`);ALTER TABLE `tiny_forum_post` ADD INDEX `inx_tiny_fp_cat_id` (`cat_id`);ALTER TABLE `tiny_forum_post` ADD INDEX `inx_tiny_fp_uid` (`uid`);ALTER TABLE `tiny_forum_post_comment` ADD INDEX `inx_tiny_fpc_uid` (`uid`);ALTER TABLE `tiny_forum_post_comment` ADD INDEX `inx_tiny_fpc_post_id` (`post_id`);
?
<?php// a tiny forum for more usersfinal class tiny_forum {/** * 所有用户标识 */const ALL_USER = -301;}// ---- 表关系模型 ----- 开始 --------------/** * * tiny_forum_category[ * :id,:name,:logo,:uid,:create_at,:update_at * ] * * tiny_forum_category_info[ * :id,:cat_id,:num_post * ] */class tiny_forum_category {public $tbCategory = 'tiny_forum_category';public $tbCategoryInfo = 'tiny_forum_category_info';private function __construct(){}/** * @return tiny_forum_category */static function getInstance(){static $inst = false;if (!$inst) $inst = new self();return $inst;}function find($cat_id){$catfields = CoreDbSqlHelper::qfields('id,name,logo,uid','c');$cifields = CoreDbSqlHelper::qfields('num_post','ci');$cond = array('c.id'=>(int) $cat_id);$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";$sql = "SELECT {$catfields},{$cifields} FROM {$this->tbCategory} AS c LEFT JOIN {$this->tbCategoryInfo} AS ci ON ci.cat_id=c.id {$sqlCond} " . CoreDbSqlHelper::getLimitSql(1);return CoreApp::$defaultDBO->getRow($sql);}function find_all($limit=null,& $counter=null){return $this->find_all_by_user(tiny_forum::ALL_USER,$limit,$counter);}function destroy_all(){return $this->destroy_all_by_user(tiny_forum::ALL_USER);}function create(array $row){$rt = assertAssocArray($row,array('name' => array(array('not_empty', false),),'uid' => array(array('not_null', false),array('is_int', false),), ));if ($rt){$row['create_at'] = CURRENT_TIMESTAMP;return SingleTableCRUD::insert($this->tbCategory,$row);}return false;}function update(array $row){$rt = assertAssocArray($row,array('id' => array(array('not_null', false),array('is_int', false),),'name' => array(array('not_empty', false),)));if ($rt){$row['update_at'] = CURRENT_TIMESTAMP;return SingleTableCRUD::update($this->tbCategory,$row,'id');}return false;}function destroy($cat_id){$cat_id = (int) $cat_id;$rt = SingleTableCRUD::delete($this->tbCategory,array('id'=>$cat_id));if ($rt){SingleTableCRUD::delete($this->tbCategoryInfo,array('cat_id'=>$cat_id));}return $rt;}function destroy_all_by_user($user_id){$user_id = (int) $user_id;$cond = ($user_id === tiny_forum::ALL_USER) ? null : array('uid'=>$user_id);$rt = SingleTableCRUD::delete($this->tbCategory,$cond);if ($rt){SingleTableCRUD::delete($this->tbCategoryInfo,$cond);}return $rt;}function find_all_by_user($user_id, $limit=null,& $counter=null){$catfields = CoreDbSqlHelper::qfields('id,name,logo,uid','c');$cifields = CoreDbSqlHelper::qfields('num_post','ci');if ($user_id === tiny_forum::ALL_USER){$sqlCond = '';}else {$cond = array('c.uid'=>(int) $user_id);$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";}$counter = $counter ? 'SQL_CALC_FOUND_ROWS' : '' ;$sql = "SELECT {$counter} {$catfields},{$cifields} FROM {$this->tbCategory} AS c LEFT JOIN {$this->tbCategoryInfo} AS ci ON ci.cat_id=c.id {$sqlCond} " . CoreDbSqlHelper::getLimitSql($limit);$rows = CoreApp::$defaultDBO->getAll($sql);if ($counter)$counter = CoreApp::$defaultDBO->getOne('SELECT FOUND_ROWS()') ;return $rows;}function incr_postnum($cat_id,$incr = 1){return SingleTableCRUD::incrField($this->tbCategoryInfo,array('cat_id'=>$cat_id),'num_post',$incr);}}/** * * tiny_forum_post[ * :id,:title,:cat_id,:uid,:create_at,:update_at * ] * * tiny_forum_post_content[ * :id,:post_id,:content * ] * * tiny_forum_post_info[ * :id,:post_id,:num_comment * ] */class tiny_forum_post {public $tbPost = 'tiny_forum_post';public $tbPostContent = 'tiny_forum_post_content';public $tbPostInfo = 'tiny_forum_post_info';private function __construct(){}/** * @return tiny_forum_post */static function getInstance(){static $inst = false;if (!$inst) $inst = new self();return $inst;}function find($post_id){$pfields = CoreDbSqlHelper::qfields('*','p');$pcfields = CoreDbSqlHelper::qfields('content','pc');$pifields = CoreDbSqlHelper::qfields('num_comment','pi');$cond = array('p.id'=>(int) $post_id);$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";$counter = $counter ? 'SQL_CALC_FOUND_ROWS' : '' ;$sql = "SELECT {$counter} {$catfields},{$cifields} FROM {$this->tbPost} AS p LEFT JOIN {$this->tbPostContent} AS pc ON pc.post_id=p.id LEFT JOIN {$this->tbPostInfo} AS pi ON pi.post_id=p.id {$sqlCond} " . CoreDbSqlHelper::getLimitSql(1);return CoreApp::$defaultDBO->getRow($sql);}function find_all_by_user($user_id, $limit=null,& $counter=null){$pfields = CoreDbSqlHelper::qfields('*','p');$pifields = CoreDbSqlHelper::qfields('num_comment','pi');if ($user_id === tiny_forum::ALL_USER){$sqlCond = '';}else {$cond = array('p.uid'=>(int) $user_id);$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";}$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";$sql = "SELECT {$catfields},{$cifields} FROM {$this->tbPost} AS p LEFT JOIN {$this->tbPostInfo} AS pi ON pi.post_id=p.id {$sqlCond} " . CoreDbSqlHelper::getLimitSql($limit);$rows = CoreApp::$defaultDBO->getAll($sql);if ($counter)$counter = CoreApp::$defaultDBO->getOne('SELECT FOUND_ROWS()') ;return $rows;}function find_all_by_category($cat_id, $limit=null,& $counter=null){$pfields = CoreDbSqlHelper::qfields('*','p');$pifields = CoreDbSqlHelper::qfields('num_comment','pi');$cond = array('p.cat_id'=>(int) $cat_id);$sqlCond = CoreDbSqlHelper::parseConditions(CoreApp::$defaultDBO,$cond);if ($sqlCond) $sqlCond = "WHERE {$sqlCond}";$sql = "SELECT {$catfields},{$cifields} FROM {$this->tbPost} AS p LEFT JOIN {$this->tbPostInfo} AS pi ON pi.post_id=p.id {$sqlCond} " . CoreDbSqlHelper::getLimitSql($limit);$rows = CoreApp::$defaultDBO->getAll($sql);if ($counter)$counter = CoreApp::$defaultDBO->getOne('SELECT FOUND_ROWS()') ;return $rows;}function destroy($post_id){$post_id = (int) $post_id;$cat_id = SingleTableCRUD::find($this->tbPost,array('id'=>$post_id),'cat_id');if (!$cat_id) return false;$rt = SingleTableCRUD::delete($this->tbPost,array('id'=>$post_id));if ($rt){tiny_forum_category::getInstance()->incr_postnum($cat_id,-1);tiny_forum_post_comment::getInstance()->destroy_all_by_post($post_id);SingleTableCRUD::delete($this->tbPostInfo,array('post_id'=>$post_id));SingleTableCRUD::delete($this->tbPostContent,array('post_id'=>$post_id));}return $rt;}function destroy_all($post_ids){$post_ids = normalize($post_ids);if (empty($post_ids)) return false;$affectRows = 0;foreach ($post_ids as $post_id){if ($this->destroy($post_id)){$affectRows ++;}}return $affectRows;}function destroy_all_by_user($user_id){$counter = false;$cond = ($user_id === tiny_forum::ALL_USER) ? null : array('uid'=>$user_id);$post_ids = SingleTableCRUD::findAll($this->tbPost,$cond,null,null,$counter,'id');if (empty($post_ids)) return false;return $this->destroy_all($post_ids);}function create(array $row){$rt = assertAssocArray($row,array('title' => array(array('not_empty', false),),'uid' => array(array('not_null', false),array('is_int', false),), 'cat_id' => array(array('not_null', false),array('is_int', false),), ));if ($rt){$row['create_at'] = CURRENT_TIMESTAMP;$post_id = SingleTableCRUD::insert($this->tbPost,$row); if ($post_id){tiny_forum_category::getInstance()->incr_postnum($row['cat_id'],1);}}return false;}function update(array $row){$rt = assertAssocArray($row,array('id' => array(array('not_null', false),array('is_int', false),),'title' => array(array('not_empty', false),)));if ($rt){$row['update_at'] = CURRENT_TIMESTAMP;if (SingleTableCRUD::update($this->tbPost,$row,'id')){if (isset($row['content'])){return SingleTableCRUD::update($this->tbPostContent,array('content' => $row['content']),'post_id');}return true;}}return false;}function incr_commentnum($post_id,$incr = 1){return SingleTableCRUD::incrField($this->tbPostInfo,array('post_id'=>$post_id),'num_comment',$incr);}}/** * * tiny_forum_post_comment[ * :id,:post_id,:content,:uid,:create_at * ] */class tiny_forum_post_comment {public $tbPostComment = 'tiny_forum_post_comment';private function __construct(){}/** * @return tiny_forum_post_comment */static function getInstance(){static $inst = false;if (!$inst) $inst = new self();return $inst;}function find_all_by_post($post_id, $limit=null,& $counter=null){return SingleTableCRUD::findAll($this->tbPostComment,array('post_id'=>(int)$post_id),'create_at',$limit,$counter);}function find_all_by_user($user_id, $limit=null,& $counter=null){return SingleTableCRUD::findAll($this->tbPostComment,array('uid'=>(int)$user_id),'create_at',$limit,$counter);}function destroy($comment_id){$cond = array('id'=>$comment_id);$post_id = SingleTableCRUD::find($this->tbPostComment,$cond,'post_id');if (!$post_id) return false;$rt = SingleTableCRUD::delete($this->tbPostComment,$cond);if ($rt){tiny_forum_post::getInstance()->incr_commentnum($post_id,-1);}return $rt;}function destroy_all($comment_ids){$comment_ids = normalize($comment_ids);if (empty($comment_ids)) return false;$affectRows = 0;foreach ($comment_ids as $comment_id){if ($this->destroy($comment_id)){$affectRows ++;}}return $affectRows;}function destroy_all_by_post($post_id){$cond = array('post_id'=>$post_id);$rt = SingleTableCRUD::delete($this->tbPostComment,$cond);if ($rt){SingleTableCRUD::update(tiny_forum_post::getInstance()->tbPostInfo,array('post_id'=>$post_id,'num_comment'=>0),'post_id');}return $rt;}function destroy_all_by_user($user_id){$counter = false;$cond = ($user_id === tiny_forum::ALL_USER) ? null : array('uid'=>$user_id);$post_ids = SingleTableCRUD::findAll($this->tbPostComment,$cond,null,null,$counter,'post_id');if (empty($post_ids)) return false;$affectRows = 0;foreach ($post_ids as $post_id){if ($this->destroy_all_by_post($post_id)){$affectRows ++;}}return $affectRows;}function create(array $row){$rt = assertAssocArray($row,array('content' => array(array('not_empty', false),),'uid' => array(array('not_null', false),array('is_int', false),), 'post_id' => array(array('not_null', false),array('is_int', false),), ));if ($rt){$row['create_at'] = CURRENT_TIMESTAMP;$comment_id = SingleTableCRUD::insert($this->tbPostComment,$row); if ($comment_id){tiny_forum_post::getInstance()->incr_commentnum($row['post_id'],1);}}return false;}}// ---- 表关系模型 ----- 结束 --------------// ---- 控制器/Action ----- 开始 --------------// ---- 控制器/Action ----- 结束 --------------??
1 楼 vb2005xu 2011-11-21 http://www.diybl.com/course/5_office/word/wordshl/2007214/23000.html