def save_text(user_id, language, url, text):
try:
time_stamp = int(time.time())
title = text[0:70]
sql = """
INSERT INTO reader_texts
(user_id, language, url, timestamp, content, read, title)
values(%s, %s, %s, %s, %s, %s, %s);
"""
# ToDo: this text cannot be saved and it is not clear why
# https://www.theatlantic.com/entertainment/archive/2019/08/my-joe-rogan-experience/594802/
# https://5sfer.com/devid-goggins-dzho-rogan-istoriya-uspeha-pohudenie/
# https://focus.ua/archivist/457635-jahnenko-simirenko
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql,
(user_id, language, url, time_stamp, text, False, title))
conn.commit()
cur.close()
conn.close()
except Exception as ex:
log.log_error("save_text(user_id, language, url, text) - part 1 " +
str(ex))
return -1, "part 1 - " + str(ex)
try:
sql = """
SELECT max(id) FROM reader_texts WHERE user_id = %s LIMIT 2;
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (user_id, ))
arr = cur.fetchall()
cur.close()
conn.close()
return arr[0][0], ""
except Exception as ex:
log.log_error("save_text(user_id, language, url, text) - part 2 " +
str(ex))
return -1, "part 2 - " + str(ex)
def get_word(new_id):
"""
id, l1, w1, l2, w2 = dbl.get_word(new_id)
:param new_id:
:return:
"""
conn = dba.get_connection()
cur = conn.cursor()
sql = ""
sql += "SELECT ID, language_word, word, language_translation, translation FROM vocabulary "
sql += "WHERE ID = %s "
cur.execute(sql, (new_id, ))
arr = cur.fetchall()
id = -1
l1 = ""
w1 = "Not enough words. Please upload at least 20 words."
l2 = ""
w2 = "Not enough words. Please upload at least 20 words."
if arr is not None:
if len(arr) > 0:
id = arr[0][0]
l1 = arr[0][1]
w1 = str(arr[0][2]).strip()
l2 = arr[0][3]
w2 = str(arr[0][4]).strip()
return id, l1, w1, l2, w2
def create_progress_table():
sql = """
CREATE TABLE IF NOT EXISTS reader_texts
(
id SERIAL,
user_id bigint,
language character(50),
url varchar(1000),
timestamp bigint,
title varchar(150),
content text,
read BOOLEAN
)
WITH (
OIDS=FALSE
);
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
def create_progress_table():
"""
parameter_id:
1 is for percentage learnd
2 is for ...
3 is for ...
"""
sql = """
CREATE TABLE IF NOT EXISTS progress_history
(
user_id bigint,
parameter_id bigint,
time_stamp_server bigint,
parameter_value numeric
)
WITH (
OIDS=FALSE
);
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql)
conn.commit()
conn.close()
def add_transition(word_id, user_id, transition):
sql = """
INSERT INTO transitions
(
user_id,
word_id,
time_stamp,
transition
)
VALUES
(
%s, %s, %s, %s
)
"""
time_stamp = int(time.time())
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (user_id, word_id, time_stamp, transition))
conn.commit()
def experiment_counter(word_id):
sql = """
SELECT
count_experiments,
count_forgotten
FROM
vocabulary
WHERE
id = %s
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (word_id, ))
try:
arr = cur.fetchall()
e = arr[0][0]
f = arr[0][1]
except:
e = 0
f = 0
return e, f
def get_learned_random(user_id):
"""
get a random word from the words that were already learned to check if it has been forgotten
this must be the actions that build the training set
:param user_id:
:return:
"""
conn = dba.get_connection()
cur = conn.cursor()
#cur.execute("SELECT id FROM vocabulary where user_id = %s AND current = FALSE AND direction = TRUE AND count_positive < 1 ORDER BY random() LIMIT 1", (user_id,))
cur.execute(
"SELECT id FROM vocabulary where user_id = %s AND current = FALSE AND count_positive > 1 ORDER BY random() LIMIT 1",
(user_id, ))
arr = cur.fetchall()
if len(arr) > 0:
l = arr[0][0]
else:
l = -1
return l
def add_to_history(user_id, word_id, answer):
r = get_calc_rank(word_id)
if answer == "YES":
result = True
else:
result = False
sql = """
INSERT INTO history
(
user_id,
word_id,
result,
time_stamp_server,
calc_rank
)
VALUES
(
%s, %s, %s, %s, %s
)
"""
time_stamp = int(time.time())
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (user_id, word_id, result, time_stamp, r))
conn.commit()
def check_session(s):
conn = dba.get_connection()
cur = conn.cursor()
cur.execute("SELECT count(*) FROM session WHERE session_string = %s ",
(s, ))
sessions = cur.fetchall()[0][0]
return sessions
def get_simple_report(user_id):
log.log_info("get_simple_report(user_id): for user " + str(user_id))
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(
"SELECT count(*) FROM vocabulary where user_id = %s AND current = false AND count_positive < 1 AND direction = TRUE",
(user_id, ))
new_words = cur.fetchall()[0][0]
cur.execute(
"SELECT count(*) FROM vocabulary where user_id = %s AND current = false AND count_positive > 0 AND direction = TRUE",
(user_id, ))
learned_words = cur.fetchall()[0][0]
parameter_name = "percentage_learned"
sql = "SELECT value FROM parameters WHERE user_id = %s AND key = %s"
cur.execute(sql, (user_id, parameter_name))
try:
arr = cur.fetchall()
p = arr[0][0]
log.log_info("value from rs is: " + str(p))
except:
log.log_info("value rs is not existing: ")
p = 0.0
ratio_learned = float(p) * 100
ratio_learned = round(ratio_learned, 2)
log.log_info("ratio_learned at end is is: " + str(ratio_learned))
return new_words, learned_words, ratio_learned
def fill_cache_from_db(user_id):
"""
copies from db into the cache
:param user_id:
:return:
"""
log.log_info("in fill_cache_from_db(user_id)")
conn = dba.get_connection()
cur = conn.cursor()
sql = """
SELECT id, COALESCE(short_memory_position, 1)
FROM vocabulary
WHERE user_id = %s
and current = true
and COALESCE(last_studied, 0) > 1
order by COALESCE(short_memory_position, 1)
LIMIT 100
"""
cur.execute(sql, (user_id, ))
arr = cur.fetchall()
log.log_info("in fill_cache_from_db(user_id) - array has length " +
str(len(arr)))
for row in arr:
add_to_cache(user_id, row[0], row[1])
def get_clicks_last_24_hours(user_id):
time_stamp_now = int(time.time())
diff = 24 * 60 * 60
time_stamp_start = time_stamp_now - diff
sql = """
SELECT
count(time_stamp_server)
FROM
history
WHERE
time_stamp_server >= %s
AND
time_stamp_server <= %s
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (time_stamp_start, time_stamp_now))
arr = rs_to_arr(cur.fetchall())
return arr[0]
def get_ip_to_location():
conn = dba.get_connection()
cur = conn.cursor()
sql = "SELECT distinct ip_address, json from ip_to_location limit 1000"
cur.execute(sql)
arr = cur.fetchall()
return arr
def get_old_by_score(user_id):
"""
take one word from the pile of words we already learned
:param user_id:
:return:
"""
log.log_info(" in get_old_by_score(user_id) ")
conn = dba.get_connection()
cur = conn.cursor()
# age of old word needs to be at least from yesterday
delta = 24 * 60 * 60
threshold = int(time.time()) - delta
sql = """
SELECT
id, calc_rank
FROM
vocabulary
WHERE
user_id = %s
AND
current = false
AND
count_positive > 0
AND
last_studied < %s
ORDER BY
/* changed following line on 16th feb 2019 because yes = 1 and we need to know failures so asc and desc*/
/* calc_rank desc */
calc_rank asc
LIMIT 2;
"""
cur.execute(sql, (user_id, threshold))
#l = cur.fetchall()[0][0]
arr = cur.fetchall()
#print(arr)
if len(arr) > 0:
i = int(random.random() * float(len(arr)))
l = arr[i][0]
set_experiment_state(l, True)
else:
l = -1
log.log_info("get_old_by_score returns " + str(l) + " for user id " +
str(user_id))
return l
def set_position_in_db(word_id, pos):
log.log_info("set_position " + str(word_id) + " to " + str(pos))
sql = "UPDATE vocabulary SET short_memory_position = %s WHERE id = %s "
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (pos, word_id))
conn.commit()
def add_new_word(user_id):
"""
Decide if we want to add a word from the pile of new words or from the pile of old words that need repetition
The decision depends on the percentage fo successfully leanrned words. If the predictions says that more than XX%
of the words are not forgotten, then we add a new word.
if the ratio is 0.0 then we add new words because the ration could not be calculated. but if the ratio is between
0.05 and 0.8 then we repeat old words first. if the ratio is > 0.85 then we add new words
:return:
"""
parameter_name = "percentage_learned"
conn = dba.get_connection()
cur = conn.cursor()
sql = "SELECT value FROM parameters WHERE user_id = %s AND key = %s"
cur.execute(sql, (user_id, parameter_name))
arr = cur.fetchall()
ratio_learned = -1.0 # default value
#print(user_id)
#print(parameter_name)
#print(arr)
if arr is not None:
if len(arr) > 0:
ratio_learned = float(arr[0][0])
log.log_info("add_new_word(user_id) " + str(user_id) +
" ratio learned is " + str(ratio_learned))
r = random.random()
if ratio_learned < 0.0 or ratio_learned > 0.99: # this happens when not enough words were so far learned and parameter is not filled
# generally we would like to add only new words but we also need to start doing experiments
if r > 0.5:
return True
else:
return False
elif ratio_learned < 0.05: # ToDo this is probably an error and needs to be removed
return True
elif ratio_learned > 0.85: # we know most of the words and make rarely experiments
if r > 0.9: # only in 10 percent we make experiments
return False
else:
return True
else:
if r > (ratio_learned *
0.8): # if 70% learned, then we make 30% experiments
return False
else:
return True
def get_user_id(u):
# this should only be used by SLAVE
conn = dba.get_connection()
cur = conn.cursor()
cur.execute("SELECT id FROM users WHERE email = %s ", (u, ))
user_id = cur.fetchall()[0][0]
return user_id
def register_slave(session_or_user, slave, user_email):
session_or_user = session_or_user.strip().lower()
slave = slave.strip() # do not make this lower case !!!
ts = time.time()
if len(session_or_user) > 3:
conn = dba.get_connection()
cur = conn.cursor()
sql = "DELETE FROM master_slave_mapping WHERE session_or_user = %s;"
cur.execute(sql, (session_or_user, ))
conn.commit()
#sql = "INSERT INTO master_slave_mapping (session_or_user, slave_id, user_email, time_stamp) VALUES (%s, %s, %s, %s);"
#cur.execute(sql, (session_or_user, slave, user_email, ts))
sql = "INSERT INTO master_slave_mapping (session_or_user, slave_id) VALUES (%s, %s);"
cur.execute(sql, (session_or_user, slave))
conn.commit()
elif len(user_email) > 3:
conn = dba.get_connection()
cur = conn.cursor()
sql = "DELETE FROM master_slave_mapping WHERE session_or_user = %s;"
cur.execute(sql, (session_or_user, ))
conn.commit()
# sql = "INSERT INTO master_slave_mapping (session_or_user, slave_id, user_email, time_stamp) VALUES (%s, %s, %s, %s);"
# cur.execute(sql, (session_or_user, slave, user_email, ts))
sql = "INSERT INTO master_slave_mapping (session_or_user, slave_id) VALUES (%s, %s);"
cur.execute(sql, (user_email, slave))
conn.commit()
else:
log.log_error(
"cannot register register_slave(session_or_user, slave) because session_or_user or user_email too short: "
+ str(session_or_user))
log.log_error("session_or_user : "******"user_email : " + str(user_email))
def register_user(u, p):
conn = dba.get_connection()
cur = conn.cursor()
sql = "INSERT INTO users (email, password) VALUES (%s, %s);"
hash_object = hashlib.md5(p.encode())
p_hash = hash_object.hexdigest()
log.log_info("p=" + p)
log.log_info("p_hash=" + p_hash)
cur.execute(sql, (u, p_hash))
conn.commit()
def update_password(u, p):
conn = dba.get_connection()
cur = conn.cursor()
sql = "UPDATE users SET password = %s WHERE email = %s;"
hash_object = hashlib.md5(p.encode())
p_hash = hash_object.hexdigest()
log.log_info("p=" + p)
log.log_info("p_hash=" + p_hash)
cur.execute(sql, (p_hash, u))
conn.commit()
def get_user_id_from_session(s):
# this should only be used by SLAVE, because user id is not unique in master
try:
conn = dba.get_connection()
cur = conn.cursor()
cur.execute("SELECT user_id FROM session WHERE session_string = %s ",
(s, ))
id = cur.fetchall()[0][0]
except:
id = -1
return id
def clean_slave():
print("now clean up SLAVE")
conn = dba.get_connection()
cur = conn.cursor()
sql = "DELETE FROM session WHERE session_string like '%$testuser$%';"
cur.execute(sql)
conn.commit()
print("clean up SLAVE done")
def update_forgot_score(id, score):
sql = """
UPDATE vocabulary SET calc_rank = %s WHERE id = %s
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (score, id))
conn.commit()
def get_no(word_id):
"""
Get for a specific word the number of negative answers
:param word_id:
:return:
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute("SELECT count_negative FROM vocabulary where ID = %s",
(word_id, ))
l = cur.fetchall()[0][0]
return l
def debug_master_slave_mapping():
conn = dba.get_connection()
cur = conn.cursor()
cur.execute("SELECT slave_id, session_or_user FROM master_slave_mapping ")
rs = cur.fetchall()
log.log_info("---------------------------------")
log.log_info("master slave mapping: ")
for r in rs:
log.log_info("row: " + str(r))
log.log_info("---------------------------------")
def count_learned(user_id):
"""
count the number of words that were already successfully learned
:param user_id:
:return:
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(
"SELECT count(ID) FROM vocabulary where count_positive > 0 AND user_id = %s",
(user_id, ))
l = cur.fetchall()[0][0]
return l
def clean_master():
print("now clean up MASTER")
conn = dba.get_connection()
cur = conn.cursor()
sql = "DELETE FROM master_slave_mapping WHERE session_or_user like '%$testuser$%';"
cur.execute(sql)
conn.commit()
print("clean up MASTER done")
sec.debug_master_slave_mapping()
def set_experiment_state(word_id, is_experiment):
"""
we flag a word if it is in experiment state or not. Important is to remove the flag once the experiment is over.
:param word_id:
:param is_experiment:
:return:
"""
sql = "UPDATE vocabulary SET is_experiment = %s WHERE id = %s "
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (is_experiment, word_id))
conn.commit()
def check_login(u, p):
conn = dba.get_connection()
cur = conn.cursor()
hash_object = hashlib.md5(p.encode())
p_hash = hash_object.hexdigest()
log.log_info("p=" + p)
log.log_info("p_hash=" + p_hash)
cur.execute(
"SELECT count(*) FROM users WHERE email = %s AND password = %s",
(u, p_hash))
ret = cur.fetchall()[0][0]
print(ret)
return ret
def insert_ip_loction(ip, time_stamp, location):
sql = """
INSERT INTO
ip_to_location
(ip_address, time_stamp, json)
VALUES
(%s, %s, %s);
"""
conn = dba.get_connection()
cur = conn.cursor()
cur.execute(sql, (ip, time_stamp, location))
conn.commit()
