Ukládání dat do databáze: Porovnání verzí

Z Freenetis Wiki
Přejít na: navigace, hledání
(Nová stránka: =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",...)
 
m (Quimi přesunul stránku Ukládání dat do databáze na Ukládání dat do databáze bez založení přesměrování)
 
(Nejsou zobrazeny 4 mezilehlé verze od 2 dalších uživatelů.)
Řádek 4: Řádek 4:
 
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.
 
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
+
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ů [http://blog.decaresystems.ie/index.php/2007/05/21/how-to-create-a-successful-multi-column-index-from-first-principals/ vysvětluje tento článek], při výrobě tohoto indexu potřebujeme statistiky, o kterých píše [http://blog.decaresystems.ie/index.php/2007/06/11/a-sample-query-to-determine-relative-cardinality/ 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.

Aktuální verze z 31. 1. 2018, 14:27

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.