Day 027 - Diving deeper into HyperFileSQL

Owned by David Harms
Last updated: Apr 26, 2013
3 min read

Lesson 4.4 covers SQL logging for HyperFileSQL databases. 

As is usual in SQL databases, logging makes the following features possible:

  • restoring after a crash
  • auditing
  • replication

Logging is done on a per-file basis - open the Analysis, choose a file's Description (properties) and click on the Various tab. You can write to a log file, or you can write to a log file and track the history of data access operations. You also have the option of just tracking data access but this is not recommended, and it really doesn't make sense to track who accessed what data if you don't also have the option of restoring sensitive data. 

Although you enable logging for each file individually, there is a central data repository in the analysis which keeps all of the events in chronological order. 

Transactions

Transactions are only briefly mentioned, along with the HTransactionStart and HTransactionEnd function. If a transaction is interrupted by a crash, the database is corrupt and has to be restored. (Which seems a bit odd to me - isn't the whole point of transactions that they either happen or they don't?)

I did a quick search for comments/concerns about HyperFileSQL's reliability and found a lot of positive reviews. 

Encryption

HyperFileSQL offers three kinds of encryption:

  • 128 bit (fastest)
  • RC5, 128 bits, 12 rounds
  • RC5, 128 bits, 16 rounds

Passwords are per-data file. You can ask for the password, embed it in the program code, or use whatever other mechanism you like. 

Replication

Most SQL databases offer some kind of replication. With WinDev you can replicate one HyperFileSQL database to another using a log-based approach, or you can replicate between different kinds of databases. In either case there is one master database; the others are slaves). If you want to replicate between non-HyperFileSQL databases you need to have a DateTime field in each file.

Triggers

Triggers are only mentioned briefly, but the tutorial seems to be saying that for HyperFileSQL you can write your triggers in the W language. 

External databases

The last lesson in this section explains how to programmatically open a connection to a database with HOpenConnection, and then associate the connection with a file using HChangeConnection. You can have as many connections as you need. 

An argument for SQL

Lesson 5.1 begins with an overview of client/server applications and SQL. The diagram shows something that caught my eye: a Manta Server:

There's no mention of Manta in the tutorial, but from a web search I discovered that's the name of the HyperFileSQL service on both Linux and Windows. 

I went ahead with the tutorial's instructions and installed a local HyperFileSQL (I think I'll call it HFS from here on) server. My C drive is filling up, so I had to take some care in changing directories as there are three sets involved; one for the server, one for the data files, and one for the Control Center, which looks like this:

You can migrate a classic HFS database to the server using the data model editor or the HFS Control Center. 

I used the WinDev IDE-based approach as shown in the tutorial. The wizard was easy to follow and in short order I'd imported my database definition and all the data into the server. 

I went back to the Control Center, logged in to the database, and tried to view the database structure. I could see the database, but I couldn't see any way to view/modify the database structure. Perhaps that will become clear to me tomorrow.