自个儿动手实现 多用户论坛系统 tiny_forum

自己动手实现 多用户论坛系统 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 users

final 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 ----- 结束 --------------