SQL Antipatterns

Při procházení knížek z nakladatelství The Pragmatic Bookshelf jsem narazil titul, který mě zaujal svým názvem.

Autor popisuje 24 vzorů na které naráží při používání klasických relačních databází. Problém popíše, navrhne možné řešení a s vědeckou metodičností rozebírá výhody a nevýhody jednotlivých variant.

Mě zaujaly dva vzory, které jsem mohl ihned aplikovat na projektu jdemenato.cz.

Enumerace

Standardním přístupem je enumeraci omezit pomocí omezení na sloupci.

CREATE TABLE Bugs (
   -- other columns
   status VARCHAR(20),
   status VARCHAR(20) check (status in ('NEW', 'IN PROGRESS', 'FIXED'))
); 

Lepším řešením je ale vytáhnout celou enumeraci do nové tabulky, kde hodnota enumerace je přímo primárním klíčem.

CREATE TABLE BugStatus (
   status VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW' ), ('IN PROGRESS' ), ('FIXED' );

CREATE TABLE Bugs (
   -- other columns
   status VARCHAR(20),
   FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE
);

Lze se pak se pak krásně dotazovat na všechny hodnoty

SELECT status FROM BugStatus ORDER by status;

Elegantně jdou přidávat nové hodnoty do enumerace i

INSERT INTO BugStatus (status) VALUES ('DUPLICATE' );

a díky ON UPDATE CASCADE i nahrazovat nahrazovat historicky špatně zvolené.

UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS' ;

Naivní stromy

Běžně jsem se setkal s tím, že stromovou struktura se řeší pomocí reference na sebe sama.

CREATE TABLE Comments (
   comment_id SERIAL PRIMARY KEY,
   parent_id BIGINT UNSIGNED,
   comment TEXT NOT NULL,
   FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);

I pokud má vaše databáze podporu pro hierarchické dotazy, není to žádná hitparáda.

WITH CommentTree
   (comment_id, bug_id, parent_id, author, comment, depth)
AS (
   SELECT *, 0 AS depth FROM Comments
   WHERE parent_id IS NULL
UNION ALL
   SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct
   JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM CommentTree WHERE bug_id = 1234;

Autor popisuje několik variant, jak vazby ukládat. Mě osobně se nejvíce líbilo řešení pomocí closure table - doporučuji k nastudování.

Spring configuration files - best practice

I prefer to configure spring with xml files. The question is, how they should be named and where should be located.

The official spring documentation do not provide any recommendation. Here are some with I advocate.

Name consistency

Start all files with the same prefix applicationConfig*.xml

E.g. applicationConfig-security.xml, applicationConfig-hibernate.xml, applicationConfig-quartz.xml etc.

File Location

• JAR -> src/main/resources/META-INF/spring
• WAR -> src/main/webapp/WEB-INF/spring

Do not use version number in schema reference

Instead of

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
  ...
</beans> 

Use this

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">
  ...
</beans> 

Why?

Spring automatically use highest version available from maven dependencies. Upgrade to newer version of spring is much more easy.

Řízení transakcí přes různé DAO implementace

Na startu projektu jdemenato.cz jsme DAO vrstvu implementovali přes JPA/Hibernate - standardně dle návodů.

V produkčním režimu, kde nám neustále roste počet uživatelů, jsme s tímto naivním řešením vydrželi jen několik měsíců. Kritické dotazy, které nejvíce vytěžovaly databázi,  jsem přepsali přes Criteria API na "lepší" SQL dotazy.

Po dalších měsících produkčního života nás zákaznické požadavky přinutili některé SQL dotazy psát ručně. Nejprimitivnější jsme zrealizovali přes Hibernate native SQL ale u složitějších jsme šáhli na JdbcTemplate a později k MyBatis.

To ovšem nastolilo problém s řízením databázových transakcí, jejichž součástí je více DAO technologií.

Ukázalo se, že na podobné případy chlapci ve springu pamatovali a stačilo vyměnit HibernateTransacionManager za DataSourceTransactionManager.

Tímto způsobem je DataSource "nejmenším společním jmenovatelem" spojující DAOs implementované přes Hibernate, MyBatis i JdbcTemplate.

Aby si AnnotationSessionFactoryBean nevytvořila svůj vlastní transakční manager, je nutné nastavit ji property useTransactionAwareDataSource=true.

        
<bean name="sessionFactory" 
  class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
  <property name="dataSource" ref="dataSource"/>       
  <property name="useTransactionAwareDataSource" value="true"/>
</bean>
<bean id="sqlSessionFactory" 
         class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="dataSource" ref="dataSource"/>
  <property name="configLocation" value="classpath:/META-INF/mybatis/myBatis-configuration.xml"/>
</bean>

Inspirace pražskou policií.

Logování

Na projektech, kterých se účastním, se ještě dnes setkávám kódem, který považuje logování pomocí System.out za výborný nápad. Proto bych v tomto článku chtěl rozebrat, co považuji konci roku 2013 za nejlepší logovací řešení.

Přehled

V javě máme několik možností, jak logovat:

1. System.out respektive System.err,
2. java.util.logging (JUL),
3. Jakarta (Apache) commons logging (JCL),
4. log4j,
5. slf4j a
6. logback.

System.out resp. System.err

Tuto variantu používám jen při zkoušení izolovaných věcí a záměrně odřezávám nepotřebné závislosti. Funguje na všech verzí javy.

Hlavní důvody, proč nepoužívat System.out

• Nelze konfiguračně vypnout na různých prostředích což má za následek výkonostní a bezpečnostní problémy a
• nelze konfiguračně nastavit úroveň zpráv.

Java Util Logging (JUL)

Tato možnost již máme v JRE myslím od verze 1.5. Kupodivu jsem ji  na komerčních projektech ani v open-source projektech nepotkal. Podle mě se neujala, protože přišla tak trochu s křížkem po funuse. 

log4j

Tuto možnost jsem používal na SE i EE projektech před pěti a více lety. Nenarážel jsem na limity tohoto řešení a byl jsem s ním byl spokojen. Možnosti konfigurovatelnosti ale daleko zaostávají za jeho nástupcem - logback.  

Jakarta Commons Logging (JCL)

Toto řešení mělo za cíl unifikovat rozhraní mezi různými logovacími frameworky. Samo o sobě logování neřeší, jen posílá zprávy do konkrétních implentací - log4i, JUL.  Rozhraní je skutečně jednoduché a můj oblíbený spring framework je tímto api prolezlý skrz naskrz. Toto řešení bohužel sebou nese některé problémy, kvůli kterým bych toto řešení nedoporučoval. 

slf4j

Tato knihovna řeší stejný problém jako JCL - tedy unifikace rozhraní nad různými logovacími API. Nové komerční projekty i řada open-source projektů dnes používá právě toto api. Dnes ho používám na několika komerčních i sranda projektech k plné spokojenosti. 

logback

Autorem je tvůrce log4j i slf4j - Ceki Gülcü.

Důvody, proč přejí z log4j na logback sám sepsal v článku Logback: Reasons to Switch 

Mě osobně nejvíce oslovily dvě vlastnosti:

1. podmíněná konfigurace, kterou používám pro nastavení úrovně logování v závislosti na prostředí. 
2. SiftingAppender, který umožňuje logovat dle dynamického parametru, např zalogovaného uživatele.

Závěr

• Nepoužívejte System.out resp. System.err
• Používejte slf4j spolu s logback.

Logování pomalých dotazů v PostgreSQL

Na produkčním prostředí jsem se snažil zjistit, které dotazy databázi nejvíce vytěžují.

Mile mě překvapilo, jak mocný je v tomto ohledu PostgreSQL.

Jednoduché logování dotazů, jejichž čas zpracování trvá více jak monitorovaný čas mě umožnil identifikovat dotazy, o kterých jsem ani netušil, že by mohli dělat problém.

Stačí v postgresql.conf zapnout magický přepínač log_min_duration_statement, který je defaultně vypnutý.

Pokud ho nastavím takto

log_min_duration_statement=100

tak mi do logu databáze zapíše dotazy, které trvají více jak 100ms.
Pak už přichází ke slovu klasický explain a refaktoring.

Nejkrásnější filmová melodie