Tutorial: Databases

This tutorial shows us how to create our first database table using Quorum

Database Data Types

Now that we have a database created, we will want to populate it with data. To do this, we first have to create a table. Recall that in a relational database, the structure is very similar to a spreadsheet such as ones we would see on Google Sheets or Microsoft Excel: a table with rows and columns that contain relevant information.

In SQL, there are three main data types:strings, numerics, and date/time. We need to know about these data types in order to create a valid table in our database. To view the entire list of data types, we can reference this table for more specific data types.
SQL Data Types
Data Type Category Data Type Description
StringCHAR(size)A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1
StringVARCHAR(size)A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters - can be from 0 to 65535
NumericINT(size)A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255)
NumericFLOAT(p)A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE()
Date/TimeDATEA date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
Date/TimeDATETIME(fsp)A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time

The next step in the process of working with databases is to tell them the kind of information we want them to contain. This can be a very complicated topic, involving entire sub-branches of mathematics (e.g., relational algebra). Learning about these areas of math can help us send and receive information quickly. Fortunately, modern database systems manage a great deal of this complicated math for us and we only need to provide the structure of our tables. In this section, we are going to discuss how to create these tables.

In Quorum, we can create a table by first connecting to our database. This makes sure a user authorized to securely make changes has access. Typically, users need administrative access to a database to create or destroy tables, which is a good thing because they can be destructive to existing data. We make a table with the CreateTable class. The CreateTable class allows us to create an object that has actions in Quorum to set up a table such as adding columns and data types to populate the table.

CreateTable newTable

The data types we can add onto our table are integers, strings, and date/time. Let us start off with creating our CreateTable object where we name it NewTable. We now have access to actions such as SetTable(text tableName), AddIntegerColumn(text columnName), and CreateTable(CreateTable objectName). Let's start off with naming our new table and adding an integer column. In addition to our grocery list, we also need to create a list of art supplies, therefore, let's call the action SetTable() and inside the parameters we are going to call our table "GroceryList" Now, we need to have IDs to track each item to purchase with an integer column. We will now be calling the action AddIntegerColum() and inside the parameters we are going to label this column "FoodID."

newTable:SetTable("GroceryList")
    newTable:AddIntegerColumn("FoodID")

We will also be adding a string column and a number column to this table. Below is a reference to the actions in creating a table.

Helper Actions on Database Table Creation with Quorum
Action / Object Description Usage
CreateTable tableObjectThe CreateTable class is a query to make a new table on a database. To create a table you need to give it a name using SetTable and then the table needs at least one column. CreateTable newTable
tableObject:SetTable(text tableName)This action sets the table of the query.newTable:SetTable("GroceryList")
tableObject:AddIntegerColumn(text label)This action adds a 32 bit integer column to the table.newTable:AddIntegerColumn("FoodID")
tableObject:AddNumberColumn(text label)This action adds a 64 bit double column to the table.newTable:AddNumberColumn("FoodPrice")
tableObject:AddDateTimeColumn(text label)This action adds a current date / timestamp column to the table.newTable:AddDateTimeColumn("TimeOfPurchase")
tableObject:AddTextColumn(text label, integer size)This action adds a text column to the table.newTable:AddTextColumn("SupplyName", 10)
databaseObject:CreateTable(Create TabletableObject)This action takes a CreateTable query and sends it to the database. If successful the metadata will be updated to include the new table. The CreateTable query adds new tables to the database. For more information on creating tables there is documentation in the CreateTable class.database:CreateTable(newTable)

Below is the full code on our table creation. For most of the following tutorials, we will be building upon this 'GroceryList' table we have made.

use Libraries.Data.Database.Database
    use Libraries.Data.Database.CreateTable


    Database database
    database:Connect("localhost", "myfirstdatabase", "root", "password")

    CreateTable newTable
    newTable:SetTable("GroceryList")
    newTable:AddIntegerColumn("FoodID")
    newTable:AddTextColumn("FoodName", 100)
    newTable:AddNumberColumn("FoodCost")
    newTable:AddIntegerColumn("FoodCalories")
    newTable:AddIntegerColumn("FoodQuantity")

    database:CreateTable(newTable)

Next Tutorial

In the next tutorial, we will discuss modifying database tables, which describes how to modify database tables by alterning columns.

Go BackNext Tutorial