Learn More About the Database

Database Terms

Alias: The unique key of each row in the database. Does not have to be a number and can have custom procedures to assign the key but must be unique and every table must have this.

Table: Contains data

Field: a column of the table
Record: a row in the database

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 and there is no upper limit on the amount 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 2 requirements to setting up a table is you need to know the 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. Tables are usually named such like Module.Tablename where module is more than 1 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.

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, the rest of the fields are optional, and the field is just assumed to be displayed as an empty textbox when rendered with the HtmlForm command.

Name: A way to address the field inside of MOX code and used as the label if no label is defined. Has 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 section as well when rendering the fields with HtmlForm.

Notes: a note that gets shown to the user, usually describes what the field is expecting to have placed in it

Attributes: Attributes can be used to tell Moxie how to display the field in the Admin section or when rendered with HtmlForm and also can 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. Underneath the box is a reference to all the available syntax you can use.

Developer Notes: Only show when you view the fields in Moxie so other developers can see your intentions


Defining relationships between tables

Moxie defines relationships as between a  Parent and a child. The 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, and then you can look up what record is related to which easily both in the interface and in the code.

Relationships


Using the database to find things

In Moxie, the search box does search everything including procedures and database definitions, which is extremely handy. Outside of the search box there are some other ways to find things and change them. You can also find stuff by clicking on fields on a certain table and then you can look up items by value.

Once you click on values you get this screen:

It's handy for when you want to do mass changes instead of going through each record one by one or writing a whole script to change it.