Created
November 8, 2012 18:36
-
-
Save abrkn/4040642 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE IF EXISTS "user" CASCADE; | |
DROP TABLE IF EXISTS match CASCADE; | |
DROP TABLE IF EXISTS transfer CASCADE; | |
DROP TABLE IF EXISTS currency CASCADE; | |
DROP TABLE IF EXISTS account CASCADE; | |
DROP TABLE IF EXISTS "order" CASCADE; | |
DROP DOMAIN IF EXISTS account_volume CASCADE; | |
DROP DOMAIN IF EXISTS order_price CASCADE; | |
DROP DOMAIN IF EXISTS order_volume CASCADE; | |
CREATE DOMAIN account_volume AS numeric(18, 8); | |
CREATE DOMAIN order_price AS numeric(15, 5); | |
CREATE DOMAIN order_volume AS numeric(13, 3); | |
CREATE TABLE currency ( | |
currency_id char(3) PRIMARY KEY | |
); | |
CREATE TABLE "user" ( | |
user_id SERIAL PRIMARY KEY, | |
username varchar(20) NOT NULL, | |
username_lower varchar(20) NOT NULL UNIQUE, | |
password varchar(60) NOT NULL | |
); | |
CREATE OR REPLACE FUNCTION user_update_username_lower() RETURNS trigger AS $$ | |
BEGIN | |
NEW.username_lower := lower(NEW.username); | |
RETURN NEW; | |
END; $$ LANGUAGE plpgsql; | |
CREATE TRIGGER user_insert | |
BEFORE INSERT OR UPDATE ON "user" | |
FOR EACH ROW | |
EXECUTE PROCEDURE user_update_username_lower(); | |
CREATE TABLE account ( | |
account_id SERIAL PRIMARY KEY, | |
currency_id char(3) NOT NULL REFERENCES currency(currency_id), | |
user_id int REFERENCES "user"(user_id), | |
balance account_volume NOT NULL DEFAULT 0, | |
created timestamp NOT NULL DEFAULT now(), | |
special varchar(100) | |
CHECK (user_id IS NULL OR special IS NULL), | |
CHECK (special IS NOT NULL OR balance >= 0) | |
); | |
CREATE TABLE transfer ( | |
transfer_id SERIAL PRIMARY KEY, | |
from_account_id int NOT NULL REFERENCES account(account_id), | |
to_account_id int NOT NULL REFERENCES account(account_id), | |
volume account_volume NOT NULL CHECK (volume > 0), | |
CHECK(from_account_id <> to_account_id) | |
); | |
CREATE TABLE "order" ( | |
order_id SERIAL PRIMARY KEY, | |
user_id int NOT NULL REFERENCES "user"(user_id), | |
have char(3) NOT NULL REFERENCES currency(currency_id), | |
want char(3) NOT NULL REFERENCES currency(currency_id), | |
price order_price NOT NULL CHECK (price > 0), | |
volume order_volume NOT NULL CHECK (volume >= 0), | |
CHECK (have <> want) | |
); | |
CREATE OR REPLACE FUNCTION account_for_user_currency ( | |
uid int, | |
currency char(3) | |
) RETURNS int AS $$ | |
DECLARE | |
res int; | |
BEGIN | |
SELECT account_id INTO res | |
FROM account | |
WHERE user_id = uid; | |
IF NOT FOUND THEN | |
INSERT INTO account (user_id, currency_id) VALUES (uid, currency); | |
res := currval('account_account_id_seq'); | |
END IF; | |
RETURN res; | |
END; | |
$$ LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION special_account ( | |
name varchar(100), | |
currency char(3) | |
) RETURNS int AS $$ | |
DECLARE | |
res int; | |
BEGIN | |
SELECT account_id INTO res | |
FROM account a | |
WHERE special = name AND currency = a.currency_id; | |
IF NOT FOUND THEN | |
INSERT INTO account (special, currency_id) VALUES (name, currency); | |
res := currval('account_account_id_seq'); | |
END IF; | |
RETURN res; | |
END; $$ LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION order_transfer() RETURNS trigger AS $$ | |
BEGIN | |
INSERT INTO transfer (from_account_id, to_account_id, volume) | |
VALUES ( | |
account_for_user_currency(NEW.user_id, NEW.have), | |
special_account('orders', NEW.have), | |
NEW.volume | |
); | |
RETURN NEW; | |
END; $$ LANGUAGE plpgsql; | |
CREATE TRIGGER order_insert_transfer | |
BEFORE INSERT ON "order" | |
FOR EACH ROW | |
EXECUTE PROCEDURE order_transfer(); | |
CREATE OR REPLACE FUNCTION transfer_update_balances() RETURNS trigger AS $$ | |
BEGIN | |
UPDATE account SET balance = balance - NEW.volume | |
WHERE account_id = NEW.from_account_id; | |
UPDATE account SET balance = balance + NEW.volume | |
WHERE account_id = NEW.to_account_id; | |
RETURN NEW; | |
END; $$ LANGUAGE plpgsql; | |
CREATE TRIGGER transfer_insert | |
BEFORE INSERT ON transfer | |
FOR EACH ROW | |
EXECUTE PROCEDURE transfer_update_balances(); | |
CREATE TABLE match ( | |
match_id SERIAL PRIMARY KEY, | |
created timestamp NOT NULL DEFAULT now(), | |
left_order_id int NOT NULL REFERENCES "order"(order_id), | |
right_order_id int NOT NULL REFERENCES "order"(order_id), | |
left_volume order_volume NOT NULL CHECK (left_volume > 0), | |
right_volume order_volume NOT NULL CHECK (right_volume > 0) | |
); | |
CREATE OR REPLACE FUNCTION match_order () RETURNS trigger AS $$ | |
DECLARE | |
lefto "order"%ROWTYPE; | |
righto "order"%ROWTYPE; | |
leftprice order_price; | |
leftvol order_volume; | |
rightvol order_volume; | |
BEGIN | |
IF NEW.volume = 0 THEN | |
RAISE EXCEPTION 'order inserted with zero volume'; | |
END IF; | |
RAISE NOTICE 'matching order %', NEW.order_id; | |
SELECT * INTO lefto FROM "order" o WHERE o.order_id = NEW.order_id; | |
SELECT * INTO righto FROM "order" o | |
WHERE | |
o.have = lefto.want AND | |
o.want = lefto.have AND | |
o.volume > 0 | |
ORDER BY | |
o.price ASC, o.order_id ASC; | |
IF NOT FOUND THEN | |
RAISE NOTICE 'nothing to match order % with', NEW.order_id; | |
RETURN NEW; | |
END IF; | |
RAISE NOTICE 'left order % will be matched with right order %', NEW.order_id, righto.order_id; | |
leftprice := ceil(1 / righto.price * 10^5) / 10^5; | |
IF leftprice * lefto.volume <= righto.volume THEN | |
RAISE NOTICE 'left order will be fulfilled'; | |
leftvol = lefto.volume; | |
rightvol = lefto.volume * leftprice; | |
ELSE | |
RAISE NOTICE 'right order will be fulfilled'; | |
rightvol = righto.volume; | |
leftvol = righto.volume * righto.price; | |
END IF; | |
RAISE NOTICE 'inserting match. left vol %, right vol %', leftvol, rightvol; | |
INSERT INTO "match" (left_order_id, right_order_id, left_volume, right_volume) | |
VALUES (lefto.order_id, righto.order_id, leftvol, rightvol); | |
IF leftvol < lefto.volume THEN | |
SELECT match_order(NEW.order_id); | |
END IF; | |
RETURN NEW; | |
END; $$ LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION update_orders_and_transfer_from_match() RETURNS trigger AS $$ | |
DECLARE | |
lefto "order"%ROWTYPE; | |
righto "order"%ROWTYPE; | |
BEGIN | |
UPDATE "order" | |
SET volume = volume - NEW.left_volume | |
WHERE order_id = NEW.left_order_id; | |
UPDATE "order" | |
SET volume = volume - NEW.right_volume | |
WHERE order_id = NEW.right_order_id; | |
SELECT * INTO lefto FROM "order" WHERE order_id = NEW.left_order_id; | |
SELECT * INTO righto FROM "order" WHERE order_id = NEW.right_order_id; | |
INSERT INTO transfer (from_account_id, to_account_id, volume) | |
VALUES | |
( | |
special_account('orders', lefto.want), | |
account_for_user_currency(lefto.user_id, lefto.want), | |
NEW.right_volume | |
), | |
( | |
special_account('orders', righto.want), | |
account_for_user_currency(righto.user_id, righto.want), | |
NEW.left_volume | |
); | |
RETURN NEW; | |
END; $$ LANGUAGE plpgsql; | |
CREATE TRIGGER match_inserted | |
AFTER INSERT ON "match" | |
FOR EACH ROW | |
EXECUTE PROCEDURE update_orders_and_transfer_from_match(); | |
CREATE TRIGGER order_insert_match | |
AFTER INSERT ON "order" | |
FOR EACH ROW | |
EXECUTE PROCEDURE match_order(); | |
INSERT INTO currency (currency_id) VALUES ('BTC'); | |
INSERT INTO currency (currency_id) VALUES ('USD'); | |
INSERT INTO "user" (username, password) VALUES ('abrkn', 'pass'); | |
INSERT INTO "user" (username, password) VALUES ('simen', 'pass'); | |
SELECT * FROM "user"; | |
INSERT INTO transfer (from_account_id, to_account_id, volume) | |
VALUES (special_account('faucet', 'BTC'), account_for_user_currency(1, 'BTC'), 5); | |
INSERT INTO transfer (from_account_id, to_account_id, volume) | |
VALUES (special_account('faucet', 'USD'), account_for_user_currency(2, 'USD'), 25); | |
INSERT INTO "order" (user_id, have, want, price, volume) | |
VALUES (2, 'USD', 'BTC', 0.08333, 5.25); | |
INSERT INTO "order" (user_id, have, want, price, volume) | |
VALUES (2, 'USD', 'BTC', 0.081, 1); | |
INSERT INTO "order" (user_id, have, want, price, volume) | |
VALUES (1, 'BTC', 'USD', 11, 1.5); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
output: