OpenStats - databázová struktura
Cílem tohoto článku je stručně (ale dostatečně podobně) popsat databázovou strukturu používanou pro logování dat v rámci projektu OpenStats, a pro ukládání vygenerovaných reportů. Obsahuje samozřejmě popis jednotlivých tabulek a jejich sloupců, ale také vysvětlení některých zdánlivě nelogických řešení.
Databáze projektu OpenStats (resp. její část používaná pro sběr informací), je tvořena následujícími tabulkami:
- sessions - seznam sezení "sezení" (session) a informace o uživateli
- actions - seznam akcí pro "sezení" (každé "sezení" se skládá z jedné nebo více akcí)
- parameters - seznam parametrů pro akce (páry klíč / hodnota)
- pages - seznam stránek (každá akce je navázána na některou stránku)
- countries - seznam zemí (ze kterých přistupují uživatelé)
- languages - seznam jazyků (které uživatelé používají)
- architectures - seznam architektur (např. x86, mips, ...)
- browsers - seznam známých prohlížečů (MS IE, Firefox, Mozilla, ...)
- operating_systems - seznam operačních systémů (Windows XP, Linux, ...)
- action_types - seznam typů akcí (zobrazení dokumentu, odeslání formuláře, ...)
- prebuilt_stats_global - vygenerované globální statistiky
- prebuilt_stats_of_action_types - vygenerované statistiky dle stránky
- prebuilt_stats_of_architectures - vygenerované statistiky dle architektury
- prebuilt_stats_of_browsers - vygenerované statistiky dle browseru
- prebuilt_stats_of_operating_systems - vygenerované statistiky dle operačního systému
- prebuilt_stats_of_pages - vygenerované statistiky dle stránky
První tři tabulky (Sessions - Actions - Parameters) jsou klíčové pro celý systém, tvoří jakousi kostru na kterou jsou následně navázány další informace. Podívejme se nyní na jednotlivé tabulky, na jejich strukturu, význam, použití a provázání s ostatními tabulkami.
Sessions
Tato tabulka obsahuje informace společné pro celé sezení, tj. základní informace o uživateli - IP adresu, informace o jeho prohlížeči a operačním systému, zemi, a některé HTTP hlavičky. Struktura tabulky je následující:
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| browser_id | integer | ano | dodatečně | |
| os_id | integer | ano | dodatečně | |
| arch_id | integer | ano | dodatečně | |
| country_id | char(2) | ano | dodatečně | |
| visitor_id | integer | ne | ||
| ip | inet | ne | ||
| http_ip_forward | varchar(255) | ano | ||
| http_referer | text | ano | ||
| http_user_agent | varchar(255) | ano | ||
| http_language | varchar(255) | ano | ||
| session_date | timestamp | ne | now() | |
| browser_minor | varchar(16) | ano | ||
| browser_major | varchar(16) | ano | ||
| processed | boolean | ne | false |
Některé parametry (označené jako "dodatečně" v poznámce) jsou doplňovány až během následného zpracování - důvodem je pravděpodobnost vyšší časové náročnosti neboť pro jejich určení jsou využívány regulární výrazy, reverzní dotazy na DNS, apod. Hodnota ve sloupci "processed" určuje zda již toto dodatečné zpracování na daném záznamu proběhlo či nikoliv.
Sloupce os_id, arch_id a country_id jsou odkazy do tabulek Architectures, Countries a Operating_systems. Na této tabulce závisí tabulka Actions.
Poznámka: Na několika webech s vyšší návštěvností jsem na touto tabulku aplikoval partitioning (dle primárního klíče), a vzhledem k propagaci této hodnoty bylo možno i další dvě "klíčové" tabulky (Actions a Parameters) rozdělit stejným způsobem (a díky tomu zachovat referenční integritu mezi těmito tabulkami). Tím se nejen výrazně zjednodušuje údržba (zálohování a odstranění starých dat), ale také lze zvýšit výkon některých dotazů při zpracování.
Actions
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| session_id | integer | ne | primary key | |
| type_id | integer | ne | ||
| page_id | integer | ne | ||
| language | char(2) | ne | ||
| action_date | timestamp | ne | now() |
Tato tabulka obsahuje informace o jednotlivých akcích (zobrazení stránky, odeslání formuláře, apod.) v rámci sezení. U každé akce jsou informace zejména o sezení ke kterému akce náleží, stránce na které akce proběhla (cizí klíč do číselníku Pages), typ akce (cizí klíč do číselníku Action_types), jazyk ve kterém byla akce provedena (2-znakový kód jazyka dle ISO 639-2 Code) a datum akce.
Tato tabulka má složený primární klíč (session_id, id).
Sloupce type_id a page_id jsou odkazy do tabulek Action_types a Pages. Na této tabulce závisí tabulka Parameters.
Poznámka: Pokud je tabulka Sessions rozdělena dle primárního klíče, lze tabulku Actions rozdělit dle sloupce "session_id" (a Parameters lze rozdělit stejným způsobem). Díky tomu lze definovat referenční integritu mezi těmito tabulkami, a výrazně zjednodušit údržbu (zálohování a odstranění starých dat), ale také zvýšit výkon některých dotazů při následném zpracován dat.
Parameters
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| session_id | integer | ne | primary key | |
| action_id | integer | ne | primary key | |
| name | varchar(32) | ne | ||
| value | varchar(255) | ne |
Občas se hodí uložit další informace o akci pro které v tabulce Actions není vyhrazen samostatný sloupec, například některé interní parametry zjištěné při zpracování dotazu (například ID zobrazovaného článku, informaci zda bylo zpracování zaslaného formuláře úspěšné, apod.). Tyto parametry lze zaznamenat ve formě párů klíč : hodnota do této tabulky.
Tato tabulka má složený primární klíč (session_id, action_id, id).
Poznámka: Pokud jsou tabulky Sessions a Actions rozděleny dle sloupců Sessions.id a Actions.session_id, lze tabulku Parameters rozdělit stejným způsobem, tj. dle sloupce "session_id". Díky tomu lze definovat referenční integritu mezi těmito tabulkami, a výrazně zjednodušit údržbu (zálohování a odstranění starých dat), ale také zvýšit výkon některých dotazů při následném zpracován dat.
Pages
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| name | varchar(32) | ne | ||
| description | text | ano |
Tato tabulka je vlastně číselníkem, ve kterém jsou definovány stránky vašeho webu. Stránkami jsou ve většině případů zjednodušeně řečeno míněny "skripty", nikoliv jednotlivé články (nebo obdobné položky) těmito skripty zobrazované.
Pokud používáte některý z CMS systémů, je možné že už tabulku s definicí stránek máte - v tom případě je třeba tyto tabulky synchronizovat (ručně nebo automaticky - například pomocí triggerů) nebo vhodným způsobem sloučit.
Countries
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| code | char(3) | ne | primary key | |
| short_code | char(2) | ano | ||
| name | varchar(64) | ne |
Tato tabulka je číselníkem zemí dle standardu ISO 3166 (jako primární klíč jsou použity 3-znakové kódy, nicméně tabulka obsahuje také 2-znakové kódy).
Languages
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| code | char(3) | ne | primary key | |
| short_code | char(2) | ano | ||
| name | varchar(32) | ne |
Tato tabulka je číselníkem jazyků dle standardu ISO 639-2 Code (jako primární klíč jsou použity 3-znakové kódy, nicméně tabulka obsahuje také 2-znakové kódy).
Architectures
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| name | varchar(64) | ne | ||
| priority | integer | ne | 0 | |
| regexp | varchar(255) | ne |
Tato tabulka je číselníkem architektur (např. x86, x86_64, mips, apod.) obohacený o definici detekčních pravidel - regulární výraz (a jeho prioritu). Detekce architektury probíhá porovnáním hodnoty HTTP User-Agent s těmito regulárními výrazy - z odpovídajících pravidel je poté vybráno to s nejvyšší prioritou.
Tato tabulka je průběžně zpřesňována a doplňována o další architektury (detekční pravidla).
Browsers
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| name | varchar(128) | ne | ||
| url | varchar(255) | ano | ||
| regexp | varchar(255) | ne | ||
| version_regexp | varchar(255) | ano | ||
| priority | integer | ne | 0 | |
| is_robot | boolean | ne | false |
Tato tabulka je číselníkem prohlížečů (např. MS IE, Firefox, Google Chrome, apod.) obohacený o definici detekčních pravidel - regulární výraz (a jeho prioritu) a informaci zda je prohlížeč robot (např. googlebot, apod.). Detekce prohlížeče probíhá obdobně jako v případě architektur - porovnáním hodnoty HTTP User-Agent s těmito regulárními výrazy, přičemž z odpovídajících pravidel je poté vybráno to s nejvyšší prioritou. Tabulka dále obsahuje pravidlo pro extrakci informace o verzi prohlížeče.
Tato tabulka je průběžně zpřesňována a doplňována o další prohlížeče (detekční pravidla).
Operating_systems
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| name | varchar(128) | ne | ||
| url | varchar(255) | ano | ||
| priority | integer | ne | 0 | |
| regexp | varchar(255) | ne | ||
| version | varchar(255) | ano |
Tato tabulka je číselníkem operačních systémů (např. Windows XP, Linux, VAX/VMS, apod.) obohacený o odhaz na domácí stránku projektu a detekční pravidlo - regulární výraz (a jeho prioritu). Některé operační systémy mají více variant (například Windows má varianty XP, 2000, Vista, ...) a pro tyto případy je v tabulce sloupec "version".
Detekce operačního systému probíhá obdobně jako v případě architektur - porovnáním hodnoty HTTP User-Agent s regulárními výrazy, přičemž z odpovídajících pravidel je poté vybráno to s nejvyšší prioritou.
Tato tabulka je průběžně zpřesňována a doplňována o další operační systémy (detekční pravidla).
action_types
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| id | serial | ne | primary key | |
| name | varchar(32) | ne |
Číselník typů akcí (zobrazení dokumentu, odeslání formuláře, apod.). Vlastní typy akcí můžete jednoduše vytvořit vkládáním řádků do této tabulky.
prebuilt_stats_global
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované "globální" statistiky. Význam "count_*" sloupců asi netřeba komentovat, takže se podívejme na ty zbývající.
Během analýzy jsou data agregována dle jednoho ze tří jasně určených časových intervalů (měsíc, týden, den) a informace potřebné pro identifikaci konkrétního intervalu jsou uloženy ve sloupcích "interval_type" a "date_from" - první jmenovaný obsahuje jednu z hodnot "MONTH", "WEEK" a "DAY" a druhý jmenovaný počátek intervalu.
Sloupec "computed_date" obsahuje informaci kdy byl daný řádek tabulky vygenerován - pokud obsahuje datum před koncem intervalu, jedná se o nekompletní data která se při další analýze mohou změnit.
prebuilt_stats_of_action_types
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| type_id | integer | ne | ||
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované statistiky dle typu akce. Význam většiny sloupců již byl vysvětlen u tabulky prebuilt_stats_global, a význam jediného nového sloupce - type_id - je jednoduchý, neboť se jedná o cizí klíč do tabulky Action_types.
prebuilt_stats_of_architectures
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| arch_id | integer | ne | ||
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované statistiky dle architektury. Význam většiny sloupců již byl vysvětlen u tabulky prebuilt_stats_global, a význam jediného nového sloupce - arch_id - je jednoduchý, neboť se jedná o cizí klíč do tabulky Architectures.
prebuilt_stats_of_browsers
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| browser_id | integer | ne | ||
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované statistiky dle prohlížeče. Význam většiny sloupců byl vysvětlen u tabulky prebuilt_stats_global, a význam jediného nového sloupce - type_id - je jednoduchý, neboť se jedná o cizí klíč do tabulky Browsers.
prebuilt_stats_of_operating_systems
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| os_id | integer | ne | ||
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované statistiky dle operačního systému. Význam většiny sloupců byl vysvětlen u tabulky prebuilt_stats_global, a význam jediného nového sloupce - type_id - je jednoduchý, neboť se jedná o cizí klíč do tabulky Operating_systems.
prebuilt_stats_of_pages
| název | typ | NULL | výchozí hodnota | poznámka |
|---|---|---|---|---|
| date_from | timestamp | ne | primary key | |
| computed_date | timestamp | ne | ||
| interval_type | varchar(16) | ne | primary key | |
| page_id | integer | ne | ||
| count_visitors | integer | ne | 0 | |
| count_ips | integer | ne | 0 | |
| count_actions | integer | ne | 0 | |
| count_sessions | integer | ne | 0 |
Tabulka do které jsou ukládány vygenerované statistiky dle typu akce. Význam většiny sloupců byl vysvětlen u tabulky prebuilt_stats_global, a význam jediného nového sloupce - type_id - je jednoduchý, neboť se jedná o cizí klíč do tabulky Pages.
