def add(cls, name=None, uid=None, session_id=None):
cursor = db_conn.cursor()
user = None
name = "" if name is None else name
uid = "" if uid is None else uid
session_id = session_id if session_id else randbytes(8)
try:
cursor.execute("""insert into user (uid, name, session_id)
values (%s, %s, %s)""",
(uid, name, session_id))
user_id = cursor.lastrowid
if uid == "":
cursor.execute("""update user set uid=%s where id=%s""",
(user_id, user_id))
db_conn.commit()
cls._clear_cache(None)
user = cls.get(user_id)
except IntegrityError:
db_conn.rollback()
finally:
cursor.close()
return user
def remove(self):
cursor = db_conn.cursor()
cursor.execute("""delete from sync_task
where id=%s""", self.id)
db_conn.commit()
cursor.close()
return None
def update_session(self, session_id):
cursor = db_conn.cursor()
cursor.execute("""update user set session_id=%s where id=%s""",
(session_id, self.id))
cursor.close()
db_conn.commit()
User._clear_cache(self.id)
def get_ids(cls, start=0, limit=20, order="id desc"):
cursor = db_conn.cursor()
sql = """select id from user
order by """ + order + """ limit %s, %s"""
cursor.execute(sql, (start, limit))
rows = cursor.fetchall()
cursor.close()
return [x[0] for x in rows]
def get_oldest_create_time(cls, cate, user_id):
cursor = db_conn.cursor()
cursor.execute('''select min(create_time) from status
where category=%s and user_id=%s''', (cate, user_id))
row = cursor.fetchone()
if row:
return row[0]
else:
return 0
def get_count_by_cate(cls, cate, user_id):
cursor = db_conn.cursor()
cursor.execute('''select count(1) from status
where category=%s and user_id=%s''', (cate, user_id))
row = cursor.fetchone()
if row:
return row[0]
else:
return 0
def get_count_by_user(cls, user_id):
cursor = db_conn.cursor()
cursor.execute('''select count(1) from status
where user_id=%s''', user_id)
row = cursor.fetchone()
if row:
return row[0]
else:
return 0
def get_min_origin_id(cls, cate, user_id):
cursor = db_conn.cursor()
cursor.execute('''select min(origin_id) from status
where category=%s and user_id=%s''', (cate, user_id))
row = cursor.fetchone()
if row:
return row[0]
else:
return 0
def get(cls, id):
task = None
cursor = db_conn.cursor()
cursor.execute("""select category,user_id,time from sync_task
where id=%s limit 1""", id)
row = cursor.fetchone()
if row:
task = cls(id, *row)
cursor.close()
return task
def get(cls, alias_id):
ot = None
cursor = db_conn.cursor()
cursor.execute("""select access_token, refresh_token
from oauth2_token where alias_id=%s order by time desc limit 1""",
(alias_id,))
row = cursor.fetchone()
if row:
ot = cls(alias_id, row[0], row[1])
return ot
def get_by_id(cls, id):
ua = None
cursor = db_conn.cursor()
cursor.execute("""select `id`, `type`, alias, user_id from user_alias
where id=%s""", id)
row = cursor.fetchone()
if row:
ua = cls(*row)
cursor.close()
return ua
def gets_by_user_id(cls, user_id):
uas = []
cursor = db_conn.cursor()
cursor.execute("""select `id`, `type`, alias from user_alias
where user_id=%s""", user_id)
rows = cursor.fetchall()
if rows and len(rows) > 0:
uas = [cls(row[0], row[1], row[2], user_id) for row in rows]
cursor.close()
return uas
def get(cls, id):
task = None
cursor = db_conn.cursor()
cursor.execute("""select id, task_id, task_kind, time from task_queue
where id=%s limit 1""", id)
row = cursor.fetchone()
if row:
task = cls(*row)
cursor.close()
return task
def get(cls, type_, alias):
ua = None
cursor = db_conn.cursor()
cursor.execute("""select `id`, user_id from user_alias
where `type`=%s and alias=%s""",
(type_, alias))
row = cursor.fetchone()
if row:
ua = cls(row[0], type_, alias, row[1])
cursor.close()
return ua
def get(cls, status_id):
status = None
cursor = db_conn.cursor()
cursor.execute("""select user_id, origin_id, create_time, site,
category, title from status
where id=%s""", status_id)
row = cursor.fetchone()
if row:
status = cls(status_id, *row)
cursor.close()
return status
def get_ids(cls, start=0, limit=0):
ids = []
cursor = db_conn.cursor()
if limit == 0:
limit = 100000000
cursor.execute("""select `id` from user_alias
limit %s, %s""", (start, limit))
rows = cursor.fetchall()
if rows and len(rows) > 0:
ids = [row[0] for row in rows]
cursor.close()
return ids
def add(cls, task_id, task_kind):
task = None
cursor = db_conn.cursor()
try:
cursor.execute("""insert into task_queue
(task_id, task_kind) values (%s,%s)""",
(task_id, task_kind))
db_conn.commit()
task = cls.get(cursor.lastrowid)
except IntegrityError:
db_conn.rollback()
finally:
cursor.close()
return task
def add(cls, alias_id, access_token, refresh_token):
ot = None
cursor = db_conn.cursor()
try:
cursor.execute("""replace into oauth2_token
(alias_id, access_token, refresh_token)
values (%s, %s, %s)""",
(alias_id, access_token, refresh_token))
db_conn.commit()
ot = cls.get(alias_id)
except IntegrityError:
db_conn.rollback()
finally:
cursor.close()
return ot
def add(cls, category, user_id):
task = None
cursor = db_conn.cursor()
try:
cursor.execute("""insert into sync_task
(category, user_id) values (%s,%s)""",
(category, user_id))
db_conn.commit()
task_id = cursor.lastrowid
task = cls.get(task_id)
except IntegrityError:
db_conn.rollback()
finally:
cursor.close()
return task
def get_ids(cls, user_id, start=0, limit=20, order="create_time", cate=None):
cursor = db_conn.cursor()
if not user_id:
return []
if cate is not None:
if str(cate) == str(config.CATE_DOUBAN_NOTE):
return []
sql = """select id from status where user_id=%s and category=%s
order by """ + order + """ desc limit %s,%s"""
cursor.execute(sql, (user_id, cate, start, limit))
else:
sql = """select id from status where user_id=%s and category!=%s
order by """ + order + """ desc limit %s,%s"""
cursor.execute(sql, (user_id, config.CATE_DOUBAN_NOTE, start, limit))
rows = cursor.fetchall()
cursor.close()
return [x[0] for x in rows]
def bind_to_exists_user(cls, user, type_, alias):
ua = cls.get(type_, alias)
if ua:
return None
ua = None
cursor = db_conn.cursor()
try:
cursor.execute("""insert into user_alias (`type`,alias,user_id)
values (%s, %s, %s)""", (type_, alias, user.id))
db_conn.commit()
ua = cls.get(type_, alias)
except IntegrityError:
db_conn.rollback()
finally:
cursor.close()
return ua
def get(cls, id):
uid = None
if isinstance(id, basestring) and not id.isdigit():
uid = id
cursor = db_conn.cursor()
if uid:
cursor.execute("""select id, uid,name,session_id,time
from user where uid=%s""", uid)
else:
cursor.execute("""select id, uid,name,session_id,time
from user where id=%s""", id)
row = cursor.fetchone()
cursor.close()
if row:
u = cls(row[0])
u.uid = str(row[1])
u.name = row[2]
u.session_id = row[3]
u.create_time = row[4]
return u
return None
def add(cls, user_id, origin_id, create_time, site, category, title,
text=None, raw=None):
status = None
cursor = db_conn.cursor()
try:
cursor.execute("""insert into status
(user_id, origin_id, create_time, site, category, title)
values (%s,%s,%s,%s,%s,%s)""",
(user_id, origin_id, create_time, site, category, title))
db_conn.commit()
status_id = cursor.lastrowid
if text is not None:
redis_conn.set(cls.STATUS_REDIS_KEY %status_id, json_encode(text))
if raw is not None:
redis_conn.set(cls.RAW_STATUS_REDIS_KEY %status_id, raw)
cls._clear_cache(user_id, None)
status = cls.get(status_id)
except IntegrityError:
log.warning("add status duplicated, ignore...")
db_conn.rollback()
finally:
cursor.close()
return status
def remove(self):
cursor = db_conn.cursor()
cursor.execute("""delete from task_queue
where id=%s""", self.id)
db_conn.commit()
cursor.close()
def get_all_ids(cls):
cursor = db_conn.cursor()
cursor.execute("""select id from task_queue order by time""")
r = [row[0] for row in cursor.fetchall()]
cursor.close()
return r
def get_ids(cls):
cursor = db_conn.cursor()
cursor.execute("""select id from sync_task""")
r = [row[0] for row in cursor.fetchall()]
cursor.close()
return r
def gets_by_user(cls, user):
cursor = db_conn.cursor()
cursor.execute("""select id from sync_task where user_id = %s""", user.id)
rows = cursor.fetchall()
cursor.close()
return [cls.get(row[0] )for row in rows]