murat genç

SQLite da bir tablo hakkında bilgi alınmak istendiğinde

PRAGMA table_info(tabloAdi)

sorgusu size o tablodaki column ların adını, veri tipini, null değer alıp alamayacağını, default değerini ve primary key olup olmadığını verir.

Karşılaştırmalar, bir tablonuz var ve kişilere ait ad ve telefon bilgilerini tutuyorsunuz ve kişi ad alanını UNIQUE olarak işaretlediniz. Peki hiç şunu düşündünüz mü? Kullanıcı Murat Genç adında bir kayıt girdi ve daha sonra başka biri MURAT GENÇ diye bir kayıt girdi. UNIQUE yaptığınız alan acaba böyle bir kayıt girişine izin verecekmidir. Cevap evet. Peki neden; SQLite da bir alana girilen veri küçük/büyük harfe duyarlı olarak eklenir. Yani SQLite için a ve A farklı şeylerdir. Peki bunu engellemenin bir yolu var mı? Veri bütünlüğü kavramı dahilinde alan bazında verinin bütünlüğünü korumak için alan tanımı yapılırken küçük/büyük harfe göre duyarlı olup olmamasını belirtebiliriz. COLLATE anahtar kelimesi ile bunu gerçekleştirebiliriz. SQLite 3 tip gömülü karşılaştırma tipi vardır. Biri default olarak ayarlı olan BINARY, diğeri küçük/büyük harfe göre duyarlılığını kaldıran NOCASE üçüncüsü ise BINARY karşılaştırmanın tersi olan REVERSE.
Sonuç olarak SQLite da tanımlanan bir alanda büyük/küçük harf duyarlılığını kaldırmak istiyorsak alan tanımında COLLATE NOCASE ifadesini kullanmamız yeterlidir.

CREATE TABLE Kisiler (id INTEGER PRIMARY KEY,ad TEXT NOT NULL COLLATE NOCASE UNIQUE)

SQLite bildiğimiz gibi Foreign Key desteği yok buda bize veri bütünlüğünü sağlamada zorluklar yaşatmaktadır. Fakat bunuda yapayda olsa sağlamak mümkün. Trigger lar yardımıyla SQLite veritabanımıza Foreign Key desteği sağlatabiliriz. Örnek olarak 2 tablo oluşturuyoruz. Basit olarak 2 tablo birinde blog entry lerini tutacağız diğerindede kategorileri. Entry ve Category tablomuz arasında ir ilişki var fakat bunu SQLite tanımlayamıyoruz.


İlk olarak Entry tablosuna yeni bir kayıt eklendiğinde eklenilmeye çalışılan kategorinin Category tablosunda olup olmadığını kontrol edelim. Olmayan bir kategoriye ait entry eklemek istemeyiz heralde :) .  Yapmamız gereken tek şey sözle ifade ettiğimiz bu koşulu SQLite a bir INSERT TRIGGER ile belirtmek.

CREATE TRIGGER EntryInsertTrigger
BEFORE INSERT ON Entry
BEGIN
      SELECT CASE
           WHEN(SELECT CategoryID FROM Category WHERE CategoryID=NEW.CategoryID) IS NULL
           THEN RAISE(ABORT,'Eklemeye çalıştığınız kategori mevcut değil')
       END;
END;

Trigger ile Entry tablosuna bir kayıt eklenmeden hemen önce eklenmeye çalışılan CategoryID bir alt sorgu yardımıyla Category tablosunda olup olmadığı kontrol ediliyor. Eğer yoksa NULL döndürülüyor ve bir hata fırlatılıyor. Böylece Entry tablomuza olmayan bir kategoriye ait entry girişi yapılmasını engelledik. Ya UPDATE işlemi:

CREATE TRIGGER EntryUpdateTrigger
BEFORE UPDATE OF CategoryID ON Entry
BEGIN
      SELECT CASE
           WHEN(SELECT CategoryID FROM Category WHERE CategoryID=NEW.CategoryID) IS NULL
           THEN RAISE(ABORT,'Eklemeye çalıştığınız kategori mevcut değil')
       END;
END;

Evet sadece CategoryID güncellenmeye çalışıldığında devreye girecek bir trigger tanımlayıp INSERT trigger ında yaptığımız işlemi aynen tekrarlıyoruz. Son olarak bütünlüğü sağlamamız için bir şeye daha ihtiyacımız var. Entry tablosunda bir kaydın silinmesi Category tablosu üzerinde hiçbir etki göstermeyecektir ki zaten böyle birşey de istemiyoruz. Fakat ya Category tablosunda bir kayıt silinmeye çalışılırsa ne olacak. İşte eğer veri bütünlüğünü sağlamamışsak artık olmayan bir kategoriye ait entry lerimiz olacaktır. O zaman şöyle birşey yapsak. Category tablosundan bir kayıt silinirken o kategorinin Entry tablosunda kullanılıp kullanmadığına baksak zaten eğer kullanılmamışsa silip silmememiz bir anlam ifade etmeyecektir fakat mevcut bir kayıt varsa sorun çıkacaktır. Tam bu noktada devreye girip bizde sorunu çözeriz.

CREATE TRIGGER CategoryDeleteTrigger
BEFORE DELETE ON Category
BEGIN
      SELECT CASE
           WHEN(SELECT COUNT(CategoryID) FROM Entry WHERE CategoryID=OLD.CategoryID) > 0
           THEN RAISE(ABORT,'Silmeye çalıştığınız kategori Entry tablosunda mevcut.')
       END;
END;

ve işte bu kadar küçük, şirin, sevimli ve bi o kadarda hızlı veritabanımıza trigger tabanlı foreign key desteği sağladık.Hiçde zor değilmiş değil mi ? :D

MySql den SQLite a veri çeken küçük tool adayımızda sorunlar vardı. Bu sorunlardan biri de MySql den gelen BLOB verinin çekilmesinde idi. İlk problem çözüldü darısı diğerlerinin başına...

test için BLOB veri tutan bir alan tanımlanıyor.

alana veri olarak bir image ekleniyor.

ve bizim mini minicik tool adayımız açılıp önce mysql server a bağlanılıyor.Daha sonra kullanılacak DB listeden seçilip Export tuşuna basılıyor.

oluşacak .db3  uzantılı SQLite dosyamızı kaydetceğimiz yeri veriyoruz ve hepsi bu kadar.Önce MySQL de belirlenen DB nin tüm şeması çıkarılıyor ve SQLite a uyarlanıp SQLite DB si hazırlanıyor.Daha sonra oluşan DB ye MySQL deki tüm veriler aktarılıyor.

ve şimdi gerçekleri görme zamanı. BLOB dışındaki alanlarda sorun yaşamamıştık. Önce SQLite için hazırlanmış GUI lerden biri ile oluşan .db3 uzantılı dosyayı açıyoruz.

ve sonuç ilk bug temizlendi.

Not:Sanırım eklenen resim ile sonuçta çıktı olarak verilen resim arasında küçük bir fark var dimi. Evet alt kısmında okunamayan pixeller var gibi
ama sorun okumada yani bizim tool cuğumuzda değil resmi eklediğimiz MySQL GUI sinde :D .

Küçük uygulamalarda SQLite a geçiş kararı verdiğim günden itibaren karşılaştığım en büyük sorun mevcut verileri SQLite aktarmak oldu. Access ve Ms SQL Server için birkaç ücretsiz araç mevcut fakat ne Postgre nede MySql için adam akıllı yada ücretsiz bir tool bulamadım.Herşeyi başkalarından beklemekde hata olurdu zaten. İş başa düşüncede madem yapıyoruz popüler tüm db leri desteklesin bari dedik demesine de tembellikten hala console da test için yazdığım tool la bu işlemi gerçekleştiriyorum. Neyse ki biran azim geldi ve en azından MySql için bir grafik arayüz yapıyım dememle zaten hazır olan framework un bir windows application a monte edilmesi sadece 1-2 dakika aldı (çok basit bir arayüz olsa da iş görüyor :D ). Tabi ilk iş ihtiyacı olan insanların kullanabilmesi için ücretsiz dağıtmaktı ama taki derinlemesine testlerde birkaç bug bulana kadar. Küçük çözümlerde örneğin bu blog gibi fazla nesne ihtiyacı duymayan db ler için sorun yok fakat daha büyük çözümlerde özellikle SQLite da henüz desteklenmeyen özellikler yüzünden sorunlar yaşanmaktadır. Umarım bunlara en kısa zamanda çözüm bulabilirsem ilk beta versiyonunu yayınlayacağım.

SQLite da geçerli olan tarih formatları

• YYYY-MM-DD
• YYYY-MM-DD HH:MM
• YYYY-MM-DD HH:MM:SS
• YYYY-MM-DD HH:MM:SS.SSS
• HH:MM
• HH:MM:SS
• HH:MM:SS.SSS
• now
• DDDD.DDDD

Eğer SQLite o an ki saati kaydetmek istiyorsanız now fonksiyonunu kullanmanız yeterli.

Formatla yapmak için ise kullanılması gereken fonksiyon C tabanlı strftime() dır.

SELECT strftime('%m/%d/%Y', '2004-10-31');

örnekte standart SQLite tarih formatı strftime fonksiyonu kullanılarak ay-gün-yıl formatına çevrilmiştir.

%d	ayın gün, 01-31
%H	saat, 00-23
%j	yılın günü, 001-366
%J	Julian günü, DDDD.DDDD
%m	ay, 00-12
%M	dakika, 00-59
%s	Seconds since 1970-01-01 (unix epoch)
%S	Seconds, 00-59
%w	haftanın günü, 0-6 (0 Pazar)
%W	hafta, 01-53
%Y	yıl, YYYY
%%	% sembol

Fonksiyonla formatlama dışında tarih ekleme çıkarma işlemleride yapılabilir.

SELECT strftime('%Y-%m-%d', '2007-10-31', '+7 days')
2007-11-07
SELECT strftime('%H:%M', '22:00', '+12 hours')
10:00
SELECT strftime('%Y-%m-%d %H:%M:%S','2007-01-01 00:00:00', '-1 second', '+1 year')
2007-12-31 23:59:59