Перейти к основному содержимому

psql_bfile. Составной тип bfile для доступа к внешнему файлу

Версия: 1.0.

В исходном дистрибутиве установлено по умолчанию: да.

Связанные компоненты: отсутствуют.

Сведения

Функциональность доступна только для редакций Enterprise и Enterprise для ERP-систем.

Для облегчения миграции с СУБД Oracle, добавляются таблицы, типы данных и набор хранимых процедур, позволяющие получить доступ к содержимому файлов из внешнего хранилища по аналогии с функциональностью Oracle BFILEs.

Добавленные объекты позволяют: сохранить URL к контейнерам файлов (директорий файловой системы, корзины S3, базовый путь к файлам, получаемый по HTTP и т.п.) и параметры подключения к ним, сохранить местоположение файлов в данных контейнерах в произвольных таблицах БД, получить содержимое этих файлов.

примечание

psql_bfile располагается в папке contrib.

Для указания местоположения файла во внешнем хранилище добавляется тип данных bfile. В нем содержится наименование псевдонима каталога и наименование файла в этом каталоге. Длина псевдонима и длина наименования файла ограничены 255 символами.

Для него добавляются операторы преобразования:

  • text -> bfile – преобразует строку формата dir_alias:file_name в значение типа bfile;
  • bfile -> text – преобразует значение типа bfile в строку формата dir_alias:file_name.

Для хранения URL к внешнему хранилищу добавляется таблица bfile_directory. Длина псевдонима ограничена 255 символами, длина пути к каталогу ограничены 4096 символами:

Тип столбцаОписание
dir_alias textПсевдоним каталога
dir_url textПуть к директории на хосте БД или URL каталога
dir_options text[]Дополнительные параметры, необходимые для обращения к контейнеру по его URL, в виде строк ключ=значение

Для ограничения доступа к файловой системе хоста БД добавляются конфигурационные параметры (в /etc/pangolin-manager/postgres.yml (/pd_data/06/data/postgresql.conf), секция postgresql|parameters):

ПараметрОписание
psql_bfile.allowed_file_directories (string)Содержит список директорий, на которые (на них самих или вложенных в них) можно ссылаться при добавлении записи в таблицу bfile_directory. По умолчанию содержит пустую строку
psql_bfile.read_file_timeoutСодержит интервал в миллисекундах, в течении которого ожидается доступность файлового дескриптора для чтения в соответствующих методах

Функции​

Функции управления данными в таблице каталогов​

ФункцияОписание
bfile_directory_create ( dir_alias text, dir_url text [, dir_options text[]]) -> voidСоздает каталог для заданных псевдонима и пути
bfile_directory_read ( dir_alias text ) → record ( dir_alias text, dir_url text, dir_options text )Возвращает данные для указанного псевдонима
bfile_directory_update ( dir_alias text, dir_url text [, dir_options text[]]) -> voidЗаменяет данные указанного псевдонима. Длина пути каталогу ограничена 4096 символами
bfile_directory_delete ( dir_alias text ) -> voidУдаляет запись с данными для указанного псевдонима
bfile_directory_rename ( dir_alias text, new_dir_alias text ) -> voidИзменяет псевдоним каталога
bfile_directory_getoptions( dir_alias text ) -> record( dir_options text[] )Возвращает значение опций, используемых для соединения с контейнером, связанных с указанным псевдонимом
bfile_directory_setoptions( dir_alias text, dir_options text[] ) -> voidЗаменяет значение опций, используемых для соединения с контейнером, связанных с указанным псевдонимом
bfile_directory_geturl( dir_alias text ) -> record( dir_path text )Возвращает значение URL контейнера, связанного с указанным псевдонимом
bfile_directory_seturl ( dir_alias text, dir_path text ) -> voidЗаменяет значение URL контейнера, связанного с указанным псевдонимом

Функции предоставления и отзыва прав доступа к каталогам​

ФункцияОписание
bfile_grant_directory ( role_name name, dir_alias text, priv_type text ) -> voidПредоставляет права доступа priv_type к каталогу dir_alias пользователю/роли role_name. Права, предоставленные групповой роли, наследуются всеми ее членами и их наследниками. Если указать псевдокаталог '*', пользователю будут предоставлены права на все каталоги в целом
bfile_revoke_directory ( role_name name, dir_alias text, priv_type text ) -> voidОтзывает права доступа к каталогу dir_alias у пользователя/роли role_name. Если указать псевдокаталог '*', то у пользователя будут отозваны права на все каталоги в целом. Имеется ограничение: нет возможности отозвать право на отдельный каталог, если это право было выделено на все каталоги сразу
bfile_has_directory_privilege ( role_name name, dir_alias text [, priv_type text]) -> boolПроверяет доступ пользователя к каталогу. По умолчанию priv_text равен SELECT. Если указать псевдокаталог '*', то будут проверен доступ на все каталоги в целом
bfile_cleanup_directory_roles ( ) -> voidУдаляет из таблицы bfile_directory_roles информацию о правах доступа к каталогам для удаленных пользователей/ролей

Функции проверки на вменяемость локатора​

ФункцияОписание
bfilename ( dir_alias text, filename text ) -> bfileСоздает локатор файла с указанными псевдонимом каталог и именем файла
bfile_fileexists ( file_loc bfile ) -> boolПроверяет наличие файла во внешнем хранилище по его локатору
bfile_filegetname ( file_loc bfile ) -> record ( dir_alias text, filename text )Возвращает псевдоним контейнера и имя файла по его локатору

Функции для работы с файлами, связанными с локаторами​

ФункцияОписание
bfile_fileopen ( file_loc bfile ) -> intОткрывает файл для чтения по его локатору и возвращает его дескриптор. Открытый дескриптор существует в рамках сеанса (процесса), в котором был открыт, и не доступен из других сеансов (процессов)
bfile_fileisopen ( file_handle int ) -> boolПроверяет валидность дескриптора и его состояние, открыт для чтения или нет
bfile_fileclose ( file_handle int ) -> voidЗакрывает файл по его дескриптору
bfile_filecloseall ( ) -> voidЗакрывает все открытые в данном сеансе (процессе) файлы
bfile_fileread ( file_handle int [, amount bigint [, offset bigint]]) -> record ( amount bigint, buffer bytea )Считывает amount байт со смещением offset из открытого файла с дескриптором handler и возвращает пару (количество прочитанных байт, прочитанные байты). По умолчанию параметр amount равен -1, а параметр offset равен 0. Значение amount не может быть больше 256 Мбайт. Если amount < 0, то будет прочитано до конца файла возможно большее количество байт (но не больше 256Мбайт) Если offset < 0, то смещение берется от конца файла
bfile_filecompare ( file_handle1 int, file_handle2 int, amount bigint [, offset1 bigint [, offset2 bigint]]) -> intСравнивает содержимое двух открытых файлов. Количество сравниваемых байт задается в amount. В offset1, offset2 смещение соответственно в первом файле и во втором файле. По умолчанию offset1 = 0, и offset2 = 0. Значение amount не может быть больше 256 Мбайт. Если offset1 < 0 или offset2 < 0, то смещение берется от конца соответствующего файла. Возвращает - 0, если содержимое двух файлов равны, отрицательное значение, если содержимое первого файла стоит перед содержимым второго файла в лексикографическом порядке, положительное значение, если наоборот

Функции для работы с содержимым файла непосредственно по его локатору​

ФункцияОписание
bfile_getlength ( file_loc bfile ) -> bigintВозвращает длину файла для заданного значения file_loc
bfile_read ( file_loc bfile [, amount bigint [, offset_ bigint]]) -> record ( amount bigint, buffer bytea )Считывает amount байт со смещением offseе из файла по его локатору file_loc и возвращает пару (количество прочитанных байт, прочитанные байты). По умолчанию параметр amount равен -1, а параметр offset равен 0. Значение amount не может быть больше 256 Мбайт. Если amount < 0, то будет прочитано до конца файла возможно большее количество байт (но не больше 256Мбайт). Если offset < 0 то смещение берется от конца файла
bfile_compare ( file_loc1 bfile, file_loc2 bfile, amount bigint [, offset1 bigint [, offset2 bigint]]) -> intСравнивает содержимое двух файлов по их локатору. Количество сравниваемых байт задается в amount. В offset1, offset2 смещение соответственно в первом файле и во втором файле. По умолчанию offset1 = 0, и offset2 = 0. Значение amount не может быть больше 256 Мбайт. Если offset1 < 0 или offset2 < 0, то смещение берется от конца соответствующего файла. Возвращает - 0, если содержимое двух файлов равны, отрицательное значение, если содержимое первого файла стоит перед содержимым второго файла в лексикографическом порядке, положительное значение, если наоборот

Представления​

Получение списка всех каталогов​

Для получения списка всех каталогов реализовано представление bfile_dba_directories:

Тип столбцаОписание
dir_alias textПсевдоним каталога
dir_url textПуть к директории на хосте БД или URL каталога
dir_options text[]Дополнительные параметры, необходимые для обращения к контейнеру по его URL, в виде строк ключ=значение

Получение списка доступных текущему пользователю каталогов​

Для получения списка доступных текущему пользователю каталогов используется представление bfile_all_directories :

Тип столбцаОписание
dir_alias textПсевдоним каталога
dir_options text[]Дополнительные параметры, необходимые для обращения к контейнеру по его URL, в виде строк ключ=значение
dir_url textПуть к директории на хосте БД или URL каталога

Получение списка прав доступа к каталогам​

Для хранения предоставленных прав доступа к каталогам добавлена таблица bfile_directory_rights:

Тип столбцаОписание
dir_alias textПсевдоним каталога
dir_acl aclitem[]Список предоставленных прав доступа к каталогу

Для всей таблицы можно предоставить права: INSERT, SELECT, UPDATE, DELETE. Для отдельной записи можно предоставить права: SELECT, UPDATE. Пользователю, создавшему запись (успешное выполнение функции bfile_directory_create), предоставляются права: UPDATE, SELECT.

Таблица соответствия API Oracle BFILE и API Pangolin psql_bfile​

Oracle BFILEPangolin psql_bfile
DIRECTORY Objects
CREATE OR REPLACE DIRECTORY bfile_dir AS '/usr/home/scott';SELECT bfile_directory_create('bfile_dir', '/usr/home/bfile_dir'); SELECT bfile_directory_update('bfile_dir ', '/usr/home/bfile_dir');
DROP DIRECTORY bfile_dirSELECT bfile_directory_delete('bfile_dir ');
GRANT CREATE ANY DIRECTORY TO user_nameSELECT bfile_grant_directory('user_name', '*', 'INSERT,UPDATE');
GRANT DROP ANY DIRECTORY TO user_nameSELECT bfile_grant_directory('user_name', '*', 'DELETE');
REVOKE CREATE ANY DIRECTORY FROM user_nameSELECT bfile_revoke_directory('user_name', '*', 'INSERT,UPDATE');
REVOKE DROP ANY DIRECTORY FROM user_nameSELECT bfile_revoke_directory('user_name', '*', 'DELETE');
GRANT READ ON DIRECTORY bfile_dir TO user_nameSELECT bfile_grant_directory('user_name', 'bfile_dir', 'SELECT');
REVOKE READ ON DIRECTORY bfile_dir FROM user_nameSELECT bfile_revoke_directory('user_name', 'bfile_dir', 'SELECT');
SELECT * FROM ALL_DIRECTORIES(OWNER,DIRECTORY_NAM,DIRECTORY_PATH)SELECT * FROM bfile_all_directories;
SELECT * FROM DBA_DIRECTORIES(OWNER,DIRECTORY_NAM,DIRECTORY_PATH)SELECT * FROM bfile_dba_directories;
BFILE Locators
BFILE typebfile type
BFILE APIs
Sanity Checking
DBMS_LOB.FILEEXISTS ( file_loc IN BFILE) RETURN INTEGER;bfile_fileexists ( file_loc bfile ) -> bool
DBMS_LOB.FILEGETNAME ( file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2);bfile_filegetname ( file_loc bfile ) -> record ( dir_alias text, filename text )
BFILENAME('directory','filename')bfilename ( dir_alias text, filename text ) -> bfile
Open / Close
DBMS_LOB.OPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly); DBMS_LOB.FILEOPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);bfile_fileopen ( file_loc bfile ) -> int
DBMS_LOB.ISOPEN ( file_loc IN BFILE) RETURN INTEGER; DBMS_LOB.FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER;bfile_fileisopen ( file_handle int ) -> bool
DBMS_LOB.CLOSE ( file_loc IN OUT NOCOPY BFILE); DBMS_LOB.FILECLOSE ( file_loc IN OUT NOCOPY BFILE);bfile_fileclose ( file_handle int ) -> void
DBMS_LOB.FILECLOSEALL;bfile_filecloseall ( ) -> void
Read Operations
DBMS_LOB.GETLENGTH ( file_loc IN BFILE) RETURN INTEGER;bfile_getlength ( file_loc bfile ) -> bigint
DBMS_LOB.READ ( file_loc IN BFILE, amount IN OUT NOCOPY INTEGER, offset IN INTEGER, buffer OUT RAW);bfile_fileread ( file_handle int [, amount bigint [, offset bigint]]) -> record ( amount bigint, buffer bytea ) bfile_read ( file_loc bfile [, amount bigint [, offset_ bigint]]) -> record ( amount bigint, buffer bytea )
DBMS_LOB.SUBSTR ( file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;–
DBMS_LOB.INSTR ( file_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;–
Operations involving multiple locators
BFILE := BFILE–
DBMS_LOB.LOADCLOBFROMFILE, DBMS_LOB.LOADBLOBFROMFILE–
DBMS_LOB.COMPARE ( lob_1 IN BFILE, lob_2 IN BFILE, amount IN INTEGER, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER;bfile_filecompare ( file_handle1 int, file_handle2 int, amount bigint [, offset1 bigint [, offset2 bigint]]) -> int bfile_compare ( file_loc1 bfile, file_loc2 bfile, amount bigint [, offset1 bigint [, offset2 bigint]]) -> int

Ограничения​

В psql_bfile версии 1.0 можно ссылаться только на файлы хоста экземпляра БД.

Пример использования​

  1. Создайте каталог для хранения данных bfile:

    mkdir "/tmp/bfiles"

  2. Создайте файл с данными:

    echo -n '123456789012345678901234567890' /tmp/bfiles/file1

  3. Запустите скрипт:

    -- Создание расширения psql_bfile:
    CREATE EXTENSION psql_bfile;

    -- Добавление допустимых к работе папок
    ALTER SYSTEM SET psql_bfile.allowed_file_directories='/tmp';

    -- Применение изменений в конфигурации
    SELECT pg_reload_conf();

    -- Создание каталога в базе данных для хранения bfile:
    SELECT bfile_directory_create('BFILES', '/tmp/bfiles');

    -- Создание таблицы bfile и добавление в нее одной записи, ссылающейся на этот файл:
    CREATE TABLE bfile_table(id int, bf bfile);
    INSERT INTO bfile_table VALUES (1, bfilename('BFILES', 'file1'));

    -- Создание пользователя:
    CREATE USER user1;

    -- Предоставление прав user1 на схему public
    GRANT ALL PRIVILEGES ON SCHEMA public TO user1;

    -- Предоставление пользователю user1 прав на таблицу bfile_table:
    GRANT ALL ON bfile_table TO user1;

    -- Предоставление пользователю user1 права чтения файлов в BFILES:
    SELECT bfile_grant_directory('user1', 'BFILES', 'SELECT');

    -- Авторизация user1:
    SET ROLE user1;

    DO $$
    DECLARE
    v_bfile bfile;
    v_buffer bytea;
    v_length bigint;
    v_handler int;
    BEGIN
    -- Открытие файла для чтения и записи:
    SELECT bfile_fileopen(bf) INTO v_handler FROM bfile_table WHERE id = 1;

    -- Считывание строки символов из файла в буфер и вывод ее длины и содержимого:
    SELECT a_buffer FROM bfile_fileread(v_handler) INTO v_buffer;
    RAISE NOTICE 'Buffer length: %', length(v_buffer);
    RAISE NOTICE 'Buffer content: %', encode(v_buffer, 'escape');

    -- Закрытие bfile:
    PERFORM bfile_fileclose(v_handler);
    END $$;

    -- Проверка существования файла:
    SELECT bfile_fileexists('BFILES:file1');

    -- Получение и вывод длины файла:
    SELECT bfile_getlength('BFILES:file1');

    -- Проверка содержимого таблицы:
    SELECT a_amount, encode(a_buffer, 'escape') FROM
    (SELECT f.* FROM bfile_table, LATERAL bfile_read(bf) AS f) x;

    -- Удаление таблицы, пользователя и расширения psql_bfile:
    RESET ROLE;
    DROP TABLE bfile_table;
    REVOKE ALL PRIVILEGES ON SCHEMA public FROM user1;
    DROP USER user1;
    DROP EXTENSION psql_bfile;

  4. Скрипт выдает следующий результат:

    CREATE EXTENSION
    ALTER SYSTEM
     pg_reload_conf
    ----------------
     t
    (1 row)
     
     bfile_directory_create
    ------------------------
      
    (1 row)
     
    CREATE TABLE
    INSERT 0 1
    CREATE ROLE
    GRANT
    GRANT
     bfile_grant_directory
    -----------------------
      
    (1 row)
     
    SET
    psql:example.sql:49: NOTICE:  Buffer length: 30
    psql:example.sql:49: NOTICE:  Buffer content: 123456789012345678901234567890
    DO
     bfile_fileexists
    ------------------
     t
    (1 row)
     
     bfile_getlength
    -----------------
                  30
    (1 row)
     
     a_amount |             encode            
    ----------+--------------------------------
           30 | 123456789012345678901234567890
    (1 row)
     
    RESET
    DROP TABLE
    REVOKE
    DROP ROLE
    DROP EXTENSION