Inheritable JSON schemas for result models

[azimov]

JSON Schemas will allow several more configurable options over the basic csv files that are currently used.

• define indexes
• Support SqlRender hinting for different platforms
• define foreign keys
• define views across multiple tables
• Allow inheritance and or linking between schemas.
• Allow significantly more specification and automatic type validation on table column types
• List migrations in the schema as well as in the project tree
• Very easy to do an object level diff between two schema versions to see what tables are added, altered or removed
• Implementable in other languages in a consistent manner
• Naturally provides an OpenAPI style specification which can be leveraged to produce RESTFUL web services with little to no code
• More naturally provide a dplyr interface to tables that could be used as an alternative and compatible interface to dbplyr

Inheritance allows extendable models that can be used. For example we could have the cohort_diagnostics schema which extends the cohort_generator schema (using all of its tables).
Similarly, this would allow packages and reporting apps to extend existing packages.
For example, it may be desirable to add a diagnostic to CohortDiagnostics that adds a page into the shiny app as well as populating tables.

Steps

• Define basic db object model in R (started)
• Current table view (column_name, data_type, primary_key, empty_is_na) etc
• Add foreign keys
• Allow indexes (note that configuration on what index types are is highly platform specific)
• Allow extension/dependencies on other schemas
• Support for jsonvalidate package