-- To create the database and the schema:
-- sudo -u postgres createdb --owner rester --encoding utf8 dummyregistry
-- sudo -u postgres createlang plpgsql dummyregistry
-- sudo -u postgres psql --user rester -f create.sql dummyregistry

CREATE TABLE Words (
        id SERIAL UNIQUE NOT NULL,
        word TEXT UNIQUE NOT NULL, -- Unicode
        origin INET NOT NULL, -- IP address of the client
        useragent TEXT,
        created TIMESTAMP NOT NULL DEFAULT now(),
        updated TIMESTAMP NOT NULL DEFAULT now(),
        comments TEXT);

-- Automatically update the field "updated" so it always reflect the
-- reality
CREATE OR REPLACE FUNCTION last_updated() RETURNS TRIGGER
   AS 'BEGIN
         NEW.updated = now();
         RETURN NEW;
       END;'        
    LANGUAGE PLPGSQL;

CREATE TRIGGER last_updated
   BEFORE UPDATE ON Words
   FOR EACH ROW
   EXECUTE PROCEDURE last_updated();

CREATE INDEX creation_idx ON Words (created);
CREATE INDEX update_idx ON Words (updated);

GRANT SELECT ON Words TO stephane;