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