Достаточно много лет назад, если память не изменяет, году этак в 2002 писал небольшую программку, которая хранила выходные данные по спец.литературе. Что-то наподобие каталогизатора, чтоб не забыть откуда чего брал, кто автор и т.д. В общем где-то до сих пор валяется программка с базой литературы по промышленной экологии для написания курсовиков. Как жаль, что в то время мне не было ничего известно про SQLite. Пришлось «лепить» базу из того, что было, а точнее из того, что более менее знал на тот момент — использовать компоненты dbGo, которые тогда вроде бы располагались на вкладке «ADO». Получилось жутенько, но программка работала. А ведь можно было бы сделать все намного проще.
Если Вам необходимо написать в Delphi 2010 что-то, наподобие моего каталогизатора, т.е. небольшую программу по хранению чего-либо в базе данных и при этом ожидается, что БД будет не большой, то воспользуйтесь SQLite, ну, а как это сделать, я попробую рассказать Вам в этой статье.
1. Как всё устроено в SQLite
Как сказано на официальном сайте, SQLite является встраиваемой библиотекой. То есть по сути Вам ничего не требуется настраивать, только положить библиотеку рядом с программой и начать работу. Вся база данных храниться в одном файле на жестком диске — 1 файл = 1 база данных.
Разобраться с синтаксисом SQLite не составит труда, если Вы хотя бы раз использовали, например, БД MySQL.
Отсюда вытекают как минимум два преимущества SQLite по сравнению с другими реляционными БД:
- Простота настройки, а точнее её отсутствие. Нет никакой необходимости, что-либо настраивать на клиентской машине — скопировали файл с БД и программку с dll-кой, запустили и работайте. Не хотите копировать — не надо. Можно работать хоть с компакт-диска (естественно в режиме «только для чтения»)
- Простота использования.
Ну и помимо всего прочего SQLite бесплатна и имеет открытый исходный код — используйте где угодно, когда угодно и как угодно.
Кстати, SQLite используется в популярных программных продуктах. Кому интересно, можете взглянуть на список программ, использующих в работе SQLite.
Теперь, что касается работы с SQLite непосредственно в Delphi 2010. Я предлагаю Вам использовать небольшую обёртку для DLL — SQLiteWrapper в виде нескольких удобных, на мой взгляд, классов для работы с БД и её таблицами. Последнюю версию обёртки, а также и библиотеки SQLite3.dll можно скачать здесь.
Скачивайте и приступим непосредственно к работе с базой данных.
2. Работа с SQLite. Используем SQLiteWrapper
Продемонстрируем работу с SQLite следующим примером — напишем небольшую программу, которая будет хранить в БД следующие данные:
- Изображение — BLOB-поле
- Название изображения — поле TEXT
- Оценку изображения (рейтинг) от 0 до 5 — поле INTEGER;
Таким образом мы продемонструрем работы практически со всеми типами данных SQLite, которых, кстати сказать, не так и много.
Итак, запускаем Delphi 2010, создаем новое приложение и подключаем в uses главного модуля риложения следующие модули:
uses ..., SQLite3, SQLiteTable3,...;
;
На форме размеситим следующие компоненты, как показано на рисунке:
В ListBox будем показывать все названия изображений из БД, с помощью RadioGroup — изменять рейтинг картинки, которая в данный момент загружена в TImage.
Теперь приступим к программированию. И первое, что нам необходимо — это создать новую базу данных или, если база уже имеется, то подключиться к ней. С использованием SQLiteWrapper это можно сделать следующим образом:
var Form8: TForm8; Pictures : TSQLiteDatabase; //база данных SQLite implementation {$R *.dfm} procedure TForm8.FormCreate(Sender: TObject); begin Pictures:=TSQLiteDatabase.Create('pictures.sqlite');//указываем файл БД end;
Теперь допишем обработчик OnCreate так, чтобы после подключения проверялось имеется ли в БД таблица, содержащая сведения об изображениях и, если таковой нет, то создавалась бы новая:
procedure TForm8.FormCreate(Sender: TObject); var s:string; begin Pictures:=TSQLiteDatabase.Create('pictures.sqlite');//указываем файл БД try if not Pictures.TableExists('pics') then //таблица в БД отсутствует - создаем begin s:='CREATE TABLE pics '; s:=s+'(id INTEGER PRIMARY KEY AUTOINCREMENT,'; s:=s+'pict_name TEXT,'; s:=s+'pict_data BLOB,'; s:=s+'rating INTEGER)'; Pictures.ExecSQL(s); end; except MessageBox(0,'Во время создания таблицы произошла ошибка','Ошибка',MB_OK+MB_ICONERROR); Application.Terminate; end; end;
Здесь я использовал локальную переменную s для записи текста запроса только для того, чтобы немного повысить читабельность кода процедуры. Итак, база есть, таблица создана. Начнем записывать в ней данные. Чтобы сильно не нагружать пример лишними проверками и вопросами поступим так: пусть в качестве названия картинки будет выступать название файла, а начальный рейтинг будет всегда равен нулю.
Записываем картинку в базу SQLite:
procedure TForm8.Button1Click(Sender: TObject); var s:string; F: TFileStream; id:int64; begin if OpenPictureDialog1.Execute then begin try F:=TFileStream.Create(OpenPictureDialog1.FileName,fmOpenRead); //добавляем в базу данных новую запись s:='INSERT INTO pics '; s:=s+'(pict_name, rating) '; s:=s+'VALUES ("'+ExtractFileName(OpenPictureDialog1.FileName)+'", 0)'; Pictures.ExecSQL(s); //получаем данные по последней измененной записи id:=Pictures.GetLastInsertRowID; //обновляем запись, заполнив поле pict_data s:='UPDATE pics SET pict_data = ? WHERE id = '+intToStr(id); Pictures.UpdateBlob(s,F); finally FreeAndNil(F); end; end; end;
Здесь мы поступаем следующим образом: вначале записываем в БД данные по названию картинки и её начальный рейтинг и только затем обновляем запись, добавляя в нее данные картинки. При этом мы используем два метода объека TSQLiteDatabase:
GetLastInsertRowID — возвращает нам идентификатор последней добавленной записи. По сути — это значение автоинкрементного поля id.
UpdateBlob — обновляет BLOB-поле. При этом в качестве параметров необходимо указать текст запроса, где вместо значения поля стоит символ «?» и второй параметр — поток с добавляемыми данными.
Теперь, для дальнейшей демонстрации работы с SQLite, напишем ещё один метод, который будет считывать из таблица названия всех добавленных изображений и выводить их в ListBox:
procedure TForm8.GenerateList; var SQLite_table: TSQLiteTable; i:integer; begin ListBox1.Clear; ListBox1.Items.BeginUpdate; SQLite_table:=Pictures.GetTable('SELECT pict_name FROM pics');//TSQLiteTable.Create(Pictures,'SELECT pict_name FROM pics'); for I := 0 to SQLite_table.Count - 1 do begin ListBox1.Items.Add(SQLite_table.FieldAsString(0)); SQLite_table.Next; end; ListBox1.Items.EndUpdate; SQLite_table.Destroy; end;
Здесь мы воспользовались новым типом данных — TSQLiteTable, который, как следует из названия, представляет собой отдельную таблицу базы данных. При создании объекта-таблицы нам необходимо указать саму БД, а также вторым параметром запрос которым мы выбираем данные из базы. В приведенном выше примере таблица состоит всего из одного столбца — названий картинок.
Используя методы TSQLiteDatabase, точно такуюже таблицу можно было бы получить так:
SQLite_table:=Pictures.GetTable('SELECT pict_name FROM pics');
Теперь, для полноты всей картины, осталось только показать картинку в TImage и научиться обновлять рейтинг. Показываем выбранную в ListBox картинку в TImage:
procedure TForm8.ListBox1Click(Sender: TObject); var M:TStream; Graph: TBitmap; SQLite_table: TSQLiteTable; begin if ListBox1.ItemIndex>-1 then begin try SQLite_table:=TSQLiteTable.Create(Pictures, 'SELECT pict_data, rating FROM pics WHERE pict_name="'+ ListBox1.Items[ListBox1.itemindex]+'"'); RadioGroup1.ItemIndex:=SQLite_table.FieldAsInteger(1);//рейтинг //загружаем картинку M:=TMemoryStream.Create; M:=SQLite_table.FieldAsBlob(0); M.Position:=0; Graph:=TBitmap.Create; Graph.LoadFromStream(SQLite_table.FieldAsBlob(0)); Image1.Picture.Bitmap.Assign(Graph) finally SQLite_table.Destroy; FreeAndNil(Graph); end; end; end;
Здесь обратите внимание на то, как идёт обращение к полям таблицы. К каждому из полей мы обращаемся по индексу. Так 0 в нашем случае соответствует полю pict_data, а 1 — рейтингу. С любым полем таблицы мы можем выполнить следующие действия:
function FieldAsInteger(I: cardinal): int64;//прочитать как Integer function FieldAsBlob(I: cardinal): TMemoryStream; //прочитать BLOB и сохранить в поток function FieldAsBlobText(I: cardinal): string; //прочитать BLOB и представить в виде строки символов function FieldIsNull(I: cardinal): boolean; //проверить на пустоту function FieldAsString(I: cardinal): string; //представить в виде строки function FieldAsDouble(I: cardinal): double;//представить в виде числа с плавающей точкой
Теперь осталась последняя операция — обновление рейтинга. Вот метод, который этим занимается:
procedure TForm8.SetRating; var Query: string; begin if ListBox1.ItemIndex>-1 then begin Query:='UPDATE pics SET rating = '+IntToStr(RadioGroup1.ItemIndex)+ ' WHERE pict_name="'+ListBox1.Items[ListBox1.ItemIndex]+'"'; Pictures.ExecSQL(Query); end; end;
На этом пример работы с SQLite в Delphi 2010 можно считать законченым. Что мы узнали:
1. Как создавать БД SQLite в Delphi
2. Как создавать таблицы и записывать в них данные
3. Как работать с BLOB в SQLite и Delphi
4. Как редактировать данные в таблицах.
В статье показан лишь небольшой пример того как осуществляется работа с SQLite в Delphi 2010 с использованием SQLiteWrapper и многие важные моменты по работе как с базами данных в целом, так и с SQLite в частности не были упомянуты и в примере приложения не показаны, т.к. это не входило в планы написания этой статьи. Ну, а для того, чтобы научиться разрабатывать БД и приложения, работающие с ними придётся изучить не одну сотню страниц полезной информации :)
Остается только добавить, что исчерпывающую информацию по SQLite вы всегда можете найти на официальном сайте.
Книжная полка
Описание Книга основана на материалах лекций и практических занятий, разработанных автором, и объединяет теоретические основы и практические аспекты разработки реляционных баз данных.
|
Купить на ЛитРес | 383 руб. | |
Автор: Анатолий Хомоненко, Владимир Гофман Название:Работа с базами данных в Delphi Описание: Рассматривается использование средств Delphi для разработки приложений баз данных. Даются понятия баз данных, характеризуются элементы и описываются этапы проектирования реляционных баз данных, изложена технология разработки информационных систем, освещаются приемы работы с данными, создание таблиц и приложений баз данных, подготовка отчетов. |
Купить на ЛитРес | 151 руб. |
Единственное что меня останавливает от перевода проектов на SQlite это отсутсвие паролирования БД.
Или я что-то не знаю?
Можно ли базу SQLite запаролить аналогично MS Access?
2Павел, прошелся по оф.сайту SQLite вроде бы ничего про паролирование не сказано. Хоят мож плохо искал…
Vlad, тексты SQL запросов (переменная S) можно было бы создавать и использованием Format(), это было бы намного читабильнее. И еще: в SQLiteWrapper от автора Synapse поддерживаются параметры при создании запросов.
А я почему-то наоборот не стал Format() использовать, думал наоборот нечитабельно будет :) Ладно, на будущее учту
2Павел. Вы можете написать свои функции чтения/записи файла с БД, и в них использовать любое шифрование. Смотрите http://sqlite.org/c3ref/vfs.html
Вот и я прошелся и не нашел)
Нашел только коммерческие компоненты для шифрации БД
2 Павел: существует Synopse SQLite3 Framework (http://blog.synopse.info/category/Open-Source-Projects/SQLite3-Framework), так вроде в нем можно шифровать/ограничивать доступ не только к таблицам, но и отдельным строкам таблицы. Но без 100 грамм в этом фреймворке не разобраться…
Чтобы шифрование БД стало доступным можно стандартную sqlite3.dll заменить на dll сторонних разработчиков.
Я использую wxsqlite3 http://wxwidgets.org/downloads/. Так в скачанном архиме в папочке Secure (хотя в названии папки могу ошибиться).
К dll-ке цепляюсь через компоненты, работал через Zeos в Delphi 7, сейчас RemObjects AnyDAC — Delphi 2010. А в качестве менеджера БД использую SQLite Maestro (который с версии 9.10 поддерживает также работу с шифрованными БД).
Немного наврал с адресом wxSQLite3. Реальный адрес — http://wxcode.sourceforge.net/components/wxsqlite3/
По поводу шифрования
я задвал этот вопрос смотри здесь
http://sql.ru/forum/actualthread.aspx?tid=638226
Решил добавить свой комментарий к вашей статье. Коротко о себе :) — у меня тоже есть своя реализация sqlite (чтобы текст не копировать посмотрите здесь http://delphikingdom.com/asp/answer.asp?IDAnswer=77975 ). С паролированием там действительно засада. Функции предусмотрены, но не реализованы (по крайне мере в публичной сборке). Если задаться целью, то где-то на sourceforce встречал реализации с паролями. Паролится sqlite не построчно а постранично. С sqlite ещё одна проблема — регистрозависимость… Разрулить её можно, но сложно: 1. при создании таблицы у текстовых полей писать «collate NOCASE», а в программе через sqlite3_create_collation реализовать свою функцию сравнения строк. 2. через sqlite3_create_function реализовать функции upper, lower, возможно… Подробнее »
Ещё в одном блоге про регистр http://begemotov.net/wxwidgets/using/poisk-v-sqlite-bez-ucheta-registra-dlya-russkogo-yazyika/
Демка не компилитруется, поскольку проект ссылается на отсутствующий файл Database.pas
О сорь, этот модуль мой — его можно безопасно удалить из uses и всё заработает
Заработало. Спасибо за оперативный ответ.
Пока ехал домой прокручивал в голове работу этой уникодной dll… и нашёл ошибку у себя: когда sqlite3_unicode.c файл просматривал, видел, что функции sqlite3_unicode_load и sqlite3_unicode_free вызываются, но пока смотрел всё остальное, про них забыл. И вот только оторвавшись от компьютера осенило, что вызываются они при загрузке и выгрузке dll — http://www.mediafire.com/?c6lyrbqfw2gar44 .
Т.е. больше нет надобности их вызывать, остаётся только sqlite3_unicode_init(db); после db := sqlite3_open(…);
А как можно создать файл БД если его не существует?
уже разобрался:
const
SQLITE_OPEN_READONLY = 1; // Ok for sqlite3_open_v2()
SQLITE_OPEN_READWRITE = 2; // Ok for sqlite3_open_v2()
SQLITE_OPEN_CREATE = 4; // Ok for sqlite3_open_v2()
function SQLite3_Open_v2(filename: PAnsiChar; var db: TSQLiteDB; flags: integer; zVfs: PAnsiChar): integer; cdecl; external SQLiteDLL name ‘sqlite3_open_v2’;
procedure SQLite3_CreateDatabase(const DatabaseName: String);
var
h: TSQLiteDB; // pointer
begin
try
sqlite3_open_v2(PAnsiChar(DatabaseName), h, SQLITE_OPEN_CREATE, nil);
except
sqlite3_close(h);
raise;
end;
end;
А мне больше понравилась реализация Wrapper’а, сделанного на основе указанного вами, от авторов Synapse (http://www.ararat.cz/doku.php/en:sqlitewrap)
yartkin, да в принципе ко всему можно привыкнуть. Можно собрать подборку wrapper’ов для SQLite Delphi и вывесить в блоге — чтоб каждый выбирал по вкусу :)
[…] […]
Я прикрутил шифрование самостоятельно.
Поскольку удобно структуру таблицы описывать с помощью соответствующего класса с методами Load, Save, Delete, то я сделал обертки на все свойства. в GetData я расшифровываю свойство из поля, в SetData — зашифровываю свойство в поле — а в БД я уже записываю поле, которое всегда зашифрованно, читаю тоже зашифрованное и расшифровываю по потребности. в итоге можно использовать любые алгоритмы шифрования — я взял anubis
здесь описан простой способ паролирования базы:
http://rgblog.ru/page/ispolzuem-sqlite-aes-256-v-delphi
нужно сделать: DB.ExecSQL(‘PRAGMA key = «MY PASSWORD»‘); при этом рядом с sqlite3.dll должна лежать libeay32.dll
[…] последней добавленной записи в БД. В обертке о которой я рассказывал, ещё работая в Delphi 2010, для этого случая был […]
зачем M:TStream в
procedure TForm8.ListBox1Click(Sender: TObject);
Ошибка у автора статьи. Должно быть:
// загружаем картинку
M := SQLite_table.FieldAsBlob(0);
M.Position := 0;
Graph := TBitmap.Create;
Graph.LoadFromStream(M);
Image1.Picture.Bitmap.Assign(Graph);
[…] больше года прошло с момента публикации статьи "SQLite в Delphi 2010." Надо сказать, что рассмотренный в статье SQLiteWrapper я […]
[…] что рассматривал либо бесплатные open source проекты типа вот этого, либо те возможности, которые уже имеются в самой Delphi, […]