· 5 years ago · Mar 29, 2020, 06:38 AM
1/*
2AUTHOR: Shuval de Villiers
3DATE: 2020-03-28
4COURSE: CS2910
5Programming Assignment #2
6FILE: "prog.cpp"
7PURPOSE:
8*/
9
10#include <sqlite3.h>
11
12#include <boost/algorithm/string.hpp>
13
14#include <stdio.h>
15#include <iostream>
16#include <string>
17#include <fstream>
18#include <vector>
19
20using namespace std;
21
22static int callback(void* data, int argc, char** argv, char** azColName)
23{
24 for (int i = 0; i < argc; i++)
25 printf(" %s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
26
27 printf("\n");
28 return 0;
29}
30
31int main(int argx, char* argv[])
32{
33 sqlite3* db;
34 char* zErrMsg = 0;
35 string sql = "";
36 string fileName = "in.txt";
37 string dbName;
38 string line;
39 int ans;
40 bool doOverwrite = false;
41
42 cout << "Enter the name of the database (include .db extension):\n";
43 cin >> dbName;
44
45 // try to open database
46 int errCode = sqlite3_open(dbName.c_str(), &db);
47
48 if (errCode)
49 {
50 fprintf(stderr, "\n *** Failed to open database: %s ***\n", sqlite3_errmsg(db));
51 return(1);
52 }
53 else
54 {
55 fprintf(stderr, "\n *** Opened database successfully! ***\n");
56 }
57
58 while (1)
59 {
60 cout << "\nSelect an option (1, 2, or 3):\n"\
61 " (1) - Overwrite mode (read from text file)\n"\
62 " (2) - Add mode (read from text file)\n"\
63 " (3) - Edit mode (perform sql commands on an existing database)\n";
64 cin >> ans;
65 if (ans > 0 && ans < 4)
66 break;
67 }
68
69 if (ans != 3)
70 {
71 if (ans == 1)
72 doOverwrite = true;
73
74 ifstream myFile;
75 while (1)
76 {
77 cout << "\nEnter the name of the input text file (include .txt extension):\n";
78 cin >> fileName;
79
80 // try to open input text file
81 myFile.open(fileName);
82 if (!myFile.is_open())
83 cout << "\n *** Could not find file ***\n\n";
84 else
85 break;
86 }
87
88 string tableName;
89 vector<string> row;
90 vector<string> header;
91 vector<int> getsQuotes;
92 while (getline(myFile, line))
93 {
94 if (!sql.length())
95 {
96 tableName = line;
97
98 if (doOverwrite)
99 {
100 sql = "drop table if exists " + tableName + ";";
101 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
102 if (errCode != SQLITE_OK)
103 {
104 fprintf(stderr, "SQL error: %s\n", zErrMsg);
105 sqlite3_free(zErrMsg);
106 }
107 }
108
109 sql = "create table if not exists " + tableName + "(";
110 getline(myFile, line);
111 boost::split(row, line, boost::is_any_of(","));
112
113 getsQuotes.clear();
114 int rowSize = row.size();
115 for (int i = 0; i < rowSize; i++)
116 {
117 boost::split(header, row[i], boost::is_any_of(":"));
118 sql += header[0] + " ";
119 if (header[1] == "I")
120 sql += "integer";
121 else if (header[1] == "F")
122 sql += "real";
123 else if (header[1][0] == 'A')
124 {
125 sql += "char(" + header[1].substr(1, string::npos) + ")";
126 getsQuotes.push_back(i);
127 }
128
129 if (i < rowSize - 1)
130 sql += ",";
131 }
132 sql += ");";
133 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
134 if (errCode != SQLITE_OK)
135 {
136 fprintf(stderr, "\n *** SQL error: %s ***\n", zErrMsg);
137 sqlite3_free(zErrMsg);
138 }
139 else
140 {
141 fprintf(stdout, "\n *** Table created successfully! ***\n");
142 }
143 }
144 else
145 {
146 sql = "insert into " + tableName + " values ";
147 do {
148 if (line == "#####")
149 {
150 if (sql.length())
151 {
152 sql.replace(sql.length() - 1, 1, ";");
153 //sql = "insert into employee values (10,'jeff',1000),(11,'gorg',null);";
154 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
155 if (errCode != SQLITE_OK)
156 {
157 fprintf(stderr, " *** SQL error: %s ***\n", zErrMsg);
158 sqlite3_free(zErrMsg);
159 }
160 else
161 {
162 fprintf(stdout, " *** Records inserted successfully! ***\n");
163 }
164 }
165 sql.clear();
166 break;
167 }
168 else
169 {
170 boost::split(row, line, boost::is_any_of(","));
171 for (int x : getsQuotes)
172 row.at(x) = "'" + row.at(x) + "'"; //lol
173
174 sql += "(" + boost::algorithm::join(row, ",") + "),";
175 }
176 } while (getline(myFile, line));
177 }
178
179 }
180 }
181
182 // ignores any extra input from previous prompt
183 cin.ignore(numeric_limits<int>::max(), '\n');
184
185 cout << "\nEnter SQL commands then type 'CLOSE' to save and exit\n$ ";
186
187 while (getline(cin, sql))
188 {
189 cout << endl;
190 transform(sql.begin(), sql.end(), sql.begin(), ::tolower);
191 if (sql == "close")
192 break;
193
194 errCode = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
195 if (errCode != SQLITE_OK)
196 {
197 fprintf(stderr, " *** SQL error: %s ***\n", zErrMsg);
198 sqlite3_free(zErrMsg);
199 }
200 else
201 {
202 fprintf(stdout, " *** Operation done successfully! ***\n");
203 }
204 cout << "\n$ ";
205 }
206
207 sqlite3_close(db);
208 return 0;
209}