/*DROP DATABASE trololo;*/
/*DROP USER 'troller'@'localhost';*/

CREATE DATABASE trololo;
USE trololo;

CREATE TABLE page (
  id INTEGER NOT NULL,
  fromPage INTEGER,
  title VARCHAR(128),
  txt TEXT,
  PRIMARY KEY(id),
  FOREIGN KEY (fromPage) REFERENCES page(id)
);

CREATE USER 'troller'@'localhost';
GRANT SELECT ON TABLE page TO 'troller';
GRANT INSERT(fromPage,title,txt) ON TABLE page TO 'troller';

/*need sequencer*/

CREATE TABLE Seq (
  id INTEGER,
  name VARCHAR(32),
  PRIMARY KEY(name)
);

INSERT INTO Seq VALUES (0,'page');

DELIMITER //
CREATE TRIGGER newPage BEFORE INSERT ON page
  FOR EACH ROW BEGIN
    SET NEW.id = (SELECT id FROM Seq WHERE name='page');
    UPDATE Seq SET id=id+1 WHERE name='page';
  END //
DELIMITER ;


/*test value*/

INSERT INTO page (fromPage,title,txt) VALUES (null,'wikitroll','write a story'),
(0,'at the airport 	','standing in front of a gorilla who is wasting my lugage, not far away a velociraptor is holding a my name on a sign'),
(0,'in the forest','there is a lot of trees');


