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): 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_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 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 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 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 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 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 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): 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 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 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(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 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 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 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(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 delete(self, link_id): mdb._ensure_connected() mdb.execute("DELETE FROM `cms_links` WHERE `link_id` = %s LIMIT 1", link_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 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 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 delete(self, user_id): mdb._ensure_connected() sql = "DELETE FROM `cms_user` WHERE `user_id`=%s" mdb.execute(sql, user_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 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 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 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 delete(self, type_key): mdb._ensure_connected() mdb.execute("DELETE FROM `cms_show_types` WHERE `type_key` = %s LIMIT 1", type_key)
def delete(self, category_id): mdb._ensure_connected() query = "DELETE FROM `cms_category` WHERE `category_id` = %s" mdb.execute(query, category_id)