File based database driver (multi-client) lazarus / 16-Databases ---------------------------------------------------------------------- ArminLinder · 2021-03-16 14:13 Hi all, is there any database-like component/library for Lazarus available which meets the following set of "must" criteria: - Must be entirely installation-free on server and client. A single .exe (dll if any must be embedded)  and a networked file structure (Windows network drive/SMB storage) must suffice. - All Data files are encrypted using some state-of-the-art encryption - Concurrent R/W access by mutliple clients - Support for one data type for storing large unstructured data (blob) - Reasonably priced single developer/unlimited copies license fee of up to say 100.- What is not required - Performance. Number of read accesses will be below 10/minute (!), and probably 1 write-access per minute. - SQL or any compatibility with any other popular database - Multi-User authentication, rights management, ... - Transactions, referential integrity, Full-Text search, ... - Extremely simple data structure, not much more than a handful of tables with simple search indexes. Even relations can be coded manually, there are only a few. - 1000 rows max. will do - integration into any of the many Lazarus DB components welcome, but not a must - Unicode or locale support - Platform independence, Windows-only is OK. Right now I am using a self-written "database" based on a simple in- memory search tree and a file of record, which has been sufficient for half a decade, mainly because there were very infrequent database accesses, and it were mostly read accesses. Almost all data changes would come from one computer. Now it was demanded that I add more functionality which makes write accesses to the database much more frequent, changes are likely to appear on different clients. Thnx, Armin. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-16 15:11 Hi Armin, First let me state that I'm not that of an expert on databases. But from what you are asking in the first list, I think that SQLite is a good fit. If I'm not mistaken it will fulfil all your requirements, even the encryption one(I think?!?!). And you can use it with any DB aware, always a plus. Have a look at this: SQLite - Free Pascal wiki Hope it helps! Cheers, Gus ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-16 15:45 Hi Armin, After a better read of SQLite - Free Pascal wiki I think that it won't satisfy your multi-user condition. Even had a read at Firebird embedded - Free Pascal wiki and it also states it's single user. I'm afraid that my limited options are now exhausted. I'm not aware of a multi-user single file database engine, though I thought I did, and I'm guessing if there's one, it will be commercial. Not having a server to manage multiple connections and relying on File System Foo to support the multi-user is quite hard to achieve. So I'm guessing that you're in for a hard find. But again, like I said, I'm far from being an expert on databases and I could just be ignorant of the single file DB landscape. So please correct me if there is something like Armin is asking for!! I would be quite interested on knowing about it myself!!! Cheers, Gus ---------------------------------------------------------------------- Jurassic Pork · 2021-03-16 15:53 hello, Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter). But it is very well tested and very stable (and widely used) so you can trust it. You may read this short document for information when to use SQLite and not: http://www.sqlite.org/whentouse.html where you find : QuoteSQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will. Friendly, J.P ---------------------------------------------------------------------- ArminLinder · 2021-03-16 15:58 Thanks, Gus, for your reply. AFAIK from the wikis, SQLite is not muti-user/client, i.e. if one user writes, the whole database gets locked. This may not be a real issue for my application though, as I wrote writes are extremely few in my application. Some simple timeout-retry loop scheme might suffice. Second concern is that you need to pass the password/key in plain text through the API, I guess it would not be too hard (by replacing the (externally stored) sqlite3.dll with my own) to get at the password. I read, however, some (Delphi) wikis where methods are described to embed the dll as a resource in my code (which would make a hack a bit harder), but still I think something better than passing a plain text password string would be preferred. Otherwise I am with you, SQLite is kind of a "gold" standard when it comes to small databases and well maintained and tested with Lazarus, I'll definitely give it a try, if I don't find anything better soon. Best regards, Armin. ---------------------------------------------------------------------- cdbc · 2021-03-16 16:02 Hi Have a look at Firebird: https://firebirdsql.org/ Edit: Or write your own server, based on SQLite, maybe with Synapse & json... I don't know  Regards Benny ---------------------------------------------------------------------- PierceNg · 2021-03-16 16:05 Quote from: Jurassic Pork on March 16, 2021, 04:53:41 pmhello, Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter). But it is very well tested and very stable (and widely used) so you can trust it. Yes, but ... OP wants to do client-server based on "networked file structure (Windows network drive/SMB storage)". For this scenario, see https://sqlite.org/useovernet.html, especially: QuoteThe bottom line is that network filesystem sync reliability varies among implementations and installations. [...] Rely upon it at your (and your customers') peril. Also: QuoteThese network unreliability issues can be mitigated, completely or to an acceptable degree, by using SQLite in rollback mode. However, the SQLite library is not tested in across-a-network scenarios, nor is that reasonably possible. Hence, use of a remote database is done at the user's risk. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-16 16:26 Hi Armin, I have to warn you that when I said SQLite was not multi-user, I forgot to mention the context. That context has been very well explained by the answer from PierceNg: It's not reliable over both NFS and SMB drives. My questioning is now: I'm assuming that you have a dedicated machine that is always on to support your network drive, right? So why not use a server type DBMS? You already have the hardware, why not use it in a more efficient way? If you do decide on that, then I would greatly advise Firebird, since it's free, quite lightweight and well supported under FPC/Lazarus. The only thing that remains to be solved is the license policies of your target company. Cheers, Gus ---------------------------------------------------------------------- ArminLinder · 2021-03-16 19:35 Aaah, I see, my fault, sorry! "- Must be entirely installation-free on server and client." is not quite what I wanted to express. There must not be a "server" (service), all I have is a "device" sharing a file system with Windows. This may be a NAS or any other WIndows PC oder "Server" sharing a network drive. I do not have a dedicated server sharing a DBMS service, and I do not have any influence which device it may be. The application is sold to customers I don't know, and is supposed to function regardless of what they have, as long as Windows can mount it as a network drive. That's what the existing version did for five years, and prime requirement is that the new version works over the same infrastructure. Firebase looks like server/client, and thus is ruled out, right? What does it look like if SQLite is "not reliable"? Any detailed reading about what malfunctions happen under which circumstances, so I can see whether I can - for my rather low requirements - tolerate the problems? Thanks so far for your input, Armin ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-16 19:55 Quote from: Nimral on March 16, 2021, 08:35:44 pmAaah, I see, my fault, sorry! "- Must be entirely installation-free on server and client." is not quite what I wanted to express. There must not be a "server" (service), all I have is a "device" sharing a file system with Windows. This may be a NAS or any other WIndows PC oder "Server" sharing a network drive. I do not have a dedicated server sharing a DBMS service, and I do not have any influence which device it may be. The application is sold to customers I don't know, and is supposed to function regardless of what they have, as long as Windows can mount it as a network drive. That's what the existing version did for five years, and prime requirement is that the new version works over the same infrastructure. Ahhh, yeap, I now get what you mean by that!! Thanks for the clarification. Quote from: Nimral on March 16, 2021, 08:35:44 pmFirebase looks like server/client, and thus is ruled out, right? Well, yes and no... If you use it embedded, it's not server/client, but then it's single- user only. If you use the server, then it fails the need you explained earlier. Quote from: Nimral on March 16, 2021, 08:35:44 pmWhat does it look like if SQLite is "not reliable"? Any detailed reading about what malfunctions happen under which circumstances, so I can see whether I can - for my rather low requirements - tolerate the problems? To be honest I don't know enough to give you a nice answer. I guess you would have to experiment and see what happens. Hopefully not at your client, never in prod right? Quote from: Nimral on March 16, 2021, 08:35:44 pmThanks so far for your input, You are more than welcome mate!! Cheers, Gus ---------------------------------------------------------------------- korba812 · 2021-03-16 21:55 Quote from: Nimral on March 16, 2021, 03:13:12 pmis there any database-like component/library for Lazarus available which meets the following set of "must" criteria: - Must be entirely installation-free on server and client. A single .exe (dll if any must be embedded)  and a networked file structure (Windows network drive/SMB storage) must suffice. - All Data files are encrypted using some state-of-the-art encryption - Concurrent R/W access by mutliple clients - Support for one data type for storing large unstructured data (blob) - Reasonably priced single developer/unlimited copies license fee of up to say 100.- What is not required - Performance. Number of read accesses will be below 10/minute (!), and probably 1 write-access per minute. - SQL or any compatibility with any other popular database - Multi-User authentication, rights management, ... - Transactions, referential integrity, Full-Text search, ... - Extremely simple data structure, not much more than a handful of tables with simple search indexes. Even relations can be coded manually, there are only a few. - 1000 rows max. will do - integration into any of the many Lazarus DB components welcome, but not a must - Unicode or locale support - Platform independence, Windows-only is OK. You can try TDbf or Advantage Database (requires additional dlls) - it runs on a shared drive and doesn't require a server. https://wiki.freepascal.org/TDbf https://wiki.freepascal.org/Advantage_Database_Server https://www.freepascal.org/~michael/articles/ace/ace.pdf ---------------------------------------------------------------------- cdbc · 2021-03-17 03:29 Hi How about TurboPowers flashfiler: https://sourceforge.net/p/tpflashfiler/wiki/Home/ Regards Benny ---------------------------------------------------------------------- PierceNg · 2021-03-17 06:00 All database type software, whether SQLite, dBase, Firebase embedded, or server-side like PostgreSQL, eventually relies on the underlying filesystem of the machine it runs on to read/write their data files. AFAIK, only Oracle has had a "raw device" driver, by which the Oracle database server manipulates block storage directly without an OS- managed filesystem in between. Let's take it as given that filesystems on mainstream OSes like Linux and Windows are reliable enough these days for the likes of SQLite and Firebird embedded, bugs in application and database code notwithstanding. As the "using SQLite over network" document explains, the picture is different for network filesystems. According to folk lore, in ancient times Banyan Vines and Novell Netware had network filesystems good enough for shared network drive databases. Never dealt with them myself though. ---------------------------------------------------------------------- Jurassic Pork · 2021-03-17 08:24 hello, with firebird client/server you don't need  a complete installation : for example on windows : 1 - download the zip package and unzip it on a folder (50Mb max and you can remove some files). 2 - configure the file databases.conf to declare your database (name and path for your fdb file) 3 - Launch the script install_service.bat 4 - Create a user for your database 5 - Allow port 3050 for private connexion in  your firewall Now you can access to your database from another computer (need fbclient.dll in the path of the client application). If you want to change the computer database server : 1 - Launch the script uninstall_service.bat on the old computer database 2 - Copy the firebird folder of your old database computer in the new computer. 3 - Launch the script install_service.bat ======================================================== Example with my lazarus database example in lazarus\examples\database\image_mushrooms Put the backup firebird file imagetest.fbk from image_mushrooms folder in your firebird3 folder Restore the fbk file with the gbak command : QuoteD:\Dev\Firebird3>gbak -r imagetest.fbk mushrooms.fdbAdd a line to declare your database in the file databases.conf : Quotemushrooms = D:\dev\Firebird3\mushrooms.fdbInstall the firebird3 service with install_service.bat Create the user for your database : QuoteD:\Dev\Firebird3>isql -user sysdba mushrooms Database: mushrooms, User: SYSDBA SQL> create user SYSDBA password 'mypasswd'; SQL> commit; SQL> quit; D:\Dev\Firebird3>Allow port 3050 connexion for your service in the firewall. For the client Application in lazarus\examples\database\image_mushrooms : Put the fbclient.dll in the application folder Modify the Unit1.Pas : Line 137 : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   FUsingFirebird := true;     Lines 175 to 185   Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    IBConnection1.HostName:='192.168.0.xxx';    IBConnection1.UserName:='SYSDBA'; //for embedded, this should be an existing name    IBConnection1.Password:='mypasswd';    IBConnection1.CharSet:='UTF8';    IBConnection1.Params.Add('PAGE_SIZE=16384'); //enough space for indexes etc//    IBConnection1.DatabaseName:=ExtractFilePath(ParamStr(0)) + FirebirdDB;    IBConnection1.DatabaseName := 'mushrooms';    IBConnection1.Transaction:=SQLTransaction1;    SQLTransaction1.Database:=IBConnection1;    SQLQuery1.DataBase:=IBConnection1; Line 221 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     Caption := Caption + ' (Firebird client/server)';   Friendly, J.P ---------------------------------------------------------------------- dseligo · 2021-03-18 08:29 Quote from: PierceNg on March 17, 2021, 07:00:47 amAFAIK, only Oracle has had a "raw device" driver, by which the Oracle database server manipulates block storage directly without an OS-managed filesystem in between. Some 20+ years ago I worked with HP-UX (I think it was 10.20?) and it had RDBMS that was using separate partition directly. I am pretty sure it wasn't Oracle, I think it was Sybase, but I am not certain. ---------------------------------------------------------------------- DonAlfredo · 2021-03-18 08:42 You might want to consider mORMot also. It has no dependencies while sqlite3 is compiled in (as static lib).