1 files changed, 167 insertions, 0 deletions
diff --git a/db/migrations.rkt b/db/migrations.rkt
new file mode 100644
index 0000000..95164e4
--- /dev/null
+++ b/db/migrations.rkt
@@ -0,0 +1,167 @@
+#lang racket
+
+(provide migrate-all!)
+
+(require db
+         sql
+         "conn.rkt")
+
+(define migrations-box (box '()))
+
+(define (migrate-all!)
+  (printf "Applying migrations on connection ~a...\n"
+          (dbsystem-name (connection-dbsystem (current-conn))))
+  (for ([pair (in-list (unbox migrations-box))])
+    (match pair
+      [(cons migration-name stmts)
+       (with-tx
+         (for ([stmt (in-list stmts)])
+           (query-exec (current-conn) stmt)))
+       (printf "Applied migration: ~a\n" migration-name)])))
+
+(define-syntax-rule (define-migration migration-name sql)
+  (let ((migrations (unbox migrations-box))
+        (name-symbol (string->symbol migration-name)))
+    (if (assoc name-symbol migrations)
+        (error 'define-migration "migration '~a' declared more than once" migration-name)
+        (set-box! migrations-box (append migrations (list (cons name-symbol sql)))))))
+
+
+;;;;;;;;;;;;
+;; NUTRIENTS
+;;;;;;;;;;;;
+
+(define-migration "create table nutrients"
+  (list
+   (create-table #:if-not-exists
+                 nutrients
+                 #:columns
+                 [id integer #:not-null]
+                 [canonical_name text #:not-null]
+                 [formula text #:not-null]
+                 #:constraints
+                 (primary-key id)
+                 (unique canonical_name)
+                 (unique formula))))
+
+(define-migration "create table nutrient_value_sets"
+  (list
+   (create-table #:if-not-exists
+                 nutrient_value_sets
+                 #:columns
+                 [id integer #:not-null]
+                 [nutrient_measurement_id integer]
+                 [nutrient_target_id integer]
+                 [crop_requirement_id integer]
+                 [fertilizer_product_id integer]
+                 #:constraints
+                 (primary-key id)
+                 (foreign-key nutrient_measurement_id
+                              #:references (nutrient_measurement id)
+                              #:on-delete #:cascade)
+                 (foreign-key nutrient_target_id
+                              #:references (nutrient_target id)
+                              #:on-delete #:cascade)
+                 (foreign-key crop_requirement_id
+                              #:references (crop_requirements id)
+                              #:on-delete #:cascade)
+                 (foreign-key fertilizer_product_id
+                              #:references (fertilizer_products id)
+                              #:on-delete #:cascade))
+   "CREATE INDEX IF NOT EXISTS idx_nvs_meas ON nutrient_value_sets(nutrient_measurement_id)"
+   "CREATE INDEX IF NOT EXISTS idx_nvs_targ ON nutrient_value_sets(nutrient_target_id)"
+   "CREATE INDEX IF NOT EXISTS idx_nvs_crop ON nutrient_value_sets(crop_requirement_id)"
+   "CREATE INDEX IF NOT EXISTS idx_nvs_prod ON nutrient_value_sets(fertilizer_product_id)"))
+
+(define-migration "create table nutrient_values"
+  (list
+   (create-table #:if-not-exists
+                 nutrient_values
+                 #:columns
+                 [value_set_id integer #:not-null]
+                 [nutrient_id integer #:not-null]
+                 [value_ppm real #:not-null]
+                 #:constraints
+                 (primary-key value_set_id nutrient_id)
+                 (foreign-key value_set_id
+                              #:references (nutrient_value_sets id)
+                              #:on-delete #:cascade)
+                 (foreign-key nutrient_id
+                              #:references (nutrients id)
+                              #:on-delete #:cascade))
+   "CREATE INDEX IF NOT EXISTS idx_nv_set_nutrient ON nutrient_values(value_set_id, nutrient_id)"))
+
+(define-migration "create table nutrient_measurements"
+  (list
+   (create-table #:if-not-exists
+                 nutrient_measurements
+                 #:columns
+                 [id integer #:not-null]
+                 ;; ISO8601 date
+                 [measured_on text #:not-null]
+                 #:constraints
+                 (primary-key id)
+                 (unique measured_on))))
+
+(define-migration "create table nutrient_targets"
+  (list
+   (create-table #:if-not-exists
+                 nutrient_targets
+                 #:columns
+                 [id integer #:not-null]
+                 ;; ISO8601 date
+                 [effective_on text #:not-null]
+                 #:constraints
+                 (primary-key id)
+                 (unique effective_on))))
+
+
+;;;;;;;;
+;; CROPS
+;;;;;;;;
+
+(define-migration "create table crops"
+  (list
+   (create-table #:if-not-exists
+                 crops
+                 #:columns
+                 [id integer #:not-null]
+                 [canonical_name integer #:not-null]
+                 #:constraints
+                 (primary-key id)
+                 (unique canonical_name))))
+
+(define-migration "create table crop_requirements"
+  (list
+   (create-table #:if-not-exists
+                 crop_requirements
+                 #:columns
+                 [id integer #:not-null]
+                 [crop_id integer]
+                 [profile text #:not-null]
+                 #:constraints
+                 (primary-key id)
+                 (foreign-key crop_id
+                              #:references (crops id)
+                              #:on-delete #:cascade))))
+
+
+;;;;;;;;;;;;;;
+;; FERTILIZERS
+;;;;;;;;;;;;;;
+
+(define-migration "create table fertilizer_products"
+  (list
+   (create-table #:if-not-exists
+                 fertilizer_products
+                 #:columns
+                 [id integer #:not-null]
+                 [canonical_name text #:not-null]
+                 [brand_name text]
+                 #:constraints
+                 (primary-key id)
+                 (unique canonical_name))))
+
+(module+ test
+  (connect!)
+  (migrate-all!))