def rssd_dict(id):
result = {}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,title,author,link,content from rss_detail where id=:id"
cursor.execute(sql, {"id": id})
adata = cursor.fetchone()
if adata:
result["id"] = adata[0]
result["title"] = adata[1]
if (adata[2] != None):
result["author"] = adata[2]
else:
result["author"] = ""
result["link"] = adata[3]
if (adata[4] != None):
result["content"] = del_script(content(adata[4]))
else:
result["content"] = ""
cursor.close()
dbconn.close()
return result
def rssd_taglist():
""" TAG List """
result = {"result": "nook", "taglist": None}
dbname = common.getdbname()
try:
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
taglists = []
sql = "select id,tname from tags order by tname"
cursor.execute(sql)
adata = cursor.fetchone()
while adata:
taglists.append({"id": adata[0], "name": adata[1]})
adata = cursor.fetchone()
cursor.close()
dbconn.close()
result["result"] = "ok"
result["taglist"] = taglists
except:
pass
return result
def rssd_taglist() :
""" TAG List """
result = {"result": "nook", "taglist": None}
dbname = common.getdbname()
try :
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
taglists = []
sql = "select id,tname from tags order by tname"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
taglists.append({"id":adata[0], "name":adata[1]})
adata = cursor.fetchone()
cursor.close()
dbconn.close()
result["result"] = "ok"
result["taglist"] = taglists
except :
pass
return result
def rssd_cancel(hashid):
# 刪除 rss_main 及 rss_detail 中關於 hashid 的資料
result = {"result": "ok"}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql0 = "select id from rss_main where hashid=:hashid"
cursor.execute(sql0, {"hashid": hashid})
adata = cursor.fetchone()
if adata:
mainid = adata[0]
else:
mainid = 0
if (mainid != 0):
sql1 = "delete from rss_main where id=:mainid"
cursor.execute(sql1, {"mainid": mainid})
sql2 = "delete from rss_detail where mainid=:mainid"
cursor.execute(sql2, {"mainid": mainid})
sql3 = "delete from tag_detail where rid=:mainid"
cursor.execute(sql3, {"mainid": mainid})
dbconn.commit()
cursor.close()
dbconn.close()
return result
def rssd_dict(id) :
result = {}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,title,author,link,content from rss_detail where id=:id"
cursor.execute(sql, {"id": id})
adata = cursor.fetchone()
if adata :
result["id"] = adata[0]
result["title"] = adata[1]
if (adata[2] != None) :
result["author"] = adata[2]
else :
result["author"] = ""
result["link"] = adata[3]
if (adata[4] != None) :
result["content"] = del_script(content(adata[4]))
else :
result["content"] = ""
cursor.close()
dbconn.close()
return result
def rssd_cancel(hashid) :
# 刪除 rss_main 及 rss_detail 中關於 hashid 的資料
result = {"result": "ok"}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql0 = "select id from rss_main where hashid=:hashid"
cursor.execute(sql0, {"hashid": hashid})
adata = cursor.fetchone()
if adata :
mainid = adata[0]
else :
mainid = 0
if (mainid != 0) :
sql1 = "delete from rss_main where id=:mainid"
cursor.execute(sql1, {"mainid": mainid})
sql2 = "delete from rss_detail where mainid=:mainid"
cursor.execute(sql2, {"mainid": mainid})
sql3 = "delete from tag_detail where rid=:mainid"
cursor.execute(sql3, {"mainid": mainid})
dbconn.commit()
cursor.close()
dbconn.close()
return result
def rssd_tagchoice(hashid,tagid,sel) :
"""
將收集的 rss網站 加上 TAG
當 tagid 或 sel 其中一個為 -1 時, 只查詢目前 TAG 情形
"""
result = {"result":"nook", "tagselected":None}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid":hashid})
adata = cursor.fetchone()
if adata :
mainid = adata[0]
else :
mainid = ""
if (mainid) :
if ((tagid != -1) and (sel != -1)) :
sql = "select count(rid) from tag_detail where rid=:rid and tid=:tid"
cursor.execute(sql, {"rid":mainid, "tid":tagid})
adata = cursor.fetchone()
acnt = adata[0]
if (sel == 1) : # 增加 TAG
if (acnt == 0) :
sql = "insert into tag_detail(rid,tid) values(:rid, :tid)"
cursor.execute(sql, {"rid":mainid, "tid":tagid})
dbconn.commit()
else : # 刪除 TAG
if (acnt > 0) :
sql = "delete from tag_detail where rid=:rid and tid=:tid"
cursor.execute(sql, {"rid":mainid, "tid":tagid})
dbconn.commit()
tagselected = []
sql = "select tid from tag_detail where rid=:rid"
cursor.execute(sql, {"rid":mainid})
adata = cursor.fetchone()
while adata :
tagselected.append(adata[0])
adata = cursor.fetchone()
result["result"] = "ok"
result["tagselected"] = tagselected
cursor.close()
dbconn.close()
return result
def patch001() :
""" rss_detail 增加欄位 star ; 2013-05-22 """
if (not common.checkfield("star", "rss_detail")) :
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "alter table rss_detail add column star boolean default 0"
cursor.execute(sql)
cursor.close()
dbconn.close()
print "patch 001 OK"
def patch001():
""" rss_detail 增加欄位 star ; 2013-05-22 """
if (not common.checkfield("star", "rss_detail")):
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "alter table rss_detail add column star boolean default 0"
cursor.execute(sql)
cursor.close()
dbconn.close()
print "patch 001 OK"
def tag_markasread(tagid, lastdate):
result = {"result": "nook"}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
try:
# 將某 tag 的所有 子item 改為已閱讀
sql = "update rss_detail set readed=1 where mainid in (select rid from tag_detail where tid=:tagid) and pubdate<=:lastdate and readed<>1"
cursor.execute(sql, {"tagid": tagid, "lastdate": lastdate})
dbconn.commit()
sql = "select rid from tag_detail where tid=:tagid"
cursor.execute(sql, {"tagid": tagid})
adata = cursor.fetchone()
while adata:
aid = adata[0]
cursor2 = dbconn.cursor()
# 重新計算 未讀 count
cursor2.execute(
"select count(*) from rss_detail where mainid=:mainid and readed<>1",
{"mainid": aid})
bdata = cursor2.fetchone()
if bdata:
unreadcnt = bdata[0]
else:
unreadcnt = 0
# 更新 未讀 count
sql2 = "update rss_main set unreadcnt=:unreadcnt where id=:aid"
cursor2.execute(sql2, {"aid": aid, "unreadcnt": unreadcnt})
dbconn.commit()
cursor2.close()
adata = cursor.fetchone()
result["result"] = "ok"
except:
pass
cursor.close()
dbconn.close()
return result
def markasread(hashid, lastdate):
result = {"result": "nook"}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,hashid from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid": hashid})
adata = cursor.fetchone()
if adata:
aid = adata[0]
# 將某 hashid 的所有 子item 改為已閱讀
cursor2 = dbconn.cursor()
sql2 = "update rss_detail set readed=1 where mainid=:mainid and pubdate<=:lastdate and readed<>1"
cursor2.execute(sql2, {"mainid": aid, "lastdate": lastdate})
dbconn.commit()
# 重新計算 未讀 count
cursor2.execute(
"select count(*) from rss_detail where mainid=:mainid and readed<>1",
{"mainid": aid})
bdata = cursor2.fetchone()
if bdata:
unreadcnt = bdata[0]
else:
unreadcnt = 0
# 更新 未讀 count
sql3 = "update rss_main set unreadcnt=:unreadcnt where hashid=:hashid"
cursor2.execute(sql3, {"hashid": hashid, "unreadcnt": unreadcnt})
dbconn.commit()
cursor2.close()
result["result"] = "ok"
result["id"] = aid
cursor.close()
dbconn.close()
return result
def rssdetail_readed(aid) :
result = {"result":"nook" ,"hashid":None, "unreadcnt":0}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "update rss_detail set readed=1 where id=:aid"
cursor.execute(sql, {"aid": aid})
dbconn.commit()
# 找出 mainid
sql = "select mainid from rss_detail where id=:aid"
cursor.execute(sql, {"aid": aid})
adata = cursor.fetchone()
if adata:
mainid = adata[0]
else :
mainid = -1
# 根據 mainid, 將 rss_main.unreadcnt - 1
if (mainid != -1) :
sql = "select count(*) from rss_detail where mainid=:mainid and readed<>1"
cursor.execute(sql, {"mainid":mainid})
adata = cursor.fetchone()
unread = adata[0]
sql = "update rss_main set unreadcnt=:unread where id=:id"
cursor.execute(sql, {"id":mainid, "unread":unread})
dbconn.commit()
sql = "select id,hashid,unreadcnt from rss_main where id=:id"
cursor.execute(sql, {"id":mainid})
adata = cursor.fetchone()
if adata :
result["result"] = "ok"
result["hashid"] = adata[1]
result["unreadcnt"] = adata[2]
cursor.close()
dbconn.close()
return result
def markasread(hashid, lastdate) :
result = {"result": "nook"}
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,hashid from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid":hashid})
adata = cursor.fetchone()
if adata :
aid = adata[0]
# 將某 hashid 的所有 子item 改為已閱讀
cursor2 = dbconn.cursor()
sql2 = "update rss_detail set readed=1 where mainid=:mainid and pubdate<=:lastdate and readed<>1"
cursor2.execute(sql2, {"mainid":aid, "lastdate":lastdate})
dbconn.commit()
# 重新計算 未讀 count
cursor2.execute("select count(*) from rss_detail where mainid=:mainid and readed<>1", {"mainid":aid})
bdata = cursor2.fetchone()
if bdata :
unreadcnt = bdata[0]
else :
unreadcnt = 0
# 更新 未讀 count
sql3 = "update rss_main set unreadcnt=:unreadcnt where hashid=:hashid"
cursor2.execute(sql3, {"hashid":hashid, "unreadcnt":unreadcnt})
dbconn.commit()
cursor2.close()
result["result"] = "ok"
result["id"] = aid
cursor.close()
dbconn.close()
return result
def patch002() :
""" 增加 table tags """
dbname = common.getdbname()
if (not common.checktable("tags")) :
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = """
create table if not exists tags (
id integer primary key autoincrement,
tname varcar
);
"""
cursor.execute(sql)
cursor.close()
dbconn.close()
print "patch 002 OK"
def patch002():
""" 增加 table tags """
dbname = common.getdbname()
if (not common.checktable("tags")):
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = """
create table if not exists tags (
id integer primary key autoincrement,
tname varcar
);
"""
cursor.execute(sql)
cursor.close()
dbconn.close()
print "patch 002 OK"
def tag_rename(tagid, newname) :
result = {"result": "nook"}
dbname = common.getdbname()
try :
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "update tags set tname=:newname where id=:tagid"
cursor.execute(sql, {"tagid":tagid, "newname":urllib.unquote(newname.decode("utf-8"))})
dbconn.commit()
cursor.close()
dbconn.close()
result["result"] = "ok"
except :
pass
return result
def rssd_star(aid, star=0):
""" 將 rss_detail 的 id 這筆記錄 加上星號 或 不加星號 """
result = {}
try:
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "update rss_detail set star=:star where id=:id"
cursor.execute(sql, {"star": star, "id": aid})
dbconn.commit()
cursor.close()
dbconn.close()
result["result"] = "ok"
except e:
result["result"] = e
return result
def rssd_star(aid, star=0) :
""" 將 rss_detail 的 id 這筆記錄 加上星號 或 不加星號 """
result = {}
try :
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "update rss_detail set star=:star where id=:id"
cursor.execute(sql, {"star":star, "id":aid})
dbconn.commit()
cursor.close()
dbconn.close()
result["result"] = "ok"
except e :
result["result"] = e
return result
def rsslist() :
rsslist_array = []
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,title,hashid,unreadcnt from rss_main order by id"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
aid = adata[0]
atitle = adata[1]
alink = adata[2]
unread = adata[3]
rsslist_array.append({"title":atitle, "link":alink, "unread":unread})
adata = cursor.fetchone()
cursor.close()
dbconn.close()
return rsslist_array
def tag_rename(tagid, newname):
result = {"result": "nook"}
dbname = common.getdbname()
try:
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "update tags set tname=:newname where id=:tagid"
cursor.execute(sql, {
"tagid": tagid,
"newname": urllib.unquote(newname.decode("utf-8"))
})
dbconn.commit()
cursor.close()
dbconn.close()
result["result"] = "ok"
except:
pass
return result
def rssd_newtag(tagname) :
""" 新增 TAG """
result = {"result": "nook", "taglist": None}
tagname = urllib.unquote(tagname.decode("utf-8"))
dbname = common.getdbname()
try :
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select count(*) from tags where tname=:tname"
cursor.execute(sql, {"tname": tagname})
adata = cursor.fetchone()
if (adata[0] == 0) :
sql = "insert into tags(tname) values(:tagname)"
cursor.execute(sql, {"tagname": tagname})
dbconn.commit()
taglists = []
sql = "select id,tname from tags order by tname"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
taglists.append({"id":adata[0], "name":adata[1]})
adata = cursor.fetchone()
cursor.close()
dbconn.close()
result["result"] = "ok"
result["taglist"] = taglists
except :
pass
return result
def rsstags() :
rsstags_array = []
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,tname from tags order by tname"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
aid = "tag_%03d" % adata[0]
atname = adata[1]
rsslist_array = []
cursor2 = dbconn.cursor()
sql2 = "select id,title,hashid,unreadcnt from rss_main where id in (select rid from tag_detail where tid=:tid) order by id"
cursor2.execute(sql2, {"tid": adata[0]})
bdata = cursor2.fetchone()
while bdata :
bid = bdata[0]
btitle = bdata[1]
blink = bdata[2]
unread = bdata[3]
rsslist_array.append({"title":btitle, "link":blink, "unread":unread})
bdata = cursor2.fetchone()
cursor2.close()
rsstags_array.append({"title":atname, "link":aid, "unread":0, "sub":rsslist_array})
adata = cursor.fetchone()
cursor.close()
dbconn.close()
return rsstags_array
def rssd_dict(atype, hashid="", keyword="", showmode=1, lastid="", tagid=-1, cnt=30) :
result = {"title":None, "link":None, "hashid":"", "unreadcnt":0, "detail":[]}
if (atype == 0) : # 一般情形
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,title,link,hashid,unreadcnt from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid": hashid})
adata = cursor.fetchone()
if adata :
mainid = adata[0]
# print mainid
result["title"] = adata[1]
result["link"] = adata[2]
result["hashid"] = adata[3]
result["unreadcnt"] = adata[4]
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid) :
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,rssid,title,pubdate,readed,star from rss_detail where mainid=:mainid and title<>''"
if (lastpubdate) :
sql2 += " and pubdate<:lastpubdate"
if (showmode == 2) :
sql2 += " and readed<>1"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {"mainid":mainid, "lastpubdate":lastpubdate, "cnt":cnt})
bdata = cursor2.fetchone()
while bdata :
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["rssid"] = bdata[1]
rsshash["title"] = bdata[2]
rsshash["pubdate"] = bdata[3]
rsshash["readed"] = bdata[4]
rsshash["star"] = bdata[5]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 1) : # 檢視 星號List
result["title"] = "星號列表"
result["hashid"] = ""
result["unreadcnt"] = 0
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid) :
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where star=1 and title<>''"
if (lastpubdate) :
sql2 += " and pubdate<:lastpubdate"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {"lastpubdate":lastpubdate, "cnt":cnt})
bdata = cursor2.fetchone()
while bdata :
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 2) : # 搜尋
result["title"] = "搜尋"
result["hashid"] = ""
result["unreadcnt"] = 0
keyword = urllib.unquote(keyword.decode("utf-8"))
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid) :
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where 1=1 and title<>''"
sql2 += " and (title like :keyword or content like :keyword or author like :keyword)"
if (lastpubdate) :
sql2 += " and pubdate<:lastpubdate"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
# print sql2
cursor2.execute(sql2, {"lastpubdate":lastpubdate, "keyword":'%'+keyword+'%', "cnt":cnt})
bdata = cursor2.fetchone()
while bdata :
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 3) : # TAG
result["title"] = "TAG"
result["hashid"] = ""
result["unreadcnt"] = 0
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# TAG 名稱
sql = "select tname from tags where id=:tagid"
cursor.execute(sql, {"tagid": tagid})
adata = cursor.fetchone()
if adata :
result["title"] = adata[0]
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata :
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid) :
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where mainid in (select rid from tag_detail where tid=:tid) and title<>''"
if (lastpubdate) :
sql2 += " and pubdate<:lastpubdate"
if (showmode == 2) :
sql2 += " and readed<>1"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {"tid":tagid, "lastpubdate":lastpubdate, "cnt":cnt})
bdata = cursor2.fetchone()
while bdata :
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
return result
cursor.close()
def updatesingle(self, hashid) :
""" 更新 db.rss_main 中某個 xmlurl 的 RSS 資料 """
cursor = self.dbconn.cursor()
sql = "select id,title,xmlurl from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid": hashid})
alldata = cursor.fetchall()
for adata in alldata:
aid = adata[0]
atitle = adata[1].encode("utf-8")
axmlurl = adata[2]
if (self.showmsg) :
print "===== %03d [%s] =====" % (aid, atitle)
self.rss2db(axmlurl, aid)
cursor.close()
if __name__ == "__main__" :
dbname = common.getdbname()
if (dbname and os.path.exists(dbname)) :
rss2db = Rss2DB(dbname)
if (len(sys.argv) > 1) :
rss2db.updatesingle(sys.argv[1])
else :
rss2db.updateall()
else :
print "Can't get db !!"
def rssd_dict(atype,
hashid="",
keyword="",
showmode=1,
lastid="",
tagid=-1,
cnt=30):
result = {
"title": None,
"link": None,
"hashid": "",
"unreadcnt": 0,
"detail": []
}
if (atype == 0): # 一般情形
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
sql = "select id,title,link,hashid,unreadcnt from rss_main where hashid=:hashid"
cursor.execute(sql, {"hashid": hashid})
adata = cursor.fetchone()
if adata:
mainid = adata[0]
# print mainid
result["title"] = adata[1]
result["link"] = adata[2]
result["hashid"] = adata[3]
result["unreadcnt"] = adata[4]
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid):
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,rssid,title,pubdate,readed,star from rss_detail where mainid=:mainid and title<>''"
if (lastpubdate):
sql2 += " and pubdate<:lastpubdate"
if (showmode == 2):
sql2 += " and readed<>1"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {
"mainid": mainid,
"lastpubdate": lastpubdate,
"cnt": cnt
})
bdata = cursor2.fetchone()
while bdata:
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["rssid"] = bdata[1]
rsshash["title"] = bdata[2]
rsshash["pubdate"] = bdata[3]
rsshash["readed"] = bdata[4]
rsshash["star"] = bdata[5]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 1): # 檢視 星號List
result["title"] = "星號列表"
result["hashid"] = ""
result["unreadcnt"] = 0
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata:
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid):
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where star=1 and title<>''"
if (lastpubdate):
sql2 += " and pubdate<:lastpubdate"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {"lastpubdate": lastpubdate, "cnt": cnt})
bdata = cursor2.fetchone()
while bdata:
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 2): # 搜尋
result["title"] = "搜尋"
result["hashid"] = ""
result["unreadcnt"] = 0
keyword = urllib.unquote(keyword.decode("utf-8"))
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata:
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid):
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where 1=1 and title<>''"
sql2 += " and (title like :keyword or content like :keyword or author like :keyword)"
if (lastpubdate):
sql2 += " and pubdate<:lastpubdate"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
# print sql2
cursor2.execute(sql2, {
"lastpubdate": lastpubdate,
"keyword": '%' + keyword + '%',
"cnt": cnt
})
bdata = cursor2.fetchone()
while bdata:
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
elif (atype == 3): # TAG
result["title"] = "TAG"
result["hashid"] = ""
result["unreadcnt"] = 0
dbname = common.getdbname()
dbconn = sqlite3.connect(dbname)
cursor = dbconn.cursor()
# TAG 名稱
sql = "select tname from tags where id=:tagid"
cursor.execute(sql, {"tagid": tagid})
adata = cursor.fetchone()
if adata:
result["title"] = adata[0]
# 建立 rss_main.id 對 title 的 dict
main_titles = {}
sql = "select id,title from rss_main"
cursor.execute(sql)
adata = cursor.fetchone()
while adata:
main_titles[adata[0]] = adata[1]
adata = cursor.fetchone()
# 取得 lastid 的 pubdate
lastpubdate = ""
if (lastid):
cursor1 = dbconn.cursor()
sql1 = "select pubdate from rss_detail where id=:id"
cursor1.execute(sql1, {"id": lastid})
lastpubdate = cursor1.fetchone()[0]
cursor1.close()
rssd_list = []
cursor2 = dbconn.cursor()
sql2 = "select id,mainid,rssid,title,pubdate,readed,star from rss_detail where mainid in (select rid from tag_detail where tid=:tid) and title<>''"
if (lastpubdate):
sql2 += " and pubdate<:lastpubdate"
if (showmode == 2):
sql2 += " and readed<>1"
sql2 += " order by pubdate desc"
sql2 += " limit 0,:cnt"
cursor2.execute(sql2, {
"tid": tagid,
"lastpubdate": lastpubdate,
"cnt": cnt
})
bdata = cursor2.fetchone()
while bdata:
rsshash = {}
rsshash["id"] = bdata[0]
rsshash["main_title"] = main_titles[bdata[1]]
rsshash["rssid"] = bdata[2]
rsshash["title"] = bdata[3]
rsshash["pubdate"] = bdata[4]
rsshash["readed"] = bdata[5]
rsshash["star"] = bdata[6]
rssd_list.append(rsshash)
bdata = cursor2.fetchone()
cursor2.close()
result["detail"] = rssd_list
cursor.close()
dbconn.close()
return result
"utf-8")
return rssdict
if __name__ == "__main__":
result = {"result": None, "result2": None, "message": None, "hashid": None}
params = cgiFieldStorageToDict(cgi.FieldStorage())
url = params["weburl"]
if (url != ""):
rssdict = webrss(url)
if (rssdict and rssdict["title"]):
dbname = common.getdbname()
if (dbname and os.path.exists(dbname)):
conn = sqlite3.connect(dbname)
cursor0 = conn.cursor()
sql0 = "select xmlurl from rss_main where xmlurl=:xmlurl"
cursor0.execute(sql0, {'xmlurl': rssdict["xmlurl"]})
adata = cursor0.fetchone()
if adata:
result["result2"] = "update"
result["message"] = "[update]:" + "(" + rssdict[
"htmlurl"] + ")" + rssdict["title"].encode("utf-8")
result["hashid"] = rssdict["hashid"]
cursor = conn.cursor()
sql = "update rss_main set title=:title,link=:link,description=:description where xmlurl=:xmlurl"
