def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_USER_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_user` ORDER BY `user_id` DESC LIMIT %s,%s" % ((int(page) - 1) * limit, limit)
return user_format(sdb.query(sql))
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_COMMENT_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_comments` ORDER BY `id` DESC LIMIT %s,%s" % ((int(page) - 1) * limit, limit)
return comment_format_admin(sdb.query(sql))
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_LINK_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_links` ORDER BY `display_order` DESC LIMIT %s,%s" % ((int(page) - 1) * limit, limit)
return sdb.query(sql)
def get_posts_by_category_id(self, category_id='', page=1, limit=None):
if limit is None:
limit = getAttr('EACH_PAGE_POST_NUM')
limit = int(limit)
sql = "SELECT * FROM `cms_posts` WHERE `category_id` = %s ORDER BY `post_id` DESC LIMIT %s,%s" % (
category_id, (int(page) - 1) * limit, limit)
sdb._ensure_connected()
return post_list_format(sdb.query(sql))
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_LINK_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_links` ORDER BY `display_order` DESC LIMIT %s,%s" % (
(int(page) - 1) * limit, limit)
return sdb.query(sql)
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_CATEGORY_NUM')
limit = int(limit)
sql = "SELECT * FROM `cms_category` ORDER BY `father_category_id` ASC, `display_order` DESC LIMIT %s,%s" % (
(int(page) - 1) * limit, limit)
sdb._ensure_connected()
return sdb.query(sql)
def get_recent_comments(self, limit=None):
if limit is None:
limit = getAttr('RECENT_COMMENT_NUM')
sdb._ensure_connected()
return comment_format(
sdb.query(
'SELECT * FROM `cms_comments` ORDER BY `comment_id` DESC LIMIT %s'
% limit))
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_USER_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_user` ORDER BY `user_id` DESC LIMIT %s,%s" % (
(int(page) - 1) * limit, limit)
return user_format(sdb.query(sql))
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_CATEGORY_NUM')
limit = int(limit)
sql = "SELECT * FROM `cms_category` ORDER BY `father_category_id` ASC, `display_order` DESC LIMIT %s,%s" % (
(int(page) - 1) * limit, limit)
sdb._ensure_connected()
return sdb.query(sql)
def get_posts_by_category_id(self, category_id='', page=1, limit=None):
if limit is None:
limit = getAttr('EACH_PAGE_POST_NUM')
limit = int(limit)
sql = "SELECT * FROM `cms_posts` WHERE `category_id` = %s ORDER BY `post_id` DESC LIMIT %s,%s" % (
category_id, (int(page) - 1) * limit, limit)
sdb._ensure_connected()
return post_list_format(sdb.query(sql))
def check_name_email(self, user_name='', email=''):
sql = "SELECT * FROM `cms_user` WHERE `user_name` = %s and `email` = %s and status=1 and deleted=0 LIMIT 1"
sdb._ensure_connected()
user = sdb.get(sql, user_name, email)
if user:
return True
else:
return False
def check_name_email(self, user_name='', email=''):
sql = "SELECT * FROM `cms_user` WHERE `user_name` = %s and `email` = %s and status=1 and deleted=0 LIMIT 1"
sdb._ensure_connected()
user = sdb.get(sql, user_name, email)
if user:
return True
else:
return False
def count_all(self, category_id=None, title=None):
sdb._ensure_connected()
sql = "SELECT COUNT(*) AS num FROM `cms_posts` WHERE 1=1"
if category_id:
sql += " and category_id = \'%s\'" % category_id
if title:
sql += " and title like \'%s\'" % title
return sdb.query(sql)[0]['num']
def get_paged(self, page=1, limit=None):
if limit is None:
limit = getAttr('ADMIN_COMMENT_NUM')
limit = int(limit)
sdb._ensure_connected()
sql = "SELECT * FROM `cms_comments` ORDER BY `id` DESC LIMIT %s,%s" % (
(int(page) - 1) * limit, limit)
return comment_format_admin(sdb.query(sql))
def get(self, category_id):
if category_id is None:
raise tornado.web.HTTPError(404)
sdb._ensure_connected()
post = sdb.get('SELECT * FROM `cms_category` WHERE `category_id` = %s' % category_id)
if post is None:
raise tornado.web.HTTPError(404)
return post
def get_all_kv(self, father_category_id=None):
if father_category_id:
sql = "SELECT `category_id`,`category_name` FROM `cms_category` WHERE `father_category_id` = 0 " \
"ORDER BY `father_category_id` ASC, `display_order` DESC"
else:
sql = "SELECT `category_id`,`category_name` FROM `cms_category` WHERE `show_type` <> \'url\' " \
"ORDER BY `father_category_id` ASC, `display_order` DESC"
sdb._ensure_connected()
return sdb.query(sql)
def get_all_kv(self, father_category_id=None):
if father_category_id:
sql = "SELECT `category_id`,`category_name` FROM `cms_category` WHERE `father_category_id` = 0 " \
"ORDER BY `father_category_id` ASC, `display_order` DESC"
else:
sql = "SELECT `category_id`,`category_name` FROM `cms_category` WHERE `show_type` <> \'url\' " \
"ORDER BY `father_category_id` ASC, `display_order` DESC"
sdb._ensure_connected()
return sdb.query(sql)
def get_page_posts_by_tag_name(self,
tag_name='',
page=1,
limit=EACH_PAGE_POST_NUM):
page = int(page)
sdb._ensure_connected()
return post_list_format(
sdb.query(
"SELECT * FROM `cms_posts` WHERE `tags` like \'%s\' ORDER BY `views` DESC LIMIT %s,%s"
% (tag_name, (int(page) - 1) * limit, limit)))
def get(self, category_id):
if category_id is None:
raise tornado.web.HTTPError(404)
sdb._ensure_connected()
post = sdb.get(
'SELECT * FROM `cms_category` WHERE `category_id` = %s' %
category_id)
if post is None:
raise tornado.web.HTTPError(404)
return post
def get_paged(self, page=1, limit=None, category_id=None, title=None):
if limit is None:
limit = getAttr('EACH_PAGE_POST_NUM')
limit = int(limit)
sql = "SELECT p.*,c.category_name FROM `cms_posts` as p inner join `cms_category` as c on p.category_id=c.category_id where 1=1"
if category_id:
sql += " and category_id = \'%s\'" % category_id
if title:
sql += " and title like \'%s\'" % title
sql += " ORDER BY `post_id` DESC LIMIT %s,%s" % ((int(page) - 1) * limit, limit)
sdb._ensure_connected()
return sdb.query(sql)
def get_post_page_comments_by_id(self, post_id=0, min_comment_id=0, limit=EACH_PAGE_COMMENT_NUM):
if min_comment_id == 0:
sdb._ensure_connected()
return comment_format(sdb.query(
'SELECT * FROM `cms_comments` WHERE `postid`= %s ORDER BY `comment_id` DESC LIMIT %s' % (
str(post_id), str(limit))))
else:
sql = "SELECT * FROM `cms_comments` WHERE `postid`= %s AND `comment_id` < %s " \
"ORDER BY `comment_id` DESC LIMIT %s"
sdb._ensure_connected()
return comment_format(sdb.query(
sql % (
str(post_id), str(min_comment_id), str(limit))))
def get_page_posts_by_archive_name(self,
archive_name='',
page=1,
limit=''):
if limit is None:
limit = getAttr('EACH_PAGE_POST_NUM')
sql = "SELECT p.* FROM `cms_posts` p " \
"inner join `cms_archive_post` ap on p.`post_id` = ap.post_id " \
"inner join `cms_archive` a on ap.`archive_id` = a.archive_id and a.archive_name = \'%s\''" \
"ORDER BY `id` DESC LIMIT %s,%s"
sdb._ensure_connected()
return post_list_format(
sdb.query(sql, archive_name, (int(page) - 1) * limit, limit))
def get_post_page_comments_by_id(self,
post_id=0,
min_comment_id=0,
limit=EACH_PAGE_COMMENT_NUM):
if min_comment_id == 0:
sdb._ensure_connected()
return comment_format(
sdb.query(
'SELECT * FROM `cms_comments` WHERE `postid`= %s ORDER BY `comment_id` DESC LIMIT %s'
% (str(post_id), str(limit))))
else:
sql = "SELECT * FROM `cms_comments` WHERE `postid`= %s AND `comment_id` < %s " \
"ORDER BY `comment_id` DESC LIMIT %s"
sdb._ensure_connected()
return comment_format(
sdb.query(sql %
(str(post_id), str(min_comment_id), str(limit))))
def get_all(self, limit=LINK_NUM):
sdb._ensure_connected()
return sdb.query(
'SELECT * FROM `cms_links` ORDER BY `display_order` DESC LIMIT %s'
% str(limit))
def get(self, link_id):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_links` WHERE `link_id` = %s' % str(link_id))
def get_all_category_name(self):
sdb._ensure_connected()
return sdb.query('SELECT `category_id`, `category_name` FROM `cms_category`')
def get_all(self, limit=HOT_TAGS_NUM):
sdb._ensure_connected()
return sdb.query(
'SELECT * FROM `cms_tags` ORDER BY `tag_id` DESC LIMIT %d' % limit)
def get_tag_by_name(self, tag_name=''):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_tags` WHERE `tag_name` = \'%s\'' %
tag_name)
def get_posts_num_by_tag_name(self, tag_name=''):
sdb._ensure_connected()
return sdb.query(
"SELECT count(*) FROM `cms_posts` WHERE `tags` like \'%s\'" %
tag_name)
def get_page_posts_by_tag_name(self, tag_name='', page=1, limit=EACH_PAGE_POST_NUM):
page = int(page)
sdb._ensure_connected()
return post_list_format(sdb.query(
"SELECT * FROM `cms_posts` WHERE `tags` like \'%s\' ORDER BY `views` DESC LIMIT %s,%s" % (
tag_name, (int(page) - 1) * limit, limit)))
def get(self, link_id):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_links` WHERE `link_id` = %s' %
str(link_id))
def get_all(self):
sdb._ensure_connected()
return sdb.query('SELECT * FROM `cms_user`')
def get(self, user_id):
sdb._ensure_connected()
return sdb.get(
'SELECT * FROM `cms_user` WHERE `user_id` = %s LIMIT 1' % user_id)
def count_all(self):
sdb._ensure_connected()
return sdb.query('SELECT COUNT(*) AS num FROM `cms_user`')[0]['num']
def get_role(self, user_id):
sql = "SELECT role_name FROM `cms_role` left join `cms_user_role` " \
"on `cms_role`.role_id = `cms_user_role`.role_id and user_id = %s"
sdb._ensure_connected()
return sdb.query(sql, user_id)
def get_all(self):
sdb._ensure_connected()
return sdb.query(
'SELECT * FROM `cms_category` ORDER BY `father_category_id` ASC, `display_order` DESC'
)
def get_post_num_by_category_id(self, category_id=''):
sql = "SELECT count(*) FROM `cms_posts` WHERE `category_id` = %s" % category_id
sdb._ensure_connected()
return sdb.query(sql)
def get_all(self, limit=HOT_TAGS_NUM):
sdb._ensure_connected()
return sdb.query('SELECT * FROM `cms_tags` ORDER BY `tag_id` DESC LIMIT %d' % limit)
def get_tag_by_name(self, tag_name=''):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_tags` WHERE `tag_name` = \'%s\'' % tag_name)
def check_has_user(self):
sdb._ensure_connected()
return sdb.get('SELECT `user_id` FROM `cms_user` LIMIT 1')
def get_by_name_or_email(self, name, email=None):
if not email:
email = name
sql = "SELECT * FROM `cms_user` WHERE `user_name` = \'%s\' or `email` = \'%s\' LIMIT 1"
sdb._ensure_connected()
return sdb.get(sql % (name, email))
def get_by_category_name(self, category_name=''):
sdb._ensure_connected()
return sdb.get(
'SELECT * FROM `cms_category` WHERE `category_name` like \'%s\'' %
category_name)
def get_all(self):
sdb._ensure_connected()
return sdb.query('SELECT * FROM `cms_category` ORDER BY `father_category_id` ASC, `display_order` DESC')
def get_all(self):
sdb._ensure_connected()
return post_list_format(sdb.query("SELECT * FROM `cms_posts` ORDER BY `post_id` DESC"))
def get_hot_tag(self, limit=HOT_TAGS_NUM):
#for sider
sdb._ensure_connected()
return sdb.query(
'SELECT * FROM `cms_tags` ORDER BY `tag_hot` DESC LIMIT %d' %
limit)
def get_all_category_name(self):
sdb._ensure_connected()
return sdb.query(
'SELECT `category_id`, `category_name` FROM `cms_category`')
def get_post_num_by_category_id(self, category_id=''):
sql = "SELECT count(*) FROM `cms_posts` WHERE `category_id` = %s" % category_id
sdb._ensure_connected()
return sdb.query(sql)
def get_post_by_author(self, user_id):
sql = "SELECT * FROM `cms_posts` where `user_id` = '%s'" % user_id
sdb._ensure_connected()
return sdb.query(sql)
def get_by_category_name(self, category_name=''):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_category` WHERE `category_name` like \'%s\'' % category_name)
def get_last_post(self, limit=None):
if limit is None:
limit = getAttr('EACH_PAGE_POST_NUM')
sdb._ensure_connected()
return post_list_format(
sdb.query("SELECT * FROM `cms_posts` ORDER BY `post_id` DESC LIMIT %s" % limit))
def get_all(self, limit=LINK_NUM):
sdb._ensure_connected()
return sdb.query('SELECT * FROM `cms_links` ORDER BY `display_order` DESC LIMIT %s' % str(limit))
def get_post_detail(self, post_id):
sql = 'SELECT p.*,`user_name`,`category_name` FROM `cms_posts` p ' \
'inner join `cms_category` c on p.category_id=c.category_id and p.`post_id` = %s ' \
'inner join `cms_user` u on p.user_id=u.user_id'
sdb._ensure_connected()
return post_detail_formate(sdb.get(sql, str(post_id)))
def count_all(self):
sdb._ensure_connected()
return sdb.query('SELECT COUNT(*) AS num FROM `cms_links`')[0]['num']
def get_post_simple(self, post_id):
sql = 'SELECT `post_id`,`user_name`,`category_name`,`title`,`comment_num`,`closecomment`,`password` FROM `cms_posts` p ' \
'inner join `cms_category` c on p.category_id=c.category_id and p.`post_id` = %s ' \
'inner join `cms_user` u on p.user_id=u.user_id'
sdb._ensure_connected()
return sdb.get(sql, post_id)
def get_hot_tag(self, limit=HOT_TAGS_NUM):
#for sider
sdb._ensure_connected()
return sdb.query('SELECT * FROM `cms_tags` ORDER BY `tag_hot` DESC LIMIT %d' % limit)
def get_post_for_sitemap(self, ids=[]):
sdb._ensure_connected()
return sdb.query("SELECT `post_id`,`last_modified_date` FROM `cms_posts` "
"WHERE `post_id` in(%s) ORDER BY `post_id` DESC LIMIT %s" % (','.join(ids), str(len(ids))))
def get_posts_num_by_tag_name(self, tag_name=''):
sdb._ensure_connected()
return sdb.query("SELECT count(*) FROM `cms_posts` WHERE `tags` like \'%s\'" % tag_name)
def get_all_tag_name(self, limit=HOT_TAGS_NUM):
#for add/edit post
sdb._ensure_connected()
return sdb.query(
'SELECT `tag_name` FROM `cms_tags` ORDER BY `tag_id` DESC LIMIT %d'
% limit)
def get_all_tag_name(self, limit=HOT_TAGS_NUM):
#for add/edit post
sdb._ensure_connected()
return sdb.query('SELECT `tag_name` FROM `cms_tags` ORDER BY `tag_id` DESC LIMIT %d' % limit)
def get(self, comment_id):
sdb._ensure_connected()
return sdb.get('SELECT * FROM `cms_comments` WHERE `comment_id` = %s' %
str(comment_id))
