Generic example: authors, posts and books

I this example we illustrate how to populate the index with tables with relations

Create the tables

drop table if exists author cascade;
CREATE TABLE author(
   id SERIAL PRIMARY KEY,
   name TEXT NOT null,
   description varchar,
   country varchar,
   homepage varchar,
   birthday timestamp,
   adruuid varchar
);

drop table if exists post cascade;
CREATE TABLE post(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author_id INT NOT NULL references author(id) on delete cascade
);

drop table if exists book cascade;
CREATE TABLE book(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   rating int null,
   author_id INT NOT NULL references author(id) on delete cascade
);

Insert test data

INSERT INTO author (id, name, description, country, homepage, birthday, adruuid)
VALUES (1, 'Pete Graham', 'Award winner', 'England', 'https://www.facebook.com/PeteGrahamm/', '1987-12-05', '0a3f507b-37cb-32b8-e044-0003ba298018'),
       (2, 'Rachid Belaid', 'French national poet', 'France', 'http://rachbelaid.com/about/', '1991-07-4', '0a3f507b-37d6-32b8-e044-0003ba298018'),
       (3, 'Robert Berry', 'Pullitzer Prize winner', 'USA', 'https://en.wikipedia.org/wiki/Robert_Berry', '1979-02-22', '16bed59f-9567-1efc-e044-0003ba298018');

INSERT INTO post (id, title, content, author_id)
VALUES (1, 'Endangered species', 'Pandas are an endangered species', 1 ),
       (2, 'Freedom of Speech', 'Freedom of speech is a necessary right missing in many countries', 2),
       (3, 'The onus of Being', 'To be or not to be', 2),
       (4, 'Star Wars vs Star Trek', 'Few words from a big fan', 3);

INSERT INTO book (id, title, content, author_id, rating)
VALUES (1, 'The book about endangered species', 'Endangered species catalog', 1, 1),
       (2, 'The book about freedom of Speech', 'Freedom of writing is a necessary right', 2, 2),
       (3, 'The book about the onus of Being', '64 ways to say "To be or not to be"', 2, 3),
       (4, 'The book about star Wars vs Star Trek', 'Few chapters from a big fan', 3, 4),
       (5, 'A book without rating', 'A serious text about the dangers of rating', 3, null);

create search indexes

Author

Register author featurertype in the index

select septima_pgindex.registerfeaturetype(
	sourceid:= 'pg_index_demodata',  --source
	typeid:= 'author',               --featuretype id
	singular:= 'Author',             --singular
	plural:= 'Authors',              --plural
	language:= 'english',            --ts language
	description:= 'Writers of books and/or posts', --description
	querybehaviour:= 'search',       --'search'|'match'|'none'
	geometrysupport:= 'none',        --'sq'|'hasgeometry'|'none'
	srid:= null,                     --srid of data (must be non-null if geometrySupport = 'sq')
	iconuri:= null,                  --iconuri
	sourcestatement:=                --sql expression retrieving features
    'select                 
      a.id::varchar as id,
      a.name as title,
      description,
      null as searchtext,
      null as matchstring,
      null as geometry,
	  country,
	  homepage,
      birthday::varchar as birthday,
	  adruuid	
     from author a'
);

Specify fields

select septima_pgindex.definefield( 
	typeid:=      'author',  --featuretype id
	field:=       'country', --field in feature's sourcestatement
	displayname:= 'Country',  --displayname af field
	description:= 'Country of Birth'
);

select septima_pgindex.definefield(
	typeid:=      'author',  --featuretype id
	field:=       'birthday', --field in feature's sourcestatement
	displayname:= 'Birthday',  --displayname af field,
	datatype:=    'iso-date'
);

select septima_pgindex.definefield(
	typeid:=      'author',  --featuretype id
	field:=       'homepage', --field in feature's sourcestatement
	displayname:= 'Web Site of [title] ',  --displayname af field,
	datatype:=    'link',
	description:= 'Professional web site',
	label:=		  'Web site'
);

Populate the index

select septima_pgindex.populateindex('author');

Query the index

select
 *
from septima_pgindex.queryindex('author', 'rachid', 3, 25832);

Posts

Register post featurertype in the index

select * from septima_pgindex.registerfeaturetype(
	'pg_index_demodata', --source
	'post',              --type id
	'Post',              --singular
	'Posts',             --plural
	'english',           --ts language
	'Demo posts',        --description
	'search',            --querybehaviour ('search'|'match'|'none')
	'none',              --geometrySupport ('sq'|'hasgeometry'|'none')
	null,                --srid (must be non-null if geometrySupport = 'sq')
	null,                --iconuri
	'select
      p.id::varchar as id,
      p.title,
      ''By '' || author.name as description,
      p.content as searchtext,
      null as matchstring,
      null as geometry,
	  author_id,
	  content	
     from post p JOIN author ON author.id = p.author_id'
);

Specify fields

select septima_pgindex.definefield(
	typeid:=      'post',
	field:=       'content',
	displayname:= 'Content'
);

select * from septima_pgindex.registermasterdetail(
	'author',	       --master featuretype
	'id',              --field
	null,	   --relation may be null
	'post',            --detail featuretype
	'author_id'          --field
);

Populate index

select septima_pgindex.populateindex('post');

Query posts

select queryindex as result from septima_pgindex.queryindex('post', 'f', 3);

Get single document by id

select document from septima_pgindex.get('post', '2');

Books

select * from septima_pgindex.registerfeaturetype(
	'pg_index_demodata', --source
	'book',              --type id
	'Book',              --singular
	'Books',             --plural
	'english',           --ts language
	'Demo books',        --description
	'search',            --querybehaviour ('search'|'match'|'none')
	'none',              --geometrySupport ('sq'|'hasgeometry'|'none')
	null,                --srid (must be non-null if geometrySupport = 'sq')
	null,                --iconuri
	'select
      b.id::varchar as id,
      b.title,
      ''By '' || author.name as description,
      b.content as searchtext,
      null as matchstring,
      null as geometry,
	  author_id,
	  rating
     from book b JOIN author ON author.id = b.author_id'
);

Define fields

select septima_pgindex.definefield(
	typeid:=      'book',
	field:=       'rating',
	displayname:= 'Rating',
	novaluetext:= 'Not rated'
);

select * from septima_pgindex.registermasterdetail(
   'author',    --master featuretype
   'id',        --field
   null,        --relation may be null
   'book',      --detail featuretype
   'author_id'  --field
);

Populate index

select septima_pgindex.populateindex('book');