--
-- SQL to create tables for example mod_perl application
--

--
-- $Id$
--

--
-- Clean up existing tables and indexen
-- WARNING: THIS WILL REMOVE ANY DATA IN THE DATABASE
-- WHEN TABLES ARE DROPPED.
--
-- UNCOMMENT THE FOLLOWING LINES TO ENABLE CLEANUP.
--

-- drop index docs_path;
-- drop index docs_hits;
-- drop index docs_rate_hits;
-- drop index users_byuser;
-- drop table documents;
-- drop table users;

--
-- Create the table to hold document info
--
create table documents (
	path	varchar,	-- Path relative our topten root
	title	varchar,	-- Title of document
	hits	int4,		-- Hits for document
	rating	float4,		-- User's average rating of document
	raters	int4		-- Number of submitted ratings
);

--
-- Make sure that each path is unique
--
create unique index docs_path on documents
	( path );

--
-- Indexen to speed up retrieving ordered by hits and ratings
--
create index docs_hits on documents
	( hits );
create index docs_rate_hits on documents
	( rating, hits );

-- 
-- User table for authentication
--
create table users (
	username 	char16,
	password	char16
);

-- Index to speed up access
create unique index users_byuser on users
	( username );

--
-- Grant permissions to our apache user
--
grant select,insert,update on documents to ap_auth;
grant select,insert,update on users to ap_auth;