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