10. Реляционные, пост-реляционные и объектно-реляционные СУБД

10.1 Как использовать DBI?

Введение

DBI -- это интерфейс прикладных программ к СУБД, использующим SQL в качестве языка запросов. Сам DBI определяет только набор функций, переменных и соглашений. Вся непосредственная работа выполняется Database Drivers (DBD) -- модулями, обеспечивающими связь с СУБД. DBI только обеспечивает стандартный интерфейс для этих драйверов.

Полная схема архитектуры при работе DBI выглядит примерно так:

+----------------------+
| Прикладная программа |
+----------------------+
| DBI                  |
+----------------------+
| DBD                  |
+----------------------+
| СУБД                 |
+----------------------+

Подсоединение к СУБД

обеспечивается при помощи метода connect класса DBI:

$dbh = DBI->connect($dsn, $user, $auth, {options});

$dbh

Это объект, при помощи его методов осуществляются взаимодействия с СУБД.

$dsn

Строка, определяющая к какой базе данных подсоединятся и другие параметры. Зависит от DBD. На сегодняшний момент стандарта нет, но рекомендовано использовать стиль ODBC:

dbi:<имя DBD>:databasename=<название БД>;host=<Имя хоста>;port=<порт>

$user

Имя пользователя.

$auth

Нечто, авторизующее пользователя. Обычно пароль.

options

Параметры DBI, передаются через анонимный хеш. В настоящее время понимаются три параметра:

RaiseError

если установлен, то при любой ошибке DBI убивает программу

PrintError

если установлен, то при ошибке DBI вызывает warn

AutoCommit

определяет порядок работы с транзакциями.

Например:

$dbh = DBI->connect('dbi:Pg:dbname=apavel', 'apavel', 'SomeSecret',
{RaiseError=>1, AutoCommit=>0});

Отсоединение обеспечивается при помощи метода disconnect: $dbh->disconnect();

Механизм курсоров и подготовки запросов

При работе с базами данных при помощи DBI используются курсоры -- специальные объекты, обеспечивающие последовательный доступ к результатам запросов. (В простейших случаях можно обойтись и без них, я расскажу об этом дальше.)

Пример таблицы, используемый в дальнейшем:

foo.sql
create table foo (
        bar varchar(50),
        baz int
)       

Получение данных

$cursor = $dbh->prepare('select bar, baz from foo');
# теперь $cursor -- курсор, и его необходимо исполнить
$cursor->execute;
# После исполнения запроса, результат можно получить из курсора при помощи
# метода fetchrow_array
while (($bar, $baz) = $cursor->fetchrow_array) {
        print "bar is: $bar, baz: $baz\n";
}       

Placeholders

Очень часто бывает надо подготовить какой-либо запрос, а потом использовать его с разными значениями данных. DBI предлагает для механизм placeholders: В запросе на месте таких данных указываются вопросительные знаки, а сами значения передаются в метод execute() курсора. Например:

$cursor = $dbh->prepare('select bar from foo where baz=?');
$cursor->execute($baz);

$cursor = $dbh->prepare('insert into foo(bar, baz) values(?, ?)');
while ( ... ) {
        $cursor->execute($bar, $baz);
}

Таким образом, СУБД разбирает запрос только один раз, а затем просто исполняет его, что экономит время. (Естественно, это верно только для DBMS с раздельными parse и execute, сейчас ни MySQL, ни PostgreSQL такое не поддерживают, поэтому их реализации DBD просто сохраняют запрос переданный $dbh->prepare() и затем подставляют в него данные при каждом $sth->execute().)

Работа без курсоров

DBI предоставляет несколько методов для такого рода работы: Методы для запросов:

selectrow_array

Возвращает одну строку запроса в виде массива

selectall_arrayref

Возвращает весь ответ сервера в виде массива ссылок на массивы.

do

исполняет запрос

Пример:

#получить значение bar при baz=3
($bar) = $dbh->selectrow_array('select bar from foo where baz=3');

# установить baz в некоторое значение при bar='somestring'
$dbh->do("update set baz=1 where bar='somestring'");

10.2 Как работать с записями кусочками: первые N записей, следующие N...?

Можно несколькими способами: 1. Просто прокручивая курсор:

        $c = $dbh->prepare('select baz, bar from foo');
        $c->execute;
        # если нужна последовательность с 26 по 50

        for ($k = 0; $k < 26; $k++) {
                $c->fetchrow_array; 
        }
        # теперь можно вывести данные
        print "<table border=1><tr><th>bar</th><th>baz</th></tr>\n";
        while (($bar, $baz) = $c->fetchtrow_array) {
                print "<tr><td>$bar</td><td>$baz</td></tr>\n";
        }
        $c->finish; # Закрыть курсор
        print "</table>";

2. Используя курсоры СУБД

        # Показан синтаксис PostgeSQL
        $dbh->do('declare mycursor cursor for select bar, baz from foo');
        $dbh->do('move 25');

        # И теперь будем получать данные
        $c = $dbh->prepare('fetch forward 25 in mycursor');
        while (($bar, $baz) = $c->fetchrow_array) {
                print ....;
        }
        $c->finish;
        $dbh->do('close mycursor');

3. Для MySQL можно использовать директиву LIMIT

        $c = $dbh->prepare('select bar, baz from foo limit 26,25');
        while (($bar, $baz) = $c->fetchrow_array) {
                print ....;
        }
        $c->finish;

10.3 Где взять документацию/учебник по SQL?

Мартин Грабер, "Введение в SQL", тем более, что недавно вышло новое издание на русском языке.

10.4 Как можно подсоединится к MS SQL Server?

Вроде как можно при помощи DBD::FreeTDS