Comparing structured lists

[SFSxOI]

I have two linked lists. I want to compare them and then show the differences between the lists. The lists are set up like this basically:

Code: Select all

Structure XUsed 
  Used$
EndStructure

Structure XAvail 
  Avail$
EndStructure

NewList XUsed.XUsed()
NewList XAvail.XAvail()


For x = 1 To 255
AddElement(XAvail())
XAvail()\Avail$ = Str(Random(3 * 23))
Next x


For y = 1 To 255
AddElement(XUsed())
XUsed()\Used$ = Str(y)
Next y

How do you compare the two lists and get the differences between the lists ?

[Kaeru Gaman]

use a double ForEach.

Code: Select all

ForEach XUsed()
  ForEach XAvail()
    If XUsed()\Used$ = XAvail()\Avail$
      Debug "Hit: " + XUsed()\Used$ + " / " + XAvail()\Avail$
    EndIf
  Next
Next

btw.. numbers for comparisons are better stored as Integers (Longs) than as Strings...

[SFSxOI]

Thanks Kaeru, That gives me the matches between the two lists but it doesn't give me the differences. Thats the part i'm having problems with, the differences between the two. It seems as if I just get both lists when i do the double foreach.

[pdwyer]

Kaeru,

This gets the hits, you would need to store these then go though the lists again and collect the misses as I think he wants the "differences"

If this was my app, I'd be starting to look for a shortcut, namely, use SQLite :memory: databases and do this in an SQL query.

It might be a little slower in this case but it means you could easily change the query when needed.

[Kaeru Gaman]

oh... I just missed that point with the differences...

you can get it with carrying a notation field.

Code: Select all

Structure XUsed
  Used$
  Flag.l
EndStructure

Structure XAvail
  Avail$
  Flag.l
EndStructure

NewList XUsed.XUsed()
NewList XAvail.XAvail()


For x = 1 To 255
AddElement(XAvail())
XAvail()\Avail$ = Str(Random(3 * 23))
Next x


For y = 1 To 255
AddElement(XUsed())
XUsed()\Used$ = Str(y)
Next y

ForEach XUsed()
  ForEach XAvail()
    If XUsed()\Used$ = XAvail()\Avail$
      XUsed()\Flag = 1
      XAvail()\Flag = 1
    EndIf
  Next
Next

ForEach XUsed()
  If XUsed()\Flag = 0
    Debug "Used:" + XUsed()\Used$ + " is Not in Avail"
  EndIf
Next
Debug "-------"
ForEach XAvail()
  If XAvail()\Flag = 0
    Debug "Avail:" + XAvail()\Avail$ + " is Not in Used"
  EndIf
Next

[pdwyer]

sql is looking better and better

[Kaeru Gaman]

I think it's a matter of taste... what would SQL do much different internally?

if the data is organized like here, means you need only one way comparison,
you can do it with a single flag and without altering the structures themselves:

Code: Select all

Structure XUsed
  Used$
EndStructure

Structure XAvail
  Avail$
EndStructure

NewList XUsed.XUsed()
NewList XAvail.XAvail()


For x = 1 To 255
AddElement(XAvail())
XAvail()\Avail$ = Str(Random(3 * 23))
Next x


For y = 1 To 255
AddElement(XUsed())
XUsed()\Used$ = Str(y)
Next y

ForEach XUsed()
  Flag = 0
  ForEach XAvail()
    If XUsed()\Used$ = XAvail()\Avail$
      Flag = 1
    EndIf
  Next
  If Flag = 0
    Debug "Used:" + XUsed()\Used$ + " is Not in Avail"
  EndIf
Next

you can check in one double-ForEach, if elements of the list of the outer loop are present in the list of the inner loop.
but you can't check the opposite additionally in the same double-Loop without implementing an elementwise flag.
... you sure can drive a second double-Loop with the lists exchanged right afterwards.
I didn't do that in yesterdays example to keep the number of iterations low.

... I would bet a single request in SQL would do just such a double loop with a temporary flag.

[pdwyer]

no bet

If there are other queries to be added later though then this manual method could get a little unweildy and be more prone to bugs though

[Kaeru Gaman]

yeah, ok...

for me it would be easier for a long way, because I never did anything with SQL.

but basically it sounds good and interesting.
do you have a link to some essay/manual/tutorial to get into SQL :memory: a bit?

[pdwyer]

Using this thread as an example. I'm doing all three here, but I guess SFSxOI just needs one of them, first or last I'm not sure. Run it a couple of times, the random()s mean you might get no return result

There may be better SQL code with a join, my SQL is getting a but rusty since the company I work for now has a DBA team and I'm not in it

Code: Select all

UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "")
    
    DatabaseUpdate(0, "CREATE TABLE Table1 (Used text)")
    DatabaseUpdate(0, "CREATE TABLE Table2 (Avail text)")
    
    For i = 1 To 255
        DatabaseUpdate(0, "INSERT INTO table1 (Used) VALUES ('"+Str(Random(3*23))+"')")
        DatabaseUpdate(0, "INSERT INTO table2 (Avail) VALUES ('"+Str(Random(3*23))+"')")
    Next

    Debug "Missing in table 2"
    sSQL.s = "select distinct used from table1 where Not used in (Select avail from table2)" 
    If DatabaseQuery(0, sSQL)
    
        While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
        Wend        
        FinishDatabaseQuery(0)
    EndIf
    
    Debug "Missing in Table 1"
    sSQL.s = "select distinct avail from table2 where Not avail in (Select used from table1)" 
    If DatabaseQuery(0, sSQL)
    
        While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
        Wend        
        FinishDatabaseQuery(0)
    EndIf
    
        
    Debug "All Differences"
    sSQL.s = "select distinct used from table1 where Not used in (Select avail from table2) union select distinct avail from table2 where Not avail in (Select used from table1)" 
    If DatabaseQuery(0, sSQL)
    
        While NextDatabaseRow(0)
            Debug GetDatabaseString(0, 0)
        Wend       
        FinishDatabaseQuery(0)
    EndIf
    
 Else
    Debug "Can't open database !"
 EndIf

[Kaeru Gaman]

I'd like to dig into that later...
can you recommend some SQLite-Readme somewhere to gain info?

[SFSxOI]

Thanks Folks

@Kaeru;

What you posted is what i'll end up using, its a small utility project and this will do nicely. I never thought about using a notation field. Thank You very much

@pdwyer;

The SQL thing looks interesting. I've got a project in mind for the near futuer and it looks like the SQL might do the trick for it.Thank you for bringing it up.

[pdwyer]

I would start with the links provided in the PB help doc

http://www.hwaci.com/sw/sqlite/lang.html is SQLite specific info on a function by function basis. This links to the core functions list http://www.hwaci.com/sw/sqlite/lang_corefunc.html which can be used inside select statements to do addition, left(), mid() etc. And there is a more generic link in the PB docs for SQL in general that points here http://www.hwaci.com/sw/sqlite/lang.html

(I'm not sure what you know already so I'm not assuming much, perhaps the following is not news to you) but you can look at a basic insert and update statement in a few minutes then spend as long as you like on SELECT. you can do lots with select, join tables, apply filters with "WHERE" with boolean operators etc. Once you learn lots of ways to use "WHERE" then you can use it better with the UPDATE statement better.

At first, I would stay away from stored procs, joins etc and for selects steer clear of "case" and "if" statements and if you see the word "pivot table" (not a key word) being implemented in SQL then run!!

nesting select statements (as I did above) is a step above the basics but it's not really advanced, it gives you a lot more power but it's not always the best way to do things.

Nesting SQL selects implemented in raw PB code can turn into a LOT of code to implement if you want to support a lot of what the "where" statement adds to this

[Kaeru Gaman]

What you posted is what i'll end up using, its a small utility project and this will do nicely. I never thought about using a notation field. Thank You very much

your welcome! I'm glad it helped.


@Paul
thanx. also thanx for the links. perhaps I should have looked into the PB-Help chapter before asking. sorry.

sounds not too complicated.. I think I should get into it easily enough... I did some RBase 20 years ago...