==================================================
Update and Commit protocol 
==================================================
----------------
protocol outline
----------------
    - the protocol is based on XML RPC
    - the Update and Commit (UpCom) protocol is essentially a synchronization protocol between two databases - CMTS on the server and cache on teh client
    - so there are 3 schemas to regard - the server, the client and that of the protocol. 
    - The protocol schema is independent of client or server, though for obvious reasons it resembles them, at least of the early version.
    - this schema is refered to as "conceptual" or "protocol" schema
    - the XML code adhering to the protocol schema is used in this document for illustration
    - there is no XML code adhereing to the protocol schema generated when the actual code runs. 
        Instead the hierarchy translates to the low level XML-RPC on the wire
        and to PHP arrays at the server side, according to the rules described later in this document
     
----------------
conceptual XML -> XML RPC or 
----------------
    - tags are nodes
    - tags attributes are moved to the separate 'metadata' node
    - since XML rpc and PHP array has no concept of named tags, the tag name is made into 'type' sub-node  
    
----------------
Realms 
----------------
    - realm is an abstration of a database table .
    - we have local cache of the user data (inventory) up to a certain revision
    - we can be insterested in more then one realm defined in the same table (two consumers or two households)
    - responses are not restricted to the data from just one table

    - realms
        - consumer
        - household

----------------
Consumer realm
----------------
entityid = consumer name
tables 
    - consumer - contains personal data about consumer. has one line?
    - playlist - contains playlists - one playlist per line item. has PL name and id
        - there was originally playlsitMedia table that mapped playlist to media.
        - it has been replaced by one field in playlist called tracklist which is a concatenation of mediaid in the ordinal order
    - Note: no Media table. Media belongs to the household

----------------
houshold realm
----------------

entityid = household  name
tables 
    - track
        - contains details of tracks of all households and may be more. 
        - it is a common pool of media data
        - consumer/playlistMedia point to entries of this table
        - household/householdMedia point to entries of this table
    - artist
        - contains description of artist whos id is refered to by entries in the "track" table
    - album
        - contains description of album which id is refered to by entries in the "track" table
    - householdMedia - a mapping between HH id and Media id.

----------------
conceptual schema 
----------------
- Responses
    - Response
        - Updates (one per table, so it might be as well called Table) 
            - Update (individual row update)

----------------
index and column
----------------

    the Update node of the conceptual protocol schema defines what cells of a row are being udpated
    it does it by locating the row using index sub-nodes and then modifying the cells using column.
    Note: when adding new row both index and column nodes are treated equally as cells.
    Note: more then one cell can form an index. In that case there will be more then one index sub-node.

----------------
Ephraim's XML (EXML)
----------------

    Ephraim has created the XML chema used in the examples below.
    The implementation uses XML-RPC, that has very primitve structure of array, struct, and name-value pairs.
    XML RPC not expressive enought to consume EXML as is.
    So the following rules are used:
        - Tag name is translated to a struct parameter with one member with name 'type' and value of EXML tag
        - Tag attributes are accumulated as name-value members of a struct called 'metadata' placed in the above struct
        - Tag's content (typically an array of diffent tags (e,g. Requests has array of Request) is an array member.
            its 'name' is 'data'. its 'value' is the array of members translated from Ephraim's XML as per these rules recursively. 

----------------
data integrity 
----------------
Data integrity is preserved at the server. 
For example if a media is deleted from a household, it should be deleted from playlists of all consumers belonging to this household 
Corollary: realms are not entirely independent.
Changes to one realm might result in chnages in another realm. 
In the example above, a change to the household realm results in changes to consumer realms for possibly many consumers.

---------------
commit
---------------

commit requests may result in more then one response. that is because request is per realm.
because of data integrity (see section "data integrity") this may result into changes to other realms
thus resulting in a different revision for those realms. such changes make for a separate response.

==================================================
Revisioning 
==================================================

Every update and commit request provides the revision number.
Every response provides the revision number.
Revision in request says what revision is the client's current
Revision in response says what revision is the server's current, and therefore 
what delta the response covers.










==================================================
Examples 
==================================================
    ---------------------
    Update 
    ---------------------
        ---------------------
        Request
        ---------------------
<?xml revision='1.0' encoding='UTF-8' ?>
<updateRequests APIVersion='1.0' ProtocolVersion='1.0' referredObjects='heuristic' >
  <updateRequest realm='consumer' entityId='ephraim' revision='3001' />
  <updateRequest realm='household' entityId='ephraims household' revision='234' />
  <updateRequest realm='household' entityId='siegel household' revision='200' />
<updateRequests>

        ---------------------
        Response
        ---------------------

<?xml revision='1.0' encoding='UTF-8' ?>
<updateResponses APIVersion='1.0' ProtocolVersion='1.0'>
  <updateResponse realm='consumer' entityId='ephraim' oldRevision='3001' revision='3002'>

    <tableUpdates table='consumer' deletes='0' adds='0' changes='1'>
      <tableUpdate type='change'>
        <indexValue name='name' value='ephraim'>
        <columnValue name='address1' value='Pnei Kedem, Asfar' />
      </tableUpdate>
    </tableUpdates>
    <!-- ------------------------- -->

    <tableUpdates table='playlist' deletes='1' adds='1' changes='0'>

      <tableUpdate type='delete'>
        <indexValue name='id' value='1000004'/>
      </tableUpdate>

      <tableUpdate type='add'>
        <indexValue name='id' value='1000009'>
        <columnValue name='name' value='My Greatest Favorites' />
      </tableUpdate>

    </tableUpdates>

    <tableUpdates table='playlistMedia' deletes='0' adds='4' changes='0'>
      <tableUpdate type='add'>
        <indexValue name='playlistid' value='1000008' />
        <columnValue name='mediaIndex' value='1' />
        <columnValue name='mediaid' value='500000003' />
      </tableUpdate>

      <tableUpdate type='add'>
            <indexValue name='playlistid' value='1000008' />
            <columnValue name='mediaIndex' value='2' />
            <columnValue name='mediaid' value='500004321' />
      </tableUpdate>

      <tableUpdate type='add'>
            <indexValue name='playlistid' value='1000008' />
            <columnValue name='mediaIndex' value='3' />
            <columnValue name='mediaid' value='500007777' />
      </tableUpdate>

      <tableUpdate type='add'>
            <indexValue name='playlistid' value='1000008' />
            <columnValue name='mediaIndex' value='4' />
            <columnValue name='mediaid' value='500004321' />
      </tableUpdate>

    </tableUpdates>
  </updateResponse>

  <updateResponse realm='household' entityId='ephraims household' oldVersion='234' revision='236'>
    <tableUpdates table='track' deletes='0' adds='2' changes='0'>
      <tableUpdate type='add'>
	<indexValue name='id' value='14026669' />
	<columnValue name='name' value='Gone Home' />
	<columnValue name='artist_id' value='1089822' />
	<columnValue name='album_id' value='593548' />
	<columnValue name='genre_id' value='500004321' />
	<!-- <columnValue name='track_number' value='12' /> -->
      </tableUpdate>

      <tableUpdate type='add'>
	<indexValue name='id' value='16928504' />
	<columnValue name='name' value='Turpentine' />
	<columnValue name='artist_id' value='1089823' />
	<columnValue name='album_id' value='976583' />
	<columnValue name='genre_id' value='' />
	<!-- <columnValue name='track_number' value='3' /> -->
      </tableUpdate>
    </tableUpdates>

    <tableUpdates table='householdMedia' deletes='0' adds='2' changes='0'>
      <tableUpdate type='add'>
	<!--  In reality, the household id in the SQLite DB will be numeric, and equal to the id on the -->
	<!--  server, but for example we use the possibilty that it is just the name of the hh -->
	<indexValue name='householdid' value='ephraims household' />
	<columnValue name='mediaid' value='500004321' />
      </tableUpdate>

      <tableUpdate type='add'>
	<indexValue name='householdid' value='ephraims household' />
	<columnValue name='mediaid' value='500007777' />
      </tableUpdate>
    </tableUpdates>
  </updateResponse>

  <updateResponse realm='household' entityId='siegel household' oldVersion='200' revision='200' />
<updateResponses>


==================================================
Commit 
==================================================
    --------------------------------------------------
    Request:
    --------------------------------------------------

<?xml revision='1.0' encoding='UTF-8' ?>
<commitRequests APIVersion='1.0' ProtocolVersion='1.0'>
  <commitRequest realm='consumer' entityId='ephraim' revision='3002'>
    <tableCommits table='consumer' deletes='0' adds='0' changes='1'>
      <tableCommit action='change'>
        <indexValue name='name' value='ephraim'>
        <columnValue name='address1' value='As Far out as you can get.' />
      </tableCommit>
    </tableCommits>

    <tableCommits table='playlistMedia' deletes='0' adds='1' changes='0'>
      <tableCommit action='add'>
        <indexValue name='playlistid' value='1000008' />
        <columnValue name='mediaIndex' value='5' />
        <columnValue name='mediaid' value='500000003' />
      </tableCommit>

    </tableCommits>
  </commitRequest>

  <commitRequest realm='household' entityId='ephraims household' revision='236'>
    <tableCommits table='media' deletes='1' adds='0' changes='0'>
      <tableCommit action='delete'>
	<indexValue name='id' value='14026669' />
	<columnValue name='name' value='Gone Home' />
	<columnValue name='artist_id' value='1089822' />
	<columnValue name='album_id' value='593548' />
	<columnValue name='genre_id' value='500004321' />
	<!-- <columnValue name='track_number' value='12' /> -->
      </tableCommit>

      <tableCommit action='add'>
	<indexValue name='id' value='16928504' />
	<columnValue name='name' value='Turpentine' />
	<columnValue name='artist_id' value='1089823' />
	<columnValue name='album_id' value='976583' />
	<columnValue name='genre_id' value='' />
	<!-- <columnValue name='track_number' value='3' /> -->
      </tableCommit>
    </tableCommits>

    <tableCommits table='householdMedia' deletes='1' adds='0' changes='0'>
      <tableCommit action='delete'>
	<indexValue name='householdid' value='ephraims household' />
	<columnValue name='mediaid' value='500088888' />
      </tableCommit>
    <tableCommits>

  </commitRequest>

  <commitRequest realm='household' entityId='siegel household' revision='200'>
    <tableCommits table='householdMedia' deletes='1' adds='0' changes='0'>
      <tableCommit action='delete'>
	<columnValue name='mediaid' value='500088888' />
      </tableCommit>
    <tableCommits>
  </commitRequest>

<commitRequests>

    ---------------------
    Commit Response:
    ---------------------

<?xml revision='1.0' encoding='UTF-8' ?>
<commitResponses APIVersion='1.0' ProtocolVersion='1.0'>
  <commitResponse realm='consumer' entityId='ephraim' revision='3003' />
  <commitResponse realm='household' entityId='ephraims household' revision='237' />
  <commitResponse realm='household' entityId='siegel household' revision='201' />
<commitRequests>


======================
Ephraim's Notes:
TODO: incorporate the notes into the main body
======================

0) In the conversations shown there is an "update requests" document and
a "commit requests document".  Each document contains requests for three
"realm-keyvalue" sets, where in one case the realm is "consumer", and in
the other two, the realm is "household" (but the household id differs).
For each request, with the realm-keyvalue pair is specified a current
revision number; the response contains the new revision number or the
old and new revision numbers.  Database "changes" (may be effectively
just query results for the update operation) in a request or a response
may refer to several tables, where the changes in a single table are
contained in a "tableUpdate" or a "tableCommit" element.

1) In reality, the household id in the SQLite DB is numeric, and equal
to the id on the server, but for example I had assumed it would just be
the name of the hh Similarly, for values the fields "entityId" I used
the name instead of the numeric id.

2) XML is suggested instead of a brittle format -- it is more robust
and allows the protocol to evolve organically, and support variations
(for different client requirements).  Concerning the space overhead,
when we get to it, we should have the messages compressed with zip.
XML will compress extremely well with zip and similar algorithms.
Apache has a module that can do this essentially transparently.

3) The data contents of the messages is based on a somewhat abstract
picture of the data schema, but intended to be close to the implementation
in the client API library (specifically the schema of the local cache).
Handling differences between the client-side schema and the server
side schema will therefore fall mostly to the responsibility of the
server side.

4) Because the track inventory of a consumer's household(s) specifies the
track ids, and the metadata is stored in separate tables, it is necessary
to populate the appropriate tables (artist, album, etc.)  with the
referred-to rows.  A client (other than possibly a kiosk) is not likely
to have the whole known universe of audio tracks metadata in its cache.
A request for update (including what is generated for "getInventory")
can specify one of several values for the attribute "referredObjects":

"full"	  -- retrieve all the referred-to rows from other tables
"none"	  -- retrieve none of the referred-to rows from other tables.
Request will be done separately "heuristic" -- do something smart, TBD.
Not guaranteed to satisfy all references "artists" -- retrieve all
referenced rows from the album table ...


5) The defined realms are "consumer" and "household".  In an update response,
there may appear a table update for any table. 

6) The table and colunn names in the client define the names that are used to describe 
the objects.  The definitive document for this is the schema found at
svn+ssh://$USER@svn.catchmedia.com/svnroot/catchmedia/trunk/playanywhere/clientapi/src/dblayer/cmdb-sqlite_schema.sql .  
It is not required to return data for all the columns, but there is no guarantee that
missing data will never break the client side.

Table names are singular.

The following is a list of the tables that are likely to be referenced, 
just the table names and column names.  Refer to the schema for more details:

consumer
( id username honorific first_name middle_name last_name email website phone_number
mobile_number fax_number address_1 address_2 city state zip country);

track
( id artist_id album_id group_id drm_id fingerprint_id disc_number track_number owned
name ownership_type buyable price popularity size length year date_recorded bit_rate
sample_rate play_count format file_name);


artist
( id name display_name image_file track_count);

album
( id genre_id muzenbr name type year label image_file track_count artist_id
multiple_artists buyable packaging_codes );

genre
( id name image_file track_count);

playlist
( id name created tracklist title comment track_count shared consumer_id);

household
( id name);

household_consumer
( consumer_id hh_id);

household_media
( hh_id track_id);


7)  Currently the client schema stores playlists as a single string, with trackids delinated by
spaces.  This will change, but for now the server side will handle the conversion.





