1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
-- UGE / L2 / Intro to relational databases / Python project prototype
-- Author: Pacien TRAN-GIRARD
-- Licence: EUPL-1.2
-- Fetch a user account by its ID.
--
-- :name fetch_account
-- :result :one-or-none
select * from accounts where id = :user_id;
-- Fetch a user account by its username.
--
-- :name fetch_account_username
-- :result :one-or-none
select * from accounts where username = :username;
-- Create a new user account with the given username and hashed password.
--
-- :name create_account
-- :result :one-or-none
insert into accounts (username, password_hash)
values (:username, :password_hash)
returning *;
-- Fetch all the transactions involving a given user.
-- The returned transaction log contains the usernames of the involved parties.
-- The sign of the amount column is adjusted to the perspective of the user,
-- so that its sum equals the balance of their account.
--
-- :name fetch_transactions
-- :result :many
select
transactions.id, datetime, operation,
account_sources.username as source,
account_recipients.username as recipient,
case when source = :user_id then -amount else amount end as amount
from transactions
left join accounts as account_sources on account_sources.id = source
left join accounts as account_recipients on account_recipients.id = recipient
where source = :user_id or recipient = :user_id
order by datetime desc, operation;
-- Deposit the specified amount to the account of the specified user and
-- generate a matching transaction log entry.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name deposit
-- :result :one
update accounts set balance = balance + :amount where id = :user_id;
insert into transactions (operation, recipient, amount)
values ('deposit', :user_id, :amount)
returning *;
-- Withdraw the specified amount from the account of the specified user and
-- generate a matching transaction log entry.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name withdraw
-- :result :one
update accounts set balance = balance - :amount where id = :user_id;
insert into transactions (operation, source, amount)
values ('withdrawal', :user_id, :amount)
returning *;
-- Transfer the specified amound from the account of a sender to the one of a
-- recipient, generating matching transaction log entries for the transfer
-- itself and the transfer fee if any is applicable.
--
-- This query should be executed within a transaction with the serializable
-- isolation level.
--
-- :name transfer
-- :result :one
update accounts set balance = balance - :amount - :fee where id = :from_user_id;
update accounts set balance = balance + :amount where id = :to_user_id;
insert into transactions (operation, source, amount)
select 'fee', :from_user_id, :fee where cast(:fee as simoleon) > 0;
insert into transactions (operation, source, recipient, amount)
values ('transfer', :from_user_id, :to_user_id, :amount)
returning *;
|