Database Structure

Database Creation

Run V1_createTables.sql and V2_alterTablePointValuesNullableValue.sql on our database.

Database Restart

Run Oopsies.sql which will deletes everything and starts us over. Beware of oopsies and make sure you really want to reset and not just delete a specific building. Adding the historical data takes a while, so just be cautious.

Database Structure

We have multiple tables: buildings, rooms, point types, point sources, points, and point values.

• Building
  • ID
  • Name
• Rooms
  • ID
  • Name
  • BuildingID
• PointTypes
  • ID
  • Name
  • Units
  • Return Type
  • Factor
• PointSources
  • ID
  • Name
• Points
  • ID
  • Name
  • RoomID
  • PointSourceID
  • PointTypeID
  • Description
• PointValues
  • PointTimestamp
  • PointID
  • PointValue

Everything that is of form _ _ ID is a reference to another table.

• A pointvalue has a point.
• A point has a room, pointsource, and pointtype.
• A room has a building.

SQL Queries You May Enjoy

Count all point values for a given building, helpful to see if importing a building is going well!

SELECT count(pointtimestamp) as count, pointid, array_agg(pointvalue) as point_values, points.name as point_name, Rooms.name as room_name,
  buildings.name as building_name, pointsources.name as point_source, pointtypes.factor as factor, pointtypes.returntype as return_type,
  points.description as description
FROM PointValues
JOIN Points on PointValues.pointID = Points.ID
JOIN Rooms on Points.RoomID = Rooms.ID
JOIN Buildings on Rooms.BuildingID = Buildings.ID
JOIN pointsources on points.pointsourceid = pointsources.id
JOIN pointtypes on points.pointtypeid = pointtypes.id
  WHERE buildings.name = 'Hulings'
GROUP BY pointid, point_name, room_name, building_name, point_source, factor, return_type, description;

Group PointValues by Timestamp, get pointname and value

SELECT pointtimestamp, ('{' || string_agg('"' || points.name || '": ' || pointvalue, ',') || '}')::json as activity
FROM pointvalues
  JOIN points on pointvalues.pointid = points.id
GROUP BY pointtimestamp;