[FB4] An improved support of UDF SCALAR_ARRAY-arguments

[dmitry-lipetsk]

Hello.

It is the implementation of the second solution to issue #7843.

These changes force a server to do two things:

1. Generation of names for INPUT-arguments of UDF and saving these names in RDB$FUNCTION_ARGUMENT::RDB$ARGUMENT_NAME.

UDF

DECLARE EXTERNAL FUNCTION UDF_DUMMY4_RPN_2 smallint, integer, float, double precision
RETURNS PARAMETER 2
ENTRY_POINT 'IB_UDF_abs' MODULE_NAME 'ib_udf';

Metadata

select x.RDB$ARGUMENT_POSITION, x.RDB$ARGUMENT_NAME from RDB$FUNCTION_ARGUMENTS x
where x.RDB$FUNCTION_NAME='UDF_DUMMY4_RPN_2'

2. Passing to client the names of UDF and SCALAR_ARRAY-argument those are linked with DSQL parameter.

DECLARE EXTERNAL FUNCTION MY_UDF__GET_DIM
INTEGER BY SCALAR_ARRAY
RETURNS INTEGER BY VALUE
ENTRY_POINT 'fn_get_dim__i4__sa' MODULE_NAME 'lcpi.test.udf.01.dll'

select MY_UDF__GET_DIM(?) from rdb$database;

-- ISQL info:
INPUT message field count: 1
01: sqltype: 540 ARRAY Nullable scale: 0 subtype: 0 len: 8
  :  name: ARG1  alias:
  : table: MY_UDF__GET_DIM  owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
  :  name: MY_UDF__GET_DIM  alias: MY_UDF__GET_DIM
  : table:   owner:

It allows client to read the information about array element (type id, subtype, scale, charset id) and create a correct array.

---

If this patch is OK, I will create more complex tests for it on my side before final merging.

Thanks.

[dmitry-lipetsk]

Test database with generated names for UDF input-arguments. Number of UDFs - 9905.

IBP_TEST_FB40_D3.ZIP

[dmitry-lipetsk]

SQL for check the names of argument (in IBP_TEST_FB40_D3)

/*check argument positions*/
select 'arg pos', T0.RDB$FUNCTION_NAME as FUNC, T0.RDB$ARGUMENT_NAME as ARG_NAME
 from RDB$FUNCTION_ARGUMENTS T0
where T0.RDB$ARGUMENT_POSITION is NULL
union all
/*check udf input-argument names*/
select 'udf in-arg', T1.FUNC, T1.ARG_NAME from
(select count(*) N, x.RDB$FUNCTION_NAME FUNC,  x.RDB$ARGUMENT_NAME ARG_NAME
  from RDB$FUNCTIONS f
   join RDB$FUNCTION_ARGUMENTS x
    join RDB$FUNCTION_ARGUMENTS x2
    on x.RDB$FUNCTION_NAME=x2.RDB$FUNCTION_NAME and x.RDB$ARGUMENT_POSITION>=x2.RDB$ARGUMENT_POSITION and (x.RDB$PACKAGE_NAME=x2.RDB$PACKAGE_NAME or x.RDB$PACKAGE_NAME is null and x2.RDB$PACKAGE_NAME is null)
   on f.RDB$FUNCTION_NAME=x.RDB$FUNCTION_NAME and (f.RDB$PACKAGE_NAME=x.RDB$PACKAGE_NAME or f.RDB$PACKAGE_NAME is null and x.RDB$PACKAGE_NAME is null)
  where coalesce(f.RDB$RETURN_ARGUMENT,0)<>x.RDB$ARGUMENT_POSITION
    and coalesce(f.RDB$RETURN_ARGUMENT,0)<>x2.RDB$ARGUMENT_POSITION
    and f.RDB$LEGACY_FLAG=1
  group by x.RDB$FUNCTION_NAME, x.RDB$ARGUMENT_POSITION, x.RDB$ARGUMENT_NAME
  order by x.RDB$FUNCTION_NAME, x.RDB$ARGUMENT_POSITION, x.RDB$ARGUMENT_NAME) as T1
where ('ARG'||T1.N)<>coalesce(T1.ARG_NAME,'')
union all
/*check stored function argument names*/
select 'sf in-arg', T2.FUNC, T2.ARG_NAME from
(select x.RDB$FUNCTION_NAME FUNC,  x.RDB$ARGUMENT_NAME ARG_NAME
  from RDB$FUNCTIONS f
   join RDB$FUNCTION_ARGUMENTS x
   on f.RDB$FUNCTION_NAME=x.RDB$FUNCTION_NAME and (f.RDB$PACKAGE_NAME=x.RDB$PACKAGE_NAME or f.RDB$PACKAGE_NAME is null and x.RDB$PACKAGE_NAME is null)
  where coalesce(f.RDB$RETURN_ARGUMENT,0)<>x.RDB$ARGUMENT_POSITION
    and f.RDB$LEGACY_FLAG=0) T2
where coalesce(T2.ARG_NAME,'')=''

Database is OK, when this query returns an empty result set.

[dmitry-lipetsk]

Hello,

I added the support of theses things in my code and made tests for them.

It is an archive with test logs and test database (without UDF dlls) -https://dropmefiles.com/dgfM4 (this link is valid within 2 weeks)

These tests check the following things:

1. database metadata (MSSQL linked server compliance tests)
2. types of scalar_array arguments in DSQL - select UDF(?) from rdb$database.
3. The transfer of arrays in UDF through DSQL-parameters.

I tested all the standard types plus CSTRING.

All is OK. There are only one problem with UDF_DUMMY2_BSA__BLOB (see #7877).

---

I wait when you approve the PR #7870.

After that I will merge with this PR the update of GBAK

• https://github.com/dmitry-lipetsk/firebird/commit/08dc802105e27e4badbefb7b2bbc80b3abb0488c
• https://github.com/dmitry-lipetsk/firebird/commits/fb4-fix7843-001--gbak2

These changes in GBAK are not necessary to me but they allows to upgrade/downgrade exist databases and make this PR more clear.

I can apply changes in GBAK later (in separate PR) to reduce the number of changes.

Thanks.

[dmitry-lipetsk]

Hello,

https://dropmefiles.com/yYJqu (this link is valid within 2 weeks)

This archive contains results of updated gbak tests

• restore to FB4.0.5 from FB2.0-FB4.0.4 backups with different FIX_UDF_ARG_NAMES modes
• restore to FB3
• C++ test of restore through FB-Services

All works OK.

[dmitry-lipetsk]

An additional idea about UDF-argument naming.

It might make sense to use the "RDB$ARG" format for it.