Jump to content

Recommended Posts

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.

INItoSQL_main.PNG.31a59c7df5a946e2815038de20c3c9d7.PNG

Older Screenshot

Spoiler

5b76e4079bde3_ConvertINI.png.05d3643f86d2dfefbec58a65767186d5.png

INItoSQL DB.zip

INItoSQL DB v1.1.zip  (see Post #3 for update details)

INItoSQL DB v1.4.zip  (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 by TheSaint

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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

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

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 by TheSaint

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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 by TheSaint

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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 by TheSaint

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

@ResNullius -  Thanks for all that. :D

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.


Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

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

An absolutely HUGE improvement. BIG THANKS to ResNullius. :thumbsup:


Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By TheSaint
      Only early days at this point, but I have been pondering such a program for a while.
      As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those.
      What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ.
      When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option.
      A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show.
      The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point.
      At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding.

      Older Screenshots
      Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max.
      As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI.
      My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that.
      Here is another screenshot, of what you see when you click the larger Cover image.

      If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such.
      Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted).
      Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section.
      CalibBrowser.zip  - Upload 4  (previous downloads: 1 + 12 + 5)
      CalibBrowser (new).zip
      My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it.
      Enjoy!
       
    • By Skeletor
      Hi All,
      Here's a really simple question. 
      I ran the code from the helpfile under: _SQLite_Open

      Issue is I end up with an error message: SQLite3.dll Can't be Loaded!
      I placed the *.dll in the include folder, but still nothing. 

      Where must this file be placed.
       
    • By RamDemon
      Hi,
      Did anyone tried to  use Redis  with AutoIT as store DB ?
       Redis <-- more about it
      Mladen
    • By Wingens
      I want to create a program/script wich uses a database to store it's data.
      Now I am puzzeled by wich database server/type I should use.
       
      Can you guys help me?
       
      The program/script I want to create is used for multiple computers, where the insert data en read/modify it afterwords.
      There is going to be adding files to a share and the links as data in de database.
    • By TheSaint
      Here is the bare bones of a UDF I have started work on.
      Mostly just a proof of concept at this stage, and still need to add some functions and dress the UDF up a bit ... to look like a UDF ... though it has my own distinct styling, especially as I have never really developed a UDF before now .... used plenty and modified plenty though. I've even invented my own UDF variable naming convention, which I am sure some of you will be aghast at. I work with what feels best for me, but others are free to adapt if they wish.
      The idea is to emulate the simplicity of INI files, but gain the benefits of SQL.
      Two scripts are provided.
      (1) The UDF, a work in progress - SimpleSQL_UDF.au3
      (2) An example or testing script - UDF_Test.au3
      Another first for me, is creating a 2D array from scratch, never done that before, that I can recall ... never had a need, and even for 1 dimension arrays, for a long time now, I have just used _StringSplit to create them. So I needed a bit of a refresher course, which my good buddy @TheDcoder assisted me with ... not without some angst I might add. LOL
      SimpleSQL_UDF.zip  (12 downloads previously)
      (I have now completed all the functions I intended to. My next update will be a big improvement, bringing things more inline with my latest INItoSQL DB program changes.)
      Program requires the sqlite3.dll, not included, but easily enough obtained.
      Hopefully the usage is self-evident ... just change the Job number variable in the UDF_Test.au3 file to check the existing functions out.
      Enjoy!
      P.S. This is also related to a new program I have just finished and uploaded - INItoSQL DB
×
×
  • Create New...