Подробное руководство по работе с базами данных в Joomla 1.5

Большая часть данных из Joomla храниться в базе данных. После установки Joomla создает 35 таблиц. Некоторые из этих таблиц принадлежат различных расширениям ядра Joomla.
Для начала получим доступ к базе данных Joomla с помощью глобального объекта JDatabase. JDatabase - это абстрактный класс, который позволяет работать с различными драйверами баз данных. На данный момент Joomla содержит только два драйвера - MySQL и MySQLi. Получим доступ к глобальному объекту JDatabase используя JFactory:

$db =& JFactory::getDBO();

Префикс

Все таблицы в базе данных имеют префикс, по-умолчанию jos_, который помогает сохранять в одной базе данных несколько установок Joomla. Когда мы пишем SQL запросы, то мы используем символический префикс, который заменяется во время выполнения. По-умолчанию символический префикс - это #__, но мы можем использовать любые символы, если нам это нужно.

Схема соглашений

Когда мы создаем таблицы для своих расширений, мы должны придерживаться некоторых стандартов. Самое важное - это правильное именование таблиц. Все названия таблиц должны начинаться с префикса и содержать вначале имя расширения. Если таблиц у расширения несколько то мы добавляем добавляем в имени таблице после названия расширения символ подчеркивания "_" и пишем дополнительное произвольное название. Например таблица для компонента "My Еxtensions" может быть названа #__myextension_items.
Имена таблиц должны быть в нижнем регистре и только по-необходимости разделятся символом подчеркивания.
Например, вы можете назвать поле хранящее адрес почты как email. Если вы имеете первичное и вторичное поле email (primary и secondary), то вы дожны назвать поля email и email_secondary; неправильно будет называть первичное поле email_primary. Если у вас имеется ключевое поле ID, то оно обязательно должно быть типа integer, автоинкрементное и с не иметь пустого значения.

Общепринятые поля таблицы

Мы можем использовать несколько зарезервированных полей в нашей таблице. Используя эти поля, мы получаем над ними дополнительное управления с помощью фрэймворка Joomla. Как этим пользоваться будет написано ниже (с помощью класса JTable).
Итак, это поля:
Publishing - поле указывает, опубликован ли элемент, или нет. Имеет тип tinyint(1) и два значения 0 - неопубликован, 1 - опубликован.
Hits - отображает сколько обращений было к записе. Имеет тип integer и значение по-умолчанию 0.
Checking Out - на слуйчай если запись могут редактировать несколько пользователей, мы можем блокировать ее с помощью этого поля. Для этого используется два поля checked_out и checked_out_time. checked_out имеет тип integer и содержит ID пользователя обратившегося к записе. checked_out_time имеет тип datetime и содержит дату и время обращения пользователя к записе. Если дата содержит значение null и id пользователя равен 0, то запись не заблокирована.
Ordering - нам часто будет нужно разрешить администратору указать по какому полю произвести сортировку. Поле ordering имеет тип integer, и может быть использовано для указания колличества записей участвующих в сортировке и выводимых на экран. Это поле необязательно должно быть уникальным, и может быть использовано в запросе WHERE.
Parameter Fields - мы часто будем использовать поле параметров, по-умолчанию названное params и имеющее тип TEXT, для сохранения дополнительной информации о записях. Данные хранящиеся в этих полях закодированы как INI строки (которые мы обрабатываем в дальнейшем с помощью класса JParameter). Перед тем как использовать поле параметров, мы должны выбрать данные которые нужно хранить в нем. Данные должны соответствовать следующим критериям:

• Не использоваться в сортировках
• Не использоваться в поиске
• Существовать только для некоторых записей
• Не участвовать в межтабличных связях

Пример схемы

Таблица нарисована для расширения названного 'My Extension' и записей названных обобщенно foobar. Имя таблицы будет #__myextension_foobars.

Поле	Тип	NOT NULL	AUTO INC	UNSIGNED	По-уомлчанию
id	INTEGER	+	+	+	NULL
content	TEXT	+
checked_out	INTEGER	+		+	0
checked_out_time	DATETIME	+			0000-00-00 00:00:00
params	TEXT	+
ordering	INTEGER	+		+	0
hits	INTEGER	+		+	0
published	TINYINT(1)	+		+	0

Эта таблица использует все зарезервированные поля и одно автоинкрементное ключевое поле ID.
SQL запрос который создаст таблицу описанную в схеме выше:

CREATE TABLE '#__myextension_foobars' (
'id' INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
'content' TEXT NOT NULL DEFAULT '',
'checked_out' INTEGER UNSIGNED NOT NULL DEFAULT 0,
'checked_out_time' DATETIME NOT NULL DEFAULT '0000-00-00
00:00:00',
'params' TEXT NOT NULL DEFAULT '',
'ordering' INTEGER UNSIGNED NOT NULL DEFAULT 0,
'hits' INTEGER UNSIGNED NOT NULL DEFAULT 0,
'published' INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY('id')
)
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Поля даты

Очень часто используется поле типа datetime, хранящее дату и время некоторого события. Когда мы используем это поле, очень важно знать про эффект временных зон. Все даты и время должны быть сохранены в нулевом поясе UTC+0 (GMT / Z).
А уже при выводе на экран используем класс JDate.

Многоязычные требования

В кодировке UTF-8 колличество бит на символ от 8 и более. К сожалению в MySql версиях до 4.1.2, один символ всегда занимает один байт, что вызывает некоторые проблемы. Единственное решение - это создать различные файлы sql запросов для различных версий MySql-серверов, поддерживающих и не поддерживающих кодировку UTF-8
Для MySql неподдерживающих данную кодировку мы должны задать увеличенный размер полей. Например для поля varchar(20) лучше задать размер varchar(60). Размер поля увеличен в три раза, потому что большинство символов в кодировке UTF-8 занимают максимум 3 байта.
Но например поле varchar(100) мы должны были бы увеличить в три раза и получить varchar(300). Но это невозможно, так как максимальный размер - varchar(255). Единственный выход - это задать тип TEXT.
Для примера, ядро Joomla содержит таблицу #__content, хранящее поле title. Для MySql поддерживающих UTF-8 поле определяется как

'title' varchar(255) NOT NULL default ''

а для неподдерживающих

'title' text NOT NULL default ''

Запрос в базу данных

Когда мы выполняем запрос, мы передаем сам запрос в глобальный объект JDatabase. Мы должны использовать метод setQuery() для установки запроса, но запомните - он не выполняет сам запрос.

$db =& JFactory::getDBO();
$result = $db->setQuery($query);

Когда запрос установлен, нужно использовать метод query(), чтобы выполнить его. Этот метод аналогичен PHP функции mysql_query().
Если запрос правильный и содержит команды SELECT, SHOW, DESCRIBE, или EXPLAIN, то он вернет результат.

if (!$result = $db->query($query))
{
// отлавливаем ошибки
// используйте $db->getError() для большей информации
}

Написание запросов

Вот несколько правил при написании запросов к базе данных.

• Используйте префикс #__ вначале всех имен таблиц.
• Используйте метод nameQuote() для формирования названных элементов в запросе.
• Используйте метод Quote() для формирования значений.

nameQuote() - устанавливает правильность разделителей в имени поля, а Quote() - устанавливает правильность разделителей в передаваемом значении.
Например:

$db = JFactory::getDBO();
$query = 'SELECT * FROM '
.$db->nameQuote('#__test')
.' WHERE '
.$db->nameQuote('name')
.' = '
.$db->Quote('Some Name');

Если мы использовали MySQL или MySQLi драйвер, то запрос в итоге будет выглядеть следующим образом:

SELECT * FROM 'jos_test' WHERE 'name' = "Some Name";

Получение результата

Мы можем использовать метод query() для получения результата. Но чаще нам приходится пользоваться другими методами класса JDatabase, для получения результата в различных форматах.
Для показа работы методов мы будем использовать таблицу #__test. Эта таблица содержит два поля: id и name.
В таблице имеются две записи:

id	name
1	Foo
2	Bar

Далее в примерах мы не будем применять методы nameQuote() и Quote().

loadResult( ) : string
Метод загружает значение первой ячейки результата.

$query = 'SELECT 'name' FROM '#__test' WHERE 'id'=2';
$db =& JFactory::getDBO();
$db->setQuery($query);
echo $db->loadResult();

Результат: Bar

loadResultArray( numinarray : int=0 ) : array
Метод загружает столбец с номером numarray, обозначающим его логическую позицию, в виде массива.

$query = 'SELECT 'name' FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadResultArray());

Результат: Array ( [0] => Foo [1] => Bar )

loadAssoc( ) : array
Метод загружает первую запись результата в виде ассоциативного массива.

$query = 'SELECT * FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadAssoc());

Результат: Array ( [id] => 1 [name] => Foo )

loadAssocList( key : string='' ) : array
Метод загружает результат в виде массива ассоциативных массивов. Если мы укажем необязательный параметр key, то будет получен массив ассоциативных массивов с единственным полем key.

$query = 'SELECT * FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadAssocList());

Результат:

Array
(
[0] => Array
(
[id] => 1
[name] => Foo
)
[1] => Array
(
[id] => 2
[name] => Bar
)
)

loadObject( ) : stdClass
Этот метод загружает первую запись в виде объекта.

$query = 'SELECT * FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadObject());
 

stdClass Object
(
 [id] => 1
 [name] => Foo
)

loadObjectList( key : string='' ) : array Этот метод загружает результат в виде массива обьектов класса stdClass. Если есть параметр key, то будет загружен массив объектов с единственным свойством key.

$query = 'SELECT * FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadObjectList());
 

Array
(
 [0] => stdClass Object
  (
   [id] => 1
   [name] => Foo
  )
 [1] => stdClass Object
  (
   [id] => 2
   [name] => Bar
  )
)

loadRow( ) : array
Метод загружает первую запись результата в виде массива.

$query = 'SELECT * FROM '#__test'';
$db =& JFactory::getDBO();
$db->setQuery($query);
 

print_r($db->loadRow());
Array
(
 [0] => 1
 [1] => Foo
)

loadRowList( key : int ) : array
Метод загружает массив массивов.

 
$query = 'SELECT * FROM '#__test';
$db =& JFactory::getDBO();
$db->setQuery($query);
print_r($db->loadRowList(0));
 

Array
(
 [0] => Array
  (
   [0] => 1
   [1] => Foo
  )
 [1] => Array
  (
   [0] => 2
   [1] => Bar
  )
)

Использование ADOdb

ADOdb - это абстрактный слой на php по управлению базами данных, работающих под BSD. ADOdb поддерживает ряд ведущих баз данных. Сама Joomla не поддерживает ADOdb, а лишь эмулирует некоторую функциюнальность в своих базах данных.
Мы должны использовать ADOdb методы, если мы портируем существующее приложение, использующее ADOdb, или создаем расширение, работающее standalone с использованием ADOdb.
Joomla! использует JRecordSet класс для эмуляции класса ADORecordSet. JRecordSet класс еще не закончен, и обладает далеко не всеми методами класса ADORecordSet. В этом примере мы покажем простейшее использование класса JRecordSet ($row - это массив).

$db =& JFactory::getDBO();
$rs = $db->Execute('SELECT * FROM #__test');
while ($row = $rs->FetchRow())
{
    // обрабатываем $row
}

Для большей информации о ADOdb читайте http://adodb.sourceforge.net/

JTable

Joomla! предоставляет нам мощный абстрактный класс JTable; при этом мы можем выполненять все основные функции по работе с таблицей. Для каждой таблицы, которую мы хотим использовать в классе JTable, мы должны создать новый подкласс.
При создании подкласса JTable, мы должны придерживаться некоторых правил. Эти правила позволят нам интегрировать наше расширение в фрэймворк Joomla.
Итак, каждый подкласс JTable должен быть распложен в отдельном файле в каталоге tables (в административной части компонента). Имя создаваемого класса должно иметь префикс table. Имя файла обязательно должно быть в единственном числе.
Используем описанную выше схему таблицы, чтобы показать на примере как работать с классом JTable.

Класс должен называться TableFoobar и расположен в каталоге JPATH_COMPONENT_ADMINISTRATOR.DS.'tables'.DS.'foobar.php'. При первом использовании нашего класса мы должны определить глобальные свойства. Глобальные свойства соответствуют полям таблицы и должны иметь такие же названия. Мы используем эти свойства как буфер для хранения отдельных записей.
Во-вторых. В целях использования метода JTable::getInstance() мы должны определить конструктор.
В-третьих нам нужно переопределить метод check(). Этот метод проверяет содержимое буфера и возвращает булев результат. Если метод вернул значение false, то используем метод setError() для пояснения ошибки.

/**
 * обработчик таблицы #__myextenstion_foobars
 * 
 */
class TableFoobar extends JTable
{
    /** @var int Primary key */ 
    var $id = null;
    /** @var string Content */ 
    var $content = null;
    /** @var int Checked-out owner */ 
    var $checked_out = null;
    /** @var string Checked-out time */ 
    var $checked_out_time = null;
    /** @var string Parameters */ 
    var $params = null;
    /** @var int Order position */ 
    var $ordering = null;
    /** @var int Number of views */
 
    var $hits = null;
    /**
     * Constructor
     *
     * @param database Database object
     */
    function __construct( &$db )
    {
        parent::__construct('#__myextension_foobars', 'id', $db);
    }
    /**
     * Проверка
     *
     * @return boolean True if buffer is valid
     */
    function check()
    {
        if(!$this->content)
        {
            $this->setError(JText::_('Ваш Foobar должен содержать контент'));
            return false;
        }
        return true;
    }
}

Теперь, когда мы создали TableFoobar класс нужно инстанцировать объект с помощью статического метода JTable:: getInstance().

JTable::addIncludePath(JPATH_COMPONENT_ADMINISTRATOR.DS.'tables');
$table = JTable::getInstance('foobar', 'Table');

Заметьте, что мы подключаем не foobar.php а только каталог с таблицами. Когда JTable начинает инстанцировать TableFoobar на объект, автоматически подключается foobar.php.

CRUD

CRUD (Create Read Update Delete) - это общее название основных задач по управлению таблицей.
Все CRUD примеры $table ссылаются на класс TableFoobar и $id ссылается на идентификатор записи которую мы в данный момент обрабатываем. В этом примере мы создаем новую запись; $table - экземпляр класса TableFoobar.

$table->reset();
$table->set('content', "Наш контент");
$table->set('ordering', $table->getNextOrder());
if ($table->check())
{
    if (!$table->store())
    {
        // обработчик ошибок записи
        // используем $table->getError()
    }
}
else
{
    // обработчик ошибки проверки буфера
    // тоже используем $table->getError()
}

Метод reset() очищает наш буфер и приводит значения всех свойств к значениям по-умолчанию. Метод getNextOrder() определяет следующий по порядку вложенности элемент. Если запись не существующая, то он ставит значение 1.
Давайте рассмотрим наш пример подробнее. Некоторые из полей имели значение по-умолчанию, и после записи, значение даты будет пустым. После выполнения предыдущего примера буфер $table выглядит так:

[id] => 1
 [content] => Наш контент
 [checked_out] => 
 [checked_out_time] => 
 [params] => 
 [ordering] => 1
 [hits] => 0

После выполнения метода store() (сохранения записи), мы можем загрузить его:

$table->load($table->id);

Теперь наш буфер выглядит так:

[id] => 1
 [content] => Наш контент
 [checked_out] => 0
 [checked_out_time] => 0000-00-00 00:00:00
 [params] => 
 [ordering] => 1
 [hits] => 0

Вместо загрузки нашей сохраненной записи, мы могли бы изначально верно установить значения по-умолчанию, и нам бы не пришлось перезагружать запись.
Однако некоторые значения по-умолчанию зависят от типа данных. Поэтому нам нужно переопределить метод reset(). Для примера значение checked_out_time будет равно $db->getNullDate(). Итак для загрузки конкретной записи используем метод:

if (!$table->load($id))
{
    // обработчик загрузки
    // используем $table->getError() для ловли ошибок
}

Для обновления записи в буфере мы можем использовать два метода: первый - это загрузить запись из БД, второй - это установить конкретные значения для свойств буфера.
В этом примере покажем как обновить запись:

// установка значений
$table->reset();
$table->setVar('id', $id);
$table->setVar('content', JRequest::getString('content'));
if ($table->check())
{
    if (!$table->store())
    {
        // обрабатываем ошибки записи с помощью $table->getError()
    }
}
else
{
    // обрабатываем ошибки ввода в буфер $table->getError()
}

Последнее что мы расмотрим - это удаление записи:

if (!$table->delete($id))
{
    // обрабатываем ошибки
}

Если мы не указываем в методе delete() номер id, то id будет браться из буфера.
Если наша запись в таблице имеет родственные записи с другими таблицами, то мы должны сначала выполнить проверку методом canDelete(). Этот метод имеет всего один параметр в виде двумерного массива. Внутри массива должно быть несколько ключей - idfield, name, joinfield, и label. idfield - это имя первичного ключа в соответствующей таблице. name - это название самой таблицы. joinfield - это имя внешнего ключа соответствующей таблицы. label - это описание отношения между таблицами, для вывода сообщения об ошибке, если родственных связей не найдено.
Представьте что есть еще одна таблица #__myextension_children. Эта таблица имеет первичный ключ childid и внешний ключ primary, который ссылается на запись в таблице #__myextension_foobars. В этом примере мы проверим нет ли зависимости между записями таблицамы #__myextension_children и #__myextension_foobars, перед удалением записи из таблицы #__myextension_foobars.

$join1 = array('idfield'   => 'childid', 
               'name'      => '#__myextension_children', 
               'joinfield' => 'parent', 
               'label'     => 'Children');
 
$joins = array($join1);
if ($table->canDelete($id, $joins))
{
    if (!$table->delete($id))
    {
        // обрабатываем ошибки удаления
    }
}
else
{
    // обрабатываем в случае нахождения зависимостей
}

Мы можем определить более одной межтабличной связи. Допустим еще существует таблица #__myextension_illegitimate_children:

$join1 = array('idfield'   => 'childid', 
               'name'      => '#__myextension_children', 
               'joinfield' => 'parent', 
               'label'     => 'Children');
$join2 = array('idfield'   => 'ichildid', 
               'name'      => '#__myextension_illegitimate_children', 
               'joinfield' => 'parent', 
               'label'     => 'illegitimate Children');
$joins = array($join1, $join2);

Управление зарезервированными полями

Наша таблица содержит все зарезервированные поля, которыми мы можем управлять с помощью методов класса JTable. Рассмотрим управление этими полями подробнее.

Publishing

Для публикации и снятия с публикации мы можем использовать метод publish(). Если таблица содержит поле checked_ out, то мы можем быть уверены что запись таблицы не редактируется другим пользователем. Приведем пример публикации:

$publishIds = array($id);
$user =& JFactory::getUser();
if (!$table->publish($publishIds, 1, $user->get('id')))
{
    // обрабатываем ошибки
}

Первый параметр - это массив из id записей, которые нужно опубликовать или снять с публикации. Второй параметр необязательный, и указывает публикуем мы запись (значение 1), или снимаем с публикации (значение 0). По-умолчанию имеет значение 1. Последний параметр используется только в случае, если существует поле checked_out, и указывает id пользователя редактирующего запись. Метод возвращает значение true если все прошло успешно.

Hits

Произвести инкремент над записью можно с помощью метода hit(). Для примера мы установим id записи и выполним инкремент.

$table->set('id', $id);
$table->hit();

Также мы можем указать в параметре метода hit() нужный нам id. Но мы должны помнить что при этом обновиться буфер.

$table->hit($id);

Checking Out

Перед тем как мы начнем блокировать наши записи, мы должны убедиться что запись уже не заблокирована. Это нужно, чтобы не получилось, что несколько пользователей одновременно редактируют одну запись. Для этого мы можем использовать метод isCheckOut(). В этом примере мы выполняем проверку:

$table->load($id);
$user =& JFactory::getUser();
if ($table->isCheckedOut($user->get('id')))
{
    // выполняем действия если запись заблокирована
}

Далее мы блокируем запись под пользователя:

$table->load($id);
$user =& JFactory::getUser();
if (!$table->checkout($user->get('id')))
{
    // обрабатываем ошибки
}

Для того чтобы обрабатывать запись используем следующий метод:

$table->load($id);
$user =& JFactory::getUser();
if (!$table->checkin($user->get('id')))
{
    // обрабатываем ошибки
}

Запомните, что эти методы можно использовать только для залогенных пользователей.

Ordering

Когда мы хотим расположить элементы в определенном порядке, JTable предоставляет нам для этого набор методов. Первый из рассмотренных нами методов будет reorder(). Этот метод исправляет ошибки в порядке расположения записей в таблице.

$table->reorder();

В более сложных таблицах записи обычно разбиты по группам, и для этого в метод reorder() нужно дописать дополнительный параметр. Представим, что в нашей таблице есть поле group. В этом примере мы упорядочим записи в группе 1.

$db =& $table::getDBO();
$where = $db->nameQuote('group').' = 1';
$table->reorder($where)

Заметьте, что мы получаем объект базы данных не из JFactory, а из таблицы!
Ранее мы уже использовали метод getNextOrder(). Метод выдает нам следующую позицию в порядке упорядочивания. Как и в случае с reorder(), мы имеем возможность определения груп. Например, получим следующий номер порядка для группы 1.

$db =& $table::getDBO();
$where = $db->nameQuote('group').' = 1';
$nextPosition = $table->getNextOrder($where);

И последний метод - это move(). Он нужен для перемещения записи на одну позицию вверх или вниз. Переместим на примере запись вверх.

$table->load($id); $table->move(-1); Опять у нас есть возможность для указания групп. Покажем это на примере:

$db =& $table::getDBO();
$where = $db->nameQuote('group').' = 1';
$table->load($id);
$table->move(1, $where)

Поле параметров

Класс JTable не имеет каких-то специальных методов по управлению параметрами INI. Буфер JTable предназначен для хранения RAW данных параметров, которые необходимо сохранить.
Для обработки поля параметров используется класс JParameter. Для начала нам нужно создать новый объект JParameter, и если мы получили доступ к существующей записи отпарсить его.
Класс JParameter расширяет класс JRegistry. В этом примере мы расмотрим как можно парсить параметры используя класс JParameter.

$params = new JParameter($table->params);

Как только мы получили доступ к параметрам, мы можем изменять их используя методы get() и set().

$value = $params->get('someValue');
$params->set('someValue', ++$value);

Также мы можем получить данные в виде INI строки используя метод toString().

$table->params = $params->toString();

Мы также можем сами создать строку параметров. Для этого нужно указать сами параметры и путь к XML файлу манифеста.

$params = new JParameter('foo=bar', $pathToXML_File);

Для вывода параметров на экран в виде формы используется метод render().

echo $params->render('params');

Поля даты

При сохранении даты, очень важно чтобы она была в правильном формате. Для MySql мы должны сохранять дату в виде YYYY-MM-DD HH:MM:SS. Самый простой способ сделать это - использовать класс JDate. В этом классе нам потребуется метод toMySQL(), для приведения даты к правильному виду.

// import JDate class
jimport('joomla.utilities.date');
// получаем текущую дату и время
$myDate = gmdate();
// создаем новый объект JDate
// для joomla 1.5.0 надо было писать $jdate = new JDate($myDate);
$jdate = & JFactory::getDate($myDate);
// создаем запрос используя toMySQL()
$query = 'SELECT * FROM #__example WHERE date < '.$jdate->toMySQL();
 

$myDate - это дата в формате UNIX (timestamp).