Tilda JSON Syntax: Object Indices

<-- Object Syntax

Indices

Indices define methods of optimized access for tables. One cay say they are preferred as they represent natual orderings of the data for optimized access. In Tilda, you can create:

• unique indices by specifying a list of 1 or more "columns"
• regular indices by specifying an "orderBy" list of 1 or more columns
• indices that exist in the database or only at the application level with "db":true|false.

// Within an Object definition, you can define any number of indices (or none).
 "indices":[
      // A unique index that exists in the database over colA and colB
      { "name":"Blah1", "columns":["colA", "ColB"],                     "db":true }
      // A regular (non-unique) index that overlaps with the previous definition
      // but only exists for application needs (no database definition).
     ,{ "name":"Blah2", "columns":["colA"]        , "orderBy":["colB"], "db":false}
      // A regular index with only an orderBy definition.
     ,{ "name":"Blah3",                             "orderBy":["colB"], "db":true }
  ]

The fields are:

• name: The name of the index (most database support naming indices). Index names must be unique for a given table.
• columns: A list of 0 or more columns in the Object that represent the main columns for the index
• orderBy: A list of 0 or more columns in the Object that represent the secondary columns for the index
• db: whether the index definition is used physically in the database, or only for the application generated code.

🎈 NOTE: An index definition requires at least 1 column defined in either "columns" or "orderBy".

🎈 NOTE: Tilda will not warn you if you create a frivolous index. For example, "Blah1" and "Blah2" above are overlapping and we were careful to create "Blah2" as db:false. If we had done true instead, Tilda would have created 2 physical indices, and most databases would have just done that internally.

🎈 NOTE: All objects must have at least one identity. That can be supplied via a Primary Key, or a unique index. Tilda will enforce that either one or the other are defined. An object/table can of course have multiple identities.

🎈 NOTE: Tilda will use regular indices for most cases, but will use text-based indices when dealing with collection of STRING values. In Postgres, this means using a gin index when the covered columns include a STRING collection.

Additionally, an index column definition can be modified with either 'lal' (i.e., Left Anchor Like) or 'fts' (i.e., Full Text Search). For example:

     { "name":"Blah2", "columns":["colA asc lal"]        , "orderBy":["colB fts"] }

🎈 NOTE: Those index specializations only make sense if the index is defined at the database level, so adding "db":false will omit the database definition and those specializations will have no impact.