History sync schema

gistfile1.txt

Things we need to be able to do:

* Record visits for places that are already synced, distinguishing local and
  remote visits for top sites, but able to merge them to upload a remote record.
* Record visits for new places that haven't been synced down.
* Merge locally created places with incoming places.
* View places that have only been visited remotely.
* Handle deletions of individual visits. (E.g., clear recent history.)
* Handle deletions of entire places.
  - It's not clear exactly what this entails. If one deletes every visit
    locally, but another device has synced a new visit in the mean time, do we:
    1. Throw away our deletion, resetting to whatever the other device just
       uploaded? This is what existing Sync clients do, but we'd have to pay
       special attention to remembering deleted visits.
    2. Throw away the server record? This doesn't necessarily make sense -- you
       might clear your phone and turn it off, then do a bunch of browsing on your
       desktop; when you sync the phone a week later, its deletions will affect
       the 'future'.
    3. Attempt to do some kind of intelligent merge?
  - Similarly, if a remote record is deleted at time T, but we visited the site
    at time T+N, do we throw away our unsynced local visits?
    - If we preserve local visits, we should generate a new GUID for the place.

Concerns:

* We would like local visits to be recorded with a single INSERT. We would like
  top sites/awesomebar queries to use nothing more painful than a UNION ALL.
* Note that visits refer to a place by some identifier. In theory, that
  identifier is the URL -- the unique, stable identity of the thing you visited.
  In practice, we need something more efficient, so we use a numeric identifier.
  This is the place ID. This implies that a URL has a single place ID, which
  makes multiple place tables more difficult.

Things we don't care about so much:

* Precise titles. The title is the only mutable attribute of a place; I'm happy
  to do two-way merges for titles, rather than take the time to track changes to
  titles for a three-way merge.


PROPOSAL

Two visit tables.

Remote visits are a pure mirror -- if we support deleting individual remote
visits, we will probably do this by making them a local deleted visit (i.e.,
taking ownership).

Local visits have date, siteID, type, and 'new' flag.

One history table.

ID: numeric.
URL: the real primary key.
GUID: initially random or null. Will be updated on each sync.
Title. The only history field that can conflict.
Server modified time. Default to null until synced.
Local last modified time. Default to null. Does not reflect visits.
Deleted.
Should upload. Flip this if the title changed or if any local visits were removed.


On download, we'll update local GUIDs for each record, replace remote visits
(record.visits - local.visits), reconcile title if local modifications
occurred. Process deletion as discussed above.

On upload: if there are any new local visits or the history row is modified
(i.e., the title changed, the record was deleted, or local visits were
removed), upload a new record.


When a local site is visited:

1. Ensure that a history row exists.
a. If one exists, we update its title and mark it as modified. (TODO: do we
   always mark as modified?)
b. If one does not exist, we insert a row with no GUID, or a random one (it
   doesn't matter), no server modified time, and should-upload set to true.

2. Insert into local visits.


When local history is cleared:

Either:

1. Remove all visits, local and remote, and mark the history item as deleted.
2. Move all remote visits into the local table, marking them individually as
   deleted.

The former has some annoying timestamp flip-flopping. The latter directly expresses intent.