Метаданные

Мне нужно получить имена столбцов и их таблицы в базе данных 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