 | Chapter 3 |  |
| Chapter 3 | |
Contents:
Overview of WebDB
WebDB Architecture
WebDB is a user-friendly tool for developing database-driven web applications and sites. You perform every WebDB task, from database administration to application development, using just a browser; everything in a WebDB site, from applications to content, can be stored inside a backend database.[1]
[1] Oracle calls this concept "100% in the browser, 100% in the database," meaning that everything is accessed with just a browser and stored in just a database.
Every WebDB component, from development tools to the database administrator toolkit, has an HTML user interface, eliminating the need for complex tools like Oracle Forms or Oracle Enterprise Manager (OEM). Because browsers are equipped with standard Internet protocols, WebDB client machines do not need SQL*Net or Net8; this allows WebDB sites to be run or administered from "thin-client" machines. WebDB can also take advantage of JavaScript-enabled browsers.
Everything in a WebDB site can be stored directly inside an Oracle database. This allows the site to be professionally administered and maintained using the same tools and techniques as for any other production Oracle database. As an added benefit, the site's performance can be monitored and improved through well-understood database tuning techniques. WebDB's "database-centric" approach helps application developers and DBAs leverage their current skills, rather than acquiring an entirely new and unfamiliar skill set.
How you use WebDB depends on your job. Database administrators can use WebDB to manage database objects, check database logs, and perform other DBA tasks. Application developers can use WebDB to create HTML-based web applications using a set of wizards that automatically build application components, like forms or reports. End users can use the WebDB components you create to view reports, fill out data entry forms, or view the content published by other users. Additionally, almost any user can use WebDB to publish web content on their own personal home page, as well as view content made available by other WebDB users.
Given the nature of the Web, these tasks are rarely mutually exclusive. For example, a DBA might want to upload "tips and tricks" to a page of her personal WebDB site. An application developer might want to monitor application performance or create database objects such as tables. An end user might want to create a report based on a SQL query (stranger things have been known to happen!).
In this chapter, I'll give you an overview of the different things you can do with WebDB, then look at the architecture that makes this all possible. As you read this chapter, keep in mind that WebDB itself is written entirely in PL/SQL. In many ways, it's the ultimate example of the power of combining HTML and PL/SQL into a web application.
WebDB is divided into three broad categories: database administration, application development, and content-driven web site management. In the next three sections, we'll briefly look at each area with an eye towards seeing what the product does, though not necessarily how you perform each task.[2]
[2] The appendix, Resources for the Oracle Web Developer, contains a list of resources that provide complete, step-by-step instructions.
You can use WebDB to perform many routine database administration tasks using just a web browser, rather than a "fat client" like the Oracle Enterprise Manager suite of database management tools. For example, suppose you've gotten a call from Bob in accounting insisting that "my Internet doesn't work." Befuddled by your patient explanation that his statement makes absolutely no sense, he demands that you walk over to his desk and help him fix his problem. Since customer service has been added to your list of job performance metrics, you comply. When you arrive at Bob's desk, you realize that he has simply forgotten his password.
Since no one in his right mind would install SQL*Plus (much less OEM) on Bob's machine, you would normally have to trek back to your desk to reset his password, confirming Bob's darkest suspicions that you really don't know what you're doing. Fortunately, you can use WebDB's security management options to save yourself the trip. While you can't do everything from WebDB, you can perform many routine tasks, such as creating database objects, managing user accounts and security roles, and monitoring database performance. Since everything is accessible with just a browser, you can perform these tasks from almost any client, whether you're on-site or not.
The following sections briefly describe how you'd use WebDB to perform typical database administration tasks.
You can use WebDB to view, or browse, the definitions for database objects. The browse capability of WebDB applies to all objects in a particular schema. Once you select the schema, WebDB displays a list of all the object types in the schema, such as tables, indexes, and views. Figure 3.1 shows the WebDB "Browse Database Objects" option.
Selecting a specific object type (for example, Tables) presents you with a list of all objects of that type owned by the specified schema (for example, EMP and DEPT). Selecting a particular object either displays its definition or takes you to the "Edit Objects" option.
WebDB security is built with Oracle's standard security model. There are three security components:
WebDB users, whether they are created in WebDB or exist in the database beforehand, are simply Oracle schemas. For example, the SCOTT (assuming it exists) and SYS accounts appear as WebDB users, even though they have nothing to do with WebDB. Consequently, WebDB users are like any other Oracle account: you can log into them with SQL*Plus, manage them with Oracle Enterprise Manager, and access their schema objects with third-party tools like ODBC.
WebDB object privileges are Oracle object privileges. In addition to the native system and object privileges, WebDB introduces two additional, non-native privileges, build-in and browse-in privileges, that allow multiple users to access WebDB components. The build-in privilege, which is like the ANY option in a system privilege, allows a WebDB user or role to create user interface components in another user's schema. The browse-in privilege allows a user or role to access the user interface components owned by another schema. These privileges are implemented as rows in WebDB's data dictionary tables.
WebDB roles are database roles. These roles are used in conjunction with the build-in and browse-in privileges; when you assign a WebDB user a role, you not only assign a corresponding database role but also a WebDB-specific privilege. For this reason, you should assign roles using the WebDB interface, and not through OEM or SQL*Plus.
Figure 3.2 shows WebDB's "Create a New User" section of the "User Manager" option.
WebDB maintains both application and database logs to help you tune performance. The application logs contain information about the frequency with which WebDB is used. The database logs contain traditional database metrics, such as memory usage and the status of the redo logs.
Each kind of log is used to create a number of reports. Reports generated by the application log include application component response times, user requests per component, and user requests by hour. Reports generated by the database log include database parameters, redo logs, and rollback segments. Most of these reports can have several different formats, such as a chart or a table; you can even download them directly into a spreadsheet and create your own graphs! Figure 3.3 shows a chart of the size of a database's datafiles.
WebDB applications consist of database objects, like tables and views, and user interface components, like forms and reports. For example, a simple application might consist of a database table, a data entry form to populate the table, and a report that queries the table and displays the results. Users use a web browser to access the form or report.
To simplify the development process, WebDB has a number of wizards that are analogous to the wizards in Microsoft Office. A wizard is a series of screens that create an application component based on information you provide. There are three kinds of wizards: database object wizards, user interface (UI) component wizards, and shared component wizards. In addition, since WebDB includes the PL/SQL toolkit, you can build your own custom components and applications. The following sections describe these wizard types.
WebDB's "Object Wizards" option guides you through the steps needed to create a database object. For example, the Table wizard option takes you through four screens. On the first screen, you select the schema in which to create the table and table name. On the second screen, which is reminiscent of Oracle Schema Manager (a GUI management tool included with Oracle Enterprise Manager that simplifies schema administration), you define each of the table's columns, providing such information as a column name and datatype. On the third screen, you define the table's storage parameters, such as its tablespace and number of initial extents. The final screen allows you to confirm the table definition; clicking the "Ok" button creates the table.
WebDB has the following database object wizards:
Template for the CREATE FUNCTION statement. A function is a group of instructions stored directly in the database. It returns a single value to the program that calls it. The function definition can contain parameters.
Template for the CREATE INDEX statement. An index is used to improve performance in SQL queries.
Template for the CREATE PACKAGE statement. A package is a group of variables, procedures, and functions stored directly in the database. A package has two parts: a specification and a body. The specification lists the package's public variables, procedures, and functions. The body contains the actual program code for each procedure. Packages are used to mimic many of the design concepts found in object-oriented languages, such as overloading and encapsulation.
TIP: The database objects you create in WebDB are normal database objects; the wizard is simply filling in the different pieces of a SQL statement for you. If you're developing an application you can bypass the wizard entirely and create objects in SQL*Plus, Enterprise Manager, or any other tool. The "Object Wizards" option comes in handy, though, when you're off-site and can't get a SQL*Net connection.
Template for the CREATE PROCEDURE statement. A procedure is a group of instructions stored directly in the database that performs a specific task. The procedure definition can include parameters.
Template for the CREATE SEQUENCE statement. A sequence is a counter that is often used to provide primary key values.
Template for the CREATE SYNONYM statement. A synonym is used to provide an alias to another database object, usually to provide location transparency.
Template for the CREATE TABLE statement. A table is a collection of columns and rows that store data in the database.
Template for the CREATE TRIGGER statement. A trigger is a group of instructions that execute in response to a specific table event, such as an insert or a delete. Triggers are often used to set a row's primary key (based on a sequence) or update its timestamp.
Template for the CREATE TYPE statement. A type is a data structure much like a record stored directly in the Oracle database that provides object-like capabilities inside the database.
Template for the CREATE VIEW statement. A view is a stored SQL query that acts like a table. Views are used to simplify complex queries for end user reporting tools. They are also used for security purposes to restrict access to a subset of columns in an important table.
Figure 3.4 shows the table wizard.
Once you've created a database object, you can use WebDB's user interface (UI) wizards to create user interface components such as data entry forms or reports. After you've stepped through a series of screens in which you define the components' properties, the wizard creates a PL/SQL stored package to implement the user interface.
There are eight user interface wizards:
Creates an HTML-based calendar using information stored in a table. You could use the calendar wizard to create an HTML-based "to-do" list displayed in a format that looks like a monthly calendar.
Creates a bar chart based on information in either a database table or a SQL query.
Allows you to create standard HTML pages that can contain SQL and PL/SQL commands embedded inside a special <ORACLE> tag, as in:
<ORACLE>SELECT * FROM emp</ORACLE>
Creates several different kinds of HTML forms, such as forms that execute stored procedures, forms that perform standard table operations like inserts or updates, master detail forms, and Query by Example (QBE) forms.
Creates a split screen containing two HTML frames. The first frame (the "navigation" frame) contains a list of hyperlinks based on a query you provide. Clicking one of these links will display the page in the second frame (the "target").
Creates a drill-down based on information on the parent-child relationships in a recursively defined table.
Allows you to create parent-child menu structures. The menu wizard is very similar to the hierarchy wizard.
Creates a report based on a table or SQL query. You can display reports as HTML pages or ASCII files, or you can download them directly into an Excel spreadsheet. Figure 3.5 shows the report wizard in action.
After you've created your interfaces, you can use the shared component wizards to create a library of reusable components that ensure a consistent design across the application. A cardinal rule of interface design is that there should be a consistent look and feel across an application's screens and reports. Unfortunately, this goal is often very difficult to reach, especially for systems that have many developers. For example, suppose you and several other programmers are building a document management system. Each screen in the system must have a Search option that the user activates by clicking an icon. You, as an avid outdoors person, would like to use a pair of binoculars as the icon. Another person wants to use a magnifying glass, and yet another wants to use a picture of a blue-tick hound dog. How do you make sure everyone does the same thing?
Rather than leaving these sorts of choices to the vagaries of each developer, many companies use a library of standardized components. Each component has a name, such as SEARCH_BUTTON, that represents a real item, such as a picture of bloodhound. When a developer needs to put a search button on a form, she uses the named component, rather than a real icon. This guarantees a reasonable degree of consistency across a wide range of applications.
To simplify the often tedious process of standardization, WebDB allows you to create reusable application elements called shared component libraries. The basic idea of a shared component is that it associates a logical name with a physical object. Developers can include these shared components in their UI components. There are seven shared component libraries:
A named Red, Green, Blue (RGB; a common way to specify a color) color combination. For example, you can associate the RGB color #9F9F5F with the name Khaki.
A named font. For example, you might want to associate the Arial font with the name INVOICE_FONT to enforce a consistent look across reports in an invoicing system.
A named JavaScript script. You can use this component to build a library of client-side scripts that developers can reuse in their applications. For example, you might create a library of field validation scripts.
A named hyperlink. You can use this component to make sure that hyperlinks leading to the same location all use a consistent name. For example, you might want to create a named hyperlink to an online help system to guarantee that every application uses a consistent link name. Additionally, you use links to bind various WebDB components together into a single system. For example, in an employee data entry component of a human resources system, you might want to create links to employee educational history, salary history, and W2 information. Finally, you can use links to create hooks to PL/SQL procedures you develop with the PL/SQL toolkit.
A named List of Values (LOV). You can use this component to create a variety of styles of lists (such as radio buttons, select lists, and pop-up windows) based on an underlying database table, view, or SQL query. For example, you might want to create a list called ACTIVE_EMPLOYEES to allow a user to select from a predefined list of valid employees.
A named page layout template. You can use this component to create a standard page format for each page in an application.
Figure 3.6 shows the LOV wizard being used to create a pop-up List of Values.
While application generators are wonderful time savers, they almost always get just the first 80% of an application's requirements, leaving you to code the remaining 20% by hand. And what if you want to write an application for which WebDB doesn't have an associated wizard? For example, there is no shopping cart wizard, so if you want to create an electronic storefront application, you must write it from scratch.
WebDB includes the PL/SQL toolkit software development kit (SDK) to allow you to write your own web applications. This is the same set of packages that comes with OAS, so anything you develop in WebDB will also work in OAS, and vice versa. Subsequent chapters detail how to use these systems to develop your own applications.
WebDB's third major function is creating content-driven web sites. The advantage of content-driven sites deriving from database information is that you can completely change and rearrange the sites and their content with a few mouse clicks. Filesystem-based sites, on the other hand, are much more difficult to modify because they have a more rigid structure. The most interesting thing about WebDB sites is that they not only allow users to view information already in the database, but also let users add their own content to the site. For example, a user could upload an Excel spreadsheet to his personal site and make it available without the webmaster's help.
Consistent with WebDB's "100% in the browser, 100% in the database" philosophy, every feature is accessed with a browser and all content is stored in an Oracle database (with a few exceptions, such as images, which can also be stored as files). WebDB uses this information to create a hierarchical view of the entire site, making user uploads available to users with the appropriate privileges. In this section, we'll look at three of WebDB's features for creating content-driven web sites: its options allowing users to publish their own content, its features for organizing and managing the content, and its methods for controlling who can access the content.
WebDB's most unique and innovative feature is that it allows users to add their own web content directly from their browsers, effectively eliminating the bottleneck of requiring the webmaster to manually add new content. Each user is assigned his or her own personal web folder to which he or she can add various kinds of content (depending on the privileges granted by the site administrator). Additionally, if given the proper privilege, users can contribute content to folders owned by other users.
Users add content from the WebDB dashboard. The dashboard is a set of options available at the top of every page that allows users to (among other things) add an item, create a new subfolder, change a folder's properties (such as its name and description), and allow other WebDB users to access a folder's content. Users can contribute the following items:
A standard file, such as a Word or Excel document, that is uploaded to a database table. WebDB is integrated into the interMedia Text cartridge, which automatically indexes it for later searching.
A call to a PL/SQL procedure; for example, a call to one of the custom PL/SQL applications we'll develop later in this book.
A plain text message that displays on the page. A user can use this item to quickly and easily post messages for the site.
A generic hyperlink to another site. For example, a user could enter a list of favorite sites on the Web.
Creates a hyperlink to a WebDB user interface component. For example, a user could create a link to a report. Optionally, you can also configure WebDB to place the component on the page, rather than just a hyperlink.
Figure 3.7 shows the screen used to upload a file to a WebDB site.
Once users have added content to their site, you can manage it as you would any other information stored in an Oracle database. For example, you can create a backup schedule, see how much space each user consumes, and audit changes to the site's content. In short, you can apply all your organization's hard-won data management skill to Internet content.
In addition, you can organize site content so that it's easy for users to find. WebDB has three basic ways to do this:
As we've already seen, users can have individual folders to which they can upload content. You can also create project- or application-specific folders. For example, WebDB includes a demo application called "The Traveler," an example of a database-driven travel site. Behind the scenes, WebDB's folders are really just rows in a table; uploaded items include the primary key for the row in the folder table. The folder table is defined recursively (i.e., parent/child rows) to represent subfolders.
Each item is assigned to a single category that identifies its general type. For example, the "The Traveler" demo uses travel-related categories, such as Flights, Lodging, Restaurants, and Travel packages. These categories let users search for specific kinds of items, such as "Restaurants that serve seafood."
Users can also assign multiple perspectives to further categorize an item. Unlike a category, which defines what an item is, a perspective defines who might be interested in it. For example, "The Traveler" demo might have the perspectives Food Connoisseurs, Outdoor Enthusiasts, or History Buffs. Users can then search for items of interest to them, for example, "Restaurants that serve seafood of gourmet quality."
A user can rearrange a site on the fly by changing each item's categories and perspectives. Figure 3.8 shows WebDB's Site Map, a hierarchical list of all folders.
The WebDB site administrator assigns access privileges to information in the site. The administrator can manipulate the following WebDB settings:
Allows the site administrator to define named groups of users, analogous to roles, that are used to easily assign web content to multiple users. Groups, however, are modeled in database tables and are not actual database roles.
Allows the site administrator to add and manage WebDB site users. This is pretty much the same set of privileges used in the Oracle database, with some additional privileges for managing content. These include the ability to administer a site, add news items, and change the site's look and feel.
|  | |
| 2.5 Database Security Review |  | 3.2 WebDB Architecture |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.