ch12.htm

好书《C++ Builder高级编程技术》

a database, I thought of it as a place to store information. After spending a lot
of time with relational databases, I now think of them primarily as a way to relate
bits of information through keys and indexes.</P>
<P>I know this is putting the cart 
before the horse, but what really interests me
about databases now is not the fact that they contain information per se, but that
I can query them to retrieve related bits of information. In other words, I'm more
interested in the logic that defines 
how tables relate to one another than I am in
the information itself.</P>
<P>No one can get excited about a list of addresses or a list of books. The lists
themselves are very boring. What's interesting is the system of keys and indexes
that relate 
tables together, and the various SQL statements you can use to ask questions
against various sets of tables.</P>
<P>When I picture a table, I see its primary and foreign keys as great big pillars,
and I envision all the rest of the data as a little 
stone altar that is dwarfed by
the pillars. Like a pagan temple, it's the pillars that you notice first; the altar
is just a small stone structure you might overlook until someone points it out. Of
course the temple is built around the altar, and 
databases are built around their
data. But in practice it is easy to overlook the data. You care about the pillars,
and you care about the primary and foreign keys. The rest tends to fade into the
background.</P>
<P>Give me a well-designed database 
with lots of interrelated tables and I can have
fun asking it all sorts of interesting questions. It's not the data per se that is
important, but the way the data is related!</P>
<P>The act of properly relating a set of tables in a database is called, 
tragically
enough, &quot;normalizing&quot; the data. Where this dreadful term came from I have
no idea, but &quot;normalizing&quot; a database is the fun part of creating a database
application.
<H3><A NAME="Heading23"></A><FONT 
COLOR="#000077">Exploring the Keys and Indices
in the BCDEMOS Database</FONT></H3>
<P>I am now going to look again at the tools that ship with BCB, and show how to
use them to view and create indexes and keys. This examination of the subject will
have 
greater depth than the quick overview presented earlier in this chapter.</P>
<P>Here is a list of the indexes on the <TT>Customers</TT>, <TT>Orders</TT>, <TT>Items</TT>,
and <TT>Parts</TT> tables: 
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		
<TD WIDTH="93" ALIGN="LEFT"><B>Table name</B></TD>
		<TD WIDTH="124" ALIGN="LEFT"><B>Primary indexes</B></TD>
		<TD ALIGN="LEFT"><B>Secondary indexes</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="93" ALIGN="LEFT"><TT>Customer</TT></TD>
		
<TD WIDTH="124" ALIGN="LEFT"><TT>CustNo</TT></TD>
		<TD ALIGN="LEFT"><TT>Company</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="93" ALIGN="LEFT"><TT>Orders</TT></TD>
		<TD WIDTH="124" ALIGN="LEFT"><TT>OrderNo</TT></TD>
		<TD 
ALIGN="LEFT"><TT>CustNo</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="93" ALIGN="LEFT"><TT>Items</TT></TD>
		<TD WIDTH="124" ALIGN="LEFT"><TT>OrderNo</TT>, <TT>ItemNo</TT></TD>
		<TD ALIGN="LEFT"><TT>OrderNo</TT>, <TT>PartNo</TT></TD>
	
</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="93" ALIGN="LEFT"><TT>Parts</TT></TD>
		<TD WIDTH="124" ALIGN="LEFT"><TT>PartNo</TT></TD>
		<TD ALIGN="LEFT"><TT>VendorNo</TT>, <TT>Description</TT></TD>
	</TR>
</TABLE>
<BR>
<BR>
Notice that the 
<TT>Items</TT> table has a composite primary index consisting of
the <TT>OrderNo</TT> and <TT>ItemNo</TT> fields. It also has two secondary indexes,
one on the <TT>OrderNo</TT> field and one on the <TT>PartNo</TT> field. The <TT>Parts</TT>
table has 
two secondary indexes, one on the <TT>VenderNo</TT>, and one on the <TT>Description</TT>
field.</P>
<P>If you do not have a pre-made list like this one, you could find this information
in at least four ways:

<UL>
	<LI>The Object Inspector
	<P>
	
<LI>The Database Explorer
	<P>
	<LI>The Database Desktop
	<P>
	<LI>By creating a program that leverages the methods of the <TT>TSession</TT> object.
	Such a program will be shown in Chapter 14.
</UL>

<P>I will explain all these methods and then 
discuss some possible alternative techniques.</P>
<P>If you drag the <TT>Customer</TT> table off the Explorer and onto a form, you
will be able to view its Indices in the Object Inspector. If you drop down the <TT>IndexName</TT>
property editor, you 
will see that there is one index listed there. This is the secondary
index, called <TT>ByCompany</TT>. If you select this index, the table will sort on
the <TT>Company</TT> field.</P>
<P>If you set the <TT>IndexName</TT> property back to blank, the 
table will sort
automatically on the primary index, which is the <TT>CustNo</TT> field. In other
words, BCB never explicitly lists the primary index in the <TT>IndexName</TT> property
editor. I suppose that the architects of the VCL assumed that all 
tables have a primary
index, and that if you don't specify a particular index name, you want to sort on
that index. Of course, it is not an error to create a table that has no primary index,
and BCB can still work with that kind of table.</P>
<P>You 
can also drop down the <TT>IndexFieldNames</TT> property, which gives you
a list of the fields that are indexed, in this case the <TT>CustNo</TT> and <TT>Company</TT>
fields. Here you can see the fields included in the primary index, but they are not

marked as belonging to any particular index.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>To study an interesting case, drop
	down the <TT>Items</TT> table on a form. Recall that it has a primary 
index on the
	<TT>OrderNo</TT> and <TT>ItemNo</TT> fields, and secondary indexes on the <TT>OrderNo</TT>
	and <TT>PartNo</TT> fields. If you drop down the index field names, you see the following
	list:</P>
	<PRE><FONT COLOR="#0066FF">OrderNo


OrderNo; ItemNo

PartNo</FONT></PRE>
	<P>The first item is the <TT>ByOrderNo</TT> index--the second the primary index--and
	the third, the <TT>PartNo</TT> index. 
<HR>


</BLOCKQUOTE>

<P>The <TT>IndexName</TT> and <TT>IndexFieldNames</TT> properties 
give you a handy
way of tracking <TT>Indices</TT> at design time. They don't, however, give you all
the information you might need, such as exactly what fields make up which parts of
the primary and secondary <TT>Indices</TT>. In this case, you could 
probably guess,
but it would still be nice to get a more definitive answer.</P>
<P>If you open up the Database Explorer, expand the <TT>BCDEMOS</TT> node, the <TT>Tables</TT>
node, the <TT>Customer</TT> node, and finally the <TT>Indices</TT> node, you 
get
(naturally enough) a list of the <TT>Indices</TT> on the <TT>Customer</TT> table!
This is a great feature, and you should use it whenever possible. Figure 12.7 shows
the expanded nodes of the <TT>Indices</TT> for the <TT>Customer</TT> table. (The

program kdAddExplore in the <TT>Chap14</TT> subdirectory on the CD-ROM that accompanies
this book uses the <TT>TSession</TT> object to do the same thing in a BCB program.)</P>
<P>While you have the Explorer open, you should also expand the 
<TT>Fields</TT> node,
as shown in Figure 12.8. This gives a quick list of all the fields and their types.
Notice that you can drag and drop individual fields onto a form.</P>
<P>A third way to get a look at the structure of a table is through the 
Database
Desktop (DBD). You can open this program from the Tools menu in C++Builder. Use the
File menu in the DBD to set the Working Directory to the <TT>BCDEMOS</TT> <TT>Alias</TT>.
Open up the <TT>Customer</TT> table and choose the Table | Info 
Structure menu choice.
Drop down the Table Properties combo box and look up the secondary <TT>Indices</TT>,
as shown in Figure 12.9. The primary index is designated by the asterisks after the
keyed fields in the Key Roster. In this case, only the 
<TT>CustNo</TT> field is starred,
because it is the sole keyed field.
<H6></H6>
<P><A NAME="Heading25"></A><A HREF="12ebu07.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu07.jpg">FIGURE 12.7.</A><FONT COLOR="#000077">
</FONT><I>The <TT>Indices</TT> of the <TT>Customer</TT> table viewed in the 
Database
Explorer.<BR>
<BR>
<A NAME="Heading26"></A></I><A HREF="12ebu08.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu08.jpg">FIGURE 12.8.</A><FONT COLOR="#000077">
</FONT><I>The Fields view of the <TT>Customer</TT> table from the Database Explorer.<BR>
<BR>
<A NAME="Heading27"></A></I><A 
HREF="12ebu09.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu09.jpg">FIGURE 12.9.</A><FONT COLOR="#000077">
</FONT><I>The Database Desktop struts its venerable features by displaying the <TT>Indices</TT>
on the <TT>Customer</TT> table.</I>

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Over time, the Database Desktop
	will probably be replaced entirely by the Explorer. However, there are still some
	things that the DBD does better than the Explorer, so both products are shipped with
	
C++Builder. 
<HR>


</BLOCKQUOTE>

<P>Notice the Save As button on the Info Structure dialog. You can use this to save
a table that contains the structure of the <TT>Customer</TT> table. You can then
print this out on a printer using 
<TT>TQuickReports</TT>. Be sure to use a fixed-size
font, not a proportional font:</P>
<PRE><FONT COLOR="#0066FF">Field Name          Type   Size Key

CustNo              N           *

Company             A      30

Addr1               A      30


Addr2               A      30

City                A      15

State               A      20

Zip                 A      10

Country             A      20

Phone               A      15

FAX                 A      15

TaxRate             N

Contact             
A      20

LastInvoiceDate     @

</FONT></PRE>
<P>In the example shown here, I have printed out only the first four fields of the
table because of space considerations. (The fields are <TT>Field Name</TT>, <TT>Type</TT>,
<TT>Size</TT>, and 
<TT>Key</TT>.) If I then recursively print out the structure of
the table used to house the structure of the <TT>Customer</TT> table, I get the following
report:</P>
<PRE><FONT COLOR="#0066FF">Field Name          Type   Size  Key

Field Name          
A      25

Type                A      1

Size                S

Key                 A      1

_Invariant Field ID S

_Required Value     A      1

_Min Value          A      255

_Max Value          A      255

_Default Value      A      255

_Picture 
Value      A      176

_Table Lookup       A      255

_Table Lookup Type  A      1