Perl DBI权威指南：数据库编程与实践

5星 · 超过95%的资源需积分: 10 179 浏览量更新于2024-07-26 收藏 1.39MB PDF 举报

《Programming the Perl DBI》是由Alligator Descartes（DBI社区中最活跃的一员）和Tim Bunce（DBI的发明者）共同编著的一本关于Perl数据库编程的权威指南，首次出版日期为2000年2月。该书的ISBN为1-56592-699-4，共350页，旨在为Perl开发者提供全面的DBI接口设计和使用教程。书中详细阐述了DBI（Database Interface）的基本架构，使读者能够理解如何编写基于DBI的程序。作者深入浅出地讲解了DBI的复杂性和每个特定数据库驱动器DBD的独特性，确保读者在实际项目中能灵活应用。章节11的“Introduction”部分，作者回顾了数据库技术的发展历程，从大型机到个人工作站，以及Perl在实际工作环境中的角色。书中还探讨了非DBI数据库的基础知识，包括存储管理、数据结构和查询语言等。对于SQL和关系型数据库（第3章），作者解释了关系数据库方法论，涵盖了数据类型、空值处理以及数据查询与修改的重要操作。此外，书中的“Summary”部分总结了本章内容，强调了在Perl中使用SQL进行高效数据库操作的关键要素。在“Flat-File Databases”章节，读者可以学习如何将复杂数据结构存储在简单的文本文件中，并应对并发访问时的锁定策略。接下来的“DBM Files and the Berkeley Database Manager”章节介绍了DBM模块及其在Perl中的应用，而“The MLDBM Module”则可能涉及到多层数据库管理的高级主题。《Programming the Perl DBI》是一本实用且深入的教材，适合对Perl编程和数据库交互感兴趣的开发者，无论是初学者还是经验丰富的程序员，都能从中获得宝贵的知识和实践经验。通过阅读这本书，读者将掌握如何在Perl环境中利用DBI进行高效、稳定的数据库操作，以及如何处理不同数据库系统的异构特性。

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页未读，继续阅读

scpman

粉丝: 1
资源: 8

Perl DBI权威指南：数据库编程与实践

Addison.Wesley.Effective.Perl.Programming.Apr.2010.rar

Programming Perl DBI 8

50本Perl经典电子书打包下载.docx

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

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

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

DBI-1.620.tar.gz

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

perl dbi connect to athena

最新资源