Database¶

LP supports Oracle and Postgres databases for Faces service and Postgres database for Events service.

Multiple hosts¶

The dsn parameter of service database config should be specified to enable multihost option for Postgres.

DSN may be specified in the following format: luna:luna@postgres01:5432,postgres02:5432/luna_faces?some_option=some_value. It is possible to partially fill in the DSN string (e.g. postgres01,postgres02/luna_faces), and then the missing parameters will be filled in from proprietary parameter values (or defaults).

At startup, the service will create a pool of connections to one of DSN hosts available. In case of problems with establishing a connection after several unsuccessful attempts, the service will again try to set up a connection pool to any of DSN hosts available.

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 from base_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”