Best DB to use for multiplatform distribution and other related questions

[Codelizard]

So, I've been tasked to take a tool we have that is written in VB6 and move it to a new technology that I can distribute to other OSs.  Linux and MAC being the immediate needs, maybe phone platforms later on.

I'm looking for a database format that I can write once for, yet be able to distribute for multiple platforms.  But thats not the whole of it.

The current program can run as a standalone app, which is simple enough.  But, it can also run as a networked app.  This is currently done using mapped drives, and all workstations just map to the same DB.  This runs nicely, but does it lend itself to MAC and Linux?

We have another program in VB that also works standalone or network.  But, if you are using it in network mode, the clients dont do direct DB access.  The "server" becomes a TCP server and handles "transactions" requested from the clients.

So, those are my two potential architectures.  Shared DB, or true client server.  I need to decide which technology makes the most sense, and then the correct DB to use.

As far as DBs go, I need something that does as simple an install as possible.  I would also prefer a SQL interface.  Our VB app uses a ms-access database using ADO, so you know where I'm coming from.  I'm looking for an east DB.  I dont know whats involved, for example, in getting MySQL running from a plain innodb install.  If I could just install a file (like a mdb, or maybe a Firebird DB file), thats how I'd like to do it.

But I dont then know the best DB sharing method, for the second part of the issue.

So, you see what it is I'm, working against.  I'm pretty excited about this platform, and definitely surprised I had not heard of it sooner.  I'm hoping with a good job on this project, we might convert our from from VB to Lazarus completely.

Any ideas, comments, and/or insight would be GREATLY appreciated.  I'd like to make some decisions on app design up front before i begin blocking out screens. 

Thanks!

[Mike.Cornflake]

Welcome :-)

Fairly common question.  I'd search the forums and the wiki, in addition to what others reply here, you'll get a feel for other opinions as well.

http://wiki.freepascal.org/Portal:Databases

http://wiki.freepascal.org/Databases summarises different requirements for each DB engine.

[kpeters58]

Welcome!

You can easily gather dozens of different answers/solutions to the requirements you describe.

The "easiest" DB from a deployment and other standpoints is certainly SQLite, which has its limitations when it comes to multi-user deployment (more than maybe 5 simultaneous users WILL cause you pain).  MySQL/InnoDB databases are very easy to move around platforms as well (I just copy them between my Macs & Windows boxes).

Since you come from VB:  I also use this for its awesome platform support - have a look at PureBasic. Its form designer lags sorely behind Lazarus, but it creates small & fast executables on all platforms and you can talk natively (without any DLLs or libs) to a number of databases.

[Codelizard]

Welcome :-)

Fairly common question.  I'd search the forums and the wiki, in addition to what others reply here, you'll get a feel for other opinions as well.

http://wiki.freepascal.org/Portal:Databases

http://wiki.freepascal.org/Databases summarises different requirements for each DB engine.

Yeah, I've already done a BUNCH of searching and reading before I posted, both on the forums and google.  I had already been to the first link you sent, but the second one was awesome   Thanks for it.  But, it left me with more questions than when I started.

The most important 2 platforms for me are Windows and OSX.  According to that chart, I may be able to use Firebird Embedded on those 2 machines, but if I want another SERVERLESS database then its foxpro, dbase, and paradox.  Obviously, Id rather use an SQL interface (can I use SQL on a DBF file?)

So, now that I see that chart, its easier to specify I need something serverless and OSX and Windows compatible, at the least.  SQL access would also be preferred, of course.

[Codelizard]

Hmmm... now that I'm looking at it, arent options #1 and #3 sort of the same?  They could both work depending on the connection settings?  So... could I deploy with firebird embedded... and a single installation could use that.  No problem.  But a networked installation could SHARE the one file, or could change settings to use an installed firebird server?


So no.  Firebird embedded is exclusive lock.  So option #1 with firebird is a nogo.

Well, now I'm wide open again.  lol.  More reading....

[Mike.Cornflake]

G'day,

:-)  I'm not an expert, but I don't think SQLite requires a server or installing either.  All you need is the library file (dll for windows - I'm ignorant as to what these are called on other platforms).

In fact, I'm sure of it.  You just need the library file...

Mike

UPDATE:  I was responding to an earlier post of yours.  I missed the last one...   So ignore this :-)

[Codelizard]

Ugh.  Seems I clicked on EDIT MESSAGE before when I was trying to quote, and deleted like 95% of my message   Not that familiar with the SMF forum.  Lesson learned, I suppose.

Anyways, yes, I would definilitely like something serverless.  The sqlite looks like it does that.  But the multiuser thing is kind of daunting, unless i can do the "hit and run" approach that I used on VB and Access.

My head hurts.  lol.  I think Ill break for the night and continue in the morning.

[exdatis]

http://forum.lazarus.freepascal.org/index.php/topic,19900.msg113973.html#msg113973

[exdatis]

http://forum.lazarus.freepascal.org/index.php/topic,17241.msg94767.html#msg94767

[exdatis]

Now I mostly use Firebird. The syntax for writing stored procedures is fantastically clear and easy to use. I also have the ability of embedded server. Great RDBMS!
Regards
p.s. Triggers, exceptions and much more...

[karaba]

serverless access like the DBase, paradox and access products (aka file based databases) is not supported from any of the open source (or commercial for that matter) databases for a simple reason, not all platforms support the idea of a shared directory, for example in linux there is no shared directory type of thing (that I know off always) but you do have NFS ee network file system which can be used for the same purpose.
Reducing the requirements from windows shares to tcp is a must if you want a multi-platform application so you should go for a server based solution it will help you have heterogeneous environments (mac, linux, and windows in a single lan) as well with out the need of more specialized software to make them work together in addition of your application, there are more to gain from avoiding the file based data including security.

The most common solution on local "database like" access is SQLite it has been seen to work on Iphone, android and various other mobile platforms and it is known that it works with windows, linux and I think macs too, having said that I would vote against using it in your situation and go for something else.

I would look for a pure pascal implemented database that can be compiled by you directly to any platform your compiler supports or if you have C/C++ skills then pick one of the better open source databases to use ee firebird is known to work with linux mac and windows and I think that it has an embedded version for all the platforms, this will solve most of your problems but its network access is server based. I think that they recently started a beta for the client library on android platforms.

[Jkey]

Although I like SQLite very much, I have experienced buggy transactions on a windows based network file share. The fifth point of SQLite FAQ ( https://sqlite.org/faq.html#q5 ) describes the possible reason: file locking problem, which seems to be unavoidable on NFS filesystems.

[Codelizard]

You guys rock!  I'm gleaning all kinds of really useful information from you and from the reading material you've pointed me to.

That being said, I'm now down to 2 possible scenarios.  Which one I do depends on, again, the ease of use.

So, obviously, I'd rather not write my programs twice.  I would RATHER not have to maintain a standalone edition, and then the CLIENT and SERVER of a Network edition.  That's 3 programs to maintain.  So, that being said, here's the two ideas I'm deciding over:

1) Firebird.  I set up my app to use firebird.  Period.  And I include the embedded version.  But, have a configuration to be able to add a host string and point it to a full-blown firebird server.   This will ONLY be feasible if I can make it simple to install the firebird server.  Some of the departments are little more than an external office with a bunch of sales people (zero IT staff).  I need something that installs VERY SIMPLE.  If I can do this with Firebird, this solves all of my issues.  I have 1 piece of code to maintain.   Each installation can run self-contained (embedded) or networked (firebird server).

2) Firebird or SQLLite / TCP.  I create a server program to do all of the data io, as well as a client with all of the GUI stuff.  I would also need to create a standalone version for simple installations that are non-networked.  I now maintain 3 code bases, and need to keep them in sync for the smallest code changes.

Now, obviously for many reasons I would love to go with option 1.  1 codebase.  but, it depends completely on:

A) Installation of Firebird server.   Is there a way to do this and automate it so its dirt simple.

B) How difficult is the firebird database to manage?  Is this solid enough to set and forget, or are there gotchas that might come back and bite me in the arse.  Remember, some offices have zero IT resources.

If you need some decent computer skills to install firebird, or the firebird server requires some sort of babysitting, then option #2 becomes the one that is going to work best.

The best news, from both of these options, is it looks like I can cross platforms either way.  For example, if a department has both MAC and Windows stations, both the firebird server AND the tcp   server will be viable options.  The could even have a office full of macs and decide to run the firebird server on linux, if need be.

Can anyone weigh in on the firebird questions?  Server Installation and Firebird maintenance requirements?

Thanks!

[hrayon]

Sorry my english .
My suggestion will make you lose time at first , but then minimize future work .
I suggest you do a server program that intercepts all requests to the database . Only the server computer needs to worry about the database . Application client < - > Application server < - > Database .
Thus , the client application need only know the server and the connection port . Nothing to install client versions of databases . Less work . Less problems.
Thus , it is interesting to have a server application with embedded database . This will minimize problems if other applications on the server computer are using the same DBMS . For example , your application server will use the firebird 2.5 ( or sqlite 3 ) and other applications can use other versions of databases .
In the server firewall , you just need release the server application port to client applications.
You always keep two applications . If you need to work in standalone mode , install the client application and server application on the same computer .
Study a little about Brook Framework , or Mormot , or tiOPF ...
Good read .

[BigChimp]

Firebird server installation is click, click, click on Windows using the installer.
IIRC there's a switch for unattended (quiet install). The installer is an Inno Setup script and can be easily adapted as well.

Firebird embedded is even easier: bundle it with your application. The embedded lib serves as a client library as well so that's all you need to install.

Maintenance burden is very low: backup/recovery as needed for any database, can be scripted or automated (e.g. programmatically see http://wiki.lazarus.freepascal.org/Firebird_in_action#Database_Administration)

For more details see firebirdsql.org and http://wiki.lazarus.freepascal.org/Firebird_in_action

Firebird server install on Linux is easy.

I would look into the 1st solution but I'm biased because of my Firebird use (it has stored procs, triggers, good support by FPC/Lazarus sqldb).


@hrayon: yes codelizard could write a 3 tier solution but would still need his clients to set up a server etc. IMO 3 tiers would complicate things; you can also write 2 tiers (and preferably keep your database management code apart from your GUI but that's obviously not required).

3 tier could of course be done; if so, look at e.g. tiopf or brook (as hrayon said) to split things out.