bd2013

/* Hay cuatro tablas en las cuales hay una bolsa, la bolsa es para tener solamente una pelota,
   Para pasarse cosas entre las tablas debe haber un combustible (token).
   El usuario debe poner cuantas transiciones quiere que haya (eso debe tenerse en cuenta de que,
   para que hayan transiciones debe haber combustible.
   Esta es una serie de triggers que se usaron para el ejercicio, cualquier consulta preguntar.
   Hay partes que no estan bien (se van a dar cuenta porque se habra comentado o algo asi) */

/* CREACION DE TABLAS */
CREATE TABLE A (
   BUCKET                  INT4                 NULL
);

CREATE TABLE B (
   BUCKET                  INT4                 NULL
);

CREATE TABLE C (
   BUCKET                  INT4                 NULL
);

CREATE TABLE D (
   BUCKET                  INT4                 NULL
);

CREATE TABLE STEP (
   N                  INT4                 NULL
);

CREATE TABLE TOKEN (
   T                  INT4                 NULL
);


/*-----------------------------------------------------------------------*/
/* Para la tabla token */
CREATE OR REPLACE FUNCTION cons_token() RETURNS TRIGGER AS $cons_token$
BEGIN
    IF NEW.t IS NULL THEN
        RAISE EXCEPTION 'token no se puede nulo';
    END IF;

    IF NEW.t < 0 THEN
        RAISE EXCEPTION 'token debe ser mayor o igual a cero';
    END IF;

    --PARA QUE AL INSERTAR SOLAMENTE PUEDA INSERTAR UN ELEMENTO--
    IF (TG_OP = 'INSERT') THEN
        IF ((SELECT COUNT(t) FROM TOKEN) > 0) THEN
            RAISE EXCEPTION 'solo un campo se puede insertar, favor actualizar';
        END IF;
    END IF;

    RETURN NEW;
END;
$cons_token$ LANGUAGE plpgsql;

CREATE TRIGGER cons_token BEFORE INSERT OR UPDATE ON TOKEN
    FOR EACH ROW EXECUTE PROCEDURE cons_token();

/* Para la tabla step */
/*-----------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION cons_step() RETURNS TRIGGER AS $cons_step$
BEGIN
    IF NEW.n IS NULL THEN
        RAISE EXCEPTION 'token no se puede nulo';
    END IF;

    IF NEW.n < 1 THEN
        RAISE EXCEPTION 'token debe ser mayor o igual a uno';
    END IF;

    --PARA QUE AL INSERTAR SOLAMENTE PUEDA INSERTAR UN ELEMENTO--
    IF (TG_OP = 'INSERT') THEN
        IF ((SELECT COUNT(n) FROM step) > 0) THEN
            RAISE EXCEPTION 'solo un campo se puede insertar, favor actualizar';
        END IF;
    END IF;

RETURN NEW;
END;
$cons_step$ LANGUAGE plpgsql;

CREATE TRIGGER cons_step BEFORE INSERT OR UPDATE ON STEP
    FOR EACH ROW EXECUTE PROCEDURE cons_step();

/*-----------------------------------------------------------------------*/
/* Para la tabla A */
CREATE OR REPLACE FUNCTION trig_A() RETURNS TRIGGER AS $trig_A$
DECLARE
    numedelstep INT;
BEGIN
    numedelstep := (SELECT n FROM STEP);
    IF NEW.bucket IS NULL THEN
        RAISE EXCEPTION 'no se puede nulo';
    END IF;

    --ACA lo que hace es preguntar si es que la cantidad de filas en B es menor que
    --C, eso quiere decir que hay algo que no envio todavia, sin necesidad de
    --preguntar si es que el token esta en cero.
    IF ((SELECT COUNT(bucket) FROM B) > (SELECT COUNT(bucket) FROM C)) THEN
        --select bucket from B order by bucket desc limit 1;
        --MI IDEA ERA LA SIGUIENTE, UTILIZAR PARA MOSTRAR LOS QUE ESTAN EN ESPERA
        --QUE SEGURO ES LA ULTIMA DE B SI ES QUE TIENE ALGUNA EN ESPERA select espera();
        RAISE EXCEPTION 'B tiene algo en espera EL NUMERO %', (SELECT bucket FROM B offset ((SELECT COUNT(bucket) FROM B)-1));
    END IF;

    INSERT INTO B(bucket) VALUES (NEW.bucket + numedelstep);
    RETURN NEW;
END;
$trig_A$ LANGUAGE plpgsql;

-- DROP TRIGGER IF EXISTS trig_1 ON T1;
CREATE TRIGGER trig_A BEFORE INSERT OR UPDATE ON A
    FOR EACH ROW EXECUTE PROCEDURE trig_A();

/*-----------------------------------------------------------------------*/
/* Para la tabla B */
CREATE OR REPLACE FUNCTION trig_B() RETURNS TRIGGER AS $trig_B$
BEGIN
    IF ((SELECT t FROM TOKEN) > 0)  THEN
        INSERT INTO C(bucket) VALUES (NEW.bucket + (SELECT n FROM STEP));
        UPDATE TOKEN SET t = t - 1;
    END IF;
    RETURN NEW;
END;
$trig_B$ LANGUAGE plpgsql;
CREATE TRIGGER trig_B BEFORE INSERT OR UPDATE ON B
    FOR EACH ROW EXECUTE PROCEDURE trig_B();

/*-----------------------------------------------------------------------*/
/* Para la tabla C */
CREATE OR REPLACE FUNCTION trig_C() RETURNS TRIGGER AS $trig_C$
BEGIN
    INSERT INTO D(bucket) VALUES (NEW.bucket + (SELECT n FROM STEP));
    RETURN NEW;
END;
$trig_C$ LANGUAGE plpgsql;
CREATE TRIGGER trig_C BEFORE INSERT OR UPDATE ON C
    FOR EACH ROW EXECUTE PROCEDURE trig_C();

/*-----------------------------------------------------------------------*/
/* Para el trigger */
CREATE OR REPLACE FUNCTION trig_token() RETURNS TRIGGER AS $trig_token$
DECLARE
    num1 INT;
    num2 INT;
BEGIN
    num1 := (SELECT COUNT(bucket) FROM B);
    num2 := (SELECT COUNT(bucket) FROM C);
    IF (NEW.t > 0) AND (num1 > num2) THEN
        --inserta el ultimo del bucket de la tabla B + el n del step
--** Sebastian Ortiz:comenté esta línea porque da error de sintaxis
        --insert into C(bucket) values (select bucket from B offset ((select count(bucket) from B)-1) + (select n from STEP));
        NEW.t = NEW.t - 1;
    END IF;
    RETURN NEW;
END;
$trig_token$ LANGUAGE plpgsql;

CREATE TRIGGER trig_token BEFORE UPDATE ON TOKEN
    FOR EACH ROW EXECUTE PROCEDURE trig_token();

/*-----------------------------------------------------------------------*/
/* Los respectivos selects */
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
SELECT * FROM D;
SELECT * FROM token;
SELECT * FROM step;

TRUNCATE a,b,c,d;

/*-----------------------------------------------------------------------*/
/* INSERTS / UPDATES */
INSERT INTO token(t) VALUES (2);
INSERT INTO step(n) VALUES (2);
INSERT INTO A(bucket) VALUES (1);
UPDATE token SET t = 1;
INSERT INTO step(n) VALUES (1);
INSERT INTO token(t) VALUES (1);
DELETE FROM step WHERE n = 1;

/* FUNCION HAY ALGO ESPERANDO */
CREATE OR REPLACE FUNCTION espera(OUT num INTEGER) RETURNS INTEGER AS $espera$
DECLARE
    num1 INT;
    num2 INT;
BEGIN
    num1 := (SELECT COUNT(bucket) FROM B);
    num2 := (SELECT COUNT(bucket) FROM C);
    IF (num1 > num2) THEN
        --ULTIMO ELEMENTO DE LA B
        num := (SELECT bucket FROM B offset ((SELECT COUNT(bucket) FROM B)-1));
    END IF;
END;
$espera$ LANGUAGE plpgsql;


TRUNCATE TABLE a; TRUNCATE TABLE b; TRUNCATE TABLE c; TRUNCATE TABLE d;
TRUNCATE TABLE step; TRUNCATE TABLE token;


INSERT INTO token (t) VALUES (-1);
INSERT INTO step (n) VALUES (-1);


SELECT 'token ', t, '' FROM token UNION ALL SELECT 'step ', n, '' FROM step UNION ALL
SELECT 'a ', bucket, '' FROM a UNION ALL SELECT 'b ', bucket, '' FROM b UNION ALL
SELECT 'c ', bucket, '' FROM c UNION ALL SELECT 'd ', bucket, '' FROM d;

TRUNCATE TABLE a; TRUNCATE TABLE b; TRUNCATE TABLE c; TRUNCATE TABLE d;
TRUNCATE TABLE step; TRUNCATE TABLE token;

INSERT INTO token (t) VALUES (1);
INSERT INTO step (n) VALUES (3);

INSERT INTO a (bucket) VALUES (1);

SELECT 'token ', t, '' FROM token UNION ALL SELECT 'step ', n, '' FROM step UNION ALL
SELECT 'a ', bucket, '' FROM a UNION ALL SELECT 'b ', bucket, '' FROM b UNION ALL
SELECT 'c ', bucket, '' FROM c UNION ALL SELECT 'd ', bucket, '' FROM d;

INSERT INTO a (bucket) VALUES (100);

SELECT 'token ', t, '' FROM token UNION ALL SELECT 'step ', n, '' FROM step UNION ALL
SELECT 'a ', bucket, '' FROM a UNION ALL SELECT 'b ', bucket, '' FROM b UNION ALL
SELECT 'c ', bucket, '' FROM c UNION ALL SELECT 'd ', bucket, '' FROM d;

INSERT INTO a (bucket) VALUES (200);

SELECT 'token ', t, '' FROM token UNION ALL SELECT 'step ', n, '' FROM step UNION ALL
SELECT 'a ', bucket, '' FROM a UNION ALL SELECT 'b ', bucket, '' FROM b UNION ALL
SELECT 'c ', bucket, '' FROM c UNION ALL SELECT 'd ', bucket, '' FROM d;

UPDATE token SET t = 2;

SELECT 'token ', t, '' FROM token UNION ALL SELECT 'step ', n, '' FROM step UNION ALL
SELECT 'a ', bucket, '' FROM a UNION ALL SELECT 'b ', bucket, '' FROM b UNION ALL
SELECT 'c ', bucket, '' FROM c UNION ALL SELECT 'd ', bucket, '' FROM d;