def deleteSource():
sourceTableName = request.json['username'] + '_sources'
wordTableName = request.json['username'] + '_words'
sourceId = request.json['sourceId']
deleteSourceMode = request.json['deleteSourceMode']
if deleteSourceMode != '0' and deleteSourceMode != '1':
print('error deleteSourceMode = ' + deleteSourceMode)
return jsonify(
{'status': 'error deleteSourceMode = ' + deleteSourceMode}), 199
row = query_db('select * from ' + sourceTableName + ' where id = ?',
[sourceId],
one=True)
if row is not None:
commit_db('delete from ' + sourceTableName + ' where id = ?',
[sourceId])
else:
return jsonify({'status': 'cant find source id:' + str(sourceId)}), 199
rowCount = query_db('select count(*) from ' + wordTableName +
' where sourceId = ?', [sourceId],
one=True)
affectedWordNum = str(rowCount['count(*)'])
r = {'status': 'success'}
if deleteSourceMode == '0':
commit_db('delete from ' + wordTableName + ' where sourceId = ?',
[sourceId])
r['detail'] = affectedWordNum + ' words deleted.'
return jsonify(r), 200
elif deleteSourceMode == '1':
commit_db(
'update ' + wordTableName +
' set sourceId = -1 where sourceId = ?', [sourceId])
r['detail'] = affectedWordNum + ' words updated.'
return jsonify(r), 200
def getSourceNameTable(sourceTableName):
sourceNameRows = query_db('select * from '+sourceTableName)
sourceNameTable = {-1:''}
'''this is for the condition that no sourceName in some words'''
if sourceNameRows is not None:
for snRow in sourceNameRows:
sourceNameTable[snRow['id']] = snRow['source']
return sourceNameTable
def listSource():
rows = query_db('select * from ' + request.json['username'] + '_sources')
sources = {}
if rows is not None:
for row in rows:
sources[row['id']] = row['source']
r = {'sources': sources}
r['status'] = 'success'
return jsonify(r), 200
def searchWordOld():
wordTableName = request.json['username']+'_words'
sourceTableName = request.json['username']+'_sources'
if not request.json['word']:
rows = query_db('select * from '+ wordTableName)
else:
queryWord = request.json['word']
realCondition = '%'+queryWord+'%'
rows = query_db('select * from '+ wordTableName +
' where word like ? or reading like ?',
[realCondition,realCondition])
'''hard coding here'''
sourceNameTable = getSourceNameTable(sourceTableName)
wordList = []
if rows is not None:
for row in rows:
aWord = generateJsonWord(row,sourceNameTable)
wordList.append(aWord)
return jsonify({'status':'success','words':wordList}),200
def deleteWord():
wordTableName = request.json['username']+'_words'
row = query_db('select * from ' + wordTableName +' where id = ?'
,[request.json['wordId']],one=True)
if row is not None:
commit_db('delete from '+wordTableName+' where id = ?',
[request.json['wordId']])
return jsonify({'status':'success'}),200
else:
return jsonify({'status':'cant find word:'+
str(request.json['wordId'])}),199
def func_checkTimeStamp():
serialNum = request.json['serialNum']
identifier = request.json['identifier']
stamp = query_db('select * from cookies where id = ? and rString = ?',
[serialNum,identifier],one= True)
if stamp is None:
return jsonify({"status":"without cookie"}),299
elif int(stamp['expiredTime']) < int(time.time()):
commit_db('delete from cookies where id = ? and rString = ?',
[serialNum,identifier])
return jsonify({"status":"time expired"}),398
else:
return func()
def searchWord():
wordTableName = request.json['username']+'_words'
sourceTableName = request.json['username']+'_sources'
conditionList = request.json['conditionList']
r = checkConditionListValid(conditionList)
if r != 'valid':
response = {'status':"error","detail":r}
return jsonify(response),199
sql = assembleConditionsToSQL(wordTableName,conditionList)
rows = query_db(sql)
sourceNameTable = getSourceNameTable(sourceTableName)
wordList = []
if rows is not None:
for row in rows:
aWord = generateJsonWord(row,sourceNameTable)
wordList.append(aWord)
return jsonify({'status':'success','words':wordList}),200
def login():
user = query_db('SELECT * FROM account WHERE USERNAME = ?',
[request.json['username']],
one=True)
if user is None:
r = {'status': 'username error'}
elif user['password'] != request.json['password']:
r = {'status': 'password error'}
else:
r = {'status': 'success'}
rString = createRamdomString(10)
expiredTime = int(time.time()) + 3600
lastId = commit_db_and_get_lastId(
'''insert into cookies
(rString, expiredTime, username)values (?,?,?)''',
[rString, expiredTime, request.json['username']])
r['serialNum'] = lastId
r['identifier'] = rString
return jsonify(r), 200
def register():
if not stringValid(request.json['username']):
return jsonify({'status': 'username invalid'}), 202
if not stringValid(request.json['password']):
return jsonify({'status': 'password invalid'}), 202
user = query_db('select * FROM account where USERNAME = ?',
[request.json['username']],
one=True)
if user is not None:
r = {'status': 'username existed'}
else:
r = {'status': 'success'}
commit_db('insert into account values(?,?)',
[request.json['username'], request.json['password']])
try:
sourcesTableName = request.json['username'] + '_sources'
wordsTableName = request.json['username'] + '_words'
query1 = '''create table '''
query2 = '''(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
source TEXT NOT NULL
);'''
commit_db(query1 + sourcesTableName + query2)
commit_db('''create table ''' + wordsTableName + '''(
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
word TEXT NOT NULL,
reading TEXT,
description TEXT,
sourceId INTEGER,
sentence TEXT,
page TEXT,
pick INTEGER DEFAULT 0,
correct INTEGER DEFAULT 0
);''')
except sqlite3.Error as e:
print('er: %s' % e.args[0])
return jsonify(r), 201