X6McWUg4

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 void creationStatement(final String statement) {
121		String createPart = statement.substring(0, 6);
122		if (createPart.compareToIgnoreCase("CREATE") == 0) {
123			String restOfTheString = statement.substring(7, statement.length() - 1);
124			creationOfDatabase(restOfTheString);
125			creationOfTable(restOfTheString);
126		}
127
128	}
129
130	private final void creationOfTable(final String statement) {
131		String tableString = filterString(statement.substring(0, 5));
132		if (tableString.compareToIgnoreCase("TABLE") == 0) {
133			String[] tableDetails = statement.substring(6, statement.length()).split(" ");
134			tableDetails = filterSplittedArray(tableDetails);
135			int length = tableDetails.length;
136			if (isValidName(tableDetails[0]) && !isReservedWord(tableDetails[0])) {
137				ArrayList<String> columnName = new ArrayList<>();
138				ArrayList<String> dataType = new ArrayList<>();
139				for (int i = 1; i < length; i++) {
140					if (i % 2 == 1) {
141						columnName.add(tableDetails[i]);
142					} else {
143						dataType.add(tableDetails[i]);
144					}
145				}
146				// raafat's method to create table
147				// par: String:tableDetails[0] ArrayList:columnName
148				// ArrayList:dataType
149				System.out.println(tableDetails[0]);// tableName
150				for (int i = 0; i < columnName.size(); i++) {
151					System.out.println(columnName.get(i) + "   " + dataType.get(i));
152				}
153
154			}
155
156		}
157	}
158
159	private final void creationOfDatabase(final String statement) {
160		String databaseString = statement.substring(0, 8);
161		if (databaseString.compareToIgnoreCase("DATABASE") == 0) {
162			String databaseName = filterString(statement.substring(9, statement.length()));
163			if (isValidName(databaseName) && !isReservedWord(databaseName)) {
164				// raafat's method to create table
165				// par: databaseName
166				System.out.println(databaseName);
167			}
168
169		}
170	}
171	// end of creational
172
173	private void deletionStatement(String statement) {
174		String deleteString = statement.substring(0, 11);
175		if (deleteString.compareToIgnoreCase("DELETE FROM") == 0 && statement.toUpperCase().contains("WHERE")) {
176			String restOfString = statement.substring(12, statement.length() - 1);
177			String tableName, columnName, valueIndicatingRow;
178			String[] splitted = restOfString.split(" ");
179			tableName = splitted[0];
180			for (int i = 0; i < splitted.length; i++) {
181				if (splitted[i].compareToIgnoreCase("WHERE") == 0 && splitted[i + 2].equals("=")) {
182					columnName = splitted[i + 1];
183					valueIndicatingRow = splitted[i + 3];
184					// send String: tableName String: columnName String:
185					// valueIndicatingRow
186					System.out.println(tableName + " \n" + columnName + "\n " + valueIndicatingRow);
187					break;
188				}
189			}
190		} else
191			deleteAllTable(statement);
192	}
193
194	private void deleteAllTable(String statement) {
195		String deleteAllString = statement.substring(0, 11);
196		String deleteAllAstString = statement.substring(0, 13);
197		String tableName;
198		if (deleteAllString.compareToIgnoreCase("DELETE FROM") == 0) {
199			tableName = statement.substring(12, statement.length() - 1);
200			System.out.println(tableName);
201			// send to raafat's method // par String:tableName
202		} else if (deleteAllAstString.compareToIgnoreCase("DELETE * FROM") == 0) {
203			tableName = statement.substring(14, statement.length() - 1);
204			System.out.println(tableName);
205			// send to raafat's method // par String:tableName
206		}
207
208	}
209	// end of delete
210
211	private void updateStatement(String statement) {
212		String updateString = statement.substring(0, 6);
213		String[] splitted = statement.substring(7, statement.length()).split(" ");
214		splitted = filterSplittedArray(splitted);
215		if (updateString.compareToIgnoreCase("UPDATE") == 0 && statement.toUpperCase().contains("SET")
216				&& statement.toUpperCase().contains("WHERE")) {
217			containingWhereUpdateStatement(splitted);
218		} else if (updateString.compareToIgnoreCase("UPDATE") == 0 && statement.toUpperCase().contains("SET")
219				&& !statement.toUpperCase().contains("WHERE")) {
220			notContainingWhereUpdateStatement(splitted);
221		}
222
223	}
224
225	private void containingWhereUpdateStatement(String[] splitted) {
226		String tableName = splitted[0];
227		if (splitted[1].compareToIgnoreCase("SET") == 0) {
228			ArrayList<String> columnName = new ArrayList<>();
229			ArrayList<String> values = new ArrayList<>();
230			String guideColumn = null;
231			String guideValue = null;
232			for (int i = 2; i < splitted.length; i++) {
233				if (splitted[i].compareToIgnoreCase("WHERE") != 0) {
234					if (splitted[i].equals("=")) {
235						columnName.add(splitted[i - 1]);
236						if (splitted[i + 1].compareToIgnoreCase("WHERE") != 0)
237							values.add(splitted[i + 1]);
238					}
239
240				} else {
241					guideColumn = splitted[i + 1];
242					guideValue = splitted[i + 3];
243					break;
244				}
245			}
246			// send to raafat String:tableName String:guideColumn
247			// String:guideValue ArrayList:columnName ArrayList:values
248			if (validateEqualSize(columnName, values)) {
249				System.out.println(tableName + " " + guideColumn + " " + guideValue);
250				for (int i = 0; i < values.size(); i++)
251					System.out.println(columnName.get(i) + "\n" + values.get(i));
252			}
253
254		}
255
256	}
257
258	private void notContainingWhereUpdateStatement(String[] splitted) {
259		String tableName = splitted[0];
260		if (splitted[1].compareToIgnoreCase("SET") == 0) {
261			ArrayList<String> columnName = new ArrayList<>();
262			ArrayList<String> values = new ArrayList<>();
263			for (int i = 2; i < splitted.length; i++) {
264				if (splitted[i].equals("=")) {
265					columnName.add(splitted[i - 1]);
266					values.add(splitted[i + 1]);
267				}
268			}
269			// send to raafat String:tableName String:guideColumn
270			// String:guideValue ArrayList:columnName ArrayList:values
271			System.out.println(tableName);
272			if (validateEqualSize(columnName, values))
273				for (int i = 0; i < values.size(); i++)
274					System.out.println(columnName.get(i) + "\n" + values.get(i));
275		}
276
277	}
278	// end of update
279
280	private void dropStatement(final String statement) {
281		String dropString = statement.substring(0, 4);
282		if (dropString.compareToIgnoreCase("DROP") == 0) {
283			String[] splitted = statement.substring(5, statement.length()).split(" ");
284			splitted = filterSplittedArray(splitted);
285			dropOfTable(splitted);
286			dropOfDatabase(splitted);
287		}
288
289	}
290
291	private void dropOfDatabase(String[] splitted) {
292		if (splitted.length == 2) {
293			if (splitted[0].compareToIgnoreCase("DATABASE") == 0) {
294				System.out.println(splitted[1]);
295				// send splitted[1] to raafat
296			}
297		}
298	}
299
300	private void dropOfTable(String[] splitted) {
301		if (splitted.length == 2) {
302			if (splitted[0].compareToIgnoreCase("TABLE") == 0) {
303				System.out.println(splitted[1]);
304				// send splitted[1] to raafat
305			}
306		}
307	}
308	// end of drop
309
310	private void insertionStatement(String statement) {
311		String insertIntoString = statement.substring(0, 11);
312		if (insertIntoString.compareToIgnoreCase("INSERT INTO") == 0 && statement.toUpperCase().contains("VALUES")) {
313			String[] splitted = statement.substring(12, statement.length()).split(" ");
314			splitted = filterSplittedArray(splitted);
315			withColumnsNamesInsertionStatement(splitted);
316			withoutColumnsNamesInsertionStatement(splitted);
317
318		}
319
320	}
321
322	private void withColumnsNamesInsertionStatement(String[] splitted) {
323		// splitted[0] is the tableName
324		ArrayList<String> columnName = new ArrayList<>();
325		ArrayList<String> valuesToBeInserted = new ArrayList<>();
326		boolean valuesTurn = false;
327		for (int i = 1; i < splitted.length; i++) {
328			if (splitted[i].toUpperCase().equals("VALUES")) {
329				i += 1;
330				valuesTurn = true;
331			}
332			if (!valuesTurn)
333				columnName.add(splitted[i]);
334			else
335				valuesToBeInserted.add(splitted[i]);
336		}
337		if (validateEqualSize(columnName, valuesToBeInserted))
338			for (int i = 0; i < columnName.size(); i++) {
339				System.out.println(columnName.get(i) + "   " + valuesToBeInserted.get(i));
340			}
341	}
342
343	private void withoutColumnsNamesInsertionStatement(String[] splitted) {
344		// splitted[0] is the tableName // splitted[1] is the word VALUES
345		ArrayList<String> valuesToBeInserted = new ArrayList<>();
346		for (int i = 2; i < splitted.length; i++) {
347			valuesToBeInserted.add(splitted[i]);
348		}
349		for (int i = 0; i < valuesToBeInserted.size(); i++) // send and table
350															// name arrayList to
351															// raafat's method
352			System.out.println("   " + valuesToBeInserted.get(i));
353		System.out.println(splitted[0]);
354
355	}
356
357	// end insert
358
359	private void selectStatement(String statement) {
360		if (statement.toUpperCase().contains("*")) {
361			selectAllStatement(statement);
362		} else {
363			String[] splitted = statement.substring(7, statement.length()).split(" ");
364			splitted = filterSplittedArray(splitted);
365			dedicatedSelectStatement(splitted);
366		}
367
368	}
369
370	private void selectAllStatement(String statement) {
371		String selectAllFromString = statement.substring(0, 13);
372		if (selectAllFromString.compareTo("SELECT * FROM") == 0) {
373			String restOfString = statement.substring(14, statement.length()).replace(";", "");
374			if (statement.toUpperCase().contains("WHERE")) {
375				String[] splitted = filterSplittedArray(restOfString.split(" "));
376				String tableName = splitted[0];
377				System.out.println(tableName);
378				containsWhereSelectStatement(splitted);
379			} else if (!statement.toUpperCase().contains("WHERE")) {
380				// send tableName String: restOfString
381				System.out.println(restOfString);
382			}
383		}
384	}
385
386	private void dedicatedSelectStatement(String[] splitted) {
387		ArrayList<String> columnName = new ArrayList<>();
388		int i = 0;
389		int holdIndex;
390		while (i < splitted.length && !splitted[i].toUpperCase().equals("FROM")) {
391			columnName.add(splitted[i]);
392			i++;
393		}
394		String tableName = splitted[++i];
395		holdIndex = i;
396		try {
397			if (splitted[++i].compareToIgnoreCase("WHERE") == 0) {
398				ArrayList<String> afterFromString = new ArrayList<>();
399				for (int j = holdIndex; j < splitted.length; j++) {
400					afterFromString.add(splitted[j]);
401				}
402				String[] sendToWhere = new String[afterFromString.size()];
403				for (int j = 0; j < afterFromString.size(); j++)
404					sendToWhere[j] = afterFromString.get(j);
405				System.out.println(columnName);
406				System.out.println(tableName);
407				containsWhereSelectStatement(sendToWhere);
408			}
409		} catch (ArrayIndexOutOfBoundsException x) {
410			System.out.println(columnName);
411			System.out.println(tableName);
412			// send ArrayList:columnName String:tableName
413		}
414
415	}
416
417	private void containsWhereSelectStatement(String[] splitted) {
418		if (splitted.length == 5) {
419			// splitted array starts after the word FROM
420			// splitted[0] table name
421			String columnName = splitted[2];
422			String conditionSymbol = splitted[3];
423			String value = splitted[4];
424			System.out.println(columnName + " \n " + conditionSymbol + "   \n" + value);
425			// send all these strings to raafat
426		}
427	}
428
429	private String[] filterSplittedArray(String[] splitted) {
430		String[] filtered = new String[splitted.length];
431		for (int i = 0; i < splitted.length; i++)
432			filtered[i] = splitted[i].replace("(", "").replace(")", "").replace("'", "").replace(";", "")
433					.replace(",", "").replace("\"", "").replace("`", "");
434		return filtered;
435	}
436
437	private boolean validateEqualSize(ArrayList one, ArrayList two) {
438		if (one.size() != two.size())
439			return false;
440		return true;
441	}
442
443	private String filterString(String string) {
444		string = string.replace("(", "").replace(")", "").replace("'", "").replace(";", "").replace(",", "")
445				.replace("\"", "").replace("`", "");
446		return string;
447	}
448
449	private void initializeReservedWords() {
450		this.reservedWords = new String[] { "ALL", "ALTER", "AND", "ANY", "ARRAY", "ARROW", "AS", "ASC", "AT", "BEGIN",
451				"BETWEEN", "BY", "CASE", "CHECK", "CLUSTERS", "CLUSTER", "COLAUTH", "COLUMNS", "COMPRESS", "CONNECT",
452				"CRASH", "CREATE", "CURRENT", "DECIMAL", "DECLARE", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP",
453				"ELSE", "END", "EXCEPTION", "EXCLUSIVE", "EXISTS", "FETCH", "FORM", "FOR", "FROM", "GOTO", "GRANT",
454				"GROUP", "HAVING", "IDENTIFIED", "IF", "IN", "INDEXES", "INDEX", "INSERT", "INTERSECT", "INTO", "IS",
455				"LIKE", "LOCK", "MINUS", "MODE", "NOCOMPRESS", "NOT", "NOWAIT", "NULL", "OF", "ON", "OPTION", "OR",
456				"ORDER", "OVERLAPS", "PRIOR", "PROCEDURE", "RANGE", "RECORD", "RESOURCE", "REVOKE", "SELECT", "SHARE",
457				"SIZE", "SQL", "SUBTYPE", "TABAUTH", "TABLE", "THEN", "TO", "TYPE", "UNION", "UNIQUE", "UPDATE", "USE",
458				"VALUES", "VIEW", "VIEWS", "WHEN", "WHERE", "WITH" };
459
460	}
461
462	private boolean isReservedWord(String name) {
463		name = name.toUpperCase();
464		for (int i = 0; i < this.reservedWords.length; i++)
465			if (name.equals(this.reservedWords[i]))
466				return true;
467		return false;
468	}
469
470}