All the procedures described in this chapter will be found on the Utilities submenu of the iconbar menu, enabling you to examine and alter the structure of an existing database. Note that, if passwords have been set, you need to enter the database with the "Manager" level password to obtain access to this submenu.
The primary key of a database is determined when it is created but is not fixed for all time. The New primary key choice displays the same dialogue box as was used for creating the primary key in the first place, but with the addition of an extra group of buttons, whose function is described below. The present key structure is shown. Simply alter it to what you require and click on Create or type Return.
This facility can also enable you to rescue a database whose primary key index has been lost or corrupted. Whenever a database is opened information about the structure of the keys is saved in a file called Keydata. Another file, called opening records the subfiles in which records reside. A similar file, closing is saved when the database is closed. All these files are in a subdirectory Recover inside the database directory. Even if you delete the index file called PrimaryKey these stored files will normally help you to put everything right. When you try to open the database a message will direct you to the Utilities menu and, on choosing New primary key, you will see the window displayed with the key structure already entered. Clicking Create will then restore your PrimaryKey file with the records in the correct subfiles
The group of buttons at the bottom of the window allow you to recover records you have deleted as long as the deletions were not performed with the "Blank record on deletion" button selected in the Preferences window. In that case the records are gone for good, but if the option wasn't selected (i.e. the default case) then the data is still present in the Database file but the indexes know nothing about it. Select Retrieve if possible and click Create. Any recovered records will be placed in a separate subfile, normally subfile 15 but the bump icons allow you to change this. It might be a good idea to keep subfile 15 free for this special purpose unless you really need all 16 subfiles for your data. Please not that if you are using subsidiary indexes they will need rebuilding after retieving deleted records in this way.
The best way to acquaint yourself with the way things work is to experiment. Suppose we have a database called !Addresses. When you choose Alter format the following things happen:
During the foregoing activity Powerbase will have been keeping track of certain things which it needs to know so that the changes can, if necessary, be undone and it will now inform you of changes which might result in the modified database containing less data that the original. Deleted fields are an obvious example, but you will also be warned about shortened fields and Scrollable lists with fewer or narrower columns than in the original. If you choose to proceed despite these warnings the restructuring will take place and you should end up with exactly what you asked for but the !Undo_01 directory will now contain copies of any files which have been changed by the reformatting. The addition to !Undo_01 which you will most commonly see is a copy of your original Database file, but there might also be copies of the Indexes directory or of directories holding External field and Scrollable list data.
One file which definitely will be there is an Obey file called !Restore. You might like to look at this very important file, which contains the instructions for putting everything back as it was.
Wish you'd left well alone? Want to go back? Just double-click on !Undo_01. All your renamings will be reversed and all the saved original files copied back. The restored database, which should look exactly as it did when you started, is then re-opened for you and !Undo_01 finally deletes itself!
Why is the "undo" application called !Undo_01, not simply !Undo? You will see why if you start another session of alterations beginning where you previously left off, that is by again choosing Alter format without having run !Undo_01 which is still sitting there in the Addresses! directory. A new application directory called !Undo_02 is created. A third alteration session will result in !Undo_03 and so on. This allows you to change your databases a little at a time, assessing the effect of each alteration before proceeding to the next. The most recent alteration session is undone by running the highest-numbered undo application, the penultimate session is undone by running the next-highest etc. If, after a lengthy series of alterations, you want to go right back to the beginning the undo applications should be run in reverse order. This can be done by double-clicking the undo applications but a better way is described below. Do not simply run !Undo_01 because it only "knows" about what you did in the first session and the database structure might by now be very different.
Immediately underneath the Alter format entry on the Utilities submenu is one called Revert to previous. This only becomes available when you have used Alter format; until then it is shaded. What it does is run the highest-numbered undo application for you, after first asking for confirmation and quoting the date-stamp of the application. Since an undo application deletes itself once it has done its work, repeated use of Revert to previous will ensure that the correct order is adhered to. When no more undo applications remain the menu entry is shaded once more.
The only slight disadvantage to using Revert to previous is that it can only be used when the database is open. Double-clicking the undo applications directly will restore the earlier formats even if Powerbase isn't running at all.
Two databases may be merged provided they have identical record structures. This means the order and specification of fields, including the maximum length of each field, must be the same in both. With the first database open, choose Merge databases which opens the Merge databases window. Drag the second database to this window and drop it on the large down-pointing arrow. The pathnames of both databases can now be seen. The writable icon labelled Save as shows the default pathname of the merged database which is the same as that of the open database with the letter M appended. (If the leafname of the open database already has the maximum permitted length it is shortened by one letter before appending the M, as shown in the example.) The open database and the second one will be merged into this new database leaving both original databases unchanged. You may if you wish delete the M making the pathname identical to that of the open database; the data from the second database will be merged into the open one. You can, however, delete the whole pathname, enter a new leafname and drag the database icon to a directory window. When you click on the Merge databases button Powerbase will check that the two record formats are identical and report an error if they aren't. An option button determines the action taken with regard to Sequence number fields (see also 8.4.1). If no such field is present this button does nothing.
The number of available records in a database may be increased or decreased. Change length leads to a window with two writable icons. The first specifies the new database length, the other determines the number of records by which the database will be lengthened each time it becomes full. If this value is zero no automatic lengthening occurs; a warning is displayed instead. You will only be allowed to shorten a database if the surplus records have never been used or have been blanked on deletion. Otherwise you can only get rid of them by exporting all the current records as a subset (see 13.2)
The self-balancing index structure first introduced in Powerbase 9.70 (see Appendix C) renders this utility far less important than it was. However, you can still use it to examine index trees. You may even try to improve them by balancing from the Indexes submenu although any improvement wrought in this way is likely to be small.
View index displays a window which lets you examine the structure of any index to find out how many keys are present in each level. The ideal numbers in a perfectly-balanced index tree are 1, 2, 4, 8, 16, 32, 64, 128 etc. (i.e. powers of 2, beginning with 20.)
The index to be examined is selected by clicking on the "bump" icons. Results may be obtained for the current subfile only (default) or for all enabled subfiles by selecting the appropriate radio button. You may list only the total numbers of nodes in each level (with the maximum ideal values beneath for comparison) or show the actual keys, positioned according to the level they occupy in the tree. If the latter option is selected by clicking Complete tree the remaining two radio buttons are enabled permitting two alternative display formats.
Having selected the required options click Display. If Complete tree has been selected the index tree is displayed sideways in a window and, if Root first is selected, the root of the tree will be in column 0 on the first line and its right "child" in column 1 on the next line and so on. If Symmetrical is selected the root will appear about halfway down the window. This format is especially suitable for small databases where the structure of the whole tree can be quickly grasped. (Try it with Friends.) The output may be saved from the window or printed out (see 3.1.1) in the same way as any other report. If you have displayed the complete tree double-clicking with SELECT on any key will retrieve the associated database record.
It is possible to make Powerbase balance the index automatically at regular intervals, although with the new index structure this should not be necessary. To turn on auto-balancing choose Preferences from the iconbar menu. Select the Balance every <n> records button, placing your chosen value of n in the writable icon provided, then click on Accept.
The main menu is headed by the Subfile control entry which displays a window giving the numbers of records available and used, and the way the latter are distributed amongst the subfiles. The subfile numbers are on action buttons which may be used to move to a chosen subfile. Tick boxes on the right of the scrolling pane are used to enable and disable subfiles List creates a report of the information in this window with the addition (in the header) of the record length and number of fields. The report may be saved as a text file in the usual way.
Field data on the iconbar Utilities menu lists the field number, class, type, data-length, tag and descriptor for each field in the record. You may print this report out (see 3.1.1). If you move the Report window out of the way so that the record window is visible you will find that double-clicking on a line in the report places the pointer over the middle of the field. This can be useful when finding your way around a complex record where many fields are without descriptors.
Wherever possible a primary key should be chosen so as to be unique. Where repetition of the primary key might occur the designer of the database can decide either to allow or forbid it (see 11.2.1). If repeated keys are permitted it is sometimes useful to have a list of them. Such a list is created by Find repeated keys and may be saved as a text file or printed out (see 3.1.1). Subsidiary keys may be repeated and will sometimes occur many times. They too may be found with the same menu choice.