aboutsummaryrefslogtreecommitdiff
path: root/sql/queries.sql
blob: 3cc83424de678b38e2d98d6837329d10c4337b42 (plain)
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 *;