Front Cover of Mastering Joomla! 1.5

Chapter 3, The Database

The following is a partial extract from Mastering Joomla! 1.5 Extensiona and Framework Development. This extract is taken from the third chapter in the book, The Database. You can download the entire chapter for free from the Packt Publishing web site!


The Core Database

Much of the data we see in Joomla! is stored in the database. A base installation has over thirty tables. Some of these are related to core extensions and others to the inner workings of Joomla!.

There is an official database schema, which describes the tables created during the installation. For more information, please refer to: http://dev.joomla.org/component/option,com_jd-wiki/Itemid,31/id,guidelines:database/.

A tabular description is available at: http://dev.joomla.org/downloads/Joomla15_DB-Schema.htm.

We access the Joomla! database using the global JDatabase object. The JDatabase class is an abstract class, which is extended by different database drivers. There are currently only two database drivers included in the Joomla! core, MySQL and MySQLi. We access the global JDatabase object using JFactory:

$db =& JFactory::getDBO();

Extending the Database

When we create extensions, we generally want to store data in some form. If we are using the database, it is important to extend it in the correct way. More information on extending the database with components is available in Chapter 4.

Table Prefix

All database tables have a prefix, normally jos_, which helps in using a single database for multiple Joomla! installations. When we write SQL queries, to accommodate the variable table prefix, we use a symbolic prefix that is substituted with the actual prefix at run time. Normally the symbolic prefix is #__, but we can specify an alternative prefix if we want to.

Schema Conventions

When we create tables for our extensions, we must follow some standard conventions. The most important of these is the name of the table. All tables must use the table prefix and should start with name of the extension. If the table is storing a speci? c entity, add the plural of the entity name to the end of the table name separated by an underscore. For example, an items table for the extension 'My Extension' would be called #__myExtension_items.

Table field names should all be lowercase and use underscore word separators; you should avoid using underscores if they are not necessary. For example, you can name an email address field email. If you had a primary and a secondary email field, you could call them email and email_secondary; there is no reason to name the primary email address email_primary.

If you are using a primary key record ID, you should call the field id, make it of type integer auto_increment, and disallow null. Doing this will allow you to use the Joomla! framework more effectively.

Common Fields

We may use some common fields in our tables. Using these fields will enable us to take advantage of the Joomla! framework. We will discuss how to implement and manipulate these fields, using the JTable class, later in this chapter.

Publishing

We use publishing to determine whether to display data. Joomla! uses a special field called published, of type tinyint(1); 0 = not published, 1 = published.

Hits

If we want to keep track of the number of times a record has been viewed, we can use the special field hits, of type integer and with the default value 0.

Checking Out

To prevent more than one user trying to edit one record at a time we can check out records (a form of software record locking). We use two fields to do this, checked_ out and checked_out_time. checked_out, of type integer, holds the ID of the user that has checked out the record. checked_out_time, of type datetime, holds the date and time when the record was checked out. A null date and a user ID of 0 is recorded if the record is not checked out.

Ordering

We often want to allow administrators the ability to choose the order in which items appear. The ordering field, of type integer, can be used to number records sequentially to determine the order in which they are displayed. This field does not need to be unique and can be used in conjunction with WHERE clauses to form ordering groups.

Parameter Fields

We use a parameter field, a TEXT field normally named params, to store additional information about records; this is often used to store data that determines how a record will be displayed. The data held in these fields is encoded as INI strings (which we handle using the JParameter class). Before using a parameter field, we should carefully consider the data we intend to store in the field. Data should only be stored in a parameter field if all of the following criteria are true:

  • Not used for sorting records
  • Not used in searches
  • Only exists for some records
  • Not part of a database relationship