977151 - use WITHOUT ROWID in annotation tables

Status: RESOLVED INACTIVE

(Toolkit :: Places, defect, P5)

Description

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

This is not backwards compatible cause while recreating the table I'm removing the obsolete columns.


ALTER TABLE moz_annos RENAME TO moz_annos_old

CREATE TABLE moz_annos (
place_id INTEGER NOT NULL,
anno_attribute_id INTEGER NOT NULL,
content TEXT,
expiration INTEGER DEFAULT 0,
type INTEGER DEFAULT 0,
dateAdded INTEGER DEFAULT 0,
lastModified INTEGER DEFAULT 0,
PRIMARY KEY (place_id, anno_attribute_id)
) WITHOUT ROWID

INSERT INTO moz_annos SELECT place_id, anno_attribute_id, content, expiration, type, dateAdded, lastModified FROM moz_annos_old

DROP INDEX moz_annos_placeattributeindex

DROP TABLE moz_annos_old

------------

ALTER TABLE moz_items_annos RENAME TO moz_items_annos_old

CREATE TABLE moz_items_annos (
item_id INTEGER NOT NULL,
anno_attribute_id INTEGER NOT NULL,
content TEXT,
expiration INTEGER DEFAULT 0,
type INTEGER DEFAULT 0,
dateAdded INTEGER DEFAULT 0,
lastModified INTEGER DEFAULT 0,
PRIMARY KEY (item_id, anno_attribute_id)
) WITHOUT ROWID

INSERT INTO moz_items_annos
SELECT item_id, anno_attribute_id, content, expiration, type, dateAdded, lastModified FROM moz_items_annos_old

DROP INDEX moz_items_annos_itemattributeindex

DROP TABLE moz_items_annos_old

Comment 1

[Althaf Hameez [:ahameez]]

Attachment: bug977151_use_without_rowid_annotation_table.diff WIP

So this patch took waaaay longer than expected!
Just need to fix some of the SQL queries I think.

The main queries being

>  QUERY_EXPIRE_ANNOS: {
>    sql: "DELETE FROM moz_annos WHERE id in ( "
>       +   "SELECT a.id FROM moz_annos a "
>       +   "LEFT JOIN moz_places h ON a.place_id = h.id "
>       +   "WHERE h.id IS NULL "
>       +   "LIMIT :limit_annos "
>       + ")",

>  QUERY_EXPIRE_ITEMS_ANNOS: {
>    sql: "DELETE FROM moz_items_annos WHERE id IN ( "
>       +   "SELECT a.id FROM moz_items_annos a "
>       +   "LEFT JOIN moz_bookmarks b ON a.item_id = b.id "
>       +   "WHERE b.id IS NULL "
>       +   "LIMIT :limit_annos "
>       + ")",

> let deleteInvalidAttributeAnnos = DBConn.createAsyncStatement(
>      "DELETE FROM moz_annos WHERE place_id IN ( " +
>        "SELECT place_id FROM moz_annos a " +
>        "WHERE NOT EXISTS " +
>          "(SELECT id FROM moz_anno_attributes " +
>            "WHERE id = a.anno_attribute_id LIMIT 1) " +
>      ")");

>    let deleteOrphanAnnos = DBConn.createAsyncStatement(
>      "DELETE FROM moz_annos WHERE id IN ( " +
>        "SELECT place_id FROM moz_annos a " +
>        "WHERE NOT EXISTS " +
>          "(SELECT id FROM moz_places WHERE id = a.place_id LIMIT 1) " +
>      ")");

>    let deleteInvalidAttributeItemsAnnos = DBConn.createAsyncStatement(
>      "DELETE FROM moz_items_annos WHERE id IN ( " +
>        "SELECT id FROM moz_items_annos t " +
>        "WHERE NOT EXISTS " +
>          "(SELECT id FROM moz_anno_attributes " +
>            "WHERE id = t.anno_attribute_id LIMIT 1) " +
>      ")");

>    let deleteOrphanItemsAnnos = DBConn.createAsyncStatement(
>      "DELETE FROM moz_items_annos WHERE id IN ( " +
>        "SELECT id FROM moz_items_annos t " +
>        "WHERE NOT EXISTS " +
>          "(SELECT id FROM moz_bookmarks WHERE id = t.item_id LIMIT 1) " +
>      ")");

> rv = mDB->MainConn()->ExecuteSimpleSQL(
>    NS_LITERAL_CSTRING(
>      "DELETE FROM moz_items_annos "
>      "WHERE id IN ("
>        "SELECT a.id from moz_items_annos a "
>        "LEFT JOIN moz_bookmarks b ON a.item_id = b.id "
>        "WHERE b.id ISNULL)"));
>  NS_ENSURE_SUCCESS(rv, rv);

Comment 2

[Althaf Hameez [:ahameez]]

Currently 22 failing tests in places/tests + errors in getPageAnnotation and getItemAnnotation

Comment 3

[BMO Automation]

Per policy at https://wiki.mozilla.org/Bug_Triage/Projects/Bug_Handling/Bug_Husbandry#Inactive_Bugs. If this bug is not an enhancement request or a bug not present in a supported release of Firefox, then it may be reopened.