Best DB to use for multiplatform distribution and other related questions lazarus / 16-Databases ---------------------------------------------------------------------- Codelizard · 2014-04-02 00:52 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 · 2014-04-02 01:44 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 · 2014-04-02 02:23 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 · 2014-04-02 03:47 Quote from: Mike.Cornflake on April 02, 2014, 03:44:25 amWelcome :-) 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 · 2014-04-02 04:00 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 · 2014-04-02 04:08 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 · 2014-04-02 04:38 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 · 2014-04-02 05:09 http://forum.lazarus.freepascal.org/index.php/topic,19900.msg113973.ht ml#msg113973 ---------------------------------------------------------------------- exdatis · 2014-04-02 05:12 http://forum.lazarus.freepascal.org/index.php/topic,17241.msg94767.htm l#msg94767 ---------------------------------------------------------------------- exdatis · 2014-04-02 05:16 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 · 2014-04-02 07:08 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 · 2014-04-02 08:17 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 · 2014-04-02 12:34 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 · 2014-04-02 13:56 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 · 2014-04-02 14:08 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_Administrat ion) 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. ---------------------------------------------------------------------- Codelizard · 2014-04-02 14:25 @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 · 2014-04-02 14:39 Quote from: Codelizard on April 02, 2014, 04:25:37 pmAny 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... Quote from: Codelizard on April 02, 2014, 04:25:37 pmOh, 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. Quote from: Codelizard on April 02, 2014, 04:25:37 pmIn 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 · 2014-04-02 14:50 Quote from: BigChimp on April 02, 2014, 04:39:48 pmDidn'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. Quote from: BigChimp on April 02, 2014, 04:39:48 pmYes 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. Quote from: BigChimp on April 02, 2014, 04:39:48 pmFinally, 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. Quote from: BigChimp on April 02, 2014, 04:39:48 pmThere'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 · 2014-04-02 15:05 Quote from: Codelizard on April 02, 2014, 04:50:29 pmI'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 Quote from: Codelizard on April 02, 2014, 04:50:29 pmSweet, 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. Quote from: Codelizard on April 02, 2014, 04:50:29 pmQuote from: BigChimp on April 02, 2014, 04:39:48 pmThere'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 · 2014-04-02 18:30 Let's throw one more contender into the mix: http://www.sqlabs.com/cubesql.php ---------------------------------------------------------------------- karaba · 2014-04-02 20:35 Quote from: kpeters58 on April 02, 2014, 08:30:03 pmLet'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.