Jump to content

[Solved] How to combine two SQLite Queries into one returned result


Go to solution Solved by TheXman,

Recommended Posts

It seems  I'm always asking SQL questions in December,  must be a tradition. Here is the latest

I have a database that is queried to return infomation about software installers and users. I can achieve what I want by calling two queries using _SQLite_QuerySingleRow, and adding the second query to the first returned array using _ArrayAdd. What I want to do is try to do it with one query.

I don't have any code as I try the queries in SQLitexpert before to obtain the results and layout I require. The one below returns the information I want but the info is duplicated for every name that uses the program. I know this is down to my code

SELECT 
       [main].[platform].[id], 
       [main].[installer].[path], 
       [main].[package].[type], 
       [main].[installer].[switches],        
       [main].[minwinver].[version], 
       [main].[category].[class], 
       [main].[installer].[install_order], 
       [main].[installer].[id] AS [id1], 
       [main].[installer].[display_name], 
       [main].[installer].[display_version], 
       [main].[installer].[display_description], 
       [main].[user].[username]
FROM   [main].[installer]
       INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id]
       INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id]
       INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id]
       INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id]
       INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id]
       INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id]
WHERE   [main].[installer].[display_name] = 'SciTE4AutoIt3'
         AND [main].[installer].[id] = 5;

What I have done is split the sql calls. This one gets the software

SELECT 
       [main].[platform].[id], 
       [main].[installer].[path], 
       [main].[package].[type], 
       [main].[installer].[switches],        
       [main].[minwinver].[version], 
       [main].[category].[class], 
       [main].[installer].[install_order], 
       [main].[installer].[id] AS [id1], 
       [main].[installer].[display_name], 
       [main].[installer].[display_version], 
       [main].[installer].[display_description]
FROM   [main].[installer]
       INNER JOIN [main].[category] ON [main].[category].[id] = [main].[installer].[category_id]
       INNER JOIN [main].[platform] ON [main].[platform].[id] = [main].[installer].[platform_id]
       INNER JOIN [main].[package] ON [main].[package].[id] = [main].[installer].[package_id]
       INNER JOIN [main].[minwinver] ON [main].[minwinver].[id] = [main].[installer].[minwinver_id]
WHERE   [main].[installer].[display_name] = 'SciTE4AutoIt3'
         AND [main].[installer].[id] = 5;

This one gets the users. The users will be added to a combo so it doesn't matter if it's an array, but I would prefer a delimited string return.

SELECT group_concat([main].[user].[username])
       
FROM   [main].[installer]
       INNER JOIN [main].[installer_user] ON [main].[installer].[id] = [main].[installer_user].[installer_id]
       INNER JOIN [main].[user] ON [main].[user].[id] = [main].[installer_user].[user_id]
WHERE  [main].[installer].[id] = 5
ORDER  BY [main].[installer_user].[user_id];

Is it possible to return the result in the second example along with the third in one array using SQLite. I have been googling and trying different things that are obviously incorrect. I have attached the database in question.

Thanks

Installers.dbc

Edited by benners
Link to post
Share on other sites
  • Solution
2 hours ago, benners said:

Is it possible to return the result in the second example along with the third in one array using SQLite.

I don't know what you meant by "in one array using sqlite".  If you meant in a single SQLITE function call, then there are several ways.  Below, is an example of one of the ways you could do it.

2 hours ago, benners said:

It seems  I'm always asking SQL questions in December,  must be a tradition.

Since I'm the one that replied to your previous SQL-related topic, almost a year ago to the day, I'll keep the tradition going by replying to this one too. 😉

 

SELECT plat.id, 
       i.path, 
       pkg.type, 
       i.switches,        
       ver.version, 
       c.class, 
       i.install_order, 
       i.id AS installer_id, 
       i.display_name, 
       i.display_version, 
       i.display_description, 
       group_concat(u.username) AS user_list
FROM   installer i
       INNER JOIN category c        ON c.id = i.category_id
       INNER JOIN installer_user iu ON i.id = iu.installer_id
       INNER JOIN user u            ON u.id = iu.user_id
       INNER JOIN platform plat     ON plat.id = i.platform_id
       INNER JOIN package pkg       ON pkg.id = i.package_id
       INNER JOIN minwinver ver     ON ver.id = i.minwinver_id
WHERE  i.display_name = 'SciTE4AutoIt3' AND i.id = 5
GROUP BY 
       plat.id, 
       i.path, 
       pkg.type, 
       i.switches,        
       ver.version, 
       c.class, 
       i.install_order, 
       i.id, 
       i.display_name, 
       i.display_version, 
       i.display_description;

 

Edited by TheXman
Link to post
Share on other sites

Yeah, Sorry about the "one array". Half the info was left in my head. I am using AutoIT for the program and the _SQLite* functions for DB interaction. When using _SQLite_QuerySingleRow, the function returns an array. I use this array to populate controls in a loop.

Your code above works great, and I will be able to use it when it becomes a function. Seems like this project will never get finished. Thanks for your continued support. See you next year 😆

Edited by benners
spelling
Link to post
Share on other sites
  • benners changed the title to [Solved] How to combine two SQLite Queries into one returned result

Having a graphical view of your tables and their relationships to one another can be very helpful, especially in the design phase but also when creating queries.  Since you're already at least a year into this project, the diagram below may not be as helpful as it could've been back when you were first getting started.  Nevertheless, it may still help you if/when you decide to add new tables, consolidate existing ones, or create additional relationships. 

See you next year.   :bye:

image.png.12562e3a34490becdebb4f4db003236e.png

Link to post
Share on other sites

Yeah that helps. The SQLite Expert program that jchd put me on to has an sql builder section. This has helped me loads, when building and to understand the returned queries. It shows the relationships betwen the table, albeit not as clearly as your picture. It is great for simple queries, which I then convert to AutoIt functions. For more complex ones, it's to the books or google to see if it is possible to do it, which it always has been.

There will be a lot more tables as each category will have one with switches to be used with the installer files. This program only creates and edits the db files, (see pic) I still need to create the program that reads the db.

If I could stop redesigning GUI's, adding functionality, changing font colours and stop trying to make code that's clean and short while still readable (instead of making it work then trimming) I may have finished it sooner. A lot of time is also lost on adding logging (I wrote my own UDF which took a while) and return codes etc, though this is not wasted time.

It seems like whatever I want to do, I have to discover how to do it. Thanks again for them pic, saved and will print off.

benners1.png

Link to post
Share on other sites

I took a closer look at your DB and, in this particular case, determined that you really didn't need all of the additional GROUP BY fields in my previous reply.  I didn't look closely at the database before, just your queries,  The query below yields the same result.

SELECT 
    i.id AS installer_id, 
    i.display_name,
    plat.id AS platform_id, 
    plat.os_arch,
    i.path, 
    pkg.type, 
    i.switches,        
    mwv.version, 
    c.class, 
    i.install_order, 
    i.display_version, 
    i.display_description, 
    group_concat(u.username) AS user_list
FROM
    installer i
    INNER JOIN category c        ON c.id = i.category_id
    INNER JOIN installer_user iu ON iu.installer_id = i.id
    INNER JOIN user u            ON u.id = iu.user_id
    INNER JOIN platform plat     ON plat.id = i.platform_id
    INNER JOIN package pkg       ON pkg.id = i.package_id
    INNER JOIN minwinver mwv     ON mwv.id = i.minwinver_id
WHERE
    i.id = 5 AND i.display_name = 'SciTE4AutoIt3'
GROUP BY i.id;

 

Edited by TheXman
Link to post
Share on other sites

Yeah. I noticed that too. I initially created the function without the Group By, I then got to thinking that you might have added them to ensure to return the results in a specific way. Even though I would expect the results to return in the sequence they are in the SELECT statement, and as I'm returning just one item (id) there shouldn't be any identical data to arrange. I added them back to the function for completeness. I'll update this now.

I have tried to normalise the DB as much as possible and marry the fields and values with AutoIt and it's controls (integers for combos to set current selection). The selection of apps is just a test to enable error checking\testing. There will be exact matches on the display_name as some apps have separate installers for 32 and 64 bits but the paths will be different as I append -x86, -x64, -Dual to the file names (if not already there) to enable easier identification.

Thanks for your diligence. I have a question about the aliases? Do you find it easier with code that shortens the table\columns names like (installer i), is there a performance increase with shorter terminology. For a beginner like myself, I do find it makes it harder to understand as I am not used to thinking that way. I always try to slim my AutoIt stuff down to the minimum fluff so I should also do the same with the SQL. I have updated the function with the edit you suggested,

; #FUNCTION# ====================================================================================================================
; Name ..........: _DBI_MainInstallGetValues
; Description ...:  Retrieves the stored values for the specified application and id
; Syntax ........: _DBI_MainInstallGetValues($s_AppName, $i_AppID[, $h_DB = -1])
; Parameters ....: $s_AppName - a string value. The name of the app to query
;                  $i_AppID   - an integer value. The database ID (used for multiple apps with same name different platform)
;                  $h_DB      - [optional] a handle value. Default is -1. The database to query
; Return values .: Success: A 1D array containing the query values
;                  Failure: string describing in english the error condition for the most recent sqlite3_* API call
; Author ........: Benners
; Modified ......:
; Remarks .......:
; Link ..........: Based on code from TheXman below
; https://www.autoitscript.com/forum/topic/207243-solved-how-to-combine-two-sqlite-queries-into-one-returned-result/?tab=comments#comment-1494299
; ===============================================================================================================================
Func _DBI_MainInstallGetValues($s_AppName, $i_AppID, $h_DB = -1)
    Local $s_SQL = _
            "SELECT " & _
            "[installer].[id], " & _
            "[installer].[path],  " & _
            "[package].[id] AS [pack.id], " & _
            "[installer].[switches], " & _
            "[platform].[id] AS [plat.id], " & _
            "[minwinver].[id] AS [minwin.id], " & _
            "[category].[class], " & _
            "[installer].[install_order], " & _
            "[installer].[display_name], " & _
            "[installer].[display_version], " & _
            "[installer].[display_description], " & _
            "GROUP_CONCAT ([user].[username], '|') AS [user_list] " & _
            "FROM   [installer] " & _
            "INNER JOIN [package] ON [package].[id] = [installer].[package_id] " & _
            "INNER JOIN [platform] ON [platform].[id] = [installer].[platform_id] " & _
            "INNER JOIN [minwinver] ON [minwinver].[id] = [installer].[minwinver_id] " & _
            "INNER JOIN [installer_user] ON [installer].[id] = [installer_user].[installer_id] " & _
            "INNER JOIN [user] ON [user].[id] = [installer_user].[user_id] " & _
            "INNER JOIN [category] ON [category].[id] = [installer].[category_id] " & _
            "WHERE  [installer].[id] = " & $i_AppID & " " & _
            "AND [installer].[display_name] = " & _SQLite_FastEscape($s_AppName) & _
            "GROUP BY " & _
            "[installer].[id];"

    Local $a_Row = 0
    Local $v_Ret = _SQLite_QuerySingleRow($h_DB, $s_SQL, $a_Row)
    If $v_Ret <> $SQLITE_OK Then Return SetError(@error, @extended, _SQLite_ErrMsg())

    ; add the available install order slots to the current one
    $a_Row[$DB_INSTALLORDER] = $a_Row[$DB_INSTALLORDER] & '|' & _DBI_InstallOrderGetAvailable()

    Return $a_Row
EndFunc   ;==>_DBI_MainInstallGetValues

 

Link to post
Share on other sites
32 minutes ago, benners said:

Do you find it easier with code that shortens the table\columns names like (installer i), is there a performance increase with shorter terminology.

I don't think using aliases for table names (or field names) has much of an effect on performance, if any at all.  The use of aliases, in most cases, is just a personal preference (or a standard in some organizations).  For me, it's just a way to cut down on a LOT of unnecessary keystrokes.  😉  In any language, whether spoken or coding, there's usually more than one way to say the same thing.  How you say something depends more on you and your environment, training/education, and comfort level more than anything else.  😀

Edited by TheXman
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By diepfeile
      I'm using the following:
      Autoit 3.3.14.5
      newly installed Beta 3.3.15.5
      SQlite version 3380000 aka 3.38.0
      I put sqlite3.dll and sqlite3_x64.dll in C:\Windows\System32 since many scripts depend on them.


      I extended the output of _SQLite_Startup()
      with:
      ConsoleWrite("@AutoItX64 " & @AutoItX64 & @CRLF) ConsoleWrite("$sDll_Filename " & $sDll_Filename & @CRLF) ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)

      Also using the script from https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm for testing.

       
      >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3.exe "R:\Download\aasdf.au3" @AutoItX64 0 $sDll_Filename sqlite3.dll _SQLite_LibVersion=0 >Running:(3.3.15.5):C:\Program Files (x86)\AutoIt3\Beta\autoit3_x64.exe "R:\Download\aasdf.au3" @AutoItX64 1 $sDll_Filename sqlite3_x64.dll _SQLite_LibVersion=3.38.0


      Why doesn't it work in 32bit, despite me having the 32bit sqlite.dll? Autoit urges running scripts in 32bit mode and Scite starts scripts just in 32bit mode without the flag?
      With #AutoIt3Wrapper_UseX64=Y it just works, both normal Autoit and beta!
      sqlite3.dll sqlite3_x64.dll
    • By t0nZ
      I was searching for a way to highlight zones (regions, provinces, counties, etc) on a map, and I don't need  super precise maps so I wrote this script, based on picking up black and white maps (2 colors BW .png or .gif tested) and filling them with colors, writing down a sqlite database to associate zones with names (and other data as well), and reuse the map and the DB to display data, in my example reading a simple .txt file.
      It's all based on this thread  and this other thread.

      So I have two modes:
      The Map "creation mode" : you provide a map image and you start to pick up colors, set "upper level" region/state, and by clicking on a region you fill it and you name it, and all the data are saved on a sqlite DB (auto-created) when you have the map image and a DB with the correct associations, you can switch the "mode" to "show" (as by .ini file) and the script tries to read a "datafile" showing the zone names listed in datafile. The code:
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=Icone\mapFlooder.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;MAP Flooder ;(C) NSC 2021 #include <GUIConstants.au3> #include <_GOLLOG.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <Misc.au3> #include <GDIPlus.au3> Opt("mousecoordmode", 2) Global $prgname = "MAP Flooder", $ver = "V.0.7", $Buttoncolor = "0xFF00FF", $MPini = @ScriptDir & "\MapFlooder.ini", $btest Global $dbfullpath, $dbtable, $dbFields, $mapfile, $FloodMode, $datafile Global $HDC, $hBrush, $hGraphics, $obj_orig Global $Pic1, $gui, $width, $height, $bColor, $realtimeCoords, $lastclickcoords, $inputSup, $zonecountNum,$labeltest #Region program Gollog(">>>>>> Start MAP Flooder " & $ver) ctrlini() Gui() SQLiteDBcreate() If $FloodMode = "createdb" Then Gollog("CreateDB Mode") DBFlooder() Else Gollog("Show MAP mode") MapShow("show") EndIf Close() #EndRegion program #Region funcS Func Gui() _GDIPlus_Startup() $Pic1 = _GDIPlus_BitmapCreateFromFile($mapfile) $width = _GDIPlus_ImageGetWidth($Pic1) $height = _GDIPlus_ImageGetHeight($Pic1) If $FloodMode = "createdb" Then $gui = GUICreate($prgname & " " & $ver, $width + 150, $height) $labelLoadedMap = GUICtrlCreateLabel("Loaded Map", $width + 10, 5) $labelLoadedMap2 = GUICtrlCreateLabel(_FileToFileName($mapfile), $width + 10, 25) $labeldim = GUICtrlCreateLabel("Width*Height", $width + 10, 45) $labeldim2 = GUICtrlCreateLabel($width & " * " & $height, $width + 10, 65) $lastclickcoordslabel = GUICtrlCreateLabel("Last Click Coords", $width + 10, 100) $lastclickcoords = GUICtrlCreateLabel("xx - xx", $width + 10, 120, 180, 20) $realtimeCoordslabel = GUICtrlCreateLabel("Real Time Coords", $width + 10, 140) $realtimeCoords = GUICtrlCreateLabel("Real Time Coords", $width + 10, 160, 80, 20) $SuPzonelabel = GUICtrlCreateLabel("Supzone (region-state)", $width + 10, 200) $inputSup = GUICtrlCreateInput("sup", $width + 10, 220, 80, 20) $bColor = GUICtrlCreateButton($Buttoncolor, $width + 10, 250, 130, 30) GUICtrlSetBkColor($bColor, $Buttoncolor) $zonecountlabel = GUICtrlCreateLabel("Done Zone Count:", $width + 10, 320, 100, 20) $zonecountNum = GUICtrlCreateLabel("x", $width + 10, 340, 100, 20) $btest = GUICtrlCreateButton("TEST MAP", $width + 10, 380, 130, 30) $labeltest = GUICtrlCreateLabel("", $width + 10, 420, 130, 30) Else $gui = GUICreate($prgname & " " & $ver, $width, $height) EndIf GUISetState() $HDC = _WinAPI_GetDC($gui) $hGraphics = _GDIPlus_GraphicsCreateFromHDC($HDC) _GDIPlus_GraphicsDrawImageRect($hGraphics, $Pic1, 0, 0, $width, $height) EndFunc ;==>Gui Func MapShow($showmode) ; reading a simple text file with zone names, searching for names in DB and fill the map using stored coordinates If $showmode = "show" Then Local $aLines = FileReadToArray($datafile) Local $iLineCount = @extended EndIf If $showmode = "test" Then $iLineCount = 1 If @error Then MsgBox(48, "MapFlooder", "There was an error reading the data file. @error: " & @error) ; Gollog("There was an error reading the data file. @error: " & @error) Close() Else Gollog("start filling zones") _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database with zone definitions Local $hQuery, $aRow For $i = 0 To $iLineCount - 1 If $showmode = "show" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " where zone = '" & $aLines[$i] & "' ORDER BY zone ASC;", $hQuery) ; the query EndIf If $showmode = "test" Then _SQLite_Query(-1, "SELECT * FROM " & $dbtable & " ORDER BY zone ASC;", $hQuery) ; the query EndIf While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $aRow[2]) ; fill color read from DB $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $aRow[3], "int", $aRow[4], "int", 0x000000) If $showmode = "test" Then GUICtrlSetData($labeltest,$aRow[0]) Sleep(200) GUISetState() EndIf WEnd _SQLite_QueryFinalize($hQuery) Next _SQLite_Close() _SQLite_Shutdown() EndIf While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd EndFunc ;==>MapShow Func DBFlooder() $zonecount = 0 While 1 $mp = MouseGetPos() GUICtrlSetData($realtimeCoords, $mp[0] & " - " & $mp[1]) $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop If $msg = $bColor Then colorP() If $msg = $btest Then MapShow("Test") If $mp[0] < $width And $mp[1] < $height And _IsPressed("01") And WinActive($gui) Then $mp = MouseGetPos() GUICtrlSetData($lastclickcoords, $mp[0] & " - " & $mp[1]) $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) Local $Zone = InputBox("Map Floode", "Zone ?") If $Zone = "" Or @error = 1 Then ; when manage wrong click, possibility to repeat ; set 'temp' color to highlight the 'wrong' click $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", 0x4ccfc6) ; fill color wrong $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) DllCall("gdi32.dll", "int", "FloodFill", "int", $HDC, "int", $mp[0], "int", $mp[1], "int", 0x000000) ; restore color $hBrush = DllCall("gdi32.dll", "long", "CreateSolidBrush", "int", $Buttoncolor) ; fill color ok $obj_orig = DllCall("gdi32.dll", "int", "SelectObject", "int", $HDC, "int", $hBrush[0]) Else _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database Local $SupZone = GUICtrlRead($inputSup) Local $data = '"' & $Zone & '","' & $SupZone & '","' & $Buttoncolor & '",' & $mp[0] & "," & $mp[1] _SQLite_Exec(-1, "INSERT INTO " & $dbtable & "(" & $dbFields & ") VALUES (" & $data & ");") If @error = -1 Then GOLLOG("Error insert record") MsgBox(48, "Error", "insert record") EndIf $zonecount += 1 GUICtrlSetData($zonecountNum, $zonecount) _SQLite_Close() _SQLite_Shutdown() EndIf EndIf WEnd EndFunc ;==>DBFlooder Func Close() Gollog("<<<<<<< closing...") _WinAPI_ReleaseDC($gui, $HDC) _GDIPlus_GraphicsDispose($hGraphics) _GDIPlus_Shutdown() Exit EndFunc ;==>Close Func SQLiteDBcreate() ;complete path e filename If Not FileExists($dbfullpath) Then GOLLOG("perform SQLite DB creation") Local $dbfolder = _FileToFilePath($dbfullpath) ;Local $dbfile = _FileToFileName($dbfullpath) If Not FileExists($dbfolder) Then DirCreate($dbfolder) ; =====================>>>>> START SQL DLL _SQLite_Startup() _SQLite_Open($dbfullpath) ; open Database ; creating first table If _SQLite_Exec(-1, "CREATE TABLE " & $dbtable & " (" & $dbFields & ");") = $SQLITE_OK Then GOLLOG("DB table - " & $dbtable & " - creation ok") Else GOLLOG("Error creating DB table : " & @error) EndIf _SQLite_Close() _SQLite_Shutdown() Else Gollog("DB already exist") EndIf EndFunc ;==>SQLiteDBcreate ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFilePath ; Description ...: Returns a folder path from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFilePath($sPath) ; Parameters ....: $sPath - a string value. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFilePath($sPath) Local $sReturn = StringLeft($sPath, StringInStr($sPath, "\", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFilePath ; #FUNCTION# ==================================================================================================================== ; Name ..........: _FileToFileName ; Description ...: Returns a filename from a FQPN (Fully Qualified Path Name) ; Syntax ........: _FileToFileName($sPath[, $bIncludeExtension = True]) ; Parameters ....: $sPath - a string value. ; $bIncludeExtension - [optional] a boolean value. Default is True. ; Return values .: Success - String ; Failure - Empty string as returned from StringLeft() ; Author ........: Sam Coates ; =============================================================================================================================== Func _FileToFileName($sPath, $bIncludeExtension = True) Local $sReturn = StringTrimLeft($sPath, StringInStr($sPath, "\", 0, -1)) If $bIncludeExtension = False Then $sReturn = StringLeft($sReturn, StringInStr($sReturn, ".", 0, -1) - 1) Return ($sReturn) EndFunc ;==>_FileToFileName Func colorP() ; modified for BGR color GOLLOG("Color Picker") Local $color = _ChooseColor(2) If $color = -1 Then GOLLOG("no color selected") Else Local $sCr = Hex($color, 6) Local $RGB_Buttoncolor = '0x' & StringMid($sCr, 1, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 5, 2) GUICtrlSetBkColor($bColor, $RGB_Buttoncolor) ; BGR color $Buttoncolor = '0x' & StringMid($sCr, 5, 2) & StringMid($sCr, 3, 2) & StringMid($sCr, 1, 2) GUICtrlSetData($bColor, $Buttoncolor) GOLLOG("new color " & $Buttoncolor & " selected") EndIf EndFunc ;==>colorP Func ctrlini() ;ini read If FileExists($MPini) Then GOLLOG("found: " & $MPini) $mapfile = IniRead($MPini, "map", "mapfile", "") $datafile = IniRead($MPini, "map", "datafile", "") $dbfullpath = IniRead($MPini, "db", "dbfullpath", "") $dbtable = IniRead($MPini, "db", "dbtable", "") $dbFields = IniRead($MPini, "db", "dbfields", "") $FloodMode = IniRead($MPini, "mode", "mode", "") Else GOLLOG($MPini & " NOT found..") Close() EndIf EndFunc ;==>ctrlini #EndRegion funcS All the needed files plus some example (image maps and DBs)
      Link to all demo files
      To test, copy all in a single folder and adjust the mapflooder.ini, also you can add to you includes the _gollog.au3 (used for log, you can avoid it deleting all Gollog() lines)
       
       
       
       
       
    • By t0nZ
      Code to read a Spiceworks Database and export text data files in INI format.
      Focused on exporting data about PC inventory and useful to migrate to another inventory system.
      Tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)
      It creates one  text file for every single machine.
       
      ;Spiceworks Db Exporter ; ;- NSC - t0nZ 2021 ;code to read a Spiceworks Database and export text data files in INI format. ;focused on exporting data about PC inventory. ;useful to migrate to another inventory system. ;tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019) ;It creates one text files for every single machine. ; --> please adapt paths to your environment. #include <SQLite.au3> #include <String.au3> #include <File.au3> dataINIfromDBspiceworks() #Region spiceworks immport Func dataINIfromDBspiceworks() ConsoleWrite("start import from db Spiceworks" & @CRLF) Local $dbspicepath = "C:\scambio" Local $dbspice = "spiceworks_prod.db" ; =====================>>>>> START SQL DLL _SQLite_Startup() ;======================<<<<<<<<<<<<<<<<<<< _SQLite_Open($dbspicepath & '\' & $dbspice) ;- Local $aCPdata Local $hQuery Local $recordcount = 0 Local $salvarec = 0 ;------------------------------------------------ Local $spiceQuery = "SELECT serial_number," & _ "server_name," & _ "manufacturer," & _ "(model || '-' || raw_model)," & _ "processor_type," & _ "raw_processor_type," & _ "processor_architecture," & _ "raw_processor_type," & _ "number_of_processors," & _ "'speed'," & _ "CAST (memory AS FLOAT) / 1073741824," & _ "current_user," & _ "domain," & _ "network_adapters.dns_domain," & _ "'logon'," & _ "operating_system," & _ "os_architecture," & _ "version," & _ "'lang'," & _ "('C:;Fixed;' || (CAST (disks.size AS FLOAT) / 1048576) || ';' || (CAST (disks.free_space AS FLOAT) / 1048576) )," & _ "devices.ip_address," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "network_adapters.gateway," & _ "(network_adapters.description ||' - '|| network_adapters.name)," & _ "devices.mac_address," & _ "devices.updated_on," & _ "user_tag " & _ "FROM devices " & _ "inner JOIN " & _ "network_adapters ON network_adapters.computer_id = devices.id " & _ "inner JOIN " & _ "disks ON disks.computer_id = devices.id " & _ "WHERE disks.name = 'C:' " & _ "ORDER BY devices.updated_on DESC;" _SQLite_Query(-1, $spiceQuery, $hQuery) While _SQLite_FetchData($hQuery, $aCPdata) = $SQLITE_OK writeINI($aCPdata) $recordcount += 1 If $recordcount = $salvarec + 10 Then $salvarec = $recordcount ConsoleWrite($recordcount & " processed records " & @CRLF) EndIf WEnd ;________________________________________________ _SQLite_Close() _SQLite_Shutdown() EndFunc ;==>dataINIfromDBspiceworks Func writeINI($aCPdata) Local $folderdataINI = "c:\scambio\ini" If Not FileExists($folderdataINI) Then DirCreate($folderdataINI) $aCPdata[28] = StringRegExpReplace($aCPdata[28], "[\D]", "") ; this "2021-04-17 02:34:16" to that "20210417023416 Local $cpini = $folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" If FileExists($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") Then FileDelete($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") ; deleted previous files ! ConsoleWrite("deleted: " & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" & @CRLF) EndIf ;Section Unique :serial, computer name Local $aSectionUnique[2][2] = [["serial", $aCPdata[0]], ["computername", $aCPdata[1]]] IniWriteSection($cpini, "UNIQUE", $aSectionUnique, 0) ;Section Machine :manufacturer,model,cpuname,cpuid,cpuarc,cpuvendor,cpucores,cpuspeed,RAM,disk1capacity,disk1used Local $aSectionMachine[9][2] = [["manufacturer", $aCPdata[2]], ["model", $aCPdata[3]], ["cpuname", $aCPdata[4]], ["cpuid", $aCPdata[5]], ["cpuarc", $aCPdata[6]], ["cpuvendor", $aCPdata[7]], ["cpucores", $aCPdata[8]], ["cpuspeed", $aCPdata[9]], ["ram", $aCPdata[10]]] IniWriteSection($cpini, "MACHINE", $aSectionMachine, 0) ;Section User : username,domain,DNSdomain,logonServer Local $aSectionUser[4][2] = [["username", $aCPdata[11]], ["domain", $aCPdata[12]], ["DNSdomain", $aCPdata[13]], ["logonServer", $aCPdata[14]]] IniWriteSection($cpini, "USER", $aSectionUser, 0) ;Section OS : OSver, OSarch, OSbuild, OSlang Local $aSectionOS[4][2] = [["OSver", $aCPdata[15]], ["OSarch", $aCPdata[16]], ["OSbuild", $aCPdata[17]], ["OSlang", $aCPdata[18]]] IniWriteSection($cpini, "OS", $aSectionOS, 0) ;Section Disks: username,domain,DNSdomain,logonServer Local $aSectionDrives[1][2] = [["drives", $aCPdata[19]]] IniWriteSection($cpini, "DRIVES", $aSectionDrives, 0) ;Section network:localIP1,localIP2,localIP3,localIP4,publicIP,GW,adapter,mac Local $aSectionNetwork[8][2] = [["localIP1", $aCPdata[20]], ["localIP2", $aCPdata[21]], ["localIP3", $aCPdata[22]], ["localIP4", $aCPdata[23]], ["publicIP", $aCPdata[24]], ["GW", $aCPdata[25]], ["adapter", $aCPdata[26]], ["mac", $aCPdata[27]]] IniWriteSection($cpini, "NETWORK", $aSectionNetwork, 0) ;Section PLUS :date, groupid Local $aSectionPlus[2][2] = [["date", $aCPdata[28]], ["groupid", $aCPdata[29]]] IniWriteSection($cpini, "PLUS", $aSectionPlus, 0) EndFunc ;==>writeINI #EndRegion spiceworks import  
      Spiceworks on premise, now pretty abadonware, has a non crypted SQLite DB usually located in:
      c:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db The query was the difficult part (at least for me), and I export in INI format because it was part of my effort to migrate from Spiceworks to a custom made system (Computer Plucker see this post) where I already parse .INI files in a custom MySQL and/or SQLite DB.
       
    • By benners
      I am trying to normalise a database to remove duplicate info. I am using SQLite Expert to design the database and test sql queries.
      The database is going to store information about setup installers, such as paths, installer specific info, users to install for, type and category of installer blah, blah.
      I have attached the database thus far and the tables function are as follows:-
      category - stores text describing the general usage the installer comes under, such as Browser, Compression etc. installer - this is the main table that has relationships with the other tables and stores info about the installer file, install order etc. installer_user - a link table. Stores the user or computer to install the program for or on. package - stores the type of installer, NSIS, Inno  Nullsoft etc. platform - the OS architecture the installer file is compiled for. postinstall - a list of activities to perform when the main install has finished. postinstall_user - a link table. Stores the users\computers that are allowed to run the post install actions user - a list of computers or usernames. I might separate into two tables, undecided yet. Now there wil be one program that deals with the installation side and another that acts as a front end for editing the database suchs as adding new files, removing old files etc.
      The idea with the editing side is to be able to delete an installer from the installer table say with the id of 1 and all other pertinent information in the other tables will also be deleted. The same goes for deleting a user. All the fields relating to that user will be removed.
      I have managed to get that part working for the most part. If I delete either a user or installer, the related info in the installer_user and postinstall tables are removed but since I added the postinstall_user table to link usernames to the postinstall action, this is where I get the foreign key error. If someone can explain why, I am sure it is an obvious reason for someone who knows what they are doing 😄
      Cheers
      Installer - Copy.db
    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
×
×
  • Create New...