def add_user_list(token, list_id):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"INSERT INTO user_lists (list_id, user_id)"\
f"VALUES('{list_id}', '{user['id']}')"
cursor.execute(query)
conn.commit()
def delete_user_list(token, list_id):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"DELETE FROM user_lists WHERE "\
f"list_id='{list_id}' AND user_id='{user['id']}'"
cursor.execute(query)
conn.commit()
def add_card(token, type, translation_id, training_id, result):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"INSERT INTO cards (type, translation_id, user_id, training_id, result)"\
f"VALUES ('{type}', '{translation_id}', '{user['id']}', '{training_id}', '{result}')"
cursor.execute(query)
conn.commit()
def get_user_lists(token):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"SELECT * FROM list_info WHERE user_id='{user['id']}'"
cursor.execute(query)
lists = cursor.fetchall()
return lists
def change_balance(token, cost):
assert type(cost) == type(0.0) or type(cost) == type(0)
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"UPDATE users SET balance=balance - {cost} WHERE id='{user['id']}'"
cursor.execute(query)
conn.commit()
return None
def check_token():
token = request.form.get("token")
relevant = True
try:
user = get_user_by_token(token)
session = get_session(user['id'])
except:
relevant = False
return {"status": "OK", "relevant": relevant}
def get_learn_words(token, list_id):
user = get_user_by_token(token)
user_id = user['id']
conn = connect()
with conn.cursor() as cursor:
query = "SELECT translations.id, w1.word as `source`, w2.word as `target` FROM translations, words as w1, words as w2 WHERE w1.id=translations.source_id AND w2.id=translations.target_id AND list_id='{list_id}' AND translations.id NOT IN (SELECT translation_id FROM cards, trainings WHERE trainings.user_id='{user_id}' AND trainings.id=cards.training_id)"\
.format(user_id=user_id, list_id=list_id)
cursor.execute(query)
words = list(cursor.fetchall())
return words
def check_list_presence(token, list_id):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = "SELECT user_id FROM user_lists WHERE list_id='{list_id}' AND user_id='{user_id}'"\
.format(list_id=list_id, user_id=user['id'])
cursor.execute(query)
data = cursor.fetchone()
if data is None:
return False
return True
def remove_translation(token, id):
user = get_user_by_token(token)
conn = connect()
with conn.cursor() as cursor:
query = f"SELECT lists.id, lists.author_id FROM lists, translations WHERE translations.list_id=lists.id AND translations.id='{id}' LIMIT 1"
cursor.execute(query)
lst = cursor.fetchone()
author_id = lst['author_id']
if author_id != user['id']:
raise Exception("Premission denied")
query = f"UPDATE translations SET list_id=NULL WHERE id='{id}'"
cursor.execute(query)
conn.commit()
def delete_list(token, list_id):
user = get_user_by_token(token)
lst = get_list(list_id)
if lst['author_id'] != user['id']:
delete_user_list(token, list_id)
return
conn = connect()
with conn.cursor() as cursor:
query = f"DELETE FROM user_lists WHERE list_id='{lst['id']}'"
cursor.execute(query)
query = f"DELETE FROM lists WHERE id='{lst['id']}'"
cursor.execute(query)
conn.commit()
def add_list(token, name, source_lang_code, target_lang_code):
author = get_user_by_token(token)
source_id = get_lang_by_code(source_lang_code)['id']
target_id = get_lang_by_code(target_lang_code)['id']
list_id = str(uuid())
conn = connect()
with conn.cursor() as cursor:
query = f"INSERT INTO lists (id, name, author_id, source_lang_id, target_lang_id)"\
f"VALUES ('{list_id}', '{name}', '{author['id']}', '{source_id}', '{target_id}')"
cursor.execute(query)
conn.commit()
add_user_list(token, list_id)
return list_id
def get_revise_words(token, list_id):
user = get_user_by_token(token)
user_id = user['id']
conn = connect()
with conn.cursor() as cursor:
query = "SELECT translation_id as `id`, GROUP_CONCAT(DISTINCT w1.word) as `source`, GROUP_CONCAT(DISTINCT w2.word) as `target` "\
"FROM cards, words as w1, words as w2, translations "\
"WHERE translations.id=translation_id AND translations.source_id=w1.id AND "\
"translations.target_id=w2.id AND type='input' AND translations.list_id IS NOT NULL AND "\
f"training_id IN (SELECT id FROM trainings WHERE user_id='{user_id}' AND list_id='{list_id}') "\
"GROUP BY translation_id ORDER BY MIN(date) ASC LIMIT 5"
cursor.execute(query)
words = list(cursor.fetchall())
# print(words)
return words
def get_card(token, training_id):
user = get_user_by_token(token)
user_id = user['id']
assert get_training(training_id)['user_id'] == user_id
last_card = get_cards(training_id)[0]
with open('trainings/{training_id}.pkl'.format(training_id=training_id),
'rb') as f:
cards = pkl.load(f)
t = None
for card in cards:
if last_card['id'] == card['id']:
card['shown'] = True
t = card
with open('trainings/{training_id}.pkl'.format(training_id=training_id),
'wb') as f:
pkl.dump(cards, f)
return t
def add_translation(token, source_word, target_word, list_id):
user = get_user_by_token(token)
lst = get_list(list_id)
source_lang_id = lst["source_lang_id"]
target_lang_id = lst["target_lang_id"]
if lst["author_id"] != user['id']:
raise PermissionError("Permission denied. Not the author")
add_word(token, target_word, target_lang_id)
add_word(token, source_word, source_lang_id)
target_word = get_word(target_word, target_lang_id)
source_word = get_word(source_word, source_lang_id)
conn = connect()
with conn.cursor() as cursor:
query = f"INSERT INTO translations (list_id, source_id, target_id) VALUES ('{list_id}', '{source_word['id']}', '{target_word['id']}')"
cursor.execute(query)
conn.commit()
def edit_translation(token, id, source_word, target_word, list_id):
user = get_user_by_token(token)
lst = get_list(list_id)
source_lang_id = lst['source_lang_id']
target_lang_id = lst['target_lang_id']
if lst["author_id"] != user['id']:
raise PermissionError("Permission denied. Not the author")
translation = get_translation(id)
add_word(token, source_word, source_lang_id)
add_word(token, target_word, target_lang_id)
source_word = get_word(source_word, source_lang_id)
target_word = get_word(target_word, target_lang_id)
conn = connect()
with conn.cursor() as cursor:
query = f"UPDATE translations SET target_id='{target_word['id']}', source_id='{source_word['id']}' "\
f"WHERE translations.id='{id}'"
cursor.execute(query)
conn.commit()
def answer_card(token, training_id, card_id, answer):
training = get_training(training_id)
user = get_user_by_token(token)
user_id = user['id']
assert training['user_id'] == user_id
conn = connect()
with conn.cursor() as cursor:
query = "SELECT words.word, translations.id as `translation_id` FROM cards, translations, words WHERE cards.translation_id=translations.id AND translations.target_id=words.id AND cards.id='{card_id}'"\
.format(card_id=card_id)
cursor.execute(query)
card = cursor.fetchone()
if card is None:
raise Exception("No such card")
translation_id = card['translation_id']
result = int(card['word'].lower() == answer)
query = "UPDATE cards SET date=NOW(), result='{result}' WHERE cards.id='{card_id}'".format(
result=result, card_id=card_id)
cursor.execute(query)
if result == False:
with open(
'trainings/{training_id}.pkl'.format(training_id=training_id),
'rb') as f:
cards = pkl.load(f)
index = None
for i, card in enumerate(cards):
if card['id'] == card_id:
index = i + 1
break
new_cards = get_word_card(training_id, translation_id)
cards.insert(index, new_cards[1])
cards.insert(index, new_cards[0])
with open(
'trainings/{training_id}.pkl'.format(training_id=training_id),
'wb') as f:
pkl.dump(cards, f)
conn.commit()
return result
def create_text_list(token, text_id):
user = get_user_by_token(token)
text = get_text(text_id)
native_lang = user['native_language_code']
list_lang = text['source_lang_code']
list_id = add_list(token, f"Words from \"{text['name']}\"", list_lang,
native_lang)
conn = connect()
with open(f'texts/{text_id}.json', 'r') as f:
text = json.loads(f.read())
words = filter(lambda x: x['type'] == 'word', text)
words = list(map(lambda x: x['lemma'].lower(), words))
conn = connect()
with conn.cursor() as cursor:
query = "SELECT * FROM words "\
f"WHERE lang_id='{get_lang_by_code(list_lang)['id']}' AND "\
f"word IN {str(tuple(words))} AND "\
f"word NOT IN (SELECT source_word FROM learning_words WHERE source_word IN {str(tuple(words))})"
cursor.execute(query)
words = cursor.fetchall()
freqs = {word['word']: 0 for word in words}
for word in words:
freqs[word['word']] += 1
words = sorted(words, key=lambda x: freqs[x['word']])[::-1]
for word in words:
word = get_word(word['word'], get_lang_by_code(list_lang)['id'])
query = f"SELECT * FROM translations_info WHERE word='{word['word']}' AND word_lang_code='{list_lang}' ORDER BY rate LIMIT 1"
cursor.execute(query)
translation = cursor.fetchone()
if translation:
query = "INSERT INTO translations (`source_id`, `list_id`, `target_id`) "\
f"VALUES ('{word['id']}', '{list_id}', '{translation['translation_id']}')"
else:
query = "INSERT INTO translations (`source_id`, `list_id`) "\
f"VALUES ('{word['id']}', '{list_id}')"
cursor.execute(query)
conn.commit()
def start_training(token, list_id):
assert check_list_presence(token, list_id)
user = get_user_by_token(token)
user_id = user['id']
training_id = str(uuid())
conn = connect()
with conn.cursor() as cursor:
query = "INSERT INTO trainings (`id`, `list_id`, `user_id`, `status`, `start`) VALUES ('{training_id}', '{list_id}', '{user_id}', 'started', NOW())"\
.format(training_id=training_id, list_id=list_id, user_id=user_id)
cursor.execute(query)
conn.commit()
revise = get_revise_words(token, list_id)
learn = get_learn_words(token, list_id)
cards = [{"id": str(uuid()), "type": "title", "text": "Let's get started"}]
for word in revise:
card = {
"id": str(uuid()),
"type": "input",
"text": word['source'],
"translation_id": word['id']
}
cards.append(card)
for word in learn:
card1 = {
"id": str(uuid()),
"type": "info",
"text": word['source'],
"answer": word['target'],
"translation_id": word['id']
}
card2 = {
"id": str(uuid()),
"type": "choice",
"text": word['source'],
"answers": random_words(list_id, word['target']),
"translation_id": word['id']
}
cards.append(card1)
cards.append(card2)
for word in learn:
card = {
"id": str(uuid()),
"type": "input",
"text": word['source'],
"translation_id": word['id']
}
cards.append(card)
cards.append({
'id': str(uuid()),
"type": "end",
"text": "Great! See you soon"
})
conn = connect()
with conn.cursor() as cursor:
for card in cards:
card['shown'] = False
if card['type'] == 'title' or card['type'] == 'end':
continue
query = "INSERT INTO cards (`id`, `type`, `translation_id`, `training_id`) " + \
"VALUES ('{id}', '{type}', '{translation_id}', '{training_id}')"\
.format(id=card['id'], type=card['type'], translation_id=card['translation_id'], training_id=training_id)
cursor.execute(query)
conn.commit()
with open("trainings/{training_id}.pkl".format(training_id=training_id),
'wb') as f:
pkl.dump(cards, f)
return training_id
def action_get_user():
token = request.form.get('token')
user = get_user_by_token(token)
return {"status": "OK", "user": user}