diff options
Diffstat (limited to 'db/migrations.rkt')
| -rw-r--r-- | db/migrations.rkt | 268 |
1 files changed, 107 insertions, 161 deletions
diff --git a/db/migrations.rkt b/db/migrations.rkt index fb8ab80..4007db4 100644 --- a/db/migrations.rkt +++ b/db/migrations.rkt @@ -14,203 +14,149 @@ (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))) + (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))) + (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))))) + (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)))) - + (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)")) + (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)))) + (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)))) - + (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)))) - + (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)))) + (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!) |