1749509 - Create dbml descriptor files and test the databases

Status: NEW

(Toolkit :: Places, task, P3)

Description

[Marco Bonardo [:mak]]

We don't have a comprehensive test checking the database is in the expected shape, instead we rely on testing specific fields during upgrades.
That is not robust enough as shown by bug 1748509.

What we should do is create places.dbml and favicons.dbml, then have tests going through these and check the database is in sync with its description.
Those files will also allow us to more easily export to tools like dbdiagram.io and supports comments and notes that will make it easier for outsiders to understand it.

Comment 1

[Marco Bonardo [:mak]]

a part of the db is already written at https://dbdiagram.io/d/60b62bceb29a09603d177750
There's also a parser at https://www.dbml.org/js-module/ but it's npm, thus I'm not sure if we can reuse it.

Comment 2

[Malik Kenendy]

Attachment: DBML for few remaining tables in places.sqlite

```

```

Comment 3

[Malik Kenendy]

Here's my attempt at finishing the places.dbml file.

Table moz_items_annos as itan {
  id INTEGER [pk]
  item_id INTEGER [not null]
  anno_attribute_id INTEGER
  content LONGVARCHAR
  flags INTEGER [default: 0]
  expiration INTEGER [default: 0]
  type INTEGER [default: 0]
  dateAdded INTEGER [default: 0]
  lastModified INTEGER [default: 0]
  Indexes {
    (item_id, anno_attribute_id) [name: 'moz_items_annos_itemattributeindex']
  }
}

Table moz_annos as ann {
  id INTEGER [pk]
  place_id INTEGER [ref: > ann.id, not null]
  anno_attribute_id INTEGER
  content LONGVARCHAR
  flags INTEGER [default: 0]
  expiration INTEGER [default: 0]
  type INTEGER [default: 0]
  dateAdded INTEGER [default: 0]
  lastModified INTEGER [default: 0]
  Indexes {
    (place_id, anno_attribute_id) [name:'moz_annos_placeattributeindex', unique]
  }
}

Table moz_previews_tombstones as ts {
  hash TEXT [pk]
}

Table moz_anno_attributes as attr {
  id INTEGER [pk]
  name VARCHAR(32) [unique, not null]
}

Table moz_bookmarks as b {
  id INTEGER [pk]
  type INTEGER
  fk INTEGER [default: null]
  parent INTEGER
  position INTEGER
  title LONGVARCHAR
  keyword_id INTEGER
  folder_type TEXT
  dateAdded INTEGER
  lastModified INTEGER
  guid TEXT
  syncStatus INTEGER [not null, default: 0]
  syncChangeCounter INTEGER [not null, default: 1]
  Indexes {
    (fk, type) [name: 'moz_bookmarks_itemindex']
    (parent, position) [name: 'moz_bookmarks_parentindex']
    (fk, lastModified) [name: 'moz_bookmarks_itemlastmodifiedindex']
    (dateAdded) [name: 'moz_bookmarks_dateaddedindex']
    (guid) [name:'moz_bookmarks_guid_uniqueindex', unique]
  }
}

Table moz_bookmarks_deleted as bd {
  id INTEGER [pk]
  item_id INTEGER [not null]
  anno_attribute_id INTEGER
  cintent LONGVARCHAR
  flags INTEGER [default: 0]
  expiration INTEGER [default: 0]
  type INTEGER [default: 0]
  dateAdded INTEGER [default: 0]
  lastModified INTEGER [default: 0]
}

Table moz_keywords as kw {
  id INTEGER [pk]
  keyword TEXT [unique]
  place_id INTEGER
  post_data TEXT
  Indexes {
    (place_id, post_data) [name:'moz_keywords_placepostdata_uniqueindex', unique]
  }
}

Table moz_meta as meta {
  key TEXT [pk]
  value BLOB [not null]
}

I'd like to continue working on this. It looks like next I would create a favicon.dbml similar to above, but I dont know what testing to keep database in sync would look like.