-- Copyright 2019 Michel Petit / Cogito Ergo Dev
--
-- Permission is hereby granted, free of charge, to any person obtaining a copy
-- of this software and associated documentation files (the "Software"), to deal
-- in the Software without restriction, including without limitation the rights
-- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
-- copies of the Software, and to permit persons to whom the Software is
-- furnished to do so, subject to the following conditions:
--
-- The above copyright notice and this permission notice shall be included in
-- all copies or substantial portions of the Software.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
-- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
-- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
-- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
-- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
-- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
-- SOFTWARE.

CREATE TABLE examples (
    id            INTEGER  PRIMARY KEY
                           UNIQUE,
    name         TEXT     NOT NULL,
    token        TEXT     UNIQUE
);

CREATE TABLE examples2 (
    id            INTEGER  PRIMARY KEY
                           UNIQUE,
    name         TEXT     NOT NULL,
    token        TEXT     UNIQUE
);


CREATE TRIGGER on_insert_example
         AFTER INSERT
            ON examples
      FOR EACH ROW
          WHEN NEW.token IS NULL
BEGIN
-- changez la valeur 4 par le nombre d’octets que vous voulez
    UPDATE examples
       SET token = lower(hex(randomblob(4)))
     WHERE id = NEW.id;
END;



-- majeure partie du code de ce trigger
-- par Chichou. Son blog : https://blog.chichou.me/
-- Vu sur <https://gist.github.com/ChiChou/97a53caa2c0b49c1991e>
CREATE TRIGGER on_insert_example2
         AFTER INSERT
            ON examples2
      FOR EACH ROW
          WHEN NEW.token IS NULL
BEGIN
    UPDATE examples2
       SET token = (
            -- Common Table Expression pour convertir hex vers decimal
            -- Voir <https://gist.github.com/ChiChou/97a53caa2c0b49c1991e>
            -- Par Chichou. Son blog : https://blog.chichou.me/
            -- Copyright ChiChou
            WITH RECURSIVE
                unhex(str, val, weight) AS (
                    -- changez la valeur 4 par le nombre
                    -- d’octets que vous voulez
                    SELECT hex(randomblob(4)), 0, 1
                    UNION ALL
                        SELECT 
                        substr(str, 1, length(str) - 1),
                        val + (instr('0123456789ABCDEF', substr(str, length(str), 1)) - 1) * weight,
                        weight * 16
                        FROM unhex WHERE length(str) > 0
                )
            SELECT val FROM unhex ORDER BY weight DESC LIMIT 1
        )
     WHERE id = NEW.id;
END;



INSERT INTO examples(name) VALUES ('foo'), ('bar'), ('thing');
INSERT INTO examples2(name) VALUES ('foo'), ('bar'), ('thing');

SELECT * FROM examples;
SELECT * FROM examples2;