-- This schema contains only the commands necessary to upgrade 
-- a Pg/MySQL 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: 8 Jul 2006

-- Add the cached_html column
ALTER TABLE page ADD COLUMN cached_html TEXT;
ALTER TABLE page ALTER COLUMN cached_html SET DEFAULT '';

-- Update Session Table (breaks any existing sessions)
DROP TABLE session;
CREATE TABLE session (
        sess_id         CHAR(32) PRIMARY KEY,
        sess_data       TEXT NOT NULL,
        sess_date       INT,
        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 INTEGER NOT NULL,
        raterpage BIGINT NOT NULL,
        rateepage BIGINT NOT NULL,
        ratingvalue FLOAT NOT NULL,
        rateeversion BIGINT NOT NULL,
        tstamp TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX rating_id ON rating (dimension, raterpage, rateepage);

-- if ACCESS_LOG_SQL > 0
-- only if you need fast log-analysis (spam prevention, recent referrers)
-- see http://www.outoforder.cc/projects/apache/mod_log_sql/docs-2.0/#id2756178
CREATE TABLE accesslog (
        time_stamp    INT,
        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        SMALLINT,
        bytes_sent    SMALLINT,
        referer       VARCHAR(255),
        agent         VARCHAR(255),
        request_duration FLOAT
);
CREATE INDEX accesslog_time ON accesslog (time_stamp);
CREATE INDEX accesslog_host ON accesslog (remote_host);
-- create extra indices on demand (usually referer. see plugin/AccessLogSql)
