Android Question Advanced Search - help needed

[tsteward]

I have a local database that I want to search. At the moment I have one column named series. I can change this to two columns if need be. EG Min & Max but I don't think its that straight forward. Some have no letters and others have letter only or a mix of both.
Below is a sample.

When I enter say H2312 I want to return all records where that might be possible.
And another for line 16 below 0L213

Any suggestions on how this might be done or the best way to do it?

Spoiler: Table

Series

H0001-H3000

X8001-X9000

S0001-S2000

X0001-X8000

G0001-G2377

P0001-P2390

X0001-X2248

W0001-W2409

10001-15000

J0001-J1200

0001X-1706X

T0001-T1000

1-22185

3001-4481

E5001-E7700

0K001-0N718

8001-9554

V2001-V3200

70000-75928

J00-U39

E0001-E2500

G0001-G2500

HK0001-HK2500

J0001-J2500

K0001-K2500

KE0001-KE2500

L0001-L1037

P0001-P2500

T0001-T2500

V0001-V2500

LK0001-LK2500

40000-49999

O0001-O5000

D0001-D3000

M0001-M2618

VN0-VN1999

50000-69999

S4001-S5200

S5298-S6300

G8001-G9000

B8001-B9000

B8001-B9000

K0001-K1000

10100-12283

20100-23284

K0001-K1000

N225-N814

U1-U2000

FA0-FA1863

T1-3456

T1-26789

T5-3456

T1-T1200

Y2001-Y3000

7001-9000

E2001-E3000

E4001-E5000

E5000-E5999

E6001-E7000

E8000-E8999

L8001-L10000

NSP4000-NSP4999

NSP7001-NSP9000

G000-G3631

S000A-S999K

30010-32009

C8001-C9000

Y7001-Y8200

30001-37850

40001-41518

6001-7001

A6001-A7000

D0001-D2000

D4001-D5200

1HM1-99HM99

1NF1-99NF99

HM1-HM1200

HM2501-HM3500

HM4001-HM7350

HM6001-HM7110

NF1-NF1200

NM2501-NM3500

5001-8442

M0001-M1200

3001-4000

6001-7000

AB6001-AB6891

N6001-N7000

V1-V3056

V5001-V8058

W1-W9640

1V00001-5V12295

L000-L999

B1001-B2200 New

B1001-B2200 Old

B5001-B6200

B5001-B6200

HB1-HB2988

HB3001-HB5000

T1-T2330

HV1-HV854

HV1001-HV1918

HV2001-HV2830

 

[Sandman]

No offense, but your table and data seems to be very poorly designed. I was about to suggest to change it to this, to improve it and make life easier:

Start prefix	Start suffix	End prefix	End suffix	Start	End	(Used to be represented as)
H		H		0001	3000	H0001-H3000
X		X		8001	9000	X8001-X9000
	X		X	0001	1706	0001X-1706X
				1	22185	1-22185
B		B	New	1001	2200	B1001-B2200 New

But then I realized that you wanted OL213 to find the row 0K001-0N718, so not even my design above would fully help.

 

[tsteward]

This is a database that sort of evolved it wasn't designed as such.
I am quite happy to re-design it.

 

[Sandman]

In that case perhaps something like what I did above could help. The simplest way to solve the case of OL213 would to split 0K001-0N718 like so:

0K001-0K999
0L001-0L999
0M001-0M999
0N001-0N718

...which might not be possible, of course. I'm just splitting it in a stupid problem-solving way that might not correctly represent the reality behind the data.

 

[emexes]

The simplest way to solve the case of OL213 would to split 0K001-0N718 like so:

but you are still left with the problem that the leading O is actually 0, and so there are potentially four sub-fields to contend with:

optional number (which can also be a range, eg 1V00001-5V12295)
optional alpha
number
optional alpha (New, Old)

plus also: are leading zeroes meaningful, eg is V0017 the same as V17, and should it match V1-V3056 ?

I am feeling like a solution is to reformat the series values into fixed-length alternating alpha/numeric subfields long enough to handle the longest alpha/numeric run, eg if we know that no group of letters or digits will be more than 6 long then:

aaaaaa nnnnnn aaaaaa nnnnnn aaaaaa nnnnnn
------ -----0 L----- ---213 ................. <-- the 0L213 series being searched for
------ -----0 K----- ---001 ................. <-- the low end of the 0K001-0N718 range
------ -----0 N----- ---718 ................. <-- the high end of the 0K001-0N718 range

but that would still return that 0K001-0N718 range as a match for 0L913 even though the 913 is outside of 001..718

so you'd have to check the search value OL913 against each returned range subfield-by-subfield

 

[Sandman]

but you are still left with the problem that the leading O is actually 0,

Yeah, I simply considered the prefix to be 0K, 0L, 0M and 0N. I didn't see the value 1V00001-5V12295.

This is sort of a guessing-game at the moment. We don't know what the values represent, and as such we don't know what they can be. To get better help from me, @tsteward would have to share some domain knowledge.

 

[emexes]

Any suggestions on how this might be done or the best way to do it?

How many rows is that table ever plausibly going to grow to?

Is the database local (on the device) or remote (across a network)?

I am starting to think that the easiest and simplest solution will be to scan all rows and compare their ranges to the series value you're searching for.

If the series ranges don't change very often, then perhaps that column could be read once at startup and held in memory.

 

[William Lancee]

I am a little late to this discussion, but I would convert from/to values to base 37, sort, and use a binary search.
Note the unexpected find, which could be valid or invalid depending on the context..

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")

'    Type sortPair(sortfield As Long, fromvalue As String, tovalue As String)
    Dim series As List = File.ReadList(File.DirApp, "xseries.txt")
    Dim sortList As List
    sortList.Initialize
    For Each s As String In series
        Dim v() As String = Regex.Split("-", s)
        sortList.add(CreatesortPair(convert(v(0)), v(0), v(1)))
    Next
    sortList.SortType("sortfield", True)
   
    Dim found As List = findAll("H2312", sortList)
    For Each s As String In found
        Log(s)
    Next
'    H2312    H0001-H3000

    Dim found As List = findAll("0L213", sortList)
    For Each s As String In found
        Log(s)
    Next
'    0L213    0K001-0N718
'    0L213    0001X-1706X

End Sub

Private Sub convert(s As String) As Long
    'convert to base 37
    Dim charset As String = ".0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim resultN As Long
    Dim multiplier As Long = 1
    For i = s.Length - 1 To 0 Step -1
        resultN = resultN + multiplier * charset.indexOf(s.CharAt(i))
        multiplier = charset.Length * multiplier
    Next
    Return resultN
End Sub

Private Sub findAll(s As String, sortlist As List) As List
    Dim found As List
    found.Initialize
    Dim lookfor As Long = convert(s)

    'find first item that has sequence number > lookfor
    'for simplicity I am doing a linear seach, but a binary search would be appropriate here
    For i = 0 To sortlist.Size - 1
        Dim sp As sortPair = sortlist.Get(i)
        If sp.sortfield > lookfor Then Exit            'gone too far
    Next
   
    'working backwards from "too far"
    For j = i - 1 To i - 10 Step -1
        Dim sp As sortPair = sortlist.Get(j)
        If lookfor > convert(sp.tovalue) Then Exit
        found.Add(s & TAB & sp.fromvalue & "-" & sp.toValue)
    Next
    Return found
End Sub

 

[tsteward]

Alright given a few brains are interesting in helping find a solution I will draft up a better post on what is needed and what is local and what is on a server when I get home tonight.

THANK YOU for your interest. I am not capable of this.

 

[William Lancee]

And I might not be either, since I spotted a logical flaw in the code above.
Explanation:

If sorted by from value then can eliminate all that are > lookup
now look for 3
0-5
0-99
1-10
1-2
5-10
5-20

NOT (they start with a range > 3)
5-10
5-20

BUT also not 1-2 (ends with a range < 3)

Valid finds:
0-5
0-99
1-10

Therefore I can't exit the reverse loop until the first item is reached, turning it into a linear search.
Back to the drawing board.

 

[emexes]

And I might not be either, since I spotted a logical flaw in the code above.

I liked that you gave it a red-hot go anyway, and tbh when you mentioned base-37 I momentarily thought: heck, that might actually work

Back to the drawing board.

with the rest of us, so you're in good company

 

[William Lancee]

Only the OP knows, but if the items are Part numbers of some kind, then they constitute a finite set.
Even if there were 100,000 distinct items, a B4X Map could index them into the ranges that are in #1.
This would provide simple and really fast lookups. It would also be simple to add new items to the index.

 

[emexes]

Even if there were 100,000 distinct items, a B4X Map could index them into the ranges that are in #1.

made slightly more complicated by maps needing unique keys vs sample data having ranges that overlap eg:

E0001-E2500 and E2001-E3000
G0001-G2377 and G0001-G2500
HM4001-HM7350 and HM6000-HM7110
W1-W9640 and W0001-W2409
X0001-X8000 and X0001-X2248

but once we know what the desired end result is, we can have another tilt at the challenge.

 

[emexes]

Only the OP knows

+1

They look like electronic components to me, except that those tend to have more variation.

They could be plots in cemeteries, although then I would expect more overlap in the letters.

 

[William Lancee]

Brute force could loop through all the ranges, enumerating all the items and for each item add the range to the map value (a list).
A rough estimate: 200 x 2000 items? It would not be pretty but it could work.

 

[emexes]

Brute force could loop through all the ranges, enumerating all the items

A key factor being whether eg J00-U39 includes J40-T99, or 1V00001-5V12295 includes 1V12296 to 5V00000

as in there are three "obvious" ways of enumerating the items, eg for 1V00001-5V12295, the incremented character groupings could be:

[1-5] [V-V] [0-1] [0-2] [0-2] [0-9] [1-5]

[1-5] [V-V] [00001-12295]

[1V00001-5V12295]

 

[tsteward]

Okay so I am a Locksmith.

What you saw in the table in the original post is an index of code series.

The index is in a local sql file on the device. Each series is a record and it links to a table in an online sql file. The online table holds every possible code in the first field and the matching bitting in the second field. (bitting is the shape or pattern of the key cuts)

At this stage there are about 245 code tables/records in the local DB I don’t expect more than a 30% increase on this size.

Here is a little screen video that may help understand LINK

Attached is entire code series list

"J00-U39 includes J40-T99" Yes it does
"1V00001-5V12295 includes 1V12296 to 5V00000" Yes
"E0001-E2500 and E2001-E3000" Two different code series so searching for E2122 should return both
"G0001-G2377 and G0001-G2500" Two different code series so searching for G0122 should return both
"HM4001-HM7350 and HM6000-HM7110" Two different code series
"W1-W9640 and W0001-W2409" I need to check this may be an error
"X0001-X8000 and X0001-X2248" Two different code series

 

[Sandman]

Okay so I am a Locksmith.

Hey, I know what that is! (He jokingly said after watching many of LPL's videos over the years).

Seriously though, I think I need to better understand the different use-cases because I can't really follow yet. And please, explain like I'm five.

Just so you understand how far away I am in my understanding, this is what I got at the moment:

1. For an unknown reason
2. a person (perhaps locksmith?)
3. wants to look up a value ("H2312")
4. on their mobile
5. and see the bitting for the value
6. so they can do something (make a copy of key perhaps?)

 

[tsteward]

Hey, I know what that is! (He jokingly said after watching many of LPL's videos over the years).

Seriously though, I think I need to better understand the different use-cases because I can't really follow yet. And please, explain like I'm five.

Just so you understand how far away I am in my understanding, this is what I got at the moment:

1. For an unknown reason
2. a person (perhaps locksmith?)
3. wants to look up a value ("H2312")
4. on their mobile
5. and see the bitting for the value
6. so they can do something (make a copy of key perhaps?)

Yes as locksmiths we receive a code from the vehicle dealer or it may be stamped on the lock H2312. Once we have this code we can get the bitting. we have the shape of the key and using various methods we can cut the correct key.

 

[William Lancee]

In that case, the solution is simple. It takes only a millisecond or so to do a linear search through the series ranges.
For a locksmith that should be fast enough to look up a bitting table.

Complete lookup code.

Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    Dim seriesList As List = File.ReadList(File.DirApp, "codeseries.txt")
    Dim markTime As Long = DateTime.now
    Log(BittingTables("H2312", seriesList))         'ignore
    Log(BittingTables("X1234", seriesList))         'ignore
    Log(BittingTables("25AE579", seriesList))         'ignore
    Log(DateTime.now - markTime)                    ' 3 milliseconds
End Sub

Private Sub BittingTables(KeyCode As String, seriesList As List) As List
    Dim result As List
    result.Initialize
    For Each s As String In seriesList
        Dim v() As String = Regex.Split("-", s)
        If KeyCode.CompareTo(v(0)) >=0 And KeyCode.CompareTo(v(1)) <=0 Then result.Add(s)
    Next
    'there could be duplicates series (old and new) they are marked with *
    result.Sort(False)
    For i = 1 To result.Size - 1
        Dim t As String = result.get(i)
        If t = result.Get(i-1) Then result.Set(i, t & "*")
    Next
    Return result
End Sub