How to format sqlite query properly?

Solar2D Lua Language
#1

Just a couple of questions…

Based on this string below we can easily see that we can make a bunch of words, how do I query a my dictionary for what words can be created from the string?

Obviously this string wouldn’t return anything because I just query if my string is within the dictionary. How do I format my sql query?

[lua]

local letters = “abcdeaowhtgillx”

local sql = “SELECT *FROM WORDS WHERE word LIKE’”…letters…"’ "

local myWords = {}

for row in db:nrows(sql) do

    if row.word == nil then

        print(" NO WORDS FOUND ")

     else

         print(" FOUND WORDS:  ", row.word)

         myWords[#myWords + 1] = row.word

     end

end

[/lua] 

I understand how to find if a word is in my dictionary, however I see there are more than one way of doing it with sqlite, what is the best way to query the database?

One way…

[lua]

local theWord = “hammer”

local sql = “SELECT * FROM WORDS WHERE word = '” … theWord… "’ "

[/lua]

Another way…

[lua]

local theWord = “hammer”

local sql = “SELECT * FROM WORDS WHERE word LIKE '” … theWord… "’ "

[/lua]

#2

You could use REGEXP in your first query. You will have to figure out the correct regular expression to use. Its possible REGEXP is not in the version Corona uses. In any case using SQLite for this is probably not a good strategy.  Instead have all of your words in memory and choose from them that way.

For example keep them all in a table, then loop through them with the appropriate regular expression and find possible matches.

Question 2: LIKE should be used when you do not know the exact match and can be used with a wildcard. If you are not using a wildcard you should not use LIKE http://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm

#3

You can also open an SQLite db in memory as an option.

Check out the first example here http://docs.coronalabs.com/daily/api/library/sqlite3/index.html#examples

#4

I just wanna shuffle the contents around of a string or array of letters to see how many correct words I would get from it, sort of like a wordfinder app. Maybe its a bit too complicated to pull off?

#5

Start small. Example, make something that returns true or false if the letters t,a,c can make the word “cat”

#6

You could use REGEXP in your first query. You will have to figure out the correct regular expression to use. Its possible REGEXP is not in the version Corona uses. In any case using SQLite for this is probably not a good strategy.  Instead have all of your words in memory and choose from them that way.

For example keep them all in a table, then loop through them with the appropriate regular expression and find possible matches.

Question 2: LIKE should be used when you do not know the exact match and can be used with a wildcard. If you are not using a wildcard you should not use LIKE http://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm

#7

You can also open an SQLite db in memory as an option.

Check out the first example here http://docs.coronalabs.com/daily/api/library/sqlite3/index.html#examples

#8

I just wanna shuffle the contents around of a string or array of letters to see how many correct words I would get from it, sort of like a wordfinder app. Maybe its a bit too complicated to pull off?

#9

Start small. Example, make something that returns true or false if the letters t,a,c can make the word “cat”