blob: deda1ac1fc333a9f758cb262a43b1811c21d0af8 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
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_measurements id)
#:on-delete #:cascade)
(foreign-key nutrient_target_id
#:references (nutrient_targets 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!))
|
