DROP DATABASE Jeux;
CREATE DATABASE Jeux;
USE Jeux;


CREATE TABLE Sequence (
  name VARCHAR(32), 
  id integer,
  PRIMARY KEY(name)
);

/*function*/
DELIMITER //
CREATE FUNCTION getSeq (nameSeq VARCHAR(32))
RETURNS INTEGER
BEGIN
  RETURN (SELECT id
    FROM Sequence
    WHERE name=nameSeq);
END//
DELIMITER ;
/*procedure doing the same thing*/
DELIMITER //
CREATE PROCEDURE PLgetSeq (IN nameSeq VARCHAR(32))
BEGIN
  SELECT id
  FROM Sequence 
  WHERE name=nameSeq;
END//
DELIMITER ;
/*procedure incrementing sequence*/
DELIMITER //
CREATE PROCEDURE incSeq (IN nameSeq VARCHAR(32))
BEGIN
  UPDATE Sequence 
  SET id=id+1
  WHERE name=nameSeq;
END//
DELIMITER ;

/*simple direct output function*/
DELIMITER //
CREATE FUNCTION Fhello()
RETURNS VARCHAR(32)
BEGIN
  RETURN 'Hello world';
END//
DELIMITER ;

/*simple direct ouput procedure*/
DELIMITER //
CREATE PROCEDURE PLhello()
BEGIN
  SELECT 'Hello world';
END//
DELIMITER ;

/*test*/
INSERT INTO Sequence (name, id) VALUES ('test',0);
CALL incSeq('test');
SELECT getSeq('test');

SELECT Fhello();

/*trigger*/
CREATE TABLE Test(
  id INTEGER,
  PRIMARY KEY(id),
  de INTEGER
);

DELIMITER //
CREATE PROCEDURE insTest(IN nde INTEGER)
BEGIN
  INSERT INTO Test (id, de) VALUES (getSeq('test',nde));
  CALL incSeq('test');
END //
DELIMITER ;

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

/*RIGHT*/
DROP USER 'jeux'@'localhost';
CREATE USER 'jeux'@'localhost';
GRANT SELECT ON TABLE Jeux.Sequence TO 'jeux';
GRANT EXECUTE ON FUNCTION Jeux.getSeq TO 'jeux';
GRANT EXECUTE ON PROCEDURE Jeux.incSeq TO 'jeux';
GRANT INSERT(de) ON TABLE Jeux.Test TO 'jeux';


/*ascii art*/
CREATE TABLE art (
  name VARCHAR(32),
  data TEXT
);

INSERT INTO art VALUES(
'duck',
'\n (\'<\n(<=)\n'
);

SELECT data FROM art WHERE name='duck';



