1	Fusion Development: A Database-Centric Approach Dr. Paul Dorsey Dulcian, Inc.
2	Background Fusion technology stack is large and complex. Hard to make the transition into the J2EE environment. Host of different tools, programming languages, architectures, and technologies Projects often have the illusion of progress. Building functioning, scalable production software often becomes an impossible task.
3	Why do OO people avoid the database? Culture? Lack of knowledge? Clinical pathology?
4	“Frameworkaphobia” Definition: An irrational avoidance of frameworks (particularly non-open source) Diagnostic Indications: Desire to build everything him/herself “If I don’t build it, it must stink.” “If Oracle built it, it must really stink.” Irrational avoidance of Application Development Framework – Business Components (ADF BC) Symptoms: Higher than expected project cost Project failure Treatment No known cure Some success with short leashes and large bats Related conditions Megalomania Paranoid delusions
5	“Database Avoidance Syndrome” Definition: An aversion to placing any logic in the database Diagnostic Indications: “We should be database-independent.” “Databases are old fashioned. Everyone is coding this way.” Symptoms: Twice as much code as is necessary Performance is 10 times slower. Network traffic is 100 times as great. Four times the load on the database server Three times the development time
6	“SOAphilia” Definition: Irrational desire to refactor small systems to use web services and BPEL Diagnostic Indications: Ownership of 72 BPEL books Desire to use BPEL for data-centric processes
7	Oracle Architecture First-rate Service Oriented Architecture (SOA)-centric environment. Built from an OO developer’s perspective: Lacks much of the vision that would make Designer users comfortable “Not-so-subtle” encouragement to place business rules enforcement in the middle tier, coded as Java Can be used to articulate data-centric complex business processes, using portions of the architecture Business Process Execution Language (BPEL) Can lead to applications with poor performance because of the number of round trips needed between the middle tier and the database.
8	“Thick Database” Defined (1) Micro-Service-Oriented-Architecture (M-SOA) approach Division between the database and user interface (UI) portions. Two key features involved in "thick database thinking": Nothing in the UI ever directly interacts with a database table. All interaction is accomplished through database views or APIs. Nearly all application behavior (including screen navigation) is handled in the database. Thick database does not simply mean stuffing everything into the database and hoping for the best.
9	“Thick Database” defined (2) Creating a thick database makes your application UI technology-independent. Creates reusable, UI technology-independent views and APIs. Reduces the complexity of UI development. Database provides needed objects. Reduces the burden on the UI developer
10	Database vs. UI Technology Stack-Independence Database Oracle will add features. DBMS will not internally refactor. Existing stack “works.” Huge DBA learning curve Huge cost of switching UI Technology Stack Java EE or .Net? AppEx FLEX All environments change Redesign assured Every year BRIM^® has been rebuilt.
11	Thick Database Benefits Minimizes development risk Helps build working applications that scale well.
12	Benefit 1. Better Performance Improved overall throughput Caused by combined effect of: Fewer roundtrips Less network traffic Better database access Test: Average improvement in performance? a) 10% b) 100% c) 10x d) 100x e) 500x
13	Benefit 2. Fewer Round Trips Requires many fewer round trips from the application server to the database. Each screen should be 1-3 round trips Test: OO developers can write screens that require this many database round trips: a) dozens b) hundreds c) thousands d) millions
14	Benefit 2. Less Code Required Less PL/SQL code is needed to perform data centric operations than Java. PL/SQL has more data tricks. Database-intensive code will always be more efficiently written in the database. Test: Average reduction in the amount of code needed is: a) 10% b) 25% c) 50% d) 90%
15	Benefit 3. Less Development Time Needed Less code means less coding time. Simpler architecture Separate user interface and logic Building two smaller applications is easier than building one large one. UI is trivial. Can be shown to users right away. Faster feedback to the development team Helps to identify design errors much earlier in the process Test: Using a thick database approach can reduce development time by a) 10% b) 33% c) 50% d) 66%
16	Benefit 4. Easier to Maintain Application being built is divided into two parts Each has less code to maintain. Application is clearly partitioned. When a business rule changes, only need to look through half of the code to find it. As the number of lines of code in an application doubles, the complexity increases by a factor of four.
17	Benefit 5. Easier to Refactor UI technology stack changes are common. .Net Java EE battle rages on. Web architecture is more volatile than the database platform. Defense against the chaos of a rapidly evolving standard Test: What is the probability that your web UI standards will be the same in 18 months?
18	Benefit 6. Better Use of Different Talent Levels With minimal additional training, skilled SQL and PL/SQL developers can help build web applications with no web skills whatsoever. If sophisticated UI developers are available, they can focus on delivering very high quality user interfaces.
19	Thick Database Development Process Two portions of an application can be coded independently Teams can work in isolation until substantive portions are working. First version of the UI is built within a few days Use as testing environment for the database team Feedback can be received from users. Use Agile process Minimal design work done to produce a partially working system. Additional functionality created in an iterative design process.
20	User Interface Design Design the application. Screens are designed on paper. White boards are used for page flows. Real screen mock-ups are usually a waste of time. A careful diagram on a piece of paper suffices for the initial UI design. MS Access is also good.
21	Interface Design Once the UI design is complete, determine: What views are required APIs that will be called
22	Interface Stubbing Stub out the code for the views and APIs. select <values> from dual APIs = functions that return a correct value (usually hard-coded). Interfaces will change as the application matures.
23	UI and Database Development UI and database development take place at the same time. UI team takes the APIs and incorporates them into the application. Database team makes them work.
24	How Thick is too Thick? What would happen if 100% of all UI logic were placed in the database? Tabbing out of a field LOV populated from database Page navigation Pathologically complete way to implement the thick database approach. A system built this way would be sub-optimal. But it works
25	How Thin is too Thin? Can a skilled team successfully build applications that are 100% database “thin”? Requires a highly skilled team. Minimize round trips ANY middle tier technology (e.g. BPEL) can also be a performance killer. Possible but difficult
26	Stateless Programming Client/server development was inherently stateful. Counted on user sessions being persistent. Used persistent global variables Assumed that transactions were only being committed to the database on demand. Web development usually needs to be stateless. Reusable set of sessions in a session pool Database developers tend to strongly resist moving into a stateless environment. Stateful coding techniques are very hard to give up. Developers must abandon a stateful development style in order to successfully build web applications.
27	Persistence in “Stateless Land” Server-side Create a table and persist all global info Persistent lock rows (lock_id column) Pass session ID on each call Worry about abandoned sessions Best approach – but requires more work Middle tier Can’t be done unless you are only using 1 application server Usually persists to the database Client Cookies Pass context to database each time
28	Function-Based Views Functions can return object collections. An object collection can be cast to a table. Object collections types are supported in SQL. The idea: Build a view over the function to hide complex procedural logic.
29	Underlying Types and Functions type lov_oty is object (id, display_tx); type lov_nt is table of lov_oty; function f_getLov_nt (i_table_tx,i_id_tx,i_display_tx,i_order_tx) return lov_nt is v_out_nt lov_nt := lov_nt(); begin execute immediate 'select lov_oty(' \|i_id_tx\|\|','\|\|i_display_tx\|\|')'\|\| ' from '\|\|i_table_tx\|\| ' order by '\|\|i_order_tx bulk collect into v_out_nt; return v_out_nt; end;
30	Query the Function as a Table Generic value list query for any UI: Uses bind variables – no significant performance impact Completely dynamic – any new fields/tables/etc. select id_nr, display_tx from table( cast(f_getLov_nt ('emp', 'empno', 'ename\|\|''-''\|\|job', 'ename') as lov_nt) )
31	Create a View Views placed on top of dynamic functions: Completely hide the logic from the UI INSTEAD-OF triggers make logic bi-directional. Minor problem – There is still no way of passing parameters into the view other than by using some type of global. Create or replace view v_generic_lov as select id_nr, display_tx from table( cast(f_getLov_nt (GV_pkg.f_getCurTable, GV_pkg.f_getPK(GV_pkg.f_getCurTable), GV_pkg.f_getDSP(GV_pkg.f_getCurTable), GV_pkg.f_getSORT(GV_pkg.f_getCurTable)) as lov_nt) )
32	De-Normalized Views The idea: Convert relational data into something that will make user interface development easier The solution: Use a view with a set of INSTEAD-OF triggers
33	De-Normalized view create or replace view v_customer as select c.cust_id, c.name_tx, a.addr_id, a.street_tx, a.state_cd, a.postal_cd from customer c left outer join address a on c.cust_id = a.cust_id
34	INSTEAD-OF Insert create or replace trigger v_customer_ii instead of insert on v_customer declare v_cust_id customer.cust_id%rowtype; begin if :new.name_tx is not null then insert into customer (cust_id,name_tx) values(object_seq.nextval, :new.name_tx) returning cust_id into v_cust_id; if :new.street_tx is not null then insert into address (addr_id,street_tx, state_cd, postal_cd, cust_id) values (object_seq.nextval,:new.street_tx, :new.state_cd,:new.postal_cd, v_cust_id); end if; end;
35	INSTEAD-OF Delete create or replace trigger v_customer_id instead of delete on v_customer begin delete from address where cust_id=:old.cust_id; delete from customer where cust_id=:old.cust_id; end;
36	INSTEAD-OF Update create or replace trigger v_customer_iu instead of update on v_customer begin update customer set name_tx = :new.name_tx where cust_id = :old.cust_id; if :old.addr_id is not null and :new.street_tx is null then delete from address where addr_id=:old.addr_id; elsif :old.addr_id is null and :new.street_tx is not null then insert into address(…) values (…); else update address set… where addr_id=:old.addr_id; end if; end;
37	Thick Database Success Stories
38	Complex Batch Routine Calculate and distribute sales goals to a sales force. Algorithm driven by parameters for each salesperson and region stored in the database. User interface built in Java All calculations were done in Java. Routine consisted of 40 classes, about 10,000 lines of code, and required six weeks to write. Most significant performance problems were due to poorly coded SQL. Significant changes to the algorithm were required, Discard existing Java and rewrite it as a database-side routine.
39	Case Study Batch routine Sales goaling
40	Complex UI Tree – V1 BRIM UI Tree (like TOAD) Trying to support a complex tree navigation structure Followed conventional wisdom to the letter. Everything was placed in the middle tier. All queries were stored on the application server No code was placed in the database. Queries spun off child queries which in turn spun off other child queries (7 levels deep), and a single tree refresh took 12 minutes. Since then, the tree control has been rewritten a few times. Each time more and more logic was moved into the database. Views that flattened the hierarchical database structure were created. UI design was refactored. Tree refresh executes with sub-second response.
41	Complex UI Tree – V2 Sophisticated tree written in Java using conventional wisdom. Virtually no code in the database Performance was poor (mainly because of excessive round trips and unnecessary full tree refreshes) 12,000 lines of code spread across 50 classes making the code difficult to modify Using a complete thick database approach Design refactored so that all tree display logic was stored in the database The tree is sent by the database to the user interface as an XML file. Modifications to the tree are also sent as small XML files that indicate tree-node refresh commands.
42	Case Study: Tree Results
43	A Tale of Two Systems 1. Internal Modification Request Tracker: Built using conventional approach by an experienced Java team. Earlier version built by offshore, inadequately skilled development team. To create a working version of the system took about 6 months – flawed architecture 2. Complex order entry system Built using the “thick database” approach by a team with equivalent experience. Thick database approach was used from the start All navigation supported using a tree on the left hand side of the screen. Tree itself is built into the database. All navigation logic is handled in the database.
44	Case Study: 2 Similar Systems - Results
45	Conclusions Moving code from the middle tier to the database had the following benefits: Reduced the total amount of code Reduced development time Improved performance Reduced network traffic Reduced the complexity of the application Thick database approach is a viable alternative to the conventional wisdom of reducing reliance on the database. Leverages existing database talent Can result in dramatic improvements in performance
46	Share your Knowledge: Call for Articles/Presentations Submit articles, questions, … to IOUG – The SELECT Journal ODTUG – Technical Journal select@ioug.org pubs@odtug.com Reviewers needed
47	Dulcian’s BRIM^® Environment Full business rules-based development environment For Demo Write “BRIM” on business card Includes: Working Use Case system “Application” and “Validation Rules” Engines
48	Contact Information Dr. Paul Dorsey – paul_dorsey@dulcian.com Dulcian website - www.dulcian.com