/*
	FVLUG - PostgreSQL 
	Database schema by Wim Kerkhoff
	wim@nyetwork.org
	January 13, 2003
	Tested with PostgreSQL 7.3.1
*/

DROP DATABASE fvlug;
CREATE DATABASE fvlug;
\connect fvlug

BEGIN;


--tables
create table authors (
	author_id serial primary key not null,
	name varchar unique not null,
	age int not null
		constraint min_age check (age > 5) 
		constraint max_age check (age < 120),
	phone varchar
		constraint min_length check (length(phone)>= 7)
);

create table books (
	book_id serial primary key not null,
	title varchar not null,
	published timestamp,
	author_id int references authors not null,
	price float not null check (price > 0),
	unique (title, author_id)
);


-- add sample data
INSERT into authors (name, age, phone) values ('Bob', 50, '795-1234');
INSERT into authors (name, age, phone) values ('Tim!', 30, '805-8888');

INSERT into books (title, published, author_id, price) values (
	'All about PostgreSQL',
	'2003-01-05',
	(select author_id from authors where name like 'Bob%'),
	30.00
);
INSERT into books (title, published, author_id, price) values (
	'All about Linux Databases',
	'2001-03-16',
	(select min(author_id) from authors where phone = '805-8888'),
	49.99
);

-- make sure it exists
select * from authors;
select * from books;

-- create a view
CREATE VIEW booklist as 
SELECT 
	books.title, books.published, authors.name, authors.age, books.price
FROM
	authors, books
WHERE
	authors.author_id = books.author_id;	

select * from booklist;
select title, name from booklist where age > 40;

-- send: book title, author name, new price
CREATE OR REPLACE FUNCTION set_book_price (varchar, varchar, float) RETURNS float AS '
	UPDATE books 
	SET price = $3
	WHERE 
		lower(title) like lower($1) AND
		author_id = (
			select author_id from authors where lower(name) like lower($2)
		);
	select price from books WHERE 
		lower(title) like lower($1) AND
		author_id = (
			select author_id from authors where lower(name) like lower($2)
		);
' LANGUAGE SQL;

-- send: book_id, new price
CREATE OR REPLACE FUNCTION set_book_price (int, float) RETURNS float AS '
	UPDATE books 
		SET price = $2
		WHERE book_id = $1;
	SELECT price
		FROM books
		WHERE book_id = $1;
' LANGUAGE SQL;

CREATE or replace FUNCTION b_booklist() RETURNS SETOF books as '
	select * from books where price > 30;
	' LANGUAGE SQL;


-- test our functions
select * from books;
select set_book_price('All about PostgreSQL', 'Bob', 14.00);
select set_book_price(2, 5.95);
select title, price from booklist;

-- we're done. commit.
END;


/*
-- index tuning
explain analyze select title, name from booklist where age > 40;
CREATE index authors_age_idx on authors (age);
CREATE index authors_phone_idx on authors (phone);
explain analyze select title, name from booklist where age > 40;
*/

--THESE will give ERRORS and abort the transaction:
--INSERT into authors (name, age, phone) values ('Bob', 3, '911');
--INSERT into authors (name, age, phone) values ('Bob', 140, '911');
--INSERT into authors (name, age, phone) values ('Bob', 30, '911');
--select * from booklist where author_id > 40;
/*
INSERT into books (title, published, author_id) values (
	'All about Linux Databases',
	'2001-03-16',
	(select min(author_id) from authors where phone = '911')
);
*/