sql: add queries (with embrace)

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 @@
 
 {
   inputs = {
-    nixpkgs.url = "github:NixOS/nixpkgs/nixos-21.05";
+    # for python3Packages.embrace: https://github.com/NixOS/nixpkgs/pull/131425
+    nixpkgs.url = "github:pacien/nixpkgs/3faf31d";
+    #nixpkgs.url = "github:NixOS/nixpkgs/nixos-unstable";
     flake-utils.url = "github:numtide/flake-utils";
   };
 
@@ -13,8 +15,13 @@
   with import nixpkgs { inherit system; };
   let
 
-    develPackagesAndScripts = [
+    python = python39;
+
+    develPackagesAndScripts = with python.pkgs; [
       postgresql_13  # PostgreSQL server with the standard admin tools.
+      ipython        # Interactive Python REPL for experimenting.
+      psycopg2       # PostgreSQL driver for Python
+      embrace        # bridges raw SQL queries to Python functions
 
       # More pleasant alternative to psql, with colours and auto-completion.
       # 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
 retained.
 
 
+## Project structure overview
+
+* `./sql/`
+  * `tables.sql`: database initialisation statements
+  * `queries.sql`: annotated SQL queries (from which the DAO is derived)
+* `./flake.nix`: project runtime and development environment description
+
+
 ## Copyright and licensing
 
 Copyright (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 @@
+-- UGE / L2 / Intro to relational databases / Python project prototype
+-- Author: Pacien TRAN-GIRARD
+-- Licence: EUPL-1.2
+
+
+-- Fetch a user account by its username.
+--
+-- :name fetch_account
+-- :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 *;
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 (
     (operation in ('fee', 'withdrawal') or recipient is not null),
   amount simoleon not null check (amount > 0)
 );
+
+-- Additional indexes for the financial transaction log for the common
+-- participant-centered queries. Those also include the datetime column, which
+-- is useful for paginating the log.
+create index transaction_source_index on transactions (source, datetime);
+create index transaction_recipient_index on transactions (recipient, datetime);