Planning and creating the database table

This step is all about setting up the database, and we are taking our time here, planning the database table. As I have explained in the previous step, through our component, we are going to manage a list of dog breeds that will be shown through the component’s front and back ends. To do so, we first have to save entries to a database and have to plan a table that will allow us to achieve this. We will also require the features of change state, ordering, checked in/out, and ACL.We will also want to track which user made the entries and at what time.

Now let’s start planning the table fields that will help us achieve our objectives. As we are working in Joomla, it provides us with some standard field names that are preprogrammed in Joomla, like ordering, state, checked_out, etc. ID field is normally used as a primary key, which is also auto-increasing, and through it, a unique record is identified. Then we will have a field to hold the breed name. This field will be used in the front and backend to link to the detail and edit pages, respectively. The url of the detailed page in the frontend will also be computed on the basis of an alias generated from the breed name, which I will cover later in the tutorial. This sort of url is also referred to as a “search engine friendly url.”

The details of a breed will be contained in a description field along with a short description. Then there would be a breed category field, and after that would come the picture field to hold multiple breed picture file names. Next come the standard Joomla fields. “State” will store the published, unpublished, or trashed state of an item.

Then we have to store the editing information of an item, and for this purpose, the Joomla standard field “checked_out” is used, along with “checked_out_time,” a datetime field to track when an item is checked out. It will include the ID of the user who is editing the record.After that, we will track the owner of the record through the “created_by” field. It is also important to know that checked_out and created_by just work as foreign keys to the id field of the user table. For any content-based extension, it is the basic database table structure that we have discussed above.

Creating the database table

Now the actual stage begins, where we will create a table in MySQL and add fields to it, as we have previously planned. To do this, we would first require a MySQL editor. I am using phpMyAdmin for this purpose because it is the most common and has a better user-friendly interface, but there are other good ones out there, like Sequel Pro, which is also a smart choice if you are using a Mac.

Make some action now and create an empty table named “jos_breed_breed.” I am assuming “jos” as your Joomla prefix, which will then be replaced with “#_” while packaging the extension. Joomla replaces “#_” with your Joomla installation prefix after installing your extension. Now in the table name, “jos_breed_breed,” after the prefix comes our component name and then the table name. It is not a rule by Joomla, but I normally use the component name in the table because it eliminates the chance of overriding another extension table because the DROP TABLE command is also used in so many places, before the actual CREATE TABLE query, to remove the previous instance of the table and create the new one. For the table encoding, normally UTF-8 is used.

Now the first field we have to create is the id field. It would be an integer field with a default value of 0. This field would be auto-incremental and also serve as the primary key. Auto-increment means that when you add a new record to the table, the next number is automatically calculated and assigned as the id.

Because the primary key cannot be negative, it is also marked as UNSIGNED.After making these settings, press Save, and our first field will be added to the table. You will notice “Allow Null” is unchecked for this field, as a null value could not be passed for this field.

The next field is the asset_id field. This is a Joomla CMS-specific field and is used to generate the single page-related access parameters. This field will be an integer and will be populated with the specific role id obtained from Joomla via the extension.

For not allowing negative values, this should be an UNSIGNED field, and it should also be marked as NOT NULL, as a value for it must be provided by your extension. You can handle its values easily if you are using the JTable class in your component, which I will discuss in detail in the next lessons.

Then we are creating a field for saving the ordering information of individual receipts. This field would have an integer value, and it should not be null. Order field is also Joomla-specific, and JTable class handles it automatically, so you don’t have to write additional code for it because Joomla is already making your life easier.Then there is the “state” field. It stores publish/unpublish information in the form of 1 and 0, as the name suggests.This field is declared as “TINYINT” as it holds only a single digit, i.e., either 1 or 0.

After this comes the “checked_out” field. It is a “NOT NULL” integer field that will hold the ID of the user currently editing the record. The next field we are going to create is “checked_out_time” for holding the checked-out time.

It would be a DATETIME NOT NULL field with a default value of “0000-00-00 00:00:00.” Now, there must be a field for holding the record owner information, and for this purpose, we are going to create a “created_by” field. As it is going to hold only the user ID, it must be declared as an INT with the NOT NULL attribute.

So far, the fields I’ve created are all Joomla-specific.Now we are going to create our extension-specific fields to actually hold the dog breed information. For this purpose, the first field is “breedname,” which will hold the name of the breed. It would be a VARCHAR field with a character length of 255. It should be declared as “not null” because the user must provide the name of the breed in order to make an entry. The picture field follows.It would also be a VARCHAR field to hold both characters and integers and have a length of about 80 characters.

Create a field called “breedcat” with VARCHAR(55) NOT NULL to store the breed category name.If you give too much length to the VARCHAR, which you actually do not need, then the database will be reserving more unnecessary space and thus increasing table size. So, only mention the numbers you think would be sufficient and avoid extra numbering. Then there are the “shortdesc” and “desc” fields to hold the description of the breed. After that, we are going to export this table and will be extracting the SQL code for use in our extension. This code will be executed along with the installation of the extension.

Files to Create / Update

create admin/sql/index.html    prevents web server from listing directory content

create admin/sql/install.mysql.utf8.sql    file allowing to initialize schema version of the com_breed component.

create admin/sql/uninstall.mysql.utf8.sql    as the name suggests, this file is used to delete any database tables related to the extension.

update  /breed.xml

File Details

admin/sql/install.mysql.utf8.sql

CREATE TABLE IF NOT EXISTS `#__breed_breed` (

`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

`asset_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',

`ordering` INT(11) NOT NULL ,

`state` TINYINT(1) NOT NULL ,

`checked_out` INT(11) NOT NULL ,

`checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

`created_by` INT(11) NOT NULL ,

`breedname` VARCHAR(255) NOT NULL ,

`pic` VARCHAR(80)  NOT NULL ,

`breedcat` VARCHAR(55) NOT NULL ,

`shortdesc` TEXT NOT NULL ,

`desc` TEXT NOT NULL ,

PRIMARY KEY (`id`)

) DEFAULT COLLATE=utf8_general_ci;

admin/sql/uninstall.mysql.utf8.sql

DROP TABLE IF EXISTS `#__breed_breed`;

an update to  /breed.xml

    <version>Breed: 1.0.0</version>
    <description></description>
    
    <install> <!-- Runs on install -->
        <sql>
            <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
        </sql>
    </install>
    <uninstall> <!-- Runs on uninstall -->
        <sql>
            <file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
        </sql>
    </uninstall>

    <files folder="site">
    <filename>index.html</filename>

Download Code

Next –> Developing a Basic Component