aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--flake.nix11
-rw-r--r--readme.md8
-rw-r--r--sql/queries.sql84
-rw-r--r--sql/tables.sql6
4 files changed, 107 insertions, 2 deletions
diff --git a/flake.nix b/flake.nix
index e09265f..578bb9b 100644
--- a/flake.nix
+++ b/flake.nix
@@ -4,7 +4,9 @@
4 4
5{ 5{
6 inputs = { 6 inputs = {
7 nixpkgs.url = "github:NixOS/nixpkgs/nixos-21.05"; 7 # for python3Packages.embrace: https://github.com/NixOS/nixpkgs/pull/131425
8 nixpkgs.url = "github:pacien/nixpkgs/3faf31d";
9 #nixpkgs.url = "github:NixOS/nixpkgs/nixos-unstable";
8 flake-utils.url = "github:numtide/flake-utils"; 10 flake-utils.url = "github:numtide/flake-utils";
9 }; 11 };
10 12
@@ -13,8 +15,13 @@
13 with import nixpkgs { inherit system; }; 15 with import nixpkgs { inherit system; };
14 let 16 let
15 17
16 develPackagesAndScripts = [ 18 python = python39;
19
20 develPackagesAndScripts = with python.pkgs; [
17 postgresql_13 # PostgreSQL server with the standard admin tools. 21 postgresql_13 # PostgreSQL server with the standard admin tools.
22 ipython # Interactive Python REPL for experimenting.
23 psycopg2 # PostgreSQL driver for Python
24 embrace # bridges raw SQL queries to Python functions
18 25
19 # More pleasant alternative to psql, with colours and auto-completion. 26 # More pleasant alternative to psql, with colours and auto-completion.
20 # Custom configuration to suppress irrelevant warnings and messages. 27 # Custom configuration to suppress irrelevant warnings and messages.
diff --git a/readme.md b/readme.md
index 3c53966..795b00e 100644
--- a/readme.md
+++ b/readme.md
@@ -64,6 +64,14 @@ of operations. For the same reason, the use of stored procedures was not
64retained. 64retained.
65 65
66 66
67## Project structure overview
68
69* `./sql/`
70 * `tables.sql`: database initialisation statements
71 * `queries.sql`: annotated SQL queries (from which the DAO is derived)
72* `./flake.nix`: project runtime and development environment description
73
74
67## Copyright and licensing 75## Copyright and licensing
68 76
69Copyright (C) 2021 Pacien TRAN-GIRARD. 77Copyright (C) 2021 Pacien TRAN-GIRARD.
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);