Archiv pro rubriku: Databáze

První krůčky s CzechIdM Kapitola 2: Připojení databázové tabulky

czechidm

V tomto díle našeho krátkého seriálu o práci v CzechIdM se podíváme na to, jak definovat konektor pro koncový systém, jak připojit námi vytvořenou tabulku ke koncovému systému a nakonec se podíváme, jak vytvořit roli pro náš koncový systém.

Vytvoření databázové tabulky

V minulém dílu jsme si nainstalovali MySQL, zatím ale stále nemáme žádné přívětivé prostředí, kde můžeme s databázemi pracovat. Proto si nainstalujeme MySQL workbench. Instalace tohoto prostředí není nijak složitá, stačí v terminálu zadat

$sudo apt-get install mysql-workbench

Po skončení instalace zadáme opět v terminálu mysql-workbench

  • Ze záložky Database vybereme možnost Manage Connection. Zde pojmenujeme naše připojení a klikneme na Test connection. Pokud nastali problémy, zkontrolujte si prosím parametry připojení:

Záložka Connection

Connection Method: Standard (TCP/IP)

Hostname: 127.0.0.1

Port: 3306

Username: root

  • Ze záložky Database vybereme Connect to Database. U možnosti Stored Connection vyberem námi vytvořené připojení a klikneme na OK.
  • Zobrazí se nám prostředí pro práci s databázemi. Uprostřed je bílé okno pro vykonávání SQL příkazů. Do něj zadáme:
CREATE SCHEMA `test` ;
CREATE TABLE test.datas (
`ID` INT,
`firstName` TEXT,
`lastName` TEXT,
`login` TEXT,
`address` TEXT,
`city` TEXT,
`dateOfBirth` TEXT,
`company` TEXT,
`possition` TEXT,
`salary` INT,
`timeStamp` TEXT);

a klikneme na žlutý blesk v horní části obrazovky. Tím se vykoná SQL příkaz, který vytvoří nové schéma test a v něm tabulku datas se sloupci jméno, příjmení, email… .

  • Dalším krokem je nastavení primárního klíče. V levé části obrazovky máme světle modré pole. Pokud máme mysql-workbench spuštěný poprvé, je toto pole prázdné. Klikneme na něj tedy pravým tlačítkem a vybereme Refresh All. Zobrazí se námi vytvořené schéma a v něm naše zatím prázdná tabulka.
  • Klikneme na ní opět pravým tlačítkem a zvolíme Alter table. Otevře se nám dialog pro nastavení tabulky test. Zaškrtneme parametry PK a NN u sloupce ID. Parametr PK znamená, že daný sloupec bude primárním klíčem. Pomocí primárního klíče se jednoznačně identifikuje každý záznam v tabulce. To znamená, že žádné ID v naší tabulce nemůže být shodné s dalším, nebo že žádné ID nesmí být prázdné. Toho docílíme právě zaškrtnutím NN parametru (nut null). Dále zaškrtneme možnost AI (Auto Increment), kterým nastavíme, že každé další ID bude o jedna větší než ID předchozí.
  • Nyní nasypeme nějaká testovací data do naší nové tabulky. Do záložky Query 1 v horní části obrazovky  zkopírujeme následující kód:
INSERT INTO test.data (`firstName`, `lastName`, `login`, `address`, `city`, `dateOfBirth`, `company`, `possition`, `salary`, `timeStamp`) VALUES ('Drahoslav', 'Miler', 'Drahoslav@Miler.cz', 'Stráň 122', 'Lobeč', '10.10.1957', 'b', 'Lorem ', '20077', '12.4.2014');

Opět klikneme na žlutý blesk vlevo nahoře do naší databáze se přidá první záznam. Takto můžeme vkládat kolik záznamů chceme. Vždy do hodnoty VALUES vložíme data přesně tak, jak je to ukázáno v kódu nahoře. Pokud si chceme zkontrolovat výsledek, klikneme pravým tlačítkem myši na tabulku datas a zvolíme Select rows – 1000 limit.

Pokud si o databázích chcete přečíst něco podrobnějšího, podívejte se například na tento seriál: http://www.linuxsoft.cz/article.php?id_article=731

Definování typu systému pro konektor

Prvním krokem je vydefinování typu systému pro Database Table konektor v CzechIdM. Tento krok stačí provést pouze jednou, a to když připojujete nějaký systém pomocí tohoto konektoru poprvé. Přejdeme tedy na záložku

Systémy → Typy systémů → Nový typ.

Zaškrtneme možnost, že se má použít lokální connector server a klikneme na Pokračovat. Posledním krokem je pojmenování nového typu systému a vybrání správného balíku. My náš typ pojmenujeme Database Table Konektor a jako balík vybereme org.identityconnectors.databasetable-1.1.xx.jar.

Nyní stačí už jenom náš nový typ systému uložit a máme hotovo.

Připojení systému s databázovou tabulkou

Snímek obrazovky pořízený 2014-07-27 23:06:42

Nyní již propojíme námi vytvořenou tabulku s CzechIdM. Pro tento krok musíme vytvořit nový systém, který bude sloužit k připojení k tabulce. Přejděme tedy na záložku

Systémy → Systémy → Nový systém
  • Vybereme konektor, který jsme vytvořili v předchozím kroku, tedy Database Table Konektor a klikneme na Next.
  • V nové tabulce vyplníme Název systému na SystemTest1, kolonku Název pro uživatele necháme v tuto chvíli volnou.
  • Authority level nastavíme na 0. Tímto číslem určujeme jak moc bude náš systém autoritativní pro data v CzechIdM. Čím je toto číslo větší, tím více je systém autoritativní. Prakticky to bývá tak, že level 1 je zdrojový systém (systém je zdroj dat pro identity v CzechIdM), level 0 je koncový systém (CzechIdM je zdroj dat pro koncový systém).
  • Pouze ke čtení nebudeme zaškrtávat, protože budeme v budoucnu upravovat tabulku pomocí CzechIdM.
  • Další položkou, kterou vyplníme, je port. Nastavíme ho na 3306, tam nám totiž běží MySQL databáze.
  • Dále jako Key Column zvolíme login. Key Column nám určuje, který atribut bude použit jako identifikátor jak při zobrazování, tak i například při synchronizaci systému.
  • Initial JNDI Properties v tuto chvíli necháme volné.
  • Do položky Host zadáme místo, kde nám běží server, tedy localhost.
  • All native nebudeme zaškrtávat.
  • Name quoting necháme volné.
  • Do políčka Table zadáme jméno námi vytvořené tabulky, tedy datas.
  • Pokud jste nechali při instalaci MySQL serveru políčko pro heslo roota prázdné, nevyplňujte ani políčko User Password. V opačném případě zadejte heslo, jež jste si nastavili pro MySQL roota.
  • Do kolonky Database se zadává jméno databáze, ke které se připojujeme. V našem případě se databáze jmenuje test.
  • Change Log Column (Sync) nastavíme na timeStamp. Tím jsme nastavili, který atribut bude použit při synchronizaci systémů jako časová značka změněných řádků.
  • Password Column necháme také volné, protože v naší databázové tabulce nemáme žádný sloupec obsahující hesla.
  • JDBC Connection URL nastavte na jdbc:mysql://%h:%p/%d. Tím jsme určili URL pro spojení CzechIdM a databáze.
  • Native Timestamps a Enable writing empty string nebudeme zaškrtávat, ale položku Rethrow all SQLExceptions označíme.
  • Validate Connection Query necháme volné.
  • Do pole User napíšeme root, což je uživatelské jméno, přes které se připojujeme k MySQL databázi.
  • Datasource Path necháme volné.
  • JDBC Driver nastavíme na com.mysql.jdbc.Driver. Tím jsme určili třídu konektoru pro připojení do databáze.

Nyní klikneme na tlačítko Test. Tím se otestuje zda-li je náš systém pro připojení správně nastaven. Zobrazí-li se hláška o úspěšném navázání spojení, klikněte na Pokračovat.

Snímek obrazovky pořízený 2014-07-27 23:08:06

Nyní se dostaneme do okna, kde můžeme editovat atributy. Klikneme tedy na Editovat atributy a zobrazí se nám jména sloupců z námi vytvořené databázové tabulky.  Do sloupce IdmName vyplníme jednotlivé atributy tak, aby CzechIdM poznalo, o který atribut se jedná, Tedy pokud pojmenujeme atribut firstName, pro CzechIdM opět firstName, CzechIdM bude vědět, že se jedná o křestní jméno. Poté klikneme na tlačítko Uložit.

Pokud si chceme zobrazit výsledky naší práce, klikneme na na odkaz Zobrazit všechny účty na seznamu našich systémů. Měli bychom vidět pouze prázdný seznam a až po kliknutí na tlačítko Hledat se nám zobrazí všechny účty z našeho koncového systému. Můžeme rozklikávat jednotlivé účty a prohlížet si jejich detaily. Ve chvíli, kdy skončíme, klikneme na tlačítko Zavřít.

Vytvoření role pro připojený systém

Poslední krok, který si v tomto článku ukážeme, je vytvoření role v CzechIdM, která vytváří účty na připojeném systému, jakmile je přiřazena uživateli. Přejdeme na záložku

Role → Role → Nová role

a zde na záložce Základní informace vyplníme název nové role (např. Test system account). Poté přejdeme na záložku Schémata zdrojů a klikneme na odkaz Přidat u systému Test end system v pravé tabulce (se seznamem všech připojených systémů). Poté klikneme na odkaz Editovat v levé tabulce (se seznamem systémů, ke kterým opravňuje tato role).

Na následující stránce můžeme nastavit, jak budou atributy účtu plněny touto rolí v závislosti na svých potřebách. Zatím to ponecháme beze změny a klikneme na Nastavit.

Naši novou roli už jen Uložíme. Tuto roli můžeme přidělit libovolné identitě a tím pro ni vytvořit nový účet na napojeném koncovém systému.

Závěr

V tomto článku jsme si ukázali, jak připojit databázovou tabulku pomocí Database Table konektoru, a jak si zobrazit data z tabulky. Dále jsme si ukázali, jak vytvořit roli, jež nám vytvoří účet pro identitu v databázové tabulce. V příštím díle se podíváme na to, jak sesynchronizovat účty a jak nastavit rekonciliaci.

Pokud jste v článku nalezli nějakou chybu, nebo máte nějaký dotaz, prosím napište mi na michal.stejskal@bcvsolutions.eu .

 

Seznámení s Hibernate ORM

V tomto článku si popíšeme základy technologie Hibernate ORM pro komunikaci Javy s relačními databázemi. Osvěžíme si znalosti o tom, co je to ORM a jak se popisují vztahy mezi jednotlivými entitami v datovém modelu. Letmo se zmíníme, jaký vztah je mezi Java Persistence API (JPA) a Hibernate. Na závěr se ve stručnosti seznámíte s použitím Hibernate v Identity Manageru CzechIdM.

Hibernate ORM

Hibernate ORM je framework v jazyce Java, umožňující objektově-relační mapování (ORM).

ORM je programovací technika, která umožňuje ukládat data objektově orientovaných jazyků do relační databáze. Vybraným Java třídám pak přísluší databázové tabulky, přičemž javovské datové typy se konvertují na SQL datové typy. ORM zpracovává také vztahy mezi objekty, které do relačních databází ukládáme. V databázi jsou pak vztahy objektů reprezentovány spojováním tabulek a cizími klíči.

Hibernate implementuje Java Persistence API. Můžeme ho tedy používat buď specificky – tak, že voláme přímo jeho metody -, nebo obecně přes rozhraní definované JPA.

Hibernate podporuje mnoho databázových dialektů. Umožňuje pracovat například s MySQL (přičemž můžeme odlišit, zda používá engine InnoDB či MyISSAM), PostgreSQL, Oracle, Microsoft SQL Server aj.

Hibernate dále vyvíjí speciální jazyk pro psaní dotazů do databáze přímo z kódu, tzv. Hibernate Query Language (HQL). HQL je inspirován syntaxí SQL, takže je dobře použitelný pro každého, kdo zná základy SQL dotazů.

Entita v Javě

Entita je samostatný typ objektu, který má své specifické vlastnosti a jehož instance jsou jednoznačně identifikovatelné.

Pro Hibernate je entita jedna Java třída, jejíž instance se ukládají jako záznamy do jedné databázové tabulky. Každá instance entity má svůj identifikátor, který se obvykle použije jako primární klíč do tabulky.

Entitní třída musí mít konstruktor bez parametrů, protože pomocí něj Hibernate vytváří objekty při načítání z databáze. Členské proměnné třídy jsou zpravidla private, používají se pro ně standardně konstruované gettery a settery. Načítání hodnot členských proměnných z databáze se může provádět pomocí lazy-loading, proto se doporučuje přistupovat k proměnným pomocí getterů (které vynutí načtení celé hodnoty).

Persistence entit

Ukládání instancí entit do databáze se provádí dvojím způsobem, v závislosti na tom, zda používáme způsob specifický pro Hibernate, nebo jeho implementaci JPA.

V prvním případě si pomocí org.hibernate.SessionFactory (objekt, který se vyskytuje v jedné instanci pro celou aplikaci) vytvoříme objekt typu org.hibernate.Session, v rámci kterého provedeme „jednotku práce“ – jednu operaci v databázi:

org.hibernate.Session session = sessionFactory.openSession();
session.beginTransaction();
session.save(nejakaEntita);
session.getTransaction().commit();
session.close();

V druhém případě použijeme javax.persistence.EntityManagerFactory, kterou dodává JPA v závislosti na konfiguraci naší aplikace, a pomocí ní vytvoříme objekt typu javax.persistence.EntityManager. Ten provádí jednotku práce podobně jako v předchozím způsobu:

javax.persistence.EntityManager entityManager = entityManagerFactory.createEntityManager();
entityManager.getTransaction().begin();
entityManager.persist(nejakaEntita);
entityManager.getTransaction().commit();
entityManager.close();

Konfigurace

Základní konfigurační soubor pro Hibernate se nazývá hibernate.cfg.xml.

V tomto souboru definujeme na prvním místě dialekt databáze, aby Hibernate používal správné datové typy a správnou syntaxi SQL:

<hibernate-configuration>
  <session-factory>
    <property name=“dialect“>org.hibernate.dialect.MySQL5InnoDBDialect</property>

V konfiguračním souboru nesmí chybět identifikace spojení do databáze. Pokud zadáme všechny potřebné údaje pro navázání JDBC spojení, Hibernate si vytvoří JDBC connection pool, který mu bude poskytovat spojení tak, jak bude třeba. Místo toho můžeme zadat existující javax.sql.DataSource registrovaný v JNDI, tedy spojení, které je spravováno naším aplikačním serverem.

<property name=“connection.datasource“>java:/CzechIdMDatasource</property>

(Komunita Hibernate doporučuje používat pro produkční prostředí spíše tento způsob. Upozorňují, že správu vlastního JDBC connection pool ještě Hibernate nezvládá úplně bez chyb).

Další důležitou vlastností je hbm2ddl.auto. Tato vlastnost určuje, co se děje s databázovým schématem ve chvíli vzniku a zániku SessionFactory, tj. při startu a skončení aplikace. Možné hodnoty jsou „update“ (při startu aktualizuje schéma na základě aktuálního nastavení), „validate“ (při startu ověří schéma, ale změny nedělá), „create“ (při startu vytvoří nové schéma, čímž přepíše původní data), „create-drop“ (při skončení smaže schéma, při startu ho znovu vytvoří).

V konfiguračním souboru jsou dále uvedeny odkazy na mapovací soubory.

Pokud používáme obecné rozhraní JPA, konfigurační soubor je META-INF/persistence.xml. Zde definujeme poskytovatele persistence, spojení do databáze a další konfigurační vlastnosti. Ty vlastnosti, které jsou určeny pro Hibernate, uvedeme s předponou „hibernate.“.

<persistence-unit name=“CzechIdM“>
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
  <jta-data-source>java:/CzechIdMDatasource</jta-data-source>
  <properties>
    <property name=“hibernate.dialect“ value=“org.hibernate.dialect.MySQL5InnoDBDialect“/>
    ...

Mapovací soubory (Hibernate)

V mapovacích souborech definujeme mapování entit na databázové tabulky. Nejlépe to ukážeme na příkladu:

<hibernate-mapping>
  <class name="org.jbpm.graph.def.Node" table="JBPM_NODE">
    <id name="id" column="ID_"><generator class="native" /></id>
    <property name="name" column="NAME_"/>
    <property name="description" column="DESCRIPTION_" type="longstring" length="4000"/>
    ...

Atribut „name“ u elementu „class“ obsahuje FQN (Fully qualified name) třídy, která je entitou. Atribut „table“ obsahuje název tabulky, kam se její instance budou ukládat.
Následuje výčet sloupců.

Element „id“ definuje sloupec, který unikátně identifikuje řádek v tabulce. Nemusí to být primární klíč, ale je to obvyklé. Uvnitř elementu „id“ může být ještě definována strategie generování identifikátorů.

Element <property name="description" column="DESCRIPTION_" type="longstring" length="4000"/> definuje proměnnou „description“ mapovanou na sloupec „DESCRIPTION_“, přičemž v atributu „type“ je uveden konvertor (Hibernate mapping type), který má Hibernate použít pro překlad z Java datového typu na databázový typ. Pokud není konvertor uveden, používá se Java reflection a defaultní mapování z javovských typů na databázové typy. Není-li uveden atribut „column“, použije se pro název sloupce přímo název proměnné.

Anotace (JPA)

Místo mapovacích souborů se dají použít anotace přímo u entitních tříd a jejich členských proměnných. Názvy anotací odpovídají názvům vlastností v mapovacích souborech. Entita pak vypadá například takto:

@Entity
@Table(name = "identities", uniqueConstraints = {
  @UniqueConstraint(columnNames = { "name" }) })
public class Identity extends ViewMainEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "id")
  private int id;

  @SignedField
  @Index(name = "name_index")
  @Column(name = "name", length = NAME_LENGTH)
  private String name;
  ...

Výhodné je, že při použití anotací vidíme ze zdrojového kódu na první pohled, jakým způsobem se entita ukládá.

Anotace jsou součástí standardu JPA. Pokud bychom tedy chtěli změnit způsob persistence dat do databáze, nemusíme při používání anotací měnit nic v kódu.

Vztahy mezi entitami

Silným nástrojem Hiberate ORM je popis vztahů mezi entitami. Každé dvě entity spolu mohou být v nějakém vztahu (relaci), přičemž jedna strana je vlastníkem relace a druhou nazýváme „inverzní strana“.

Vlastník relace je zodpovědný za udržování konzistence v datech na obou stranách relace. Při definici relace určíme pomocí CascadeType, jaké typy operací se mají v rámci relace propagovat. Například CascadeType.REMOVE způsobuje, že smaže-li se jedna strana relace, smaže se i druhá strana.

V databázi se vztah entit realizuje cizími klíči mezi jednotlivými tabulkami, přičemž cizí klíče jsou vydefinované v tabulce vlastníka relace.

Základní typy relací jsou Many-to-one resp. One-to-many, One-to-one a Many-to-many. Každý vztah si nyní ukážeme na příkladu z datového modelu CzechIdM spolu s ukázkami kódu.

Many-to-one & One-to-many

Situace: Každá identita patří právě do jedné organizace, přičemž do každé organizace může patřit více identit.

  • Entita Identity – vlastník relace
    @ManyToOne
    @JoinColumn(name = "home_organisation")
    private Organisation homeOrganisation;
  • Entita Organisation
    @OneToMany(mappedBy = "homeOrganisation", cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH })
    private List<Identity> identities;
    

Vazba: V databázové tabulce „identities“ je vytvořen speciální sloupec „home_organisation“, který obsahuje identifikátor (primární klíč) domovské organizace identity.

Pokud bychom na straně organizace nepotřebovali znát seznam identit, které do ní patří, mohli bychom úplně vynechat proměnnou „identities“ a jakýkoliv odkaz na to, že mezi entitami Identity a Organisation je nějaký vztah. Relaci udržuje entita Identity. CascadeType bychom pak samozřejmě museli přidat do kódu k identitě.

One-to-one

Situace: Každá identita má právě jednu konfiguraci.

  • Entita Identity – vlastník relace
    @OneToOne(optional = false, cascade = CascadeType.ALL)
    @JoinColumn(name = "configuration")
    private IdentityConfigurations identityConfigurations;
    
  • Entita IdentityConfigurations
    @OneToOne(mappedBy = "identityConfigurations")
    private Identity identity;
    

Vazba: V databázové tabulce „identities“ je vytvořen speciální sloupec „configuration“, který obsahuje primární klíč konfigurace identity z tabulky „identity_configurations“.

Opět platí to, že kdyby entita IdentityConfigurations nepotřebovala znát referenci na identitu, k níž patří, můžeme celý atribut vynechat.

Při relaci One-to-one je možné definovat mapování speciálně pomocí primárního klíče. K tomu stačí použít anotaci @PrimaryKeyJoinColumn namísto @JoinColumn(name = ...). Pokud instance obou entit mají stejný identifikátor, Hibernate dokáže entity spojovat na základě tohoto identifikátoru. Výhodou je, že v databázové tabulce vlastníka relace není třeba vytvářet navíc žádný sloupec. Nevýhodou naopak to, že identifikátor instancí pak nelze snadno měnit, protože se vyskytuje ve více tabulkách.

Many-to-many

Situace: Jedna role může opravňovat uživatele k účtům na více koncových systémech (schématech). K jednomu schématu může opravňovat více rolí.

  • Entita Role – vlastník relace
    @ManyToMany
    @JoinTable(name = "roles_schemas", 
    		joinColumns = @JoinColumn(name = "role"),
    		inverseJoinColumns = @JoinColumn(name = "resource_schema"))
    private List<Schema> schemas;
  • Entita Schema
    @ManyToMany(mappedBy = "schemas")
    private List<Role> roles;

Vazba: Vytvoří se nová databázová tabulka „roles_schemas“, která udržuje informace o mapování rolí a schémat. Sloupec „role“ zde obsahuje identifikátor role a sloupec „resource_schema“ obsahuje identifikátor schématu (obojí jsou primární klíče).

Hibernate a CzechIdM

Identity Manager CzechIdM používá Hibernate ORM pro persistenci veškerých dat do databáze.

Standardní entity, které jsou součástí zdrojového kódu CzechIdM (identity, organizace, role, systémy,…), používají JPA a anotace. Konfigurační soubor pro persistenci entit je umístěn v CzechIdM-ejb.jar/META-INF/persistence.xml. Pokud bychom se v budoucnu rozhodli přejít na jiný ORM framework, ve zdrojovém kódu by to znamenalo žádné, nebo jen minimální úpravy.

CzechIdM používá jako workflow engine technologii jBPM. Tato technologie ukládá svá data do relační databáze také pomocí Hibernate ORM, přičemž používá přímo specifické metody Hibernate. Konfigurační soubor pro jBPM je umístěn v CzechIdM-ejb.jar/jbpm.hibernate.cfg/hibernate.cfg.mysql.xml a jednotlivé mapovací soubory pro entity jBPM jsou umístěny v knihovně jbpm-jpdl.jar. Výhodou tohoto přístupu je, že jsme mohli provést drobné změny v konfiguraci ukládání entit jBPM do databáze, aniž bychom museli znovu sestavovat celou knihovnu jBPM ze zdrojových kódů.

Spojení CzechIdM s databází udržuje aplikační server JBoss. V obou konfiguračních souborech je tedy uveden odkaz na tentýž datasource (java:/CzechIdMDatasource). Můžeme tedy konfigurovat či optimalizovat spojení s databází pro oba persistentní moduly zároveň.

Závěr

Tento článek obsahuje základy použití technologie Hibernate ORM zejména z pohledu Java vývojáře. Zdaleka jsme nepokryli všechny otázky, které by zvídavého čtenáře napadly, ale nic vám nebrání zaslat je na můj e-mail alena.peterova@bcvsolutions.eu!

Databázové rozhraní personálního systému Vema

Personální systém Vema je v Česku velmi oblíbený a používá ho celá řada státních institucí i komerčních společností. Často se s ním proto setkáváme u zákazníků v rámci našich integračních projektů; Vema je pro náš Identity Manager CzechIdM typický zdroj dat o zaměstnancích. Systém Vema ovšem běžně nenabízí přístup k datům v reálném čase, člověk zpravidla dostane jen pravidelné noční exporty do CSV souborů. Naše zákazníky proto obvykle překvapí, že cesta k datům v reálném čase existuje a je daleko elegantnější než CSV soubory. Společnost Vema totiž vyvinula modul, který umí exportovat personální data v reálném čase do Oracle relační databáze. A právě o něm bude můj dnešní článek.

vema-logo

Pokračování textu

Oracle: paralelní zpracování

Dnešní databáze běžně obsahují miliony záznamů. Kdyby je měl databázový stroj při každé změně či každém dotazu procházet postupně jeden po druhém, trvalo by to několik hodin. Řešením, které vytěží maximum z dostupného hardware a zvládne úkol v mnohem kratším čase, může být paralelizace. Databáze Oracle verze 11 tuto možnost poskytuje. V článku se podíváme, jak si z pozice administrátora paralelní zpracování na některých databázových objektech vynutit, anebo naopak zakázat, a ukážeme si některé zajímavé parametry, které s paralelizací souvisejí.

 

Pokračování textu

Vizualizace dat z userstats patche do MySQL

Přibližně před rokem jsem sem napsal zápisek o nástroji kterým sledujeme stav MySQL databáze – „Bič na uživatele sdílené MySQL“. Dnes jsem prováděl úklid na disku pracovního notebooku a narazil jsem na archiv s kódy toho udělátka na sbírání a tvorbu statistik nad MySQL patchovanou userstats patchem. Protože by se to mohlo hodit i někomu jinému, tak je dávám k dispozici.

Pokračování textu

Bič na uživatele sdílené MySQL

MySQL databázi používáme jako hlavní (a posledních pár měsíců jedinou*). S konfigurací MySQL pro naše prostředí jsme se za těch několik let co běží už docela poprali, takže nemáme výrazné problémy s výkonem, dostupností nebo zálohama. Co nám dlouho scházelo jsou informace o čerpání prostředků jednotlivými uživateli.Před nějakou dobou jsme v rámci upgrade MySQL přešli na upravenou verzi s patchem userstats.

Aktualizace 10/2011: skripty je možné stáhnout

Pokračování textu

Timestamp v MSSQL

MSSQL sice obsahuje datový typ „timestamp“, který je aktualizován při každé změně záznamu. Problém je ale v tom, že nemá žádný vztah k datumu a času. Jde o číslo v binárním formátu.
Pokud tedy chceme zjistit čas, kdy došlo ke změně záznamu, musíme použít trigger, který bude nastavovat hodnotu do pole typu „datetime“.

Spustíme MS SQL Server Manager Studio.
Předpokládáme, že máme v databázi tabulku „users“ a v ni sloupec „mtime“ typu „datetime“.
Pole „mtime“ bude obsahovat timestamp.
Jako defaultni hodnotu nastavime „getDate()“. Tím zajistíme, že nově vložený záznam bude mít v poli „mtime“ aktuální čas.
Teď už zbývá pouze zajistit, aby se aktuální čas nastavil i v případě, že budeme záznam modifikovat.
K tomu nám poslouží trigger na událost update.
Ten vytvoříte tak, že v kontextovém menu vybereme možnost „New Trigger…“. Viz obrázek.

Jak vytvořit nový trigger

Otevře se okno, kam napíšeme samotný kód.
Ten by mohl vypadat nějak takto:

CREATE TRIGGER tr_UserTimestamp
   ON  users
   FOR UPDATE
AS 
BEGIN
    DECLARE changedRecords Cursor
    FOR
    SELECT d.id FROM Deleted d, Inserted i
    WHERE d.id = i.idz
    AND (d.firstName <> i.firstName OR d.lastName <> i.lastName)
    
    DECLARE @recordId CHAR(12)
    
    OPEN changedRecords
    FETCH NEXT FROM changedRecords INTO @recordId
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        UPDATE users
        SET mtime=getDate()
        WHERE id=@recordId
        
        FETCH NEXT FROM changedRecords INTO @recordId
    END
    
    CLOSE changedRecords
    DEALLOCATE changedRecords
END

Tím se říká, aby se vytvořil trigger s názvem „tr_UserTimestamp“ spoustěný při updatu tabulky „users“. V logických tabulkách „Inserted“ a „Deleted“ jsou záznamy, na kterých byl proveden update. V tabulce „Deleted“ jsou staré hodnoty, kdežto v „Inserted“ jsou hodnoty po provedeni update. Zrada je ale v tom, že pokud provedeme update na záznam, během něhož nastavite atributu hodnotu stejnou, jako měl před tím, tak se i tento záznam v tabulkách objeví. Proto jsou v uvedeném kódu porovnávány hodnoty odpovídajicích atributů a záznam je vybrán, pouze pokud se liší. Jen taková malá odbočka – existuje funkce Update() s názvem atributu jako parametr, která vrátí true, pokud byl atribut updetován. Což znamená, že vrátí true vždy, když se vyskytne atribut v UPDATE příkazu bez ohledu na to, jestli se hodnota změní, nebo ne.
Vybrané záznamy jsou po té procházeny pomocí kurzoru, přičemž je nastavován jejich atribut mtime na aktuální čas.

Až máme kód napsány, můžeme kliknout na „Execute“ a tím vytvoříme samotný trigger.