Using Powerbase — Part 7

by Derek Haslam

Our subject this month concerns some of the many choices which are available to determine how Powerbase works, and also how non-standard features may be added. When preparing Article 6 I quite overlooked the fact that it would appear on a compilation disc: the different structure of the disc meant that none of the links in the article worked! Sorry about that. (Whoops that will actually be my fault - ED).I presume readers who are following this series located everything referred to all right. Since I have received no requests for further enlightenment this will be the last Powerbase article in RISC World.

The latest version of Powerbase, PB918SH, has further changes and you should upgrade your copy. The documentation, now fully compliant with this latest version, is also supplied in four formats (Impression, Ovation Pro, PDF and HTML)

Control buttons

Clicking ADJUST on the Powerbase icon on the iconbar opens the Preferences window. We won't be discussing every single option in this window - many are pretty self-explanatory anyway - but it's worth a reminder that every one of them has a message which will be displayed by the !Help application.

In the section labelled "Control" three radio buttons give you the choice of using the tool-pane (default), the alternative keypad, or neither. Their effect is immediate: no need even to click the Accept button. The main difference between the tool-pane and keypad is that the latter has pairs of buttons to perform opposite functions such as next record and previous record, whereas the tool-pane has just one. Since ADJUST is consistently used on the browsing buttons to reverse the action of SELECT, there is no loss of functionality when using the tool-pane, but anyone who is an infrequent RISC OS user may be unfamiliar with this use of ADJUST. A 'Windows' user said to me recently "I know how to go forward to the next record but how the heck do I get back?" Such users will find the keypad more intuitive.

The dual function of many of the tool-pane buttons may be emphasised by selecting New sprites which replaces the unidirectional arrows of the regular sprites with pairs of left and right-pointing arrows. The option button is deselected and greyed out when the keypad is selected since there is then no point in using the alternative sprites. Since a right-pointing arrow is used to mean "next" and a left-pointing one to mean "previous" some users might like to make the left and right mouse buttons reflect this by reversing their normal function. Rev(erse) mouse does this, and changes one of the button sprites from blue to red as an indication. This feature was suggested by a long-standing Powerbase user and I have come to prefer it myself. Fig.1 illustrates the relevant part of the tool-pane with the standard sprites and with the alternative set and reversed mouse-buttons selected.

Whatever choices you make will affect the current work session only, unless you Save choices, of which more will be said later.

Two disadvantages of the keypad in earlier versions of Powerbase are firstly it is easy to lose it behind the record window (although pressing Tab will bring it to the front again) and secondly, if you are running two copies of Powerbase with two databases open, you can get the two keypads mixed up. v.9.18 overcomes these little problems by enabling you to attach the keypad to the record window. To do this click MENU over any keypad button (which opens the Key equivalents window) and select Attach keypad then click Close. The keypad may still be moved around freely and toggled to its reduced size just as before but it will follow the record window when the latter is moved and always stays in front. You must Save choices in the Key equivalents window if you want the keypad to be attached next time you open the database.

Choosing Neither tool-pane nor keypad does not prevent you from using the function keys to browse, search etc. and is really intended for users who prefer to have all their control buttons on the record window itself. In article 2 we added Quit, Exit and a Menu button to !MusicBase. Any of the buttons on the keypad or tool-pane may be placed on the record window in the same way, i.e.

Choose Alter format from the iconbar Utilities submenu.
Click MENU over the blue grid (not over a pre-existing field) and choose Create field
Select the radio button labelled Keypad button or Tool-pane button. (The labelling depends on what you have selected in Preferences.)
Choose the required button from the field-type menu and click Create.
When you have added the buttons you want and dragged them into position choose Quit design from the main menu.

If other people use your database and you want to give them restricted facilities, such as being able to browse and search, but not add or delete records, placing the appropriate buttons on the record window and turning off the keypad/tool-pane is an obvious way to do it. By itself, however, this isn't good enough because the "restricted" user can easily make him/herself into an unrestricted one by opening the Preferences window and enabling the keypad/tool-pane! To prevent this you need to password protect your database. In what follows the term "keypad" should be understood as referring to the keypad or tool-pane.

Restricted access and password protection

Choosing Miscellaneous=>Set passwords from the main menu opens the Password window (Fig.2)

Access to this window is only possible if either no passwords have been set or if the user opened the database with the Manager password. For the present enter "read" and "write" respectively in the first two boxes and your own name (or anything you fancy) in the third. You can actually see the two lower level passwords but the Manager icon shows only a line of hyphens. Click Set and close the database.

When you try to re-open the database you are first presented with with the Access window which requests a password. Try entering "rhubarb" followed by Return or clicking Open. The message Password not known is displayed, followed by a request to enter the password again. Three such "fails" throw you out altogether and remove Powerbase from the iconbar. Enter "read" and the database opens but some of the keypad control buttons are greyed out. You'll find you can move around the database and retrieve individual records but you can't alter anything. The caret can't be placed in the fields and many of the menu choices on both main and iconbar menus are barred to you. Close the database and re-open it with "write". This time things are (superficially) as you're accustomed to seeing them, but most of the iconbar Utilities submenu is still unavailable so you can't rename the database, alter its structure or redefine its primary key. Also, crucially, you can't get at the Password window. If you open the database with whatever you set up as the Manager password then all facilities are open to you, including the ability to alter the passwords.

As you will have noticed, the Password window has quite an extensive list of features which you may enable or disable. Since only a Manager password will let you into the Password window, any features you decide to disable will be protected from re-enabling by lower-level users. The first of these features is the keypad. Deselect this button and click Set. The keypad disappears just as it does when you select Neither in Preferences. The difference, however, can be seen if you open the Preferences window. The whole Control section is greyed out.

So, if you wish to restrict the way your database is used by others you have the following facilities:

Set passwords and only divulge one of the lower level ones.
Disable things you don't want anyone else but yourself using by deselecting buttons on the password window.
If allocating a Read password doesn't restrict keypad use as much as you wish, disable the keypad altogether and place such buttons as you do want available on the record window.

No doubt you've noticed the greyed out Access button at the bottom of the Password window and wondered what it's for? It opens up a whole new system of password protection in which individual users can be allocated their own personal login names and passwords which may be set at any of the three levels (Read, Write or Manager) previously referred to. This gives much better security but is not something to describe in an open article like this. If anyone is interested in using this feature please email me for further instructions.

Automatic index balancing and saving

Because of the way Powerbase stores keys in its indexes it isn't a good idea to enter records in key order, especially primary key order. If you do so the "tree" structure of the index becomes degraded to a mere linear list which takes longer to search when retrieving a record and is also slower when inserting or deleting records. Even if the records are entered in a more-or-less random order with regard to key the index tree can become very lopsided. That's why there is a utility to balance the tree to improve the efficiency of search, insert and delete operations.

There are times, however, when it is inconvenient or almost impossible not to enter records in key order. If you are presented with a pile of several hundred forms with someone's name on each, and the forms have been tidily alphabetised by name, you'd get some funny looks (to say the least) if you threw them in the air and gathered them up in random order "because the computer prefers them that way"! You can always pause every couple of dozen records to balance the index using the choice from the Utilities submenu. In fact typing Ctrl-B will do the same thing, but it's nicer not to have to bother about it at all. This is where you might want to select Balance every [n] records. [n] is set by default at 25 but you can enter any number you wish. With auto-balancing enabled you will find that Powerbase pauses every now and again, ignoring your keystrokes, while it balances its indexes.

Whatever choices you make in the Preferences window will only be remembered next time you use the database if you have saved them. Save choices saves them in a text file called Preference and gives you the option of saving them inside the database (in which case they apply to that database only and different databases can have different saved Preference files) or in Powerbase itself. If you choose the latter the choices will affect all databases except those which have their own Preference file. A "private" Preference file saved inside the database directory always takes precedence over the one in Powerbase.

There is a third radio button on this section of the window which is relevant only if you are using RISC OS Select 4.33 or later and have the operating system's Users facility enabled. This provides a separate directory for each user inside the !Boot application where programs can save the user's personal choices. The third radio button on the Preferences window (and on some other windows) will then incorporate the name of the logged-in user in its labelling. Selecting the button causes the Preference file to be saved there. The priority level of such a file falls between that of a file saved in a database and one saved in Powerbase. Thus, when loading a Preference file the sequence of testing and selecting is as follows:

Does this database have its own Preference file? If so, load it.
If there is no database-specific Preference file, is there one for the exclusive use of the logged-in user? If so, load that.
Failing either of the above, load the file in !Powerbase.Resources

This raises the question of what happens when you click the Load default button. If simply clicked with SELECT it reloads the same file that was loaded on opening the database, i.e. it looks for a file using the priority order shown above. You can, however, override this by Shift-clicking. If there's a user-specific file then that will be loaded, even if there is also a Preference file in the database. Ctrl-click forces the file from !Powerbase.Resources to be loaded. In extreme circumstances (where you've got the Preference file in Resources into an unholy mess and wished you'd left it alone!) you can use Alt-click to load the one from !Powerbase.Initial. This is the "factory default" (like all other files in Initial) and should not be altered.

Using Query-by-example

The default method of targetting records to include in a report is by entering a search formula which describes the records required in terms of the contents of fields. Article 3 contained several examples of search formulae and readers are referred to that article to refresh their memories if necessary. If your query requirements are very simple you might prefer the alternative method: query-by-example.

Load the MusicBase database we worked on in earlier articles. If you don't have it extract it from the zip file Music. Open the Preferences window and select the Query by example button. Now click Print on the tool-pane. The Match window appears as usual but the writable icon where you would normally type your search formula is greyed out. Then you notice that the record has become blank! Suppose we want a list of all music on CD. Select the fields for printing by ADJUST-clicking in the usual way, then enter "CD" in the Medium field and click Print on the Match window or press the Print key on the keyboard. The list produced is exactly the same as if you had entered the search formula MED=CD. You can use any number of fields in this way. Close the report window and click Print on the tool-pane again. Enter "LP" in Medium and "PS" in Category. This time the report lists all solo piano music on LP. The principle is very simple: just fill in the fields in which you're interested with the data you want matched - the text in the window title tells you to do just that. It's not as flexible or as powerful as using search formulae (you can't use it to match data in a Scrollable list for one thing), but it's very intuitive and doesn't require you to remember or look up the tags of fields.

Adding non-standard features to Powerbase

The ability to add non-standard features arose out of the original use of Powerbase for maintaining student records in an F.E. college (see "Origins" in Article 1 of this series). Some one said how nice it would be if one could print personal timetables for students and obtain a list of class totals for all subjects. These features were already provided by another program which reads a Powerbase database and does fancy things with it, but it was inconvenient to have to run the latter program every time a student came along with a timetable change. Powerbase itself, on the other hand, was always running with the student database open, so could I please make it offer these handy facilities? It was really just a matter of lifting some of the code from the timetabling program and incorporating it into Powerbase but I didn't want specially-doctored versions of Powerbase around the place. They'd have to be updated separately every time I made changes to the standard program.

Eventually I developed a method of providing these features without modifying Powerbase, and it is that which is now to be demonstrated and explained. The method involves placing a new subdirectory inside the database directory. This subdirectory must always be called Customise and contains all the files needed to implement the new features. The minimum requirement is a Basic file whose name is specific to the database with which it will be used.

Our first demonstration uses the Elements database and the feature we will add is one to print out the elements group by group in parallel columns, i.e. Group 0 (the noble gases) in the first column, group 1 in the second column etc., ending with a column listing the actinides. To try this you will need the database and its appropriate Customise folder. These are in zip files on the CD and may be readily obtained by clicking the links Elements and ElemCust. Be sure to extract them from the zip files before attempting to use them!

Run Powerbase and open Elements, just to remind yourself what the non-customised database looks like. Note especially that the tool-pane ends, as usual, with the four grey "book-mark" icons. Close the database (no need to quit Powerbase), open the database directory by Shift/double-clicking, and copy the Customise directory into it. Now re-open the database. If Powerbase and Elements aren't in the same directory you will at this point see a message recommending that they should be. You may dismiss this message and ignore it for the present. Look at the bottom of the tool-pane and you should see a new button called Groups. Clicking this button creates the specialised report described above and you may save it as a text file in the usual way by clicking MENU over the report window.

Before we delve into details of how this is achieved you'd probably like to see the second demonstration. This uses a modified version of the Music database called MusicDemo. It contains a subset of my own, large, database of recorded music. For the demo I have selected BBC Music Magazine cover discs since they cover a wide range of composers and musical genres. Get the database from the link MusicDemo and the Customise directory from MusicCust. Take care not to confuse this Customise directory with the one for Elements!

Open the MusicDemo database, which contains 114 records, noting once again that the tool-pane is quite normal. Now close it, copy Customise into the database directory, and re-open the database. This time there are three new buttons at the bottom of the tool-pane as in Fig.3:

Clicking the Disc button lists the contents of disc BBC MM44, since that is the disc which appears in the Cat.No. field. Move on a few records until one is reached with a different catalogue number and click Disc again. This is a genuinely useful feature. Whenever you retrieve a record and are wondering what else (if anything) is on the same disc as the displayed work all you have to do to find out is click the Disc button.

The "menu" button produces, as you'd expect, a pop-up menu. The music in the database is classified as Orchestral, Chamber music, Instrumental (solo), Choral & Song, and Opera. Choosing one of these categories from the pop-up menu lists all matching items in the database. Thus, choosing Opera lists all works which have "Opera" in the Type field. The little blue "window" button opens a window - surprise, surprise! This is not, however, one of Powerbase's own windows; it's one specific to this database's Customise directory (Fig.4).

It enables you to list all the works by a specified composer. Try entering Beethoven and either clicking List or pressing Return. It also provides an alternative means of listing the contents of a single disc - any disc, not just the one referred to in the displayed record. Select the Catalogue number radio button, enter BBC MM126 and click List. Having developed these customised features for demonstration purposes I've decided I like them well enough to use them in my own music database!

You can hop back and forth between Elements and MusicDemo; the appropriate custom buttons for each database will appear on the tool-pane and the special features will work. If you switch to the keypad as described at the beginning of the article the custom buttons will be missing but will appear if you quit Powerbase and re-run it. (You might need to click the Toggle-size icon on the keypad a couple of times before correct operation is achieved.)

Customisation uses memory most efficiently when all customised databases are in the same directory as Powerbase itself, which is why an advisory message is displayed when you first open a database in a different directory. When Powerbase is run it searches whatever directory it is in for customised databases and allocates space for the biggest Basic file it might need to load. The last entry (Libraries) on the iconbar Utilities submenu will list all customisation files Powerbase knows about. Double-clicking an entry in this list opens the relevant Customise directory so that you can edit its contents.

So how does it all work?

Here comes the steepest part of the Powerbase learning curve! What follows requires some knowledge of programming in Basic and you should feel free to skip this section if the idea doesn't appeal to you.

Open the Customise directory for MusicDemo. It contains two objects: a Basic file which I've called MusicLib and a Templates file. The latter contains just the template for the non-standard window used to list works by composer or disc and there's really no more to be said about it. The MusicLib file is what does all the work and this should be loaded into your favourite editor for examination. A quick scroll through the listing will reveal several functions and procedures in which "MusicLib" forms part of the name. This is important, as will be explained below.

I have provided another Customise directory in CustDemo for readers who wish to experiment. If you examine this you'll see that its Basic file is called Demo and functions and procedures within it have "Demo" instead of "MusicLib" in their names. e.g. the function FN_MusicLib_select, which handles choices from the special menu, is called FN_Demo_select in the Demo file. If you decide to write a customisation file you are advised to start from Demo, which is liberally REMmed. Your first action should be to choose a suitable new name for Demo - one which won't clash with any other customised databases on your system - and rename Demo accordingly. Then load the file and replace all occurrences of the string "Demo" with the new file-name. If the function names and the filename aren't related in this way the new code won't work and will probably hang the computer.

It will also be immediately apparent that this isn't a complete Basic program since it actually begins with a DEF FN. It is in fact a library of functions and procedures many of which are called from Powerbase and which may, in turn, call functions and procedures within Powerbase. The first function in this customisation library is called FN_MusicLib_setup. There is always a function whose name is of the form FN_<filename>_setup, where <filename> is the name of the Basic library file itself. When a database is opened Powerbase checks whether there is a Customise directory and, if so, calls the appropriate setup function in the library.

One of the most important actions performed by the setup function is the creation of the custom buttons on the tool-pane or keypad. This is done by calls to FNadd_button (which is part of Powerbase). In this instance there are three calls to FNadd_button and it is important that the variable names holding the icon numbers of these added buttons aren't used by Powerbase for something else. Names which have "MusicLib" as part of their name are safe to use and you are advised to stick to such a pattern in any work of your own. I've called one icon handle "Discbutton%" because I know no such variable name is used by Powerbase, but other users wouldn't know that. For a detailed description of how FNadd_button is used (and for other information about customisation) you are referred to Ch 15 in the user manual.

Some of the actions in the setup function need to be performed each time the database is opened without quitting Powerbase. Others must be done only once and these are placed inside the IF new% THEN...ENDIF block. Dimensioning arrays is a common example, although none are used in this library. What we do have are five calls to FNfield (part of Powerbase again) to define variables holding the field-numbers of the fields whose tags are COMP (Composer), WORK, TYPE, DISC (Cat.No.) and TOTL (duration). Even if your own database has these five fields with different field-numbers things should still work provided the tags are as stated. If not this part of the library is easily changed. The template for the non-standard window is also loaded here.

The next function definition in the library is of FN_MusicLib_button which handles mouse-clicks on the custom buttons. A click on the blue "window" (MusicLibwindow%) opens the special window. (The fiddling with Wimp_GetWindowState, block% and PROCpostion_window here ensures that the special window opens just to the right of the tool-pane.) If the menu button is clicked the menu is created and displayed.

Clicking the Disc button directly calls PROC_MusicLib_list, which is at the heart of these special features. FN_MusicLib_select can also call call PROC_MusicLib_list directly, using the text from the menu choice itself (choice$(1)). FN_MusicLib_click is called whenever the Wimp informs Powerbase of a mouse-click but the window involved isn't one of Powerbase's own and therefore must be a custom window. There could be several such but there's only one in this instance and its handle is MusicLibW%. PROC_MusicLib_list is called from this function too when the List button is clicked. FN_MusicLib_press takes a sneaky short-cut by "faking" a click on the List button if Return has been pressed.

You are probably wondering about the apparently-useless FN_MusicLib_drop. It does absolutely nothing but return a value of TRUE, so why is it there? This is the function which would be called if you dropped a text-file (or a directory) on the custom window. We don't want any action taken in such an event - but we don't want Powerbase returning a "No such FN/PROC" error either. The function to respond to such file drops must always be present, even if there are no custom windows on which files might accidentally be dropped. This is because dropping a file on any of Powerbase's own windows for which there is no defined action (such as the tool-pane) will also result in a call to the library's drop function - and an error if it isn't present. The empty function just quietly returns TRUE and Powerbase is happy.

We will now move on to examine PROC_MusicLib_list itself. It takes three parameters. The first one, mode%, will have the value 0,1 or 2 depending on whether the request is for a list of a certain type of music, music by a specified composer, or the contents of a disc, and is mainly used to set up a sensible heading for the list. The second parameter (field%) is the number of the record field which is being examined and content$ is the target-value we are looking for. e.g. if we were listing all Orchestral music we would have mode%=0, field%=TypeField% (actually field 10, tag=TYPE) and content$="Orchestral".

PROC_MusicLib_start_list does what is necessary to set the list up with an appropriate width, top and left margins etc. (There is really too much detail in this procedure to go into here.) The first call to FNneighbour points address% at the first key in the index. The WHILE loop is then entered and performs the following actions at each iteration:

Get the record number by calling FNrec_no with the value of address%. (This function also returns the actual key as key$, but we don't need it here.)
Call PROCsimple_read which accesses the numbered record and loads it into the array F$() - one field into each element.
If the relevant field matches content$ assemble a string containing the composer's name (omitted if the request was for music by a named composer), the name of the work, and the duration. This string is then stored in the list buffer using a procedure in Powerbase.
Point address% to the next key in the index.

The loop terminates when address% points to a special node called the list-head whose address is in the variable top. The contents of the list buffer are then displayed. The composer's name (if present in each line) is forced to uppercase via FNu to make it stand out from the rest of the line.

Well, that's it for Article 7, and for the series as a whole. I hope some readers have found it useful and that it has given them an incentive to investigate Powerbase. Anyone struggling with a customisation library of their own is welcome to email me for help and advice.

Derek Haslam