Метаданные
Мне нужно получить имена столбцов и их таблицы в базе данных SQLite. Мне нужен набор результатов с двумя столбцами: table_name | column_name.
В MySQL я могу получить эту информацию с SQL-запросом в базе данных INFORMATION_SCHEMA. Однако SQLite предлагает таблицу sqlite_master:
sqlite> create table students (id INTEGER, name TEXT);
sqlite> select * from sqlite_master;
table|students|students|2|CREATE TABLE students (id INTEGER, name TEXT)
который приводит к строительному запросу DDL (CREATE TABLE), что не помогает мне, и мне нужно проанализировать это, чтобы получить соответствующую информацию.
Мне нужно получить список таблиц и объединить их со столбцами или просто получить столбцы вместе с столбцом имени таблицы. Поэтому PRAGMA table_info(TABLENAME) не работает для меня, так как у меня нет имени таблицы. Я хочу получить все метаданные столбца в базе данных.
Есть ли лучший способ получить эту информацию как результат, запросив базу данных?
Ответы
Ответ 1
В основном вы назвали решение в своем вопросе.
Чтобы получить список таблиц (и представлений), выполните запрос sqlite_master как в
SELECT name, sql FROM sqlite_master
WHERE type='table'
ORDER BY name;
(см. часто задаваемые вопросы SQLite)
Чтобы получить информацию о столбцах в конкретной таблице, используйте PRAGMA table_info(table-name);, как описано в документации SQLite PRAGMA.
Я не знаю, как получить tablename | имя столбца, возвращаемое в результате одного запроса. Я не верю, что SQLite поддерживает это. Лучше всего использовать эти два метода вместе, чтобы вернуть информацию, которую вы ищете, - сначала получите список таблиц с помощью sqlite_master, а затем проведите их по ним, чтобы получить их столбцы с помощью PRAGMA table_info().
Ответ 2
Существуют команды ".tables" и ".schema [table_name]", которые дают вид разделенной версии для результата, который вы получаете от "select * from sqlite_master;"
Существует также "pragma table_info ([table_name]); чтобы получить лучший результат для синтаксического анализа вместо строительного запроса:
sqlite> .tables
students
sqlite> .schema students
create table students(id INTEGER, name TEXT);
sqlite> pragma table_info(students);
0|id|INTEGER|0||0
1|name|TEXT|0||0
Надеюсь, это помогает в некоторой степени...
Ответ 3
FYI, если вы используете .Net, вы можете использовать метод DbConnection.GetSchema для извлечения информации, которая обычно находится в INFORMATION_SCHEMA. Если у вас есть уровень абстракции, у вас может быть один и тот же код для всех типов баз данных (обратите внимание, что MySQL, похоже, swich 1-й 2 аргумента массива ограничений).
Ответ 4
Еще один полезный трюк - сначала получить все имена таблиц из sqlite_master.
Затем для каждого из них запустите запрос "выберите * из t, где 1 = 0". Если вы проанализируете структуру результирующего запроса - зависит от того, на каком языке /api вы его вызываете, вы получаете богатую структуру, описывающую столбцы.
В python
c = ...db.cursor()
c.execute("select * from t where 1=0");
c.fetchall();
print c.description;
Юрай
PS. Я привык использовать 'where 1 = 0', потому что синтаксис ограничения записи, похоже, меняется от db до db. Кроме того, хорошая база данных оптимизирует это предложение всегда-ложное.
Тот же эффект в SQLite достигается с помощью "limit 0".
Ответ 5
Попробуйте этот синтаксический анализатор таблицы sqlite, я внедрил парсер sqlite table для анализа определений таблиц в PHP.
Он возвращает полные определения (уникальный, первичный ключ, тип, точность, а не нуль, ссылки, ограничения таблицы... и т.д.)
https://github.com/maghead/sqlite-parser
Синтаксис следует за синтаксисом оператора sqlite create table: http://www.sqlite.org/lang_createtable.html
