changeset
Code cleanup in database functions.
Mon, 06 Jun 2022 20:19:27 +0200
- author
- Michiel Broek <mbroek@mbse.eu>
- date
- Mon, 06 Jun 2022 20:19:27 +0200
- changeset 262
- 6bab9440aeb9
- parent 261
- 246893ad04a6
- child 263
- 14d79ec33db4
Code cleanup in database functions.
| src/database/db_product.cpp | file | annotate | diff | comparison | revisions | |
| src/database/db_recipe.cpp | file | annotate | diff | comparison | revisions |
--- a/src/database/db_product.cpp Mon Jun 06 19:35:39 2022 +0200 +++ b/src/database/db_product.cpp Mon Jun 06 20:19:27 2022 +0200 @@ -538,10 +538,14 @@ bool DB_product::save(Product *prod, QDialog *dialog) { QSqlQuery query; + QString sql = ""; if (prod->record == -1) { - query.prepare("INSERT INTO products SET " - "name=:name, code=:code, birth=:birth, stage=:stage, notes=:notes, log_brew=:log_brew, " + sql = "INSERT INTO products SET "; + } else { + sql = "UPDATE products SET "; + } + sql.append("name=:name, code=:code, birth=:birth, stage=:stage, notes=:notes, log_brew=:log_brew, " "log_fermentation=:log_fermentation, log_ispindel=:log_ispindel, log_co2pressure=:log_co2pressure, " "inventory_reduced=:inventory_reduced, locked=:locked, " "eq_name=:eq_name, eq_boil_size=:eq_boil_size, eq_efficiency=:eq_efficiency, " @@ -610,81 +614,15 @@ "divide_type=:divide_type, divide_size=:divide_size, divide_factor=:divide_factor, " "divide_parts=:divide_parts, divide_part=:divide_part, " "json_fermentables=:json_fermentables, json_hops=:json_hops, json_miscs=:json_miscs, " - "json_yeasts=:json_yeasts, json_mashs=:json_mashs, uuid=:uuid"); + "json_yeasts=:json_yeasts, json_mashs=:json_mashs"); + + if (prod->record == -1) { + sql.append(", uuid=:uuid"); } else { - query.prepare("UPDATE products SET " - "name=:name, code=:code, birth=:birth, stage=:stage, notes=:notes, log_brew=:log_brew, " - "log_fermentation=:log_fermentation, log_ispindel=:log_ispindel, log_co2pressure=:log_co2pressure, " - "inventory_reduced=:inventory_reduced, locked=:locked, " - "eq_name=:eq_name, eq_boil_size=:eq_boil_size, eq_efficiency=:eq_efficiency, " - "eq_batch_size=:eq_batch_size, eq_tun_volume=:eq_tun_volume, eq_tun_weight=:eq_tun_weight, " - "eq_tun_specific_heat=:eq_tun_specific_heat, eq_tun_material=:eq_tun_material, eq_tun_height=:eq_tun_height, " - "eq_top_up_water=:eq_top_up_water, eq_trub_chiller_loss=:eq_chiller_loss, eq_evap_rate=:eq_evap_rate, " - "eq_boil_time=:eq_boil_time, eq_calc_boil_volume=:eq_calcboil, eq_top_up_kettle=:eq_top_up_kettle, " - "eq_hop_utilization=:eq_hopfactor, eq_notes=:eq_notes, eq_lauter_volume=:eq_lauter_volume, " - "eq_lauter_height=:eq_lauter_height, eq_lauter_deadspace=:eq_lauter_deadspace, eq_kettle_volume=:eq_kettle_volume, " - "eq_kettle_height=:eq_kettle_height, eq_mash_volume=:eq_mash_volume, eq_mash_max=:eq_mash_max, " - "brew_date_start=:brew_date_start, brew_mash_ph=:brew_mash_ph, brew_mash_sg=:brew_mash_sg, " - "brew_mash_efficiency=:brew_mash_efficiency, brew_sparge_temperature=:brew_sparge_temperature, " - "brew_sparge_volume=:brew_sparge_volume, brew_sparge_est=:brew_sparge_est, brew_sparge_ph=:brew_sparge_ph, " - "brew_preboil_volume=:brew_preboil_volume, brew_preboil_sg=:brew_preboil_sg, brew_preboil_ph=:brew_preboil_ph, " - "brew_preboil_efficiency=:brew_preboil_efficiency, brew_aboil_volume=:brew_aboil_volume, " - "brew_aboil_sg=:brew_aboil_sg, brew_aboil_ph=:brew_aboil_ph, brew_aboil_efficiency=:brew_aboil_efficiency, " - "brew_cooling_method=:brew_cooling_method, brew_cooling_time=:brew_cooling_time, brew_cooling_to=:brew_cooling_to, " - "brew_whirlpool9=:brew_whirlpool9, brew_whirlpool7=:brew_whirlpool7, brew_whirlpool6=:brew_whirlpool6, " - "brew_whirlpool2=:brew_whirlpool2, brew_fermenter_volume=:brew_fermenter_volume, " - "brew_fermenter_extrawater=:brew_fermenter_extrawater, brew_fermenter_tcloss=:brew_fermenter_tcloss, " - "brew_aeration_time=:brew_aeration_time, brew_aeration_speed=:brew_aeration_speed, " - "brew_aeration_type=:brew_aeration_type, brew_fermenter_sg=:brew_fermenter_sg, brew_fermenter_ibu=:brew_fermenter_ibu, " - "brew_fermenter_color=:brew_fermenter_color, brew_date_end=:brew_date_end, " - "og=:og, fg=:fg, primary_start_temp=:primary_start_temp, primary_max_temp=:primary_max_temp, " - "primary_end_temp=:primary_end_temp, primary_end_sg=:primary_end_sg, primary_end_date=:primary_end_date, " - "secondary_temp=:secondary_temp, secondary_end_sg=:secondary_end_sg, secondary_end_date=:secondary_end_date, " - "tertiary_temp=:tertiary_temp, package_date=:package_date, package_volume=:package_volume, " - "package_infuse_amount=:package_infuse_amount, package_infuse_abv=:package_infuse_abv, " - "package_infuse_notes=:package_infuse_notes, package_abv=:package_abv, package_ph=:package_ph, " - "bottle_amount=:bottle_amount, bottle_carbonation=:bottle_carbonation, bottle_priming_sugar=:bottle_priming_sugar, " - "bottle_priming_amount=:bottle_priming_amount, bottle_priming_water=:bottle_priming_water, " - "bottle_carbonation_temp=:bottle_carbonation_temp, keg_amount=:keg_amount, keg_carbonation=:keg_carbonation, " - "keg_priming_sugar=:keg_priming_sugar, keg_priming_amount=:keg_priming_amount, keg_priming_water=:keg_priming_water, " - "keg_carbonation_temp=:keg_carbonation_temp, keg_forced_carb=:keg_forced_carb, keg_pressure=:keg_pressure, " - "taste_notes=:taste_notes, taste_rate=:taste_rate, taste_date=:taste_date, taste_color=:taste_color, " - "taste_transparency=:taste_transparency, taste_head=:taste_head, taste_aroma=:taste_aroma, " - "taste_taste=:taste_taste, taste_mouthfeel=:taste_mouthfeel, taste_aftertaste=:taste_aftertaste, " - "st_name=:st_name, st_letter=:st_letter, " - "st_guide=:st_guide, st_category=:st_category, st_category_number=:st_catnr, st_type=:st_type, " - "st_og_min=:st_og_min, st_og_max=:st_og_max, st_fg_min=:st_fg_min, st_fg_max=:st_fg_max, " - "st_ibu_min=:st_ibu_min, st_ibu_max=:st_ibu_max, st_color_min=:st_color_min, st_color_max=:st_color_max, " - "st_carb_min=:st_carb_min, st_carb_max=:st_carb_max, st_abv_min=:st_abv_min, st_abv_max=:st_abv_max, " - "type=:type, batch_size=:batch_size, boil_size=:boil_size, " - "boil_time=:boil_time, efficiency=:efficiency, est_og=:est_og, est_og3=:est_og3, est_fg=:est_fg, est_abv=:est_abv, " - "est_color=:est_color, color_method=:color_method, est_ibu=:est_ibu, ibu_method=:ibu_method, " - "est_carb=:est_carb, sparge_temp=:sparge_temp, sparge_ph=:sparge_ph, " - "sparge_volume=:sparge_volume, sparge_source=:sparge_source, sparge_acid_type=:sparge_acid_type, " - "sparge_acid_perc=:sparge_acid_perc, sparge_acid_amount=:sparge_acid_amount, mash_ph=:mash_ph, " - "mash_name=:mash_name, calc_acid=:calc_acid, " - "w1_name=:w1_name, w1_amount=:w1_amount, w1_calcium=:w1_calcium, w1_sulfate=:w1_sulfate, " - "w1_chloride=:w1_chloride, w1_sodium=:w1_sodium, w1_magnesium=:w1_magnesium, " - "w1_total_alkalinity=:w1_total_alkalinity, w1_ph=:w1_ph, w1_cost=:w1_cost, " - "w2_name=:w2_name, w2_amount=:w2_amount, w2_calcium=:w2_calcium, w2_sulfate=:w2_sulfate, " - "w2_chloride=:w2_chloride, w2_sodium=:w2_sodium, w2_magnesium=:w2_magnesium, " - "w2_total_alkalinity=:w2_total_alkalinity, w2_ph=:w2_ph, w2_cost=:w2_cost, " - "wg_amount=:wg_amount, wg_calcium=:wg_calcium, wg_sulfate=:wg_sulfate, " - "wg_chloride=:wg_chloride, wg_sodium=:wg_sodium, wg_magnesium=:wg_magnesium, " - "wg_total_alkalinity=:wg_total_alkalinity, wg_ph=:wg_ph, " - "wb_calcium=:wb_calcium, wb_sulfate=:wb_sulfate, wb_chloride=:wb_chloride, wb_sodium=:wb_sodium, " - "wb_magnesium=:wb_magnesium, wb_total_alkalinity=:wb_total_alkalinity, wb_ph=:wb_ph, " - "wa_acid_name=:wa_acid_name, wa_acid_perc=:wa_acid_perc, wa_base_name=:wa_base_name, " - "starter_enable=:starter_enable, starter_type=:starter_type, starter_sg=:starter_sg, " - "starter_viability=:starter_viability, yeast_prod_date=:yeast_prod_date, yeast_pitchrate=:yeast_pitchrate, " - "prop1_type=:prop1_type, prop1_volume=:prop1_volume, prop2_type=:prop2_type, prop2_volume=:prop2_volume, " - "prop3_type=:prop3_type, prop3_volume=:prop3_volume, prop4_type=:prop4_type, prop4_volume=:prop4_volume, " - "divide_type=:divide_type, divide_size=:divide_size, divide_factor=:divide_factor, " - "divide_parts=:divide_parts, divide_part=:divide_part, " - "json_fermentables=:json_fermentables, json_hops=:json_hops, json_miscs=:json_miscs, " - "json_yeasts=:json_yeasts, json_mashs=:json_mashs WHERE record = :recno"); + sql.append(" WHERE record = :recno"); } + query.prepare(sql); query.bindValue(":name", prod->name); query.bindValue(":code", prod->code); query.bindValue(":birth", prod->birth); @@ -923,7 +861,7 @@ obj.insert("f_percentage", round(prod->fermentables.at(i).f_percentage * 10) / 10); obj.insert("f_di_ph", round(prod->fermentables.at(i).f_di_ph * 100000) / 100000); obj.insert("f_acid_to_ph_57", round(prod->fermentables.at(i).f_acid_to_ph_57 * 100000) / 100000); - qDebug() << "fermentables_Json" << i << obj; + // qDebug() << "fermentables_Json" << i << obj; array.append(obj); /* Append this object */ } QJsonDocument doc; @@ -953,7 +891,7 @@ obj.insert("h_cohumulone", round(prod->hops.at(i).h_cohumulone * 100) / 100); obj.insert("h_myrcene", round(prod->hops.at(i).h_myrcene * 100) / 100); obj.insert("h_total_oil", round(prod->hops.at(i).h_total_oil * 100) / 100); - qDebug() << "hops_Json" << i << obj; +// qDebug() << "hops_Json" << i << obj; array.append(obj); /* Append this object */ } QJsonDocument doc; @@ -974,7 +912,7 @@ obj.insert("m_time", round(prod->miscs.at(i).m_time)); obj.insert("m_amount_is_weight", prod->miscs.at(i).m_amount_is_weight ? 1:0); obj.insert("m_cost", round(prod->miscs.at(i).m_cost * 10000) / 10000); - qDebug() << "miscs_Json" << i << obj; +// qDebug() << "miscs_Json" << i << obj; array.append(obj); /* Append this object */ } QJsonDocument doc; @@ -1014,7 +952,7 @@ obj.insert("y_gr_hl_hi", prod->yeasts.at(i).y_gr_hl_hi); obj.insert("y_sg_hi", round(prod->yeasts.at(i).y_sg_hi * 1000) / 1000); obj.insert("y_cost", round(prod->yeasts.at(i).y_cost * 1000) / 1000); - qDebug() << "yeasts_Json" << i << obj; + // qDebug() << "yeasts_Json" << i << obj; array.append(obj); /* Append this object */ } QJsonDocument doc; @@ -1040,7 +978,7 @@ obj.insert("step_wg_ratio", round(prod->mashs.at(i).step_wg_ratio * 100) / 100); obj.insert("step_ph", round(prod->mashs.at(i).step_ph * 100) / 100); obj.insert("step_sg", round(prod->mashs.at(i).step_sg * 10000) / 10000); - qDebug() << "mashs_Json" << i << obj; + // qDebug() << "mashs_Json" << i << obj; array.append(obj); /* Append this object */ } QJsonDocument doc; @@ -1054,7 +992,6 @@ query.bindValue(":recno", prod->record); } query.exec(); - qDebug() << query.lastQuery(); if (query.lastError().isValid()) { qWarning() << "saveProduct" << query.lastError(); QMessageBox::warning(dialog, QObject::tr("Database error"), @@ -1063,19 +1000,19 @@ .arg(query.lastError().driverText()) .arg(query.lastError().databaseText())); return false; + } + + /* + * If this was a new product, find out what record number we + * have got and set it. So when the user saves this record + * again, it will be updated instead of inserting a new copy. + */ + if (prod->record < 0) { + QVariant id = query.lastInsertId(); + prod->record = id.toInt(); + qInfo() << "saveProduct Inserted record" << prod->record; } else { - /* - * If this was a new product, find out what record number we - * have got and set it. So when the user saves this record - * again, it will be updated instead of inserting a new copy. - */ - if (prod->record < 0) { - QVariant id = query.lastInsertId(); - prod->record = id.toInt(); - qInfo() << "saveProduct Inserted record" << prod->record; - } else { - qInfo() << "saveProduct Updated record" << prod->record; - } + qInfo() << "saveProduct Updated record" << prod->record; } return true; }
--- a/src/database/db_recipe.cpp Mon Jun 06 19:35:39 2022 +0200 +++ b/src/database/db_recipe.cpp Mon Jun 06 20:19:27 2022 +0200 @@ -356,9 +356,14 @@ bool DB_recipe::save(Recipe *reci, QDialog *dialog) { QSqlQuery query; + QString sql = ""; if (reci->record == -1) { - query.prepare("INSERT INTO recipes SET locked=:locked, st_name=:st_name, st_letter=:st_letter, " + sql = "INSERT INTO recipes SET "; + } else { + sql = "UPDATE recipes SET "; + } + sql.append("locked=:locked, st_name=:st_name, st_letter=:st_letter, " "st_guide=:st_guide, st_category=:st_category, st_category_number=:st_catnr, st_type=:st_type, " "st_og_min=:st_og_min, st_og_max=:st_og_max, st_fg_min=:st_fg_min, st_fg_max=:st_fg_max, " "st_ibu_min=:st_ibu_min, st_ibu_max=:st_ibu_max, st_color_min=:st_color_min, st_color_max=:st_color_max, " @@ -383,35 +388,14 @@ "wb_magnesium=:wb_magnesium, wb_total_alkalinity=:wb_total_alkalinity, wb_ph=:wb_ph, " "wa_acid_name=:wa_acid_name, wa_acid_perc=:wa_acid_perc, wa_base_name=:wa_base_name, " "json_fermentables=:json_fermentables, json_hops=:json_hops, json_miscs=:json_miscs, " - "json_yeasts=:json_yeasts, json_mashs=:json_mashs, uuid=:uuid"); + "json_yeasts=:json_yeasts, json_mashs=:json_mashs"); + if (reci->record == -1) { + sql.append(", uuid=:uuid"); } else { - query.prepare("UPDATE recipes SET locked=:locked, st_name=:st_name, st_letter=:st_letter, " - "st_guide=:st_guide, st_category=:st_category, st_category_number=:st_catnr, st_type=:st_type, " - "st_og_min=:st_og_min, st_og_max=:st_og_max, st_fg_min=:st_fg_min, st_fg_max=:st_fg_max, " - "st_ibu_min=:st_ibu_min, st_ibu_max=:st_ibu_max, st_color_min=:st_color_min, st_color_max=:st_color_max, " - "st_carb_min=:st_carb_min, st_carb_max=:st_carb_max, st_abv_min=:st_abv_min, st_abv_max=:st_abv_max, " - "name=:name, notes=:notes, type=:type, batch_size=:batch_size, boil_size=:boil_size, " - "boil_time=:boil_time, efficiency=:efficiency, est_og=:est_og, est_fg=:est_fg, est_abv=:est_abv, " - "est_color=:est_color, color_method=:color_method, est_ibu=:est_ibu, ibu_method=:ibu_method, " - "est_carb=:est_carb, sparge_temp=:sparge_temp, sparge_ph=:sparge_ph, " - "sparge_volume=:sparge_volume, sparge_source=:sparge_source, sparge_acid_type=:sparge_acid_type, " - "sparge_acid_perc=:sparge_acid_perc, sparge_acid_amount=:sparge_acid_amount, mash_ph=:mash_ph, " - "mash_name=:mash_name, calc_acid=:calc_acid, " - "w1_name=:w1_name, w1_amount=:w1_amount, w1_calcium=:w1_calcium, w1_sulfate=:w1_sulfate, " - "w1_chloride=:w1_chloride, w1_sodium=:w1_sodium, w1_magnesium=:w1_magnesium, " - "w1_total_alkalinity=:w1_total_alkalinity, w1_ph=:w1_ph, w1_cost=:w1_cost, " - "w2_name=:w2_name, w2_amount=:w2_amount, w2_calcium=:w2_calcium, w2_sulfate=:w2_sulfate, " - "w2_chloride=:w2_chloride, w2_sodium=:w2_sodium, w2_magnesium=:w2_magnesium, " - "w2_total_alkalinity=:w2_total_alkalinity, w2_ph=:w2_ph, w2_cost=:w2_cost, " - "wg_amount=:wg_amount, wg_calcium=:wg_calcium, wg_sulfate=:wg_sulfate, " - "wg_chloride=:wg_chloride, wg_sodium=:wg_sodium, wg_magnesium=:wg_magnesium, " - "wg_total_alkalinity=:wg_total_alkalinity, wg_ph=:wg_ph, " - "wb_calcium=:wb_calcium, wb_sulfate=:wb_sulfate, wb_chloride=:wb_chloride, wb_sodium=:wb_sodium, " - "wb_magnesium=:wb_magnesium, wb_total_alkalinity=:wb_total_alkalinity, wb_ph=:wb_ph, " - "wa_acid_name=:wa_acid_name, wa_acid_perc=:wa_acid_perc, wa_base_name=:wa_base_name, " - "json_fermentables=:json_fermentables, json_hops=:json_hops, json_miscs=:json_miscs, " - "json_yeasts=:json_yeasts, json_mashs=:json_mashs WHERE record = :recno"); + sql.append(" WHERE record = :recno"); } + + query.prepare(sql); query.bindValue(":locked", reci->locked ? 1:0); query.bindValue(":st_name", reci->st_name); query.bindValue(":st_letter", reci->st_letter); @@ -647,28 +631,27 @@ query.bindValue(":recno", reci->record); } query.exec(); - qDebug() << query.lastQuery(); if (query.lastError().isValid()) { - qWarning() << "EditRecipe" << query.lastError(); + qWarning() << "saveRecipe" << query.lastError(); QMessageBox::warning(dialog, QObject::tr("Database error"), QObject::tr("MySQL error: %1\n%2\n%3") .arg(query.lastError().nativeErrorCode()) .arg(query.lastError().driverText()) .arg(query.lastError().databaseText())); return false; + } + + /* + * If this was a new recipe, find out what record number we + * have got and set it. So when the user saves this record + * again, it will be updated instead of inserting a new copy. + */ + if (reci->record < 0) { + QVariant id = query.lastInsertId(); + reci->record = id.toInt(); + qInfo() << "saveRecipe Inserted record" << reci->record; } else { - /* - * If this was a new recipe, find out what record number we - * have got and set it. So when the user saves this record - * again, it will be updated instead of inserting a new copy. - */ - if (reci->record < 0) { - QVariant id = query.lastInsertId(); - reci->record = id.toInt(); - qInfo() << "saveRecipe Inserted record" << reci->record; - } else { - qInfo() << "saveRecipe Updated record" << reci->record; - } + qInfo() << "saveRecipe Updated record" << reci->record; } return true; }
