3.1.1.1 Browse database objects

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.

Figure 3.1: Browsing database objects with WebDB

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.

3.1.1.2 Administer WebDB and database security

WebDB security is built with Oracle's standard security model. There are three security components:

Users

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.

Privileges

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.

Roles

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.

Figure 3.2: Creating a user with WebDB

3.1.1.3 Monitor database and application performance

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.

Figure 3.3: A WebDB chart

3.1.2.1 Building database objects

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:

Function wizard

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.

Index wizard

Template for the CREATE INDEX statement. An index is used to improve performance in SQL queries.

Package wizard

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.

Procedure wizard

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.

Sequence wizard

Template for the CREATE SEQUENCE statement. A sequence is a counter that is often used to provide primary key values.

Synonym wizard

Template for the CREATE SYNONYM statement. A synonym is used to provide an alias to another database object, usually to provide location transparency.

Table wizard

Template for the CREATE TABLE statement. A table is a collection of columns and rows that store data in the database.

Trigger wizard

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.

Type wizard

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.

View wizard

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.

Figure 3.4: Creating a table with WebDB

3.1.2.2 Building user interface components

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:

Calendar wizard

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.

Chart wizard

Creates a bar chart based on information in either a database table or a SQL query.

Dynamic page wizard

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>

Form wizard

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.

Frame driver wizard

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").

Hierarchy wizard

Creates a drill-down based on information on the parent-child relationships in a recursively defined table.

Menu wizard

Allows you to create parent-child menu structures. The menu wizard is very similar to the hierarchy wizard.

Report 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.

Figure 3.5: Formatting the columns of a WebDB report

3.1.2.3 Building shared components

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:

Colors library

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.

Fonts library

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.

Images library

A named image.

JavaScripts library

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.

Links library

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.

Lists of Values library

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.

UI templates library

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.

Figure 3.6: Creating a list of values in WebDB

3.1.2.4 Building custom components

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.

3.1.3.1 User-uploaded 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:

File

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.

Folderlink

A hyperlink to another WebDB folder.

Imagemap

A standard imagemap.

PL/SQL call

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.

Text item

A plain text message that displays on the page. A user can use this item to quickly and easily post messages for the site.

URL

A generic hyperlink to another site. For example, a user could enter a list of favorite sites on the Web.

WebDB component

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.

Figure 3.7: Uploading a file to a WebDB site

3.1.3.2 Managing content

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:

Folders

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.

Categories

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."

Perspectives

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.

Figure 3.8: The Site Map, a list of all folders in WebDB

3.1.3.3 Controlling access to content

The WebDB site administrator assigns access privileges to information in the site. The administrator can manipulate the following WebDB settings:

Group

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.

Privilege

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.