Is there a better way - Class Code Filter

[margret]

I am working on a Class for DataBase functions. I have been stuck on this for over a week now. Erel says there is no macro substitution in B4A and so what would be pretty easy has now made my brain(what little I have):BangHead: explode. My goal is to write a filtering system for the stored data in the file. The programmer should be able to set the condition. They call another function that breaks their string into the Filters Type array like:

NOTE: I am looking for a way to do this with B4A code only. Hoping to not use libraries, etc.

db.SetFilters("name = jimmy AND zip_code # 37782 AND webpage @ facebook")

Now any time the database is searched, the search calls the TestFilter() function below to see if it passes the filter condition. I would be grateful for any input on this.

Private Sub TestFilter(Felement As Int) As Boolean
   If Empty(Filters(Felement).FieldName) Then Return True
   If Filters(Felement).Comparator = "=" Then
      If GetField(Filters(Felement).FieldName) = Filters(Felement).FilterValue Then
         Return True
      End If
   End If   
   If Filters(Felement).Comparator = "<" Then
      If GetField(Filters(Felement).FieldName) < Filters(Felement).FilterValue Then
         Return True
      End If
   End If   
   If Filters(Felement).Comparator = ">" Then
      If GetField(Filters(Felement).FieldName) > Filters(Felement).FilterValue Then
         Return True
      End If
   End If   
   If Filters(Felement).Comparator = "#" Then
      If GetField(Filters(Felement).FieldName) <> Filters(Felement).FilterValue Then
         Return True
      End If
   End If   
   If Filters(Felement).Comparator = "@" Then
      If At(GetField(Filters(Felement).FieldName), Filters(Felement).FilterValue) > -1 Then
         Return True
      End If
   End If   
   Return False
End Sub

 

[Jack Cole]

I do something like this in one of my apps to filter database results. I use a map to store the filter criteria. The key is the field name in the database, and the value is the criteria for the for the field. It might be easier to build your filter with a map rather than a text string.

 

[margret]

Thanks for the reply. How do you handle the other portions of the filter? Ones like AND, OR and Equals, contains and Not equal, etc. I want them to be able to select all these options plus select the field name and the value to compare or ignore.

 

[Jack Cole]

I didn't build a catch all kind of filter as it is for a specific purpose. So I don't do OR conditions only AND conditions and equal or not equal.

If I was going to build a catch all kind of thing, I would probably use a list of predefined types.

type criteria (field as string, operation as string, value as string)
dim lstfilter as list

Then add the criteria to the list.

dim comparison as criteria

lstfilter.initialize
comparison.initialize
comparison.field="Field1"
comparison.operation=">"
comparison.value="10"
lstfilter.add(comparison)

Then have a function to build / run the filter.

for x = 0 to lstfilter.size-1
   dim c as comparison
   c=lstfilter.get(x)
   select c.operation
       case "="  : 'code here for equals
       case ">"  : 'code here for greater than
       case "<"  : 'so forth
       case "<>" :
       case "!=" : 'code for not equal
   end select
next

You could add another variable to the type for containing AND or OR. That's not a working example above, but just off the top of my head for how I'd try to start.

 

[margret]

This is what I have done. The code listed above is the same as your last block but I didn't use the case statement. I want to allow up to four filter for ten different work areas.

If GetField(Filters(Felement).FieldName) < Filters(Felement).FilterValue Then

The line above is the Type element for the filter: Filters().FieldName. So we are thinking right down the same line. I was hoping someone was going to show us a much easier way. I am still hoping!

 

[Jack Cole]

Yes, seems like there should be an easier way. Maybe someone will have an idea.

I've not ever worked with the BasicLib, but I wonder if you could build the filter as code and have basiclib run it.

For example execute a line of code:

if field1="test" and field2="test2" and field3>1 then result=true

 

[mc73]

If you have successfully extracted the filters' return boolean value, and at the same time you have the logical operators, I think you can give Agraham's evaluation library a try.
Though it's using doubles, we can trick it, by setting a) 'true' to '1', 'false' to '0', and b) 'AND' to '*' and 'OR' to '+'.

Here's a sample I've made. To be honest, I didn't get to really test it, I leave it up to you, to find out whether it can work, I am sure you will do it carefully as always

' suppose we have 4 filters, which have just returned a true or false, in the variables a()
    ' we want to evaluate the expression 'a(0) and a(1) or a(2) and a(3) and so on...'
    
    Dim res,res2 As Double
    Dim ret As Boolean 
    Dim err,exp,msg, expOrig As String
    
    ' here we get the results of our filters and their logical operators
    Dim a(4) As Boolean, op(4) As String 
    a=Array As Boolean (True,False,True,True)
    op=Array As String ("","AND","OR","AND")
    
    ' we begin the evaluation
    Eval.Initialize
    exp="":expOrig=""
    For k=0 To a.Length -1
        Dim tempvar,tempOp As Char
        tempvar=Chr(65+k)
        
        If a(k)=True Then
            Eval.SetGlobal (tempvar,1)
        Else
            Eval.SetGlobal (tempvar,0)
        End If
    
        Select op(k)
            Case "AND"
                tempOp="*"
            Case "OR"
                tempOp="+"
            Case Else
                tempOp=op(k)
        End Select
    
        exp=exp & tempOp & tempvar
        expOrig=expOrig & " " & op(k) & " " & a(k)
    Next
    exp=exp.Trim 
    expOrig=expOrig.Trim 
    
    res = Eval.Evaluate(exp)
    err = Eval.Error
    
    If err = "" Then err = "No Error"
    If res>0 Then ret=True Else ret=False
    
    msg = expOrig & CRLF & exp & CRLF & " = " & ret
    Msgbox(msg, err)

 

[margret]

@mc73,

Thank you so much for your input. I should of said before but I was hoping to complete this with just native support from B4A and not use any other libraries, etc. I may not have any choice but this is my goal. I want the users to have the Class code and B4A and be ready to run. So I am really looking for any way/improvement that can be done in B4A code only. I know this is asking a lot but I will make this work this way if my brain don't explode first. I have the class completed other than being stuck on this filtering issue. I will still look into this option because you spent time looking into it. If you can think of any way or inprovement with code, please post it.

 

[mc73]

Another choice would be to study evaluation algorithms posted in different places over the net. For e.g. here's one I found, it's in VB, but guess you'll have no problem reading it
An expression evaluator written in VB.NET - CodeProject

 

[mc73]

Still, Margret, I am curious to know what put you into doing this, instead of using sqlite. What where the drawbacks?

 

[margret]

I come from a long history of coding in DBase and I miss the straight forward logic of that language. It also was the longest learning curve for me in B4A when I started. After I had almost a 1000 copies of my app on the market, they updated the OS for the device and none of them would read the SQL data files. The app was the same, the SQL database was the same but the OS was update. This alone really turned me off to SQL. I then tested it in house with Android 2.2 and Android 2.3. The same app on both devices but they would not read the SQL database created on the other device. The devices are the same, one with 2.2 and the other with the manufactures update to 2.3. To me it didn't matter where the real issue came from and it didn't matter to my customers, they were mad at me. After I converted to my own database structure they have been through three more updates and I don't have those issues. My customers can also exchange database files now between devices and different OSs without any issues.

I don't have these filtering issues within my app because I write them for my needs. So a long story short, I cleaned up my code and put it in a class and wanted to add the filters that could cover most test cases and share the code for others to use. Now, it's just a big challenge for me and I also want this class to use in my future apps without having to write filter code in each situation.

 

[mc73]

I am a bit surprised, didn't know that there were (or are) compatibility issues with sqlite! If so, I can understand why you made this step!

 

[BPak]

I have found it easier to have a display that shows in a combo box with the list of fields and a text box to enter the Value. Also Combo Box to select the Comparator and a Radio button set for AND and OR

The entry is made and then via button is added to a List

Finally the List is read and created into a String which is then placed in the WHERE clause.

Would that idea work in your Class?

 

[margret]

Thank you for your information and it sounds great and I am sure it works great. In the class code Its not what I need. This class is a programming tool and is used by the programmer and not the end user. What you have listed is Great for the end user and a great idea. We are at the stage we are writing the Where clause for our code.

Thank You