Recent

Author Topic: which database?  (Read 5807 times)

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8779
  • FPC developer.
Re: which database?
« Reply #15 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 :-)

ttomas

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

rvk

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

ttomas

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

lawman

  • New Member
  • *
  • Posts: 42
Re: which database?
« Reply #19 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

chrnobel

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

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8779
  • FPC developer.
Re: which database?
« Reply #21 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

chrnobel

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

MarkMLl

  • Hero Member
  • *****
  • Posts: 1319
Re: which database?
« Reply #23 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 8779
  • FPC developer.
Re: which database?
« Reply #24 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.


Thaddy

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

MarkMLl

  • Hero Member
  • *****
  • Posts: 1319
Re: which database?
« Reply #26 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

lawman

  • New Member
  • *
  • Posts: 42
Re: which database?
« Reply #27 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



lazdeveloper

  • Jr. Member
  • **
  • Posts: 61
Re: which database?
« Reply #28 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.

lawman

  • New Member
  • *
  • Posts: 42
Re: which database?
« Reply #29 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/

 

TinyPortal © 2005-2018