Generowanie danych losowych dla PostgreSQL
2019-04-26
Dobre dane są jednym z najpotrzebniejszych narzędzi podczas wytwarzania i testów oprogramowania. Często są niezbędne do wczesnego wykrycia błędów działania aplikacji, co zmniejsza potencjalne ryzyko przedostania się błędu do aplikacji działającej w środowisku produkcyjnym. Dziś rozpoczynamy cykl tekstów poświęconych tej tematyce. Na początek o tym, jak przy pomocy czystego SQL wygenerować dane dla niektórych podstawowych […]
Dobre dane są jednym z najpotrzebniejszych narzędzi podczas wytwarzania i testów oprogramowania. Często są niezbędne do wczesnego wykrycia błędów działania aplikacji, co zmniejsza potencjalne ryzyko przedostania się błędu do aplikacji działającej w środowisku produkcyjnym.
Dziś rozpoczynamy cykl tekstów poświęconych tej tematyce. Na początek o tym, jak przy pomocy czystego SQL wygenerować dane dla niektórych podstawowych typów danych w PostgreSQL.
Funkcja random() i generowanie danych liczbowych z przedziału
Podstawowym narzędziem, z którego będziemy korzystać, jest funkcja random(). Pozwala ona na wygenerowanie wartości z przedziału [0,1).
SELECT random();
random
-------------------
0.711595530621707
(1 row)
Aby wygenerować losową liczbę z jakiegoś przedziału, można skorzystać z funkcji PostgreSQL width_bucket(). Pozwala ona na przełożenie dowolnej wartości z wybranego zakresu na określoną liczbę „pudełek”. Jest to funkcja przydatna podczas analiz statystycznych, ale i w tym wypadku będzie miała dobre zastosowanie. Aby wygenerować dane z zakresu [1, 10], wystarczy wywołać:
SELECT width_bucket(random(), 0, 1, 10) + 1;
width_bucket
--------------
5
(1 row)
Teraz, aby wygenerować dane z zakresu [a, b], można posłużyć się następującą funkcją:
CREATE OR REPLACE FUNCTION random_number(a INTEGER, b INTEGER) RETURNS INTEGER AS $$ SELECT width_bucket(random(), 0, 1, (b-a)+1) + (a-1); $$ LANGUAGE SQL;
Najpierw potrzebujemy określić liczbę pudełek, które będziemy mieć w naszym zakresie (b-a+1), a potem przesunąć początek interwału. Tak oto stworzyliśmy bardzo prosty generator danych o rozkładzie jednostajnym. Możemy to sprawdzić na przykład w ten sposób:
SELECT random_number(3,6), COUNT(1) FROM generate_series(1,1000) GROUP BY 1;
random_number | count
---------------+-------
3 | 255
5 | 246
4 | 246
6 | 253
(4 rows)
Warto w tym momencie zwrócić uwagę na funkcję generate_series(), która pozwala na tworzenie sekwencji. W przypadku generowania danych świetnie nadaje się to do tworzenia większej ilości danych w sposób analogiczny do powyższego zapytania.
Generowanie napisów
Przy pomocy funkcji, którą przed chwilą stworzyliśmy, możemy również stworzyć zapytanie, które będzie generowało losowy napis. Poniżej prezentuję przykład wykorzystujący czysty SQL (bez wykorzystywania języka skryptowego PL/pgSQL):
WITH random_ascii AS (
SELECT chr(random_number(32,126)) AS char
FROM generate_series(1,10)
)
SELECT array_to_string(array_agg(char),'') FROM random_ascii;
Pierwsza część zapytania to utworzenie tymczasowej tabeli random_ascii, w której będą się znajdować drukowalne znaki z tabeli ASCII. Długość generowanego napisu kontrolujemy argumentami funkcji generate_series(). Następnie korzystamy kolejno z funkcji array_agg() tworzącej tablicę ze wszystkich elementów string oraz array_to_string(), która łączy elementy tablicy w pojedynczy napis. Przekształcenie tego zapytania do funkcji SQL nie powinno stanowić kłopotu.
Jednak co wtedy, gdy nie chcemy, aby napisy były generowane z całego zestawu znaków drukowalnych, a jedynie ze znaków znajdujących się w alfabecie?
\set alphabet 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM'
WITH random_alpha AS (
SELECT substr(:alphabet,random_number(1, char_length(:alphabet)),1)
AS char
FROM generate_series(1,10)
)
SELECT array_to_string(array_agg(char), '') FROM random_alpha;
Stosujemy analogiczne podejście – generujemy losowy napis, tym razem z napisu zawierającego cały alfabet. Najistotniejszą funkcją jest substr(), która pozwala na wycinanie fragmentu napisu. Poszczególne argumenty, które przyjmuje to:
- napis będący źródłem wycinka,
- pozycja, od której ma zacząć wycinanie,
- długość wycinka.
W naszym wypadku korzystamy z wcześniej zdefiniowanej funkcji random_number, aby otrzymać losową pozycję znaku, który będziemy wycinać.
Opakowując to w funkcję, otrzymamy:
CREATE OR REPLACE FUNCTION get_random_string(
string_length INTEGER,
alphabet TEXT DEFAULT 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM'
) RETURNS text AS $$
WITH random_alpha AS (
SELECT substr(alphabet,random_number(1, char_length(alphabet)),1)
AS char
FROM generate_series(1,string_length)
)
SELECT array_to_string(array_agg(char), '') FROM random_alpha;
$$ LANGUAGE SQL;
Z tak zdefiniowaną funkcją mamy większą dowolność w przypadku zbioru znaków, z których generowany będzie napis. W ten sposób możemy również generować napisy składające się na przykład z cyfr:
select get_random_string(3,'0123456789'); get_random_string ------------------- 353 (1 row)
Generowanie adresów IP
Korzystając z napisanych powyżej funkcji, możemy przejść do generowania bardziej skomplikowanych typów danych. Dla adresów IPv4 PostgreSQL udostępnia typ inet. Pozwalają one na przechowywanie adresów hostów oraz sieci dla IPv4 i IPv6. My jednak skupimy się na razie na generowaniu starszej wersji adresów.
Adres IPv4 to 32-bitowa liczba, którą tradycyjnie zapisuje się w postaci czterech 8-bitowych liczb – A.B.C.D. Przejdźmy teraz do pisania funkcji, która wygeneruje adres zgodny z formatem przyjmowanym przez typ inet.
W celu wygenerowania liczby z zakresu [0, 255], możemy skorzystać z random_number(). Pozostaje nam jedynie połączyć kolejne składowe adresu w całość.
CREATE OR REPLACE FUNCTION get_random_ipv4()
RETURNS inet AS $$
WITH cte AS (
SELECT random_number(0,255) AS n
FROM generate_series(1,4)
)
SELECT array_to_string(array_agg(n), '.')::inet FROM cte;
$$ LANGUAGE SQL;
W ten prosty sposób możemy wygenerować losowe adresy IPv4.
SELECT get_random_ipv4() FROM generate_series(1,10); get_random_ipv4 ----------------- 107.172.71.74 34.58.156.117 213.62.16.139 125.246.84.58 145.169.141.13 121.155.229.203 158.251.193.212 131.7.199.238 179.15.57.214 73.213.75.31 (10 rows)
Podsumowanie
Jak widać, implementacja języka SQL w PostgreSQL pozwala na tworzenie bardzo elastycznych rozwiązań, które mogą posłużyć podczas generowania danych testowych. Jest to jednak dość żmudny proces i wymaga napisania odpowiedniego generatora dla każdego typu danych, które chce się losowo wygenerować. Kolejnym ograniczeniem jest trudność generowania danych o rozkładach innych niż jednostajny. Prawdziwe dane trafiające do bazy danych często przybierają zupełnie inny rozkład. A każdy inny rozkład będzie wymagał dodatkowej pracy ze strony programisty.