MySQL-owe widoki w Django

Niniejszy artykuł traktuje o widokach w bazie danych (konkretnie w MySQL) i możliwości tworzenia modeli do tychże widoków z użyciem ORM-a framewokra Django. Zwracam na to uwagę aby nie pomylić – pomimo zbieżności nazw – widoków SQL-owych z widokami (views.py) Django.

Czym są właściwie widoki w relacyjnej bazie danych i po co się je tworzy wyjaśnia – skądinąd świetnie napisany rozdział pod tytułem „Widoki” w przygotowanym przez Heliona kursie MySQL.

W jednym z projektów, nad którymi pracuję stanąłem przed potrzebą stworzenia widoku w MySQL-u. Postanowiłem więc zdefiniować taki widok, stworzyć do niego model w Django i posługiwać się jak dowolnym innym modelem z tym, że tylko do odczytu. ORM w Django jest jednym z najbardziej rozbudowanych i zaawansowanych narzędzi tego typu mimo to ma swoje ograniczenia dlatego też nie byłem pewien czy uda mi się zrealizować to co sobie założyłem. Okazało się to możliwe aczkolwiek dopiero od wersji 1.3 frameworka Django.

Poniżej prezentuję jak zdefiniować prawidłowo model dla widoku. Na potrzeby tego artykułu stworzyłem możliwie proste przykłady aby pokazać mechanizm. Skonstruowany przeze mnie widok był daleko bardziej rozbudowany.

Załużmy, że mamy nowy projekt django a w nim aplikację do prezentacji newsów.

news/models.py
from django.db import models
from django.contrib.auth.models import User
 
class Article(models.Model):
    name = models.CharField(max_length=255)
    slug = models.SlugField(unique=True, max_length=100)
    text = models.TextField()
    added_by = models.ForeignKey(User)
    added_on = models.DateTimeField(auto_now_add=True)
    updated_on = models.DateTimeField(auto_now=True)
    is_active = models.BooleanField(default=True)
news/admin.py
from news.models Article
from django.contrib import admin
 
class ArticleAdmin(admin.ModelAdmin):
    list_filter = ['is_active']
    list_display = ('name', 'added_by', 'added_on', 'updated_on', 'is_active')
    prepopulated_fields = {'slug': ('name',)}
    search_fields = ('name', )
 
admin.site.register(Article, ArticleAdmin)

Z pewnych powodów potrzebujemy widoku, który prezentuje się następująco:

DROP VIEW IF EXISTS `users_newsview`;
CREATE VIEW `users_newsview` AS
    SELECT 0 AS id, n.id AS news_id, n.name AS news_name, n.added_by_id AS user_id, u.username
    FROM news_article AS n
    INNER JOIN auth_user AS u ON u.id = n.added_by_id;

Tworzymy do niego model, który będzie tylko do odczytu.

users/models.py
from django.db import models
from news.models import Article
from django.contrib.auth.models import User
 
class NewsView(models.Model):
 
    class Meta:
        managed = False
 
    news = models.ForeignKey(Article, null=True, blank=True, on_delete=models.DO_NOTHING)
    news_name = models.CharField(max_length=255, null=True, blank=True)
    user = models.ForeignKey(User, null=True, blank=True, on_delete=models.DO_NOTHING)
    username = models.CharField(max_length=255, null=True, blank=True)

O tym, że obiekty modelu „NewsViews” są niemodyfikowalne decyduje Zdefniowany w podklasie „Meta” parametr „managed = False”. Innym bardzo istotnym elementem jest dodanie do definicji kluczy obcych „on_delete=models.DO_NOTHING”. Django w przypadku kluczy obcych domyślnie emuluje zachowanie „on delete cascade”. Możliwość zmiany tego zachowania Django wspiera dopiero od wersji 1.3. Jeśli nie zmienimy sposobu w jaki Django ma postępować z elementami powiązanymi w trakcie usuwania danego obiektu to przy próby usunięcia newsa, albo usera zostanie zgłoszony błąd gdyż próba usunięcia także rekordu widoku z oczywistych względów się niepowiedzie.

W starszej wersji frameworka Django też można stworzyć model do widoku, ale trzeba w nim zrezygnować z tworzenia relacji do innych obiektów i zamiast pól „news” i „user” zdefiniować pola typu integer „news_id” oraz „user_id”. Jak się domyślacie rozwiązanie to jest o wiele mniej wygodne.

PHP – operacje na bitach w praktyce

Na początku mojej nauki PHP kupiłem sobie książkę „PHP4 Aplikacje” (Tobiasa Ratschiller i Till Gerken – Wydawnictwo Robomatic). Zawarta w tej lekturze tematyka była wtedy dla mnie zbyt zaawansowana i potem wielokrotnie wracałem do tej pozycji stopniowo dojrzewając do poruszanych w niej tematów. Najdłużej wzbraniałem się przed zgłębieniem wiedzy dotyczącej operacji na bitach. Dzisiaj nie wiem właściwie dlaczego bo zagadnienie jest całkiem proste, a rozwiązania oparte o system binarny mają wiele zalet.

Najpowszechniej chyba spotykanym przypadkiem stosowania wartości bitowych są wszelkiego rodzaju systemy uprawnień. Każdy chyba programista PHP zaprzyjaźnił się z dyrektywami

ini_set('display_errors', 1);
ini_set('error_reporting', E_ALL);

Druga z wymienionych dyrektyw ma też odpowiadającą jej funkcję „error_reporting”, która jako argument przyjmuje pozom raportowania błędów. Poziom ten można przekazać w postaci maski bitowej złożonej ze stałych odzwierciedlających wartości przypisane poszczególnym rodzajom błędów PHP.

Wartość bitowe stałych nie są jak widać w formacie binarnym tylko dziesiętnym.

stała zapis w formacie dwójkowym (binarnym) zapis w formacie dziesiętnym
E_ERROR 00000001 1
E_WARNING 00000010 2
E_PARSE 00000100 4
E_NOTICE 00001000 8

Do przeliczania wartości binarnych na dziesiętne służy w PHP funkcja bindec, a z dziesiętnych na binarne decbin.

I tak wywołanie …

error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

spowoduje że wyświetlane będą wszystkie błędy czasu wykonania, ostrzeżenia, błędy parsowania oraz uwagi.

Z kolei użycie takiej maski …

error_reporting(E_ALL &~ E_NOTICE);

pozwoli na ukrycie wszystkich mało ważnych uwag. Natomiast wszystkie pozostałe błędy i bardziej ważne ostrzeżenia będą dalej raportowane.

W celu zrozumienia wyżej zaprezentowanych operacji niezbędnym będzie poznanie operatorów logicznych. Ambitnych z kolei odsyłam do Algebry Boole’a, choć nie zachęcam zbyt mocno.

Czasami zbyt gruntowny wykład teoretyczny utrudnia zrozumienie prostych spraw, które podawane w małych porcjach powoli poszerzają horyzonty. Czytałem kilka prac poświęconych operacjom na bitach, w których była cała masa operacji na 0 (zerach) i 1 (jedynkach), wiele tabel, wzorów, równań. Wiele z nich wspominało o prawach De Morgana czy postulatach Huntigtona, ale mało który materiał traktował o tym jak tego używać. Dopiero niedawno kolega podesłał mi link do artykułu, który podchodzi do tematu z praktycznej strony.

Nospor w jednym z wpisów na swoim blogu pt. opcje dwuwartościowe prezentuje studium przypadku użycia operacji bitowych. Proponuje zastąpienie kilku flag – czyli dwustanowych pól przyjmujących wartość logiczną TRUE lub FALSE – w tabeli w bazie danych, jednym polem przechowującym wartość bitową.

Polega to konkretnie na tym, że zamiast mieć w tabeli trzy pola i trzy indeksy:

CREATE TABLE `offer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `is_active` tinyint UNSIGNED NOT NULL DEFAULT 0,
  `is_promotion` tinyint UNSIGNED NOT NULL DEFAULT 0,
  `is_sale` tinyint UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY  (`id`),
  KEY `is_active` (`is_active`),
  KEY `is_promotion` (`is_promotion`),
  KEY `is_sale` (`is_sale`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Można je zastąpić jednym polem i jednym indeksem.

CREATE TABLE `offer` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL,
  `options` tinyint UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY  (`id`),
  KEY `options` (`options`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Dla trzech, niezależnych od siebie opcji powyższe rozwiązanie nie jest zbyt wygodne, ale dla potrzeb edukacyjnych wystarczy. Niżej pokazuję bardziej adekwatny przypadek użycia, tymczasem wyjaśniam że aby móc zapisać w jednym polu wartości kilku opcji trzeba najpierw każdej opcji przypasować bit.

// Normalnie staram się nie nadużywać stałych 
// jednak tutaj zrobiłem to dla jasności przykładów
define('OPT_NONE', 0);_
define('OPT_IS_ACTIVE', 1);
define('OPT_IS_PROMOTION', 2);
define('OPT_IS_SALE', 4);

Przy okazji chciałbym wspomnieć że poniższe zapisy są tożsame.

1 1 << 0 bindec(‚00000001’)
2 1 << 1 bindec(‚00000010’)
4 1 << 2 bindec(‚00000100’)

Wiedząc jaki bit oznacza jaką opcję możemy zapisać tę informację w bazie danych. W tym celu trzeba zsumować poszczególne opcje przy pomocy logicznego operatora OR „|”.

// Wszystkie opcje
$options = (OPT_IS_ACTIVE | OPT_IS_PROMOTION | OPT_IS_SALE);
// otrzymana wartość to 7
 
// Oferta jest aktywna i w wyprzedaży, ale nie w promocji
$options = (OPT_IS_ACTIVE | OPT_IS_SALE);
// otrzymana wartość to 5
 
// Oferta jest w promocji, ale nie jest aktywna ani w wyprzedaży
$options = OPT_IS_PROMOTION;
// otrzymana wartość to 2

Kiedy już przypiszemy ofercie jakieś opcje chcielibyśmy sprawdzić czy takową posiada.

// Oferta jest aktywna i w wyprzedaży, ale nie w promocji
$options = (OPT_IS_ACTIVE | OPT_IS_SALE);
 
// Upewniamy się czy oferta jest aktywna
($options & OPT_IS_ACTIVE) > 0 ? 'yes' : 'no';
// otrzymany wynik yes
 
// Sprawdzamy czy jest w promocji
($options & OPT_IS_PROMOTION) > 0 ? 'yes' : 'no';
// otrzymany wynik no
 
// Sprawdzamy czy jest aktywna i w wyprzedaży
($options & (OPT_IS_ACTIVE | OPT_IS_SALE)) == ((OPT_IS_ACTIVE | OPT_IS_SALE)) ? 'yes' : 'no';
// otrzymany wynik yes
 
// Sprawdzamy czy jest w promocji i/lub w wyprzedaży
($options & (OPT_IS_PROMOTION | OPT_IS_SALE)) > 0 ? 'yes' : 'no';
// otrzymany wynik yes
 
// Sprawdzamy czy jest w promocji albo w wyprzedaży
($options & (OPT_IS_PROMOTION | OPT_IS_SALE)) != (OPT_IS_PROMOTION | OPT_IS_SALE) ? 'yes' : 'no';
// otrzymany wynik yes

Proszę zwrócić uwagę na zastosowane nawiasy. Operatory logiczne mają priorytet niższy od operatorów arytmetycznych, a także od operatorów porównania więc jeśli wykonamy test (2 | 4 == 6) to z pewnością otrzymamy inny wynik niż jeśli zastosujemy następujący zapis ((2 | 4) == 6)

Opcje możemy modyfikować

// Oferta jest aktywna i w wyprzedaży, ale nie w promocji
$options = (OPT_IS_ACTIVE | OPT_IS_SALE);
// 5
 
// Dodajemy opcję w promocji
$options |= OPT_IS_PROMOTION;
// 7
 
// Usuwamy opcję w wyprzedaży
$options &= ~OPT_IS_SALE;
// 3
 
// Usuwamy też opcję jest aktywna
$options ^= OPT_IS_ACTIVE;
// 2
 
// I dodajemy ją spowrotem
$options ^= OPT_IS_ACTIVE;
// 3

Jak widać w ostatnim przykładzie operator ^ jest przełącznikiem, który usuwa bit jeśli jest ustawiony i ustawia jeśli nie jest ustawiony.

Przykład opcji – zapożyczony zresztą od Nospora – nie prezentuje pełnego potencjału bitów, które nie bez powodu są często używane przy konstruowaniu wszelkiego rodzaju systemów uprawnień. Oprócz oszczędności miejsca za pomocą szablonów bitowych można w łatwy sposób zaimplementować dziedziczenie uprawnień.

Stwórzmy kilka stref dostępu, a następnie grupy użytkowników, o różnych poziomach uprawnień pozwalających na dostęp do poszczególnych stref.

strefy dostępu

  • Strefa dla wszystkich – dostęp do niej powinni mieć wszyscy użytkownicy.
  • Strefa dla użytkowników uwierzytelnionych. Zalogowani użytkownicy powinni mieć dostęp do tego co użytkownicy anonimowi oraz do kilku innych funkcjonalności
  • Strefa dla moderatorów – moderatorzy mogą z założenia wszystko to co użytkownicy zalogowani, ale mają też funkcje edycyjne.
  • Strefa dla sponsorów – ich pole obejmuje zakres aktywności użytkowników zalogowanych oraz częściowo pokrywa się ze strefą moderatora. Nie mogą jednak edytować treści, za to mają dostęp do raportów, które zwykły moderator nie ma prawa widzieć
  • Administrator jak to zwykle bywa może wszystko

Przypiszmy każdej ze stref bit.

$for_logged = 1;
$for_moderators = 2;
$for_sponsors = 4;
$for_administrators = 8;

Następnie poszczególnym grupom użytkowników ustawmy taki szablon bitowy, który pozwoli im na dostęp do określonych stref. Przy pomocy szablonów bitów stosunkowo łatwo jest zdefiniować hierarchię grup użytkowników pozwalającą zrealizować założenie dziedziczenia uprawnień.

$logged_user = 1;
$moderator = $logged_user | 2; // 3
$sponsor = $logged_user | 4; // 5
$admin = $moderator | $sponsor | 8; // 15

Przetestujmy!

$bob = $sponsor; // 5
 
// Czy Bob ma dostęp do strefy zalogowanych użytkowników?
($for_logged & $bob) > 0 ? 'yes' : 'no';
// wynikiem jest yes
 
// Czy Bob ma dostęp do strefy moderatora?
($for_moderators & $bob) > 0 ? 'yes' : 'no';
// wynikiem jest no
 
// Czy Bob ma dostęp do strefy sponsorów?
($for_sponsors & $bob) > 0 ? 'yes' : 'no';
// wynikiem jest yes
 
// Czy Bob ma dostęp do strefy administratorów?
($for_administrators & $bob) > 0 ? 'yes' : 'no';
// wynikiem jest no

Operatory bitowe wyglądają tak samo w PHP, Pythonie czy MySQL-u. Można zapisać strefy, grupy i użytkowników bazie danych i większość operacji wykonać za pomocą zapytań sql-owych.

CREATE TABLE `zones` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`level` TINYINT NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;
 
INSERT INTO `zones` (`id`, `name`, `level`) VALUES 
(1, 'for_logged', '1'), (2, 'for_moderators', '2'), 
(3, 'for_sponsors', '4'), (4, 'for_administrators', '8');
 
CREATE TABLE `groups` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL ,
`perms` TINYINT NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;
 
INSERT INTO `groups` (`id`, `name`, `value`) VALUES 
(1, 'logged_user', '1'), (2, 'moderator', '3'), 
(3, 'sponsor', '5'), (4, 'administrator', '15');
 
CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL ,
`groups_id` INT UNSIGNED NOT NULL
) ENGINE = MYISAM ;
 
INSERT INTO `users` (`id`, `username`, `groups_id`) VALUES 
('1', 'Frank', '1'), ('2', 'Charlie', '2'), ('3', 'Bob', '3'), 
('4', 'Jon', '4'), ('5', 'Mary', '1');

Poniżej użyłem podzapytań w celu pobrania listy użytkowników, którzy mają dostęp do strefy dla użytkowników zalogowanych, a następnie dla moderatorów.

SELECT username FROM `users` AS u INNER JOIN groups AS g ON u.groups_id = g.id 
WHERE g.perms & (SELECT z.level FROM zones AS z WHERE z.name = 'for_logged');
// wynikiem jest lista: Frank, Mary, Charlie, Bob, Jon
 
SELECT username FROM `users` AS u INNER JOIN groups AS g ON u.groups_id = g.id 
WHERE g.perms & (SELECT z.level FROM zones AS z WHERE z.name = 'for_moderators');
// wynikiem jest lista: Charlie, Jon

Na zakończenie chciałbym zaprezentować mały testowy skrypt. Jeśli nie chce Ci się analizować kodu, skopiuj go, zapisz w pliku i uruchom, a wszystko stanie się jasne.

/**
 * Rodzaje powiadomień
 */
$notification_methods = array(
    'email' => 1,
    'internal_message' => 2,
    'notification' => 4,
    'wall' => 8
);
 
 
$nm = $notification_methods;
 
/**
 * Rodzaje zdarzeń
 *
 * Do każdego rodzaju zdarzeń przypisane są dopuszczalne rodzaje powiadomień
 * np. o prywatnej wiadomości można powiadomić mailem lub za pośrednictwem
 * wewnętrznej wiadomości, ale nie wolno wyświetlić tej informacji na ścianie
 */ 
$event_types = array(
    'newsletter'        => $nm['email'],
    'invite_to_friends' => $nm['internal_message'] | $nm['notification'],
    'priv_message'      => $nm['email'] | $nm['internal_message'],
    'image_comment'     => $nm['internal_message'] | $nm['notification'] | $nm['wall']
);
 
 
print '<form action="" method="post">';
foreach ($event_types as $event_name => $avaliable_noti_methods) {
    echo '<p>';
    echo '<strong>'.$event_name.'</strong><br>';
    foreach ($notification_methods as $noti_name => $noti_val) {
        if ($avaliable_noti_methods & $noti_val) {
            $checked = (isset($_POST[$event_name.'-'.$noti_name]) ? 'checked="checked"' : '');
            echo '<input type="checkbox" name="'.$event_name.'-'.$noti_name.'[]" id="id_'.$event_name.'-'.$noti_name.'" '.$checked.'>';
            echo '<label for="id_'.$event_name.'-'.$noti_name.'">'.$noti_name.'</label><br>';
        }
    }
    echo '</p>';
}
echo '<p><input type="submit" name="ok" value="OK"></p>';
echo '</form>';
 
if (isset($_POST['ok'])) {
 
    $user_notification_settings = array_combine(array_keys($event_types), array_fill(0,4,0));
    foreach (array_keys($_POST) as $key) {
        $values = explode("-", $key);
        if (count($values) != 2) continue;
        list($event_name, $noti_name) = $values;
 
        if (!isset($user_notification_settings[$event_name])) {
            $user_notification_settings[$event_name] = $notification_methods[$noti_name];
        } else {
            $user_notification_settings[$event_name] |= $notification_methods[$noti_name];
        }
    }
 
    printf('<strong>Ustawienia wybrane przez użytkownika</strong>%s', print_r($user_notification_settings, true));
 
    print '<strong>Podsumowanie</strong><br>';
    foreach ($user_notification_settings as $event_name => $avaliable_noti_methods) {
        foreach ($notification_methods as $noti_name => $noti_val) {
            if ($avaliable_noti_methods & $noti_val) {
                print $event_name.' - tak - '.$noti_name.'<br>';
            } else {
                print $event_name.' - NIE - '.$noti_name.'<br>';
            }
        }
    }
}