Lazarus

Programming => Databases => Topic started by: lawman on January 20, 2020, 07:49:44 pm

Title: which database?
Post by: lawman on January 20, 2020, 07:49:44 pm
hi,

I have to create a program that can schedule tasks and hearing dates.

Last programmed with databases over a decade ago.   Back in the day used Visual Basic, C and also pascal amongst a few others so very rusty.

Which database would you suggest that fits the bill below?

1.  Start off single user on local machine.

2.  Once app grows, then switch to working over internet (including merging single user databases into one big central one).

3.  Encrypted preferred unless this will affect speed a lot.

4.  Have 2-3 database files open.  eg. user, common.    Would like to be able to query and build relations between both files and tables within.

5.  Speed over internet.

6.  Small size for distribution.

7.  Free

Was going to use sqlite, but need to future proof (2) so can't use sqlite as it doesn't have proper multi-user capabilities.  (Only 1 user can write at a time).

To fulfil 1,2,6 the only conclusion I came up with was Firebird.

However, 5 seems to be problem with slow speed over internet stated here (http://www.firebirdfaq.org/faq53/).

Also, tried creating relationships in tables and had problem creating many to many gui relationship.   Not sure what the problem is here?

Storing procedures in the database seems to be a pain from my reading?

Not sure if my concerns about firebird can be resolved by some guidance from here?

Or if another solution available that is small, free, can do both embedded single user and central multi-user without much change.

Thanks
Title: Re: which database?
Post by: HeavyUser on January 20, 2020, 09:01:04 pm
From an embedded point of view you can't do much better than firebird its small and full fledged sql engine.
I think that mySQL also has an embedded engine under very strict "no commercial" use (with out pay) license but not sure if that changed the last couple of years.

Title: Re: which database?
Post by: JD on January 20, 2020, 09:58:48 pm
To fulfil 1,2,6 the only conclusion I came up with was Firebird.

However, 5 seems to be problem with slow speed over internet stated here (http://www.firebirdfaq.org/faq53/).

Also, tried creating relationships in tables and had problem creating many to many gui relationship.   Not sure what the problem is here?

Storing procedures in the database seems to be a pain from my reading?

Not sure if my concerns about firebird can be resolved by some guidance from here?

Or if another solution available that is small, free, can do both embedded single user and central multi-user without much change.

Thanks

I went through the same thought process some time ago and given all you stated; Firebird is your best bet. I used Firebird too in a similar use case for several years. Firebird is slow when sending data over the Internet BUT I changed the architecture of the application by developing an application server that sits in front of the database and receives/send data to clients over the Internet in JSON format. So only the application server interacts with the Firebird database on the same machine (localhost) and the speed gains were very good. By the way, this also works in a single user use case.

However as the application grew, I needed more database functionalities (e.g power SQL commands) that Firebird did not have so I moved on to PostgreSQL and I've had no regrets because functions, stored procedures and views are much faster in PostgreSQL and I can handle larger volumes of data very, very quickly and easily.

In conclusion, Firebird will work very well as long as you change the architecture of the application so that only the backend server is doing the interaction with Firebird. At least, that is what I found out.

Cheers,

JD
Title: Re: which database?
Post by: lawman on January 20, 2020, 10:06:09 pm
but postgresql doesn't have single user embedded and isn't small?  :(
Title: Re: which database?
Post by: JD on January 20, 2020, 10:31:35 pm
but postgresql doesn't have single user embedded and isn't small?  :(

I never said that. I was refering to an application scaling up and the limitations of Firebird. Read my conclusion again. Firebird is great for your use case BUT if you continue to grow, you may have to abandon the single user embedded and small constraint.

Actually, there is another way to do it using mORMot and SQLite but that requires more research and lots of reading; however it is well worth it.

JD 
Title: Re: which database?
Post by: MarkMLl on January 20, 2020, 10:39:03 pm
but postgresql doesn't have single user embedded and isn't small?  :(

It's still the best overall fit. If you were /strictly/ single-user, i.e. just looking for a repository for a local program, I'd suggest Sqlite, but your requirement to be able to grow and /in/ /particular/ your statement that you require access over the Internet (i.e. not just over a LAN) fairly strongly suggests Postgres.

And I'm afraid that my experience with Firbird wasn't entirely positive: too "bitty" with manual addenda and so on.

MarkMLl

Title: Re: which database?
Post by: JD on January 20, 2020, 11:07:13 pm
but postgresql doesn't have single user embedded and isn't small?  :(

And I'm afraid that my experience with Firbird wasn't entirely positive: too "bitty" with manual addenda and so on.

MarkMLl

One of the reasons, I had to drop Firebird was I could not modify a stored procedure in real-time. I had to first make sure that no users were online, then shut down the Firebird server and after updating the stored procedure, I could then restart the Firebird server. This got on my nerves so many times that after 6 years, I just gave up and moved to PostgreSQL and I can now do this seamlessly.  :D

JD
Title: Re: which database?
Post by: rvk on January 20, 2020, 11:14:47 pm
One of the reasons, I had to drop Firebird was I could not modify a stored procedure in real-time.
Which is possible in the meantime.

Performance over low latency networks (i.e. Internet) has also increased a lot (although using an extra layer using json could have its advantages).
Title: Re: which database?
Post by: JD on January 20, 2020, 11:38:09 pm
One of the reasons, I had to drop Firebird was I could not modify a stored procedure in real-time.
Which is possible in the meantime.

Performance over low latency networks (i.e. Internet) has also increased a lot (although using an extra layer using json could have its advantages).

That is very good to know. The last version I used was Firebird 2.5. Thanks a lot.

JD
Title: Re: which database?
Post by: mangakissa on January 21, 2020, 08:24:54 am
Quote
2.  Once app grows, then switch to working over internet (including merging single user databases into one big central one).
Restserver over firewall. Don't use database over internet. It's slow and not secure.

As using standalone / single user use firebird as well
Title: Re: which database?
Post by: ttomas on January 21, 2020, 10:36:28 am
Firebird as any software is evolving and many things is right for old versions of firebird.
Never expose firebird or any database to Internet.
I use firebird on internet only with VPN connection.
Slow connection is not true with Firebird3 and WireCompression
I have select of ~50000 records in my app over VPN
This fetch cost 20 sec.
I try to replace with mORMot SynDB, reasult is 2.7 sec.
In same time I enable WireCompression and direct connection cost is 2.5 sec.
Now I use same app only with WireCompression enabled.

Modify of stored procedure in real time is not a problem in SuperServer, just don't use no_wait transaction.

Personally I don't like ZeosDbo with Firebird. Zeos only support one active transaction per connection, like PostgreSQL and MySQL. Firebird support have future to have many active transaction per connection.
Title: Re: which database?
Post by: rvk on January 21, 2020, 10:55:49 am
Personally I don't like ZeosDbo with Firebird. Zeos only support one active transaction per connection, like PostgreSQL and MySQL. Firebird support have future to have many active transaction per connection.
WHAT? I personally don't use Zeos because at the moment I use Delphi i.c.w. IBX. But, if in a future, I would switch to Lazarus, I thought about using Zeos. But if a TZQuery doesn't even have a separate TTransaction option then it seems more and more likely I will just use IBX in Lazarus (which is actively developed and maintained too).

B.T.W. I see that this is changed since version 6. So I suppose before that version it was possible to use multiple transactions per connection.
Quote
Multiple connection components should do it. There is no more transaction component, since Zeos 6.
Title: Re: which database?
Post by: marcov on January 21, 2020, 11:34:00 am
I currently also our mostly Windows db client that uses zeos+postgresql that way, but enabling VPN (openvpn) becomes annoying, and requires to install and preconfigure

Does somebody know a better, more seamless solution?

I mean this for the current configuration (iow two tier with pgsql on linux, no 3 tier or orm rearchitecting please)
Title: Re: which database?
Post by: rvk on January 21, 2020, 11:44:53 am
Does somebody know a better, more seamless solution?
Port Forwarding (tunnel) over SSH (if you run a SSH-server of course).

I've used Zebedee in the past. But for that you also need to punch a hole in the router/firewall and since I have lots of RPI's everywhere, I now use putty/SSH.

I never tried to access a Firebird server over SSH (only use it for internal HTTP and some other ports) but it should be possible for 3050 too.

Edit: Just tried it with Firebird and it works fine (although slow because I still used Firebird 2.5 without optimization as discussed above).
Title: Re: which database?
Post by: chrnobel on January 21, 2020, 11:46:35 am
Zeos only support one active transaction per connection, like PostgreSQL and MySQL. Firebird support have future to have many active transaction per connection.
Can you elaborate what you mean by that?

I do only use Zeos, and are having no problem with only having one connection, and several query's to that.

I would recommend SQLite, especially if you use Zeoslib, as it is very easy to switch to eg. MariaDB later, if there should be a demand, with only a few lines of change - and SQLite is actually really fast, even with several thousands of records.

I do only make webapplications, so the only client from my programs (and thus the database) point of view is the webserver - in that way it is no problem having multiple end users, even using SQLite - and SQLite is very fast.

And by doing it this way, the database as such is not exposed to the internet (and thereby only reply, not data, is transferred over the net, giving a huge increase in speed), and by proper setup of the webserver it is very secure - just remember to make all sql sentences with parameters, and do input check to avoid sql injection.

The only problem I sometimes run into is rare locking of the SQLite base, if two updates are made within the exact same time - I have described that in another post, but I am working on a solution - typically it takes less than 5ms to make an update, so just a few retries of the commit in case of locking should do it.
Title: Re: which database?
Post by: marcov on January 21, 2020, 12:23:06 pm
Does somebody know a better, more seamless solution?
Port Forwarding (tunnel) over SSH (if you run a SSH-server of course).

I do, and I have putty already. Some finagling about the destination IP, the first tutorial didn't work and confused. The second one worked :-)
Title: Re: which database?
Post by: ttomas on January 21, 2020, 06:53:53 pm
Zeos only support one active transaction per connection, like PostgreSQL and MySQL. Firebird support have future to have many active transaction per connection.
Can you elaborate what you mean by that?
In PostgreSQL and MySQL you have this pseudo code:
Code: Pascal  [Select][+][-]
  1. connect
  2. start transaction 1
  3. do some sql's in tr1
  4. commit tr1
  5. start transaction 2
  6. do some sql's in tr2
  7. commit tr2
  8. ...
  9. disconnect
  10.  
In FirebirdSQL you can open multiple transactions in same connection
Code: Pascal  [Select][+][-]
  1. connect
  2.   start transaction 1
  3.   do some sql's in tr1
  4.    start transaction 2
  5.    do some sql's in tr2
  6.     commit tr2
  7.     start transaction 3
  8.     do some sql's in tr3
  9.      start transaction 4
  10.      do some sql's in tr4
  11.       do some sql's in tr3
  12.      commit tr4
  13.    do some sql's in tr3
  14.     commit tr3
  15.   do some sql's in tr1
  16.  commit tr1
  17. disconnect
  18.  
For this type of nested transactions you will need another connections in PostgreSQL and MySQL. In Firebird you need only one connection. Any additional connection to server need resources on server side (memory, TCP connections, etc.).
In ZeosDbo you can't open multiple transactions to Firebird :-(. 
Title: Re: which database?
Post by: rvk on January 21, 2020, 10:57:53 pm
For this type of nested transactions you will need another connections in PostgreSQL and MySQL.
Transactions in Firebird can't be nested, can they?
(You can't rollback transaction 1 and expect commited transaction 4 to rollback too)

But they are parallel to each other, which sometimes is necessary. I wouldn't want to establish a seperate connection for that.

For real nesting I use savepoints (manually).
https://www.wisdomjobs.com/e-university/firebird-tutorial-210/nested-transactions-7833.html

FireDac (which I don't use) even emulates nested transaction with SavePoints.
Note 3: EnableNested
Quote
If StartTransaction is called from within an active transaction, FireDac will emulate a nested transaction by creating a savepoint. Unless you are very confident in the effect of enabling nested transactions, set EnableNested to False. With this setting, calling StartTransaction inside the transaction will raise an exception.
https://firebirdsql.org/file/documentation/reference_manuals/fbdevgd-en/html/fbdg30-firedac-transactions.html
Title: Re: which database?
Post by: ttomas on January 22, 2020, 12:28:40 am
Transactions in Firebird can't be nested, can they?
Yes you are right, my typo. Transaction is independent/parallel not nested. You can program nested transaction logic in app, not the same as native nested transaction on db site.
Title: Re: which database?
Post by: lawman on January 22, 2020, 09:29:00 am
I've returned to programming after 15 years playing catching up on everything.

Even though I could code about 7 languages back then I've forgotten most and everything's updated.

Add this isn't my day job so makes time to learn slim as hobby right now.

I can see 3 options at present.

1.  Stick with Firebird if faster over internet now to future proof.   No way for me to test speed as only doing single user initially.  Also concerned about security over net and if that prohibits it later.   For security if there is a server client structure then only results are being passed over net as in all cases?

2.  mORMot and SQLite .   Seems confusing and a lot to learn.  Not clear of benefits.

3.  Some other database that can do both serverless and server based??

Not sure how sqlite behind web server helps.

Simpler the better. 
 
Guidance appreciated for this once experienced newbie.

Thanks
Title: Re: which database?
Post by: chrnobel on January 22, 2020, 10:41:36 am
SQLite .   Seems confusing and a lot to learn.  Not clear of benefits.
SQLite is very easy to learn, in the sense that if one understand SQL it is straight forward - but that is the same for all kinds of SQL.

Benefit is portability, simplicity, easy installation, very strong user base, fast.

Quote
Not sure how sqlite behind web server helps.
As the webserver deals with all the UI on one side (outside towards the Internet) and the database internally, meaning there is no transport of raw data over the Internet.

But if you really want to transfer data over the Internet (which imo is a very bad idea), then I see no alternatives to a real client-server database like Maria/MySQL or Postgress - but remember this requires open ports.
Title: Re: which database?
Post by: marcov on January 22, 2020, 11:28:08 am
Btw, if you have an Raspberry PI or can buy or otherwise obtain one, you maybe can drop the local use requirement.

Install the db on the rpi and in case you need to travel carry the whole server with you  :-X
Title: Re: which database?
Post by: chrnobel on January 22, 2020, 12:30:04 pm
Btw, if you have an Raspberry PI or can buy or otherwise obtain one, you maybe can drop the local use requirement.

Install the db on the rpi and in case you need to travel carry the whole server with you  :-X
Ahem, I would be very precarious with doing that.

RPi is a notorious SD card killer, so having a database on a SD card is risky business - you could attach an external USB harddrive, and thus being a bit more on the safe side.

And yes, there is a trail of corrupted SD cards here in my office.

100% OT - if you are making embedded solutions with RPi, I can warmly recommend using Alpine Linux instead of Trashbian.
Title: Re: which database?
Post by: MarkMLl on January 22, 2020, 12:51:35 pm
RPi is a notorious SD card killer, so having a database on a SD card is risky business - you could attach an external USB harddrive, and thus being a bit more on the safe side.

And yes, there is a trail of corrupted SD cards here in my office.

As a cautionary tale, I think that I managed to break an SDCard- and by that I mean make it so unusable that not even engineering utilities running on an Arduino could do anything with it- by using  telinit 0  rather than my customary  poweroff  command.

MarkMLl
Title: Re: which database?
Post by: marcov on January 22, 2020, 12:58:13 pm
RPi is a notorious SD card killer, so having a database on a SD card is risky business - you could attach an external USB harddrive, and thus being a bit more on the safe side.

I know. I used the Sheevaplug for years as remote backup solution, and it had the same problem. 

Anyway, there are mitigations, depends on how critical the data is.  In my case it was mainly nightly off-site backup of the SVN repo, but it was redundant since the offsite went to two such locations (my boss and my home).

The actual backup went to an USB stick, which mitigated the SD card problems. (but added a problem of guaranteedly mounting the USB stick on reboot/powerfailure). Even the place where updates placed the downloaded archives was rerouted to the USB stick.

Any problems -> replace USB stick, but not complicated configuration/installation necessary.

---
On the RPI it was a very low mutation database, so also a  nightly backup to an usb stick was ok and the clients kept a log anyway, so in the case of potential critical dataloss, I could manually apply the (relative few) mutations from the logs.

A while I also used the NFS of a NAS as such backup medium.


RPI4 should be able toattach sata SSDs via USB3. (haven't tried yet, still on RPI3)

Quote
And yes there is a trail of corrupted SD cards here in my office.

100% OT - if you are making embedded solutions with RPi, I can warmly recommend using Alpine Linux instead of Trashbian.

My experiences with musl libc are bad. But it probably depends if you run own built stuff, or just prepackaged.

Title: Re: which database?
Post by: Thaddy on January 22, 2020, 02:12:25 pm
My RPi's had some sd card killing issues in the early days, but not after I over-dimensioned all power supplies or used RPi sourced ones that are compatible with the version.
Title: Re: which database?
Post by: MarkMLl on January 22, 2020, 02:22:33 pm
My RPi's had some sd card killing issues in the early days, but not after I over-dimensioned all power supplies or used RPi sourced ones that are compatible with the version.

I agree that it's necessary, and can assure you that in my case the various RPis I was using- and their number was legion- were endowed with generously over-specified PSUs and supply wiring.

MarkML
Title: Re: which database?
Post by: lawman on January 23, 2020, 04:08:30 pm
Seems there is no other serverless option aside from sqlite and Firebird.

Decided to try Firebird as it's been updated quite a lot from some experiences here.

On issue of changing procedures without sitting down server I think this was resolved years ago. 

https://firebirdsql.org/refdocs/langrefupd20-alter-proc.html

It has built in compression and encryption for internet Comms.
Encryption aside if clients are making request to server over server then server is only sending back results and doing all searches  locally, so can't see benefit of web interface for clients?

Only concern is if I do end up needing to convert to postgresql, seems much easier with sqlite?

Still unsure of how mormot assists/benefits?

It's it quite easy to change sqlite to postgresql.   If so it might change decision.

Thanks


Title: Re: which database?
Post by: lazdeveloper on January 23, 2020, 06:37:28 pm
Hi,
This is a good question but in my opinion there is no optimal answer!
My experience  suggests you mysql. But as I said there is no optimal answer. I don't suggest postgres as I see it to slow your development process a bit. Good luck.
Title: Re: which database?
Post by: lawman on January 25, 2020, 01:43:34 am
almost decide that sqlite is the way to go.   firebird didn't even install on my manjaro linux but thats not the real reason.

1.  db files can be physically copied.   firebird may have issues with that
2.  can copy and read from a central master database concurrently, without a server.   This fulfils my requirement in the medium term.
3.  can also write (almost) concurrently,  with small delay to retry if db locked - but don't need centralised writing at this point.   
4.  small fast
5.  has encryption

2 questions

1.  how easy would it be to convert db and code to postgresql or firebird, if needed later?

2.  Is there a simplish way to sync between local db and remote master.db, merging all the locals from different clients on sql?



ps.  unrelated but i found an sqlite server too.  http://litesync.io/
Title: Re: which database?
Post by: DonAlfredo on January 25, 2020, 07:21:13 am
If you have decided to use sqlite, I would definitely recommend using the mORMot.
Learning curve is steep, but the reward(s) will be high.

Switching from sqlite towards postgresql or mysql or Mongo is as simple a changing one line of source-code.

Besides, mORMot offers static binding of the sqlite3 engine (with encryption). So, the only thing that is needed is a single executable. And it offers easy REST, websockets and many many more features. And a very active community.

Again, it will consume learning time, but extensive help (docs/people) is available.
Title: Re: which database?
Post by: Thaddy on January 25, 2020, 10:41:15 am
I agree with that. (except the learning curve is not as steep as you wrote, it is just slopy but there are many examples)
Static bindings are a major asset indeed because it removes dependencies. A.K.A. true embedding.
Title: Re: which database?
Post by: lawman on January 25, 2020, 12:27:04 pm
If you have decided to use sqlite, I would definitely recommend using the mORMot.
Learning curve is steep, but the reward(s) will be high.

Switching from sqlite towards postgresql or mysql or Mongo is as simple a changing one line of source-code.

Besides, mORMot offers static binding of the sqlite3 engine (with encryption). So, the only thing that is needed is a single executable. And it offers easy REST, websockets and many many more features. And a very active community.

Again, it will consume learning time, but extensive help (docs/people) is available.

1. sounds like mormot is more useful when i want to create a client/server scenario.     can i incorporate mormot later, or do i have to use from start?    as initially I'll just want a local app.

2.  cant sqlite already statically bind and db encrypt already without mormot?

3.  is it also just one line to switch from sqlite to firebird?

Title: Re: which database?
Post by: lawman on January 25, 2020, 10:44:31 pm
If you have decided to use sqlite, I would definitely recommend using the mORMot.
Learning curve is steep, but the reward(s) will be high.

Switching from sqlite towards postgresql or mysql or Mongo is as simple a changing one line of source-code.

can you use mormot with firebird?
Title: Re: which database?
Post by: lawman on January 27, 2020, 03:33:33 pm

The only problem I sometimes run into is rare locking of the SQLite base, if two updates are made within the exact same time - I have described that in another post, but I am working on a solution - typically it takes less than 5ms to make an update, so just a few retries of the commit in case of locking should do it.

Doesn't sqlite have a timeout feature to retry locked db for writes?   I'm hoping this resolves the issue as wanting to create a db which will stay on a mapped network drive.

Title: Re: which database?
Post by: delphi2pk on January 30, 2020, 09:52:49 am
Instead of mormot you can use REST Dataware. These are RAD opensource components and also available in Lazarus online package manager. Data over net is a charm in it. It uses ZeosLib to connect any supported database.

Only drawback is its in Portuguese language.
Title: Re: which database?
Post by: Thaddy on January 30, 2020, 10:03:55 am
There are rest examples in fcl-web (standard) and here: https://medium.com/@marcusfernstrm/create-rest-apis-with-freepascal-441e4aa447b7
Title: Re: which database?
Post by: devEric69 on January 30, 2020, 12:15:18 pm
Static bindings are a major asset indeed because it removes dependencies. A.K.A. true embedding.

Very true.
On Linux, we can also try and adopt aptitude (= to freeze a\the package{s} of a database server): «=» on the desired version.



Title: Re: which database?
Post by: lawman on January 31, 2020, 06:02:50 pm
Instead of mormot you can use REST Dataware. These are RAD opensource components and also available in Lazarus online package manager. Data over net is a charm in it. It uses ZeosLib to connect any supported database.

Only drawback is its in Portuguese language.

But this would still require a program running to serve requests from clients.

In that scenario why not just use client server DBMS?
Title: Re: which database?
Post by: delphi2pk on February 04, 2020, 07:38:01 am
Instead of mormot you can use REST Dataware. These are RAD opensource components and also available in Lazarus online package manager. Data over net is a charm in it. It uses ZeosLib to connect any supported database.

Only drawback is its in Portuguese language.

But this would still require a program running to serve requests from clients.

In that scenario why not just use client server DBMS?

REST Dataware do not require a persistent connection to open and it is stateless. Data transfer speed over internet will be high enough. An example server is provided with it. This server is enough for almost your all projects and databases.
TinyPortal © 2005-2018