ch16.htm

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

<P>To get started backing up a database, sign on to the InterBase Server Manager.
To sign on, all you need to do is specify the <TT>masterkey</TT> password; everything
else is automatic when signing on to the local version of InterBase. Of 
course, if
you changed the <TT>SYSDBA</TT> password from <TT>masterkey</TT> to something else,
then you need to use the new password you created.</P>
<P>Go to the Tasks menu and select Backup. Enter the path to the local database you
want to back up. 
For instance, you might type <TT>e:\data\info.gdb</TT> in the edit
control labeled Database Path. This means you want to back up the database called
<TT>info.gdb</TT>.</P>
<P>Enter the name of the backup table you want to create in the Backup File or 
Device
field. For instance, you might type <TT>e:\data\info.gbk</TT>. Use the <TT>GDB</TT>
extension for live tables and <TT>GBK</TT> for backed-up tables. These are just conventions,
but they are good ones.</P>
<P>Select Transportable Format from the 
Options group box and set any other flags
you want to use. Click OK, and then be prepared for a short delay while InterBase
contemplates certain knotty passages from the works of the philosopher Immanuel Kant.
If all goes well, the results of your 
work might look something like this:</P>
<PRE><FONT COLOR="#0066FF">Backup started on Tue Dec 24 15:26:42 1996...

gbak: gbak version WI-V4.1.0.194

gbak:     Version(s) for database &quot;e:\data\info.gdb&quot;

    InterBase/x86/Windows NT (access 
method), version &quot;WI-V4.2.1.328&quot;

    on disk structure version 8.0

Request completed on Tue Dec 24 15:26:45 1996

</FONT></PRE>
<P>You can now close the InterBase Server Manager and copy your backed-up file to
a floppy disk, zip drive, or 
other storage medium. Remember, the great thing about
these files is that they are small, highly compressed, and can be moved from one
operating system to another.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>I exist so 
completely within the Windows
	world that I neglected to point out that InterBase runs on a wide variety of UNIX
	platforms. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The Music Program</FONT></H2>
<P>It's now time to begin work on the Music 
program. This program enables you to
keep track of CDs, records, tapes, and books. The main goal of the program is to
enable you to enter the name of an artist (a musician or a writer) and add one or
more titles associated with that artist.



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>The main table of this program is called
	Artist for historical reasons. The database was originally intended to hold only
	CDs, records, and tapes. I expanded the program's scope later 
when I suddenly saw
	a way to add the Book table to the project. You can now regard the Artist table as
	featuring a somewhat quaint conceit for a naming convention. Of course, it is difficult
	to undo the naming convention at this stage because the 
names of the Artist table
	and its fields are already spread throughout the program's code as well as the 12
	stored procedures that support the code. 
<HR>


</BLOCKQUOTE>

<P>The Music program uses eight tables, but three of them, called Artist, 
Book, and
Album, dominate the application. The Artist table is the master table, and the Book
and Album tables are detail tables.</P>
<P>Besides the three main tables, several lookup tables are used to store the various
lists of possible categories to 
which the albums and books can belong. For instance,
a record can be type Jazz, Rock, Folk, Blues, and so on, and a book can be type Fiction,
Computer, Mystery, Science Fiction, Reference, and so on. These words are stored
in the lookup tables. In 
some cases, I can store information used by both the Album
and Book tables in one lookup, using a filter and the range of the table's primary
key to distinguish between the different groups of information. More on this technique
later in the 
chapter.</P>
<P>Even with this relatively simple structure, however, there are still enough tables
to provide some food for thought. In particular, how are these tables related, and
how can you put constraints on them so that it's difficult for the 
user to accidentally
break a dependency? For instance, if there are six albums associated with an artist,
a user should not be able to delete the artist without first deleting or reassigning
the albums. How about generating the IDs for each artist and 
each album? This is
not Paradox, so there is no autoincrement field. This means that you must create
generators and employ some means of accessing the generators.</P>
<P>Clearly, there are enough questions to keep someone busy for an hour or two. To

resolve these issues, you need to generate a specific database schema.
<H2><FONT COLOR="#000077">Creating the Database Schema</FONT></H2>
<P>It's probably best to start your work at the top with the Artist table:</P>
<PRE><FONT COLOR="#0066FF">/* 
Table: ARTIST, Owner: SYSDBA */

CREATE TABLE ARTIST (CODE CODE_DOM NOT NULL,

        LAST VARCHAR(30),

        FIRST VARCHAR(30),

        BORN DATE,

        DIED DATE,

        BIRTHPLACE VARCHAR(35),

        COMMENT BLOB SUB_TYPE TEXT SEGMENT 
SIZE 80,

        ARTISTTYPE INTEGER NOT NULL,

        PRIMARY KEY (CODE));

</FONT></PRE>
<P>The definition for this table assumes the presence of a domain called <TT>CODE_DOM</TT>.
You can create a domain in WISQL with the following code:</P>

<PRE><FONT COLOR="#0066FF">CREATE DOMAIN CODE_DOM AS INTEGER;

</FONT></PRE>
<P>This code states that <TT>CODE_DOM</TT> is a domain of type <TT>Integer</TT>.</P>
<P>A domain is an alias for a type that is used more than once in the program. For

instance, the <TT>Code</TT> field used in the Album table is referenced in the Album
table in the <TT>GroupCode</TT> field:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE ALBUM (CODE CODE_DOM NOT NULL,

        ALBUM VARCHAR(25) NOT NULL,

        TYPES 
SMALLINT,

        LOUDNESS SMALLINT,

        MEDIUM SMALLINT,

        RATING SMALLINT,

        GROUPCODE CODE_DOM NOT NULL,

        PRIMARY KEY (CODE));

</FONT></PRE>
<P>Make sure you understand what is happening here. The <TT>GroupCode</TT> 
field
in the Album table references the Group, or Artist, associated with this particular
album. For instance, if Bob Dylan's code is 57, and the name of the current album
is Blonde on Blonde, the <TT>GroupCode</TT> field in the Album table is set to 
<TT>57</TT>.
This ties the album Blonde on Blonde to the artist Bob Dylan. (Properly speaking,
this is a foreign key, but I get to that subject as it applies to InterBase later
in the chapter.)</P>
<P>Creating a domain called <TT>CODE_DOM</TT> allows 
you to easily assign the same
type to the <TT>Code</TT> field in the Artist table and the <TT>GroupCode</TT> field
in the Album table. It's not earth-shattering in importance, but it can be helpful.
<H3><FONT COLOR="#000077">Altering Tables: To Null 
or Not to Null</FONT></H3>
<P>Notice that the <TT>Code</TT> field is declared as <TT>Not Null</TT>. This means
that the user cannot leave this field blank. This rule is implemented by the server
and is enforced regardless of which front end you use to 
access the data. By definition,
all primary keys must be declared <TT>Not Null</TT>.</P>
<P>The <TT>ArtistType</TT> field in the Artist table is declared as <TT>Not Null</TT>.
All artists must be distinguished by type; that is, they have to be labeled 
as either
Authors or Musicians. If they don't fit into one of these two categories, then they
are never seen by the user because I set up a filter on this field, excluding all
but the one type that the user currently wants to see. In short, the table 
is filtered
to show either only musicians or only authors. If an entry in the Artist table does
not fit into one of these two categories, then it is never seen by the user. As a
result, I declare this field as <TT>Not Null</TT> and then use a lookup 
table to
give the user only two choices when filling it in. This way, I am sure that no records
are lost.</P>
<P>Deciding which fields should get the value <TT>Not Null</TT> is one of the more
difficult chores in creating a database. This is one of 
those designations that I
almost never call right in design mode. Instead, I am forced to go back and massage
my data after creating a first draft of the data definition.</P>
<P>To change a table using WISQL, you must call an SQL command called 
<TT>Alter Table</TT>:</P>
<PRE><FONT COLOR="#0066FF">ALTER TABLE MYTABLE

ADD NAME VARCHAR(25),

DROP NAMES;

</FONT></PRE>
<P>This code adds a field called <TT>NAME</TT> to a table and drops a field called
<TT>NAMES</TT>. You don't have to add and 
drop fields at the same time; for instance,
you can write</P>
<PRE><FONT COLOR="#0066FF">ALTER TABLE MYTABLE

ADD NAME VARCHAR(25)

</FONT></PRE>
<P>You can also write</P>
<PRE><FONT COLOR="#0066FF">ALTER TABLE MYTABLE

  DROP NAMES

</FONT></PRE>

<P>Because you often alter the structure of an existing table, make sure you run
many tests on your program before entrusting a large amount of data to your tables.</P>
<P>You cannot alter a field that is part of a unique index, primary key, or 
foreign
key, nor can you drop a unique index, primary key, or foreign key. You can, however,
drop a standard index:</P>
<PRE><FONT COLOR="#0066FF">drop index myindex

</FONT></PRE>
<P>When you start altering tables, you soon need to transfer the 
values from one
field to a new field. In other words, you want to slightly alter the traits of one
field. The rest of this section outlines a simple technique for altering a table.</P>
<P>To get started, I will create a simple table that can serve as 
a scratch pad.
All the work shown here was done with the WISQL32 utility that ships with InterBase
in the <TT>BIN</TT> directory. I always keep WISQL on the Tools menu of my copy of
BCB:</P>
<PRE><FONT COLOR="#0066FF">create table foo (sam Integer not 
null, Name VarChar(30), primary key (Sam));

insert into foo (Sam, Name) values (1, &quot;Fred&quot;);

insert into foo (Sam, Name) values (2, &quot;Sam&quot;);

insert into foo (Sam, Name) values (3, &quot;Joe&quot;);

</FONT></PRE>
<P>The four lines 
shown here create a table called Foo and place some simple values
in it. WISQL lets you use the <TT>Previous</TT> and <TT>Next</TT> commands so you
can easily alter the <TT>insert</TT> command without retyping it each time.</P>
<P>After creating the 
table, I can easily test the data:</P>
<PRE><FONT COLOR="#0066FF">select * from foo

        SAM NAME

=========== ==============================

          1 Fred

          2 Sam

          3 Joe

</FONT></PRE>
<P>Suppose that I now decide I want to 
change the <TT>Name</TT> field to be <TT>Not
Null</TT> and somewhat longer. How do I proceed?</P>
<P>The first step is to create a new field with all the traits in it that I want:</P>
<PRE><FONT COLOR="#0066FF">alter table foo

  add AName Varchar(50) 
Not Null;

</FONT></PRE>
<P>Now the table has a field called <TT>AName</TT> that is longer than the <TT>Name</TT>
field and declared <TT>Not Null</TT>.</P>
<P>To copy the data from the <TT>Name</TT> field to <TT>AName</TT>, issue the following
command 
in WISQL:</P>
<PRE><FONT COLOR="#0066FF">update foo

  set Aname = Name;

</FONT></PRE>
<P>Here is how things stand at this point:</P>
<PRE><FONT COLOR="#0066FF">select * from Foo;

        SAM NAME                           ANAME

=========== 
============================== =========

          1 Fred                           Fred

          2 Sam                            Sam

          3 Joe                            Joe

</FONT></PRE>
<P>You can simply delete the <TT>Name</TT> 
column:</P>
<PRE><FONT COLOR="#0066FF">alter table foo

  drop name;

</FONT></PRE>
<P>Now you have a table that looks like what you want:</P>
<PRE><FONT COLOR="#0066FF">select * from foo;

        SAM ANAME