Recent

Author Topic: which database?  (Read 8507 times)

lawman

  • New Member
  • *
  • Posts: 43
which database?
« 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

HeavyUser

  • Sr. Member
  • ****
  • Posts: 397
Re: which database?
« Reply #1 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.


JD

  • Hero Member
  • *****
  • Posts: 1848
Re: which database?
« Reply #2 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
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

lawman

  • New Member
  • *
  • Posts: 43
Re: which database?
« Reply #3 on: January 20, 2020, 10:06:09 pm »
but postgresql doesn't have single user embedded and isn't small?  :(

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: which database?
« Reply #4 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 
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
Re: which database?
« Reply #5 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

MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: which database?
« Reply #6 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
« Last Edit: January 20, 2020, 11:08:53 pm by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: which database?
« Reply #7 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).

JD

  • Hero Member
  • *****
  • Posts: 1848
Re: which database?
« Reply #8 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
« Last Edit: January 20, 2020, 11:40:29 pm by JD »
Windows - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe),
Linux Mint - Lazarus 2.1/FPC 3.2 (built using fpcupdeluxe)

mORMot; Zeos 8; SQLite, PostgreSQL & MariaDB; VirtualTreeView

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: which database?
« Reply #9 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
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

ttomas

  • Full Member
  • ***
  • Posts: 245
Re: which database?
« Reply #10 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.

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: which database?
« Reply #11 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.

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11382
  • FPC developer.
Re: which database?
« Reply #12 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)

rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: which database?
« Reply #13 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).
« Last Edit: January 21, 2020, 11:46:54 am by rvk »

chrnobel

  • Sr. Member
  • ****
  • Posts: 283
Re: which database?
« Reply #14 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.

 

TinyPortal © 2005-2018