Re: Decouple SQL queries from class in OOP design

"Mikito Harakiri" <mikharakiri_nospaum@xxxxxxxxx> writes:
> Patrick May wrote:
> > The reasons why the application should not embed SQL in this case
> > include:
> >
> > - It couples the application to the database schema. Changes
> > to the schema (due, for example, to requirements of
> > completely different applications or database administrator
> > decisions) could break the application.
>
> Wrong. The database schema outlives the application.

That statement does not contradict what I wrote. In fact, it
demonstrates yet another reason why applications should be decoupled
from database schemas: the schemas are not owned by the application.

> I just went to DMV where the clerk was still using character based
> entry screen. Now tell me, which of the following is likely to
> happen on the next step
>
> 1. DMV would throw away the database of all vehiles/drivers/ etc in
> california and write a completely new application with new
> persistence store. (Persistence is just implementation detail, you
> know).
>
> 2. They write GUI based application that will work with existing
> database. Both applications would coexist at some point.

How about 3: For a sufficiently important application, the
existing database schema will be modified. Existing applications that
are not decoupled from the schema will be impacted.

> > - The "validate employee ID" functionality is likely to be
> > used in more than one application. Encapsulating it as a
> > reusable component or service reduces the complexity of each
> > application and provides more consistency across the
> > enterprise.
>
> Aha. In case #2 of my new example tell me what components of the old
> application do you think would be reusable for the new application.

I don't understand the point of your question. Are you
suggesting that there is no potential for reuse of business
functionality across different applications?

> > - Embedded SQL requires more than a simple SQL statement.
> > The application must deal with database connections,
> > transactions, cursors, etc. Encapsulating this overhead and
> > allowing it to be reused across multiple applications reduces
> > the complexity of each application and the opportunity for
> > bugs to be introduced.
>
> If you have any difficulty opening database connection, or iterating
> a cursor, you may think refreshing your programming skills. Those
> are not the kind things that are worth abstracting.

The application should focus on _why_ it needs particular
functionality and _when_ to invoke that functionality. Mixing
database access details and schema-specific logic in with application
logic reduces maintainability. Further, duplicating that logic in
multiple applications contributes further to maintenance problems and
introduces more opportunities for bugs.

> > - Encapsulating SQL in components or services that expose
> > required functionality instead of the underlying database
> > schema makes impact analysis much more straightforward and
> > reliable. When SQL is embedded in applications across the
> > enterprise, it becomes virtually impossible to determine the
> > effect of any proposed change to the database schema.
>
> If the schema change is nontrivial (because just adding a column
> wouldn't break anything), then you make backward compatible
> views. Is this kind of obvious?

And for the next change? And the next? Are you suggesting that
adding an arbitrary number of new views with names like EMPLOYEE_V1,
EMPLOYEE_V2, EMPLOYEE_AS_OF_20051122, etc. is better practice than
encapsulating the business functionality required in one place? What
about changes that are more complex than simple additions? Those
views are going to start to affect performance.

Sincerely,

Patrick

------------------------------------------------------------------------
S P Engineering, Inc. | The experts in large scale distributed OO
| systems design and implementation.
pjm@xxxxxxx | (C++, Java, Common Lisp, Jini, CORBA, UML)
.

Relevant Pages

  • Re: Win32 Needs
    ... VB applications would be using the BDE through ODBC and NexusDB ... They too are pushing towards ANSI SQL compliance. ... contract I defined my database schemas in my own xml format. ...
    (borland.public.delphi.non-technical)
  • Re: Decouple SQL queries from class in OOP design
    ... > If the data is owned by the application, embedding SQL might be ... the database is owned by the application. ... Why would this not be possible if you are emedding SQL in your ... > effect of any proposed change to the database schema. ...
    (comp.object)
  • Re: If you were developing a database in Forth...
    ... John Passaniti wrote: ... blown database system is dumb. ... application necessarily maps well to a relational model or that SQL ... If you don't need all that power or the standardized interface (and most applications, particularly embedded ones, don't) there are much better alternatives, either pre-existing or, in some cases, a simple custom development. ...
    (comp.lang.forth)
  • Re: Question regarding OOP and database access
    ... trying to ensapsulate access to one table inside one class is ... There are two kind of changes that can be made in a database schema: ... all old SQL statements will still work. ...
    (comp.object)
  • Fwd: Module submission DBIx::DB
    ... extend DBI itself, or bundled inside a larger package (Catalyst, ... DBI Database schemes with perl interfaces ... - A database schema, and - A perl interface to that database schema ... but as it stands, there is no DBIx::UserDB distribution, instead ...
    (perl.dbi.users)