Firebird 3 and SP in Object Pascal

[Jacek Laskowski 57]

Firebird 3 has very interesting possibilities. Among other things:

 

"Object-oriented C++ APIs enable external code routines to plug in and run safely inside Firebird engine space, including (but not limited to):

•  Encryption schemes for data
•  User authentication schemes, including secure key exchange
•  Eventually, plug-in support for stored procedures, triggers and functions written in Java, C++, ObjectPascal, etc."

 

The quote comes from here: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-new-sumry.html

 

I am particularly interested in the last point, the use of SP written in Delphi. Unfortunately, I haven't found a piece of code anywhere that would show how to do it.

 

Does anyone know any documentation or examples?

[Markus Kinzler 174]

https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-engine.html#rnfb30-engine-extengines

https://github.com/FirebirdSQL/firebird/tree/master/examples/udr

http://www.firebirdsql.org/file/community/ppts/fbcon11/Firebird3Plugins2011.pdf

[Fr0sT.Brutal 900]

From what I saw regarding UDR's it's just a bloated nightmare even for elementary things. And they haven't got too much advantages over UDF's except sharing current connection/transaction handles.

[Guest]

I would agree with @Fr0sT.Brutal, if i understood the post above 100%, Postgres have had "Procedure Plugin" for years as i would guess M$SQL also have.

 

Now, why would anyone want to go over the hazzle to connect compiled code for IN- and OUT stuff and access all the objects through error-prone pointers and the like? Only if you have employees that cannot learn PSQL i assume.

 

BTW - if i was to evaluate a system for recommendations (it happens, not too often though) i'd put a big minus for introducing the possibility to call OS functions from inside the server processes.

 

There's not much wrong with PSQL, we just need good "IDEs" for it. IBExpert offers debugging with breakpoints and value-peek for PSQL procs and triggers. I'd guess other admin tools do too.

 

I am a little bit interested though, is there any RDBMS-manager that provides debugging for "library-code" (i.e. Java, OP, C# et. al)? How would that work?

[Fr0sT.Brutal 900]

1 hour ago, Dany Marmur said:

I am a little bit interested though, is there any RDBMS-manager that provides debugging for "library-code" (i.e. Java, OP, C# et. al)? How would that work? 

M$ tools maybe... Oracle probably (as they both own the language itself) but I doubt about other ones. Anyway the debugger could be attached to any running process so these DLL's could be debugged with RAD studio / MS VS / whatever.

 

[Guest]

1 hour ago, Fr0sT.Brutal said:

Anyway the debugger could be attached

Of course. That's what i do with services sometimes. Silly me. Thanks.

[Frickler 11]

23 hours ago, Markus Kinzler said:

https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-engine.html#rnfb30-engine-extengines

https://github.com/FirebirdSQL/firebird/tree/master/examples/udr

http://www.firebirdsql.org/file/community/ppts/fbcon11/Firebird3Plugins2011.pdf

Pascal example:

 

https://github.com/Arcantar/FirebirdSQL_Sample

[Lars Fosdal 1792]

On 11/5/2019 at 2:33 PM, Fr0sT.Brutal said:

M$ tools maybe... Oracle probably (as they both own the language itself) but I doubt about other ones. Anyway the debugger could be attached to any running process so these DLL's could be debugged with RAD studio / MS VS / whatever.

 

 

We use a couple of C# assemblies - mostly for being able to break down stuff like bar code scan strings into AIs, or other forms of string analysis, validation (checksums) or manipulation.
It is a bit cumbersome, tbh, but it is a lot more efficient than trying to do the same stuff in T-SQL.

[Joseph MItzen 251]

On 11/5/2019 at 2:33 AM, Jacek Laskowski said:

Firebird 3 has very interesting possibilities. Among other things:

•  Eventually, plug-in support for stored procedures, triggers and functions written in Java, C++, ObjectPascal, etc."

 

I began looking at Firebird in 2005. I was told that support for writing functions in other languages was coming in Firebird 3.0, which was going to be coming shortly. It came 10 years later. 😭 And of course, no support for writing functions in Delphi, etc. yet. One would be much better off choosing PostgreSQL, which is also open source, has a long history of approximately yearly releases, is highly extensible (create your own data types, indexes, etc.), has a large plugin ecosystem, and allows for inlining code in many languages. Of course, there are also many enterprise-grade features such as parallel queries, significant replication support, etc. I'm not aware of FreePascal language support, but that would only be due to lack of interest; the plugin system can certainly support it. I've even seen a plugin for LOLcode.

[Vandrovnik 214]

I wrote UDF for Firebird in Delphi in year 2010 and cross compiled it for linux using FreePascal, so writing functions in Delphi already works quite a long time...

[Guest]

Nervermind...

Edited November 8, 2019 by Guest

[Joseph MItzen 251]

5 minutes ago, Vandrovnik said:

I wrote UDF for Firebird in Delphi in year 2010 and cross compiled it for linux using FreePascal, so writing functions in Delphi already works quite a long time...

There's a difference between writing code in an external IDE, compiling it, and having a system administrator install it versus simply enabling a user to inline another language along with their SQL code.

 

[Vandrovnik 214]

6 minutes ago, Joseph MItzen said:

There's a difference between writing code in an external IDE, compiling it, and having a system administrator install it versus simply enabling a user to inline another language along with their SQL code.

 

But these routines also have to be installed on the machine where Firebird server is running (that is how I understood "run safely inside Firebird engine space"), or am I wrong?

[Joseph MItzen 251]

36 minutes ago, Vandrovnik said:

But these routines also have to be installed on the machine where Firebird server is running (that is how I understood "run safely inside Firebird engine space"), or am I wrong?

If I understood the original post, what's being talked about is the ability to write procedural code in other languages directly within SQL statements being sent to the server. PostgreSQL offers this, and SQL Server offers this (for .NET languages). For example, in PostgreSQL if Python is installed on the server and the python language support is enabled in the database:

jmadden=> CREATE FUNCTION pymax (a integer, b integer)
          RETURNS integer
          AS $$
             if a > b:
                 return a
             return b
          $$ LANGUAGE plpythonu;
CREATE FUNCTION

jmadden=> select pymax(1, 2);

 pymax
-------
   2
(1 row)

Here a user was able to send a function to the server just like a regular SQL statement and then call it. Such code could be in the middle of a batch of SQL statements. Triggers can be coded in other languages, custom aggregates, etc.

 

Firebird doesn't offer this level of support for inlining other languages today.

[Fr0sT.Brutal 900]

On 11/9/2019 at 1:45 AM, Joseph MItzen said:

Firebird doesn't offer this level of support for inlining other languages today.

AFAIU Firebird guys prefer perf and stability over language abilities. While it's pretty hard to break a server with just SQL, it is much easier with DLL. And calling a scripting lang like Python from SQL is just a perf killer. Btw, I guess there's also security that is concerned. What if a non-privileged user executes a query with "rm -rf" of "cat /etc/passwords"?

[Joseph MItzen 251]

10 hours ago, Fr0sT.Brutal said:

AFAIU Firebird guys prefer perf and stability over language abilities. While it's pretty hard to break a server with just SQL, it is much easier with DLL. And calling a scripting lang like Python from SQL is just a perf killer. Btw, I guess there's also security that is concerned. What if a non-privileged user executes a query with "rm -rf" of "cat /etc/passwords"? 

Well, again, they're working towards adding other language abilities, so it's not like it's been rejected on design grounds. As for security, some of the languages available with PostgreSQL come in "trusted" and "untrusted" modes. For instance, from the PostgreSQL documentation:
 

Quote

Normally, PL/Perl is installed as a "trusted" programming language named plperl. In this setup, certain Perl operations are disabled to preserve security. In general, the operations that are restricted are those that interact with the environment. This includes file handle operations, require, and use (for external modules). There is no way to access internals of the database server process or to gain OS-level access with the permissions of the server process, as a C function can do. Thus, any unprivileged database user may be permitted to use this language.

 

And of course any of these other languages will need to be enabled by a system administrator. Regarding "rm -rf" or the like, the PostgreSQL database should be set up to run under its own account with very limited privileges and its own group. In PostgreSQL, only database superusers can create functions with an untrusted language.

 

Now as for performance, there are two points. The first regards performing actions on a large volume of data.It can be far quicker to perform the action on the server, even via Python, than to move the entire body of data to the target machine and perform that action locally. The second is that other languages can allow the end user to easily perform actions that would be incredibly difficult via the inbuilt procedural SQL language, hence it's a performance enhancer.

 

To quote from the anonymous author of "PostgreSQL Vs. MS Server":

Quote

"Pure" declarative SQL is good at what it was designed for – relational data manipulation and querying. You quickly reach its limits if you try to use it for more involved analytical processes, such as complex interest calculations, time series analysis and general algorithm design. SQL database providers know this, so almost all SQL databases implement some kind of procedural language. This allows a database user to write imperative-style code for more complex or fiddly tasks.

PostgreSQL's procedural language support is exceptional. It's impossible to do justice to it in a short space, but here's a sample of the goods. Any of these procedural languages can be used for writing stored procedures and functions or simply dumped into a block of code to be executed inline.

• PL/PGSQL: this is PostgreSQL's native procedural language. It's like Oracle's PL/SQL, but more modern and feature-complete.

• PL/V8: the V8 JavaScript engine from Google Chrome is available in PostgreSQL. This engine is stable, feature-packed and absurdly fast – often approaching the execution speed of compiled, optimised C. Combine that with PostgreSQL's native support for the JSON data type (see below) and you have ultimate power and flexibility in a single package.

  Even better, PL/V8 supports global (i.e. cross-function call) state, allowing the user to selectively cache data in RAM for fast random access. Suppose you need to use 100,000 rows of data from table A on each of 1,000,000 rows of data from table B. In traditional SQL, you either need to join these tables (resulting in a 100bn row intermediate table, which will kill any but the most immense server) or do something akin to a scalar subquery (or, worse, cursor-based nested loops), resulting in crippling I/O load if the query planner doesn't read your intentions properly. In PL/V8 you simply cache table A in memory and run a function on each of the rows of table B – in effect giving you RAM-quality access (negligible latency and random access penalty; no non-volatile I/O load) to the 100k-row table. I did this on a real piece of work recently – my PostgreSQL/PLV8 code was about 80 times faster than the MS T-SQL solution and the code was much smaller and more maintainable. Because it took about 23 seconds instead of half an hour to run, I was able to run 20 run-test-modify cycles in an hour, resulting in feature-complete, properly tested, bug-free code....

• PL/Python: you can use full Python in PostgreSQL. Python2 or Python 3, take your pick, and yes, you get the enormous ecosystem of libraries for which Python is justifiably famous. Fancy running a SVM from scikit-learn or some arbitrary-precision arithmetic provided by gmpy2 in the middle of a SQL query? No problem!

• PL/Perl: Perl has been falling out of fashion for some time, but its versatility earned it a reputation as the Swiss army knife of programming languages. In PostgreSQL you have full Perl as a procedural language.

• PL/R: R is the de facto standard statistical programming environment in academia and data science, and with good reason - it is free, robust, fully-featured and backed by an enormous library of high-quality plugins and add-ons. PostgreSQL lets you use R as a procedural language.

 

Python and R have become the dominant languages in data analysis, and machine learning which is why MS SQL Server has added support for them (along with .NET languages). Being able to use a machine learning routine or do logistic regression right on the server inlined with SQL is a really powerful feature.

[Guest]

@Fr0sT.Brutal, yes! @Joseph MItzen like Lars von Trier says at the en of some of his movies, "must take the good with the bad". Thanks for the quotes, pertinent. I'll eventually will have to jump on some of the trains that my competitors are riding. But still. With a good eco-system i have been able to keep things apart. Each monster in it's own wardrobe. There's a HUGE amount of processing that can be efficiently done using a service connecting if you really use the "wonders" of ACID transaction handling. FB-FTW 🙂