def UpdateRoute():
""" 更新 """
try:
req_id = int(request.form['id'])
req_title = request.form['title']
req_content = request.form['content']
req_group_id = int(request.form['group_id'])
except:
raise ParamError(ParamType.FORM)
if not (Config.FMT_NOTE_TITLE_MIN <= len(req_title) <=
Config.FMT_NOTE_TITLE_MAX):
return Result().error(
ResultCode.BAD_REQUEST).setMessage('Format Error').json_ret()
req_note = Note(nid=req_id,
title=req_title,
content=req_content,
group=req_group_id)
status, new_note = NoteDao().updateNote(uid=g.user, note=req_note)
if status == DbStatusType.NOT_FOUND:
return Result.error(
ResultCode.NOT_FOUND).setMessage("Note Not Found").json_ret()
elif status == DbStatusType.FAILED or not new_note:
return Result.error(ResultCode.DATABASE_FAILED).setMessage(
"Note Update Failed").json_ret()
else: # Success
return Result.ok().setData(new_note.to_json()).json_ret()
def queryAllNotesByGroupId(self, uid: int, gid: int) -> List[Note]:
"""
根据分组查询笔记
:param uid: 用户id
:param gid: 所有分组为 -1
"""
cursor = self.db.cursor()
if gid == -1:
cursor.execute(f'''
SELECT {col_user}, {col_id}, {col_title}, {col_content}, {col_group_id}, {col_create_time}, {col_update_time}
FROM {tbl_name}
WHERE {col_user} = {uid}
''')
else:
cursor.execute(f'''
SELECT {col_user}, {col_id}, {col_title}, {col_content}, {col_group_id}, {col_create_time}, {col_update_time}
FROM {tbl_name}
WHERE {col_user} = {uid} AND {col_group_id} = {gid}
''')
returns = []
results = cursor.fetchall()
for result in results:
# noinspection PyBroadException
try:
group = GroupDao().queryGroupByIdOrName(uid, gid_name=int(result[4]))
if group is None:
group = GroupDao().queryDefaultGroup(uid)
returns.append(Note(nid=result[1], title=result[2], content=result[3], group=group, create_time=result[5], update_time=result[6]))
except:
pass
cursor.close()
return returns
def InsertRoute():
""" 插入 """
try:
req_title = request.form['title']
req_content = request.form['content']
req_group_id = int(request.form['group_id'])
req_ct = request.form['create_time']
req_ut = request.form['update_time']
except:
raise ParamError(ParamType.FORM)
if not (Config.FMT_NOTE_TITLE_MIN <= len(req_title) <=
Config.FMT_NOTE_TITLE_MAX):
return Result().error(
ResultCode.BAD_REQUEST).setMessage('Format Error').json_ret()
req_note = Note(nid=-1,
title=req_title,
content=req_content,
group=req_group_id,
create_time=req_ct,
update_time=req_ut)
status, new_note = NoteDao().insertNote(uid=g.user, note=req_note)
if status == DbStatusType.FOUNDED:
return Result.error(
ResultCode.HAS_EXISTED).setMessage("Note Existed").json_ret()
elif status == DbStatusType.FAILED or not new_note:
return Result.error(ResultCode.DATABASE_FAILED).setMessage(
"Note Insert Failed").json_ret()
else: # Success
return Result.ok().setData(new_note.to_json()).json_ret()
def updateNote(self, uid: int, note: Note) -> (DbStatusType, Note):
"""
更新笔记 (title, content, group_id, ut) SUCCESS | NOT_FOUND | FAILED
"""
sameIdNote = self.queryNoteById(uid, note.id)
if not sameIdNote: # 不存在
return DbStatusType.NOT_FOUND, None
if not GroupDao().queryGroupByIdOrName(uid=uid, gid_name=note.group.id): # 分组不存在
note.group = GroupDao().queryDefaultGroup(uid=uid)
# 沒更新
if note.title == sameIdNote.title and note.content == sameIdNote.content and note.group.id == sameIdNote.group.id:
return DbStatusType.SUCCESS, sameIdNote
cursor = self.db.cursor()
# noinspection PyBroadException
try:
cursor.execute(f'''
UPDATE {tbl_name}
SET {col_title} = '{note.title}', {col_content} = '{note.content}',
{col_group_id} = {note.group.id}, {col_update_time} = '{note.update_time}'
WHERE {col_user} = {uid} AND {col_id} = {note.id}
''')
if cursor.rowcount == 0:
self.db.rollback()
return DbStatusType.FAILED, None
return DbStatusType.SUCCESS, self.queryNoteById(uid, note.id)
except:
self.db.rollback()
return DbStatusType.FAILED, None
finally:
self.db.commit()
cursor.close()
def insertNote(self, uid: int, note: Note) -> (DbStatusType, Note):
"""
插入新笔记 (title, content, group_id, ct, ut) SUCCESS | FOUNDED | FAILED
"""
if self.queryNoteById(uid, note.id): # 已存在
return DbStatusType.FOUNDED, None
if not GroupDao().queryGroupByIdOrName(uid=uid, gid_name=note.group.id): # 分组不存在
note.group = GroupDao().queryDefaultGroup(uid=uid)
cursor = self.db.cursor()
# noinspection PyBroadException
try:
cursor.execute(f'''
INSERT INTO {tbl_name} (
{col_user}, {col_title}, {col_content}, {col_group_id}, {col_create_time}, {col_update_time}
)
VALUES ({uid}, '{note.title}', '{note.content}', {note.group.id}, '{note.create_time}', '{note.update_time}')
''')
if cursor.rowcount == 0:
self.db.rollback()
return DbStatusType.FAILED, None
return DbStatusType.SUCCESS, self.queryNoteById(uid, nid=cursor.lastrowid)
except:
self.db.rollback()
return DbStatusType.FAILED, None
finally:
self.db.commit()
cursor.close()
def queryNoteById(self, uid: int, nid: int) -> Optional[Note]:
"""
根据 nid 查询笔记
:return: nullable
"""
cursor = self.db.cursor()
cursor.execute(f'''
SELECT {col_user}, {col_id}, {col_title}, {col_content}, {col_group_id}, {col_create_time}, {col_update_time}
FROM {tbl_name}
WHERE {col_user} = {uid} AND {col_id} = {nid}
''')
result = cursor.fetchone()
# noinspection PyBroadException
try:
group = GroupDao().queryGroupByIdOrName(uid, gid_name=int(result[4]))
if group is None:
group = GroupDao().queryDefaultGroup(uid)
return Note(nid=result[1], title=result[2], content=result[3], group=group, create_time=result[5], update_time=result[6])
except:
return None
finally:
cursor.close()
def GetByGidRoute(gid: int):
""" gid 查询笔记 """
notes = NoteDao().queryAllNotesByGroupId(uid=g.user, gid=gid)
return Result.ok().setData(Note.to_jsons(notes)).json_ret()
def GetAllRoute():
""" 所有笔记 """
notes = NoteDao().queryAllNotes(uid=g.user)
return Result.ok().setData(Note.to_jsons(notes)).json_ret()