def add(cls, name=None, uid=None, session_id=None):
cursor = None
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 = db_conn.execute("""insert into user (uid, name, session_id)
values (%s, %s, %s)""",
(uid, name, session_id))
user_id = cursor.lastrowid
if uid == "":
cursor = db_conn.execute("""update user set uid=%s where id=%s""",
(user_id, user_id), cursor=cursor)
db_conn.commit()
cls._clear_cache(None)
user = cls.get(user_id)
except IntegrityError:
db_conn.rollback()
finally:
cursor and cursor.close()
return user
def _find_by(cls, col, value, start=0, limit=1):
assert limit >= 0
if limit == 0:
cursor = db_conn.execute(
"""select id, user_id, token, device
from user_tokens where `"""
+ col
+ """`=%s""",
value,
)
else:
cursor = db_conn.execute(
"""select id, user_id, token, device
from user_tokens where `"""
+ col
+ """`=%s limit %s, %s""",
(value, start, limit),
)
if limit == 1:
row = cursor.fetchone()
cursor and cursor.close()
return row and cls(*row)
else:
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def get(cls, id):
uid = None
if isinstance(id, basestring) and not id.isdigit():
uid = id
cursor = None
if uid:
cursor = db_conn.execute(
"""select id, uid,name,session_id,time
from user where uid=%s""",
uid,
)
else:
cursor = db_conn.execute(
"""select id, uid,name,session_id,time
from user where id=%s""",
id,
)
row = cursor.fetchone()
cursor and 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 get_ids(cls, user_id, start=0, limit=20, order="create_time", cate=None):
cursor = None
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 = db_conn.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 = db_conn.execute(sql, (user_id, config.CATE_DOUBAN_NOTE, start, limit))
rows = cursor.fetchall()
cursor and cursor.close()
return [x[0] for x in rows]
def move_status():
STATUS_REDIS_KEY = "/status/text/%s"
RAW_STATUS_REDIS_KEY = "/status/raw/%s"
start = 3720000
limit = 100000
#r =db_conn.execute("select count(1) from status")
#total = r.fetchone()[0]
total = 4423725
print '----total status:', total
sys.stdout.flush()
ef = open("error.log", "a")
#cf = open("cmd.txt", "w")
while (start <= int(total)):
f = open("./midfile.txt", "w")
print '-------start ', start
sys.stdout.flush()
cursor = db_conn.execute("select id from status order by id limit %s,%s", (start, limit))
rows = cursor.fetchall()
for row in rows:
text = mongo_conn.get(STATUS_REDIS_KEY % row[0])
raw = mongo_conn.get(RAW_STATUS_REDIS_KEY% row[0])
if text and raw:
text = json_encode(text) if not isinstance(text, basestring) else text
raw = json_encode(raw) if not isinstance(raw, basestring) else raw
db_conn.execute('''replace into raw_status (status_id, text, raw)
values(%s,%s,%s)''', (row[0], text, raw))
db_conn.commit()
start += limit
def delete_by_random_id(cls, random_id):
db_conn.execute(
"""delete from confirmation
where random_id=%s""",
random_id,
)
db_conn.commit()
def myset(status_id, text, raw):
cursor = None
text = json_encode(text) if not isinstance(text, basestring) else text
raw = json_encode(raw) if not isinstance(raw, basestring) else raw
db_conn.execute('''replace into raw_status (status_id, text, raw)
values(%s,%s,%s)''', (status_id, text, raw))
def update(self, title, content, fmt):
if title and title != self.title or fmt and fmt != self.fmt or content and content != self.content:
_fmt = fmt or self.fmt
_title = title or self.title
_content = content or self.content
db_conn.execute('''update note set title = %s, content = %s, fmt = %s where id = %s''',
(_title, _content, _fmt, self.id))
db_conn.commit()
self.flush_note()
if title != self.title:
from past.model.status import Status
Status._clear_cache(None, self.get_status_id(), None)
def remove_status(uid):
cursor = db_conn.execute("select id from status where user_id=%s", uid)
if cursor:
rows = cursor.fetchall()
for row in rows:
sid = row[0]
print "---- delete mongo text, sid=", sid
RawStatus.remove(sid)
print "---- delete from status, uid=", uid
db_conn.execute("delete from status where user_id=%s", uid)
db_conn.commit()
Status._clear_cache(uid, None)
def get_oldest_create_time(cls, cate, user_id):
if cate:
cursor = db_conn.execute('''select min(create_time) from status
where category=%s and user_id=%s''', (cate, user_id))
else:
cursor = db_conn.execute('''select min(create_time) from status
where user_id=%s''', user_id)
row = cursor.fetchone()
cursor and cursor.close()
if row:
return row[0]
else:
return None
def get(cls, user_id):
cursor = db_conn.execute('''select user_id, profile, time from user_profile
where user_id=%s''', user_id)
row = cursor.fetchone()
if row:
return cls(*row)
cursor and cursor.close()
def get_by_random_id(cls, random_id):
cursor = db_conn.execute('''select text, time from confirmation
where random_id=%s''', random_id)
row = cursor.fetchone()
cursor and cursor.close()
if row:
return cls(random_id, row[0], row[1])
def get(cls, status_id):
cursor = db_conn.execute('''select status_id, text, raw, time from raw_status
where status_id=%s''', status_id)
row = cursor.fetchone()
if row:
return cls(*row)
cursor and cursor.close()
def get(cls, key_):
cursor = db_conn.execute('''select `key`, value, time from kv
where `key`=%s''', key_)
row = cursor.fetchone()
if row:
return cls(*row)
cursor and cursor.close()
def add(cls, user_id, origin_id, create_time, site, category, title, text=None, raw=None):
status = None
cursor = None
try:
cursor = db_conn.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:
mongo_conn.set(cls.STATUS_REDIS_KEY % status_id, json_encode(text))
if raw is not None:
mongo_conn.set(cls.RAW_STATUS_REDIS_KEY % status_id, raw)
cls._clear_cache(user_id, None, cate=category)
status = cls.get(status_id)
except IntegrityError:
# log.warning("add status duplicated, ignore...")
db_conn.rollback()
finally:
cursor and cursor.close()
return status
def get_ids(cls, start=0, limit=20, order="id desc"):
sql = """select id from user
order by """ + order + """ limit %s, %s"""
cursor = db_conn.execute(sql, (start, limit))
rows = cursor.fetchall()
cursor and cursor.close()
return [x[0] for x in rows]
def get_ids_asc(cls, start=0, limit=20):
sql = """select id from user
order by id asc limit %s, %s"""
cursor = db_conn.execute(sql, (start, limit))
rows = cursor.fetchall()
cursor and cursor.close()
return [x[0] for x in rows]
def update_uid(self, uid):
assert isinstance(uid, basestring)
if self.id != self.uid:
return False, "already_set"
if uid == self.uid:
return True, "same_with_old"
if len(uid) > User.UID_MAX_LEN:
return False, u"太长了，不能超过%s" %User.UID_MAX_LEN
if len(uid) < User.UID_MIN_LEN:
return False, u"太短了，不能小于%s" %User.UID_MIN_LEN
uid_re = re.compile(User.UID_RE)
if not uid_re.match(uid):
return False, u"只能包括字母数字和.-_"
uid = uid.lower()
if uid in ["user", "pdf", "explore", "home", "visual", "settings", "admin", "past", "connect", "bind",
"i", "notes", "note", "status", "share", "timeline", "post", "login", "logout", "sync", "about",
"connect", "dev", "api", "thepast", "thepast.me", ]:
return False, u"被系统占用了:)"
try:
cursor = db_conn.execute("""update user set uid=%s where id=%s""",
(uid, self.id))
db_conn.commit()
User._clear_cache(self.id)
return True, u"设置成功"
except IntegrityError:
db_conn.rollback()
return False, u"被别人占用了:)"
finally:
cursor and cursor.close()
return False, "fail"
def get_ids_by_date(cls, user_id, start_date, end_date):
cursor = db_conn.execute('''select id from status
where user_id=%s and create_time>=%s and create_time<=%s
order by time desc''',
(user_id, start_date, end_date))
rows = cursor.fetchall()
cursor and cursor.close()
return [x[0] for x in rows]
def remove(self):
cursor = db_conn.execute(
"""delete from task_queue
where id=%s""",
self.id,
)
db_conn.commit()
cursor and cursor.close()
def get_user_by_alias(cls, type_, alias):
cursor = db_conn.execute(
"""select user_id from user_alias
where type=%s and alias=%s""",
(type_, alias),
)
row = cursor.fetchone()
cursor and cursor.close()
return row and cls.get(row[0])
def get_user_by_email(cls, email):
cursor = db_conn.execute(
"""select user_id from passwd
where email=%s""",
email,
)
row = cursor.fetchone()
cursor and cursor.close()
return row and cls.get(row[0])
def get_recent_updated_user_ids(cls, limit=16):
cursor = db_conn.execute(
"""select distinct user_id from status
order by create_time desc limit %s""",
limit,
)
rows = cursor.fetchall()
cursor and cursor.close()
return [row[0] for row in rows]
def get_email(self):
cursor = db_conn.execute(
"""select email from passwd
where user_id=%s""",
self.id,
)
row = cursor.fetchone()
cursor and cursor.close()
return row and row[0]
def get_count_by_user(cls, user_id):
cursor = db_conn.execute('''select count(1) from status
where user_id=%s''', user_id)
row = cursor.fetchone()
cursor and cursor.close()
if row:
return row[0]
else:
return 0
def remove(self):
cursor = db_conn.execute(
"""delete from sync_task
where id=%s""",
self.id,
)
db_conn.commit()
cursor and cursor.close()
return None
def get_count_by_cate(cls, cate, user_id):
cursor = db_conn.execute('''select count(1) from status
where category=%s and user_id=%s''', (cate, user_id))
row = cursor.fetchone()
cursor and cursor.close()
if row:
return row[0]
else:
return 0
def remove(cls, user_id):
cursor = None
try:
cursor = db_conn.execute('''delete from user_profile where user_id= %s''', user_id)
db_conn.commit()
cls.clear_cache(user_id)
except IntegrityError:
db_conn.rollback()
cursor and cursor.close()
def remove(cls, key_):
cursor = None
try:
cursor = db_conn.execute('''delete from kv where `key` = %s''', key_)
db_conn.commit()
cls.clear_cache(key_)
except IntegrityError:
db_conn.rollback()
cursor and cursor.close()
def remove(cls, status_id):
cursor = None
try:
cursor = db_conn.execute('''delete from raw_status where status_id = %s''', status_id )
db_conn.commit()
cls.clear_cache(status_id)
except IntegrityError:
db_conn.rollback()
cursor and cursor.close()
def delete_by_random_id(cls, random_id):
db_conn.execute(
'''delete from confirmation
where random_id=%s''', random_id)
db_conn.commit()
def is_user_id_exists(cls, user_id):
cursor = db_conn.execute(
'''select user_id from pdf_settings where user_id=%s''', user_id)
row = cursor.fetchone()
cursor and cursor.close()
return True if row else False
def remove_user_id(cls, user_id):
cursor = db_conn.execute(
'''delete from pdf_settings where user_id=%s''', user_id)
db_conn.commit()
cls._clear_cache(user_id)
def remove_user(uid, clear_status=True):
user = User.get(uid)
if not user:
print '---no user:%s' % uid
suicide_log.info("---- delete from user, uid=%s" % uid)
db_conn.execute("delete from user where id=%s", uid)
db_conn.commit()
User._clear_cache(uid)
if clear_status:
cursor = db_conn.execute("select id from status where user_id=%s", uid)
if cursor:
rows = cursor.fetchall()
for row in rows:
sid = row[0]
suicide_log.info("---- delete status text, sid=%s" % sid)
RawStatus.remove(sid)
suicide_log.info("---- delete from status, uid=" % uid)
db_conn.execute("delete from status where user_id=%s", uid)
db_conn.commit()
Status._clear_cache(uid, None)
suicide_log.info("---- delete from passwd, uid=%s" % uid)
db_conn.execute("delete from passwd where user_id=%s", uid)
suicide_log.info("---- delete from sync_task, uid=%s" % uid)
db_conn.execute("delete from sync_task where user_id=%s", uid)
suicide_log.info("---- delete from user_alias, uid=%s" % uid)
db_conn.execute("delete from user_alias where user_id=%s", uid)
db_conn.commit()
def remove(self):
cursor = db_conn.execute(
"""delete from task_queue
where id=%s""", self.id)
db_conn.commit()
cursor and cursor.close()
import sys
sys.path.append('../')
import os
from past.store import db_conn
user_ids = []
cursor = db_conn.execute('''select user_id from pdf_settings''')
if cursor:
rows = cursor.fetchall()
user_ids = [row[0] for row in rows]
cursor and cursor.close()
print user_ids, len(user_ids)
def file_visitor(args, dir_, files):
#print "-------", dir_, files
pendding = set()
if not isinstance(files, list):
return
for f in files:
if not (f.startswith("thepast.me") and f.endswith(".pdf.tar.gz")):
continue
user_id = int(f.split("_")[1])
if user_id not in user_ids:
pendding.add(user_id)
print pendding, len(pendding)
for user_id in pendding:
print '---deleting pdf of', user_id
os.popen("rm ../var/down/pdf/thepast.me_%s_2*.pdf.tar.gz" % user_id)
#-*- coding:utf-8 -*-
import sys
sys.path.append('../')
from datetime import timedelta
from past.store import db_conn
from past.model.kv import RawStatus
with open("ids.txt") as f:
for id_ in f:
id_ = id_.rstrip("\n")
print id_
cursor = db_conn.execute("delete from status where id=%s", id_)
db_conn.commit()
RawStatus.remove(id_)
#cursor = db_conn.execute("select * from status where id=%s", id_)
#print cursor.fetchone()
def get_ids(cls):
cursor = db_conn.execute("""select id from sync_task""")
r = [row[0] for row in cursor.fetchall()]
cursor and cursor.close()
return r
def gets_by_user(cls, user):
cursor = db_conn.execute(
"""select id from sync_task where user_id = %s""", user.id)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls.get(row[0]) for row in rows]
def get_all_ids(cls):
cursor = db_conn.execute("""select id from task_queue order by time""")
r = [row[0] for row in cursor.fetchall()]
cursor and cursor.close()
return r
def get_all_user_ids(cls):
cursor = db_conn.execute('''select user_id from pdf_settings''')
rows = cursor.fetchall()
cursor and cursor.close()
return [str(row[0]) for row in rows]
