def get_messages(current_user_id, user_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Message)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
m.id,
m.content,
m.creator_id,
m.recipient_id,
u.id AS "current_user_id",
u.username AS "current_username",
v.id,
v.username
FROM BirdieApp_message m
JOIN auth_user u, auth_user v ON m.creator_id = u.id AND m.recipient_id = v.id
OR m.creator_id = v.id
AND m.recipient_id = u.id
WHERE u.id = ?
AND v.id = ?
ORDER BY m.id;
""", (current_user_id, user_id))
return db_cursor.fetchall()
def get_bags():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Bag)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
b.id AS bag_id,
b.user_id,
b.brand
FROM BirdieApp_bag b
""")
return db_cursor.fetchall()
def disc_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Disc)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
d.id,
d.user_id,
d.name,
d.disc_type,
d.bag_id,
d.color,
d.speed,
d.glide,
d.turn,
d.fade,
d.brand
FROM BirdieApp_disc d
ORDER BY d.disc_type
""")
all_discs = db_cursor.fetchall()
template = ('discs/list.html', 'bags/details.html')
context = {'all_discs': all_discs}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
user_id = request.user.id
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO BirdieApp_disc
(user_id, brand, name, disc_type, color, speed, glide, turn, fade)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (user_id, form_data['brand'], form_data['name'],
form_data['disc_type'], form_data['color'],
form_data['speed'], form_data['glide'], form_data['turn'],
form_data['fade']))
return redirect(reverse('BirdieApp:discs'))
def get_parks():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Park)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
p.id AS park_id,
p.user_id,
p.title,
p.city,
p.state
FROM BirdieApp_park p
""")
return db_cursor.fetchall()
def game_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Game)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
g.user_id,
g.id,
g.score,
g.started_at,
b.brand,
p.title
FROM BirdieApp_game g
JOIN BirdieApp_bag b ON b.id = g.bag_id
JOIN BirdieApp_park p ON p.id = g.park_id
ORDER BY g.started_at;
""")
all_games = db_cursor.fetchall()
template = ('games/list.html')
context = {
'all_games': all_games,
}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
user_id = request.user.id
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO BirdieApp_game
(user_id, started_at, score, bag_id, park_id)
VALUES
(?, ?, ?, ?, ?)
""",
(user_id, form_data['started_at'], form_data['score'], form_data['bag_id'], form_data['park_id']))
return redirect(reverse('BirdieApp:games'))
def user_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(User)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
id AS user_id,
username
FROM auth_user
""")
all_users = db_cursor.fetchall()
template = ('messages/list.html')
context = {'all_users': all_users}
return render(request, template, context)
def park_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Park)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
p.id,
p.user_id,
p.title,
p.city,
p.state
FROM BirdieApp_park p
ORDER BY p.state
""")
all_parks = db_cursor.fetchall()
template = 'parks/list.html'
context = {
'all_parks': all_parks
}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
user_id = request.user.id
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO BirdieApp_park
(user_id, title, city, state)
VALUES
(?, ?, ?, ?)
""",
(user_id, form_data['title'], form_data['city'], form_data['state'],))
return redirect(reverse('BirdieApp:parks'))
def bag_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Bag)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
b.id,
b.user_id,
b.brand
FROM BirdieApp_bag b
""")
all_bags = db_cursor.fetchall()
template = 'bags/list.html'
context = {'all_bags': all_bags}
return render(request, template, context)
elif request.method == 'POST':
form_data = request.POST
user_id = request.user.id
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO BirdieApp_bag
(user_id, brand)
VALUES
(?, ?)
""", (
user_id,
form_data['brand'],
))
return redirect(reverse('BirdieApp:bags'))