New Admin Console and SQL config storage format

Unique IDs for columns, custom hierarchies, and extended metadata support
Added by Andy Dufilie about 4 years ago

The old SQL config storage method used the following three tables, primary keys shown in bold.

Table name Column names Purpose
config_data title, name, keyType, dataType, dataTable, geometryCollection,
year, min, max, title, number, string, connection, sqlQuery, sqlParams
Stores private and public metadata for attribute columns
config_geometry name, connection, schema, tablePrefix, keyType, projection, importNotes Stores private and public metadata for geometry columns
weave_dataset_metadata dataTable, element, value Stores public metadata for data tables
This format was based on the original XML storage format. Some disadvantages of this format are as follows:
  • Lack of unique id in config_data for attribute columns forces us to use metadata matching to uniquely identify an attribute. Changes to certain metadata (name, dataTable, year) will break existing visualizations.
  • Each "entity" type (data table, attribute column, geometry column) has a different storage method and is difficult to expand upon.
  • Public and private metadata are stored in the same place, forcing us to maintain a list of which properties should be shared to the Weave client and which should not.

The new storage format has the following four tables, primary keys shown in bold.

Table name Column names Purpose
weave_manifest entity_id, type_id Declare an entity and its type. Deprecated
weave_hierarchy parent_id, child_id, sort_order Define ordered parent-child relationships between entities.
weave_meta_public entity_id, meta_name, meta_value Define public metadata, visible to Weave users.
For each entity_id, there must be an entry specifying entityType metadata.
Possible values for entityType are: table, column, hierarchy, category.
weave_meta_private entity_id, meta_name, meta_value Define private metadata, never shared to the Weave client.
Advantages of the new storage format:
  • Unique IDs allow changing the metadata without breaking previously-created visualizations (starting from this version).
  • All entity types have the same metadata storage method.
  • Allows storing arbitrary metadata for any entity.
  • Public and private metadata are clearly separated.
  • Allows us to create new entity types without changing the table structure.
  • We can now create and save custom attribute hierarchies in the Admin Console.
Some notes on changes in the admin console interface:
  • The first time you open the new Admin Console, any old configuration data will be automatically copied into the new format. The old config tables will remain unchanged.
  • "Data Tables" and "Geometry Collections" are no longer separated. Geometry collections are now displayed as columns under a data table. All three data import wizards (SQL, CSV, SHP+DBF) will create a single Data Table entry.
  • The keyType for a newly imported table can no longer be specified indirectly by picking a geometry collection.
  • Data import wizards will not overwrite any existing Weave data tables.
  • Metadata editor now has optional drop-down lists for changing keyType and dataType metadata.
  • Hierarchy editor is fully functional but the Weave client does not read the hierarchies yet. It will in a future update.
  • The values for type_id in the manifest table are: 0=table, 1=column, 2=hierarchy, 3=category
  • Here's a description of metadata properties for Weave columns: Server metadata descriptions

Please share any suggestions or bug reports you have about the new interface. Thanks!


Comments