Extension Documentation

Once the database requirements are defined, the next step is to turn these into a database design and an outline that describes how the user interface accepts and presents data. Given the requirements described above the three main tables can be created:

Categories tracks all current Rialto categories
Users contains information about who can be create Rialto entries
Labels contain the various types of advertisement types.
Entries contain the individual advertisements (items)

Further detail notes upon the main tables is provided later in this document.

System generated primary keys will be used for all tables so that all the data can be edited without executing a cascade update.

Database Objects - Procedures, Views, Triggers

Our experience tends to indicate that very few (if any) Joomla components make use of the underlying database to its full advantage, preferring instead to implement features at the application layer. This is in our opinion missing a very important 'trick', since the database is 'closest' to the data itself.

Database Triggers

The component does not implement any database triggers in the initial release.

The following description is taken from Wikipedia:

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Triggers are commonly used to:

audit changes (e.g. keep a log of the users and roles involved in changes)
enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock)
enforce business rules (e.g. require that every invoice have at least one line item)
execute business rules (e.g. notify a manager every time an employee's bank account number changes)
replicate data (e.g. store a record of every change, to be shipped to another database later)
enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data is manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit and rollback. Such DDL triggers can be used for database auditing purposes.

The following are major features of database triggers and their effects:

triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

MySQL

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

Insert Trigger
Update Trigger
Delete Trigger

	Note
	MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

	Note
	MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

Whether a specific database user can create these triggers will depend upon the permissions granted to the database user. The database user that has to have the correct permission in the Joomla environment is the user that is used to perform all database connects as defined in the Joomla installation itself.

	Note
	Different databases implement database features such as database triggers in different way, and often with slightly different syntax. For that reason the current version only supports MySQL databases, the most commonly used database for Joomla systems.

Database Routines (Procedures)

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralise logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another. This is more efficient that handling the actions at the application layer, since the database is 'closer' to the actual data being manipulated.

Stored procedures are similar to database functions. The major difference is that functions can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

The component in the initial release does not make use of any database procedures.

Database Views

A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated from data within the database, dynamically when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

Views can provide advantages over tables:

Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
Views can join and simplify multiple tables into a single virtual table
Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents
Depending on the SQL engine used, views can provide extra security

Rialto does not in its initial release make use of any database views.

Data Model

The data model designed is graphically shown in Figure 9.1, “Data model for Rialto 1.0”.

Figure 9.1. Data model for Rialto 1.0

Categories Table

Each entry belongs to a specific category.

Table 9.1. Categories table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Integer	n/a	Yes	Primary key	A unique numeric identification for each category (or sub-category). Populated by an auto-sequence.
parent_id	Number	n/a	No	None	Key to parent category.
title	Varchar	255	Yes	Unique key	A unique alphanumeric name for the category.
alias	Varchar	255	Yes	None	Category Alias.
description	Varchar	255	No	None	Description of the category.
image	Varchar	255	No	None	Name of the category image if required.
lft	Integer	11	Yes	None	Nested table left.
rgt	Integer	11	Yes	None	Nested table right.
level	Integer	10	Yes	None	Nested table level.
access	TinyInt	3	Yes	None	Required for nested table.
path	Varchar	255	Yes	None	Required for nested table.
ordering	Int	11	No	None	the column used for ordering records. Not used.
state	TinyInt	1	No	None	State of the specific record. i.e. Published, archived, trashed etc.
language	Character	7	No	None	Language code.
asset_id	Integer	255	No	None	Asset identifier as used in Joomla assets table.
created	Timestamp	-	No	None	The date and time the record was created. Default NULL.
modified	Timestamp	-	No	None	If populated the date and time the record was last modified.
show_image	TinyInt	1	Yes	None	Whether to display the category image. Default 0 (No).
use_firstname	TinyInt	1	Yes	None	Whether to display the users Christian (first) name. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_lastname	TinyInt	1	Yes	None	Whether to display the users surname. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_company	TinyInt	1	Yes	None	Whether to display the entry cvendor's company name. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_street	TinyInt	1	Yes	None	Whether to display the entry vendor's street address. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_zip	TinyInt	1	Yes	None	Whether to display the entry vendor's zip (post) code. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_city	TinyInt	1	Yes	None	Whether to display the entry vendor's city. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_state	TinyInt	1	Yes	None	Whether to display the entry vendor's state. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_country	TinyInt	1	Yes	None	Whether to display the entry vendor's county. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_phone	TinyInt	1	Yes	None	Whether to display the entry vendor's land line phone number. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_mobile	TinyInt	1	Yes	None	Whether to display entry vendor's mobile phone number. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_email	TinyInt	1	Yes	None	Whether to display the entry vendors email address. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_web	TinyInt	1	Yes	None	Whether to display the entry web address (URL). Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_condition	TinyInt	1	Yes	None	Whether to display the entry condition. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.
use_price	TinyInt	1	Yes	None	Whether to display the entry price. Valid values are: 0 = No, 1 = Yes, 2 = User Global setting. Default is Use Global.

Note

1) It is possible to define sub-categories as well. Each sub-category is linked through the parent_id field. In some of the Joomla displays the full category name is displayed for every sub-category. i.e. For a category with three levels of sub-category the category name is displayed as:

Category Name Level 1 - Level 2 - Level 3

Where ‘Level 1’, ‘Level 2’ and ‘Level 3’ are the defined category name values for the sub categories. This means that the displayed ‘combined category name may become quite long, and for that reason it is suggested that the ‘category name’ is kept to a minimum length sufficient to uniquely identify it.

2) There is a 'Root ' category that is never displayed, but acts as the base of the category tree.

Users Table

The users table is used to provide information for their entries, rather than specifying them every time. A user may also be defined as a Rialto moderator, and may be blocked from creating Rialto entries. The basic entry is populated / created when a user first registers in Joomla.

Table 9.2. Users table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Integer	11	Yes	Primary key	The system generated unique identifier for the user. From the Joomla users table.
username	Varchar	150	Yes	-	A unique name that identifies each user.
status	TinyInt	1	Yes	None	User status.
ads	Integer	11	Yes	None	The number of adds attributed to the user.
moderator	TinyInt	1	Yes	None	Indicates whether the user is a Rialto moderator.
blocked	TinyInt	1	No	None	Whether the user is blocked from using Rialto.
firstname	Varchar	255	No	None	User's first (Christian) name.
lastname	Varchar	255	No	None	User's last name.
company	Varchar	255	No	None	User's company if applicable.
street	Varchar	255	No	None	User street address.
zipcode	Varchar	50	No	None	User's zip code (Or postcode) address.
city	Varchar	255	No	None	User's city address.
statename	Varchar	255	No	None	User's state address (or county) if applicable.
country	Varchar	255	No	None	User's country address.
phone	Varchar	255	No	None	User phone number for contacts if required.
mobile	Varchar	255	No	None	An alternative mobile phone number for the user.
email	Varchar	255	No	None	An email address for the user.
website	Varchar	255	No	None	A website address (URL) for the user,if applicable.

Note:

The minimum amount of information is required about the user, sufficient only to be able to provide sufficient information in the Rialto entries to enable their items to be advertised.

The basic user information is automatically inserted into the table when a new user is registered upon the Joomla site. This is achieved via a Joomla plug-in, installed when the component is installed. The plug-in also handles the deletion of users when they are removed from the Joomla users table.

Removal of a user will also automatically remove any associated Rialto entries.

Entries Table

This is the main table containing the different items (entries) that are being advertised.

Table 9.3. Entries table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a record. Populated by an auto sequence.
category_id	Int	10	No	-	The integer value of the category of the entry. Reference to the categories table for the entry.
label_id	Int	10	No	-	The integer value of the label of the entry. Reference to the labels table for the entry.
user_id	Int	10	No	-	The integer value of the owner of the entry. Reference to the users table for the entry.
firstname	Varchar	255	No	None	The first name (Christian name) of the vendor.
lastname	Varchar	255	No	None	The last name (surname) of the vendor.
company	Varchar	255	No	None	The company of the vendor, if applicable.
street	Varchar	255	No	None	The street address of the vendor.
zip	Varchar	50	No	None	The zip code (or postcode) for the vendor.
city	Varchar	255	No	None	The city where the entry item (or vendor) is located.
statename	Varchar	255	No	None	The name of the state where the entry item is located.
country	Varchar	255	No	None	The country where the entry item is located.
phone	Varchar	255	No	None	A land line phone number for contacting the vendor of the entry.
mobile	Varchar	255	No	None	A mobile phone number for contacting the vendor of the entry item.
email	Varchar	255	No	None	An email address of the vendor of the entry.
web	Varchar	255	No	None	A web address (URL) associated with the entry.
title	Varchar	255	No	None	A 'catchy' headline for the entry. Formally named 'headline' in releases prior to 1.3.0.
alias	Varchar	255	No	None	Entry Alias used to mask primary key of the entry from random selection.
description	Text	-	No	None	A detailed description of the entry.
condition	Varchar	255	Yes	None	Descriptive text describing the condition of the entry.
price	Varchar	255	Yes	None	The price of the item. Specified as text to allow for other possible 'cos' or 'exchange' mechanisms.
date_created	Timestamp	-	Yes	None	The date the entry was created. Default Current timestamp.
date_lastmodified	Timestamp	-	Yes	None	The date the entry was last modified.
flag_commercial	TinyInt	1	Yes	None	Indicates whether the entry is a commercial entry.
flag_featured	TinyInt	1	Yes	None	Indicates whether the entry is a featured entry.
flag_top	TinyInt	1	Yes	None	Indicates whether the entry should be displayed at the top of the entry list.
state	TinyInt	1	No	None	State of the specific record. i.e. Published, archived, trashed etc. in front end.
language	Character	7	No	None	Language code.
asset_id	Integer	255	No	None	Asset identifier as used in Joomla assets table.
expired	Tinyint	1	No	None	Specifies whether the entry is expired.
image1	Varchar	255	Yes	None	The name of the first image associated with the entry.
image2	Varchar	255	Yes	None	The name of the second image associated with the entry.
image3	Varchar	255	Yes	None	The name of the third image associated with the entry.
image4	Varchar	255	Yes	None	The name of the fourth image associated with the entry.
image5	Varchar	255	Yes	None	The name of the fifth image associated with the entry.
image6	Varchar	255	Yes	None	The name of the sixth image associated with the entry.
image7	Varchar	255	Yes	None	The name of the seventh image associated with the entry.
image8	Varchar	255	Yes	None	The name of the eighth first image associated with the entry.
image9	Varchar	255	Yes	None	The name of the ninth image associated with the entry.
image10	Varchar	255	Yes	None	The name of the tenth image associated with the entry.
video1	Varchar	255	Yes	None	The name of the first video associated with the entry.
vtype1	TinyInt	1	Yes	None	Video source type.
video2	Varchar	255	Yes	None	The name of the second video associated with the entry.
vtype2	TinyInt	1	Yes	None	Video source type.
video2	Varchar	255	Yes	None	The name of the second video associated with the entry.
vtype3	TinyInt	1	Yes	None	Video source type.

Labels Table

The structure of the labels table is as shown below. This table is used to store the various label types which can be applied to Rialto entries.

Table 9.4. Labels table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Integer	n/a	Yes	Primary key	A unique numeric identification for each labels record. Populated by an auto-sequence.
name	Varchar	255	No	None	Name of the label.
ordering	Number	n/a	No	None	Order in which label records are displayed.
state	Number	4	No	None	State of the specific record. i.e. Published, archived, trashed etc.
language	Character	7	No	None	Language code.
description	Varchar	255	No	None	Description of the label.
image	Varchar	255	No	None	Name of the label image if required. This is usually stored in the /images/rialto directory.

Rialto Log Table

The Rialto Log table is used by the logging routines to store information, of different priorities that are used to provide additional information upon the operation of the component. It is built on top of the standard Joomla JLog libraries.

Table 9.5. Log table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a record. Populated by an auto sequence.
priority	Number	11	Yes	n/a	The priority of the message. i.e. Alert, Info, Notice, Warning etc.
message	Varchar	512	No	n/a	The text message to store.
date	Date		No	n/a	The date the message was generated.
category	Varchar	255	No	n/a	The message category. Default ‘com_rialto’

There is a compound index upon the category, date and priority columns in that order.

Rialto Cat_Users Table

Introduced in release 1.3.0 the Rialto Cat_Users tables is used to hold the links that define the categories that a user is subscribed to. The table is used to simplify the selection of users to which email messages should be sent.

Table 9.6. Cat_Users table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a record. Populated by an auto sequence.
cat_id	Number	11	Yes	n/a	The foreign key to the Railto categories table.
user_id	Integer	11	No	n/a	The foreign key to the Rialto users table.

There is a compound index upon the cat_id and user_id.

Meta Data Table

The Meta table is/was used by the component itself to record version numbers of its component parts. i.e. Modules, plugins etc. Although still present in release 1.2.0 the component retrieves thee version number form the Joomla manifest instead of this table.

Table 9.7. Meta data component table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Int	11	Yes	Primary key	The system generated unique identifier for the record. Populated by an auto-sequence.
version	Varchar	100	Yes	None	Version number of the installed component.
type	Varchar	20	Yes	None	Type of extension.

	Important
	The meta table was removed from the distribution in release 1.2.0. These details are retained for reference purposes only.

Implementing Database Objects

This first step in building an application was to create the database objects. The tables have been defined above but there are a few other database objects we require.

Additional Database Objects Needed

To populate the primary key values of the tables needed for this application, an auto incrementing sequence is used in the MySQL implementation.

About Building Database Objects

There are several tools for creating objects in MySQL. You can:

Use phpAdmin to create tables, views, indexes, procedures, functions, triggers etc.
Use a product such as Toad, which provides the same abilities as phpAdmin with a cleaner interface. This is downloadable from the Quest website.
Create a script in a standard text editor and then upload the script and use the MySQL command interface, Toad, or phpAdmin to execute it.

Default Base Data

There are a few provided records inserted into the tables upon installation.

The categories table has a Root entry created with an id value of 1, which acts as the base category from which all other categories derive. This particular 'Root' project is 'hidden' from viewing or changing within the component.

The labels table is populated with a few sample label types which are commonly required.

Prev	Up	Next
Chapter 8. Version Changes	Home	Chapter 10. CSS Styling