Learn More About the Database

Database Terms

Alias: The unique key of each row in the database. (You can also think of this as a single-field primary key.) Every table must have this. Does not have to be a number and can have custom procedures to assign this value.

Table: Contains data.

Field: A column of the table.

Record: A row of the table.

Relationship: An explicit relation declared in Moxie. Must have a parent and child table. Can be 1-1, 1-m, m-1, and can even have a circular relationship.

Query: The current data selected. Can be joined, appended, or moved to another query and can be passed into methods and functions. There is no upper limit on the number of queries you can have.

Attributes: MOX Database code that can be run when a field or relationship is updated. The attributes also dictate how a field is displayed (ie. date picker, hidden, etc.) It is a limited language and anything over a few lines should be placed in a message handler.


Creating a Table

The two requirements for setting up a table are: you need to know the Table Name and what you want for an Alias.

The convention for naming tables is Module.Tablename, where Module is a grouping for two or more related tables. Standard rules apply here such as: it may not contain spaces or symbols other than -, _, and .. You also want to refrain from making the table name too long as you are likely to be typing it frequently.

The default values of Auto Alias by Number and the First Auto Number works for smaller systems but if you are expecting there to be a lot of data, you should add additional digits to the First Auto Number (still starting with a leading 1.) The First Auto Number input is only used if the Auto Alias is set to Number.

For larger system that may be multi-process or distributed in the future, consider setting Auto Alias to use either CUID (which is similar to a GUID but with additional AES encryption rounds and no punctuation) or CTID (which is a time-sorted unique ID with the last six digits coming from a GUID.)

If Auto Alias is set to None, new records will need to specify the Alias. If set to Custom, you will need to set the Alias via an attribute on the Alias field or via a DB-New-Pre Message Handler Procedure.

New table dialog


Defining Fields

At the very least, a table must have an alias as a field. Usually that's not very useful, so Moxie allows us to define other fields. You can define just about as many fields as you want for a table. Fields may have Names, Labels, Notes, Attributes and Developer notes.The minimum a field needs is a Name (all other fields are optional), and the field is just assumed to be displayed as an empty textbox when rendered with the HtmlForm command.

Name: Used to reference the field using MOX code. Used as the label if no label is defined. Has almost the same rules as Aliases; may not contain spaces or symbols other than - and _.

Label: What gets displayed to the user as the name of the field in the Admin area as well as when rendering the fields with HtmlForm.

Note: A note that is shown to the user. Usually describes what the field is expecting to have placed in it. (ie. eg. Module.TableName)

Attributes: Attributes can be used to tell Moxie how to display the field in the Admin section or when rendered with HtmlForm, and can also define code to run when the field is updated. A simple example would be [DatePicker] which shows a simple calendar when the field is displayed. Attributes are largely designed to take care of simple functions straightforwardly without having to define a whole procedure for 2 or 3 lines of actual code. Click Reference List to view all of the available attributes.

Developer Notes: A note that is only shown when you view the fields in the admin area of Moxie, so other developers can see your intentions.

Language: Content.Lang values, as fetched by the Lang$ function, may also be referenced within Label, Note, and some Attributes using a format of {$ $}, such as {$Category$}. Specifying a value of just {$$} will use the current Field's fully qualified name, such as Content.Cateogry.Name.

 


Defining Relationships Between Tables

Moxie defines relationships as between a parent and a child table. This relationship can be 1-1, 1-m, m-1, and can even have a circular relationship (with some considerations).

You want to define relationships so that you can attach children and parents together; then you can easily look up which records are related to which, both in the admin interface and in the code.

Relationships


Using The Database To Find Things

The search box in the Admin area searches everything, including procedures and database definitions. Use " " to wrap multiple words that must be found together as quoted.

In the Fields tab of each Database Table, you can get a list of unique values within that field across all records, and then either List records matching a particular value, Modify a particular value, or Delete all Records with a particular value.

After clicking the arrow beside the Edit button, click <!--? i list ?--> Values. You will then a screen that looks similar to the following image:

This is handy for ad hoc work when you need to make mass changes, instead of going through each record one by one or writing a one-time procedure to make those changes.