Extract, Transform, Load – The Magic Behind HeidiSQL
HeidiSQL is a wildly successful open source database management tool. Apart from being extremely useful in the management of MySQL, SQL Server, PostgreSQL and SQLite databases it is also open source – and that source code is written in Delphi. We’ve taken a brief overview of it before but that time we only scratched the surface of this wonderfully artful example of Delphi programming at its best. The code itself is packed with really great techniques. Let’s take a closer look at it.
Things you will need to compile the HeidiSQL code
- You need RAD Studio Delphi 10.4 or higher. I used Delphi 10.42.
- It’s easier if you have some form of Git source code control client installed. I used my favorite GitHub Desktop client.
- The HeidiSQL source relies on two custom components – the source for them is included in the HeidiSQL source download.
- You should also download and install madExcept.
Installing madExcept
If you haven’t come across madExcept before you’re missing out! It’s a really great tool for intercepting and reporting on program exceptions which occur while your program is running. The website explains in more detail but I thoroughly recommend it.
- Go to http://www.madshi.net/madExceptDescription.htm
- Download and run the installer
- Make sure you check madExcept v5. The installer is a little bit confusing – click on version 5 and it will select it to be installed (the default is not to install version 4 or 5 which has confused me in the past!)
When it’s properly installed there will be an extra menu item added to your RAD Studio tools menu
Getting the HeidiSQL source code
Head on over to the HeidiSQL site and click on the “download source” button. This will take you to the following link: https://github.com/HeidiSQL/HeidiSQL
Installing the required third-party components
HeidiSQL relies on two additional components. Note that both components are very popular so make sure you don’t already have them installed. If you don’t have them installed follow the instructions below. The required items are:
- SynEdit to provide a syntax-highlighted query editor area.
- VirtualTreeView to implement a number of very fast tree and listview style UI views.
Installing SynEdit
Navigate to the .HeidiSQLcomponentssyneditPackagesDelphi10.4SynEdit.groupproj
project and load it. Right click and compile the SynEdit_R
project and then right click and select “install” for the SynEdit_D
design-time package.
Installing VirtualTreeView
Navigate to the .HeidiSQLcomponentsvirtualtreeviewpackagesDelphi10.4VirtualTrees.groupproj
and load it. Right click and compile the VirtualTreesR
runtime package. Now right-click and select “install” for the VirtualTreesD
design-time package.
Compiling required resource (.res) files
There’s a little bit of a gap in the steps I saw about compiling HeidiSQL from source code. There are a few .rc resource files for things like icons and fonts and there didn’t appear to be anywhere saying that they needed to be compiled. I may have missed them (I did try compiling the various group projects) in which case let me know in the comments and I’ll update this post with the correction – but until then do the following:
- Navigate to the
.HeidiSQL
root source folder. In there is a batch file called “build-res.bat” – run that file. It should complete without errors. - Now navigate to the
.HeidiSQLsourcevcl-styles-utils
folder. In there is a file called “CompileResources.bat” - Edit that file with a text editor and remove the paths at the start of the “brcc32.exe” line so is just says “
brcc32.exe AwesomeFont.rc
” - Now save and then run that batch file.
- Edit the second file called “CompileResources_zip.bat” in the same way, removing the path; then save and execute it.
- Both should run the Borland Resource command-line compiler and create the .res files required by the main project.
Opening and compiling the HeidiSQL Delphi source code
Right, we’re ready to open the main HeidiSQL project and should be able to compile it without errors!
Navigate to .HeidiSQLpackagesDelphi10.4
and open the “heidisql.dproj
” project.
It should open without any errors.
If you hit CTRL+F9
to build it will build the source code and create your own personal copy of the HeidiSQL exe which you should find in the .HeidiSQLout
folder
Little gems found in the HeidiSQL source code
The HeidiSQL Delphi code is a cornucopia of interesting methods, little tricks and techniques.
For example, it recently introduced support for custom styles. The styles are embedded in the app using a .res compiled resource. If you navigate to the project’s source (main menu, project, view source). Scroll down and you will see the line: {$R ....resstyles.RES}
It still uses the TStyleManager to safely load those styles but they are loaded from the internal resource. The advantage of this the style files do not have to be shipped with the end-user compiled application but the downside is that the app is limited to the embedded selection. Personally when I do this myself I prefer to look for external style files as a part of the process which allows for people supplying new styles simply by placing them in a designated folder which my code scans and loads into the list. That said, the HeidiSQL code leans toward doing things its own way and trying to be as lean as possible which I think is an admirable approach.
HeidiSQL is polyglottal
HeidiSQL can “speak” to several of the major relational database systems. What’s remarkable is that it does this without using any of the popular database component sets such as FireDAC, AnyDAC, MyDAC and so on.
If we navigate to the dbConnection unit and then the CreateConnection method we will see the following code:
This is the key point where HeidiSQL can so deftly twist its tongue around the various SQL dialects.
From then on HeidiSQL executes SQL queries with very little variation required. It’s a remarkably terse piece of code overlaying a superbly succinct programming style. Developer Ansgar Becker really knows his stuff. I’ve always said that if you read another programmer’s code for long enough you will truly get an idea of the mind which created it.
Other hidden gems
Following through the various elements of the HeidiSQL code reveals many other delights. There is a logging section which records and reports back usage and environment statistics to an external web server via a PHP script. The app can also optionally log the queries executed to a local log file.
There is also a custom updater which can check for and run an automatic update of the executable. Look at the “UpdateCheck” unit for details.
HeidiSQL also has an excellent export facility allowing users to take the results of their SQL queries and export them in all sorts of formats including the slightly esoteric LaTex and WikiMarkup. It’s this kind of ability to run queries to extract the data, almost irrespective of SQL database server technology, show them in a user-friendly view, transform and export them to something else which has very quickly made HeidiSQL the goto DB manager tool for many people.
The HeidiSQL project accepts donations at their office page here: https://www.heidisql.com/donate.php – it’s definitely a deserving Delphi project.
RAD Studio Delphi is the power behind some of the world’s most popular software – why not try it yourself and see what it can do for you?