Using Firebird as embedded database for Standalone OpenPetra

pokorra

Sqlite is working fine for us at the moment for the demo installation Windows.
For production use, for example on one Laptop in a very small office, we have these additional requirements, that are important:

a) database has to survive a Windows Hibernation. We have had reports with PostgreSQL service broken / database corrupted after a windows hibernation. Any details on that?
b) upgrade the database: ALTER TABLE should be able to add / delete columns. See for sqlite: firebirdsql.org/refdocs/lang ... table.html
c) single file for easy deployment of a base database. Firebird does not seem to provide cross-platform file formats, but that might be ok for us.

See more comparison of Firebird and SQLite here: sqlite.org/cvstrac/wiki?p=SqliteVersusFirebird

Anyone interested on investigating this further?

matthiasschwarz

will have a look at some alternatives. As far as I understood it really should be a small DBMS rather than going for mySQL or Cubrid.

pokorra

Yes, I forgot this requirement:
d) the database system should be easy to install, the best would be that it is embedded in the OpenPetra installer and application.

Interesting to read about Cubrid, never heard about that RDbms before...

matthiasschwarz

Is there any recent proof for postgresql DBs being corrupted through windows hibernation that could be followed up?
Maybe it just has been the case in any previous version or could be avoided by changing some settings.

pokorra

Ask TimH about the PostgreSQL problems with hibernation. He told me, but I cannot remember the details.
You might want to retest with recent versions of PostgreSQL.

It does not seem to be discussed as a problem on this page: wiki.postgresql.org/wiki/Running ... ve_Windows

matthiasschwarz

Installation and setup of Firebird
I am a bit disappointed with the installation process of firebird.
The installation itself is fast, but there is no setup that works out of the box.
They offer a command line tool that is very special in the way it requires commands to be entered.
The documentation of how to set up a new database is very poor and just mentions the obvious, but does not explain in detail what commands

lead to the desired results and even the basic commands did not work for me.
There are third party admin GUIs offered like FlameRobin which also did not help in resolving the issues.
It basically gives the same errors although the GUI is supposed to assist you in the process.

In generell there is quite a bit of admin stuff to be done and to be learned, before you can actually use it.
Since I have not set up databases beforehand, but just configured them after they have been set up, my guess is that it would probably
take me a week to setup up a new firebird DB to run and be configured as desired with lots of googling involved.

I also realized that re-installing firebird with different settings is a bit a a mess.
Firebird company highly recommends to de-install the old installation first.
Having done so I figured out that uninstalling does not properly clean up the previously used installation directories (even though I did not really change anything).
The uninstalling process even mentions that some (which=?) files might be left and need manual clean up.
This then causes problems in re-installing firebird. It for example gives all kinds of permission errors and is not able to overwrite its own files.

If you are used to the easy installations and easy setup of DB systems like mySQL, then firebird still has a long way to go to make it user friendly.
btw: The most user friendly interface I have come across is the one that comes with Cubrid. They have a great admin tool that does it all and is very simple to use.
I had some other issues with the Cubrid database itself loosing its connection to the 'brokers' which are their 'listener' services to the DB, though.

Summary on the cons of firebird

no easy install, installation options and effects not explained
no proper upgrade mechanism
no Admin GUI by default, several command line tools needed and command line tools difficult to use
poor documentation
no easy creation of new DB

I did not get far enough to list the pros.

matthiasschwarz

pokorra wrote
Ask TimH about the PostgreSQL problems with hibernation. He told me, but I cannot remember the details.
You might want to retest with recent versions of PostgreSQL.

It does not seem to be discussed as a problem on this page: wiki.postgresql.org/wiki/Running ... ve_Windows

TimH had to deal with this problem about >1 year ago. The postgresql database was corrupted to a state where it could not be restored other than by replacing it from a backup.
We need to test it again with different energy saving options, but I guess it will be difficult to reproduce a similar crash.
It might also depend on the configuration of the laptop, additional applications running etc.

matthiasschwarz

After having downloaded and installed several databases, I realized that there is hardly any lightweight DB that is freely available apart from SQLite and Firebird.
From my point of view we should stay with SQLite as this runs out of the box, is very small and reliable and somewhat better documented.
It's a petty that id does not allow the full alter table settings, but I also doubt that there will be a frequent need of dropping columns.

Adding a column does work, though. If there is a major change in the table structure, one would need to migrate the old data anyway.
If so, then creating a table with the new structure and copying the data from the old table should not be a big issue.

Firebird to me is somewhere in the middle between SQLite and MySQL on what they claim to be able to do,
but from what I have seen it is by far not as userfriendly as the other two.
It would take a while to figure out the different server options as for Firebird a Classic, SuperClassic and SuperServer can be installed.
The differences are listed, but really don't mean that much or are just confusing:
http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-classic-or-super.html#qsg25-classic-or-super-embedded

"Processes
Classic uses a separate process for each connection; SuperClassic and Superserver use a single process. Thus, if a Classic server process crashes, the other connections remain unaffected. With SuperClassic and Superserver, a crash take down all the connections."

"Classic and SuperClassic offer an “embedded” local connection mode on Linux..." on Windows it's called SuperServer(?!) and "...even less secure"

"...On 32-bits systems, SuperClassic will be the first to run out of memory under high load..." -> oh!?

It also requires 2 databases, one for the security and user management and the other for the actual data.
This also means different tools have to be used as the administration cannot be done from within their ISQL command line utility any more.

=> I will stop investing time on firebird (although it annoys me that I have not seen it working on Windows 7,
for it just takes too much time to investigate several issues that are as simple as creating a new database file).
SQLite seems to me the better approach.

pokorra

Thanks, Matthias, for looking into this.
It was just that a friend at ICCM recommended Firebird, but he is using it not in the embedded mode.
We should then focus on supporting SQLite for database upgrades, and also add the support for constraints etc which I initially neglected to implement.
For bigger database upgrades, we can always continue to do a fulldump/fullload approach as with Petra 2.x