Skip to content

Instantly share code, notes, and snippets.

@d630
Last active August 26, 2018 19:28
Show Gist options
  • Save d630/cea13607aa8bbc5e73cf016e2e90aec0 to your computer and use it in GitHub Desktop.
Save d630/cea13607aa8bbc5e73cf016e2e90aec0 to your computer and use it in GitHub Desktop.
bestellando
START TRANSACTION;
SET foreign_key_checks=0;
DROP DATABASE IF EXISTS bestellando;
CREATE DATABASE bestellando;
USE bestellando;
CREATE TABLE dish (
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uuid BIGINT UNSIGNED NULL,
name VARCHAR(40),
note VARCHAR(255),
price DOUBLE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE ttable (
id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uuid BIGINT UNSIGNED NULL,
num INTEGER
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE oorder (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uuid BIGINT UNSIGNED NULL,
ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ttable_id TINYINT UNSIGNED,
dish_id TINYINT UNSIGNED,
is_paid BOOLEAN DEFAULT FALSE,
is_servable BOOLEAN DEFAULT FALSE,
is_served BOOLEAN DEFAULT FALSE,
FOREIGN KEY(dish_id) REFERENCES dish(id),
FOREIGN KEY(ttable_id) REFERENCES ttable(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE backlog (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
oorder_ctime TIMESTAMP NULL,
oorder_uuid BIGINT UNSIGNED,
ttable_uuid BIGINT UNSIGNED,
dish_uuid BIGINT UNSIGNED
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- STORED PROCS AND FUNCS
DELIMITER ;;
CREATE PROCEDURE get_all_dishes
()
BEGIN
SELECT id, name, note, price
FROM dish;
END;;
CREATE PROCEDURE get_all_dishes_short
()
BEGIN
SELECT id, name
FROM dish;
END;;
CREATE PROCEDURE get_all_tables
()
BEGIN
SELECT id, num
FROM ttable;
END;;
CREATE PROCEDURE set_order
(IN _ttable_id TINYINT UNSIGNED, _dish_id TINYINT UNSIGNED)
BEGIN
INSERT INTO oorder(ttable_id, dish_id)
VALUES(_ttable_id, _dish_id);
END;;
CREATE PROCEDURE get_all_unservable_orders
()
BEGIN
SELECT oorder.id, oorder.ttable_id, ttable.num AS ttable_num, dish.name
FROM oorder, dish, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.dish_id = dish.id
AND oorder.is_servable = 0;
END;;
CREATE PROCEDURE get_all_unservable_tables
()
BEGIN
SELECT oorder.ttable_id, ttable.num
FROM oorder, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.is_servable = 0;
END;;
CREATE PROCEDURE get_all_ready_orders
()
BEGIN
SELECT oorder.id, oorder.ttable_id, ttable.num AS ttable_num, dish.name
FROM oorder, dish, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.dish_id = dish.id
AND oorder.is_servable = 1
AND oorder.is_served = 0;
END;;
CREATE PROCEDURE get_all_ready_tables
()
BEGIN
SELECT oorder.ttable_id, ttable.num
FROM oorder, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.is_servable = 1
AND oorder.is_served = 0;
END;;
CREATE PROCEDURE get_all_unpaid_orders
()
BEGIN
SELECT oorder.id, oorder.ttable_id, ttable.num AS ttable_num, dish.name
FROM oorder, dish, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.dish_id = dish.id
AND oorder.is_paid = 0;
END;;
CREATE PROCEDURE get_all_unpaid_tables
()
BEGIN
SELECT oorder.ttable_id, ttable.num
FROM oorder, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.is_paid = 0;
END;;
CREATE PROCEDURE get_all_unpaid_orders_with_price
()
BEGIN
SELECT oorder.id, oorder.ttable_id, ttable.num AS ttable_num, dish.name, dish.price
FROM oorder, dish, ttable
WHERE oorder.ttable_id = ttable.id
AND oorder.dish_id = dish.id
AND oorder.is_paid = 0;
END;;
CREATE PROCEDURE delete_unpaid_order
(IN _id INTEGER UNSIGNED)
BEGIN
DELETE
FROM oorder
WHERE id = _id;
END;;
CREATE PROCEDURE update_unservable_order
(IN _id INTEGER UNSIGNED)
BEGIN
UPDATE oorder
SET is_servable = 1
WHERE id = _id;
CALL on_ready(_id);
END;;
CREATE PROCEDURE update_unserved_order
(IN _id INTEGER UNSIGNED)
BEGIN
UPDATE oorder
SET is_served = 1
WHERE id = _id;
CALL on_ready(_id);
END;;
CREATE PROCEDURE update_unpaid_order
(IN _id INTEGER UNSIGNED)
BEGIN
UPDATE oorder
SET is_paid = 1
WHERE id = _id;
CALL on_ready(_id);
END;;
CREATE PROCEDURE on_ready
(IN _id INTEGER UNSIGNED)
BEGIN
IF is_ready(_id) THEN
CALL move_order(_id);
END IF;
END;;
CREATE FUNCTION is_ready
(_id INTEGER UNSIGNED)
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
DECLARE ret BOOLEAN;
SELECT is_servable AND is_served AND is_paid
INTO ret
FROM oorder
WHERE id = _id;
return ret;
END;;
CREATE PROCEDURE move_order
(IN _id INTEGER UNSIGNED)
BEGIN
INSERT INTO backlog (oorder_ctime, oorder_uuid, ttable_uuid, dish_uuid)
SELECT oorder.ctime, oorder.uuid, ttable.uuid, dish.uuid
FROM oorder, ttable, dish
WHERE oorder.id = _id
AND oorder.ttable_id = ttable.id
AND oorder.dish_id = dish.id;
DELETE
FROM oorder
WHERE id = _id;
END;;
DELIMITER ;
-- TRIGGERS.
DELIMITER ;;
CREATE TRIGGER before_insert_dish
BEFORE INSERT ON dish
FOR EACH ROW
BEGIN
IF new.uuid IS NULL THEN
SET new.uuid = UUID_SHORT();
END IF;
END;;
CREATE TRIGGER before_insert_ttable
BEFORE INSERT ON ttable
FOR EACH ROW
BEGIN
IF new.uuid IS NULL THEN
SET new.uuid = UUID_SHORT();
END IF;
END;;
CREATE TRIGGER before_insert_oorder
BEFORE INSERT ON oorder
FOR EACH ROW
BEGIN
IF new.uuid IS NULL THEN
SET new.uuid = UUID_SHORT();
END IF;
END;;
DELIMITER ;
-- INSERTS.
INSERT INTO dish (name, note, price)
VALUES ('dish0', 'foo bar', 5.99),
('dish1', 'foo bar', 5.99),
('dish2', 'foo bar', 12.99),
('dish3', 'foo bar', 13.99),
('dish4', 'foo bar', 1.99),
('dish5', 'foo bar', 2.99),
('dish6', 'foo bar', 5.99),
('dish7', 'foo bar', 8.99),
('dish8', 'foo bar', 5.99),
('dish9', 'foo bar', 9.99);
INSERT INTO ttable (num)
VALUES(1),
(2),
(3),
(4),
(5),
(6),
(7),
(9),
(15);
INSERT INTO oorder (ttable_id, dish_id)
VALUES (1, 9),
(1, 2),
(1, 3),
(3, 4),
(3, 2),
(7, 1),
(9, 2);
COMMIT;
-- vim: set ft=sql :
START TRANSACTION;
CREATE USER 'bestellando'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON bestellando.* TO 'bestellando'@'localhost';
FLUSH PRIVILEGES;
COMMIT;
-- vim: set ft=sql :
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment