def create(fields):
return (execute("INSERT INTO posts(user_id, content, title, "
"description) "
"VALUES(%d, %s, %s, %s)" % (
int(fields['user_id']),
quote_string(fields['content']),
quote_string(fields['title']),
quote_string(fields['description']))),
fit(fetch("SELECT LAST_INSERT_ID()"), ('last_id',)))
def create(fields):
pwd = bcrypt.hashpw(fields['password'].encode('utf-8'),
bcrypt.gensalt(14))
return insert(table='users',
columns=['username', 'password', 'bio'],
values=[quote_string(fields['username']),
quote_string(pwd),
quote_string(fields['bio'])])
def find_by_username(username):
result = select(table='users',
columns=('user_id', 'username', 'bio', 'join_date'),
condition='username={}'.format(quote_string(username)))
if len(result) > 0:
return result[0]
else:
return None
def update(post_id, fields):
attrs = ['content']
nonnull = [attr for attr in attrs if fields[attr] is not None]
return update(table='comments',
columns=[attr for attr in nonnull],
values=[quote_string(fields[attr]) for attr in nonnull],
condition="comment_id=%d" % post_id)
def auth(fields):
result = select(table='users',
columns=('user_id', 'username', 'password'),
condition="username=%s" %
quote_string(fields['username']))
if len(result) > 0:
if bcrypt.checkpw(fields['password'].encode('utf-8'),
result[0]['password'].encode('utf-8')):
del result[0]['password']
return result[0]
return None
def update(id, fields):
attrs = ['username', 'password', 'bio']
nonnull = [attr for attr in attrs if fields[attr] is not None]
if 'password' in attrs:
fields['password'] = bcrypt.hashpw(
fields['password'].encode('utf-8)'),
bcrypt.gensalt(14)
)
return update(table='users',
columns=[attr for attr in nonnull],
values=[quote_string(fields[attr]) for attr in nonnull],
condition="user_id=%d" % id)
def get_create_new(topic):
r = select(table='topics',
columns=('topic_id', 'topic', 'description'),
condition='topic=LOWER(%s)' % quote_string(topic.lower()))
if len(r) == 0:
_, last_id = Topic.create({'topic': topic,
'description': 'New Topic'})
r = [Topic.find(last_id)]
if len(r) == 0:
return None
else:
return r[0]
def get(self):
args = parser.parse_args()
query = args['query']
words = [x.strip().lower() for x in query.split()]
words = list(set(words))
if len(words) == 0:
return []
tentative = [word for word in words if word not in stopwords]
if len(tentative) > 0:
words = tentative
word_values = ",".join(["({})".format(quote_string(_))
for _ in words])
non_pk_cols = ','.join(['title', 'description', 'content'])
r = fetch("""
DROP TEMPORARY TABLE IF EXISTS words;
DROP TEMPORARY TABLE IF EXISTS words2;
CREATE TEMPORARY TABLE words(word VARCHAR(40));
CREATE TEMPORARY TABLE words2(word VARCHAR(40));
INSERT INTO words VALUES {1};
INSERT INTO words2 VALUES {1};
SELECT post_id, {0}, MAX(tf_idf)
FROM (
SELECT post_id, {0}, word, LOG(((SELECT COUNT(*) FROM posts) + 1) / (doc_freq)) * tf as tf_idf
FROM (
SELECT p.post_id, {0}, w.word, IFNULL(doc_freq,0) as doc_freq,
ROUND((LENGTH(p.content) - LENGTH(REPLACE(LOWER(p.content), w.word, ""))) / LENGTH(w.word)) AS tf
FROM posts p
JOIN words w
LEFT JOIN (
SELECT word, COUNT(*) as doc_freq
FROM posts p, words2 w
WHERE LOCATE(w.word, LOWER(p.content)) > 0
GROUP BY word
) AS tf
ON w.word = tf.word
) as d
) AS a
GROUP BY post_id
ORDER BY MAX(tf_idf) DESC;""".format(non_pk_cols, word_values),
multi=True)
posts = fit(r, ('post_id', 'title', 'description', 'content', 'tf_idf'))
for post in posts:
post['topics'] = Post.fetch_topics(post['post_id'])
return posts
def create(fields):
return insert(table="quizzes",
columns=("title", "description"),
values=[quote_string(fields['topic']),
quote_string(fields['description'])])
def create(fields):
return create(table="topics",
columns=("topic", "description"),
values=[quote_string(fields['topic']),
quote_string(fields['description'])])
def create(post_id, fields):
return insert(table='comments',
columns=['content', 'user_id', 'post_id'],
values=[quote_string(fields['content']),
quote_string(fields['user_id']),
str(post_id)])
