Materiál k cvičení DBM1 týden #2, sezona 2023/2024

Příprava vývojového prostředí

DBeaver pro práci s databázemi

DBeaver je nástroj pro práci s databázemi, který podporuje různé databázové systémy. Community edice je zdarma a dostupná pro Windows, Linux a macOS.

DBeaver Community Edition lze stáhnout z oficiálních stránek. Ve většině případů stačí portable (zip) verze, která není potřeba instalovat.
Po stažení a rozbalení archivu spusťte dbeaver.exe
Stáhněte soubor ex.db.
Pomocí ikony úplně vlevo New Database Connection vytvořte nové připojení k databázi. Typ databáze je DuckDB, v dalším kroku dialogu u pole Path vyberte stažený soubor ex.db pomocí tlačítka Open.
V levém panelu Database Navigator by se měl zobrazit připojený soubot ex.db obsahující hierarchii objektů. Jedním z objektů je i ex → main → Tables → sample_user_data. Kliknutím na objekt by se měl zobrazit obsah tabulky uprostřed okna.

01dbeaverpng Fig – Základní orientace v prostředí DBeaver. Barevně zvýrazněny jsou ovládací prvky pro vytvoření nového spojení s DB (vlevo nahoře), tabulka v hierarchii objektů (vlevo), otevřený objekt k podrobnějšímu prohlížení (uprostřed nahoře), ikona pro otevření nového SQL skriptu k momentálně otevřené databázi (vlevo nahoře)

V tabulce jsou uměle vygenerovaná data popisující chování uživatelů eshopu. Data lze v případě potřeby editovat inline, tj. přímo v zobrazené tabulce dvojklikem na příslušnou buňku, či lze přidat nové řádky pomocí ovládacích prvků dole pod tabulkou. Změny je potřeba potvrdit tlačítkem Save (provede Commit operaci), či zrušit tlačítkem Revert.

SQL skript

V DBeaveru lze vytvářet a spouštět SQL skripty. Pomocí ikony SQL → New SQL script v horní části okna se otevře nový editor. Příkazy v editoru lze spouštět pomocí klávesové zkratky CTRL + enter nebo tlačítka Execute SQL statement – tím se provede příkaz, u kterého je zrovna kurzor. Příkazy SQL jsou oddělené středníkem ;. Existuje i varianta spuštění celého skriptu, tj. všech příkazů postupně za sebou.


  
-- vypsání počtu záznamů v tabulce 
SELECT count(*)
FROM sample_user_data 
;

Ověřte funkčnost například příkazem výše. Výsledek by měl být 700

Programovací prostředí (Java, IntelliJ IDEA)

Pro manipulaci s daty, jejich čištění, úpravu struktury a další pomocné skripty bude potřeba použít vybraný programovací jazyk. V rámci tohoto cvičení bude použit jazyk Java a vývojové prostředí IntelliJ IDEA, ale koncepty a postupy jsou obecné a lze je aplikovat i v jiných jazycích a prostředích (populární je obzvláště Python).

Na školních PC je vše potřebné již připraveno. Pokud budete pracovat na vlastním počítači, je potřeba nainstalovat:

Java Development Kit (JDK >=8) – pro vývoj a spouštění Java aplikací
IntelliJ IDEA Community Edition nebo jiné IDE – vývojové prostředí pro Java (nezaměňovat s IntelliJ IDEA Ultimate, která je placená)
Maven – nástroj pro správu závislostí a sestavování projektů (většinou bývá součástí instalace IntelliJ IDEA a jiných IDE)

Založení projektu v IntelliJ IDEA

Pro ověření funkčnosti Java prostředí a připojení k databázi DuckDB vytvořme nový projekt v IntelliJ IDEA.

File → New → Project... pro otevření dialogu.
Vybrat Maven Archetype jako typ šablony.
Vhodně vyplnit formulář - viz obrázek níže.

01ideaprojectpng Fig – při vytvoření projektu je zadáváno jeho jméno (název adresáře), umístění na disku, verze Java JDK, Archetyp (šablona) sestavovacího souboru. V advanced settings je vhodné změnit GroupId - typicky se používá kód_země.identifikátor_autora; a ArtifactId jakožto identifikátor projektu.

Po vytvoření by v hierarchickém stromu projektu vlevo měla být složka src obsahující App.java, ve kterém je Hello World skript. Dále je v projektu konfigurační soubor pom.xml, který používá Maven pro správu závislostí a sestavování projektu.

Zkuste spustit aplikaci pomocí zelené šipky vpravo nahoře v editoru App.java. V konzoli by se měl zobrazit text Hello World!.

Přidání knihovny pro práci s DuckDB

Pro práci s databází DuckDB je potřeba přidat závislost na knihovnu duckdb-jdbc do souboru pom.xml. V souboru by již měla existovat sekce <dependencies>, kam se přidá nový záznam.


  
  <dependencies>
    <dependency>
      <groupId>org.duckdb</groupId>
      <artifactId>duckdb_jdbc</artifactId>
      <version>0.10.0</version>
    </dependency>

    <!-- a další závislosti ... -->
        
  </dependencies>

※ Kód výše lze najít na titulní straně webu DuckDB v sekci Installation. Obecně je vhodné závislosti hledat na Maven Central.

Tato knihovna slouží pro připojení k databázi DuckDB pomocí JDBC. JDBC je standardní rozhraní pro připojení k relačním databázím v Javě a kód využívající JDBC je obecně přenositelný mezi různými databázovými systémy. Tzn. pokud byste potřebovali použít jiný databázový systém, mělo by stačit jen změnit závislost a drobně upravit část kódu řešící připojení.

Při editaci pom.xml ještě zkontrolujte, že je správně nastavena verze JDK. V horní části souboru by měl být záznam <properties> s položkou maven.compiler.source a maven.compiler.target nastavenou na verzi JDK, kterou máte nainstalovanou.


  
  <properties>
    <!-- pro verzi JDK 8 -->
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

Připojení k databázi

Pro ověření funkčnosti připojení k databázi DuckDB využijme třídu App obsahující vygenerovaný Hello World a upravme ji tak, aby se připojila k databázi a provedla jednoduchý dotaz.


  
import java.sql.*;

public class App 
{
    public static void main( String[] args ) throws ClassNotFoundException, SQLException {
        Class.forName("org.duckdb.DuckDBDriver");
        Connection conn = DriverManager.getConnection("jdbc:duckdb:ex.db");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT count(*) FROM sample_user_data;");
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

V kódu je nejprve načten ovladač pro DuckDB, poté se vytvoří spojení k databázi a vytvoří se nový Statement pro provedení SQL příkazu. Výsledek je uložen do proměnné typu ResultSet a postupně se z něj vypisují jednotlivé řádky. Jelikož jde o dotaz vracející jediný sloupec s jedním řádkem, v konzoli by se měl zobrazit výsledek dorazu 700.

Při spuštění mohlo dojít k několika problémů.

Pokud je problém s nenalezenou třídou org.duckdb.DuckDBDriver je nutné manuálně obnovit projekt skrze Maven. Ro lze provést pravým kliknutím na název projektu v levém hierarchickém zobrazení a volbou Maven → Reload Project.
Pokud je problém s chybějící tabulkou sample_user_data, zřejmě je špatně nastavena cesta ke staženému souboru (v kódu je předpokládané umístění v root projektu). Lze upravit cestu například tímto způsobem DriverManager.getConnection("jdbc:duckdb:C:\\Users\\Keiras\\db\\ex.db");
SQLException většinou bude znamenat, že je špatně napsán SQL příkaz. Zkontrolujte překlepy, případně se zorientujte pomocí chybového výpisu obsahující znak, na kterém došlo k chybě při zpracování.
Může být problém kvůli tomu, že databázi (soubor) máte otevřený v DBeaver. V tom případě je potřeba zavřít spojení v DBeaveru (pravým kliknutím na spojení a volbou Disconnect).

Q1: Zkuste upravit kód tak, abyste v Java postupně načetli všechny řádky z tabulky sample_user_data a vypsali je do konzole.

Výsledek

1, 2023-01-01, 11, No
1, 2023-01-02, 119, No
1, 2023-01-03, 68, No
1, 2023-01-04, 62, No
1, 2023-01-05, 60, Yes
...

Q2: Vypište v Java postupně ID zákazníků seřazené podle času stráveného na stránce sestupně.

Výsledek

74, 629
38, 621
90, 612
71, 603
69, 575
...

ChatGPT, Copilot a jiné LLM nástroje

Během práce na cvičení se může hodit nějaký nástroj pro generování kódu, či ukázkových dat. V poslední době je poměrně populární používat nástroje založené na velkých jazykových modelech (LLM), které na základě zadaného textu generují odpovídající kód, text, či jiný obsah.

Za sebe mohu doporučit ChatGPT od OpenAI, který je zdarma a lze ho používat přímo v prohlížeči. Typické použití je specifikace problému např. Generate me some sample csv data., na základě kterého mi vygeneroval sample_user_data.csv uložené v ex.db. Případně vložení ukázkových dat a požadování vygenerování vhodné struktury Java objektu nebo SQL dotazu.

※ Stejně jako u všech cloudových věcí, které nemáte plně pod kontrolou, je hloupé do nich vkládat citlivá data.

※ Je potřeba být ostražití a zkontrolovat, jestli vygenerovaný kus kódu nebo myšlenka dává smysl. Občas nástroje (z principu svého fungování) halucinují nebo nabízí řešení, které v používané verzi knihoven/nástrojů není podporované.