Hi FreePascal and Firebird people,
(Cross-posted to the FPC and Firebird Development mailing lists)

1. Background
=============
For the upcoming Firebird 3.0 database server release, developers are working on allowing external languages (at first Java) for writing stored procedures and functions.
I found this, rather old, reference:
http://web.firebirdsql.org/index.php?op=devel&sub=plugins&id=external_java

FreePascal (FPC) is an object oriented variant of Pascal that looks very much like Delphi (in fact, there's a Delphi compatibility switch so you can port your existing code).

Would it be possible and desirable to have FreePascal as an embedded stored procedure/trigger language in some future release of Firebird? (I can't recall this having been brought up before, so I just start the ball rolling).

2. End user considerations
==========================
2.1 A lot of developers that use Firebird program in Delphi. As FreePascal syntax is (almost) identical, these developers can easily port their code between the database and the application.

2.2 FreePascal may be easier to learn than Java for some people and would make a nice alternative as opposed to e.g. including C or C++

2.3 FreePascal might not run/is not supported for some platforms that Firebird 3 will be released for. Perhaps HP-UX? Firebird 2.5 runs on Windows (x86/x64), Linux versions (x86/x64), Solaris (Sparc and Intel), HP-UX (PA-Risc) and Mac OSX.

3. Development considerations
========================
I only know enough to make wild guesses... that doesn't stop me from making them, though.

3.1 Obviously, Firebird must allow running compiled code (as opposed to running code in a Java Virtual Machine), presumably in a form of dynamic library.

3.2 Firebird must be modified to include (a stripped down version of?) the FPC compiler (or store the location of the compiler) and call it whenever the FreePascal source code in a procedure is changed. This might not be necessary for production servers as long as embedded Pascal code is compiled on developer machines and the resulting object code is loaded into the production database.

3.3 FreePascal will need to have some way to access, manipulate and return the data in the database and call other Firebird stored procedures, functions, etc. FPC already has good database access, including Interbase/Firebird support, but some kind of interface unit for direct access will need to be added.

3.4 FreePascal will need to use the Firebird memory manager. Using various memory managers is fortunately already possible in FPC.

3.5 Most importantlY: it will probably require a lot of work on both ends to get this done. Fortunately, both Firebird and FPC have an active developer community with good communication and bug tracker systems, so this helps.

Your thoughts?

Jim wrote

Hi FreePascal and Firebird people, (Cross-posted to the FPC and Firebird Development mailing lists)

[snip]

As a long-time firebird/interbase user, I would welcome this possibility.

If the firebird developers are interested, I'm definitely willing to communicate with them and see what we can do to make this happen.

Michael.

JIm,

A very interesting proposal. Having recently ported IBX from Delphi/InterBase to Free Pascal/Lazarus/Firebird, I know that both Firebird and Free Pascal are great products and could and should work well together for server side as well as client side functions. The question is what benefit do you get from further integration beyond just using Free Pascal to write a Firebird UDF?

Firstly, I would agree that if you are going to embed Pascal in Firebird Stored Procedures (or even just to write UDFs) then Free Pascal is the right choice over Delphi. It's not just that it is Open Source. Free Pascal targets far more processor architectures than Delphi and its support libraries are available for many more platforms.

Currently, the main reason I do not make much use of UDFs is the extra work involved in testing, configuring and deploying a shared library/DLL separate from the database itself. Unless there is a big reason for writing a server side UDF then it is often easier to find another way using the Store Procedure Language or just to make it a client side function. On the other hand, if you could write a Firebird Stored Procedure where the procedure body was actually Pascal and that Pascal code was initially syntax checked and then subject to a "just in time" compilation when first used, it would make it much easier to write and deploy server side functions - and to move existing (Pascal) client side functions to server side. It would thus seem to be a valid requirement.

If you go down this path, then my understanding is that you are proposing something like the following (with a lot of elaboration):

1. An extension to the CREATE/ALTER Procedure syntax to declare a procedure body as being Pascal rather than the Firebird stored procedure language.

2. A means to declared "used" Pascal Library Units in this declaration. It would also be useful to declare a reference to Firebird Transaction and Session Variables, as well as local Pascal Variables.

3. A well defined mapping between Firebird data types and Pascal data types for both input and output parameters. As Pascal is strongly typed this should not be problem, even for Blobs, which map naturally on to a TStream type.

4. Integration of Exception Handling (see other discussions).

5. Syntax checking, using the Free Pascal compiler when an embedded Pascal Stored Procedure is defined.

6. Just in time compilation of the embedded procedure on first use (after create/alter) into a shared library/DLL which is then effectively a dynamically generated UDF library. A JIT approach is important because the database can be moved between processor architectures/platforms and it is important to be able to recompile automatically for the new platform.

I assume that you are not proposing embedded SQL in the Pascal source as this would require a new set of preprocessors/support libraries and I can't see the benefit of that unless it was also for client side use.

Otherwise, I can see the above enabling applications that could be done with UDFs but require much less effort to develop and deploy with an embedded approach i.e. improving further the usability of Firebird.

Regards

Tony Whyman

<snip>

Thanks for the discussion on both lists; I've created some feature requests to keep track of things:

Firebird issue/feature request:
http://tracker.firebirdsql.org/browse/CORE-3571
FreePascal issue/feature request:
http://bugs.freepascal.org/view.php?id=19946

Thanks!

In our previous episode, Jim said:

IMHO this FPC tracker item is totally open ended, and it is not clear what exactly is requested. It is only an intention to do something with Firebird stored procedures and FPC, and not a concrete proposal or even a reference to something that has to be implemented.

Maybe it is more logical to wait till Firebird has a defined sandbox model for native languages ? This way it is just mantis ballast.

A couple of points:
1. Maybe the way the compiler is set up forces certain restrictions/architecture on a sandbox model. It might be wise to get in touch with the Firebird people first before they invent a round wheel while we need an octagonal one.

2. The Firebird people might want to know which platforms FPC can run. I don't know whether they intend to drop HP-UX, but that stood out as the platform FPC doesn't cover yet.
Coordination on this might be useful, too.

3. If FPC/embedded is to work on Win64, I suppose bug http://bugs.freepascal.org/view.php?id=17360
Firebird database exceptions don't generate EIBDatabaseError but a general exception on 64 bit windows
will need to be fixed, so that could be set as related.

4. I just tried to open up communication and provide a way for both sides to communicate and keep track of things.

However, please feel free to close/delete it if you think that's best. After all, I just floated the idea, while I know perfectly well it's unlikely my skills are not sufficient to do a lot of the work required.

Jim wrote

4. I just tried to open up communication and provide a way for both sides to communicate and keep track of things.

The problem is that this is generally not how things actually get implemented in open source software. Things get implemented by someone with an interest who writes code/patches, and if this code adds significant new functionality then this person is generally also expected to volunteer to maintain this new code for the foreseeable future. In the specific case of this feature request, a) adding HP-UX support to FPC will simply not happen unless someone with an interest in that platform submits patches and wants to maintain the platform support. b) defining the API to use for communicating with Firebird is by definition something that the Firebird people have to do (or someone else who then proposes it to the Firebird people). Once that is done, an RTL for this new API can be written by that same person or someone else with an interested in this project and then added to FPC (which in turn makes turn makes the underlying OS largely irrelevant, except for calling conventions and things like that) c) this project may depend on other bugs getting fixed in FPC, but the mere existence of this feature request is unlikely to influence how quickly they will actually get fixed

Filing feature requests saying "people of project A and project B, I would love if you got together and somehow integrated your software, adding any missing functionality in the process such as additional platform support" has very little chance of getting anything done. While communication is useful, the basic requirement is someone (or a group of people) that will do the work.

Jonas

It helps if people know the possibility for FPC as embedded language exists though ;)

In the specific case of this feature request, a) adding HP-UX

support to FPC will simply not happen unless someone with an interest in that platform submits patches and wants to maintain the platform support.

Didn't even imply that. Just mentioned that communicating with the Firebird people on what platforms FPC runs on might be handy, so they can have realistic expectations, too.

b) defining the API to use for communicating with Firebird

There must be easy and diffficult APIs for FPC to work with. Getting something that's awful to work with (C++ style) is more difficult than somethign easy (C style).

I'm no expert though.

c)

this project may depend on other bugs getting fixed in FPC, but the mere existence of this feature request is unlikely to influence how quickly they will actually get fixed

Could have been clearer there: having an overarching feature request could be handy to relate bugs to. In this way, you know when you're ready/what needs to be done, etc.

That's why I submitted it to both mailing lists first. See Michael Van Canneyt's and Tony Whyman's responses here and a lot of other responses on the Firebird dev list.
The platform support is not something I'm interested in. Personally, I would be thrilled if it just ran on Linux.

While communication is useful, the basic requirement is someone (or a group of people) that will do the work.

Totally agreed with you there.

In the end, if people don't want to do it, they won't. It's their time.

Once again, if you want to close this issue, no problem.