Механизм базы данных «SQLite»
В этой статье представлен довольно интересный механизм базы данных «SQLite», который позволяет легко создавать базы данных и управлять ими. В нем освещаются основные аспекты работы с программой, а также показано, как можно использовать библиотеку функций C\C++, предоставляемую «SQLite», для подключения и работы с базой данных в ваших программах.
База данных «SQLite» система управления представляет собой программу с открытым исходным кодом, доступную на сайте www.sqlite.org. Что отличает SQLite от большинства других механизмов баз данных, так это его простота. Простота системы дает следующие преимущества:
- Простое администрирование
- Простота в работе
- Систему легко внедрить в свои программы
- Простота настройки и обслуживания
Простота системы — не единственное достоинство системы, «SQLite» — еще и очень надежная система, отказоустойчивая, обеспечивающая меньше ошибок в случае сбоя оборудования. Однако «SQLite» является очень быстрой системой баз данных.
Основные особенности системы:
- Поддержка версии языка запросов SQL92 (без характеристик типа процедуры)
- База данных хранится в одном файле на диске
- Файл базы данных может быть свободно разделен между процессами
- Поддерживает базы данных размером до 2 терабайт (241 байт)
- Размеры строковых и BLOB-данных ограничены только памятью
- Минимум кода на C/C++
- Простая и удобная библиотека C/C++
- Отсутствие внешней зависимости от других библиотек
- Поддержка операционной систем WINDOWS (почти все версии, начиная с WIN95), *unix
Нет Однако простота системы имеет недостаток, некоторыми функциями приходится жертвовать: высокий параллелизм процессов, богатство встроенные библиотечные функции, ограниченный язык SQL и многое другое. Если вам нужны эти функции и вы не возражаете против сложности, которую они добавляют, то эта система, вероятно, не для вас. «SQLite» не предназначен для использования в качестве корпоративной базы данных и не предназначен для конкуренции с Oracle или PostgreSQL или аналогичными системами управления базами данных.
Эти системные функции ограничивают возможности «SQLite». Наиболее вероятное использование программы, по-видимому, находится в следующих областях:
- прикладные программы с небольшими базами данных и приложения, не требующие управления базами данных
- для обучения SQL
- Используйте «SQLite» в качестве механизма базы данных для небольших и средних веб-сайтов с точки зрения объема и трафика.
- создайте временную базу данных в программах с большим объемом данных, если вам нужно отсортировать эти данные или сделать выборку на основе условия, что проще сделать с помощью SQL.
Вы можете загрузить программу из раздела загрузок на сайте www.sqlite.org. Рекомендую скачать версию 3 программы и выше. Для полной функциональности вам потребуются следующие файлы (для ОС WINDOWS):
- sqlite-3 x x.zip — инструмент командной строки для создания и редактирования базы данных
- sqlitedll-3 x x.zip — DLL
- sqlite-source-3 x x.zip — C
исходники
Это в статье описано, как работать с программой в среде WINDOWS. Язык запросов SQL здесь не рассматривается, но я просто укажу, что «SQLite» поддерживает почти весь стандартный набор команд SQL.
Использование инструмента командной строки «sqlite.exe»
программа командной строки «sqlite.exe» предназначена для создания и работы с базой данных с использованием языка запросов SQL и специальных команд. Вся работа, т.е. ввод команд и получение результатов выполнения запросов, производится в окне консоли WINDOWS.
В этой главе вы узнаете о специальных управляющих командах командной строки sqlite.exe. программа. Эти команды в основном используются для изменения формата вывода SQL-запросов и для выполнения других команд, таких как чтение данных из внешнего файла. Все специальные команды начинаются с точки, если команда введена без ведущей точки, она будет интерпретирована как SQL-запрос. Ниже приведен список всех специальных команд и их краткое описание.
Команда | Краткое описание | |
. databases | Список имен всех подключенных баз данных в данных текущего сеанса и соответствующих им файлов. | |
.dump ?TABLES?: | Создает дамп всех операторов SQL, используемых для создания базы данных или отдельной таблицы в текстовом формате | |
.echo ON | .show | Отображение значений заданных переменных |
.tables ?ШАБЛОН? | Показать имена баз данных таблиц (или по шаблону) | |
.timeout MS | Блокировать открытие таблиц на несколько миллисекунд MS | |
.width NUM NUM : | Установка ширины столбца при печати в столбцовом режиме |
Рассмотрим подробнее некоторые из этих команд. Сначала мы создадим базу данных, в которой будем рассматривать специальные команды. Из командной строки запустите консольную программу sqlite3.exe, введя следующее:
Где exam.db — имя создаваемой базы данных.
Создайте таблицу tbl1, используя оператор CREATE TABLE SQL.
Давайте вставим несколько записей в эту таблицу. Я заметил, что в инструкции по созданию таблицы для каждого поля мы задаем тип данных для его значений, программа «SQLite» игнорирует этот тип данных. Система управления базами данных «SQLite» весьма неточна в определении типов данных, что не характерно для стандарта языка SQL. Типизация данных в «SQLite» основана на так называемом «нетипизированном» принципе, что означает игнорирование информации о типе данных в определении столбца таблицы при его создании с помощью команды CREATE TABLE. Имена типов столбцов все же лучше указывать при объявлении таблиц, это повысит переносимость вашей базы данных. Есть одно исключение из принципа «нетипизации» — столбец типа INTEGER PRIMARY KEY (INTEGER не является INT; INT PRIMARY KEY — соответствует принципу отсутствия типа). Столбец типа INTEGER PRIMARY KEY должен содержать 32-битное целое число; любая попытка записи данных любого другого типа в такой столбец приведет к ошибке. Этот тип часто используется перед спецификатором столбца AUTOINCREMENT, столбец типа INTEGER PRIMARY KEY AUTOINCREMENT является ключевым столбцом таблицы, его значения генерируются автоматически.
Создадим таблицу для теперь пустой tbl2 и индексировать в свое первое поле «ikey».
Позволяет получить полный список (дамп) всех операторов SQL, используемых для создания определенной базы данных или таблицы.
где TABLE — необязательное имя. таблицы, для которой вы хотите просмотреть операторы SQL.
Эту команду можно использовать для экспорта базы данных, созданной в SQLite, в другую программу базы данных. Для этого необходимо сохранить полный оператор SQL в файл.
Включить/выключить отображение имен заголовков столбцов.
- ON: Включает отображение имена заголовков столбцов выходные имена заголовки столбцов
- OFF: отключить отображение имен заголовков столбцов
Используйте эту команду для импорта данных из файла. Данные в текстовом файле должны быть разделены строкой разделителя, эта строка разделителя определяется командой .separator. Разделителем по умолчанию является вертикальный символ «data
Мы подготовим следующие данные в файле exam.txt
Вставим эти данные в таблицу tbl2, для этого выполним следующие команды:
Эта команда изменяет формат вывода таблицы SQL с помощью запроса SELECT. «SQLite» может выводить результаты запроса в следующих форматах: csv, столбец, html, вставка, строка, список, вкладки, tcl. 2.8 поддерживает только следующие форматы: строка(и), столбец(и), вставка, лист, html Рассмотрим их по порядку.
— режим CSV В этом формате столбцы входной таблицы разделяются запятыми.
Режим подходит для экспорта таблицы в сторонние программы, например Excel понимает этот формат.
столбцовый режим. В этом режиме выходные данные выравниваются по левому краю столбцов записи таблицы.
См. также команду .width для установки ширины столбцов.
HTML-режим. Вывод в этом режиме выполняется в стиле определения таблицы html. Удобно для включения таблицы на веб-страницу.
Режим вставки. В этом режиме запросы SQL INSERT генерируются для таблицы, имя которой указано в качестве третьего параметра в команде.
где TABLE — имя таблицы, для которой генерируются операторы INSERT
Линейный режим. Здесь каждый столбец отображается на отдельной строке в формате имя_столбца = значение. Записи разделяются пустой строкой.
режим списка. Это режим «по умолчанию». Каждая запись таблицы отображается на отдельной строке со столбцами, разделенными разделителем. Символ-разделитель определяется командой .separator и по умолчанию равен строке. Работа с базой данных может быть разделена на три этапа:
- Открытие базы данных с помощью функции sqlite3_open().
- Выполняйте запросы к базе данных SQL, используя следующие sqlite3_exec() и функции-оболочки, но предпочтительнее использовать новый интерфейс доступа к базе данных, используя sqlite3_prepare(), sqlite3_step(), sqlite3_reset() и sqlite3_finalize(). На этом этапе выполняются все основные действия по работе с базой данных: извлечение и запись данных, их изменение, удаление, создание таблиц и т. д.
- Закройте базу данных с помощью функции sqlite3_close().
Алгоритм написания программы на C\C++ в Microsoft Visual C++ описан ниже.
Во-первых, перед написанием программ необходимо создать библиотеку импорта и файл экспорта. . Компоновщик (ССЫЛКА) использует эти файлы для создания программы, содержащей вызовы функций из библиотеки DLL.
Мы будем использовать программу Microsoft Visual C++ lib.exe. Эту программу можно найти в каталоге /bin. Выполните следующую команду из командной строки:
После копирования файлов sqlite3.dll и sqlite3.def в каталог /bin. Получите файлы sqlite3.lib и sqlite3.exp.
Создадим новый проект в Microsoft Developer Studio с помощью меню File/New, на вкладке Projects выберем консольное приложение Win32 и назовем проект exam1 .
Рядом с каталогом, в котором был создан проект (обычно «C:\Program Files\DevStudio\MyProjects\exam1\»), необходимо скопировать следующие файлы: sqlite3.dll , sqlite3.def, sqlite3.lib, sqlite3 .exp и файл базы данных exam1.db. Вам также понадобится заголовок sqlite3.h, который вы должны взять из файла sqlite3-source-3 x x.zip.
Чтобы создать базу данных, введите следующие команды:
Используя меню Проект/Настройки: открыть диалоговое окно настроек свойств проекта. Установите в раскрывающемся меню «Настройки» для версии Win32. На вкладке Ссылка в панели редактирования модуля Объект/библиотека добавьте sqlite3.lib.
в конец строки
Создайте новый файл «main.cpp» с помощью меню Проект/Добавить в проект/Новый и введите следующий текст программы, все пояснения даны в тексте программы.
Та же программа, написанная на C++ в классах.
Вывод обеих программ:
Теперь возьмем подробнее рассмотрим функции базы данных.
Открытие базы данных
Давайте рассмотрим функции открытия базы данных
Первый параметр dbname указывает имя файла базы данных.
Второй параметр ppDB — это указатель на открытую базу данных. После успешного выполнения функции этот указатель будет содержать дескриптор(ы) базы данных. Идентификатор базы данных используется для ссылки и выполнения операций с открытой базой данных из других библиотечных функций. Тип данных дескриптора — это частная структура данных, поля которой недоступны из программы.
Эти функции возвращают SQLITE_OK = 0 при успешном открытии базы данных. В противном случае возвращается код ошибки. Вы можете использовать функции sqlite3_errmsg() или sqlite3_errmsg16(), чтобы получить описание ошибки.
Закрыть базу данных
После выполнения всех необходимых действий базу данных следует закрыть.
Эта функция принимает в качестве параметра идентификатор открытой базы данных. Если функция закрытия базы данных вызывается во время транзакции, она завершится ошибкой. Функция sqlite3_close() возвращает SQLITE_OK в случае успеха, в противном случае — код ошибки. Эта функция закрытия базы данных должна вызываться, даже если функция открытия базы данных не работает во время выполнения.
Выполнение SQL-запросов
Рассмотрите доступ к базе данных с помощью функций sqlite3_exec().
С помощью этой функции можно выполнить один или несколько операторов SQL. Операторы SQL определяются вторым параметром функции: sql, если несколько запросов SQL определены через запятую, они выполняются последовательно.
Третий параметр функции sqlite_exec() — это Callback(). function.
Эта функция обратного вызова используется для получения и обработки данных из результата запроса SELECT. Функция обратного вызова вызывается для каждой строки результирующей таблицы.
Первый параметр функции, pArg, используется для передачи произвольной информации в функцию. обратный вызов из функции sqlite_exec() с использованием ее четвертого параметра.
Второй параметр функции — argc количество столбцов в результате запроса.
Третий параметр функции — argv an массив строк, где каждая строка — это один столбец результата выполнения запроса на выборку данных из своей таблицы (SELECT).
Четвертый параметр функции — массив columnNames элементов argc, каждый элемент массива содержит имя столбца результирующей таблицы. Если вы установите значение прагмы SHOW_DATATYPE в значение ON, этот параметр также будет содержать имя типа столбца (значение прагмы по умолчанию — OFF).
По умолчанию функция обратного вызова не будет вызываться, если результат запроса SQL SELECT пуст. Если прагма EMPTY_RESULT_CALLBACK включена, функция обратного вызова будет выполняться только один раз для получения имен столбцов.
Функция обратного вызова в sqlite_exec() может быть установлена в NULL. В таком случае никакая функция обратного вызова не будет вызываться. Это полезно при выполнении инструкций SQL, которые не возвращают данных, таких как инструкции INSERT, UPDATE и DELETE.
Функция обратного вызова должна возвращать значение null. Если Callback() возвращает ненулевое значение, выполнение запроса прерывается, а sqlite_exec() возвращает SQLITE_ABORT.
Если sqlite_exec() завершается успешно, возвращается SQLITE_OK. Если при разборе оператора SQL возникает ошибка, но функция обратного вызова не выполняется, функция sqlite_exec() возвращает один из определенных кодов ошибки. Строка сообщения об ошибке записывается в параметр errmsg. Библиотека автоматически выделяет память для этой строки ошибки, вы несете ответственность за освобождение выделенной памяти. Используйте для этого функцию sqlite3_free().
Если SQL-запрос не может быть выполнен из-за того, что база данных заблокирована, например, другим потоком, функция sqlite_exec() возвращает значение SQLITE_BUSY. Поведение библиотеки в этом случае можно изменить с помощью функций sqlite3_busy_handler() и sqlite3_busy_timeout().
Начиная с версии 3.0 и выше предпочтительнее использовать другой способ запроса к БД, без определения функция обратного вызова.
Запрос на выполнение SQL состоит из следующих шагов:
Шаг 1 . Подготовка инструкции SQL к выполнению
Перед выполнением запроса SQL необходимо подготовить его к выполнению, скомпилировав его в байт-код с помощью одной из следующих функций:
Эти функции принимают zSql параметр, строка с SQL по запросу. Третий параметр функции задает размер строки, если является отрицательным числом, то строка принимается полностью определенной в zSql, в противном случае — указанное количество символов в строке.
Функция возвращает указатель ppStmt на частную структуру, которая используется для представления скомпилированного оператора SQL. .
Функция также может возвращать строку pzTail, содержащую часть неиспользуемого SQL-запроса, указанного параметром zSql. В zSql можно определить несколько SQL-запросов, но выполняется только один SQL-запрос: первый, остальные записываются в pzTail.
Если SQL-запрос был скомпилирован успешно, функция возвращает SQLITE_OK, в противном случае — код ошибки. .
Шаг 2. Выполнение оператора SQL
После того, как оператор SQL был успешно скомпилирован, его можно выполнить с помощью одной или нескольких функций. вызовы:
Эта функция может возвращать одно из следующих значений во время выполнения оператора SQL, которое определяет ход выполнения запроса:
- SQLITE_BUSY : была предпринята попытка запроса базы данных, которая была заблокирована другим процессом.
- SQLITE_DONE — оператор SQL выполнен успешно
- SQLITE_ROW — возвращается, если есть строка данных из результата запроса. Такие функции, как sqlite3_column_*()
- SQLITE_ERROR : если при выполнении запроса произошла ошибка. Описание ошибки можно получить из функции sqlite3_errmsg().
- SQLITE_MISUSE : неправильное выполнение запроса. Можно попытаться выполнить запрос после получения кода ошибки SQLITE_ERROR или после успешного выполнения запроса и принятия код SQLITE_DONE или после завершения SQL-запроса с помощью функции sqlite3_finalize().
, используются для извлечения данных
.
Шаг 3 . Получить данные из результата запроса
Прежде чем вы сможете получить данные, вы должны указать количество столбцов в результате запроса. С помощью следующих функций:
Первая функция позволяет получить количество столбцов сразу после успешной компиляции SQL-команды, т.е. после выполнения функции sqlite3_prepare(). Эта функция возвращает значение null, если оператор SQL не возвращает данных (например, оператор UPDATE).
Вторая функция возвращает количество столбцов в текущей строке результата после вызова функции sqlite3_step(), которая возвращает SQLITE_ROW.
Для извлечения данных из результата запроса используется следующий набор функций sqite3_column_***():
- const void * sqlite3_column_blob(sqlite3_stmt *, int iCol) ;
- int sqlite3_column_bytes(sqlite3_stmt *, int iCol);
- int sqlite3_column_bytes16(sqlite3_stmt *, int iCol);
- double sqlite3_column_double(sqlite3_stmt *, int iCol);
- int sqlite3_column_int(sqlite3_stmt *, int iCol);
- sqlite_int64 sqlite3_column_int64(sqlite3_stmt *, int iCol);
- const unsigned char * sqlite3_column_text(sqlite3_stmt *,int iCol);
- const void* sqlite3_column_text16(sqlite3_stmt *, int iCol);
Чтобы получить значение, в зависимости от типа данных результата, необходимо вызвать одну из этих функций и указать номер столбца iCol, начинающийся с 0. Например, чтобы получить фактическое значение значения, вы должны вы бы вызвали функцию sqlite3_column_double(). Функции sqlite3_column_bytes() и sqlite3_column_bytes16() возвращают размер столбца TEXT или BLOB в байтах.
Чтобы получить тип результата столбца (с учетом операций), вы можете использовать функцию:
Функция возвращает одно из следующих значений определения типа:
Следующая функция может использоваться для определения типа столбца, объявленного при создании таблицы в предложении CREATE:
Для получения заголовков столбцов используются следующие функциональные функции:
Шаг 4 . Завершение выполнения запроса
Когда оператор SQL завершит выполнение, вы должны освободить виртуальную машину, выполнив следующую функцию:
Возвращает SQLITE_OK в случае успеха.
Если вы хотите снова запустить скомпилированный оператор SQL, вы должны сначала сбросить виртуальную машину в исходное состояние с помощью функции:
Рассмотрим этот способ выполнения запроса на примере:
Источник