Тема 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
В даному випадку ми створили базу даних з ім'ям mydb і розширенням sqlite3. У цьому ми можемо легко переконатися, скориставшись dot-командою .database:
Якщо ви відкриєте робочу папку, то побачите, що в ній з'явиться новий файл, в нашому випадку файл буде називатися testsavedb, а його розширення буде .sample. Запам'ятайте, в SQLIte3 немає команди CREATE DATABASE, бази даних тут не створюються засобами мови запитів SQL. У SQLite їх можна створювати тільки за допомогою адміністративних можливостей бібліотеки.
Управління базами даних в SQLite3
У SQLite є можливості по управлінню базами даних посредствам мови запитів SQL. Давайте подивимося на можливості управління базами даних в SQLite3
Підключення бази даних: реалізація SQL команди ATTACH DATABASE
Отже, перше, про що варто сказати, так це про те, що в SQLite є спеціальна SQL команда ATTACH DATABASE. яка дозволяє працювати з декількома базами даних в одному з'єднання. Давайте розглянемо цю можливість адміністрування баз даних. У нас є три недавно створених файлу баз даних: testsavedb.sample, mydatabase.db3 і mydb.sqlite3. В даний момент ми працюємо з файлом testsavedb.sample. Давайте скористаємося SQL командою ATTACH DATABASE, щоб підключити дві що залишилися бази даних:
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 ми можемо використовувати в такий спосіб: