INItoSQL DB

[TheSaint]

Here is a simple program that some of you might appreciate.

It is a more full fleshed out version of something I worked on a while back as a proof of concept.

I will just quote the information found in the Program Information dialog.

Quote

This program will convert an INI file to an SQL DB file.
Add the source file to convert, either by Drag & Drop onto the Source input field or by browsing. It can also be provided via the Command-Line at program start.
By default, values such as Table Name and the new filename, are taken from existing filename, but each can be specified as something else by the user. The destination folder by default, is the source one, but it too can be set to something else, using one of the provided options.
To remove a user added Table Name from the list of choices, just re-select and click the ADD button for a removal prompt.
Basically, INI entries are saved to Section, Key and Value fields, belonging to the chosen Table Name.
The value of doing this conversion from INI, should be self evident to those familiar with the capabilities of SQL and limitations of INI. In short though, data from the INI file can be interrogated or manipulated in a lot more ways, some quite complex.
While INI files are simplistic, and the conversion is a simple straight one, you can add more than one INI file to a single SQL DB one, providing you are aware of any possible ramifications if not using a different Table Name to those used previously. While an INI file doesn't allow duplicate Keys for a Section, SQL does not have that limitation. So be careful if you want to maintain uniqueness.

It can be of great benefit to turn multiple INI files into one SQL database file, with each being saved to a different Table name.

Older Screenshot

Spoiler

INItoSQL DB.zip  1.27 MB  (205 downloads)

INItoSQL DB v1.1.zip  1.27 MB  (181 downloads)   (see Post #3 for update details)

INItoSQL DB v1.4.zip  1.27 MB  (166 downloads)   (see Post #4 for update details)

INItoSQL DB v1.6.zip  (see Post #7 for update details) BIG THANKS to ResNullius for huge speed improvement etc.

Program requires the sqlite3.dll, not included, but easily enough obtained.

I have also included the beginnings of a new UDF (SimpleSQL_UDF) I am working, which you can use with the included 'Check conversion.au3' file to check a resulting conversion ... just modify the values for a few variables to suit your situation.

I've not yet gotten around to coding a testing/checking results script, so I recommend the following open source program, which I have been using for some time. It was here, but is now here at GitHub - DB Browser for SQLite

Edited September 11, 2023 by TheSaint

[TheSaint]

Please be advised, that my program only does very basic things and takes quite a simplistic approach. It does not setup any cross-referencing or indexing.

To be sure, there is plenty of room for improvement, especially as my background is primarily INI files and Spreadsheets (Excel).

One of the great benefits from sharing, is that you sometimes get input from others, even if it is only in private, and I always appreciate the time and effort taken to do so, so thank you @jchd for doing so in this instance ... quite the expert in SQL so I am told.

It is clear to me, that I am only at the beginning of what is likely to be a long journey with SQL, especially as its possibilities impress me greatly.

I also like to share my learning with others, hence the scripts and programs I provide ... hopefully they assist others on occasion ... even if it's just what not to do.

P.S. If some of you have been observant enough, you will have noticed I am involved in a few SQL based projects right now ( CalibBrowser and most recently SimpleSQL_UDF ), and I am basically teaching myself as I go. For me, that means simplify in the first instance, paring things back to a level that I can understand, even if that means oversimplified and not fully correct. It is a beginning that I will continue to work upon as my experience and understanding grows. I first talked about eventually trying out SQL a few years ago, and earlier this year, I finally made the jump. I once went through a similar process with XML, but I never really took to it ... I learnt enough to utilize it in some projects of my own, plus work with XML files in general ... I never found it intuitive enough to ever really like it though ... and I always felt like I was reinventing the wheel almost, every time I used it ... no doubt due to some limitation of my own mind and how I recall things ... certainly if not used constantly enough. I already like SQL far more than I ever liked XML.

Edited August 18, 2018 by TheSaint

[TheSaint]

INItoSQL DB has been updated to v1.1.

I have now had some time to experiment a bit more and work on my thoughts and some inspiration from jchd ... though the interpretation is all my own.

(v1.1)
Added a checkbox to keep the Table name the same if selected or clear it after a Conversion completes ... ready for the next source INI file to be added.
Added more Tables and assigned IDs to all but the Values taken from an INI file, so some cross-referencing and indexing now exists.

NOTES - I recommend using a program like  DB Browser for SQLite to check on the results. At this point, I am not entirely sure about a few things, like whether I should have made each ID an integer rather than string, and whether I should also have assigned an ID to each Value.

Instead of my program just reading an INI file and writing to the specified SQL DB file, it now also reads from the SQL DB file, and modifies input accordingly. These new changes are particularly focused on when adding the entries from subsequent INI files to just the one (same) destination SQL DB file.

I don't doubt for a moment, that there is a better way to do what I have done with my update, but once again, I have elected for simplicity.

Current Structure

Unless you choose not to (etc), each INI file is a uniquely named Table. Table Name is based on filename, minus extension.

A Table of Table Names is now created. It contains two fields - an ID for each Table and its Name.

A Table of Section Names (taken from the INI section names) is now created. It contains three fields - an ID for each Section, the ID of the Table Name, and a Name for each Section.

A Table of Key Names (taken from the INI key names for each section) is now created. It contains four fields - an ID for each Key, the ID for each Section, the ID for the Table Name, and a Name for each Key.

The same Table as created for the first version of the program, is still created, but three additional fields have been added - Key ID, Section ID and Table ID. So in short, you now have six fields all up - Key ID, Section ID, Table ID, Section Name, Key Name, Key Value.

So it is no longer a simple affair, but eminently more powerful and flexible.

P.S. For my testing, I had the Destination combo set to 'Last'. That meant every INI file I converted got added to the one DB file. Keep checkbox, deselected.

Edited August 19, 2018 by TheSaint

[TheSaint]

INItoSQL DB updated to v1.4. See first post for the download and a new screenshot.

Made some significant changes to speed conversion up, which it needed due to some flawed thinking on my part.

(v1.2)
Added a 'Cancel' during conversion option.
Some detail is now shown on the splash during conversion, to give an indication of progress.
That conversion splash can now be 'shown' or 'hidden' during the process.
These 'Cancel' and 'Hide' checkboxes are checked for after every 2 sections are processed.
BUGFIX for unnecessary SQLite execute lines.
Basic details of each job, is now saved to a Log.txt file.

(v1.3)
SPEEDFIX for unnecessary repetitive ID checking.
Source file is now copied to program folder and renamed Source.ini, so that sections can be deleted after processing, to speed up reading.

(v1.4)
Further testing revealed I could remove (revert) the copy source (and delete sections) method, as no apparent advantage.

NOTE - A large INI file can take quite a while to process/convert .... dependent on your PC of course ... mine are quite slow.

NOTES - Yesterday, I attempted to convert a 306 kb INI file, that contained almost 1800 Sections and 6 keys per section. It was taking way too long, so after a few hours I cancelled the process, and set about investigating things. I discovered a few unnecessary things were happening. Today, I was able to process a slightly larger INI file of 319 Kb's and 1833 Sections, in around 50 minutes. The resulting DB file was 900 kb. Most of you should see a much faster conversion rate, and I expect you would likely be using smaller INI files as well.

Edited August 31, 2018 by TheSaint

[ResNullius]

On 8/31/2018 at 2:30 AM, TheSaint said:

NOTE - A large INI file can take quite a while to process/convert .... dependent on your PC of course ... mine are quite slow.

NOTES - Yesterday, I attempted to convert a 306 kb INI file, that contained almost 1800 Sections and 6 keys per section. It was taking way too long, so after a few hours I cancelled the process, and set about investigating things. I discovered a few unnecessary things were happening. Today, I was able to process a slightly larger INI file of 319 Kb's and 1833 Sections, in around 50 minutes. The resulting DB file was 900 kb. Most of you should see a much faster conversion rate, and I expect you would likely be using smaller INI files as well.

Howdy Saint,

I had occasion to test your fine project and I'd like to share a two-line speed up tip that took me a while to discover when I first started with SQLite.
I tested your script with the additions on the largest ini file I could find on my system and my log reads as follows:

Quote

2018-09-01 07:39:03 : Finished.
2018-09-01 07:39:03 : Size = 204 kb's
2018-09-01 07:39:03 : Destination = D:\Users\Evan\Downloads\{Autoit}\INItoSQL DB v1.4\SynUnst.db
2018-09-01 07:39:03 : Keys = 1311
2018-09-01 07:38:47 : Sections = 401
2018-09-01 07:38:47 : Size = 1824 kb's
2018-09-01 07:38:47 : Source = D:\Users\Evan\Downloads\{Rescue}\image-health_17.01.05\Image-Health\HP-8560w-Win10Drivers 2018-04-27\synpd.inf_amd64_1daab254cef532b4\SynUnst.ini

Sorry I don't have a "before" log, I got tired of waiting....

The only problem is that it's so fast  your Splash progress messages don't show up properly

The "secret"?
Add this line after you open your db (line 281 in v 1.4 of your code)

$DBhandle = _SQLite_Open($DBfile)   ; original code opening db
_SQLite_Exec($DBhandle, "BEGIN;")   ; <<<< Add this line

And add the following line before you close your database (line 360 of your version 1.4 code)

_SQLite_Exec($DBhandle, "COMMIT;")  ; <<< Add this line
_SQLite_Close($DBhandle)            ; original code closing db

 

PS: I also had to add a check on reading empty ini sections to prevent the script from crashing (line 327 of your v 1.4 code)

$keys = IniReadSection($srcfle, $section)   ; original code
If Not (IsArray($keys)) Then ContinueLoop   ; <<< Insert this line to prevent crashing on reading an empty section
$entries = $keys[0][0]                      ; original code

Hope these help,

Cheers.

[TheSaint]

@ResNullius -  Thanks for all that.

Will certainly check it all out, and yes I am guilty of not always doing enough error checking, and that one slipped through for sure.

I finished another update late yesterday, but not uploaded yet. Essentially it just adds a resume capability for last conversion, if you cancelled it before completion ... or maybe added some new sections to the end of the INI file. At this point, it only works with last conversion if source file matches.

Time for bed shortly, but tomorrow I will investigate and no doubt implement your suggestions. Not aware of BEGIN or COMMIT, so should read up on those i am guessing.

[TheSaint]

INItoSQL DB updated to v1.6. See first post for the download.

BIG THANKS to ResNullius for the huge speed improvement etc. See Post #5 above.

(v1.5)
A Resume ability has been added, to continue on with the last used INI file, from where a conversion was cancelled.
NOTE - While Resume is not a checking to update process, it can convert newly added entries from the end of an INI file, and so achieve a kind of update. This only applies to the last converted INI, not any prior to that.

(v1.6)
Bugfixes. Code improvement (huge speed gain) and Bugfix as suggested by ResNullius.

 

Before Speed Improvement  (50 to 51 minutes)

Quote

2018-08-31 15:50:20 : Finished.
2018-08-31 15:50:20 : Size = 900 kb's
2018-08-31 15:50:20 : Destination = C:\Program Files\Ebooks\AZWPlug\Ebooks.db
2018-08-31 15:50:20 : Keys = 10971
2018-08-31 14:59:34 : Sections = 1833
2018-08-31 14:59:34 : Size = 319 kb's
2018-08-31 14:59:34 : Source = C:\Program Files\Ebooks\AZWPlug\Ebooks.ini

After Speed Improvement  (1 to 2 minutes)

Quote

2018-09-03 22:19:42 : Finished.
2018-09-03 22:19:42 : Size = 900 kb's
2018-09-03 22:19:42 : Destination = C:\Program Files\Ebooks\AZWPlug\Ebooks.db
2018-09-03 22:19:42 : Keys added = 10971
2018-09-03 22:18:42 : Sections = 1833
2018-09-03 22:18:42 : Start Section = 1
2018-09-03 22:18:42 : Resume skipped.
2018-09-03 22:18:33 : Size = 319 kb's
2018-09-03 22:18:33 : Source = C:\Program Files\Ebooks\AZWPlug\Ebooks.ini

So 50 to 51 minutes before the changes suggested by ResNullius, and 1 to 2 minutes after having implemented them.

An absolutely HUGE improvement. BIG THANKS to ResNullius.