Двигатель базы данных "SQLite"
26 января 2006 года
В данной статье рассказывается о довольно интересном "двигателе" базы данных "SQLite", с помощью которого можно с легкостью создавать и управлять базой данных. Освещаются основные аспекты работы с программой, а также показано, как можно использовать библиотеку функций на C\C++, предоставляемой "SQLite" для подключения и работы с базой данных в ваших программах.
Система управления базами данных "SQLite" является программой предоставляемой на условиях "открытого исходного кода" и доступна на сайте www.sqlite.org. "SQLite" отличает от большинства других двигателей баз данных - простота. Простота системы дает следующие преимущества:
- Простота администрирования
- Легкость в работе
- Система проста во внедрении в ваши программы
- Простота в настройке и поддержке
Простота системы не является единственным достоинством системы, "SQLite" также очень надежная система, стойкая к ошибкам, дающая меньшее количество ошибок при аппаратных сбоях. Вместе с тем "SQLite" очень быстрая система работы с базой данных.
Основные характеристики системы:
- Поддержка языка запросов версии SQL92 (без особенностей типа процедур)
- База данных сохраняется в одиночном файле на диске
- Файл базы данных можно свободно разделить между процессами
- Поддерживает базы данных размером до 2-х терабайт (241 байт)
- Размеры строковых данных и BLOB данных ограничены только памятью
- Минимальный код на С/С++
- Простая и удобная библиотека на С/С++
- Нет никакой внешней зависимости от других библиотек
- Поддержка операционных систем WINDOWS (практически все версии начиная с WIN95), *unix
Однако простота системы имеет и обратную сторону, приходится жертвовать некоторыми характеристиками: высоким параллелизмом процессов, богатством встроенных библиотечных функций, ограниченность языка SQL и другие. Если вы нуждаетесь в этих особенностях и не боитесь сложностей добавляемых этими возможностями, то вероятно эта система не для вас. Система "SQLite" не предназначена, чтобы быть двигателем базы данных предприятия, она не разработана, чтобы конкурировать с Oracle или PostgreSQL или аналогичными системами управления баз данных.
Такие характеристики системы накладывают ограничения на область применения "SQLite". Наиболее вероятным использование программы представляется в следующих областях:
- прикладные программы с небольшими базами данных, и приложений не требующих администрирования базы данных
- для обучения языку SQL
- использование "SQLite" как двигатель базы данных небольших и средних по объему и посещаемости интернет сайтов.
- для создания временной базы данных в программах имеющих множество данных, если эти данные требуется сортировать, или делать выборки по условиям, что проще сделать это с помощью SQL.
Загрузить программу можно с сайта www.sqlite.org раздел download. Советую загружать программу версии 3 и выше. Для полноценной работы вам потребуются следующие файлы (для операционной системы WINDOWS):
- sqlite-3 x x.zip - программа командной строки для создания и изменения базы данных
- sqlitedll-3 x x.zip - DLL библиотека
- sqlite-source-3 x x.zip - Исходники на С
В данной статье описана работа с программой в среде WINDOWS. Здесь не рассматривается язык запросов SQL, отмечу лишь, что "SQLite" поддерживает практически весь стандартный набор SQL инструкций.
Работа с программой командной строки "sqlite.exe"
Программа командной строки "sqlite.exe" предназначена для создания и работы с базой данных, используя язык запросов SQL и специальные команды. Вся работа, а именно ввод команд и получение результатов выполнения запросов происходит в консольном окне WINDOWS.
В этой главе вы познакомитесь со специальными командами управления программы командной строки "sqlite.exe". Эти команды используются в основном для изменения формата вывода результатов SQL запросов, и для выполнения некоторых других команд, например чтение данных из внешнего файла. Все специальные команды начинаются с точки, если команда введена без ведущей точки она будет интерпретирована как SQL запрос. Ниже представлен список всех специальных команд и дано краткое их описание.
Команда | Краткое описание |
.databases | Вывод списка имен всех подключенных в текущем сеансе баз данных и соответствующих им файлов. |
.dump ?TABLES?: | Дамп всех SQL инструкций использованных в создании БД или отдельной таблицы в текстовом формате |
.echo ON|OFF | ВКЛ | ВЫКЛ эхо введенных команд |
.exit | Выход из программы |
.explain ON|OFF | Управляет режимом вывода виртуальных машинных команд. Используется при выполнении SQL запроса EXPLAIN. |
.header(s) ON|OFF | ВКЛ | ВЫКЛ показ заголовков столбцов |
.import FILE TABLE | Импорт данных из файла FILE в таблицу TABLE |
.indices TABLE | Показывает имена всех индексов таблицы |
.mode MODE | Установка режима вывода: line(s), column(s), insert, list, html и других |
.nullvalue STRING | Напечатает строку STRING вместо NULL данных при выводе SQL запроса SELECT |
.output FILENAME | Послать весь вывод в файл FILENAME |
.output stdout | Послать весь вывод на экран |
.promt MAIN COTINUE | Изменить стандартную строку подсказки |
.quit | Выход их программы |
.read FILENAME | Выполнение SQL инструкций из файла FILENAME |
.schema ?TABLE? | Покажет текст SQL инструкции CREATE для всех таблиц или указанной таблицы |
.separator STRING | Изменить строку разделитель колонок, используется при выводе SQL запроса SELECT и команды .import |
.show | Показать значения установленных переменных |
.tables ?PATTERN? | Вывод списка имен таблиц БД (возможно по шаблону) |
.timeout MS | Блокирование открытия таблиц на число миллисекунд MS |
.width NUM NUM : | Установка ширины столбцов при выводе в режиме column |
Подробно рассмотрим некоторые из этих команд. Предварительно создадим базу данных, на которой мы и рассмотрим специальные команды. Из командной строки запустите консольную программу "sqlite3.exe" введя следующее:
sqlite3 exam.db SQLite version 3.1.3 Enter ".help" for instructions
Где exam.db имя создаваемой базы данных.
Создадим таблицу tbl1, с помощью SQL инструкции CREATE TABLE.
sqlite> CREATE TABLE tbl1 (one INTEGER PRIMARY KEY AUTOINCREMENT, two VARCHAR(50), three REAL);
Вставим несколько записей в данную таблицу. Отмечу, что в инструкции создания таблицы для каждого поля мы задали тип данных для его значений, этот тип данных игнорируется программой "SQLite". Система управления базой данных "SQLite" довольно свободно относится к определению типов данных, что не характерно для стандарта языка SQL. Типизация данных в "SQLite" основывается на так называемом принципе "typeless", означающий игнорирование информации о типе данных в определении столбцов таблицы при ее создании с помощью инструкции CREATE TABLE. И все же лучше включать имена типов столбцов при объявлении таблиц, это повысит переносимость вашей базы данных. Имеется одно исключение из принципа "typeless" - это столбец типа INTEGER PRYMARY KEY (INTEGER не INT; INT PRIMARY KEY - удовлетворяет принципу "typeless"). Столбец с типом INTEGER PRIMARY KEY должен содержать 32-х битное целое число, попытка записать данные любого другого типа в столбец этого типа приведет к ошибке. Этот тип обычно используется перед спецификатором столбца AUTOINCREMENT, столбец с типом INTEGER PRIMARY KEY AUTOINCREMENT является ключевым столбцом таблицы, его значения генерируются автоматически.
sqlite> INSERT INTO tbl1 VALUES(NULL, 'hello', 3.10); sqlite> INSERT INTO tbl1 VALUES(NULL, 'is', 5.34); sqlite> INSERT INTO tbl1 VALUES(NULL, 'from', NULL);
Создадим, пока пустую таблицу tbl2 и индекс по ее первому полю "ikey".
sqlite> CREATE TABLE tbl2 (ikey INTEGER PRIMARY KEY, nm VARCHAR(50)); sqlite> CREATE UNIQUE INDEX itbl2 ON tbl2(ikey); sqlite>
Команда .dump
Позволит вам получить полный дамп (листинг) всех SQL инструкций использованных при создании базы данных или некоторой конкретной таблицы.
Синтаксис:
.dump ?TABLE?
где TABLE - необязательное имя таблицы, для которой вы хотите получить дамп SQL инструкций.
Пример:
sqlite> .dump BEGIN TRANSACTION; CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50), three real); INSERT INTO "tbl1" VALUES(1, 'hello', 3.1); INSERT INTO "tbl1" VALUES(2, 'is', 5.34); INSERT INTO "tbl1" VALUES(3, 'from', NULL); DELETE FROM sqlite_sequence; INSERT INTO "sqlite_sequence" VALUES('tbl1', 3); CREATE TABLE tbl2 (ikey integer integer primary key, nm VARCHAR(20)); CREATE UNIQUE INDEX itbl2 on tbl2(ikey); COMMIT; sqlite>
Эту команду можно использовать для экспорта базы данных созданную в SQLite в другую программу работы с базами данных. Для этого необходимо сохранить полный дамп SQL инструкций в файле.
sqlite> .output exam.sql --перенаправить вывод в файл sqlite> .dump
Команда .header(s)
Включает / выключает вывод названий заголовков столбцов.
Синтаксис:
.header(s) ON | OFF
где,
- ON - включить вывод имен заголовков столбцов
- OFF - отключить вывод имен заголовков столбцов
Пример:
sqlite> .header ON sqlite> SELECT * FROM tbl1; one|two|three 1|hello|3.1 2|is|5.34 3|from| sqlite> .header OFF sqlite> SELECT * FROM tbl1; 1|hello|3.1 2|is|5.34 3|from| sqlite>
Команда .import
С помощью данной команды имеется возможность импортировать данные из текстового файла. Данные в текстовом файле должны быть разделены строкой-разделителем, эта строка разделитель определяется командой .separator. По умолчанию символом-разделителем является символ вертикальной черты "|". Данная команда введена в версии 3.
Синтаксис:
.import FILE TABLE
где,
- FILE - имя входного тестового файла с данными
- TABLE - имя таблицы куда вставляются данные
Пример:
Подготовим следующие данные в файле exam.txt
1;Roy 2;Ted 3;Sam 4;Bob
Вставим эти данные в таблицу tbl2, для этого выполним следующие команды:
sqlite> .separator ';' -- установим символ-разделитель в точку с запятой sqlite> .import exam.txt tbl2 -- импорт данных sqlite> SELECT * FROM tbl2; 1; Roy 2; Ted 3; Sam 4; Bob
Команда .mode
Данная команда изменяет формат вывода таблицы SQL запросом SELECT. "SQLite" может вывести результат запроса в следующих форматах: csv, column, html, insert, line, list, tabs, tcl. Для версии 2.8 поддерживается только следующие форматы: line(s), column(s), insert, list, html. Рассмотрим их по порядку.
- Режим csv. В данном формате столбцы записи таблицы разделяются запятыми.
Пример:
sqlite> .mode csv sqlite> SELECT * FROM tbl1; 1,"hello",3.1 2,"is",5.34 3,"from", sqlite>
Режим удобен для экспорта таблицы в сторонние программы, например данный формат, понимает Excel.
Режим column. В этом режиме вывод выровнен по левой границе столбцов записей таблицы.
Пример:
sqlite> .mode columns sqlite> SELECT * FROM tbl1; 1 hello 3.1 2 is 5.34 3 from sqlite>
Смотрите также команду .width для задания ширины столбцов.
Режим html. Вывод в этом режиме выполнен в стиле определения таблицы языка html. Удобен для включения таблицы в страничку сайта.
Пример:
sqlite> .mode html sqlite> SELECT * FROM tbl2; <TR><TD>1</TD> </TD>Roy </TR> <TR><TD>2</TD> </TD>Ted </TR> <TR><TD>3</TD> </TD>Sam </TR> <TR><TD>4</TD> </TD>Bob </TR> sqlite>
Режим insert. В данном режиме генерируются SQL запросы INSERT для таблицы, имя которой указано третьим параметром в команде.
Синтаксис:
.mode insert TABLE
где TABLE - имя таблицы, для которой генерируются инструкции INSERT
Пример:
sqlite> .mode insert tbl3 sqlite> SELECT * FROM tbl1; INSERT INTO tbl3 VALUES(1,'hello',3.1); INSERT INTO tbl3 VALUES(2,'is',5.34); INSERT INTO tbl3 VALUES(3,'from',NULL); sqlite>
Режим line. Здесь каждый столбец выводится в отдельной строке в формате имя_столбца = значение. Записи разделяются пустой строкой.
Пример:
sqlite> .mode line sqlite> SELECT * FROM tbl1; one = 1 two = hello three = 3.1 one = 2 two = is three = 5.34 one = 3 two = from three = sqlite>
Режим list. Является режимом, установленным по умолчанию. Каждая запись таблицы выводится в отдельной строке с разделением столбцов символом-разделителем. Символ-разделитель определяется командой .separator, и по умолчанию равен прямой черте '|'.
Пример:
sqlite> .mode list sqlite> SELECT * FROM tbl1; 1|hello|3.1 2|is|5.34 3|from| sqlite>
Режим tabs. В данном режиме столбцы записей таблицы отделяются друг от друга символом табуляции.
Пример:
sqlite> .mode tabs sqlite> SELECT * FROM tbl1; 1 hello 3.1 2 is 5.34 3 from sqlite>
Режим tcl. Вывод выполнен в формате элементов списка языка TCL.
Пример:
sqlite> .mode tcl sqlite> SELECT * FROM tbl2; "1" " Roy \r" "2" " Ted\r" "3" " Sam\r" "4" " Bob\r" sqlite>
Команда .output
Данная команда перенаправляет вывод в файл или обратно на экран.
Синтаксис:
.output FILENAME .output stdout
где,
- FILENAME - имя файла, куда направлен вывод
- stdout - направит вывод на экран
Команда .read
Читает и выполняет SQL инструкции из файла.
Синтаксис:
.read FILENAME
где FILENAME - имя файла содержащего SQL инструкции
Пример:
Создайте текстовый файл, содержащий следующие SQL запросы:
BEGIN TRANSACTION; CREATE TABLE tbl3 (name VARCHAR(30), lastname VARCHAR(50), num INT); INSERT INTO tbl3 VALUES ('Jim', 'Green', 1653); INSERT INTO tbl3 VALUES ('Ann', 'Folkin', 1891); INSERT INTO tbl3 VALUES ('Fredy', 'Donovan', 2345); COMMIT;
Назовем созданный файл exam.sql, и выполним следующие команды:
sqlite> .read exam.sql sqlite> SELECT * FROM tbl3; Jim|Green|1653 Ann|Folkin|1891 Fredy|Donovan|2345 sqlite>
Команда .schema
Данная команда выдаст все SQL инструкции CREATE TABLE и CREATE INDEX использованные для создания таблиц и индексов текущей базы данных. Возможно указание имени конкретной таблицы, для которой необходимо вывести SQL инструкцию ее создания.
Синтаксис:
.schema ?TABLE?
где, ?TABLE ? - необязательное имя таблицы
Пример:
sqlite> .schema CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE tbl1 (one integer primary key autoincrement, two varchar(50), three real); CREATE TABLE tbl2 (ikey integer integer primary key, nm VARCHAR(20)); CREATE TABLE tbl3 (name VARCHAR(30), lastname VARCHAR(50), num INT); CREATE UNIQUE INDEX itbl2 on tbl2(ikey); sqlite>
Команда .tables
Команда выдаст список названий таблиц созданных в текущей базе данных. Возможно, использовать определение шаблона, для выдачи имен таблиц удовлетворяющему шаблону. Синтаксис шаблона аналогичен определению шаблона в предложении LIKE.
Синтаксис:
.tables ?PATTERN?
где ?PATERN? - необязательное определение шаблона поиска
Пример:
sqlite> .tables sqlite_sequence tbl1 tbl2 tbl3 sqlite> .tables %tb% 'с шаблоном tbl1 tbl2 tbl3 sqlite>
Команда .width
Используется для установки ширины столбцов, при формате вывода установленном в column.
Синтаксис:
.width NUM NUM :
где NUM - ширина соответствующего столбца в символах.
Пример:
sqlite> .mode column sqlite> .width 3 10 7 sqlite> SELECT * FROM tbl1; 1 hello 3.1 2 is 5.34 3 from sqlite>
Использование библиотеки функций C\C++
Рассмотрим доступ к базе данных из программы на языке "С/C++".
Алгоритм работы с базой данных можно разбить на три этапа:
- Открытие базы данных, используя функцию sqlite3_open().
- Выполнение SQL запросов к базе данных, используя следующие функции sqlite3_exeс() и функции обертки, но предпочтительнее использование нового интерфейса доступа к базе данных, с использованием функций sqlite3_prepare(), sqlite3_step(), sqlite3_reset() и sqlite3_finalize(). На данном этапе выполняются все основные действия по работе с базой данных: извлечение и запись данных, их модификация, удаление, создание таблиц и прочее.
- Закрытие базы данных, используя функцию sqlite3_close().
Далее описан алгоритм написания программы на C\C++ в Microsoft Visual С++.
В начале перед написанием программ необходимо создать библиотеку импорта и файл экспорта. Компоновщик (LINK) использует эти файлы, чтобы сформировать программу, которая содержит вызовы функций из динамической библиотеки DLL.
Воспользуемся программой lib.exe из Microsoft Visual C++. Эту программу можно найти в каталоге /bin. Выполните следующую команду из командной строки:
lib /DEF: sqlite3.def
Предварительно скопировав в каталог /bin файлы sqlite3.dll и sqlite3.def. Получим файлы sqlite3.lib и sqlite3.exp.
Создадим новый проект в Microsoft Developer Studio с помощью меню File / New, выберите на вкладке Projects Win32 Console Application (консольное приложение WINDOWS) и введите имя проекта 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.
Для создания базы данных введите следующие команды:
-- создание таблицы с двумя полями: one, two sqlite> create table tbl1 (one varchar(10), two smallint); sqlite> insert into tbl1 values('hello!', 10); -- вставка записи sqlite> insert into tbl1 values('goodbye', 20); -- вставка записи sqlite> select * from tbl1; -- выполнение запроса выборки данных hello!|10 goodbye|20 sqlite>
С помощью меню Project / Settings: вызовите диалоговое окно установок свойств проекта. Выпадающий список Settings For установите в Win32 Release. На вкладке Link в строке редактирования Object/library modules в конце строки добавьте sqlite3.lib.
Создайте новый файл "main.cpp" с помощью меню Project / Add to Project / New, и введите следующий текст программы, все пояснения даны в тексте программы.
Листинг программы:
#include <stdio.h> #include <stdlib.h> #include "sqlite3.h" /* Функция повторного вызова. Здесь происходит обработка результата SQL запроса. Эта функция будет вызвана для каждой строки результата выполняемого запроса. */ static int callback(void *notused, int coln, char **rows, char **colnm) { /* coln - число столбцов в запрашиваемой таблице rows - массив значений в записи colnm - имена столбцов */ int i; static int b = 1; // печать названий столбцов if (b) // печатать только один раз { for(i=0; i<coln; i++) printf("%s\t", colnm[i]); printf("\n"); b = 0; } // печать разделителя строк for(i=0; i<coln; i++) printf("-------\t"); printf("\n"); //печать значений for(i=0; i<coln; i++) printf("%s\t|", rows[i]); printf("\n"); return 0; } // end callback void main() { int rc; sqlite3 *db; // указатель на открытую базу данных char * errmsg; // сообщение об ошибке char const * sql = "select * from tbl1;"; // SQL запрос // открываем БД rc = sqlite3_open("exam1.db", &db); if (rc) { //если ошибка при открытии БД errmsg = (char*) sqlite3_errmsg(db); printf("%s\n", errmsg); sqlite3_close(db); exit(1); } // выполнение SQL запроса rc = sqlite3_exec(db, sql, callback, NULL, &errmsg); if (rc != SQLITE_OK) { //если ошибка при выполнении запроса printf("%s\n", errmsg); sqlite3_close(db); exit(1); } //закрытие БД sqlite3_close(db); system("pause"); } // end main
Та же программа написанная на C++ в классах.
Листинг программ:
#include <stdlib.h> #include <iostream.h> #include <string.h> #include "sqlite3.h" class DB { private: sqlite3 *database; //функция повторного вызова static int callback(void *notused, int coln, char **rows, char **colnm) { /* coln - число столбцов в запрашиваемой таблице rows - массив значений в записи colnm - имена столбцов */ int i; static int b = 1; // печать названий столбцов if (b) // печатать только один раз { for(i=0; i<coln; i++) cout << colnm[i] << "\t"; cout << "\n"; b = 0; } // разделитель строк for(i=0; i<coln; i++) cout << "------\t"; cout << "\n"; //печать значений for(i=0; i<coln; i++) cout << rows[i] << "\t"; cout << "\n"; return 0; } // end callback public: // класс ошибки class Err { public: char *errstr; Err(const char * str) {strcpy(errstr, str);} }; // конструктор открывает БД DB(char *db_name) { int rc; char *errmsg; rc = sqlite3_open(db_name, &database); // если ошибка if (rc) { errmsg = (char*) sqlite3_errmsg(database); throw Err(errmsg); sqlite3_close(database); } } // end DB //деструктор закрывает БД ~DB() {sqlite3_close(database);} // выполнение SQL запроса void SQLRun(const char *sql) { int rc; char *errmsg; rc = sqlite3_exec(database, sql, callback, NULL, &errmsg); if (rc != SQLITE_OK) { throw Err(errmsg); sqlite3_close(database); } } // end SQLRun }; // end class DB // основная функция void main() { DB *db; const char *sql = "select * from tbl1"; //открываем БД try { db = new DB("exam1.db"); } catch (DB::Err e) { cout << e.errstr << "\n"; exit(1); } //выполняем SQL запрос try { db->SQLRun(sql); } catch(DB::Err e) { cout << e.errstr << "\n"; exit(1); } // закрывает БД delete(db); } //end main
Вывод обеих программ:
one two ------- ------- hello! |10 | ------- ------- goodbye |20 |
Теперь более подробно рассмотрим функции работы с базой данных.
Открытие Базы Данных
Рассмотрим функции открытия базы данных.
int sqlite3_open ( const char * dbname;//имя файла БД в кодировке UTF-8 sqlite3 ** ppDB // описатель базы данных ); int sqlite3_open16 ( const char * dbname;//имя файла БД в кодировке UTF-16 sqlite3 ** ppDB // описатель базы данных );
Первый параметр dbname указывает на имя файла базы данных.
Второй параметр ppDB указатель на открытую базу данных. При успешном выполнении функции данный указатель будет содержать описатель (дескриптор) базы данных. Дескриптор базы данных используется для ссылки на открытую базу данных из других функций библиотеки и выполнения операций над ней. Типом данных описателя является закрытая структура данных, поля которой не доступны из программы.
typedef struct sqlite3 sqlite3;
Данные функции возвращают SQLITE_OK = 0 при успешном открытии базы данных. Иначе возвращается код ошибки. Используя функции sqlite3_errmsg() или sqlite3_errmsg16() можно получить описание ошибки.
// возвращает сообщение об ошибке в кодировке UTF-8 const char * sqlite3_errmsg( sqlite3 * db // описатель БД ); // возвращает сообщение об ошибке в кодировке UTF-16 const void * sqlite3_errmsg16( sqlite3 * db // описатель БД ); // возвращает код ошибки int sqlite3_errcode( sqlite3 * db // описатель БД );
Закрытие базы данных
После выполнения всех необходимых действий над базой данных ее необходимо закрыть.
int sqlite3_close( sqlite3 * db // описатель базы данных );
Данная функция в качестве параметра принимает описатель открытой базы данных. Если функция закрытия базы данных будет вызвана во время выполнения транзакции, то она потерпит неудачу. Функция sqlite3_close() возвращает SQLITE_OK при успешном своем выполнении иначе код ошибки. Также данную функцию закрытия базы данных следует вызвать, даже если функция открытия базы данных в ходе выполнения потерпит неудачу.
Выполнение SQL запросов
Рассмотрим доступ к базе данных с помощью функций sqlite3_exec().
int sqlite3_exec ( // описатель базы данных полученный из функции // sqlite_open() sqlite * db, // строка SQL запроса, возможно определить // несколько запросов в одной строке const char * sql, // указатель на функцию повторного вызова // данная функция будет вызвана для каждой // строки результата выполнения запроса SELECT // возможно указать пустое значение NULL int (*xCallback) (void*, int, char **, char **), // значение переданное функции повторного // вызова в качестве ее первого аргумента void * pArg, // строка сообщения об ошибке char ** errmsg );
С помощью данной функции, возможно, выполнить одну или несколько SQL инструкций. Инструкции SQL определяются вторым параметром функции - sql, если определяются несколько SQL запросов через точку запятую, они выполняются последовательно.
Третьим параметром функции sqlite_exec() служит функция повторного вызова Callback().
int Callback(void * pArg, int argc, char ** argv, char ** columnNames)
Данная функция повторного вызова служит для получения и обработки данных из результата выполнения запроса SELECT. Функция Callback вызывается для каждой строки результирующей таблицы.
Первый параметр функции - pArg служит для передачи произвольной информации в функцию повторного вызова из функции sqlite_exec() используя ее четвертый параметр.
Второй параметр функции - argc число столбцов в исходе запроса.
Третий параметр функции - argv массив строк, где каждая строка одиночный столбец результата выполнения запроса выборки данных их таблицы (SELECT).
Четвертый параметр функции - columnNames массив из argc элементов, каждый элемент массива содержит название столбца результирующей таблицы. Если установить значение прагмы SHOW_DATATYPE в значение ON, данный параметр будет содержать дополнительно имя типа столбца (по умолчанию значение прагмы равно OFF).
По умолчанию функция повторного вызова не будет вызвана, если результат выполнения SQL запроса SELECT пуст. Если прагма EMPTY_RESULT_CALLBACK установлена в значение ON, функция повторного вызова запустится только однажды для получения названий столбцов.
Функцию повторного вызова в sqlite_exec() можно установить в значение NULL. В этом случае никакая функция повторного вызова не будет вызвана. Это полезно при выполнении инструкций SQL не возвращающих данные, например инструкции вставки (INSERT), модификации (UPDATE) и удаления (DELETE) данных.
Функция повторного вызова должна возвращать нулевое значение. Если функция 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().
Пример программы.
#include <stdio.h> #include <stdlib.h> #include "sqlite3.h" /* функция повторного вызова здесь происходит обработка результата SQL запроса Эта функция будет вызвана для каждой строки результата выполняемого запроса. */ static int callback(void *notused, int coln, char **rows, char **colnm) { /* coln - число столбцов в запрашиваемой таблице rows - массив значений записи colnm - имена столбцов */ int i; static int b = 1; // печать названий столбцов if (b) // печатать только один раз { for(i=0; i<coln; i++) printf("%s\t", colnm[i]); printf("\n"); b = 0; } // разделитель строк for(i=0; i<coln; i++) printf("-------\t"); printf("\n"); //печать значений for(i=0; i<coln; i++) printf("%s\t|", rows[i]); printf("\n"); return 0; } // end callback void main() { int rc, i; sqlite3 *db; // указатель на открытую базу данных char * errmsg; // сообщение об ошибке // SQL инструкций char *sql[] = { "CREATE TABLE tbl1 (N INTEGER PRIMARY KEY AUTOINCREMENT, nm CHAR(20), score INT);", "INSERT INTO tbl1 VALUES(NULL, 'Ben Frees', 2478);", "INSERT INTO tbl1 VALUES(NULL, 'Roy Askey', 2289);", "INSERT INTO tbl1 VALUES(NULL, 'Lary Terner', 2021);", "INSERT INTO tbl1 VALUES(NULL, 'Jonny Hitlop', 1471);", "INSERT INTO tbl1 VALUES(NULL, 'Zoran Covoc', 1143);", "SELECT N AS 'NUMBER', nm AS 'NAME', score AS 'SCORE' FROM tbl1;" "DROP TABLE tbl1;" "VACUUM;" }; // создадим новую базу данных rc = sqlite3_open("exam2.db", &db); if (rc) { //если ошибка при открытии БД errmsg = (char*) sqlite3_errmsg(db); printf("%s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(db); exit(1); } // выполнение SQL инструкций for (i=0; i<sizeof(sql)/sizeof(sql[0]); i++) { rc = sqlite3_exec(db, sql[i], callback, NULL, &errmsg); if (rc != SQLITE_OK) { //если ошибка при выполнении запроса printf("%s\n", errmsg); sqlite3_free(errmsg); sqlite3_close(db); exit(1); } } //закрытие БД sqlite3_close(db); system("pause"); }
Начиная с 3.0 и выше предпочтительнее использовать иной способ выполнения запросов к базе данных, без определения функции повторного вызова.
Выполнение SQL запроса состоит из следующих шагов:
Шаг 1. Подготовка SQL инструкции к выполнению
Прежде чем выполнить SQL запрос, его необходимо подготовить к выполнению, откомпилировав в байт-код, используя одну из следующих функций:
int sqlite3_prepare ( sqlite3 *db, // описатель БД const char *zSql, // инструкция SQL в кодировке // UTF-8 int nBytes, // длина строки zSql sqlite3_stmt **ppStmt // возвращает указатель на // закрытую структуру const char **pzTail // возвращает строку с // неиспользованной SQL // инструкцией ); int sqlite3_prepare16 ( sqlite3 *db, // описатель БД const void *zSql, // инструкция SQL в кодировке // UTF-16 int nBytes, // длина строки zSql sqlite3_stmt **ppStmt // возвращает указатель на // закрытую структуру const void **pzTail // возвращает строку с // неиспользованной SQL // инструкцией );
Данные функции принимают параметр zSql строку с SQL запросом. Третий параметр функции определяет размер строки, если он равен отрицательному числу, то принимается вся строка, определяемая в zSql, в противном случае конкретно установленное число символов строки.
Функция возвращает указатель ppStmt на закрытую структуру, которая используется для представления откомпилированной SQL инструкции.
typedef struct sqlite3_stmt sqlite3_stmt;
Функция также может вернуть строку pzTail, содержащую часть неиспользованного SQL запроса определенного параметром zSql. В строке zSql возможно определить несколько запросов SQL, однако выполнена будет только одна SQL инструкция - первая, остальные будут записаны в pzTail.
При успешном компилировании SQL запроса функция вернет SQLITE_OK, иначе код ошибки.
Шаг 2. Выполнение SQL инструкции
После успешной компиляции SQL инструкции ее можно выполнить с помощью одного или нескольких вызовов функции:
int sqlite3_step (sqlite3_stmt *pStmt);
Данная функция в ходе выполнения SQL инструкции может вернуть одно из следующих значений, которые определяют ход выполнения запроса:
- SQLITE_BUSY - Попытка выполнить запрос к базе данных заблокированной другим процессом.
- SQLITE_DONE - инструкция SQL успешно выполнилась
- SQLITE_ROW - возвращается если имеется строка данных из результата выполнения запроса. Для получения данных используются функции типа sqlite3_column_*()
- SQLITE_ERROR - если в ходе выполнения запроса произошла ошибка. Описание ошибки можно получить из функции sqlite3_errmsg().
- SQLITE_MISUSE - выполнение запроса не уместно. Возможно попытка выполнить запрос после получения кода ошибки SQLITE_ERROR, или после успешного выполнения запроса и получение кода SQLITE_DONE, или после завершения выполнения SQL запроса функцией sqlite3_finalize().
Шаг 3. Получение данных из результата запроса
Перед получением данных необходимо определить число столбцов в результате выполнения запроса. Используя следующие функции:
int sqlite3_column_count (sqlite3_stmt * pStmt); int sqlite3_data_count(sqlite3_stmt * pStmt);
Первая функция позволяет получить число столбцов сразу после успешной компиляции SQL инструкции, то есть после выполнения функции sqlite3_prepare(). Данная функция вернет нулевое значение, если инструкция 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.
Для получения результирующего типа столбца (с учетом операций) можно воспользоваться функцией:
int sqlite3_column_type(sqlite3_stmt *, int iCol);
Функция возвращает одно из следующих значений определяющих тип:
SQLITE_INTEGER 1 SQLITE_FLOAT 2 SQLITE_TEXT 3 SQLITE_BLOB 4 SQLITE_NULL 5
Для определения типа столбца объявленного при создании таблицы в предложении CREATE можно воспользоваться следующей функцией:
const char * sqlite3_column_decltype(sqlite3_stmt *, int iCol);
Для получения заголовков столбцов используются следующие функции:
const char * sqlite3_column_name(sqlite3_stmt *, int iCol); const void *sqlite3_column_name16(sqlite3_stmt *, int iCol);
Шаг 4. Завершение выполнения запроса
После завершения выполнения SQL инструкции необходимо освободить виртуальную машину, выполнив следующую функцию:
int sqlite3_finalize(sqlite3_stmt * pStmt);
При успешном выполнении возвратит SQLITE_OK.
Если требуется выполнить откомпилированную инструкцию SQL повторно, то необходимо предварительно сбросить виртуальную машину в исходное состояние функцией:
int sqlite3_reset (sqlite3_stmt * pStmt);
Рассмотрим данный способ выполнения запроса на примере:
#include <stdio.h> #include <stdlib.h> #include "sqlite3.h" void main() { sqlite3 *db; sqlite3_stmt * pStmt; int i, j, coln, rc; int b = 1; // SQL инструкций char *sql[] = { "CREATE TABLE tbl1 (N INTEGER PRIMARY KEY AUTOINCREMENT, nm CHAR(20), score INT);", "INSERT INTO tbl1 VALUES(NULL, 'Ben Frees', 2478);", "INSERT INTO tbl1 VALUES(NULL, 'Roy Askey', 2289);", "INSERT INTO tbl1 VALUES(NULL, 'Lary Terner', 2021);", "INSERT INTO tbl1 VALUES(NULL, 'Jonny Hitlop', 1471);", "INSERT INTO tbl1 VALUES(NULL, 'Zoran Covoc', 1143);", "SELECT N AS 'NUMBER', nm AS 'NAME', score AS 'SCORE' FROM tbl1;" "DROP TABLE tb1;" "VACUUM;" }; // открытие (создание) БД if (sqlite3_open("exam3.db", &db)) { printf("Error: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); system("pause"); exit(1); } // выполнение SQL инструкций (каждой по порядку) for (i=0; i<sizeof(sql)/sizeof(sql[0]); i++) { // шаг 1 подготовка SQL инструкции к выполнению //(компиляция ее в байт-код) if (sqlite3_prepare(db, sql[i], -1, &pStmt, NULL)) { printf("Error: %s\n", sqlite3_errmsg(db)); sqlite3_finalize(pStmt); sqlite3_close(db); system("pause"); exit(1); } // получения количества столбцов coln = sqlite3_column_count(pStmt); // печать названий столбцов только 1 раз if (b & coln) { for(j=0; j<coln; j++) printf("%s\t", sqlite3_column_name(pStmt, j)); printf("\n"); b = 0; } // шаг 2 выполнение SQL инструкций while((rc = sqlite3_step(pStmt)) == SQLITE_ROW) { // тело цилка выполнится только для //инструкции SELECT // для остальных запросов функция //sqlite3_step вернет SQLITE_DONE // получить число столбцов в текущей строке //результата coln = sqlite3_data_count(pStmt); // шаг 3 получение данных for(j=0; j<coln; j++) // получение данных как текстовые строки // вне зависимости от объявленного типа // столбца printf("%s\t",sqlite3_column_text(pStmt, j)); printf("\n"); } // получение сообщения при ошибке if (rc != SQLITE_DONE) printf("Error: %s\n", sqlite3_errmsg(db)); // шаг 4 завершение выполнения запроса sqlite3_finalize(pStmt); } // закрытие БД sqlite3_close(db); system("pause"); } //end main
Рассмотрены основные способы доступа к базе данных из ваших программ, библиотека функций предоставляет еще ряд полезных процедур, для работы с базой данных за их описанием обратитесь на сайт www.sqlite.org.
Оставить комментарий
Комментарии
Двигатель у автомобиля. А у программы движок.
Столкнулся с такой проблемой: при выполнении команды lib /def:sqlite3.def выскакивает окно ошибки, что lib не является приложением win32, а в самой cmd "Отказано в доступе".
Не сталкивались с таким?
Заранее спасибо