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