Tuesday, April 06, 2010

Why Transactional Database API approach?


We like (Transactional) Database API approach, because it:

We like Transactional Database API approach, because it:

DRY, do not repeat yourself

*. make software components modular, I am totally into modular programming.
*. software modules must carry out a very specific task (and be very efficient at carrying it out).
*. The same APIs are available to all applications in any Language that access the database. No duplication of effort.


*. each software module should be loosely coupled (to limit dependencies)
  -- Put SQL scattered willy-nilly around in Java/C*.JavaScript is high coupled.
  -- Schema change, adding table/column should only be changed in database, one place.
  -- B calls A, changed A, do not bother to touch B.
*. Make test simpler, easy to setup function and load test.
*. Easy to deploy.
E.g. PL/SQL installation only, no need to touch Java/C*.JavaScript mid tier and UI.
*. Defined clear interface contract.

*. It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loathe triggers.

*. Clearly separate all database access code (APIs Are King)

*. To understand the consequences of database refactorings, it is important to be able to see how the database is used by the application. If SQL is scattered willy-nilly around the code base, this is very hard to do. As a result, it is important to have a clear database access layer to show where the database is being used and how. To do this we suggest Database API approach.
*. The underlying structure of the database is hidden from the users, so I can make structural changes without client applications being changed. The API implementation can be altered and tuned without affecting the client application.

Control and Responsibility, DevOPS

*. It prevents people who do not understand SQL writing stupid queries.
  -- All SQL would be written by Database developers or DBAs, reducing the likelihood of dodgy queries.
*. SDLC: 80% is maintenance, Dealing with Change.
  -- Changing the database schema.
  -- Migrating the data in the database.
  -- Online data fix,
  -- Changing the database access code / data process logic.
*. Troubleshooting and firefighting
  -- Database developer and DBA can easily get and fix the SQL. Do not bother Java programmers.
*. Tuning SQL. Do not bother C# programmers.

*. Having a clear database layer (APIs Are King) has a number of valuable side benefits. It minimizes the areas of the system where developers need SQL knowledge to manipulate the database, which makes life easier to developers who often are not particularly skilled with SQL. For the database expert it provides a clear section of the code that he can look at to see how the database is being used. This helps in preparing indexes, database optimization, and also looking at the SQL to see how it could be reformulated to perform better. This allows the database expert to get a better understanding of how the database is used.

Profession = High Efficiency + High Quality

*. (ORM) Anything that generates SQL on-the-fly worries me, not just Java. I want to be able to cut and paste the SQL, not try and capture or trace it during a run.

*. More database features and functions
  -- Partition
*. Less code, less bug, easy to maintain.

*. Eliminate SQL Parse in host language. Parse consume client host CPU and Server CPU and Latches. PL/SQL keep the SQL cursor cached and opened.
*. Eliminate data round trip; data type conversion, the context switch.
*. Tightly couple the data model and data process design. Database world favor of Up Front Big Design.

*. Maximum the data share and reuse.


*. Business logic, Validation and lots of IF statements can be put and refined in Java with advanced language features, such as OO.


This list goes on and on.

Our concept is "build the data API in the database, you call the data API".
The data API encapsulate a transaction in a bit of code. Here we agree - no SQL in the client application, just call stored procedures - well tuned and developed explicitly to do that one thing.

Database API has been layered by different UI technologies over time.

All about API's. The applications don't do table level(select/insert/update/delete) stuff, the apps don't even really know about tables.

On top of database schema we have an API that does the data stuff.
(generally, functions or Ref cursor to retrieve data, procedures to change data)

In the application we call this API but have our own application logic as well
(only application logic is in the application, data logic is - well, right where it belongs - next to the data, waiting for the 'next great programming paradigm to come along')

The fact that our UI is in Java isn't really relevant. You could pretty much see how you would use this package from C#, Java/JSP, Swing, VB, Pro*C, Forms, Powerbuilder, Perl, PHP, Python, a mobile phone, <whatever the heck you want>.



ORM is flawed
Performance Anti-Patterns in Database-Driven Applications ,
  • Misuse of O/R Mappers
  • Load More Data Then Needed
  • Inadequate Usage of Resources
  • One Bunch of Everything

No comments: