-- This schema contains only the commands necessary to upgrade 
-- a SQL database created by the phpwiki 1.3.10 debian package to the
-- schema used by the 1.3.11p1 release of PHPwiki.
-- 
-- Author:  Matt Brown <debian@mattb.net.nz>
-- Date: 15 Oct 2005

-- Debian's Current sqlite lacks ALTER TABLE support, so use the following hack
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE page_backup(id,pagename,hits,pagedata);
INSERT INTO page_backup SELECT * FROM page;
DROP TABLE page;
CREATE TABLE page (
	id              INTEGER PRIMARY KEY,
	pagename        VARCHAR(100) NOT NULL,
	hits            INTEGER NOT NULL DEFAULT 0,
	pagedata        MEDIUMTEXT NOT NULL DEFAULT '',
	cached_html 	MEDIUMTEXT
);
CREATE UNIQUE INDEX page_index ON page (pagename);
INSERT INTO page (id, pagename, hits, pagedata) SELECT * FROM page_backup;
DROP TABLE page_backup;
COMMIT;

-- Update Session Table (breaks any existing sessions)
DROP TABLE session;
CREATE TABLE session (
	sess_id   CHAR(32) NOT NULL DEFAULT '' PRIMARY KEY,
	sess_data MEDIUMTEXT NOT NULL,
	sess_date INTEGER UNSIGNED NOT NULL,
	sess_ip   CHAR(40) NOT NULL
);
CREATE INDEX sessdate_index ON session (sess_date);
CREATE INDEX sessip_index ON session (sess_ip);

-- Add the rating and accesslog tables in case users want to use those themes
CREATE TABLE rating (
        dimension TINYINTEGER NOT NULL,
        raterpage INTEGER NOT NULL,
        rateepage INTEGER NOT NULL,
        ratingvalue FLOAT NOT NULL,
        rateeversion INTEGER NOT NULL,
        tstamp INTEGER UNSIGNED NOT NULL,
        PRIMARY KEY (dimension, raterpage, rateepage)
);
CREATE INDEX rating_dimension ON rating (dimension);
CREATE INDEX rating_raterpage ON rating (raterpage);
CREATE INDEX rating_rateepage ON rating (rateepage);

CREATE TABLE accesslog (
        time_stamp    INTEGER UNSIGNED,
	remote_host   VARCHAR(50),
	remote_user   VARCHAR(50),
        request_method VARCHAR(10),
	request_line  VARCHAR(255),
	request_args  VARCHAR(255),
	request_file  VARCHAR(255),
	request_uri   VARCHAR(255),
	request_time  CHAR(28),
	status 	      TINYINTEGER UNSIGNED,
	bytes_sent    TINYINTEGER UNSIGNED,
        referer       VARCHAR(255), 
	agent         VARCHAR(255),
	request_duration FLOAT
);
CREATE INDEX log_time ON accesslog (time_stamp);
CREATE INDEX log_host ON accesslog (remote_host);
