def get(self):
"Retrieve the n most recent comments on a URL"
data = request.get_json()
author = data['author']
title = data['title']
n = data['n']
conn = get_db()
c = conn.cursor()
c.execute(
"""SELECT * from articles where title == (?) and author == (?)""",
(title, author))
row = c.fetchone()
article_id = row[0]
if row != None:
try:
c.execute(
"""SELECT * from comments where article_id == (?) ORDER BY post_time DESC LIMIT (?)""",
(article_id, n))
rows = c.fetchall()
return jsonify(rows), 200
except Exception:
conn.rollback()
return jsonify(message="Failed to get comments"), 409
def get_user_details(username):
conn = get_db()
c = conn.cursor()
c.execute("""SELECT * FROM users WHERE username = ?""", (username, ))
rows = c.fetchall()
if len(rows) == 0:
return False
return rows
def delete_user(username):
conn = get_db()
c = conn.cursor()
try:
c.execute("""DELETE FROM users WHERE username = ?""", (username, ))
conn.commit()
except Exception:
conn.rollback()
def delete_tag(tag_id):
conn = get_db()
c = conn.cursor()
try:
c.execute("""DELETE FROM tags WHERE tag_id = ?""", (tag_id, ))
conn.commit()
except Exception:
conn.rollback()
def delete_article(article_id):
conn = get_db()
c = conn.cursor()
try:
c.execute("""DELETE FROM articles WHERE article_id = ?""",
(article_id, ))
conn.commit()
except Exception:
conn.rollback()
def create_user(username, password, display_name):
hash_password = encode_password(password)
conn = get_db()
c = conn.cursor()
try:
c.execute("""INSERT into users values (NULL, ?, ?, ?)""",
(username, hash_password, display_name))
conn.commit()
except Exception:
conn.rollback()
def update_password(username, new_password):
hash_password = encode_password(new_password)
conn = get_db()
c = conn.cursor()
try:
c.execute("""UPDATE users SET password = ? WHERE username = ?""",
(hash_password, username))
conn.commit()
except Exception:
conn.rollback()
def get_article_by_url(url):
conn = get_db()
c = conn.cursor()
try:
c.execute("""SELECT * FROM articles where url = ?""", (url, ))
rows = c.fetchall()
if len(rows) == 0:
return False
return rows
except Exception as e:
return e
def get_tag_details(url):
conn = get_db()
c = conn.cursor()
try:
c.execute("""SELECT * FROM tags where url = ?""", (url, ))
rows = c.fetchall()
if len(rows) == 0:
return False
return rows
except Exception as e:
return e
def get_article_details(user_id, title):
conn = get_db()
c = conn.cursor()
try:
c.execute("""SELECT * FROM articles where user_id = ? AND title = ?""",
(user_id, title))
rows = c.fetchall()
if len(rows) == 0:
return False
return rows
except Exception as e:
return e
def post_tag(tag_name, url):
conn = get_db()
c = conn.cursor()
unix = int(time.time())
post_time = str(
datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
try:
c.execute("""INSERT INTO tags VALUES (NULL, ?, ?, ?)""",
(tag_name, url, post_time))
conn.commit()
except Exception:
conn.rollback()
def post(self):
data = request.get_json()
title = data['title']
author_name = data['author']
comment = data['comment']
post_time = datetime.now()
success_flag = 0
if author == "Anonymous Coward":
user_id = -1
display_name = "Anonymous Coward"
else:
user_id = data['user_id']
display_name = data['display_name']
conn = get_db()
c = conn.cursor()
try:
c.execute(
"""SELECT * from articles where title == (?) and author == (?)""",
(title, author_name))
row = c.fetchone()
article_id = row[0]
except Exception:
response = app.response_class(response=json.dumps(
{"message": "Article Not Found"}, indent=4),
status=404,
content_type='application/json')
return response
if row != None:
try:
c.execute(
"""INSERT into comments(user_id, article_id, display_name, comment, post_time) values (?,?,?,?,?)
""", (user_id, article_id, display_name, comment, post_time))
conn.commit()
success_flag = 1
except Exception:
conn.rollback()
if success_flag == 1:
data['user_id'] = user_id
data['display_name'] = display_name
resp = jsonify(data)
resp.status_code = 201
return resp
else:
response = app.response_class(response=json.dumps(
{"message": "Failed to add Comment"}, indent=4),
status=409,
content_type='application/json')
return response
def get_article_id(data):
title = data['title']
author = data['author']
conn = get_db()
c = conn.cursor()
c.execute(
"""SELECT * from articles where title == (?) and author == (?)""",
(title, author))
row = c.fetchone()
article_id = row[0]
return article_id
def get_article(title):
conn = get_db()
c = conn.cursor()
try:
c.execute(
"""SELECT * FROM articles WHERE title = ? ORDER BY post_time desc""",
(title, ))
rows = c.fetchall()
if len(rows) == 0:
return False
return rows
except Exception as e:
return e
def edit_article(title, author, text, article_id):
conn = get_db()
c = conn.cursor()
unix = int(time.time())
last_updated_time = str(
datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
try:
c.execute(
"""UPDATE articles SET text = ?, author = ?, title = ?,last_updated_time = ? WHERE article_id = ?""",
(text, author, title, last_updated_time, article_id))
conn.commit()
except Exception:
conn.rollback()
def get_n_articles(n):
conn = get_db()
c = conn.cursor()
c.execute(
"""SELECT text, author, title, post_time, last_updated_time FROM articles ORDER BY \
post_time desc LIMIT ?""", (n, ))
rows = c.fetchall()
if len(rows) == 0:
return False
row_headers = [x[0] for x in c.description]
articles = []
for article in rows:
articles.append(dict(zip(row_headers, article)))
return articles
def get_tags_metadata(n):
conn = get_db()
c = conn.cursor()
c.execute(
"""SELECT tag, title, url FROM tags ORDER BY post_time LIMIT ?""",
(n, ))
row_headers = [x[0] for x in c.description]
rows = c.fetchall()
tags = []
for tag in rows:
tags.append(dict(zip(row_headers, tag)))
return tags
def post_article(user_id, text, author, title, url):
conn = get_db()
c = conn.cursor()
unix = int(time.time())
post_time = str(
datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
last_updated_time = str(
datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
try:
c.execute(
"""INSERT INTO articles VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)""",
(user_id, text, author, title, url, post_time, last_updated_time))
conn.commit()
except Exception:
conn.rollback()
def get_tags_by_url(url):
conn = get_db()
c = conn.cursor()
try:
c.execute("""SELECT tag_name, url FROM tags where url = ?""", (url, ))
rows = c.fetchall()
if len(rows) == 0:
return False
row_headers = [x[0] for x in c.description]
tags = []
for tag in rows:
tags.append(dict(zip(row_headers, tag)))
return tags
except Exception as e:
return e
def get(self):
"Retrieve the number of comments on a given article"
data = request.get_json()
# title = data['title']
# author = data['author']
article_id = data['article_id']
conn = get_db()
c = conn.cursor()
try:
c.execute('SELECT COUNT(*) from comments where article_id == (?)',
(article_id))
rows = c.fetchone()
return jsonify(rows), 200
except Exception:
conn.rollback()
error_msg = "Failed to retrieve the Number of Comments"
return jsonify(error_msg), 409
def delete(self):
# Delete an individual comment
data = request.get_json()
comment_id = data['comment_id']
conn = get_db()
c = conn.cursor()
c.execute("""SELECT * from comments where comment_id == (?)""",
(comment_id))
row = c.fetchone()
if row:
c.execute("""DELETE from comments where comment_id == (?)""",
(comment_id))
conn.commit()
response = app.response_class(response=json.dumps(
{"message": "OK"}, indent=4),
status=200,
content_type='application/json')
return response
else:
response = app.response_class(response=json.dumps(
{"message": "Comment Not Found"}, indent=4),
status=404,
content_type='application/json')
return response
#!/usr/bin/python
# -*- coding: utf-8 -*-
import db_connection
# Set up database connection
con = db_connection.get_db()
def get_busline(relation_id):
# Execute query to obtain line of bus route
sql = "SELECT ST_LineMerge(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)";
busline = con.ExecuteSQL(sql);
return busline;
def get_busstops(relation_id):
# Execute query to obtain points of bus stops
sql = "SELECT name, public_transport, highway, '' AS official_status, amenity, osm_id, ST_Transform(way, 4326) FROM planet_osm_point JOIN (SELECT ltrim(member, 'n')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(relation_id) + ") u WHERE member LIKE 'n%') x USING (osm_id) WHERE public_transport = 'platform'";
busstops = con.ExecuteSQL(sql);
return busstops;
def get_stoparea(node_id):
# Execute query to obtain bus stop area relation
sql = "SELECT id, tags FROM planet_osm_rels WHERE ARRAY['public_transport','stop_area']<@tags AND ARRAY['n" + str(node_id) + "','platform']<@members";
stoparea = con.ExecuteSQL(sql);
return stoparea;
def get_routemaster(relation_id):
# Execute query to obtain route master relation
#!/usr/bin/python
# -*- coding: utf-8 -*-
import db_connection
# Set up database connection
con = db_connection.get_db()
def get_busline(relation_id):
# Execute query to obtain line of bus route
sql = "SELECT ST_LineMerge(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(
relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)"
busline = con.ExecuteSQL(sql)
return busline
def get_busstops(relation_id):
# Execute query to obtain points of bus stops
sql = "SELECT name, public_transport, highway, amenity, osm_id, ST_Transform(way, 4326) FROM planet_osm_point JOIN (SELECT ltrim(member, 'n')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id = " + str(
relation_id
) + ") u WHERE member LIKE 'n%') x USING (osm_id) WHERE public_transport = 'platform'"
busstops = con.ExecuteSQL(sql)
return busstops
def get_length(relation_id):
sql = "SELECT ST_Length(ST_Transform(ST_Collect(way), 4326)) FROM planet_osm_line JOIN (SELECT ltrim(member, 'w')::bigint AS osm_id FROM (SELECT unnest(members) AS member FROM planet_osm_rels WHERE id =" + str(
relation_id) + ") u WHERE member LIKE 'w%') x USING (osm_id)"
busline_length = con.ExecuteSQL(sql)
