Monday, September 10, 2007

Good practice: Middle-tier DAL or Database API – Database access guideline

The reason I don't use "Best Practice" is that will prevent rest of people create some better new ideas, and let you out the 'Keep an open mind' zone.

So I turned to Good practice:

Middle-tier DAL or Database API – Database access guideline
(Hibernate vs Store procedure)

• Maximise SQL and minimise PL/SQL
• Minimise client code and maximise database server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

Reference:

Business Logic - PL/SQL Vs Java
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:883929178230

I prefer to put all logic that deals with data in PLSQL. There is no more natural language to interact with SQL data then PLSQL. None.

For example -- native compilation was added -- turned plsql into object code that runs natively on the OS.

Database API, build the data API in the database, you call the data API
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25405782527721

When to use Hibernate DAL
• One step SQL (SELECT OR UPDATE) to finish your goal
• Heavy and forced interaction in mid tier, like CCBB encryption, that means high coulping too, and hard to encapsulate the process logic.

Jave developer can help us on this list.

When to use Database API and Store Procedure Package
• Complex SQL
For example, book delete check, reference Single Hash Table and list of IDs.
• Many (more than 2) steps data machinations logic in a sigle module
• Reture rows more than one page, let's say 20 rows from database.

We like Database API approach, because it:
1.. make software components modular, I'm totally into modular programming
2.. software modules must carry out a very specific task (and be very efficient at carrying it out)
3.. each software module should be loosly coupled (to limit dependencies)
4.. 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 loath triggers.
5.. It prevents people who don't understand SQL writing stupid queries.
All SQL would be written by PL/SQL developers or DBAs, reducing the likelyhood of dodgy queries.
6.. 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.
7.. The same APIs are available to all applications that access the database. No duplication of effort.

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.

Our concept is "build the data API in the database, you call the data API" .
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.

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, a mobile phone, .


Storing application code in the database has had it's champions and detractors.

When you start putting application code in the database, you are in the thoroughly non-portable arena. That code, were you to port your application to another database, would have to be rewritten. But think about your application mid-tier layer, will you port Java code to C#/.Net,-> Pascal -> C -> VB, then to Python, Ruby on Rail ...etc., will you do that?

Its very specificity to that database means it can also take advantage of, and wire close to that engine. There are situations where stored code in the database can be notably faster. Supposed you have to update some chunk of a million rows after doing some machinations on the data.

In a stored procedure the data is read, manipulated, and updated in one step. Meanwhile if you did the same in your middle tier application code, you would have to send that data set over the network, do your manipulations, and send it back. Not only would it make this one task slower, but other transactions vying for that same data could potentially have to wait while that data is in transit, and being manipulated. Also, stored code can serve to encapsulate specific requests which can be invaluable at simplifying your overall application. All three databases support stored procedures and functions. Oracle also supports packages, or collections of stored procedures as well as various object oriented features, which almost no one ever uses. An additional note, a database engine actually context switches between stored code, and the SQL code embedded therein. As since of 9i, Oracle introduced bulk binding, so you can do work on large sets of rows, and update them all in one go, instead of each loop iteration. This feature can even further improve performance quite dramatically.

Document the SQL access database
We'll build a small application for this target, on Oracle APEX (HTML DB).
then QA (and Developer) need to record all the SQL access db in the new developing applications.
* SQL execution path
* SQL performance statistics

Tuesday, July 24, 2007

我年轻时所不了解的事情

-- 转贴子从 生活帮-LifeBang
-- 以供 自我勉励

那些你曾经为之激动、兴奋,消耗了你无数时间和精力的事情,大多数是无足轻重的。

那些等着万事俱备才去做的事情往往永远也没机会做。
-- 立刻行动! 行动上热情!

如果大家都抱怨你说你太特例独行与别人格格不入时,你一定在正确的轨道上。
如果工作是你全部的生活,你的生活就是一项艰苦的工作。

任何一个成功的人都有失败的时候。不要企图永远占据成功者的位置。

每件事都要花费计划中两倍的时间而只得到期望中一半的成果。没必要为此沮丧,认清现实,继续前进。

无论你怎么尝试改变,你总是你自己。
世界上最响的声音就是人们的抱怨声。别再让它更响了。

Saturday, July 14, 2007

PL/SQL 在 Oracle 11g 里面的功能完善: "continue"

Oracle 终于支持 CONTINUE 了, 像我一样 做 Development DBA 的人用得着了.
原来老得翻翻文档, 找出 Label 和 GoTo的语法, 特别烦人.

这个东西实在算不上一个新特性, 只能叫作功能完善而已.

使用"CONTINUE" 的 PL/SQL程序样本, 请参考:
http://technology.amis.nl/blog/?p=2261