Mercurial > bmsapp / file diff

--- a/src/MainWindow.cpp	Sat Jan 28 10:36:59 2023 +0100
+++ b/src/MainWindow.cpp	Sun Jan 29 14:40:43 2023 +0100
@@ -67,6 +67,7 @@
     db = new DataBase();
     db->openDataBase(useDevelopOption);
 
+    updateDataBase();
     loadSetup();
     maintDataBase();
     openWS(useDevelopOption);
@@ -158,6 +159,74 @@
 
 
 /*
+ * Upgrade database. Check and do upgrades.
+ */
+void MainWindow::updateDataBase()
+{
+    QSqlQuery query1, query2, query3, query4;
+    int	count = 0;
+
+    qDebug() << "updateDatabase() start";
+
+    /*
+     * Make sure we have the inventory_yeastpack with initial records.
+     */
+    query1.exec("CREATE TABLE IF NOT EXISTS `inventory_yeastpack` ("
+  		"`record` int(11) NOT NULL AUTO_INCREMENT,"
+  		"`uuid` varchar(36) NOT NULL,"
+  		"`laboratory` varchar(128) NOT NULL,"
+  		"`form` tinyint(4) NOT NULL DEFAULT 0,"
+  		"`package` varchar(128) NOT NULL,"
+  		"`notes` text DEFAULT NULL,"
+  		"`cells` double NOT NULL DEFAULT 0,"
+  		"`viability` double NOT NULL DEFAULT 0.99,"
+		"`max` tinyint(4) NOT NULL DEFAULT 100,"
+  		"`size` float NOT NULL DEFAULT 0,"
+  		"PRIMARY KEY (`record`),"
+  		"UNIQUE KEY `uuid` (`uuid`),"
+  		"KEY `lab_form` (`laboratory`,`form`) USING BTREE"
+		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Yeast packages data'");
+    if (query1.lastError().isValid()) {
+	qWarning() << "  create inventory_yeastpack" << query1.lastError();
+    } else {
+	query1.exec("SELECT DISTINCT laboratory,form FROM inventory_yeasts");
+	while (query1.next()) {
+	    query2.prepare("SELECT record FROM inventory_yeastpack WHERE laboratory=:laboratory AND form=:form");
+	    query2.bindValue(":laboratory", query1.value("laboratory").toString());
+	    query2.bindValue(":form", query1.value("form").toInt());
+	    query2.exec();
+	    if (! query2.first()) {
+	    	qDebug() << "  add yeastpack" << query1.value("laboratory").toString() << query1.value("form").toInt();
+
+	    	query3.prepare("SELECT record,cells FROM inventory_yeasts WHERE laboratory=:laboratory AND form=:form");
+	    	query3.bindValue(":laboratory", query1.value("laboratory").toString());
+            	query3.bindValue(":form", query1.value("form").toInt());
+	    	query3.exec();
+	    	query3.first();
+	    	/* Should succeed */
+
+	    	query4.prepare("INSERT INTO inventory_yeastpack SET uuid=:uuid, laboratory=:laboratory, "
+			   "form=:form, package=:package, cells=:cells");
+	    	query4.bindValue(":uuid", QUuid::createUuid().toString().mid(1, 36));
+	    	query4.bindValue(":laboratory", query1.value("laboratory").toString());
+	    	query4.bindValue(":form", query1.value("form").toInt());
+	    	query4.bindValue(":package", g_yeast_forms[query1.value("form").toInt()]);
+	    	query4.bindValue(":cells", query3.value("cells").toDouble());
+	    	query4.exec();
+	    	if (query4.lastError().isValid()) {
+		    qWarning() << "  add yeastpack" << query4.lastError();
+	    	} else {
+		    count++;
+	    	}
+	    }
+    	}
+    }
+
+    qDebug() << "updateDatabase()" << count << "updates";
+}
+
+
+/*
  * On the server where bmsd runs, there is a crontask.php that does these checks
  * too. Here we do some of the same commands so that we have the results sooner.
  * Currently this takes 6 to 9 mSecs.