OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

Program MySQL Workbench z ER diagramu automaticky vygeneruje databázi MySQL, ke které se pak připojíme programem Base. Čtenář, který se chce seznámit jen se základy práce v Base, může tento díl přeskočit. Tomu, kdo chce v budoucnu prostřednictvím Base pracovat třeba s víceuživatelskou databází na internetu, by se ale díl mohl hodit.

Odpovědi na otázky ze shrnutí 6. dílu

Logický model databáze

1. Vysvětlete, v čem spočívá přechod od konceptuálního k logickému modelu.

   Hrubší plán uspořádání databáze, ve kterém vystupují entity s vlastnostmi (atributy) a s různými vztahy mezi nimi a kterému by měl porozumět i laik, se kterým konzultujeme plnění požadavků na databázi, je nahrazen konkrétnějším plánem, složeným z tabulek se sloupci a z vazeb mezi nimi.

2. Jak se dá využít toho, že tento přechod má jasně daná pravidla (postupy)? Vysvětlete zkratku CASE.

   Jasná pravidla převodu od konceptuálního k logickému modelu umožňují ponechat provedení tohoto procesu na programu. Takový komplexnější program, který umožní nejen nakreslit ER diagram, ale dokáže ho i převést na tabulky a vazby, zahrnujeme mezi CASE nástroje, ze slov Computer-Aided Software Engineering, počítačem podporované softwarové inženýrství. Právě tento automatizovaný převod je popsán v tomto dílu seriálu.

3. Popište přechod od vztahu M – M mezi dvěma entitami v ER diagramu k obdobně fungující vazbě mezi jim odpovídajícími tabulkami v databázi.

   Je potřeba vytvořit třetí, zprostředkující tabulku. Primární klíče ze dvou původních tabulek se stanou cizími klíči této třetí tabulky. To znamená, že tato pomocná tabulka bude ve vazbě 1 – M s původními tabulkami na straně M. Je to zobrazeno na obrázku v první části předchozího dílu.

2. možnost: přechod k logickému modelu programem Workbench

V minulém dílu jsme si uvedli ER diagramy pro databázi Knihovna v jednodušší verzi „B“ i v o něco složitější verzi „A“. Logický model – tabulky a vazby – jste potom vytvářeli v Base sami. Nyní můžete zkusit využít pomoc automatického vygenerování databáze programem MySQL Workbench. Vytvoření databáze proběhne ve dvou krocích:

• Krok 1: Vygenerování databáze MySQL z ER diagramu programem MySQL Workbench
• Krok 2: Přístup z Base k této databázi MySQL

Oba kroky jsou rozsáhlejší, a proto jejich postup ještě rozdělím na několik částí.

Krok 1: Vytvoření databáze MySQL z ER diagramu programem MySQL Workbench

Tento krok rozdělím na následující části:

• Úvodní informace o programu MySQL Workbench
• Nakreslení ER diagramu v programu
• Instalace serveru MySQL
• Vytvoření spojení programu Workbench s databázovým serverem
• Vygenerování databáze MySQL z diagramu
• Připojení se k databázi na serveru.

Informace o programu MySQL Workbench. Nástrojem CASE, který zvládne přechod k logickému modelu, je i tento program. Stránka pro stažení instalačního souboru nabízí verzi pro platformy Microsoft Windows, několik distribucí Linuxu a Mac OS X (v Ubuntu stačí otevřít Centrum Softwaru pro Ubuntu). Základní informace o programu je třeba v práci „Porovnání vybraných CASE nástrojů“ Evy Menčlové z VŠB-TU Ostrava nebo v blogu Romana Ožana. Na stránkách mysql.com je podrobnější popis programu, popř. důkladný pdf manuál (pokud by se vám déle načítal, vydržte, má 256 stran) a kvalitní nápovědu nabízí i samotný program.

Nakreslení ER diagramu: uvádím jen základní rady. Volba File | New Model (také [Ctrl+N] nebo ikona New Document) otevře novou záložku Model, v jejíž horní části EER Diagrams se budou zobrazovat ER diagramy, níže v části Physical Schemata tabulky databáze MySQL a další prvky databáze dle potřeby. Databáze je v sekci Physical Schemata standardně pojmenována „mydb“. Poklepejte na tento název a v dolní části okna se otevře záložka Schema pro nastavení databáze. Změňte název databáze např. na „KnihovnaVerzeB“ a můžete vybrat vhodné kódování, třeba „utf8 – utf8_czech_ci“ (podrobněji o kódování na cs.wikiversity.org). Poklepáním na ikonu Add Diagram se pak otevře další záložka pro kresbu ER diagramu.

 

Část okna EER Diagram

Při kreslení diagramu využijete hlavně ikonu Place a New Table (rychleji klávesu T) a klepnutí na požadované místo v ploše diagramu. Po dvojkliku na vzniklou tabulku se v dolní oblasti objeví několik záložek: zatím potřebujete jen první z nich pro změnu jména tabulky a druhou pro zadávání sloupců tabulky. (Jak je vidět, MySQL Workbench používá už při kreslení ER diagramu spíše terminologii logického než konceptuálního modelu). Tyto záložky zavřete souhrnně křížkem v jejich záhlaví. Dál upotřebíte ikony vazeb, především první dvě, vazby 1 – 1 a 1 – M nakreslené čárkovaně (tzv. neidentifikující vazby, viz poznámka níže), popř. předposlední vazbu M – M. Nepřekvapí vás už, že vazbu se podaří vytvořit teprve tehdy, když v tabulkách založíte primární klíče (cizí klíče do vazby si už Workbench doplní sám). Při vytváření vazeb můžete sledovat nápovědu ve stavovém řádku.

Vysvětlení rozdílu mezi neidentifikující a identifikující vazbou, popř. silnou a slabou entitou, nebudeme v našem seriálu potřebovat. Zájemce může tyto pojmy najít v materiálech, nabídnutých na konci předchozího dílu.

Instalace serveru MySQL. Jestli jste dosud s databází MySQL nepracovali, budete si asi muset také doinstalovat server MySQL. Nelekejte se, je to jednoduché – pro tyto pokusy postačí lokální server na vašem počítači, jehož instalace je snadná. Instalační soubor MySQL Community Server stáhnete opět ze stránek mysql.com, je možno vybrat verzi pro velké množství operačních systémů (v Ubuntu je server nabízen přímo v Centru softwaru pro Ubuntu).

Bylo by příjemné vyzkoušet vygenerování databáze na vzdálený server některého z webhostingů, ale to se vám v současnosti nepodaří (aspoň pokud si za takovou nadstandardní službu nepřiplatíte, momentálně by to obnášelo tarif asi od 180 Kč měsíčně výše, např. u webhostingu OneBit, všimněte si řádku „Externí správa MySQL“, který je v přehledu na devátém řádku od konce). K databázím, které jsou součástí webových stránek, je možné běžně přistupovat jen pomocí grafického rozhraní phpMyAdmin (pro tvorbu a úpravy databáze) nebo jazykem php (při přístupu z internetových stránek). Přímý přístup, který by se právě nám hodil, je zakázán z bezpečnostních důvodů i z důvodů zneužívání databáze k dalším účelům, jak se lze dočíst na různých internetových fórech.

Vytvoření spojení programu Workbench s databázovým serverem. Aby mohl Workbench spolupracovat s MySQL serverem, musíte v něm ještě vytvořit spojení s tímto (v našem případě lokálním) serverem. Ten je v programu Workbench označen zkratkou DBMS (DataBase Management System, tento pojem znáte i pod zkratkou SŘBD, Systém Řízení Báze Dat). V programu zvolte z horního menu volbu Database | Manage Connections... Otevře se nové okno Manage DB Connections.

 

Okno průvodce pro vytvoření spojení se serverem MySQL

V levém dolním rohu okna klikněte na tlačítko New. V horní části okna pak změňte nabízený název spojení „New connection 1“ na požadované jméno, třeba „DomaciServerMySQL“. Všimněte si ještě, že uživatelské jméno je přednastaveno na „root“, můžete zadat i heslo, když kliknete na odpovídající tlačítko Store in Keychain... Stále ještě ve stejném okně nyní ověřte možnost spojení s MySQL serverem kliknutím na spodní tlačítko Test Connection. Pokud se vypíše následující hlášení:

Hlášení o uskutečněném spojení

potom je vše v pořádku a tímto spojením budete moci s vytvářenou databází MySQL na serveru MySQL pracovat. Pokud se spojení nedaří, zkontrolujte, jestli jste nezapomněli nainstalovat server dle předchozích odstavců a jestli je server spuštěný.

Vygenerování tabulek a vazeb databáze MySQL z diagramu. Konečně jsme se dostali k cíli prvního kroku tohoto dílu seriálu. V nabídce Database horního menu vyberte volbu Forward Engineer... Ta otevře průvodce s názvem Forward Engineer to Database, v němž pro naše potřeby není nutné už téměř nic nastavovat. Ve třetí části průvodce (Review SQL Script) si můžete prohlédnout vygenerované příkazy jazyka SQL (viz níže). Ve čtvrté části (Connection Options) je nutné vybrat (nebo teprve vytvořit) spojení se serverem MySQL a nastavit jeho vlastnosti, my ale nemusíme měnit nic, snad jen zadat heslo. Obvyklé tlačítko Next je v této části průvodce nahrazeno tlačítkem Execute. Po kliknutí na něj bude v páté, poslední části průvodce (Commit Progress) pomocí zatržítek naznačeno, že byly provedeny dva kroky: připojení k serveru (Connect to DBMS) a příkazy jazyka SQL (Execute Forward Engineered Script). Po zavření průvodce se dostanete zpět do programu Workbench.

Vraťme se ještě ke třetímu kroku průvodce, který nabízí prohlídku a případné úpravy příkazů jazyka SQL, které byly programem vygenerovány na základě ER diagramu a které se v posledním kroku provedou a vytvoří tak na serveru databázi. Jako příklad uvádím ukázku části vygenerovaného kódu pro Knihovnu verze „B“ (stručný komentář následuje níže v kapitolce Jazyk SQL):

...
-- -----------------------------------------------------
-- Table `mydb`.`Spisovatel`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Spisovatel` (
 `idSpisovatel` INT NOT NULL AUTO_INCREMENT ,
 `Prijmeni` VARCHAR(30) NULL ,
 `KrestniJmeno` VARCHAR(45) NULL ,
 PRIMARY KEY (`idSpisovatel`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Ctenar`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Ctenar` (
 `idCtenar` INT NOT NULL AUTO_INCREMENT ,
 `Prijmeni` VARCHAR(30) NOT NULL ,
 `Jmeno` VARCHAR(30) NULL ,
 `DatumVypujcky` DATE NOT NULL ,
 PRIMARY KEY (`idCtenar`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Kniha`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Kniha` (
 `idKniha` INT NOT NULL AUTO_INCREMENT ,
 `Nazev` VARCHAR(45) NULL ,
 `RokVydani` YEAR NULL ,
 `Ctenar_idCtenar` INT NULL ,
 `Spisovatel_idSpisovatel` INT NULL ,
 PRIMARY KEY (`idKniha`) ,
 INDEX `fk_Kniha_Ctenar1` (`Ctenar_idCtenar` ASC) ,
 INDEX `fk_Kniha_Spisovatel1` (`Spisovatel_idSpisovatel` ASC) ,
 CONSTRAINT `fk_Kniha_Ctenar1`
 FOREIGN KEY (`Ctenar_idCtenar` )
 REFERENCES `mydb`.`Ctenar` (`idCtenar` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION,
 CONSTRAINT `fk_Kniha_Spisovatel1`
 FOREIGN KEY (`Spisovatel_idSpisovatel` )
 REFERENCES `mydb`.`Spisovatel` (`idSpisovatel` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;
...

Připojení se k databázi na serveru. Abyste si vyzkoušeli, že databáze byla na serveru vytvořena a že funguje, připojte se k ní volbou Database | Query Database. Otevře se už povědomé okno Connect to Database. Vyberte vytvořené spojení (např. DomaciServerMySQL) a potvrďte. Po zadání hesla se otevře další záložka programu Workbench: SQL Editor.

 

Část okna s dotazem na databázi.

Jak ukazuje obrázek, ve spodní části SQL Editoru je několik dalších záložek. V první (Overview) by se zobrazil seznam všech databází, vytvořených s daným spojením (pro naše potřeby navrhuji zbytečné databáze vymazat, podrobněji o tom píši níže v upozornění v části Připojení se k databázi MySQL pomocí Base). U každé databáze uvidíte v této záložce její tabulky a pokud na některou poklepáte myší, objeví se další záložka s názvem tabulky a s jejími záznamy. Zde můžete také zadávat do tabulky další hodnoty. Přitom se v horní části okna v záložce SQL Query ukazují příkazy v jazyce SQL, které budou ve skutečnosti v databázi provedeny (např. po poklepání na tabulku Ctenar – to znamená, že chcete upravovat její obsah – se zde objeví příkaz EDIT `KnihovnaVerzeB`.`Ctenar`;). Na obrázku je vidět naše situace, kdy do tabulky Ctenar nebyly zatím zadány žádné záznamy, takže je ve všech buňkách prvního řádku hodnota NULL.

Jazyk SQL

Pro někoho je tento text možná prvním setkáním s jazykem SQL. Zkratka vychází ze slov Structured Query Language (strukturovaný dotazovací jazyk). Jazyk SQL se skládá se ze dvou základních částí:

• DDL (Data Definition Language, jazyk pro definici dat) – pro vytváření databáze i jejích objektů (tabulek, pohledů, vazeb, indexů). Příklad: jeden z příkazů jazyka DDL je CREATE TABLE, který se v předchozí ukázce vyskytuje třikrát. Mezi omezeními (CONSTRAINT) jsou u třetí tabulky zadány dva cizí klíče (FOREIGN KEY) a odpovídající vazba (příkaz REFERENCES).
• DML (Data Manipulation Language, jazyk pro manipulaci s daty) – pro vkládání dat do databáze (příkazem INSERT), pro jejich aktualizaci (UPDATE), mazání (DELETE) a hlavně pro výběr hledaných dat (příkazem SELECT). S některými příkazy se v dalších dílech občas setkáte.

Krok 2: Přístup z Base k databázi MySQL, vygenerované z programu Workbench

Workbench tedy vytvořil z ER diagramu logický model – databázi MySQL (tím, že jde konkrétně o databázi MySQL, jde vlastně už i o model fyzický). Ze zmínky v předchozím dílu víte, že se Base umí k takové „externí“ databázi připojit a fungovat k ní jako tzv. Front-end, čili program (grafická nadstavba), který usnadňuje práci s externí databází grafickým uživatelským rozhraním. Myslím, že oproti používání grafického rozhraní phpMyAdmin (nemluvě o práci v textovém režimu) a oproti nutnosti používání jazyka php u databází na webu je použití programu Base velmi příjemné, včetně různých průvodců, snadného vytvoření i složitějších formulářů, sestav (výpisů) apod. Obzvláště pro menší databáze v rámci firmy, školy apod. by toto mohla být zajímavá alternativa k nutnosti programování internetových stránek.

Postup připojení Base k databázi si opět rozdělíme na několik částí:

• Instalace potřebného rozhraní
• Připojení se k databázi MySQL pomocí Base

Instalace potřebného rozhraní. Rozhraní umožní, aby k datům databáze mohla přistupovat i jiná aplikace (v našem případě Base). Takovým rozhraním je ODBC (Open DataBase Connectivity) nebo JDBC (Java Database Connectivity), ale nově (od verze OpenOffice 3.1) se můžete obejít i bez nich a použít nativní (přirozené) spojení MySQL s OpenOffice.org pomocí rozšíření MySQL Connector.

O rozhraních vybírám jen základní zmínku ze zdroje reboot.cz: „Jedná se o standardní aplikační rozhraní (tzv. API) pro přístup k datům. Jejich použitím mohou aplikace přistupovat k datům nezávisle na tom, jakým systémem pro řízení báze dat (SŘBD resp. DBMS) jsou tato data spravována, a to i přesto, že každý SŘBD používá pro uložení dat jiný formát a jiné programové rozhraní.“ Podrobné informace o ODBC najdete např. na uvedené stránce reboot.cz, o JDBC třeba na interval.cz nebo na Wikipedii a o jejich použití v OpenOffice.org (OOo) na OpenOffice.org wiki.

O nativním rozhraní mezi databází MySQL a OpenOffice.org pomocí rozšíření MySQL Connector se pěkně píše např. na další OpenOffice.org wiki stránce nebo v pdf souboru. Samotné rozšíření stáhnete na stránkách extensions OpenOffice.org (je zde výběr verze pro platformy Windows, Linux, MacOS i Solaris). V Ubuntu se rozšíření nabízí opět přímo v Centru softwaru pro Ubuntu pod názvem MySQL Connector.

Mně připadá nejjednodušší právě použití rozšíření MySQL Connector (odpadá instalace driverů), proto se budu zabývat touto možností. Na stránce, uvedené v předchozím odstavci, spustíte stažení tlačítkem Get it! Instalace rozšíření je jednoduchá, popř. využijte návod na openoffice.cz. Tím jste připravení na připojení Base k databázi MySQL, kterou vygeneroval MySQL Workbench.

Připojení se k databázi MySQL pomocí Base. Je k tomu potřeba založit v Base novou databázi, ale tentokrát v počátečním průvodci vyberte volbu Připojit se k databázi a v rozevíracím seznamu databází zvolte MySQL.

 

Zdrojem pro novou databázi bude databáze MySQL

Pokud pro umožnění přístupu ke zdrojové databázi také volíte rozšíření MySQL Connector, vyberte ve druhém kroku průvodce (Nastavit spojení s MySQL) volbu Připojit přímo (Protože mi následující postup připadá trochu obtížnější, raději přikládám více obrázků).

 

Vybíráme nativní rozhraní mezi Base a MySQL

Ve třetím kroku (Nastavit MySQL server) zadejte jméno databáze MySQL, vytvořené programem Workbench a název serveru (u lokálního serveru 127.0.0.1).

 

Zadejte název databáze MySQL a serveru

Jestliže si nejste jisti tím, jaké názvy zadat, otevřete MySQL Workbench a vyberte spojení se serverem, které jste při generování databáze použili.

 

Oblast, kde vyberete spojení, ukazuje ve výseku okna programu myš

Potom vám Workbench sám ukáže (v levé dolní části okna), jaké databáze byly v tomto spojení na MySQL serveru vytvořeny i jak je server označen. Upozornění: na následujícím obrázku vidíte kromě údajů o spojení také databáze, které momentálně na serveru MySQL jsou (zde „KnihovnaVerzeB“, ale i „mydb“ a „phpmyadmin“). Pro naši práci by bylo myslím nejlepší další databáze před napojením na Base odstranit. Přerušte tedy popř. průvodce a před jeho znovuspuštěním tabulky odstraňte (pravá klávesa nad názvem databáze, příkaz Drop Schema...).

 

Názvy vám ukáže Workbench

Po zadání údajů přejdete do čtvrtého kroku průvodce (Nastavit autentizaci uživatele). Zadejte uživatelské jméno (i to vám ukázal Workbench vedle slova User, viz předchozí obrázek). Pravděpodobně jste také ponechali pro tyto pokusy původní jméno „root“. Jestliže jste přístup k databázi MySQL zajistili heslem (což je v principu velmi rozumné), vyberte zatržítko Vyžadováno heslo. Hned otestujte funkčnost spojení s databází kliknutím na odpovídající tlačítko (podobně jsme testovali spojení během činností v kapitole Vytvoření spojení programu Workbench s databázovým serverem, uvedené v první části tohoto dílu).

 

Zadejte jméno uživatele a otestujte spojení

Otevře se menší dialogové okno, požadující heslo:

Zadejte heslo, pokud bylo nastaveno

Konečně se dostáváme k poslednímu kroku (Uložit a pokračovat), který znáte už ze zakládání databáze v druhém dílu seriálu, kde je i obrázek tohoto okna. Je rozumné ponechat vybrané volby „Ano, zaregistrovat databázi“ a „Otevřít databázi pro úpravy“. Na závěr zadáte jméno nové databáze v Base, která je už napojena na databázi MySQL, např. Knihovna-z-SQL-verzeB.odb (příponu databáze v Base .odb doplní program sám).

Stálo všechno toto úsilí zato? Snad ano: v okně Base, které se následně otevře, vidíte pěkně všech pět tabulek databáze Knihovna verze B. Poznámka: Protože Base nyní spolupracuje se serverem MySQL, zobrazuje navíc i tabulky pomocné databáze s názvem „mysql“, vytvořené na serveru automaticky. Tato databáze obsahuje tabulky uživatelských účtů s oprávněními uživatelů k dalším databázím na serveru. Její základní tabulkou je tabulka „user“ s oprávněními. V následujícím obrázku vidíte v části okna Tabulky databázi „KnihovnaVerzeB“ a část databáze „mysql“. Její tabulka „user“ je až na konci, proto ji v okně nevidíte. Byla však před sejmutím okna vybrána, takže její část obsahuje náhled, umístěný v okně Base vpravo.

Base se napojil na databáze na serveru MySQL

A kromě toho, že nová databáze obsahuje všechny tabulky Knihovny verze „B“ dle diagramu, byly vytvořeny i vazby mezi nimi, jak ukazuje okno vazeb, které zobrazíte už známou volbou Nástroje | Vztahy...

Vazby v databázi, převzaté v Base z programu Workbench

Porovnejte tento obrázek vazeb v Base s obrázkem ER diagramu ze závěru předchozího dílu: zcela si odpovídají; oba programy (Workbench při převodu konceptuálního modelu na databázi a Base při připojení se k této databázi) odvedly svou práci dobře.

Nyní lze pracovat s daty Knihovny jak na serveru MySQL (třeba v programu Workbench), tak v nově vytvořené databázi v Base – obě databáze jsou propojeny, změny se projeví v obou. Nezapomeňte, že když budete tuto databázi příště v Base otvírat, musí být spuštěn váš MySQL server.

Závěr

V tomto dílu jsme vytvořili databázi Knihovna s podporou programu typu CASE. Bylo to obtížnější než práce v samotném Base, ale máme zároveň databázi na serveru, se kterou by časem mohli pracovat i další uživatelé. Navíc zde Base funguje jako rozhraní (front-end), které může zpříjemnit a usnadnit další práci s databází.

Otázky na závěr – Shrnutí sedmého dílu (vygenerování databáze)

1. Zkuste stručně popsat postup, jakým byla založena databáze Knihovna v Base v tomto dílu. Nezapomeňte také na jisté přípravné fáze, které byly potřebné.
2. Vysvětlete stručně účel těchto příkazů jazyka SQL: CREATE TABLE, INSERT, UPDATE, DELETE a SELECT. Které z nich patří podmnožiny jazyka DML (Data Manipulation Language) a které do DDL (Data Definition Language)?
3. Poskytují webhostingy běžně přímý přístup k externí databázi, uložené na serveru hostingu? Vysvětlete. Jaké jiné přístupy k databázi na vzdáleném serveru jsou povoleny?
4. Jaký smysl má internetová adresa 127.0.0.1?
5. Zkuste najít, k jaké komunikaci je určen port číslo 3306 a jak máme rozumět pojmu port v této souvislosti.

Co vás čeká v dalším dílu?

Protože stavba databáze je v podstatě hotová, bylo by dobré ji už začít plnit daty. Uvidíte ale, že zadávat hodnoty přímo do tabulek není příliš výhodné. Bude tedy následovat první seznámení s formuláři, které se pro zadávání dat používají.