The Horror of finding the right database! Part 3

If you haven't read Part 1 or Part 2...

Here is Part 1!
Here is Part 2!

There is a Window Service running that knows "everything"?

What do we need on top?

Let's rethink our idea of moving from flat files to a database. 

- Fixed Record length
- Variable record length too complicated
- SQL overhead is significant – in most cases, a simple CRUD database suffices.
- Do we really need advanced functions, views, and JOINs?
- What about transactions?

These are all good questions, but there's another crucial point that could have a significant impact and benefit!

What if we had a Windows Service that could perform advanced operations alongside communication between our client PCs? What would we do?

One part of a running System Service is: This service could install and register components without requiring administrative rights from the user. For instance, it could use regsvr32 to register COM DLLs. These COM DLLs could be compiled with the latest Delphi version and for 64-bit architecture.

This approach kills two birds with one stone.

(If you don't know me, I'm stuck on D2007 with my 12M  LOC Main Application)

I don't plan to use other compilers, but I could, for example, use C# to create a COM DLL.

Back to the database... And the question from Part 2:

"Do we still need a dedicated database server, or could we return to using a flat-file database?"

Some details for the current status:

I've been using BTREE-ISAM from TurboPower since the Turbo Pascal days, later adapting it to Delphi 3. It has worked fine without any errors since ~1998. My Application works fine with AddRec, PutRec, DelRec, and the keys to find the reference. A JOIN or anything else was never needed. This "Database" also supports variable record lengths, though I've never used that feature. But times have changed and a real database could help clean up some old methods. To be fair: Only address data is stored in this database. I have also developed a BTREE-like flat-file database that could handle any kind of data and store it compressed in one file. I use this to store large copies of text files. (No need to search). I also have a cluster-based flat file that stores compressed data but with a fixed length in a cluster. (4KB Cluster could store compressed data) benefit fixed seek positions - very fast).

I want to use a real database - and I don't talk about NOSQL - we have to use the SQL language to do our CRUD work. Perhaps we could benefit from transactions and joins and other fancy stuff. But we have the overhead of Data->SQL->DBServer and also DBServer-Text-Data. Hundreds of fields must be mapped to and from a query. Why? I have a record in memory, this must be stored and also be loaded. Why convert it to text back and forth? I don't think - no I'm sure - this could not be faster with a DB-Server of any kind. 

Nothing beats a Blockwrite.

If we have to transfer the Data over TCP/IP this is another sorry, but a binary compressed stream is also better than a huge blob of SQL Insert statements.

Perhaps the best solution for me – your application might have different requirements – is an elegant interface capable of handling any kind of record.

Since Delphi 2007 lacks comprehensive RTTI, we'll handle the RTTI operations in an XE DLL. With this information, we could create a database structure on the fly without using any SQL.

On top, we must define the indexes and for every record, we have to register a function that can get the necessary information from the record to build the keys. With all this, we could also store only these fields from a record that has a value. So we treat every field as nullable. With a really simple comparison, we could ignore all fields that have no value and just don't store or even send the data over TCP/IP. Although I haven't implemented this yet, it seems promising – at the very least faster than a SQL INSERT with numerous unused fields. (I'm talking about records that have other embedded records and a field count of 400 or more.)

I think I could write my own database server to do this job; in this case, I have no external dependencies.

Is this a good idea?

Perhaps there'll be a Part 4 – or another story about my failed attempt at developing a database. The future will prove this.

Stay tuned...

The Horror of finding the right database! Part 2

If you haven't read the first part: Here is Part 1!

Btw: This topic is not new - Here are two blog posts from 2020:

• Is a database just a data storage?
• The database on network file problem

Perhaps we have special needs, but I don't think so.

The simplest database would certainly be SQLite in Journal mode. Although it should theoretically work with locking and file modes, there are many warnings against using SQLite or other file-based databases in a multi-user network environment.

So, how do we work with a server without having a real server?

Our application could communicate with other applications on the network and coordinate through a communication channel to determine which PC has access to the database at any given time. This would work, but it would be very inefficient. Each PC would need to request access, open the database, perform its task, and then close it before releasing access for the next PC.

As a proof of concept, we could write our own file to the hard drive, but this might lead to the same problems. Additionally, antivirus software tends to dislike frequent file writes in short intervals.

Encryption is also a key requirement for our database. Unfortunately, the SQLite’s encryption feature has become a real hurdle, with a $2,000/year licensing fee. So, you're either stuck with an old version or forced to pay the fee, which is not a viable option for small applications.

Another option would be Firebird. A Firebird database can be accessed either via an embedded driver or a server. The first PC that starts could access the database directly or launch the server instance locally. When a second PC starts, it could "ask" the other PCs on the network if a server instance is already running and then use that server.

We could attempt to establish a connection from each PC to the server instance on the other PCs, read a table where the active server logs its activity, and if no instance is active, we can start the server and update the table accordingly.

In theory, this should work! If the respective PC is already turned off, we would just need to wait for the connection to time out, and then start the server instance ourselves - if we are faster than another PC on the network. Another PC might have already taken over the job.

Without having programmed this functionality yet, I already foresee numerous problems with this system.

No matter how we approach it, we need communication between the PCs. Of course, we can build these functions into our application. With each application shutdown, another PC might need to "take over." For example, if I shut down my application, the communication would be interrupted, and the application on another PC would need to take control, coordinating access between PC 2 and PC 3. At this point, things start to get complicated.

Moreover, the application could crash, or the user could simply shut down the PC as mentioned above.

Starting or stopping a server instance, or applying updates, may also require administrator rights, and you wouldn’t want to constantly prompt the user for those permissions.

The conclusion of these considerations is that a Windows System Service must be installed on each PC to handle these tasks. This way, our application can simply connect to the Service-Application on the correct port, which will act as a mediator.

First, we need to develop a Windows System Service capable of maintaining the information on which PC the current server is running and how it can be reached. The service handles this task in its own thread and can respond very quickly if needed. For example, each system service receives a notification when Windows is shutting down! The service can immediately notify the other system services and, based on a priority list, instruct the next PC to start a server instance.

Within a few milliseconds, a new server will be ready to take over the work.

To establish our connection to the database, we simply need to send a query to our system service, which will return the IP and port for the server instance. Since our system service only provides the correct "login details," all database drivers work normally.

However, communication between FireDAC (for example) and the database happens without our intervention. So, if we want to execute a longer database operation, we need to inform the system service currently holding the server instance and ensure that the PC isn’t shut down until the operation is complete.

Even though communication with the database already happens via TCP/IP, FireDAC handles this communication in the background. We definitely don’t want to analyze that traffic.

A query wrapper or a wrapper for the FDConnection could handle this task.

If we’re already "announcing" the database access via TCP/IP, do we still need a real server, or can we switch back to a flat-file database?

Stay tuned for what's coming next.

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...

How to execute a method with elevated rights?

 Well, let's start with the question: What are elevated rights?

We focus on Windows only for this. Elevated rights are otherwise called Windows UAC control, which is formerly known as "Admin Rights."

Sometimes you will need this to install something in Windows or get permission to change the Registry in some parts.

So how can your application get these rights? 

You can (Shell)execute your application again with "RunAs" to get these rights from the user. But in this case, you must overcome your complete startup procedure, and perhaps a window will be shown. 

At this point it is much easier to put your code into a DLL - the only thing that you need on top of that is a small application to load your DLL.

You can write this in Delphi - piece of cake. 

I like to include everything my application needs in resource so you just need to copy my exe and dll's into on your drive and execute it.

Bad news: Your virus scanners really don't like applications embedded into other applications and I would like to keep the size to a minimum. 

So my Idea was a really small application written in ASM.

How do we write applications this small these days? We ask Chat-GPT. Easy task isn't it?
The last time I wrote ASM by hand it was for the Z80 CPU... So my knowledge is a bit rusty on how to assemble and link with modern tools. And to be honest - this was an even bigger challenge than expected.

To make this story short - with a friend (he wrote his own assembler and linker) it took 2h to find the right tool... FASM every other Assembler / Links did not do the job. 

The resulting application has 2048 bytes - as a packed resource I'm down to 384 bytes... This application can load one of my DLL's execute a procedure and the Exit-Code is the result.

If you have an interface everything looks like a nail!

Of course: If you have a hammer, everything looks like a nail...

So the question is: What kind of implementation should I use?

Let's collect some possibilities.

1. Interface
2. Class
3. Record
4. Methods in a Unit

OK - this makes no sense... Let me try this again from another point of view. Can you guess where this will lead us again?

You have a Form and you don't want to put your business code into this form. Ring a bell?

Where to store the code? Of course in a different unit, but that is not the point.

Every form needs data, so where do we get the data from? 

We can get it from a database, but in my opinion, this is the worst way to use data-sensitive components. 

We can create a unit with procedures and functions to pass the data or get the data. or we can create a class that can hold the data as well as read and store it. 

But wasn't there a rule that a class should only do one task?

We should have separate classes or methods for saving and loading. 

So first we need a storage structure that can hold the data we need for our input on our form.

We can go the old way with new and dispose or getmem and freemen. 

We can use a record or a class. And if we need a list, we can use a TList<T> or a TArray<T>. or a linked list as in the old days? 

Are you too young to know what a linked list is? Well then, here is a short explanation:

Assume you have a record and a Pointer

PFoo = ^TFoo;
TFoo = Record

  Name : ShortString;

  Next : PFoo;

end;

Var

  Root,

  Akt,

  NewFoo : PFoo;

begin

   Root := NIL;

   New(NewFoo);

   Root := NewFoo;

   Root.Next := NIL;

   New(NewFoo);

   Root.Next := NewFoo;

   NewFoo.Next := NIL

   Akt := Roo;

   While Akt <> NIL do

     begin

      Writeln(Akt^.Name);

       Akt := Akt^.Next;

     end;

end;

 

You get the memory with New and you can easily iterate through the list. You have to free the memory with dispose.

Is it good or bad?

On the positive side: You do not need a contiguous memory block to store the data. Unfortunately, you do not have direct access to the nth element.

You can of course create the record with New and store the pointer in an array. This means you can access the nth element at any time. You only need a memoryblock for the size of n-pointer, but even in this case you have to release all elements with dispose. Let's ignore the smart records at this point.

A TList<TFoo> and a TArray<TFoo> again must fit a linear block of memory.

You can use a TObjectList with owns value to great your classes get destroyed.

From all this I always come back to: TArray<IFoo>.

Why no link to the Implementation just to the Interface declaration and no problems with memory.

So happy coding...