Hello all,
I'm sure the question of "which database engine is right for me" gets asked a lot, but I'm really looking for some quality opinions on anyone who knows much about Advantage Database. Apologies beforehand for what may be a long-winded post riddled with misunderstandings.
Not to get into specifics, but I'm about to inherit the source code for the applications the small company I work at produces. There are around 5 of them, each serving a specific purpose, and they are all built around providing bookkeeping services for a niche market. They are all written in Delphi, across various compilers, and are in need of updating. I've been studying and learning about app development using Lazarus for the past few months, having a bit of general programming knowledge already, but the task is seeming a bit daunting, to say the least. One thing I'd like to get right off the bat is what database to move to for updated versions of the apps, as Advantage Database licenses are hard to acquire now and SAP doesn't seem keen in continuing the project based on my what I've seen.
For an idea of what the apps do, the 'main' app is the central ledger. Sometimes other programs may post information into its database, or users will enter in invoices, purchase orders, new vendors, etc. as needed. The market the apps are aimed for typically have small offices with one to three people entering this kind of information as it comes in, so the database wouldn't need to be receiving constant small writes or very large writes. Reports are generated from the database a couple times a day. The majority of operations will be read/writes, the tables are rarely altered and no join operations or anything occur.
We offer a "local" and "client/server" versions of the programs, but they are essentially the same. They both use Advantage Database embedded as the main database engine, with .dbf files located in the program folders. The main difference between the two versions is that the "client/server" version looks for the ADS service running on the server. The "client/server" version works by having the program folder shared over a network drive (all the apps are Windows only) to whatever workstations need it, and the users just launch the program from the mapped drive. The number of concurrent apps/connections to the database files they are allowed is determined by the Advantage license they have. For the vast majority of customers, a max 5 people are going to be accessing a particular program at once.
I'd like to move forward changing as little as possible, in regards to our workflow for software support and the installation/use process for customers. The .dbf tables we're currently using support password encryption, and some of our support team often using the Advantage Database tool ARC to open the tables and troubleshoot issues. I've been toying with SQLite with SQLCipher, using the DB Viewer for SQLite to achieve a similar effect. And SQLite works well for a local version of any app, but I'm worried about concurrency with the write limits, and SQLite's own admission that "file locking is buggy in many network file systems" (
https://www.sqlite.org/whentouse.html). As mentioned, the software is bookkeeping related with a smallish number of concurrent users, so write conflicts may be manageable with proper implementation, but I'm not sure. I'd ideally also like the eventual migration for customers to be as painless as well.
Our current sole developer has been maintaining these programs in some form for nearly 25 years, but is very behind-the-times in much of modern tech, so I'd love any and all outside advice. Any references, books, examples, what components to use, best practices, criticisms, anything and everything not just related to the above database questions would be greatly appreciated.