All, I will agree from a unit testing perspective it makes it more difficult, and it makes it harder to keep the SQL queries safe from manipulation from the outside world after delivery (will be a concern in the future).
The issue I am trying to solve is that in our operational environment we are starting to run into trouble because of some subtleties of SQL when hit with very large databases. Thus we need our DBA to have the ability to iterate on the queries in order to see that they are optimal. As our delivery process is long it would be nice to have the sql editable without incurring a month or more to get the code into operations again (where we might potentially see a new unforeseen error in the way our sql is laid out). Here is my next proposal, keep the sql in "java" but have the actual data access (sql) be in a separate class from the catalog implementation. This sql class can be chosen by a property. That way our project could implement a new sql class in our "adaptation" layer which is easier for us to deliver than core code but we would still be using the column-based organization. It may also make it easier to adapt to other (non-oracle) databases as the sql is separated from the code that depends simply on java.sql.* (and a JDBC driver) from the code that is oracle specific. Chris is this similar to what you were suggesting? Thanks for your input! -Michael On 4/5/12 1:23 PM, "Mattmann, Chris A (388J)" <[email protected]> wrote: >Hi Mike, > >On Apr 4, 2012, at 1:00 PM, Starch, Michael D (388L) wrote: > >> All, >> >> I am currently working on designing a catalog implementation that >>interacts with an oracle database for use with the filemanager >>component. The current version uses sql that is inline with the java >>code. However, a recent need for maintenance to this SQL has >>highlighted the need to pull the sql into separate files for easier >>maintenance. >> >> What is the preferred way in oodt to store such information and load it? > >There is a class called SqlScript, here: > >http://s.apache.org/4As > >You could feasibly leverage that, but I agree with Brian, in that >approach it certainly does make it >harder to unit test. Would it be easier to simply factor the SQL out into >an abstract interface of MetKeys, >static final Strings, and then to reference that in the >ColumnOrientedCatalog? > >Cheers, >Chris > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Chris Mattmann, Ph.D. >Senior Computer Scientist >NASA Jet Propulsion Laboratory Pasadena, CA 91109 USA >Office: 171-266B, Mailstop: 171-246 >Email: [email protected] >WWW: http://sunset.usc.edu/~mattmann/ >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Adjunct Assistant Professor, Computer Science Department >University of Southern California, Los Angeles, CA 90089 USA >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >
