Построение отчетов - Использование защищенного интерпретатора в pltcl

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

CREATE OR REPLACE FUNCTION public.interp_test(script text)
RETURNS void AS
$BODY$

set script $1

interp create -safe slave
interp alias slave elog {} elog
if {[catch {
interp eval slave $script
} errmsg]} {
interp delete slave
elog ERROR $errmsg
}
interp delete slave

return

$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;
ALTER FUNCTION public.interp_test(text) OWNER TO offline;


select public.interp_test('elog NOTICE "Safe interp works fine!"');

В интерпретаторе определяем набор функций и необходимые переменные. Никакие иные действия, кроме вызова указанных функций и операций над переменными, не могут быть выполнены. В случае возникновения ошибки будет выдано соответствующее сообщение и исполнение функции корректно завершится. При использовании pltcl код исполняется в защищенном интерпретаторе, но мы явно указываем, что создаваемый интерпретатор является защищенным, так что эта функция будет корректно работать и в untrusted tcl.

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

А вот рабочее решение:

CREATE OR REPLACE FUNCTION reports.interp_checks_eval(script text, questions_list text, args text)
RETURNS text AS
$BODY$

array set scripts $1
set questions_list [lsort -dictionary -unique $2]
array set args $3

if { ![info exists args(cumulative)] } {
set args(cumulative) 0
}

spi_exec -array V "SELECT relname, pg_class.relkind as relkind FROM pg_class, pg_namespace
WHERE pg_class.relnamespace=pg_namespace.oid
AND pg_class.relkind IN ('r')
AND pg_namespace.nspname || '.' || relname='$args(table_out)'" {
if [info exists V(relname)] {
spi_exec "DROP TABLE $args(table_out) CASCADE"
}
}

set sql "create table $args(table_out) ("
foreach column $args(columns_key_list) {
spi_exec "SELECT data_type
FROM information_schema.columns
WHERE table_schema || '.' || table_name = '$args(table_in)' AND column_name = '$column'" {
append sql "$column ${data_type},"
}
}
append sql [join [array names scripts] " text, "] " text) WITHOUT OIDS"
spi_exec $sql

set func_names_list {}
foreach func [array names scripts] {
lappend func_names_list $func
set count [\
spi_exec "select code from reports.scripts where name='[quote $scripts($func)]'" {
set scripts($func) $code
}]
if { (![info exists args(allow_code)] || $args(allow_code) eq 0) && $count eq 0 } {
#elog ERROR "Не найден скрипт с названием '[quote $scripts($func)]'"
}
}

catch {interp delete slave}
interp create -safe slave
interp alias slave elog {} elog
interp alias slave quote {} quote

interp eval slave {
proc Result {question_id {answer_id {}} } {
upvar #0 answers_array answers_array
if { $answer_id eq {} } {
set answer_id $question_id
}
if { [info exists answers_array($answer_id)] } {
return $answers_array($answer_id)
} else {
return {}
}
}
}

set key_names_list {}
foreach key $args(columns_key_list) {
lappend key_names_list $key
}

# передать список вопросов указанный в параметрах вызова функции
interp eval slave [list set questions_list $questions_list]

if {[catch {
set count [\
spi_exec "select [join $args(columns_key_list) ,], answers_array,questions_list from $args(table_in)" {
interp eval slave [list array unset answers_array]
interp eval slave [list array set answers_array $answers_array]

set func_values_list {}
foreach func $func_names_list {
set result [interp eval slave [list eval $scripts($func)]]
lappend func_values_list "'[quote $result]'"
}

if { $args(cumulative) ne {1}} {
set key_values_list {}
foreach key $args(columns_key_list) {
upvar 0 $key value
lappend key_values_list "'[quote $value]'"
}

spi_exec "insert into $args(table_out) ([join $key_names_list ,], [join $func_names_list ,]) values ([join $key_values_list ,], [join $func_values_list ,])"
}
}]
} errmsg]} {
interp delete slave
elog ERROR $errmsg
}
interp delete slave

if { $args(cumulative) eq {1}} {
set key_values_list {}
foreach key $args(columns_key_list) {
lappend key_values_list "NULL"
}
spi_exec "insert into $args(table_out) ([join $key_names_list ,], [join $func_names_list ,]) values ([join $key_values_list ,], [join $func_values_list ,])"
}

if { $args(cumulative) ne {1}} {
foreach column $args(columns_key_list) {
spi_exec "CREATE INDEX [string map {. _} $args(table_out)]_${column}_idx ON $args(table_out) USING btree ($column)"
}
}

return $count

$BODY$
LANGUAGE 'pltcl' VOLATILE SECURITY DEFINER;

Приведенный ниже скрипт работает в одной из наших систем:

select reports.interp_checks_eval('data {
if { [Result 101 201] eq "1" } {
return "Работает"
}
if { [Result 101 202] eq "1" } {
return "Не работает"
}
if { [Result 101 203] eq "1" } {
return "Временно не работает"
}
if { [Result 101 204] eq "1" } {
return "Отказано"
}
return "Неизвестно"
}','','table_in cache.checks_yy07mm10 table_out reports.test_out column_data answers_array columns_key_list {check_date object_id user_id}

Разумеется, в таком виде скрипт изменять не очень удобно, однако после написания соответствующего транслятора пользователь может работать через веб-интерфейс (и не только) с его текстом в виде:

if { [Result {Статус точки} Работает] eq "1" } {
return "Работает"
}
if { [Result {Статус точки} {Не работает}] eq "1" } {
return "Не работает"
}
if { [Result {Статус точки} {Временно не работает}] eq "1" } {
return "Временно не работает"
}
if { [Result {Статус точки} Отказано] eq "1" } {
return "Отказано"
}
return "Неизвестно"

Логика анализа может быть и намного более сложной, а также можно указывать набор скриптов, которые будут выполнены над исходным набором данных. Заметим, что состояние интерпретатора сохраняется, что позволяет делать анализ с накоплением результата. Определив набор нужных функций и переменных, мы обеспечиваем возможность для анализатора получить любую хранимую в БД информацию и также применить ее в процессе анализа.

P.S. Приведенный код упрощен для иллюстрации идеи. Для промышленного использования необходимо обеспечить интерфейс доступа (на чтение) ко всей нужной информации, хранимой в БД. Но это совсем другая история.

P.P.S. Заметим, что на практике первой встает задача преобразования исходных данных в необходимый вид (тиклевский список, массив или более сложные структуры). Как показывает практика, в некоторых случаях можно ограничиться достаточно простым представлением (различные системы анкетирования, мерчендайзинга, etc.). Для систем документооборота и подобных получается довольно сложное представление данных, но указанный метод эффективен и для них.