sql: add queries (with embrace)

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 @@
+-- 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);