Tiny Radio Player #08 – Baza danych

Dane aplikacji możemy przechowywać w różny sposób. Możemy je zapisywać w plikach, w bazie danych lub w chmurze. W naszym przypadku będą to słowniki oraz dane stacji więc dla przejrzystości moglibyśmy wybrać plik xml. Jednak w celach edukacyjnych użyjemy bazy danych SQLite oraz biblioteki ZeosLib (opis jej instalacji znajdziesz w tym miejscu).

Instalacja SQLite

Właściwie nie można mówić o instalacji bo jedyne co musimy zrobić to ściągnąć odpowiedni plik biblioteki SQLite i umieścić go w katalogu ./data/lib. Następnie już z poziomu kodu aplikacji, korzystając z biblioteki ZeosLib, ustanowić połączenie do pliku bazy danych. Jeżeli plik bazy danych jeszcze nie istnieje to zostanie utworzony przy pierwszym uruchomieniu aplikacji.

Pliki biblioteki SQLite można znaleźć pod adresem http://www.sqlite.org lub bezpośrednio w repozytorium TinyRadioPlayer w katalogu ./lib/sqlite/.

• Windows – pobieramy plik sqlite3.7z, rozpakowujemy i umieszczamy w katalogu bin\i386-win32\data\lib
• Linux – pobieramy plik libsqlite3.7z, rozpakowujemy i umieszczamy w katalogu bin/x86_64-linux/data/lib
• MacOSX –  pobieramy plik libsqlite.7z, rozpakowujemy i umieszczamy w katalogu bin/i386-darwin/data/lib

Nawiązanie połączenia

Dla przejrzystości przedstawię tylko prosty przykład tworzenia połączenia z bazą danych, resztę znajdziecie w repozytorium na GitHubie. Aby jednak nie zostawiać Was z niepełną wiedzą opiszę również ogólną koncepcję jaka przyświecała mi podczas projektowania dostępu do bazy danych.

Główną część zarządzającą bazą danych umieścimy w pliku BaseRepository. Jest to klasa abstrakcyjna, której zadaniem będzie nawiązanie połączenia z bazą danych, utworzenie schematu nowej bazy oraz podpięcie pozostałych repozytoriów. Dodatkowo znajdą się tutaj również metody abstrakcyjne, których implementacja zostanie przeniesiona do klas dziedziczących. Dla przykładu metoda CreateDML (tworząca domyślne dane) zostanie zaimplementowana w klasie MainRepository. Taki sposób da nam możliwość podpięcia się pod inną bazę danych, np. bazę z ulubionymi stacjami.

Do utworzenia połączenia z bazą danych użyjemy komponentu TZConnection. Jest on częścią pakietu zcomponent więc dodajemy go do Required Packages w oknie Project Inspector.

Teraz możemy nawiązać połączenie z bazą danych.

function TBaseRepository.Connect(const DBName: string): ErrorId;
var
  err: ErrorId;
  filePath: string;
begin
  err := ERR_OK;

  if DBName = EMPTY_STR then
    err := ERR_DB_NO_DATABASE_NAME;

  if err = ERR_OK then
  begin
    err := Disconnect;

    if err = ERR_OK then
    begin
      try
        FConnection := TZConnection.Create(nil);
        FConnection.Protocol := 'sqlite-3';
        FConnection.LibraryLocation :=
          ConcatPaths([GetApplicationPath, LIB_PATH,
          {$IFDEF MSWINDOWS}
          'sqlite3.dll'
          {$ENDIF}
          {$IFDEF LINUX}
          'libsqlite3.so'
          {$ENDIF}
          {$IFDEF MACOS}
          'sqlite3.dylib'
          {$ENDIF}
          ]);

        // Try connect to database
        if not FileExists(DBName) then
        begin
          // check if directory exists, if not create it
          filePath := ExtractFilePath(DBName);
          if not DirectoryExists(filePath) then
            if not CreateDir (filePath) then
            begin
              Result := ERR_DB_CREATE_DIR;
              Exit;
            end;

          FConnection.Database := DBName;
          FConnection.Connect;
          CreateDB;
        end else
        begin
          FConnection.Database := DBName;
          FConnection.Connect;
          SetDBSettings;
        end;

      except
        on E: Exception do
        begin
          LogException(EmptyStr, ClassName, 'Connect', E);
          //
          err := ERR_DB_CONNECT_ERROR;
        end;
      end;

    end;
  end;

  Result := err;
end;

Ważne jest, aby prawidłowo ustawić protokół z jakiego korzystamy oraz podać dokładne ścieżki do biblioteki SQLite i pliku bazy danych. Jeżeli plik bazy danych wcześniej nie istniał to przy nawiązaniu połączenia zostanie utworzony.

Tworzenie nowej bazy

Nowo utworzony plik bazy danych nie zawiera tabel oraz danych więc musimy utworzyć je sami.

function TBaseRepository.CreateDB: ErrorId;
var
  err: ErrorId;
begin
  err := ERR_OK;

  try
    // Database settings

    // The Boolean synchronous value controls whether or not the
    // library will wait for disk writes to be fully written to disk
    // before continuing. In typical use the library may spend a lot of
    // time just waiting on the file system.
    // Setting "PRAGMA synchronous=OFF" can make a major speed difference.
    FConnection.ExecuteDirect('PRAGMA synchronous = OFF;');

    // The temp_store values specifies the type of database back-end to use
    // for temporary files.
    // The choices are DEFAULT (0), FILE (1), and MEMORY (2).
    // The use of a memory database for temporary tables can produce
    // signifigant savings. DEFAULT specifies the compiled-in default,
    // which is FILE unless the source has been modified.
    FConnection.ExecuteDirect('PRAGMA temp_store = MEMORY;');

    // The default behavior of the LIKE operator is to ignore case for
    // .SCII characters
    FConnection.ExecuteDirect('PRAGMA case_sensitive_like = OFF;');

    // Unless already in a transaction, each SQL statement has a new
    // transaction started for it. This is very expensive, since it requires
    // reopening, writing to, and closing the journal file for each statement.
    // This can be avoided by wrapping sequences of SQL statements with
    // BEGIN TRANSACTION; and END TRANSACTION; statements.
    // This speedup is also obtained for statements which don't alter
    // the database.
    // The keyword COMMIT is a synonym for END TRANSACTION.
    FConnection.ExecuteDirect('BEGIN TRANSACTION;');

    err := CreateDDL;

    if err = ERR_OK then
      err := CreateDML;

    if (err = ERR_OK) and (not FConnection.ExecuteDirect('COMMIT;')) then
      err := ERR_DB_CREATE_ERROR;

    if err <> ERR_OK then
      FConnection.ExecuteDirect('ROLLBACK;');

  except
    on E: Exception do
    begin
      LogException(EmptyStr, ClassName, 'CreateDB', E);
      FConnection.ExecuteDirect('ROLLBACK;');
      err := ERR_DB_CREATE_ERROR;
    end;
  end;

  Result := err;
end;

Aby przyspieszyć zapis i odczyt z bazy danych zmieniamy kilka ustawień. Przede wszystkim ustawiamy, aby wszystkie tymczasowe tabele były trzymane w pamięci. Dodatkowo wyłączamy synchroniczny zapis co da możliwość dodawania dużej ilości danych bez zbędnego czekanie na ukończenie zapisu do pliku. Wyłączamy również rozróżnianie wielkości liter przy korzystaniu z operatora LIKE.

Cały proces tworzenia schematu i dodawania danych umieszczamy w transakcji. W taki sposób wyeliminujemy konieczność tworzenia transakcji dla każdej operacji.

Tworzenie schematu bazy danych

Schemat bazy danych utworzymy w metodzie CreateDDL.

function TBaseRepository.CreateDDL: ErrorId;
var
  query: TZQuery;
  err: ErrorId;

  procedure ExecuteQuery(AQuery: string);
  begin
    query.SQL.Add(AQuery);
    query.ExecSQL;
    query.SQL.Clear;
  end;

begin
  err := ERR_OK;

  try
    query := TZQuery.Create(nil);
    try
      query.Connection := FConnection;

      // Stations
      ExecuteQuery(
        'CREATE TABLE ' + DB_TABLE_STATIONS + ' (' +
        'ID INTEGER PRIMARY KEY NOT NULL, ' +
        'Name VARCHAR NOT NULL, ' +
        'StreamUrl VARCHAR NOT NULL, ' +
        'Description TEXT NULL, ' +
        'WebpageUrl VARCHAR NULL, ' +
        'GenreCode VARCHAR NULL, ' +
        'CountryCode VARCHAR NULL, ' +
        'Created INTEGER NOT NULL, ' +
        'Modified INTEGER NULL);');

    finally
      query.Free;
    end;

  except
    on E: Exception do
    begin
      LogException(EmptyStr, ClassName, 'CreateDDL', E);
      err := ERR_DB_CREATE_DDL_ERROR;
    end;
  end;

  Result := err;
end; 

Tworzymy zapytanie i wskazujemy aktywne połączenie z bazą danych. Następnie wykonujemy je co powoduje utworzenie nowej tabeli Stations.

Tworzenie danych

Dane słownikowe oraz informacje o stacjach utworzymy w metodzie CreateDML. Jest to metoda abstrakcyjna, której wywołanie znajduje się w klasie TBaseRepository. Jej implementacja została natomiast przeniesiona do klasy dziedziczącej TMainRepository.

function TMainRepository.CreateDML: ErrorId;
var
  err: ErrorId;
begin
  err := ERR_OK;

  try
    err := CreateDictionaries;
    err := CreateStations;
  except
    on E: Exception do
    begin
      LogException(EmptyStr, ClassName, 'CreateDML', E);
      err := ERR_DB_CREATE_DML_ERROR;
    end;
  end;

  Result := err;
end;

function TMainRepository.CreateStations: ErrorId;
var
  err: ErrorId;
  stationId: integer;
begin
  err := ERR_OK;

  // Stations
  err := StationRepo.AddStation('Radio Kaszebe', 'http://stream3.nadaje.com:8048',
    EMPTY_STR, 'http://radiokaszebe.pl/', 'Pop', 'PL', stationId);

  err := StationRepo.AddStation('Radio Malbork', 'http://78.46.246.97:9022',
    EMPTY_STR, 'https://www.radiomalbork.fm/', 'Pop', 'PL', stationId);

  err := StationRepo.AddStation('Planeta RnB', 'http://plarnb-01.cdn.eurozet.pl:8216/',
    EMPTY_STR, 'https://www.planetafm.pl/', 'RnBSoul', 'PL', stationId);

  Result := err;
end;

function TStationRepository.AddStation(const StationName: string;
  const StreamUrl: string; const Description: string; const WebpageUrl: string;
  const GenreCode: string; const CountryCode: string; out StationId: integer): ErrorId;
var
  query: TZQuery;
  err: ErrorId;
  dateNow: integer;
begin
  err := ERR_OK;

  try
    StationId := TRepository.GetNewDbTableKey(DB_TABLE_STATIONS);
    dateNow := GetUnixTimestamp();

    query := TZQuery.Create(nil);
    try
      query.Connection := TRepository.GetDbConnection;

      query.SQL.Add(
        'INSERT INTO ' + DB_TABLE_STATIONS +
        ' (ID, Name, StreamUrl, Description, WebpageUrl, GenreCode, CountryCode, Created, Modified) ' +
        'VALUES(:ID,:Name,:StreamUrl,:Description,:WebpageUrl,:GenreCode,:CountryCode,:Created,:Modified);'
      );

      query.Params.ParamByName('ID').AsInteger := StationId;
      query.Params.ParamByName('Name').AsString := StationName;
      query.Params.ParamByName('StreamUrl').AsString := StreamUrl;

      if (Description <> EMPTY_STR) then
        query.Params.ParamByName('Description').AsString := Description;

      if (WebpageUrl <> EMPTY_STR) then
        query.Params.ParamByName('WebpageUrl').AsString := WebpageUrl;

      if (GenreCode <> EMPTY_STR) then
        query.Params.ParamByName('GenreCode').AsString := GenreCode;

      if (CountryCode <> EMPTY_STR) then
        query.Params.ParamByName('CountryCode').AsString := CountryCode;

      query.Params.ParamByName('Created').AsInteger := dateNow;
      query.Params.ParamByName('Modified').AsInteger := dateNow;

      query.ExecSQL;

    finally
      query.Free;
    end;
  except
    on E: Exception do
      begin
        LogException(EMPTY_STR, ClassName, 'AddDatabaseStation', E);
        err := ERR_DB_ADD_STATION;
      end;
  end;

  Result := err;
end;

W powyższym kodzie widać, że powstało dodatkowe repozytorium o nazwie StationRepo. W taki sposób odseparowaliśmy operacje związane z dostępem do danych stacji. Istnieje również kolejne repozytorium o nazwie DictionaryRepo, które agreguje operacje słownikowe.

Pobieranie danych

Wszystkie operacje na danych zapisanych w bazie danych wykonujemy przy użyciu TZQuery, więc aby pobrać dane wystarczy wykonać komendę SELECT.

Pobranie pojedynczej wartości na przykład maksymalnego ID tabeli może wyglądać mniej więcej tak:

query := TZQuery.Create(nil);
try
  query.Connection := FConnection;

  // sql query
  query.SQL.Add('SELECT MAX(ID) FROM ' + TableName + ';');

  query.Open;

  if query.RecordCount = 1 then
	Result := query.Fields[0].AsInteger;

finally
  query.Free;
end;

Pobranie wielu rekordów wygląda podobnie, jedyną różnicą jest konieczność iteracji po obiekcie query:

query := TZQuery.Create(nil);
try
  query.Connection := TRepository.GetDbConnection;

  query.SQL.Add(
	'SELECT ' +
	'  S.ID, S.Name, S.GenreCode, DRG.Text AS GenreText, S.CountryCode, DRC.Text AS CountryText ' +
	'FROM ' + DB_TABLE_STATIONS + ' S ' +
	'WHERE S.Name LIKE :StationName;'
  );
  
  query.ParamByName('StationName').AsString := '%radio%';
  
  query.Open;
  
  while not query.EOF do
  begin
    myId := query.FieldByName('ID').AsInteger,
    myName := query.FieldByName('Name').AsString,
    myGenre := query.FieldByName('GenreText').AsString,
    myCountry := query.FieldByName('CountryText').AsString
	
    query.Next;
  end;
finally
  query.Free;
end;

Podsumowanie

Cały proces zarządzania bazą danych został rozbity na repozytoria. Głównym jest BaseRepository. Jest to klasa abstrakcyjna więc nie możemy utworzyć jej instancji. Jej zadaniem jest ustanowienie połączenia z bazą danych, utworzenie schematu dla nowych baz oraz podpięcie pozostałych repozytoriów takich jak StationRepo oraz DictionaryRepo.

Z BaseRepository dziedziczy MainRepository. Jest to repozytorium, do którego podpięty jest główny plik bazy danych i to właśnie przez te repozytorium wykonujemy wszystkie operacje na bazie danych.

Dodatkowo tworzymy repozytorium Repository, które jest wrapperem dla pozostałych repozytoriów. Tutaj tworzymy instancję MainRepository oraz mapujemy wszystkie operacje bazodanowe, do których powinny mieć dostęp inne obszary aplikacji.

Kod aplikacji dostępny jest na GitHubie. Jeżeli interesują Cię zmiany dotyczące tylko tego wpisu to znajdziesz je tu.

• Tiny Radio Player #01 – Wprowadzenie
• Tiny Radio Player #02 – Instalacja komponentów
• Tiny Radio Player #03 – Roboczy interfejs aplikacji
• Tiny Radio Player #04 – Budujemy silnik
• Tiny Radio Player #05 – Zapis ustawień aplikacji
• Tiny Radio Player #06 – Zmiana języka aplikacji
• Tiny Radio Player #07 – Logowanie błędów
• Jesteś tu => Tiny Radio Player #08 – Baza danych
• Tiny Radio Player #09 – Zarządzanie bazą SQLite
• Tiny Radio Player #10 – Lista stacji radiowych, konfiguracja VirtualTreeView