CSqlite search.

[TheVig]

This example uses spelling checker with aspell.
See forum post http://www.quickmacros.com/forum/showthr...p?tid=2962 for spell checker instructions

Code:

Copy      Help

out
#compile CSqlite

str dbfile="$desktop$\searchDB.db3"
str sql

;create database for testing
CSqlite db1.Open(dbfile)
db1.Exec("DROP TABLE table1")
db1.Exec("CREATE TABLE 'table1' (A,B)")

db1.Exec("INSERT INTO table1 VALUES ('Rock and Roll','Data for Rock and Roll')")
db1.Exec("INSERT INTO table1 VALUES ('Rock','Data for Rock')")
db1.Exec("INSERT INTO table1 VALUES ('Jazz','Data for Jazz')")
db1.Exec("INSERT INTO table1 VALUES ('Funk','five')")
db1.Exec("INSERT INTO table1 VALUES ('Folk','five')")
db1.Exec("INSERT INTO table1 VALUES ('Electronica','five')")
db1.Exec("INSERT INTO table1 VALUES ('Rap','five')")
db1.Exec("INSERT INTO table1 VALUES ('Country','five')")
db1.Exec("INSERT INTO table1 VALUES ('Indie','five')")
db1.Exec("INSERT INTO table1 VALUES ('Hip Hop','five')")

CSqlite db2.Open(dbfile)

out
str w
w="Hip Hop"
int lw=len(w)
;out lw

#compile Caspell
Caspell k.Init
ARRAY(str) a
k.Suggest(w a)
str Suggestions=a
;out a
ARRAY(str) arr
int i nt
nt = tok(Suggestions arr -1 "[]")
for(i 0 nt)
,;out arr[i]
,;out "---"
,_s=""
,ARRAY(str) b.create(arr[i].len)
,for(_i 0 a.len)
,,a[_i].get(arr[i] _i 1)
,,_s.format("%s%s%%" _s a[_i])
,,_s.findreplace("'")
,,;out _s
,,str SearchWord=_s
,str sqlSearchWord.format("SELECT * FROM 'table1' WHERE A LIKE '%s'" SearchWord)
,ARRAY(str) ar; int r c
,db2.Exec(sqlSearchWord ar)
;,out sqlSearchWord
,int x=-1
,for r 0 ar.len(2)
,,for c 0 ar.len(1)
,,,out ar[c r]

This is starting to work pretty good.
I feel it might be a little to lenient when it comes to matches...but its a start.

Two problems I haven't figured out that maybe someone will see the solutions.
1. Duplicate matches...using wild cards the way I am it allows for suggestions in the spell checker to hit shorter versions of longer words which outs the entry for each match. I can't figure out how to flag an entry as being matched and to skip that on future suggestions.
2. Obviously too long match. Like Cop will match country in the example. The lengths are so obviously different.

I'm gonna code around a bit more and try to develop other bugs that will need solutions. If any one has other ideas of how to maximize searching of databases in any form, I would love to hear what you have to suggest!

I was thinking of taking words that have matches then evaluating them with a match rating program...then run the top word through the data base looking for an exact match...and so forth. If an exact match is not found...then "No Matches"

I'm really curious to see what other people might have to add to this. Could be very useful to a lot of programming...I think?

Thanks,
Jimmy Vig