· 5 years ago · Mar 30, 2020, 01:24 AM
1/*
2Author: Matthew Abbott
3Date: March 27th, 2020
4Course: CS2910
5Instructor: Franco Carlacci
6Assignment: Programming Assignment #2
7
8Description:
9 SQLITE3 Database Creator/Editor. This program offers three modes of interaction
10 1 - Overwrite existing tables with new schema/tuples from a .csv file.
11 2 - Add to an existing table with new data from a .csv file.
12 3 - Run queries and/or commands on an existing table and recieve output.
13*/
14
15
16#include <iostream>
17#include <fstream>
18#include <string>
19#include <vector>
20#include <fstream>
21
22#include <boost/algorithm/string.hpp>
23#include <boost/archive/xml_iarchive.hpp>
24#include <boost/archive/xml_oarchive.hpp>
25#include <boost/serialization/map.hpp>
26#include <sqlite3.h>
27
28using namespace std;
29using std::vector;
30
31//function prototypes
32string makeSchema(string);
33string makeInserts(vector<string>);
34
35vector<string> attrFormats;
36
37
38// Create a callback function
39int callback(void *NotUsed, int argc, char **argv, char **azColName){
40
41 for (int i = 0; i < argc; i++)
42 printf(" %s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
43
44 // Return successful
45 return 0;
46}
47
48int main() {
49 string db_name; //database name variable for user input
50
51 string fileName; //filename variable for user input
52
53 int rc; //rc will be true later on if our database fails to open
54
55 char *zErrMsg = 0; //pointer to an array of chars that stores sqlite's z errors for debugging purposes
56
57 sqlite3 *db; //pointer to the sqlite3 database connection
58
59 int mode; //mode is used to store what mode the user selects
60
61 while(true){
62 cout << "Enter your existing database name or enter a new database name (with .db extension): ";
63 cin >> db_name;
64
65 // This is just a simple syntax check to make sure the extension is correct and that there is a title
66 if(db_name.size() < 4 || db_name.substr(db_name.size() - 3) != ".db"){
67 cout << db_name << " is not a valid name for a database. Try again\n ex: myDatabase.db" << endl;
68 continue;
69 }
70
71 rc = sqlite3_open(db_name.c_str(), &db); //sending command to sqlite3's API and storing the output in rc
72 if( rc){ //this condition is true if the database failed to open
73 cout << "Failed to open: " << db_name << endl;
74 cout << "DB Error: " << sqlite3_errmsg(db) << endl;
75 // Close the connection to the database so we can try connecting again
76 sqlite3_close(db);
77
78 }else{
79 cout << "**Opened Database -" << db_name << "- Succesfully**" << endl;
80 break;
81 }
82 }
83
84 while(true){
85 cout << "\nChoose a mode:\n" \
86 "1. Overwrite mode - (Reads data from .csv file)\n"\
87 "2. Concatenate mode - (Reads data from .csv file)\n"\
88 "3. Query/Command mode - (Run queries/commands in SQL against an existing database)\n\n"\
89 "Enter mode number: ";
90 cin >> mode;
91
92 //note: I didn't use any less than or greater than comparisons in case of the
93 //user entering non integer values
94 if(mode == 1 || mode == 2 || mode == 3){
95 cout << "Entering mode " << mode << "\n" << endl;
96 break;
97 }
98 }
99
100 vector<string> strs; //strs is used to store lines from the text file
101 string line; //temporary storage for lines in the text file that are appended to strs
102 ifstream inputFile; //using ifstream here instead of cin so we can take user input and use cin for file input
103
104 string sql = ""; //string for storing SQL commands to be sent to our database
105
106 if(mode == 1 || mode == 2){
107 //mode 1 and 2 both read from file so they share most of the same code
108 //(besides dropping previous tables if they exist already)
109 while(true){ //infinite loop that can only be broken when a valid file path is input by user
110 cout << "Enter filename (with .csv extension included): ";
111 cin >> fileName;
112 inputFile.open(fileName);
113 if(inputFile.is_open()) break;
114 cout << "File: " << fileName << " not found.\n\n";
115 }
116
117 cout << fileName << " succesfully opened. \n" << endl;
118
119 //loading contents of file into string vector
120 while ( getline(inputFile,line) ) {
121 if(line == "#####") break;
122 strs.push_back(line);
123 }
124
125 if(mode == 1){ //if we are in overwrite mode we simply send the "DROP TABLE IF EXISTS" command
126 string dropCmd = "drop table if exists " + strs[0];
127 rc = sqlite3_exec(db, dropCmd.c_str(), callback, 0, &zErrMsg);
128 }
129
130 sql = "CREATE TABLE " + strs[0] + " (" + makeSchema(strs[1]) + ");";
131 //note: even if the table is already created this command will not overwrite that table
132 rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
133
134 //calling our makeInserts function and passing the generated command to SQL
135 sql = makeInserts(strs);
136 rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
137
138 cout << strs.size() - 3 << " tuples succesfully added to " << db_name << "'s " << strs[0] << " table.\n" << endl;
139
140
141 }else{
142 //Mode 3
143 cin.ignore(numeric_limits<int>::max(), '\n'); //clearing the input stream from previous inputs
144
145 string command; //string for storing commands to be sent to sql
146
147 while (getline(cin, command)) //this loops until the user enters "close"
148 {
149 cout << endl;
150
151 // converting command to lowercase (just to make conditions on string easier to check)
152 transform(command.begin(), command.end(), command.begin(), ::tolower);
153 if (command == "close")
154 break;
155
156 // sending command to sql
157 rc = sqlite3_exec(db, command.c_str(), callback, 0, &zErrMsg);
158 if (rc != SQLITE_OK)
159 {
160 cout << " Error in SQL: " << zErrMsg << endl;
161 sqlite3_free(zErrMsg); //free up zErrMsg in case of future errors from user input
162 }
163 else
164 {
165 cout << " ** SQL recieved command without error ** " << endl;
166 }
167 cout << "\nEnter Operation: ";
168 }
169
170 }
171
172 cout << "closing SQL connection.\n";
173 sqlite3_close(db);
174
175 return 0;
176}
177
178string makeSchema(string s){
179 /*
180 This function takes in the .csv file's schema string (s) and formats it into
181 proper SQL syntax before returning it.
182
183 */
184 vector<string> tmp; //temporary storage for each token in s (split on ';' and ',')
185 string newCommand = ""; //this will store the sql string as it's built
186 boost::split(tmp,s,boost::is_any_of(",:"));
187
188 for(int j = 0; j < tmp.size(); j++){
189 //in this for loop we alternate between dealing with datatype and value
190 //so I use modulo to alternate between the two
191 if(j % 2 == 0){
192 //this is just the values so it can be added as it is to the command as is
193 newCommand += tmp[j];
194 }else{
195 //A series of conditions to check the given datatype
196 //each condition pushes to the attrFormats vector (global variable) so we know what
197 //character to encase their values with when used for other commands
198 if(tmp[j][0] == 'I'){
199 newCommand += " INT";
200 attrFormats.push_back("");
201 }else if(tmp[j][0] == 'F'){
202 newCommand += " REAL";
203 attrFormats.push_back("");
204 }else if(tmp[j][0] == 'A'){
205 //this condition has an extra block that handles the amount of characters
206 //specified in the schema
207 attrFormats.push_back("'");
208 newCommand += " CHAR(";
209 for(int i = 1; i < tmp[j].length(); i++) newCommand += tmp[j][i];
210 newCommand += ")";
211 }
212 //formatting each attribute/value with a comma unless they are the last element of the schema
213 if(j != tmp.size() - 1) newCommand += ",";
214 }
215 }
216 return newCommand;
217}
218
219string makeInserts(vector<string> vec){
220 /*
221 This function takes in the .csv file's tuples and formats it into
222 proper SQL syntax before returning it, so it can be inserted.
223
224 The passed in string vector (vec) contains all lines from the processed .csv file.
225 It uses the tuple values and table title to build its insert string.
226 */
227 string newCommand = "INSERT INTO " + vec[0] + " VALUES ";
228 //newCommand stores the command to be returned at the end of the function
229
230 //I start the for loop at 2 so we skip past the schema and table title
231 for (int i = 2; i < vec.size() - 1; i++){
232 vector<string> tmp; //vector to store split up values from the vec file
233 boost::split(tmp,vec[i],boost::is_any_of(","));
234
235 newCommand += "(";
236
237 //in this inner loop we use the attrFormats vector to properly encase our
238 //tuple values in the right character ( either none or " ' ")
239 for(int j = 0; j < tmp.size(); j++){
240 newCommand += attrFormats[j];
241 newCommand += tmp[j];
242 newCommand += attrFormats[j];
243 if(j != tmp.size() - 1) newCommand += ", ";
244 }
245
246 newCommand += ")";
247
248 //these conditions are for either appending a " , " or a " ; " depending on if it's
249 //the last string in the vector or not
250 if(i != vec.size() - 2){
251 newCommand += ", ";
252 }else{
253 newCommand += ";";
254 }
255 }
256 return newCommand;
257}