Best DB to use for multiplatform distribution and other related questions

[Codelizard]

@Bigchimp - Thanks so much for the insight.  This is the way I am leaning, at the moment.

The other reason I like the firebird server solution is scalability...  I would MUCH rather keep this simple and contained.  And I'm very familiar with Inno Setup, I use it for our current VB app.

Any idea how simple the Firebird setup is on OSX?  Windows and OSX are my important areas.  And my only concern with Firebird apart from the install is, again, simplicity.   I dont want to be in the business of supporting firebird itself.  If it is set and forget and runs unattended forever, then its the way to go.

Oh, how about the DB itself?  Is it in a single file?  If there IS an issue, rather than me having to get in a car or on a plane, can they send me thier database for me to fix in case of a catastrophe?



Now, to completely change the subject.  If, for some reason, I decide to go Client/Server.

In my head, when I've been thinking about a SERVER and a CLIENT, I was envisioning the server as having ALL of the data logic.  I mean, the client would send a structured request.  For example, it might send the following request:

001Smith

Which could me interpreted as "Search for customers (transaction #1), with the name of smith". 

But maybe I'm overthinking this. 

What if I create some sort of thin-server, that accepts pre-written queries from the client, executes them, and then returns the resulting data?  I'd just need to figure out the scemantics of how to format the requests and the resulting data.  But this could essentially solve a ton of issues, and I'm kind of surprised noone has already designed and written one of these.

[BigChimp]

Any idea how simple the Firebird setup is on OSX?

Didn't have any problems installing it. I think it's easy as well. It's been a while though. You'll probably also have to open up the firewall if you intend to use it as a server for remote clients.

Haven't tried Firebird embedded on OSX...

Oh, how about the DB itself?  Is it in a single file?  If there IS an issue, rather than me having to get in a car or on a plane, can they send me thier database for me to fix in case of a catastrophe?

Yes it's an FDB file; yes they can send it to you. Another tip: automate backups and restore on a different location. Then let the server/embedded open the restored file, which checks whether the backup is trustworthy. See www.firebirdsql.org and http://www.firebirdfaq.org/ for lots more info.

Support via SSH/VPN can also be done and there's commercial support for Firebird as well. Also good support via Firebird mailing lists (see site above).

Finally, Firebird comes configured by default in a conservative setting (write to disk as soon as possible) which takes away a lot of crash potential. Just a hobby user so can't really comment based on experience.

In my head, when I've been thinking about a SERVER and a CLIENT, I was envisioning the server as having ALL of the data logic.  I mean, the client would send a structured request.  For example, it might send the following request:

001Smith

Which could me interpreted as "Search for customers (transaction #1), with the name of smith". 

But maybe I'm overthinking this. 

What if I create some sort of thin-server, that accepts pre-written queries from the client, executes them, and then returns the resulting data?  I'd just need to figure out the scemantics of how to format the requests and the resulting data.  But this could essentially solve a ton of issues, and I'm kind of surprised noone has already designed and written one of these.

Sounds reasonable.
I'm using something like this for a home OCR/document managmeent project: sending requests like "list all documents", "show image z", "OCR image x" and I get back e.g. an array of document names, creation dates, a TIFF image or a TOCR text.
I use the JSON and fcl-web code provided by Lazarus/FPC, i.e JSON+REST via http.

see https://www.ohloh.net/p/papertiger
Brook is built on these technologies as well.

That said, I'm a beginner here so I'll leave it at that.

There's another solution where somebody wrote a "send query SQL" and "get records back" engine which allows you to basically plug in the network.

[Codelizard]

Didn't have any problems installing it. I think it's easy as well. It's been a while though. You'll probably also have to open up the firewall if you intend to use it as a server for remote clients.

Haven't tried Firebird embedded on OSX...

I've already done some reading about the embedded on osx, and looked simple enough.

Yes it's an FDB file; yes they can send it to you. Another tip: automate backups and restore on a different location. Then let the server/embedded open the restored file, which checks whether the backup is trustworthy. See www.firebirdsql.org and http://www.firebirdfaq.org/ for lots more info.

Sweet, so i could potentially have them send me the fdb file, check it to be sure its good.  They could actually uninstall/reinstall firebird, and then replace the fdb file?  Im just trying to wrap my head around this.

Finally, Firebird comes configured by default in a conservative setting (write to disk as soon as possible) which takes away a lot of crash potential. Just a hobby user so can't really comment based on experience.

No, that sounds perfect.  I'd rather err on the side of caution, for sure.

There's another solution where somebody wrote a "send query SQL" and "get records back" engine which allows you to basically plug in the network.

You wouldnt have a link to that would you?  That would basically let me turn any embedded db into a network server.  My only hangup right now, is how i would structure and return the data.  The server would be "dumb", so to speak.  I'd probably build out a number of stored procedures for some of the business logic, so I didnt have to keep going back to the db when coding a certain piece of logic.

[BigChimp]

I've already done some reading about the embedded on osx, and looked simple enough.

Ok, if you get it done, all additions to http://wiki.lazarus.freepascal.org/Firebird_embedded#OSX are welcome

Sweet, so i could potentially have them send me the fdb file, check it to be sure its good.  They could actually uninstall/reinstall firebird, and then replace the fdb file?  Im just trying to wrap my head around this.

Yes... but...
Caveats:
1. Bitness must be the same between the system. x64/x86 is little endian so no problem with current Windows, OSX and x86/x64 Linux.
Otherwise you'd have to scrounge e.g. an old PowerPC mac...
2. Firebird files have a version number (the ODS version). Old ODS versions work on newer Firebird servers.
Vice versa: no.
In practice: install current Firebird (version 2.5.x) and make sure you have the newest version on your machine as well.
For added security, you could let your program report the firebird version it finds/last found as well so end users can read it out to you on support calls.

Using a backup file (.fbk) avoids these caveats.

Having the users reinstall Firebird sounds like it would work after you've checked the database. A more foolproof solution: send a backup file of the fixed db instead of the db itself, then let the client's program restore the backup. This way they can use a newer or older version of Firebird without any risk.

There's another solution where somebody wrote a "send query SQL" and "get records back" engine which allows you to basically plug in the network.

You wouldnt have a link to that would you?  That would basically let me turn any embedded db into a network server.  My only hangup right now, is how i would structure and return the data.  The server would be "dumb", so to speak.  I'd probably build out a number of stored procedures for some of the business logic, so I didnt have to keep going back to the db when coding a certain piece of logic.

No sorry, can't find it right now.
As for structure, I'd go for JSON (arrays for datasets I think). It's the fad du jour but also quite sensible in that it's not as bloated as XML, it's fairly self-describing and close enough to ASCII to appeal to the cough more mature generation cough

Also, FPC supports it out of the box.

[kpeters58]

Let's throw one more contender into the mix:

http://www.sqlabs.com/cubesql.php

[karaba]

Let's throw one more contender into the mix:

http://www.sqlabs.com/cubesql.php

Through in a small summary as well, is it a server based solution does it supported embedded developement is it a commercial or does it have commercial support etc etc just the basics.