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