The Oracle database handles multimedia information

The ORACLE tutorial you are looking at is :Oracle database processing multimedia information.

Quote:

For a long time, multimedia information in the computer is stored in the form of files, by the operating system management, but with the development of computer network, distributed computing, the efficient management of multimedia information, access, query has become an urgent need. But the relational database has the powerful data management ability. The combination of the two aspects, the multimedia database came into being. This article will summarize the methods, tools, features and basic principles of Oracle's multimedia information processing.

But we can't help but regret to see that the existing database is very limited in its ability to manipulate some multimedia data. Performance tuning for multimedia databases is now a new problem. Nevertheless, the multimedia database still has produced the broad application value and the prospect.
Of course, it is impossible to cover all aspects in this paper. Please refer to the relevant manual of Oracle for details of multimedia information processing.

1. Overview of application prospects

With the improvement of computer processing capacity, multimedia has already been integrated into the computer, if the lack of multimedia, the lack of a variety of colorful images, audio, video, it is difficult to imagine that the computer will now enter thousands of households. For a long time, the multimedia information is stored in the form of a file on your computer, managed by the operating system, but with the computer network, the development of distributed computing, the simple file type of management have been overwhelmed, and efficient management of multimedia information, access, query has become an urgent need. But the relational database has the powerful data management ability. The two aspects are closely combined, thus multimedia database comes into being. It can be predicted that with the enhancement of multimedia processing requirements, the application of multimedia database will be more and more extensive.

As a famous database manufacturer, Oracle is in the forefront of multimedia information processing. This article will summarize the methods, tools, features and basic principles of Oracle's multimedia information processing. Hope to enlighten the reader. Of course, there are many other relational databases, although the development tools, programming interface statements are different, but in the data types, processing methods are relatively similar. So let's skip it here.

2. Introduction to multimedia data types

In relational databases, large objects such as multimedia information are accessed by lob type fields. In Oracle8i, this standard was formally introduced to meet the needs of multimedia large object processing. In the Oracle database, there are several types of lob data:

--------------------------------------------

Lob type description

Clob: similar to long of Oracle7, clob can store single-byte data

Nclob: Nclob stores multi-byte national character set data of fixed width

Blob: similar to the long raw type in Oracle7. You can store unstructured binary data. Oracle8 does not interpret this data, but simply stores and retrieves it in its original form.

Bfile: Bfile allows read-only access to large binary files stored outside the Oracle database. Unlike the other three types of lob data, the bfile type data is stored in a single file that is not maintained by Oracle.

Features:

1. In Oracle7, the corresponding long or long raw fields have a limit of 2g, while the limit for lob is 4g.

2.lob can be manipulated using the call interface OCI or by pl/sql using the dbms_lob package.

3. Unlike type long, which has a maximum of one field per table, lob can have multiple fields and take advantage of triggers.

4.lob data processing can obtain the same properties as other data.

5. The storage of lob is special. Instead of being stored in the same database table with other data, it can be stored in a separate table space with a locator pointing to the actual lob data.

[NextPage]

3. Example of multimedia large object access routine

Follow these steps to complete each operation:

(1) establish a database table first:

create table lobdemo(key NUMBER KEY,clob_col CLOB,blob_col BLOB,bifle_col BFILE);

This database stores one code in the first column and three columns in the lob type data.

(2) after the table is built, insert a piece of data into the table:

insert into lobdemo(key,clob_col,blob_col,bfile_col) values (10,'abcdefghijklmnopqrsatuvwxyzasdffasfsdafsdafsdfadfsadfsdfsdfdsdsffds',empty_blob(),null);

In this data, we insert a string in the second column, which is formatted as clob data for storage. In the second column, we use the empty_blob () function to initialize a locator, which can then be obtained for operations such as select, update, etc. The third column is null, which does not get a locator, which is different from the empty_blob () function.

(3) query the data inserted into the table

select blob_col from boldemo where key=20;

Note: this 1sql statement returns a locator (locator), not the actual data itself.

(4) modify the data in the database

update lobdemo SET blob_col='aedevbagddgagdfdfasasdfdsa' where key=10;

From these sql statements, we see the basic approach to lob type data processing, which is similar to other types of data processing. However, the particularity of lob data processing in sql statement lies in the following aspects:

(1) processing of bfile documents:

The bfile type is different from clob and blob. The actual data files are stored outside of the operating system: so there are two features: 1. There is no transactional control 2.bfile is read-only and cannot be modified with dbms_lob or oracl8 oci. Let's look at the steps for bfile:

< 1 > In order to access external files, the server needs to know where the files are in the operating system. Let's create a directory:
create DIRECTORY utils AS '/home/utils';
utils represents the logical name of the directory, and '/home/utils' is the actual directory.

< 2 > Insert 1 row of data using the bfilename function:

insert into lobdemo(key,bfile_col) values (-1,biflename('utils','file1');

The first argument to the bfilename function is the logical directory name, and the second argument is the file name. Note: this 1 row is inserted in a point/home/utils/file1 lob locator, not the file itself.

(2) use dbms_lob package for lob data operation

sql can manipulate only the entire lob, not the slices. The dbms_lob package liberates this limitation by providing operations on data slices in the lob data. This package contains more than 10 routines, and you can refer to the Oracle pl/sql manual. This article introduces only three functions: fileopen () to open an operating system file; The loadfromfile () routine populates an operating system file with the incoming and outgoing target lob field. This function is useful because many of the lob contents are not described in the sql statement. The getlength function calculates the length of the file. See the use of these three functions in a stored procedure below:

create or replace procedure FileExec(
p_Directory in VARCHAR2,
p_Filename IN VARCHAR2
) AS
v_FileLocator BFILE;
v_CLOBLocator CLOB;
BEGIN
-- initializes a locator for update
SELECT clob_col
INTO v_CLOBLocator
FROM lobdemo
WHERE key = 1
FOR UPDATE;

-- initializes an BFILE locator for the read file

v

[1] [2] next page

The ORACLE tutorial you are looking at is :Oracle database processing multimedia information. _FileLocator: = BFILEOPEN (p_Derectory p_Filename);

-- open an operating system file with fileopen of the dbms_lob package

DBMS_LOB. FILEOPEN (v_FileLocator, DBMS_LOB FILE_READONLY);

-- load the entire operating system file into lob

DBMS_LOB. LOADFROMFILE (v_CLOBLocator, v_FileLocator, DBMS_LOB GETLENGTH (v_FileLocator));

END FileExec;

4. Introduction to the Oracle InterMedia tool

interMedia is an integrated part of Oracle which extends the multimedia function of Oracle8i. It makes Oracle more convenient to manage image, audio, video, text and other information. It enhances the reliability and availability of Oracle in the management of multimedia data in internet, e-commerce and other fields. It includes parts of image, audio, video3. interMedia use object type, similar to JAVA or C + +, to describe image, audio, video type data, Oracle in this three kinds of object types defined in many ways to manipulate these data, such as for image type data, you can easily to graphics format conversion, compression, copy, intercept any one part of the graphics, etc., and these is unmatched by ordinary LOB type data, ordinary sql statement also unable to complete the operation.

The development of Internet highlights the value of interMedia in the application of WEB. Most of the existing WEB applications store multimedia information such as image,audio and video in the file system. Its advantage is simple operation and fast speed, but its disadvantage is complex management, especially when managing a large number of image, it is limited by the number of directories. If you save image in a database, it will be maintained by the database and backed up by the database. This will simplify administration.

Several multimedia object types are defined in Intermedia. For example, ORDAUDIO is one of them. It provides some manipulation functions for the data of audio object types. Let's take a look at an example in which we define a song object, create a table of a song object, and insert and query the table.

(1) define a song object:

CREATE TYPE songObject as OBJECT (
songId VARCHAR2(20), -- song no
title VARCHAR2(4000), -- problem
artist VARCHAR2(4000), -- writer
awards VARCHAR2(4000), -- award
timePeriod VARCHAR2(20), -- date and time
txtinroduction CLOB, -- introduction
audioSource ORDSYS. ORDAUDIO -- audio data, this field is defined by the ORDAUDIO object.
);

(2) create a table named songtable:

CREATE TABLE of songObject (UNIQUE (songId), songId NOT NULL);

(3) insert 1 row of data into SongsTable table

INSERT INTO SongsTable VALUES ('00',
'Under Pressure',
'Queen',
'no awards',
'80-90,
In 243,
NULL,
EMPTY_CLOB (),
ORDSYS. ORDAudio (NULL,
ORDSYS. ORDSource (EMPTY_BLOB (), NULL NULL, NULL, NULL, NULL),
NULL, NULL, EMPTY_CLOB(), NULL, NULL, NULL, NULL, NULL, NULL));
Note:. The EMPTY_CLOB, EMPTY_BLOB() function simply initializes an lob data locator and does not store the actual data.

(4) load 1 row of data into SongsTable table:

DECLARE
audioObj ORDSYS. ORDAUDIO;
ctx RAW (4000) : = NULL;
BEGIN

-- gets the audioSource field locator

SELECT S. audioSource INTO audioObj

FROM SongsTable S
WHERE S. songId = '00'
FOR UPDATE;

-- the following four functions are member functions of the ORDAUDIO object. For details, please refer to the Oracle development manual

audioObj. setSource (' FILE ', 'AUDDIR', 'UnderPressure. au'); -- setting file

audioObj. setMimeType (' audio/basic '); -- type setting

audioObj. import (ctx); - loading ctx

audioObj. setProperties (ctx); -- setting properties

Load the song object into the database table

UPDATE SongsTable S
SET S audioSource = audioObj
WHERE S. songId = '00';

COMMIT;

END;

[NextPage]

5. Technical difficulties and prospects of multimedia database

The efficient management of multimedia data, as it were, is the inevitable requirement of IT technology development, with multimedia information needs further strengthen, multimedia database technology will have greater development, we can see that from Oracle database in data type, data management, or on the development and management tools, now the mainstream of the relationship between database already has a strong ability of multimedia information management. Unfortunately, however, the ability to manipulate some multimedia data (such as querying multimedia information) is limited.

One problem is the special operation implementation of multimedia data, because lob data is a big object in base 2, which cannot be calculated simply according to the operator of data like 1. For example, look up the audio in a table that is similar to a song. So for this' similar 'operation, it won't be supported by traditional operators, and it will involve very complex algorithms. Fortunately, Oracle9i Intermedia already has the function of query and match of multimedia data. For example, it can query similar images from texture (texture), shape, color, color component and other elements, and the weight of each element can be set during the query. The test results are not satisfactory. However, the query of audio, video and other types of data is still not ideal.

In addition, how to describe the multimedia information, how to search according to your description of the multimedia information, is also a difficult problem, such as this query task: query the dark color and forest landscape photos, which is based on descriptive information query example. This will require some kind of industry standard and technology implementation. In short, how to realize and optimize the special operation for multimedia data (such as similar query, etc.) will be a hot issue in the future research and application of multimedia database.

Also is one important problem in multimedia database performance tuning, the original tuning methods such as index optimization still apply, but now have a new problems: multimedia data is huge, so to the operation of the multimedia information (especially the retrieval) costly, so how to reduce the cost, shorten the operation time, is an important subject.

On 1 page

Previous page [1] [2]