admin / 18.01.2018

Создание хранимой процедуры и SQLite? stored-procedures | CODE Q&A [русский]

Использование SQLite в C#: Часть 3 – Основные сценарии работы

сб, 2014-05-31 22:10 | SergeChel
Войдите или зарегистрируйтесь, чтобы получить возможность отправлять комментарии  |  Tags: ADO.NET, C Sharp, C#, Net Framework, SQL, SQLite, базы данных, программирование, статьи
Приведенные здесь примеры программного кода не претендуют на идеальность и призваны продемонстрировать основные принципы практического использования SQLite.

Если вы уже имеете опыт работы с классами ADO.NET для MS SQL или Oracle, то освоение методики работы с классами для SQLite не вызовет у вас каких-либо затруднений. Для работы с файлом — источником данных используется класс SQLiteConnection, для работы с SQL-запросами используется класс SQLiteCommand, для получения и обработки результатов выполнения SQL-запросов используется класс SQLiteDataReader, либо SQLiteDataAdapter в связке с универсальными классами DataSet, DataTable и т.п.

Простейший сценарий работы с объектом класса SQLiteConnection выглядит следующим образом:
SQLiteConnection conn = new SQLiteConnection(«Data Source=filename.db; Version=3;»); try { conn.Open(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); } if (conn.State == ConnectionState.Open) { // ****** } conn.Dispose();
можно также использовать следующую конструкцию:
using (SQLiteConnection conn = new SQLiteConnection(«Data Source=filename.db; Version=3;»)) { // ****** }
Если указанный в строке соединения файл не существует, то по умолчанию создается новый файл с указанным именем.
При завершении работы с объектом класса SQLiteConnection крайне желательно вызывать метод Dispose() (или использовать конструкцию using), поскольку при выполнении этого метода происходит закрытие соединения и снятие блокировки с файла данных. Если требуется закрыть соединение без уничтожения объекта, то можно использовать метод Close().
Работу с объектами класса SQLiteCommand наглядно рассмотрим на примере создания таблицы и заполнения ее тестовыми данными:
SQLiteCommand cmd = conn.CreateCommand(); string sql_command = «DROP TABLE IF EXISTS person;» + «CREATE TABLE person(» + «id INTEGER PRIMARY KEY AUTOINCREMENT, » + «first_name TEXT, » + «last_name TEXT, » + «sex INTEGER, » + «birth_date INTEGER);» + «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (‘John’, ‘Doe’, 0, strftime(‘%s’, ‘1979-12-10’));» + «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (‘Vanessa’, ‘Maison’, 1, strftime(‘%s’, ‘1977-12-10’));» + «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (‘Ivan’, ‘Vasiliev’, 0, strftime(‘%s’, ‘1987-01-06’));» + «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (‘Kevin’, ‘Drago’, 0, strftime(‘%s’, ‘1991-06-11’));» + «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (‘Angel’, ‘Vasco’, 1, strftime(‘%s’, ‘1987-10-09’));»; cmd.CommandText = sql_command; try { cmd.ExecuteNonQuery(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); }
В приведенном примере новый объект класса SQLiteCommand создается вызовом метода CreateCommand() ранее созданного объекта класса SQLiteConnection. В зависимости от сценария работы также можно использовать конструкции:
SQLiteCommand cmd = new SQLiteCommand(conn); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = conn;
Текст SQL-команды записывается в свойство CommandText. Строку с командой или содержащую ее переменную также можно передать прямо в в конструктор, например:
SQLiteCommand cmd = new SQLiteCommand(«SELECT * FROM person»); SQLiteCommand cmd = new SQLiteCommand(sql_command, conn);
Выполнение SQL-команды, не предполагающей возвращения данных, выполняется вызовом метода ExecuteNonQuery().
Выполнение SQL-запроса, предполагающего возвращение единственного значения, выполняется вызовом метода ExecuteScalar(): cmd.CommandText = «SELECT count(id) FROM person»; object amount = 0; try { amount = cmd.ExecuteScalar(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); } Console.WriteLine(«Total count of records: » + amount.ToString());
Для выполнения SQL-команды, предполагающей возврат множества данных, используются два подхода, каждый из которых имеют как плюсы так и минусы. Первый – построчное считывание результатов выполнения команды с помощью объекта класса SQLiteDataReader. Реализация этого подхода требует большее, по сравнению со вторым, количество программного кода, однако дает разработчику несравнимо более богатый набор возможностей для контроля над процессом считывания и обработки данных из результата выполнения SQL-запроса.
cmd.CommandText = «SELECT id, first_name, last_name, » + «CASE WHEN sex = 0 THEN ‘male’ WHEN sex = 1 THEN ‘female’ END AS sex, » + «datetime(birth_date, ‘unixepoch’) AS birth_date FROM person»; try { SQLiteDataReader r = cmd.ExecuteReader(); string line = String.Empty; while (r.Read()) { line = r[«id»].ToString() + «, » + r[«first_name»] + «, » + r[«last_name»] + «, » + r[«sex»] + «, » + r[«birth_date»]; Console.WriteLine(line); } r.Close(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); }
В результате выполнения метода ExecuteReader() объекта класса SQLiteCommand создается объект класса SQLiteDataReader. Используя в цикле метод Read() этого объекта, мы последовательно считываем записи результата выполнения SQL-запроса. Для доступа к полям очередной считанной записи имеется целый ряд вариантов, в приведенном примере доступ производится по именам полей, например r[«id»]. Также возможны варианты c приведением данных к нужному типу, например GetString() или GetInt32(), однако в качестве параметра вызова они принимают только порядковый номер поля в записи с данными, что не всегда удобно. Можно также разом считать все поля в коллекцию NameValueCollection с помощью метода GetValues(). По окончании считывания данных вызывается метод Close().
Второй подход заключается в считывании данных в объект класса DataSet и последующую работу с ними, как с единой копией-слепком данных. Этот подход удобен для «ленивого» программирования — собирания программы из готовых «кубов» (это даже не кубики).
DataSet data = new DataSet(); data.Reset(); SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd); ad.Fill(data);
В приведенных ранее примерах для простоты изложения параметры SQL-команд передавались прямо в тексте команды, что не есть хорошо и безопасно. Корректный подход заключается в передаче параметров запроса в виде отдельного набора объектов класса SQLiteParameter. На примере выполнения команды добавления новых данных приведены несколько вариантов формирования таких параметров.
cmd.CommandText = «INSERT INTO person(first_name, last_name, sex, birth_date) » + «VALUES (@first_name, @last_name, @sex, strftime(‘%s’, @birth_date));»; cmd.Parameters.AddWithValue(«@first_name», «Sergey»); cmd.Parameters.Add(new SQLiteParameter(«@last_name», «Petrov»)); SQLiteParameter param = new SQLiteParameter(«@sex», DbType.Int32); param.Value = 0; cmd.Parameters.Add(param); cmd.Parameters.Add(«@birth_date», DbType.DateTime).Value = DateTime.Parse(«2000-01-15»); cmd.ExecuteNonQuery();
При программировании многоэтапных сценариев записи или обновления данных разумно использовать транзакции. Для их реализации используется класс SQLiteTransaction. Объект класса создается вызовом метода BeginTransaction () объекта класса SQLiteConnection:
SQLiteTransaction trans = conn.BeginTransaction();
Созданный объект связывается с нужным объектом класса SQLiteCommand:
В случае удачного завершения последовательности действий вызывается метод Commit() класса SQLiteTransaction, а в случае неудачи — метод Rollback().
Обратите внимание на то, как реализована работа с датами в приведенных примерах. Как ранее упоминалось, SQLite не имеет специального типа данных для хранения даты и времени. Наиболее распространенным способом хранения данных значений является целое число секунд, прошедших от точки отчета 1970-01-01 00:00:00 UTC. В приведенных примерах для преобразования даты в целое число используется функция strftime(‘%s’, value), а для обратного преобразования используется функция datetime(value, ‘unixepoch’). Также подобные преобразования можно выполнять в программном коде C#, однако это не очень удобно, поскольку в инструментарии языка нет функций для данных преобразований и все придется ваять самостоятельно.

Использование SQLite в C#

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

Другие материалы

SQLite¶

SQLite — компактная встраиваемая реляционная база данных. Исходный код библиотеки передан в общественное достояние. Является чисто реляционной базой данных.

Слово «встраиваемый» означает, что SQLite не использует парадигму клиент-сервер. Т.е. движок SQLite не является отдельно работающим процессом, с которым взаимодействует программа, а предоставляет библиотеку, с которой программа компонуется и движок становится составной частью программы.

Таким образом, в качестве протокола обмена используются вызовы функций (API) библиотеки SQLite. Такой подход уменьшает накладные расходы, время отклика и упрощает программу. SQLite хранит всю базу данных (включая определения, таблицы, индексы и данные) в единственном стандартном файле на том компьютере, на котором исполняется программа. Простота реализации достигается за счёт того, что перед началом исполнения транзакции записи весь файл, хранящий базу данных, блокируется; ACID [1]-функции достигаются в том числе за счёт создания файла журнала.

Несколько процессов или потоков могут одновременно без каких-либо проблем читать данные из одной базы. Запись в базу можно осуществить только в том случае, если никаких других запросов в данный момент не обслуживается; в противном случае попытка записи оканчивается неудачей, и в программу возвращается код ошибки. Другим вариантом развития событий является автоматическое повторение попыток записи в течение заданного интервала времени. Можно, также, ввести таймаут операций. Тогда подключение, столкнувшись с занятостью БД, будет ждать N секунду прежде, чем отвалиться с ошибкой SQLITE_BUSY.

Также с версии 3.7.0 присутствует режим WAL [2], с помощью которого можно испольовать одну и ту же базу несколькими приложениями, как на чтение, так и на запись.

В комплекте поставки идёт также функциональная клиентская часть в виде исполняемого файла sqlite3, с помощью которого демонстрируется реализация функций основной библиотеки.

Модуль sqlite — Работаем с базой данных

Клиентская часть работает из командной строки, позволяет обращаться к файлу БД на основе типовых функций ОС.

Благодаря архитектуре движка возможно использовать SQLite как на встраиваемых системах, так и на выделенных машинах с гигабайтными массивами данных.

Формат файла базы данных является кросс-платформеным, что позволяет без проблем использовать одну и ту же базу на нескольких операционных системах. Также присутствует возможность хранения базы в памяти, без её записи на диск. Этот вариант используется по умолчанию для консольной утилиты sqlite3, если не указано имя файла.

Поддержка стандарта SQL¶

Как известно, в своем развитии SQL устремился в разные стороны. Крупные производители начали впихивать всякие расширения. И хотя принимаются всякие стандарты, в реальной жизни все крупные БД не поддерживают стандартов полностью. Но зато имеют что-то свое.

Так вот, SQLite старается жить по принципу «минимальный, но полный набор». Она не поддерживает сложные штуки, но во многом соответствует SQL 92. И вводит некие свои особенности, которые очень удобны, но — не стандартны.

Неподдерживаются следующие возможности:

  • RIGHT и FULL OUTER JOIN. Реализован только LEFT OUTER JOIN.
  • Частично реализован ALTER TABLE. Доступны только RENAME TABLE и ADD COLUMN.
  • Частичная поддержка тригеров. Доступены только FOR EACH ROW тригеры.
  • Запись во VIEWS. В SQLite VIEWS доступны только на чтение. Частично обходится через тригеры.
  • В силу реализации базы данных, как единственного файла и отхода от концепции «клиент-сервер», не используются возможности GRANT и REVOKE.
  • По умолчанию отключены внешние ключи. Это сделано для обратной совместимости.

Типизация¶

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

Тип столбца определяет как сравнивать значения (нужно же их привести к единому типу при сравнении, скажем, внутри индекса). Но не обязывает заносить значения именно такого типа в столбец.

Допустим, мы объявили столбец как «A INTEGER». SQLite позволяет занести в этот столбец значения любого типа (999, «abc», «123», 678.525). Если вставляемое значение — не целое, то SQLite пытается привести его к целому. Т.е. строка «123» превратится в целое 123, а остальные значения запишутся «как есть».

Возможные типы полей: NULL, INTEGER, REAL, TEXT, BLOB.

Надёжность¶

Ситуация с покрытиями тестами исходного кода SQLite в некотором роде является легендой.

Это связно с тем, что тестами описано практически все возможные (и невозможные) ситуации. Кода, описывающего тесты, намного больше, чем кода реализующего SQLite. Естественно, что покрытие кода тестами 100%.

Сам же подход к тестированию использующийся разработчиками SQLite достоин подражания. И может быть взят как идеал, к которому следуют стремиться.

Практика¶

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

Для того чтобы запустить SQL-код необходимо выполнить следующую команду:

Далее откроется интерактивный SQLite-shell, куда можно вводить команды.

Создание структуры каталога¶

В этом примере мы рассмотрим как создать простую струкртуру каталога товаров.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
CREATETABLEcategory(nameTEXTNOTNULL);INSERTINTOcategory(name)VALUES('Тапки'),('Самолёты'),('Ноутбуки');CREATETABLEproduct(nameTEXTNOTNULL,priceNUMERICNOTNULL,categoryREFERENCEScategory(rowid));INSERTINTOproduct(name,price,category)VALUES('Босоножки',1.17,1),('Вьетнамки',2.36,1),('Макасины',4.99,1),('ИЛ-2',556000,2),('Суперджет 100',1500000,2),('Ту-160',25000000,2),('Dell',590,3),('Lenovo',200,3),('Sony',437,3);

Расширение структуры каталога¶

В этом примере мы рассмотрим, как расширить существующую структуру под новые требования.

Требования будут следующими:

  • Для категории «Тапки» необходимо добавить поле «Размер».
  • Для категории «Самолёты» необходимо добавить поле «Вместимость».
  • Для категории «Ноутбуки» необходимо добавить поле «Прцессор».
ALTERTABLEproductADDsizeINTEGER;ALTERTABLEproductADDcapacityINTEGER;ALTERTABLEproductADDprocessorTEXT;

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

Т.е., например, поле «размер», которое добавлено для категории «Тапки», становится обязательным и для товаров из категории «Самолёты» и «Ноутбуки».

Классическая реляционная модель не позволяет удобно применить отдельные поля для разных категорий. Можно, конечно, использовать поле типа BLOB и складывать туда всё, что душе вздумается, но тогда потеряются такие прелести реляционных СУБД, как индексы, тригеры (либо они очень усложнятся) и фильтрация в запросах.

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

Поэтому предлагаю оставить замечательную реляционную базу SQLite в покое. Она замечательно справляется с возложенными на неё обязанностями. Благодаря чему и является, пожалуй, самой популярной встраиваемой базой данных в мире.

Сейчас мы научимся работать с параметрами при создании запроса.

Руководство по SQLite: настраиваем и учимся работать

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

Python модуль sqlite3 поддерживает два типа заполнителей: знаки вопроса и именованные заполнители.

Мы обновляем цену одной машины. В этом примере кода, мы используем заполнители типа «знак вопроса».

Знаки вопроса ? являются заполнителями для значений. Значения добавляются на его место.

Свойство rowcount возвращает количество обновленных строк.

В нашем случае, одна строка была обновлена.

Такой подход программирования и создания запрос к базе данных увеличивает безопасность приложения. webmartsoft.ru/web-razrabotka.html — профессиональная веб разработка выполняет качественные и безопасные проекты которые позволяют своим клиентам не бояться за сохранность данных своего сайта.

Была обновлена цена машины.

Второй пример использует запрос с параметрами с именованными заполнителями.

Мы выбираем название и цену машины, используя именованные заполнители.

Именованные заполнители начинаются с символа двоеточия.

SQLite — это база данных, чем-то похожая на MySQL. Принципиальное отличие SQLite от других БД в том, что вся база представляет собой один файл. Если в MySQL база хранится где-то в дебрях сервера и недоступна для переноса, то в SQLite с этим всё до безобразия просто: один файл — одна база.

Конечно же, сервер должен поддерживать драйвер SQLite (также как и любой другой БД), но как правило сейчас с этим проблем нет.

SQLite позволяет привычно работать с базой через SQL, создавать таблицы, поля и т.д. В целом можно сказать, что SQLite ни в чем не уступает привычной MySQL, за исключением, пожалуй более медленной работы с «тяжелыми» sql-запросами по обновлению данных (insert и update). Но, опять же, это для высоконагруженных сайтов.

Огромным плюсом SQLite будет её легкая переносимость. Скопировать файл — что может быть проще? Не нужно заботиться о бэкапах, как в MySQL, не нужно создавать на сервере пользователя с паролем, не нужно создавать саму базу. С SQLite просто берём и пользуемся.

Для работы с базой данных в PHP лучше использовать PDO — Объекты данных PHP — это т.н. абстракция, которая предлагает единый интерфейс для работы с разными базами. В теории, используя PDO, можно переключиться на любую базу, не переделывая SQL-запросы, например с MySQL на SQLite. Меняются только параметры подключения.

Таким образом SQLite будет подключаться через PDO. Для этого ничего не требуется, поскольку сам PDO уже в составе PHP, а драйвер SQLite, как правило, также включен на серверах.

Но, прежде, чем приступать к программированию, нужно создать саму базу. Например для MySQL существует phpMyAdmin, через который можно выполнять различные операции. Для SQLite также есть похожие разработки, но я покажу, как можно это делать через браузер FireFox. Для этого потребуется только установить дополнение SQLite Manager.

Чтобы добавить это дополнение в основное меню FireFox («гамбургер»), нажмите Изменить и перетащите мышью икноку в меню.

На этом SQLite Manager установлен и можно им пользоваться.

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

SQLite + C#

В результате будет создан sqlite-файл с новой базой.

Этот файл можно перемещать (и переименовывать) куда угодно, а после открывать командой меню Базы данных — Подключить базу данных.

Теперь нужно создать таблицу (или таблицы) в базе данных.

SQLite Manager автоматом создаёт служебные таблицы sqlite_XXX. Мы их не трогаем и нам они не мешают.

Таблица в БД — это то место, где хранится структурированная информация. Таблица должна иметь набор полей с заданными свойствами. Например поле может быть integer — для целых чисел, или text — для текста. Количество полей может быть произвольным и определяется только задачей вебмастера.

Пусть, например, у нас будет таблица с полями

  • id — уникальный номер (автоинкремент)
  • slug — ссылка
  • text — произвольный текст
  • hits — число просмотров

После того, как таблица создана, обратите внимание на блок «SQL-оператор создавший этот объект». В нем будет SQL-запрос, которым можно создать таблицу. Он может пригодится, если требуется создать таблицу в базе через PHP.

На вкладке «Просмотр и Поиск» можно редактировать таблицу. Создадим для примера две строчки, где поле slug будет и . Это будут две страницы: главная и сайт/contact.

Поле hits будет содержать счетчик просмотров страницы. Текст может быть любым.

Всё, база готова, теперь можно её использовать.

Поставим задачу. Пусть у нас будет простенький сайт, который будет выдавать по короткой ссылке (slug) соответствующий текст и количество просмотров.

Если мы делаем это на локальном сервере, то пусть сайт будет в каталоге sqlite. В нём подкаталог db, куда мы и скопируем наш pages.sqlite.

Роутинг мы можем сделать, как описано в предыдущей статье PHP-роутинг (Routing) для новичков. Файл

AddDefaultCharset UTF-8 Options -Indexes <IfModule mod_rewrite.c> RewriteEngine on RewriteBase /sqlite/ RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule (.*) /sqlite/index.php?$1 [QSA,L] </IfModule>

В index.php сам роутинг будет описан одной строчкой:

$page = ($p = key($_GET)) ? $p : ‘home’;

То есть будет содержать ссылку или для главной.

Дальше алгоритм будет такой:

  • подключаем базу
  • делаем в ней выборку по
  • выводим полученные данные

Я намеренно упрощаю алгоритм, чтобы не усложнять php-код.

Существуют два варианта работы с БД. Первый — это нативный php-код. Он не очень сложный, но обилие параметров немного напрягает. Поэтому второй вариант — использование дополнительных библиотек-оберток. С ними код становится лаконичней.

Приведу код в первом варианте:

<?php $page = ($p = key($_GET)) ? $p : ‘home’; try { $pdo = new PDO(‘sqlite:db/pages.sqlite’); $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $sql =’SELECT * FROM pages WHERE slug=:page LIMIT 1′; $sth = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)); $sth->execute(array(‘:page’ => $page)); $rows = $sth->fetchAll(); print_r($rows); // здесь выводим данные } catch(Exception $e) { echo $e->getMessage(); } # end of file

Для второго варианта я использовал php-библиотеку с сайта labaka.ru, которую разместил в подкаталог lib.

Код :

<?php $page = ($p = key($_GET)) ? $p : ‘home’; require ‘lib/sqlite.php’; try { $db = new Db(‘db/pages.sqlite’); $row = $db->queryRow(‘SELECT * FROM pages WHERE slug=:page LIMIT 1’, array(‘:page’ => $page)); if ($row) { echo $row[’text’]; echo ‘<br>Просмотров: ‘ . $row[’hits’] . ‘<br>’; $db->update(‘pages’, array(‘hits’ => $row[’hits’] + 1), ‘id=:id’, array(‘:id’ => $row[’id’])); } } catch(Exception $e) { echo $e->getMessage(); } # end of file

Данный пример будет выводить для разных адресов разный текст и при этом будет работать счетчик просмотров. Если требуется добавить новую страницу, то её достаточно создать в базе. Для удобства, в SQLite Manager можно подключить базу прямо из каталога сайта.

Здесь следует отметить пару важных моментов.

В первую очередь, вся работа с PDO должна заключаться в блок . Таким способом отлавливаются сообщения об ошибках.

Данные, которые отправляются в sql-запрос должны проходить через валидацию. В PDO, когда используется предподготовка данных (PDO::prepare), выполняется принудительное экранирование параметров. Это позволяет защититься от возможных SQL-инъекций.

Когда происходит подключение базы данных SQLite, в случае отсутствия файла базы, он будет создан автоматически. На этом базируется создание базы сразу после его подключения и создание нужных таблиц первым sql-запросом (о котором я написал выше).

Еще одно замечание по SQLite. Поскольку база это файл, то его можно скачать по URL прямо через браузер. Поэтому каталог с SQLite-файлами лучше защищать через строчкой . Или же размещать выше чем основной www-каталог.

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

Вы можете скачать файлы к этой статье.

FILED UNDER : IT

admin

Submit a Comment

Must be required * marked fields.

:*
:*