dierk
d360ff1a78
- pgvector-demo/sql/setup.sql: enable pgvector extension, create images table and HNSW index - oravector-demo/sql/setup_vectors_user.sql: create vectors_user, images table and HNSW vector index - oravector-demo/sql/setup_vector_schema.sql: create VECTOR user, load CLIP ONNX models, create FOTO_VEKTOR table Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
48 lines
1.8 KiB
SQL
48 lines
1.8 KiB
SQL
-- Oracle 26ai setup for the in-database embedding backend (port 8002)
|
|
-- Run as SYSDBA connected to FREEPDB1
|
|
--
|
|
-- Prerequisites:
|
|
-- - CLIP_TXT and CLIP_IMG ONNX model files must be present in the Oracle
|
|
-- VEC_DUMP directory before loading (see notes below)
|
|
-- - vector_memory_size must be set in SGA (512M recommended)
|
|
--
|
|
-- Usage:
|
|
-- podman exec -it oracle.free sqlplus / as sysdba
|
|
|
|
ALTER SESSION SET CONTAINER=FREEPDB1;
|
|
|
|
-- Create VECTOR user
|
|
CREATE USER vector IDENTIFIED BY Vektor;
|
|
GRANT CONNECT, RESOURCE TO vector;
|
|
GRANT UNLIMITED TABLESPACE TO vector;
|
|
GRANT READ, WRITE ON DIRECTORY VEC_DUMP TO vector;
|
|
|
|
-- Load ONNX models (run as VECTOR user or SYSDBA)
|
|
-- The .onnx files must be placed in the VEC_DUMP directory on the Oracle host
|
|
-- beforehand (typically /opt/oracle/dbs/vec_dump/ inside the container).
|
|
--
|
|
-- CLIP_TXT: text encoder using CLS-token pooling (position 0).
|
|
-- Standard CLIP exports use EOS-token pooling (ArgMax), which Oracle rejects
|
|
-- because it introduces an ArgMax node on input_ids. This model must be
|
|
-- re-exported with CLS pooling to pass Oracle's ONNX validator.
|
|
--
|
|
EXEC DBMS_VECTOR.LOAD_ONNX_MODEL('VEC_DUMP', 'clip_txt.onnx', 'CLIP_TXT',
|
|
JSON('{"function":"embedding","embeddingOutput":"output","input":{"input":["DATA"]}}'));
|
|
|
|
EXEC DBMS_VECTOR.LOAD_ONNX_MODEL('VEC_DUMP', 'clip_img.onnx', 'CLIP_IMG',
|
|
JSON('{"function":"embedding","embeddingOutput":"output","input":{"input":["DATA"]}}'));
|
|
|
|
-- Photo table (stores filename, image BLOB, and vector)
|
|
CREATE TABLE vector.foto_vektor (
|
|
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
filename VARCHAR2(100),
|
|
foto BLOB,
|
|
foto_vek VECTOR
|
|
);
|
|
|
|
-- Grant SELECT on FOTO_VEKTOR to vectors_user so both backends share one table
|
|
-- (optional — remove if the schemas should remain isolated)
|
|
GRANT SELECT ON vector.foto_vektor TO vectors_user;
|
|
|
|
EXIT;
|