Jump to content

one sqlite database to multiple autoit users


Recommended Posts

I was reading this "How to corrupt your database" in the sqlite help file and my condern right now is a single sqlite database being used by multiple autoit at the same time. This means that different users in the network are opening/writing/saving to a single shared sqlite database. Is this possible? If not then what are the best approach for this to prevent corruption? And the file lock thing mentioned in the help file.. will this prevent multiple users from accessing the file at the same time?

Thanks in advance.

Link to post
Share on other sites

Hey there.

Yes, network shares are way dangerous because network file locks don't work the same way as local file system locks.

You can use a "semaphore" system, I know I've seen it in the forum in the past.

Ah, here it is: www.autoitscript.com/forum/topic/117033-file-locking-with-cooperative-semaphores/

Basically it creates a folder via command line, in the same directory structure as the database file.

Whenever you want to write to the database, you need to check for the existance of that semaphore folder. If it's there, some other user is writing to the database, thus locking it.

When you stop writing to the databse, remove the folder, thus removing the lock.

You also need to implement a timeout feature that will bypass a lock when it's there for too long (these are called dead locks, I guess).

footswitch

Link to post
Share on other sites

I know this isn't what you asked, but let me say that SQLite just wasn't made for concurrent access over a network share.

My suggestion is, if this isn't a portable software, neither is it stored in one of those standalone network drives, and if you have administrative privileges over the server, you may as well install MySQL or MS SQL Express, which handle "locks" appropriately.

There are UDFs for those as well; though not officially supported, the SQL one for instance works really well for me.

Good luck either way :)

Link to post
Share on other sites

Hi alex,

Please read again what I wrote about this in your previous thread.

Now you have basically two ways to handle this: either drop SQLite and use a client/server engine instead or workaround the unreliable file locking issue.

The latter solution is much easier to manage: for a way to do it.

@footswitch,

I disagree: SQL indeed _was_ made for shared access. It just happens that no commonly available remote file locking protocol works correctly. This isn't SQLite fault, since its code is correct.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

jchd,

I'm not fond of disagreeing over semantics :)

In SQLite, "shared access" is supported within the same "machine".

From what I've read some time ago (in Mike Owens' book if I'm not mistaken), when SQLite was first developed, shared access across the network was an intention but not an actual goal, not one that was "written in stone" anyways. If you ask me, it still remains very much so.

EDIT: but yes you're absolutely right that it's not SQLite's "fault"!

Edited by footswitch
Link to post
Share on other sites

I do this thing where I create a text file in the same network folder as the database and I write the local computer name in the text file.

If the text file exists and it is not "My" @ComputerName on line 1 then I know someone else is using the database, so I wait until the text file is removed before "My Computer" then creates a new one and writes its @ComputerName to the file.

So far this has worked OK but I don't have a deadlock get around

Link to post
Share on other sites

All replies are good... I am so limited with privileges and so looks like a "turn based" system would work plus a timeout. Jchd, glad you replied. I know what you meant from the other thread but i just wanted to put this into a new one. I guess i am going on a fair direction. Great help from here thanks a lot guys.

Link to post
Share on other sites

As I said, I've something much better in the works, but not yet ready for consumption.

Anyway, keep in mind that reads (SELECTs) also need to read-lock the DB file: searching an index or doing a full table scan while another process is changing the DB under your feet would certainly be a very bad thing!

Hence the locking semaphore file has to be used not only for writes (UPDATE, INSERT, CREATE, ALTER, REINDEX, ..., various pragmas).

Note that the semaphore file is subject itself to the same locking issues than the DB, albeit at a slower pace and without the difference in locking types SQLite uses. So it isn't a perfectly failproof method either.

What I and other say about all this is nonetheless overly pessimistic in many use cases. In practice, unless you're doing a heavy use of the DB from 20+ machines, you're unlikely to encounter corruption easily (modulo decent network reliability). But as the saying goes, better sad than sorry.

Have a look and use this wrapper UDF to backup your DB at times. It will happily hot backup on the fly reliably (but backup restarts when a write is made, if using the default journaling mode).

In any event, make good use of transactions to group statements that need to operate fast (bulk inserts) or as an atomic block (typically read-modify-write operations).

Right after openning a DB connection, use _SQLite_SetTimeout with a (ms) value greater than the duration of the worst possible sequence of worst duration statements you might ever encounter. To make a long story short, don't hesitate to make it 5 or 10 minutes or much more if you need much more, meaning "wait forever, but not that much".

Use BEGIN IMMEDIATE .. COMMIT transactions. With that, you are sure to never get SQLITE_BUSY nor risk a DB deadlock. This way, you don't even have to test for that.

I'll do my best to come up with a better transparent and reliable LAN support but my current "half homeless" situation makes it difficult for me to work normally.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
  • 8 months later...

I'm really sorry to say NO.

I've not written a single line of AutoIt code for months and that situation will last for some time.

In the meantime, I've heard of a promising solution which I've had no time to try myself.

Have a look at this. From what I read it should be fairly easy to adapt the standard SQLite UDF to invoke this library instead.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Why struggle with the inadequacies of SqlLite and just use MySql instead?

It's like if you know it has issues, irregardless of who's fault it is, then why bother using it at all if it can't be fixed?

In fact, if you took the time to read the FAQ, its all layed out here:

http://www.sqlite.org/faq.html#q5

If it isn't the right tool, trying to fit a round peg into a square hole is a complete waste of time. Bite the bullet and use something else.

Link to post
Share on other sites

Fast, flexible, reliable, unumcombered from legaleese, portable, blind easy to setup, predictable, stable, ...

These are only some of the advantages of SQLite over most (if not all) RDBMS, especially over MySQL.

I fail to see why the fore-mentionned DLL wouldn't fit the sqare hole.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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 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 benners
      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
    • 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...