This section describes concepts concerning the design of a 4D Server database. An understanding of these concepts will be helpful to non-4D Server users who want to interpret the information returned by 4D Open for Java's structure definition methods.

The sections in this chapter include:

Table and fields,

Table and field attributes,

Field types,

Table relations,

Discriminant fields,

Deletion control.

In 4D Server, the structure of a database is organized into tables and fields.

Each table is typically given a name that describes the category of information it will store. Each field in the table stores one piece of information in that category.

The following graphic shows a table named People. Each field in the People table contains information that will be stored for each person. For example, the LastName field stores each person's last name.

You can get the number of tables in the database by calling CountTables and the names of the tables by calling the GetTableNames method.

You can get detailed information about a specific table and its fields by calling GetTableProperties method. This information includes the table and field attributes as well as the field types.

If a table has the Invisible attribute set, users cannot see the table or its fields in 4D Client's User and Runtime environments. This attribute is most often used to hide unused or maintenance tables from the user. When you call GetTableProperties method, 4D Open for Java returns information about the specified table and its fields, whether or not the table is invisible. If a table has the Invisible attribute, you may want to mimic 4D Client's behavior by hiding the table in your 4D Open for Java application.

In 4D Server, each field has a field type. The field type determines the type of information that can be stored in the field. There are 11 available field types.

When you call GetTableProperties, 4D Open for Java returns each field type. A description of each type follows below.

Alpha: Alpha field type can contain alphanumeric characters (letters and numbers), punctuation marks, and special characters such as the asterisk (*), percent sign (%), and hyphen (-). An Alpha field can store up to 80 characters. However, the maximum length of the field can be set by the database designer to be any length between 2 and 80 characters.

Text: Text field type can hold up to 32,000 alphanumeric characters. It is used to hold blocks of text that are longer than 80 characters.

Real: Real field type can contain real numbers within the range of 1.9E-4951 to 1.1E4932.

Integer: Integer field type can contain whole numbers within the range of ±32,767.

Long Integer: Long Integer field type can store whole numbers within the range of ±2,147,483,647.

Date: Date field type can store dates within the range of 100 to 32,767 AD.

Time: Time field type can store hours entered in HH:MM:SS format.

Boolean: Boolean field type can store information that can be one of only two choices, such as Sex (M/F) or Married? (Yes/No). If the first choice is selected, the value of the field is TRUE. If the second choice is selected, the value of the field is FALSE.

Picture: Picture field type can store graphics created using Macintosh applications that produce graphics in PICT or PICT2 formats. In fact, picture fields are often used to store various types of data. A Picture field accepts up to 8 MB of data. For instance, the 4D plug-ins such as 4D Write or 4D Draw can save their documents in Picture fields. Using the terminology adopted by the major Windows databases vendors, 4D Picture fields are BLOBs (Binary Large OBjects).

Subtable: Subtable field type is a field that associates a complete table with each record. For example, a People table could include Children as a Subtable field. The subtable associated with the field —also called Children— contains a record for each person's children (their names, ages, birth dates, and so on).

Note: Due to its uniqueness in the database world and also for compatibility with future versions of 4th Dimension, subtable fields are not accessible using 4D Open Suite.

BLOB (Binary Large Object): BLOB field type can store all binary objects as graphical object, application or document. Blobs can hold up to 2 Go.

When you call GetTableProperties, 4D Open for Java indicates whether a given attribute is set for each field.

The field attributes can be divided into two groups:

User interface attributes,

Database engine attributes.

User interface attributes are used mainly for data entry and are checked only by 4D Client. These attributes include Mandatory, Display only, Can't modify, Choices, and Invisible. If any of these attributes have been selected, you may want to mimic their behavior during data entry operations. For instance, if the Mandatory field attribute has been selected for a Social Security field, you may want to ensure that records entered from your 4D Open for Java application are not accepted unless a social security number has been entered.

The Database engine attributes —Indexed and Unique— are used by 4D Server. Since they are used by 4D Server rather than 4D Client, their behavior applies to your 4D Open for Java applications. The index table, if one has been created, is automatically used for queries and order by. Moreover, 4D Server checks the Unique attribute for all new or modified records, even those entered from within your 4D Open application.

Mandatory: In 4D Client, when the Mandatory attribute is set for a field, you must enter a value in that field during data entry. 4D Client will not accept a record that contains an empty mandatory field. If a field has the Mandatory attribute set, you may want to mimic 4D Client's behavior by making the user enter a value for this field in your 4D Open for Java application before adding or modifying the record.

Display Only: You cannot enter values into a field that has the Display Only attribute set. The value set for the field can be displayed, but not modified. If a field has the Display Only attribute set, you may want to mimic 4D Client's behavior by allowing the user to view but not modify this field in your 4D Open for Java application.

Can't Modify: If the Can't Modify attribute is set for a field, 4D Client accepts the value initially entered in the field, but does not allow you to modify the value after the record has been saved. If a field has this attribute set, you may want to mimic 4D Client's behavior by not allowing the user to modify this field in your 4D Open for Java application.

Indexed: The Indexed attribute causes 4D Server to create an index table for the field. This index table allows 4D Server to perform rapid queries and order by on the field. Alpha, Real, Integer, Long Integer, Time, Boolean, and Date fields can be indexed.

Note: 4th Dimension indexes are binary tree-like indexes. Contrary to some other database programs in which a primary key field allows you to choose a default order for the rows of a table, 4th Dimension's indexes allow you to sort the records in both ascending and descending orders.

Unique: The Unique attribute requires that each record have a different (unique) value in this field. The Unique attribute prevents duplication of empty values as well as actual entries. If a field has the Unique attribute set, 4D Server automatically checks if the field is unique when adding or modifying the record from your 4D Open for Java application. If the field value is not unique, an exception is generated.

Choices: In 4D Client, the Choices attribute connects a choice list to a field so that the list appears when the user attempts to enter data in the field.

Note: With 4D Open you cannot access choice lists.

Invisible: When the Invisible field attribute is selected, users cannot see the field in 4D Client's User and Custom Menus environments. This attribute is most often used to hide unused fields from the user. If a field has the Invisible attribute, you may want to mimic 4D Client's behavior by hiding the field in your 4D Open for Java application.

In 4D Server, you can have multiple tables in each database. These tables can be related to each other by means of table relations. In table relations, one table is related to another table through a common, related field in each table. The values in the related fields indicate which records in each table are related to each other.

For instance, the following graphic depicts the relationship between the [People] table and the [Companies] table in a personnel database. The two tables are related by means of the related fields, CompanyID (in the [People] table) and CompanyIDNum (in the [Companies] table).

When a person's record becomes the current record for the [People] table, 4D Server looks at the value in the CompanyID field and searches in the [Companies] table for records with the same value in the CompanyIDNum field.

The automatic selection of a record in the [Companies] table occurs as long as the relationship is an automatic Many to One relationship (described in the next section).

This process also works when a company record becomes the current record for the [Companies] table. In this case, 4D Server looks at the value in the CompanyIDNum field and searches in the [People] table for records with the same value in the CompanyID field. This occurs as long as there is an automatic One to Many relationship between the tables.

When you call GetTableProperties, 4D Open returns for each related field the number of the table and field to which it is related. For instance, given the following structure, the information returned for the [People] table would indicate that it is related to field 1 (CompanyIDNum) of table 2 ([Companies]).

When you create a relation in 4D Server, there is a Many table and a One table. The tables are called the One table and the Many table because one record in the One table relates to many records in the Many table and many records in the Many table relate to one record in the One table. This type of table relation is called a Many to One relation.

Taking the previous example, the [People] table is the Many table and the [Companies] table is the One table. Many people work for one company and one company employs many people.

The concepts of the Many table and the One table are important because they reflect the type of selection made in each table when a record from the other table becomes the current record. If a record in the Many table (People) becomes current, one record from the [Companies] table will be selected. If a record in the One table (Companies) becomes current, many records from the [People] table will be selected.

4D Open for Java contains two methods, RelateOne and RelateMany, that rely on the distinction between the One table and the Many table. These methods create a selection of records in a table based on the current selection of records in the related table.

When a relation is created between two tables, 4D Server allows the database designer to choose a discriminant field from the One table. This discriminant field is used by 4D Client when the user types "@" (the wildcard character) in the related field in the Many table. When the user types this character, 4D Server displays a list of possible choices for the field, taken from the records in the One table.

For instance, if the user enters "100@" in the CompanyID field of the [People] table, 4D Client displays a list of all the companies in the [Companies] table whose ID numbers start with "100." The list contains the company ID numbers (from the CompanyIDNum field) and the values from the discriminant field chosen when the relation was created. If the discriminant field is the Company Name field, for example, the list displays the company ID numbers along with the company name.

To reproduce 4D Client's behavior regarding the discriminant field, you could use GetTableProperties to determine which field is the discriminant one, call Search to search on the related table, download the fields you need by calling LoadFields, and finally display the data to the user in a list, pop-up menu, or any other interface item.

Note: In 4D Client, a Selection window such as the one displayed above appears in only two cases. A Selection window will appear if the user types an "@" in a related, alphanumeric field in the Many table; or, for a related field of another type if that related field matches more than one record in the related One table. In this case, there are duplicated values in the One table and the Selection window appears to allow the user to select the record to which the Many record should be related.

For each table relation in a database, the database designer may want to control what happens when a user deletes a record in the One table. If a user deletes a record from the One table, should the records in the related Many table automatically be deleted? Should the user be prevented from deleting the record?

When tables are related, 4D Server allows the database designer to specify one of three options for records deleted from the One table. Before selecting an option, the Allow Deletion Control option from 4D Client's Database Properties dialog box must be selected. The options described below will then be enabled:

Leave Related Many Intact: Selecting this option allows the user to delete a record in the One table, leaving the corresponding records in the Many table untouched. When the records are deleted, the records in the Many table do not correspond to a record in the One table. This option is set by default.

Auto Delete Related Many: Selecting this option allows the user to delete a record in the One table. 4D Server then automatically deletes the corresponding records in the Many table. This choice ensures that no related Many records exist without a corresponding related One record. This choice makes it easy to delete unwanted records, but it may also result in deleting records that should not be deleted.

Can't Delete if Related Many: Selecting this option allows the user to delete a record in the One table, but only after all corresponding records have been deleted from the Many table. This choice ensures that no records exist without corresponding records and that no records are mistakenly deleted, but it requires the prior step of deleting all corresponding records from the Many table.

The GetTableProperties method returns information about the deletion control options specified for a field. It first indicates whether deletion control checks are enabled in the 4D Client Properties dialog box and then indicates whether records can be deleted from the One table. If the records cannot be deleted from the One table, it means that the "Can't Delete if Related Many" option was selected.

The tables, fields, and the relations between the tables are depicted in 4D Client's Structure window.

If you want to recreate the Structure window in your 4D Open for Java application, you can use the coordinates returned by 4D Open for each table in the database to determine where you should place each table in a window. The coordinates are returned by the GetTableProperties method.