📄 ch16.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<META NAME="Author" Content="Steph Mineart">
<TITLE>Ch 16 -- Advanced InterBase Concepts</TITLE>
</HEAD>
<BODY
BACKGROUND="bg1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/bg1.gif" BGCOLOR="#FFFFFF">
<P ALIGN="CENTER"><IMG SRC="sams.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/sams.gif" WIDTH="75" HEIGHT="24" ALIGN="BOTTOM"
BORDER="0"><BR>
<BR>
<A HREF="index-3.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/index.htm"><IMG SRC="toc.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/toc.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="TOC" BORDER="0" NAME="toc4"></A><A HREF="ch15.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch15.htm"><IMG SRC="back-1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/back.gif"
WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="BACK" BORDER="0" NAME="toc1"></A><A HREF="ch17.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch17.htm"><IMG
SRC="forward.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/forward.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="FORWARD" BORDER="0"
NAME="toc2"></A></P>
<H2 ALIGN="CENTER"><FONT COLOR="#000077">Charlie Calvert's C++ Builder Unleashed</FONT></H2>
<P>
<H1 ALIGN="CENTER"><FONT COLOR="#000077">- 16 -<BR>
Advanced InterBase Concepts</FONT></H1>
<P>In this
chapter, you get a look at the Music program, which shows a good deal
about working with relational databases in general and about working with InterBase
in particular. This chapter is also meant to sum up much of what has been said so
far about
databases and push the whole subject on to another level. It should help
advance your knowledge of SQL, database design, and client/server programming. This
is not, however, a review chapter. There is a lot of important new material in this
chapter.</P>
<P>This chapter features overviews of
<UL>
<LI>InterBase and Security: granting rights on a table
<P>
<LI>The InterBase Server Manager: backing up a database
<P>
<LI>Relational database design
<P>
<LI>Referential integrity
<P>
<LI>Stored procedures and the <TT>TStoredProc</TT> components
<P>
<LI>Triggers
<P>
<LI>Domains
<P>
<LI>Querying data with SQL. These techniques help you prepare reports on the information
in a database, as well as answer the user's questions
about how much data they have
and what kind.
<P>
<LI>Using SQL to extract facts from a database. How many of this type of item do
I have? How can I write a stored procedure that retrieves information from several
tables at once while still
answering a real-world question about the amount of a
particular kind of data?
<P>
<LI>Placing forms on a <TT>TabControl</TT>. The kdAdd program had a huge number of
fields in the main form for the application. In this chapter, you will see how to
create separate forms for each page in an application that uses a tabbed notebook
metaphor. This way, each page exists inside its own discrete object, which helps
you create well-organized, robust applications.
<P>
<LI>Create the illusion of
dynamically changing the shape of a form at runtime. It
turns out that placing forms on a <TT>TabControl</TT> gives you a remarkable amount
of flexibility. In particular, you can show one form on a particular page under one
set of circumstances and
another form on the same page under slightly different circumstances.
This gives the user the impression that you know how to dynamically change the shape
of a form at runtime.
<P>
<LI>Storing multiple types of data in a database and displaying it
with one program.
This database shows information on both a table that contains books and a table that
contains albums and CDs. The <TT>TabControl</TT> technique described in the last
section allows you to seamlessly integrate different types of
data in one program.
From your user's point of view, it appears that the program morphs to accommodate
the type of data currently being displayed.
<P>
<LI>Using SQL to alter a table
</UL>
<P>The burden of the argument for this chapter is again
carried by a sample database
application. This one is designed to track household items such as books, CDs, or
records. However, you can easily expand it to hold many different types of data.
The core strength of this program is its flexible,
extensible design.</P>
<P>One of the interesting features of the Music database is the way it uses stored
procedures to report on the information in the database. For instance, it lets you
store CDs, tapes, and records and rate them according to four
different extensible
criteria:
<UL>
<LI>What type of music is it? Classical? Jazz? Rock? Do you have categories of your
own you want to add?
<P>
<LI>How loud is the music? Is it peaceful, moderate, or loud? You can add other categories
if you
want.
<P>
<LI>How good is the music? On a scale of one to ten, how do you rate it?
<P>
<LI>Finally, what medium is it on? CD? Tape? Record?
</UL>
<P>You can easily expand most of these lists to create as many categories as you
want. Furthermore,
you can query the database to ask questions such as
<UL>
<LI>How many records do I have?
<P>
<LI>How many different artists are listed here?
<P>
<LI>How many albums do I have that I rated in a certain range? For instance, which
records did I
rate as a complete ten? Which ones did I rate as only one or two?
<P>
<LI>Which albums did I rate as loud?
<P>
<LI>What albums are listed under the category called Jazz or Folk?
</UL>
<P>By the time you finish this chapter, you should have a
pretty good feel for how
to tap into the power of InterBase. This chapter is not meant to appeal only to InterBase
developers, however. It also contains many general comments about working with relational
databases in general and SQL databases in
particular. In other words, this chapter
is about real client/server database programming.</P>
<P>The Music program uses several advanced database features of Borland C++Builder
(BCB). For instance, there are examples of calculated fields, data
modules, lookups,
filters, and searching a database with <TT>FindFirst</TT> and <TT>FindNearest</TT>.
This program uses many other standard database techniques, such as calculated fields
and working with ranges. There are numerous examples of how to
use stored procedures
and also an example of how to search on records in the detail table of a master-detail
relationship.</P>
<P>My favorite feature of this program is the way it leverages form inheritance to
give the user the ability to switch back
and forth between the <TT>Album</TT> and
<TT>Book</TT> tables. I like the idea of having one program that can deal with multiple
types of data. The program was designed so you can add new modules to it, such as
forms for handling addresses or
inventory items and so on.</P>
<P>When working with this chapter, you should remember that a complete copy of the
Music database is available on the CD that accompanies this book. Throughout the
first half of this chapter, I talk about the incremental
steps involved in creating
this database, but if you feel the need to see the complete database at any time,
then you can retrieve it from the CD. I quote the entire data definition for the
database about two thirds of the way through this chapter,
and it is available on
the CD in the <TT>Data</TT> directory in the file called <TT>Music.ddl</TT>.</P>
<P>If you need to set up the alias for the database on the CD, a full description
of how to do so is provided in the readme file on the CD that
accompanies this book.
I also talk about the alias at several points in this chapter.</P>
<P>Music is a fairly complex program, but I assure you that the version on the CD
works correctly. If you are having trouble with it, check my Web sites for tips
or
hints on using the program.</P>
<P>Overall, this chapter aims at taking the discussion of databases to a new level.
After you read the text, you will be prepared to write professional-level client/server
applications. All the information in this
chapter applies to common professional
database tasks such as creating an inventory system or even a point of sales application.
<H2><FONT COLOR="#000077">Security and the InterBase Server Manager</FONT></H2>
<P>Before beginning the discussion of the
Music program, it might be a good idea
to cover a few basic issues regarding security. I included enough information so
far to make any BCB programmer dangerous, so I might as well also equip you with
some of the tools you need to defend your work
against prying eyes. If you have the
skill to create programs that others can use, then you have to know how to manage
those clients.</P>
<P>When working with passwords, it's important to make a distinction between user
security for a whole server and
access rights for a particular table. If you open
the InterBase Server Manager, log on, and select Tasks | User Security, you will
find menu options that let you create new users for the system. By default, these
users have access to very little. All
you do is let them in the front door. You haven't
yet given them a pass to visit any particular rooms in the house. As this discussion
matures, I discuss how to grant particular rights to a user after he or she has been
admitted into the
"building."</P>
<P>If you are interested in setting up real security for your database, then the
first thing you should do is change the <TT>SYSDBA</TT> password. To change the password,
sign on to the InterBase Server Manager as
<TT>SYSDBA</TT> using the password <TT>masterkey</TT>.
Select Tasks and then User Security from the menu. Select the username <TT>SYSDBA</TT>,
and choose Modify User. Now enter a new password. Once you do this much, the system
is truly under your
control. No one else can get at your data unless you decide they
should have the right to do so. Even then, you can severely proscribe their activities
with a remarkable degree of detail. (If you are a control freak, this is paradise!)</P>
<P>After
establishing your sovereignty, the next step is to go out and recruit the
peons who will inhabit your domain. Once you find a new user, select Tasks | User
Security and choose Add User and give him or her a password. The person who creates
users is
the one who signs on as <TT>SYSDBA</TT>. <TT>SYSDBA</TT> has all the power,
which is why it is important to change the <TT>SYSDBA</TT> password if you are really
serious about security.</P>
<P>If you create a new user, this newcomer has no rights on
the system by default.
To give a user rights, you must use the SQL <TT>Grant</TT> command, which is discussed
in the next section.
<H2><FONT COLOR="#000077">Defining Access Rights to a Table</FONT></H2>
<P>After you create a user in the InterBase
Server Manager, you grant them rights
to access a table. SQL databases give you extraordinary control over exactly how
much access a user can have to a table. For instance, you can give a user only the
right to query one or more tables in your
database:</P>
<PRE><FONT COLOR="#0066FF">grant select on Test1 to user1
</FONT></PRE>
<P>Conversely, you may, if you want, give a user complete control over a table, including
the right to grant other people access to the table:</P>
<PRE><FONT
COLOR="#0066FF">grant all on album to Sue with grant option
</FONT></PRE>
<P>The <TT>with grant option</TT> clause shown here specifies that Sue not only has
her way with the album table, but also can give access to the table to others.</P>
<P>You
can give a user six distinct types of privileges:
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">All</TD>
<TD ALIGN="LEFT">Has select, delete, insert, update, and execute privileges</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Select</TD>
<TD ALIGN="LEFT">Can view a table or view</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Delete</TD>
<TD ALIGN="LEFT">Can delete from a table or view</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD
ALIGN="LEFT">Insert</TD>
<TD ALIGN="LEFT">Can add data to a table or view</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT">Update</TD>
<TD ALIGN="LEFT">Can edit a table or view</TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD
ALIGN="LEFT">Execute</TD>
<TD>Can execute a stored procedure</TD>
</TR>
</TABLE>
<DL>
<DD>
</DL>
<P><BR>
Using these keys to the kingdom, you can quickly start handing out passes to particular
rooms in the palace. For instance, you can
write</P>
<PRE><FONT COLOR="#0066FF">grant insert on Test1 to Sue
grant delete on Test1 to Mary with grant option
grant select on Test1 to Tom, Mary, Sue, User1
grant select, insert, delete, update on Test1 to Mary
grant delete, insert, update,
references on country to public with grant option;
</FONT></PRE>
<P>The last statement in this list comes from the <TT>Employee.gdb</TT> example that
ships with BCB. Notice that it grants rights to the public, which means all users
have absurdly
liberal rights on the table.</P>
<P>The opposite of the <TT>Grant</TT> command is <TT>Revoke</TT>. <TT>Revoke</TT>
removes privileges given with <TT>Grant</TT>. Here is an example of using <TT>Revoke</TT>:</P>
<PRE><FONT COLOR="#0066FF">revoke select
on Test1 from Sue
</FONT></PRE>
<P>This brief overview of the Server Manager and some related issues involving the
<TT>grant</TT> command should give you a sense of how to limit access to your database.
None of this material is particularly
difficult, but you should be sure you understand
it because SQL databases can be mysterious and frustrating if you don't know the
basics about how to control them. If you need more information than what I presented
here, you will probably find all you
need to know to cover this small domain of knowledge
in the online help.
<H3><FONT COLOR="#000077">Backing Up Tables with the Server Manager</FONT></H3>
<P>Another important feature of the InterBase Server Manager is backing up tables.
This task can
be especially important if you need to move a table from Windows 95
or Windows NT to UNIX. The highly compressed backup format for InterBase tables is
completely version- independent, so you can back up an NT table and then restore
it on a UNIX
system.</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -