[Lazarus] Embedded/small database

classic Classic list List threaded Threaded
27 messages Options
12
Reply | Threaded
Open this post in threaded view
|

[Lazarus] Embedded/small database

Chavoux Luyt
Hi guys,

I asked advice a long time ago on a small/embedded database that could be used for a desktop application and distributed by CD as part of the application.I got a variety of answers including mySQL, sqlite and others. At the time I was very partial to somehow using postgreSQL, because I had had good experiences with it in the past and also because of postGIS (the application should include a spatial/GIS component). But it looks like setting up postgreSQL with a pre-existing database from the CD and all the actual data, taking into account whether the desktop already has postgreSQL installed and simply the size of postgreSQL (and running it as a service), makes it not such a good fit for my application. I want the app to be simple and quick to install and use for end-users.

I have decided to go with good old dbase (.dbf files), simply because this is what many GIS's use, at least for their non-spatial data. I have three questions:
1. What is your opinion on dbase for this use case? It will be a personal Decision Support System/Expert System that will use a combination of pre-compiled data (stored in the database during installation) and data entered by the user (added to the database during use). I would like things like referential integrity etc. to work as it should in a database, even though it is small. What would be you alternatives and why?
2. What tool would you recommend for actually creating the database, integrity etc.? I used Borland Database Engine all those years ago (it came with Delphi), but suppose there should be better (and open source) options now. MyDBF Studio? Or something else?
3. I see on the wiki that there are still issues with the TDbf component for Lazarus. What would be the best option to access the .DBF files? (And why?)

Thanks again,
Chavoux  

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

zeljko
On 02/15/2015 05:26 PM, Chavoux Luyt wrote:

> Hi guys,
>
> I asked advice a long time ago on a small/embedded database that could
> be used for a desktop application and distributed by CD as part of the
> application.I got a variety of answers including mySQL, sqlite and
> others. At the time I was very partial to somehow using postgreSQL,
> because I had had good experiences with it in the past and also because
> of postGIS (the application should include a spatial/GIS component). But
> it looks like setting up postgreSQL with a pre-existing database from
> the CD and all the actual data, taking into account whether the desktop
> already has postgreSQL installed and simply the size of postgreSQL (and
> running it as a service), makes it not such a good fit for my
> application. I want the app to be simple and quick to install and use
> for end-users.

Maybe you should look at Firebird.

z.


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

FreeMan
Yes, my vote firebird too. its support embeded and server. and win,
linux, osx support too. For embeded, no need to install just copied
files. where your application.

On 15.02.2015 18:58, zeljko wrote:
> Maybe you should look at Firebird.


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Graeme Geldenhuys-3
In reply to this post by zeljko
On 2015-02-15 16:58, zeljko wrote:
> Maybe you should look at Firebird.

+1

Regards,
  - Graeme -



--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Kostas Michalopoulos
Is this something that will be networked or running locally? If it is a local only program, i'd go with sqlite.

On Sun, Feb 15, 2015 at 7:16 PM, Graeme Geldenhuys <[hidden email]> wrote:
On 2015-02-15 16:58, zeljko wrote:
> Maybe you should look at Firebird.

+1

Regards,
  - Graeme -



--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Graeme Geldenhuys-3
On 2015-02-16 15:50, Kostas Michalopoulos wrote:
> Is this something that will be networked or running locally? If it is a
> local only program, i'd go with sqlite.


Firebird RDBMS allows you to run both Embedded (local DB access only) or
Client/Server (TCP/IP access). This means the application can easily
scale from a Embedded DB (desktop database app) to a full blown
Client/Server application.


Regards,
  - Graeme -

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Kostas Michalopoulos
Possibly, but if there is no need to be networked then this adds extra baggage (ie. bloat) for no reason at all. Sqlite is a tiny db designed specifically for local use, used in a ton of places (including Firefox and almost everything made by Apple these days) and has some of the highest quality source code. Also it is very easy to integrate and supported everywhere. Since it is a very common starting point, there are conversion tools to other DBs in case it proves limiting in the future.

But all that only if the program is local.

On Mon, Feb 16, 2015 at 5:11 PM, Graeme Geldenhuys <[hidden email]> wrote:
On 2015-02-16 15:50, Kostas Michalopoulos wrote:
> Is this something that will be networked or running locally? If it is a
> local only program, i'd go with sqlite.


Firebird RDBMS allows you to run both Embedded (local DB access only) or
Client/Server (TCP/IP access). This means the application can easily
scale from a Embedded DB (desktop database app) to a full blown
Client/Server application.


Regards,
  - Graeme -

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Marcos Douglas
On Tue, Feb 17, 2015 at 10:34 AM, Kostas Michalopoulos
<[hidden email]> wrote:
> Possibly, but if there is no need to be networked then this adds extra
> baggage (ie. bloat) for no reason at all. Sqlite is a tiny db designed
> specifically for local use, used in a ton of places (including Firefox and
> almost everything made by Apple these days) and has some of the highest
> quality source code. Also it is very easy to integrate and supported
> everywhere. Since it is a very common starting point, there are conversion
> tools to other DBs in case it proves limiting in the future.
>
> But all that only if the program is local.

+1

Marcos Douglas

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Michael Van Canneyt
In reply to this post by Kostas Michalopoulos


On Tue, 17 Feb 2015, Kostas Michalopoulos wrote:

> Possibly, but if there is no need to be networked then this adds extra baggage (ie. bloat) for no reason at all.

There is no baggage. Both systems work with a dll, which you must ship. Up to that point, it is the same.

The difference is that Firebird - in difference with sqlite - is a REAL rdbms, with fully functional type safety,
actual stored procedures, triggers and whatnot.

Sqlite is only good for storing preferences. Anyone using it for something else is playing with fire.

Michael.

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

leledumbo
Administrator
> Sqlite is only good for storing preferences. Anyone using it for something else is playing with fire.

Tested, it can't even manage 50,000 rows efficiently. Searching a string takes minutes, while it takes a fraction of second in real dbms.

It has much easier setup, though. Even easier than embedded firebird. Choose wisely.
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

zeljko
In reply to this post by Michael Van Canneyt
On 02/17/2015 03:21 PM, Michael Van Canneyt wrote:

> Sqlite is only good for storing preferences. Anyone using it for
> something else is playing with fire.

Since I've already burned my fingers ... + 1 :))))))

zeljko



--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Henry Vermaak
In reply to this post by leledumbo
On Tue, Feb 17, 2015 at 07:54:00AM -0700, leledumbo wrote:
> Tested, it can't even manage 50,000 rows efficiently. Searching a
> string takes minutes, while it takes a fraction of second in real
> dbms.

Interesting.  I've seen benchmarks where sqlite query times for a
full-text search on 120000+ rows is less than 20ms.  Did you use FTS4
for your search index?

Lucene was the only embedded database that could beat sqlite for
full-text search, IIRC.

Henry

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Graeme Geldenhuys-3
In reply to this post by leledumbo
On 2015-02-17 14:54, leledumbo wrote:
> It has much easier setup, though. Even easier than embedded firebird.

Firebird's setup is pretty easy. I've deployed Client/Server and
Embedded firebird under Windows, Linux and FreeBSD. For embedded I
simply shipped the DLL/SO files needed and it all worked like a charm.

Something else to comment on - I have no clue about this for Sqlite.
Firebird has loads and loads of 3rd party tools like Flamerobin etc to
view, query and analyse your database. In recent Firebird versions you
also have server side tracing support which is awesome for debugging SQL
calls.

Regards,
  - Graeme -

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Kostas Michalopoulos
Sqlite can be very fast, but it needs the proper setup for that (which may not be relevant in all cases).

Beyond that, Sqlite has a lot of high profile users (not to make a piss match, but i couldn't see any big users in Firebird's site and even those didn't seem to mention if it was embedded or client/server):

https://www.sqlite.org/famous.html

There are other less known uses of course, like the Fossil VCS and AFAIK Apple also uses it for the time capsule and FS versioning.

Honestly, i wouldn't put down Sqlite :-).

However you need to make sure that Sqlite is what you are looking for. After all in their site they say that  "SQLite is not designed to compete with Oracle. SQLite is designed to compete with fopen()." (or AssignFile in Lazarus' case :-P).

To put it differently, if i wanted to make an application to associate tags and colors with images (for fast theme lookup), i'd use Sqlite. If i wanted to make a bookstore chain application, i'd probably use PostgreSQL.

I wouldn't use Sqlite for configuration since this is what INIs are for :-P


On Tue, Feb 17, 2015 at 6:26 PM, Graeme Geldenhuys <[hidden email]> wrote:
On 2015-02-17 14:54, leledumbo wrote:
> It has much easier setup, though. Even easier than embedded firebird.

Firebird's setup is pretty easy. I've deployed Client/Server and
Embedded firebird under Windows, Linux and FreeBSD. For embedded I
simply shipped the DLL/SO files needed and it all worked like a charm.

Something else to comment on - I have no clue about this for Sqlite.
Firebird has loads and loads of 3rd party tools like Flamerobin etc to
view, query and analyse your database. In recent Firebird versions you
also have server side tracing support which is awesome for debugging SQL
calls.

Regards,
  - Graeme -

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Michael Van Canneyt


On Tue, 17 Feb 2015, Kostas Michalopoulos wrote:

> Sqlite can be very fast, but it needs the proper setup for that (which may not be
> relevant in all cases).
>
> Beyond that, Sqlite has a lot of high profile users (not to make a piss match, but i
> couldn't see any big users in Firebird's site and even those didn't seem to mention if
> it was embedded or client/server):
>
> https://www.sqlite.org/famous.html
>
> There are other less known uses of course, like the Fossil VCS and AFAIK Apple also
> uses it for the time capsule and FS versioning.
>
> Honestly, i wouldn't put down Sqlite :-).

I would.
Any database that allows you to store a string in a field that is declared as
integer (as sqlite does), is out. It goes against the basic rules of the RDBMs: ACID. Period.

If you ever encounter such a situation, the whole pascal db code simply goes 'poof'.
If the table definition says 'integer', it should be an integer.
if sqlite does not guarantee this (and it does not, it considers this a feature),
it is out.

I don't even understand that any pascal programmer (pascal being stronly typed)
can consider such a system. But there are many things I don't understand :)

Michael.

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

leledumbo
Administrator
In reply to this post by Graeme Geldenhuys-3
> Did you use FTS4 for your search index?

Nope, I'm talking about searching on non-indexed column.

> Firebird's setup is pretty easy

I didn't say it's difficult, but certainly sqlite is much easier. You use sqlite just like any other libraries, no setup required, just supply a single dll/so (or nothing if you statically linked it). With firebird embedded, you need to:
- supply a bunch of dll/so (firebird doesn't consist of just one)
- specify a config file
- set some environment variables before program start
easy, but certainly more than sqlite.

> I don't even understand that any pascal programmer (pascal being stronly typed)
can consider such a system. But there are many things I don't understand :)

As sqlite page says, it's designed to compete with fopen. sqlite does a good job for easy data storage and retrieval, where it doesn't need complicated setup like fopen, but it can be used (in terms of storing and retrieving data, from its users point of view) like an rdbms :)
rvk
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

rvk
leledumbo wrote:
> I didn't say it's difficult, but certainly sqlite is much easier. You use
> sqlite just like any other libraries, no setup required, just supply a
> single dll/so (or nothing if you statically linked it). With firebird
> embedded, you need to:
> - supply a bunch of dll/so (firebird doesn't consist of just one)
> - specify a config file
> - set some environment variables before program start
> easy, but certainly more than sqlite.

I've never done those last two steps with Firebird Embedded
(specify a config file and set an environment variable).
Why are they necessary?

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

patspiper
In reply to this post by Michael Van Canneyt
On 17/02/15 21:50, Michael Van Canneyt wrote:

>
>
> On Tue, 17 Feb 2015, Kostas Michalopoulos wrote:
>
>> Sqlite can be very fast, but it needs the proper setup for that
>> (which may not be
>> relevant in all cases).
>>
>> Beyond that, Sqlite has a lot of high profile users (not to make a
>> piss match, but i
>> couldn't see any big users in Firebird's site and even those didn't
>> seem to mention if
>> it was embedded or client/server):
>>
>> https://www.sqlite.org/famous.html
>>
>> There are other less known uses of course, like the Fossil VCS and
>> AFAIK Apple also
>> uses it for the time capsule and FS versioning.
>>
>> Honestly, i wouldn't put down Sqlite :-).
>
> I would. Any database that allows you to store a string in a field
> that is declared as integer (as sqlite does), is out. It goes against
> the basic rules of the RDBMs: ACID. Period.
>
> If you ever encounter such a situation, the whole pascal db code
> simply goes 'poof'.
> If the table definition says 'integer', it should be an integer. if
> sqlite does not guarantee this (and it does not, it considers this a
> feature), it is out.
>
> I don't even understand that any pascal programmer (pascal being
> stronly typed) can consider such a system. But there are many things I
> don't understand :)

Is there any other free WinCE database engine?

Stephano

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Bee
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Bee
I don't understand why FreePascal users need SQLite while we already have ZMSQL. Basically ZMSQL acts more or less like sqLite. But ZMSQL doesn't need external library. Check it out at: http://wiki.freepascal.org/ZMSQL


On Wed, Feb 18, 2015 at 4:28 PM, patspiper <[hidden email]> wrote:
On 17/02/15 21:50, Michael Van Canneyt wrote:


On Tue, 17 Feb 2015, Kostas Michalopoulos wrote:

Sqlite can be very fast, but it needs the proper setup for that (which may not be
relevant in all cases).

Beyond that, Sqlite has a lot of high profile users (not to make a piss match, but i
couldn't see any big users in Firebird's site and even those didn't seem to mention if
it was embedded or client/server):

https://www.sqlite.org/famous.html

There are other less known uses of course, like the Fossil VCS and AFAIK Apple also
uses it for the time capsule and FS versioning.

Honestly, i wouldn't put down Sqlite :-).

I would. Any database that allows you to store a string in a field that is declared as integer (as sqlite does), is out. It goes against the basic rules of the RDBMs: ACID. Period.

If you ever encounter such a situation, the whole pascal db code simply goes 'poof'.
If the table definition says 'integer', it should be an integer. if sqlite does not guarantee this (and it does not, it considers this a feature), it is out.

I don't even understand that any pascal programmer (pascal being stronly typed) can consider such a system. But there are many things I don't understand :)

Is there any other free WinCE database engine?

Stephano


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus



--
-Bee-


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Embedded/small database

Sven Barth

Am 18.02.2015 11:12 schrieb "Bee" <[hidden email]>:
>
> I don't understand why FreePascal users need SQLite while we already have ZMSQL. Basically ZMSQL acts more or less like sqLite. But ZMSQL doesn't need external library. Check it out at: http://wiki.freepascal.org/ZMSQL

Likely because it's not known as much ;)

Regards,
Sven


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
12