Knowledge bases end-to-end example usage

-- Create extension
DROP EXTENSION aidb CASCADE;
CREATE EXTENSION aidb CASCADE;

drop table if exists test_source_table_ajz72eb cascade;
drop table if exists test_knowledge_base_ajz72eb_vector cascade;

-- Create source test table
CREATE TABLE test_source_table_ajz72eb
(
    id               INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    content          TEXT NOT NULL,
    unrelated_column TEXT
);
INSERT INTO test_source_table_ajz72eb
VALUES (43941, 'Catwalk Women Brown Heels'),
       (55018, 'Lakme 3 in 1 Orchid  Aqua Shine Lip Color'),
       (19337, 'United Colors of Benetton Men Stripes Black Jacket');

SELECT aidb.create_table_knowledge_base(
    name => 'test_knowledge_base_ajz72eb',
    model_name => 'bert',  -- this is a pre-defined locally running model
    source_table => 'test_source_table_ajz72eb',
    source_data_column => 'content',
    source_data_format => 'Text'
);


SELECT * FROM aidb.kbs;  -- This is an alias for the view aidb.knowledge_bases. It shows the configuration for all knowledge bases

SELECT aidb.bulk_embedding('test_knowledge_base_ajz72eb');  -- Since no auto-processing was configured, we manually run it

SELECT * FROM aidb.kbstat;  -- This is an alias for aidb.knowledge_base_stats. It shows statistics about the number of records and vectors

-- Perform retrieval similarity search for the closest `key`
SELECT * FROM aidb.retrieve_key('test_knowledge_base_ajz72eb', 'orchid');
SELECT * FROM aidb.retrieve_key('test_knowledge_base_ajz72eb', 'orchid', 2);  -- Limit to top 2 results

SELECT * FROM aidb.retrieve_text('test_knowledge_base_ajz72eb', 'orchid');
SELECT * FROM aidb.retrieve_text('test_knowledge_base_ajz72eb', 'orchid', 2);  -- Limit to top 2 results


-- enable live auto-processing
SELECT aidb.set_auto_knowledge_base('test_knowledge_base_ajz72eb', 'Live');


-- add additional data to test auto-processing
INSERT INTO test_source_table_ajz72eb
VALUES (11211, 'Bicycle'),
       (11311, 'What is this?'),
       (11411, 'Elephants');


-- check embeddings
SELECT id FROM test_knowledge_base_ajz72eb_vector;

-- delete one of the source rows
DELETE FROM test_source_table_ajz72eb WHERE id = 11211;

-- check embeddings
SELECT id FROM test_knowledge_base_ajz72eb_vector;