Базы данных

По воли рока так случилось, что пришлось связаться с сабжем на FB и с удивлением обнаружил, что в нашей онлайн книге эта важная страница пуста И вот решил этот недостаток исправить
FB (во всяком случае используемая мною версия 0.90.0) поддерживает три СУБД - sqlite, mysql и posgresql. Я решил остановиться на mysql из следующих соображений:
1. sqlite не поддерживает технологию client-server и большие БД
2. (субъективно) mysql более распространён, а потому и больше и лучше описан в литературе
3. (ещё более субъективно) C API mysql более простой и наглядный
4. В дистрибутив mysql входят утилиты для конвертации данных из большинства других СУБД, включая и такую распространённую, как MS Access (и совместимую с ней OOo Basе)
Итак, начнём...

Что такое Базы Данных и зачем они нужны

Хотя тема эта более чем хорошо описана в различной литературе, всё же для ясности начнём с небольшой теории.

Базы Данных (далее - БД) - это средство упорядоченного хранения и обработки больших объёмов информации - одной из главных задач, решаемых вычислительной техникой. Исторически так сложилось, что к нашему времени прижился в основном один типа БД - реляционные, в которых информация хранится в таблицах (как минимум одной, но обычно в нескольких, связанных между собой). Каждая таблица БД состоит из столбцов (полей - field) и строк (записей - row). Каждая запись хранит информацию об одном объекте данных. Например, если мы построим БД "Телефонный справочник", то запись у нас будет хранить всю необходимую нам информацию об одном абоненте. Поля записи - это части этой информации, имеющие разное значение и тип. Например, текстовые поля "Имя", "Адрес" и "Электронная почта", числовые поля "Рабочий телефон" и "Мобильный телефон", так же нам может быть полезна информация о дне рождения - поместим её в поле типа Data и о времени  последнего звонка - поте типа Time... Поля БД позволяют хранить и многое другое - URLы, ссылки на файлы, рисунки и даже большие тексты. И не только хранить, но и быстро искать - для этого Системы Управления Базами Данных (далее - СУБД) формируют индексы - специальные служебные таблицы, ускоряющие поиск по таблицам БД. СУБД - это очень сложные программы, но их мы писать не будем, так как их и так уже разработано очень много. А мы займёмся тем, как их возможности использовать для наших целей. Рассмотрим их на примере общения нашей программы с СУБД MySQL. Подробно с этой СУБД можно познакомиться в многочисленной литературе, которая есть в свободном доступе, например:

Р. Яргер, Дж. Риз, Т. Кинг MySQL. Базы данных для небольших предприятий и Интернета
Л. Веллинг, Л. Томпсон MySQL. Учебное пособие
В. Гольцман. MySQL 5.0
Л. Аткинсон. MySQL. Библиотека профессионала
Б. Шварц, П. Зайцев, В. Ткаченко MySQL Оптимизация производительности

Кроме того, существует отличный русский перевод оригинальной документации фирмы MySQL AB, состоящей из двух книг: Руководство администратора и Справочник по языку
Так же во многих аспектах применимы учебники по языку SQL и другим подобным СУБД (например, по MS SQL Server или даже по MS Access)

Здесь же мы рассмотрим устройство СУБД очень кратко, озвучив только основные понятия, которые необходимы для понимания происходящего.

MySQL построен по технологии "client-server" - т.е. его таблицы и ядро их обслуживающее (т.н. "сервер") могут находиться на одном физическом компьютере, а программы, работающие с этими таблицами (т.н. "клиенты") - на других компьютерах хоть в пределах локальной сети, хоть на другом конце Земли Но, конечно же, это не исключает и нахождение всех компонентов на одном компьютере. Сервер MySQL можно (и нужно) скачать из Интернета - он распространяется по лицензии GNU GPL. Кроме того, нам потребуется библиотека связи libmysql.dll, в моём компиляторе она находится в папке /examples/database или её так же можно легко найти в Интернете. Эту библиотеку необходимо скопировать в папку с разрабатываемой программой.

По сути взаимодействие клиента (нашей программы) и сервера БД состоит из последовательности запросов - команд, и анализа их результатов. например, мы можем послать серверу запрос: "Создай БД Телефонный_справочник" или запрос: "Добавь в таблицу Абоненты БД Телефонный_справочник новую запись" или запрос: "Найди всю информацию об абоненте Иван Петров". Разумеется, такие запросы СУБД MySQL не поймёт. Для того, что бы формализовать общение клиента с сервером БД разработан специальный язык SQL (Structured Query Language - Язык структурированных запросов). Команды языка SQL, включённые в наши программы, в дальнейшем я буду писать ЗАГЛАВНЫМИ БУКВАМИ, в то время, как функции C API, предназначенные для передачи команд SQL серверу - строчными буквами.

MySQL С АPI и FB

FB для связи с сервером MySQL использует "обёртку" для С API - библиотеки для программирования на языках C/C++. Все функции и структуры данных этой обёртки содержатся во входящем в состав компилятора заголовочном файле mysql.bi. Поэтому начало любой программы - клиента MySQL выгляди так:

Код:


' Подключаем заголовки обёртки для C API
#Include "mysql/mysql.bi"

Dim db As MYSQL Ptr ' структура, хранящая информацию доступа к серверу
Dim db_query As ZString Ptr ' строка запроса на языке SQL
Dim db_result As MYSQL_RES Ptr ' структура с заголовком таблицы результатов запроса

db = mysql_init (NULL) ' инициализация библиотеки libmysql.dll и заполнение структуры MYSQL

' параметры доступа к БД
Dim db_name As String   ' название БД (один сервер может обслуживать много БД)
Dim db_server As String   ' сетевой адрес сервера (URL, IP или localhost)
Dim db_port As Integer ' TCP/IP порт (по умолчанию 3306)
Dim db_user As String  ' логин (если в настройках сервера разрешён анонимный доступ, можно установить в NULL)
Dim db_password As String  ' пароль (если в настройках сервера разрешён анонимный доступ, можно установить в NULL)

' подключаемся к серверу
If mysql_real_connect (db, db_server, NULL, NULL, NULL, db_port, NULL, NULL) = 0 Then ' функция возвращает нуль при ошибке подключения
     Print "Ошибка соединения с сервером MySQL:"; *mysql_error (db) ' функция возвращает указатель на текст описания ошибки
End If

' если БД уже существует - выбираем её
' если мы планируем создать БД в процессе работы программы, следующие строки следует пока опустить
If mysql_select_db (db, db_name) Then
 Print "Не найдена база данных "; db_name
End If

' далее основная часть программы  
' ...

' завершаем программу так:
mysql_close (db)
End
"

Подробно с функциями MySQL C API можно познакомиться здесь: http://www.cyberguru.ru/database/mysql/c-api.html
Мы же дальше рассмотрим пример их использования в программах на FB на примере создания и использования программы "Телефонный справочник".

Язык SQL

Главными для нас функциями C API являются функция передачи серверу запроса mysql_query (db As MYSQL Ptr, query as ZString Ptr) As Integer и функция получения результата выполнения запроса сервером mysql_store_result (db as MYSQL Ptr) As MYSQL_RES Ptr

Функция mysql_query посылает написанный на языке SQL запрос, содержащийся в строке по адресу query, серверу, описанному в структуре db. Рассмотрим, какие бывают запросы. Сразу оговорюсь, что эти же запросы, которые мы будем передавать серверу через функцию mysql_query (), можно задавать ему и через входящего в состав дистрибутива сервера консольного клиента. Это удобно использовать для проверки правильности синтаксиса и логики работы запроса. Впрочем, есть много сторонних GUI приложений, в которых это делать ещё удобнее, например, MySQL-Front.

Предположим, что нашей БД "Телефонный справочник" ещё не существует и нам её надо создать в программе. Для этого предназначена SQL команда CREATE DATABASE. В приведённом ниже коде мы создадим БД под именем "phonebook" и подключимся к ней:

Код:


' подготовка строки запроса
Dim str_query as String
db_name = "phonebook"
str_query = "CREATE DATABASE " + db_query
db_query = Allocate (Len (str_query) + 1)
*db_query = str_query + Chr (0)

' посылаем запрос серверу
If mysql_query (db, db_query) <> 0 then
     Print "Ошибка при создании БД ";  *mysql_error (db)
Else
     If mysql_select_db (db, db_name) Then
         Print "Не найдена база данных "; db_name
     End If
End If


Примечание: в C API присутствует отдельная функция mysql_create_db (), однако (наверное, из-за её избыточности) в обёртке для FB она отсутствует.
Что бы потом не забыть, сразу скажу, что для удаления существующей БД предназначена SQL команда DROP DATABASE phonebook.

Как уже было сказано выше, БД состоит из таблиц. Значит следующим шагом должно быть создание таблиц.

Код:


' подготовка строки запроса
Dim str_query As String
Dim tbl_name As String
tbl_name = "phonebook"
str_query = "CREATE TABLE " + tbl_name + " (name VarChar NOT NULL, phone VarChar NULL, gsm VarChar NULL,  address Text NULL, photo Image NULL, comment LongText NULL)"
db_query = Allocate (Len (str_query) + 1)
*db_query = str_query + Chr (0)

' посылаем запрос серверу
If mysql_query (db, db_query) <> 0 then Print "Ошибка при создании таблицы ";  *mysql_error (db)


Этот код создаёт таблицу под именем "phonebook". В скобках указаны поля таблицы в формате: имя_поля тип_поля NULL (разрешает полю быть пустым или NOT NULL (запрещает полю оставаться пустым - такие поля обязательно должны содержать данные). Обратите внимание, что кроме указанных полей, сервер MySQL создаст ещё одно с именем Id, типом Int, параметром NOT NULL и параметром AUTO INCREMЕNT. Дело в том, что если в БД есть несколько связанных таблиц, то в каждой из них должно быть хотя бы одно поле, записи которого уникальны (не повторяются). Такое поле называется "ключевым"  (Primary Key). Что бы мы не забыли его создать, MySQL делает это за нас.

MySQL поддерживает большое разнообразие типов полей. Перечислю наиболее важные из них:
Bit - содержит один бит, удобен для хранения логических условий, состояние чекбоксов и т.п.
TinyInt, SmallInt, Int, BigInt - целые числа, дополнительно в скобках после имени типа можно уточнить количество разрядов
Real, Double, Float - вещественные числа разной точности, после имени типа в скобках через запятую вводится количество целых и дробных разрядов
Char, VarChar, TinyText, Text, LongText - текстовые поля, отличаются длиной (которую можно ограничивать, указывая в скобках максимальное количество символов), кроме того, поиск и сортировку можно проводить только по полям типа Char и VarChar
Date, Time, DateTime, Year - поля для хранения отметок даты и времени
Binary - для хранения произвольных данных большого объёма, не поддерживаются поиск и сортировка
Point, Polygon, Geometry - векторная графика
Image - растровая графика

В полученной таблице каждая строка - запись, - хранит информацию об одном абоненте. Но у неё есть один изъян - что делать, если у абонента несколько мобильных телефонов? такое ведь встречается... Предусмотреть по нескольку полей gsm1, gsm2, gsm3 и т.д.? Но у большинства телефон один - значит в большинстве записей остальные поля будут пустыми... Или делать несколько записей с одинаковым именем абонента? Это ещё хуже, хотя бы потому, что имя надо вводить каждый раз заново и можно ошибиться, что приведёт к ошибкам поиска. Правильный вариант - создать несколько таблиц так, чтобы не было повторений одних и тех же данных. Это называется "нормализованной БД". В нашем случае нам нужны как минимум две таблицы:
abonent - содержит информацию о человеке
phone - содержит информацию о телефонах

Код:


Dim str_query As String
Dim tbl_name As String

tbl_name = "abonent"
str_query = "CREATE TABLE " + tbl_name + " (name VarChar NOT NULL, address Text NULL, photo Image NULL, comment LongText NULL)"

db_query = Allocate (Len (str_query) + 1)
*db_query = str_query + Chr (0)

' посылаем запрос серверу
If mysql_query (db, db_query) <> 0 then Print "Ошибка при создании таблицы абонентов: ";  *mysql_error (db)

tbl_name = "phone"
str_query = "CREATE TABLE " + tbl_name + " (abonent Int NOT NULL, phone VarChar NOT NULL, comment LongText NULL)"

db_query = Allocate (Len (str_query) + 1)
*db_query = str_query + Chr (0)

' посылаем запрос серверу
If mysql_query (db, db_query) <> 0 then Print "Ошибка при создании таблицы абонентов: ";  *mysql_error (db)


С первой таблицей всё ясно - из первоначальной таблицы просто выкинуты поля для номеров телефонов. А вот во второй осталось только одно поле для номера, а поле "abonent" стало цифровым (Integer). В это поле должны помещаться номера записей из поля Id таблицы abonent. Т.о. если наш абонент имеет несколько телефонов, мы делаем только одну запись для него в таблице abonent и несколько записей в таблице phone, при заполнении поля abonent которой мы не вводим каждый раз имя заново, а выбираем нужного абонента из таблицы абонентов. Это называется связью таблиц (relations), что и делает нашу базу нормализованной и реляционной.

В завершении рассказа о создании структуры БД отмечу, что в результате наших действий в нашей БД появилась избыточность - в ней осталась ненужная нам таблица phonebook. Удалим её:

Код:


Dim str_query As String
Dim tbl_name As String

tbl_name = "phonebook"
str_query = "DROP TABLE " + tbl_name

db_query = Allocate (Len (str_query) + 1)
*db_query = str_query + Chr (0)

' посылаем запрос серверу
If mysql_query (db, db_query) <> 0 then Print "Ошибка при удалении таблицы: ";  *mysql_error (db)