api.vessels_view
Description
Expose vessels listing to web api
Table Definition
CREATE VIEW vessels_view AS (
SELECT v.name,
v.mmsi,
v.created_at,
md."time" AS last_contact,
md.active,
((now() - md."time") > 'PT1H10M'::interval) AS offline,
(now() - md."time") AS duration,
lm.last_metric_time,
((now() - lm.last_metric_time) > 'PT1H10M'::interval) AS metrics_offline,
(now() - lm.last_metric_time) AS duration_last_metrics,
CASE
WHEN (md."time" IS NULL) THEN true
WHEN ((now() - md."time") > 'PT1H10M'::interval) THEN true
ELSE false
END AS is_offline
FROM ((auth.vessels v
LEFT JOIN api.metadata md ON ((md.vessel_id = v.vessel_id)))
LEFT JOIN LATERAL ( SELECT metrics."time" AS last_metric_time
FROM api.metrics
WHERE (metrics.vessel_id = v.vessel_id)
ORDER BY metrics."time" DESC
LIMIT 1) lm ON (true))
WHERE ((v.owner_email)::text = current_setting('user.email'::text))
)
Columns
| Name | Type | Default | Nullable |
|---|---|---|---|
| active | boolean | true | |
| created_at | timestamp with time zone | true | |
| duration | interval | true | |
| duration_last_metrics | interval | true | |
| is_offline | boolean | true | |
| last_contact | timestamp with time zone | true | |
| last_metric_time | timestamp with time zone | true | |
| metrics_offline | boolean | true | |
| mmsi | numeric | true | |
| name | text | true | |
| offline | boolean | true |
Referenced Tables
| Name | Columns | Comment | Type |
|---|---|---|---|
| auth.vessels | 7 | vessels table link to accounts email user_id column | BASE TABLE |
| api.metadata | 18 | Stores metadata received from vessel, aka signalk plugin. single-row-per-vessel store | BASE TABLE |
| LATERAL | 0 | ||
| api.metrics | 11 | Stores time-series metrics from vessel via SignalK plugin. TimescaleDB hypertable partitioned by time. | BASE TABLE |
Relations
erDiagram
"api.vessels_view" {
boolean active ""
timestamp_with_time_zone created_at ""
interval duration ""
interval duration_last_metrics ""
boolean is_offline ""
timestamp_with_time_zone last_contact ""
timestamp_with_time_zone last_metric_time ""
boolean metrics_offline ""
numeric mmsi ""
text name ""
boolean offline ""
}
Generated by tbls