Database¶
LP supports Oracle and Postgres databases for Faces service and Postgres database for Events service.
DB matching installation¶
To allow matching in db, one needs to:
Compile a library with a VLMatch function using appropriate database development libraries.
Import the library in the running database and check it availability.
See luna_python_matcher/db/vlmatch/postgres/readme.md for Postgres Database:
Instruction for PostgreSQL database¶
Standard matching option¶
You can find all the required files for the VLMatch user-defined extension (UDx) compilation in the following directory:
/var/lib/luna/current/extras/VLMatch/postgres/
The following instruction describes installation for PostgreSQL 12.
For VLMatch UDx function compilation one needs to:
Make sure, that PostgreSQL of the required version is installed and launched.
Install the required PostgreSQL development environment. You can find more information at postgres installation manual.
The llvm-toolset-7-clang is required for postgresql12-devel. Install it from the centos-release-scl-rh
repository.
yum -y install centos-release-scl-rh
yum -y --enablerepo=centos-sclo-rh-testing install llvm-toolset-7-clang
Then install the development environment.
sudo yum -y install postgresql12 postgresql12-server postgresql12-devel
Install the gcc-c++ package. The package version 4.8 or higher is required.
yum -y install gcc-c++.x86_64
Install CMAKE. The version 3.5 or higher is required.
Open the make.sh script using a text editor. It includes paths to the currently used PostgreSQL version. Change the following values (if necessary):
SDK_HOME
specifies the path to PostgreSQL home directory. The default value is/usr/pgsql-12/include/server
;LIB_ROOT
specifies the path to PostgreSQL library root directory. The default value is/usr/pgsql-12/lib
.
Go to the make.sh script directory and run it:
cd /var/lib/luna/current/extras/VLMatch/postgres/
chmod +x make.sh
./make.sh
Define the function inside the service database:
sudo -u postgres -h 127.0.0.1 -- psql -d luna_events -c "CREATE FUNCTION VLMatch(bytea, bytea, int) RETURNS float8 AS 'VLMatchSource.so', 'VLMatch' LANGUAGE C PARALLEL SAFE;"
Test function by sending re following request to the service database:
sudo -u postgres -h 127.0.0.1 -- psql -d luna_events -c "SELECT VLMatch('\x1234567890123456789012345678901234567890123456789012345678901234'::bytea, '\x0123456789012345678901234567890123456789012345678901234567890123'::bytea, 32);"
The result returned by the database must be “0.4765625”.
Alternative matching options¶
When it is not possible to use postgres C-extensions for some reason, an alternative SQL user defined function can be used for matching. Define the SQL-VLMatch function inside the service database:
CREATE OR REPLACE FUNCTION vlmatch(bytea, bytea, int) RETURNS float AS
-- original `vlmatch` function compatible interface
$BODY$
DECLARE
acc float = 0;
BEGIN
FOR i IN 0..$3-1 LOOP
acc = acc + (get_byte($1, i) - get_byte($2, i)) ^ 2.0;
END LOOP;
RETURN acc / (128 * 128);
END;
$BODY$
LANGUAGE plpgsql PARALLEL SAFE IMMUTABLE;
Test the function by the following query to the database:
SELECT * FROM vlmatch('\x1234567890123456789012345678901234567890123456789012345678901234'::bytea, '\x0123456789012345678901234567890123456789012345678901234567890123'::bytea, 32)
The result returned by the database must be “0.4765625”.
⚠️Beware the performance penalty. When using the SQL-VlMatch, the query execution time is about two orders of magnitude slower than when using the standard C-VLMatch. Also make sure your database has query parallelization configured.
See also luna_python_matcher/db/vlmatch/oracle/readme.md for Oracle Database:
For VLMatch UDx function compilation one needs to:
Install required environment, see requirements:
sudo yum install gcc g++ # needed gcc/g++ 4.8 or higher
Change
SDK_HOME
variable - oracle sdk root (default is$ORACLE_HOME/bin
, check$ORACLE_HOME
environment variable is set) in the makefile and run make frombase_scripts/database_matching/oracle
directory:chmod +x make.sh
./make.sh
Define the library and the function inside the database (from database console):
CREATE OR REPLACE LIBRARY VLMatchSource AS '$ORACLE_HOME/bin/VLMatchSource.so'; CREATE OR REPLACE FUNCTION VLMatch(descriptorFst IN RAW, descriptorSnd IN RAW, length IN BINARY_INTEGER) RETURN BINARY_FLOAT AS LANGUAGE C LIBRARY VLMatchSource NAME "VLMatch" PARAMETERS (descriptorFst BY REFERENCE, descriptorSnd BY REFERENCE, length UNSIGNED SHORT, RETURN FLOAT);
Test function within call (from database console):
SELECT VLMatch(HEXTORAW('1234567890123456789012345678901234567890123456789012345678901234'), HEXTORAW('0123456789012345678901234567890123456789012345678901234567890123'), 32) FROM DUAL;
– result should be “0.4765625”