Database Applications

DRAFT, 8/24/01 Chapter 9 We have spent t sort of vacuum. We should there of a database a development in tant not only to tional database issues as unders applications and base. Architectu Architecture des relate to one an content has the components: the MySQL database Architecture is t identify at a hig technologies wi shows the Web Figure 9-1. . The avarious languages. This detour programming with MySQL, but engine. Our look at database tanding the basic architectures how to map complex program re cribes how the different com other. A simple Web applicati architecture shown in Figure 9 Web browser, the Web serv . he starting point for the desig h level all of the relevant tech ll use to integrate. The archit browser talking to the server us FIGURE9-1.BM rchitecture of a simple Web applicaCopyright © 2001 O’Rein of any application. It helps you nologies and what standards those ecture in Figure 9-1, for example, ing HTTP. P tionponents of a complex application on using Perl to generate dynamic -1. This architecture describes four er, the Perl CGI engine, and the9 9.Database Applications he entire book so far discussing the database as if it exists in some It serves its purpose only when being used by other applications. fore take a look at how the database relates to the other elements pplication before exploring the details of database application examines conceptual issues impor- also to programming with any rela- programming covers such complex common to Web-oriented database ming models into a relational data-158 lly & Associates, Inc. As we will cover a variety of AP access MySQL th The architecture tion will use the There are nume we will cover th Web. Though on resent three very tions. Client/Serve At its simplest, t cessing into two the client/server that data is a ‘ machines; in mo face to the datab ent/server system You have proba example, is a gi ent and the Web database server, server is that it s Application Logi Because client/s processing, actu In other words, application to d Figure 9-2. The cliDRAFT, 8/24/01 in the later chapters of this section, MySQL exposes itself through Is tailored to specific programming languages. Java applications rough JDBC; Python applications through the Python DB-API, etc. above clearly shows to any observer that the application in ques- Perl DBI API to access MySQL. rous architectures used in database applications. In this chapter, e three most common architectures: client/server, distributed, and e could argue that they are all variations on a theme, they do rep- different philosophical approaches to building database applica- r Architecture he client/server architecture is about dividing up application pro- or more logically distinct pieces. The database makes up half of architecture. The database is the ‘server’; any application that uses client.’ In many cases, the client and server reside on separate st cases, the client application is some sort of user-friendly inter- ase. Figure 9-2 provides a graphical representation of a simple cli- . bly seen this sort of architecture all over the Internet. The Web, for ant client/server application in which the Web browser is the cli- server is the server. In this scenario, the server is not a relational but instead a specialized file server. The essential quality of a erves data in some format to a client. c erver specifically calls out components for user interface and data al application processing is left up to the programmer to integrate. client/server does not provide an obvious place for a banking ent/server architecture client ServerCopyright © 2001 O’Reilly & Associates, Inc. o interest calculations. Some client/server applications place this DRAFT, 8/24/01 kind of processi in the client with this question. Under MySQL, t to the lack of st MySQL to-do lis cedures will eve configuration. W rarely used in a used with the W Fat and Thin Cli It used to be tha client was a clie ing; a thin clien Web application client is any cli when you press Web form. The advantage o tous client. As l some sort of ma programmer nee needs to respon component in th has no such com Distributed The distributed logic is suppose Figure 9-3 show architecture. As you can see, special place fo ever, represents short, that it is im Figure 9-3. . The dng in the database in the form of stored procedures; others put it the user interface controls. In general, there is no right answer to he right answer currently is to put the processing in the client due ored procedure support in MySQL. Stored procedures are on the t, and—perhaps even by the time you read this book—stored pro- ntually be a viable place for application logic in a client/server hether or not MySQL has stored procedures, however, MySQL is client/server environment. It is instead much more likely to be eb architecture we will describe later in this chapter. ents t there were two kinds of clients: fat clients and thin clients. A fat nt in a client/server applications that included application process- t was one that simply had user interface logic. With the advent of s, we now have the term ultra-thin to add to the list. An ultra-thin ent that has only display logic. Controller logic—what happens “Submit”—happens elsewhere. In short, an ultra-thin client is a f an ultra-thin client is that it makes real the concept of a ubiqui- ong as you can describe the application layout to a client using rkup language, the client can paint the UI for a user without the ding to know the details of the underlying platform. When the UI d to a user action, it sends information about the action to another e architecture to respond to the action. Client/server, of course, ponent. Application Architecture application architecture provides a logical place where application d to occur, but it does not provide a place for UI controller logic. s the layout of an application under the distributed application this architecture is basically the client/server architecture with a r application logic—the middle tier. This small difference, how- a major philosophical shift from the client/server design. It says, in portant to separate application logic from other kinds of logic. FIGURE9-3.BMP istributed application architectureCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 In fact, placing a way to hinder y example, you n significant chang database. A chan touch the UI cod to do with the ch The distributed home for applic user interface co independent of data model affec base. The client changes. The distributed of all, the applic clients. Specifica tion points, it is when the applic The second, no ability to provide architecture are multiple actual clustering, it can multiple physica itself is still up a Complex transac MySQL today m tions in MySQL m Web Archite The Web archite ent than the dis possible by prov controller logic the Web architec Figure 9-4. . The Wpplication logic in the database or in the user interface is a good our application’s ability to grow with changing demands. If, for eed a simple change to your data model, you will have to make es to your stored procedures if your application logic is in the ge to application logic in the UI, on the other hand, forces you to e as well and thus risk adding bugs to systems that have nothing anges you are introducing. application architecture thus does two things. First, it provides a ation processing so that it does not get mixed in with database or de. The second thing it does, however, is make the user interface the underlying data model. Under this architecture, changes to the t only how the middle tier gets data from and puts it into the data- has no knowledge of this logic and thus does not care about such application architecture introduces two truly critical elements. First ation logic tier enables the reuse of application logic by multiple lly, by calling out the application logic with well-defined integra- possible to reuse that logic with user interfaces not conceived ation logic was written. t so obvious thing this architecture brings to applications is the easy support for fail-over and scalability. The components in this logical components, meaning that they can be spread out across instances. When a database or an application server introduces act and behave as a single tier while spreading processing across l machines. If one of those machines goes down, the middle-tier nd running. tions are a hallmark of distributed applications. For that reason, akes a poor backend for this architecture. As support for transac- atures, this state of affairs may change. cture cture is another step in evolution that appears only slightly differ- tributed application architecture. It makes a true ultra-thin client iding only display information in the form of HTML to a client. All occurs in a new component, the Web server. Figure 9-4 illustrates ture. FIGURE9-4.BMP eb application architectureCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 The controller c you are using. P of technologies things up furthe agement system tool for dynamic to a servlet actio The focus of thi architecture in w architecture show is embedded wi mostly relevant applications—ap Connectio Whatever archite your application worry about ho tioned earlier, th however, requir tions under that transactions. Connection The starting poi details behind w theless, making your code and t cal connections. work link is est until long after place until you a The details abo should not conc connection is es base. Once you are d resources it mayomes in many different forms, depending on what technologies HP, CGI, JSP, ASP, ColdFusion, and WebObjects are all examples for processing user events. Some of these technologies even break r into content creation and controller logic. Using a content man- like OpenMarket, for example, your JSP is nothing more than a ally building your HTML. The actual controller logic is passed off n handler that performs any application server interaction. s book will be the Web architecture since it is the most common hich MySQL is used. We will use both the vision of the Web n in Figure 9-4 and a simpler one in which the application logic th controller logic in the Web server. The simpler architecture is to MySQL applications since MySQL performs best for heavy read plications without complex application logic. ns and Transactions cture you are using, the focus of this book lies at the point where talks to the database. As a database programmer, you need to w you get data from and send it to your database. As we men- e tool to do that is generally some sort of database API. Any API, es a basic understanding of managing a connection, the transac- connection, and the processing of the data associated with those s nt of your database interaction is in making the connection. The hat exactly it is to be a connection vary from API to API. Never- a connection is basically establishing some sort of link between he database. The variance comes in the form of logical and physi- Under some APIs, a connection is a physical connection—a net- ablished. Other APIs, however, may not establish a physical link you make a connection, to ensure that no network traffic takes ctual need the connection. ut whether or not a connection is logical or physical generally ern a database programmer. The important thing is that once a tablished, you can use that connection to interact with the data- one with your connection, you need to close it and free up any have used. It stands to reason that before you actually issue aCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 query, you shou people to forget should always fr with them. In a written system c Part of cleaning ming languages (network failure of your language tions can arise fr uation. Transaction You talk to the database transac together, or not plex transaction debit of one acc debit occurs but the debit. A dat tion begins, whe the transaction f Until recently, M executed a SQL ately. This behav ever, support th Specifically, the supports two tra In Chapter 4, we MySQL client co often very differ committing, and low the comman * Even if you are usi the database can, i abort or package mld first connect to the database. It is not uncommon, however, for the other piece of the puzzle—cleaning up after themselves. You ee up any database resources you grab the minute you are done long-running application like an Internet daemon process, a badly an eat up database resources until it locks up the system. up after yourself involves proper error handling. Better program- make it harder for you to fail to handle exceptional conditions , duplicate keys on insert, SQL syntax errors, etc.); but, regardless of choice, you must make sure that you know what error condi- om a given API call and act appropriately for each exceptional sit- s database in the form of transactions.* A simple description of a tion is one or more database statements that must be executed at all.A bank account transfer is a very good example of a com- . In short, an account transfer is actually two separate events: a ount and a credit to another. Should the database crash after the before the credit, the application should be able to back out of abase transaction enables a programmer to mark when a transac- n it ends, and what should happen should one of the pieces of ail. ySQL had no support for transactions. In other words, when you statement under old versions of MySQL, it took effect immedi- ior is still the default for MySQL. Newer versions of MySQL, how- e ability to use transactions with certain tables in the database. table must use a transaction-safe table format. Currently, MySQL nsaction-safe table types: BDB (Berkeley DB) and InnoDb. described the MySQL syntax for managing transactions from the mmand line. Managing transactions from within applications is ent. In general, each API will provide a mechanism for beginning, rolling back transactions. If it does not, then you likely can fol- d line SQL syntax to get the desired effect. ng a version of MySQL without support for transactions, each statement you send to n a sense, be thought of as an individual transaction. You simple have no option to ultiple statements together in a complex transaction.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Transaction Isola Managing transa consider when u tions come with support transact Transactions ser this works, you A transaction iso are in the middl els, however, yo dirty read A dirty read another tran that read the repeatable read A repeatable from the sa many chang tion. In othe the committ start a new t phantom read A phantom result in the for example accounts wi reads of tha account to t query. If yo new “phanto set of rows e MySQL supports READ UNCOMM The transact READ COMMITT The transact phantom reation Levels ctions may seem simple, but there are many issues you need to sing transactions in a multi-user environment. First of all, transac- a heavy price in terms of performance. MySQL did not originally ions because MySQL’s goal was to provide a fast database engine. iously impact database performance. In order to understand how need to have a basic understanding of transaction isolation level. lation level basically determines what other people see when you e of a transaction. In order to understand transaction isolation lev- u first need to understand a few common terms: occurs when one transaction views the uncommitted changes of saction. If the original transaction rolls back its changes, the one data is said to have “dirty” data. read occurs when one transaction always reads the same data me query no matter how many times the query is made or how es other transactions make to the rows read by the first transac- r words, a transaction that mandates repeatable reads will not see ed changes made by another transaction. An application needs to ransaction to see those changes. read deals with changes occurring in other transactions that would new rows matching your transaction’s WHERE clause. Consider, , a situation in which you have a transaction that reads all th a balance of less than $100. Your transaction performs two t data. Between the two reads, another transaction adds a new he database with no balance. That account will now match your ur transaction isolation allows phantom reads, you will see the m” row. If it disallows phantom reads, then you will see the same ach time. the following transaction isolations levels: ITTED ion allows dirty reads, non-repeatable reads, and phantom reads. ED ion disallows dirty reads, but it allows non-repeatable reads and ds.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 REPEATABLE RE Committed, repeatable r SERIALIZABLE Only comm disallowed. As you climb th transactions, yo need to balance eral, READ COM very exceptional Using READ UNC One mechanism integrity of REA mary key plus a last updated. W the database, it u UPDATE ACCO SET BALANCE = WHERE ACCOU If this transaction cation can then Object/Re Accessing a rela special paradox while the object iors. In an obje objects across ap an object-oriente The most basic database is how be to simply ma does not create • Objects do lections or rAD repeatable reads as well as phantom reads are allowed. Non- eads are not allowed. itted, repeatable reads are allowed. Phantom reads are specifically e transaction isolation chain, from no transactions to serializable u decrease the performance of your application. You therefore your data integrity needs with your performance needs. In gen- MITTED is as high as an application wants to go, except in a few cases. OMMITTED of getting the performance of READ UNCOMMITTED but the data D COMMITTED is to make a row’s primary key the normal pri- timestamp reflecting the time in milliseconds when the row was hen an application performs an update on the underlying row in pdates that timestamp but uses the old one in the WHERE clause: UNT 5.00, LAST_UPDATE_TIME = 996432238000 NT_ID = 5 AND LAST_UPDATE_TIME = 996432191119 has dirty data, the update will fail and throw an error. The appli- re-query the database for the new data. lational Modeling tional database from an object-oriented environment exposes a : the relational world is entirely about the manipulation of data world is about the encapsulation of data behind a set of behav- ct-oriented application, the database serves as a tool for saving plication instances. Instead of seeing the query data as a rowset, d application sees the data from a query as a collection of objects. question facing the object-oriented developer using a relational to map relational data into objects. Your immediate thought might p object attributes to fields in a table. Unfortunately, this approach the perfect mapping for several reasons. not store only simple data in their attributes. They may store col- elationships with other objects.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 • Most relation itance. Think about an like the address The basi data man meth men then data Figure 9-6 show Each row from fore takes a resu Person instance lier: how do you database applica son’s Address o table of a relatio tionships throug person table. Rules • Each pers • Object fie map to co • Each row ated pers • Each man base entit • Inheritanc two tableal databases—including MySQL—have no way of modeling inher- address book application. You would probably have something and person tables shown in Figure 9-5. least apparent issue facing programmers is one of mindset. The c task of object-oriented access to relational data is to grab that and immediately instantiate objects. An application should only ipulate data through the objects. Most traditional programming ods, including most C, PowerBuilder, and VisualBasic develop- t, require the developer to pull the data from the database and process that data. The key distinction is that in object-oriented base programming, you are dealing with objects, not data. s the object model that maps to the data model from Figure 9-5. the database turns into a program object. Your application there- lt set and, for each row returned, instantiates a new Address or . The hardest thing to deal with here is the issue mentioned ear- capture the relationship between a person and her address in the tion? The Person object, of course, carries a reference to that per- bject. But you cannot save the Address object within the person nal database. As the data model suggests, you store object rela- h foreign keys. In this case, we carry the address_id in the of Thumb for Object/Relational Modeling istent class has a corresponding database table. lds with primitive datatypes (integers, characters, strings, etc.) lumns in the associated database table. from a database table corresponds to an instance of its associ- istent class. y-to-many object relationship requires a join table just as data- ies with many-to-many relationships require join tables. e is modeled through a one-to-one relationship between the s corresponding to the class and subclass.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 With just a tiny world of comple extra bit of com Company class a rate from a Pers a guideline. In s quently have no would not have Figure 9-5. The da Figure 9-6. The ob P chang familyN givenNa middleN maiden title : Stamount of extra complexity to the object model, we can add a xity to the challenge of mapping our objects to a data model. The plexity could be to have Person inherit from Entity with a lso inheriting from Entity. How do we capture an Entity sepa- on or a Company? The rule we outlined above is actually more of ome instances, the base class may be purely abstract and subse- data associated with it in the database. In that instance, you an entity in the database for that class. ta model for a simple address book application ject model supporting a simple address book application person person_id (PK) address_id family_name given_name middle_names maiden_name title address address_id (PK) line_one line_two line_three city state postal_code Address lineOne : String lineTwo : String lineThree : String city : String postalCode : String 1 erson eAddress( ) ame : String me : String ames : String Name : String ringCopyright © 2001 O’Reilly & Associates, Inc.