stuck on a query to identify unique items

[TheXman]

I understand what you are trying to do.  You want to use the ID3 tag info as a unique identifier.  The problem is that you can't do that if some of the songs may not have an ID3 tag.

 

Edited January 14, 2019 by TheXman

[gcue]

The logic you are using definitely makes sense and should be the case. But if a record has both song title and id3 it should only use id3 as the comparing field. The example I mentioned earlier with id3-1 would not show as missing.  

Edited January 14, 2019 by gcue

[gcue]

I can create an error for songs that do not have an id3 when the data gets populated.  It will force the user to manually add an id3 tag. That way each song will always have an id3 tag and the query is easier

Edited January 20, 2019 by gcue

[gcue]

any thoughts?

[TheXman]

If I understand correctly, it will be possible for different song titles to have the same same id3 tag.  With that being stipulated, what is the expected result of the following scenario?

Device   | Title  | ID3
---------------------------
Device A | Song A | Song A
Device B | Song B | Song A
Device C | Song C | Song C

Obviously, ID3 "Song C", with title "Song C", is missing from devices A & B.  But my question for you is what's your logic for determining which song title, with ID3 "Song A" is missing from device C?  That is the piece of the puzzle, so far, that has not been described.  That is the problem with your previous request to be able to show the missing song title, in addition to the ID3 tag, based solely on the ID3 tag.

Edited January 20, 2019 by TheXman

[gcue]

2 different songs will not have the same id3 tag.  id3 tag will always be unique to a specific song.  so it is safe to just go by id3 as the comparison.  upon scanning media, id3 tag will be a required field for every song

hope that answers your question and should make the query super easy!

[gcue]

also would it be too much trouble to add another field?  not to the comparison but just want to add it to the sql database and to the array output?  the field would be populated on media scan and used for my own referential purposes.  after this is complete - i promise no more additions or changes.  I'd have all i need.

Device | Title | ID3 | SHA1 Checksum

thank you thank you thank you!!!

Edited January 20, 2019 by gcue

[TheXman]

On 1/13/2019 at 9:59 PM, gcue said:

for instance, if i change the song name in Device B (from song 1 to song 9) and the id3 field remains the same - it should not come up on the missing results for Device A or Device D because the same id3 tag (id3-1) exists on both Device A and D. the song name doesnt matter because the only comparison field is the id3 field.

The statement above does not match the statement below.

7 minutes ago, gcue said:

2 different songs will not have the same id3 tag.  id3 tag will always be unique to a specific song.  so it is safe to just go by id3 as the comparison.

 

[TheXman]

8 minutes ago, gcue said:

also would it be too much trouble to add another field?  not to the comparison but just want to add it to the sql database and to the array output?  the field would be populated on media scan and used for my own referential purposes.  after this is complete - i promise no more additions or changes.  I'd have all i need.

Device | Title | ID3 | SHA1 Checksum

thank you thank you thank you!!!

No problem at all, where should I send the invoice? 

Edited January 20, 2019 by TheXman

[gcue]

14 minutes ago, TheXman said:

The statement above does not match the statement below.

 

actually it does match.  "for instance, if i change the song name in Device B (from song 1 to song 9) and the id3 field remains the same" was just to test the results.  this would not happen in a real situation because each song would have its own unique id3 tag. 

from your lastest example

Device   | Title  | ID3
---------------------------
Device A | Song A | Song A
Device B | Song B | Song A
Device C | Song C | Song C

Song A is not missing from Device B so it wouldnt be in the results.  Song A is missing from Device C only

Song B is really Song A just a different title but id3 matters title doesnt

Song C is missing from Device A and B

 

 

 

 

[TheXman]

16 minutes ago, gcue said:

actually it does match.  "for instance, if i change the song name in Device B (from song 1 to song 9) and the id3 field remains the same" was just to test the results.

If it were just for testing and not a consideration, why even point it out?

 

 

[gcue]

Sorry didn't mean to cause confusion

[TheXman]

30 minutes ago, gcue said:

actually it does match.  "for instance, if i change the song name in Device B (from song 1 to song 9) and the id3 field remains the same" was just to test the results.  this would not happen in a real situation because each song would have its own unique id3 tag.

If this is true, then the last query that was suggested, barring the addition of the new checksum field, should have been been okay.  I must be missing something because I don't understand why it wasn't sufficient at the time, especially now since every song will have an id3 tag.

[gcue]

Initially every song wasn't going to have an id3 tag. Wasn't till later that I thought to make it a requirement in order to facilitate the query

[gcue]

not sure why im getting sha1 column doesnt exist.  i dont get an error when committing the table with the sha1 column created.  -thank you in advance!

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, song, id3, sha1);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1', 'id3-1', '1x2323'), ('Device A', 'Song 3', 'id3-1', '1x2323'), ('Device A', 'Song 4', 'id3-4', '1x2323'), ('Device A', 'Song 5', 'id3-5', '1x2323'), " & _
                "('Device B', 'Song 1', 'id3-1', '1x2323'), ('Device B', 'Song 2', 'id3-2', '1x2323'), ('Device B', 'Song 4', 'id3-4', '1x2323'), " & _
                "('Device C', 'Song 2', 'id3-2', '1x2323'), ('Device C', 'Song 3', 'id3-2', '1x2323'), ('Device C', 'Song 4', 'id3-4', '1x2323'), ('Device C', 'Song 5', 'id3-5', '1x2323'), " & _
                "('Device D', 'Song 1', 'id3-1', '1x2323'), ('Device D', 'Song 2', 'id3-2', '1x2323'), ('Device D', 'Song 3', 'id3-1', '1x2323'); " _
        )
        _SQLite_Exec(-1, "COMMIT;")

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

        ConsoleWrite(@CRLF & "Selected devices: " & $sDevices & @CRLF & @CRLF)
        _SQLite_GetTable2d(-1, _
            "SELECT " & _
            "   device, " & _
            "   song, " & _
            "   id3 [missing id3], " & _
            "   sha1 " & _
            "FROM ( " & _
            "     (SELECT DISTINCT device FROM songs WHERE device IN (" & $sDevices & ")) " & _
            "     CROSS JOIN " & _
            "     (SELECT DISTINCT id3 FROM songs WHERE device IN (" & $sDevices & ")) " & _
            "     ) " & _
            "EXCEPT " & _
            "SELECT device, song, id3, sha1 " & _
            "FROM songs " & _
            "WHERE device IN (" & $sDevices & ") " & _
            "ORDER BY device, song; ", _
            $a2DResult, $iRows, $iCols _
        )
        _SQLite_Display2DResult($a2DResult, 15)

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

EndFunc

 

[TheXman]

Looking at your latest query, it appears that you want to find missing songs based solely on the sh1 value.  If this is true, then the following example is a different way to achieve your goal. 

The current query that you are using, with "cross join", is rather difficult to keep extending to meet your expanding needs.

I used the table data from your last post.  As I'm sure you know, all of the sha1 values are the same.  So when you run the example, it will show that there are no missing songs based on sha1.

 

I added some additional information to the console so that you can see what the example is doing.

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

        ; From a list of all distinct sha1/song/id3 values for a given set of
        ; selected devices, this query finds all sha1 values that don't already
        ; exist on a specified device.  So to find the result for all selected
        ; devices, this query needs to be UNION'd once for each selected device.
        ; (See the generated query in the console output)
        Const $SQL_QUERY_TEMPLATE = _
            "SELECT <device> [device], song [missing song], id3, sha1" & @CRLF & _
            "FROM (SELECT DISTINCT id3, song, sha1 from songs WHERE device IN (<selected_devices>))" & @CRLF & _
            "WHERE sha1 NOT IN (SELECT sha1 FROM songs WHERE device = <device>)" & @CRLF


        Local $a2DResult, $aSelectedDevices[0]
        Local $iRows = 0, $iCols = 0
        Local $sSelectedDevices = "", $sSqlQuery = ""


        ;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, song, id3, sha1);" & _
            "INSERT INTO songs VALUES " & _
                "('Device A', 'Song 1', 'id3-1', '1x2323'), ('Device A', 'Song 3', 'id3-1', '1x2323'), ('Device A', 'Song 4', 'id3-4', '1x2323'), ('Device A', 'Song 5', 'id3-5', '1x2323'), " & _
                "('Device B', 'Song 1', 'id3-1', '1x2323'), ('Device B', 'Song 2', 'id3-2', '1x2323'), ('Device B', 'Song 4', 'id3-4', '1x2323'), " & _
                "('Device C', 'Song 2', 'id3-2', '1x2323'), ('Device C', 'Song 3', 'id3-2', '1x2323'), ('Device C', 'Song 4', 'id3-4', '1x2323'), ('Device C', 'Song 5', 'id3-5', '1x2323'), " & _
                "('Device D', 'Song 1', 'id3-1', '1x2323'), ('Device D', 'Song 2', 'id3-2', '1x2323'), ('Device D', 'Song 3', 'id3-1', '1x2323'); " _
        )
        _SQLite_Exec(-1, "COMMIT;")


        ;Create an array of selected devices to be queried
        _ArrayAdd($aSelectedDevices, "Device A|Device B|Device D")

        ;Build "selected devices" string to be used by the query
        For $i = 0 To UBound($aSelectedDevices) - 1
            If $i > 0 Then $sSelectedDevices &= ","
            $sSelectedDevices &= _SQLite_Escape($aSelectedDevices[$i])
        Next
        ConsoleWrite(@CRLF & "Selected Devices: " & $sSelectedDevices & @CRLF)

        ;Build SQL Query UNIONs (one for each selected device)
        For $i = 0 To UBound($aSelectedDevices) - 1
            If $i > 0 Then $sSqlQuery &= "UNION" & @CRLF
            $sSqlQuery &= StringReplace($SQL_QUERY_TEMPLATE, "<device>", _SQLite_Escape($aSelectedDevices[$i]))
        Next
        $sSqlQuery = StringReplace($sSqlQuery, "<selected_devices>", $sSelectedDevices)
        ConsoleWrite(@CRLF & "Generated SQL Query: " & @CRLF & $sSqlQuery)

        ;Execute query and display the results
        _SQLite_GetTable2d(-1, $sSqlQuery, $a2DResult, $iRows, $iCols)
        If $iRows > 0 Then
            ConsoleWrite(@CRLF & "Selected Devices: " & $sSelectedDevices & @CRLF & @CRLF)
           _SQLite_Display2DResult($a2DResult, 15)
        Else
            ConsoleWrite(@CRLF & "WARNING: No missing songs found" & @CRLF)
        EndIf

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

 

Edited January 22, 2019 by TheXman

[gcue]

awesome!  thank you so much for your help and patience