Extract String and StringBuffer

[mikew01]

Hi all

I need to extract all of the SQL select statements from a codebase ideally during build time.
The SQL queries are embedded in the source code in either String or StringBuffer form, none of them are annotated with a unique annotation.

I've been looking into AspectJ and maybe applying an aspect to the class Connection for instance which could output the sql to whereever at some point during execution, this does mean however that I'd need some way of executing each and every method which executes a select statement in a unit test for instance.

I've also briefly looked into AST as a possibility.

If the queries were annotated I could have used an annotation processor but alas they aren't.

Has anyone done this kind of thing before or can reccommend a suitable method?

Thanks

[Leif Roar Moldskred]

mikew01 <mik...@blueyonder.co.uk> wrote:

> I need to extract all of the SQL select statements from a codebase
> ideally during build time.

That sounds like an odd requirement. Why do you need to do so?

> The SQL queries are embedded in the source code in either String or
> StringBuffer form, none of them are annotated with a unique
> annotation.

I think you are going to struggle. The SQL statements in StringBuffers
are not accessible at build time at all, and some of the ones in
Strings might not be either.

If you truly need this, you should probably bite the bullet and do the
work of digging through the code and annotate all the statements by
hand.

--
Leif Roar Moldskred

[mikew01]

The requirement is to pull out the sql statements and profile them using the Oracle explain plan which will be executed by Sonar.

[Robert Klemme]

On Thursday, May 24, 2012 12:21:46 PM UTC+2, mikew01 wrote:
> The requirement is to pull out the sql statements and profile them using the Oracle explain plan which will be executed by Sonar.

Plans can vary dramatically depending on data. How do you want to deal with that, especially if SQL statements are created using StringBuilder? Where do you get the data from which you use in your test case?

Also creation of a single statement can be spread across different methods. That'll be difficult to resolve.


It's probably better to benchmark the application while enabling one of the Oracle traces during execution. You'll then get the real data and execution plans. For production usage the point in time when statistics are updated is also an important factor. That might be difficult to properly test in the lab if you only have those SQL statements (assumed you can recover them from the source).

Kind regards

robert

[markspace]

On 5/24/2012 3:21 AM, mikew01 wrote:
> The requirement is to pull out the sql statements and profile them
> using the Oracle explain plan which will be executed by Sonar.

In my opinion, I better requirement would be to isolate the SQL so that
it was no longer part of the code, but was in a separate file where it
could be accessed. That way no one has to do this kind of busy work
again.

Another better requirement would be to produce a decent integration test
so that it could be used to as the test bed for performance/Sonar.

[markspace]

On 5/24/2012 8:20 AM, Robert Klemme wrote:

> It's probably better to benchmark the application while enabling one
> of the Oracle traces during execution. You'll then get the real data
> and execution plans.

And this is an even better idea than trying to rely on integration tests
(although a basic check of SQL goodness isn't a bad idea either, to be
done before things get to production).

Another idea would be to capture the inputs to a typical production
case, and add them to a performance test or stress test. Instrumenting
production systems is OK if you can do it, but I like automated,
reproducible tests better.


I just did a quick web search for SONAR. It's one of those cruddy
little "code goodness" tools, where you apparently don't trust your
programmers to write good code, or you have no control over software
quality, so instead of fixing those problems you install a tool to give
you little reports about how crappy your code is. Dear sweet Jebus on a
pogo stick.

<http://www.sonarsource.org/>

[Lew]

Here's an interceptor for JDBC calls. I've seen this in action and it's awesome.

<http://code.google.com/p/log4jdbc/>

--
Lew

[Martin Gregorie]

On Thu, 24 May 2012 03:21:46 -0700, mikew01 wrote:

> The requirement is to pull out the sql statements and profile them using
> the Oracle explain plan which will be executed by Sonar.
>

Has time I had to do that I was able to use ODBC tracing facilities to
capture SQL statements as they were executed, but that was a while ago
and I can't recall whether all ODBC implementations had that ability or
was a capability of the particular driver I was using.

Of course, using this approach does rather assume that you have a
regression test that is known to exercise all SQL statements and/or there
is somebody who knows enough about the system to be able to reliably run
them all.

If this is part of a DB tuning exercise you should be aware that you'll
need to run the complete set of statements more than once, so it would be
worthwhile to build a set of scripts that can can be used to (a) re-
capture the set of scripts and (b) run them and record performance data
for analysis.

There's one other vital bit of information you need too - a use frequency
for each SQL statement. If you don't have this information it would be
well worth instrumenting your system to capture it. DB tuning is a really
good example of a place where the 80/20 rule applies: unless a piece of
SQL isn't in the top 20% of most frequently run statements there's little
point in optimising the DB for it unless its something quite unusual,
e.g. the CEO uses it once a day/week/month or it is run once a night, is
tooth-achingly slow, accesses most of the DB and tends to run longer than
the quiet period its meant to complete its task in.


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Roedy Green]

On Thu, 24 May 2012 08:21:28 -0700, markspace <-@.> wrote, quoted or
indirectly quoted someone who said :

>
>In my opinion, I better requirement would be to isolate the SQL so that
>it was no longer part of the code, but was in a separate file where it
>could be accessed. That way no one has to do this kind of busy work
>again.

It would be something like the way you do internationalisation with
resource bundles.

It could for example let you specialize code for different SQL
engines.
--
Roedy Green Canadian Mind Products
http://mindprod.com
Controlling complexity is the essence of computer programming.
~ Brian W. Kernighan 1942-01-01
.

[Roedy Green]

On Thu, 24 May 2012 01:29:25 -0700 (PDT), mikew01
<mik...@blueyonder.co.uk> wrote, quoted or indirectly quoted someone
who said :

>I need to extract all of the SQL select statements from a codebase

see http://mindprod.com/jgloss/parser.html

You might concoct a parser to find them or to add annotations that you
manually proofread.

[markspace]

On 5/28/2012 9:28 AM, Roedy Green wrote:
> On Thu, 24 May 2012 08:21:28 -0700, markspace<-@.> wrote, quoted or
> indirectly quoted someone who said :
>
>>
>> In my opinion, I better requirement would be to isolate the SQL so that
>> it was no longer part of the code, but was in a separate file where it
>> could be accessed. That way no one has to do this kind of busy work
>> again.
>
> It would be something like the way you do internationalisation with
> resource bundles.
>
> It could for example let you specialize code for different SQL
> engines.

Yes, that's exactly what I implemented. JavaDB (i.e. Derby) gets used
for unit testing, and something else for integration and production, so
I had a little properties file with the SQL in it. Just swap out the
properties files to switch SQL flavors.