Простое использование SQLite. Использование sqlite manager для работы с субд sqlite

Sqlite – это очень простой и быстрый SQL-механизм с открытым исходным кодом. Данное руководство описывает установку и оптимальное использование Sqlite, в отличие от полнофункциональных СУРБД, таких как Mysql и Postgres, а также рассматривает и приводит примеры основного использования CRUD (Create, Read, Update, and Delete).

Не стоит думать, что Sqlite создан только для тестирования и разработки. К примеру, он может обеспечить сайту, по скромным подсчетам, до 100 000 просмотров в день. Максимальный размер базы данных Sqlite – 140 терабайт (что более чем достаточно), она может быть гораздо быстрее полномасштабной СУРБД. Полная база данных и другие необходимые данные хранятся в обычном файле в файловой системе хоста, следовательно, отдельный процесс сервера не нужен (что устраняет необходимость взаимодействия между процессами).

Оптимальное использование на VPS

Sqlite ориентируется на простоту. Благодаря тому, что эта база данных полностью внутренняя, она часто значительно быстрее, чем альтернативные варианты. Sqlite идеально подходит тем, кому нужна мобильность (что касается языков и платформ), простота, скорость и небольшое потребление памяти. Недостатки Sqlite видны только при необходимости читать или писать параллельно в несколько файлов: Sqlite поддерживает только одного редактора; кроме того, обычно большое запаздывание файловой системы может быть неудобным при необходимости работать в Sqlite одновременно нескольким пользователям. Последний возможный недостаток: синтаксис Sqlite уникален, хотя и похож на другие системы SQL. Перейти на другую систему, переросши Sqlite, достаточно просто, но это может повлечь за собой некоторые затраты ресурсов.

Установка Sqlite на VPS

Модуль sqlite3 является частью стандартной библиотеки Python, следовательно, на стандартной системе Ubuntu и на любой другой системе с установленной библиотекой Python не понадобится никаких дополнительных установок. Чтобы установить интерфейс командной строки Sqlite на Ubuntu, используйте эти команды:

sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev

При необходимости скомпилировать Sqlite из исходного кода, загрузите последнюю версию autoconf с sqlite.org/download.html. Во время записи:

wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz
tar xvfz sqlite-autoconf-3080100.tar.gz
cd sqlite-autoconf-3080100
./configure
make
make install

Примечания к компилированию из исходного кода:

Не делайте этого на стандартной Ubuntu, так как, вероятнее всего, из-за конфликта между уже имеющейся и недавно установленной версиями случится ошибка «header and source version mismatch» («несоответствие заголовочных и исходных файлов»);
Если покажется, что команда «make» ожидает дальнейшего ввода данных, просто подождите – компиляция из исходного кода занимает некоторое время.

Базовый интерфейс командной строки Sqlite

Чтобы создать базу данных, запустите команду:

sqlite3 database.db

где «database» — имя базы данных. Если файл «database.db» уже существует, Sqlite откроет его; если такого файла нет, он будет создан. Выведенный результат выглядит примерно так:

SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Теперь создайте таблицу и внесите в нее тестовые данные. Данная таблица называется «wines» и состоит из четырех столбцов: ID, производитель вина, вид вина, страна происхождения. В таблицу внесены три строки:

CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20));
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");

Итак, была создана база данных и таблица, в которую внесены некоторые записи. Теперь нажмите Ctrl + D, чтобы выйти из Sqlite, и введите следующее (заменяя «database» именем своей БД), что выведет только что созданную базу данных:

sqlite3 database.db

Теперь наберите:

SELECT * FROM wines;

Будут выведены только что внесенные записи:

Это все, что нужно знать о создании и чтении в Sqlite. Теперь нужно ознакомиться с обновлением и удалением:

UPDATE wines SET country="South Africa" WHERE country="France";

Это обновит базу данных, и все вина в списке, происходящие из Франции, будут отмечены как происходящие из Южной Африки. Проверьте результат:

SELECT * FROM wines;

Результат будет такой:

Теперь все вина происходят из Южной Африки. Теперь попробуйте удалить KWV из базы данных:

DELETE FROM wines WHERE id=2;
SELECT * FROM wines;

Теперь в списке будет на одну запись меньше:

Все основные операции с базами данных выполнены. В завершение приведен немного более сложный пример, иллюстрирующий использование двух таблиц и базовую операцию объединения.

Выйдите из Sqlite при помощи команды Ctrl + D и снова подключитесь к новой базе данных с помощью

sqlite3 database2.db

Будет создана очень похожая на «wines» таблица, а также таблица «countries», хранящая название страны и имя ее действующего президента. Сначала создайте таблицу «countries» и внесите в нее Южную Африку и Францию (обратите внимание, можно скопировать и вставить сразу несколько строк кода SQLite):

CREATE TABLE countries (id integer, name varchar(30), president varchar(30));
INSERT INTO countries VALUES (1, "South Africa", "Jacob Zuma");
INSERT INTO countries VALUES(2, "France", "Francois Hollande");

Теперь снова создайте таблицу «wines»:

CREATE TABLE wines (id integer, kind varchar(30), country_id integer);
INSERT INTO wines VALUES (1, "Pinotage", 1);
INSERT INTO wines VALUES (2, "Shiraz", 1);
INSERT INTO wines VALUES (3, "Pinot Noir", 2);

Посмотрите, какие виды вина происходят из Южной Африки:

SELECT kind FROM wines JOIN countries ON country_id=countries.id WHERE countries.name="South Africa";

Будет выведен такой результат:

Pinotage
Shiraz

Так выполняется основная операция объединения. Обратите внимание, Sqlite многое выполняет самостоятельно. На самом деле в команде «JOIN» по умолчанию используется «INNER JOIN», но можно использовать только ключевое слово «JOIN». Также не нужно указывать «wines.country_id», так как это значение однозначно. С другой стороны, если ввести команду:

SELECT kind FROM wines JOIN countries ON country_id=id WHERE country_id=1;

Будет выведено сообщение об ошибке: «Error: ambiguous column name: id.». это справедливо, так как в обеих таблицах существует столбец «id». В целом, сообщения об ошибке Sqlite помогают довольно быстро обнаружить и устранить любые проблемы, что значительно ускоряет процесс разработки. Чтобы получить более подробную информацию о синтаксисе, изучите официальную документацию, содержащую множество графиков: sqlite.org/lang_delete.html ; она очень полезна, но если вы предпочитаете конкретные примеры, ниже приведена ссылка на руководство, рассматривающее большинство видов объединения: zetcode.com/db/sqlite/joins/ .

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

Tags: ,

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. Для этого потребуется только установить дополнение .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Роутинг мы можем сделать, как описано в предыдущей статье . Файл.htaccess

AddDefaultCharset UTF-8 Options -Indexes RewriteEngine on RewriteBase /sqlite/ RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule (.*) /sqlite/index.php?$1

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

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

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

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

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

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

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 .

Код index.php:

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

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

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

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

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

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

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

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

Понадобилось мне на днях найти какой-нибудь небольшой, но функциональный менеджер баз данных SQLite под Windows. Предложений в Сети оказалась масса, начиная от простеньких бесплатных программок-браузеров БД и, заканчивая, навороченными утилитами с массой как необходимых, так и бесполезных наворотов. В итоге образовался этот обзор. Свой конечный выбор я делал, исходя из следующих требований к программе:

Функциональность. Возможность создавать таблицы, триггеры, индексы и т.д.
Корректная работа с кодировкой UTF-8
Цена. Желательно, конечно, чтоб цена была рана нулю, но в пределах разумного можно и немного раскошелиться.
Наличие русского интерфейса. Не принципиально, но всё-таки удобнее работать с программой на родном языке.
Удобство. Не хотелось бы долго и упорно ковыряться в интерфейсе в поисках необходимых функций.

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

SQLite Database Browser

Цена: бесплатно

Простой в использовании браузер баз данных SQLite. Так как основное назначение программы - просмотр данных, то, соответственно, функциональность ограничена.

Используя “SQLite Database Browser” можно:

просмотреть структуру базы данных
создавать новые таблицы с использованием мастера
создавать индексы
просматривать данные в таблицах и выполнять элементарный SQL-запросы (программа виснет наглухо при выполнении запросов типа “CREATE TRIGGER….”).

SQLite Database Browser понимает UTF-8 и, при необходимости, позволяет сменить кодировку. Русский интерфейс отсутствует.

SQLite3 manager LITE

Сайт производителя: http://www.pool-magic.net/sqlite-manager.htm

Цена: версия Lite распространяется бесплатно .

По сравнению с предыдущей программой “SQLite3 manager LITE” выглядит более функциональным. Кроме того, что можно просто просматривать данные в таблицах, также можно просматривать и создавать триггеры, индексы, представления и т.д. Дополнительно можно экспортировать все мета-данные базы данных. При этом можно создавать файлы с данными для экспорта таблиц в Paradox и Interbase.

Также в программе была предпринята попытка зделать, что-то вроде визуального мастера создания запросов наподобие MS Access, но, на мой взгляд, попытка успехом не увенчалась.

У бесплатной версии есть один недостаток - не понимает данные в кодировке UTF-8. Есть, конечно, возможность указать кодировку базы данных при открытии файла, но в списке кодировок UTF-8 отсутствует. Как работает Full-версия программы я так и не увидел, т.к. на сайте производителя чёрт ногу сломит. Висит какой-то непонятный javascript , выводящий непонятную инфу. В общем, сложилось впечатление, что проект успешно заглох.

SQLite Administrator

Цена: бесплатно

Достаточно удобная в использовании и функциональная программа. Вся структура базы данных представлена в виде дерева объектов, которое можно настраивать в зависимости от своих предпочтений. Судя по всему SQLite Administrator написан с использованием , т.к. среди дополнительных функций программы есть генерация кода Delphi по выделенному фрагменту SQL. Для создания таблиц, триггеров и т.д. используются удобные мастера. При использовании этой программы очень сильно огорчило отсутствие возможности нормальной работы с UTF-8 и, незначительно, но всё же несколько подпортил впечатление русский интерфейс - некоторые надписи не помещаются в отведенное им место. ак что при тестировании использовал дефолтный English.

Если для вас не принципиально использование UTF-8, то качайте SQLite Administrator - не пожалеете.

SQLiteManager

Цена: 49$ Есть Demo-версия

SQLiteManager как и предыдущая программа имеет достаточно удобный интерфейс, прекрасно понимает UTF-8 и наряду с этим имеет ряд своих “фишек”. Среди которых:

Анализатор SQL-запросов
Проверка целостности базы данных
Оптимизация базы данных
Возможность использования в программе своих скриптов (по-моему VBScript) для повышения удобства работы.

К дополнительным функциям программы можно отнести возможность генерации отчётов по базе данных. Demo-версия имеет свои жёсткие ограничения по запросам, например, в результаты SELECT попадают только первые 20 записей из набора. В плане удобства работы подпортило впечатление уж очень сильно мельтешение при перерисовки окон. У меня не такой уж и древний комп, но даже при 512 Mb видюшки окна заметно моргали.

SQLite Maestro

Цена: от 79$ есть Trial-версия на 30 дней .

Вообще линейка продуктов от SQL Maestro насчитывает огромное количество продуктов для администрирования самых разнообразных баз данных, в том числе и SQLite. Первое впечатление от программы - добротно сделанная игрушка для созерцания её со стороны. Да SQLite Maestro имеет всю ту функциональность, что мне была необходима, удобное дерево объектов баз данных, возможность достаточно быстро создавать различные объекты БД и т.д., но после достаточно лёгкого в использовании SQLite Administrator вид многоуровневых замороченных меню с кучей различных опций, которые по-большому счёту-то в принципе не нужны, как-то становится жутковато работать с этой программой. По этой причине и была выставлена 1 за удобство. Цена тоже не особо порадовала - 79$. По мне так лучше бы упростили интерфейс и снизили ценник хотя б до 50$ тогда можно было бы и подумать о покупке.

SQLite Expert

Цена: версия Personal - бесплатно, версия Professional - от 38$

В целом, для себя я не слишком уж больших отличий версии Personal от Professional . Чтобы разобраться с работой SQLite Expert потребовалось не более пяти минут. Программа имеет весь тот набор функций, которые были мне необходимы. Как и SQLiteManager Expert умеет проверять целостность базы данных, экспортировать данные в CSV или другие форматы баз данных, имеет удобный редактор SQL, мастеров для создания таблиц, индексов, триггеров и т.д. Доступ к функциям программы несколько сложнее, чем у SQLite Administrator , но не настолько, чтобы отпугивать только своим видо:). В общем, я остановил свой выбор на SQLite Expert Personal .

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

Книжная полка

Название :Базы данных и Delphi. Теория и практика

Описание Книга основана на материалах лекций и практических занятий, разработанных автором, и объединяет теоретические основы и практические аспекты разработки реляционных баз данных.

Купить на ЛитРес

383 руб.

Автор : Анатолий Хомоненко, Владимир Гофман
Название :Работа с базами данных в Delphi
Описание : Рассматривается использование средств Delphi для разработки приложений баз данных. Даются понятия баз данных, характеризуются элементы и описываются этапы проектирования реляционных баз данных, изложена технология разработки информационных систем, освещаются приемы работы с данными, создание таблиц и приложений баз данных, подготовка отчетов.

Купить на ЛитРес

151 руб.

MV framework поддерживает СУБД MySQL и SQLite. Ниже представлена технология работы с базой данных SQLite, которая является компактной и быстро переносимой с одного сервера на другой, при этом обладает всеми необходимыми возможностями для современной СУБД.

MV работает с SQLite по средствам библиотеки PDO. На большинстве серверов оно включено, но если база данных не запускается по причине отсутствия драйвера, то можно прочитать о его настройке. SQLite хоанит всю информацию в одном файле "userfiles/database/sqlite/database.sqlite".

Чтобы открыть файл базы данных для работы с таблицами необходимо установить дополнение к браузеру Mozilla Firefox под названием SQLite Manager. Дополнение позволяет работать с базой данных по аналогии с phpMyAdmin для MySQL. https://addons.mozilla.org/ru/firefox/addon/sqlite-manager/

Когда дополнение будет установлено, нужно запустить SQLite Manager и выбрать файл нужной базы данных "userfiles/database/sqlite/database.sqlite". После подключения файла базы данных к SQLite Manager в левой колонке появится список таблиц.

Для создания новой таблицы необходимо нажать в верхнем меню "Таблица -> Создать таблицу", после чего откроется окно для ввода полей таблицы.

Для перехода в уже созданную таблицу достаточно нажать на название таблицы в левой колонке. В верхней части на вкладках можно посмотреть структуру таблицы и список записей таблицы.

При двойном клике на запись в таблице откроется окно для редактирования полей записи.

В целом, работа в SQLite Manager не сильно отличается от работы в phpMyAdmin для MySQL. Также можно управлять таблицами и индексами, выполнять прямые запросы и создавать триггеры. Вся работа происходит только с одним файлом, который затем копируется с локального сервера на рабочий. При этом, важно не забывать ставить права на запись для файла базы данных и папки в которой он находится. В зависимости от конфигурации сервера права могут быть: 777, 770, 775 и другие.

Настройка PDO SQLite

Если драйвер PDO для SQLite подключен, то при вызове функции phpinfo() можно увидеть информацию о версии драйвера. Если же данной информации нет, то это значит, что драйвер не подключен и его надо включить в настройках PHP.

В папке библиотек (расширений) PHP должны быть файлы php_pdo_sqlite.dll и php_sqlite3.dll.

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

После перезапуска сервера в phpinfo() должна появиться информация о подключении драйвера PDO для SQLite, как на скриншоте выше.