Mark tutorial street_edge_id in database

[misaugstad]

Brief description of problem/feature

I added the tutorial street_edge_id to the cityparams.conf file so that we can access it throughout our application. But we still don't have access to the ID when running queries on the database directly, outside of the application. The workaround right now is that we can get the the ID with the following query:

SELECT MAX(street_edge_id) FROM street_edge WHERE timestamp = '2015-11-16 20:20:19.46-08'

This happens to get the correct ID in every city, but we should add something more direct.

Potential solution(s)

We should probably add a config table at this point. There are probably some other configs in the cityparams.conf file that would work better in the database, either because they are useful when running standalone queries or because the config can change frequently and we don't want to do a new code deployment for a config change.

[misaugstad]

There is some debate as to whether to go with a single row table or to use key-value pairs with many rows in the config table. I would lean towards the former so that everything is in the correct data type instead of keeping everything in a string when using key-value pairs.

https://softwareengineering.stackexchange.com/questions/163606/configuration-data-single-row-table-vs-name-value-pair-table

[jonfroehlich]

I like the former for reasons you articulate though I realize this might be a large number of fields with a single row!

[misaugstad]

Looking at the conf/cityparams.conf file, here's the initial list of configs that should probably go in this new configs table. I'm only including things that could change and don't need to be seen by other cities at this time.

• open-status
• mapathon-event-link
• all the lat/lngs; not sure if it makes sense to store them in a postgis geometry column or as doubles in two separate columns
• default zoom for all maps
• tutorial-street-edge-id
• update-offset-hours
• exclude-tags

[misaugstad]

Whoever does this will need to do the following:

1. Make a new evolutions file that creates a new database with the appropriate columns
2. Create a new Scala file that corresponds to that table
3. Replace all instances of Play.configuration.get that reference the configs you're moving into the database to instead use the values in the database.
4. Insert the data for the appropriate city into the database (while setting this up to work on all of our servers when the code is pushed). This happens in the evolutions file. I'll add some code below showing approx what the evolutions file will look like.

# --- !Ups
CREATE TABLE config (
    column_1  <data-type> NOT NULL,
    column_2  <data-type> NOT NULL,
    ...
);

# Insert one row into the database, choosing the correct city based on the db name.
INSERT INTO config VALUES (<col-1-data>, <col-2-data>, ...) WHERE current_database() = '<db-1-name>';
INSERT INTO config VALUES (<col-1-data>, <col-2-data>, ...) WHERE current_database() = '<db-2-name>';
...

# ---!Downs
DROP TABLE config;

For the database names, we'll need to use the database names from our servers, even if they don't match what you named your database locally. If your local database doesn't match the name, you can manually run an insert into the table on your database!

Here are the names of the databases:

• sidewalk-seattle
• sidewalk-columbus
• sidewalk-cdmx
• sidewalk-spgg
• sidewalk-pittsburgh
• sidewalk-newberg
• sidewalk (the DC one is just called "sidewalk" I guess, since it was our first city)
• sidewalk-chicago
• sidewalk-amsterdam
• sidewalk-la-piedad
• sidewalk-oradell
• sidewalk_validation (another unfortunate break in the naming convention)
• sidewalk-zurich
• sidewalk-taipei
• sidewalk-auckland
• sidewalk-cuenca

[dylanbun]

closing as this is under #3320.