Geavanceerd filteren in GSAK, THEX 25 november 2015, update 27 maart 2016
Filteren in GSAK kan door een handmatig filter menu te activeren, of een filter menu-item in de macro aan te maken (zie boek GSAK voor de beginnende gevorderde) of filteren op database niveau. Iedere genoemde optie brengt zijn eigen mogelijkheden, maar ook zijn eigen complexiteit. In dit document wordt met grid bedoelt: de zichtbare matrix van caches in het GSAK scherm. In dit document gebruiken we 2 tabellen van GSAK: Caches (die bevat de geocaches) en Custom (die bevat Custom velden bij de geocaches). Er zijn meer tabellen, waaronder logjes, corrected coördinaten en info over geocachers. Voor de eenvoud laten we die hier weg.
Figuur 1, Tabel Caches
Figuur 2, Tabel Custom
In basis zijn er in GSAK 3 mogelijkheden waarop een ingesteld filter zich zichtbaar kan maken: • • •
UserFlag: kan handmatig of door macro gezet worden, en hier handmatig op worden gefilterd. MacroFlag: kan door macro gezet worden en wordt op gefilterd. Is echter niet handmatig te bedienen via een menu (wel database) en niet zichtbaar in het grid. GridTemp tabel die het zichtbare Grid in het GSAK scherm met zichtbare caches omvat. Is het resultaat van een filteractie (handmatig of per macro).
In een macro kunnen de filters als volgt uitgeschakeld worden: CancelFilter
Wissen van de individuele vlaggen kan als volgt: macroflag type=clear range=all userflag type=clear range=all
Een actief filter zetten op een vlag resultaat: Mfilter where=$d_Userflag Mfilter where=$d_Macroflag
M.b.v. het handmatige Filter menu (GSAK Menu) kan een filter worden gecreëerd en permanent opgeslagen en vanuit de macro of menu worden geactiveerd: FILTER Name="naam handmatig opgeslagen filter" Het is ook mogelijk de handmatige filter instellingen in GSAK zelf in te vullen en op te slaan onder de gereserveerde naam <macro>. Ze komen dan als copy-paste beschikbaar om in de macro opgenomen te worden (zonder opslag onder het Filter menu, waar het ook zichtbaar en toepasbaar is voor gebruikers). Hier is als voorbeeld de complete code voor het filteren op een "event" weergegeven (waarbij het datablok
d.m.v. copy-paste is verkregen): MACROSET Dialog=Filter VarName=$FilterEvent Filter Name=<macro> return VarName=$FilterEvent edtUserId= cbxState=0 cbxCountry=0 edtState= edtCountry= cbxUsort=4
edtUsort= edtUsort2= edtCode= cbxCode=0 cbxFoundCount=0 EdtFoundCount= cbxDegrees=0 edtDegrees= cbxCounty=0 edtCounty= cbxElevation=0 edtElevation= edtElevation2= cbxN=True cbxNW=True cbxNe=True cbxS=True cbxSW=True cbxSE=True cbxE=True cbxW=True cbxMicro=True cbxRegular=True cbxLarge=True cbxSmall=True cbxOther=True cbxNotChosen=True cbxVirtual=True chkUnknown=True chkLockYes=True chkLockNo=True chkCorrectYes=True chkCorrectNo=True chkDNFYes=True chkDNFNo=True chkWatchYes=True chkWatchNo=True chkChildYes=True chkChildNo=True Traditional=False Multi=False Letterbox=False CITO=False Event=True Locationless=False
Virtual=False Webcam=False Mystery=False Benchmark=False Other=False Earth=False Project APE=False Mega Event=False Maze Exhibit=False Wherigo=False Waymark=False L&F Event=False Groundspeak HQ=False L&F Celebration=False Block Party=False Giga Event=False Lab Cache=False *where*= <enddata>
De where clause onder de Where tab staat filteren (zij het dan beperkte functionaliteit) toe op database niveau als ware je een sqlite where-clause implementeert (zonder de where): WilsonUpdateDate="2000-01-01" or LastGPXDate>WilsonUpdateDate
Koppelen van 2 filters - deel 1 Het volgende voorbeeld geeft een handige aanpak om verder te gaan op een eerdere selectie: MACROFLAG Type=Clear Range=All $_sql="UPDATE Caches SET Macroflag=1 WHERE Found=0 AND Status='A' AND IsOwner=0 AND Not(CacheType='U' and HasCorrected=0) AND $_WHERE" $SQLresult=Sqlite("sql",$_sql) MFILTER WHERE=MACROFLAG
De variabele $_WHERE bevat de vorige where clause en wordt gekoppeld aan het doorfilteren op: • • • •
beschikbaar Status='A' niet gevonden Found=0 niet de eigenaar IsOwner=0 en is geen puzzel die niet opgelost is CacheType='U' and HasCorrected=0
Koppelen van 2 filters - deel 2 Het is vaak eenvoudiger 2 losse filters separaat op te bouwen en samengevoegd toe te passen. FILTER Name="Filter 1" FILTER Name="Filter 2" Join=and Het bovenstaande filter commando staat meer mogelijkheden toe zoals het figuur hier onder laat zien: FILTER [] [<Join=None|or|and>]
Naast het FILTER commando is er het MFILTER commando. Dit filter maakt gebruik van de macro vlag. Deze vlag is niet zichtbaar onder een view, maar wel benaderbaar in de database als het moet. Als een macro filter actief is zie je dit aan de rode macro-vlag melding links onder op het scherm (uit te zetten via menu CancelFilter) . De MFILTER (in tegenstelling tot FILTER) staat je toe niet opgeslagen filters toe te passen op basis van sqlite. MFILTER <Where=SQLite where> [] [<Join=None|or|and>] Voorbeeld: filteren op de UserFlag: MFILTER Where=UserFlag IF $_FilterCount > 0 ... do some action ELSE PAUSE Msg="No waypoints in filter, action canceled" ENDIF
De volgende filter situaties komen veelvuldig voor: 1. Filter 1 AND Filter 2. Het gefilterde resultaat moet voldoen aan zowel filter 1 als filter 2. 2. Filter 1 OR Filter 2. Het gefilterde resultaat moet voldoen aan ofwel filter 1, ofwel filter 2 ofwel filter1 en filter 2. Natuurlijk zijn er meer denkbare combi's. Stel nu dat ieder filter op zichzelf bijzonder complex is. Bij opbouw blijkt dat ineens het filter extreem traag wordt. Hoe gaan we hier zodanig mee om dat het filteren toch snel loopt.
Substractie methode Deze methode gaat ervan uit dat er al een filter in GSAK loopt, en van het verkregen resultaat weer een subselectie gemaakt moeten worden. Het principe is dit filteren na filteren. Probleem hierbij is dat je niet weet (en eigenlijk ook niet wilt weten) welk filter al operationeel is, of wat het filter is in de vorige stap. De basis opzet is als volgt. Deze stap maakt gebruik van de Macroflag op een heel bijzondere en snelle wijze: het niet zichtbare GridTemp tabel (ook niet zichtbaar in de sqlite browser). Deze tabel representeert nl wat er op het scherm visueel is, het Grid. De basis structuur is als volgt en is uitbreidbaar. MFILTER Where=Code IN(part a) AND Code IN(part b) AND Code IN(Select Code from Caches WHERE RowID IN(Select * From GridTemp)) IF $_FilterCount=0 CANCEL Msg=There are no caches to update. ENDIF
Het rode deel is verplicht, het blauwe deel is een dynamisch uitbreidbaar deel (en kan lang en complex zijn). Code IN (huppeldepup) geeft aan dat Code in de resultaat set huppeldepup moet voorkomen. In het onderstaande voorbeeld wordt de Custom tabel (met index cCode) op deze wijze betrokken bij de Caches tabel (met index Code). Vul voor part a en part b onderstaande rode stukken in: Part a: SELECT cCode FROM Custom where cCode=Code Part b: Select cCode from Custom WHERE SomeCustomField='some value' Er staat nu in normale menselijke taal: Filter op: •
Selecteer op Code die voldoet aan 3 criteria:
• • •
Part 1: Code zit in de set cCode's van tabel Custom. Maar koppel die cCode welke gelijk is aan Code (het resultaat van where cCode=Code). (Part 2): Code zit in de set cCode's van tabel Custom waarbij Custom veld SomeCustomField een bepaalde waarde heeft (de aanvullende uitbreiding). (Part 3): Code zit al in GridTemp, dwz zit al in een lopende en actieve selectie. Deze selectie is het lastigst te begrijpen, temeer omdat we GridTemp niet kunnen bekijken.
De totale constructie is lastig te begrijpen, maar heb je het principe eenmaal door, dan kun je er veel mee. Als je deze constructie herhaald verkleint het volgende filter het resultaat van het vorige filter zonder dat iets bekend is over dat filter. De bovenstaande aanroep past GridTemp aan, zodat bij een volgende en soortgelijke aanroep van een filter op het resultaat kan worden doorgefilterd. De Filter1 AND Filter2 situatie is nu bereikt. Stel we willen filteren op een combi van elementen uit 2 tabellen die vergeleken worden: een Custom veld (SomeCustomField) en een Caches veld (LastGPXDate). Dit gaat lastig met de bovenstaande methode (het kan wel, maar blijkt ineens ongelofelijk traag te zijn). Door een tijdelijke view aan te maken welke tabellen handig verenigd, kan dit wel eenvoudige gerealiseerd worden: $_sql="drop view if exists theo;" $result=SQLite("sql",$_sql) $_sql="create temp view theo as SELECT LastGPXDate, SomeCustomTekstField, Code FROM Caches JOIN Custom ON Code=cCode;" $result=SQLite("sql",$_sql) MFILTER Where=Code IN(SELECT Code FROM theo where (date(SomeCustomTekstField)
In SQLLite bestaan er diverse vormen van table joins (o.a. inner en outer joins). Zoek maar met Google op de mogelijkheden, het wordt dan pas echt ingewikkeld.
Nu de situatie nog met Filter 1 OR Filter 2 Dit kan door ieder filter separaat uit te voeren (met UserFlag eenmalig gewist), en het filter resultaat aan de UserFlag toe te voegen. Om nu terug te kunnen gaan naar de aanvangssituatie voor filter 2 kunnen we het filter tijdelijk opslaan (uitgaande dat het aanvangsfilter niet gebruik maakt van de UserFlag): $SavedFilter = SaveFilter()
userflag type=clear range=all Voer filter 1 uit
userflag type=set range=filter If RestoreFilter($SavedFilter,True) = 0 Pause Msg="No records match your restored filter. Filter has been canceled" EndIf Voer filter 2 uit
userflag type=set range=filter Mfilter where=$d_Userflag
Saven van een Joined filter Stel dat je een Macrofilter hebt opgesteld m.b.v. een JOIN. Dergelijke complexe filters kunnen niet met savefilter() worden opgeslagen. De volgende truuk zet het JOIN filter om in een normaal macro filter: MFILTER Where=Code IN(Select Code from Caches WHERE RowID IN(Select * From GridTemp)) $SavedFilter = SaveFilter()
