Alternatives to Advantage Database - Advice Needed lazarus / 16-Databases ---------------------------------------------------------------------- re_Chained · 2022-07-27 14:00 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. ---------------------------------------------------------------------- marcov · 2022-07-27 14:12 File based databases on shares are old, so basically that is a 25 year old technical debt.  15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow. Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is. ---------------------------------------------------------------------- re_Chained · 2022-07-27 15:31 Quote from: marcov on July 27, 2022, 04:12:08 pmFile based databases on shares are old, so basically that is a 25 year old technical debt.  15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow. Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is. Yes, when I first joined the company I was a bit astonished at the level of technical debt we were under. And I'm afraid you may be right, a full rewrite to something more modern and scalable/maintainable is in order. PostgreSQL was on my short list of candidates, I think I'll move forward using that and see how it fares. NexusDB seems like the almost perfect solution, but we would be confined to using Delphi and paying a license for both, and I would like to move away from that. I want to use something that a larger number of people have experience with, and PostgreSQL seems like a suitable choice. Perhaps if we manage to find another developer that insists on using Delphi we can follow the NexusDB route. Thank you very much for the insight marcov. ---------------------------------------------------------------------- PierceNg · 2022-07-27 15:59 By your description the 'client-server' mode has no server component, as users launch your applications from network shared folders in said mode. But PostgreSQL does have a server component, and you'll have to consider whether every one of your customers is prepared to set up and operate a PostgreSQL server in their environment for their 3-5 users. ---------------------------------------------------------------------- Thaddy · 2022-07-27 16:20 Quote from: marcov on July 27, 2022, 04:12:08 pmFile based databases on shares are old, so basically that is a 25 year old technical debt.  15-25 years ago, the "don't want to change much" was acceptable, but it might be time to pull the plug and rewrite to a normal DBMS (I'd advise Postgresql).  Specially since you have to change DB anyhow. Lazarus doesn't have much like this from itself, specially when shared over multiple machines. Candidates are sqllite, and maybe access over a freetds link.  In theory there is also tdbf, but for share use I wouldn't even trust it as much as sqlite. I sometimes also hear rumours about NexusDB, but don't know exactly what that is. Well, both Advantage - which I frankly loved back in the days - and Nexus were really good in its days, but indeed 25 years ago and nobody uses it. The concurrency issues with the more modern Sqlite are a bit of a hoax.(See how mORMot handles this ), but in general a more mainstream database format is recommended. Sqlite, Postgress, MySql/MariaDb and corporate: expensive but good Oracle itself, although even Oracle has an almost free version . In a book keeping scenario with multiple users, I would personally use Postgress, but any big gun is a good choice. ---------------------------------------------------------------------- esdonis · 2022-07-27 16:49 For me, with Firebird it would be more than enough. ---------------------------------------------------------------------- Thaddy · 2022-07-27 17:10 Quote from: esdonis on July 27, 2022, 06:49:40 pmFor me, with Firebird it would be more than enough. For me it would pose a risk to my customers. ( as is well known) ---------------------------------------------------------------------- re_Chained · 2022-07-27 17:19 Quote from: PierceNg on July 27, 2022, 05:59:04 pmBy your description the 'client-server' mode has no server component, as users launch your applications from network shared folders in said mode. But PostgreSQL does have a server component, and you'll have to consider whether every one of your customers is prepared to set up and operate a PostgreSQL server in their environment for their 3-5 users. Indeed, the only "server" component is the Advantage Database Service that (essentially) monitors active connections and enforces the license limit. And our market base mainly consists of older people who are heavily resistant to change. We have around ~60 customer installations, and a tech team with a grand total of 1: me (along with an IT partner who manages many sites for us). Our prospective market share looks much the same: roughly 2 to 5 users using a single program at once, hence my want to keep everything simple. In the far future we may look at expanding to customers with a larger userbase, in which case we would want a solution that is a bit more scalable. Quote from: esdonis on July 27, 2022, 06:49:40 pmFor me, with Firebird it would be more than enough. Firebird was my initial choice, and the one our current developer initially suggested, but it doesn't seem to support encryption of the tables. Security is a major concern going forward, and another item on our long list of technical deficits, and I would like to implement a proper user login system as the system we have is rather poor in my opinion. If I'm going to be storing user passwords in a table, even if they are hashed, I would like the table to have an additional level of security. Thank you all for the advice. I have much to think about and discuss with business management about how best to move forward. I believe it will come down to whether or not we think our existing customer base will be open to having some SQL server installed or not, and how large we think any userbase in the future may be. ---------------------------------------------------------------------- dsiders · 2022-07-27 18:47 Quote from: re_Chained on July 27, 2022, 07:19:51 pmFirebird was my initial choice, and the one our current developer initially suggested, but it doesn't seem to support encryption of the tables. Database encryption is available since version 3: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/r nfb30-security-encryption.html Over-the-wire encryption is available in the same release: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/r nfb30-security-wirecrypt.html Additional encryption functions added in version 4: https://www.firebirdsql.org/file/documentation/release_notes/html/en/4 _0/rlsnotes40.html ---------------------------------------------------------------------- alpine · 2022-07-27 18:58 Quote from: Thaddy on July 27, 2022, 06:20:23 pm*snip* The concurrency issues with the more modern Sqlite are a bit of a hoax.(See how mORMot handles this ), *snip* How mORMot handles this? What exactly do you mean with that? ---------------------------------------------------------------------- re_Chained · 2022-07-27 19:32 QuoteDatabase encryption is available since version 3: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/r nfb30-security-encryption.html Over-the-wire encryption is available in the same release: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/r nfb30-security-wirecrypt.html Additional encryption functions added in version 4: https://www.firebirdsql.org/file/documentation/release_notes/html/en/4 _0/rlsnotes40.html I see, that's my mistake for not doing my research well enough. I will look into Firebird more and see if that's another possible route. ---------------------------------------------------------------------- Thaddy · 2022-07-27 19:33 Well, mORMot is free, examine its sources and docs.... ---------------------------------------------------------------------- Thaddy · 2022-07-27 19:36 Quote from: Thaddy on July 27, 2022, 09:33:45 pmWell, mORMot is free, examine its sources and docs.... And don't use Firebird. ---------------------------------------------------------------------- re_Chained · 2022-07-27 20:07 Quote from: Thaddy on July 27, 2022, 09:36:08 pmQuote from: Thaddy on July 27, 2022, 09:33:45 pmWell, mORMot is free, examine its sources and docs.... And don't use Firebird. Why do you suggest against Firebird, Thaddy? It could potentially fit our specific situation nicely, so if there's a certain major reason you're advising against it I'd like to know. ---------------------------------------------------------------------- alpine · 2022-07-27 21:14 Quote from: Thaddy on July 27, 2022, 09:33:45 pmWell, mORMot is free, examine its sources and docs.... I know it is free, I have examined the source and I have read the docs. In fact, I've been doing this a lot in recent years. Again - How mORMot handles this? What exactly do you mean with that? And why the mORMot could be a positive example at the expense of the Firebird? ---------------------------------------------------------------------- rvk · 2022-07-27 22:47 Quote from: re_Chained on July 27, 2022, 10:07:05 pmWhy do you suggest against Firebird, Thaddy? It could potentially fit our specific situation nicely, so if there's a certain major reason you're advising against it I'd like to know. Thaddy doesn't like Firebird that much  Something about not being 'industry standard' and 'problems' when you pass on the project. He is correct that it's not used as much as Oracle etc. (Neither is FPC/Lazarus) but for both being server and embedded without any change in code and support for it, it will do just fine. I've been using it for years and have no complaints. Especially because I can use embedded and server interchangeable. But everyone can have his/her own opinion. ---------------------------------------------------------------------- SymbolicFrank · 2022-08-01 11:26 For a major rewrite I would choose FPC / Lazarus over Delphi, as it is more adaptable and the people on this forum give excellent help. As for the choice of a database: as said, you don't want to maintain a database server on-site at each location. So, do your customers have internet access? If they do, use a big one in the cloud, like PostgreSQL. Make a database for each customer and make sure the users accounts can only access that one. If they don't, it becomes tricky. As said, file-based databases on a share are a bad idea. Multiple users on an SQLite database isn't an easy thing to do. With an Embedded Firebird, you have to delegate one app to be the server. Tricky. For security and safety, the big cloud platforms have all the tools you need: VPN connections, automatic management and backup. You can even deploy a different database for each customer. In the long run, that is the best bet.