Tutorial: Databases

Modifying Tables

In a database system, we often break up what we want to store into a series of tables. The reasons are numerous, but largely come down to taking less storage or retrieving data more quickly. An example might be making online purchases. In such a case, one table might have a user's card information and another would have transactions. It would be easier to separate the two because if a user changes credit card information previous purchases would not have to get updated information since it is in its own separate category.

As shown by our tables, if we have multiple tables, we can spend less time trying to update a larger table. We can have simple tables and update a smaller one which gives less overhead.

Say that we have created a new table, but we accidentally forgot to add a column or may have made a typo in our table name. Instead of deleting that table and creating a new one, we can modify the table. To do this, we would create a new object called EditTable, that allows us to use helper actions to edit the existing table inside of our database.

With our EditTable object, we are now able to change the name of our table, remove columns, edit column data types, filter our data, and many more other actions to useFor this example, we are going to create another empty table, which we can do in a separate file or use SQL commands in order to modify our table using Quorum actions. Below is some sample code on creating an empty table and it is important that this table exists before using the EditTable object.

 CreateTable newTable
    newTable:SetTable("newtable")
    newTable:AddIntegerColumn("tableID")
    newTable:AddIntegerColumn("tableText")
    newTable:AddIntegerColumn("tableText2")
    database:CreateTable(newTable)

We will be learning how to edit the name of our table, change a column's data type, remove a column, and establish an autoincrement on our table. A note on auto increment is that typically when adding new queries, we would have to manually log in the ID. However, if we edit our table, we can increment a value automatically.

To have access to these actions, we will need to create the EditTable object, which we can name 'edit.'

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

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

    EditTable edit

Using edit we will be using the actions: SetTable(text tableName), AlterAutoIncrement(int newStart), renameTable(text newName), ModifyColumn(text columnName, text dataType), AddColumn(text columnName, text dataType), DropColumn(text columnName), and EditTable(EditTable objectName).

With our object created, let's first get the table we are modifying using SetTable() and let's pass in "newTable" which is the table object we previously made. Because we do not want to manually change the IDs of each entry, lets also use AlterAutoIncrement and pass in the value of 1 so it automatically starts to change the IDs as soon as we start adding entries. Next, the current name "newtable" does not seem that descriptive in terms of the changes we have reflected so far, so now we will want to change the table name using RenameTable(); let's rename our table "supernewtable" and pass that string as the parameter to RenameTable.

edit:SetTable("newtable") edit:AlterAutoIncrement(1) edit:RenameTable("superNewTable")

Next, suppose we want to edit the columns of this "supernewtable" such as modifying an existing column, removing a column, and adding a new column. To modify a column, we will be using ModifyColumn and pass in two parameters, the column name and the new data type we can convert this column to accept. For this, let's pass in the value "tableText" as the first parameter and "string" as the second parameter.

Originally "tableText" was an integer column but it is now a text column. Additional actions we can take on "newtable" would lead us to know how to modify an existing column, remove a column, and add a new column. To add a column, we will be using the AddColumn(columnName, datatype) and pass in two parameters, the column name and the data type. For this let's pass in the value "newColumn" as the first parameter and "integer" as the second parameter.

edit:ModifyColumn("tableText","string")
 edit:AddColumn("newColumn","integer")
 edit:DropColumn("tableText2")

To reflect our modifications, we will need to call our database object and pass in the EditTable object using the EditTable() action.

database:EditTable(edit)

Below is the full code of altering a table in our database.

/* equivalent to alter table*/
/* changing table labels */
/* changing the datatypes */

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

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

    EditTable edit
    edit:SetTable("newtable")
    edit:AlterAutoIncrement(1)
    edit:RenameTable("superNewTable")
    edit:ModifyColumn("tableText","string")
    edit:AddColumn("newColumn","integer")
    edit:DropColumn("tableText2")
    database:EditTable(edit)

Next Tutorial

In the next tutorial, we will discuss drop table, which describes how to delete a table from a database.