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 default alias works for most types of data but if you are expecting there to be a lot of data, you may want to make the number bigger, or you can use a custom value or a CUID (which is similiar to a GUID but using our own algorithm to generate it). Auto-number only works if the type of alias is a number, so if you choose to use something else, you will need to specify the alias yourself or create a procedure that auto generates the 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.
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.
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:
- <!--? i list ?--> List: This displays all records with this value.
- <!--? i refresh ?--> Modify: This lets you change all records with this value.
- <!--? i remove ?--> Delete: This lets you delete all records with this value.
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.