Planning and creating the database table | Joomla 3.x extension development crash course.

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

Now lets start planning the table fields which will make us to achieve our objectives. As we are working in Joomla, so it provides us with some standard field names which are pre programmed in Joomla, like ordering, state, checked_out etc. ID field is normally used as a primary key which is also auto increamental 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 front and backend to link to the detail and edit page respectively. The url of the detailed page in frontend will also be computed on the basis of 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 detail of a breed will be contained in a description field along with short description. Then there would be breed category field and after that it comes the picture field to hold multiple breed pics file names. Next comes the standard joomla fields. "State" will store the publish and unpublish or trashed state of an item. Then we have to store the editing information of an item and for this purpose 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 contain the id of the user that would be editing the record. After that we will track the owner of the record through "created_by" field. It is also a point to know that checked_out and created_by just work as a foreign key to id field of 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 will add fields to it, as we have planned previously. To do this, first we would require a MySQL editor. I am using phpMyAdmin for this purpose because it is most common and have a better user friendly interface but there are also good one out there, like Sequel Pro is also a smart choice if you are using 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 prefix, there comes our component name and then the table name. It is not a rule by joomla but I normally use component name in the table because of eliminating the chance of overriding other extension table because DROP Table command is also used in so many places, before the actual CREATE table query, to remove the previous instance of 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 will also serve as the primary key. Auto increment means that when you add a new record to table, next number is automatically calculated and assigned as the id.  As the primary key could not be negative, so we are also marking it as UNSIGNED. After making these settings, press save and our first field would be added to table. You will notice "Allow Null" is unchecked for this field, as null value could not be passed for this field. 

The next field is the asset_id field. This is Joomla CMS specific field and is used to generate the single page related access parameters. This field would be an integer and will get the specific role id from joomla, through 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 of it must be provided by your extension. You can handle its values easily if you are using JTable class in your component, which I will discuss in detail in next lessons.

Then we are creating a field for saving the ordering information of individual recods. This field would be an integer value and it should not be null. Order field is also joomla specific and JTable class automatically deals with it, so you dont have to write extra code for it as joomla is already making your life easier. Then there is the "state"field. As the name indicates, publish / unpublish information is stored in it, in the form of 1 and 0 respectively. This field is declared as "TINYINT" as it hold only a single digit, i.e, either 1 or 0.

After this, comes the "checked_out" field. It is a "NOT NULL" integer field which 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 user id, so it must be declared as INT with NOT NULL attribute.

The fields which I have created till yet are Joomla specific fields. Now we are going to create our extension specific fields to actually hold the dog breed information. For this purpose, the first field is the "breedname", which will hold the name of breed. It would be a VARCHAR field with characted length of 255. It should be declared as NOT NULL because user must provide the name of the breed, in order to make an entry. Then comes the pic field. It would also be VARCHAR field to hold both characters and integers and will have a length of about 80 characters. Then, in order to store the breed category name, create a field called, "breedcat" with VARCHAR(55) NOT NULL. If you are giving to much length for the VARCHAR, which actually you do not need, then 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 comes 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 using in our extension. This code will be executed along with the installation of 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

You have no rights to post comments

Enjoy best web development services at an affordable price. Looking forward to build a good relationship and serve you better...

Copyright © 2017. All rights reserved.