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