94 lines
2.7 KiB
PL/PgSQL
94 lines
2.7 KiB
PL/PgSQL
CREATE TYPE node_type AS ENUM ('marker', 'restaurant');
|
|
CREATE EXTENSION postgis;
|
|
|
|
CREATE TABLE nodes(
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
node_type NODE_TYPE NOT NULL,
|
|
coordinates GEOMETRY(POINT,4326) NOT NULL
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION check_linked_restaurants()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Check if the node is being updated from 'restaurant' to a different type
|
|
IF OLD.node_type = 'restaurant' AND NEW.node_type <> 'restaurant' THEN
|
|
-- Check if the node still has linked restaurants
|
|
IF EXISTS (SELECT 1 FROM restaurants WHERE node_id = OLD.id) THEN
|
|
RAISE EXCEPTION 'Cannot change node type from "restaurant" as it still has linked restaurants';
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER check_linked_restaurants_trigger
|
|
BEFORE UPDATE OF node_type ON nodes
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION check_linked_restaurants();
|
|
|
|
|
|
CREATE TABLE restaurants(
|
|
id SERIAL PRIMARY KEY,
|
|
node_id INTEGER REFERENCES nodes(id) NOT NULL,
|
|
address VARCHAR(30) NOT NULL,
|
|
price SMALLINT CHECK ( price >= 0 AND price <= 10 ) NOT NULL
|
|
);
|
|
|
|
CREATE FUNCTION check_node_type()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Check if the node referenced by the foreign key is of type 'restaurant'
|
|
IF (SELECT node_type FROM nodes WHERE id = NEW.node_id) <> 'restaurant' THEN
|
|
RAISE EXCEPTION 'The referenced node has not type "restaurant"';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER enforce_node_type
|
|
BEFORE INSERT OR UPDATE ON restaurants
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION check_node_type();
|
|
|
|
CREATE TABLE images(
|
|
id SERIAL PRIMARY KEY,
|
|
url VARCHAR(50) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE users(
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(20) NOT NULL,
|
|
uuid UUID NOT NULL,
|
|
avatar_id INTEGER REFERENCES images(id),
|
|
|
|
UNIQUE(uuid)
|
|
);
|
|
|
|
CREATE TABLE restaurant_ratings(
|
|
id SERIAL PRIMARY KEY,
|
|
restaurant_id INTEGER REFERENCES restaurants(id) NOT NULL,
|
|
user_id INTEGER REFERENCES users(id) NOT NULL,
|
|
note VARCHAR(100) NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE meals(
|
|
id SERIAL PRIMARY KEY,
|
|
restaurant_rating_id INTEGER REFERENCES restaurant_ratings(id) NOT NULL,
|
|
user_id INTEGER REFERENCES users(id) NOT NULL,
|
|
name VARCHAR(30) NOT NULL,
|
|
rating SMALLINT CHECK ( rating >= 0 AND rating <= 10 ) NOT NULL,
|
|
price DECIMAL(12,2) CHECK (price >= 0) NOT NULL,
|
|
price_currency VARCHAR(1) CHECK (TRIM(price_currency) <> '') NOT NULL,
|
|
note VARCHAR(100) NOT NULL DEFAULT '',
|
|
date DATE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE meal_images(
|
|
image_id INTEGER NOT NULL,
|
|
meal_id INTEGER NOT NULL,
|
|
CONSTRAINT image_id_constraint FOREIGN KEY (image_id) REFERENCES images(id),
|
|
CONSTRAINT meal_id_constraint FOREIGN KEY (meal_id) REFERENCES meals(id),
|
|
PRIMARY KEY(image_id,meal_id)
|
|
);
|
|
|