X Local File Search Code

SET client_encoding = 'KOI8';
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

CREATE TYPE search_results AS
   (proto varchar(5),
    host varchar(255),
    path varchar,
    name varchar(300),
    size int8);

CREATE OR REPLACE FUNCTION search(text)
  RETURNS SETOF search_results AS
$BODY$
SELECT proto,host,path,name,size
FROM filebase
WHERE vectors @@ to_tsquery('default_russian', $1);
$BODY$
  LANGUAGE 'sql' STABLE;

CREATE FUNCTION update_vectors() RETURNS "trigger"
    AS $$
BEGIN
     UPDATE filebase SET vectors=to_tsvector('default_russian',replace(replace(replace(new.lower_name, '-', ' ' ), '_', ' '), '.', ' ')) WHERE proto=new.proto AND host=new.host AND path=new.path AND name=new.name;
  RETURN new;
END
$$
    LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION updatefile(text, text, text, text, text, text, bigint, timestamp) RETURNS boolean AS $$
DECLARE
BEGIN
   update filebase set
    proto = $1,
    host = $2,
    path = $3,
    name = $4,
    lower_name = $5,
    ext = $6,
    size = $7,
    date = $8
	WHERE proto = $1 AND host = $2 AND path = $3 AND lower_name = $5;

    IF NOT FOUND THEN
        return false;
	END IF; return true;

END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION insertfile(text, text, text, text, text, text, bigint, timestamp) RETURNS boolean AS $$

DECLARE
   rep boolean DEFAULT false;
BEGIN
   insert into filebase (proto,host,path,name,lower_name,ext,size,date)
   values ($1, $2, $3, $4, $5, $6, $7, $8);
 return rep;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION replacefile(proto text, host text, path text, name text, lowername text, ext text, size bigint, date text) RETURNS boolean AS $$

DECLARE
rep boolean = false;
BEGIN
   rep = updatefile($1, $2, $3, $4, $5, $6, $7, $8::timestamp);
   if not rep then
       rep =  insertfile($1, $2, $3, $4, $5, $6, $7, $8::timestamp);
   end if;
   return rep;
END
$$ LANGUAGE plpgsql;



SET default_tablespace = '';

SET default_with_oids = true;

CREATE TABLE filebase (
    proto character varying(5) NOT NULL,
    host character varying(255) NOT NULL,
    path character varying NOT NULL,
    name character varying(300) NOT NULL,
    lower_name character varying(300),
    ext character varying(45) DEFAULT NULL,
    size bigint DEFAULT 0 NOT NULL,
    date timestamp without time zone,
    vectors tsvector

);

CREATE TABLE hosts (
    proto character varying(5),
    host character varying(255) NOT NULL,
	ipaddr inet NOT NULL,
    date timestamp without time zone,
    files integer DEFAULT 0 NOT NULL,
    dirs integer DEFAULT 0 NOT NULL,
    size bigint DEFAULT 0 NOT NULL,
    recursive boolean DEFAULT true NOT NULL,
    online boolean DEFAULT false NOT NULL,
    busy boolean DEFAULT false NOT NULL,
    errors smallint DEFAULT 0 NOT NULL
);



ALTER TABLE ONLY hosts
    ADD CONSTRAINT host_pk PRIMARY KEY (host);

CREATE INDEX host_ip_idx ON hosts USING btree (ipaddr);
	
ALTER TABLE ONLY filebase
    ADD CONSTRAINT pkey PRIMARY KEY (proto, host, path, name);

CREATE INDEX name_ft_idx ON filebase USING gist (vectors);

CREATE INDEX name_idx ON filebase USING btree (lower_name);

CREATE INDEX file_proto_idx ON filebase USING btree(proto);

CREATE INDEX file_host_idx ON filebase USING btree (host);

CREATE INDEX file_date_idx ON filebase USING btree (date);

CREATE INDEX file_size_idx ON filebase USING btree (size);

CREATE INDEX file_ext_idx ON filebase USING btree (ext);

CREATE TRIGGER update_row
    AFTER INSERT ON filebase
    FOR EACH ROW
    EXECUTE PROCEDURE update_vectors();