Кудравцев Создание баз данных 2010

SQL вариант запроса:

PARAMETERS [Mag] Long, [Dat1] DateTime, [Dat2] DateTime;

SELECT Sum([Отгрузка-приход].Факт_отгрузка) AS [SumФакт_отгрузка]

FROM Магазины INNER JOIN [Отгрузка-приход] ON Магазины.Id = [Отгрузка-приход].Id_магазин

WHERE [Отгрузка-приход].Id_магазин=[Mag] AND [Отгрузкаприход].Дата_отгрузки Between [Dat1] And [Dat2];

4.Создать подчиненную форму, источником записей которой будет запрос, созданный в п. 2, а режимом работы – режим таблицы.

5.Добавить процедуру обработки события на изменение для поля со списком «Klient» и на выход для текстовых полей «Дата начала» и «Дата окончания»:

Private Sub Klient_Change()

Forms![Форма1]!

[Отгрузки по клиентам за период].Form.Requery

Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset Dim strSQL As String

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("AllGroup")

qdf.Parameters![Mag] = [Forms]![Форма1]![Klient] qdf.Parameters![Dat1] = [Forms]![Форма1]![Dat1] qdf.Parameters![Dat2] = [Forms]![Форма1]![Dat2] Set rst = qdf.OpenRecordset

Itog.Value = rst.Fields(0)

End Sub

Следует обратить внимание на использование префикса DAO,

для типов Database, Recordset, QueryDef.

91

Контрольные вопросы

1.Какие основные семейства и объекты составляют модель DAO?

2.В каком случае для ссылки на семейства, объекты и свойства применяется символ «!», а в каком «.»?

3.Что собой представляет объектная переменная и каким образом осуществляется ее инициализация?

4.Для чего вызывается процедура CurrentDb()?

5.Что собой представляет список событий, связанных с элементом управления?

92

Глава 4. КЛИЕНТ-СЕРВЕРНЫЕ СУБД

Сетевое многопользовательское приложение строится по принципу файл-серверной архитектуры. Данные в виде одного или нескольких файлов размещаются на файловом сервере. Файловый сервер принимает запросы, поступающие по сети от компьютеровклиентов, и передает им требуемые данные. Однако обработка этих данных выполняется на компьютерах-клиентах. На каждом из компьютеров запускается полная копия процессора обработки данных (например, DBEngine в случае MS Access). Любая копия процессора обработки данных независимо управляет файлами базы данных. Для обеспечения совместного доступа используется некоторый механизм блокировок. В MS Access для этого применяется специальный файл, который имеет имя, совпадающее с именем файла приложения, но с расширением idb, который обязательно создается для каждого файла базы данных с расширением mdb.

В архитектуре "клиент-сервер" сервер базы данных не только обеспечивает доступ к общим данным, но и берет на себя всю обработку этих данных. Клиент посылает на сервер запросы на чтение или изменение данных, которые формулируются на языке SQL. Сервер выполняет все необходимые изменения или выборки, контролируя при этом целостность и согласованность данных, и результаты в виде набора записей или кода возврата посылает на компьютер клиента.

Недостатки архитектуры с файловым сервером очевидны и вытекают главным образом из того, что данные хранятся в одном месте, а обрабатываются в другом. Это означает, что их нужно передавать по сети, что приводит к очень высоким нагрузкам на сеть и, вследствие этого, резкому снижению производительности приложения при увеличении числа одновременно работающих клиентов. Вторым важным недостатком такой архитектуры является децентрализованное решение проблем целостности и согласованности данных и одновременного доступа к данным. Такое решение снижает надежность приложения.

Архитектура "клиент-сервер" позволяет устранить все указанные недостатки. Кроме того, она позволяет оптимальным образом распределить вычислительную нагрузку между клиентом и серве-

93

ром, что также влияет на многие характеристики системы: стоимость, производительность, поддержку.

Процесс разработки таких систем достаточно сложен и одной из наиболее важных задач является как раз решение о том, как функциональность приложения должна быть распределена между клиентской и серверной частью. Существуют двух-, трех- и многозвенные архитектуры в зависимости от того, сколько промежуточных звеньев включается между клиентом и сервером.

Основная задача, которую решает клиентское приложение, — это обеспечение интерфейса с пользователем, т. е. ввод данных и представление результатов в удобном для пользователя виде, и управление сценариями работы приложения.

Основные функции серверной СУБД — обеспечение надежности, согласованности и защищенности данных, управление запросами клиентов, быстрая обработка SQL-запросов.

Вся логика работы приложения — прикладные задачи, бизнесправила — в двухзвенной архитектуре распределяются разработчиком между двумя процессами: клиентом и сервером.

Сначала бóльшая часть функций приложения решалась клиентом, сервер занимался только обработкой SQL-запросов. Такая архитектура получила название "толстый клиент — тонкий сервер". Появление возможности создавать на сервере хранимые процедуры, т. е. откомпилированные программы с внутренней логикой работы, привело к тенденции переносить все бóльшую часть функций на сервер. Сервер становился все более "толстым", а клиент — "все более тонким". Такое решение имеет очевидные преимущества, например его легче поддерживать, так как все изменения нужно вносить только в одном месте — на сервере. Однако язык, на котором пишутся хранимые процедуры, не является достаточно мощным и гибким, чтобы на нем было удобно реализовывать сложную логику приложения.

Впоследствии возникла тенденция поручить выполнение прикладных задач и бизнес-правил отдельному компоненту приложения (или нескольким компонентам), которые могут работать как на специально выделенном компьютере — сервере приложений, так и на том же компьютере, где работает сервер базы данных. Так возникли трех- и многозвенные архитектуры "клиент-сервер". Такие приложения являются гибкими, масштабируемыми, но сложными в разработке.

94

В настоящее время общепризнанными лидерами среди клиентсерверных СУБД являются Microsoft SQL Server и Oracle. Это коммерческие СУБД, требующие значительных программноаппаратных ресурсов. В связи с этим широкой популярностью пользуются свободно распространяемые СУБД MySQL [5] и PostgreSQL [6], которые по своим функциональным возможностям и быстродействию мало в чем уступают вышеназванному коммерческому ПО, а по некоторым характеристикам и превосходят их.

СУБД MySQL

Дистрибутив СУБД MySQL можно абсолютно бесплатно скачать с сайта www.mysql.com. Этот проект изначально разрабатывался применительно к операционной системе Unix (Linux), хотя в настоящее время имеются дистрибутивы и для ОС MS Windows. В дальнейшем будут рассматриваться примеры применительно к ОС

Unix.

Запуск и работа с MySQL

После установки программный комплекс MySQL содержит довольно большое количество библиотечных, исполняемых и конфигурационных файлов, главным из которых является файл mysqld — сервер MySQL. Для запуска сервера необходимо выполнить команду

/usr/local/mysql/bin/safe_mysqld &

Предполагается, что MySQL установлен в каталог по умолчанию /usr/local/mysql/. Вообще говоря, собственно сервер MySQL находится в файле mysqld. Однако рекомендуется запускать его не напрямую, а с помощью сценария (скрипта) safe_mysqld. Данный скрипт запускает mysqld, а затем непрерывно проверяет, выполняется ли mysqld, и перезапускает его, если тот неожиданно завершает свою работу. Скрипт safe_mysqld запускается в фоновом режиме, о чем свидетельствует знак амперсанда «&».

Далее необходимо запустить клиентское приложение, которое должно установить соединение с сервером базы данных (виртуальный канал) по протоколу TCP/IP. Существует большое количество

95

разнообразных клиентов, в том числе и графических. В стандартный комплект входит простейший клиент, работающий в режиме командной строки. Для его запуска надо ввести команду

mysql --host=192.168.0.1 --user=root --password=pass1 myBase

здесь

host=192.168.0.1 – IP-адрес сервера базы данных; user=root – имя пользователя;

password=pass1 – пароль пользователя;

myBase – имя базы данных (сразу после установки имеется только одна база данных — mysql).

Регистрация нового пользователя, назначение пароля и прав доступа к конкретной базе данных будут рассмотрены ниже, в разделе, посвященном системе безопасности MySQL.

При успешном установлении соединения клиент mysql будет готов получать команды от пользователя и выдавать результаты обработки запросов. Пример работы клиента, после установления соединения представлен на рис. 4.1.

Рис. 4.1. Пример работы клиента mysql

Следует отметить, что клиент mysql имеет встроенную систему помощи, которую можно вызвать, нажав клавишу знак вопроса «?».

Работа с сервером базы данных заключается наборе специальных команд и передаче их серверу для обработки. Команды пред-

96

ставляют из себя SQL-запросы, с помощью которых можно создать, модифицировать или удалить базу данных, а главное — произвести добавление, изменение, удаление и выборку необходимых данных. Сервер, получив команду от клиента, выполняет запрос (предварительно проверив его корректность) и отправляет результат обратно клиенту. Сервер способен одновременно поддерживать несколько баз данных и обслуживать большое количество запросов. Для обеспечения целостности данных используются механизмы блокировок, транзакций и триггеров, которые будут рассмотрены ниже.

Структура хранения данных в MySQL

Для хранения всех баз данных отводится специальный каталог, например:

/usr/local/mysql/data

Для каждой базы данных в указанном каталоге создается свой подкаталог, например база данных mysql хранится в подкаталоге

/usr/local/mysql/data/mysql

Для хранения таблиц MySQL поддерживает несколько механиз-

мов (storage engines). Наиболее используемы MyISAM (Indexed Sequential Access Management Индексно последовательный метод доступа) и InnoDB (разработка компании Innobase http://www.innodb.com). MyISAM является механизмом по умолча-

нию и хранит каждую таблицу на диске в виде трех файлов. Файл с расширением frm содержит формат таблицы (т.е. имена и типы полей). Данные хранятся в файле с расширением MYD, а в файле с расширением MYI содержится информация о ключах, индексах и прочих внутренних данных, необходимых для быстрого поиска данных. Например, таблица user из базы данных mysql будет храниться в виде трех файлов:

/usr/local/mysql/data/mysql/user.frm

/usr/local/mysql/data/mysql/user.MYD

/usr/local/mysql/data/mysql/user.MYI.

97

Формат данных MyISAM обеспечивает высокую эффективность обработки данных, однако он не поддерживает механизм транзакций, который будет рассмотрен ниже.

Формат InnoDB обеспечивает механизм транзакций и блокировок в стиле СУБД Oracle, сохраняет данные из таблиц, а также информацию о индексах в специальном файле (или нескольких файлах) табличного пространства (tablespace). Размер файла табличного пространства неограничен, даже если операционная система накладывает ограничения на максимальный размер файла.

Для восстановления поврежденных файлов формата MyISAM необходимо пользоваться утилитой

myisamchk [options] <tables>

Данная утилита содержит большое количество опций, наиболее полезной из которых является опция восстановления (-r). Например, для восстановления поврежденной таблицы user.MYI необходимо выполнить команду

myisamchk -r /usr/local/mysql/data/mysql/user.MYI

Система безопасности MySQL

Система безопасности MySQL основана на механизме аутентификации пользователей подключающихся к базе данных, а также на механизме авторизации поступающих запросов, т.е. проверке прав доступа к тем или иным данным для того или иного пользователя. Информация о правах пользователей хранится в специальной базе данных mysql [5], которая создается при установке сервера MySQL. База данных mysql содержит более 15 таблиц, однако для системы безопасности наиболее важными являются следующие:

▪user;

▪db;

▪host;

▪tables_priv;

▪columns_priv.

Втаблице user, структура которой представлена в табл. 4.1, хранится информация о правах подключения пользователя к серверу

98

базы данных (но не к конкретной базе данных) с определенного хоста.

Втекстовых полях Host, User и Password содержится информация о имени пользователя (User), его пароле (Password) и доменном имени или IP-адресе хоста (Host) с которого можно подключаться к серверу MySQL. В столбцах User и Host можно использовать символ процента «%», который является шаблоном, заменяющим произвольный набор символов. Специальное имя пользователя nobody также является шаблоном (равносилен %), охватывающим всех пользователей не упомянутых ранее. Поля Select_priv, Insert_priv,,, Alter_priv имеют тип перечисления и могут принимать одно из двух значений: Y или N. Установка значения в Y дает возможность пользователю выполнять соответствующие SQL команды SELECT, INSERT,,, ALTER. По умолчанию эти поля принимают значение N,

итем самым пользователю разрешается только соединиться с сервером, а дальнейшее разграничение прав регулируется другими таблицами.

Втаблице user из-за использования шаблонов одному и тому же соединению может соответствовать несколько записей. Поэтому поиск выполняется следующим образом:

▪ищется соответствие для узлов (хостов), не содержащих шаблонов (пустое поле Host трактуется как %);

▪для одного и того же узла сначала проверяется соответствие имен, не содержащих шаблонов (пустое поле User трактуется как %);

99

▪первое найденное соответствие считается окончательным, и дальнейший поиск не производится.

Для пояснения данных правил рассмотрим табл. 4.2.

Если пользователь Peter соединяется с сервером MySQL с любого хоста, кроме k36.mephi.ru и k12.mephi.ru, то ему разрешается выполнять SQL-запросы SELECT, INSERT. Если же он выходит на связь с хоста k36.mephi.ru, то эти запросы для него запрещены. В данном случае порядок расположения записей не имеет значения, так как перед выполнением поиска выполняется сортировка таблицы в соответствии с перечисленными правилами.

В таблице user не упоминаются конкретные базы данных, т.е. она управляет правами доступа к серверу в целом. Права доступа к отдельным базам данных хранятся в таблице db, структура которой представлена в табл. 4.3.

Данная таблица похожа на таблицу user, но в ней вместо поля Password содержится поле Db, в котором указывается имя базы

100