Stones / Matthew Beginning Databases with PostgreSQL

From Novice to Professional
2. ed
ISBN: 978-1-4302-0018-5
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 664 Seiten

*The most updated PostgreSQL book on the market, covering version 8.0 *Highlights the most popular PostgreSQL APIs, including C, Perl, PHP, and Java *This is two books in one; it simultaneously covers key relational database design principles, while teaching PostgreSQL

Richard Stones graduated from university with an electrical engineering degree, but decided software was more fun. He has programmed in a variety of languages, but only admits to knowing Visual Basic under duress. He has worked for a number of companies, from the very small to the very large, in a variety of areas, from real-time embedded systems upward. He is employed by Celesio AG as a systems architect, working principally on systems for the retail side of the business. He has co-authored several computing books with Neil Matthew, including Beginning Linux Programming, Professional Linux Programming, and Beginning Databases with MySQL.

Autoren/Hrsg.

Stones, Richard

Matthew, Neil

Inhaltsverzeichnis

1;Contents;6
2;About the Authors;18
3;About the Technical Reviewer;20
4;Acknowledgments;21
5;Introduction;22
6;Chapter 1 Introduction to PostgreSQL;24
6.1;Programming with Data;24
6.1.1;Constant Data;25
6.1.2;Flat Files for Data Storage;25
6.1.3;Repeating Groups and Other Problems;26
6.2;What Is a Database Management System?;27
6.2.1;Database Models;27
6.2.2;Query Languages;31
6.2.3;Database Management System Responsibilities;33
6.3;What Is PostgreSQL?;34
6.3.1;Short History of PostgreSQL;35
6.3.2;The PostgreSQL Architecture;36
6.3.3;Data Access with PostgreSQL;38
6.4;What Is Open Source?;38
6.5;Resources;39
7;Chapter 2 Relational Database Principles;40
7.1;Limitations of Spreadsheets;40
7.2;Storing Data in a Database;44
7.2.1;Choosing Columns;44
7.2.2;Choosing a Data Type for Each Column;44
7.2.3;Identifying Rows Uniquely;45
7.3;Accessing Data in a Database;46
7.3.1;Accessing Data Across a Network;47
7.3.2;Handling Multiuser Access;48
7.3.3;Slicing and Dicing Data;49
7.4;Adding Information;51
7.4.1;Using Multiple Tables;51
7.4.2;Relating a Table with a Join Operation;52
7.5;Designing Tables;55
7.5.1;Understanding Some Basic Rules of Thumb;56
7.5.2;Creating a Simple Database Design;57
7.5.3;Extending Beyond Two Tables;58
7.5.4;Completing the Initial Design;60
7.6;Basic Data Types;63
7.7;Dealing with the Unknown: NULLs;64
7.8;Reviewing the Sample Database;65
7.9;Summary;65
8;Chapter 3 Getting Started with PostgreSQL;66
8.1;Installing PostgreSQL on Linux and UNIX Systems;66
8.1.1;Installing PostgreSQL from Linux Binaries;67
8.1.2;Anatomy of a PostgreSQL Installation;70
8.1.3;Installing PostgreSQL from the Source Code;72
8.1.4;Setting Up PostgreSQL on Linux and UNIX;76
8.2;Installing PostgreSQL on Windows;82
8.2.1;Using the Windows Installer;82
8.2.2;Configuring Client Access;87
8.3;Creating the Sample Database;87
8.3.1;Creating User Records;88
8.3.2;Creating the Database;88
8.3.3;Creating the Tables;90
8.3.4;Removing the Tables;91
8.3.5;Populating the Tables;92
8.4;Summary;95
9;Chapter 4 Accessing Your Data;96
9.1;Using psql;97
9.1.1;Starting Up on Linux Systems;97
9.1.2;Starting Up on Windows Systems;97
9.1.3;Resolving Startup Problems;98
9.1.4;Using Some Basic psql Commands;101
9.2;Using Simple SELECT Statements;101
9.2.1;Overriding Column Names;104
9.2.2;Controlling the Order of Rows;104
9.2.3;Suppressing Duplicates;106
9.3;Performing Calculations;109
9.4;Choosing the Rows;110
9.4.1;Using More Complex Conditions;112
9.4.2;Pattern Matching;114
9.4.3;Limiting the Results;115
9.5;Checking for NULL;116
9.6;Checking Dates and Times;117
9.6.1;Setting the Time and Date Style;117
9.6.2;Using Date and Time Functions;121
9.7;Working with Multiple Tables;123
9.7.1;Relating Two Tables;123
9.7.2;Aliasing Table Names;128
9.7.3;Relating Three or More Tables;129
9.8;The SQL92 SELECT Syntax;133
9.9;Summary;135
10;Chapter 5 PostgreSQL Command-Line and Graphical Tools;136
10.1;psql;136
10.1.1;Starting psql;137
10.1.2;Issuing Commands in psql;137
10.1.3;Working with the Command History;138
10.1.4;Scripting psql;138
10.1.5;Examining the Database;140
10.1.6;psql Command-Line Quick Reference;141
10.1.7;psql Internal Commands Quick Reference;142
10.2;ODBC Setup;144
10.2.1;Installing the ODBC Driver;144
10.2.2;Creating a Data Source;146
10.3;pgAdmin III;148
10.3.1;Installing pgAdmin III;148
10.3.2;Using pgAdmin III;149
10.4;phpPgAdmin;152
10.4.1;Installing phpPgAdmin;153
10.4.2;Using phpPgAdmin;153
10.5;Rekall;156
10.5.1;Connecting to a Database;157
10.5.2;Creating Forms;158
10.5.3;Building Queries;159
10.6;Microsoft Access;160
10.6.1;Using Linked Tables;160
10.6.2;Entering Data and Creating Reports;164
10.7;Microsoft Excel;165
10.8;Resources for PostgreSQL Tools;169
10.9;Summary;170
11;Chapter 6 Data Interfacing;171
11.1;Adding Data to the Database;171
11.1.1;Using Basic INSERT Statements;171
11.1.2;Using Safer INSERT Statements;174
11.1.3;Inserting Data into Serial Columns;176
11.1.4;Inserting NULL Values;180
11.1.5;Using the \copy Command;181
11.1.6;Loading Data Directly from Another Application;184
11.2;Updating Data in the Database;187
11.2.1;Using the UPDATE Statement;187
11.2.2;Updating from Another Table;190
11.3;Deleting Data from the Database;191
11.3.1;Using the DELETE Statement;191
11.3.2;Using the TRUNCATE Statement;192
11.4;Summary;193
12;Chapter 7 Advanced Data Selection;194
12.1;Aggregate Functions;194
12.1.1;The Count Function;195
12.1.2;The Min Function;203
12.1.3;The Max Function;204
12.1.4;The Sum Function;205
12.1.5;The Avg Function;205
12.2;The Subquery;206
12.2.1;Subqueries That Return Multiple Rows;208
12.2.2;Correlated Subqueries;209
12.2.3;Existence Subqueries;212
12.3;The UNION Join;213
12.4;Self Joins;215
12.5;Outer Joins;217
12.6;Summary;221
13;Chapter 8 Data Definition and Manipulation;222
13.1;Data Types;222
13.1.1;The Boolean Data Type;223
13.1.2;Character Data Types;225
13.1.3;Number Data Types;227
13.1.4;Temporal Data Types;230
13.1.5;Special Data Types;230
13.1.6;Arrays;231
13.2;Data Manipulation;233
13.2.1;Converting Between Data Types;233
13.2.2;Functions for Data Manipulation;235
13.2.3;Magic Variables;236
13.2.4;The OID Column;237
13.3;Table Management;238
13.3.1;Creating Tables;238
13.3.2;Using Column Constraints;239
13.3.3;Using Table Constraints;243
13.3.4;Altering Table Structures;244
13.3.5;Deleting Tables;248
13.3.6;Using Temporary Tables;248
13.4;Views;249
13.4.1;Creating Views;249
13.4.2;Deleting and Replacing Views;252
13.5;Foreign Key Constraints;253
13.5.1;Foreign Key As a Column Constraint;254
13.5.2;Foreign Key As a Table Constraint;255
13.5.3;Foreign Key Constraint Options;261
13.6;Summary;263
14;Chapter 9 Transactions and Locking;264
14.1;What Are Transactions?;264
14.1.1;Grouping Data Changes into Logical Units;265
14.1.2;Concurrent Multiuser Access to Data;265
14.1.3;ACID Rules;267
14.1.4;Transaction Logs;268
14.2;Transactions with a Single User;268
14.2.1;Transactions Involving Multiple Tables;271
14.2.2;Transactions and Savepoints;272
14.2.3;Transaction Limitations;275
14.3;Transactions with Multiple Users;276
14.3.1;Implementing Isolation;276
14.3.2;Changing the Isolation level;282
14.3.3;Using Explicit and Implicit Transactions;282
14.4;Locking;283
14.4.1;Avoiding Deadlocks;283
14.4.2;Explicit Locking;285
14.5;Summary;287
15;Chapter 10 Functions, Stored Procedures, and Triggers;288
15.1;Operators;289
15.1.1;Operator Precedence and Associativity;290
15.1.2;Arithmetic Operators;291
15.1.3;Comparison and String Operators;293
15.1.4;Other Operators;294
15.2;Built-in Functions;294
15.3;Procedural Languages;297
15.3.1;Getting Started with PL/pgSQL;298
15.3.2;Function Overloading;300
15.3.3;Listing Functions;302
15.3.4;Deleting Functions;302
15.3.5;Quoting;302
15.4;Anatomy of a Stored Procedure;303
15.4.1;Function Arguments;304
15.4.2;Comments;305
15.4.3;Declarations;305
15.4.4;Assignments;309
15.4.5;Execution Control Structures;310
15.4.6;Dynamic Queries;318
15.5;SQL Functions;319
15.6;Triggers;320
15.6.1;Defining a Trigger Procedure;321
15.6.2;Creating Triggers;321
15.7;Why Use Stored Procedures and Triggers?;327
15.8;Summary;328
16;Chapter 11 PostgreSQL Administration;329
16.1;System Configuration;329
16.1.1;The bin Directory;330
16.1.2;The data Directory;331
16.1.3;Other PostgreSQL Subdirectories;336
16.2;Database Initialization;337
16.3;Server Control;338
16.3.1;Running Processes on Linux and UNIX;338
16.3.2;Starting and Stopping the Server on Linux and UNIX;339
16.4;PostgreSQL Internal Configuration;340
16.4.1;Configuration Methods;340
16.4.2;User Configuration;341
16.4.3;Group Configuration;345
16.4.4;Tablespace Management;346
16.4.5;Database Management;348
16.4.6;Schema Management;351
16.4.7;Privilege Management;357
16.5;Database Backup and Recovery;358
16.5.1;Creating a Backup;359
16.5.2;Restoring from a Backup;361
16.5.3;Backing Up and Restoring from pgAdmin III;363
16.6;Database Performance;367
16.6.1;Monitoring Behavior;367
16.6.2;Using VACUUM;368
16.6.3;Creating Indexes;372
16.7;Summary;376
17;Chapter 12 Database Design;377
17.1;What Is a Good Database Design?;377
17.2;Understanding the Problem;377
17.3;Taking Design Aspects into Account;378
17.4;Stages in Database Design;380
17.4.1;Gathering Information;381
17.4.2;Developing a Logical Design;381
17.4.3;Determining Relationships and Cardinality;386
17.5;Converting to a Physical Model;391
17.5.1;Establishing Foreign Keys;393
17.5.2;Establishing Data Types;395
17.5.3;Completing the Table Definitions;397
17.5.4;Implementing Business Rules;397
17.5.5;Checking the Design;398
17.6;Normal Forms;398
17.6.1;First Normal Form;398
17.6.2;Second Normal Form;399
17.6.3;Third Normal Form;399
17.7;Common Patterns;400
17.7.1;Many-to-Many;400
17.7.2;Hierarchy;401
17.7.3;Recursive Relationships;402
17.8;Resources for Database Design;404
17.9;Summary;404
18;Chapter 13 Accessing PostgreSQL from C Using libpq;405
18.1;Using the libpq Library;406
18.2;Making Database Connections;407
18.2.1;Creating a New Database Connection;407
18.2.2;Using a Makefile;410
18.2.3;Retrieving Information About Connection Errors;411
18.2.4;Learning About Connection Parameters;411
18.3;Executing SQL with libpq;412
18.3.1;Determining Query Status;412
18.3.2;Executing Queries with PQexec;414
18.3.3;Creating a Variable Query;416
18.3.4;Updating and Deleting Rows;416
18.3.5;Extracting Data from Query Results;417
18.3.6;Handling NULL Results;420
18.4;Printing Query Results;421
18.5;Managing Transactions;424
18.6;Using Cursors;424
18.6.1;Fetching All the Results at Once;426
18.6.2;Fetching Results in Batches;428
18.7;Dealing with Binary Values;431
18.8;Working Asynchronously;431
18.8.1;Executing a Query in Asynchronous Mode;432
18.8.2;Canceling an Asynchronous Query;435
18.8.3;Making an Asynchronous Database Connection;435
18.9;Summary;437
19;Chapter 14 Accessing PostgreSQL from C Using Embedded SQL;438
19.1;Using ecpg;438
19.1.1;Writing an esqlc Program;439
19.1.2;Using a Makefile;442
19.1.3;Using ecpg Arguments;443
19.2;Logging SQL Execution;444
19.3;Making Database Connections;444
19.4;Error Handling;446
19.4.1;Reporting Errors;447
19.4.2;Trapping Errors;450
19.5;Using Host Variables;451
19.5.1;Declaring Fixed-Length Variable Types;451
19.5.2;Working with Variable-Length Data;453
19.6;Retrieving Data with ecpg;455
19.6.1;Dealing with Null-Terminated Strings;456
19.6.2;Dealing with NULL Database Values;457
19.6.3;Handling Empty Results;458
19.7;Implementing Cursors in Embedded SQL;460
19.8;Debugging ecpg Code;462
19.9;Summary;463
20;Chapter 15 Accessing PostgreSQL from PHP;464
20.1;Adding PostgreSQL Support to PHP;464
20.2;Using the PHP API for PostgreSQL;465
20.3;Making Database Connections;466
20.3.1;Creating a New Database Connection;466
20.3.2;Creating a Persistent Connection;467
20.3.3;Closing Connections;468
20.3.4;Learning More About Connections;468
20.4;Building Queries;469
20.4.1;Creating Complex Queries;470
20.4.2;Executing Queries;471
20.5;Working with Result Sets;471
20.5.1;Extracting Values from Result Sets;472
20.5.2;Getting Field Information;475
20.5.3;Freeing Result Sets;476
20.5.4;Type Conversion of Result Values;477
20.6;Error Handling;477
20.7;Getting and Setting Character Encoding;478
20.8;Using PEAR;478
20.8.1;Using PEAR’s Database Abstraction Interface;479
20.8.2;Error Handling with PEAR;480
20.8.3;Preparing and Executing Queries with PEAR;481
20.9;Summary;482
21;Chapter 16 Accessing PostgreSQL from Perl;483
21.1;Installing Perl Modules;484
21.1.1;Using CPAN;484
21.1.2;Using PPM;485
21.2;Installing the Perl DBI;486
21.2.1;Installing DBI and the PostgreSQL DBD on Windows;487
21.2.2;Installing DBI and the PostgreSQL DBD from Source;489
21.3;Using DBI;490
21.3.1;Making Database Connections;491
21.3.2;Executing SQL;495
21.3.3;Working with Result Sets;496
21.3.4;Binding Parameters;499
21.3.5;Using Other DBI Features;501
21.4;Using DBIx::Easy;502
21.5;Creating XML from DBI Queries;503
21.5.1;SQL to XML;505
21.5.2;XML to SQL;506
21.6;Summary;507
22;Chapter 17 Accessing PostgreSQL from Java;508
22.1;Using a PostgreSQL JDBC Driver;508
22.1.1;Installing a PostgreSQL JDBC Driver;510
22.1.2;Using the Driver Interface and DriverManager Class;510
22.2;Making Database Connections;515
22.2.1;Creating Database Statements;515
22.2.2;Handling Transactions;516
22.2.3;Retrieving Database Meta Data;517
22.3;Working with JDBC Result Sets;519
22.3.1;Getting the Result Set Type and Concurrency;519
22.3.2;Traversing Result Sets;520
22.3.3;Accessing Result Set Data;521
22.3.4;Working with Updatable Result Sets;522
22.3.5;Using Other Relevant Methods;524
22.4;Creating JDBC Statements;524
22.4.1;Using Statements;525
22.4.2;Using Prepared Statements;529
22.5;Summary;533
23;Chapter 18 Accessing PostgreSQL from C#;534
23.1;Using the ODBC .NET Data Provider on Windows;534
23.1.1;Setting Up the ODBC .NET Data Provider;534
23.1.2;Connecting to the Database;535
23.1.3;Retrieving Data into a Dataset;536
23.2;Using Npgsql in Mono;537
23.2.1;Connecting to the Database;538
23.2.2;Retrieving Data from the Database;542
23.2.3;Using Parameters and Prepared Statements with Npgsql;549
23.2.4;Changing Data in the Database;553
23.3;Using Npgsql in Visual Studio;556
23.4;Summary;557
24;APPENDIX A PostgreSQL Database Limits;559
25;APPENDIX B PostgreSQL Data Types;561
25.1;Logical Types;561
25.2;Exact Number Types;562
25.3;Approximate Number Types;562
25.4;Temporal Types;563
25.5;Character Types;563
25.6;Geometric Types;564
25.7;Miscellaneous PostgreSQL Types;564
26;APPENDIX C PostgreSQL SQL Syntax Reference;566
26.1;PostgreSQL SQL Commands;566
26.2;PostgreSQL SQL Syntax;567
27;APPENDIX D psql Reference;588
27.1;Command-Line Options;588
27.2;Internal Commands;589
28;APPENDIX E Database Schema and Tables;592
29;APPENDIX F Large Objects Support in PostgreSQL;595
29.1;Using Links;595
29.2;Using Encoded Text Strings;596
29.3;Using BLOBs;597
30;INDEX;602

Leseproben

Accessing PostgreSQL from Perl (S. 465-466)

As earlier chapters have shown, communicating with PostgreSQL generally involves a lot of string manipulation. One language that excels at string manipulation is Perl. In Chapter 13, we demonstrated that the libpq interface is a powerful way to access a PostgreSQL database, but there are disadvantages. We need to use string manipulation to pass values to queries and to retrieve results, and for short programs, the C code dealing with strings can overshadow the database interactions.

As Chapter 13 pointed out, although binary access is possible, its benefits are minimal. With Perl, strings are much more sophisticated, supporting functionality such as joining, splitting, pattern matching, and automatic conversion to and from other data types. Perl has also historically been associated with web server processing (although more modern mechanisms such as PHP, described in the previous chapter, are taking over that role).

Having interfaces to databases definitely adds benefits. If you know even a little about Perl, you will be aware that one of the language’s axioms is that there is always more than one way to tackle any given job. In fact, Perl enthusiasts would be disappointed if they had to limit their options to single figures. We do not propose to bombard you with numerous techniques for accessing PostgreSQL databases from Perl, however. Instead, we will present a single methodology. There are essentially three ways to access PostgreSQL from Perl:

• Low-level access, which is essentially a Perl mapping of the libpq C interface (Module Pg)
• High-level access, using a database independent layer (DBI)
• Access by embedding the Perl interpreter (similar to the description in Chapter 14)

We will describe only the high-level DBI access mechanism, because it is the simplest to install and use. This method is database-independent, yet is still very flexible and powerful. If you are interested in a libpq-style of working, we suggest taking a look at Module Pg, which is part of the DBD:: Pg database driver. PL/Perl requires a version of Perl to have been initially built as a shared library - libperl.so, as opposed to the more usual libperl.a (see the instructions for building Perl found in Perl source distributions).

Installing Perl Modules

The Perl programming language supports the concept of modules - additional functions that can be integrated into a Perl installation to provide extra features. Many developers have developed modules to extend Perl, providing diverse functionality, including network protocols for file transfer or sending e-mail, parsing and manipulating XML documents, generating graphical images, and more. Much of Perl’s standard functionality is provided by modules included with a basic Perl installation.

In this chapter, we will be using several modules that are not included in the base Perl installation. Because installing modules is a fairly common task, the Perl community has developed a standard process for finding and installing modules. This process has itself been encapsulated in a Perl module, the CPAN module, which we will meet in a moment. Perl modules can be written in several languages, not just Perl. Modules written in a language other than Perl need to be compiled before they can be installed, but binaries are often available to download.