|
|
 |
|
|
Advanced Database usage
In addition to controlling and accessing the Database Module from the command line, the database can also access scripting variables itself. While the user-interface was designed to hide some advanced options from new users, there are several ways to customize database views beyond the simple sorts and filters. In addition, you can set up additional columns that display computed results rather than just displaying database field values.
Formula Fields
A Formula Field is a database field whose value is calculated on the fly rather than being stored in the database. These fields can be added to views, sort order, filters, or anything else that a normal field can be. The main difference between a Formula Field and a normal field is that a Formula Field is read-only. None of the operations or commands that modify a field will work on a Formula Field.
To define a Formula Field, go to the Field mode and create a new field. Click the option labeled "Use Formula" and a new text box will be displayed for you to enter your formula. The formula should be a standard CMUD scripting expression. It can return a text or numeric result depending upon the field type that you set. For number fields you can also still select the formatting option that you want.
For example, most MUD weapons have a Damage field indicating the amount of damage the weapon normally does, represented by a dice roll such a 1d6 (roll one 6-sided dice) or 2d4 (roll 2 4-sided dice), etc. CMUD has several functions for manipulating dice rolls. A common attribute that you might be interested in is the average damage caused by the weapon. This can be computed using the %diceavg function. So, create a New field and turn on the Use Formula option. In the Formula box, enter:
%diceavg(&Damage)
and set it to be a numeric field. Now when you add this column to your Weapons view you will see the average damage caused by the weapon (times 100).
Internal Fields
Internal fields are created by checking the Internal option in the Field definition window. Internal fields are never returned as a result of a scripting command or function. They are basically hidden from the scripting language. The main use for Internal fields is for display-only formula fields. If the field is not marked as internal, the formula is computed whenever the record is accessed by the script. For complex formulas this can slow down database access. Set the formula field to Internal to prevent these extra calculations.
Using Variables in Filters
When creating a filter for a view, you might want to filter based upon the value of a CMUD variable. For example, you might want to only display equipment that has a Level field less than or equal to your current character level. All of the record filters have a button next to them labeled with a @ character. Click this button and the field will change to a standard text field where you can enter a CMUD formula expression. Notice that the @ button changes to a # button which will take you back to the normal input box. Text fields do not have the @ button since you can already enter a text formula into the box. The only fields that have the @ button are Number and Option fields.
For example, to only display items with the Level field less than or equal to the @Level variable, turn on the Filter Bar in the Toolbar option of the View menu if it isn't already displayed. Select the Level field in the field selection box. Select Less than or Equal to in the Operation selection box. Click the @ button next to the numeric entry field to switch it to a text entry field. Enter @Level in the text box. Click the Filter button to apply the filter. Now, from your CMUD command line, if you change the value of the @Level variable, the database view will automatically refresh to show the equipment that matches the new query.
The above example illustrates the drawback associated with using variables to filter views. If the value of the variable changes, the view must be resorted and re-filtered. This can take some time on large databases. So, make sure you don't filter on a variable that is changing often.
Sorting based upon a Score
In many cases, you don't just want to sort on a specific field, but you want to sort based upon a combination of fields. For example, if you are a Fighter, you want to display the best equipment for a fighter, which means weapons that have a high damage, high hit bonus, and high damage bonus. If you are a magic user you want to display equipment that adds to your mana or your intelligence. So, deciding which equipment is "best" depends upon the type of character you are playing and can be quite complex.
You can define a formula that returns a numeric value for each record in the database and then sort on the results of that formula. To do this, customize the view and enter the formula into the Formula field in the Sort Order area.
For example, a formula to display the best equipment for a fighter might be:
%diceavg(&Damage)+&Dam*2+&Hit
which will display the weapons with the best ones on top. In this case we are looking for weapons with a high average damage potential and giving extra weight to those weapons with a high damage bonus (the *2 acts as a weighting factor).
Pivot Tables
Pivot Tables extend the concept of sorting equipment based on a score one step further. In a pivot table, the "best" records for each possible value of the first column in the view are displayed. To create a pivot table, simply click the Pivot Table option in the View window. The view will be displayed with each possible value in of the first column of the view displayed. In each row, the best equipment whose field contains that value will be displayed.
To better understand this, think of creating a new view where you filter the first column to contain a specific value. CMUD will filter and sort and display the view. The first record displayed in that view is the best record for that particular query. Next, change the filter for the first column to equal the next possible value and resort the view. The first record is the best record for the new first column value.
As a more concrete example, consider an equipment database with a field called Kind which indicated the type of equipment (Weapon, Armor, Helm, Ring, etc). When you create a pivot table with Kind as the first column, the best equipment for each value of Kind will be displayed. If you use a Sort Score of something like &Hit+&Dam+%diceavg(&Damage) then the best equipment for a Warrior will be displayed. You can still use any other record filters that you want. A better view would only display equipment less than or equal to the current character level. Then, as your level changes you just consult this pivot table to determine what the best equipment you should be wearing is.
When you create filters in a Pivot Table, filters on Formula fields occur after the pivot occurs and filters on normal records occur before the pivot. So, if you filter on a normal field like &Name, you are restricting the portion of the database that the pivot is created from. But, if you filter on a Formula field, such as %count(Name), this filters which pivot rows are displayed in the view.
The use of Formula fields allows you to perform standard pivot table analysis on a database. These formulas operate on the list of records being considered for each row in the pivot table. For example, if you use a formula of %count(Name) in a column called "Count", then the Count column will display the number of records in each pivot table. In the example above, it would show the number of Weapons on the weapon row, the number of Armor objects on the Armor row, etc. Using a formula like %dblist(Name) will display the names of each item considered in the pivot row. Note that the use of column formulas will further slow down the calculation of the pivot table.
This is an incredibly powerful feature of the database module. However, performing a pivot table computation can also be slow depending upon the size of the database. If you can set up any view filters to reduce the number of records that the pivot table must search, you will speed up the computation. But, while you are waiting on it, just think about how hard it would be to search the database yourself for the best equipment of each type!
The Database and the Mapper
The Database is integrated to the mapper via Zone and Room fields. There should only be one zone and room field per record. Zones and Rooms are stored as numeric fields, but are displayed in the database by their Names when possible. Room names can only be displayed if the Zone field for the record matches the current zone being displayed in the mapper window since the mapper only loads one zone into memory at a time. Rooms can only be edited as integers though since many rooms on a map tend to have the same name.
The Actions menu contains commands to manipulate the mapper. The Set Object Location commands sets the Zone and Room fields of the current record to match the currently selected location in the mapper. The Find Object Location takes the Zone and Room fields of the current database record and centers the mapper on that location. The Walk to Object command speedwalks from the current map position to the position given by the Room and Zone fields for the object. If no zone field is present, the current zone is assumed.
Record Fields
Fields of type "Record" are pointers to other database records. They contain the normal record specifier which is the record number with the optional two-character database name appended to it. The value of these records will be displayed as the Name field of the matching database record. If no Name field is found, the first Text field is displayed.
These fields are used to link databases together. For example, you might link an Object to the Monster that carries it. The Goto Linked Record command in the Action menu loads the record that the current record is linked to. |
|
Creating a Database