list of chapters    previous chapter   next chapter

Ch 10 - Utilities


Ch 10 - Utilities

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.

10.1 Changing the Primary Key

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 two radio 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.

The first of the two radio buttons (Retain subfile structure) is selected by default, making the records appear in the same subfiles as before. Any subsidiary indexes, therefore, will still be valid. If you deselect this radio button with ADJUST (i.e. have neither button selected) then all the records will be placed in the currently selected subfile and you will need to rebuild your subsidiary indexes.

Selecting the second button (Restore deleted records) also places the records in the current subfile, but has the additional effect of recovering "deleted" records, i.e. records which are still lurking in the Database file but aren't in the primary key index. You would need to use this option when the primary key index is lost or corrupted. If the PrimaryKey file is actually missing both radio buttons are shaded in order to enforce this mode. Note that you cannot recover deleted records if the Blank record on deletion button is set in the Preferences window: the data has really gone for good in this case.

10.2 Altering the record format

v.9.20 of Powerbase introduces far-reaching changes in the way the record format is edited. Earlier versions caused confusion in the minds of many users by having two choices on the Utilities submenu which did similar things. Adjust format enabled minor changes such as moving fields about in the window, renaming fields and the addition or removal of External, Button, and Scrolling list fields. New record format was needed in order to make any change which required the Database file to be rebuilt, i.e. addition or removal of fields (other than those mentioned above), changing the length of fields, and swapping or renumbering fields. Users no longer need to decide whether an alteration will require a rebuild of the Database file since Powerbase can now make such a decision by itself. The two choices have therefore been replaced by a single menu choice: Alter format.

The process has been improved in other ways too. After changing the structure using older versions it was often found that self-calculating fields no longer worked until their formulae had been re-entered. Users were warned against changing both the tag and number of a field (otherwise data could end up in the wrong field). Sometimes indexes needed rebuilding. Under v.9.20 these annoyances should not occur. Close attention has been paid to providing the means to undo format alterations and revert to the database as it was before the changes were made (although common prudence suggests that you should always keep a backup copy of any database on an alternative medium such as another hard drive, a floppy disc or a CD ROM).

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 from the iconbar Utilities submenu the following things happen:

You can now go ahead and make changes ad libitum. Rename field tags if you wish, add new fields (including ones inserted before the last-numbered one in the record), delete redundant fields, lengthen or shorten fields, increase or reduce the number of columns (and the widths of columns) in Scrollable lists etc. etc. When you have finished gleefully messing up the previous record structure, bring up the main menu and choose Quit design.

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 !Undo01 directory will now contain copies of any files which have been changed by the reformatting. The addition to !Undo01 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 !Undo01. 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 !Undo01 finally deletes itself!

Why is the "undo" application called !Undo01, 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 !Undo01 which is still sitting there in the Addresses! directory. A new application directory called !Undo02 is created. A third alteration session will result in !Undo03 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 you should run the undo applications in reverse order. Do not simply run !Undo01 because it only "knows" about what you did in the first session and the database structure might by now be very different.

How many undo applications can you have? This depends on the configured maximum for auto-generated filenames. The value will be found in the Config file beside the token MaxNames and the default is 10. After !Undo10 you will be informed that this is the highest-numbered name and subsequent sessions will use (and keep overwriting) !Undo. Deleting some or all of the existing undo applications (starting with the highest-numbered) allows auto-name-generation to resume.

10.4 Merging two databases

Two databases may be merged provided they have identical record structures. With the first database open, select Merge database and drag the second database to the displayed window. The pathnames of both databases can now be seen. The pathname of the open database is duplicated in a writable icon labelled Save as. If you accept this default the data from the second database will be merged into the open one. You can, however, change this name and drag the database icon to a directory window. The merged data will then go into a completely new database leaving both source databases as they were. When you click on Merge databases 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).

Two radio buttons are also present, as in the dialogue box for rebuilding the primary key, and their functions are essentially the same (see 10.1). Having the first button selected merges records into the same subfiles as those they occupy in the second database. Having neither selected merges records into the current subfile of the first database, regardless of where they are in the second database. Having the second button selected does the same, but also imports records which were "deleted" in the second database.

10.5 Changing the Database Length

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)

10.6 Inspecting and balancing index trees

Print index lets you examine the structure of the current 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.)

A submenu gives you a number of options for printing the index details. You may print only the numbers of nodes in each level (with the ideal values listed above for comparison) or print the actual keys, positioned according to the level they occupy in the tree. The tree is printed sideways with a choice of two formats, Root first or Symmetrical. The data may be printed for all subfiles of the current key or for the current subfile only. Output is to a screen window from which it may be saved in the same way as any other report. If you have printed the keys themselves then double-clicking with SELECT on any key will retrieve the associated database record.

If the tree is very unbalanced and, especially, if there is an enormous number of levels with only 1 or 2 keys in each, you are advised to balance it using Balance index (Ctrl B) (Note 1). The totals in each level are displayed after balancing. If you initiate the process with Ctrl Shift B the full tree structure is printed after balancing.

It is also possible to make Powerbase balance the index automatically at regular intervals. 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.

Auto-balancing is most likely to be useful when a large number of records are being entered in primary-key order. This will happen if you are entering data from a lot of forms in alphabetical order of name where name is the primary key. It is also very likely to happen when importing CSV files as these are often ordered according to the data item which becomes the primary key field of the database record.

10.7 Useful database details

The main Powerbase menu is headed by the Information 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 names are on action buttons which may be used to move to a chosen subfile.) A Save button enables the information in this window to be saved as a text file.

Database details on the iconbar Utilities menu has its own submenu with two choices. Record data displays the same information as described above with a couple of useful additions: the record length in bytes and the number of fields in the record. Field data lists the field number, class, type, data-length, tag and descriptor for each field in the record. 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.

10.8 Finding duplicate primary keys

Wherever possible a primary key should be chosen so as to be unique. Where duplication of the primary key might occur the designer of the database can decide either to allow or forbid it (see 11.2.1). If duplicate keys are permitted it is sometimes useful to have a list of them. Such a list is created by Find duplicates and may be saved as a text file.

10.9 Libraries

Ch 15 describes how non-standard features can be added to a database by means of a customisation library. Where possible all customised databases should occupy the directory specified by CustDir in !Powerbase.Resources.Config. The Libraries choice on the Utilities submenu will list all the customisation libraries known to Powerbase at that time. The entry is shaded if none were found. Double-clicking on a line of the report opens the relevant Customisation directory for examination and editing.




Notes

1. A poor choice of key, with many duplications of just a few values, inevitably leads to a tree with many levels in which each level is sparsely populated. An attempt to balance the tree under such circumstances may give very little improvement. (back)



top of page   list of chapters    previous chapter   next chapter