Skrypt w PHP korzystający z bazy SQLite

Jest to kolejny artykuł z serii o Lotto. Poprzednio nauczyliśmy się jak:

Oczywiście loterie są przeznaczone dla osób pełnoletnich, a poniższa treść jest jedynie w celach edukacyjnych / rozrywkowych. Zastrzegam ponownie, że nie można przewidzieć wyników losowania.

Co trzeba znać przed rozpoczęciem artykułu?

Podstawy HTML:

seohost
  • struktura strony
  • tagi (div, p)
  • klasy

Podstawy PHP:

  • zmienne
  • tablice
  • klasy
  • pętle (for, while, foreach).

Czego się nauczysz?

  • Podstaw języka SQL
  • Używanie bazy SQLite w PHP

Plan działania

  1. Logujemy się na serwer i tworzymy bazę SQLite w której będziemy przechowywać dane.
  2. Pobieramy losowania Dużego Lotka, które zapisujemy w bazie danych SQLite.
  3. Wykonujemy analizę po stronie serwera i jej wyniki również zapisujemy w bazie.
  4. Stawiamy stronę zgodnie z kursem tworzenia stron na WordPress
  5. Tworzymy własną wtyczkę zgodnie z kursem tworzenia wtyczek w WordPress.
  6. Pobieramy regularnie dane z serwera, aby wyświetlić je użytkownikowi.

Korzystamy z bazy SQLite

Baza SQLite to system bazy danych dostarczony standardowo z PHP (od wersji 5). W przeciwieństwie do popularnego MySQL, baza będzie nieco wolniejsza przy dużej ilości danych oraz nie obsługuje wielu użytkowników jednocześnie. Nam to nie jest potrzebne, gdyż tylko my będziemy z niej korzystać. Baza nie jest też izolowana – to zwykły plik umieszczony we wskazanym miejscu (technicznie MySQL to też plik, ale nie mamy do niego często bezpośredniego dostępu). W każdym momencie zresztą będziemy mogli przenieść się np. na bazę MySQL, który zapewne udostępnia firma w której masz wykupiony hosting.

Po wykupieniu hostingu (możemy też poszukać darmowego, bądź zainstalować serwer lokalnie na komputerze), logujemy się na serwer i tworzymy katalog w którym umieszczamy nasz skrypt. Katalog nie powinien być widoczny w Internecie.

Tutaj mała uwaga zanim zje mnie ktoś doświadczony – projekty tego typu tworzy się zwykle lokalnie na komputerze (stawia środowisko programistyczne razem z serwerem, a najlepiej z wykorzystaniem Dockera). Piszemy wówczas kod lokalnie, testujemy i przesyłamy automatyczne na serwer. Dodatkowo warto wprowadzić kontrolę wersji np. z użyciem gita. Kontrola wersji pozwala nam na przywrócenie np. poprzednich wersji programu jak okaże się, że coś popsuliśmy. Ponadto takie podejście pozwala na kodowanie w zespołach (za pośrednictwem np. GitHuba) i tworzenie przykładowo wersji beta, albo eksperymentalnych. Niemniej to jest szeroki temat i niech będzie tematem na inny artykuł.

Prywatnie i zawodowo do projektów korzystam m.in. z płatnego PHPStorm, ale przy prostych rzeczach wystarczy mi Notepad++ (btw. cały kod w zasadzie i tak piszę przy okazji artykułu w samym artykule, a później go kopiuję ewentualnie). Do nauki wystarczy.

Wracamy do tematu. Utwórzmy na serwerze katalog z naszym projektem i dodajmy tam pierwszy plik, którego celem będzie komunikacja z bazą danych. Zwykle konfigurację umieszcza się w osobnym pliku, ale tutaj nie będzie to konieczne.

<?php
class Db {

  private $database_name = 'lotto.db';

  private $connection;

  public function __construct() {
    $this->connection = new SQLite3($this->database_name);
  }
}
?>

Początkowo chciałem skorzystać z PDO do komunikacji (łatwiej byłoby później zmienić rodzaj obsługiwanej bazy), ale od wersji PHP 7.4 są potrzebne dodatkowe biblioteki, których na serwerze może nie być. Zostaniemy więc przy wbudowanych w klasę SQLite3 metod.

Sam kod nie wymaga mam nadzieję większego komentowania. Tworzymy połączenie z bazą danych w trybie do odczytu i zapisu. Jeżeli baza nie istnieje to zostanie utworzona. To jest działanie domyślne, ale w konstruktorze SQLite3 możemy określić w drugim parametrze w jakim trybie ma zostać nawiązane połączenie (ustawiając odpowiednie flagi – tylko odczyt, zapis i odczyt, tworzenie nowej bazy jak nie istnieje).

Podstawy języka SQL

Do komunikacji z bazą danych używamy języka SQL. Są różne jego implementacje i z tego powodu SQLite nieco różni się od MySQL, PostgreSQL czy MSSQL (tu akurat różnic jest nieco więcej), ale generalnie podstawy zostają te same. Różnice dotyczą możliwości bazy, typów danych i nowych funkcji, które pozwalają niektóre operacje wykonać efektywniej. Dla naszego projektu podstawowa wiedza wystarczy, bo większość operacji i tak będziemy wykonywać w PHP na serwerze, choć pokażę jak niektóre dane można łatwo wyciągnąć przy użyciu SQL-a..

Tabele

Każda baza składa się z tabel, które składają się z kolumn i wierszy (tzw. rekordów). Kolumny mogą zawierać dane określonego typu (np. tekst, liczba, data itp.). Najczęściej jedna z kolumn przechowuje dane, które mogą wystąpić tylko jeden raz (jest to tzw. klucz podstawowy – primary key). Przykładowo dla naszej bazy takim kluczem może być numer losowania, który jest niepowtarzalny i występuje tylko raz.

Nasza tabela z losowaniami będzie wyglądała np. tak:

Numer losowaniaData losowaniaLiczby
688725.05.20232,7,11,15,41,46
688623.05.20231,6,11,12,28,35
688520.05.20237,8,12,16,40,48
688418.05.20235,7,20,29,45,48
tabela z losowaniami Dużego Lotka

Pierwsza kolumna z numerem losowania będzie zawsze liczbą i będzie zawsze unikatowa (klucz podstawowy). Druga kolumna to data, a trzecia.. tekst. Nie możemy w prosty sposób zapisać ciągu liczb. To znaczy możemy to zrobić na kilka sposobów:

  • przechowywać w formie zwykłego tekstu
  • stworzyć dodatkowe 6 kolumn przechowujących każdą liczbę osobno
  • stworzyć dodatkowe 49 kolumn przechowujących wartości prawda / fałsz
  • stworzyć osobną tabelę w której będzie przechowywana liczba wraz z numerem losowania
  • przechowywać w formacie JSON (niedostępne jednak w SQLite) – format ten jest zwykłym tekstem, więc przechowywać można go oczywiście w każdej bazie, ale przeszukiwać itp. już nie (przynajmniej nie bez sztuczek)
  • na upartego da się jeszcze przechowywać w formie maski bitów (jedna duża liczba 64-bitowa), ale to już „lekka” przesada.

Najlepsze z praktycznego punktu widzenia będzie według mnie dodanie 6 kolumn (nazwijmy je l1,l2,l3,l4,l5,l6 – kolumny nie powinny zaczynać się od cyfry). Nazwijmy przy okazji pozostałem kolumny. Sama zaś tabela to duzy_lotek_losowania

nrdrawn_datel1l2l3l4l5l6
688725.05.20232711154146
688623.05.20231611122835
688520.05.20237812164048
688418.05.20235720294548
tabela duzy_lotek_drawns

Podstawowe typy danych

Dane zapisane w poszczególnych „kolumnach”.., mają zawsze sprecyzowany typ i najczęściej długość (ile zajmują miejsca). Niektóre typy danych to:

  • int – liczba całkowita np. 5544
  • real – liczba zmienno-przecinkowa (można ustawić dodatkowy parametr z precyzją) np. 25.66
  • datetime – data i czas
  • varchar – tekst (w nawiasie podajemy długość))
  • text – długi tekst

Dodam tylko, że gdybyśmy kiedyś chcieli porównywać do siebie dwie wartości liczb zmiennoprzecinkowych to musimy być ostrożni, gdyż typy zmiennoprzecinkowe mają to do siebie, że ich zapis w bazie jest przybliżony. Lepiej w wielu sytuacjach używać typu int (np. do zapisu kwot – mnożyć je przez 100). Ustawienie precyzji teoretycznie rozwiązuje problem, ale dalej mogą się dziać dziwne rzeczy jak zaczniemy wykonywać na nich różne operacje np. dzielenia czy mnożenia.

Podstawowe operacje na bazie

Wyróżniamy dwie podstawowe grupy operacji z których możemy wyróżnić kilka najczęściej używanych operacji.

Operacje zapisu do bazy

  • CREATE – tworzenie np. tworzenie tabeli, widoku, bazy
  • INSERT – dodawanie np. rekordu (nowego wiersza) do tabeli
  • UPDATE – aktualizowanie np. rekordu

Do powyższej kategorii możemy zaliczyć jeszcze używane od czasu do czasu polecenie ALTER TABLE, które służy do modyfikacji struktury już utworzone tabeli np. kiedy chcemy dodać lub usunąć jeszcze jedną kolumnę.

-- tworzenie nowej tabeli
CREATE TABLE nazwa_tabeli (kolumna1 typ, kolumna2 typ, kolumna3 typ);

-- dodawanie nowego rekordu do istniejącej tabeli
INSERT INTO nazwa_tabeli (kolumna1, kolumna2, kolumna3) VALUES (wartość1, wartość2, wartość3);

-- aktualizacja kolumny o nazwie kolumna2 dla rekordu w którym kolumna1 ma wartość 1234
UPDATE nazwa_tabeli SET kolumna2=nowa_wartosc WHERE kolumna1=1234

-- dodanie lub aktualizacja rekordu bez zmiany wartości dla kolumna1
INSERT INTO nazwa_tabeli (kolumna1, kolumna2, kolumna3) VALUES (wartość1, wartość2, wartość3)
  ON DUPLICATE KEY UPDATE kolumna2=VALUES(wartosc2), kolumna3=VALUES(wartosc3)

-- dodanie kolumny do istniejącej tabeli
ALTER TABLE nazwa_tabeli ADD kolumna4 typ

-- usunięcie kolumny z istniejącej tabeli
ALTER TABLE nazwa_tabeli DROP kolumna4

-- zmiana nazwy kolumny w istniejącej tabeli
ALTER TABLE nazwa_tabeli RENAME kolumna4 to nowa_nazwa

Przedstawione operacje są najczęściej używane w SQL-u, choć w praktyce CREATE TABLE będziemy wykonywać sporadycznie (głównie raz na początku), a ALTER TABLE sporadycznie – o ile kiedykolwiek. Niemniej warto zapamiętać.

Najważniejsza dla nas jest operacja INSERT, która pozwala dodawać rekordy, ewentualnie UPDATE, która w praktyce jest używana bardzo często (choć można ją zastąpić często konstrukcją INSERT INTO ... ON DUPLICATE KEY UPDATE.

Operacje odczytu z bazy

  • SELECT – pobiera rekordy z bazy według wskazanych kryteriów np. data losowania numer 6887.

SELECT jest chyba najczęściej używaną komendą przy okazji pracy z bazą danych. Za jej pomocą wyciągamy większość rzeczy. Zapytania z SELECT mogą być bardzo skomplikowane, choć w praktyce lepiej wyciągać dane częściowo. Spójrzmy na proste przykłady:

-- zwraca wszystkie rekordy z tabeli
SELECT * FROM nazwa_tabeli

-- zwraca wszystkie rekordy z tabeli w której kolumna1 ma wartość równą 222
SELECT * FROM nazwa_tabeli WHERE kolumna1=222

-- zwraca rekordy (tylko kolumnę kolumna3) z tabeli w której kolumna1 jest większa od 100 i kolumna2=20
SELECT kolumna3 FROM nazwa_tabeli WHERE kolumna1>100 AND kolumna2=20

Oczywiście operacji, które coś odczytują, bądź zapisują jest znacznie więcej i spotkasz się z nimi przy okazji nauki SQL-a. Wymieniłem tutaj jednak te z którymi prywatnie i zawodowo spotykam się najczęściej.

Tworzenie tabeli z wylosowanymi numerami

Wszystkie operacje nie wymagające większej odpowiedzi zwrotnej (np. zapis) wykonujemy za pomocą metody exec. W odpowiedzi uzyskamy tylko true lub false – w zależności od tego czy operacja się udała czy wystąpił jakiś błąd.

Operacje związane z tworzeniem tabel to operacje jednorazowe i z tego powodu można je przenieść je całkowicie poza skrypt – umieścić w osobnym pliku z rozszerzeniem .sql. W przypadku baz danych nie musimy wcale wykonywać operacji przy użyciu PHP, a za pomocą dedykowanej konsoli. Dostęp do konsoli powinniśmy otrzymać po wpisaniu sqlite3 na serwerze.

Ze względów projektowych zostawimy tworzenie tabel w skrypcie do obsługi bazy. Do naszej klasy Db.php dodajmy metodę, która wykona odpowiednią operację.

function create() {

  $query = 'CREATE TABLE duzy_lotek_drawns (
    nr int PRIMARY KEY, 
    drawn_date datetime, 
    l1 int, 
    l2 int, 
    l3 int, 
    l4 int, 
    l5 int, 
    l6 int)
  ';

  if ( !$this->connection->exec ($query) ) {
    throw new Exception ( $this->connection->lastErrorMsg() );
  }
}

W powyższym kodzie dodaliśmy metodę create w której wywołujemy metodą exec tworzenie omawianej tabeli. Jeżeli metoda zwróci błąd tworzymy wyjątek z informacją o błędzie zwracaną z wykorzystaniem metody lastErrorMsg. Kolumna nr jest kluczem podstawowym (PRIMARY KEY) co oznacza, że jest unikatowa – występuje tylko raz w bazie (nie dodasz kolejnego rekordu z tym samym numerem losowania).

Teraz utwórzmy plik „instalacyjny” w którym wywołamy napisaną metodę.

include_once Db.php

echo "Inicjuję bazę danych\n";
$db = new Db();
echo "Tworzę tabele w bazie danych\n";
$db->create();

Jeżeli wszystko działa to wywołanie skryptu powinno się zakończyć bez błędu. W przeciwnym wypadku otrzymamy stosowny komunikat.

Aby mieć pewność, że operacja tworzenia tabeli przebiegła zgodnie z planem dodajmy jeszcze jedną metodę do naszej obsługi bazy danych.

function tables() {

  $return = [];
  $query = 'SELECT * FROM sqlite_master WHERE type = "table"';
  $result = $this->connection->query ($query);
  while ($row = $result->fetchArray()) {
    $return[] = $row;
  }
}

Powyżej wykonaliśmy zapytanie do bazy, które zwraca wszystkie tabele w bazie (w MySQL podobny efekt uzyskamy wywołując SHOW TABLES). Tym razem jednak nie skorzystaliśmy z metody exec, a query. Metoda query zwraca obiekt SQLite3Result z którego możemy odczytać odpowiedź korzystając z metody fetchArray. Metoda pobiera zawsze kolejny rekord przy wywoływaniu, a jeżeli nie ma (już) rekordów to zwraca false.

Dodajmy plik w którym wywołamy to zapytanie.

include_once Db.php

$db = new Db();
$result = $db->tables();
print_r ($result);

Pobieranie losowań Dużego Lotka i dodawanie ich do bazy

Pobieranie losowań zostało wyjaśnione w artykule Web scrapping w PHP. Tutaj skupimy się bardziej na logice pobierania danych i ich zapisie do bazy. Zastanówmy się jak to zrobić najefektywniej:

  • sprawdzamy jaki ostatni numer losowania jest zapisany w bazie
  • do ostatniego numeru dodajemy 10 i pobieramy dane
  • sortujemy pobrane losowania od najmniejszego do największego numeru
  • dodajemy pojedyńczo losowania, które są większe od ostatniego numeru
  • sprawdzamy czy losowanie dodało się do bazy, jeżeli nie to wyrzucamy błąd

Pobranie numeru losowania

Jak wspominaliśmy do pobierania danych służy instrukcja SELECT. Spójrzmy na zapytanie:

SELECT MAX(nr) FROM duzy_lotek_losowania

Zaraz po SELECT wskazujemy co chcemy pobrać. Tutaj wskazujemy na kolumnę nr, ale interesuje nas tylko maksymalna wartość tego numeru – dlatego posłużyliśmy się instrukcją MAX. Dalej podajemy z jakiej tabeli chcemy pobrać dane – tutaj duzy_lotek_losowania.

Dodajmy kolejną metodę do naszej bazy:

function lastDL() {

  $query = 'SELECT MAX(nr) FROM duzy_lotek_drawns';
  $result = $this->connection->querySingle($query);
  if (empty ($result) ) return 0;
  return $result;
}

Tym razem użyliśmy metody querySingle, która działa podobnie do query, ale zamiast zwrócić tablicę rekordów (tablic), zwraca tylko jeden rekord (tablicę) lub jedno pole (w tym przypadku). W odpowiedzi zawsze otrzymamy jeden rekord lub pole, bo nie może być ich więcej (nr jest kluczem podstawowym co oznacza, że jest zawsze unikalny).

Nie ma też potrzeby wywoływania dodatkowych metod do obsługi odpowiedzi. Zwracamy po prostu wynik lub 0 jeżeli nie otrzymaliśmy niczego.

Znając ostatni numer, możemy pobrać dane z serwisu Lotto zgodnie z artykułem Web scrapping w PHP

Zapisanie numeru losowania

Mając informacje o losowaniach dodajmy je do bazy korzystając z pętli. Przypomnijmy sobie najpierw format w jakim otrzymujemy dane. Przykładowo:

    [8] => Array
        (
            [date] => Niedziela, 03.02.1957
            [nr] => 2
            [numbers] => Array
                (
                    [0] => 5
                    [1] => 10
                    [2] => 11
                    [3] => 22
                    [4] => 25
                    [5] => 27
                )

        )

    [9] => Array
        (
            [date] => Niedziela, 27.01.1957
            [nr] => 1
            [numbers] => Array
                (
                    [0] => 8
                    [1] => 12
                    [2] => 31
                    [3] => 39
                    [4] => 43
                    [5] => 45
                )

        )

Pobrane dane są wysyłane od najnowszych do najstarszych. Zależy nam jednak na tym, by zapisywać je w odwróconej kolejności. W metodzie zwracającej dane możemy użyć funkcji array_reverse, która zwróci tabelę w odwrotnej kolejności.

return array_reverse(wylosowane);

Dzięki temu do zapisu użyjemy później wygodnej pętli foreach. Dodajmy najpierw dodawanie pojedynczego losowania do bazy.

function addDL(int $drawn_nr, string $drawn_date, array $numbers) {

  foreach ($numbers as $nr) 
    if (!is_int ($nr) ) throw new Exception ('Numer nie jest typu int!');

  $numbers_frm = implode(',',$numbers);

	$matches = [];
	preg_match_all('/(\d{2}.\d{2}.\d{4})|(\d{2}:\d{2})/',$drawn_date, $matches);
	$date = implode(' ',$matches[0]);

  $query = "INSERT INTO duzy_lotek_losowania (nr, drawn_date, l1, l2, l3, l4, l5, l6)
            VALUES ($drawn_nr, \"$date\", $numbers_frm)";

  if ( !$this->connection->exec ($query) ) {
    throw new Exception ( $this->connection->lastErrorMsg() );
  }
}

W powyższym metoda oczekuje na liczbę, która jest typu integer, datę, która jest łańcuchem oraz tablicę z liczbami. Dodatkowo przez użycie is_int sprawdzamy czy w przekazanej tablicy są same liczby. Jeżeli nie ma – przerywamy program. Dalej wyciągamy datę i godzinę (o ile jest podana) korzystając z wyrażeń regularnych. Pole z datą przy dodawaniu do bazy zostało otoczone apostrofami, gdyż jest to wartość tekstowa. Z operacjami na bazie w których otrzymujemy dane z zewnątrz lepiej obchodzić się ostrożnie, gdyż wprowadzając dane bez weryfikacji moglibyśmy w tym miejscu wprowadzić złośliwy kod (atak typu SQL Injection). W naszym przykładzie użyliśmy dodatkowych funkcji, które sprawdzają czy dane są poprawne. Częściej jednak spotkamy się z wykorzystaniem do tego celu specjalnej metody prepare.

Stwórzmy teraz osobny plik w którym będziemy przechowywać podstawową logikę naszej aplikacji.. Nie dodałem tutaj funkcji związanych z pobieraniem danych z zewnętrznego źródła. Zakładam, że odpowiednie dane już są w tablicy $drawns_result.

include_once Db.php;
$db = new Db();

$lastDL = $db->lastDL();
foreach ($drawns_resultas $drawn) {

  if ($drawn['nr'] > $lastDL {
    $db->addDL($drawn['nr'], $drawn['date'], $drawn['numbers']);
    $lastDL = $drawn['nr'];
  }
}

Dla każdego losowanie sprawdzamy czy numer losowania w bazie jest większy od tego, który chcemy dodać. Jeżeli nie – dodajemy. W ten sposób do bazy będziemy zawsze dodawać tylko najnowsze numery.

Cykliczne wykonywanie skryptu

Pobieranie danych ze strony powinno odbywać się cyklicznie – co jakiś czas, albo w ustalonych godzinach. Do realizacji tego zadania możemy wykorzystać mechanizm cron-a. Wszelkie operacje wykonamy logując się do serwera przez SSH i wpisując polecenie crontab -e.

Komunikacja ze światem

Nasz skrypt pobiera dane z serwisów przynajmniej dwa razy dziennie. Zakładamy, że działa on niezależnie od wtyczki w WordPress, która jest osobnym modułem. Wtyczka pobiera dane z naszego skryptu i aktualizuje je w swojej lokalnej bazie danych. Komunikacja wtyczki z naszym skryptem może odbywać się na kilka sposobów:

  • skrypt wysyła informacje do wtyczki z nowymi danymi
  • wtyczka odpytuje skrypt o nowe dane

Analiza liczb

Wykonanie analizy liczb opisałem szerzej w artykule Analiza wylosowanych liczb w PHP. To co możemy tutaj zrobić to:

  • utworzyć nowe tabele do przechowywania wykonanych analiz dopisując ją do metody create();

Dodanie tabel do napisanej metody create() spowoduje wystąpienie błędu, gdyż wcześniejsza tabela już istnieje (no chyba, że jeszcze jej nie utworzyłeś). Aby tego uniknąć możemy nieco zmodyfikować polecenie dodając IF NOT EXISTS

  $query = 'CREATE TABLE IF NOT EXISTS duzy_lotek_drawns (
    nr int PRIMARY KEY, 
    drawn_date datetime, 
    l1 int, 
    l2 int, 
    l3 int, 
    l4 int, 
    l5 int, 
    l6 int)
  ';
  • wykonywać analizy po pobraniu danych i nadpisywać dane w bazie

Nadpisywanie danych możemy zrealizować za pomocą funkcji UPDATE lub korzystając z INSERT, ale z dodatkiem ON DUPLICATE KEY UPDATE. Na końcu wskazujemy, które kolumny mają być zaktualizowane jeżeli rekord już istnieje w bazie.

-- dodanie lub aktualizacja rekordu bez zmiany wartości dla kolumna1
INSERT INTO nazwa_tabeli (kolumna1, kolumna2, kolumna3) VALUES (wartość1, wartość2, wartość3)
  ON DUPLICATE KEY UPDATE kolumna2=VALUES(wartosc2), kolumna3=VALUES(wartosc3)

Pozostałe operacje

Pozostałe tematy opisałem już w poniższych kursach.

Skrypt na GitHub: https://github.com/Eskim83/lotto-pl-parser dostępny na licencji GPL 3.