SQL optimization

[melamoud]

hi all

I need to build a key to a table that is not depend on case of the string
so a line with value "ABC" and a line with value "abc" can not exist

I tried to do something like this
'SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS alerts1 (artist TEXT NOT NULL , desc TEXT NOT NULL , type TEXT NOT NULL, value TEXT NOT NULL, status TEXT NOT NULL, CONSTRAINT pk_ID PRIMARY KEY (lower(artist),lower(value)))")

but it wont work,
I know I can duplicate the data hold one field with the original case and one with the toLowerCase of it and use the second as a key

any other option ?

 

[Erel]

You can only store the lower case version and then lower case the string that you are searching for (either in SQL or Basic4android).

 

[melamoud]

You can only store the lower case version and then lower case the string that you are searching for (either in SQL or Basic4android).

I need to store both the original and lower case (and trying to find a way to avoid it) because I need the original case to present to the user

 

[Erel]

See this page: How to set Sqlite3 to be case insensitive when string comparing? - Stack Overflow

 

[melamoud]

See this page: How to set Sqlite3 to be case insensitive when string comparing? - Stack Overflow

thanks worked like a charm! now allI have to do is restructure al;l my tables, all my queries and test for integrity and performance again :sign0098: