Jest to problem, nad którym w przeszłości spędziłem godziny. Wydaje mi się, że jest to coś, co powinno było zostać rozwiązane przez nowoczesne rozwiązania RDBMS , ale jak dotąd nie znalazłem niczego, co naprawdę odnosi się do tego, co uważam za niezwykle powszechną potrzebę w dowolnej aplikacji internetowej lub Windows z zapleczem bazy danych.
Mówię o sortowaniu dynamicznym. W moim świecie fantasy powinno to być tak proste, jak coś takiego:
ORDER BY @sortCol1, @sortCol2Jest to kanoniczny przykład podawany przez początkujących programistów SQL i procedur składowanych na forach w Internecie. "Dlaczego nie jest to możliwe?" pytają. Niezmiennie ktoś w końcu przychodzi, aby pouczyć ich o skompilowanej naturze procedur składowanych, ogólnie o planach wykonania i wielu innych powodach, dla których nie jest możliwe umieszczenie parametru bezpośrednio w ORDER BYklauzuli.
Wiem, co niektórzy z was już sobie myślą: „Pozwól więc klientowi sortować”. Oczywiście odciąża to pracę bazy danych. Jednak w naszym przypadku nasze serwery bazodanowe nie są nawet obciążone w 99% przypadków, a nawet nie są jeszcze wielordzeniowe ani nie są żadnymi innymi niezliczonymi ulepszeniami architektury systemu, które mają miejsce co 6 miesięcy. Tylko z tego powodu posiadanie przez nasze bazy danych obsługi sortowania nie byłoby problemem. Dodatkowo bazy danych są bardzodobry w sortowaniu. Są pod tym kątem zoptymalizowane i mieli lata, aby to zrobić dobrze, język do tego jest niesamowicie elastyczny, intuicyjny i prosty, a przede wszystkim każdy początkujący pisarz SQL wie, jak to zrobić, a co ważniejsze, umie go edytować wprowadzać zmiany, konserwować itp. Gdy Twoje bazy danych są dalekie od opodatkowania i chcesz po prostu uprościć (i skrócić!) czas programowania, wydaje się to oczywistym wyborem.
Jest też problem z siecią. Bawiłem się JavaScriptem, który wykonuje sortowanie tabel HTML po stronie klienta, ale nieuchronnie nie są one wystarczająco elastyczne dla moich potrzeb, a ponieważ moje bazy danych nie są nadmiernie opodatkowane i potrafią naprawdę bardzo łatwo sortować, ja Trudno mi uzasadnić czas potrzebny na ponowne napisanie lub zrolowanie własnego sortownika JavaScript. To samo dotyczy generalnie sortowania po stronie serwera, chociaż jest już prawdopodobnie znacznie preferowane niż JavaScript. Nie jestem osobą, która szczególnie lubi narzuty związane z DataSets, więc pozwól mi.
Ale to przypomina, że nie jest to możliwe - a raczej niełatwe. Z poprzednimi systemami zrobiłem niesamowity sposób na uzyskanie dynamicznego sortowania. Nie był ani ładny, ani intuicyjny, prosty ani elastyczny, a początkujący pisarz SQL zostałby stracony w ciągu kilku sekund. Już teraz wygląda to nie tyle na „rozwiązanie”, ale na „komplikację”.
Poniższe przykłady nie mają na celu ujawnienia jakichkolwiek najlepszych praktyk ani dobrego stylu kodowania ani niczego takiego, ani nie wskazują na moje umiejętności jako programisty T-SQL. Są tym, czym są i przyznaję, że są zagmatwane, mają złą formę i są po prostu hackami.
Przekazujemy wartość całkowitą jako parametr do procedury składowanej (nazwijmy parametr po prostu „sort”) i na tej podstawie określamy kilka innych zmiennych. Na przykład ... powiedzmy, że sortowanie to 1 (lub wartość domyślna):
DECLARE @sortCol1 AS varchar(20)
DECLARE @sortCol2 AS varchar(20)
DECLARE @dir1 AS varchar(20)
DECLARE @dir2 AS varchar(20)
DECLARE @col1 AS varchar(20)
DECLARE @col2 AS varchar(20)
SET @col1 = 'storagedatetime';
SET @col2 = 'vehicleid';
IF @sort = 1 -- Default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'asc';
SET @sortCol2 = @col2;
SET @dir2 = 'asc';
END
ELSE IF @sort = 2 -- Reversed order default sort.
BEGIN
SET @sortCol1 = @col1;
SET @dir1 = 'desc';
SET @sortCol2 = @col2;
SET @dir2 = 'desc';
END
Możesz już zobaczyć, jak gdybym zadeklarował więcej zmiennych @colX, aby zdefiniować inne kolumny, naprawdę mógłbym uzyskać kreatywność z kolumnami do sortowania na podstawie wartości „sort”… aby go użyć, zwykle kończy się to następująco niesamowicie niechlujna klauzula:
ORDER BY
CASE @dir1
WHEN 'desc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir1
WHEN 'asc' THEN
CASE @sortCol1
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END,
CASE @dir2
WHEN 'desc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END DESC,
CASE @dir2
WHEN 'asc' THEN
CASE @sortCol2
WHEN @col1 THEN [storagedatetime]
WHEN @col2 THEN [vehicleid]
END
END
Oczywiście jest to bardzo okrojony przykład. Prawdziwe rzeczy, ponieważ zwykle mamy cztery lub pięć kolumn do obsługi sortowania, każda z ewentualnymi drugorzędnymi lub nawet trzecimi kolumnami do sortowania oprócz tego (na przykład data malejąca, a następnie sortowana wtórnie według nazwy rosnąco) i każda obsługująca bi- sortowanie kierunkowe, które skutecznie podwaja liczbę przypadków. Tak ... bardzo szybko robi się owłosiony.
Chodzi o to, że można „łatwo” zmienić sortowanie przypadków, tak aby identyfikator pojazdu był sortowany przed czasem przechowywania ... ale pseudoelastyczność, przynajmniej w tym prostym przykładzie, naprawdę się kończy. Zasadniczo każdy przypadek, który nie przejdzie testu (ponieważ nasza metoda sortowania nie ma do niego zastosowania tym razem) renderuje wartość NULL. W ten sposób otrzymujesz klauzulę, która działa jak poniżej:
ORDER BY NULL DESC, NULL, [storagedatetime] DESC, blah blahMasz pomysł. Działa, ponieważ SQL Server skutecznie ignoruje wartości null w kolejności według klauzul. Jest to niezwykle trudne do utrzymania, co prawdopodobnie zauważy każdy z podstawową wiedzą praktyczną na temat SQL. Jeśli straciłem kogokolwiek z was, nie czuj się źle. Zajęło nam dużo czasu, zanim zaczęło działać, a wciąż jesteśmy zdezorientowani, próbując go edytować lub tworzyć nowe, podobne do tego. Na szczęście nie trzeba go często zmieniać, w przeciwnym razie szybko stałby się „nie wart zachodu”.
A jednak zadziałało.
Moje pytanie brzmi zatem: czy jest lepszy sposób?
Nie przeszkadzają mi rozwiązania inne niż procedury składowane, ponieważ zdaję sobie sprawę, że może to nie być droga. Najlepiej chciałbym wiedzieć, czy ktokolwiek może zrobić to lepiej w ramach procedury składowanej, ale jeśli nie, jak wszyscy poradzicie sobie z umożliwieniem użytkownikowi dynamicznego sortowania tabel danych (również dwukierunkowo) za pomocą ASP.NET?
I dziękuję za przeczytanie (lub przynajmniej przejrzenie) tak długiego pytania!
PS: Ciesz się, że nie pokazałem mojego przykładu procedury składowanej, która obsługuje dynamiczne sortowanie, dynamiczne filtrowanie / wyszukiwanie tekstu kolumn, paginację za pomocą ROWNUMBER () OVER, AND spróbuj ... złapać wycofywanie transakcji na błędach ... „wielkości behemota” nawet nie zaczyna ich opisywać.
Aktualizacja:
- Chciałbym uniknąć dynamicznego SQL . Parsowanie łańcucha razem i uruchomienie na nim procedury EXEC podważa wiele z celów posiadania procedury składowanej w pierwszej kolejności. Czasami jednak zastanawiam się, czy wady zrobienia czegoś takiego nie byłyby tego warte, przynajmniej w tych specjalnych dynamicznych przypadkach sortowania. Mimo to zawsze czuję się brudny, gdy robię takie dynamiczne ciągi SQL - jakbym nadal żył w świecie klasycznej ASP.
- Głównym powodem, dla którego chcemy, aby procedury składowane były przede wszystkim, jest bezpieczeństwo . Nie mogę dzwonić w sprawach bezpieczeństwa, tylko sugeruję rozwiązania. Dzięki SQL Server 2005 możemy ustawić uprawnienia (w zależności od użytkownika, jeśli zajdzie taka potrzeba) na poziomie schematu dla poszczególnych procedur składowanych, a następnie bezpośrednio odmówić wszelkich zapytań dotyczących tabel. Krytyka zalet i wad tego podejścia to być może inna kwestia, ale znowu nie jest to moja decyzja. Jestem tylko małpą prowadzącą kod. :)