def update(self, params):
query = "UPDATE `cms_links` SET `link_name` = \'%s\', `url` = \'%s\'," % (
params['link_name'], params['url'])
query += "`display_order` = %s WHERE `link_id` = %s" % (
params['display_order'], params['link_id'])
mdb._ensure_connected()
mdb.execute(query)
def create(self, params):
query = "INSERT INTO `cms_comments` (`post_id`, `user_name`, `email`, `website`, `content`, `status`,`created_date`) " \
"values(%s,%s,%s,%s,%s,%s,current_date)"
mdb._ensure_connected()
return mdb.execute(query, params['post_id'], params['user_name'],
params['email'], params['website'],
params['content'], params['status'])
def create(self, params):
mdb._ensure_connected()
query = "INSERT INTO `cms_category` (`father_category_id`, `category_name`, `display_order`, `show_type`, " \
"`description`, `allow_comment`, `allow_publish`,`created_date`) values(%s,%s,%s,%s,%s,%s,%s,%s,current_date)"
mdb.execute(query, params['father_category_id'],
params['category_name'], params['display_order'],
params['show_type'], params['description'],
params['allow_comment'], params['allow_publish'])
def remove_posts_from_archive(self, archive_id='', post_ids=[]):
mdb._ensure_connected()
mdb.execute(
"DELETE FROM `cms_archive_post` WHERE `archive_id` = %s and `post_id` in (%s) LIMIT 1",
archive_id, ','.join(post_ids))
mdb.execute(
"UPDATE `cms_archive` SET `post_num` = `post_num`-%s WHERE `archive_id` = %s LIMIT 1",
len(post_ids), archive_id)
def remove_post_from_archive(self, archive_id='', post_id=''):
mdb._ensure_connected()
mdb.execute(
"DELETE FROM `cms_archive_post` WHERE `post_id` = %s LIMIT 1",
post_id)
mdb.execute(
"UPDATE `cms_archive` SET `post_num` = `post_num`-1 WHERE `archive_id` = %s LIMIT 1",
archive_id)
def add_posts_to_archive(self, archive_id='', post_ids=[]):
mdb._ensure_connected()
mdb.execute(
"INSERT INTO `cms_archive_post` (`archive_id`, `post_id`) values (%s,%s)",
archive_id, post_ids)
mdb.execute(
"UPDATE `cms_archive` SET `post_num` = `post_num`+%s WHERE `archive_id` = %s LIMIT 1",
len(post_ids), archive_id)
def add_post_to_archive(self, archive_id='', post_id=''):
mdb._ensure_connected()
mdb.execute(
"INSERT INTO `cms_archive_post` (`archive_id`, `post_id`) values %s",
','.join('(%d,%d)' % (archive_id, x) for x in post_id))
mdb.execute(
"UPDATE `cms_archive` SET `post_num` = `post_num`+1 WHERE `archive_id` = %s LIMIT 1",
archive_id)
def create(self, params):
query = "INSERT INTO `cms_posts` (`category_id`, `user_id`, `title`, `tags`, `digest`, `content`, " \
"`image_url`, `password`, `salt`, `top`, `allow_comment`,`created_date`) " \
"values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,current_date)"
mdb._ensure_connected()
return mdb.execute(query, params['category_id'], params['user_id'], params['title'], params['tags'],
params['digest'], params['content'], params['image_url'], params['password'],
params['salt'], params['top'], params['allow_comment'])
def delete(self, comment_id):
cobj = self.get(comment_id)
post_id = cobj.post_id
from model.posts import Posts
pobj = Posts.get(post_id)
mdb._ensure_connected()
mdb.execute("DELETE FROM `cms_comments` WHERE `comment_id` = %s LIMIT 1", comment_id)
if pobj:
Posts.update_comment_num(pobj.comment_num - 1, post_id)
return
def update(self, params):
sql = "UPDATE `cms_category` SET "
if params['father_category_id']:
sql += "`father_category_id` = %s," % params['father_category_id']
sql += "`category_name` = \'%s\'," % params['category_name']
sql += "`display_order` = %s," % params['display_order']
sql += "`show_type` = \'%s\'," % params['show_type']
sql += "`description` = \'%s\'," % params['description']
sql += "`allow_comment` = %s," % params['allow_comment']
sql += "`allow_publish` = %s," % params['allow_publish']
sql += "category_id = %s WHERE category_id = %s"
mdb._ensure_connected()
mdb.execute(sql, params['category_id'], params['category_id'])
def update(self, params):
sql = "UPDATE `cms_category` SET "
if params['father_category_id']:
sql += "`father_category_id` = %s," % params['father_category_id']
sql += "`category_name` = \'%s\'," % params['category_name']
sql += "`display_order` = %s," % params['display_order']
sql += "`show_type` = \'%s\'," % params['show_type']
sql += "`description` = \'%s\'," % params['description']
sql += "`allow_comment` = %s," % params['allow_comment']
sql += "`allow_publish` = %s," % params['allow_publish']
sql += "category_id = %s WHERE category_id = %s"
mdb._ensure_connected()
mdb.execute(sql, params['category_id'], params['category_id'])
def flush_all_data(self):
sql = """
TRUNCATE TABLE `cms_category`;
TRUNCATE TABLE `cms_comments`;
TRUNCATE TABLE `cms_links`;
TRUNCATE TABLE `cms_posts`;
TRUNCATE TABLE `cms_tags`;
TRUNCATE TABLE `cms_archive`;
TRUNCATE TABLE `cms_user`;
TRUNCATE TABLE `cms_role`;
TRUNCATE TABLE `cms_user_role`;
"""
mdb._ensure_connected()
mdb.execute(sql)
def delete(self, comment_id):
cobj = self.get(comment_id)
post_id = cobj.post_id
from model.posts import Posts
pobj = Posts.get(post_id)
mdb._ensure_connected()
mdb.execute(
"DELETE FROM `cms_comments` WHERE `comment_id` = %s LIMIT 1",
comment_id)
if pobj:
Posts.update_comment_num(pobj.comment_num - 1, post_id)
return
def flush_all_data(self):
sql = """
TRUNCATE TABLE `cms_category`;
TRUNCATE TABLE `cms_comments`;
TRUNCATE TABLE `cms_links`;
TRUNCATE TABLE `cms_posts`;
TRUNCATE TABLE `cms_tags`;
TRUNCATE TABLE `cms_archive`;
TRUNCATE TABLE `cms_user`;
TRUNCATE TABLE `cms_role`;
TRUNCATE TABLE `cms_user_role`;
"""
mdb._ensure_connected()
mdb.execute(sql)
def create(self, params):
if params['user_name'] and params['email']:
user = Users.get_by_name_or_email(params['user_name'], params['email'])
if not user:
password = random_string(16)
salt = random_string(8)
password += salt
sql = "insert into `cms_user` (`user_name`,`email`, `password`, `salt`, `status`,`created_date`)" \
" values(%s,%s,%s,%s,%s,current_date)"
mdb._ensure_connected()
mdb.execute(sql, params['user_name'], params['email'],
md5(password.encode('utf-8')).hexdigest(), salt, params['status'])
return password
return None
def update(self, params):
if params['user_id']:
sql = "update `cms_user` set `status`=%s" % params['status']
if params['user_name'] != '':
sql += ", `user_name` = \'%s\'" % params['user_name']
if params['email'] != '':
sql += ", `email` = \'%s\'" % params['email']
if params['password'] and params['password'] != '':
salt = random_string(8)
params['password'] += salt
sql += ", `password` = \'%s\', `salt` = \'%s\'" % (md5(params['password']).hexdigest(), salt)
sql += " where `user_id` = \'%s\' LIMIT 1" % params['user_id']
mdb._ensure_connected()
return mdb.execute(sql)
else:
return None
def create(self, params):
if params['user_name'] and params['email']:
user = Users.get_by_name_or_email(params['user_name'],
params['email'])
if not user:
password = random_string(16)
salt = random_string(8)
password += salt
sql = "insert into `cms_user` (`user_name`,`email`, `password`, `salt`, `status`,`created_date`)" \
" values(%s,%s,%s,%s,%s,current_date)"
mdb._ensure_connected()
mdb.execute(sql, params['user_name'], params['email'],
md5(password.encode('utf-8')).hexdigest(), salt,
params['status'])
return password
return None
def update(self, params):
sql = "UPDATE `cms_posts` SET "
sql += "`category_id` = \'%s\'," % params['category_id']
sql += "`title` = \'%s\'," % params['title']
sql += "`tags` = \'%s\'," % params['tags']
sql += "`digest` = \'%s\'," % params['digest']
sql += "`content` = \'%s\'," % params['content']
sql += "`image_url` = \'%s\'," % params['image_url']
sql += "`password` = \'%s\'," % params['password']
sql += "`salt` = \'%s\'," % params['salt']
sql += "`image_url` = \'%s\'," % params['image_url']
sql += "`top` = %s," % params['top']
sql += "`allow_comment` = %s" % params['allow_comment']
sql += " where post_id = %s" % params['post_id']
mdb._ensure_connected()
mdb.execute(sql)
return params['post_id']
def update(self, params):
if params['user_id']:
sql = "update `cms_user` set `status`=%s" % params['status']
if params['user_name'] != '':
sql += ", `user_name` = \'%s\'" % params['user_name']
if params['email'] != '':
sql += ", `email` = \'%s\'" % params['email']
if params['password'] and params['password'] != '':
salt = random_string(8)
params['password'] += salt
sql += ", `password` = \'%s\', `salt` = \'%s\'" % (md5(
params['password']).hexdigest(), salt)
sql += " where `user_id` = \'%s\' LIMIT 1" % params['user_id']
mdb._ensure_connected()
return mdb.execute(sql)
else:
return None
def post(self):
self.set_header("Content-Type", "application/json")
rspd = {'error': ''}
email = self.get_argument("email")
if email:
if re.match("^.+\\@(\\[?)[a-zA-Z0-9\\-\\.]+\\.([a-zA-Z]{2,3}|[0-9]{1,3})(\\]?)$", email):
try:
query = "INSERT INTO `uc_subscribe` (`email`,`add_time`) values(%s,%s)"
mdb._ensure_connected()
mdb.execute(query, email, int(time.time()))
rspd['status'] = 200
except:
rspd['error'] = 'already_subscribed'
else:
rspd['error'] = 'invalid_email'
else:
rspd['error'] = 'empty_email'
self.write(json.dumps(rspd))
return
def update_user_audit(self, user_id, status=''):
sql = "update `cms_user` set `status` = %s where `user_id` = %s LIMIT 1"
mdb._ensure_connected()
return mdb.execute(sql, status, user_id)
def delete(self, user_id):
mdb._ensure_connected()
sql = "DELETE FROM `cms_user` WHERE `user_id`=%s"
mdb.execute(sql, user_id)
def delete(self, type_key):
mdb._ensure_connected()
mdb.execute("DELETE FROM `cms_show_types` WHERE `type_key` = %s LIMIT 1", type_key)
def update(self, params):
query = "UPDATE `cms_links` SET `link_name` = \'%s\', `url` = \'%s\'," % (params['link_name'], params['url'])
query += "`display_order` = %s WHERE `link_id` = %s" % (params['display_order'], params['link_id'])
mdb._ensure_connected()
mdb.execute(query)
def delete_by_father_category_id(self, father_category_id):
mdb._ensure_connected()
query = "DELETE FROM `cms_category` WHERE `father_category_id` = %s"
mdb.execute(query, father_category_id)
def create(self, params):
query = "INSERT INTO `cms_show_types` (`type_key`, `type_name`) values(%s,%s)"
mdb._ensure_connected()
return mdb.execute(query, params['type_key'], params['type_name'])
def create_db():
sql = """
DROP TABLE IF EXISTS `cms_user`;
CREATE TABLE IF NOT EXISTS `cms_user` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(40) NOT NULL DEFAULT '',
`email` varchar(40) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`salt` varchar(8) NOT NULL DEFAULT '',
`status` tinyint(1) NOT NULL DEFAULT '0',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_role`;
CREATE TABLE IF NOT EXISTS `cms_role` (
`role_id` int unsigned NOT NULL AUTO_INCREMENT,
`role_name` varchar(20) NOT NULL DEFAULT '',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`role_id`),
KEY `role_name` (`role_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_user_role`;
CREATE TABLE IF NOT EXISTS `cms_user_role` (
`user_id` int unsigned NOT NULL,
`role_id` int unsigned NOT NULL,
PRIMARY KEY (`user_id`, `role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_function`;
CREATE TABLE IF NOT EXISTS `cms_function` (
`function_id` int unsigned NOT NULL AUTO_INCREMENT,
`function_name` varchar(20) NOT NULL DEFAULT '',
`url` varchar(80) NOT NULL DEFAULT '',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`function_id`),
KEY `function_name` (`function_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_role_function`;
CREATE TABLE IF NOT EXISTS `cms_role_function` (
`role_id` int unsigned NOT NULL,
`function_id` int unsigned NOT NULL,
PRIMARY KEY (`role_id`, `function_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_category`;
CREATE TABLE IF NOT EXISTS `cms_category` (
`category_id` int unsigned NOT NULL AUTO_INCREMENT,
`father_category_id` int unsigned NOT NULL DEFAULT '0',
`category_name` varchar(20) NOT NULL DEFAULT '',
`display_order` tinyint(3) NOT NULL DEFAULT 0,
`show_type` varchar(10) NOT NULL DEFAULT 'default',
`description` varchar(500) NOT NULL,
`allow_comment` TINYINT(1) NOT NULL DEFAULT 1,
`allow_publish` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`category_id`),
KEY `category_name` (`category_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_show_type`;
CREATE TABLE IF NOT EXISTS `cms_show_type` (
`type_key` varchar(20) NOT NULL DEFAULT '',
`type_name` varchar(20) NOT NULL DEFAULT '',
KEY `type_key` (`type_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_posts`;
CREATE TABLE IF NOT EXISTS `cms_posts` (
`post_id` int unsigned NOT NULL AUTO_INCREMENT,
`category_id` int unsigned NOT NULL DEFAULT 0,
`user_id` int unsigned NOT NULL DEFAULT 0,
`title` varchar(100) NOT NULL DEFAULT '',
`tags` VARCHAR(255) NOT NULL DEFAULT '',
`digest` VARCHAR(500) NOT NULL DEFAULT '',
`content` mediumtext NOT NULL,
`image_url` varchar(80) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`salt` varchar(8) NOT NULL DEFAULT '',
`top` TINYINT(1) NOT NULL DEFAULT 0,
`rate` TINYINT(3) NOT NULL DEFAULT 0,
`rate_times` int unsigned NOT NULL DEFAULT 0,
`views` int unsigned NOT NULL DEFAULT 0,
`comment_num` int unsigned NOT NULL DEFAULT 0,
`allow_comment` TINYINT(1) NOT NULL DEFAULT 1,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_archive`;
CREATE TABLE IF NOT EXISTS `cms_archive` (
`archive_id` int unsigned NOT NULL AUTO_INCREMENT,
`archive_name` varchar(17) NOT NULL DEFAULT '',
`post_num` int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`archive_id`),
KEY `archive_name` (`archive_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_archive_post`;
CREATE TABLE IF NOT EXISTS `cms_archive_post` (
`archive_id` int unsigned NOT NULL,
`post_id` int unsigned NOT NULL,
PRIMARY KEY (`archive_id`, `post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_comments`;
CREATE TABLE IF NOT EXISTS `cms_comments` (
`comment_id` int unsigned NOT NULL AUTO_INCREMENT,
`post_id` int unsigned NOT NULL DEFAULT 0,
`user_name` varchar(40) NOT NULL DEFAULT '',
`email` varchar(40) NOT NULL DEFAULT '',
`website` varchar(80) NOT NULL DEFAULT '',
`content` mediumtext NOT NULL,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_links`;
CREATE TABLE IF NOT EXISTS `cms_links` (
`link_id` int unsigned NOT NULL AUTO_INCREMENT,
`link_name` varchar(100) NOT NULL DEFAULT '',
`url` varchar(200) NOT NULL DEFAULT '',
`display_order` tinyint(3) NOT NULL DEFAULT 0,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_tags`;
CREATE TABLE IF NOT EXISTS `cms_tags` (
`tag_id` int unsigned NOT NULL AUTO_INCREMENT,
`tag_name` varchar(20) NOT NULL DEFAULT '',
`tag_hot` int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`tag_id`),
KEY `tag_name` (`tag_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
INSERT INTO cms_category (category_id, father_category_id, category_name, display_order, show_type, url, description, allow_comment, allow_publish, deleted, last_modified_date, created_date) VALUES (0, 0, '顶级菜单', 0, 'url', '', '', false, true, false, '2013-07-21 16:20:56', '2013-07-21 16:13:12');
insert into cms_show_types values ('default', '默认显示');
insert into cms_show_types values ('list', '简单列表');
insert into cms_show_types values ('digest', '摘要列表');
insert into cms_show_types values ('pic', '图片瀑布');
insert into cms_show_types values ('video', '视频瀑布');
insert into cms_show_types values ('url', 'URL 网址');
"""
mdb._ensure_connected()
mdb.execute(sql)
def update(self, params):
query = "UPDATE `cms_comments` SET `content` = %s WHERE `comment_id` = %s LIMIT 1"
mdb._ensure_connected()
mdb.execute(query, params['content'], params['comment_id'])
return params['comment_id']
def create(self, params):
query = "INSERT INTO `cms_comments` (`post_id`, `user_name`, `email`, `website`, `content`, `status`,`created_date`) " \
"values(%s,%s,%s,%s,%s,%s,current_date)"
mdb._ensure_connected()
return mdb.execute(query, params['post_id'], params['user_name'], params['email'], params['website'],
params['content'], params['status'])
def delete(self, user_id):
mdb._ensure_connected()
sql = "DELETE FROM `cms_user` WHERE `user_id`=%s"
mdb.execute(sql, user_id)
def update_comment_num(self, num=1, post_id=''):
query = "UPDATE `cms_posts` SET `comment_num` = %s WHERE `post_id` = %s LIMIT 1"
mdb._ensure_connected()
return mdb.execute(query, num, post_id)
def update_user_audit(self, user_id, status=''):
sql = "update `cms_user` set `status` = %s where `user_id` = %s LIMIT 1"
mdb._ensure_connected()
return mdb.execute(sql, status, user_id)
def delete(self, link_id):
mdb._ensure_connected()
mdb.execute("DELETE FROM `cms_links` WHERE `link_id` = %s LIMIT 1",
link_id)
def update_post_edit_author(self, post_id, user_id):
sql = "UPDATE `cms_posts` SET `user_id` = %s WHERE `post_id` = %s LIMIT 1" % (user_id, post_id)
mdb._ensure_connected()
mdb.execute(sql)
def update(self, params):
query = "UPDATE `cms_show_types` SET `type_name` = %s WHERE `type_key` = %s LIMIT 1"
mdb._ensure_connected()
mdb.execute(query, params['type_name'], params['type_key'])
def delete(self, post_id=''):
if post_id:
mdb._ensure_connected()
mdb.execute("DELETE FROM `cms_posts` WHERE `post_id` = %s LIMIT 1", post_id)
mdb.execute("DELETE FROM `cms_comments` WHERE `post_id` = %s", post_id)
def update(self, params):
query = "UPDATE `cms_comments` SET `content` = %s WHERE `comment_id` = %s LIMIT 1"
mdb._ensure_connected()
mdb.execute(query, params['content'], params['comment_id'])
return params['comment_id']
def create(self, params):
mdb._ensure_connected()
query = "INSERT INTO `cms_category` (`father_category_id`, `category_name`, `display_order`, `show_type`, " \
"`description`, `allow_comment`, `allow_publish`,`created_date`) values(%s,%s,%s,%s,%s,%s,%s,%s,current_date)"
mdb.execute(query, params['father_category_id'], params['category_name'], params['display_order'],
params['show_type'], params['description'], params['allow_comment'], params['allow_publish'])
def create(self, params):
query = "INSERT INTO `cms_links` (`link_name`, `url`, `display_order`,`created_date`) values(%s,%s,%s,current_date)"
mdb._ensure_connected()
return mdb.execute(query, params['link_name'], params['url'],
params['display_order'])
def delete(self, category_id):
mdb._ensure_connected()
query = "DELETE FROM `cms_category` WHERE `category_id` = %s"
mdb.execute(query, category_id)
def delete(self, category_id):
mdb._ensure_connected()
query = "DELETE FROM `cms_category` WHERE `category_id` = %s"
mdb.execute(query, category_id)
def create(self, params):
query = "INSERT INTO `cms_links` (`link_name`, `url`, `display_order`,`created_date`) values(%s,%s,%s,current_date)"
mdb._ensure_connected()
return mdb.execute(query, params['link_name'], params['url'], params['display_order'])
def delete_by_father_category_id(self, father_category_id):
mdb._ensure_connected()
query = "DELETE FROM `cms_category` WHERE `father_category_id` = %s"
mdb.execute(query, father_category_id)
def delete(self, link_id):
mdb._ensure_connected()
mdb.execute("DELETE FROM `cms_links` WHERE `link_id` = %s LIMIT 1", link_id)
def create_db():
sql = """
DROP TABLE IF EXISTS `cms_user`;
CREATE TABLE IF NOT EXISTS `cms_user` (
`user_id` int unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(40) NOT NULL DEFAULT '',
`email` varchar(40) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`salt` varchar(8) NOT NULL DEFAULT '',
`status` tinyint(1) NOT NULL DEFAULT '0',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `user_name` (`user_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_role`;
CREATE TABLE IF NOT EXISTS `cms_role` (
`role_id` int unsigned NOT NULL AUTO_INCREMENT,
`role_name` varchar(20) NOT NULL DEFAULT '',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`role_id`),
KEY `role_name` (`role_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_user_role`;
CREATE TABLE IF NOT EXISTS `cms_user_role` (
`user_id` int unsigned NOT NULL,
`role_id` int unsigned NOT NULL,
PRIMARY KEY (`user_id`, `role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_function`;
CREATE TABLE IF NOT EXISTS `cms_function` (
`function_id` int unsigned NOT NULL AUTO_INCREMENT,
`function_name` varchar(20) NOT NULL DEFAULT '',
`url` varchar(80) NOT NULL DEFAULT '',
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`function_id`),
KEY `function_name` (`function_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_role_function`;
CREATE TABLE IF NOT EXISTS `cms_role_function` (
`role_id` int unsigned NOT NULL,
`function_id` int unsigned NOT NULL,
PRIMARY KEY (`role_id`, `function_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
DROP TABLE IF EXISTS `cms_category`;
CREATE TABLE IF NOT EXISTS `cms_category` (
`category_id` int unsigned NOT NULL AUTO_INCREMENT,
`father_category_id` int unsigned NOT NULL DEFAULT '0',
`category_name` varchar(20) NOT NULL DEFAULT '',
`display_order` tinyint(3) NOT NULL DEFAULT 0,
`show_type` varchar(10) NOT NULL DEFAULT 'default',
`description` varchar(500) NOT NULL,
`allow_comment` TINYINT(1) NOT NULL DEFAULT 1,
`allow_publish` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`category_id`),
KEY `category_name` (`category_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_show_type`;
CREATE TABLE IF NOT EXISTS `cms_show_type` (
`type_key` varchar(20) NOT NULL DEFAULT '',
`type_name` varchar(20) NOT NULL DEFAULT '',
KEY `type_key` (`type_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_posts`;
CREATE TABLE IF NOT EXISTS `cms_posts` (
`post_id` int unsigned NOT NULL AUTO_INCREMENT,
`category_id` int unsigned NOT NULL DEFAULT 0,
`user_id` int unsigned NOT NULL DEFAULT 0,
`title` varchar(100) NOT NULL DEFAULT '',
`tags` VARCHAR(255) NOT NULL DEFAULT '',
`digest` VARCHAR(500) NOT NULL DEFAULT '',
`content` mediumtext NOT NULL,
`image_url` varchar(80) NOT NULL DEFAULT '',
`password` varchar(32) NOT NULL DEFAULT '',
`salt` varchar(8) NOT NULL DEFAULT '',
`top` TINYINT(1) NOT NULL DEFAULT 0,
`rate` TINYINT(3) NOT NULL DEFAULT 0,
`rate_times` int unsigned NOT NULL DEFAULT 0,
`views` int unsigned NOT NULL DEFAULT 0,
`comment_num` int unsigned NOT NULL DEFAULT 0,
`allow_comment` TINYINT(1) NOT NULL DEFAULT 1,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_archive`;
CREATE TABLE IF NOT EXISTS `cms_archive` (
`archive_id` int unsigned NOT NULL AUTO_INCREMENT,
`archive_name` varchar(17) NOT NULL DEFAULT '',
`post_num` int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`archive_id`),
KEY `archive_name` (`archive_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_archive_post`;
CREATE TABLE IF NOT EXISTS `cms_archive_post` (
`archive_id` int unsigned NOT NULL,
`post_id` int unsigned NOT NULL,
PRIMARY KEY (`archive_id`, `post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_comments`;
CREATE TABLE IF NOT EXISTS `cms_comments` (
`comment_id` int unsigned NOT NULL AUTO_INCREMENT,
`post_id` int unsigned NOT NULL DEFAULT 0,
`user_name` varchar(40) NOT NULL DEFAULT '',
`email` varchar(40) NOT NULL DEFAULT '',
`website` varchar(80) NOT NULL DEFAULT '',
`content` mediumtext NOT NULL,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_links`;
CREATE TABLE IF NOT EXISTS `cms_links` (
`link_id` int unsigned NOT NULL AUTO_INCREMENT,
`link_name` varchar(100) NOT NULL DEFAULT '',
`url` varchar(200) NOT NULL DEFAULT '',
`display_order` tinyint(3) NOT NULL DEFAULT 0,
`status` TINYINT(1) NOT NULL DEFAULT 1,
`deleted` TINYINT(1) NOT NULL DEFAULT 0,
`last_modified_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`link_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `cms_tags`;
CREATE TABLE IF NOT EXISTS `cms_tags` (
`tag_id` int unsigned NOT NULL AUTO_INCREMENT,
`tag_name` varchar(20) NOT NULL DEFAULT '',
`tag_hot` int unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`tag_id`),
KEY `tag_name` (`tag_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
INSERT INTO cms_category (category_id, father_category_id, category_name, display_order, show_type, url, description, allow_comment, allow_publish, deleted, last_modified_date, created_date) VALUES (0, 0, '顶级菜单', 0, 'url', '', '', false, true, false, '2013-07-21 16:20:56', '2013-07-21 16:13:12');
insert into cms_show_types values ('default', '默认显示');
insert into cms_show_types values ('list', '简单列表');
insert into cms_show_types values ('digest', '摘要列表');
insert into cms_show_types values ('pic', '图片瀑布');
insert into cms_show_types values ('video', '视频瀑布');
insert into cms_show_types values ('url', 'URL 网址');
"""
mdb._ensure_connected()
mdb.execute(sql)
