forerad

commit 0c7c12667a81efe12c32b6627de9a6a36174a789
parent c5bb7d5df26da533163797c3ca0808b867d34dce
Author: Steve Gattuso <steve@stevegattuso.me>
Date:   Mon, 30 Oct 2023 09:04:13 +0100

add initial schema

Diffstat:
A
data.sqlite3
 | 
0

A
migrations/20231030_01_HoM9F-create-the-initial-schema.sql
 | 
39
+++++++++++++++++++++++++++++++++++++++
A
yoyo.ini
 | 
6
++++++

3 files changed, 45 insertions(+), 0 deletions(-)
diff --git a/data.sqlite3 b/data.sqlite3
Binary files differ.
diff --git a/migrations/20231030_01_HoM9F-create-the-initial-schema.sql b/migrations/20231030_01_HoM9F-create-the-initial-schema.sql
@@ -0,0 +1,39 @@
+-- Create the initial schema
+-- depends: 
+CREATE TABLE raw_trips (
+    id INTEGER PRIMARY KEY,
+    tripduration INTEGER NOT NULL,
+    starttime TEXT NOT NULL,
+    stoptime TEXT NOT NULL,
+    start_station_id TEXT NOT NULL,
+    end_station_id TEXT NOT NULL,
+    bikeid INTEGER NOT NULL
+);
+
+
+CREATE TABLE raw_stations (
+    station_id TEXT NOT NULL PRIMARY KEY,
+    name TEXT,
+    short_name TEXT,
+    capacity INTEGER,
+    lat REAL NOT NULL,
+    lon REAL NOT NULL
+);
+
+
+CREATE TABLE cleaned_stations (
+    station_id TEXT NOT NULL PRIMARY KEY,
+    name TEXT,
+    borough TEXT,
+    capacity INTEGER,
+    lat REAL NOT NULL,
+    lon REAL NOT NULL
+);
+
+
+CREATE TABLE hourly_trip_rollup (
+    datetime TEXT NOT NULL,
+    borough TEXT NOT NULL,
+    ride_count INTEGER NOT NULL,
+    PRIMARY KEY (datetime, borough)
+);
diff --git a/yoyo.ini b/yoyo.ini
@@ -0,0 +1,6 @@
+[DEFAULT]
+sources = migrations
+database = sqlite://./data.sqlite3
+batch_mode = off
+verbosity = 0
+