· 5 years ago · Mar 31, 2020, 06:44 PM
1/*
2AUTHOR: Shuval de Villiers
3DATE: 2020-03-28
4COURSE: CS2910
5Programming Assignment #2
6FILE: "prog.cpp"
7PURPOSE:
8 Database creator/editor program. Allows the user to run sql commands on an existing .db file or create a new one.
9 There are 3 modes the user can choose:
10 (1) Read from a csv file and overwrite the existing table(s) if they exist
11 (2) Read from a csv file and add to the existing tables(s) if they exist
12 (3) Do not read from a csv file just run sql commands on a database
13*/
14
15#include <sqlite3.h>
16
17#include <boost/algorithm/string.hpp>
18
19#include <stdio.h>
20#include <iostream>
21#include <string>
22#include <fstream>
23#include <vector>
24
25using namespace std;
26
27// query output
28static int callback(void* data, int argc, char** argv, char** azColName)
29{
30 for (int i = 0; i < argc; i++)
31 printf(" %s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
32
33 printf("\n");
34 return 0;
35}
36
37int main(int argx, char* argv[])
38{
39 sqlite3* db;
40 char* zErrMsg = 0;
41 string sql = ""; // used for executing sql commands
42 string fileName; // input csv file
43 string dbName; // database file
44 string line; // for parsing
45 string ans; // user input
46 int mode; // mode selected
47 bool doOverwrite = false;
48
49 // get database name
50 cout << "Enter the name of the database (include .db extension):\n";
51 cin >> dbName;
52
53 // try to open database
54 int errCode = sqlite3_open(dbName.c_str(), &db);
55
56 if (errCode)
57 {
58 fprintf(stderr, "\n *** Failed to open database: %s ***\n", sqlite3_errmsg(db));
59 return(1);
60 }
61 else
62 {
63 fprintf(stderr, "\n *** Opened database successfully! ***\n");
64 }
65
66 // loop until valid input
67 while (1)
68 {
69 // ignores any extra input from previous prompt
70 cin.ignore(numeric_limits<int>::max(), '\n');
71
72 // get mode
73 cout << "\nSelect an option (1, 2, or 3):\n"\
74 " (1) - Overwrite mode (read from csv file)\n"\
75 " (2) - Add mode (read from csv file)\n"\
76 " (3) - Edit mode (perform sql commands on an existing database)\n";
77 cin >> ans;
78
79 // convert string to int
80 try { mode = stoi(ans); }
81 catch (...) {}
82
83 // invalid number
84 if (mode > 0 && mode < 4)
85 break;
86 }
87
88 // read from csv file if not in Edit Mode
89 if (mode != 3)
90 {
91 // overwrite mode (doOverwrite is false by default)
92 if (mode == 1)
93 doOverwrite = true;
94
95 // loop until file is opened
96 ifstream myFile;
97 while (1)
98 {
99 // get input csv file
100 cout << "\nEnter the name of the input csv file (include .csv extension):\n";
101 cin >> fileName;
102
103 // try to open input csv file
104 myFile.open(fileName);
105 if (!myFile.is_open())
106 cout << "\n *** Could not find file ***\n\n";
107 else
108 break;
109 }
110
111 string tableName; // name of table
112 vector<string> row; // vector of values for a row
113 vector<string> header; // contains { <fieldname> , <datatype> }
114 vector<int> getsQuotes; // determines which values should get wrapped with single quotes
115
116 // loop until end of file is reached
117 while (getline(myFile, line))
118 {
119 // first lets build the 'create table' sql command
120 // sql is cleared when "#####" is found
121 if (!sql.length())
122 {
123 // first line is the table name
124 tableName = line;
125
126 // in overwrite mode the table will be dropped if it already exists
127 if (doOverwrite)
128 {
129 sql = "drop table if exists " + tableName + ";";
130 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
131 if (errCode != SQLITE_OK)
132 {
133 fprintf(stderr, "SQL error: %s\n", zErrMsg);
134 sqlite3_free(zErrMsg);
135 }
136 }
137
138 sql = "create table if not exists " + tableName + "(";
139
140 // the next line will have the relation schema
141 getline(myFile, line);
142 boost::split(row, line, boost::is_any_of(","));
143
144 // new relation
145 getsQuotes.clear();
146
147 // getting the column names and associated datatypes
148 int rowSize = row.size();
149 for (int i = 0; i < rowSize; i++)
150 {
151 boost::split(header, row[i], boost::is_any_of(":"));
152 sql += header[0] + " ";
153
154 if (header[1] == "I")
155 sql += "integer";
156 else if (header[1] == "F")
157 sql += "real";
158 else if (header[1][0] == 'A')
159 {
160 sql += "char(" + header[1].substr(1, string::npos) + ")";
161 getsQuotes.push_back(i);
162 }
163
164 if (i < rowSize - 1)
165 sql += ",";
166 }
167 sql += ");";
168
169 // run sql to create table
170 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
171 if (errCode != SQLITE_OK)
172 {
173 fprintf(stderr, "\n *** SQL error: %s ***\n", zErrMsg);
174 sqlite3_free(zErrMsg);
175 }
176 else
177 {
178 fprintf(stdout, "\n *** Table created successfully! ***\n");
179 }
180 }
181 // now lets insert some data into our table
182 else
183 {
184 sql = "insert into " + tableName + " values ";
185
186 // loop until end of file or "#####"
187 do {
188 if (line == "#####")
189 {
190 // are there values to be inserted?
191 if (sql[sql.length()-1] == ',')
192 {
193 sql.replace(sql.length() - 1, 1, ";");
194
195 // run sql to insert values
196 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
197 if (errCode != SQLITE_OK)
198 {
199 fprintf(stderr, " *** SQL error: %s ***\n", zErrMsg);
200 sqlite3_free(zErrMsg);
201 }
202 else
203 {
204 fprintf(stdout, " *** Records inserted successfully! ***\n");
205 }
206 }
207 // clear sql so that a new table can be created
208 sql.clear();
209 break;
210 }
211 else
212 {
213 // wrap text in single quotes
214 boost::split(row, line, boost::is_any_of(","));
215 for (int x : getsQuotes)
216 row.at(x) = "'" + row.at(x) + "'"; //lol
217
218 // append tuple
219 sql += "(" + boost::algorithm::join(row, ",") + "),";
220 }
221 } while (getline(myFile, line));
222 }
223 }
224 }
225
226 // ignores any extra input from previous prompt
227 cin.ignore(numeric_limits<int>::max(), '\n');
228
229 cout << "\nEnter SQL commands then type 'CLOSE' to save and exit\n$ ";
230
231 // loop breaks when user enters "close"
232 while (getline(cin, sql))
233 {
234 cout << endl;
235
236 // transform to lowercase
237 transform(sql.begin(), sql.end(), sql.begin(), ::tolower);
238 if (sql == "close")
239 break;
240
241 // run sql
242 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
243 if (errCode != SQLITE_OK)
244 {
245 fprintf(stderr, " *** SQL error: %s ***\n", zErrMsg);
246 sqlite3_free(zErrMsg);
247 }
248 else
249 {
250 fprintf(stdout, " *** Operation done successfully! ***\n");
251 }
252 cout << "\n$ ";
253 }
254
255 sqlite3_close(db);
256 return 0;
257}