The Horror of finding the right database! Part 1

How often do you use a database?

• With every application?
• With some applications?
• Never?

A friend recently told me that he believes that if we switch our application from flat files to a database, all access would be much faster.

Of course, this is not the case.

We have large structures with many fields in records. Apart from complicated searches, there is, of course, nothing faster than writing or reading this data directly to disk with a blockwrite or reading it with a blockread command. A stream has almost the same speed apart from a few calls; both methods result in the same Windows API function.

With the overhead of passing the fields to a query so that it then generates long strings from them—which then have to be interpreted again by the database so that the data is finally stored on the hard disk with countless calls—is, of course, much slower.

To make it clear, databases have many functions that are hard to do with a flat file - especially if the file does not fit into memory.

But there is more to consider.

For a single user without any shared access, a single-file database like SQLite fits most of the needs.

If you need more, you could use MariaDB, Firebird, or any other database.

So let's start by selecting the right database! But first, what are our needs?

• Free for use in a commercial application (no license fees)
• Easy installation
• Multi-users over the network
• No dedicated server (we only have a main PC and a notebook in most cases)
• Encryption
• Perhaps a NAS for data storage

So, without a dedicated server, where are our data and our server instance installed? Perhaps on the main PC or on the NAS.

If the main PC is off, the notebook must fire up a server instance to load the data from the NAS (we assume the NAS is too basic to run the server).

What if the main PC gets started? Perhaps we inform the notebook to stop the server and start a server instance on the main PC—because it is faster.

How do we inform a PC in the network to stop the local server and use another one?

Every problem starts with not having a dedicated server! All file-based databases are not really "good" in a multiuser environment.

And now?

We need a CRUD database. Normally, there is no need for joins or any master-detail tables, but it would be nice to have. Do we need to access the database over SQL? Absolutely not—in most cases, we need an object-based database. So more like a NoSQL database?

How do we connect to a database? Of course, over TCP/IP.

Perhaps we could use a database, but—as always—nothing fits our needs perfectly.

Stay tuned for our ideas.

Spoiler: TCP/IP is easy to use...

Setup your Bitbucket replacement for your Mercurial repository

A long time ago the German DelphiPraxis community convinced me to abandon my previous strategy of saving my source code via zipping only and use a source code management system.

So I googled a little bit and found SVN.

After some days of getting used to it - I proudly announced: "I have switched to SVN"! And with that, the shit storm has begun.

Why aren't you using a distributed revision-control-system? Don't use this old SVN system! Uses Mercurial or Git!

So I took the first "Mercurial" and with a little help and a talk from MVP Uwe Raabe on one of the Delphi-Tage-Event,  I had to realize that this system "just" works very well and offers all the advantages I missed in SVN. ( Sorry Craig )

Pushing everything for backup to a network-driver or a USB-Stick works perfectly. So no need for the old SVN Server installation on my local server, because mercurial is file-based. But after some time I had to share my source code with other developers and here comes BitBucket into play.

The use of BitBucket was as easy as Mercurial itself. 

I've never used Git, but I was told that it was just like Mercurial. Unfortunately, this is not the case. Even the first time I used git, there were big differences and I immediately hated git. But this was not a big deal, because I could use Mercurial for my local repositories and use Git for sharing where necessary.

I know many more developers using Git that Mercurial, but I never got used to the remote and local heads for different users and the parallel branches are show differently comparing to Mercurial.

After Bitbucket broke away from Mercurial repositories and hg is no longer supported, I had to find an alternative.  After some testing, I was able to run the SCMManager on a virtual Ubuntu installation. With the online available tutorials, the installation was not complicated. Unfortunately, SSL did not work out of the box.

The SCMManager is a Java-Client and this is not so easy with the certbot for Let's Encrypt. As far as I know, you have to convert the certificates and install them into the java key store. I like the cronjob based renewal, so hand converting was not my goal.

I found a little hint, that the Nginx server could be used as a proxy server to do the https/SSL connection and leave the java installation without any changes.

Installing software on Linux with this apt-get stuff is really easy and I wish this kind of installation would also possible on Windows. One drawback often is the location or the necessary changes to config files by hand. In this case, I had to type the config file from scratch.

By adding a new A record to the nameserver I was able to run the certbot with the "--nginx" param script who not only installed the certificate but also added the necessary parameters to my handwritten config file. Well done - this kind of "service" is really nice...

With some tweaking and some reload/retries, the server was reachable over https!

Small repositories worked immediately, but large files could not be transferred and caused an hg error: "request entity too large". The reason for this problem could only be the installed proxy server because everything worked before. But also this problem could be solved by a little googling. 

So now I have my own Mercurial server. If you want to know more about this, please write this in the comments, then I will try to collect my google knowledge into another post.