aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/queries.sql84
-rw-r--r--sql/tables.sql6
2 files changed, 90 insertions, 0 deletions
diff --git a/sql/queries.sql b/sql/queries.sql
new file mode 100644
index 0000000..066781f
--- /dev/null
+++ b/sql/queries.sql
@@ -0,0 +1,84 @@
1-- UGE / L2 / Intro to relational databases / Python project prototype
2-- Author: Pacien TRAN-GIRARD
3-- Licence: EUPL-1.2
4
5
6-- Fetch a user account by its username.
7--
8-- :name fetch_account
9-- :result :one-or-none
10select * from accounts where username = :username;
11
12
13-- Create a new user account with the given username and hashed password.
14--
15-- :name create_account
16-- :result :one-or-none
17insert into accounts (username, password_hash)
18 values (:username, :password_hash)
19 returning *;
20
21
22-- Fetch all the transactions involving a given user.
23-- The returned transaction log contains the usernames of the involved parties.
24-- The sign of the amount column is adjusted to the perspective of the user,
25-- so that its sum equals the balance of their account.
26--
27-- :name fetch_transactions
28-- :result :many
29select
30 transactions.id, datetime, operation,
31 account_sources.username as source,
32 account_recipients.username as recipient,
33 case when source = :user_id then -amount else amount end as amount
34 from transactions
35 left join accounts as account_sources on account_sources.id = source
36 left join accounts as account_recipients on account_recipients.id = recipient
37 where source = :user_id or recipient = :user_id
38 order by datetime desc, operation;
39
40
41-- Deposit the specified amount to the account of the specified user and
42-- generate a matching transaction log entry.
43--
44-- This query should be executed within a transaction with the serializable
45-- isolation level.
46--
47-- :name deposit
48-- :result :one
49update accounts set balance = balance + :amount where id = :user_id;
50insert into transactions (operation, recipient, amount)
51 values ('deposit', :user_id, :amount)
52 returning *;
53
54
55-- Withdraw the specified amount from the account of the specified user and
56-- generate a matching transaction log entry.
57--
58-- This query should be executed within a transaction with the serializable
59-- isolation level.
60--
61-- :name withdraw
62-- :result :one
63update accounts set balance = balance - :amount where id = :user_id;
64insert into transactions (operation, source, amount)
65 values ('withdrawal', :user_id, :amount)
66 returning *;
67
68
69-- Transfer the specified amound from the account of a sender to the one of a
70-- recipient, generating matching transaction log entries for the transfer
71-- itself and the transfer fee if any is applicable.
72--
73-- This query should be executed within a transaction with the serializable
74-- isolation level.
75--
76-- :name transfer
77-- :result :one
78update accounts set balance = balance - :amount - :fee where id = :from_user_id;
79update accounts set balance = balance + :amount where id = :to_user_id;
80insert into transactions (operation, source, amount)
81 select 'fee', :from_user_id, :fee where cast(:fee as simoleon) > 0;
82insert into transactions (operation, source, recipient, amount)
83 values ('transfer', :from_user_id, :to_user_id, :amount)
84 returning *;
diff --git a/sql/tables.sql b/sql/tables.sql
index d56156b..37ffbff 100644
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -41,3 +41,9 @@ create table transactions (
41 (operation in ('fee', 'withdrawal') or recipient is not null), 41 (operation in ('fee', 'withdrawal') or recipient is not null),
42 amount simoleon not null check (amount > 0) 42 amount simoleon not null check (amount > 0)
43); 43);
44
45-- Additional indexes for the financial transaction log for the common
46-- participant-centered queries. Those also include the datetime column, which
47-- is useful for paginating the log.
48create index transaction_source_index on transactions (source, datetime);
49create index transaction_recipient_index on transactions (recipient, datetime);