Create a First Table

In this tutorial, we will begin creating our database. We will start the finance package with a basic order system. This tutorial shows how to create the first table in that system. The first table will be the customers table.

Related Reference Material

The topics covered in this tutorial are also covered in reference sections Application Directories, Database Specification File, module, column, table.column, and table.

About the YAML Syntax

Andromeda uses files in the "YAML" format (http://www.yaml.org) for its data definition files. The basic things you need to know for now about the YAML format is that it depends upon indentation, and cannot use TABs for indentation. If you use TABs for indentation your file will not work. The indents do not have to be any particular size, like 2 chars or 6 (we use 3), but they must be consistent.

The YAML format should be mostly self-explanatory, but one thing bears mentioning. You do not have to quote strings in a DD.YAML file, but you do have to put double-quotes around a "Y" for something like 'permsel: "Y"', otherwise the YAML processor will turn it into a number 1.

The DD.YAML File

When you built the finance application for the first time in the previous tutorial, a collection of directories was automatically built. The top level directory was named "finance" and it contains several subdirectories:

We will be putting all of our files into the "application" subdirectory. The framework never modifies files that are put into that directory.

Using your favorite text editor or IDE, create a new empty file in the application directory named "finance.dd.yaml".

The Orders Module

Andromeda groups all tables into modules, for convenience both in assigning security and creating menus.

The first thing we are going to add to our DD.YAML file is a module definition:

# Remember! Indent with spaces, not tabs!
module orders:
   uisort: 100
   description: Orders

The definition begins with the keyword "module", followed by the name and then a colon. There are two properties of the module. For now we will ignore "uisort", that will be explained in a later tutorial. The "description" property will appear on menus and any other place where the module is referenced.

We will be putting all of the first tables of this system into the "gl" module.

Define A Column

Our first table will be the table of customers. We will use user-assigned customer codes, and make them 10-digit alphanumerics.

Before we can create the table, we need to define the customer code column. Andromeda makes column definitions outside of tables, the column itself is defined before it is put into a table. This is very important, because it reinforces the idea that whenever a column of a certain name appears, it will always be exactly the same kind of column.

The column definition can come right after the module:

column customer: 
   type_id: char
   colprec: 10
   description: Customer Code

Like the module, the definition begins with a keyword, in this case "column", followed by the name of the item to be defined, in this case "customer" and then a colon. The definition itself contains property:value assignments.

The "type_id" corresponds to SQL data types. The "colprec" is the SQL concept of "column precision", which just means how many characters in the column. The "description" is used wherever the column is referred to, for captions on screen, column headings and so forth.

Defining The Table

Now we are ready to define the table itself. We will start with a very simple customers table:

table customers:
   module: orders
   description: Customers
   
   column customer:
      primary_key: "Y"
      uisearch:    "Y"
   column description:
      uisearch: "Y"

Once again we see a keyword, "table", followed by a name "customers", terminated with a colon.

Notice that we have assigned the table to module "orders", which we defined earlier, and have also given the table a description, which will appear on menus and other places in the UI.

A table contains nested definitions. Within the table are two column definitions, each follows the same rules as other definitions, having a keyword, a name, a colon, and then their properties (if any) indented below them. There are many places in Andromeda where one definition will contain other definitions.

The table contains two columns. One of the columns is the primary key. We will see in a moment what effect the 'uisearch' assignment has.

When a column definition occurs inside of a table, we call that a column placement. Strictly speaking, the column is not being defined, it is only being placed into the table.

You may ask, why did we need to define the "customer" column, but it does not look like we needed to define "description", did it just come out of nowhere? This is because this column is already defined for us, it is one of the commonly-used columns that is defined in a system ADD file named andro_universal.add.

Security

For the beginning of this tutorial series we will assume you are logging in with a superuser account and can do anything. Security will be introduced in later tutorials. Therefore we will not explain the security assignments right now.

Run A Build

We now build the application again, just as we did in the previous tutorial. The builder will examine the DD.YAML file, detect all of the changes, and upgrade the database to its new form.

Once the build is complete, you will be able to login to the application and see our new menu module, the new menu entry, and the data in the table:

At this point you will be unable to edit the data, even as a superuser. This is because we have not yet created any security, and Andromeda is a deny by default system. After we have created two more tables we will go back and create some security definitions.