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