On 7/3/2015 8:39 AM, Rob Willett wrote:
> Simon,
>
> We had missed the incorrect defn of Calculate in the index. We?ve been
> changing around stuff and suspect we omitted to check as carefully as we we
> should when changing :(
>
> We?ll also update BayesAttribute as well.
>
> We?re actually rebuilding the database from scratch anyway, it?ll take around
> 3-4 days now as opposed to 12-16 days as before :)
>
> We?ll check if we get any improvements from rebuilding using .dump by copying
> the database to another server and doing it there. Thats one of the great
> things about SQLite, pick the database up and copy it somewhere else :)
>
> is Vacuum the same as doing the .dump and restore or is it different? We like
> the .dump as it gives us a nice easy to use backup :)
>
> Rob
>
>> On 3 Jul 2015, at 13:15, Simon Slavin <slavins at bigfraud.org> wrote:
>>
>>
>> On 3 Jul 2015, at 11:35am, Rob Willett <rob.sqlite at robertwillett.com>
>> wrote:
>>
>>> CREATE TABLE "RAG" (
>>> "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>>> "Count" integer NOT NULL DEFAULT 0,
>>> "Text" TEXT COLLATE NOCASE ,
>>> "Peak" integer,
>>> "Calculation" integer NOT NULL DEFAULT 0,
>>> "Red" integer DEFAULT 0,
>>> "Amber" integer DEFAULT 0,
>>> "Green" integer DEFAULT 0,
>>> "BayesAttributes" TEXT
>>> );
>> [...]
>>
>>> CREATE UNIQUE INDEX "RAG_Idx1" ON RAG ("Text" COLLATE NOCASE ASC);
>>> CREATE UNIQUE INDEX "RAG_Idx2" ON RAG ("Calculation" COLLATE NOCASE ASC,
>>> "Text" COLLATE NOCASE ASC);
>>> CREATE INDEX "Rag_Idx3" ON RAG ("Calculation" COLLATE NOCASE ASC,
>>> "BayesAttributes" COLLATE NOCASE ASC);
>> To explain further, now that you have declared your "Text" column as being
>> COLLATE NOCASE, NOCASE is now the default collation for it, and all sorting
>> and matching on Text will be NOCASE unless you state otherwise. So it's not
>> necessary to state COLLATE NOCASE when you create indexes on it or mention
>> it elsewhere. It won't do any harm, though. Shout out to Clemens for his
>> corrections to my earlier post.
Clearly, in this case, using COLLATE NOCASE in the table
definition is the right thing to do. Under what conditions
would using it in the index instead be the right
thing to do?
-Bill
