Firebird - or another embedded database
lazarus / 16-Databases

----------------------------------------------------------------------
Birger52 · 2019-10-03 13:08

Been looking at Firebird all day.
Latest Firebird is 3.0 - and there are no embedded version of it
available at Sourceforge, even tho the tutorials say there are.
But I can find a document saying Firebird 3 has an embedded version -
and an explanation, that doesn't explain anything, unless you already
understand how and what to use.
(http://www.ibphoenix.com/files/Embedded_fb3.pdf)
Versions are out of date - as well Lazarus as FDC. None of the
tutorials I can find, is using latest versions of anything - and not
much of it, is actually making sense....

Is Firebird no longer a viable solution?
are there any other embedded databases, that are easier to use (and
understand?)

----------------------------------------------------------------------
RayoGlauco · 2019-10-03 13:51

I am testing SQLite3: https://www.sqlite.org/index.html 

This is some code to open and close a connection.
You need a SQLite3 database and the libraries for your OS.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i uses  ... 
, DB   // tDataSource  , sqldb  , sqlite3conn  ,
sqlite3dyn; ...  procedure Test;var  SQL3conn: tSQLite3Connection; 
pathtolib: string;begin  // You need different files depending on your
OS  {$IFDEF WIN32}    pathtolib:= ProgramDirectory +
'sqlite\win32\sqlite3.dll';  {$ENDIF}  {$IFDEF WIN64}    pathtolib:=
ProgramDirectory + 'sqlite\win64\sqlite3.dll';  {$ENDIF}  {$IFDEF
LINUX}      {$IFDEF cpui386}        pathtolib:= ProgramDirectory +
'sqlite/linux32/libsqlite3.so';      {$ELSE}        pathtolib:=
ProgramDirectory + 'sqlite/linux64/libsqlite3.so';      {$ENDIF} 
{$ENDIF}  sqlite3dyn.SQLiteDefaultLibrary := pathtolib;   //
Connection  SQL3conn := tSQLite3Connection.Create(nil);  
SQL3conn.Close; // Ensure the connection is closed when we start 
SQL3conn.Password := MyPassword;  // blank if you want an unencrypted
database.  SQL3conn.DatabaseName := PathToMyBDfile;  
SQL3conn.Transaction := tSQLTransaction.Create(nil); 
SQL3conn.Transaction.DataBase := SQL3conn;  SQL3conn.Open;  //
Activate Foreign Keys control - (does this work?) 
SQL3conn.Transaction.Active := True;  SQL3conn.ExecuteDirect('PRAGMA
foreign_keys = ON;');  SQL3conn.Transaction.Commit;    // End of
connection  SQL3conn.Transaction.Active := False;  SQL3conn.Close; 
SQL3conn.Destroy;end;

----------------------------------------------------------------------
devEric69 · 2019-10-03 14:24

QuoteIs Firebird no longer a viable solution?
On which OS: Windows or Linux (personally, I can do what I want, ie
master-details, TDBCtrlGrid, monitoring, etc, with Firebird 3 + IBX +
FlameRobin \ IBexpert personal version, on Ubuntu 18.04)?

ps1: do "Embedded" under Linux, when we come from Windows - like me -
requires a learning curve... from Linux in fact  : it's more
complicated, than just transposing what's done under Windows towards
Linux.
ps2: if your database does not have to support, neither a large volume
of data, nor a strict typing of data fields (very precise and
potentially extensive typing; in short, a large data dictionary), then
SQLite is a better choice.
ps3: a lot of information and support for Firebird, is done on
https://groups.yahoo.com (group: firebird-support; but, there are many
questions from database administrators, few questions asked by
developers).

----------------------------------------------------------------------
Thaddy · 2019-10-03 14:41

Quote from: Birger52 on October 03, 2019, 03:08:10 pmBeen looking at
Firebird all day.
Latest Firebird is 3.0 - and there are no embedded version of it
available at Sourceforge, even tho the tutorials say there are.
But I can find a document saying Firebird 3 has an embedded version -
and an explanation, that doesn't explain anything, unless you already
understand how and what to use.
(http://www.ibphoenix.com/files/Embedded_fb3.pdf)
Versions are out of date - as well Lazarus as FDC. None of the
tutorials I can find, is using latest versions of anything - and not
much of it, is actually making sense....

Is Firebird no longer a viable solution?
are there any other embedded databases, that are easier to use (and
understand?)
Firebird is still viable, but it is a niche solution. (You won't find
many applications in the wild)
Most choose SQLite as an embedded database solution. That's pretty
much the industry standard.
This an observation, not an advice: decide for yourself.

----------------------------------------------------------------------
tonyw · 2019-10-03 15:42

Quote from: Thaddy on October 03, 2019, 04:41:23 pmFirebird is still
viable, but it is a niche solution. (You won't find many applications
in the wild)
Most choose SQLite as an embedded database solution. That's pretty
much the industry standard.
This an observation, not an advice: decide for yourself.
I am not sure that this is a good line of argument given that some may
claim the same about Pascal when compared with C/C++!

LIke Pascal, Firebird has a strong support community with many
dedicated users. It's USP is that it is a full featured SQL database
supporting both client/server and embedded mode, while requiring very
little in respect of database administration. It is therefore very
suitable for an application where the database is distributed with the
app and where the end user has little experience in database
administration.

The reason why you may not have seen a separate embedded version is
that with Firebird 3 the same software is used for both cases.
Firebird has a plugin architecture where the database engine "plugin"
can either be loaded and run as part of a user application, or loaded
and run from a FIrebird Server application. The FIrebird Client
DLL/Shared object can work in either mode. If the database connect
string does not include a host identifier (FQDN or IP Address) then
embedded mode is assumed and the DLL loads the Firebird engine plugin.
The Firebird engine will then open the database file directly and use
the current application's file access rights when so doing.

If the connect string includes a host identifier then the "remote"
plugin is loaded and a TCP connection is attempted with the remote
host. If the Server application is running on that host then it
receives the request and will open the database file itself using the
file access rights assigned to the server process.

I am also surprised by the comment that embedded mode is more complex
on Linux instead of WIndows. This is probably due to Linux file access
rights following a multi-user model while Windows being a bit more
"open" in this area. The one problem that I have tripped over is when
both a FIrebird Server and embedded mode are used on the same system.
There can then be a conflict over which one is first to create the
temporary directory. If they are both trying to use the same temporary
path then the "loser" can't create its temporary files. The solution
is to always set the FIREBIRD_TMP environment variable before a
database file is opened in embedded mode so that it points to a
different temporary directory for each user.

Finally, a common newbie error is to use prefix the Firebird connect
string with "localhost:" and assume that this means use embedded mode.
It does not. This syntax results in the Firebird client trying to
establish a local TCP connection with a Firebird Server running in the
same system. The rule is that if you want a private database
accessible by a single user then use embedded mode. If you want a
common database file accessible my many users on the same system then
use a local server and prefix the connect string with "localhost:"

P.S. IBX includes non-visual component to support embedded mode
including support for database creation, backup and field upgrade.

----------------------------------------------------------------------
Thaddy · 2019-10-03 16:23

Quote from: tonyw on October 03, 2019, 05:42:45 pm[P.S. IBX includes
non-visual component to support embedded mode including support for
database creation, backup and field upgrade.
Visual components have nothing to do with embedded database
programming.
Do not ask be to be opinionated. I was very careful not to do....

----------------------------------------------------------------------
guest48180 · 2019-10-03 16:26

Quote from: Birger52 on October 03, 2019, 03:08:10 pmare there any
other embedded databases, that are easier to use (and understand?)

You can't go wrong with SQLite3. For ease of use, it's simple.  You
only need one sqlite3 file, sqlite3.dll for Windows and I forget the
extension used on Linux.  SQLite3's rock solid, though.  I've been
using it for a few years, used it with C++ and with Free Pascal.  No
issues whatsoever.  No special setup.  Just include the one sqlite3
file in your project directory, add the database components to your
form, get it all connected and you're good to go.  SQLite3 may be
small, and it is, but it's powerful.  Give it a try.

----------------------------------------------------------------------
Thaddy · 2019-10-03 16:33

That is good advice in my opinion.

----------------------------------------------------------------------
guest48180 · 2019-10-03 17:23

Quote from: Thaddy on October 03, 2019, 06:33:11 pmThat is good advice
in my opinion.

Thanks, Thaddy.  I mean, SQLite3 is so small and reliable that it's
the most-used database in all the Android and iPhone apps, if not the
only one used.  It's also used in many web browsers.  And if database
size is a concern for you, SQLite3 doesn't max out until 140Tb.

Some well-known SQLite3 users.

----------------------------------------------------------------------
devEric69 · 2019-10-03 18:58

QuoteSQLite3 is so small and reliable that it's the most-used database
in all the Android and iPhone apps, if not the only one used.  It's
also used in many web browsers.
The two databases do not "live" in the same world. There are Firebird
databases of several Gb, without any problem. Indeed, SQLite databases
are massively used on smartphones and tablets, and by browsers on
smartphones.
==> Cf. https://db-engines.com/en/ranking ( adds Interbase and
Firebird ;-) ).

Deja vu:
- SQLite has few field types (It may be enough for your application).
- Firebird offers many types of fields, natively (you may need them).

On the components side:
- native components of Lazarus offers everything you need to manage
data, with SQLite and Firebird.
- but, IBX components for Firebird also offers (I don't know if it's
useless or not, for your application):
   · IBEvents which allow to deport on the database side, a prorata of
synchronization, if the threads\workers are already numerous on the
client side.
   · TIBDataSet is a beautiful piece of code, that is an ideal OMT
modeling object.
   · it is possible to program the CRUD in cache memory.
   · simple support management (update of the database schema), etc.

By caricaturing:
- if you have to make the CRUD of a rather large, complicated fast
data flow... ==> Firebird.
- if you have to do the CRUD of a rather lightweight, similar data
flow...  ==> SQLite.

You can read these documentations, that explain how to program with
Firebird and Lazarus https://www.mwasoftware.co.uk/documentation (I
don't "owe" anything to tonyw; but I recognize someone who knows what
he's talking about).


And one last point: whether you choose SQLite or Firebird, under
Linux, dropping the dll\so *SQLite_or_Firebird*.so, your executable,
and your database in the same directory (in the Windows way)... will
not be enough (loading libraries on Linux is more complicated, and
therefore needs to be understood). You can look at these posts, to get
an idea of the problem and of the solution:
- https://forum.lazarus.freepascal.org/index.php/topic,44924.msg318799
.html#msg318799
- https://forum.lazarus.freepascal.org/index.php/topic,45786.msg325496
.html#msg325496

----------------------------------------------------------------------
guest48180 · 2019-10-04 00:15

Quote from: devEric69 on October 03, 2019, 08:58:20 pmThe two
databases do not "live" in the same world.
I cldn't agree more.  But I was talking abt SQLite3.  That's because
the Op specifically asked abt "other" embedded databases that are easy
to use and understand.
Quote from: Birger52 on October 03, 2019, 03:08:10 pmare there any
other embedded databases, that are easier to use (and understand?)
Not sure why you felt compelled to quote me and then defend tonyw and
Firebird in lieu of my post.  I was simply trying to help someone who
asked for it.

----------------------------------------------------------------------
mangakissa · 2019-10-04 06:26

Quote from: Birger52 on October 03, 2019, 03:08:10 pmBeen looking at
Firebird all day.
Latest Firebird is 3.0 - and there are no embedded version of it
available at Sourceforge, even tho the tutorials say there are.
But I can find a document saying Firebird 3 has an embedded version -
and an explanation, that doesn't explain anything, unless you already
understand how and what to use.
It looks like it, but the installation file has not only a server
site, but also a client site. It's still working with fbclient.dll,
but if you read in the documentation you need another dll to make the
database embedded.

This link https://www.firebirdsql.org/en/news/embedded-server-in-
firebird-3/ has a pdf to work with embedded.

----------------------------------------------------------------------
devEric69 · 2019-10-04 07:31

For information, IBExpert is also a valuable source of information
about Firebird ( https://www.firebirdsql.org/ ): this site has a
forum, and publish articles, videos and documentations, that are at
the border between the database administrator's knowledge and the
knowledge needed by the developer:
https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.Doc.

----------------------------------------------------------------------
rvk · 2019-10-04 08:25

Quote from: mangakissa on October 04, 2019, 08:26:19 amIt looks like
it, but the installation file has not only a server site, but also a
client site. It's still working with fbclient.dll, but if you read in
the documentation you need another dll to make the database
embedded.There is no need for extra .dll's other than provided in the
.zip.

Just unzip the zip to your .exe directory and you have everything in
the correct place to make the embedded Firebird working.

From that pdf:
QuoteEverything you need for deploying Firebird 3 embedded with your
application is present in the .zip (Windows) or .tar.gz (Linux) kit
that you download from the Firebird website.
Only if you are creating a new separate minimum installation you need
several dll's besides the fbclient. That's what is explained in that
pdf ("Tuning the Directory Structure").

----------------------------------------------------------------------
ttomas · 2019-10-04 08:27

If you use db only as data store of tables with relation and size<1GB
use SQLite.
If you need additional client to connect to db (now or in the future)
use Firebird.
If you need to move some business logic in database (recommended for
speed) in triggers and stored procedures use Firebird.
Embedded Firebird works ok on Linux and Windows (Mac not tested)

----------------------------------------------------------------------
MarkMLl · 2019-10-04 09:09

Quote from: ttomas on October 04, 2019, 10:27:50 amIf you use db only
as data store of tables with relation and size<1GB use SQLite.
If you need additional client to connect to db (now or in the future)
use Firebird.
If you need to move some business logic in database (recommended for
speed) in triggers and stored procedures use Firebird.
Embedded Firebird works ok on Linux and Windows (Mac not tested)

Or PostgreSQL. The important point here is that while SQLite is a good
embedded (by definition, single-user) database, PostgreSQL and
Firebird give you far better facilities for accessing the data from
other programs and are, broadly speaking, compatible when it comes to
triggers and event notification.

Obviously there are differences in detail. I spent quite a long time
investigating a program that could handle both, and was dismayed that
while I could set up databases/schemata/tables/users through
Postgres's standard SQL interface I had to navigate Firebird's poor
documentation to find which program to use for the various operations.

MarkMLl

----------------------------------------------------------------------
tonyw · 2019-10-04 09:50

Quote from: Thaddy on October 03, 2019, 06:23:42 pmQuote from: tonyw
on October 03, 2019, 05:42:45 pm[P.S. IBX includes non-visual
component to support embedded mode including support for database
creation, backup and field upgrade.
Visual components have nothing to do with embedded database
programming.
Do not ask be to be opinionated. I was very careful not to do....
I totally agree. That's why it's a non-visual component that also
works without a GUI. I mentioned it because I found it useful to
package support for embedded mode in a single component that:

- checks for embedded mode support (Firebird 2.5)
- sets up to environment variables to avoid any conflicts
- By default places private databases in the appropriate Windows
Folder or a directory in a Linux Home Directory.
- Provides the support you need to package a database for non-database
aware users
     i.e. initial database creation from an SQL Script or an archive
          taking backups and restoring them
          upgrading the database schema if necessary when a new
software release is made.

With Client/Server these tasks are part of database administration and
your user does have to have some minimal knowledge in order to ensure
that they are carried out, but with personal/private databases, there
is a need to automate them as you really shouldn't be expecting your
user to be able to perform them unaided - at least if you want your
app to be successful.

----------------------------------------------------------------------
Birger52 · 2019-10-04 20:03

Thank you for all the input.
Quite a lot of reading to be done.
Looks like it'll be SQLite3.

----------------------------------------------------------------------
devEric69 · 2019-10-29 18:47

● For information (I post here since the title explicitly refers to "
Firebird "), there is a manager for Firebird that is a GUI tool, free,
being developed by RedSoft ( https://reddatabase.ru ):
https://www.firebirdnews.org/redexpert-1-0-6-released/

It is a very advanced program (it is written in Java 8: so, it runs on
Windows and Linux; the database's directory ownership & group & others
in Linux must be the server, ie "Firebird" to create a new one; the
database's ownership & group & others in Linux must be the server, ie
"Firebird" to open it): this tool can scroll, do local sorting,
filtering by clicking on a column, exporting data, scripts,
bookmarking of favorite queries, draw \ map the ERD like phpMyAdmin,
...

Flamerobin does the work, but there is no release of this project
since 2016 (the author is now interested to do something else, or has
an incapacity).

So, "Red expert" is a good tool to take over from FlameRobin (which
has been well used; maybe it will become relevant again later...?), to
manage a Firebird database with a "user friendly" GUI.

● And to be complete, there is also DBeave (https://dbeaver.io/): a
very complete database graphic management program too. It can also
design SQLite databases).

----------------------------------------------------------------------
Soner · 2019-10-30 17:58

You can also use Firebird 3 as embedded database. I did it 3 years
ago.
Here is document:
https://firebirdsql.org/en/news/embedded-server-in-firebird-3/