· 8 years ago · Jan 24, 2018, 08:22 AM
1#include <stdlib.h>
2#include <stdio.h>
3#include <string.h>
4#include <stdarg.h>
5#include <libpq-fe.h>
6
7#define MAX_LINE_SIZE 100
8#define MAX_FILE_LINES 100
9#define FILE_EXTENSION_CSV "csv"
10#define TRUE 1
11#define FALSE 0
12
13typedef struct Columns
14{
15 char** Names;
16 int Count;
17} Columns;
18
19typedef struct ListOfColumns
20{
21 Columns** List;
22 int Count;
23} ListOfColumns;
24
25int GetColumnsCount(char* line)
26{
27 int columnsCount = 1;
28 int i;
29 for (i = 0; i < strlen(line); i++)
30 {
31 if (line[i] == ';')
32 columnsCount++;
33 }
34
35 return columnsCount;
36}
37
38char** GetColumnsNames(char* line, int columnsCount)
39{
40 char** names = malloc(columnsCount * sizeof(char*));
41 int columnIndex = 0;
42
43 char* columnName = strtok(line, ";");
44 while (columnName != NULL)
45 {
46 names[columnIndex] = malloc(sizeof(char) * strlen(columnName));
47 strcpy(names[columnIndex++], columnName);
48 columnName = strtok(NULL, ";");
49 }
50
51 return names;
52}
53
54// Remove end of line character sequence from the specified line
55void RemoveEndOfLineFrom(char* line)
56{
57 int length = strlen(line);
58 if (length == 0)
59 return;
60
61 char* last = &line[length - 1];
62 if (*last == '\n') // Unix and Windows
63 {
64 *last = '\0';
65 if (length > 1)
66 {
67 char* beforeLast = &line[length - 2];
68 if (*beforeLast == '\r') // Windows
69 *beforeLast = '\0';
70 }
71 }
72}
73
74Columns* ParseLine(char* line)
75{
76 RemoveEndOfLineFrom(line);
77
78 // Parse line
79 Columns* columns = malloc(sizeof(Columns));
80 columns->Count = GetColumnsCount(line);
81 columns->Names = GetColumnsNames(line, columns->Count);
82
83 return columns;
84}
85
86void DoSQL(PGconn *conn, char *command)
87{
88 printf("%s\n", command);
89
90 PGresult* result = PQexec(conn, command);
91 printf("status is : %s\n", PQresStatus(PQresultStatus(result)));
92 printf("#rows affected: %s\n", PQcmdTuples(result));
93 printf("result message: %s\n", PQresultErrorMessage(result));
94
95 switch(PQresultStatus(result)) {
96 case PGRES_TUPLES_OK:
97 {
98 int n = 0, m = 0;
99 int nrows = PQntuples(result);
100 int nfields = PQnfields(result);
101 printf("number of rows returned = %d\n", nrows);
102 printf("number of fields returned = %d\n", nfields);
103 for(m = 0; m < nrows; m++) {
104 for(n = 0; n < nfields; n++)
105 printf(" %s = %s", PQfname(result, n),PQgetvalue(result,m,n));
106 printf("\n");
107 }
108 }
109 }
110 PQclear(result);
111}
112
113char* GetSqlQuery(char* tableName, ...)
114{
115 char* query = malloc(sizeof(char) * 1000);
116 va_list list;
117 va_start(list, tableName);
118
119 char* str;
120 do
121 {
122 str = va_arg(list, char*);
123 strcat(query, str);
124 }
125 while (strcmp(str, ";") != 0);
126
127 va_end(list);
128 return query;
129}
130
131char* GetSqlQueryCreateTable(char* tableName, Columns* columns)
132{
133 char* query = malloc(sizeof(char) * 1000); // C sucks, Go calculate size manually LOL
134 strcat(query, "CREATE TABLE ");
135 strcat(query, tableName);
136 strcat(query, "\n(\n");
137
138 strcat(query, columns->Names[0]);
139 strcat(query, " VARCHAR(30) UNIQUE,\n");
140
141 int i;
142 for (i = 1; i < columns->Count - 1 ;i++)
143 {
144 strcat(query, columns->Names[i]);
145 strcat(query, " VARCHAR(30),\n");
146 }
147 strcat(query, columns->Names[i]);
148 strcat(query, " VARCHAR(30)\n");
149
150 strcat(query, ")");
151 return query;
152}
153
154char* GetSqlQueryInsert(char* tableName, Columns* columns)
155{
156 char* query = calloc(1000, sizeof(char)); // C sucks, Go calculate size manually LOL
157 strcat(query, "INSERT INTO ");
158 strcat(query, tableName);
159 strcat(query, " VALUES(");
160
161 int i;
162 for (i = 0; i < columns->Count - 1; i++)
163 {
164 strcat(query, "'");
165 strcat(query, columns->Names[i]);
166 strcat(query, "', ");
167 }
168 strcat(query, "'");
169 strcat(query, columns->Names[i]);
170 strcat(query, "'");
171 strcat(query, ")");
172 return query;
173}
174
175char* GetSqlQueryDropTable(char* tableName)
176{
177 char* command = "DROP TABLE ";
178 char* query = calloc(strlen(command) + strlen(tableName) + 1, sizeof(char)); // + 1 for NULL
179 strcat(query, command);
180 strcat(query, tableName);
181
182 return query;
183}
184
185char* ChopFileExtension(char* fileName)
186{
187 char* extensionSuffix = strrchr(fileName, '.');
188 while(*extensionSuffix != '\0')
189 {
190 *extensionSuffix = '\0';
191 extensionSuffix++;
192 }
193
194 return fileName;
195}
196
197ListOfColumns* ReadFile(FILE* file)
198{
199 char line[MAX_LINE_SIZE];
200 ListOfColumns* columnsList = malloc(sizeof(ListOfColumns));
201 columnsList->List = malloc(sizeof(Columns*) * MAX_FILE_LINES);
202
203 int columnsIndex = 0;
204 // Read file line by line
205 while (fgets(line, MAX_LINE_SIZE, file) != NULL)
206 {
207 Columns* columns = ParseLine(line);
208 columnsList->List[columnsIndex++] = columns;
209 columnsList->Count++;
210 }
211
212 return columnsList;
213}
214
215// Returns:
216// NOT NULL = SUCCESS
217// NULL = FAILURE
218FILE* OpenFile(char* fileName)
219{
220 FILE* file = fopen(fileName, "r");
221 if (file == NULL)
222 {
223 printf("Error: unable to open file %s. Please make sure the file exists.", fileName);
224 perror(NULL);
225 }
226 return file;
227}
228
229// Creates a table defined in a file
230// Table name equals file name wthout extension
231void CreateTableFromFile(char* fileName)
232{
233 FILE* file = OpenFile(fileName);
234 if (file == NULL)
235 {
236 return EXIT_FAILURE;
237 }
238
239 ListOfColumns* columnsList = ReadFile(file);
240
241 PGconn* conn = PQconnectdb("host=localhost port=5432 dbname=kjercha user=kjercha password=");
242 if (PQstatus(conn) == CONNECTION_OK)
243 {
244 puts("Connection made");
245
246 char* tableName = ChopFileExtension(fileName);
247
248 char* dropTableQuery = GetSqlQueryDropTable(tableName);
249 DoSQL(conn, dropTableQuery);
250
251 char* createTableQuery = GetSqlQueryCreateTable(tableName, columnsList->List[0]);
252 DoSQL(conn, createTableQuery);
253
254 int i;
255 for (i = 1; i < columnsList->Count; i++)
256 {
257 Columns* columns = columnsList->List[i];
258 char* insertQuery = GetSqlQueryInsert(tableName, columns);
259 DoSQL(conn, insertQuery);
260 }
261 }
262 else
263 printf("connection failed: %s\n", PQerrorMessage(conn));
264
265 PQfinish(conn);
266}
267
268// Returns:
269// 1 = TRUE if file name has specified extension,
270// 0 = FALSE otherwise
271int FileNameHasExtension(char* fileName, char* extension)
272{
273 char* extensionSeparator = strrchr(fileName, '.');
274 if (extensionSeparator == NULL)
275 return FALSE;
276
277 char* fileExtension = ++extensionSeparator;
278 if (strcmp(fileExtension, extension) == 0)
279 return TRUE;
280
281 return FALSE;
282}
283
284// Returns:
285// 1 = TRUE if there is only one argument in the form "fileName.csv"
286// 0 = FALSE any other case
287int ProgramArgumentsAreCorrect(int argc, char* fileName)
288{
289 if (argc != 2)
290 {
291 puts("Error: Incorrect number of program arguments. Only one argument is accepted. Example usage: ./program fileName.csv");
292 return FALSE;
293 }
294
295 if (!FileNameHasExtension(fileName, FILE_EXTENSION_CSV))
296 {
297 printf("Error: File name extension is not %s", FILE_EXTENSION_CSV));
298 return FALSE;
299 }
300
301 return TRUE:
302}
303
304int main(int argc, char *argv[])
305{
306 char* fileName = argv[1];
307
308 if (!ProgramArgumentsAreCorrect(argc, fileName))
309 {
310 return EXIT_FAILURE;
311 }
312
313 CreateTableFromFile(fileName);
314
315 return EXIT_SUCCESS;
316}