İleri Seviye SQL: CTE ile IP Adresi Aralığı Oluşturmak
# SQL’de CTE ile IP Adresi Aralığı Oluşturmak
Diyelim ki başlangıç ve bitiş IP adresleri belli olan aralık için IP adres listesi oluşturmak istiyoruz. Standart bir WHILE döngüsü bu ihtiyacınızı karşılayacaktır, ancak bu işlemi CTE ile hem daha performanslı bir hale getirebilir, hem de SELECT ifadesi içinde SQL’in tüm sorgulama nimetlerinden yararlanarak zenginleştirebiliriz.
CTE ile bir çok işlemi gerçekleştirebiliriz ancak bunlardan en önemlisi, recursive özelliğidir. CTE yardımıyla SQL tarafında da recursive sorgularımızı hazırlayabiliriz.
Peki, nedir bu CTE? : İleri Seviye SQL : CTE (Common Table Expressions)
Dipnot: CTE ile Recursive işlemler çerçevesinde yaptığımız her sorgu “başlama noktasında ki satır sayısı” + “100 biriktirme satırı” ile sınırlıdır. Bu sayıyı geçtiğinizde SQL hata verecektir. Varsayılan limiti kaldırmak içinse, sorgunun en sonuna “OPTION (MAXRECURSION 0)” ifadesini eklememiz gerekmektedir.
# IP Adresi Dönüştürme ve Test Kodlarını Yazmak
Senaryoya göre, ‘127.0.0.1’ ve ‘127.0.1.30’ arasında ki tüm IP adreslerini satır olarak oluşturmak istiyoruz.
Başlangıç ve test kodlarımızı yazmaya başlayalım.
Biraz sorgudan bahsetmem gerekirse;
- @vStartIPAddress ve @vEndIPAddress değişkenleri ile string olarak başlangıç ve bitiş IP adresslerini hafıza aldık.
- Standart bir IPv4 adresinin 4 byte’ten oluştuğunuzu biliyoruz, bunun içinde SQL de buna denk gelen BINARY(4) türünde, başlangıç ve bitiş IP adresleri için hafızada tutmak üzere değişkenleri oluşturduk
- Daha sonrasında ise, SQL’in string_split fonksiyonu yardımıyla “.” lara göre parçalama yaparak her byte-decimal değerine ulaştık ve sonrasında bu değerleri 1 byte’lık binary değerlere dönüştürüp son aşamada ise 4 binary değeri toplayarak sonuç değerimize ulaştık.
- Son kısımda ki test kodlarımız ise, yaptığımız işlemlerin hem doğruluğunu bize göstermektedir, hem de BINARY değeri DECIMAL’e ve BINARY değerinin her bir Byte’ını DECIMAL’e sonunda da ulaştığımız DECIMAL değerleri nasıl STRING IP Adresine çevirebildiğimizi görmüş olduk.
Sonuç ise aşağıda ki gibi olacaktır;
# IP Adresi Aralığı Oluşturmak
Test kodlarımızdan sonra ise, senaryomuza göre CTE ile nasıl bir sorgu hazırlamamız gerekirdi ?
Biraz sorgudan bahsetmem gerekirse;
UNION ALL’dan önceki sorgumuz, başlama noktamız olarak düşünebilir. Yani lk satırımız.
UNION ALL’dan sonra ki sorgumuz ise, biriktirme noktamız olarak düşünebilir. Yani ilk satırımızdan sonra nasıl bir artış ile diğer satırlarımızı oluşturacağımızı belirtiyoruz. Biriktirme noktamızda ki “WHERE” koşullandırmamızda ise, bu işleme ne kadar devam edeceğimizi, yani hangi tarihe kadar devam edeceğimizi belirtmiş oluyoruz.
Sunum noktamızda ise, IP adreslerinin her biri için olan decimal değerleri öncelikle BINARY değere çeviriyoruz, sonrasında ise BINARY değerlerin her bir Byte’ını tekrar DECIMAL’e çevirdikten sonra birleştirerek IP adresimizin STRING halini oluşturuyoruz.
Sonuç ise aşağıda ki gibi olacaktır;
Artık bu bilgiler ışığında, kendi veri üretme ihtiyacınıza göre ip adresleri oluşturabilirsiniz :)
# Kazanımlar
- CTE’nin tek bir DML ifadesi süresince var olduğunu ve genellikle recursive işlemlerde kullanıldığını,
- View, temp table ve table-value constructora benzediğini ve alanların deklare edilmemesi gerektiğini,
- Birden fazla CTE ifadesi kullanılabildiğini,
- Recursive işlemlerde satır limitinin olduğunu,
- CTE’nin Recursive özelliğiyle, SQL’in de byte ve binary dönüşüm fonksiyonları ile birlikte IP Adresi üretebildiğini öğrenmiş olduk.
# Sosyal Medya Hesaplarım
- LinkedIn: Karcan Ozbal | LinkedIn
- Github: karcan (Karcan Ozbal) (github.com)
- Patreon: Karcan is creating little extensions for make your life funny :)
Konu istekleri ve makale ile ilgili tartışmak için yoruma bekliyorum.
