php работа с базой данных sqlite
Изучаем PHP 7. №29. Работа с базой данных. SQLite
И первой базой, с которой мы поработаем, будет SQLite. Почему? Потому, что для нее не требуется сервер, все данные хранятся в одном файле, существует удобный плагин firefox для управления базой. Она как нельзя лучше подойдет для маленьких не нагруженных сайтов, визиток, блогов, лендингов.
Из минусов: отсутствие авторизации при подключении. То есть, если кто-то знает где лежит ваша база и она при этом открыта для полного доступа, то он может считать все данные с этой базы.
Но для лендингов и маленьких сайтов — это то, что нужно. Думаю, что вы оцените ее удобство и простоту.
Для начала напишем инсталятор базы и занесем несколько демо данных:
install.php
Так будет выглядеть результат:
Таблица пользователей создана
Демо данные внесены
Демо данные внесены
——————————————-
Результат
id: Имя: Andrey Возраст: 15 Город: OdessaРезультат
id: Имя: Vitaliy Возраст: 28 Город: Dnepr
index.php
func.php
А вот и результат: 
—————————
Металлически двери в деревянный дом следует устанавливать с учётом ряда особенностей. В районах с очень низкой температурой зимой правильная установка металлической двери в деревянный дом позволит избежать полного или частичного замерзания дверей.
2 идей о “ Изучаем PHP 7. №29. Работа с базой данных. SQLite ”
Ресурс сдох, судя по всему — за год никто не ответил.
Вопрос неактуален с момента выхода PHP 7.3.
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
SQLite и PHP
Для кого эта статья
В последнее время, вы, возможно, слышали о новом расширении для PHP: SQLite. Есть много причин, по которым SQLite может показаться лучшим достижением с тех пор, как научились резать хлеб. SQLite предлагает добротный SQL интерфейс к нереляционной базе данных и создаёт элегантную альтернативу громоздким интерфейсам других баз данных без потери функциональности или скорости, как можно было бы ожидать. Мы рассмотрим в статье это удивительное расширение и, будем надеяться, нам удастся подтвердить те преимущества, о которых, возможно, вы наслышаны.
SQLite – это встраиваемая библиотека в которой реализовано многое из стандарта SQL 92. Её притязанием на известность является как собственно сам движок базы, так и её интерфейс (точнее его движок) в пределах одной библиотеки, а также возможность хранить все данные в одном файле. Я отношу позицию функциональности SQLite где-то между MySQL и PostgreSQL. Однако, на практике, SQLite не редко оказывается в 2-3 раза (и даже больше) быстрее. Такое возможно благодаря высокоупорядоченной внутренней архитектуре и устранению необходимости в соединениях типа «сервер-клиент» и «клиент-сервер».
Всё это, собранное в один пакет, лишь немногим больше по размеру клиентской части библиотеки MySQL, является впечатляющим достижением для полноценной базы данных. Используя высоко эффективную инфраструктуру, SQLite может работать в крошечном объёме выделяемой для неё памяти, гораздо меньшем, чем в любых других системах БД. Это делает SQLite очень удобным инструментом с возможностью использования практически в любых задачах возлагаемых на базу данных.
Что мне проку от SQLite?
Помимо скорости и эффективности у SQLite есть ряд других преимуществ, которые делают её идеальным решением для многих задач. Так как база данных SQLite по сути своей – обычные файлы, нет ни какой необходимости в дополнительных средствах администрирования требующих много времени на создание сложной структуры прав доступа для защиты пользовательских баз данных. Всё это уже автоматически поддерживается организацией прав доступа в самой файловой системе, это также подразумевает (в области ограничения пространства), что не требуется ни каких специальных правил для отслеживания заполнения дискового пространства пользователем. Преимущество для пользователей в том, что есть возможность создать такое количество баз данных, какое они себе пожелают плюс абсолютный контроль над всеми этими базами данных.
Факт, что база данных – это единственный файл, делает её легко переносимой. SQLite к тому же, устраняет необходимость в запуске дополнительных служебных процессов (daemons), которые могли бы «отъедать» значительное количество памяти и других ресурсов, даже в случае умеренного использования базы данных.
SQLite также поддерживает гибкий механизм для передачи ресурсов базы данных в процедурные интерфейсы, делая это одинаково лёгким для перехода из MySQL, где ресурс передаётся последним, и PostgreSQL, где он передаётся первым.
SQLite также отличает мощный объектно-ориентированный интерфейс, который может быть использован для эффективного извлечения данных из базы, избавляя вас от необходимости выполнения вашей собственной оболочки для процедурного интерфейса. Как показано в примере ниже, объектно-ориентированный интерфейс также позволяет вам избежать передачи всех ресурсов кучей.
В PHP 5.0 установка SQLite имеет свою особенность, так как и расширение и библиотека связаны вместе, поэтому всё, что вам необходимо сделать – это добавить –with-sqlite в строке конфигурирования. Я бы ещё порекомендовал установить SQLite, но только в случае если речь об откомпилированном бинарном файле, что позволит вам открывать базу и управлять ею без использования PHP. Это весьма полезно для отладки и выполнения различных одновременных команд, а также полезно для тестирования кода запросов. В будущем, довольно часто вы сможете обнаружить, что связанная библиотека SQLite немного «устарела», поэтому связка вашего PHP с внешней библиотекой позволит вам извлечь выгоду из последних исправлений и нововведений SQLite. Это также позволит вам в будущем обновлять вашу SQLite без перекомпиляции PHP.
Чтобы собрать расширение SQLite в качестве внешней библиотеки, просто используйте –with-sqlite=/path/to/lib/.
Мне также следует упомянуть, что расширение SQLite проходит исчерпывающую серию тестов, как для объектно-ориентированного, так и для процедурного интерфейсов. Тестируются каждая отдельная функция, и каждый метод поддерживаемый SQLite. Это великолепный источник примеров не только того, как работает каждый метод и каждая функция SQLite, но и ожидаемого вывода, позволяющего вам видеть конечный результат каждой операции.
Процедурный интерфейс к SQLite почти такой же, как у MySQL и других расширений БД. По большей части переход к SQLite потребует только изменить mysql/pq/etc… префикс функции на sqlite.
Собственно значительные отличия между SQLite и другими базами данных находятся в самом движке. В отличие от других БД в SQLite нет привязки к типам; все данные сохраняются как строки оканчивающиеся символом NULL, что лучше, чем двоичное представление данных в столбцах специального типа. По причине совместимости SQLite пока поддерживает тип спецификации в конструкциях CREATE TABLE, например, такой как INT, CHAR, FLOAT, TEXT и тому подобные, но реально их не использует. Внутри базы, SQLite только делает различие между строковыми и целочисленными данными во время сортировки. Поэтому, если вы не собираетесь сортировать данные, вы можете обойтись без указания специального типа полей при создании таблиц в SQLite.
«Безтиповая природа» SQLite делает сортировку и сопоставление данных в некотором роде медленнее, так как каждый раз SQLite будет вынуждена определять тип данных и применять либо строковый механизм сортировки/сравнения либо числовой. SQL таблицы часто требуют автоматически присваиваемый ключ для быстрого доступа к ячейкам, подразумевая возврат ссылки на последнюю добавленную ячейку. Для SQLite этот синтаксис, мягко говоря, бесполезен. Чтобы создать такую таблицу, вам понадобится объявить поле как INTEGER PRIMARY KEY, что более удобно чем указание специализированного типа или присваивание дополнительных свойств, которые указывают на то, что поле является автоинкрементным.
Как вы можете ожидать, SQLite несёт в себе много новых особенностей улучшающих исполнение и расширяющих функциональность. Одна из таких особенностей – возможность выполнять связанные запросы, которые подразумевают выполнение множественных запросов через функцию выполнения одного запроса. Это снижает количество задействованных функций PHP и таким образом увеличивает скорость работы скрипта. А также позволяет вам легко группировать блоки запросов внутри транзакций, улучшая выполнение в дальнейшем. Такая возможность может быть значительным фактором во время выполнения множественных обращений на запись в базу. Однако существует несколько пикантных особенностей, о которых не следует забывать.
Если какой-либо запрос в SQLite использует что-то введённое пользователем напрямую, вам следует предпринять дополнительные меры предосторожности для проверки такого ввода, чтобы пресечь нежелательный запрос. В противоположность MySQL, где такое может повлечь за собой лишь недоразумение при запросе к БД и выдать ошибку, в SQLite это позволит атакующему выполнить какой-нибудь запрос на вашем сервере с возможными плачевными последствиями. Если вы добавляете записи через блок запросов и хотели бы возвращать идентификатор, то sqlite_last_insert_rowid() хорошо справится с такой задачей, но вернёт идентификатор только последней записи. С другой стороны, при попытке определить какое количество ячеек было изменено, используя sqlite_changes(), мы получим результат, содержащий общее число ячеек изменённых всеми выполненными запросами. Если ваш блок запросов содержит SELECT, убедитесь что это самый первый запрос, в противном случае ваша окончательная выборка не будет содержать ячеек, возвращаемых этим запросом.
Помимо своих новых внутренних особенностей, SQLite предлагает ряд новых функций, которые упрощают и ускоряют извлечение данных из базы.
Это позволяет и выполнять запрос и извлекать данные одним вызовом функции, сводя на нет всю лишнюю работу PHP. Сам по себе PHP скрипт упрощается, за счёт использования всего одной функции там, где иначе бы Вам пришлось использовать несколько функций для извлечения данных работающих в цикле. В тех случаях, когда возвращается результат только одного столбца, можно использовать sqlite_single_query(), которая мгновенно возвращает строку или массив строк в зависимости от числа ячеек, из которых извлекается информация.
Как и в случае с другими особенностями, вы можете этим пользоваться, но вам не следует этим злоупотреблять. В случае выборки всех данных, запросом в один проход, вы должны помнить, что все результаты будут сохраняться в памяти. Если результат выборки содержит большое количество данных, цена затрат на память безусловно сведёт на нет все преимущества полученные путём снижения числа вызовов функций. Следовательно, вам нужно приберечь использование этих функций для случаев выборки небольшого количества данных.
Итераторы в SQLite
В PHP 5.0 есть другой способ выборки данных запросом с использованием итераторов.
Получение результатов в цикле работает точно так же, как и проход по массиву в цикле через foreach(), за исключением того, что в это время у вас нет доступа к ключам и значение представляет собой массив, содержащий данные в особых ячейках. Так как итераторы это не функции, а внутренние указатели в самом движке, они требуют гораздо меньше ресурсов по сравнению с функциями sqlite_fetch_*(), и не требуют, чтобы результаты кэшировались в памяти. Конечный результат будет чрезвычайно быстрым, это более простой и доступный метод извлечения данных. Нет ни каких побочных эффектов в использовании итераторов объектов в SQLite, и когда бы вам ни понадобилось получить результат в виде множества строк, вам нужно обязательно помнить о такой возможности.
SQLite также отличает ряд вспомогательных функций, которые могут пригодится во время работы с базой данных. Одна из таких функций, sqlite_num_fields(), может быть использована для определения количества столбцов в специальных результатах выборки. Как вариант, если вы собираетесь извлекать данные, просто используйте count() с первым результатом, которая вернёт вам то же самое число. Если извлечены как строковый, так и числовой ключи, вам понадобится отделить результат, поскольку в результирующем массиве будет столько вхождений сколько существует полей в таблице. Это число может быть важно, если вашему скрипту необходимо получить названия полей внутри какой-то таблицы. В таком случае вы могли бы использовать sqlite_field_name() в цикле, для доступа к этой информации, как показано в примере ниже.
Конечно, это не идеальный способ для получения имён столбцов из таблицы, просто по тому, что он окажется неудачным в случае, когда таблица не содержит ни одной ячейки, а также по тому, что это потребует от вас извлечения данных, которые вы не собираетесь использовать. Гораздо лучшим решением будет использование функции sqlite_fetch_column_types(), которая получает столбцы вместе с их типами из таблицы не зависимо от того, есть ли там данные или нет.
В большинстве случаев, по причинам выполнения или использования памяти, вы не захотите использовать запросы без кеширования. Это может привести к небольшой потери функциональности, что может быть необходимо в определённых случаях, для которых запросы без кеширования не всегда самый лучший выбор.
Например, предположим, вы хотели бы выяснить реальное количество ячеек, выбранное Вашим запросом. В запросе без кеширования, вам придётся делать выборку каждой отдельной ячейки, прежде чем вы сможете её определить. Кешированные же запросы – более простой способ, заключающийся в использовании функции sqlite_num_rows(), которая легко получит эту информацию из результата выборки. Запросы без кеширования также ограничены в прямой выборке данных, подразумевая, что вы обязаны выбрать данные из всех ячеек за один проход. В кешированных запросах такого ограничения нет, вы можете использовать sqlite_seek() для смещения на любую ячейку и выборки из неё данных, или даже для доступа к ячейкам в обратном порядке, если это необходимо.
Одна из самых интересных возможностей в том, что расширение SQLite предоставляет таблицу для возможности создания своих собственных функций используемых в пределах SQL. Это возможно благодаря тому, что SQLite содержит в одной библиотеке связанной с PHP, как интерфейс, так и сам движок базы. Используя sqlite_create_function() вы можете создавать функции, которые в дальнейшем могут быть применены к результату выборки или использованы внутри какого-нибудь условия WHERE.
Возможность использования PHP в SQL позволяет вам упростить данный скрипт, делая его доступным большей аудитории разработчиков. Это позволяет использовать PHP в качестве движка шаблонов, который будет вставлять HTML код в данные из базы. Во многих случаях это может упростить код так сильно, что не возникнет необходимости в использовании дополнительного движка шаблонов. Помимо упрощения кода, это также улучшает выполнение программы и снижает затраты скрипта на память вследствие того, что нет ни каких действий с данными, происходящими в пользовательском пространстве.
Не забывайте, что в том случае, если данные, с которыми вы работаете, могут содержать двоичный код, вам следует использовать функцию sqlite_udf_decode_binary() для перевода их из внутренней кодировки, в которой они хранятся в SQLite, прежде чем работать с ними. Если вы так поступили, вам понадобится закодировать двоичные данные, используя sqlite_udf_encode_binary() для того, чтобы быть уверенным, что данные не будут повреждены при следующем к ним доступе.
Теперь, посмотрев как работает SQLite, и что она может предложить, вы, возможно, обратите на неё внимание для использования в своих текущих или будущих приложениях. Надеюсь, этот краткий обзор дал вам некоторое представление о функциональности SQLite, и не развеял ничего из того, что вы слышали о ней хорошего.
Как и любой инструмент, SQLite имеет свои сильные и слабые стороны. Будучи идеальным решением, для приложений небольших и/или предназначенных главным образом для считывания данных, она не очень хорошо подходит для крупномасштабных приложений, часто выполняющих запись в базу. Это ограничение – следствие того, что архитектура SQLite основана на одном единственном файле, которая не предусматривает распределения данных на многократно создаваемые файлы, а также использует блокировку на запись.
Php sqlite
Недавно решил оптимизировать расходы на содержание своих интернет проектов. Обратил внимание, что хостинг с поддержкой mysql дороже чем хостинг только с php. Тем более количество баз данных ограниченно тарифным планом. Конечно без бд то же никак, но отказаться от использования mysql можно. Ему на замену приходит sqlite.
В отличии от mysql, sqlite является библиотекой, а не серверной программой. Эта библиотека уже встроена в интерпритатор php и не требует каких то ухищрений с администрированием. Для работы достаточно наличия на сервере только интерпритатора php. Это значительно снижает стоимость у хостера тарифного плана. Sqlite пишет все свои данные в файл, который создаётся на сервере. Для каждой бд создаётся свой файл. Ограничением по количеству бд может являться только объём жёсткого диска под сайт. Так как все бд хранятся в файлах то для резервирования базы или переезда к другому хостеру файлы бд всего лиш нужно скопировать.
Все свои сайты я сначала создаю на локальном компьютере. На нём у меня стоит windows. Поэтому разберём подключение sqlite и sqlite3 под windows.
Подключение sqlite
Будем считать что веб сервер apache и php у нас уже установлен. В зависимости от версии интерпритатора в папке ext корневой директории PHP должны быть файлы php_pdo.dll, php_pdo_sqlite.dll, php_sqlite.dll. Для того что бы подключить sqlite, открываем файл php.ini в любом текстовом редакторе и раскомментируем директивы подключения нужных нам модулей. Модули, закомментированные при помощи точки с запятой ;, как правило, требуют дополнительных внешних библиотек. Директивы должны выглядеть так:
Для подключения php_sqlite.dll в php.ini, нужно сначала подключить расширения php_pdo.dll и php_pdo_sqlite.dll. Только в такой последовательности:
Строки extension=php_pdo.dll может в ini и не быть, раскоментируем тогда только extension=php_pdo_sqlite.dll и extension=php_sqlite.dll После всего этого перезапустим апачь. Для проверки правильности установки запустим строку кода:
Должно показать версию sqlite.
Php sqlite примеры
Создание таблицы в базе данных
Получение результата в виде массива
Подключение php sqlite3
Php sqlite3 подключается по такому же принципу как и sqlite. В папке ext должны быть библиотека php_sqlite3.dll. В php.ini должна быть раскомментирована строчка extension=php_sqlite3.dll. Проверяем подключение:
Получаем массив с информацией о библиотеке.
Php sqlite3 примеры
Создание файла БД, открытие и закрытие соединения
SQLite — замечательная встраиваемая БД (часть 2)
В этой части будут затронуты непростые вопросы использования SQLite через работу с его программным интерфейсом (API).
Что такое SQLite API? Это набор функций sqlite3_XXX на языке C.
Заголовки этих функций находятся в sqlite3.h, а описание API в виде одного большого HTML находится тут.
Мы начнем использовать API, а позднее плавно перейдем к вопросам оборачивания его в уютный фреймворк.
Попробуем создать небольшой проект на C++ (я делал в MS Visual Studio), который что-то делает с SQLite базой.
Использовать SQLite в своем проекте можно двумя способами.
«Вкомпилировать» код (если это C или C++). Или загружать и использовать sqlite3.dll (позволяет легко обновлять SQLite и не привязан к языку).
В любом случае, надо создать новый проект (консольного) приложения (я использовал MS Visual Studio) и добавить в него вот такой main.cpp:
const char * SQL = «CREATE TABLE IF NOT EXISTS foo(a,b,c); INSERT INTO FOO VALUES(1,2,3); INSERT INTO FOO SELECT * FROM FOO;» ;
sqlite3 * db = 0 ; // хэндл объекта соединение к БД
char * err = 0 ;
Далее, если мы желаем собрать проект вместе с кодом SQLite, то надо:
— скачать исходный код в виде amalgamation;
— извлечь из него sqlite3.h и sqlite3.c и добавить их в проект.
Если же мы хотим использовать sqlite3.dll, необходимо:
— скачать SQLite DLL и распаковать;
— выполнить «LIB.EXE /DEF:sqlite3.def» в папке куда распаковали для получения sqlite3.lib (убедитесь, что пути к lib.exe прописаны через вызов vcvars32.bat);
— включить в проект sqlite3.lib;
— скачать amalgamation и извлечь из него sqlite3.h;
— включить в проект sqlite3.h;
Компилируем, выполняем (во втором случае, DLL должна быть доступна для исполняемого файла).
При первом запуске будет создан файл «my_cosy_database.dblite» с БД, в нем — одна таблица и две записи в ней.
При последующих запусках — программа будет присоединяться к уже существующей БД и удваивать число записей в таблице.
Давайте разбираться с кодом проекта.
Использование SQLite предполагает, что мы хотим выполнять команды на языке SQL в какой-то БД (Ваш К.О.!).
Можно представить работу с SQLite базой как работу с файлами в ОС Windows. Мы открываем файл и получаем _хэндл_ файла, к которому «привязан» некий системный объект (файл). Передавая этот хэндл в различные функции мы просим систему что-то сделать с файлом. Затем — закрываем файл. Также и в SQLite. Мы открываем файл с БД и получаем хэндл объекта «соединение к БД». Затем мы исполняем некие SQL команды через вызовы функций, получающих этот хэндл, и, в конце, закрываем соединение.
Ничего оригинального, особенного или хитрого, как видим, тут нет.
Расширение файла с БД SQLite не стандартизовано. Некоторые делают его «.sqlite3», но можно поставить любое.
создает или открывает БД в указанном файле (UTF-8!) и заносит хендл соединения в db. Она возвращает 0 (успех) или код ошибки. Закрывающий вызов sqlite3_close нужен в любом случае (даже при ошибке sqlite3_open).
выполняет команду SQL (состояющую из одного или нескольких операторов SQL в UTF-8, разделенных «;») в контексте указанного (открытого) соединения к БД.
Функция обратного вызова (callback) и custom аргумент к ней нужны, если делается SELECT (для извлечения данных). Об этом позже, пока не используем.
В параметре errmsg можно получить текст ошибки и позднее очистить его через sqlite3_free. Если передать здесь NULL, то текста ошибки не получим.
Как и sqlite3_open возвращается 0 или код ошибки.
Команда «CREATE TABLE» содержит уточнение «IF NOT EXISTS». Это означает, что таблица создается, если ее — нет. Если есть, то ничего не происходит (и нет ошибки).
Собственно, вот и все. Несмотря на то, что в реальных проектах не используются ни sqlite3_open (есть более мощный вызов sqlite3_open_v2), ни sqlite3_exec (обычно используется связка вызовов, компилирующих SQL, привязывающих параметры и пр.) это вполне рабочий проект.
Он несет и еще одну важную миссию. Как уже было сказано при каждом запуске он удваивает кол-во строк в таблице foo. После 20 запусков в таблице будет
Т.е. 21-й запуск вставляет
На моей машине (Windows 7 x64, i5 2.8 Ghz, HDD, не SSD ) это заняло
15 секунд. Пускай это убогий и синтетический тест, но он все-таки дает определенное представление о производительности SQLite.
Работа с базой данных SQLite с помощью обертки SQLitePCL
Есть на хабре и статья с описанием чуть менее известной обертки:
SQLite. Готовимся к Windows 10 (Universal App Platform)
А ранее была такая статья про обертку с названием SQLite-net PCL:
SQLite теперь для мобильных приложений на С# под любую платформу
Казалось бы, это и есть SQLitePCL, тем более что URL у проекта тот же самый, но на деле это оказалась обертка с совершенно другим синтаксисом.
Мы все-таки рассмотрим именно текущую версию SQLitePCL.
Сначала нам нужно добавить SQLite в проект UAP. Сделать это можно таким образом:
После установки для того, чтобы изменения вступили в силу, требуется перезапустить Visual Studio.
Теперь в проекте нужно задать ссылку на SQLite:
Tim Heuer в своей статье о SQLite и приложениях Windows 8 рекомендует также добавить ссылку на Microsoft Visual C++ Runtime Package, мотивируя тем, что вряд ли на клиентской машине будет отсутствовать C++ Runtime, но если эту ссылку не добавить, то приложение провалит сертификационные тесты перед публикацией в Windows Store.
Саму библиотеку SQLite и ссылку на нее мы добавили, теперь нам нужно добавить обертку SQLitePCL. Делаем это зайдя в меню Сервис – Диспетчер пакетов NuGet – Управление пакетами NuGet для решения… Находим по поиску SQLitePCL и устанавливаем:
Либо можно установить PCL с помощью консоли диспетчера пакетов (Сервис – Диспетчер пакетов NuGet – Консоль диспетчера пакетов) с помощью команды:
Install-Package SQLitePCL
Теперь все готово к работе. Если вдруг ваш проект при развертывании выдаст сообщение о том, что развертывание пропущено:
Значит необходимо, чтобы в диспетчере конфигураций:
конфигурация была предназначена для развертывания (а если нужно то и для сборки):
Немного вводной информации о SQLite, которая будет полезна при первом знакомстве с этой базой:
Каждая строка таблиц SQLite (за исключением таблиц WITHOUT ROWID) имеет 64-битный signed integer ключ который называется ROWID. Получить значение этого столбца можно по одному из следующих регистр независимых псевдонимов: «rowid», «oid» или «_rowid_».
По информации с официального сайта SQLite Datatypes In SQLite Version 3 данные хранятся в одном из следующих классов:
NULL Значением является NULL
INTEGER Значение знакового целого числа может хранится в 1,2,3,4,6 или 8 байтах в зависимости от величины числа.
REAL Значение числа с плавающей точкой хранится в 8-байтном IEEE числе с плавающей точкой.
TEXT Значением является текстовая строка, которая хранится с использованием кодирования базы данных (UTF-8, UTF-16BE or UTF-16LE).
BLOB Значением являются blob-данные, которые заносятся так, как они и были введены.
Класс данных содержит в себе более широкий смысл, чем тип данных. Скажем, класс INTEGER содержит в себе 6 типов данных различной длины.
В базах SQLite нет типа BOOLEAN. Вместо этого типа используется тип INTEGER со значениями 1 и 0.
Время и дата может сохраняться в типах TEXT, REAL или INTEGER:
1. В виде текста как строка ISO8601 («YYYY-MM-DD HH:MM:SS.SSS»).
2. В виде REAL числа с плавающей точкой как количество дней Юлианского календаря, начиная считать с полдня по Гринвичу 24 ноября 4714 года до нашей эры в соответствии с пролептическим григорианским календарем.
3. В виде целого числа INTEGER как время Unix, — число секунд прошедших начиная с 1970-01-01 00:00:00 UTC.
Для того чтобы добиться максимальной совместимости с другими базами данных SQLite поддерживает так называемый концепт сходства типов — type affinity. Имеется порядок правил, сопоставляющий типы данных других баз и SQLite. Скажем, первым правилом идет, что если в строке типа данных чужой базы содержится текст «INT», то этот тип будет сопоставлен типу SQLite INTEGER. Если это правило не выполняется, то проверяется второе правило — содержит ли строка типа данных текст «CHAR», «CLOB» или «TEXT». Если содержит, то тип данных сопоставляется с типом SQLite TEXT. И так далее…
И вот после такого относительно большого введения, большая часть информации которого в той или иной части уже встречалась в рунете, наконец-то перейдем к коду примеров работы с SQLitePCL
Для работы нам нужно добавить ссылку на пространство имен:
Создать таблицу в базе можно так:
Столбец первичного ключа задается с помощью выражения PRIMARY KEY и может быть только один у таблицы.
UNIQUE – ограничение, которое требует, чтобы в столбце содержались только уникальные значения. Таких столбцов в таблице может быть несколько.
NOT NULL – это constraint, который запрещает наличие в колонке значений NULL.
CHECK – задает условие, которое будет проверено. Пример:
Если у таблицы есть внешний ключ, то для него необходимо создать индекс. Также индексы желательно создавать для оптимизации скорости поиска по большим таблицам. Для столбцов первичного ключа индекс создается автоматически. Создать индекс можно так:
Более подробная информация о SQL синтаксисе создания таблиц доступна по ссылке: SQLite Query Language: CREATE TABLE
Из минусов SQLitePCL можно отметить то, что она не поддерживает создание внешних ключей (SQLite Foreign Key Support).
Если вам необходимо добавить связи между таблицами создавая их «на лету», то вы можете воспользоваться какой-либо другой оберткой. Кстати, внешние ключи в SQLite по умолчанию отключены.
Добавить запись в таблицу можно простым запросом:
А можно использовать параметры:
Следующего пользователя мы можем добавить в таблицу, используя точно такую же конструкцию, или же можем внутри using данной конструкции после Step() совершить следующие действия:
Также есть возможность вместо указания номера параметра использовать псевдоним:
Удаление происходит аналогично добавлению или обновлению записей:
Процесс чтения записей немного отличается:
Транзакции могут быть запущены вручную, используя «COMMIT TRANSACTION» или «ROLLBACK TRANSACTION». Для этого перед списком запросов выполняем:
А уже после запросов подтверждаем с помощью:
Сама база данных приложения на десктопе находится по адресу
%USERPROFILE%\AppData\Local\Packages\
Для просмотра базы на мобильном устройстве необходима утилита.
Например, вы можете воспользоваться Power Tools for WP8.1
Мне удалось с помощью этой утилиты получить доступ к базам SQLite и на телефоне с Windows 10:
На официальном сайте можно найти инструменты для работы с SQLite
Мне по вкусу пришлась польская программа SQLite Studio, которая имеет поддержку русского языка и не требует установки под Windows (.zip архив)
Кроме нее можно выделить бесплатную SQLite2009 Pro Enterprise Manager
Персональная версия SQLite Expert бесплатна для личного и коммерческого использования.