Skip to content

Instantly share code, notes, and snippets.

@abrkn

abrkn/db.sql

Created Jan 7, 2015
Embed
What would you like to do?
CREATE SCHEMA core;
CREATE DOMAIN currency_id AS TEXT
CHECK (LENGTH(VALUE) > 0);
CREATE TABLE currency (
currency currency_id PRIMARY KEY
);
CREATE DOMAIN amount AS NUMERIC(20, 8)
CHECK (VALUE >= 0);
CREATE DOMAIN account_type AS TEXT
CHECK (VALUE IN ('user'));
CREATE TABLE user (
user_id SERIAL PRIMARY KEY
);
-- Create accounts with user
CREATE FUNCTION user_create_accounts_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO account (user_id, currency, type)
SELECT NEW.user_id, currency, 'user'
FROM currency;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER create_accounts
AFTER INSERT ON user
FOR EACH ROW
EXECUTE PROCEDURE user_create_accounts_trigger();
-- Account
CREATE TABLE account (
account_id SERIAL PRIMARY KEY,
currency currency_id NOT NULL,
user_id INT REFERENCES user(user_id),
balance amount NOT NULL DEFAULT(0) CHECK(balance >= 0),
held amount NOT NULL DEFAULT(0) CHECK (held >= 0),
available amount NOT NULL DEFAULT(0) CHECK (available >= 0),
type account_type NOT NULL,
CHECK (held + available = balance)
);
-- Keep available in sync
CREATE FUNCTION account_before_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.available := NEW.balance - NEW.held;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER before_update_trigger
BEFORE UPDATE OF balance, held ON account
FOR EACH ROW
EXECUTE PROCEDURE account_before_update_trigger();
-- Hold
CREATE TABLE hold (
hold_id SERIAL PRIMARY KEY,
account_id INT NOT NULL REFERENCES account(account_id),
amount amount NOT NULL CHECK (amount > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT(now())
);
-- Transfer
CREATE TABLE transfer (
transfer_id SERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
debit_account_id INT NOT NULL REFERENCES account(account_id),
credit_account_id INT NOT NULL REFERENCES account(account_id),
amount amount NOT NULL CHECK (amount > 0),
CHECK (debit_account_id <> credit_account_id)
);
-- Validate account currencies
CREATE FUNCTION transfer_validate_account_currencies()
RETURNS TRIGGER AS $$
DECLARE
debit_currency currency_id;
credit_currency currency_id;
BEGIN
SELECT currency
INTO debit_currency
FROM account
WHERE account_id = NEW.debit_account_id;
SELECT currency
INTO credit_currency
FROM account
WHERE account_id = NEW.credit_account_id;
IF debit_currency <> credit_currency THEN
RAISE 'Currency mismatch (debit %, credit %)', debit_currency, credit_currency;
END IF;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER validate_account_currencies
BEFORE INSERT ON transfer
FOR EACH ROW
EXECUTE PROCEDURE transfer_validate_account_currencies();
-- Update account balances
CREATE FUNCTION transfer_update_balances_trigger()
RETURNS TRIGGER AS $$
BEGIN
UPDATE account
SET balance = balance - NEW.amount
WHERE account_id = NEW.debit_account_id;
UPDATE account
SET balance = balance + NEW.amount
WHERE account_id = NEW.credit_account_id;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER update_balances
AFTER INSERT ON transfer
FOR EACH ROW
EXECUTE PROCEDURE transfer_update_balances_trigger();
CREATE SCHEMA exchange;
CREATE DOMAIN exchange.order_amount AS NUMERIC(20, 5)
CHECK (VALUE >= 0);
CREATE DOMAIN exchange.order_price AS NUMERIC(20, 3)
CHECK (VALUE > 0);
CREATE DOMAIN exchange.order_side AS TEXT
CHECK (VALUE IN ('bid', 'ask'));
CREATE TABLE exchange.market (
market_id TEXT PRIMARY KEY,
base currency_id NOT NULL,
quote currency_id NOT NULL
);
CREATE FUNCTION exchange.market_set_id()
RETURNS TRIGGER AS $$
BEGIN
NEW.market_id := NEW.base || '/' || NEW.quote;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER set_id
BEFORE INSERT ON exchange.market
FOR EACH ROW
EXECUTE PROCEDURE exchange.market_set_id();
CREATE TABLE exchange.order (
order_id SERIAL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
source_account_id INT NOT NULL REFERENCES account(account_id),
dest_account_id INT NOT NULL REFERENCES account(account_id),
base currency_id NOT NULL,
quote currency_id NOT NULL,
amount exchange.order_amount NOT NULL,
side exchange.order_side NOT NULL,
hold_id INT NOT NULL REFERENCES hold(hold_id),
price exchange.order_price NOT NULL,
amount_remaining exchange.order_amount NOT NULL,
amount_matched exchange.order_amount NOT NULL DEFAULT(0),
amount_canceled exchange.order_amount NOT NULL DEFAULT(0),
canceled_at TIMESTAMPTZ,
CHECK (amount = amount_remaining + amount_matched + amount_canceled),
CHECK (canceled_at IS NULL AND amount_canceled = 0 OR canceled_at IS NOT NULL AND amount_canceled > 0 AND amount_remaining = 0)
);
-- Cancel order (by id)
CREATE FUNCTION cancel_order (_order_id INT)
RETURNS BOOLEAN AS $$
DECLARE
_order exchange.order%ROWTYPE;
BEGIN
SELECT *
INTO _order
FROM exchange.order
WHERE order_id = _order_id
FOR SHARE;
IF NOT FOUND OR _order.amount_remaining = 0 THEN
RETURN FALSE;
END IF;
UPDATE "order"
SET
hold_id = NULL,
aomunt_canceled = _order.amount_remaining,
amount_remaining = 0
WHERE order_id = _order_id;
RETURN TRUE;
END; $$ LANGUAGE PLPGSQL VOLATILE;
-- Create hold on insert
CREATE FUNCTION exchange.order_insert_create_hold_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- TODO: Confirm currency
NEW.amount_remaining := NEW.amount;
INSERT INTO hold (account_id, amount)
VALUES (NEW.source_account_id, CASE WHEN _side = 'bid' THEN NEW.amount * NEW.price ELSE NEW.amount END)
RETURNING hold_id
INTO NEW.hold_id;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER create_hold
BEFORE INSERT ON exchange.order
FOR EACH ROW
EXECUTE PROCEDURE exchange.order_insert_create_hold_trigger();
-- Update hold on update
CREATE FUNCTION exchange.order_update_hold()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.amount = 0 THEN
NEW.hold_id := NULL;
ELSIF
UPDATE hold
SET amount = (CASE WHEN _side = 'bid' THEN NEW.amount * NEW.price ELSE NEW.amount END)
WHERE hold_id = NEW.hold_id;
END IF;
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER update_hold
BEFORE UPDATE OF amount ON exchange.order
FOR EACH ROW
EXECUTE PROCEDURE exchange.order_update_hold();
-- Delete hold when amount
CREATE FUNCTION exchange.order_delete_hold()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM hold
WHERE hold_id = OLD.hold_id;
RETURN NULL;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER update_hold
BEFORE UPDATE OF amount ON exchange.order
FOR EACH ROW
WHEN (OLD.hold_id IS NOT NULL AND NEW.hold_id IS NULL)
EXECUTE PROCEDURE exchange.order_delete_hold();
-- Order match
CREATE TABLE exchange.match (
match_id SERIAL PRIMARY KEY,
bid_order_id INT NOT NULL REFERENCES exchange.order(order_id),
ask_order_id INT NOT NULL REFERENCES exchange.order(order_id),
created_at TIMESTAMPTZ NOT NULL DEFAULT(now()),
amount exchange.order_amount NOT NULL CHECK (amount > 0),
price exchange.order_price NOT NULL,
-- TODO: Market id?
);
CREATE FUNCTION exchange.match_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
_bid exchange.order%ROWTYPE;
_ask exchange.order%ROWTYPE;
BEGIN
SELECT *
INTO _bid
FROM exchange.order
WHERE order_id = NEW.bid_order_id
FOR SHARE;
SELECT *
INTO _ask
FROM exchange.order
WHERE order_id = NEW.ask_order_id
FOR SHARE;
NEW.price := CASE WHEN NEW.bid_order_id < NEW.ask_order_id THEN _bid.price ELSE _ask.price END;
NEW.amount := MIN(_bid.amount, _ask.amount);
-- Reduce amounts (causes hold reductions)
UPDATE "order"
SET
amount = amount - NEW.amount,
matched = matched + NEW.amount
WHERE order_id IN (NEW.bid_order_id, NEW.ask_order_id);
-- Perform swaps
INSERT INTO transfer (debit_account_id, credit_account_id, amount)
VALUES
(_bid.source_account_id, _ask.dest_account_id, NEW.amount * NEW.price),
(_ask.source_account_id, _bid.dest_account_id, NEW.amount);
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER insert_trigger
BEFORE INSERT ON exchange.match
FOR EACH ROW
EXECUTE PROCEDURE exchange.match_insert_trigger();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.