Project: Build an SQLSelectvParser

Your job is to develop, and test thoroughly, an SQLSelectParser by implementing the classes provided to you. SQLSelectParser must be able to parse complex SQL statements, including complex JOINs, and extract relevant data from it. Each method of SQLSelectParser has a clear description of the required input and output. You can use regular expressions or any other String-based parsing approach, as long as the parsing is accurate and [login to view URL] first priority is accuracy, then speed. Start by reading the PROJECT INSTRUCTIONS below and the javadoc instructions on the [login to view URL] file. ## Deliverables PROJECT: BUILD AN SQLPARSER! Dear Developer, Your job is to develop, and test thoroughly, an SQLSelectParser by implementing the classes provided to you. Start by reading the PROJECT INSTRUCTIONS below and the javadoc instructions on the [login to view URL] file. Before you develop your parser, you will need to build extensive unit tests to test it. For that, you'll need to build an example database based on the SELECT statements of different complexities found on the Join_(SQL) wikipedia page (the page is attached here). Please use your dummy database and run your select statements before you run them through the parser. We are only interested in parsing SQL queries that work! Once you submit your project version 1, we will test the parser on our own SQL statements and return to you with any bugs and issues. The less bugs we find, the more of the bonus you will get! So make sure to thoroughly test your code on as many complex select statements as you can! You can use any standard SQL database to test your sql code, such as MySQL. If any questions arise, please don't hesitate to ask us! We hope you'll enjoy working on this challenging yet rewarding project! Best Regards, The Elintegro Team PROJECT INSTRUCTIONS Phase 1 - Build a Test Database and Junit Tests (Deliverable: SQL dump, SQL files and JUnit tests. You will first have to build tables with data on which the SQL queries run. Use the SELECT statements found on the wikipedia page [login to view URL](SQL)#Sample_tables to build the database and run the statements. Once you've built your SELECT statements, database, and dummy data, place each SQL statement in its own .sql file and place all the files in the package [login to view URL] (UPDATE: this is already done - the [login to view URL] package contains 16 sql files with queries from the Wikipedia page). Then, examine the JUnit test SubqueryParserTest to see how JUnit tests are constructed. Complete constructing this JUnit test, making sure to carefully test the expected output every method in SQLSelectParser (you'll have to already have studied the SQLSelectParser interface to know how to test the desired output of each method). Then construct a separete test file for every SQL statement (duplicate SQLSelectParser for each of the SQL queries). Whenever relevant, use JDBC to help you test: to test the output of, for example, [login to view URL], make a JDBC call to the select query, and compare the names of the columns in the [login to view URL] with the columns you parsed. You can also use the [login to view URL] object (in some cases, such as with table aliases, you won't be able to do this, so you'll have to manually enter the names of the table aliases). Once you're done building the JUnit tests, submit to us the tests and a MySQL dump of the dummy database you've created, and move on to Phase 2. Phase 2 - Build the SQLSelectParser SQLSelectParser must be able to parse complex SQL statements, including complex JOINs, and extract relevant data from it. Each method of SQLSelectParser has a clear description of the required input and output. You can use regular expressions or any other String-based parsing approach, as long as the parsing is accurate and [login to view URL] first priority is accuracy, then speed. The first and obstacle is that an SQL statement can have an unlimited number of subqueries, and each of these subqueries can have their own subqueries. Therefore before parsing the SQL Select Statement into its individual components, we need to extract all the first level subqueries from it. First level subqueries are all the subqueries in an sql query, which may include other subqueries as well. Your algorithm has to be smart enough to make sure that you take out all the subqueries and leave the sub-subqueries intact within the subqueries. For example, consider : SELECT something, (select somethingElse from (select a from b) ) a, FROM SomeTable where (SELECT b from c) = 3 Then the two first-level subqueries in this select are: 1. (select somethingElse from (select a from b)) 2. (SELECT b from c) To get started, extract the ZIP file [login to view URL] and import it into eclipse (File -> Import -> Existing Projects into Workspace -> Select archive file). You may use another IDE if you wish, but please deliver the files back also as an eclipse project. DELIVERABLES Fully unit-tested implementation of SQLSelectParer and any supporting code. Your project needs to be ZIPPED as an eclipse project. Include JUnit tests in a separate package. The best way to test your code is through querying the original select with JDBC and then comparing column names, table names, etc. from the ResultSetMetaData and DatabaseMetaData objects COMPENSATION Upon delivery, we will thoroughly test your code. For each error or bug we find, we will come back to you with a request for fixing it. A bonus of up to 50% of the project price will be included upon completing of the project. The amount will be based on our judgement, according to the quality of your solution, the robustness of the algorithms and the number of bugs and iterations. For late delivery, you lose 5% of your bonus for each late day. If you are late past your bonus, or the code is low-quality and requires more than 3-4 fixing iterations, the project will be entered into arbitration and you might lose some or all of your original quoted price. IN YOUR APPLICATION The perfect candidate for this project will not only provide credentials for why you are fit for the job, but also give us an overview for how you are planning to approach the problem. How will you be extracting subqueries? How will you go about parsing out JOINs? Provide an analysis for what you think are the challenges in this project and how you think you will solve them. Also, you can comment on the data structures we provided and suggest ways we can imrprove the data structures we built to store the SQL Select statement details to be most inclusive of as many cases as possible.