blob: 48e788f377a4f3a86d4e790b2a80f21c4211ebe3 (
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
|
#lang racket
(provide migrate-all!)
(require db
sql
"conn.rkt")
(define migrations-box (box '()))
(define (migrate-all!)
(for ([pair (in-list (unbox migrations-box))])
(match-define (cons migration-name stmts) pair)
(with-tx (for ([stmt (in-list stmts)])
(query-exec (current-conn) stmt)))))
(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)))))))
;;;;;;;;
;; USERS
;;;;;;;;
(define-migration "create table users"
(list (create-table #:if-not-exists users
#:columns [id integer #:not-null]
[name text #:not-null]
[role_id integer]
#:constraints (primary-key id)
(unique name)
(foreign-key role_id #:references (user_roles id)))))
(define-migration "create table user_roles"
(list (create-table #:if-not-exists user_roles
#:columns [id integer #:not-null]
[name text #:not-null]
#:constraints (primary-key id)
(unique name))))
;;;;;;;;;;;;
;; 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)
(unique nutrient_measurement_id)
(unique nutrient_target_id)
(unique crop_requirement_id)
(unique fertilizer_product_id)
(check (or (and (is-not-null nutrient_measurement_id)
(is-null nutrient_target_id)
(is-null crop_requirement_id)
(is-null fertilizer_product_id))
(and (is-null nutrient_measurement_id)
(is-not-null nutrient_target_id)
(is-null crop_requirement_id)
(is-null fertilizer_product_id))
(and (is-null nutrient_measurement_id)
(is-null nutrient_target_id)
(is-not-null crop_requirement_id)
(is-null fertilizer_product_id))
(and (is-null nutrient_measurement_id)
(is-null nutrient_target_id)
(is-null crop_requirement_id)
(is-not-null fertilizer_product_id)))))
"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 #:not-null]
#:constraints (primary-key id)
(unique canonical_name brand_name))))
(module+ test
(connect!)
(migrate-all!))
|
