def get(self, id):
id = int(id) # 转换成数字
cur = self.db.cursor()
cur.execute('SELECT posttime FROM articles WHERE id = %s', (id,))
posttime = fetchone(cur).posttime
cur.execute('UPDATE tags SET count = count - 1 '
'WHERE tag in (SELECT tag FROM link_tags_articles WHERE article_id = %s)', (id,))
cur.execute('DELETE FROM link_tags_articles WHERE article_id = %s', (id,))
cur.execute('DELETE FROM tags WHERE count = 0')
cur.execute('DELETE FROM link_files_articles WHERE article_id = %s', (id,))
cur.execute('DELETE FROM articles WHERE id = %s', (id,))#删除文章
date_add(self.db, posttime, add=-1) # 减去一个日期计数
search.delete_document(id) # 删除索引
search.flush()
return self.write(u'ID:%s 的文章已删除' % id)
def call():
"""运行此任务
:returns: none
"""
cur = db.cursor()
cur.execute(
'SELECT id, title, content, posttime, '
'ARRAY('
'SELECT tag FROM link_tags_articles WHERE link_tags_articles.article_id = articles.id'
') as tags '
'FROM articles WHERE id != %s ',
(0,))
for row in fetchall(cur):
search.index(row['id'], row['title']+row['content']+(' '.join(tag for tag in row['tags'])), values={1:xapian.sortable_serialise(time.mktime(row['posttime'].timetuple()))})
search.flush()
def post(self):
cur = self.db.cursor()
title = self.get_argument('title', '') # 标题
content = self.get_argument('content', '') # 内容
render = html_body(content) # 渲染rst结果到缓存
posttime = datetime.now()
tags = self.get_argument('tags', '').split(',') # tag集合
tags = tuple(set(tags)) # 去除重复记录
files = self.get_argument('files', '').split(',') # files集合
if '' in files:
files.remove('')#去掉空文件
cur.execute('SELECT tag FROM tags WHERE tag in %s', (tags,))
exists_tags = fetchall(cur)
insert_tags = list(tags)
for t in exists_tags:
insert_tags.remove(t.tag)
cur.execute('INSERT INTO articles(title,content,render,posttime) VALUES(%s,%s,%s,%s) RETURNING id',
(title, content, render, posttime))#插入文章
article_id = cur.fetchone()[0]#取得插入的文章id
for t in insert_tags:#插入新的tag
cur.execute('INSERT INTO tags VALUES(%s,%s)', (t, 0))
for t in tags:
cur.execute('INSERT INTO link_tags_articles(tag,article_id) VALUES(%s,%s)', (t, article_id))#插入tag关系
cur.execute('UPDATE tags SET count = count+1 WHERE tag in %s', (tags,))#更新count值
for f in files:
cur.execute('INSERT INTO link_files_articles(filename,article_id) VALUES(%s,%s)', (f, article_id))#插入附件关系
date_add(self.db, posttime) # 添加日期计数
#加到xapian全文索引中
terms = title+content+(' '.join(tag for tag in tags))
if type(terms) == unicode:
terms = terms.encode('utf-8')
search.index(article_id, terms, values={1:xapian.sortable_serialise(mktime(posttime.timetuple()))})
search.flush()
self.redirect(self.reverse_url('index'))
def post(self, id):
id = int(id)
cur = self.db.cursor()
title = self.get_argument('title', '') # 标题
content = self.get_argument('content', '') # 内容
render = html_body(content) # 渲染rst结果到缓存
tags = self.get_argument('tags', '').split(',') # tag集合
tags = tuple(set(tags)) # 去除重复记录
files = self.get_argument('files', '').split(',') # files集合
if '' in files:
files.remove('')#去掉空文件
files = tuple(files)
# ------------------处理tags---------------------
if tags:
cur.execute('SELECT tag FROM link_tags_articles WHERE article_id = %s and tag not in %s', (id, tags))
del_tags = tuple([t.tag for t in fetchall(cur)])#要删除的tag
cur.execute('SELECT tag FROM link_tags_articles WHERE article_id = %s and tag in %s', (id, tags))
exists_tags = fetchall(cur)#已经存在的tag
add_tags = list(tags)#要添加的tag
for t in exists_tags:
if t.tag.decode('utf-8') in add_tags:
add_tags.remove(t.tag.decode('utf-8'))#生成要添加的tag列表
cur.execute('SELECT tag FROM tags WHERE tag in %s', (tags,))
exists_tags = fetchall(cur)
insert_tags = list(tags)#需要新插入的tag
for t in exists_tags:
if t.tag.decode('utf-8') in insert_tags:
insert_tags.remove(t.tag.decode('utf-8'))
else:
del_tags = ()
add_tags = []
insert_tags = []
# ------------------处理附件---------------------
if files:
cur.execute('SELECT filename FROM link_files_articles WHERE article_id = %s and filename not in %s',
(id, files))
del_files = tuple([f.filename for f in fetchall(cur)])#要删除的file
cur.execute('SELECT filename FROM link_files_articles WHERE article_id = %s and filename in %s',
(id, files))
exists_files = fetchall(cur)#已经存在的file
add_files = list(files)#要添加的file
for f in exists_files:
if f.filename.decode('utf-8') in add_files:
add_files.remove(f.filename.decode('utf-8'))#生成要添加的file列表
else:
del_files = ()
add_files = []
if del_tags:
cur.execute('DELETE FROM link_tags_articles WHERE tag in %s and article_id = %s', (del_tags, id))
cur.execute('UPDATE tags SET count = count - 1 WHERE tag in %s', (del_tags,))
cur.execute('DELETE FROM tags WHERE count <=0')
for t in insert_tags:
cur.execute('INSERT INTO tags VALUES(%s,%s)', (t, 0))
for t in add_tags:
cur.execute('INSERT INTO link_tags_articles(tag,article_id) VALUES(%s,%s)', (t, id))#给新tag添加关联
if del_files:
cur.execute('DELETE FROM link_files_articles WHERE filename in %s and article_id = %s', (del_files, id))
for f in add_files:
cur.execute('INSERT INTO link_files_articles(filename,article_id) VALUES(%s,%s)', (f, id))
cur.execute('UPDATE articles SET title = %s,content=%s,render=%s WHERE id = %s',
(title, content, render, id))#更新文章内容
self.db.commit()
#重新索引
terms = title+content+(' '.join(tag for tag in tags))
if type(terms) == unicode:
terms = terms.encode('utf-8')
search.update_index(id, terms)
search.flush()
return self.redirect(self.reverse_url('article', id))