stuck on a query to identify unique items

[gcue]

hello

I have several devices (flash drives, external drives) which have songs.  i am tracking all the songs residing on each device in a sql database which has only 2 columns - device_name and song_name.  I would like to identify the songs on each device and show the songs missing on the remaining devices.  Then I can choose which ones I want to copy over and which ones I do not. (some devices belong to my kids so dont want to synch all the songs)

i do a select distinct sql query to id each device.  i thought about exporting an array of the entire database to cycle through but am stuck on the logic

can anyone please help?

here is what ive started with

Local $array, $iRows, $iColumns, $iRval

    _SQLite_Open($sql_db)

    $iRval = _SQLite_GetTable2d(-1, "SELECT * FROM aSongs;", $array, $iRows, $iColumns)
    If $iRval <> $SQLITE_OK Then
        MsgBox($msg_error, @ScriptName, "Unable to generate array from SQL db.")
        Return
    EndIf


    Local $hQuery, $aRow

    _SQLite_Open($sql_db)

    _SQLite_Query(-1, "SELECT DISTINCT Device_Label FROM aSongs", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
;~      GUICtrlCreateListViewItem("|", $compare_listview)
    WEnd

    _SQLite_QueryFinalize($hQuery)

    _SQLite_Close()

thanks in advance.

[TheXman]

@gcue

Below is an example of a few basic queries, that you can modify as needed, that can return some of result sets that you detailed above.  There are numerous ways that it can be done.  The example, below, is not meant to be exhaustive nor is it meant to be used as-is.  It is merely provided to give you an idea of one way that it can be done and to help you move forward.

Click below to reveal the example

Spoiler

expandcollapse popup

#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "C:\Program Files\SQLite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 5');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 6');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")

        ;Display songs table
        ConsoleWrite("Temp Song Table" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT * FROM songs;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;Display list of unique song names in the songs table
        ConsoleWrite(@CRLF & "List of unique song names" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT DISTINCT name FROM songs ORDER BY name;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs on Device A
        ConsoleWrite(@CRLF & "List of all songs on Device A" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT device, name " & _
            "FROM songs " & _
            "WHERE device = 'Device A' " & _
            "ORDER BY name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs missing from Device A
        ConsoleWrite(@CRLF & "List of all songs not on Device A and which devices have it" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT name, device " & _
            "FROM songs " & _
            "WHERE name NOT IN (SELECT name from songs WHERE device = 'Device A') " & _
            "ORDER BY device, name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()
EndFunc

 

 

[jchd]

You can also use the following query sketch:

select ... from tableA except select ... from tableB

 

[gcue]

TheXman

Thank you for the example however will that work if there are multiple devices? If so, would I have to write a new set of code every time a device is added? 

Jchd, 

Only have 1 table with all the info that's why I have the device_name column. 

 

[TheXman]

48 minutes ago, gcue said:

will that work if there are multiple devices? If so, would I have to write a new set of code every time a device is added? 

@gcue

The only thing in the query that would change in order to find the missing songs for a given device, regardless of how many device names exist, is the name of the device.  So, in the query, just make the device name a variable,  As you can see in the example below, the same query is used but just a different name is passed to it each time.  Nothing changed from my previous example except making the query that reports missing songs into a function that accepts a device name.

 

Spoiler

expandcollapse popup

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "C:\Program Files\SQLite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device A', 'Song 5');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device B', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 1');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 3');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 6');" & _
            "INSERT INTO songs VALUES ('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")

        ;Display songs table
        ConsoleWrite("Temp Song Table" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT * FROM songs;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;Display list of unique song names in the songs table
        ConsoleWrite(@CRLF & "List of unique song names" & @CRLF)
        _SQLite_GetTable2d(-1, "SELECT DISTINCT name FROM songs ORDER BY name;", $a2DResult, $iRows, $iCols)
        _SQLite_Display2DResult($a2DResult, 10)

        ;List of songs on Device A
        ConsoleWrite(@CRLF & "List of all songs on Device A" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT device, name " & _
            "FROM songs " & _
            "WHERE device = 'Device A' " & _
            "ORDER BY name;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ShowMissingSongs("Device A")
        ShowMissingSongs("Device B")

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

Func ShowMissingSongs($sDeviceName)
    Local $a2DResult
    Local $iRows, $iCols

    ConsoleWrite(@CRLF & "List of all songs not on " & $sDeviceName & " and which devices have it" & @CRLF)
    _SQLite_GetTable2d(-1, _
        "SELECT name, device " & _
        "FROM songs " & _
        "WHERE name NOT IN (SELECT name from songs WHERE device = '" & $sDeviceName & "') " & _
        "ORDER BY device, name;", _
        $a2DResult, $iRows, $iCols _
    )
    _SQLite_Display2DResult($a2DResult, 10)
EndFunc

 

 

Edited December 17, 2018 by TheXman

[jchd]

Here's a reworked version of @TheXman code showing how to list all files found missing on each device, all in one single query:

expandcollapse popup

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1'), " & _
                "('Device A', 'Song 5'), " & _
                "('Device B', 'Song 1'), " & _
                "('Device B', 'Song 3'), " & _
                "('Device C', 'Song 1'), " & _
                "('Device C', 'Song 3'), " & _
                "('Device C', 'Song 6'), " & _
                "('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")


        ConsoleWrite(@CRLF & "List which songs are missing on which devices" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "select name Missing, device [on device] " & _
            "from (" & _
            "       (select distinct name from songs)" & _
            "           cross join" & _
            "       (select distinct device from songs)" & _
            ") " & _
            "except " & _
            "select name, device " & _
            "from songs;", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

This query first builds an unnamed temporary table of all possible couples {song, device} but removes (except verb) those found in your actual songs table. Mathematically this is a set complementation operation. This approach isn't recommended for large tables but probably good enough in such simple use cases.

Hope this gives you some incentive to look deeper into SQL and SQLite.

[gcue]

a true work of art

thank you both VERY MUCH for your help.

hope both of you have wonderful holidays

[jchd]

You can as well produce AutoIt code to copy missing file from their known-existing location:

select 'FileCopy(' ||
        '"' || substr(
                      (select device
                       from songs
                       where name = t.name
                       order by device
                       limit 1
                      ),
               -1) || ':' || name || '"' || 
        ', ' ||
        '"' || device || ':' || '"' ||
        ')' [AutoIt code]
from (
  select *
  from (
        (select distinct name from songs)
            cross join
        (select distinct device from songs)
  )               
  except
  select name, device
  from songs  
) t

This is a pretty simple query!

[gcue]

would it be possible to specify just certain devices?  so i'd use my original query to generate a list of available devices then build a checkbox gui for the user to select which devices to compare.  then i would ideally plug into the query the selected devices

Local $hQuery, $aRow

    _SQLite_Open($sql_db)

    _SQLite_Query(-1, "SELECT DISTINCT Device_Label FROM aSongs", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    WEnd

    _SQLite_QueryFinalize($hQuery)

    _SQLite_Close()

thanks again! 

[TheXman]

2 hours ago, gcue said:

would it be possible to specify just certain devices?

@gcue

Yes, of course it's possible.  What have you tried and what issues did you have with what you came up with?

[caramen]

16 hours ago, TheXman said:

Yes, of course it's possible.  What have you tried and what issues did you have with what you came up with?

It is nice to ask what the op have tryed so far. But if he is asking if it's possible how he can even have tryed it ?

Edited December 28, 2018 by caramen

[Earthshine]

It’s not worth getting steamed. Just try to help them learn. 😀

[TheXman]

5 hours ago, caramen said:

But if he is asking if it's possible how he can even have tryed it ?

@caramen

Let me get this straight.  You pop into this topic just to ask a rhetorical question?  Who do you think you are, a wanna be Mod?  The answer is because some people that ask "is it possible..." are just looking for others to give them the code without putting in any effort,  If you would have spent any time reading through the topic, you would have seen that I provided a solid foundation for gcue to build upon already (my content is hidden under "Reveal hidden content" so it doesn't clutter up the thread due to the length of the snippet).  To add insult to injury, you didn't even provide any additional information or further the conversation.  @caramen you are a MORON!  Next time, if there's a next time, you need to stay in your lane or risk getting run over.

Obviously I don't care if I get kicked from the forum for saying that.  I've been around since 2007 and 99.9% of my posts have been in an effort to help others.  With less than 300 posts in almost 12 years, obviously my life doesn't revolve around AutoIt.  I'm a semi-retired CTO with plenty of other things to do.  I just try to give back where and when I can.  so, if I get banned, so be it.  There are plenty of other places to volunteer a little of my time.

[caramen]

49 minutes ago, TheXman said:

The answer is because some people that ask "is it possible..." are just looking for others to give them the code without putting in any effort,

Give me your cristal ball please

If I am a moron, I guess you feel like: I insulted you ?

You have to stay down bro and calm yourself. Anyway I dont even would like to get you banned. I dont even report you. What is the problem about what I asked ?

It just reveal: the moron thing was the question.

52 minutes ago, TheXman said:

You pop into this topic just to ask a rhetorical question?

What is forbidden here ?

49 minutes ago, TheXman said:

If you would have spent any time reading through the topic

This is not related to what we are talking about. But yes i revealed your "Quote", so what then ?

49 minutes ago, TheXman said:

Who do you think you are, a wanna be Mod?

That is what you do actually. Think yourself too much important to judge other people. Thing that i dont did.

49 minutes ago, TheXman said:

There are plenty of other places to volunteer a little of my time.

Go for it. We want helpers but if helpers are rude like you => Goaway .

 

I wish you good fiesta actually you look like so happy .

Edited December 28, 2018 by caramen

[Earthshine]

Caramen, How was he rude? Where you not rude to him first?

 Maybe you should learn English a little better

Edited December 28, 2018 by Earthshine

[iamtheky]

devolve much kids? Mindless bickering is my bag, yall can sod off. 

[gcue]

life is too short to be arguing

here is what i have but cant figure out where to put the string for devices

expandcollapse popup

_SQLite_Open($sql_db)

    Local $iArray, $iRows, $iColumns, $iRval

    $iRval = _SQLite_GetTable2d(-1, "SELECT DISTINCT Device_Label FROM aSongs;", $iArray, $iRows, $iColumns)
    If $iRval <> $SQLITE_OK Then
        MsgBox($msg_error, @ScriptName, "Unable to generate array from SQL db.")
        Return
    EndIf

;~  Debug($iArray)

    $devices = _ArrayToString($iArray, "", 1, 0, ",")
;~  Debug($devices)

    Local $jArray, $jRows, $jColumns, $jRval

    _SQLite_GetTable2d(-1, _
            "SELECT Song_Name Missing, Device_Label [On Device]" & _
            "FROM (" & _
            "       (SELECT DISTINCT Song_Name FROM aSongs)" & _
            "           CROSS JOIN" & _
            "       (SELECT Song_Name FROM aSongs WHERE Device_Label='" & $devices & ")" & _
            ") " & _
            "EXCEPT " & _
            "SELECT Song_Name, Device_Label " & _
            "FROM aSongs;", _
            $jArray, $jRows, $jColumns _
            )
    Debug($jArray)
    
Func Debug($variable1 = "", $variable2 = "", $variable3 = "", $variable4 = "", $variable5 = "")

;~  #include <array.au3>
;~  $msg_normal = 0

    If IsArray($variable1) Or IsArray($variable2) Then
        If IsArray($variable1) Then _ArrayDisplay($variable1, $variable2)
        If IsArray($variable2) Then _ArrayDisplay($variable2, $variable1)
    Else
        $variable = ""

        If $variable1 <> "" Then $variable &= $variable1 & @CRLF
        If $variable2 <> "" Then $variable &= $variable2 & @CRLF
        If $variable3 <> "" Then $variable &= $variable3 & @CRLF
        If $variable4 <> "" Then $variable &= $variable4 & @CRLF
        If $variable5 <> "" Then $variable &= $variable5 & @CRLF

        $variable = StringStripWS($variable, 2)

        ClipPut($variable)

        MsgBox($msg_normal, "Debug", $variable)
    EndIf

EndFunc   ;==>Debug

 

[caramen]

1 hour ago, iamtheky said:

devolve much kids? Mindless bickering is my bag, yall can sod off. 

Exactly what I mean. <

1 hour ago, Earthshine said:

Caramen, How was he rude? Where you not rude to him first?

He was rude at the moment i readed i am a "MORON". Does it is so impressive to you guys ? Or maybe i am alone to get that as a malicius word.

 

Well @gcue I am sorry about all that trash post. I shud it now.

[Earthshine]

You came at him first for no reason and added nothing to the thread. Go read it. That’s considered rude btw. 

[TheXman]

@gcue

Below is one way that it could be done.  It uses jchd's original snippet since that appears to be what you are using..

expandcollapse popup

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
        Const $SQLITE_DLL = "c:\program files\sqlite\sqlite3.dll" ;<-- Change to the location of your sqlite dll

        Local $a2DResult
        Local $iRows, $iCols
        Local $sDevices

        ;Init sqlite and create a memory db
        _SQLite_Startup($SQLITE_DLL, False, 1)
        If @error Then Exit MsgBox($MB_ICONERROR, "SQLite Error", "Unable to start SQLite. Check existence of DLL")
        _SQLite_Open()

        ;Create a songs table
        _SQLite_Exec(-1, "BEGIN TRANSACTION;")
        _SQLite_Exec(-1, _
            "CREATE TABLE songs (device, name);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1'), " & _
                "('Device A', 'Song 5'), " & _
                "('Device B', 'Song 1'), " & _
                "('Device B', 'Song 3'), " & _
                "('Device C', 'Song 1'), " & _
                "('Device C', 'Song 3'), " & _
                "('Device C', 'Song 6'), " & _
                "('Device C', 'Song 8');" _
        )
        _SQLite_Exec(-1, "COMMIT;")


        $sDevices = "'Device A', 'Device B'"  ;<== comma-delimited string that lists devices you want to query

        ConsoleWrite(@CRLF & "List which songs are missing on which devices" & @CRLF)
        _SQLite_GetTable2d(-1, _
            "select name Missing, device [on device] " & _
            "from (" & _
            "       (select distinct name from songs)" & _
            "           cross join" & _
            "       (select distinct device from songs)" & _
            ") " & _
            "where device in (" & $sDevices & ") " & _
            "except " & _
            "select name, device " & _
            "from songs " & _
            "order by device, name ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 10)

        ;Close db and shutdown sqlite
        _SQLite_Close()
        _SQLite_Shutdown()

EndFunc

 

Edited December 28, 2018 by TheXman
Added ORDER BY clause