[sqlite] fix rowid's of fts table

[Clemens Ladisch]

Rael Bauer wrote:
> Now I have restructured the normal table (i.e. delete column) using the
> algorithm mentioned here a couple of times (insert into normal select
> (columns) from normal_OLD, etc..)
>
> This "resets" the rowid's of the normal table.

If you rely on the rowid values, you should declare this column as
INTEGER PRIMARY KEY to prevent such changes.


Regards,
Clemens

[sqlite] fix rowid's of fts table

[Rael Bauer]

Hi,

I have a normal table, and a fts table that are joined by rowid's. E.g.
when I add a row to the normal table, I add some values into the fts
table as well, and set the rowid based on the normal table rowid (as
recommended).

Now I have restructured the normal table (i.e. delete column) using the
algorithm mentioned here a couple of times (insert into normal select
(columns) from normal_OLD, etc..)

This "resets" the rowid's of the normal table.
E.g. if the table started with rowid's like:
1
2
6
7
10

after restructuring they will be:
1
2
3
4
5

So the fts table is now out of sync with the normal table. Do i have to
completely re-populate the fts table or is there a simpler way to
"reset" the fts rowids?

(If I tried to just reset the rowid's on the "main" fts table I imagine
this would not work, since there are the aux fts tables as well..)

Thanks
Rael

[sqlite] fix rowid's of fts table

[Simon Davies]

On 12 March 2015 at 20:20, Rael Bauer  wrote:
>
> Hi,
>
. 
. 
. 
> Now I have restructured the normal table (i.e. delete column) using the
> algorithm mentioned here a couple of times (insert into normal select
> (columns) from normal_OLD, etc..)
>
> This "resets" the rowid's of the normal table.
> E.g. if the table started with rowid's like:
> 1
> 2
> 6
> 7
> 10
>
> after restructuring they will be:
> 1
> 2
> 3
> 4
> 5
>
> So the fts table is now out of sync with the normal table. Do i have to
> completely re-populate the fts table or is there a simpler way to
> "reset" the fts rowids?

Propagate the rowids from the old table when populating the new table:

INSERT INTO nTable( rowid, data1, ... ) SELECT rowid, data1, ... FROM oTable;

>
> (If I tried to just reset the rowid's on the "main" fts table I imagine
> this would not work, since there are the aux fts tables as well..)
>
> Thanks
> Rael

Regards,
Simon