:: madsearch 2 :: creating a table

Creating A Table

It All Starts With A Mouse Click

Before creating a table prepare yourself:

What entities are you trying to describe through your table?
What attributes characterize this entity?
How large are the data values that specify these attributes?
Must these attributes be of a certain format?
Where are you going to store your table?
Have you prepared the directory?
What security/restrictions do you need to place upon the records within the table?

Once you have answered these questions you're ready to define your entity through the structuring of its attributes in table form.

Records & Data Fields

When you create a new table, you first create it's structure. A table's structure is stored within it's ".msstr2" structure file. The structure file specifies the structure of the data table at the level of its records.

A record is defined within madsearch2 by breaking it down into its compositedata fields which store the attributes of an entity record. Fields are, in turn, further broken down and defined by the following elements:

Field Name
Field Type
Field Length
Field Format
Key
Required
Default Value

Taken together, these defining elements of a data field are called a "field definition.

Once you've entered the table structure utility, you'll see the following:

Each row of this table contains all the elements necessary to create a field definition. Each column represents an element of a field definition. The first row corresponds to the field definition of the first row within our table. The second row corresponds to the field definition of the second row, and so on...

Field Definitions

Field Names

The field name element of a field definition names the attribute to be stored within a column of a table. Field names may contain any combination of alphanumeric characters. Spaces are not permitted.

Field names are used in constructing queries against data tables and to refer to data fields within reports.

Field Type

The field type element defines the type of attribute information to be stored within a data field. The field type determines how comparisons are made against values within this field in addition to what types of data are valid.

Whenever a data field is assigned a value, madsearch2 validates the appropriateness of the value given a field's type. Validation of input includes validation of a data type's range, formatting, minimum length, and content. For example, if a field were declared to be of type "Integer", the following input values would be unacceptable:

Input Value	Reason for Invalidity
999999999999999	Out of Range. An integer value can at most be 2,147,483,647 in madSearch2
Barnes & Snobles	Not an integer
Ninety	Although ninety is the name of an integer, a field of type integer expects a numeric representation.
9.45	This is a floating point (decimal) number. Integers do not contain decimal points.
6.54e20	Just another floating point number.

There are two categories of field types: data types and security types. Data types store attribute values. Security types store information permitting/restricting who may view/modify records and their attributes.

The following is a table describing the various data field types and the values that may be entered within them:

Data Field Type	Description
Alphanumeric	An alphanumeric field may be used to store any mixture of letters, digits, any any other printable ascii characters. If you specify an alphanumeric field, you must specify the size of the data field -- how many characters the field will maximally contain. It is important to note that madSearch2 does not interpret the data stored within alphanumeric fields.
AutoIncrement	The autoincrement field type is a non-modifiable field that assigns a sequential, integral number (starting with one) for each record inserted into the table. The autoincrement number assigned to a new record cannot be changed and is guaranteed to be unique for the lifetime of the table. No more than one autoincrement field may be specified for a table. It is often used to provide unique id numbers to the records stored within a table.
Date	Date fields store the month, day, and year elements of dates. Date fields recognize leap years and leap centuries. Whenever a field of type date is specified, you must additionally define the formatting of dates to be stored within the field. For example, do you store dates as mm/dd/yyyy or dd/mm/yyyy?
DateTime	DateTime fields store within them both a date and time value. DateTime fields are often used to time stamp records. Whenever a field of type datetime is specified, you must additionally define the formatting of the datetime values to be stored within the field.
Integer	The integer field type is used to store whole numbers in the range of -2,147,483,647 to 2,147,483,647. The following integer string formats are recognized: value sign value
Float	The float data type is used to store decimal numbers in the range of 1E-307 to 1E+308. The following float string formats are recognized: value or sign value where the value consists of significant or significant efield where the significant must contain one or more digits and may contain one decimal point; possible forms of the significant include are as follows: digits or digits. or .digits or digits.digits The efield may consist of the following: echar digits or echar sign digits or where echar is one of either "e" or "E", and digits contains one or more digits. Fields of type float are used in storing currency values, grade point averages, etc.
Time	Time fields store the hour, month, and seconds of time values. Whenever a field of type time is specified, you must additionally define the formatting of the times to be stored within the field.

The next data types we will describe are security field types. Security data types are used in establishing record level security. Record level security is the ability to specify, record by record, who may view, modify, or delete a record.

In implementing record-level security, madsearch2 takes its cue from unix file system permission levels. In madsearch2, just as in unix, there are three levels: user, group, and other. For each level, madsearch2 allows you to specify which group may have access and what types of access (i.e. what permissions they have with a given record).

The following table describes the five security data types:

Security Field Type	Description
User	The user field is made up of alphanumeric characters specifying the user id of the user to which the record belongs.
User Permissions	The user permissions field contains characters representing permissions the user owner has to the record. Valid permissions characters are as follows: "R"ead permission: permission to read/view the record "U"pdate permission: permission to update/change the record "D"eletion permission: permission to delete the record "A"ccess Control permission: permission to provide record access to a specific group/others or set/modify user/group/other permissions.
Group	The group field is made up of alphanumeric characters specifying the group owner of the record.
Group Permissions	The group permissions field contains characters representing permissions the group owner has to the record. The group permissions field may contain the same permissions characters as those of a user permissions field.
Other Permissions	The other permissions field contains characters representing permissions the rest of the world have to a record. The other permission field may contain the same permissions characters as those of a user permissions field.

There are a number of rules and restrictions imposed on the values of these fields. For example, unlike unix file system permissions, the user owner of a record cannot grant themselves a user permission (including the access control permission) unless the table administrator has previously granted them the access control permission for the record. A detailed explanation of madSearch2 record level security is contained within the section "Security".

It is extremely important that you understand madSearch2's security features and policies prior to creating a table for an application. For now, it suffices to know that these fields exist and are used for the stated purposes.

Field Length

Many of madSearch2's field types store fixed-length values. For example, integers are always stored as a 4 byte (32 bit) value.

However, the following fields require that you specify the maximum space to be reserved to accomodate the field's values:

AlphaNumeric
User
Group

These fields are sometimes referred to as variable width fields. Their size is specified as the number of characters necessary to accomodate their possible values. For example, a field storing social security numbers that are formatted to include dashes (###-##-####) would require a field size of 11.

It is important that you not under allocate space for your data. You do, after all, want to be able to store all possible valid values. However, it is also in your interests not to over allocate. madSearch2 provides no means for compressing large alphanumeric fields. As a general rule: the smaller the table, the better the performance.

Field Format

A useful madSearch2 data validation feature is that of imposing a field format on field values. For example, one may wish that date values always be entered in the format "mm/dd/yyyy" or that social security numbers be entered as "###-##-####". The field format element of a field definition provides this functionality.

There are two classes of field formatting options: those for alphanumeric-type fields and those for date/time type fields.

The first class of field formats, allows you to specify a regular expression as the field format. All data field values must match the regular expression provided. The following table describes the characters that combine to form a regular expression in madSearch2:

Special Characters	Description
#	Numeric digit
?	Any letter (uppercase or lowercase)
&	Any letter (convert to uppercase)
~	Any letter (convert to lowercase)
@	Any character
!	Any character (convert to uppercase)
^	Interpret the next character as a literal, not as a special character.
*	Any number of repeats of the following character
[abc]	Optional characters a, b, or c
[a,b,c]	Optional characters a, b, or c
{abc}	Set length clause (i.e. [g{abc}] means either clause g or abc appears as optional clause
_	Represents a space in regular expression

For those of you who found the preceeding table to be rather dense, the following examples are provided to illustrate madSearch2 regular expressions:

Regular Expression	Value(s) Matching Expression
"[{(###)}{###}]-##-##"	"(777)-44-44" or "888-44-66"
*[#{[&]?}_]5&-	"4y5t-" becomes "4y5T"
"[{ab[dc{op}]ef}t]"	"abopopef"
"*[#?]@columbia.edu"	"ccs4@columbia.edu"

The second class of field formatting expressions are those that deal with date, time, and datetime field value validation.

The format strings for date, time, and datetime fields are made of formatting descriptors which specify which pieces of the field value are to be incorporated in the value presented. You can think of their format strings as a combination of surrounding text and descriptors where the surrounding text provides structure. You can also think of them as mini-templates.

Fields of type date and time do not share descriptors within one another. Therefore, we present them in separate tables. Fields to type datetime can mix and match the descriptors of both date and time fields within their format strings.

Date Field Format Descriptors

Field Descriptor	Description
%a	day of week, using abbreviated weekday names
%A	day of week, using full weekday names
%b	month, using abbreviated month names
%B	month, using full month names
%d	day of month (1-31); single digits are preceeded by a blank
%D	date as %m/%d/%y
%j	day number of year (1-366)
%m	month number (1-12)
%U	week number of year (1-52); Sunday is the first day of the week
%w	day of week; Sunday is day 0
%W	week number of year (1-52); Monday is the first day of the week
%x	date, using locale's date format
%y	year within century (00-99)
%Y	year, including century (for example, 1988)

Time Field Format Descriptors

Field Descriptor	Description
%H	hour (00-23)
%I	hour (0-12)
%k	hour (0-23); single digits are preceeded by a blank
%l	hour (1-12); single digits are preceeded by a blank
%M	minute (0-59)
%p	AM/PM
%r	time as %I:%M:%S %p
%R	time as %H:%M
%S	seconds (0-59)
%T	time as %H:%M:%S
%X	time, using locale's time format
%Z	time zone abbreviation

Key

madSearch2 supports primary keys on its tables. A primary key is used to uniquely identify records within a table.

A madSearch2 primary key can consist of one or more fields. A primary key made up of multiple fields is referred to as a composite key. The primary key is used to build an index on the table. This index orders records within the table and speeds various query operations that reference key fields.

The field(s) of a primary key must appear as the first fields within the table. In a composite primary key, just as in a one field primary key, the key fields taken together must uniquely identify a record.

In specifying that a field is part of the primary key, you may select "ascending" or "descending" from the "Key" element of the field definition. The "ascending" option instructs madSearch2 to sort values in the field in ascending order: a -> z, 1 -> 9. The "descending" option instructs madSearch2 to sort values in the field in descending order: z -> a, 9 -> 1.

In ordering the records of a table with a composite primary key, madSearch2 begins with the first field and continues to sort according to the following fields of the primary key.

madSearch2 enforces primary key constraints on tables for which a primary key has been defined. Any attempt to insert a record whose primary key field(s) duplicate those of another record within the table yields a "key violation" and a rejection of the record. Records violating key constraints are removed from the data table and written to an associated non-keyed table. The non-keyed table's name is that of the core data table with the phrase "-keyviol" concatenated. For example, if your table was named "people" and located within the directory "/wwws/data/cu/myData", it's associated key violation table would be "people-keyviol" located in "/wwws/data/cu/myData".

If a field is not a member of the primary key, leave the "Key" element set to blank (no selected option).

Required

A required field is one for which a value must be specified at all times. MadSearch2 checks that the required field constraint has been met when the query is executed. The keyword "blank" (to be described later) is not acceptable for a required field as "blank" specifies the absence of a value.

Default Value

This field definition element specifies the default value to be given to the associated field of a newly inserted record should no value be specified.

Any default value provided must conform to the field type, field length, field format, and field content restrictions specified or implied.

Creating A Table

To create a new madSearch2 table, simply follow the instructions below. The instructions assume that you have prepared a directory in which to store your new table as per the directions in the previous section, "Preparing A Directory For MadSearch2 Data Tables"

Creating A New Table
step 1	Go to the madSearch2 main menu. Click the "Create New Table" option. The "Create Table Structure" utility screen will load within your browser.
step 2	In the field to the right of "Database Directory", type in the path/location of the directory in which your table is to be stored. Note that the directory path is case-sensitive.
step 3	In the field to the right of "Table Name", provide your table with a name. The name may contain any combination of alphanumeric characters. Spaces and all other non-alphanumeric characters are not permissible. Note that table names in madSearch2 are case-sensitive since the unix file system is case-sensitive.
step 4	In the table that follows the "Table Name" field, define the structure of your table by specifying, through field definitions, the attributes that define the entity your table seeks to model. Use the preceeding discussion of the elments of a field definition to guide you.
step 5	When you have completed defining the structure of your table, click the "Create Table" button in the lower left hand corner of your browser window. madSearch2 will then attempt to create the table you have specified. Should you have specified conflicting or improper parameters or violated a table structure restriction, madSearch2 will inform you of your error and ask you to correct it.
step 6	When madSearch2 completes the task of creating the new table, it will show you the structure of the new table.

A Few Final Thoughts & A Promise

When you create a madSearch2 table, you are the administrator of the table. The administrator of the table is the person responsible for granting users access to the table and its data. The administrator is a privileged role which allows you to use various madSearch2 utilities to import, export, encrypt, modify, and empty the table.

By default at the table level of security, all other users will have "read" permissions on the table. We will delve into madSearch2 security further in a later section entitled, "Security".

For now, this may not mean very much to you. It is very important to remember these default assignments when you later consider further access control/security arrangements.