Have you ever got upset and felt to cry out loudly (or you just did so) when you had an SQL statement failing somewhere deeply embedded in your code? Was it easy to find and fix it? Still today, many database libraries force us to embed SQL in the code, which is then very cumbersome to maintain. Fragments of query strings are scattered throughout the source code and we never really see the final SQL statements assembled and sent to the database. What is even worse, the SQL code is not processed at compilation time and – in the worst case – a syntax error can remain in production code.
Relying on Eclipse technologies, we implemented a static analyzer (SQLInspect) to inspect database usage in Java applications. It performs a string analysis using the parser of Eclipse JDT to extract SQL code from Java, and it is seamlessly integrated into Eclipse as a plugin. One can use it to identify database-related quality issues, e.g., to spot SQL Antipatterns, or queries with poor metrics which could be optimized (e.g., deeply nested subqueries). We also used it in our research project where we analyzed the database usage of thousands of top-starred Android apps on GitHub. In the end, we sent the developers some nice pull-requests fixing a few issues (e.g., missing indexes/triggers due to unexecuted SQL statements).
In our talk, we will present how we’ve put together such a plugin using Eclipse technologies, which was an adventurous journey for us, and as we know, “it is the journey that matters in the end.”