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