WIP: Change API queries to the v2 database schema

Change SQL queries in the following routes:

• city
• state
• region
• school
• enrollment

Also, change tests to reflect the new schema where appropriate. Specially in the case of cities, the primary key now becomes pk_cod_ibge (city IBGE code) and, therefore, any query that relies on the city id should use this value instead of the old primary key.

Aliases of fields that have different names were added to ensure compatibility with the UI of LDE.

Please refer to the database repository in order to understand how the current schema works. The corresponding ER diagram can be located there.

/cc @vsbc14

Anything that might break besides the school table not having the school name?

Are these changes in some database or VM?

Working on it

@rce16, build is failing because there is no config.json.example

The new schema is setup at simcaqdb3. Same name, simcaq_dev

I forgot to mention, sorry about that.

Should have branched from this branch, sorry about that, my error.

Please, take a look on this suggestion. On that branch, I applied the constants from this file in the routes. With this, anytime we need to change names we would be able to do it on a single shot.

I think it is a good start to have all the constants and names in a single place.

Elaborating on that concept, we can infer the schema from the database once the application is built (it is doable in MonetDB [1]) and save it to something like _db/schema.js _. On a second iteration, implement a base model class which deals with squel, queries its appropriate table (e.g. City model queries city table), is able to perform some basic validations and error reporting (on save and update).

Perhaps it is just my biased view based on what Rails does (:o-)) but it would certainly speed things up not having hard-coded constants like table names and fields lying around.

[1] For example, try SELECT * FROM sys.tables WHERE system = 0

Added 1 commit:

• 75cfb911 - Rename config.json to config.json.example

@jvtr12 I view this class model proposal better for retrieving "isolated dimensions", like querying cities informations, states, etc, but for simulations might be too restrictive to depend on that system. I know that generating queries from Squel by hand is not the best case scenario, but maybe we can think on a better alternative for simulations queries.

Added 1 commit:

• b9c21e88 - Change simcaqdb1 to simcaqdb3 in config.json.example

Compare with previous version

@vsbc14 For simulations we can think of composing these base model classes (city, school, enrollment), but it would be nice to have the basic stuff already automatically handled.

Marked this merge request as a Work In Progress

Change to WIP, there seems to be a problem with some of the enrollments SQL queries. Will fix that before merging

Mentioned in merge request !27 (merged)

Status changed to closed

Superseded by !27 (merged)