Ukládání dat do databáze

Ochrana proti vícenásobnému importu

Mohlo by se stát, že uživatel omylem naimportuje jeden výpis 2x. Nemusí to být chyba z nepozornosti typu "import 2x stejného souboru", klidně je možné, že naimportuje týdenní výpis a pak měsíční výpis, ve kterém je ten týden už také obsažen. V každém případě výsledkem bude několik desítek až stovek transakcí v tabulkách transfers a bank_transfers, které tam nepatří.

První řešení, které nás napadlo pro ošetření této chyby, je použití UNIQUE indexu přes všechny sloupce tabulky bank_transfers, které dohromady tvoří unikátní klíč. Bohužel jsme zjistili, že řádky výpisů z ebanky unikátní nejsou - běžně se nám stává, že ve stejné sekundě přijdou 2 platby od jednoho člena, které mají stejnou částku, variabilní symbol i text.

Proto jediné možné řešení spočívá v tomto přístupu:

• při ukládání každé bankovní transakce do databáze je nutné se podívat, jestli stejná transakce už v databázi není z nějakého minulého importu. Čas importu je zaznamenán v podvojném zaúčtování každé bankovní transakce, tj. v záznamu v tabulce transfers, který je s každým bank_transfer-em svázán.
• budeme tedy testovat, zda není splněná podmínka:

bank_transfer_exist() && bank_transfer->transfer->datetime != čas_aktuálního_importu

• test "bank_transfer_exist()" ovšem znamená, že budeme muset procházet všechny bank_transfers (tj. tisíce řádků) a porovnávat údaje ze všech sloupců na shodu. Aby tato operace byla co nejrychlejší, je potřeba tabulku bank_transfers vhodně naindexovat - vytvořit index přes více sloupců. Problematiku vícesloupcových indexů vysvětluje tento článek, při výrobě tohoto indexu potřebujeme statistiky, o kterých píše tento článek.

Ve FreenetISu kontrolu duplicit v tabulkách bank_transfers JOIN transfers řeší funkce public static function get_duplicities($data) která je v souboru freenetis/.../application/models/bank_transfer.php

Tato funkce je dostatečně efektivní.

Příklad: naše tabulka "bank_transfers" má teďka 20684 záznamů, tj. počet bank. převodů za 3 roky. Tabulka "transfers" má kolem 120tis. záznamů.

Přitom SQL dotaz pro hledání duplicitního řádku výpisu je bleskový. Vypadá takto (příklad našich reálných dat):

SELECT t.datetime, t.creation_datetime, t.text, bt . * FROM bank_transfers AS bt JOIN transfers AS t ON bt.transfer_id = t.id AND t.datetime = '2010-10-01 07:54:00' AND t.text = 'Prispevek 2010 - Karel V., Horní Dolní 23' AND bt.number = '20' AND bt.variable_symbol = 1234567

Zobrazeny záznamy 0 - 0 (1 celkem, dotaz trval 0.0211 sekund)

SQL dotaz "explain" vysvětluje, proč je to tak rychlé: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t ref PRIMARY,datetime datetime 773 const,const 1 Using where 1 SIMPLE bt ref transfer_id,number transfer_id 5 freenetis.t.id 1 Using where

Tj. nejdřív se v tabulce "transfers" pomocí multicolumn indexu (s hloupě zvoleným jménem) "datetime", který je vytvořen nad sloupci "datetime" a "text" najdou všechny transfery ze zadaného data se zadaným textem platby. To je typicky 1, max. 2 záznamy. Multicolumn používám proto, že by to mělo být efektivnější než 2 nezávislé indexy.

S těmito 1-2ma transfery se pak udělá JOIN na bank_transfers, kde jsou velmi rychle dohledány opravdové duplicity. Jak vidíš, multicolumn index "number" nakonec mysql asi nepoužije, protože pro 1-2 joinované položky už je to zbytečné.

Proč nepoužívám hash

V některých parserech jsem viděl kontrolu duplicit řešeno pomocí md5 hashe.

Pokud bych použil (md5) hash, pak bych tuto hash v databázi - jako každou součást join podmínky - určitě taky indexoval a abych tam neměl falešné duplicity (jakýkoli hash - tedy i md5 - může mít kolize, tj. že hash(x) == hash(y), přičemž x!=y), stejně bych v tom dotazu v případě shody hashů musel testovat i shodu všech hashovaných údajů.

Tj. ve výsledku by ten dotaz byl ještě o jednu AND podmínku delší a v tabulce bank_transfers bych měl o jeden sloupec víc.

Proto jsem hash nakonec vůbec nepoužil.

Tvorba převodů mezi podvojnými účty

Pokud se díváte na kód importu (application/controllers/import.php), tak se prosím radši ani nedívejte na funkci

   public function store_transfer_ebanka($data)

neboť to je naprosto strašný kód, vzniklý šílenými nápady naší SR, která chtěla, aby se za každou platbu členovi strhl poplatek 30 Kč, ale zároveň aby bankovní poplatek (teď myslím 7 Kč?) eBanky mu byl "darován" sdružením zpátky a aby to opravdu ve výpise transakcí člen viděl jako 2 řádky (-30 Kč, pak +7 Kč).

Kromě této hrůzy, která naštěstí byla novou SR uznána jako kravina a u FIO účtu už nebude provozvána, je tam další velký kus kódu, který se snaží z kombinací částek, poplatků a textu plateb sám poznat, zda je v položce výpisu členský příspěvek, úrok, bank. poplatek, operace s termínovaným vkladem, platba dodavateli atd. Rozpoznané typy plateb pak správně podvojně zaúčtuje.