A Sample Application

Oracle8i supports LOBs, large objects which can hold up to 4 gigabytes of binary or character data. What does this mean for you, the application developer?

Consider the following hypothetical application:

The Multimedia Content-Collection System

Multimedia data is used in an increasing variety of media channels -- film, television, webpages, and CD-ROM being the most prevalent. The media experiences having to do with these different channels vary in many respects (interactivity, physical environment, the structure of information, to name a few). Yet despite these differences, there is often considerable similarity in the multimedia authoring process, especially with regard to assembling content.

Figure 8-1 The Multimedia Authoring Process

For instance, a television station that creates complex documentaries, an advertising agency that produces advertisements for television, and a software production house that specializes in interactive games for the web could all make good use of a database management system for collecting and organizing the multimedia data. Presumably, they each have sophisticated editing software for composing these elements into their specific products, but the complexity of such projects creates a need for a pre-composition application for organizing the multimedia elements into appropriate groups.

Taking our lead from movie-making, our hypothetical application for collecting content uses the clip as its basic unit of organization. Any clip is able to include one or more of the following media types:

• Character text (e.g.,storyboard, transcript, subtitles,),

• Images (e.g., photographs, video frames),

• Line drawings (e.g., maps),

• Audio (e.g., sound-effects, music, interviews)

Since this is a pre-editing application, the precise relationship of elements within a clip (such as the synchronization of voice-over audio with a photograph) and between clips (such as the sequence of clips) is not defined.

The application should allow multiple editors working simultaneously to store, retrieve and manipulate the different kinds of multimedia data. We assume that some material is gathered from in-house databases. At the same time, it should also be possible to purchase and download data from professional services.

Note: The Example is Only An Example

Our mission in this chapter is not to create this real-life application, but to describe everything you need to know about working with LOBs. Consequently, we only implement the application sufficiently to demonstrate the technology. For example, we deal with only a limited number of multimedia types. We make no attempt to create the client-side applications for manipulating the LOBs. And we do not deal with deployment issues such as, the fact that you should implement disk striping of LOB files, if possible, for best performance.

See Figure 8-2, "Schema Plan for Table MULTIMEDIA_TAB".

Applying an Object-Relational Design to the Application

Figure 8-2 Schema Plan for Table MULTIMEDIA_TAB

Structure of Multimedia_tab Table

Figure 8-3 Schema Plan for Table MULTIMEDIA_TAB

Figure 8-3, "Schema Plan for Table MULTIMEDIA_TAB" shows table MULTIMEDIA_TAB's structure. Its columns are described below:

• CLIP_ID: Every row (clip object) must have a number which identifies the clip. This number is generated by the Oracle number SEQUENCER as a matter of convenience, and has nothing to do with the eventual ordering of the clip.

• STORY: The application design requires that every clip must also have text, that is a storyboard, that describes the clip. Since we do not wish to limit the length of this text, or restrict its format, we use a CLOB datatype.

• FLSUB: Subtitles have many uses -- for closed-captioning, as titles, as overlays that draw attention, and so on. A full-fledged application would have columns for each of these kinds of data but we are considering only the specialized case of a foreign language subtitle, for which we use the NCLOB datatype.

• PHOTO: Photographs are clearly a staple of multimedia products. We assume there is a library of photographs stored in the PhotoLib_tab archive. Since a large database of this kind would be stored on tertiary storage that was periodically updated, the column for photographs makes use of the BFILE datatype.

• FRAME: It is often necessary to extract elements from dynamic media sources for further processing For instance, VRML game-builders and animation cartoonists are often interested in individual cells. Our application takes up the need to subject film/video to frame-by-frame analysis such as was performed on the film of the Kennedy assassination. While it is assumed that the source is on persistent storage, our application allows for an individual frame to be stored as a BLOB.

• SOUND: A BLOB column for sound-effects.

• VOICED_REF: This column allows for a reference to a specific row in a table which must be of the type Voiced_typ. In our application, this is a reference to a row in the table VoiceOver_tab whose purpose is to store audio recordings for use as voice-over commentaries. For instance, these might be readings by actors of words spoken or written by people for whom no audio recording can be made, perhaps because they are no longer alive, or because they spoke or wrote in a foreign language.

  This structure offers the application builder a number of different strategies from those discussed thus far. Instead of loading material into the row from an archival source, an application can simply reference the data. This means that the same data can be referenced from other tables within the application, or by other applications. The single stipulation is that the reference can only be to tables of the same type. Put another way: the reference, Voiced_ref, can refer to row objects in any table which conforms to the type, Voiced_typ.

  Note that Voiced_typ combines the use of two LOB datatypes:

  • CLOB to store the script which the actor reads

  • BFILE for the audio recordings.

  Figure 8-4, "Schema Design for Inclusion of VOICED_REF Reference" shows VOICED_REF column referencing the Voiced_typ row in table VoiceOver_tab.

Figure 8-4 Schema Design for Inclusion of VOICED_REF Reference

• INSEG_NTAB: While it is not possible to store a Varray of LOBs, application builders can store a variable number of multimedia elements in a single row using nested tables. In our application, nested table InSeg_ntab of predefined type InSeg_typ can be used to store zero, one, or many interview segments in a given clip. So, for instance, a hypothetical user could use this facility to collect together one or more interview segments having to do with the same theme that occurred at different times.

  See Figure 8-5, "Schema Design for Inclusion of Nested Table INSEG_NTAB".

  In this case, nested table, interviewsegments_ntab, makes use of the following two LOB datatypes:

  • BFILE to store the audio recording of the interview

  • CLOB for transcript.

  Since such segments might be of great length, it is important to keep in mind that LOBs cannot be more than 4 gigabytes.

Figure 8-5 Schema Design for Inclusion of Nested Table INSEG_NTAB

• MUSIC: The ability to handle music must be one of the basic requirements of any multimedia database management system. In this case, the BFILE datatype is used to store the audio as an operating system file.

• MAP_OBJ: Multimedia applications must be be able to handle many different kinds of line art -- cartoons, diagrams, and fine art, to name a few. In our application, provision is made for a clip to contain a map as a column object, MAP_OBJ, of the object type MAP_TYP. In this case, the object is contained by value, being embedded in the row.

  As defined in our application, MAP_TYP has only one LOB in its structure -- a BLOB for the drawing itself. However, as in the case of the types underlying REFs and nested tables, there is no restriction on the number of LOBs that an object type may contain. See Figure 8-6, "Schema Design for Inclusion of Column Object MAP_OBJ".

Figure 8-6 Schema Design for Inclusion of Column Object MAP_OBJ