Tutorial: Databases

This tutorial introduces views and how to create them with a database table

Introduction to Views

In SQL and in Quorum, views are virtual tables. What this means is that it also contains rows and columns like a real table, but we get to choose the specific rows of certain tables to be in a view. The purpose of using a view versus using the entire table is to provide simplicity to the user. Data tables can get very extensive so fast, therefore if we want to only examine a section of a table, we can simply create a view.

For this tutorial, we will be learning how to create a view on our "likes" table. We will want to connect our database once again as well as create a Find object and a CreateView object. For our Find object, we can reference this tutorial on how we can Find data. Our CreateView object is something completely new where we can use helper actions to create our view. Let's go ahead and name our Find and CreateView objects simply "find" and "view."

We will be connecting our table using our Find object first. To get our "likes" table, we will be using the action SetTable(text tableName) and insert "likes" as our parameter. Next, we will be adding our column tables so that our Find object can hold them like a container using the action AddColumn(text columnName). We will add our two columns, "id" and "likes" since our table is fairly small.

Find find
    find:SetTable("likes")
    find:AddColumn("id")
    find:AddColumn("likes")

Now that we have a container that holds our table, we can create a view. We will be using two actions from the CreateView object: SetQuery(Find objectName) and SetName(text viewName) that allows us to make a view from our Find object. With our CreateView object, to send it back to the database, we will also be using the action CreateView(CreateView objectName).

Views Helper Actions
Object / Action Description Usage
CreateView objectNameThe CreateView class is a query that allows creating a table that lives on the database composed of the results of a Find query. Once the view is created it can be accessed as if it was another database table. Although you can not insert, update, or delete from a view.CreateView view
viewObjectName:SetQuery(Find findObject)This action is used to set the Find query of the view.view:SetQuery(find)
viewObject:SetName(text viewTableName)This action is used to set the name of the view.view:SetName("grocery_view")
databaseObject:CreateView(CreateView objectName)This action takes a CreateView query and sends it to the database.database:CreateView(view)

Using the action, SetQuery() on our "view" object, we will pass in the Find object that contains our table. Now, our "view" is created, but like any table, we must name it which we can do using SetName(). Let's go ahead and name our table "likes_query" With our view all set up, we will go back to our database object and use the action CreateView() that will send back our table into the database. This action will take in the "view" object we have just set up.

view:SetQuery(find)
    view:SetName("likes_query")
     database:CreateView(view)

Congratulations! We are able to create a view from any table within our database. The entire program can be viewed below:

use Libraries.Data.Database.Database
    use Libraries.Data.Database.Find
use Libraries.Data.Database.CreateView

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

    Find find
    find:SetTable("likes")
    find:AddColumn("id")
    find:AddColumn("likes")

    CreateView view
    view:SetQuery(find)
    view:SetName("likes_query")

    database:CreateView(view)

Next Tutorial

In the next tutorial, we will discuss summarized Database Tables, which describes going beyond database tables and summarizing the data.

Go BackNext Tutorial