Catalog and schema support for SQL functions – difficulty interesting EasyHack

LibreOffice has a database application called Base. It can connect to various database management systems, and is integrated with two internal database engines: Firebird and HSQLDB. Here I discuss how to add catalog and schema support for SQL functions in LibreOffice Base.

SQL window

One can use SQL to create and use internal functions. For example, with Firebird:

CREATE FUNCTION F(X INT) RETURNS INT
AS
BEGIN
  RETURN X+1;
END;

To run this, you can use “Tools > SQL…”, and then write the above SQL query. To see the result, you need to run this query:

SELECT F(5) FROM RDB$DATABASE;

Catalog and schema support

On the other hand, support for SQL commands is limited. For example, as the issue tdf#95174 describes, SQL parser of LibreOffice parser currently does not handle catalog and schema in function names:

• tdf#95174 – SQL parser doesn’t support catalog&schema in function name

Currently, this command  works fine:

SELECT function_name(a, b) FROM C

But this one does not:

SELECT schema_name.function_name(a, b) FROM C

The goal is to make the second one also work in LibreOffice Base.

Code Pointers

To add the support for catalog and schema in function names, you should refer to the Yacc rule for the SQL Parer.

Lionel, the experienced Base developer describes what to do in the first comment. In the file connectivity/source/parse/sqlbison.y, you can find this rule

function_name:
		string_function
	|	date_function
	|	numeric_function
	|	SQL_TOKEN_NAME

Here, you should add two new cases, like:

	|	SQL_TOKEN_NAME '.' SQL_TOKEN_NAME 
			{$$ = SQL_NEW_RULE;
			$$->append($1);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($3);
			}
	|	SQL_TOKEN_NAME '.' SQL_TOKEN_NAME '.' SQL_TOKEN_NAME
			{$$ = SQL_NEW_RULE;
			$$->append($1);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($3);
			$$->append(newNode(".", SQLNodeType::Punctuation));
			$$->append($5);}

After that, one should invoke this command:

git grep -E '(function_name|set_fct_spec)'

to find parts of the code that use them.

If the code is examining one of the above nodes, it expects a single token at the function_name. The code should be changed to expect a token or a node to handle the schema_name and function_name.

Final Notes

An implementation should be accompanied with a test to make sure that the code actually works, and will remain fine in the future changes. To see other discussed EasyHacks, follow the EasyHacks tag in this blog.