SQLite-песочница в браузере
Чего мне всегда не хватало, так это аналога JSFiddle для SQLite. Онлайн-песочницы, в которой можно быстро проверить SQL-запрос и поделиться с другими.
Возможность загрузить готовую базу, а не писать SQL для создания таблиц.
Подключать как локальные базы, так и удаленные (по url).
Сохранять базу и запросы в облаке.
Бесплатно и без регистрации.
Свежайшая версия SQLite.
В итоге сделал такую песочницу сам.
Реляционная база данных в браузере
Сначала покажу результат, потом расскажу, как все устроено:
SQLite в браузере
Абсолютно во всех браузерах — что мобильных, что десктопных — уже встроена отличная СУБД, которая реализует стандарт SQL-92 (и большой кусок более поздних стандартов) — SQLite. Логично было бы дать к ней доступ через API браузера.
В конце нулевых многие браузерные вендоры так думали. Тогда появился стандарт Web SQL, который поддержали Apple (Safari), Google (Chrome) и Opera (еще популярная тогда). А Mozilla (Firefox) — нет. В результате в 2010 Web SQL убили, и дальше хранение данных в браузере пошло по NoSQL-пути (Indexed Database, Cache API).
В 2019 году в проекте sql.js Офир Ложкин скомпилировал исходники SQLite в WebAssembly («родной» бинарный формат для браузера). Это полноценная СУБД SQLite, которая работает в браузере (благо, она небольшая — бинарник занимает 300+ Кб).
sql.js — идеальный движок для онлайн-песочницы. Его я и взял.
Загрузка базы из файла
Загрузка базы по URL
Запросы к базе
Пожалуй, самая простая часть — sql.js предоставляет удобное API для запросов:
Экспорт базы в SQL-скрипт
Бинарный контент базы получить несложно — sql.js предоставляет отдельный метод:
Но мне хотелось сохранять не бинарник, а полный SQL, который создает схему и заполняет таблицы данными. Так результат можно посмотреть глазами и загрузить в PostgreSQL или другую СУБД.
Для экспорта базы в SQL я воспользовался алгоритмом питонячей утилиты sqlite-dump. Код довольно объемный, поэтому не буду приводить здесь (если интересно, подробности в dumper.js). Вкратце:
Выбираем данные из каждой таблицы и формируем запросы insert into.
Получается вполне читаемый скрипт:
Сохранение в облаке
Базу и запросы нужно где-то хранить, чтобы можно было поделиться ссылкой на заполненную песочницу. Меньше всего хотелось пилить бекенд с авторизацией и хранилищем — пришлось бы сделать сервис платным, да и для людей лишняя головная боль с регистрацией.
К счастью, есть GitHub Gist API, который идеально подходит по всем критериям:
многие разработчики уже зарегистрированы на гитхабе;
разрешен CORS (можно делать запросы с других доменов);
приятный интерфейс пользователя;
бесплатный и надежный.
Интеграция через обычный fetch() : GET на выборку гиста, POST на сохранение.
От пользователя требуется только указать гитхабовский API-токен. Удобно, что токену можно дать разрешение исключительно на работу с гистами — сделать что-то плохое в репозиториях по такому токену заведомо не получится.
Пользовательский интерфейс
Современные фронтенд-проекты переполнены тулингом и инфраструктурными штуками, а мне это совсем не интересно (я не js-разработчик). Поэтому сознательно не стал использовать UI-фреймворки и сделал все на ванильном html + css + js. Кажется, для небольшого проекта это вполне допустимо.
Позаботился о верстке под мобильные — хотелось, чтобы песочницей можно было пользоваться с телефона. А на десктопе работают шорткаты для основных операций.
Код при этом получился достаточно модульным, благодаря нативным js-модулям и веб-компонентам — их поддерживают все современные браузеры. Наверно, настоящий фронтендер поморщится, но мне норм.
Резюме
Буду рад, если SQLite-песочница вам пригодится. Или, возможно, кому-то окажется полезен сам подход к созданию бессерверных проектов на JS + GitHub API. За конструктивную критику тоже буду благодарен, конечно ツ
Если интересно, как использовать SQLite на полную — подписывайтесь на канал @sqliter
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Тема 16: Администрирование и управление базами данных в библиотеки SQLite
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем рубрику реляционные базы данных и начинаем новый раздел библиотека SQLite. В этой записи мы поговорим про возможности администрирования и управления базами данных, которые есть в библиотеки SQLite3. Замечу, что таких возможностей не так уж и много, так как SQLite — это встраиваемая СУБД, которая не предполагает наличие административной части. Во многих больших и известных СУБД возможности по администрирование и управлению пользователями и самими базами данных довольно широкие, так как эти СУБД работают по принципу клиент-сервер. SQLite не работает по данному принципу.
Администрирование и управление базами данных в библиотеки SQLite
Итак, в этой записи мы поговорим о том, как мы можем управлять пользователями в базах данных SQLite. Затем разберемся с процессом создания баз данных в SQLite (в SQLite нет команды CREATE DATABASE, опять же, это потому, что SQLite является встраиваемой СУБД), поговорим о том, как подключать и отключать базы данных в рамках одного соединения (для этого есть специальные команды ATTACH DATABASE и DETACH DATABASE). Также мы рассмотрим SQL команду VACUUM, которая позволяет заново собрать базу данных (если можно так сказать, сделать дефрагментацию файла базы данных). А в завершении статьи мы поговорим про импорт базы данных в SQLite, возможности резервного копирования баз данных и о том, как удалить базу данных (команды DROP DATABASE в SQLite нет).
Управление пользователя в SQLite3 и их правами доступа
Возможностей по администрирование и управлению базами данных в SQLite не так уж и много. Начнем мы с управления пользователями баз данных SQLite, которых, кстати, нет. Поэтому в SQLite не команд определения доступа к данным. Права на доступ к базам данных определяются правами пользователя в операционной системе или приложением, в которое SQLite встроена.
Итак, в SQLite нет возможности администрировать и управлять пользователями базы данных за исключением среды, в которой библиотека SQLite3 работает. Но мы можем, например, создавать VIEW в базе данных и не создавать для VIEW различные триггеры, которые позволяют манипулировать данными в базе данных.
При этом мы можем написать программный код так, чтобы он работал только с представлениями (если у представлений нет INSTEAD OF триггеров, то для него доступна только команда SELECT), таким образом мы ограничим количество команд доступных пользователям.
Создание базы данных в SQLite
Здесь мы опишем три способа создания базы данных в SQLite. Отметим, что в SQLite3 нет команды CREATE DATABASE. Команда CREATE в SQLite создает любые объекты базы данных, но не саму базу данных. Итак, мы помним, что при запуске шелла sqlite3.exe мы можем передать в качестве параметра имя базы данных. Поэтому первый способ создания базы данных SQLite заключает в том, чтобы передать параметр команде, позволяющей запустить шелл:
Создание базы данных в SQLite
Второй способ создания базы данных в SQLite3 заключается в том, чтобы сперва создать файл базы данных, а затем показать его SQLite, чтобы библиотека добавила в него служебную информацию.
Второй способ создание базы данных в SQLite
Мы создали файл mydatabase.db3 и сохранили его в папку: c:\sqlite. Этот файл еще не является файлом базы данных SQLite3, так как у него нет служебных заголовков. Давайте это исправим, запустим sqlite3 без параметров и откроем созданный файл при помощи терминала:
Как только будет выполнена первая команда, SQLite добавит в наш файл свою служебную информацию и наш обычный файл превратиться в файл базы данных SQLite3. Посмотрим третий способ создания базы данных в SQLite3. Он заключается опять-таки в использование специальных команд шелла.
Управление базами данных в SQLite3
В SQLite есть возможности по управлению базами данных посредствам языка запросов SQL. Давайте посмотрим на возможности управления базами данных в SQLite3
Подключение базы данных: реализация SQL команды ATTACH DATABASE
Итак, первое, о чем стоит сказать, так это о том, что в SQLite есть специальная SQL команда ATTACH DATABASE, которая позволяет работать с несколькими базами данных в одном соединение. Давайте рассмотрим эту возможность администрирования баз данных. У нас есть три недавно созданных файла баз данных: testsavedb.sample, mydatabase.db3 и mydb.sqlite3. В данный момент мы работаем с файлом testsavedb.sample. Давайте воспользуемся SQL командой ATTACH DATABASE, чтобы подключить две оставшиеся базы данных:
Вторая команда выполнена не будет, так как мы не задали псевдоним для подключаемой базы данных, давайте это исправим:
Мы видим, что у подключенных баз данных есть псевдонимы, а также указан путь к файлу базы данных SQLite3. Теперь, если мы захотим поработать с той или иной базой данных, то нам следует использовать квалификатор или полное имя объекта базы данных, например, давайте создадим таблицу в базе данных first.db1:
И попробуем создать таблицу в главной базе данных, которая называется main:
Вторая команда CREATE выполнена не будет, так как если мы не указываем имя базы данных, то SQLite по умолчанию работает с главной базой данных main. Квалификатор это: database_name.table_name. Но обратите внимание, если во всех трех базах данных у нас будет таблица с уникальным именем, то к ней мы сможем обращаться без использования квалификатора, SQLite нас поймет.
Отключение базы данных: реализация SQL команды DETACH DATABASE
Так же мы можем отключать базы данных от текущего соединения при помощи SQL команды DETACH DATABASE. Команде DETACH DATABASE мы должны передать псевдоним, который мы использовали в команду ATTACH. Давайте отключим все подключенные ранее базы данных:
SQLite отключит все базы данных, кроме main. Main является основной базой данных в SQLite и ее мы никогда отключить не сможем, даже если мы явно не указали файл базы данных, с которым будем работать.
Стоит вспомнить о том, что все команды манипуляции данными (за исключением команды SELECT) и все команды определения данных работают в SQLite как транзакции, поэтому стоит отметить, что свойство атомарности при работе с несколькими базами данных в одном соединение сохраняется.
Повторная сборка базы данных: реализация SQL команды VACUUM. Дефрагментация базы данных в SQLite
В данном случае термин дефрагментация файла базы данных не совсем уместен, по крайней мере документация SQLite не использует термин дефрагментация. Но данный термин нам поможет понять, что делает SQL команда VACUUM с базой данных под управлением SQLite.
Все мы знаем, что дефрагментация диска – это процесс оптимизации его логической структуры с целью увеличения скорости доступа к объектам файловой системы (папкам и файлам). Примерно тоже самое делает команда VACUUM с базами данных SQLite3. Допустим, у нас есть файл базы данных, с которым мы постоянно работаем: удаляем строки, модифицируем значения в таблица, добавляем новые строки в таблицу. При этом стоит помнить, что любая СУБД – это в первую очередь абстракция над физическими данными, которая позволяет нам с ними работать, как с таблицами.
Поскольку это абстракция, то мы не знаем, как данные хранятся на диске. Обычно, если база данных довольно старая, то одна строка может храниться одной части файла, а соседняя строка в другой. Из-за этого уменьшается скорость работы с базами данных, поскольку данные, хранящиеся в базе данных записаны в файл не последовательно, а разбросаны по его разным частям.
Команда VACUUM позволяет это исправить. В SQLite команда VACUUM может быть применена только к базе данных main. Данная команда помещает содержимое основной базы данных во временные файлы, а затем собирает новый файл базы данных из временных файлов, в котором данные упорядочены и записаны последовательно.
Стоит обратить внимание на то, что команда VACUUM может нарушить внутренние индексы таблицы в SQLite, если в таблице нет ограничения первичного ключа PRIMARY KEY. Причем ключевой атрибут таблицы должен быть объявлен, как INTEGER PRIMARY KEY (в этом случае он совпадает со столбцом ROWID).
Команду VACUUM в SQLite мы можем использовать следующим образом:
Стартуем с SQLite3 – Основные команды
Установка
Мы будем взаимодействовать с базой данных через интерфейс командной строки sqlite3 (CLI) в Linux. Работа с sqlite3 CLI в MAC OS и Windows осуществляется таким же образом, однако я рекомендую вам потратить 5 минут на установку виртуальной машины, чтобы не захламлять свой компьютер лишним софтом.
Для установки sqlite3 на Linux выполняем команду:
sudo apt-get install sqlite3 libsqlite3-dev
В результате на вашей машине будет установлен sqlite3. Для установки данного инструмента на других ОС следуйте инструкциям. Для запуска sqlite выполняем команду sqlite3 в консоли. Результат должен быть таким:
Мета Команды
| Команда | Описание |
| .show | Показывает текущие настройки заданных параметров |
| .databases | Показывает название баз данных и файлов |
| .quit | Выход из sqlite3 |
| .tables | Показывает текущие таблицы |
| .schema | Отражает структуру таблицы |
| .header | Отобразить или скрыть шапку таблицы |
| .mode | Выбор режима отображения данных таблицы |
| .dump | Сделать копию базы данных в текстовом формате |
Стандартные команды
Теперь давайте пройдёмся по списку стандартных команд sqlite3, которые предназначены для взаимодействия с базой данных. Стандартные команды могут быть классифицированы по трём группам:
Заметка: SQLite так же поддерживает и множество других команд, список которых можно найти тут. Поскольку данный урок предназначен для начинающих, мы ограничимся перечисленным набором команд.
Файлы баз данных SQLite являются кроссплатформенными. Они могут располагаться на различного рода устройствах.
Далее знакомство с sqlite3 будет осуществляться на базе данных, предназначенной для хранения комментариев. Для публикации комментария пользователю необходимо будет добавить следующие данные:
Теперь давайте определимся с типами данных для каждой из колонок:
| Атрибут | Тип данных |
| post_id | INTEGER |
| name | TEXT |
| TEXT | |
| website_url | TEXT |
| comment | TEXT |
Тут вы сможете найти все типы данных, поддерживаемые в SQLite3.
Так же следует отметить, в SQLite3 данные, вставляемые в колонку могут отличаться от указанного типа. В MySQL такое не пройдёт.
В результате, в текущем каталоге у нас появится файл comment_section.db.
Заметка: если не указать название файла, sqlite3 создаст временную базу данных.
Создание таблицы
NOT NULL обеспечит уверенность, что ячейка не будет содержать пустое значение. PRIMARY KEY и AUTOINCREMENT расширяют возможности поля post_id.
Теперь можем внести данные в таблицу.
ВСТАВКА СТРОК
Предположим, что нам необходим внести следующую запись:
Для вставки воспользуемся командой INSERT.
Чтобы набить руку можете вставить ещё несколько строк.
ВЫБОРКА
Для выборки данных воспользуемся командой SELECT.
Этот же запрос может выглядеть так:
В результате из таблицы будут извлечены все строки. Результат может выглядеть без разграничения по колонкам и без заголовка. Чтобы это исправить выполняем:
Выполняем SELECT запрос ещё раз.
ОБНОВЛЕНИЕ
Предположим, что поле email для пользователя ‘Shivam Mamgain’ необходимо изменить на ‘zyx@email.com’. Выполняем следующую команду:
В результате запись будет изменена.
УДАЛЕНИЕ
Для выполнения команды DELETE нужно так же указать условие.
К примеру нам необходимо удалить комментарий с post_id = 9. Выполняем команду:
Для удаления комментариев пользователей ‘Bart Simpson’ и ‘Homer Simpson’ выполним:
ИЗМЕНЕНИ СТРУКТУРЫ
Для добавления новой колонки следует использовать команду ALTER. К примеру введём поле username. Выполняем команду:
УДАЛЕНИЕ
Для удаление нашей таблицы выполните следующую команду:
Заключение
SQLite3 даёт множество преимуществ в отличии от других СУБД. Множество фрэймворков таких как Django, Ruby on Rails и web2py по умолчанию используют SQLite3. Многие браузеры используют данный инструмент для хранения локальных данных. Так же она используется в качестве хранилища данных таких ОС как Android и Windows Phone 8.
Для работы с SQLite3 можно воспользоваться и программами с графическим интерфейсом. К примеру: DB Browser for SQLite и SQLiteStudio. Для тренировки работы с SQL можете поиграть с SQL Fiddle.
Данный урок может помочь стартовать с SQLite3. Для взаимодействия с данным СУБД в PHP можем воспользоваться расширением PDO.
Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: http://www.sitepoint.com/getting-started-sqlite3-basic-commands/
Перевел: Станислав Протасевич
Урок создан: 9 Апреля 2015
Просмотров: 152066
Правила перепечатки
5 последних уроков рубрики «Разное»
Как выбрать хороший хостинг для своего сайта?
Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.
Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.
Разработка веб-сайтов с помощью онлайн платформы Wrike
20 ресурсов для прототипирования
Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.
Топ 10 бесплатных хостингов
Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.
Freevi
Бесплатная программа SQLiteStudio для редактирования SQLite баз данных
SQLite не нужно устанавливать, единственный исполняемый файл, который вы скачаете, это и есть вся программы, который запускаем по необходимости. Только во время первого запуска приложение скромно поинтересуется, какой язык вы предпочитаете использовать, русский присутствует. Во время каждого запуска выскакивает таблица с подсказками, которую можно отключить, поставив соответствующую галочку.
Чтоб открыть базу данных жмем в основном меню «Базы данных»-> «Добавить базу данных», после чего выскакивает окошко, в котором выбираем файл с интересующей нас базой данной, при необходимости делаем небольшие настройки (какой именно версии база данных и запоминать ли её), и жмем «ОК». С настройками можете особо не заворачиваться и все оставить по умолчанию, в 99% случаев все прекрасно работает.
После подключения базы данных она добавляется в левую табличку, по щелчку на которой раскрывается список таблиц, которые там содержаться. В правой части можно увидеть структуру таблице на одноименной вкладке и данные, которые там храниться, для чего нужно щелкнуть по вкладке «Данные».
Об остальных возможностях SQLiteStudio нет смысла рассказывать, большинству простых пользователей они не понадобиться, а все остальные сами понимают, зачем лезут редактировать базы данных и как это нужно делать. Только хочу дать одну рекомендацию, делайте резервную копию базы данных, перед тем как начнете лезть в неё, а тем более редактировать.
На удивление здесь много настроек, однако, они все в основном связны с внешним видом программы (цвета, шрифты, что показывать и что прятать) и быстрыми клавишами.
Программа на 5+ справляется со своими задачами позволяя получить доступ к данным хранящимся в базах данных формата SQLite, редактировать данные и таблицы. Самое главное все работает довольно стабильно, быстро и не стопориться на некоторых файлах по непонятным причинам, как приложения других разработчиков.
Программа прекрасно работает на 32-х и 64-х битных операционных системах. Интерфейс переведен приблизительно на десять языков, в том числе и русский.
Официальный сайт SQLiteStudio http://sqlitestudio.pl/
Страница для бесплатного скачивания SQLiteStudio http://sqlitestudio.pl/?act=download
Последняя версия на момент написания SQLiteStudio 2.1.5
Размер программы: исполняемый файл 5,28Мб
Совместимость: Windows Vista, Windows 7, 8 и Windows XP
Менеджер SQLite баз данных, который позволяет просматривать и редактировать SQLite 3, SQLite 2 и SQLCipher базы данных.
Менеджер SQLite баз данных, который решает проблему, чем открыть db файл, и при этом позиционируется как редактор SQLite для SQLite 3, SQLite 2 и SQLCipher базы данных. Переведен на русский язык.
Особенности SQLite Editor
SQLiteStudio не нужно устанавливать, можно скачать портативную версию и начать редактировать SQLite базы данных без усилий.
На панели слева отображаются все доступные базы данных вместе с их данными. Иерархическое древовидное представление позволяет выбрать любую таблицу, триггер, процедуру или оператор и просмотреть данные в главном окне.
Через меню Базы данных можно открыть файл *.db, *.sdb, *.sqlite или *.db2 и подключиться к нему с помощью контекстного меню. Кроме того, можно импортировать схему из других баз данных или из файлов различных форматов, таких как CSV и dBase. После этого можно просматривать все существующие данные и манипулировать ими как угодно.
Используя Редактор запросов SQL, расположенный в меню Инструменты, можно вручную создать сценарий SQL и выполнить его.
История SQLiteStudio
Проект стартовал в начале 2007 года. Разработчик искал какой-нибудь SQLite-менеджер, которым легко пользоваться. Просто нужно было быстро создать таблицу и поместить в нее данные. Так как найти ничего не удалось, именно так появилась SQLiteStudio.
В то время разработчик был очень увлечен Tcl, поэтому это был предпочтительный язык программирования.
SQLiteStudio версии 1.0.0 имела очень простой графический интерфейс с сеткой для ввода данных.
Версия 1.1.0 (Рождество 2008 г.) представила поддержку SQLite 2, Tcl / Tk 8.5 (со сглаживанием, которого не было в версии 1.0.0), а также много других функций. Это был большой шаг вперед.
Версия 2.0.0 (ноябрь 2010 г.) внедрила более умное завершение синтаксиса, подсветку синтаксических ошибок, красивый принтер для SQL-кода (форматировщик), поддержку перетаскивания и многое другое. Это была первая версия, в которой использовался настоящий парсер кода. Кроме того, он был первым, работающим на Mac OS X.
Выпущено 28 исправлений! Проект настолько вырос, что поддерживать его стало проблемой. Исходный код также был не самым красивым (в плане поддержки).
SQLiteStudio версии 2.1.0 (декабрь 2012 года, канун Нового года) не добавляла «огромных» возможностей, но расширила множество второстепенных функций, которые уже были в 2.0.0. Многое изменилось в исходном коде, чтобы его было легче поддерживать.
Было принято решение перейти на C++ и библиотеку Qt.
После выпуска 2.1.2 началась работа над версией 3.0.0 на C ++ и Qt. Это был март 2013 года.
SQLiteStudio 3.0.0В версии приобрела большую скорость и ощущение легкости. Кроме того, она был намного лучше спроектирована, поэтому было легче поддерживать и расширять. Порт на MacOS X стал намного стабильнее и стал выглядеть нативно.