Aplikační a programové vybavení

1. Aplikační a programové vybavení Databáze – speciality šéfkuchařů

2. Databázové systémy • Nejpoužívanější relační databázové systémy: • Adaptive Server Enterprise (Sybase) • DB2 (IBM) • Firebird • MySQL • Oracle Database • PostgreSQL • SQL Server (Microsoft) • SQLite • Každý systém implementuje jazyk SQL jinak a žádný úplně. • Mezi jednotlivými DBS bývají velké rozdíly.

3. Rozhraní • Pro práci s databázovým systémem se vždy používá nějaké rozhraní: • Firemní • pouze pro jeden konkrétní DB systém • zpravidla více funkcí • optimální výkon • Obecná • obvykle větší podpora standardů • použitelné z různých aplikací a pro různé DBS • ODBC, ADODB, JDBC, … • Každé rozhraní poskytuje funkce pro připojení k databázi, vykonání dotazu, vypsání výsledku dotazu a zjištění chyb.

4. Datové typy - řetězce • character_varying(mohutnost) / varchar(mohutnost) / nvarchar(mohutnost) • standardní řetězec, omezený délkou • mohutnost se udává v byte [B] • char(mohutnost) / character(mohutnost) / nchar(mohutnost) • řetězec omezený délkou • doplňuje se mezerami • operátor LIKE porovnává řetězce bez mezer • text / ntext • „neomezený“ řetězec

5. Datové typy – celá čísla • bigint(šířka) – 8B – -263..263 • 263 = 9 223 372 036 854 775 808 • integer(šířka) – 4B – -231..232 • mediumint(šířka) – 3B – -223..223 • smallint(šířka) – 2B – -215..215 • tinyint(šířka) – 1B – -27..27 • celé číslo • signed(se znaménkem) – záporné rozsahy • unsigned (bez znaménka) – pouze kladná čísla • pozor na převod mezi signed a unsigned

6. Datové typy – desetinná čísla • real / float(velikost) • plovoucí desetinná čárka • velikost <= 24 – 7 číslic, 4 byty • velikost > 24 – 15 číslic, 8 bytů • number / numeric / decimal(přesnost, měřítko) • pevná desetinná čárka • přesnost – maximální počet platných číslic • měřítko – počet číslic za desetinnou čárkou • velikost – 5..17 bytů podle nastavení • money -263..263 = 922 337 203 685 477,5808

7. Datové typy – datum a čas • timestamp • Datum a čas se ukládá jako časové razítko. • Časové razítko je počet milisekund od 1.1.1970. • platné pouze do 19.1.2038 03:14:07 • Převod na běžný formát data je velice složitý, je nutné počítat se přestupnými dny a sekundami. • Timestampnení možné použít k uložení intervalu (rok, měsíc, den, …). • Podle SQL standardu by měl typ timestampobsahovat i časovou zónu. • datetime / date / time • větší rozsah a přesnost • uložení intervalu • komplexní datový typ

8. Datové typy – datum • je možné použít také formát data aplikace – např. • pg_query("UPDATE osoby SET datum_narozeni=". time()); • datový typ sloupce je integer • výhody: • jednoduché, pro jednu aplikaci bezproblémové • nevýhody: • není atomické, nelze vyhledávat podle části data (lze pouze řadit) • obtížně se zobrazuje interval – respektive závisí to na aplikaci • vracíme se k problému závislosti dat a aplikace • nespolehlivé pokud k databázi přistupuje více aplikací

9. Datové typy – binární data • bit / boolean/ binary – jedna hodnota • BLOB / image – binární data • BLOB – binary large object • Detaily práce s binárními daty závisí na použitém rozhraní. • Jednodušší případ: • Data je možno odesílat (po správném escapování) SQL dotazem a přijímat v rámci jeho výsledků. • Od určité velikosti nemusí fungovat správně. • Složitější případ: • Binární data (může zahrnovat i typ text) je nutné odesílat a přijímat speciálními funkcemi.

10. Definice struktury databáze • SQL příkaz CREATE • CREATE TABLE nazev_tabulky (sloupec datovy_typ [NOT NULL][DEFAULT hodnota] [PRIMARY KEY][,sloupecdatovy_typ…]) • CREATE TABLE kontakty(id serial PRIMARY KEY,osoba integer NOT NULL REFERENCES osoby,typ integer REFERENCEStypy_kontaktu,kontakt varchar(200) NOT NULL)

11. Definice struktury databáze • Prakticky není možné využívat SQL pro definici databáze mezi jednotlivými DBS. • Každý DB systém používá jiné datové typy • Generovaný primární klíč je implementován různě • Jednotlivé DBS používají rozšíření, která významně ovlivňují práci s DB (např. MySQL – MyISAM × InnoDB) • SQL pro definici struktury se používá nejčastěji pro import a export struktury v rámci jednoho DBS. • Pro převod dat mezi DB systémy je nutné používat nástroje pro migraci (migration toolkit)

12. Automatický generovaný klíč (ID) • nejčastěji používaný typ primárního klíče • abstraktní identifikátor záznamu • nezávislý na vnějších podmínkách • odpadají problémy se složeným klíčem • kromě něj by měl existovat ještě jiný klíč (definice relace) • Žádný databázový systém neodpovídá přesně standardu. • Mezi jednotlivými databázovými systémy jsou velké rozdíly.

13. ID - MySQL • sloupci se přiřadí speciální atribut auto_increment • při vložení dat INSERT dotazem se klíč automaticky aktualizuje i v případě, že je hodnota zadaná (např. při importování) • SELECT LAST_INSERT_ID(); • pouze jeden sloupec v tabulce může mít atribut nastaven musí být klíčem • CREATE TABLE tabulka (id int(5) unsigned NOT NULL auto_increment)

14. ID – MS SQL • sloupci se přiřadí speciální atribut is_identity • při vložení dat INSERT dotazem se klíč automaticky aktualizuje • SELECT IDENT_CURRENT('nazev_tabulky'); • v případě, že je hodnota sloupce zadaná, je nutné použít příkazy: • SET IDENTITY INSERT on – povolí vkládání dat • SET IDENTITY INSERT off – ukončí vkládání dat • vkládání dat do generovaného klíče může být povoleno pouze pro jednu tabulku • CREATE TABLE tabulka (id int(5) unsigned NOT NULL is_identity,

15. ID - PostgreSQL • Sloupci se přiřadí speciální datový typ SERIAL • Automaticky se vytvoří sekvence pro generování hodnot klíče. • Při vložení dat INSERT dotazem se klíč automaticky aktualizuje. • SELECT currval('nazev_sekvence') • V případě, že je hodnota sloupce explicitně zadaná se klíč neaktualizuje! • CREATE TABLE tabulka (id serial NOT NULL,

16. Autoincrement, Identiy, Currval ? • Pro zjištění hodnoty ID posledního vloženého záznamu se musí používat předdefinované funkce. • Nefunguje: SELECT MAX(id) FROM tabulka • Zjištění hodnoty musí být thread-safe • viz Obrázky: • http://akela.mendelu.cz/~xpopelka/cs/apv/ost/sekvence-currval.pdf • http://akela.mendelu.cz/~xpopelka/cs/apv/ost/sekvence-max.pdf • http://akela.mendelu.cz/~xpopelka/cs/apv/ost/sekvence-max-soubeh.pdf

17. Referenční integritní omezení • integritní omezení – NOT NULL, UNIQUE, PRIMARY KEY, CHECK • cizí klíč zpravidla představuje referenční integritní omezení • nelze změnit záznam v jedné tabulce bez aktualizace záznamů, které se na něj odkazují • Příklad: • sloupec osoba v tabulce kontakty se odkazuje na tabulku osoby • při smazání osoby je žádoucíupravit i všechny připojené kontakty • CASCADE – smazat • RESTRICT / NO ACTION - nepovolit smazání osoby • SET NULL – nastavit hodnoty ve sloupci na NULL • CREATE TABLE kontakty (osoba integer NOT NULL REFERENCES osoby ON DELETE CASCADE • osoby-kontakty vs. osoby- adresy

18. Referenční integritní omezení Adresy Při odstranění adresy se stane id_adresy=11 neplatné. ON DELETE SET NULLsmaže pouze referenci, pravděpodobně není žádoucí smazat odkazující se záznam. Osoby Při odstranění Karla z databáze se stane id_osoby=2neplatné. ON DELETE CASCADEsmaže i záznamy, které se na tuto hodnotu odkazují. Kontakty

19. Úprava struktury databáze • Struktura databáze je uložena ve speciální databázi INFORMATION_SCHEMA • Pro DBS, které INFORMATION_SCHEMA nepodporují je možné použít příkazů DESCRIBE, SHOW, LIST, ALTER, RENAME, DROP, MODIFY, ADD… • Příklad: • SHOW TABLES FROM databaze; • SELECT * FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE' ;

20. Omezení počtu řádků dotazu • Nestandardní (PgSQL, MySQL): • SELECT ... LIMIT n; • SELECT vyska FROM osoby ORDER BY vyska DESC LIMIT 10; • Standardní (SQL Server): • SELECT [TOP | BOTTOM n] FROM ... • SELECT TOP 10 vyska FROM osoby ORDER BY vyska DESC; • A další: • SELECT vyska FROM osoby ORDER BY vyska DESC WHERE ROWNUM <= 10;

21. Index • volba primárního klíče souvisí se způsobem ukládání dat v DBS • ISAM (Index Sequential Access Method) • využívá se indexových souborů(telefonní seznam) • u moderních DBS už se využívají jiné metody, pro snížení časové složitosti operací datový soubor index

22. Volně dostupné relační DBS • Firebird: • http://www.firebirdsql.org/ • MySQL • http://dev.mysql.com/ • Microsoft SQLServer (Express edice) • http://msdn2.microsoft.com/en-us/sql/default.aspx • Oracle (Express edice) • http://www.oracle.com/technology/products/database/oracle10g/index.html • PostgreSQL • http://www.postgresql.org/ • SQLite • http://www.sqlite.org/

23. http://kantorek.webzdarma.cz/