· 9 years ago · Nov 24, 2016, 09:06 AM
1package databaseManager;
2
3import java.util.ArrayList;
4import java.util.regex.Matcher;
5import java.util.regex.Pattern;
6
7/**
8 * This class parsers and conquers the SQL statements entered. To check if the
9 * entered statement is true, and send the creation order to the creation class
10 * or the operations to the operations class
11 *
12 * @author FNSY
13 *
14 */
15public class StatementsParser {
16 // private String statement;
17 private static StatementsParser statementsParser = null;
18 private String[] reservedWords;
19
20 private StatementsParser() {
21 initializeReservedWords();
22 }
23
24 public static synchronized StatementsParser createObject() {
25 if (statementsParser == null) {
26 statementsParser = new StatementsParser();
27 }
28 return statementsParser;
29 }
30
31 public final void enterStatement(final String statement) throws Exception {
32 String checkedStatement;
33 checkedStatement = removeWhiteSpaces(statement);
34
35 String upperCaseStatement = checkedStatement.toUpperCase();
36 try {
37 System.out.println(checkedStatement);
38 selectRightAction(upperCaseStatement.split(" "), checkedStatement);
39 } catch (Exception x) {
40 System.out.println("PLEASE ENTER A VALID STATEMENT");
41
42 }
43
44 }
45
46 private String removeWhiteSpaces(final String statement) {
47 String checkedStatement;
48 checkedStatement = statement.replaceAll("=", " = ");
49 checkedStatement = checkedStatement.replaceAll(">", " > ");
50 checkedStatement = checkedStatement.replaceAll("<", " < ");
51 checkedStatement = checkedStatement.replaceAll(" +", " ");
52 checkedStatement = checkedStatement.replaceAll(" ,", ", ");
53 checkedStatement = checkedStatement.replaceAll(",", ", ");
54 checkedStatement = checkedStatement.replaceAll(" ;", "; ");
55 checkedStatement = checkedStatement.replace("(", " (");
56 checkedStatement = checkedStatement.replaceAll("\\( ", "\\(");
57 checkedStatement = checkedStatement.replaceAll(" \\)", "\\)");
58 checkedStatement = checkedStatement.replaceAll("\\*", " \\* ");
59 checkedStatement = checkedStatement.replaceAll("\n", " ");
60 checkedStatement = checkedStatement.replaceAll(" +", " ");
61 checkedStatement = checkedStatement.trim();
62 return checkedStatement;
63 }
64
65 private void selectRightAction(String[] upperCaseStatement, String checkedStatement) {
66 if (isValidStatement(checkedStatement))
67 if (upperCaseStatement[0].equals("USE"))
68 useStatement(checkedStatement.split(" "));
69 else if (upperCaseStatement[0].equals("CREATE"))
70 creationStatement(checkedStatement);
71 else if (upperCaseStatement[0].equals("DELETE"))
72 deletionStatement(checkedStatement);
73 else if (upperCaseStatement[0].equals("UPDATE"))
74 updateStatement(checkedStatement);
75 else if (upperCaseStatement[0].equals("DROP"))
76 dropStatement(checkedStatement);
77 else if (upperCaseStatement[0].equals("INSERT") && upperCaseStatement[1].equals("INTO"))
78 insertionStatement(checkedStatement);
79 else if (upperCaseStatement[0].equals("SELECT"))
80 selectStatement(checkedStatement);
81 else
82 System.out.println("PLEASE ENTER A VALID STATEMENT");
83 }
84
85 private void useStatement(String[] splitted) {
86 splitted = filterSplittedArray(splitted);
87 if (splitted.length == 2) {
88 if (splitted[0].compareToIgnoreCase("USE") == 0) {
89 System.out.println(splitted[1]);
90 // send database name splitted[1];
91 }
92 }
93
94 }
95
96 /**
97 * This method checks if the SQL statement is written in right format. with
98 * no extra spaces and trailed by a semicolon
99 *
100 * @param statement
101 * @return
102 */
103 private final boolean isValidStatement(final String statement) {
104 int semiColonIndex = statement.length() - 1;
105 if (!(statement.charAt(semiColonIndex) == ';') && !statement.toUpperCase().contains("DROP")) {
106 return false;
107 }
108 return true;
109 }
110
111 private final boolean isValidName(String name) {
112 name = name.toUpperCase();
113 for (int i = 0; i < name.length(); i++) {
114 int singleChar = name.charAt(i);
115 if ((singleChar < 65 && singleChar != 36) || (singleChar > 90 && singleChar != 95)) {
116 if (singleChar < 48 || singleChar > 57)
117 return false;
118 }
119 }
120 return true;
121 }
122
123 private final boolean isValidNameInArrayList(ArrayList<String> list) {
124 for (int i = 0; i < list.size(); i++) {
125 if (!isValidName(list.get(i)))
126 return false;
127 }
128 return true;
129 }
130
131 private final void creationStatement(final String statement) {
132 String createPart = statement.substring(0, 6);
133 if (createPart.compareToIgnoreCase("CREATE") == 0) {
134 String restOfTheString = statement.substring(7, statement.length() - 1);
135 creationOfDatabase(restOfTheString);
136 creationOfTable(restOfTheString);
137 }
138
139 }
140
141 private final void creationOfTable(String statement) {
142 statement = replaceVarchar(statement);
143 statement = removeWhiteSpaces(statement);
144 System.out.println(statement);
145 String tableString = filterString(statement.substring(0, 5));
146 if (tableString.compareToIgnoreCase("TABLE") == 0) {
147 String[] tableDetails = statement.substring(6, statement.length()).split(" ");
148 tableDetails = filterSplittedArray(tableDetails);
149 int length = tableDetails.length;
150 if (isValidName(tableDetails[0]) && !isReservedWord(tableDetails[0])) {
151 ArrayList<String> columnName = new ArrayList<>();
152 ArrayList<String> dataType = new ArrayList<>();
153 for (int i = 1; i < length; i++) {
154 if (i % 2 == 1) {
155 columnName.add(tableDetails[i]);
156 } else {
157 dataType.add(tableDetails[i]);
158 }
159 }
160 // raafat's method to create table
161 // par: String:tableDetails[0] ArrayList:columnName
162 // ArrayList:dataType
163 System.out.println(tableDetails[0]);// tableName
164 for (int i = 0; i < columnName.size(); i++) {
165 System.out.println(columnName.get(i) + " " + dataType.get(i));
166 }
167
168 } else
169 throw new RuntimeException();
170
171 }
172 }
173
174 private final void creationOfDatabase(final String statement) {
175 String databaseString = statement.substring(0, 8);
176 if (databaseString.compareToIgnoreCase("DATABASE") == 0) {
177 String databaseName = filterString(statement.substring(9, statement.length()));
178 if (isValidName(databaseName) && !isReservedWord(databaseName)) {
179 // raafat's method to create table
180 // par: databaseName
181 System.out.println(databaseName);
182 }
183
184 }
185 }
186 // end of creational
187
188 private void deletionStatement(String statement) {
189 String deleteString = statement.substring(0, 11);
190 if (deleteString.compareToIgnoreCase("DELETE FROM") == 0 && statement.toUpperCase().contains("WHERE")) {
191 String restOfString = statement.substring(12, statement.length() - 1);
192 String tableName, columnName, valueIndicatingRow;
193 String[] splitted = restOfString.split(" ");
194 tableName = splitted[0];
195 for (int i = 0; i < splitted.length; i++) {
196 if (splitted[i].compareToIgnoreCase("WHERE") == 0 && splitted[i + 2].equals("=")) {
197 columnName = splitted[i + 1];
198 valueIndicatingRow = splitted[i + 3];
199 if (isValidName(tableName) && isValidName(columnName) && isValidName(valueIndicatingRow))
200 System.out.println(tableName + " \n" + columnName + "\n " + valueIndicatingRow);
201 else
202 throw new RuntimeException();
203 break;
204 }
205 }
206 } else
207 deleteAllTable(statement);
208 }
209
210 private void deleteAllTable(String statement) {
211 String deleteAllString = statement.substring(0, 11);
212 String deleteAllAstString = statement.substring(0, 13);
213 String tableName;
214 if (deleteAllString.compareToIgnoreCase("DELETE FROM") == 0) {
215 tableName = statement.substring(12, statement.length() - 1);
216 if (isValidName(tableName))
217 System.out.println(tableName);
218 else
219 throw new RuntimeException();
220 // send to raafat's method // par String:tableName
221 } else if (deleteAllAstString.compareToIgnoreCase("DELETE * FROM") == 0) {
222 tableName = statement.substring(14, statement.length() - 1);
223 if (isValidName(tableName))
224 System.out.println(tableName);
225 else
226 throw new RuntimeException();
227 // send to raafat's method // par String:tableName
228 }
229
230 }
231 // end of delete
232
233 private void updateStatement(String statement) {
234 String updateString = statement.substring(0, 6);
235 String[] splitted = statement.substring(7, statement.length()).split(" ");
236 splitted = filterSplittedArray(splitted);
237 if (updateString.compareToIgnoreCase("UPDATE") == 0 && statement.toUpperCase().contains("SET")
238 && statement.toUpperCase().contains("WHERE")) {
239 containingWhereUpdateStatement(splitted);
240 } else if (updateString.compareToIgnoreCase("UPDATE") == 0 && statement.toUpperCase().contains("SET")
241 && !statement.toUpperCase().contains("WHERE")) {
242 notContainingWhereUpdateStatement(splitted);
243 }
244
245 }
246
247 private void containingWhereUpdateStatement(String[] splitted) {// need to
248 // be less
249 // than 20
250 // line
251 String tableName = splitted[0];
252 if (splitted[1].compareToIgnoreCase("SET") == 0) {
253 ArrayList<String> columnName = new ArrayList<>();
254 ArrayList<String> values = new ArrayList<>();
255 String guideColumn = null, guideValue = null;
256 for (int i = 2; i < splitted.length; i++) {
257 if (splitted[i].compareToIgnoreCase("WHERE") != 0) {
258 if (i % 3 == 0)
259 if (splitted[i].equals("=")) {
260 columnName.add(splitted[i - 1]);
261 if (splitted[i + 1].compareToIgnoreCase("WHERE") != 0)
262 values.add(splitted[i + 1]);
263 } else
264 throw new RuntimeException();
265
266 } else if (splitted[i + 2].equals("=")) {
267 guideColumn = splitted[i + 1];
268 guideValue = splitted[i + 3];
269 break;
270 } else {
271 throw new RuntimeException();
272 }
273 }
274 // send to raafat String:tableName String:guideColumn
275 // String:guideValue ArrayList:columnName ArrayList:values
276 if (validateEqualSize(columnName, values) && isValidName(tableName) && isValidName(guideColumn)
277 && isValidName(guideValue) && isValidNameInArrayList(columnName)
278 && isValidNameInArrayList(values)) {
279 System.out.println(tableName + " \n" + guideColumn + " \n" + guideValue);
280 for (int i = 0; i < values.size(); i++)
281 System.out.println(columnName.get(i) + "\n" + values.get(i));
282 } else
283 throw new RuntimeException();
284
285 } else
286 throw new RuntimeException();
287
288 }
289
290 private void notContainingWhereUpdateStatement(String[] splitted) {
291 String tableName = splitted[0];
292 if (splitted[1].compareToIgnoreCase("SET") == 0) {
293 ArrayList<String> columnName = new ArrayList<>();
294 ArrayList<String> values = new ArrayList<>();
295 for (int i = 2; i < splitted.length; i++) {
296 if (splitted[i].equals("=")) {
297 columnName.add(splitted[i - 1]);
298 values.add(splitted[i + 1]);
299 } else
300 throw new RuntimeException();
301 }
302 // send to raafat String:tableName String:guideColumn
303 // String:guideValue ArrayList:columnName ArrayList:values
304 System.out.println(tableName);
305 if (validateEqualSize(columnName, values) && isValidName(tableName) && isValidNameInArrayList(columnName)
306 && isValidNameInArrayList(values))
307 for (int i = 0; i < values.size(); i++)
308 System.out.println(columnName.get(i) + "\n" + values.get(i));
309 else
310 throw new RuntimeException();
311 }
312
313 }
314 // end of update
315
316 private void dropStatement(final String statement) {
317 String dropString = statement.substring(0, 4);
318 if (dropString.compareToIgnoreCase("DROP") == 0) {
319 String[] splitted = statement.substring(5, statement.length()).split(" ");
320 splitted = filterSplittedArray(splitted);
321 dropOfTable(splitted);
322 dropOfDatabase(splitted);
323 }
324
325 }
326
327 private void dropOfDatabase(String[] splitted) {
328 if (splitted.length == 2) {
329 if (splitted[0].compareToIgnoreCase("DATABASE") == 0) {
330 System.out.println(splitted[1]);
331 // send splitted[1] to raafat
332 }
333 }
334 }
335
336 private void dropOfTable(String[] splitted) {
337 if (splitted.length == 2) {
338 if (splitted[0].compareToIgnoreCase("TABLE") == 0) {
339 System.out.println(splitted[1]);
340 // send splitted[1] to raafat
341 }
342 }
343 }
344 // end of drop
345
346 private void insertionStatement(String statement) {
347 String insertIntoString = statement.substring(0, 11);
348 if (insertIntoString.compareToIgnoreCase("INSERT INTO") == 0 && statement.toUpperCase().contains("VALUES")) {
349 String[] splitted = statement.substring(12, statement.length()).split(" ");
350 splitted = filterSplittedArray(splitted);
351 withColumnsNamesInsertionStatement(splitted);
352 withoutColumnsNamesInsertionStatement(splitted);
353
354 }
355
356 }
357
358 private void withColumnsNamesInsertionStatement(String[] splitted) {
359 // splitted[0] is the tableName
360 ArrayList<String> columnName = new ArrayList<>();
361 ArrayList<String> valuesToBeInserted = new ArrayList<>();
362 boolean valuesTurn = false;
363 for (int i = 1; i < splitted.length; i++) {
364 if (splitted[i].toUpperCase().equals("VALUES")) {
365 i += 1;
366 valuesTurn = true;
367 }
368 if (!valuesTurn)
369 columnName.add(splitted[i]);
370 else
371 valuesToBeInserted.add(splitted[i]);
372 }
373 if (validateEqualSize(columnName, valuesToBeInserted) && isValidNameInArrayList(columnName)
374 && isValidNameInArrayList(valuesToBeInserted) && isValidName(splitted[0]))
375 for (int i = 0; i < columnName.size(); i++)
376 System.out.println(columnName.get(i) + " " + valuesToBeInserted.get(i));
377 else
378 throw new RuntimeException();
379 }
380
381 private void withoutColumnsNamesInsertionStatement(String[] splitted) {
382 // splitted[0] is the tableName // splitted[1] is the word VALUES
383 ArrayList<String> valuesToBeInserted = new ArrayList<>();
384 for (int i = 2; i < splitted.length; i++) {
385 valuesToBeInserted.add(splitted[i]);
386 }
387 if (isValidNameInArrayList(valuesToBeInserted) && isValidName(splitted[0]))
388 for (int i = 0; i < valuesToBeInserted.size(); i++)
389 System.out.println(" " + valuesToBeInserted.get(i));
390 System.out.println(splitted[0]);
391 // send tableName and arrayList
392
393 }
394
395 // end insert
396
397 private void selectStatement(String statement) {
398 if (statement.toUpperCase().contains("*")) {
399 selectAllStatement(statement);
400 } else {
401 String[] splitted = statement.substring(7, statement.length()).split(" ");
402 splitted = filterSplittedArray(splitted);
403 dedicatedSelectStatement(splitted);
404 }
405
406 }
407
408 private void selectAllStatement(String statement) {
409 String selectAllFromString = statement.substring(0, 13);
410 if (selectAllFromString.compareTo("SELECT * FROM") == 0) {
411 String restOfString = statement.substring(14, statement.length()).replace(";", "");
412 if (statement.toUpperCase().contains("WHERE")) {
413 String[] splitted = filterSplittedArray(restOfString.split(" "));
414 String tableName = splitted[0];
415 System.out.println(tableName);
416 containsWhereSelectStatement(splitted);
417 } else if (!statement.toUpperCase().contains("WHERE")) {
418 // send tableName String: restOfString
419 if (isValidName(restOfString))
420 System.out.println(restOfString);
421 else
422 throw new RuntimeException();
423 }
424 }
425 }
426
427 private void dedicatedSelectStatement(String[] splitted) {
428 ArrayList<String> columnName = new ArrayList<>();
429 int i = 0;
430 int holdIndex;
431 while (i < splitted.length && !splitted[i].toUpperCase().equals("FROM")) {
432 columnName.add(splitted[i]);
433 i++;
434 }
435 String tableName = splitted[++i];
436 holdIndex = i;
437 try {
438 if (splitted[++i].compareToIgnoreCase("WHERE") == 0) {
439 ArrayList<String> afterFromString = new ArrayList<>();
440 for (int j = holdIndex; j < splitted.length; j++) {
441 afterFromString.add(splitted[j]);
442 }
443 String[] sendToWhere = new String[afterFromString.size()];
444 for (int j = 0; j < afterFromString.size(); j++)
445 sendToWhere[j] = afterFromString.get(j);
446 System.out.println(columnName);
447 System.out.println(tableName);
448 containsWhereSelectStatement(sendToWhere);
449 } else
450 throw new RuntimeException();
451 } catch (ArrayIndexOutOfBoundsException x) {
452 if (isValidNameInArrayList(columnName) && isValidName(tableName)) {
453 System.out.println(columnName);
454 System.out.println(tableName);
455 } else {
456 throw new RuntimeException();
457 }
458
459 // send ArrayList:columnName String:tableName
460 }
461
462 }
463
464 private void containsWhereSelectStatement(String[] splitted) {
465 if (splitted.length == 5) {
466 // splitted array starts after the word FROM
467 // splitted[0] table name
468 String columnName = splitted[2];
469 String conditionSymbol = splitted[3];
470 String value = splitted[4];
471 if (isValidName(columnName) && isValidName(value)
472 && (conditionSymbol.equals("=") || conditionSymbol.equals("<") || conditionSymbol.equals(">")))
473 System.out.println(columnName + " \n " + conditionSymbol + " \n" + value);
474 else
475 throw new RuntimeException();
476 // send all these strings to raafat
477 } else
478 throw new RuntimeException();
479 }
480
481 private String[] filterSplittedArray(String[] splitted) {
482 String[] filtered = new String[splitted.length];
483 for (int i = 0; i < splitted.length; i++)
484 filtered[i] = splitted[i].replace("(", "").replace(")", "").replace("'", "").replace(";", "")
485 .replace(",", "").replace("\"", "").replace("`", "");
486 return filtered;
487 }
488
489 private boolean validateEqualSize(ArrayList one, ArrayList two) {
490 if (one.size() != two.size())
491 return false;
492 return true;
493 }
494
495 private String filterString(String string) {
496 string = string.replace("(", "").replace(")", "").replace("'", "").replace(";", "").replace(",", "")
497 .replace("\"", "").replace("`", "");
498 return string;
499 }
500
501 private void initializeReservedWords() {
502 this.reservedWords = new String[] { "ALL", "ALTER", "AND", "ANY", "ARRAY", "ARROW", "AS", "ASC", "AT", "BEGIN",
503 "BETWEEN", "BY", "CASE", "CHECK", "CLUSTERS", "CLUSTER", "COLAUTH", "COLUMNS", "COMPRESS", "CONNECT",
504 "CRASH", "CREATE", "CURRENT", "DECIMAL", "DECLARE", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP",
505 "ELSE", "END", "EXCEPTION", "EXCLUSIVE", "EXISTS", "FETCH", "FORM", "FOR", "FROM", "GOTO", "GRANT",
506 "GROUP", "HAVING", "IDENTIFIED", "IF", "IN", "INDEXES", "INDEX", "INSERT", "INTERSECT", "INTO", "IS",
507 "LIKE", "LOCK", "MINUS", "MODE", "NOCOMPRESS", "NOT", "NOWAIT", "NULL", "OF", "ON", "OPTION", "OR",
508 "ORDER", "OVERLAPS", "PRIOR", "PROCEDURE", "RANGE", "RECORD", "RESOURCE", "REVOKE", "SELECT", "SHARE",
509 "SIZE", "SQL", "SUBTYPE", "TABAUTH", "TABLE", "THEN", "TO", "TYPE", "UNION", "UNIQUE", "UPDATE", "USE",
510 "VALUES", "VIEW", "VIEWS", "WHEN", "WHERE", "WITH" };
511
512 }
513
514 private boolean isReservedWord(String name) {
515 name = name.toUpperCase();
516 for (int i = 0; i < this.reservedWords.length; i++)
517 if (name.equals(this.reservedWords[i]))
518 return true;
519 return false;
520 }
521
522 private String replaceVarchar(String statement) {
523 Pattern pattern = Pattern.compile("\\(\\d+\\)");
524 Matcher matcher = pattern.matcher(statement);
525 while (matcher.find()) {
526 statement = statement.replace(matcher.group(), "").replace("varchar", "String");
527 }
528 return statement;
529 }
530
531}