3 |
3 |
4 #include "database.h" |
4 #include "database.h" |
5 |
5 |
6 // This program is simply responsible for creating a SQLite database for the Seafood Selector |
6 // This program is simply responsible for creating a SQLite database for the Seafood Selector |
7 |
7 |
8 void insertFish(QString name, int cat, int calories, |
8 void createTable(QString sqlStmt) |
|
9 { |
|
10 QSqlQuery query; |
|
11 bool rc = true; |
|
12 rc = query.exec(sqlStmt); |
|
13 } |
|
14 |
|
15 int getLastInsertRowId() |
|
16 { |
|
17 int rc = -1; |
|
18 QSqlQuery query; |
|
19 query.exec("select last_insert_rowid()"); |
|
20 if (!query.exec()) { |
|
21 QString errCode = "last rowid query Failed: " + query.lastError().text(); |
|
22 qWarning(errCode.toStdString().c_str()); |
|
23 |
|
24 } else { |
|
25 query.next(); |
|
26 rc = query.value(0).toInt(); |
|
27 } |
|
28 return rc; |
|
29 } |
|
30 |
|
31 void insertFish( QString name, int cat, int calories, |
9 float fat, float protein, float omega3, |
32 float fat, float protein, float omega3, |
10 int cholesterol, int sodium) |
33 int cholesterol, int sodium) |
11 { |
34 { |
12 QSqlQuery query; |
35 QSqlQuery query; |
13 |
36 |
14 query.prepare("INSERT INTO fish (name, category, calories, fat, protein,omega3,cholesterol, sodium) " |
37 query.prepare("INSERT INTO fish ( name, category, calories, fat, protein,omega3,cholesterol, sodium) " |
15 "VALUES (:name,:category,:calories,:fat,:protein,:omega3,:cholesterol, :sodium) "); |
38 "VALUES (:name,:category,:calories,:fat,:protein,:omega3,:cholesterol, :sodium) "); |
16 query.bindValue(":name", name); |
39 query.bindValue(":name", name); |
17 query.bindValue(":category",cat); |
40 query.bindValue(":category",cat); |
18 query.bindValue(":calories",calories); |
41 query.bindValue(":calories",calories); |
19 query.bindValue(":fat",fat); |
42 query.bindValue(":fat",fat); |
26 qDebug() << query.lastError(); |
49 qDebug() << query.lastError(); |
27 qFatal("Failed to add fish."); |
50 qFatal("Failed to add fish."); |
28 } |
51 } |
29 } |
52 } |
30 |
53 |
|
54 void insertEco(int lastID, QString details) |
|
55 { |
|
56 |
|
57 QSqlQuery query; |
|
58 |
|
59 query.prepare("INSERT INTO ecoDetails (lastID, details) VALUES (:lastID,:details) "); |
|
60 query.bindValue(":lastID", lastID); |
|
61 query.bindValue(":details",details); |
|
62 if (!query.exec()) |
|
63 { |
|
64 qDebug() << query.lastError(); |
|
65 qFatal("Failed to add eco detail."); |
|
66 } |
|
67 } |
|
68 |
31 int main(int argc, char *argv[]) |
69 int main(int argc, char *argv[]) |
32 { |
70 { |
33 QCoreApplication a(argc, argv); |
71 QCoreApplication a(argc, argv); |
34 QSqlDatabase db; |
72 QSqlDatabase db; |
35 |
73 |
36 |
|
37 // Find QSLite driver |
74 // Find QSLite driver |
38 db = QSqlDatabase::addDatabase("QSQLITE"); |
75 db = QSqlDatabase::addDatabase("QSQLITE"); |
39 |
76 |
40 |
77 db.setDatabaseName("C:\\workspace\\QtExamples\\Seafood\\populateDB\\seafood.db"); |
41 db.setDatabaseName("C:\\workspace\\qt\\populateDB\\seafood.db"); |
|
42 |
78 |
43 // Open databasee |
79 // Open databasee |
44 if(!db.open()) |
80 if(!db.open()) |
45 { |
81 { |
46 qDebug() << "DB: failed to open" << endl; |
82 qDebug() << "DB: failed to open" << endl; |
47 exit (1); |
83 exit (1); |
48 } |
84 } |
49 |
85 |
50 qDebug() << "DB: database opened " << endl; |
86 qDebug() << "DB: database opened " << endl; |
51 |
87 |
52 QSqlQuery query; |
88 createTable("create table fish " |
53 bool rc = true; |
89 "(lastID integer primary key, " |
|
90 "name varchar(32)," |
|
91 "category int," |
|
92 "calories int," |
|
93 "fat float," |
|
94 "protein float," |
|
95 "omega3 float," |
|
96 "cholesterol int," |
|
97 "sodium int)"); |
54 |
98 |
55 // rc = query.exec("drop table if exists"); |
99 createTable ("create table ecoDetails (eid integer primary key, " |
|
100 "lastID integer, " |
|
101 "details varchar(128))"); |
56 |
102 |
57 QString call("create table fish " |
103 int lastID = -1; |
58 "(id integer AUTO INCREMENT primary key, " |
104 insertFish("Crab, Dungeness",EBEST,86,0.96,17.4,0.3,59,295); |
59 "name varchar(32)," |
105 lastID = getLastInsertRowId(); |
60 "category int," |
106 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."); |
61 "calories int," |
107 insertEco(lastID, "Pots and traps are also equipped with built-in exits that allow escape of undersize crabs."); |
62 "fat float," |
108 insertEco(lastID, "This gear has negligible bycatch and causes little habitat damage."); |
63 "protein float," |
|
64 "omega3 float," |
|
65 "cholesterol int," |
|
66 "sodium int)"); |
|
67 |
109 |
68 qDebug() << call << endl; |
110 insertFish("Trout, rainbow (farmed)",EBEST,131,5.4,20.8,0.986,59,35); |
|
111 lastID = getLastInsertRowId(); |
|
112 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."); |
|
113 insertEco(lastID, "Industry practices and stringent government regulations also prevent pollution and fish escapes."); |
69 |
114 |
70 rc = query.exec(call); |
115 insertFish("Cod, Pacific (trawl)",EOK,83,0.63,17.9,0.0,37,71); |
|
116 lastID = getLastInsertRowId(); |
|
117 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."); |
|
118 insertEco(lastID, "U.S. Pacific cod are also caught by bottom trawl, which can damage seafloor habitats."); |
71 |
119 |
|
120 insertFish("Tuna, canned light",EOK,103,1.01,22.0,0.256,47,37); |
|
121 lastID = getLastInsertRowId(); |
|
122 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."); |
|
123 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)."); |
|
124 insertEco(lastID, "This highly migratory species is managed by international organizations."); |
72 |
125 |
73 insertFish("Crab, Dungeness",EBEST,86,0.96,17.4,0.3,59,295); |
126 insertFish( "Orange Roughy",EWORST,69,0.7,14.7,0.02,20,63); |
74 insertFish("Trout, rainbow (farmed)",EBEST,131,5.4,20.8,0.986,59,35); |
127 lastID = getLastInsertRowId(); |
75 insertFish("Cod, Pacific (trawl)",EOK,83,0.63,17.9,0.0,37,71); |
128 insertEco(lastID, "Orange roughy mature at a very slow rate and live extremely long lives. Intensive exploitation in past decades has greatly decreased populations."); |
76 insertFish("Tuna, canned light",EOK,103,1.01,22.0,0.256,47,37); |
129 insertEco(lastID, "Management in New Zealand and Australia is good, but unregulated landings by other countries continue."); |
77 insertFish("Orange Roughy",EWORST,69,0.7,14.7,0.02,20,63); |
130 |
78 insertFish("Salmon, farmed or Atlantic",EWORST,142,6.33,19.8,1.73,55,44); |
131 insertFish("Salmon, farmed or Atlantic",EWORST,142,6.33,19.8,1.73,55,44); |
|
132 lastID = getLastInsertRowId(); |
|
133 insertEco(lastID, "Atlantic salmon are usually farmed in large-scale, densely stocked netpens that pollute surrounding waters with waste and chemicals."); |
|
134 insertEco(lastID, "Fish can escape and compete for resources, breed with or spread parasites to wild fish."); |
79 |
135 |
80 return a.exec(); |
136 return 0; |
81 } |
137 } |