· 5 years ago · Mar 29, 2020, 11:42 PM
1//check for #####
2//get output from SQL queries (sample code)
3//comment code
4//readme
5
6
7#include <iostream>
8#include <fstream>
9#include <string>
10#include <vector>
11#include <fstream>
12
13#include <boost/algorithm/string.hpp>
14#include <boost/archive/xml_iarchive.hpp>
15#include <boost/archive/xml_oarchive.hpp>
16#include <boost/serialization/map.hpp>
17#include <sqlite3.h>
18
19using namespace std;
20using std::vector;
21
22string makeSchema(string);
23string makeInserts(vector<string>);
24
25vector<string> attrFormats;
26
27// Create a callback function
28int callback(void *NotUsed, int argc, char **argv, char **azColName){
29
30
31 // Return successful
32 return 0;
33}
34
35int main() {
36 string db_name;
37
38 string fileName;
39
40 int rc;
41
42 char *zErrMsg = 0;
43
44 sqlite3 *db;
45
46 int mode;
47
48 while(true){
49 cout << "Enter your existing database name or enter a new database name (with .db extension): ";
50 cin >> db_name;
51
52 if(db_name.size() < 4 || db_name.substr(db_name.size() - 3) != ".db"){
53 cout << db_name << " is not a valid name for a database. Try again\n ex: myDatabase.db" << endl;
54 continue;
55 }
56
57 rc = sqlite3_open(db_name.c_str(), &db);
58 if( rc){
59 // Show an error message
60 cout << "Failed to open: " << db_name << endl;
61 cout << "DB Error: " << sqlite3_errmsg(db) << endl;
62 // Close the connection
63 sqlite3_close(db);
64 // Return an error
65 return(1);
66 }else{
67 cout << "**Opened Database -" << db_name << "- Succesfully**" << endl;
68 break;
69 }
70 }
71
72 while(true){
73 cout << "\nChoose a mode:\n" \
74 "1. Overwrite mode - (Reads data from .csv file)\n"\
75 "2. Concatenate mode - (Reads data from .csv file)\n"\
76 "3. Query/Command mode - (Run queries/commands in SQL against an existing database)\n\n"\
77 "Enter mode number: ";
78 cin >> mode;
79
80 if(mode == 1 || mode == 2 || mode == 3){
81 cout << "Entering mode " << mode << "\n" << endl;
82 break;
83 }
84 }
85
86 vector<string> strs;
87 string line;
88 ifstream inputFile;
89
90 string sql = "";
91
92 if(mode == 1 || mode == 2){
93 while(true){
94 cout << "Enter filename (with .csv extension included): ";
95 cin >> fileName;
96 inputFile.open(fileName);
97 if(inputFile.is_open()) break;
98 cout << "File: " << fileName << " not found.\n\n";
99 }
100
101 cout << fileName << " succesfully opened. \n" << endl;
102
103 //loading contents of file into string vector
104 while ( getline(inputFile,line) ) {
105 if(line == "#####") break;
106 strs.push_back(line);
107 }
108
109 if(mode == 1){
110 string dropCmd = "drop table if exists " + strs[0];
111 rc = sqlite3_exec(db, dropCmd.c_str(), callback, 0, &zErrMsg);
112 }
113
114 sql = "CREATE TABLE " + strs[0] + " (" + makeSchema(strs[1]) + ");";
115 rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
116 sql = makeInserts(strs);
117 rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
118
119 cout << strs.size() - 3 << " tuples succsefully added to " << db_name << "'s " << strs[0] << " table.\n" << endl;
120
121
122
123 }else{
124 //EDIT MODE
125 cin.ignore(numeric_limits<int>::max(), '\n');
126
127 string command;
128
129 while (getline(cin, sql))
130 {
131 cout << endl;
132
133 // transform to lowercase
134 transform(command.begin(), command.end(), command.begin(), ::tolower);
135 if (command == "close")
136 break;
137
138 // run sql
139 rc = sqlite3_exec(db, command.c_str(), callback, 0, &zErrMsg);
140 if (rc != SQLITE_OK)
141 {
142 fprintf(stderr, " *** SQL error: %s ***\n", zErrMsg);
143 sqlite3_free(zErrMsg);
144 }
145 else
146 {
147 fprintf(stdout, " *** Operation done successfully! ***\n");
148 }
149 cout << "\n$ ";
150 }
151
152 }
153
154 cout << "closing SQL connection.\n";
155 sqlite3_close(db);
156
157 return 0;
158}
159
160string makeSchema(string s){
161 vector<string> tmp;
162 string tmpString = "";
163 boost::split(tmp,s,boost::is_any_of(",:"));
164
165 for(int j = 0; j < tmp.size(); j++){
166 if(j % 2 == 0){
167 tmpString += tmp[j];
168 }else{
169 if(tmp[j][0] == 'I'){
170 tmpString += " INT";
171 attrFormats.push_back("");
172 }else if(tmp[j][0] == 'F'){
173 tmpString += " REAL";
174 attrFormats.push_back("");
175 }else if(tmp[j][0] == 'A'){
176 attrFormats.push_back("'");
177 tmpString += " CHAR(";
178 for(int i = 1; i < tmp[j].length(); i++) tmpString += tmp[j][i];
179 tmpString += ")";
180 }
181 if(j != tmp.size() - 1) tmpString += ",";
182 }
183 }
184 return tmpString;
185}
186
187string makeInserts(vector<string> vec){
188 string tmpString = "INSERT INTO " + vec[0] + " VALUES ";
189 for (int i = 2; i < vec.size() - 1; i++){
190 vector<string> tmp;
191 boost::split(tmp,vec[i],boost::is_any_of(","));
192
193 tmpString += "(";
194
195 for(int j = 0; j < tmp.size(); j++){
196 tmpString += attrFormats[j];
197 tmpString += tmp[j];
198 tmpString += attrFormats[j];
199 if(j != tmp.size() - 1) tmpString += ", ";
200 }
201
202 tmpString += ")";
203 if(i != vec.size() - 2){
204 tmpString += ", ";
205 }else{
206 tmpString += ";";
207 }
208 }
209 return tmpString;
210}