Sqlite sequence что это

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

Системные таблицы SQLite

Системные таблицы SQLite могут запрашиваться в базе данных с помощью оператора SELECT, как и любая другая таблица.

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

Обзор системных таблиц

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

1. sqlite_master

Таблица sqlite_master содержит следующие столбцы:

2. sqlite_sequence

Таблица sqlite_sequence содержит следующие столбцы:

3. sqlite_stat1

Таблица sqlite_stat1 содержит следующие столбцы:

Источник

Внутренние таблицы SQLite

В данном посте описаны внутренние таблицы SQLite: их строение и назначение.

SQLITE_MASTER

Первая страница файла базы данных – это корневая страница «table B – tree», содержащая специальную таблицу «sqlite_master» (либо «sqlite_temp_master» для временной базы данных). Структура таблицы описана в таблице 1.

Нормализация поля SQL означает, что запрос соответствует следующим свойствам:

В данной таблице одна строка – это один объект базы данных. В дополнение к пользовательским объектам в «sqlite_master» хранятся и внутренние объекты базы, за исключением самой таблицы «sqlite_master». Имена внутренних объектов базы начинаются с «sqlite_», соответственно база запрещает пользователю создавать объекты, имена которых начинаются с «sqlite_».

SQLITE_SEQUENCE

Внутренняя таблица, необходимая для реализации AUTOINCREMENT. Для каждой пользовательской таблицы использующей инкремент, соответствует строка таблицы «sqlite_sequence».
Схема таблицы:
CREATE TABLE sqlite_sequence (name, seq);

Пользователь может добавлять, изменять и удалять строки из таблицы «sqlite_sequence», но не может удалять саму таблицу.

SQLITE_STAT1

Внутренняя таблица, создается с помощью команды ANALYZE. Используется для хранения справочной информации о таблицах и индексах, которой может воспользоваться планировщик для поиска эффективного способа выполнения запросов.
Схема таблицы:
СREATE TABLE sqlite_stat1 (tbl, idx, stat)

Поле stat после последовательности чисел, может содержать ключевое слово UNORDERED (отделенное от последнего числа пробелом). Если данное ключевое слово присутствует, то планировщик не использует индекс для сортировки или диапазонного запроса.
Если столбец idx равен NULL, то поле stat содержит число строк в указанной таблице.

SQLITE_STAT2

Устаревшая внутренняя таблица для версий SQLite 3.6.18 – 3.7.8. Содержала дополнительную информацию о распределении ключей.

SQLITE_STAT3

Внутренняя таблица для версий SQLite 3.7.9 и выше. Используется, если база собрана с параметром SQLITE_ENABLE_STAT3 или SQLITE_ENABLE_STAT4. Содержит дополнительную информацию о распределении ключей в пределах индекса.
Схема таблицы:
CREATE TABLE sqlite_stat3 (tbl, idx, nEq, nLt, nDLt, sample)

SQLITE_STAT4

Внутренняя таблица для версий SQLite 3.8.1 и выше. Используется, если база собрана с параметром SQLITE_ENABLE_STAT4. Содержит дополнительную информацию о распределении ключей в пределах индекса.
Схема таблицы:
CREATE TABLE sqlite_stat4 (tbl, idx, nEq, nLt, nDLt, sample);

SQLITE_STAT3 vs SQLITE_STAT4

sqlite_stat4 является обобщением таблицы sqlite_stat3. В таблице sqlite_stat3 предоставляется информация о крайнем левом столбце индекса, тогда как таблица sqlite_stat4 предоставляет информацию обо всех столбцах индекса.

RECORD FORMAT

Формат записи (RF) определяет последовательность значений, соответствующих столбцов в таблице или индексе. RF определяет количество столбцов, тип данных каждого столбца и содержание каждого столбца. Для записи используется VARINT (целое переменной длинны, 1-9 байт в длину, метод статического кодирования Хаффмана). Заголовок RF начинается с одного VARINT, который определяет общее число байт в заголовке (включая сам VARINT). Затем следует несколько VARINT (по одному на каждый столбец таблицы/индекса). Эти дополнительные числа называются Serial Type значения которых указаны в таблице 2.

Пример для понимания работы SQLITE_STAT1:

Допустим, имеется база данных:
CREATE TABLE test (a, b, c);
CREATE INDEX ind ON test(«a»,«b»,«c»)

Данные таблицы test:

После выполнения команды ANALYZE, поле stat для данной таблицы будет выглядеть (8 3 2 1):

Источник

Внутренние таблицы SQLite

В данном посте описаны внутренние таблицы SQLite: их строение и назначение.

SQLITE_MASTER

Первая страница файла базы данных – это корневая страница «table B – tree», содержащая специальную таблицу «sqlite_master» (либо «sqlite_temp_master» для временной базы данных). Структура таблицы описана в таблице 1.

В данной таблице одна строка – это один объект базы данных. В дополнение к пользовательским объектам в «sqlite_master» хранятся и внутренние объекты базы, за исключением самой таблицы «sqlite_master». Имена внутренних объектов базы начинаются с «sqlite_», соответственно база запрещает пользователю создавать объекты, имена которых начинаются с «sqlite_».

SQLITE_SEQUENCE

SQLITE_STAT1

SQLITE_STAT2

Устаревшая внутренняя таблица для версий SQLite 3.6.18 – 3.7.8. Содержала дополнительную информацию о распределении ключей.

SQLITE_STAT3

SQLITE_STAT4

SQLITE_STAT3 vs SQLITE_STAT4

sqlite_stat4 является обобщением таблицы sqlite_stat3. В таблице sqlite_stat3 предоставляется информация о крайнем левом столбце индекса, тогда как таблица sqlite_stat4 предоставляет информацию обо всех столбцах индекса.

RECORD FORMAT

Формат записи (RF) определяет последовательность значений, соответствующих столбцов в таблице или индексе. RF определяет количество столбцов, тип данных каждого столбца и содержание каждого столбца. Для записи используется VARINT (целое переменной длинны, 1-9 байт в длину, метод статического кодирования Хаффмана).
Заголовок RF начинается с одного VARINT, который определяет общее число байт в заголовке (включая сам VARINT). Затем следует несколько VARINT (по одному на каждый столбец таблицы/индекса). Эти дополнительные числа называются Serial Type значения которых указаны в таблице 2. Значение каждого столбца идет сразу за заголовком.

Пример для понимания работы SQLITE_STAT1:

Допустим, имеется база данных:
CREATE TABLE test (a, b, c);
CREATE INDEX ind ON test(«a»,«b»,«c»)

Данные таблицы test:

Источник

1. Summary

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

2. Background

In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table. (WITHOUT ROWID tables are the exception.)

You can access the ROWID of an SQLite table using one of the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.

The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

3. The AUTOINCREMENT Keyword

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the largest possible ROWID has previously been inserted, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. Only ROWID values from previous transactions that were committed are considered. ROWID values that were rolled back are ignored and can be reused.

SQLite keeps track of the largest ROWID using an internal table named «sqlite_sequence». The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes. The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.

Note that «monotonically increasing» does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

Источник

SQLite: как организовывать таблицы

Apr 10 · 12 min read

Организация данных — все равно, что поддержание порядка на рабочем месте. Хорошо всегда знать, где что находится, и в случае необходимости освобождать больше места для хранения или работы.

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

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

SQLite

SQLite — это реляционная база данных, основанная на языке SQL. «Реляционная» означает, что в базе данных есть таблицы, которые связаны друг с другом через общие атрибуты. Эти таблицы представляют собой двумерные массивы информации, состоящие из строк (или записей) и столбцов (или атрибутов).

Ниже показан пример таблицы с тремя записями и тремя атрибутами, содержащими различные типы данных. Построение примера осуществлено с использованием приложения DB Browser для SQLite.

SQL — это стандартизированный язык. Тем не менее разработчики вольны соблюдать или не соблюдать стандарты до определенной степени. Это зависит от конкретной реализации. Одной из таких реализаций и является SQLite. В отличие от MySQL или PostgreSQL, это более простая библиотека, созданная для использования локально и без необходимости внешних серверов.

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

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

A расшифровывается как Atomicity («Атомарность») и означает, что изменения происходят в целом и их нельзя разделить. C значит Consistency («Согласованность») и предполагает, что состояние базы данных должно оставаться согласованным после того, как результаты транзакции зафиксированы или произошел откат к предыдущему состоянию. I означает Isolation («Изолированность») и указывает на то, что транзакции видны только в текущем сеансе. Наконец, D расшифровывается как Durability («Долговечность») и означает, что изменения должны быть сохранены.

А теперь создадим базу данных с информацией о расах и подрасах из игры Dungeons and Dragons («Подземелья и драконы»). Почему я выбрал ее? Уж очень она отличается от традиционных примеров и в игру эту мы с удовольствием играем с друзьями.

Начнем создание базы данных с использования следующих команд SQLite в командной строке:

Первой командой меняем каталог в файл, в котором будет база данных, а второй создаем базу данных, активировав библиотеку sqlite3 и выбрав для нее название.

Создание таблицы

Таблицы — это структуры, которые удерживают данные, вставленные в реляционные и подобные им базы данных. Вот обобщенное представление кода для создания таблицы:

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

О том, что такое типы данных и ограничения, расскажем позже. А пока достаточно знать, что они существуют и определяются в момент создания таблицы.

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

Чтобы вы понимали, race_name — это название той или иной расы в игре «Подземелья и драконы», например Эльфы, Гномы или Дворфы (будем использовать здесь чуть более экзотические примеры). А в ability_score_increase содержатся оценки способностей, по которым раса по своей природе превосходит другие, например Aasimar более харизматичны, поэтому их харизма выше на два пункта.

Атрибут size — это то, насколько велики в среднем по размеру представители той или иной расы, как правило они либо маленькие, либо средние. Speed — это максимальное расстояние, которое представители расы способны пробежать за один раунд сражения. Languages — это языки, на которых говорит раса, а age — это средняя продолжительность жизни представителей расы.

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

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

А теперь заполним таблицу. Для этого напишем оператор INSERT с тремя записями:

Посмотрите: мы передаем название таблицы, а также ее атрибуты, куда вставляем значения, за которыми идут фактические, желаемые нами значения. Вот данные, сохраняющиеся в races:

sqlite>.read races.txt автоматически создаст таблицу и заполнит ее.

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

Типы данных

Вообще говоря, тип данных TEXT представляет собой текстовую информацию, заключенную в кавычки или отделенную строками. В таблице races атрибут race_name установлен для хранения данных TEXT текстового типа, а строки Aasimar, Kalashtar и Genasi заполняют этот атрибут.

Тип данных INTEGER представляет собой целые числа (положительные или отрицательные) размером от 1 до 8 байтов. Speed — хороший пример того, где использовать целые числа, потому что этот атрибут обозначает количество футов, которые персонаж способен пробежать в раунде сражения.

REAL — это тип данных, содержащий числа длиной более 8 байтов или десятичные и экспоненциальные числа, например 1,5 или 2⁴.

NULL используется для отсутствующей или неизвестной информации. Допустим, нам не удалось найти, на каких языках говорят представители расы Genasi. В этом случае на какое-то время будем сохранять это значение как NULL.

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

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

Ограничения

Ограничения помогают устанавливать границы в атрибутах, значениях по умолчанию или связях между таблицами. Начнем с последнего. Есть два ограничения, которые играют важную роль в том, что называется нормализацией. Это PRIMARY KEY (первичный ключ) и FOREIGN KEY (внешний ключ).

Прежде чем переходить к более подробному рассмотрению этих двух ограничений, создадим новую таблицу subraces, содержащую связь с races, и попробуем понять, что такое нормализация.

Благодаря такой ссылке мы сохраняем информацию о подгруппах, сводя к минимуму повторы этой информации в базе данных. Посмотрите на значения в таблице subraces:

Без нормализации нам пришлось бы создавать таблицу типа races_subraces со следующим кодом:

Источник