/*------------------------------------------------------------
  0.  Clean‑up (optional)
  ------------------------------------------------------------*/
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

/*------------------------------------------------------------
  1.  Bloom64() user‑defined function
  ------------------------------------------------------------*/
DELIMITER //

CREATE FUNCTION bloom64(s TEXT) RETURNS BIGINT UNSIGNED
DETERMINISTIC SQL SECURITY INVOKER
BEGIN
  DECLARE i   INT DEFAULT 1;
  DECLARE n   INT DEFAULT CHAR_LENGTH(s);
  DECLARE sig BIGINT UNSIGNED DEFAULT 0;
  DECLARE crc INT UNSIGNED;          -- variabile locale, non @

  WHILE i <= n - 2 DO                -- trigrammi di caratteri
    SET crc = CRC32(SUBSTRING(s, i, 3));

    SET sig = sig
              | (CAST(1 AS UNSIGNED) << ( crc         & 63))
              | (CAST(1 AS UNSIGNED) << ((crc >> 13) & 63))
              | (CAST(1 AS UNSIGNED) << ((crc >> 26) & 63));

    SET i = i + 1;
  END WHILE;

  RETURN sig;
END//

DELIMITER ;

/*------------------------------------------------------------
  2.  Table:  `molecole`
      – id           : PK
      – smiles       : testo della molecola
      – sig          : colonna PERSISTENT calcolata con Bloom64
  ------------------------------------------------------------*/
CREATE TABLE molecole (
  smiles  TEXT            NOT NULL,
  sig     BIGINT UNSIGNED NOT NULL
) ENGINE = ColumnStore;

INSERT INTO molecole (smiles, sig) VALUES
  ('CCO',                                              bloom64('CCO')),
  ('c1ccccc1',                                         bloom64('c1ccccc1')),
  ('Cc1ccccc1',                                        bloom64('Cc1ccccc1')),
  ('CC(=O)OC1=CC=CC=C1C(=O)O',                         bloom64('CC(=O)OC1=CC=CC=C1C(=O)O')),
  ('Cn1cnc2c1c(=O)n(c(=O)n2C)C',                       bloom64('Cn1cnc2c1c(=O)n(c(=O)n2C)C')),
  ('C(C1C(C(C(C(O1)O)O)O)O)O',                         bloom64('C(C1C(C(C(C(O1)O)O)O)O)O')),
  ('CC(=O)NC1=CC=C(C=C1)O',                            bloom64('CC(=O)NC1=CC=C(C=C1)O')),
  ('CCC(=O)O[C@]1(C[C@H]2[C@H]3[C@@H]1[C@]4(C(=O)C(O)=C(C4=O)O)'
   'O[C@@H]3C[C@H]2O)C',                               bloom64(
     'CCC(=O)O[C@]1(C[C@H]2[C@H]3[C@@H]1[C@]4(C(=O)C(O)=C(C4=O)O)O[C@@H]3C[C@H]2O)C')),
  ('O=C(O)C(O)C(O)C(O)CO',                             bloom64('O=C(O)C(O)C(O)C(O)CO')),
  ('C1=CC=C2C(=C1)C=CC=C2',                            bloom64('C1=CC=C2C(=C1)C=CC=C2'));


insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;
insert into molecole (smiles, sig) select smiles, sig from molecole;

-- -----------------------------------------------------------
-- 4.  Query accelerata con Bloom
-- -----------------------------------------------------------
SET @mask := bloom64('C(=O)C(O)=C(C4=O)O)O[');     -- frammento/substring da cercare

-- 1 second query
SELECT count(*) FROM   molecole WHERE  smiles LIKE '%C(=O)C(O)=C(C4=O)O)O[%' AND sig = 18373419839315247039;
-- 4 seconds query
SELECT count(*) FROM   molecole WHERE  (smiles LIKE '%C(=O)C(O)=C(C4=O)O)O[%') AND (sig & @mask) = @mask;

-- # why??
