diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/conn.rkt | 41 | ||||
| -rw-r--r-- | db/data/dolibarr_crop_requirements_ppm.csv | 9 | ||||
| -rw-r--r-- | db/data/dolibarr_fertilizer_compositions_percentage.csv | 18 | ||||
| -rw-r--r-- | db/data/dolibarr_nutrient_measurements_ppm.csv | 44 | ||||
| -rw-r--r-- | db/migrations.rkt | 167 | ||||
| -rw-r--r-- | db/seed.rkt | 137 |
6 files changed, 416 insertions, 0 deletions
diff --git a/db/conn.rkt b/db/conn.rkt new file mode 100644 index 0000000..e083d94 --- /dev/null +++ b/db/conn.rkt @@ -0,0 +1,41 @@ +#lang racket + +(require db) + +(provide current-conn + connect! + disconnect! + with-db + with-tx) + +(define current-conn (make-parameter #f)) + +(define (connect! #:path [path 'memory]) + (cond + [(connection? (current-conn)) + (printf "Database connection already exists: ~e\n" (current-conn))] + [else + (current-conn (sqlite3-connect #:database path + #:mode 'create)) + (printf "Created database connection at path: ~a\n" path)])) + +(define (disconnect!) + (disconnect (current-conn)) + (printf "Closing database connection: ~e\n" (current-conn)) + (current-conn #f)) + +(define-syntax-rule (with-db body ...) + (begin (connect!) body ...)) + +(define-syntax-rule (with-tx body ...) + (call-with-transaction (current-conn) (λ () body ...))) + +(module+ test + (require rackunit) + (check-equal? (current-conn) #f) + (connect!) + (check-true (connection? (current-conn))) + (disconnect!) + (check-equal? (current-conn) #f) + (with-db + (check-true (connection? (current-conn))))) diff --git a/db/data/dolibarr_crop_requirements_ppm.csv b/db/data/dolibarr_crop_requirements_ppm.csv new file mode 100644 index 0000000..e9bf8fd --- /dev/null +++ b/db/data/dolibarr_crop_requirements_ppm.csv @@ -0,0 +1,9 @@ +Plante,Profil,NNO3,P,K,Ca,Mg,S,Na,Cl,Si,Fe,Zn,B,Mn,Cu,Mo,NNH4 +,générique croissance,160,30,230,100,30,60,0,0,0,5,0.15,0.3,0.5,0.15,0.05,0 +,générique floraison,130,60,300,100,30,60,0,0,0,2,0.1,0.5,0.5,0.05,0.05,0 +Laitue,générique,190,50,210,200,50,66,0,0,0,5,0.15,0.3,0.5,0.15,0.05,0 +Tomate,cycle entier,140,50,352,180,50,168,0,0,0,5,0.1,0.3,0.8,0.07,0.03,0 +Tomate,10-14 jours,100,40,200,100,20,53,0,0,0,3,0.1,0.3,0.8,0.07,0.03,0 +Tomate,1ère grappe,130,55,300,150,33,109,0,0,0,3,0.1,0.3,0.8,0.07,0.03,0 +Tomate,à maturité,180,65,400,400,45,144,0,0,0,3,0.1,0.3,0.8,0.07,0.03,0 +Framboise,tous stades,70,12,88,90,24,48,0,0,50,0.56,0.325,0.11,0.11,0.032,0.01,0 diff --git a/db/data/dolibarr_fertilizer_compositions_percentage.csv b/db/data/dolibarr_fertilizer_compositions_percentage.csv new file mode 100644 index 0000000..f58042c --- /dev/null +++ b/db/data/dolibarr_fertilizer_compositions_percentage.csv @@ -0,0 +1,18 @@ +Ref,Libellé,Nom commercial,NNO3,P,K,Ca,Mg,S,Na,Cl,Fe,Zn,B,Mn,Cu,Mo,NNH4 +Nitrate_de_Potassium,Nitrate de potassium,Multi K Reci,13.50,0,38.60,0,0,0,0.015,0,0,0,0,0,0,0,0 +Acide_nitrique_53%,Acide nitrique 53%,,11.70,0,0,0,0,0,0,0,0,0,0,0,0,0,0 +Bicarbonate_de_potassium,Bicarbonate de potassium,,0,0,0,39.05,0,0,0,0,0,0,0,0,0,0,0 +Boronia_LS,Bore-Molybdène,,0,0,0,0,0,0,0,0,0,13.50,0,0,0,0.028,0 +Phosphate_de_diammonium,Phosphate de diammonium,DAP 18/46/00,21.00,53.00,0,0,0,0,0,0,0,0,0,0,0,0,0 +Ferexel_D12,Fer chélaté DTPA,,0,0,0,0,0,0,0,0,0,11.80,0,0,0,0,0 +Nitrate_de_calcium,Nitrate de calcium,Multi-Cal Haïfa,15.50,0,0,18.94,0,0,0,0,0,0,0,0,0,0,0 +Sulfate_de_Potassium,Sulfate de potassium,Patenkali,0,22.66,0,0.44,6.16,17.77,2.16,3.34,0,0,0,0,0,0,0 +Sulfate_de_Manganese,Sulfate de Manganèse,Fixa Mn,0,0,0,0,0,7.17,0,0,0,0,12.00,0,0,0,0 +Sulfate_de_Magnesium,Sulfate de Magnésium,Eso Top,0,0,0,9.648,13.016,0,0,0,0,0,0,0,0,0,0 +Nitrate_d_ammonium,Nitrate d’ammonium,,27.00,0,0,0,0,0,0,0,0,0,0,0,0,0,0 +Carbonate_de_Calcium,Carbonate de Calcium,,0,0,0,38.00,0,0,0,0,0,0,0,0,0,0,0 +Nitrate_d_ammonium_27kg,Nitrate d’ammonium 27 en 25 kg,Ammonitrate 27,13.50,0,0,0,0,0,0,0,0,0,0,0,0,0,13.5 +Fixa_Zinc,Fixa Zinc,,0,0,0,0,0,0,0,0,0,0,5.00,0,12.00,0,0 +HelioCuivre,HelioCuivre,,0,0,0,0,0,0,0,0,0,0,0,40.00,0,0,0 +Boronia_Molybdène_-_Boronia,Boronia MO12 10L,,0,0,0,0,0,0,0,0,0,0,8.90,0.089,0,0.89,0 +Molybdate_de_sodium,Molybdate de sodium,,0,0,0,0,0,0,39.50,0,0,0,0,0,0,0,0 diff --git a/db/data/dolibarr_nutrient_measurements_ppm.csv b/db/data/dolibarr_nutrient_measurements_ppm.csv new file mode 100644 index 0000000..c5d12e6 --- /dev/null +++ b/db/data/dolibarr_nutrient_measurements_ppm.csv @@ -0,0 +1,44 @@ +date,NNO3,P,K,Ca,Mg,S,Na,Cl,Si,Fe,Zn,B,Mn,Cu,Mo,NNH4 +08/05/2021,1.87,0.0031936,0.91,100.79,8.37,1.08666558,6.73,12.28,5.42,0.01,0.01,0.01,0.01,0.01,0.01,0.02 +02/07/2021,66.3,0.0095808,110.91,93.09,17.95,32.84663382,10.27,18.53,5.02,4.37,0.03,0.18,0.15,0,0,0.63 +12/07/2021,61.66,2.011968,122.53,73.5,17.44,47.64328569,0.45,0.82,0.36,1.76,0.05,0.13,0.32,0,0,0 +12/08/2021,67.27,0.351296,94.45,89.48,16.25,9.11665755,10.76,16.51,4.95,3.2,0.03,0.14,0.07,0,0,0.06 +06/09/2021,77.8,7.1153408,96.29,86.31,14.21,23.90664276,9.32,14.92,4.61,2.45,0.08,0.12,0.05,0.03,0,0.02 +21/09/2021,87.88,11.33728,117.24,90.64,17.25,29.90663676,9.92,15.96,4.68,2.4,0.08,0.27,0.12,0.04,0.01,0.02 +19/10/2021,77.84,13.4195072,121.91,94.71,18.3,33.19663347,12.36,15.28,4.47,2.72,0.11,0.74,0.04,0.04,0.02,0.03 +01/12/2021,57.09,6.6842048,81.46,61.45,10.52,17.72331561,17.36,23.05,2.11,1.27,0.17,0.32,0.01,0.03,0.02,0.07 +04/02/2022,43.56,6.5979776,63.1,63.72,9.92,17.3999826,17.08,21.15,1.44,1.6,0.22,0.34,0.01,0.04,0.01,0.03 +12/04/2022,38.36,6.5021696,83.48,49.75,6.42,14.14331919,29.8,38.85,0.23,0.3,0.2,0,0.02,0.01,0.4,0.07 +16/05/2022,24.63,0.542912,30.22,39.79,6.75,0.0666666,25.98,25.89,0.14,1.13,0.3,0.23,0.01,0.02,0.005,0.12 +15/06/2022,2.45,0.0606784,0.69,24.58,4.14,1.24999875,19.59,11.33,0.32,0.79,0.25,0.13,0.01,0.02,0.01,0.1 +04/07/2022,24.3,3.081824,42.34,39.79,8.09,0.41333292,19.79,11.32,1.24,0.7,0.18,0.07,0.02,0.01,0.005,0.68 +01/08/2022,4.88,0.2299392,27.77,22.48,6.71,18.68331465,23,5,0.64,0.97,0.17,0.04,0.005,0.005,0.005,0.07 +07/09/2022,29.28,2.07584,79.65,36.05,13.82,31.17663549,33.41,12.32,0.83,0.84,0.13,0.07,0.01,0.005,0.005,0.8 +24/10/2022,41.61,1.2646656,68.38,47.44,11.54,21.55664511,28.22,18.73,0.81,0.41,0.1,0.05,0.005,0.005,0.005,0.09 +21/11/2022,40.34,3.1105664,53.6,51.09,9.69,17.97331536,25.5,18.85,0.97,0.58,0.11,0.07,0.005,0.005,0.005,0.04 +09/01/2023,48.71,6.6171392,63.08,71.45,11.58,6.55666011,23.81,21.32,1.46,1.04,0.13,0.13,0.01,0.01,0.01,0.03 +21/02/2023,56.03,7.5273152,53.3,81.75,12.11,19.31998068,23.16,27.27,1.86,0.95,0.18,0.19,0.01,0.005,0.02,0.13 +20/04/2023,50.67,7.0546624,76.45,76.63,10.57,15.54331779,22.25,26.97,0.95,0.96,0.19,0.12,0.005,0.005,0.005,0.21 +07/06/2023,48.82,7.3005696,90.04,64.58,9.96,16.92664974,22.39,25.93,0.78,0.42,0.2,0.11,0.005,0.005,0.005,0.16 +13/07/2023,21.79,1.5712512,77.61,40.41,9.74,20.82331251,26.23,22.49,0.6,1.16,0.22,0.31,0,0,0.02,0.14 +17/07/2023,21.79,4.9213376,77.61,40.41,9.74,20.82331251,26.23,22.49,0.6,1.16,0.22,0.31,0.01,0.01,0.02,0.14 +23/08/2023,23.88,2.778432,97.75,49.74,26.25,31.24996875,31.11,16.43,1.33,0.8,0.18,0.14,0,0,0.01,1.01 +02/09/2023,23.88,2.778432,97.75,49.74,16.15,31.24996875,31.11,16.43,1.33,0.8,0.18,0.14,0,0,0.01,1.01 +13/10/2023,71.9,5.0905984,106.35,91.03,18.11,34.90663176,32.36,14.59,1.43,0.97,0.17,0.05,0,0,0,0.24 +28/12/2023,70.58,9.1368896,141.05,68.18,11.96,24.18664248,27.95,21.57,1.31,0.88,0.16,0.15,0,0,0.02,0.06 +16/01/2024,77.26,11.433088,149.17,68.71,12.14,23.24331009,29.88,27.04,1.15,1.14,0.26,0.14,0,0,0,0.09 +04/03/2024,84.65,13.4418624,185.21,66.05,15.61,30.69330264,40.12,36.82,0.66,0.93,0.39,0.35,0,0,0.03,0.16 +23/04/2024,69.17,11.2350848,116.41,52.96,14.07,28.95330438,80.73,37.54,0.57,0.79,0.42,0.38,0.01,0,0.03,0.16 +30/05/2024,46.74,7.520928,81.84,38.61,10.48,26.52997347,79.96,40.47,0.34,0.87,0.45,0.37,0,0.03,0.03,0.12 +05/07/2024,27.51,3.960064,75.58,27.78,8.29,25.99330734,92.2,44.74,0.32,0.59,0.45,0.37,0,0.03,0.03,0.15 +23/07/2024,16.55,2.4143616,83.62,16.52,4.9,20.45997954,79.04,40.51,0.32,0.77,0.43,0.26,0,0.03,0.02,0.43 +06/08/2024,0,0.3001984,74.41,6.82,2.56,17.28664938,78.79,35.04,0.42,0.9,0.37,0.29,0,0.03,0.02,0.13 +09/09/2024,18.3,2.87424,85.3,8.7,1.7,10.5666561,53.7,22.7,2.5,0.7,0.2,0.1,0,0,0,0 +01/10/2024,22.94,4.1740352,112.46,9.44,2,13.51998648,58.57,28.76,0.27,0.6,0.26,0.17,0,0.02,0,0.2 +22/11/2024,66,2.87424,127,33,9,7.333326,63,40,2.8,0.73,0.37,0.16,0.01,0.01,0.01,0 +20/01/2025,94.26,15.664608,146.27,76.06,14.24,32.05663461,64.29,46.99,0.45,0.37,0.48,0.16,0.08,0.03,0.01,0.11 +06/03/2025,95.82,20.007904,171.83,92.24,15.75,35.2332981,82.27,54.5,0.45,0.17,0.49,0.16,0.03,0.02,0,0.06 +04/04/2025,85.49,17.2646016,109.91,88.54,13.47,31.08663558,65.02,53.96,0.28,0.65,0.51,0.13,0.01,0.01,0,0 +22/05/2025,49.8,9.9927744,92.41,61.42,8.47,22.42331091,52.95,38.25,0.32,0.86,0.48,0.23,0.03,0,0.02,0.13 +11/06/2025,30.28,5.700576,50.2,48.96,5.73,22.10664456,51.34,33.41,0,0.96,0.51,0.26,0,0,0,0 +01/08/2025,1.34,0.4854272,77.64,16.45,5.52,22.93997706,58.45,25.83,0.5,0.48,0.44,0.17,0,0,0,0 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!)) diff --git a/db/seed.rkt b/db/seed.rkt new file mode 100644 index 0000000..6b253a6 --- /dev/null +++ b/db/seed.rkt @@ -0,0 +1,137 @@ +#lang racket + +;; Seed the database with default values. + +(provide seed-database!) + +(require csv-reading + "conn.rkt" + "../models/nutrient.rkt" + "../models/nutrient-measurement.rkt" + "../models/crop.rkt" + "../models/crop-requirement.rkt" + "../models/fertilizer-product.rkt") + +(define (seed-database!) + (seed-nutrients!) + (seeded "nutrients") + (seed-historical-nutrient-measurements!) + (seeded "historical nutrient measurements") + (seed-crops!) + (seeded "crops") + (seed-crop-requirements!) + (seeded "crop requirements") + (seed-existing-fertilizer-products!) + (seeded "existing fertilizer products")) + +(define (seeded entity) + (displayln (format "Seeded entity: ~a" entity))) + +(define (seed-nutrients!) + (define nutrient-names (map nutrient-name (get-nutrients))) + (define default-nutrients + '(("Nitrate Nitrogen" "NNO3") + ("Phosphorus" "P") + ("Potassium" "K") + ("Calcium" "Ca") + ("Magnesium" "Mg") + ("Sulfur" "S") + ("Sodium" "Na") + ("Chloride" "Cl") + ("Silicon" "Si") + ("Iron" "Fe") + ("Zinc" "Zn") + ("Boron" "B") + ("Manganese" "Mn") + ("Copper" "Cu") + ("Molybdenum" "Mo") + ("Ammonium Nitrogen" "NNH4"))) + (with-tx + (for ([pair (in-list default-nutrients)]) + (define name (first pair)) + (define formula (second pair)) + ;; Ensure idempotence + (unless (member name nutrient-names) + (create-nutrient! name formula))))) + +(define (seed-historical-nutrient-measurements!) + (define input-csv "/home/blendux/git/ferti-v2/db/data/dolibarr_nutrient_measurements_ppm.csv") + (define next-row (make-csv-reader (open-input-file input-csv))) + (define header (next-row)) + (define (row->seed! row) + (define row-alist (map cons header row)) + (define measured-on (cdar row-alist)) + (define nutrient-values + (for/list ([nm (in-list (cdr row-alist))]) + (define formula (car nm)) + (define n (get-nutrient #:formula formula)) + (define v (string->number (cdr nm))) + (cons n v))) + (create-nutrient-measurement! measured-on nutrient-values)) + (with-tx + (csv-for-each row->seed! next-row))) + +(define (seed-crops!) + (define crop-names (map crop-name (get-crops))) + (define default-crops + '("salade" + "laitue" + "tomate" + "framboise")) + (with-tx + (for ([name (in-list default-crops)]) + ;; Ensure idempotence + (unless (member name crop-names) + (create-crop! name))))) + +(define (seed-crop-requirements!) + (define input-csv "/home/blendux/git/ferti-v2/db/data/dolibarr_crop_requirements_ppm.csv") + (define next-row (make-csv-reader (open-input-file input-csv))) + (define header (next-row)) + (define (row->seed! row) + (define row-alist (map cons header row)) + (define crop-name (string-downcase (cdr (assoc "Plante" row-alist)))) + (define profile (cdr (assoc "Profil" row-alist))) + (define nutrient-values + (for/list ([crop-requirement (in-list (list-tail row-alist 2))]) + (define formula (car crop-requirement)) + (define n (get-nutrient #:formula formula)) + (define v (string->number (cdr crop-requirement))) + (cons n v))) + (cond + [(non-empty-string? crop-name) + (define crop (get-crop #:name crop-name)) + (create-crop-requirement! profile nutrient-values crop)] + [else + (create-crop-requirement! profile nutrient-values)])) + (with-tx + (csv-for-each row->seed! next-row))) + +(define (seed-existing-fertilizer-products!) + (define input-csv "/home/blendux/git/ferti-v2/db/data/dolibarr_fertilizer_compositions_percentage.csv") + (define next-row (make-csv-reader (open-input-file input-csv))) + (define header (next-row)) + (define (row->seed! row) + (define row-alist (map cons header row)) + (define canonical-name (cdr (assoc "Libellé" row-alist))) + (define brand-name (cdr (assoc "Nom commercial" row-alist))) + (define nutrient-values + (for/list ([fertilizer-component (in-list (list-tail row-alist 3))]) + (define formula (car fertilizer-component)) + (define n (get-nutrient #:formula formula)) + (define v (string->number (cdr fertilizer-component))) + (cons n v))) + (cond + [(non-empty-string? brand-name) + (create-fertilizer-product! canonical-name nutrient-values brand-name)] + [else + (create-fertilizer-product! canonical-name nutrient-values)])) + (with-tx + (csv-for-each row->seed! next-row))) + +(module+ test + (require "migrations.rkt") + (connect! #:path "test.sqlite3" + ) + (migrate-all!) + (seed-database!)) |