SQLite Tabloları ve Sütunları Listeleme: sqlite_schema, PRAGMA

Örnek şema

Farklı nesne türlerinin listede nasıl göründüğünü görebilmek için tablo, indeks, görünüm, tetikleyici, hesaplanan sütun, yabancı anahtar ve bir STRICT, WITHOUT ROWID tablo içeren küçük bir şema kuruyoruz.

CREATE TABLE musteri(
  id     INTEGER PRIMARY KEY,
  eposta TEXT NOT NULL UNIQUE,
  ad     TEXT,
  kayit  TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE siparis(
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  musteri_id INTEGER NOT NULL REFERENCES musteri(id) ON DELETE CASCADE,
  net        INTEGER,
  kdv        INTEGER GENERATED ALWAYS AS (net * 20 / 100) VIRTUAL
);
CREATE INDEX siparis_musteri ON siparis(musteri_id);
CREATE VIEW buyuk_siparis AS SELECT * FROM siparis WHERE net > 1000;
CREATE TABLE ayar(anahtar TEXT PRIMARY KEY, deger ANY) STRICT, WITHOUT ROWID;
CREATE TRIGGER musteri_sil AFTER DELETE ON musteri BEGIN SELECT 1; END;
INSERT INTO musteri(eposta, ad) VALUES ('a@ornek.com', 'Ayşe');
INSERT INTO siparis(musteri_id, net) VALUES (1, 500);

sqlite_schema tablosu

Her SQLite veritabanında, veritabanındaki bütün tabloların, indekslerin, görünümlerin ve tetikleyicilerin tanımını tutan tek bir şema tablosu bulunur. Belgelere göre bu tablonun beş sütunu vardır: type, name, tbl_name, rootpage ve sql. Her nesne için bir satır bulunur; şema tablosunun kendisi için satır yoktur.

SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;
-- çıktı:
-- index|siparis_musteri|siparis
-- index|sqlite_autoindex_musteri_1|musteri
-- table|ayar|ayar
-- table|musteri|musteri
-- table|siparis|siparis
-- table|sqlite_sequence|sqlite_sequence
-- trigger|musteri_sil|musteri
-- view|buyuk_siparis|buyuk_siparis

SELECT name FROM sqlite_schema
 WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
 ORDER BY name;
-- çıktı:
-- ayar
-- musteri
-- siparis

Listede tanımlamadığımız iki nesne var. sqlite_autoindex_musteri_1, eposta sütunundaki UNIQUE kısıtını uygulamak için SQLite'ın kendiliğinden oluşturduğu bir indekstir; belgelere göre bu adlar sqlite_autoindex_TABLO_N biçimindedir ve N her kısıtta bir artar. sqlite_sequence ise AUTOINCREMENT kullanan tablolar için SQLite'ın tuttuğu iç tablodur. Kullanıcı tablolarını listelerken bu iç nesneleri name NOT LIKE 'sqlite_%' koşuluyla dışarıda bırakmak yaygın bir yöntemdir; belgelerde .tables komutunun karşılığı olarak verilen sorgu da bu koşulu kullanır.

Eski ad: sqlite_master

Şema tablosuna her zaman sqlite_schema adıyla başvurulabilir. Tarihsel uyumluluk için sqlite_master adı da her yerde çalışır; eski kod örneklerinde ve kütüphanelerde çoğunlukla bu adı görürsünüz. sqlite_temp_schema ve sqlite_temp_master ise yalnızca bağlantıya ait geçici (TEMP) veritabanı için geçerlidir. Yeni yazdığınız sorgularda sqlite_schema adını kullanın.

Komut satırı kısayolları: .tables, .schema, .indexes

sqlite3 aracı şemaya bakmak için birkaç kısayol sunar. Belgeler bunların SQL ile yapılamayacak bir şey yapmadığını, yalnızca kolaylık sağladığını belirtir. .tables tablo ve görünüm adlarını sütunlar hâlinde dizer, .schema nesnelerin CREATE ifadelerini gösterir, .indexes ise indeksleri listeler; ikisine de bir tablo adı vererek çıktıyı daraltabilirsiniz.

.tables
-- çıktı:
-- ayar           buyuk_siparis  musteri        siparis

.schema buyuk_siparis
-- çıktı:
-- CREATE VIEW buyuk_siparis AS SELECT * FROM siparis WHERE net > 1000
-- /* buyuk_siparis(id,musteri_id,net,kdv) */;

.indexes musteri
-- çıktı: sqlite_autoindex_musteri_1

.tables ile .schema, yalnızca ana veritabanının değil bağlı (ATTACH) bütün veritabanlarının şemasını gösterir; yalnızca birine bakmak isterseniz .schema main.* gibi bir desen verin. .schema --indent uzun CREATE ifadelerini okunur biçimde girintiler. .fullschema ise şemaya ek olarak ANALYZE istatistik tablolarının içeriğini de döker; belgeler, sorgu planlayıcı ile ilgili bir sorunu bildirirken bu çıktının istendiğini, ancak istatistik tablolarında gerçek indeks değerlerinden örnekler bulunabileceği için gizli veriler içeren bir veritabanında bu çıktıyı herkese açık bir yerde paylaşmamanız gerektiğini hatırlatır.

.schema çıktısındaki /* ... */ satırı, aracın görünümün sütunlarını göstermek için eklediği bir yorumdur; şema tablosunda saklanan metnin parçası değildir. .tables çıktısındaki sütun hizası da araca ve sürüme göre değişebilir. Bu kısayolları betiklerde ayrıştırmak yerine aşağıdaki SQL yöntemlerini kullanmak daha sağlamdır.

sql sütunundaki tanımlar

sql sütunu, nesneyi yeniden oluşturmak için çalıştırılabilecek CREATE ifadesini tutar. Metin çoğunlukla sizin yazdığınız ifadenin kopyasıdır, ancak belgelere göre bazı düzeltmeler uygulanır: baştaki CREATE TABLE gibi anahtar sözcükler büyük harfe çevrilir, TEMP sözcüğü ve şema adı öneki kaldırılır, baştaki boşluklar silinir. Sonradan çalıştırılan ALTER TABLE ifadeleri de bu metni günceller. Kısıtlar için otomatik oluşturulan indekslerde sql sütunu NULL'dır.

SELECT sql FROM sqlite_schema WHERE name = 'siparis_musteri';
-- çıktı: CREATE INDEX siparis_musteri ON siparis(musteri_id)

SELECT name, sql IS NULL FROM sqlite_schema WHERE type = 'index';
-- çıktı:
-- sqlite_autoindex_musteri_1|1
-- siparis_musteri|0

Bir veritabanının şemasını yedeklemek veya başka bir dosyada aynısını kurmak için bu metinleri okuyabilirsiniz, ancak şema ile birlikte veriyi de taşımak istiyorsanız .dump komutu veya SQLite yedekleme yazısındaki yöntemler daha uygundur.

Sütunları listelemek: table_info ve table_xinfo

PRAGMA table_info(tablo) bir tablonun normal sütunlarının her biri için bir satır döndürür. Belgelere göre sonuçta sütunun adı (name), tanımlanmış tipi (type, belirtilmemişse boş), NULL olup olamayacağı (notnull), varsayılan değeri (dflt_value) ve birincil anahtarın kaçıncı sütunu olduğu (pk, anahtarda değilse 0) yer alır. table_info bir görünüm adıyla da çalışır.

.headers on
PRAGMA table_info(musteri);
-- çıktı:
-- cid|name|type|notnull|dflt_value|pk
-- 0|id|INTEGER|0||1
-- 1|eposta|TEXT|1||0
-- 2|ad|TEXT|0||0
-- 3|kayit|TEXT|0|CURRENT_TIMESTAMP|0

PRAGMA table_xinfo(siparis);
-- çıktı:
-- cid|name|type|notnull|dflt_value|pk|hidden
-- 0|id|INTEGER|0||1|0
-- 1|musteri_id|INTEGER|1||0|0
-- 2|net|INTEGER|0||0|0
-- 3|kdv|INTEGER|0||0|2
.headers off

İki pragmanın farkı hesaplanan sütundadır. table_info, hesaplanan (generated) ve gizli sütunları göstermez; bu yüzden siparis tablosunun kdv sütununu görmek için table_xinfo kullandık. table_xinfo çıktısına eklenen hidden sütunu normal sütunlarda 0, sanal tablolardaki gizli sütunlarda 1, hesaplanan sütunlarda 2 veya 3 değerini alır. Belgeler ayrıca cid sütununa "bu sonuç kümesi içindeki sıra" dışında bir anlam yüklenmemesini söyler. Hesaplanan sütunlar için SQLite generated columns yazısına bakın.

Tabloların özellikleri: table_list

SQLite 3.37.0 sürüm notu ile gelen PRAGMA table_list, her tablo ve görünüm için bir satır döndürür ve şema tablosunda doğrudan görünmeyen bilgileri de içerir: nesnenin hangi şemada olduğu, türü (table, view, gölge tablolar için shadow, sanal tablolar için virtual), hesaplanan ve gizli sütunlar dahil sütun sayısı, WITHOUT ROWID olup olmadığı (wr) ve STRICT olup olmadığı.

.headers on
PRAGMA table_list;
-- çıktı:
-- schema|name|type|ncol|wr|strict
-- main|ayar|table|2|1|1
-- main|buyuk_siparis|view|4|0|0
-- main|sqlite_schema|table|5|0|0
-- main|musteri|table|4|0|0
-- main|sqlite_sequence|table|2|0|0
-- main|siparis|table|4|0|0
-- temp|sqlite_temp_schema|table|5|0|0
.headers off

Varsayılan olarak bütün şemalardaki tablolar listelenir; yalnızca birine bakmak için PRAGMA main.table_list, tek bir tablo için PRAGMA table_list(tablo) yazabilirsiniz. Çıktıda şema tablolarının kendisinin de yer aldığına dikkat edin. Belgeler bu pragmanın ileride yeni sütunlar kazanabileceğini belirtir; bu yüzden uygulama kodunda sütunlara konumla değil adla erişin. STRICT ve WITHOUT ROWID tablolar için SQLite STRICT tablolar ve WITHOUT ROWID tablolar yazılarına bakabilirsiniz.

İndeksler ve yabancı anahtarlar

PRAGMA index_list(tablo) bir tablonun indekslerini listeler. Sonuçtaki unique sütunu indeksin benzersiz olup olmadığını, origin sütunu ise nereden geldiğini gösterir: c bir CREATE INDEX ifadesini, u bir UNIQUE kısıtını, pk bir PRIMARY KEY kısıtını ifade eder. partial sütunu kısmi indeksleri işaretler. PRAGMA index_info(indeks) indeksin anahtar sütunlarını, index_xinfo ise satıra geri dönmek için kullanılan yardımcı sütunlar dahil bütün sütunlarını verir.

.headers on
PRAGMA index_list(musteri);
-- çıktı:
-- seq|name|unique|origin|partial
-- 0|sqlite_autoindex_musteri_1|1|u|0

PRAGMA index_info(sqlite_autoindex_musteri_1);
-- çıktı:
-- seqno|cid|name
-- 0|1|eposta

PRAGMA foreign_key_list(siparis);
-- çıktı:
-- id|seq|table|from|to|on_update|on_delete|match
-- 0|0|musteri|musteri_id|id|NO ACTION|CASCADE|NONE
.headers off

PRAGMA foreign_key_list(tablo) tablonun REFERENCES tanımlarını, hangi sütunun hangi tablonun hangi sütununa baktığını ve silme ile güncelleme eylemlerini listeler. Yabancı anahtar denetiminin bağlantı başına açılması gerektiği ve eylemlerin anlamı SQLite foreign key yazısında anlatılıyor.

PRAGMA'ları SELECT içinde kullanmak

Sonuç döndüren ve yan etkisi olmayan PRAGMA komutları, adlarının önüne pragma_ eklenerek tablo değerli fonksiyon olarak SELECT içinde kullanılabilir. Bu biçimin üstünlüğü, sonucu süzebilmeniz, yalnızca istediğiniz sütunları seçebilmeniz ve başka tablolarla birleştirebilmenizdir. Belgelere göre bu özellik 3.16.0 sürümünden beri vardır.

SELECT name, type FROM pragma_table_info('musteri') WHERE pk = 0 ORDER BY cid;
-- çıktı:
-- eposta|TEXT
-- ad|TEXT
-- kayit|TEXT

SELECT m.name AS tablo, p.name AS sutun, p.type
  FROM sqlite_schema AS m
  JOIN pragma_table_info(m.name) AS p
 WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
 ORDER BY 1, p.cid;
-- çıktı:
-- ayar|anahtar|TEXT
-- ayar|deger|ANY
-- musteri|id|INTEGER
-- musteri|eposta|TEXT
-- musteri|ad|TEXT
-- musteri|kayit|TEXT
-- siparis|id|INTEGER
-- siparis|musteri_id|INTEGER
-- siparis|net|INTEGER

İkinci sorgu, şema tablosundaki her tabloyu kendi pragma_table_info sonucuyla birleştirerek bütün veritabanının sütun listesini tek sorguda çıkarıyor. Başka veritabanlarındaki information_schema görünümlerine en yakın karşılık budur; belgeler bu fonksiyonlarla bir information_schema görünüm kümesi kurulabileceğini de not eder. Aynı yöntemle pragma_index_list ve pragma_index_info birleştirilerek bütün indekslenmiş sütunlar listelenebilir.

Bu tablo veya sütun var mı?

Göç betiklerinde ve uygulama başlangıcında sık sorulan iki soru, bir tablonun veya sütunun var olup olmadığıdır. İkisi de yukarıdaki araçlarla tek satırda yanıtlanır. Var olmayan bir tablo adı verildiğinde pragma_table_info hata vermez, yalnızca boş sonuç döndürür; bu da onu bir varlık denetimi için elverişli kılar.

SELECT EXISTS (SELECT 1 FROM sqlite_schema WHERE type = 'table' AND name = 'musteri');
-- çıktı: 1

SELECT count(*) FROM pragma_table_info('yok_boyle_tablo');
-- çıktı: 0

SELECT count(*) FROM pragma_table_info('musteri') WHERE name = 'telefon';
-- çıktı: 0

Tablo oluşturmadan önce varlığını denetlemek yerine çoğu zaman CREATE TABLE IF NOT EXISTS yazmak yeterlidir; bkz. CREATE TABLE ve kısıtlamalar. Sütun ekleme kararını ise bu denetimle vermek yerine şema sürümünü tutmak daha güvenilirdir; bu yöntem SQLite şema göçü ve user_version yazısında anlatılıyor.

Geçici tablolar ve bağlı veritabanları

Her bağlantının ana veritabanı (main) yanında geçici nesneler için bir temp veritabanı vardır ve onun da kendi şema tablosu bulunur. CREATE TEMP TABLE ile oluşturduğunuz tablolar ana şema tablosunda değil, temp.sqlite_schema içinde görünür. PRAGMA database_list bağlantıda açık olan bütün veritabanlarını, adları ve dosya yollarıyla listeler; bellekteki veritabanlarında dosya yolu boştur.

SELECT count(*) FROM sqlite_master;
-- çıktı: 8

CREATE TEMP TABLE gecici(x);
SELECT name FROM temp.sqlite_schema;
-- çıktı: gecici

PRAGMA database_list;
-- çıktı:
-- 0|main|
-- 1|temp|

ATTACH ile eklenen veritabanlarının şemasına da ad.sqlite_schema biçiminde erişirsiniz; bu konu SQLite ATTACH DATABASE yazısında. Şema bilgisi döndüren pragmaların listesi ve diğer ayarlar için SQLite PRAGMA komutları yazısına bakabilirsiniz.

Hangi yöntemi ne zaman kullanmalı?

Sık Sorulan Sorular

SHOW TABLES veya DESCRIBE komutu var mı?

Hayır, bunlar MySQL komutlarıdır. SQLite'ta karşılıkları komut satırında .tables ve .schema tablo, SQL ile ise sqlite_schema sorgusu ve PRAGMA table_info(tablo)'dur.

Uygulama kodundan tablo listesini nasıl alırım?

Nokta komutları yalnızca sqlite3 aracında çalışır; programlama dili bağlayıcıları bunları tanımaz. Kodda SELECT name FROM sqlite_schema WHERE type = 'table' sorgusunu sıradan bir sorgu gibi çalıştırın. Dil bağlayıcılarının kullanımı için Python sqlite3, Node.js sqlite modülü ve PHP PDO yazılarına bakın.

sqlite_master ile sqlite_schema aynı şey mi?

Evet. sqlite_master aynı tablonun tarihsel adıdır ve her yerde çalışmaya devam eder. Yeni kodda sqlite_schema adını tercih edin.

Bir veritabanının sütun listesini toplu olarak nasıl dışa aktarırım?

Yukarıdaki sqlite_schema ile pragma_table_info birleştirmesini .mode csv ile çalıştırıp çıktıyı bir dosyaya yönlendirebilirsiniz. CSV çıktısının ayrıntıları SQLite CSV import ve export yazısında.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · CLI nokta komutları · PRAGMA komutları · CREATE TABLE · ALTER TABLE · Şema göçü ve user_version