def clear_user_table():
try:
with base_db.get_conn().cursor() as cursor:
sql = "drop table `User`"
cursor.execute(sql)
finally:
base_db.get_conn().close()
def clear_user_table():
try:
with base_db.get_conn().cursor() as cursor:
sql = "drop table `User`"
cursor.execute(sql)
finally:
base_db.get_conn().close()
def create_diary(user_id, uuid, title, content, time_created=None):
"""
Raise: errors.DbCreateError
"""
if user_id is None or uuid is None or title is None or content is None:
raise errors.ArgumentShouldNotBeNull()
_user = db_user.get_user(user_id)
if _user is None:
raise errors.InvalidUserIdDuringCreatingDiary()
conn = base_db.get_conn(pymysql.cursors.DictCursor)
if time_created is None:
time_created = int(time.time())
new_diary_id = -1
try:
with conn.cursor() as cursor:
sql = "insert into `Diary` (`user_id`, `uuid`, `title`, `content`, `time_created`, `time_modified`) " \
"values (%s, %s, %s, %s, %s, %s)"
cursor.execute(sql,
(str(user_id), str(uuid), str(title), str(content),
str(time_created), str(time_created)))
new_diary_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbCreateError()
finally:
conn.close()
return new_diary_id
def create_user(email, password):
"""If success, return real user_id, otherwise return -1.
"""
new_user_id = -1
if email is None or password is None:
return new_user_id
if not regxutils.validate_email(email):
raise errors.EmailFormatWrong()
if check_email_existance(email):
raise errors.UserEmailAlreadyUsed()
email_hash = abs(hash(email))
password = safetyutils.get_hash_password(password)
conn = base_db.get_conn(pymysql.cursors.DictCursor)
time_created = int(time.time())
try:
with conn.cursor() as cursor:
sql = "insert into `User` (`email`, `email_hash`, `password`, `time_created`, `time_modified`) " \
"values (%s, %s, %s, %s, %s)"
cursor.execute(sql, (str(email), str(email_hash), str(password),
str(time_created), '0'))
new_user_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.error(e)
raise errors.UserCreateFailure()
finally:
conn.close()
if new_user_id == -1:
raise errors.UserCreateFailure()
return new_user_id
def create_user(email, password):
"""If success, return real user_id, otherwise return -1.
"""
new_user_id = -1
if email is None or password is None:
return new_user_id
if not regxutils.validate_email(email):
raise errors.EmailFormatWrong()
if check_email_existance(email):
raise errors.UserEmailAlreadyUsed()
email_hash = abs(hash(email))
password = safetyutils.get_hash_password(password)
conn = base_db.get_conn(pymysql.cursors.DictCursor)
time_created = int(time.time())
try:
with conn.cursor() as cursor:
sql = "insert into `User` (`email`, `email_hash`, `password`, `time_created`, `time_modified`) " \
"values (%s, %s, %s, %s, %s)"
cursor.execute(sql, (str(email), str(email_hash), str(password), str(time_created), '0'))
new_user_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.UserCreateFailure()
finally:
conn.close()
if new_user_id == -1:
raise errors.UserCreateFailure()
return new_user_id
def create_diary(user_id, uuid, title, content, time_created=None):
"""
Raise: errors.DbCreateError
"""
if user_id is None or uuid is None or title is None or content is None:
raise errors.ArgumentShouldNotBeNull()
_user = db_user.get_user(user_id)
if _user is None:
raise errors.InvalidUserIdDuringCreatingDiary()
conn = base_db.get_conn(pymysql.cursors.DictCursor)
if time_created is None:
time_created = int(time.time())
new_diary_id = -1
try:
with conn.cursor() as cursor:
sql = "insert into `Diary` (`user_id`, `uuid`, `title`, `content`, `time_created`, `time_modified`) " \
"values (%s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (str(user_id), str(uuid), str(title), str(content), str(time_created), str(time_created)))
new_diary_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbCreateError()
finally:
conn.close()
return new_diary_id
def check_email_existance(email):
email_hash = abs(hash(email))
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from User where email_hash = %s"
cursor.execute(sql, str(email_hash))
user = cursor.fetchall()
return len(user) > 0
finally:
conn.close()
def check_email_existance(email):
email_hash = abs(hash(email))
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from User where email_hash = %s"
cursor.execute(sql, str(email_hash))
user = cursor.fetchall()
return len(user) > 0
finally:
conn.close()
def reinit_table():
conn = base_db.get_conn()
print 'reinit start droping tables'
try:
with conn.cursor() as cursor:
sql = ""
for table_name in tables_name:
sql += 'drop table if exists %s;' % table_name
cursor.execute(sql)
finally:
conn.close()
print 'reinit start init all schema files'
base_db.init_all_schema()
def get_user(user_id, with_password=False):
user = None
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from User where id = %s"
cursor.execute(sql, str(user_id))
user = cursor.fetchall()[0]
if not with_password:
user.pop('password')
except Exception as e:
logger.exception(e)
finally:
conn.close()
return user
def get_user(user_id, with_password=False):
user = None
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from User where id = %s"
cursor.execute(sql, str(user_id))
user = cursor.fetchall()[0]
if not with_password:
user.pop('password')
except Exception as e:
logger.error(e)
finally:
conn.close()
return user
def delete_user(user_id):
"""Delete user."""
current_time = int(time.time())
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "update `User` set time_removed = %s, time_modified = %s where `id` = %s"
cursor.execute(sql, (str(current_time), str(current_time), str(user_id)))
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.UserDeleteFailure()
finally:
conn.close()
def delete_user(user_id):
"""Delete user."""
current_time = int(time.time())
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "update `User` set time_removed = %s, time_modified = %s where `id` = %s"
cursor.execute(
sql, (str(current_time), str(current_time), str(user_id)))
conn.commit()
except Exception as e:
logger.error(e)
raise errors.UserDeleteFailure()
finally:
conn.close()
def delete_diary(user_id, uuid, time_removed=None):
"""
Raise: errors.DbDeleteError
"""
conn = base_db.get_conn(pymysql.cursors.DictCursor)
if time_removed is None:
time_removed = int(time.time())
try:
with conn.cursor() as cursor:
sql = "update `Diary` set time_removed = %s, time_modified = %s where `uuid` = %s and `user_id` = %s"
result = cursor.execute(sql, (str(time_removed), str(time_removed), str(uuid), str(user_id)))
print result
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbDeleteError()
finally:
conn.close()
def delete_diary(user_id, uuid, time_removed=None):
"""
Raise: errors.DbDeleteError
"""
conn = base_db.get_conn(pymysql.cursors.DictCursor)
if time_removed is None:
time_removed = int(time.time())
try:
with conn.cursor() as cursor:
sql = "update `Diary` set time_removed = %s, time_modified = %s where `uuid` = %s and `user_id` = %s"
result = cursor.execute(sql, (str(time_removed), str(time_removed), str(uuid), str(user_id)))
print result
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbDeleteError()
finally:
conn.close()
def get_diary_by_id(diary_id):
"""
Raise: errors.DbReadError
"""
diary = None
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from Diary where id = %s and time_removed = %s"
cursor.execute(sql, (str(diary_id), str(0)))
result = cursor.fetchall()
diary = result[0] if len(result) > 0 else None
except Exception as e:
logger.exception(e)
raise errors.DbReadError()
finally:
conn.close()
return diary
def get_diary_by_id(diary_id):
"""
Raise: errors.DbReadError
"""
diary = None
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from Diary where id = %s and time_removed = %s"
cursor.execute(sql, (str(diary_id), str(0)))
result = cursor.fetchall()
diary = result[0] if len(result) > 0 else None
except Exception as e:
logger.exception(e)
raise errors.DbReadError()
finally:
conn.close()
return diary
def add_event_log(user_id, log_item):
"""
Record the event log from client
"""
conn = base_db.get_conn(pymysql.cursors.DictCursor)
new_event_id = -1
try:
with conn.cursor() as cursor:
sql = 'insert into `EventLog` (`user_id`,`event_name`,`page_source`,`time_created`) values (%s,%s,%s,%s)'
cursor.execute(sql, (str(user_id), str(log_item['event_name']), str(log_item['page_source']), str(log_item['time_created'])))
new_event_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbCreateError()
finally:
conn.close()
return new_event_id
def get_diary_list_since_last_sync(user_id, last_sync_time):
"""
Raise: errors.DbReadError
"""
print 'get_diary_list_since_last_sync for user_id: ', user_id, ', last_sync_time: ', last_sync_time
diary_list = []
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from Diary where user_id = %s and time_modified > %s"
cursor.execute(sql, (str(user_id), str(last_sync_time)))
diary_list = cursor.fetchall()
print diary_list
except Exception as e:
logger.exception(e)
raise errors.DbReadError()
finally:
conn.close()
return diary_list
def get_diary_list_since_last_sync(user_id, last_sync_time):
"""
Raise: errors.DbReadError
"""
print 'get_diary_list_since_last_sync for user_id: ', user_id, ', last_sync_time: ', last_sync_time
diary_list = []
conn = base_db.get_conn(pymysql.cursors.DictCursor)
try:
with conn.cursor() as cursor:
sql = "select * from Diary where user_id = %s and time_modified > %s"
cursor.execute(sql, (str(user_id), str(last_sync_time)))
diary_list = cursor.fetchall()
print diary_list
except Exception as e:
logger.exception(e)
raise errors.DbReadError()
finally:
conn.close()
return diary_list
def update_diary(user_id, uuid, title, content, time_modified=None):
"""
Raise: errors.DbUpdateError
"""
if time_modified is None:
time_modified = int(time.time())
conn = base_db.get_conn(pymysql.cursors.DictCursor)
print 'prepare update_diary user_id: ', user_id, ', uuid: ', uuid, 'title: ', title, ', content: ', content
try:
with conn.cursor() as cursor:
sql = "update Diary set title = %s, content = %s, time_modified = %s where uuid = %s and user_id = %s"
print 'update!'
result = cursor.execute(sql, (str(title), str(content), str(time_modified), str(uuid), str(user_id)))
print 'update_diary result:', result
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbUpdateError()
finally:
conn.close()
return
def add_event_log(user_id, log_item):
"""
Record the event log from client
"""
conn = base_db.get_conn(pymysql.cursors.DictCursor)
new_event_id = -1
try:
with conn.cursor() as cursor:
sql = 'insert into `EventLog` (`user_id`,`event_name`,`page_source`,`time_created`) values (%s,%s,%s,%s)'
cursor.execute(
sql,
(str(user_id), str(log_item['event_name']),
str(log_item['page_source']), str(log_item['time_created'])))
new_event_id = cursor.lastrowid
conn.commit()
except Exception as e:
logger.error(e)
raise errors.DbCreateError()
finally:
conn.close()
return new_event_id
def update_diary(user_id, uuid, title, content, time_modified=None):
"""
Raise: errors.DbUpdateError
"""
if time_modified is None:
time_modified = int(time.time())
conn = base_db.get_conn(pymysql.cursors.DictCursor)
print 'prepare update_diary user_id: ', user_id, ', uuid: ', uuid, 'title: ', title, ', content: ', content
try:
with conn.cursor() as cursor:
sql = "update Diary set title = %s, content = %s, time_modified = %s where uuid = %s and user_id = %s"
print 'update!'
result = cursor.execute(sql, (str(title), str(content), str(time_modified), str(uuid), str(user_id)))
print 'update_diary result:', result
conn.commit()
except Exception as e:
logger.exception(e)
raise errors.DbUpdateError()
finally:
conn.close()
return
def login(email, password):
if email is None or password is None:
return False
email_hash = abs(hash(email))
conn = base_db.get_conn(pymysql.cursors.DictCursor)
_user = None
try:
with conn.cursor() as cursor:
sql = "select * from `User` where `email_hash` = %s "
cursor.execute(sql, (str(email_hash)))
_user = cursor.fetchone()
except Exception as e:
logger.exception(e)
finally:
conn.close()
if not _user:
raise errors.UserNotFound()
elif not safetyutils.verify_hash_password(_user['password'], password):
raise errors.WrongPassword()
else:
_user.pop('password')
return _user
def login(email, password):
if email is None or password is None:
return False
email_hash = abs(hash(email))
conn = base_db.get_conn(pymysql.cursors.DictCursor)
_user = None
try:
with conn.cursor() as cursor:
sql = "select * from `User` where `email_hash` = %s "
cursor.execute(sql, (str(email_hash)))
_user = cursor.fetchone()
except Exception as e:
logger.error(e)
finally:
conn.close()
if not _user:
raise errors.UserNotFound()
elif not safetyutils.verify_hash_password(_user['password'], password):
raise errors.WrongPassword()
else:
_user.pop('password')
return _user
