Kapsamlı SQL Check Constraint Oluşturma İhtiyacı
Kullanıcı Oyu:
/ 0
- Ayrıntılar
- Üst Kategori: C#
- Kategori: Problemler ve Çözümler C#
- Perşembe, 21 Nisan 2011 11:51 tarihinde oluşturuldu
- Super User tarafından yazıldı.
- Gösterim: 407
PROBLEM:
Check Constraint sayesinde bir veri alanına giriş veya güncelleme yaparken, verinin hangi kurallara uyması gerektiğini belirleyebiliyoruz. Örneğin bitiş saati sütununa saat verisi girerken, başlangıç saati sütunundaki saat verisinden daha ilerideki bir zamanı göstermesi şartını koymak gibi. Ancak benim ihtiyacım bununla bitmiyor.
Giriş yapacağım başlangıç ve bitiş saati, tablodaki diğer başlangıç ve bitiş saatleri arasında kalan zamanla çakışmamak zorunda! Bunu çözdüğümüzde şu tarz ihtiyaçları giderebiliyor olacağız; bir toplantı odasında aynı anda birden fazla toplantı yapılamaz, doktor aynı zaman aralığı için birden fazla hastaya randevu veremez, bir sınıfta aynı anda birden fazla eğitim etkinliği düzenlenemez.
ÇÖZÜM:
Ben kendi ihtiyacımı giderecek kadarını yaptım http://www.tek-tips.com/viewthread.cfm?qid=1451066&page=9 sayfasındaki formül sayesinde, formülü kolayca anlamak işin aşağıdaki grafik çok faydalı oldu:
Bunu anlamak için yukarıdaki şekli bir kağıda çizin. Dikey kesikli iki çizgi sizin yeni giriş yapmak istediğiniz zaman aralığı. Örneklerde tarihsaat üzerinden anlatılmış ama bana sadece saat aralığı lazım olduğundan saate göre kod ve veri alanları kullandım.
O iki dikey çizgiye kesişen veritabanındaki satırlarlar çakışma var anlamına geliyor. Tamamı iki çizginin sağ veya sol dışında kalan başlangıç bitiş aralığı veri satırları çakışmayanları temsil ediyor.
Kısaca benim giriş yapmak istediğim zaman aralığı, veritabanı tablosundaki satırlarda yazan başlangıç ve bitiş aralıklarının hiçbiriyle kesişmiyorsa bunu tespit edebiliyorum. Böylece çakışma ve çakışmama durumları için programın gidişatını ayrı ayrı düzenleyebiliyorum.
LINQ kodu şu şekilde oluşturdum, bu yazı biraz karışık oldu, sorusu olan olursa yardım etmeye çalışırım.
TimeSpan bat = TimeSpan.Parse(DropDownList2.SelectedValue.ToString() + ":" + DropDownList3.SelectedValue.ToString()); //http://stackoverflow.com/questions/26760/c-parse-string-to-timespan
TimeSpan bit = TimeSpan.Parse(DropDownList5.SelectedValue.ToString() + ":" + DropDownList4.SelectedValue.ToString());
var qtest1 = (from c in db.tblZamans
where c.idgun == gun && c.idkullanici == kullaniciID && (c.saatBitis >= bat && c.saatBaslangic <= bit)
select c);
int sonuc1 = qtest1.Count();
if (sonuc1 == 0)
{
Response.Write("Kayıt yapılabilir.");
}
else
{
Response.Write("Çakışan zaman aralığı sayısı: " + sonuc1.ToString() + "<br />");
}
Benim çözümüm bu kadar.
Aşağıya yazdıklarımı ziyan olmasınlar diye silmedim.
Özellikle stackoverflow sitesindeki kod ve linkler ufkumu açtı ve çok şey öğretti ancak orada bahsedilen, örneğin bir gün içerisindeki saat aralıklarını sıraya sokup, aralarda boşluk vakitler kalmış mı gibi bir konu. Aslında benim işimi çözüyor ancak fazlasıyla. Yani bunu oturup kavrayıp kodu değiştirmek falan gerçekten işkence. Ben 2 gündür buna takıldım, sql üzerinde denedim, tablo halinde çakışan çakışmayan vs. zaman aralıklarını raporlayabildim aşağıdaki kodla ancak bu dediğim gibi epey karışık takip etmesive sql üzerinde denediğim sorguyu fonksiyon halinde c# programımda denemeye kalktığımda beklediğim sonucu alamadım iyice kafam karıştı. Sonra anladım ki bu kod veritabanında çakışan saatlerin birleşimini alıp yekpare hale getirilmişini tablo olarak sunuyo. Neyse. Ama ciddi ciddi uğraşacaksanız bir gün vakit ayırıp mantığını inceleyin. Bazı sql bilgi eksikliklerim yüzünden belki 2 günü gitti ama buna değidi çünkü çok şey öğrendim. Başkaları da bu problemde epey takılmışlar.
Şu anda denemeler yapıyorum. http://stackoverflow.com/questions/1025688/calculate-missing-date-ranges-and-overlapping-date-ranges-between-two-dates adresinde bulduğum SQL fonksiyonunun 53üncü satırdaki insert satırında eksik bırakılan parametreyi düzeltip, ihtiyacım olan datetime değil time olduğundan değişkenleri TıME(7) yapıp, fonksiyon çalıştırmasını beceremediğimden fonksiyon kullanmadan orada direk sorgu penceresinde çalıştırmak için başlangıç ve bitiş saatlerini set edip, çıktısını konsolda görmek için kodun oluşturduğu raporlama tablosunu print komutuyla çıktıya yazdırmadıyı beceremediğim için(parametreleri print ile tek tek yazdırabiliyorum fakat) en son satırda select * from tblTest sorgusu çalıştırmak suretiyle, SQL Management studio aracılığıyla oluşturduğum bir veritabanındaki tblTest ismindeki içinde test saat aralıkları olan tablom üzerinde denedim, ve sonuçları gördüm. Şimdi bu kod çalışıyor ve istediğime yaklaştım.
Bu kodu kendi istediğime uygun halde deneyip, düzenleyip, asp.net C# programımda kullanılacak hale getireceğim, bu arada sql üzerinde mantıksal işlemleri, döngüleri, fonksiyon tanımlamasını, değişken tanımlamasını ve CURSOR kullanmasını az çok anlamış oldum:
BEGIN
DECLARE @p_from TIME(7)
DECLARE @p_till TIME(7)
set @p_from = '12:00'
set @p_till = '15:45'
DECLARE @t TABLE
(
q_type VARCHAR(20) NOT NULL,
q_start TIME(7) NOT NULL,
q_end TIME(7) NOT NULL
)
END
BEGIN
DECLARE @qs TIME(7)
DECLARE @qe TIME(7)
DECLARE @ms TIME(7)
DECLARE @me TIME(7)
DECLARE cr_span CURSOR FAST_FORWARD
FOR
SELECT dbo.tblTest.saatbas, dbo.tblTest.saatbit
FROM dbo.tblTest
WHERE saatbas BETWEEN @p_from AND @p_till ORDER BY saatbas
OPEN cr_span
FETCH NEXT
FROM cr_span
INTO @qs, @qe
SET @ms = @qs
SET @me = @qe
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT
FROM cr_span
INTO @qs, @qe
IF @qs > @me
BEGIN
INSERT
INTO @t
VALUES ('çakışma, aynı anda başlıyor', @ms, @me)
INSERT
INTO @t
VALUES ('çakışma, vakti kapsıyor', @me, @qs)
SET @ms = @qs
END
SET @me = CASE WHEN @qe > @me THEN @qe ELSE @me END
END
IF @ms IS NOT NULL
BEGIN
INSERT
INTO @t
VALUES ('çakışma yok',@ms, @me)
END
CLOSE cr_span
DEALLOCATE cr_span
END
SELECT * FROM @t
check constraint complex
http://tonyandrews.blogspot.com/2004/10/enforcing-complex-constraints-in.html
http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c22integ.htm
check constraint meeting room
Bu kitap çok kapsamlı ve önümüzü açacak türden.
http://www.amazon.com/gp/product/0123693799/#reader_0123693799 (Joe Celko's SQL for Smarties: Advanced SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management Systems)
http://bytes.com/topic/mysql/answers/447746-how-check-date-time-ranges-within-record-check-schedule-conflicts
Amazon.com's "search inside the book" feature. The 3rd edition moves the
content I mentioned to chapter 13, pp. 275-286. Looks like pp. 277-281 are
viewable onlineSırf OVERLAP diye komut olduğunu bile öğrenmem işime yaradı o kiğtabın indexinde BETWEENin yanında gördüm. sayfa 275 time periods and overlaps konusu. ve google anahtar kelimemiz "sql overlapping date ranges"
ve işte bir çözümkodu hem de çok basit: http://www.tek-tips.com/viewthread.cfm?qid=1451066&page=9
ayrıca anladığım kadarıyla bu sorgunun sonucu output olarak zaman aralığı diyagramı olarak görüntülenebiliyor ki çok faydalı birşey.
çözüm tamamen sql ile yapılmış, bunu inceleyerek anlayabilirim herhalde: http://stackoverflow.com/questions/1025688/calculate-missing-date-ranges-and-overlapping-date-ranges-between-two-dates
http://datacogs.com/datablogs/archive/2004/10/21/170.aspx
http://blogs.msdn.com/b/jaybaz_ms/archive/2004/08/19/217226.aspx
http://ryanfarley.com/blog/archive/2004/08/19/966.aspx
http://mysql.bigresource.com/time-overlap-check-5jrnSg6p.html
hazır yazılım kullanım kılavuzuna bakarak yapılabilecekleri görmek açısından çok faydalı: https://www.egi.eu/indico/ihelp/html/UserGuide/RoomBooking.html
http://www.oracle.com/technetwork/issue-archive/2006/06-mar/o26asktom-097731.html
online kitap: http://books.google.com.tr/books?id=ot5w8P5ueqgC&pg=PA26&lpg=PA26&dq=check+constraint+meeting+room&source=bl&ots=JwRQcrWO9e&sig=y7YWTAYQLFvaVzwg0KoAEArrEMo&hl=tr&ei=SfGvTYmCC5GWswbe1ajrCw&sa=X&oi=book_result&ct=result&resnum=1&ved=0CBcQ6AEwAA#v=onepage&q=check%20constraint%20meeting%20room&f=false
http://msdn.microsoft.com/en-us/library/aa175780%28SQL.80%29.aspx
global data management
konu dışı ancak faydalı: http://www.wikihow.com/Organise-an-Event