Firebird: Fast Search

PHOTO EMBED

Fri Jul 14 2023 02:07:12 GMT+0000 (Coordinated Universal Time)

Saved by @marcopinero #sql 

CREATE OR ALTER PROCEDURE SP_SPLIT_INTO_WORDS (A_TEXT VARCHAR(32000), A_DELS VARCHAR(100) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/', A_SPECIAL CHAR(1) default ' ')
RETURNS (
	WORD VARCHAR(50), POSIC integer
)
AS
DECLARE norder integer;
BEGIN

	-- Aux SP, used only in oltp_data_filling.sql to filling table PATTERNS
	-- with miscelan combinations of words to be used in SIMILAR TO testing.
	
	norder = 0;

	for
	    with recursive
	    j as( -- loop #1: transform list of delimeters to rows
	        select s,1 i, substring(s from 1 for 1) del
	        from(
	          select replace(:a_dels,:a_special,'') s
	          from rdb$database
	        )
	        
	        UNION ALL
	        
	        select s, i+1, substring(s from i+1 for 1)
	        from j
	        where substring(s from i+1 for 1)<>''
	    )
	 
	    ,d as(
	        select :a_text s, :a_special sp from rdb$database
	    )
	    ,e as( -- loop #2: perform replacing each delimeter to `space`
	        select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del
	        from d join j on j.i=1
	 
	        UNION ALL
	 
	        select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del
	        from e
	        -- nb: here 'column unknown: e.i' error will be on old builds of 2.5,
	        -- e.g: WI-V2.5.2.26540 (letter from Alexey Kovyazin, 24.08.2014 14:34)
	        join j on j.i = e.i + 1
	    )
	    ,f as(
	        select s1 from e order by i desc rows 1
	    )
	    
	    ,r as ( -- loop #3: perform split text into single words
	        select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s,
	             iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k,
	             t.i,
	             t.del,
	             iif(t.k>0,left(t.s, t.k-1),t.s) word
	        from(
	          select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d
	        )t
	 
	        UNION ALL
	 
	        select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s,
	             iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k,
	             r.i+1,
	             r.del,
	             iif(r.k>0,left(r.s, r.k-1),r.s) word
	        from r
	        where r.k>=0
	    )
	    select word from r where word>''
	    INTO word
	do
	BEGIN
		posic = norder;
    	suspend;
    	norder = norder + 1;
    end
end;

CREATE TABLE alumno_srch_nombre(
	id integer NOT NULL,
	valor varchar(255) NOT NULL,
	orden integer
);

CREATE INDEX ndx_alum_srch_nombre ON alumno_srch_nombre(id, valor);

CREATE OR ALTER TRIGGER t_alumno_biu_ndx
FOR alumno 
BEFORE INSERT OR UPDATE 
AS
BEGIN
	DELETE FROM alumno_srch_nombre
	WHERE id = NEW.id;

	INSERT INTO alumno_srch_nombre
	SELECT NEW.id, word, posic FROM SP_SPLIT_INTO_WORDS(NEW.apenom);
END;

SELECT * FROM (
SELECT a.*,
  CASE 
	  WHEN a.apenom LIKE replace(:INPUT,' ','%') || '%' THEN 0
	  ELSE 1
  END NIVEL
FROM alumno a
INNER JOIN (
	SELECT id, count(*) cnt
	FROM (
		SELECT id, word, min(orden) orden 
		FROM SP_SPLIT_INTO_WORDS(:input) x
		INNER JOIN ALUMNO_SRCH_NOMBRE asn ON asn.valor LIKE word || '%'
		GROUP BY id, word
	)
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) b ON b.id=a.id
) ORDER BY NIVEL;


--- OTHER EXAMPLE:

SELECT a.* FROM alumno a
INNER JOIN (
	SELECT id, count(*) cant FROM alumno
	INNER JOIN SP_SPLIT_INTO_WORDS(:input) X ON 
		ALUMNO.APELLIDOS LIKE X.WORD||'%'
	  		OR ALUMNO.NOMBRES LIKE X.WORD||'%'
	  		OR ALUMNO.CEDULA LIKE X.WORD||'%'
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) x ON a.id = x.id 
UNION 
SELECT a.* FROM ALUMNO a
where a.cedula LIKE :input || '%'
content_copyCOPY