Red Queen Review Engine User Manual APPENDIX: COLUMN PROPERTIES

Adjust Text:  a a a a

« Table of Contents   |   Obtain Red Queen »

• APPENDIX: COLUMN PROPERTIES
• Column Properties
• Form Properties
• Key Properties

APPENDIX: COLUMN PROPERTIES

Each Thing and its Container is represented as a record in an SQL table, as are several other relationships within Red Queen. On this page you will find more detailed explanations of the individual component fields that make up these records--how they are represented internally as columns, and externally as form elements with which a Member, Editor, or the Administrator can interact. Keys, which indexes on the SQL tables are also briefly discussed. In order to add or modify the columns of a table you need to have an appreciation of the information presented here. For examples of how columns may be added to your tables, see ADDING COLUMNS TO A TABLE.

Column Properties

Below are the definitions for the various column properties that appear on the Add and Modify frames of the Database control panel. It may seem that the number of possible configuration options is quite large, but in practice javascript-enforced constraints are applied when selecting/updating column properties.

• Column Name: The name of your column. Valid column names consist of letters, numbers and the underscore character. To avoid column names potentially confusing to the MySQL server, a javascript check on the suitability of the column name is done within the form before submission. Almost all names WILL be found suitable.
• Column Type: This is one of the supported MySQL column types. The most commonly used ones are explained here:
  • INT: Used to store an integer field, i.e. whole numbers like 0, 1, 2, ... Strictly, the signed range is -2^31 to 2^31 - 1. The unsigned range is 0 to 2^32 - 1. Other integer types can be used to store smaller/larger ranges.
  • FLOAT: A fractional number, such as 3.14159. Can vary in size from roughly ±1.2E-38 to 3.4E+38. Good for storing statistical measures. If you have to deal with either REALLY small or REALLY large fractional numbers, research the DOUBLE and DECIMAL types. Otherwise stick to using FLOAT. Precision can be specified with the Column Precision parameter. The format is M,D where M is the number of characters used to display the floating point number, including the decimal point and any leading sign, and D is the number of decimal places used.
  • CHAR: Used to store a fixed-length string, ranging in size from 0 to 255 characters. The size must be specified by the Column Size parameter. Internally, MySQL right pads strings shorter than 255 characters with spaces before storing, then strips the spaces when retrieving. Use CHAR for small strings known not to exceed a certain size; retrieval of fixed-length strings is fast. e.g. email addresses restricted to 60 characters.
  • VARCHAR: Similar to CHAR, but used to store variable-length strings ranging in size from 0 to 255 characters. The size must also be specified in the Column Size parameter. Internally, MySQL removes trailing spaces before storing. Use VARCHAR if dealing with strings that vary widely in size and can be large. e.g. website URLs. This saves on storage space, but retrieval of variable length strings is slower.
  • TEXT: Used to store a large string, up to 64 kB in length. e.g. the content of an article. other TEXT types can be used for storing smaller/larger documents.
  • BLOB (not supported in Red Queen): Differs from TEXT type only in that it is case sensitive in string comparison tests. Commonly used to store binary files. Other BLOB types can be used to store smaller/larger (possibly binary) strings.
  • ENUM: Used to store a list of alternate values, such as available shoe sizes, or seating area identifiers. Good for storing data that a user will select from a SELECT, or RADIO menu. Values are not case sensitive in string comparisons. An example ENUM set to represent pizza toppings might be "Peperoni", "Tomato", "Basil", "Mushroom".
  • SET: Similar to ENUM, but can store a subset of the alternate values making up the list, instead of just a single value. Might be used to represent add-ons to a car purchase: "Sun roof", "Leather Upholstery", "Wide-rim wheels". Useful for storing values collected from a multi-SELECT or CHECKBOX menu.
  • DATE: Used to store a date in 'YYYY-MM-DD' format. Default value is '00-00-00' when the column is declared NOT NULL.
  • TIME: Used to store an elapsed time (so values can be negative). Format is 'hh:mm:ss' -- hours, mins, seconds. Range is from '-838:59:59' to '838:59:59'. Default value is '00:00:00' when the column is declared NOT NULL.
  • DATETIME: Used to store both a time and a date. Both parts are required and the format is 'YYYY-MM-DD hh:mm:ss'. Range is '1000-01-01 00:00:00' to '999-12-31 23:59:59'. Default value is '0000-00-00 00:00:00' when the column is declared NOT NULL.
  • TIMESTAMP: Used to store a time and date timestamp of the form 'YYYYMMDDhhmmss'. A display length M can be specified using the Column Size paramter. M can be any of the even integers 2,4,6,8,10,12, or 14. Default value is the current date and time.
  • YEAR: Used to store a four-digit year in YYYY format. A display length M can be specified using the Column Size paramter. M can be either of the even integers 2 or 4. Default value is '0000' when the column is declared NOT NULL. Range is '1900' to '2155'.
• Column Size/Precision: Several of the MySQL column types can have a display size M associated with them. This is the number of characters used to display values for the column. If a number requires more than M characters to be displayed correctly it will receive them. Values are not chopped. Others (non-integer numeric types) may also have a precision, or the number of decimal places. These are covered below. When supplying a column size/precision the format is M or M,D if a precision is involved.
  • INT: All of the integer types MAY have a display size M associated with them. M should be a value from 1 to 255, if supplied.
  • FLOAT, DOUBLE, DECIMAL: For FLOAT and DOUBLE a display size M may be specified (1 to 255) and also a precision D. D may be from 0 to 30, but no more than M-2. For DECIMAL, the display size and precision are REQUIRED.
  • CHAR and VARCHAR: The string types CHAR and VARCHAR may have a display size M, again with M from 1 to 255.
  • TIMESTAMP: Although the TIMESTAMP value is stored as 14 digits, the display value can be of length M = 2,4,6,8,10,12, or 14 characters. This provides a range of strings from the 2-digit year 'YY' all the way up to the 14-digit timestamp 'YYYYMMDDhhmmss'.
  • YEAR: Set M = 2 for a 2-digit year, or M = 4 for the default 4-digit year.
• Column Values: For ENUM and SET datatypes the possible values a column can assume can be thought of as a list. The values of that list should be entered into the textarea box, each separated by a linebreak.
• Attributes: Certain datatypes can have extra attributes associated with them. These are covered below:
  • BINARY: The CHAR and VARCHAR datatypes are case-insensitive string types. These can be made case-sensitive by specifying that they have the BINARY attribute.
  • UNSIGNED: The integer types can have a negative as well as a positive range. By specifying that they be UNSIGNED the negative range is removed, doubling the number of positive values that may be stored. For example, the TINYINT datatype has a range of integer values from -128 through 127, but when UNSIGNED has the range 0 through 255.
  • ZEROFILL: If the ZEROFILL attribute is specified for a numeric datatype, the displayed value is padded with leading zeros to the display width.
• Not Null: If a column MUST have a value associated with it, even if that value is the empty string, then set it as Not Null. Otherwise, if it would be convenient to store records with missing column values, then do not specify the column as Not Null. In that case missing values are represented internally with the NULL value.
• Extra: For every table, at most ONE column can be an AUTO_INCREMENT column. A column of this type represents an integer field and has the property that when a NULL is inserted for a new record, the column assumes a value one greater than the current maximum value for the column. An AUTO_INCREMENT column is used primarily to ensure a unique column value for every record, an thus usually appears in the PRIMARY KEY definition for the table. This application REQUIRES that all AUTO_INCREMENT columns appear as part of the PRIMARY KEY (which almost always should consist of the AUTO_INCREMENT column only).
• PRIMARY KEY Column: Toggle this select menu to specify that the column should be part of the PRIMARY KEY for the table. In most cases, the PRIMARY KEY will consist of a single column, usually an AUTO_INCREMENT column if an abstract numeric representation of the records is appropriate. This option to specify that the column should be part of the PRIMARY KEY is ONLY PRESENT when CREATING a table. Altering the PRIMARY KEY after table creation is possible, unless foreign key constraints have been added to other tables which reference the current one, so make life easy on yourself by thinking carefully about the PRIMARY KEY before creating the table.
• PRIMARY KEY Position: If the column is part of the PRIMARY KEY (all new tables must have a PRIMARY KEY defined before they can be created) then the column's position within the PRIMARY KEY is specified by the value of this select menu. This option also appears only when CREATING a table.
• Column Position: Represents the column's position within the table. Columns that appear in the PRIMARY KEY generally go first. TIMESTAMP columns generally appear last. This is merely standard practice; columns can be ordered as you please.
• Default: Represents the value that will be inserted into a column if the column is No Null and no other value is explicitly provided.
• Search Weight: Columns that ought to be searchable via keywords or keyphrases can be assigned a search weight (a positive integer). Columns with a search weight will be indexed and the indexing information stored in separate tables for fast lookup. CHAR and VARCHAR are the datatypes best suited to indexing. BLOB and TEXT datatypes cannot be indexed this way.

Form Properties

In order to collect values from the user to add or modify a record, form elements must be placed on the page. Specification of the form element for each column in the table is carried out with the following variables. As is the case for the Column Properties, javascript-enforced constraints are applied when specifying the Form Properties.

• Form Display: The label, or name, of the column shown in forms. e.g. 'Email Address' for the User column named 'email'.
• Form Type: The type of form element used to collect a new value for the column, or to edit an old value. The various types are covered below:
  • HIDDEN: Pass a value through a CGI form without displaying its value. Limited usefulness. Might be used to pass around an auto_incremented column value (as it should not be altered once created).
  • SELECT: Present a drop-down select menu of mutually exclusive choices for the column value. May be used to present values associated with an ENUM datatype.
  • MULTIPLE: Present a select menu of mutually compatible choices for the column value. May be used to present values associated with a SET datatype.
  • CHECKBOX: Present a series of mutually compatible checkbox choices for the column value. May be used to present values associated with a SET datatype.
  • RADIO: Present a series of mutually exclusive radio choices for the column value. May be used to present values associated with an ENUM datatype.
  • TEXT: Present a textbox for string input. Useful for CHAR and VARCHAR types. Also small TEXT types.
  • TEXTAREA: Present a textarea box for larger input strings. Useful for medium-sized TEXT types.
  • PUBLIC_FILE, PRIVATE_FILE: Present the file upload button for retrieving a file from the user's local machine. The file is actually stored in the filesystem and NOT internal to the database. The file is assigned an UPLOAD ID and the column should be of INTEGER type. Files for this form type can be of any type, but should NOT be images (which should be handled by the next form type). For more information see the tb_hashing_depth configuration variable
  • PUBLIC_IMAGE, PRIVATE_IMAGE: Present the file upload button for retrieving an IMAGE file from the user's local machine. Otherwise identical to the PUBLIC_FILE and PRIVATE_FILE form types. The main difference is that the file is assumed to represent an image and special formatting will be applied when presenting links to these image files (e.g. image width and height will be determined for IMG tags). For more information see the tb_hashing_depth configuration variable
  • DATE: Prsent a dropdown menu for selecting a time interval over which to perform a search. As, for example, all records that date from 3 months ago. Useful for representing the DATE, TIME, DATETIME, TIMESTAMP, and YEAR datatypes.
  • DATE_SELECTABLE: Present select menus for the year, month, and day. Present textboxes for the hours, minutes, and seconds associated with the column. Allows an exact date (or elapsed time, for a TIME column) to be selected. Useful for representing the DATE, TIME, DATETIME, TIMESTAMP, and YEAR datatypes.
  • URL: Otherwise identical to the TEXT form type but especially identifies a string as a URL (allowing URL-related options to be implemented for the column). Presents a textbox for URL input. To be used with either CHAR and VARCHAR column types.
  • URL_STATUS: Otherwise identical to the TEXT form type but especially identifies a string as an HTTP STATUS code for a URL column (this allows the status code for the associated URL column to be recorded when the URL is retrieved). Presents a textbox for the HTTP STATUS code input, though normally this will be determined automatically. To be used with one of the INT column types. If the URL column is named 'frog_url' then the associated column with the URL_STATUS form type should be named 'frog_url_status'.
• Form Size/Length: These two parameters have an interpretation that depends on the form type being considered. If S is the size, and L the length the combination should be specified as S,L. A length cannot be specified unless a size is also.
  • TEXT/URL/URL_STATUS: 'Size' represents the length of the input box. 'Length' can be used to specify the maxlength attribute of the input box.
  • TEXTAREA: 'Size' represents the width of the input box, or number of columns displayed. 'Length' represents the number of rows.
  • SELECT/MULTIPLE: For a 'Size' greater than 1, the popup menu becomes a scrolling list, with the 'Size' representing the number of choices that can be seen without scrolling.
  • PUBLIC_FILE, PRIVATE_FILE, PUBLIC_IMAGE, PRIVATE_IMAGE: 'Size' represents the length of the filepath input box.
• Form Values: For SELECT, MULTIPLE, CHECKBOX, and RADIO form types you may wish to show the user labels (or display values) in place of the actual column values they may choose from. e.g. instead of the values 'super', 'medium', and 'small' for cup sizes which make up an ENUM list you may want to display 'Super Sized', 'Medium', and 'Child'. Each form value should appear on its own line, one for each of the actual values associated with the column. If no form name is provided, the actual value itself will be used as the label.
• Form Regex: This string is interpreted as a perl regular expression pattern and is used to filter the column value obtained from a TEXT form type. Useful for validating the format of a user email address, and similar tasks. The pattern is performed server side, after the form has been submitted. an example: ^https?:\/\/ to check the format of a supplied URL.
• Hide on Add/Modify Forms: Toggle this option to 'Yes' and the field will NOT appear on any Add or Modify form. Many inaccessible system columns are hidden this way. Unless you are adding a field that you don't intend to be managed by this application, leave the the form element visible. Exceptions might be the addition of date/time columns which will automatically be filled at the time a record is created (or modified if it is the second TIMESTAMP column). For the most part, hidden columns are meant to be managed by a Table subclass named after the table containing the column.

Key Properties

Keys, or Indices, are applied to one or more columns of a table in order to achieve one or more effects. A regular INDEX is placed on one or more columns so that records with a given set of values may be looked up quickly. A UNIQUE index is placed on one or more columns ensure that a given set of values can occur for no more than one record in the table. Finally, a PRIMARY KEY is just a UNIQUE index with the name PRIMARY. You may add keys to a table at any time, though usually you do this just after the table is created. If you drop a key from the table that has been placed there to facilitate lookups, don't be surprised if the program starts taking a much longer time to complete its queries--loss of a key can result in server-busting full table scans.

• Key Name: The name of a key on one or more columns. Valid key names follow the same rules as column and table names: they consist of letters, numbers and the underscore character.
• Key Type: PRIMARY KEY columns (not covered here) are specified at the time a table is created. When adding extra keys on the table you may choose between a regular (i.e. INDEX) key, defined on columns that may have repeated values in the records of the table, or UNIQUE keys which require that no two records contain the same set of values for the columns that make up the key. If you atempt to place a UNIQUE key on a table that already has non-unique records, the attempt will fail.

« Table of Contents   |   Obtain Red Queen »