Wednesday, May 03, 2006

User-Defined Database Functionality

Beyond the out-of-the-box built-in RDBMS functionality, you often need to extend the capabilities of your database by developing your own database-resident functionality, or data logic.

The requirements for user-defined database functionality range from performance (see free-sample chapter in previous posts), integration with existing functionality, security, ability to share data logic (across all clients applications), cost reduction, skill reuse, deployment/topology choices (see dilemma below), and many other reasons.

The following applications are candidates for being implemented as user-defined database functionality:
- Traditional data logic: SQL statements grouping
- Content/Repository management
- In-database Message Queuing and propagation
- Reporting functionality: generate pdf, excel, and XML documents from relational data
- Data-based Alert, and Notification
- Expressions Parsers (see code fragment below)
- Key generation, MD5 CRC checksum
- Multi-media data processing: store, retrieve, transform/convert images (GIF, PNG, JPEG), audio, video, or heterogeneous media data. Alternatively you can just use Oracle interMedia, a packaged multi-media processing framework.
- Text data management: index, search, and analyze texts stored in RDBMS. Alternatively you can just use Oracle Text, a packaged text data processing framework.

However, an RDBMS is not an application server, you must make the determination that the user-defined functionality pertains indeed to the RDBMS.
Examples:
- tax and rate engines, which take input values and return tax amounts or rates, are better implemented as EJBs and do not belong to the RDBMS.
- a JavaServer Pages that generate user-interface rendered in a browser does not belong to the RDBMS.
As discussed in many forums, making the determination is not always a clear cut.
Examples:
- a frequent dilemma is the choice between caching data in the middle-tier (i.e., CMP Beans, POJO) or pushing the processing closer to the data (i.e., stored procedures)? See http://www.theserverside.com/discussions/thread.tss?thread_id=2613
- a server-side UI can be produced using either a JavaServer Pages (that is deployed in the middle-tier) or the database-resident Oracle Application Express (formerly known as HTML DB), or use the HTP package, directly in the database.

The mechanisms and programming models for building user-defined database functionality
range from proprietary procedural languages (e.g., PL/SQL, T-SQL), standard languages (e.g., Java), de facto standard languages (C#, VB), messaging systems for database messaging, job/task scheduler, and so on.

The Oracle database furnishes PL/SQL, Java, Streams AQ, DBMS_SCHEDULER and so on for building user-defined functionality.
There are tons of PL/SQL examples, see http://asktom.oracle.com.
Java in the database is an open alternative to PL/SQL for implementing advanced functionality such as a domain-specific language parser. Imagine an Excel expression evaluator/parser that can be used to generate formulas representing complex interrow calculations in the database and dynamically generating the corresponding SQL.

Mini Parser code fragment
public class Mini parser {
package example;
import example.parser.parser;
import example.formula.Expr;
import java.io.StringReader;

public static String eval(String spreadsheetExpr) throws Exception {
parser p =
new parser(new StringReader(spreadsheetExpr));
Expr expr = (Expr)p.parse().value;
return expr.eval();
}

public static void main(String[] args) throws Exception {
System.out.println(eval(args[0]));
}
}

Then the following query returns 15 which is not the generated SQL, but gives you an idea of how the expression evaluator works:

select eval('=sum(1,2,3,4,5)') from dual;

The complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

In the next posting, I'll elaborate on how to make the user-defined database functionality available for invocation by other database components as well as external components.

18 comments:

Catherine Granger said...

Hi Kuassi,

so where do you put object-relational mapping? Is that also a User-Defined Database Functionality?

Kuassi Mensah said...

Catherine,

Good point however, unless an OR mapping framework is hosted direclty within the RDBMS -- didn't this recall you the infamous OODBMS? -- OR mapping will happen in the middle or client tiers and hence cannot be viewed as database-side user-defined functionality.

Having said that user-defined database functionality can participate to OR mapping. See my reponse to similar question on the following blog:
http://www.theserverside.com/blogs/thread.tss?thread_id=39969#207450

Anonymous said...

I copy/paste your code example and it doesn't even compile!!!

Anonymous said...

What do you think of Oracle/other DB vendors exporting some of the core db processing logic as APIs in C/Java...

Ex. sorting, joins, searching, n-memory tables, ...

As of now your code and data need to live in/or swtich context by making stored procedure call to the DB tier, to make use of these features .. so this does not exaclty fit in as 360 degree programming ...

Kuassi Mensah said...

>I copy/paste your code >example and it doesn't even >compile!!!

Of course, it won't compile, this is just a code fragment to get to the point. As i have mentionned, the complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

Kuassi Mensah said...

> What do you think of
> Oracle/other DB vendors
> exporting some of the core
> db processing logic as APIs in C/Java...

Interesting comment, only speaking for Oracle, i doubt that any of the core db processing be exported outside of the DB. In fact the core Oracle DB functionality is generated as C/C++; then some surrounding core functionality such as the Java VM runtime are generated Java classes that are installed by default but run in interpreted mode (you need to install the native compiler libraries to run the system classes as well as user-defined Java classes in natively compiled mode).
Having said that, as i like to say, the RDBMS is not an application server so the removal of the J2EE stack from within the database is a sound choice, and there might be other functionality that are better fit for a middle-tier but then these are out of the scope of this blog (maybe i should start another blog on user-defined middle-tier functionality :)).

Regarding the last segment of your comment all the user-defined database functionality i am talking about (including the mini-pasrer) run/execute within the dtabase.
Vut as i will touch on in following posts, 360 database programming also involves calling user-defined database functionality from within the database, from outside the database and calling client-side or middle-tier functinality from within the database (this last one is a fast growing trend that many of you are adopting, big and exciting topic, stay tuned)
Hope this clarifies.

Herod T said...

Never do any processing in the middle tier. Why have the middle tier process the tax amount when the database can do it faster with better scalability and better security and store it for later use and/or auditing?

The middle tier will be replaced with some brand new widget gizmo some high paid marketing company has told everybody is great and everything will have to be done .

Do it in the database. It has been here for decades and will continue to be here for decades more.


The only candidate of yours that I would settle with being in the middle tier is the report output generation - converting output to PDF etc. But, I would argue the point that it shouldn't be done in the middle tier, instead it should be a program that the database calls to generate the information then the output stored in the database then the output presented to the user. The output is cleaned by the database later.

Nothing else.

Never take the data away from the database and process it and put it back. The only exception to that rule is when a human being has to process the data.

Kuassi Mensah said...

Great comment, i agree with most things you said however don't you think this is a too radical approach?

Herod T said...

Not radical at all.

Data is "forever"
front ends change.

Anonymous said...

HI:
I hope this meets you in fine time .I am munch intrested in oracle i am about to finish OCA EXAM by this month.can you people refere me to some one in oracle india.
Thank you
A.mohamed suhail
suhail_moh@yahoo.co.in

Esteban Capoccetti said...

Hi kuassi, glad to read from you again,

Herod T; I don't know if you have already read the following article:

http://www.oracle.com/technology/pub/articles/odtug_award.pdf

It is called "A DATABASE-CENTRIC APPROACH TO J2EE
APPLICATION DEVELOPMENT" and shows the different architectures we can adopt when developing an application,where Bussines/Data Logic should reside. I recommend this article.

After having using Oracle Database functionalities for more than 7 years I can confirm that the "thin->thin->Fat" approach is the most convenient for any new project, and even more convenient if you have to meet a deadline!!; but this is MY opinion.

Kuassi, thanks for your interesting blogs.

Esteban Capoccetti.

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰套,自慰,性感吊帶襪,吊帶襪,情趣用品加盟AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,成人網站,A片,A片下載,免費A片,免費A片下載愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片,豆豆聊天室,聊天室,UT聊天室,尋夢園聊天室,男同志聊天室,UT男同志聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,6K聊天室,女同志聊天室,小高聊天室,上班族聊天室,080中部人聊天室,同志聊天室,聊天室交友,中部人聊天室,成人聊天室,一夜情聊天室,情色聊天室,寄情築園小遊戲情境坊歡愉用品,情趣用品,成人網站,情人節禮物,情人節,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,成人影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,視訊聊天,A片,A片下載,免費A片,嘟嘟成人網,寄情築園小遊戲,女同志聊天室,免費視訊聊天室,一夜情聊天室,聊天室

Anonymous said...

童裝批發
童裝批發
童裝
童裝
酒店喝酒
酒店消費
暑假打工
寒假打工
酒店經紀人
酒店現領
禮服店
酒店小姐
酒店兼職
酒店上班
酒店兼職
禮服店
酒店上班
酒店打工
酒店小姐
酒店經紀
酒店兼差
假日打工
台北酒店經紀
寒暑假打工
專業酒店經紀
酒店藝名
台北酒店
酒店日保
酒店入行
台北禮服店
酒店類型
酒店午場
酒店規定
酒店行政
酒店資訊

Edwin said...

Buy Kamagra
Earn Google
Viagra Cialis
Cheap Kamagra
Cheap Viagra
Cheap Cialis
Make Money on Google
M65 Jacket
M65 Field Jacket
Airline Dog Carrier
Airline Dog Carriers
Viagra Cialis
Earn Google
Airline Dog Carrier
Airline Dog Carriers
Airline Approved Dog Carriers
ED Hardy Wholesale
Copy DVD Software
How to Send Fax
14k Yellow Gold
Redneck Costume
14k Gold Heart
Tandem Baby Stroller

VUONG said...

医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
医師 求人
ビジネス英会話
英語 メール
英語塾
社会人入試
toeic対策
埋没法

会社設立 手続き
人材派遣
東横 賃貸
田園都市 賃貸
デザイナーズ 賃貸
恵比寿 賃貸
学芸大学 賃貸
目黒 賃貸
中目黒 賃貸
新築 賃貸
ペット可 賃貸
三軒茶屋 賃貸
パチンコ求人
販売管理
債務整理 新宿
過払い 東京
液晶 テレビ 32

VUONG said...

先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
事業再生
経営計画書
経営改革
経営改善
会社分割
経営コンサルティング
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
婚活 名古屋
浮気調査 松戸
浮気調査 船橋
備考調査 追跡調査 東京
不倫調査 離婚調査 東京
浮気調査 素行調査 東京 
ストーカー相談 ストーカー対策 東京
結婚調査 結婚詐欺 東京
信用調査 企業調査 東京
調査料金 東京
身上調査 身元調査 身辺調査 東京
所在調査 東京
夫 妻 浮気 東京

vancouver website design said...

That's a good feature for reduce the complexity and easily maintain the database. Thanks for post.

r4 said...

This is just a code fragment to get to the point. As i have mentionned, the complete mini-parser source code as well as the corresponding CUP.