-- Run psql by typing
--   psql template1
-- to connect to the template1 database.  Next, type

CREATE DATABASE bookings;

-- to create a database called "bookings".  You will need to have
-- permission to create a database.  If you do not, change to the
-- postgres system user, run psql as above, and then run

CREATE DATABASE bookings WITH OWNER dbusername;

-- where dbusername is the user you created with the createuser command.

-- Set the display of null values so that we can see them:
\pset null '(null)'


-- Create some custom types that we will use in later tables.  This
-- means if we need to change the size of the types at a later point,
-- we only need to alter the domain (ALTER DOMAIN) rather than
-- altering every single table.

-- From 0.00% to 100.00%
CREATE DOMAIN custom_percent AS numeric(5,2)
  CHECK (VALUE >= 0.00 AND VALUE <= 100.00);

-- Up £9999.99
CREATE DOMAIN price AS numeric(6,2);

-- Create the customers table.
-- PRIMARY KEY makes this column a primary key, for use in FOREIGN KEY
-- references from other tables.
-- NOT NULL requires that a value be present in this column.
-- UNIQUE specifies that all the values in this column must be unique.
-- DEFAULT sets a default value to use if we don't specify one of our own.

CREATE TABLE customers (
  id serial PRIMARY KEY,
  name varchar(30) NOT NULL,
  telephone varchar(15) UNIQUE,
  discount custom_percent NOT NULL DEFAULT 0.00
);

-- Insert some example customers.
-- Note that we don't need to specify every column.  See how the NOT
-- NULL and DEFAULT constraints have worked.

INSERT INTO customers (name) VALUES ('Guest');
INSERT INTO customers (name, telephone) VALUES ('Leigh', '01254 664642');
INSERT INTO customers (name, telephone) VALUES ('Edwards', '01214 543262');
INSERT INTO customers (name, telephone) VALUES ('Mavis', '02454 343232');
INSERT INTO customers (name, telephone, discount) VALUES ('Coulter', '01854 234342', 15.00);
INSERT INTO customers (name, telephone) VALUES ('Baister', '01154 988323');

-- Now there is some data, try out some simple queries on them.
-- SELECT is the SQL command to extract data from tables.

-- Display everything.
SELECT * FROM customers;

-- Display just two columns.
SELECT name, telephone FROM customers;

-- Display just one customer.
SELECT * FROM customers WHERE (name = 'Edwards');

SELECT * FROM customers WHERE (name = 'Mavis' OR telephone = '01154 988323');

-- Combine both concepts.
SELECT name, telephone FROM customers WHERE (name = 'Mavis' OR telephone = '01154 988323');

-- Create some more tables.
CREATE TABLE staff (
  id serial PRIMARY KEY,
  name varchar(30) UNIQUE NOT NULL,
  commission custom_percent NOT NULL DEFAULT 15.00
);

-- Example staff members.
INSERT INTO staff (name) VALUES ('Jaques');
INSERT INTO staff (name) VALUES ('Claude');
INSERT INTO staff (name) VALUES ('Ernie');


-- Tables.  The CHECK constraint ensures that there is always at least
-- one seat at a table (it's not useful to have tables with zero or
-- negative numbers of seats).

CREATE TABLE tables (
  id integer PRIMARY KEY,
  near_window boolean NOT NULL,
  seats integer NOT NULL CHECK (seats >= 1)
);

-- Example tables.
INSERT INTO tables (id, near_window, seats) VALUES (1, TRUE, 4);
INSERT INTO tables (id, near_window, seats) VALUES (2, TRUE, 4);
INSERT INTO tables (id, near_window, seats) VALUES (3, TRUE, 2);
INSERT INTO tables (id, near_window, seats) VALUES (4, TRUE, 4);
INSERT INTO tables (id, near_window, seats) VALUES (5, FALSE, 4);
INSERT INTO tables (id, near_window, seats) VALUES (6, FALSE, 10);

-- This is a SELECT query just like before.  We added the ORDER BY
-- keyword to sort the rows by the value of the "id" column.  In a
-- relational database, the rows in a table have /no/ implicit
-- ordering.  Order is only imposed when we request that it be
-- ordered.  This is sorted in ascending order.

SELECT * FROM tables WHERE (seats = 4 AND near_window = TRUE) ORDER BY id;

-- Non-standard SQL extensions to limit the number of rows returned.
-- There is a standard (SQL99) way of doing this, but Postgres doesn't
-- support it yet.
SELECT * FROM tables WHERE (seats = 4 AND near_window = TRUE) ORDER BY id LIMIT 2;
SELECT * FROM tables WHERE (seats = 4 AND near_window = TRUE) ORDER BY id LIMIT 2 OFFSET 1;

-- We want to categorise the items on the menu (for example, to assist
-- in automatically generating a printed copy of the menu).  This
-- table contains the menu types.

CREATE TABLE menu_item_types (
  id serial PRIMARY KEY,
  name varchar(15) UNIQUE NOT NULL
);

INSERT INTO menu_item_types (name) VALUES ('starter');
INSERT INTO menu_item_types (name) VALUES ('main course');
INSERT INTO menu_item_types (name) VALUES ('side order');
INSERT INTO menu_item_types (name) VALUES ('dessert');
INSERT INTO menu_item_types (name) VALUES ('beverage');


-- Next, the menu itself.  This is the same as previous tables but
-- with one exception.  "item_type" is a FOREIGN KEY reference to
-- another table.  This means that the number in this column *must*
-- also exist in the id column of a row in the menu_item_types table.
-- Enforcing this relationship maintains "relational integrity".

CREATE TABLE menu (
  id serial PRIMARY KEY,
  item varchar(40) UNIQUE NOT NULL,
  description text,
  item_type integer NOT NULL REFERENCES menu_item_types(id),
  price price NOT NULL CHECK (price > 0.00)
);

-- The data for this table is contained within the file menuitems.sql
-- In psql, use the command
--   \i menuitems.sql
-- to process all the SQL commands in the file.  The insert statements
-- in this file are different to those used previously, so do take a
-- look at it.

-- Views
-- A view is a saved SELECT query.  This commands creates a view called menu_view.

CREATE VIEW menu_view AS
  SELECT m.item, m.price, i.name AS "type"
    FROM menu AS m
         CROSS JOIN menu_item_types AS i
    WHERE (m.item_type = i.id)
    ORDER BY m.item;

-- The view can be considered as a kind of "virtual table".  Try a SELECT FROM it:
--   SELECT * FROM menu_view;
--   SELECT item, price FROM menu_view WHERE (price > 9.50);

-- The select query introduces a new concept: JOINS.  The join
-- combines the menu and menu_item_types tables together, and selects
-- those rows where the primary key (menu_item_types.id) and foreign
-- key (menu.item_type) match.
-- See set.sql for more information about how joins work.


-- The bookings table stores booking information (which table was
-- booked, who booked it, the number of guests and the time of the
-- booking).  This has three foreign key references.

CREATE TABLE bookings (
  id serial PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  staff_id integer REFERENCES staff(id),
  table_id integer REFERENCES tables(id),
  start_time timestamp without time zone NOT NULL,
  end_time timestamp without time zone NOT NULL CHECK (start_time < end_time),
  guests integer NOT NULL CHECK (guests >= 1)
);

-- The data for the bookings is bookingsdata.sql
-- Run
--   \i bookingsdata.sql
-- to insert the data.  Look at the file to see what it is doing.  As
-- before, it inserts data which is the result of a SELECT query.


-- PostgreSQL can run user-written functions in the database server
-- itself as part of a query.  These are called "stored procedures".
-- One use of stored procedures is to validate data.  This example
-- checks that a booking is sane (it prevents double booking a table,
-- and it also prevents booking a table which cannot seat the full
-- number of guests).

CREATE OR REPLACE FUNCTION validate_booking() RETURNS TRIGGER AS '
  DECLARE
    foundrow bookings%ROWTYPE;
    reftable tables%ROWTYPE;
  BEGIN
    SELECT INTO foundrow * FROM bookings
      WHERE (table_id = NEW.table_id AND
             ((start_time >= NEW.start_time AND
               end_time <= NEW.end_time) OR
              (start_time <= NEW.start_time AND
               end_time >= NEW.end_time)))
      LIMIT 1;
    IF foundrow.id IS NOT NULL THEN
      RAISE EXCEPTION ''The booking overlaps an existing booking for this table.'';
    END IF;

    SELECT INTO reftable * FROM tables
      WHERE (id = NEW.table_id);
    IF reftable.seats < NEW.guests THEN
      RAISE EXCEPTION ''The table does not have enough seats for the number of guests'';
    END IF;

    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

-- We now specify that this "trigger" be called whenever we insert or
-- update a row in the bookings table.

CREATE TRIGGER validate_booking
        BEFORE INSERT OR UPDATE
        ON bookings
        FOR EACH ROW
        EXECUTE PROCEDURE validate_booking();

-- Try this INSERT query to test the trigger.  If you inserted all the
-- data as requested above, this will fail with a descriptive error.

INSERT INTO bookings (customer_id,staff_id,table_id,start_time,end_time,guests)
  SELECT c.id AS "customer_id",
         s.id AS "staff_id",
         t.id AS "table_id",
         '21 Nov 2005 19:30' AS "start_time",
         '21 Nov 2005 21:00' AS "end_time",
         6 AS "guests"
    FROM customers AS c,
         staff as s,
         tables AS t
    WHERE (c.name = 'Leigh' AND
           s.name = 'Claude' AND
           t.id = 5);

-- And a query to test the data...
SELECT c.id AS "customer_id",
       s.id AS "staff_id",
       t.id AS "table_id",
       '20 Nov 2005 18:00' AS "start_time",
       '20 Nov 2005 20:00' AS "end_time"
  FROM customers AS c,
       staff as s,
       tables AS t
  WHERE (c.name = 'Mavis' AND
         s.name = 'Claude' AND
         t.id = 5);

-- Notice that this is a join across three tables, and we specify the
-- row we want from each to choose just that one row from the join
-- (the cartesian product).

-- Because triggers are run on every insert and update, they need to
-- be efficient.  This trigger selects rows based on the table number
-- and the start and end times.  Creating an index across all three
-- columns speeds up this query (at the expense of slowing inserts and
-- updates, which require updating the index):

CREATE INDEX bookings_time_key
  ON bookings USING btree (table_id,start_time,end_time);

-- We now create a new view.  This also demonstrates a variation of
-- table joining.  Before, we used CROSS JOIN.  INNER JOIN is the same
-- concept, but rather than use WHERE to specify the rows we want, we
-- use ON.  This is exactly the same as WHERE, but allows us to use
-- WHERE just to specify the rows we want, and use ON to specify the
-- join conditions separately.  This makes the query more readable,
-- and e.g. make the automatic generation of queries easier if you
-- want to do that.

CREATE VIEW booking_view AS
  SELECT b.id,
         b.start_time,
         b.end_time,
         b.guests,
         c.name AS "customer_name",
         s.name AS "staff_name",
         t.id AS "table_id"
    FROM ((bookings AS b
           INNER JOIN customers AS c ON (c.id = b.customer_id))
           INNER JOIN staff AS s ON (s.id = b.staff_id))
           INNER JOIN tables AS t ON (t.id = b.table_id);

-- It's sometimes the case that we don't want to see all the rows in a
-- result set.  Suppose we want to ask the question "when was the last
-- time each customer booked a table?".  We might start with a query
-- like this:

SELECT c.id,c.name,b.start_time
  FROM customers AS c
       INNER JOIN bookings AS b
         ON (c.id = b.customer_id);

-- This gives use the information, but not in a usable form.  Each
-- customer might appear several times; once for each booking they
-- made, but we are only interested in the last.  SELECT DISTINCT ON
-- can eliminate all but the first duplicate row:

SELECT DISTINCT ON (c.name) c.id,c.name,b.start_time
  FROM customers AS c
       INNER JOIN bookings AS b
         ON (c.id = b.customer_id)
  ORDER BY name ASC,
           start_time DESC;

-- Now each customer name only appears once.  Note that in our
-- database schema, the customer name is not guaranteed to be unique,
-- so

SELECT DISTINCT ON (c.id) c.id,c.name,b.start_time
  FROM customers AS c
       INNER JOIN bookings AS b
         ON (c.id = b.customer_id)
  ORDER BY c.id ASC,
           start_time DESC;

-- would be safer (it just doesn't sort the names alphabetically; we
-- need a subselect for that).

-- Lastly, we'll create an orders table to hold the orders for the
-- guests at a particular table (associated with the booking).

CREATE TABLE orders (
  booking_id integer NOT NULL REFERENCES bookings(id),
  menu_id integer NOT NULL REFERENCES menu(id),
  quantity integer NOT NULL CHECK (quantity >= 1)
);

-- The data is in orderdata.sql.  As before,
--   \i orderdata.sql
-- will run the INSERT queries within it.  Examine them and run them
-- by hand to see what they do.


-- And we'll create a view to tie the order with the menu items.
-- Notice how the inner joins nest.  These work from left-to-right
-- (note the use of brackets).  The two tables (orders and menu) are
-- combined in the first join to give a result set which becomes the
-- table to combine with menu_item_types in the second join.

CREATE VIEW order_view AS
  SELECT o.booking_id AS "booking_id",
         m.item AS "item_name",
         m.price AS "item_price",
	 o.quantity AS "item_quantity",
	 m.price * o.quantity AS "item_total",
         t.name AS "item_type"
    FROM (orders AS o
          INNER JOIN menu AS m
            ON (m.id = o.menu_id))
          INNER JOIN menu_item_types AS t
            ON (m.item_type = t.id);

-- Aggregate functions.  These operate by combining related rows
-- together.  These can computer e.g. total, mean, mode, min, max,
-- stdev etc., and we can write our own functions to do additional
-- calculations.

-- Here we combine all rows where booking_id is the same, and then
-- calculate the total of the item_total column for all those rows.
-- This might be used to calculate the total amount for a bill or
-- receipt.  The key here is the GROUP BY keyword, which specifies
-- which rows to combine.

SELECT booking_id,
       sum(item_total) AS "total"
    FROM booking_view AS b
      INNER JOIN order_view AS o
        ON (b.id = o.booking_id)
      GROUP BY booking_id;

-- Like the WHERE clause in a normal SELECT query, HAVING allows us to
-- choose particular grouped rows:

SELECT booking_id,
       sum(item_total) AS "total"
    FROM booking_view AS b
      INNER JOIN order_view AS o
        ON (b.id = o.booking_id)
      GROUP BY booking_id
        HAVING (sum(item_total) > 50.00);

-- Updating rows.  In addition to inserting rows, we might want to
-- change an existing row (or set of rows).  UPDATE does this.

UPDATE customers SET name = 'Graham' WHERE (name = 'Edwards');

-- If we don't specify the row(s) with WHERE, it will update all the
-- rows in the table.  The value to set might also be an expression
-- rather than a static value, so we could e.g. give all employees a
-- 5% pay rise by increasing a salary column by 5%.

-- Deleting row.  Done using the DELETE keyword.

DELETE FROM customers WHERE (name = 'Graham');


-- Profiling.  Complex queries can take time to execute, and a busy
-- server may be reduced to a crawl if the database work takes up too
-- much time.  It's possible to rewrite queries to execute optimally,
-- but first we need to know where the bottlenecks are.  EXPLAIN can
-- profile an SQL statement and tell you.

EXPLAIN SELECT * FROM menu_view ORDER BY name,price;

-- If the system is forced to do a sequential scan on a large table,
-- consider adding an index to speed up access.

CREATE UNIQUE INDEX menu_item_types_unique_name_key ON menu_item_types USING btree (name);

-- Also vacuum the database to clean up old data and optimise access
-- to commonly-used data.  Modern versions of postgres do this
-- automatically (autovacuum), and with version 8.1, it's part of the
-- core server.

VACUUM ANALYZE;

-- Deleting database objects.  Everything created with CREATE has a
-- corresponding DROP keyword to destroy it:

DROP VIEW menu_view;

DROP TABLE orders;

DROP DATABASE bookings;

-- Use with caution!!