A Step-by-Step Guide to Adding a Database Table to Your Moodle Block

Welcome back! In our previous adventure, we explored the basics of creating a block in Moodle. If you missed that, no worries! Today, we’re taking the next exciting step by adding a database table to our block.

Now, you might wonder, why do we need a database table? Think of it like a special notebook where our block can neatly organize and store information. It’s like a digital bookshelf for our data.

So, grab your virtual toolkit because we’re diving into the process of creating and integrating a database table in Moodle. Don’t worry; it’s not as complicated as it sounds. Let’s get started on this journey of making our block even more powerful!

Download

Table of Content

Overview of install.xml and db Directory
Creating a Database Table in install.xml
Available Data Types
Example install.xml File and db Directory Structure
Updating version.php for Existing Blocks
Conclusion

1. Overview of install.xml and db Directory

Creating a table in a database involves using two important things: the install.xml file and the db directory.

Creating the db Directory:

First, go into your block’s main folder.
Create a new directory named db inside it if it does not exist.

Introduction to install.xml:

Inside the db directory, we’ll create a file called install.xml. This file is like a set of instructions that Moodle follows to set up the database.
In this file, we use special tags like <TABLES> and <TABLE> to define how our table should look.

So, to create a new database table, you’ll start by making sure your block has a db directory with the crucial install.xml file inside.

2. Creating a Database Table in install.xml

Now that our db directory and install.xml file are ready, let’s take a closer look at the install.xml file. Imagine we’re telling Moodle all the details about each shelf (field) on the neat shelves (table) we want in our block.

In the install.xml file, we use special tags to outline our table. One important tag is <TABLES>, and inside it, we use <TABLE> to explain how our table is set up.

Creating a Database Table in install.xml:

Inside the install.xml file located in the db directory, use the <TABLES> and <TABLE> tags to define your database table.
Specify the data types for each container (INT, NUMBER, FLOAT, etc.) based on the type of information you want to store.
Ensure your <TABLE> tag has the necessary attributes like NAME and COMMENT to help Moodle understand your table’s structure.

Adding Fields to the Database Table:

Inside the <TABLE> tag, utilize the <FIELDS> tag to define the different types of items (fields) you want to store in your database table.
Choose the appropriate data type for each field (INT, CHAR, TEXT, etc.) based on the nature of the information.
Consider adding default values for fields if needed.

Adding Keys to the Database Table:

Within the <TABLE> tag, use the <KEYS> tag to define keys for your table. Keys are like special labels that provide a quick way to organize and retrieve information.
Types of keys include the primary key (primary), uniquely identifying each item, and the foreign key (foreign), linking one table to another.

3. Available Data Types

In Moodle’s XMLDB, you’ll come across specific data types that serve as containers for different types of information in your database table. Here are the key data types:

INT (Integer):
- Think of this as a whole number box.
- Example: Useful for counting items, like the number of students in a course.
NUMBER:
- Similar to INT but may handle decimal values.
- Example: Appropriate for storing numerical data that might include decimals.
FLOAT:
- A floating-point number container, allowing a wider range of decimal values.
- Example: Useful when dealing with more complex numerical data.
CHAR (Character):
- This container can hold a fixed-length sequence of characters.
- Example: Suitable for codes or identifiers with a specific length.
TEXT:
- Imagine a container with an extensive capacity – perfect for storing paragraphs of text.
- Example: Ideal for storing detailed instructions, descriptions, or any lengthy text.
BINARY:
- Reserved for a container designed for binary data, which includes things like images or files.
- Example: When you need to store non-textual data, such as images or documents.
DATETIME:
- Combines date and time, serving as a container for precise time tracking.
- Example: Recording when a certain event or activity occurred.

Understanding these XMLDB data types is like recognizing the various containers available for different types of information in your database. It helps you choose the right container for the right type of information, making your database organized.

4. Example install.xml File and db Directory Structure

Now, let’s create a practical example of the install.xml file using the Moodle <XMLDB> tag and ensure our block’s directory has the correct structure.

Creating the db Directory:
- Open your block’s main folder.
- If it doesn’t already exist, create a new directory named db inside it.
Crafting the install.xml File:
- Inside the db directory, create a file named install.xml.
- Open this file using a text editor.
Basic Structure of install.xml using <XMLDB>:

<XMLDB PATH="blocks/custom_block/db" VERSION="20220530" COMMENT="XMLDB file for Moodle blocks/custom_block"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd">
<!-- Your table definition goes here -->
</XMLDB>

- This sets up the basic structure. I set the path attribute value to the path to custom_block block db directory which you need to replace according to your block, local, mod, etc location.
Adding a Table to install.xml using <TABLE>:

<TABLES>
     <TABLE NAME="custom_block_table" COMMENT="Description of your table">
         <FIELDS> 
              <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
              <FIELD NAME="name" TYPE="char" LENGTH="100" NOTNULL="true" DEFAULT=""/>
              <FIELD NAME="description" TYPE="text" NOTNULL="false"/>
              <FIELD NAME="duration" TYPE="int" LENGTH="4" NOTNULL="false" DEFAULT="0"/>
              <FIELD NAME="status" TYPE="int" LENGTH="2" NOTNULL="true" DEFAULT="1"/>
         </FIELDS>
         <KEYS>
              <KEY NAME="primary" TYPE="primary" FIELDS="id" COMMENT="The unique id" />
         </KEYS>
     </TABLE>
</TABLES>

- Replace custom_block_table with the actual name of your table.
- You can add more fields and keys based on your block’s requirements.
Saving and Checking:
- Save the install.xml file.
- Double-check that your block’s directory now has a db folder with the correct install.xml file.

Full Code

<?xml version="1.0" encoding="UTF-8" ?>
<XMLDB PATH="blocks/custom_block/db" VERSION="20220530" COMMENT="XMLDB file for Moodle blocks/custom_block"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"
>
      <TABLES>
           <TABLE NAME="custom_block_table" COMMENT="Description of your table">
                <FIELDS> 
                     <FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" SEQUENCE="true"/>
                     <FIELD NAME="name" TYPE="char" LENGTH="100" NOTNULL="true" DEFAULT=""/>
                     <FIELD NAME="description" TYPE="text" NOTNULL="false"/>
                     <FIELD NAME="duration" TYPE="int" LENGTH="4" NOTNULL="false" DEFAULT="0"/>
                     <FIELD NAME="status" TYPE="int" LENGTH="2" NOTNULL="true" DEFAULT="1"/>
                </FIELDS> 
                <KEYS>
                     <KEY NAME="primary" TYPE="primary" FIELDS="id" COMMENT="The unique id" />
                </KEYS>
           </TABLE>
      </TABLES>

</XMLDB>

5. Updating version.php for Existing Blocks

If your Moodle block already exists, and you’ve made changes to the install.xml file, it’s crucial to update the version.php file. Think of this as informing the building manager about the changes you made to your storage space so that they can manage it better.

Locating the version.php File:
- Inside your block’s main folder, find the version.php file. This file keeps track of the version and any changes made to your block.
Understanding the Version.php File:
- Open version.php using a text editor. You’ll find information about your block, including its version number and changes.
Updating the File:
- Locate the $plugin->version section and increase the version number. This tells Moodle that changes have been made.
Example:
- Here’s a simple example of how your version.php file might look after an update:

$plugin->requires = 2022112800; // YYYYMMDDHH (year, month, day, hour)
$plugin->version = 2024010701; // Requires this Moodle version (version 3.8.0)

Ensure that the version number is higher than the previous one. I changed the version value from 2024010700 to 2024010701.

Why Update version.php:
- Moodle uses the version number to track changes and updates. Updating it helps Moodle recognize that there are changes to your block.

After updating the version, log in to your Moodle website as an administrator. Moodle will automatically notice the plugin update and prompt you to install it. Just follow the installation instructions, and you’ll receive confirmation once it’s done.

6. Conclusion

In this quick journey, we’ve effortlessly designed an efficient storage space for your Moodle block using the magic of install.xml. It’s like setting up shelves, deciding what goes where, and adding labels for instant access.

We started by shaping our database table, defining its structure, and specifying what each section (field) holds. Keys acted as smart labels, ensuring a tidy and efficient organization.

Now armed with a streamlined storage solution, your Moodle block is not just organized; it’s optimized. Users will appreciate the simplicity and ease of information retrieval.

NOTE – You can follow the same steps if you want to add a database table to other moodle plugins like – mod, local instead of block.

If you found this tutorial helpful then don't forget to share.