Apress - The Firebird Book A Reference for Database Developers (2004).pdf

(14174 KB) Pobierz
28842837 UNPDF
The Firebird Book:
Database Developers
HELEN BORRIE
A Reference for
28842837.003.png 28842837.004.png
The Firebird Book: A Reference for Database Developers
Copyright © 2004 by Helen Borrie and IBPhoenix
All rights reserved. No part of this work may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN (pbk): 1-59059-279-4
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Lead Editor: Chris Mills
Technical Reviewer: Geoff Worboys
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis,
Chris Mills, Steve Rycroft, Dominic Shakeshaft, Jim Sumser, Karen Watterson, Gavin Wray,
John Zukowski
Project Manager: Kylie Johnston
Production Manager: Kari Brooks
Production Editor: Janet Vail
Compositor: Molly Sharp, Content Works
Proofreaders: Patrick Vincent and Katie Stence
Indexer: Valerie Perry
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 175 Fifth
Avenue, New York, NY 10010 and outside the United States by Springer-Verlag GmbH & Co. KG,
Tiergartenstr. 17, 69112 Heidelberg, Germany.
In the United States: phone 1-800-SPRINGER, email orders@springer-ny.com , or visit
http://www.springer-ny.com . Outside the United States: fax +49 6221 345229, email
orders@springer.de , or visit http://www.springer.de .
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219,
Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, email info@apress.com , or visit
http://www.apress.com .
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com in the
Downloads section.
28842837.005.png
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Part One Boot Camp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Chapter 1
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Chapter 2
Network Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Chapter 3
Configuring Firebird . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Chapter 4
Operating Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Part Two Client/Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Chapter 5
Introduction to Client/Server Architecture . . . . . . . 75
Chapter 6
Firebird Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Chapter 7
Firebird Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Part Three Firebird Data Types and Domains . . . . . . . . . . . 111
Chapter 8
About Firebird Data Types . . . . . . . . . . . . . . . . . . . . . . . 113
Chapter 9
Number Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Chapter 10
Date and Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Chapter 11
Character Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Chapter 12
BLOBs and Arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Chapter 13
Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Part Four A Database and Its Objects . . . . . . . . . . . . . . . . . 205
Chapter 14
From Drawing Board to Database . . . . . . . . . . . . . . . . . . 207
Chapter 15
Creating and Maintaining a Database . . . . . . . . . . . . . 231
Chapter 16
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
Chapter 17
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Chapter 18
Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Part Five Firebird SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Chapter 19
Firebird’s SQL Language . . . . . . . . . . . . . . . . . . . . . . . . . 347
Chapter 20
DML Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Chapter 21
Expressions and Predicates . . . . . . . . . . . . . . . . . . . . . . 387
Chapter 22
Querying Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . 431
iii
28842837.006.png
Contents at a Glance
Chapter 23
Ordered and Aggregated Sets . . . . . . . . . . . . . . . . . . . . . 467
Chapter 24
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Part Six Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 503
Chapter 25
Overview of Firebird Transactions . . . . . . . . . . . . . . . 505
Chapter 26
Configuring Transactions . . . . . . . . . . . . . . . . . . . . . . . . 521
Chapter 27
Programming with Transactions . . . . . . . . . . . . . . . . . . . 535
Part Seven Server Programming . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Chapter 28
Introduction to Firebird Programming . . . . . . . . . . . . 563
Chapter 29
Developing PSQL Modules . . . . . . . . . . . . . . . . . . . . . . . . . 573
Chapter 30
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Chapter 31
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
Chapter 32
Error Handling and Events . . . . . . . . . . . . . . . . . . . . . . . 665
Part Eight Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
Chapter 33
Security in the Operating Environment . . . . . . . . . . . 689
Chapter 34
Server Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697
Chapter 35
Database-Level Security . . . . . . . . . . . . . . . . . . . . . . . . . 713
Chapter 36
Configuration and Special Features . . . . . . . . . . . . . . 739
Part Nine Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777
Chapter 37
Interactive SQL Utility (isql) . . . . . . . . . . . . . . . . . . 779
Chapter 38
Database Backup and Restore (gbak) . . . . . . . . . . . . . . 813
Chapter 39
Housekeeping Tool (gfix) . . . . . . . . . . . . . . . . . . . . . . . . 835
Chapter 40
Understanding the Lock Manager . . . . . . . . . . . . . . . . . . 855
Appendices
Appendix I External Function Summary . . . . . . . . . . . . . . . . . . . . . . . 881
Appendix II Solving Network Problems . . . . . . . . . . . . . . . . . . . . . . . . 915
Appendix III Application Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . 923
Appendix IV Database Repair How-to . . . . . . . . . . . . . . . . . . . . . . . . . . 933
Appendix V Administration Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . 937
Appendix VI The Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 943
Appendix VII Firebird Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 945
Appendix VIII Character Sets and Collations . . . . . . . . . . . . . . . . . . . 951
Appendix IX System Tables and Views . . . . . . . . . . . . . . . . . . . . . . . . 957
Appendix X Error Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 981
Appendix XI Reserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1011
Appendix XII Readings and Resources . . . . . . . . . . . . . . . . . . . . . . . . . 1015
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1021
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1047
iv
28842837.001.png
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Part One Boot Camp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Chapter 1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
How to Get an Installation Kit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Linux Kits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Windows Kits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Testing Your Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Installing an Embedded Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Other Things You Need to Know . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Default Disk Locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Up Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Chapter 2 Network Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Network Protocols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Named Pipes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Local Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Mixed Platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
A Network Address for the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
HOSTS File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Server Name and Path . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Configuring the TCP/IP Port Service . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Testing the Connection with ping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Up Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Chapter 3 Configuring Firebird . . . . . . . . . . . . . . . . . . . . . . . 45
Database-Level Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
The Firebird Configuration File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Up Next . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
v
28842837.002.png

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin