Metadata Architecture for Dataset Layers

[jbranigan]

(Sprint B)

1. When a user clicks on a dataset that is stored in the Data Library it would open a pre-populated map
2. In that map the map would be designed to a pre-set style and would have other items automatically configured (this is where the design metadata would come in)
3. There may also be predefined filters that show up in the filters menu that would be tied to specific columns as well as info-windows (hover/pop up/columns shown) - list is below
   • Styles
   • Filters
   • Tooltips
   • Layer Name Alias
   • Field Name Aliases (UI in common data + implement)
4. This data wouldn’t be stored in the user’s account as it would be a synced dataset in the data library.

There is another component here that is more complex and will likely need to be handled from the CartoDB side related to the Bloomberg Data Library. When a user selects a Bloomberg Data Library dataset it will not go into their account and they will not have Write privileges on that dataset. Additionally it will not get imported into their account or count against their quota.

[CloudNiner]

Subtasks added to the board this sprint are as follows:

(A): Add a configuration option to the vagrant-cartodb repo that allows us to provision a dev environment with a second user, pre-populated with a few data sets that can be linked to via postgres_fdw

(B): Setup the CartoDB FDW config in such a way that properly links a table from one account to another via FDW. Consider here: postgresql user permissions, maybe a separate user? Or publicuser?

(C): Setup a rake task in the ruby application that, given the proper parameters, will create a new visualization in a user account, using a remote visualization + FDW instead of a standard import

(D): Modify the UI such that an import from the data library uses the above FDW rake task instead of a standard import

[CloudNiner]

Here's the PR that adds the FDW functions to cartodb-postgresql. These are available in our branch.

https://github.com/CartoDB/cartodb-postgresql/pull/194

And some additional notes from CartoDB:

hi! right now the FDWs aren't used explicitly from CartoDB code, we set up for internal purposes but you can still use the functions

the cdb_conf.fdws is actually an entry on the cartodb.cdb_conf table IIRC. it can be accessed by the CDB_GetConf / CDB_SetConf functions on postgresql

it assumes certain conventions as: the remote table must live on a specific schema (which will also have to be a schema that exists locally). i.e: if fdw is named foo, user must have a foo schema where foreign tables will be created on, and the remote tables must be on the foo local schema as well...

depending on what you want to do, you might want to go to the route of creating the FDW manually instead of going through our functions.

[CloudNiner]

In order to support FDW tables in the CartoDB Importer, it looks like we'll want to setup a custom Loader, add it here, and implement the run and supported? methods of a new FDWLoader: https://github.com/cartodb-org/cartodb/blob/bbg_production/services/importer/lib/importer/runner.rb#L22 https://github.com/cartodb-org/cartodb/blob/bbg_production/services/importer/lib/importer/tiff_loader.rb

We may also need a custom Downloader class to handle 'fdw' job types.

[CloudNiner]

For table and column name aliasing, alias should happen automatically at table creation. The column and table name aliases should be caps case for now, lower case prepositions such as and,or.