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.
