1 #lang racket 2 3 (provide migrate-all!) 4 5 (require db 6 sql 7 "conn.rkt") 8 9 (define migrations-box (box '())) 10 11 (define (migrate-all!) 12 (for ([pair (in-list (unbox migrations-box))]) 13 (match-define (cons _ stmts) pair) 14 (with-tx (for ([stmt (in-list stmts)]) 15 (query-exec (current-conn) stmt))))) 16 17 (define-syntax-rule (define-migration migration-name sql) 18 (let ([migrations (unbox migrations-box)] 19 [name-symbol (string->symbol migration-name)]) 20 (if (assoc name-symbol migrations) 21 (error 'define-migration "migration '~a' declared more than once" migration-name) 22 (set-box! migrations-box (append migrations (list (cons name-symbol sql))))))) 23 24 ;;;;;;;; 25 ;; USERS 26 ;;;;;;;; 27 28 (define-migration "create table users" 29 (list (create-table #:if-not-exists users 30 #:columns [id integer #:not-null] 31 [name text #:not-null] 32 [role_id integer] 33 #:constraints (primary-key id) 34 (unique name) 35 (foreign-key role_id #:references (user_roles id))))) 36 37 (define-migration "create table user_roles" 38 (list (create-table #:if-not-exists user_roles 39 #:columns [id integer #:not-null] 40 [name text #:not-null] 41 #:constraints (primary-key id) 42 (unique name)))) 43 44 ;;;;;;;;;;;; 45 ;; NUTRIENTS 46 ;;;;;;;;;;;; 47 48 (define-migration "create table nutrients" 49 (list (create-table #:if-not-exists nutrients 50 #:columns [id integer #:not-null] 51 [canonical_name text #:not-null] 52 [french_name text #:not-null] 53 [formula text #:not-null] 54 #:constraints (primary-key id) 55 (unique canonical_name) 56 (unique formula)))) 57 58 (define-migration 59 "create table nutrient_value_sets and indexes" 60 (list 61 (create-table 62 #:if-not-exists nutrient_value_sets 63 #:columns [id integer #:not-null] 64 [nutrient_measurement_id integer] 65 [crop_requirement_id integer] 66 [fertilizer_product_id integer] 67 #:constraints (primary-key id) 68 (foreign-key nutrient_measurement_id #:references (nutrient_measurements id) #:on-delete #:cascade) 69 (foreign-key crop_requirement_id #:references (crop_requirements id) #:on-delete #:cascade) 70 (foreign-key fertilizer_product_id #:references (fertilizer_products id) #:on-delete #:cascade) 71 (unique nutrient_measurement_id) 72 (unique crop_requirement_id) 73 (unique fertilizer_product_id) 74 (check (or (and (is-not-null nutrient_measurement_id) 75 (is-null crop_requirement_id) 76 (is-null fertilizer_product_id)) 77 (and (is-null nutrient_measurement_id) 78 (is-null crop_requirement_id) 79 (is-not-null fertilizer_product_id)) 80 (and (is-null nutrient_measurement_id) 81 (is-not-null crop_requirement_id) 82 (is-null fertilizer_product_id))))) 83 "CREATE INDEX IF NOT EXISTS idx_nvs_meas ON nutrient_value_sets(nutrient_measurement_id)" 84 "CREATE INDEX IF NOT EXISTS idx_nvs_crop ON nutrient_value_sets(crop_requirement_id)" 85 "CREATE INDEX IF NOT EXISTS idx_nvs_prod ON nutrient_value_sets(fertilizer_product_id)")) 86 87 (define-migration 88 "create table nutrient_values and indexes" 89 (list 90 (create-table #:if-not-exists nutrient_values 91 #:columns [value_set_id integer #:not-null] 92 [nutrient_id integer #:not-null] 93 [value_ppm real #:not-null] 94 #:constraints (primary-key value_set_id nutrient_id) 95 (foreign-key value_set_id #:references (nutrient_value_sets id) #:on-delete #:cascade) 96 (foreign-key nutrient_id #:references (nutrients id) #:on-delete #:cascade)) 97 "CREATE INDEX IF NOT EXISTS idx_nv_set_nutrient ON nutrient_values(value_set_id, nutrient_id)")) 98 99 (define-migration "create table nutrient_measurements" 100 (list (create-table #:if-not-exists nutrient_measurements 101 #:columns [id integer #:not-null] 102 ;; ISO8601 date 103 [measurement_date text #:not-null] 104 #:constraints (primary-key id) 105 (unique measurement_date)))) 106 107 ;;;;;;;; 108 ;; CROPS 109 ;;;;;;;; 110 111 (define-migration "create table crops" 112 (list (create-table #:if-not-exists crops 113 #:columns [id integer #:not-null] 114 [canonical_name text #:not-null] 115 #:constraints (primary-key id) 116 (unique canonical_name)))) 117 118 (define-migration 119 "create table crop_requirements" 120 (list (create-table #:if-not-exists crop_requirements 121 #:columns [id integer #:not-null] 122 [crop_id integer] 123 [profile text #:not-null] 124 #:constraints (primary-key id) 125 (foreign-key crop_id #:references (crops id) #:on-delete #:cascade)))) 126 127 (define-migration "create table crop_rotations" 128 (list (create-table #:if-not-exists crop_rotations 129 #:columns [id integer #:not-null] 130 ;; ISO8601 date 131 [rotation_date text #:not-null] 132 #:constraints (primary-key id) 133 (unique rotation_date)))) 134 135 (define-migration 136 "create table crop_rotation_requirements" 137 (list (create-table 138 #:if-not-exists crop_rotation_requirements 139 #:columns [crop_rotation_id integer #:not-null] 140 [crop_requirement_id integer #:not-null] 141 [proportion_percent integer #:not-null] 142 #:constraints (primary-key crop_rotation_id crop_requirement_id) 143 (foreign-key crop_rotation_id #:references (crop_rotations id) #:on-delete #:cascade) 144 (foreign-key crop_requirement_id #:references (crop_requirements id) #:on-delete #:cascade)))) 145 146 ;;;;;;;;;;;;;; 147 ;; FERTILIZERS 148 ;;;;;;;;;;;;;; 149 150 (define-migration "create table fertilizer_products" 151 (list (create-table #:if-not-exists fertilizer_products 152 #:columns [id integer #:not-null] 153 [canonical_name text #:not-null] 154 [brand_name text #:not-null] 155 #:constraints (primary-key id) 156 (unique canonical_name brand_name)))) 157 158 (module+ test 159 (connect!) 160 (migrate-all!)) 161