--- a/Seafood/populateDB/main.cpp Thu Mar 25 13:55:27 2010 -0700
+++ b/Seafood/populateDB/main.cpp Thu Mar 25 16:21:13 2010 -0700
@@ -5,13 +5,36 @@
// This program is simply responsible for creating a SQLite database for the Seafood Selector
-void insertFish(QString name, int cat, int calories,
+void createTable(QString sqlStmt)
+{
+ QSqlQuery query;
+ bool rc = true;
+ rc = query.exec(sqlStmt);
+}
+
+int getLastInsertRowId()
+{
+ int rc = -1;
+ QSqlQuery query;
+ query.exec("select last_insert_rowid()");
+ if (!query.exec()) {
+ QString errCode = "last rowid query Failed: " + query.lastError().text();
+ qWarning(errCode.toStdString().c_str());
+
+ } else {
+ query.next();
+ rc = query.value(0).toInt();
+ }
+ return rc;
+}
+
+void insertFish( QString name, int cat, int calories,
float fat, float protein, float omega3,
int cholesterol, int sodium)
{
QSqlQuery query;
- query.prepare("INSERT INTO fish (name, category, calories, fat, protein,omega3,cholesterol, sodium) "
+ query.prepare("INSERT INTO fish ( name, category, calories, fat, protein,omega3,cholesterol, sodium) "
"VALUES (:name,:category,:calories,:fat,:protein,:omega3,:cholesterol, :sodium) ");
query.bindValue(":name", name);
query.bindValue(":category",cat);
@@ -28,17 +51,30 @@
}
}
+void insertEco(int lastID, QString details)
+{
+
+ QSqlQuery query;
+
+ query.prepare("INSERT INTO ecoDetails (lastID, details) VALUES (:lastID,:details) ");
+ query.bindValue(":lastID", lastID);
+ query.bindValue(":details",details);
+ if (!query.exec())
+ {
+ qDebug() << query.lastError();
+ qFatal("Failed to add eco detail.");
+ }
+}
+
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
QSqlDatabase db;
-
// Find QSLite driver
db = QSqlDatabase::addDatabase("QSQLITE");
-
- db.setDatabaseName("C:\\workspace\\qt\\populateDB\\seafood.db");
+ db.setDatabaseName("C:\\workspace\\QtExamples\\Seafood\\populateDB\\seafood.db");
// Open databasee
if(!db.open())
@@ -49,33 +85,53 @@
qDebug() << "DB: database opened " << endl;
- QSqlQuery query;
- bool rc = true;
+ createTable("create table fish "
+ "(lastID integer primary key, "
+ "name varchar(32),"
+ "category int,"
+ "calories int,"
+ "fat float,"
+ "protein float,"
+ "omega3 float,"
+ "cholesterol int,"
+ "sodium int)");
- // rc = query.exec("drop table if exists");
+ createTable ("create table ecoDetails (eid integer primary key, "
+ "lastID integer, "
+ "details varchar(128))");
+
+ int lastID = -1;
+ insertFish("Crab, Dungeness",EBEST,86,0.96,17.4,0.3,59,295);
+ lastID = getLastInsertRowId();
+ insertEco(lastID,"Only adult males are caught in this fishery, which uses pots and traps made of biodegradable webs to avoid \'ghost fishing\' from lost gear.");
+ insertEco(lastID, "Pots and traps are also equipped with built-in exits that allow escape of undersize crabs.");
+ insertEco(lastID, "This gear has negligible bycatch and causes little habitat damage.");
- QString call("create table fish "
- "(id integer AUTO INCREMENT primary key, "
- "name varchar(32),"
- "category int,"
- "calories int,"
- "fat float,"
- "protein float,"
- "omega3 float,"
- "cholesterol int,"
- "sodium int)");
+ insertFish("Trout, rainbow (farmed)",EBEST,131,5.4,20.8,0.986,59,35);
+ lastID = getLastInsertRowId();
+ insertEco(lastID, "Although trout consume considerable amounts of wild fish in their feed, recent improvements have made them less reliant on this finite natural resource.");
+ insertEco(lastID, "Industry practices and stringent government regulations also prevent pollution and fish escapes.");
- qDebug() << call << endl;
+ insertFish("Cod, Pacific (trawl)",EOK,83,0.63,17.9,0.0,37,71);
+ lastID = getLastInsertRowId();
+ insertEco(lastID, "U.S. Pacific cod populations are healthy, and cod caught by bottom longline, traps or hook-and-line do the least environmental harm.");
+ insertEco(lastID, "U.S. Pacific cod are also caught by bottom trawl, which can damage seafloor habitats.");
- rc = query.exec(call);
-
+ insertFish("Tuna, canned light",EOK,103,1.01,22.0,0.256,47,37);
+ lastID = getLastInsertRowId();
+ insertEco(lastID, "Canned light tuna consists primarily of skipjack. These small tunas are resilient to fishing pressure because of their short life spans and rapid growth and reproduction rates.");
+ insertEco(lastID, "Most skipjack are caught by pole-and-line (a method of fishing that does relatively little ecological harm) or purse seine fleets (which can result in considerable bycatch).");
+ insertEco(lastID, "This highly migratory species is managed by international organizations.");
- insertFish("Crab, Dungeness",EBEST,86,0.96,17.4,0.3,59,295);
- insertFish("Trout, rainbow (farmed)",EBEST,131,5.4,20.8,0.986,59,35);
- insertFish("Cod, Pacific (trawl)",EOK,83,0.63,17.9,0.0,37,71);
- insertFish("Tuna, canned light",EOK,103,1.01,22.0,0.256,47,37);
- insertFish("Orange Roughy",EWORST,69,0.7,14.7,0.02,20,63);
+ insertFish( "Orange Roughy",EWORST,69,0.7,14.7,0.02,20,63);
+ lastID = getLastInsertRowId();
+ insertEco(lastID, "Orange roughy mature at a very slow rate and live extremely long lives. Intensive exploitation in past decades has greatly decreased populations.");
+ insertEco(lastID, "Management in New Zealand and Australia is good, but unregulated landings by other countries continue.");
+
insertFish("Salmon, farmed or Atlantic",EWORST,142,6.33,19.8,1.73,55,44);
+ lastID = getLastInsertRowId();
+ insertEco(lastID, "Atlantic salmon are usually farmed in large-scale, densely stocked netpens that pollute surrounding waters with waste and chemicals.");
+ insertEco(lastID, "Fish can escape and compete for resources, breed with or spread parasites to wild fish.");
- return a.exec();
+ return 0;
}