Perl数据库编程权威指南

需积分: 10 3 浏览量更新于2024-07-25 收藏 1.39MB PDF 举报

"Programming_the_Perl_DBI.pdf" 《Programming the Perl DBI》是Alligator Descartes和Tim Bunce合著的一本关于Perl数据库接口(DBI)编程的专业书籍，第一版发布于2000年。这本书对于Perl语言中的数据库编程而言是权威性的参考资料。Alligator Descartes是DBI社区中最活跃的成员之一，而Tim Bunce则是DBI的创始人。书中深入探讨了DBI的架构，指导读者如何编写基于DBI的程序，并详细解析了DBI的微妙之处以及每个特定数据库驱动(DBD)的独特性。这使得开发者能够更好地理解和利用Perl进行数据库操作。在目录中，我们可以看到本书分为多个章节，逐步引导读者了解数据库编程的世界： 1. 引言：从大型机到工作站，讲述了数据库技术的发展历程，以及Perl语言在其中的角色。 2. 基础非DBI数据库：讨论了存储管理器、数据层、查询语言和数据函数等概念，通过实例展示了非关系型数据库如站立石(Sample Database)、扁平文件数据库以及复杂数据存储方法。还涵盖了并发数据库访问和锁定，以及DBM文件和伯克利数据库管理器(Berkeley DB Manager)的使用，以及MLDBM模块的应用。 3. SQL和关系型数据库：深入讲解关系数据库方法论，包括数据类型、NULL值处理，以及如何查询和修改表内的数据。这部分内容对于理解SQL的基础和高级特性至关重要。通过这些章节，读者不仅可以学习到DBI的基本用法，还能掌握如何在实际项目中高效地使用Perl进行数据库操作，包括处理并发问题、优化查询性能以及与其他数据库系统交互。书中的实例和详细解释将帮助开发者成为Perl DBI领域的专家。《Programming the Perl DBI》是任何想要在Perl环境中进行数据库开发的程序员不可或缺的工具书，无论你是初学者还是经验丰富的开发者，都能从中受益匪浅。

Programming the Perl DBI

age 11

Updating

Once data is stored within a database, it is not necessarily immutable. It can be changed if

required. For example, in a database storing information on products that can be purchased,

the pricing information for each product may change over time. The operation of changing a

value of existing data within the database is known as updating. It is important to note that

this operation doesn't add items to or remove items from the database; rather, it just changes

existing items.

[3]

Logically, that is. Physically, the updates may be implemented as deletes and inserts.

Deleting

The final core operation that you generally want to perform on data is to delete any old or

redundant data from your database. This operation will completely remove the items from

the database, again using the storage managers to excise the data from the data files. Once

data has been deleted, it cannot be recovered or replaced except by reinserting the data into

the database.

[4]

Unless you are using transactions to control your data. More about that in Chapter 6.

These operations are quite often referred to by the acronym C.R.U.D. (Create, Read, Update, Delete).

This book discusses these topics in a slightly different order primarily because we feel that most

readers, at least initially, will be extracting data from existing databases rather than creating new

databases in which to store data.

2.3 Standing Stones and the Sample Database

Our small example databases throughout this chapter will contain information on megalithic sites

within the UK. A more complex version of this database is used in the following chapters.

The main pieces of information that we wish to store about megaliths

[5]

are the name of the site, the

location of the site within the UK, a unique map reference for the site, the type of megalithic setting

the site is (e.g., a stone circle or standing stone), and a description of what the site looks like.

[5]

Storing anything on a megalith is in direct violation of the principles set forth in Appendix C. In case you

missed it, we introduced megaliths in Chapter 1.

For example, we might wish to store the following information about Stonehenge in our database:

Name:

Stonehenge

Location:

Wiltshire, England

Map Reference:

SU 123 400

Type:

Stone Circle and Henge

Description:

The most famous megalithic site in the world, comprised of an earthen bank, or henge, and

several concentric rings of massive standing stones formed into trilithons.

With this simple database, we can retrieve all sorts of different pieces of information, such as, ''tell me

of all the megalithic sites in Wiltshire,'' or ''tell me about all the standing stones in Orkney,'' and so on.

Now let's discuss the simplest form of database that you might wish to use: the flat-file database.

Programming the Perl DBI

age 12

2.4 Flat-File Databases

The simplest type of database that we can create and manipulate is the old standby, the flat-file

database. This database is essentially a file, or group of files, that contains data in a known and

standard format that a program scans for the requested information. Modifications to the data are

usually done by updating an in-memory copy of the data held in the file, or files, then writing the

entire set of data back out to disk. Flat-file databases are typically ASCII text files containing one

record of information per line. The line termination serves as the record delimiter.

In this section we'll be examining the two main types of flat-file database: files that separate fields

with a delimiter character, and files that allocate a fixed length to each field. We'll discuss the pros

and cons of each type of data file and give you some example code for manipulating them.

The most common format used for flat-file databases is probably the delimited file in which each field

is separated by a delimiting character. And possibly the most common of these delimited formats is

the comma-separated values (CSV) file, in which fields are separated from one another by commas.

This format is understood by many common programs, such as Microsoft Access and spreadsheet

programs. As such, it is an excellent base-level and portable format useful for sharing data between

applications.

[6]

More excitingly, a DBI driver called DBD::CSV exists that allows you to write SQL code to manipulate a flat

file containing CSV data.

Other popular delimiting characters are the colon (

: ), the tab, and the pipe symbol ( | ). The Unix

/etc/passwd file is a good example of a delimited file with each record being separated by a colon.

Figure 2.1 shows a single record from an /etc/passwd file.

Figure 2.1, The /etc/passwd file record format

2.4.1 Querying Data

Since delimited files are a very low-level form of storage manager, any manipulations that we wish to

perform on the data must be done using operating system functions and low-level query logic, such as

basic string comparisons. The following program illustrates how we can open a data file containing

colon-separated records of megalith data, search for a given site, and return the data if found:

#!/usr/bin/perl -w

# ch02/scanmegadata/scanmegadata: Scans the given megalith data file for

# a given site. Uses colon-separated data.

### Check the user has supplied an argument for

### 1) The name of the file containing the data

### 2) The name of the site to search for

die "Usage: scanmegadata <data file> <site name>\n"

unless @ARGV == 2;

my $megalithFile = $ARGV[0];

my $siteName = $ARGV[1];

### Open the data file for reading, and die upon failure

open MEGADATA, "<$megalithFile"

or die "Can't open $megalithFile: $!\n";

### Declare our row field variables

my ( $name, $location, $mapref, $type, $description );

### Declare our 'record found' flag

my $found;

Programming the Perl DBI

age 13

### Scan through all the entries for the desired site

while ( <MEGADATA> ) {

### Remove the newline that acts as a record delimiter

chop;

### Break up the record data into separate fields

( $name, $location, $mapref, $type, $description ) =

split( /:/, $_ );

### Test the sitename against the record's name

if ( $name eq $siteName ) {

$found = $.; # $. holds current line number in file

last;

}

### If we did find the site we wanted, print it out

if ( $found ) {

print "Located site: $name on line $found\n\n";

print "Information on $name ( $type )\n";

print "===============",

( "=" x ( length($name) + length($type) + 5 ) ), "\n";

print "Location: $location\n";

print "Map Reference: $mapref\n";

print "Description: $description\n";

}

### Close the megalith data file

close MEGADATA;

exit;

For example, running that program with a file containing a record in the following format:

[7]

In this example, and some others that follow, the single line has been split over two lines just to fit on the

printed page.

Stonehenge:Wiltshire:SU 123 400:Stone Circle and Henge:The most famous stone circle

and a search term of Stonehenge would return the following information:

Located site: Stonehenge on line 1

Information on Stonehenge ( Stone Circle and Henge )

====================================================

Location: Wiltshire

Map Reference: SU 123 400

Description: The most famous stone circle

indicating that our brute-force scan and test for the correct site has worked. As you can clearly see

from the example program, we have used Perl's own native file I/O functions for reading in the data

file, and Perl's own string handling functions to break up the delimited data and test it for the correct

record.

The downside to delimited file formats is that if any piece of data contains the delimiting character,

you need to be especially careful not to break up the records in the wrong place. Using the Perl

split() function with a simple regular expression, as used above, does not take this into account and

could produce wrong results. For example, a record containing the following information would cause

the

split() to happen in the wrong place:

Stonehenge:Wiltshire:SU 123 400:Stone Circle and Henge:Stonehenge: The most famous

stone circle

The easiest quick-fix technique is to translate any delimiter characters in the string into some other

character that you're sure won't appear in your data. Don't forget to do the reverse translation when

you fetch the records back.

Programming the Perl DBI

age 14

Another common way of storing data within flat files is to use fixed-length records in which to store

the data. That is, each piece of data fits into an exactly sized space in the data file. In this form of

database, no delimiting character is needed between the fields. There's also no need to delimit each

record, but we'll continue to use ASCII line termination as a record delimiter in our examples because

Perl makes it very easy to work with files line by line.

Using fixed-width fields is similar to the way in which data is organized in more powerful database

systems such as an RDBMS. The pre-allocation of space for record data allows the storage manager to

make assumptions about the layout of the data on disk and to optimize accordingly. For our

megalithic data purposes, we could settle on the data sizes of:

[8]

The fact that these data sizes are all powers of two has no significance other than to indicate that the authors

are old enough to remember when powers of two were significant and useful sometimes. They generally aren't

anymore.

Field Required Bytes

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

Name 64

Location 64

Map Reference 16

Type 32

Description 256

Storing the data in this format requires slightly different storage manager logic to be used, although

the standard Perl file I/O functions are still applicable. To test this data for the correct record, we

need to implement a different way of extracting the fields from within each record. For a fixed-length

data file, the Perl function

unpack() is perfect. The following code shows how the unpack() function

replaces the

split() used above:

### Break up the record data into separate fields

### using the data sizes listed above

( $name, $location, $mapref, $type, $description ) =

unpack( "A64 A64 A16 A32 A256", $_ );

Although fixed-length fields are always the same length, the data that is being put into a particular

field may not be as long as the field. In this case, the extra space will be filled with a character not

normally encountered in the data or one that can be ignored. Usually, this is a space character (ASCII

32) or a

nul (ASCII 0).

In the code above, we know that the data is space-packed, and so we remove any trailing space from

the name record so as not to confuse the search. This can be simply done by using the uppercase

format with

unpack().

If you need to choose between delimited fields and fixed-length fields, here are a few guidelines:

The main limitations

The main limitation with delimited fields is the need to add special handling to ensure that

neither the field delimiter or the record delimiter characters get added into a field value.

The main limitation with fixed-length fields is simply the fixed length. You need to check for

field values being too long to fit (or just let them be silently truncated). If you need to increase

a field width, then you'll have to write a special utility to rewrite your file in the new format

and remember to track down and update every script that manipulates the file directly.

Space

A delimited-field file often uses less space than a fixed-length record file to store the same

data, sometimes very much less space. It depends on the number and size of any empty or

partially filled fields. For example, some field values, like web URLs, are potentially very long

but typically very short. Storing them in a long fixed-length field would waste a lot of space.

While delimited-field files often use less space, they do "waste" space due to all the field

delimiter characters. If you're storing a large number of very small fields then that might tip

the balance in favor of fixed-length records.

Programming the Perl DBI

age 1

Speed

These days, computing power is rising faster than hard disk data transfer rates. In other

words, it's often worth using more space-efficient storage even if that means spending more

processor time to use it.

Generally, delimited-field files are better for sequential access than fixed-length record files

because the reduced size more than makes up for the increase in processing to extract the

fields and handle any escaped or translated delimiter characters.

However, fixed-length record files do have a trick up their sleeve: direct access. If you want to

fetch record 42,927 of a delimited-field file, you have to read the whole file and count records

until you get to the one you want. With a fixed-length record file, you can just multiply 42,927

by the total record width and jump directly to the record using

seek().

Furthermore, once it's located, the record can be updated in-place by overwriting it with new

data. Because the new record is the same length as the old, there's no danger of corrupting

the following record.

2.4.2 Inserting Data

Inserting data into a flat-file database is very straightforward and usually amounts to simply tacking

the new data onto the end of the data file. For example, inserting a new megalith record into a colon-

delimited file can be expressed as simply as:

#!/usr/bin/perl -w

# ch02/insertmegadata/insertmegadata: Inserts a new record into the

# given megalith data file as

# colon-separated data

### Check the user has supplied an argument to scan for

### 1) The name of the file containing the data

### 2) The name of the site to insert the data for

### 3) The location of the site

### 4) The map reference of the site

### 5) The type of site

### 6) The description of the site

die "Usage: insertmegadata"

." <data file> <site name> <location> <map reference> <type> <description>\n"

unless @ARGV == 6;

my $megalithFile = $ARGV[0];

my $siteName = $ARGV[1];

my $siteLocation = $ARGV[2];

my $siteMapRef = $ARGV[3];

my $siteType = $ARGV[4];

my $siteDescription = $ARGV[5];

### Open the data file for concatenation, and die upon failure

open MEGADATA, ">>$megalithFile"

or die "Can't open $megalithFile for appending: $!\n";

### Create a new record

my $record = join( ":", $siteName, $siteLocation, $siteMapRef,

$siteType, $siteDescription );

### Insert the new record into the file

print MEGADATA "$record\n"

or die "Error writing to $megalithFile: $!\n";

### Close the megalith data file

close MEGADATA

or die "Error closing $megalithFile: $!";

print "Inserted record for $siteName\n";

exit;

剩余259页未读，继续阅读

huzhouhzy

粉丝: 83
资源: 1949

Perl数据库编程权威指南

perl-DBI-1.52-2.el5.x86_64.rpm

Perl.DBI手册中文翻译版.pdf

mariadb10.4.7的rpm包及安装所需要的依赖包

错误：依赖检测失败： perl(DBI) 被 perl-DBD-MySQL-4.023-6.el7.x86_64 需要 perl(DBI::Const::GetInfoType) 被 perl-DBD-MySQL-4.023-6.el7.x86_64 需要

perl(Data::Dumper) 被 perl-DBI-1.627-4.el7.x86_64 需要

libmysqlclient.so.18 和 perl(DBI) 等软件包的rpm包安装

libmysqlclient.so.18 和 perl(DBI) 等软件包。

最新资源