GB2410577A - Development and maintenance of computer databases - Google Patents

Abstract

A database system is disclosed that includes a data repository and a data dictionary, in which dictionary is contained definitions of data to be stored within the repository and of operations to be performed upon the data. This architecture centralises the definition of the data and the processes to be performed on the data into a single dictionary, thereby removing the traditional separation between defining the structure of the data contained within the database and defining the code that is applied to it. In particularly advantageous embodiments of the invention, the contents of the data dictionary may be evaluated at an arbitrary time during execution of a database system. That is to say, evaluation of the contents of the dictionary is not limited to times at which specific events, such as the creation of a new record, occur. Thus, a definition within the dictionary is expressed formulaically, based upon the instantaneous state of the database, the definition being re-evaluated as often as necessary to maintain consistency as the state of the database is changed. For instance, the database structure may be automatically updated and changed whenever the dictionary is updated. In a desirable refinement of this, the issuer can determine whether the updates will take place automatically, or be controlled by the user, or are to be abandoned if certain pre-conditions are not met.

Description

24 1 0577 Development and maintenance of computer databases This invention

relates to development and maintenance of computer databases.

A typical relational computer database application includes data that is organised in tables, each table containing multiple rows. Each row contains one or more fields of a given type.

The number and types of fields are the same in each row of a table, but the rows of different tables will normally be of different designs.

Most database applications include program code that can perform operations upon the data that change the data itself and which interact with a user to display or update data. For such code to work properly, it must be designed with knowledge of the structure of the tables.

This is to ensure that the code does not attempt to access a non-existent field, that only appropriate operations are performed on any given field (e.g. arithmetic operation are not performed on a field that contains text), and, most fundamentally, that the correct program logic is applied to the content of a given field. In a typical system, the code is executed in response to events, such as events arising from a user interface. Therefore, it is common for the code to be implemented as part of the interface in the form of event handlers, rather than as an inherent part of the database.

The relation between code and data might not pose undue difficulty if both the code and the structure of the data were to remain unchanging. Provided that sufficient care were taken during development of the database, a fixed body of code will remain in accordance with a fixed body of data. However, in most practical cases, both code and data structure will be changed over time, and, as is well known, it becomes increasingly difficult to ensure that the code and the data structure remain consistent. Moreover, it is common for a database to be distributed by a developer to many users who will each populate it with their own data. In such cases, the developer does not have direct access to each database to verify the current structure and to perform updates to the structure and the code when these become necessary.

Updates must, therefore, be applied remotely. Typically, larger systems involve many event handlers with much code, making it difficult to make consistent amendments and necessitating expensive testing of the whole system after each set of amendments.

Remote updating of a database presents considerable technical challenges. Using conventional techniques, the database structure and the associated code must be in a specific state before an update is applied. If a user has failed to apply an update, but then attempts to apply a later update, the update process is likely to fail, and may corrupt the database or render its code inoperable in so doing. Therefore, it is normally considered too risky a procedure to be attempted by unskilled end users. While this problem can be overcome by allowing only suitably knowledgeable persons to apply updates, this is often not possible in practice. Users may not be willing to pay for a level of support that will provide on-site visits by technicians, and issues of data confidentiality may prevent granting of remote access to the data to a technician.

Therefore, the aim of this invention is to provide a mechanism to read the specification of a database and automatically derive the structure of the data and the associated code and event handlers. With such a mechanism amendments can be made to the database automatically in a reliable and secure manner. Since no code or event handlers are written, there is no code to go wrong or need testing.

From a first aspect, this invention provides a database system that includes a data repository and a data dictionary, in which dictionary is contained definitions of data to be stored within the repository and of operations to be performed upon the data.

This centralizes the definition of the data and the processes to be performed on the data into a single dictionary, thereby removing the traditional separation between defining the structure of the data contained within the database and defining the code that is applied to it.

In particularly advantageous embodiments of the invention, the contents of the data dictionary may be evaluated at an arbitrary time during execution of a database system.

That is to say, evaluation of the contents of the dictionary is not limited to times at which specific events, such as the creation of a new record, occur. Thus, a definition within the dictionary is expressed formulaically, based upon the instantaneous state of the database, the definition being re-evaluated as often as necessary to maintain consistency as the state of the database is changed. Such formulate definitions may be dependent upon the content of the data repository, of the data dictionary or both. The effect of this is to allow actions upon data to be initiated as the state of the database itself changes, without reliance on an external trigger such as an event in a GUI.

In a normal embodiment of the system, the database structure is automatically updated and changed whenever the dictionary is updated. In a desirable refinement of this, the issuer can determine whether the updates will take place automatically, or be controlled by the user, or are to be abandoned if certain pre-conditions are not met.

The definitions of data within the dictionary may include one or more specifiers including: the type of data that is to be stored within a field; a validation rule for data that can be stored within a field; an initial value to be stored within a field when a new instance of the field is created; a definition of the value contained within the field at any time; a value to be stored within the field if the value becomes empty; a suggested value that can be conditionally stored in the field in the event that a value has not been stored explicitly in it; whether data must be unique amongst instances of a field; and whether or not it is compulsory that each instance of the field contain data. Embodiments of the invention permit some or all of the above specifiers to be defined in terms of one or more of: the contents of the field to which the definition relates; the contents of another field; or as the result of evaluation of an arbitrary function. In a typical embodiment, all specifiers except the data type are re- evaluated whenever any event or change occurs at run time.

Typically, each field has a value defined only in the context of an instance of the field within a record, the instances associated with each several records having identical or different values. A formula that specifies the value in a current instance may depend upon the value of any field or fields in the current instance or in any other instance or instances.

Typically, a single definition of a field within the dictionary applies to all instances of the field, but the definition may itself contain conditional elements, which result in the formula having a different effect in different instances of the same field.

The data dictionary could also contain definitions of fields, which define the conditions under which operations are to be performed upon the data. For example, such conditions may specify that an action be taken in response to one or more of the following: an event external to the database such as an event originating in a user interface, a filesystem or another computer input device; a change within the data stored within the repository. Thus, the definitions of how to react to all changes internal to and external to the data are defined in a common place: within the dictionary.

Thus, everything may be described in terms of results. For example, CO. Network = MortTotal - Sharesub Sharesub = ShareDeposit + ShareBalance The sequence of these definitions in the dictionary does not matter; Sharesub will always be calculated before co. Network.

In the context of the invention, the dictionary can contain such descriptions of all processing, display characteristics and conditions in a database.

A result operation specified within the data dictionary may be an operation affecting data within the repository or an operation upon objects external to the repository. That is, a developer can define external events or objects as data values in the dictionary. For example, the objects external to the repository may be objects within a graphical user interface or another computer output device.

In embodiments as defined in the last two preceding paragraphs, the entire structure of the database, programmatic operations upon the data, and interaction with the user - both input and output - can be specified entirely within the data dictionary. This removes the need to provide separate code, for example user interface event handlers, with all of the associated complications and difficulties of maintenance to which such code gives rise.

The data dictionary may include display attributes that describe how the content of the field is to be represented upon an output device such as a computer display or a filesystem. In such embodiments, the representation may be a direct representation of the data within the field (for example, a representation of a text string or a numerical value) or it may be a representation of a value that is an output of a function applied to the value of the field.

Embodiments of the invention typically include a calculation engine that analyses the dictionary and determines the conditions upon which an operation will be performed. Thus, the designer of a database application need specify only the relation between the operation and the data; the design does not include procedural code to determine when such an operation should be performed.

From a second aspect, this invention provides a computer program product (interpreter) that implements a database system embodying the first aspect of the invention.

An embodiment of the invention will now be described in detail, by way of example, and with reference to the accompanying drawings, in which: Figure 1 is a simple block diagram of components of a database system embodying the invention (numbers in the text refer to this diagram); Figures 2 to 4 are screenshots that illustrate screen definitions that could be used by a database application developed using a system embodying the invention; and Figure 5 is a diagram showing one embodiment of the invention.

Introduction to the Embodiment

The embodiment that will now be described is implemented upon a computer platform 10 that supports the FoxPro relational database management system (DBMS). This DBMS permits production of GUI database applications for execution under the Microsoft Windows operating environment. However, it will be understood that this is in no sense limiting. Many other platforms might also be used including alternative database development systems, or embodiments may be implemented as an application in a general- purpose programming language.

The embodiment includes a series of data definitions 12 and a program 14 that processes these (non-procedural) definitions implemented within the DBMS. The interpreter reduces these definitions to a series of tables. A continuation of the program proceeds to load the resulting tables and to interpret them together with menus and screen definitions, thus providing a fully-featured GUI database system directly from the definitions. In this embodiment, the application designer also provides a (small) amount of application code which is interpreted to start the process, and to allow procedural responses to screen buttons, such as command buttons.

The invention requires the application designer to store information in the definitions of the fields and files of a database system beyond that required in a conventional database system.

As well as the conventional information about types, initial values and validation of data, the designer may also enter formulas which as are used to calculate values, formats and other properties. This collection of information that defines the data is referred to in this document as the 'dictionary'. However, the designer does not specify when calculations are to be carried out. The point at which calculations take place is decided by the runtime part of the system in response to changing values of fields on a current screen or the changing values of fields in related subfiles. It is of particular importance that, following a systematic (mechanically determined) decision that a field has changed, all the formulas that depend on the field are re-evaluated, any effects on other data items are also re-evaluated, and the appearance of the screen is updated.

The dictionary is used with a runtime system and a set of screen, menu and document interface specifications to create and run a complete program application directly from the data specification. This embodiment provides an interpreter for the application language.

Standard programming features such as screen display, user input, interface with external documents, and file parsing and conversion. The interpreter may make calls to the platform upon which the embodiment is implemented to implement some or all of such features.

It is also notable that, in this embodiment, (in contrast with known GUI database development environments) screen definitions carry no program code at all.

This means this program manages all screen display characteristics, data values and menu options directly from the definitions of the dictionary (as above) the screen and the menu layouts: no further programming is necessary.

The examples given in this specification are inputs for an interpreter that reads the dictionary (as well as the program code) from an ASCII text file. Many other implementations of the invention are possible. In the embodiment, each database is specified once using the dictionary, which is separate from the data that it describes. The database may then be implemented on a variety of actual database engines.

The Data Dictionary The first type of information stored in the dictionary beyond that in a conventional database application involves the actual value that a field in the dictionary should have.

In most modern database management systems, some information about values is stored for each field. The invention extends this set of values with options for specifying field values as formulas that are automatically calculated during the running of the program. The interpreter changes field values in the database at run time, as the fields upon which the formula depends (the 'independent variables') change.

This embodiment includes the following field specifiers: these are in common with a typical modern database system: the type of the field (e.g. number, text, or logical variable); validation rules for the field (e.g. minimum or maximum length, masks that define what input should look like, and allowed ranges of values); an Initial (default) value to put in the field when it is first created;

whether the field values should be unique; and

5. whether it is compulsory to have some value in the field, or if it can be left blank.

These items may be written as formulas involving other fields in the database, or fields in other databases. In known systems, they may include involve actions to be executed either when a user enters new data or when a record is first created - i.e. to be done once, at a procedurally defined point in the logic. However, existing systems do not have any facility to specify formulas to be evaluated at any other time.

The extensions to the field values provided in this embodiment will now be described. All of the standard options described above are also allowed in the embodiment. However, for all options (except the type of the field), formulas are allowed in the definition. In the current implementation, these formulas can refer to any values in the same record or in other records to which the current record is linked, as well as to global and system variables (such as the current screen name). There are also functions that allow groups of records in the same file to be referred to e.g. Sum ( ) . The results of the formulas are therefore allowed to change at run-time, unlike traditional methods.

The initiaVdefault value option is extended by the embodiment to allow field values to be altered at other times, not just when the record is created. This embodiment identifies four distinct ways to specify value (although others may be specified within the context of the invention).

Initial: this is the recognised feature of specifying a value to be used when a record is created (and only when a record is created).

Value: this defines a value to be set in the field whenever an independent variable changes. This normally allows the user to change the field at any time, but such user values are retained only until the next independent variable change. For example, a quick reference may contain the name of a client and of the client's partner. A user can alter this, but if either name changes, the quick reference is regenerated.

ValueIfNull: In this case, the new value of the formula is only written to the field if the field is currently null (i.e. does not currently contain a value). For example, a reference code for a matter is derived from a client code, but only if no reference number is already present. Once set, the system makes no further changes, even if the client code changes.

This is allows an existing stable value to outweigh the value of a calculation. The user can override this specifier by entering any value into the field. If the user deletes the content of the field, the system will reset it to the value defined in the ValueIfNull specifier.

ValueSuggest: In this case, if an independent variable changes, the new value of the formula is only written to the field if the user has not yet changed the previous value.

The database is extended to track those values that the user has altered and these are left intact. For example, such a calculation might be the selling price of contracted work (suggested as time multiplied by unit time cost). Once the user changes this value, the user must continue to maintain it; the system will not make any automatic changes to it.

Two related settings in the embodiment are: Auto: This is the usual feature which says that a field in a new record is automatically given a program-generated unique value. This can be regarded as a variation of the

Initial' specification.

Define: This specifies that the field should be always given the result of a formula, and that the user cannot overwrite the value. This is equivalent to using the 'Value' setting and disallowing user input by setting Enabled=No (see next section). For example, this might be used to specify a total invoice value as = goods + vat.

The second type of information stored in the dictionary describes how a field should be displayed on a graphical user interface. This particular embodiment allows only a modest number of display attributes in its dictionaries, but many extensions to these are clearly possible. The standard database features that are provided in this embodiment are as follows: Format: a description of the way a text-string representation of a field should be displayed on screen.

Caption: a string for use in identifying the field, used for labels and grid columns, etc. List: the options that should appear in list boxes.

The additional features in this embodiment are the following flags: Visible: a flag to specify whether the field should be visible on the screen.

Enabled: a flag to specify whether the user can alter the value in the field. In a Windows system, setting this to 'No' would cause the input box (object) to be grayed-out on the screen.

In this embodiment, each of the above items (including the standard settings) has an associated formula. This is significant because settings can change at run-time in dependence upon many different variables. For instance, a field could be visible for some users and not others; a field could be enabled on certain screens and disabled on others; and the display format of a field can be set to change based on the context in which it is shown.

The ability to write code in the dictionary to conditions and specifications for display of data permits all code to be removed from the screens. As well as centralising all code within the dictionary, this also allows the description of the display to be specified in a platformindependent manner.

In this embodiment, a few display properties are still set manually (e.g. colour), however the mechanism can be extended to handle all settings which specify the way a field is to be displayed on the screen.

While the attributes discussed above are all display attributes, there is no reason why the mechanism cannot be used to calculate any attributes related to fields, groups of fields, screens, or files that can be expressed as formulas; that is, to all values and properties that can be set at design time or at run time.

Field Processing

Field processing is controlled by the contents of several tables. These are referred to as the field tables, the formula tables, and the file tables. Their contents and functions will be described below.

The file tables and field tables are of particular importance to operation of the system. The dictionary defines all processing and formulas that set the values or properties of fields. The calculation of each field (as well as its format, access and display options) is specified once.

The run-time process of the embodiment takes the full specification for each field and analyses it to derive separate formulas. Each formula changes the value or properties of this field, which is the result field for that formula. (Fields in the formula itself are the

independent fields for that formula.)

The runtime process now monitors all events (including user input and changes to independent fields) to determine when each formula should be executed. It then executes each formula at the appropriate time. Note that execution of a formula may change the appearance of the field as well as its value. Thus, the programmer determines only the formula and does not define when the formula will be executed, nor its attachment to any screen or other event.

The function of the field table will now be described.

The runtime process reads in the dictionary. This is a complete specification of the data, fields and calculations for a file processing application (as defined above). The system puts the definition of each field into the table of fields (ArrField). Each field definition is a separate row of the table. The table of fields has the following columns: Tag Type Len Link Form Valid Opt File Set Depend Pre CUP /M DT/ A/CIUIH Dependant VN/ L Functions The fields will be discussed in greater detail below.

Tag: The name of the file and the name of the field, separated by a full stop. This is the 'tag'

of the field.

Type: This is the type of the data being stored. A single letter is used to reference the data type.

Len: The maximum length of the data being stored. For the purposes of this document, types and lengths of fields have been ignored, but they are handled in the usual manner, and type checking is performed.

Link: This is a formula that is used to fill in the values in list ('combo') boxes. Link includes the names of files or fields that will be used to generate the list of options for a list box. This is either a comma-separated list of options, or a list of fields to take the values out of to create the list box. Also the other field name that will be linked 'on' this field (e.g.: sales invoice will link to a client file).

Form: This is where information about the format of the field is stored (e.g. British dates, visible, enabled etc.) More on this will be presented below.

Valid: This is where information about when the field is valid is stored. Form and Valid formulas may change their values at run-time, so are stored as the formula, not the result of the formula. More on this below.

Opt: Other options about the field, stored as single letters in a string: [A]uto (the field will calculate unique values for this field in new records), [C]ompulsory (the user must fill in the field before the new record will be saved), [Ulnique (the field's value must only appear in one record in that file), or [H]idden (a field that is never visible to users or in searches) File: This is a reference to the file that the field is in (i.e., its position in the file table), so that it can be looked up quickly Set: This is information about when a field should be set to a particular value, and what that value is. This determines the fields value and is recalculated if any independent fields change. The following cases arise (see above for details) Initial (new records), Value (normal), ValueIfNull (recalculate only if null), ValueSuggest (recalculate until the user enters a value, then stop), Define (always recalculate, ignore user input).

This is embodied as a pointer to one formula for the 'Value' attribute. This determines the fields value and is recalculated if any independent fields change.

The two subsequent columns are calculated by the interpreter from the dictionary. The following columns allow the task of detailed programming to be eliminated.

Depend: This is a list of (pointers to) formulas (see below) that depend on the value of this field. The formulas may be linked to fields in this file, or in other files, and may be format or value formulas. These formulas are those that will be recalculated if the value of this field changes. These formulas may affect appearance and accessibility as well as other properties

(not just the result field's value).

Pre: This is a list of (pointers to) formulas (see below) that should be evaluated before this field is displayed on screen. For example, if there are many memory variables, 'Pre' is used to determine the correct order of evaluation so that the final result(s) are meaningful.

The following are notes on the above fields.

The fields Set, Depend and Pre are not found in conventional database systems.

Name, type and length are the only attributes that must exist for each record in this table.

In practice, it is not easy to fill in the 'depend' and 'Pre' fields on a first pass. They are currently derived from a second pass through all the formulas, determining which fields are used in which formulas. This is 'where used' analysis.

Unlike in a conventional database management system, Form and Valid are stored as lists of formulas (see below). These formulas may change at runtime and are recalculated by the system when required. Formulas are stored in the table described below, and a list of pointers into this formula table is what is stored with the field entry. Fields do not necessarily need to be fields in files; they can also be free variables which are not in any file.

Formulas Formulas have been mentioned in the context of the field table. Formulas can be stored in a separate table (though this is not essential), and can be referenced by their position in this table. This table contains the following values: Field A reference to the field that to which the formula refers.

Option The general type of expression that this formula is. The types are Link (for list boxes), Set (value formulas), Form (format expressions: visible, enabled etc.), Valid (validity formulas). These different general types are handled in different ways by the system. Link relates to the linked files or the pulldown (listbox) contents; Set related to setting the fields value; Form relates to visibility, screen display styles and format; Valid this is the a conventional validity formula (but changing at run time).

Word The specific type of expression that this formula is, stored as the keyword as _ __ in the input code, e.g. Value, Define, IfNull, Visible, Range, enabled et_. Expression The actual formula, directly from the code, as a string.

All formulas related to fields are stored in this table. Usually formulas are referred to by their position in this table.

The data definition becomes most effective when combined with any picture of the screen.

A screen picture is a list of objects and the fields that are bound to those objects. The runtime interpreter determines the field number for each object on the screen. This produces an array (not shown here) of the fields on the screen and the related objects. Now when we change a fields appearance, we know which screen object(s) are affected.

In processing the field tables, the interpreter intercepts all allowable events as they occur.

This includes all user-initiated changes to fields that are on this screen as well as changing values of fields that are on the related subfiles (e.g. any navigation - see file table below). In response to these field changes, the interpreter changes the consequent formulas and through

them the fields and displayed values and formats.

When a value changes (by any means): the runtime system finds the relevant entry in the field table; 10. in the case of user input, the new value is checked with the formula or formulas referenced by the 'Valid' entry (if invalid, the user is informed and the invalid value rejected): automatic return to the previous value is built in; the runtime system goes through each formula listed in the 'Depend' column (looking them up in other rows in the formula table); and 15. for each formula, the result variable is re-calculated using the current data values. Each calculated value may affect the result field's value, the result field's screen attributes, or other properties.

When a field is displayed on screen:

the runtime system finds the relevant entry in the field table; 20. some display attributes (e.g. Form, Opt) can be set from values stored directly in that record; list boxes are filled using the expressions in the 'Link' column; the runtime system goes through each formula listed in the 'Pre' column (again, looking them up in other rows in the formula table); for each formula the result variable is calculated; and from the type of the formula, the rest of the field display attributes can be set.

File Processing Information about the files in this embodiment is stored in a table of files (called ArrFile).

Each file has a separate row in the table. The table has the following columns:

Name Index Title Field Navigate Newrec

List of Field Index to List of Expressions to reset List of Fields To set

names arrField after navigate on New record

As in the case of the field table, some of these entries (name, index, title, field) are directly analogous to entries used in a conventional database system and others (Navigate, Newrec) are particular to embodiments of the invention. The function of these fields will now be described.

Name is the unique name of the current file (e.g. CL) Index gives a list of field names for which indexes are available on the database. The first index is conventionally a unique index for the file; often called the primary key in other database systems.

Title is a title to display when communicating with the user (e.g. Client File).

Field is a pointer into the table of fields, indicating the first field for this file (start of fields for this file).

Nav is a list of formulas (or references to formulas) that must be recalculated whenever this file is 'navigated'. Navigated means that a different record becomes the current referenced record. This can be either because a new view (screen) has been started, because there the user chooses to select or move to a different record, or as a consequence of movement of a linked file. Typical navigate dependent results are totals or memory variables.

For example, a memory variable = goods*vatrate will change as a user navigates through an invoice lines file. Note that variables in files are rarely (if ever) dependent on navigation of this file. They can, of course, depend on changes in the values of fields in this file: this is the usual form of dependency as discussed in relation to fields, above) NewRec is a list of (references to) formulas that are recalculated whenever a new record is created in this file. This will be all fields in the record which have value formulas, also dependent memory variables and totals.

Examples of Dictionary Features (and the interpretation of them) 1. A simple value example.

In this example, the dictionary shows charges subtracted from a mortgage total to give the net mortgage value.

Co.NetMort I Value=(MortTotal - co.sharesub - co.guarantee - co.retention - co.lessorfee) This defines a field, NetMort, in the file co (conveyancing). Its value is set by a formula.

This formula is a of type 'Value', meaning it is recalculated whenever any of it's constituent parts changes, but may be amended by the user at any time. From the view of this formula, Co.NettMort is the result variable, and MortTotal, co.sharesub and so on are the independent variables. The interpreter processes the values as set forth in the flowchart below: Rcad definition of result field Extract each independent field (eg:Co.NettMort). Identify the from the formula (eg: relevant formula co. sharesub _. _. . _.. ..

Amend the list of independent fields so that co. sharesub points A 11st of independent fields, , Each one points to all the to tne forms original formulas in which it co.sharesub-co.guarentee- participates co. retention-co. lessorfee) . 1 Initially, suppose MortTotal=25000 and all charges are zero, then NettMort=25000.

Now, suppose the user types some input into a box linked to the co. sharesub field

When the value of the Look up the field. .

independent field changes in the list of A 11st of independent fields, (eg: co.sharesub is set to Independent fields each with pointers to all 2000) relevant formulas Follow the pointer to the formula, and (re) calculate the result value(s) _ _. . , Reset the va lue of the The value of co.NettMort is

result field(s) now set to 23000

2. Value if Null In the next example, the aim is to initially set the ref in database 'matter' to be the same as a number in the database 'client'. But the corresponding client record may not exist at the time the 'matter' record is created.

matter.ref I ValueIfNull= client.ref The "dependent" entry for the client. ref field points to the formula 'matter.ref = client.ref', with the type ValueIfNull. Also, ref features in the NewRec coluron of the matter file table. When a new matter record is first created, the interpreter uses the file newrec entry to attempt to calculate the matter.ref field. If there is no related client file with client.ref value, the matter.ref field is left empty.

When the client record is created or linked in, the runtime program will note that client.refiS altered, and that the formula matter.ref = client. ref depends on it. It will now check that field matter.ref is (still) empty, and then replace matter.ref with client. ref.

If the user has already set a different value in matter.ref, the value of matter.ref will not be changed even if client.ref changes (unless matter. ref somehow becomes empty again).

3. ValueSuggest In the next example, a title is suggested for a record in the 'p' database. The suggested title comes from the section name and the chapter name of a related record in the 'I' database: p.title I ValueSuggest = t.sectionname + ": " + t.chaptername When this is processed, the dependent fields entries for t.sectionname and t. chaptername are set to point to this formula. When the record is first created, the title is set to t.sectionname + ": " + t.chaptername.

If the user changes t.sectionname, the interpreter looks up the formula and alters the title in the 'p' record. If the user changes the field p. title, the new (user determined) title becomes permanent. If the user now changes the related t.sectionname field, the p. title

field remains as the title the user typed in.

4. An option List In this example there is a list of options: Mortgaged IList=''No,Yes,''+iif(IsLandlord,''Buy to Let","") This will be read into an entry in the table that has properties about the fields in files. Then, when the field 'Mortgaged' is displayed on screen, the interpreter will check this table and make the field a list box, with the options 'No', 'Yes' and 'Buy to Let' to choose from. In this case, the option changes at run-time: "Buy to Let" shows only if the memory variable IsLandlord is true 5. Visible In this example, the field 'BSInsures'iS only visible in certain cases: BSInsures I Visible=(MA.Worktype="Purc") When this is read in, the formula MA.Worktype="Purc" will be pointed to by the Visible part of the BSInsures entry in the field table, and by the dependent section of MA. Worktype.

When displaying the screen, the interpreter examines this formula, and displays the BSInsures field on screen only if the Worktype field in the MA file has the value 'Pure' 6. if - Alternative Values The following example uses an 'if' statement to calculate the total 'in's (receipts) for a house sale or purchase.

TotalIns IValue= if( ma.worktype="Sale", pricetot + coretotal + co.balpur, co.deposit + co.balsale + co.nettmort + CoRetotal) This will be read in and processed like any other Value attribute (see example 1, above).

When the formula is evaluated, if the related record in the MA file has the value Sale in its Worktype field, then the value will be set to the first sum, if not then it will be set to the second sum.

7. Enabled - user may amend This example shows an application of the 'Enabled' attribute: BSInsures IEnabled=No When this is read in, the formula No will be pointed to by the Enabled part of the

BSInsures entry in the field table.

When displaying the screen, the interpreter will look at this formula, and will evaluate it.

Since the formula is always evaluates to No (which is equivalent to a Boolean False), the field BSInsures cannot be amended on any screen, even where the screen designer had made provision for it.

An example application implemented on an embodiment of the invention A simple example application implemented using the embodiment of the invention will now be described. The application is a simple address book system for a company that allows work (units of work being referred to as "matters") to be associated with clients. (The details of the linking between matters and clients are not within the scope of this invention.) The application features three screens, taking different views of the system: in Figure 2, a screen for scrolling through clients; in Figure 3, a screen for scrolling through matters; and in Figure 4, a screen displaying a grid of clients, which can be sorted into various orders.

All these screens are accessible through a menu system. The later two screens also provide buttons to move into the first screen at the currently selected client record.

Other features of the system are: the ability for a user to select a client for a matter in the matter screen by using an automaticallypopulated drop-down list box; the 'closed date' field not being accessible until the status of the matter is 'closed'; a validity check on client codes, to make sure they are four characters or longer; setting of various initial or default values; the 'fax' field being invisible unless the 'hasfax' option is selected; and the creation of a field containing the full name of a client.

Using the techniques described above, this application is implemented as shown in the listings below. Listing 1 shows the definition of the data dictionary for this application, Listing 2 shows the definition of the menu structure of the application and Listing 3 shows the code that defines how the forms operate.

COMPONENTS OF ONE EMBODIMENT OF THE INVENTION

This embodiment of the invention is made up of four modules, each of which will now be discussed in more detail. Numbers in brackets in this section of the specification refer to numbers in Figure 5.Interpreter Module The Interpreter module reads in the application lines and interprets them Interpreter Data The Interpreter module contains the definition of the procedure table (ArrProc) (4) | Procedure Name | Line Number l The application code lines (for an example, see listing 3) are first numbered. The line number in ArrProc is a reference to the first line number of the associated procedure in the code.

Important Interpreter Routines Main (1) This is the initial set up of the program. It sets paths, reads name of application code from command or icon, and initialises all tables. It then calls PR_Proc to read the application code.PR_Proc (2) This routine reads in the application code lines for the program (5), numbers the lines and identifies the line number of the start of the code for each screen or command button on the screen. These line numbers are stored in the ArrProc table (4). It then sets the current program line to line 1, and passes control to PR_Execute.

PR_Execute (3) This is the main interpreter procedure which looks at the current program line and then performs the actions required for the command in that line, calling other routines where required, such as (7), (8),(9) and (15)). When the command is finished, there are two possibilities: if it is the end of the routine, exit this instance of PR_Execute else increment the current line number and loop to interpret it Routine_call (7) Given the name of a command button or screen, this procedure looks up the code stored for this section in ArrProc (4) and then calls the interpreter PR_Execute (3) to run this code.

Screen Module This module loads the screen (in this case, using the native Fox screen file) builds a table of all controls on the screen, and sets the initial values into each control.

Screen Data This module contains the definition of a table to store all the objects on the screen (10), ArrCtrls:

Afield Number | (pointer to) Control l

The field number is the number in the field table of the field that is associated with the control. There is a new ArrCtrls table created for every screen opened.

Important Screen Routines PR_LoadScreen (8) This saves the tables associated with the previous screen, then loads the new screen (by name), and shows it, calling the SC_oadControl routine to initialise each screen object.

On termination of the screen, the previous tables are restored.

Procedure SC_LoadControl This identifies and initializes each different type of object on the screen, adding the field number and object identity to the ArrCtrls array (10) Pseudocode for the implementation of SC_LoadControl is as follows: procedure SC_LoadControl(control) begin if (control is a grid) then for each column in grid [1] addcontrol(control. column); else if (control is a listbox) then [2]

do_expr(control.field.link); [3]

addcontrol(control) endif; else addcontrol(control); endif; end Note [1]: The columns are the controls within the grid which contain the fields, and it is these that getlset focus, not the whole grid.

Note [2]: Listboxes must have their contents evaluated (and possibly linked in) when the control is loaded. The only information manually entered onto the screen layout is the name of the field in the dictionary (or, in the case of command buttons, the caption or tag).

Note [3]: do_expr will be defined later, in the "Function" Module.

Addcontrol (c ontro l) This procedure identifies the associated field and then adds the control to Arrctrls. It then evaluates any formulas relating to the fields value or display format. The field number is stored in the tag (or, if that is not relevant, in the comment) attached to the control. This enables the event handler to instantly identify the relevant field for each event.

Event Handlers (14) In this embodiment, there is a separate event handling routine for each relevant event on each type of control on the screen. Each routine takes as a parameter a reference to the object associated with the event (e.g. the textbox or the command button).

The first action of an event handler is to convert the control id to a field number using the tag. The relevant formulas can now be looked up in the dictionary. Each formula can now be executed from the formula table, using the same code that is run when a field's value changes by whatever means (including setting in the code, or knock-on events).

For lost-focus events, we have the following pseudocode:

if value_has_changed(field) then

if value_is_valid(field) then

do_expressions(formula); 'in Function module else 15reject_value(); endif endif Reject_Value Reject values in the usual way for this control to inform the user that the value is invalid e.g., forbidding the textbox to lose focus or resetting the previous value. A message 'Invalid Value' can be displayed on screen, possibly with reasons derived from the valid formulas.

Value_Has_Changed This checks that the value of the field has actually changed (even if we know the user has pressed keys, the same value may have been chosen again). The implementation may vary, but the current embodiment stores the old value when the control gets focus, so that is can be compared with the value when the control loses focus, but any method for deriving the previous value would do.

Value_Is Valid This checks validity using the field.valid column (if any). Formulas are expected to give Boolean results. If there are multiple formulas (range, minimum length etc.) they must all be satisfied for the value to be valid.

Command_Click (14->7) This routine takes the caption (or tag) of the button and calls Routine_call(7) in the nterpreter module to perform the code for that button.

Data Module The third module in the embodiment is the Data module.

Data Module Data The Data module contains the definitions of the file (11) and field (12) tables, and functions to handle these tables.

The file table is called ArrFile: | Name | Index | Title | Field | Navigate | NewRec |

The field table is called ArrField:

| Tag | Type | Len | Link | Form | Valid | Opt | File | Set | Depend | Pre | The meanings of the fields in these tables have already been discussed.

Important Data Routines

DA_AddFile, DA_AddField

These procedures add new rows of data to the ArrFile and ArrField tables.

DA_GetTag(n), DA_SetTag(n) These procedures get and set the value in the Tag column in the row number n.

Other Table Setting Procedures (15! Similarly, there are procedures in this module to get and set all of the values in the ArrFile

and ArrField tables.

Database Access Procedures (15! There are procedures in the data module for accessing the actual database tables in the standard way. This includes getting and setting the values stored in the fields in the tables.

These procedures will be called by the interpreter and screen modules.

Relation Handling There are procedures in the Data module to perform standard relation handling between the tables in the database. In this example, simple Fox relational links are constructed for the tables. The relationships are then managed by the Fox system.

Function Module The fourth module in this embodiment is the Function module.

Function Data The Function module contains the definition of the table which contains the formulas, ArrExpr (13):

| Field | Option | Word | Expression l

This table has already been discussed in detail.

Important Function Routines PR_LoadDictionary A procedure to load the dictionary (6) into the File and Field tables (9) using the data routines Add_File and Add_Field. Each field formula (ea. 'Value= cost * vatrate / 100') is added to the formula list ArrExpr (13). When all fields have been processed, the routine EX_ResolveExpr is invoked.

EX_ResolveExpr A procedure that performs a second-pass of the ArrExpr table to set up the dependent expressions correctly (another part of (9) on the diagram). This cannot be done until all fields have been loaded into the ArrField table because the sequence of definitions is indeterminate.

do_expressions This procedure takes a list of numbers that refer to rows in the ArrExpr table (formulas). It evaluates these formulas (using the routine do_expr), and recursively evaluates any formulas that depend on the ones that are evaluated. This is the way that this embodiment deals with knock-on expressions.

For this routine we have the following pseudocode: Procedure Do_Expressions(exprList) begin do while not(empty(exprlist)) ExpNo = getnext(exprlist) [1] Do_Expr(ExpNo)

ResultFieldNo = ExpNo.Field

if not(empty(ResultFieldNo.depend))

Do_Expressions(ResultFieldNo.depend) endif enddo end

Note [1]: Getnext(exprlist) removes and returns the next element in this copy of the variable exprlist.

do expr The do_expr procedure is the main part of the invention. The pseudocode for it is as follows: procedure do_expr(formula) [1] begin

FieldNo = formula.field;

case (formula.option) of TO Set: [I]

FieldName = get_field_name(FieldNo)

case (formula.word) of Initial: 'only invoked on a new record FieldName. value = evaluate(formula.expression); [3] ValueIfNull:

if IsNull(FieldName) then

FieldName.value = evaluate(formula.expression);

endif; ValueSuggest:

if IsChanged(FieldName) then

FieldName.value = evaluate(formula.expression);

endif; Value:

FieldName.value = evaluate(formula.expression);

Default:

FieldName.value = evaluate(formula.expression); end; Form:

controls = get_related_controls(FieldNo);

for each control in controls case (formula.word) of Enabled: control. enabled = evaluate(formula.expression); Visible: control.visible = evaluate(formula.expression); Caption: control.caption = evaluate(formula. expression); endcase; endfor; refreshscreen(); Link:

controls = get_related_controls(FieldNo);

newlist = evallist(formula.expression); for each control in controls if IsListBox(control.class) control.rows = newlist; endif; endfor; refreshscreen(); endcase; end In relation to the above listing, the following comments apply: [1] 'Formula' is a reference to the ArrExpr table.

[2] The knock-on effects of evaluating a 'Set' expression are similar to what occurs when the user changes a value, described in the Screen module above. The changing of a field's value will trigger that event.

[3] Notation: FieldName.value means the actual value in the field referenced by FieldName.

Note: Valid is also a type of expression option, but it is not handled in do_expr, because validity expressions are evaluated separately (see the events that occur when a control loses focus, in the Screen module above).

IsChanged This checks to see if the field's value has been altered by the user, or just by the program.

This is done with a simple flag on the database that gets set if the user alters a field.

Get_Related_Controls(FieldNo)

This routine gets all of the controls on screen that are related to the given FieldNo. This will usually just be one control.

Refreshscreen This command redraws the screen with the new values.

Evallist This function evaluates list-type expressions, returning a list suitable for showing in a listbox.

IsListBox This function checks to see if the given control is a list (combo) box.

FoxPro, Microsoft, Windows are registered trade marks of Microsoft Corp. Listing 1: Example data dictionary FILE IMA IKeys=ID, ClientCode, Code ITitle="Matters" ID IN6 lAuto IHide Code IU9 [Unique Ref IUl5 IDefault=(MA.Code) ClientID IN6 ClientCode IU6 ILinkTo=CL.Code I

List=Fields(CL.Code,Surname,Forenames,Address)

Opened ID IInitial=(Today) Worktype IP8 Matter IP50 Estimate IV Source IU15 IList="Existing Client,Advert,Other" Status IC8 IList="Open, Closed,Archived"lInitial="Open Closed ID IEnabled=MA.Status="Closed " FILE ICL IKeys=ID,Code,Names,Surname ITitle="Clients" ID IN6 lAuto IHide Code IU6 IValid=len(CL.Code)>3 Title IP5 lInitial=''Mr. " Forenames IP30 Surname |P20 NameslP60 IValue=CL.Title+left(CL.Forenames,1)+". "+CL. Surname Address IPl20 TelD IU20 TelE IU20 IDefault=CL.TelD Fax IU20 IVisible=CL.HasFax HasFax IL ICaption="Has Fax" Listing 2: Example menu dehmition MenuStyle I Buttons I Backcolour = "Blue" Menu I Main ITitle="Main Menu" Item I "See \<Matter" I Call Screen.fmMatter Item I "See \<Client" I Call Screen.fmClient Item I "\<List Clients" I Call Screen.fmLstCli End Menu Listing 3: Example code Set I DataPath | "Data" Set I ScreenPathl "Screens" Set I TempPath I "Temp" Load Dictionary l''Dict.sql''

Open Tables

Do Menus I "Menu.sql" I FastExit=No Quit Screen IfmMatter; Show Command ISee Client; Call Screenl fmClient Command IExit; Unload End Screen ScreenlfmLstCli; Show Command IBy Name; sort I CL I Surname Command IBy Code; sort I CL I Code Command ISee Client Call Screen| fmClient Command I Exit; Unload End Screen Screen I fmClient; Show Command I Exit; Unload End Screen Clams

Claims (24)

1. A database system that includes a data repository and a data

dictionary, in which dictionary is contained definitions of data to be stored within the repository and of operations to be performed upon the data.

2. A database system according to claim 1 in which the contents of the data dictionary are evaluated at an arbitrary time during execution of a database system.

3. A database system according to claim 2 in which a definition within the dictionary is expressed formulaically, based upon the instantaneous state of the database, the dehmition being re-evaluated as often as necessary to maintain consistency as the state of the database is changed.

4. A database system according to claim 3 in which the formulate definitions are dependent upon the content of the data repository, of the data dictionary or both.

5. A database system according to claim 4 in which actions upon data can be initiated as the state of the database itself changes, without reliance on an external trigger.

6. A database system according to any preceding claim in which the database structure is automatically updated and changed whenever the dictionary is updated.

7. A database system according to claim 6 in which the issuer can determine whether the updates will take place automatically, or be controlled by the user, or are to be abandoned if certain pre-conditions are not met.

8. A database system according to any preceding claim in which the definitions of data within the dictionary include one or more specifiers including: the type of data that is to be stored within a field; a validation rule for data that can be stored within a field; an initial value to be stored within a field when a new instance of the field is created; a definition of the value contained within the field at any time; a value to be stored within the field if the value becomes empty; a suggested value that can be conditionally stored in the field in the event that a value has not been stored explicitly in it; whether data must be unique amongst instances of a field; and whether or not it is compulsory that

each instance of the field contain data.

9. A database system according to claim 8 in which some or all of the specifiers are defined in terms of one or more of: the contents of the field to which the definition relates; the contents of another field; or as the result of evaluation of an arbitrary function.

10. A database system according to claim 8 or claim 9 in which all specifiers except the data type are re-evaluated whenever any event or change occurs at run time.

11. A database system according to any preceding claim in which each field has a value defined only in the context of an instance of the field within a record, the instances associated with each several records having identical or different values.

12. A database system according to claim 11 in which a formula that specifies the value in a current instance can depend upon the value of any field or fields in the current instance or in any other instance or instances.

13. A database system according to claim 12 in which a single definition of a field within the dictionary applies to all instances of the field, but the definition itself contains conditional elements.

14. A database system according to any preceding claim in which the data dictionary contains definitions of fields, which define the conditions under which operations are to be performed upon the data.

15. A database system according to claim 14 in which the conditions specify an action be taken in response to one or more of the following: an event external to the database such as an event originating in a user interface, a filesystem or another computer input device; a change within the data stored within the repository.

16. A database system according to any preceding claim in which data and actions are described in terms of results.

17. A database system according to claim 16 in which a result operation specified within the data dictionary is an operation affecting data within the repository or an operation upon objects external to the repository.

18. A database system according to claim 16 in which the objects external to the repository may be objects within a graphical user interface or another computer output device.

19. A database system according to any one of claims 16 to 18 in which the entire structure of the database, programmatic operations upon the data, and interaction with the user - both input and output - can be specified entirely within the data dictionary.

20. A database system according to any preceding claim in which the data dictionary includes display attributes that describe how the content of the field is to be represented upon an output device such as a computer display or a filesystem.

21. A database system according to claim 20 in which the representation is a direct

representation of the data within the field.

22. A database system according to claim 20 in which the representation is a representation of a value that is an output of a function applied to the value of

the field.

23. A database system according to any preceding claim including a calculation engine that analyses the dictionary and determines the conditions upon which an operation will be performed.

24. A database system substantially as herein described with reference to the accompanying drawings.