Enhance indexes for incremental sync

[githubbob42]

Mingle Card: 4111 Narrative

When storing sobject data during a sync, we need to be able ensure appropriate indexes are available for use when querying.

We currently generate indexes for:

• Id (Primary Key for “lookup” data, Secondary Key for “transaction” data)
• External Id – For fields like Tracking_Number
• Sync Id (Primary Key for “transaction” data)
• Name (for sorting)
• keywords (for searching)
• references on “lookup” data
  • Id (Currently only used in Equip/Crew Planning to filter by Job)
  • Name (+Name for sorting) – for reference picker filters (Account has an index on `RecordType.Name+Name` e.g. [‘Office’, ‘Odessa’]) 
• references on “transaction” data (e.g. selecting Quote_Items for a Quote)
  • Id
  • Sync Id

When querying for certain “known” entities, we want to be able to provide custom indexes to enable proprietary filtering, such as:

• Filtering price book items by their price book + ticket item record type (filtering) + sequence number (sorting)
• Filtering price book item by price book + catalog item id

Analysis

It may be acceptable to either:

• provide a means to provide custom indexes
• enhance the set of “generic” indexes created. For instance:
  • If sequence number is available on metadata, use sequence number instead of name for sorting
  • If the entity is a ”detail” record, provide composite indexes based on the “master” (e.g. make indexes for price book items composite based on the price book)
  • Automatically provide an index for any field with name a name containing “Record_Type” (e.g. `Ticket_Item_Record_Type`). This is not really a generic index, however, Ticket_Item_Record_Type was always a hack to handle the parallel inheritance hierarchy of Ticket_Item and Price_Book_Item.

There may be some value in the “generic” approach, for example, ensuring all entities with “Sequence_Number” are sorted by default without requiring bespoke code for particular sobjects. 

The current price book table has the following names indexes:

1. hasPriceBook: 'Price_Book' – used to show that items exist for the given price book id
2. priceBook: ['Price_Book', 'Sequence_Number'] – get all items for given pricebook, sorted by sequence number
   1. We don’t need this, it was implemented to assist in deleting price book items when you no longer need that price book.
3. catalogItemId: ['Price_Book', 'Catalog_Item'] – find catalog item(s) for given pricebook *should* be unique but is not enforced
   1. We need this because contacts and equipment are referenced by the catalog item id
4. ticketItemTypeName: ['Price_Book', 'Ticket_Item_Record_Type', 'Sequence_Number'] – find catalog item(s) for given pricebook, by record type, sorted by sequence number

Looking at the above set of indexes, we can probably simplify:

• Removing `hasPricebook` – we can use the `priceBook` index to achieve the same outcome (albeit the results will be sorted by sequence number)
• Automatically grouping composite indexes by the “master” field: 'Price_Book' – this eliminates the need for a bespoke `catalogItemId` index 
• Automatically providing sorting by sequence number (instead of name) if the field is present – this eliminates the need for a bespoke `priceBook` index
• Automatically provide an index for any field with name a name containing 'Record_Type' – this eliminates the need for a bespoke `ticketItemTypeName` index

Acceptance Criteria

There shouldn’t be any observable impact to behavior withing the app (nothing will be using these extra indexes yet). However, we should try to keep an eye on:

• Sync speed – more indexes = more overhead when writing data
• _Storage – _more indexes = more storage used

Related Stories

4100

Tasks

{{table query: SELECT Number, Name, Owner, 'Task Status' WHERE Type = Task and Story = THIS CARD}}

Defects

{{ table query: SELECT Number, Name, Owner, 'Status' WHERE Type = Defect and 'Related Story' = THIS CARD }}

Test Plan

[githubbob42]

Matthew Todd : 05/05/2016 15:18 CDT Testing has been completed and has passed on multiple users and tablets using the pr1836 branch.

[githubbob42]

Pull Request #1836