mirror of
https://git.dirksys.ovh/dirk/bankserver.git
synced 2025-12-20 02:59:20 +01:00
57 lines
1.8 KiB
SQL
57 lines
1.8 KiB
SQL
create table users(
|
|
id uuid primary key,
|
|
name varchar(32) not null unique,
|
|
password varchar(128)
|
|
);
|
|
|
|
create table accounts(
|
|
id uuid primary key,
|
|
"user" uuid not null references users(id),
|
|
name varchar(32) not null,
|
|
balance bigint not null default 0 constraint positive_balance check (balance >= 0),
|
|
unique ("user", name)
|
|
);
|
|
|
|
create table transactions(
|
|
id uuid primary key,
|
|
"from" uuid references accounts(id),
|
|
"to" uuid references accounts(id),
|
|
interop_name text,
|
|
system bool not null default false,
|
|
amount bigint not null constraint positive_amount check (amount > 0),
|
|
timestamp timestamp with time zone not null default now(),
|
|
message text,
|
|
constraint check_interop_name_from_to_null check (
|
|
((("from" is null and "to" is not null) or ("from" is not null and "to" is null)) and (interop_name is not null or system)) or
|
|
("from" is not null and "to" is not null and interop_name is null)
|
|
)
|
|
);
|
|
|
|
create index transactions_from on transactions ("from");
|
|
create index transactions_to on transactions ("to");
|
|
|
|
create view transactions_with_user_info as select
|
|
t.amount,
|
|
t.message,
|
|
t.timestamp,
|
|
t.system,
|
|
case
|
|
when t."from" is null then t.interop_name
|
|
when t."to" is null then t.interop_name
|
|
end as interop_name,
|
|
-- From Participant
|
|
uf.id AS from_user_id,
|
|
t."from" as from_account_id,
|
|
uf.name AS from_user_name,
|
|
af.name AS from_account_name,
|
|
-- To Participant
|
|
ut.id AS to_user_id,
|
|
t."to" as to_account_id,
|
|
ut.name AS to_user_name,
|
|
at.name AS to_account_name
|
|
from transactions t
|
|
left join accounts af ON t."from" = af.id
|
|
left join users uf ON af."user" = uf.id
|
|
left join accounts at ON t."to" = at.id
|
|
left join users ut ON at."user" = ut.id;
|