1556009 - Use new window functions to provide better position fixing in PlacesDBUtils

Status: RESOLVED FIXED

(Toolkit :: Places, task, P2)

Description

[Mark Banner (:standard8)]

From the review comments in bug 1540669:

For PlacesDBUtils we could probably use PARTITION BY to update ALL the bookmarks:

WITH positions(id, pos, seq) AS (
SELECT id, position AS pos,(row_number() OVER (PARTITION BY parent ORDER BY position)) - 1 AS seq
FROM moz_bookmarks
)
UPDATE moz_bookmarks
SET position = (SELECT seq FROM positions WHERE positions.id = moz_bookmarks.id)
WHERE id IN ( SELECT id FROM positions WHERE seq <> pos);

Just note that using the row_number() window function we can likely fix all of the indexing problems we have around.

Comment 1

[Marco Bonardo [:mak]]

Attachment: Bug 1556009 - Use a window function and update-from to fix bookmark indices. r=scunnane!

Comment 2

[Pulsebot]

Pushed by [e-mail]:
https://hg.mozilla.org/integration/autoland/rev/3a3942cde8aa
Use a window function and update-from to fix bookmark indices. r=scunnane

Comment 3

[Marian-Vasile Laza]

https://hg.mozilla.org/mozilla-central/rev/3a3942cde8aa