SQLite is case-sensitive by default. That’s something I had forgotten. With regards to yesterday’s post, I had originally planned on using a list of integers which were my primary indices in the SQLite table. Later, the customer preferred using the alphanumeric ‘name’ for the list items. This inevitably led to excluding those items in the table that had different casing.

To solve, I could have modified the PHP and the SQLite WHERE clause to be something like

$aUpperCasedFileData = array_map(function($v) { return trim(strtoupper($v));}, $aFiledata);
$name_csv = "'".join("','",$aUpperCasedFileData)."'";
$query = 'SELECT key,name,description,value,timestamp FROM mytable WHERE UPPER(name) IN ('.$name_csv.');

but in the end I decided to recreate the table using COLLATE NOCASE on the ‘name’ column. I did this by yanking the original CREATE statement to create a new table.

CREATE TABLE mytable2 (name VARCHAR(50) COLLATE NOCASE, key INTEGER PRIMARY KEY, description VARCHAR(80), value VARCHAR(255), timestamp VARCHAR(20));
INSERT INTO mytable2 SELECT * FROM mytable;
DROP TABLE mytable;
ALTER TABLE mytable2 RENAME TO mytable;

And then the to speed up queries against name, add an index:

CREATE INDEX ix_name_mytable ON mytable(name COLLATE NOCASE);

Now not only did I remove case sensitivity, but significantly sped up the search by building the appropriate index.