On Tuesday, February 1, 2011 at 10:30 AM, Ian Hardingham wrote: > Hi Igor, thankyou. > > If I wish to make this modification now, what steps would I need to > take? And in your opinion what % of the optimisation of doing it with > integers would this provide? > ALTER TABLE MyTable RENAME TO MyTable_orig;
CREATE TABLE MyTable ( id INDEX PRIMARY KEY, username TEXT COLLATE NOCASE, .. rest of schema from MyTable .. ); INSERT INTO MyTable (username, .. other old columns ..) SELECT username, .. other old columns .. FROM MyTable_orig; The above commands will preserve your existing MyTable, and also create a new table called MyTable_new that will have an integer primary key, and will also have a username column that doesn't care about case. Then, start refactoring your code one step at a time. Resist the urge to make mass modifications. Don't try fancy tricks with grep and regexp replace. Every time you make a mod, test it. Oh, and use a version control system for your code. Your db will be safe because your original table will be untouched. When you are completely satisfied, DROP TABLE MyTable_orig. > Thanks, > Ian > > On 01/02/2011 16:19, Igor Tandetnik wrote: > > On 2/1/2011 10:10 AM, Ian Hardingham wrote: > > > My core users table has a user defined by a string which is their name. > > > This string is used to address many other tables relating to users. Not > > > only is the primary key a string (which I understand is bad enough), but > > > I also have to use LIKE rather than = because the high level language I > > > use is a bit eccentric about case-ing. > > You could have created your table like this: > > > > create table MyTable(username text primary key collate NOCASE, ...); > > > > Then, plain vanilla = comparison would be case-insensitive (for latin > > characters A-Z and a-z only, but then LIKE has the same limitation), and > > would use the index. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
