Autor Téma: FireDAC + SQLite + TBCD - úvaha  (Přečteno 3861 krát)

Offline miroB

  • Guru
  • *****
  • Příspěvků: 655
  • Karma: 17
    • Verze Delphi: D1,2,3,4,7,2005 .. D Tokyo 10.2.3 Pro C/S, D12.2
FireDAC + SQLite + TBCD - úvaha
« kdy: 23-07-2018, 15:01:42 »
Dovoľte krátky technický rozbor a úvahu s návrhom riešenia. Ak by ste mali k tomu čo povedať, budem rád.
FireDAC pre TBCD v SQLite zabezpečuje určitú podporu, pričom platí
  • NUMERIC(30,0) automaticky priradí typ TNumericField
  • NUMERIC(<30,0) nemá zmysel. lebo sa prevádza na krátke typy ako INT, alebo Double ..
  • Keďže SQLite ako také nepozná BCD, všetky operácie s TNumericField v rámci SQL ho teraz zredukujú na kratšie typy. Len vďaka tomu, že je uložený ako text a ten sa konvertuje pri výpočte na krátke typy
  • Bez využitia UDF, BCD vlastne nemá význam
    Pre ozajstné využitie je treba vytvoriť vlastné UDF funkcie. To nie je problém 1
  • FireDAC,Phys.SQLiteWrapper pre typ etNumber uvažuje subtyp Text, alebo Blob. Sám však pole Numeric/Bcd zakladá ako Text, nie ako Blob.
Možné riešenie na základe predošlého:
  • Aj tak sa všetky výpočty musia diať cez UDF
  • Pre BCD použiť Blob. Umožní nám to uložiť priamo BCD a nie text. Pre ukladanie UNICODE má text min 2 znaky pre každú číslicu.
  • Z BCD Ukladať len tie bajty, ktoré obsahujú hodnoty. Napr. pre číslo 10.1 stačia 4 bajty, netreba všetkých 34 bajtov. Rozsah by bol od 3 bajtov po 34 max. Ten Max by sa dosiahol len málokedy
  • Pri čítaní pre ďalšie spracovanie automaticky identifikovať veľkosť a vyplniť komplet BCD.
    O tomto zatiaľ len matne tuším. Ale dúfam, by sa to malo dať zvládnuť.
  • Pri čítaní pre prezentáciu BCD konvertovať na Text s prípadným formátovaním
UDF funkcie, ktoré by mohli pokryť odhadom tak 90% potrieb pre bežné analýzy:
  • Rozdiel
  • Súčet
  • Násobenie
  • Delenie
  • Teoreticky aj vybrané kombinácie, vyššie uvedených
  • Porovnanie/Compare
  • Agregačné funkcie: SUM/COUNT/MAX/MIN/AVG
To je prvotná úvaha. Samozrejme rátam s tým, že bude treba prekopať FireDAC. Len verím, že toho nebude až tak extra veľa. To nemám na mysli UDF. Tie zvládnem.
Vaše námety, pripomienky, či podnety sú vrelo vítané.

1 Viď funkcia Rozdiel( bcd1, bcd2: TBCD ):
author=Miroslav Baláž link=topic=16248.msg100776#msg100776 date=1532201517
« Poslední změna: 23-07-2018, 15:18:11 od Miroslav Baláž »

100831

  • Host
Re:FireDAC + SQLite + TBCD - úvaha
« Odpověď #1 kdy: 24-07-2018, 12:28:17 »
Dovoľte krátky technický rozbor a úvahu s návrhom riešenia. Ak by ste mali k tomu čo povedať, budem rád.
FireDAC pre TBCD v SQLite zabezpečuje určitú podporu, pričom platí
  • NUMERIC(30,0) automaticky priradí typ TNumericField
  • NUMERIC(<30,0) nemá zmysel. lebo sa prevádza na krátke typy ako INT, alebo Double ..
  • Keďže SQLite ako také nepozná BCD, všetky operácie s TNumericField v rámci SQL ho teraz zredukujú na kratšie typy. Len vďaka tomu, že je uložený ako text a ten sa konvertuje pri výpočte na krátke typy
  • Bez využitia UDF, BCD vlastne nemá význam
    Pre ozajstné využitie je treba vytvoriť vlastné UDF funkcie. To nie je problém 1
  • FireDAC,Phys.SQLiteWrapper pre typ etNumber uvažuje subtyp Text, alebo Blob. Sám však pole Numeric/Bcd zakladá ako Text, nie ako Blob.

Ono je to o affinity SQLite a vyberu storage class na zaklade predavanych hodnot (a jejich pripadne konverze, coz muze zpusobit ztratu dat!).

Samozrejme muzes predelat FireDAC tak aby cetl a ukladal hodnoty pro sloupce s NUMERIC affinity jako raw TBcd do storage class BLOB, prijdes tim vsak o moznost s nimi pracovat v SQL (SELECT napr. v externim DB manageru je zobrazi jako raw data). Pomerne snadno:

Kód: Delphi [Vybrat]
  1. procedure TSQLiteValue.SetData(AValue: Pointer; ASize: Integer; AExtDataType: TSQLiteExtDataType = etUnknown);
  2. begin
  3.   ...
  4.   case AExtDataType of
  5.     ...
  6.     etNumber:
  7.     begin
  8.       pVal := AValue;
  9.       iSize := ASize;
  10.       iType := SQLITE_BLOB;
  11.     end;
  12.     ...
  13.   end;
  14.   ...
  15. end;

Kód: Delphi [Vybrat]
  1. function TSQLiteValue.GetData(var AValue: Pointer; var ASize: Integer; AByRef: Boolean = False;
  2.   AExtDataType: TSQLiteExtDataType = etUnknown): Boolean;
  3. begin
  4.   ...
  5.   case AExtDataType of
  6.     ...
  7.     etNumber:
  8.       if AByRef then
  9.       begin
  10.         AValue := pVal;
  11.         ASize := iSize;
  12.       end
  13.       else
  14.       begin
  15.         if iType = SQLITE_BLOB then
  16.         begin
  17.           ASize := iSize;
  18.           Move(pVal^, PBcd(AValue)^, ASize);
  19.         end
  20.         else
  21.         begin
  22.           case iType of
  23.             SQLITE_TEXT: FDStr2BCD(PChar(pVal), iSize, PBcd(AValue)^, '.');
  24.             SQLITE_INTEGER: FDDouble2BCD(PInt64(pVal)^, PBcd(AValue)^);
  25.             SQLITE_FLOAT: FDDouble2BCD(PDouble(pVal)^, PBcd(AValue)^);
  26.           end;
  27.           ASize := SizeOf(TBcd);
  28.         end;
  29.       end;
  30.     ...
  31.   end;
  32.   ...
  33. end;

A v UDF pak vratit:

Kód: Delphi [Vybrat]
  1. procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput;
  2.   var AUserData: TObject);
  3. var
  4.   Result: TBcd;
  5. begin
  6.   Result := AInputs[0].AsNumber - AInputs[1].AsNumber;
  7.   AOutput.SetData(@Result, SizeOf(Result), etNumber);
  8. end;

Ale prinese to mene uzitku nez prospechu. Porad moc netusim co je cilem. Mej vsak stale na pameti co je SQLite affinity a storage class.

Offline miroB

  • Guru
  • *****
  • Příspěvků: 655
  • Karma: 17
    • Verze Delphi: D1,2,3,4,7,2005 .. D Tokyo 10.2.3 Pro C/S, D12.2
Re:FireDAC + SQLite + TBCD - úvaha
« Odpověď #2 kdy: 24-07-2018, 15:00:40 »
..
Kód: Delphi [Vybrat]
  1. procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput;
  2.   var AUserData: TObject);
  3. var
  4.   Result: TBcd;
  5. begin
  6.   Result := AInputs[0].AsNumber - AInputs[1].AsNumber;
  7.   AOutput.SetData(@Result, SizeOf(Result), etNumber);
  8. end;
..
Dalo by sa ušetriť pri ukladaní dát, predstavoval by som si to takto (Doplnený výpočet potrebnej veľkosti pre uloženie):
Kód: Delphi [Vybrat]
  1. procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput;
  2.   var AUserData: TObject);
  3. var
  4.   iSize : Integer;
  5.   Result: TBcd;  // BCD ukladá 2 bajty + reťazec bajtov [0..31]. Ale nie všetky sú potrebné.
  6. begin            // Z prvého bajtu sa dá vypočítať potrebná dĺžka na uloženie
  7.   Result := AInputs[0].AsNumber - AInputs[1].AsNumber;
  8. // Vypočíta len potrebné bajty pre uloženie.
  9.   iSize := 2 + Result.Precision div 2 + Result.Precision mod 2;  
  10. // Počet odložených bajtov je iSize, namiesto SizeOf(Result),
  11.   AOutput.SetData( @Result, iSize, etNumber );
  12. end;
Ušetí sa nielen manipulácia s textom / UTF16 / UTF8, ale aj nepotrebné bajty.
To by v reále mohlo predstavovať kľudne 3/4 úspory miesta ba možno aj výrazne viac.
Pre milióny riadkov, by to možno už stálo za úvahu.
Asi by to aj bežalo rýchlejšie.
  • V zmysle tvojho podkladu, bolo vyriešené správne ukladanie pre funkciu.
  • Vkladanie cez kód Delphi v tvare Field.AsBcd=.. správne ukladá data do BLOBU.
  • Ukladanie cez INSERT INTO by potrebovalo vyriešiť.
    To sa stále ukladá ako TEXT, aj po týchto zásahoch.
    Prikladám obrázok, ako sú data viditeľné v programe SQLiteSPY.
    Oba riadky obrázku obsahujú rovnaké BCD čísla
Čo sa týka kompatibility dát mimo môjho programu, tak si nerobím veľké ambície.
Budem tam mať viacero úprav s cieľom vylepšiť vlastné používanie SQLite.
Ale bude k dispozícii export, import.. Nakoniec, je stále možnosť využiť klasický Double..

Pre istotu kód, ktorý ukazuje naplnenie tabulky z priloženého obrázku:
Kód: Delphi [Vybrat]
  1.   qryCreate.SQL.Text := 'DROP TABLE IF Exists tblBCD;' + #13#10 +
  2.                         'CREATE TABLE tblBCD ( n_bcd23 FLOATTEXT, n_bcd25 FLOATTEXT );';
  3.   qryCreate.ExecSQL;
  4.   qryCreate.SQL.Text := 'SELECT n_bcd23, n_bcd25 from tblBCD;';
  5.   qryCreate.Open;
  6.   qryCreate.Append;                    // vloženie do tabuľky
  7.   x2                 := StrToBcd( '-12345678901234567890.12345678901234567890123456789012', fmtSettingsDOT );   // BCD číslo
  8.   qryCreate.Fields[ 0 ].AsLocaleBCD := x2; // vloženie do tabuľky
  9.   qryCreate.Fields[ 1 ].setAsLocaleBCD( '123456789012345.6789' ); // vloženie do tabuľky
  10.   qryCreate.Post;
  11. //  qryCreate.Close;
  12.   qryCreate.SQL.Text := 'Insert into tblBCD ( n_bcd23, n_bcd25 ) values' + #13#10 +
  13.                         '( ''-12345678901234567890.12345678901234567890123456789012'',' + #13#10 +
  14.                            '123456789012345.6789 );';
  15.   qryCreate.ExecSQL;
« Poslední změna: 24-07-2018, 15:09:10 od Miroslav Baláž »

Offline miroB

  • Guru
  • *****
  • Příspěvků: 655
  • Karma: 17
    • Verze Delphi: D1,2,3,4,7,2005 .. D Tokyo 10.2.3 Pro C/S, D12.2
Re:FireDAC + SQLite + TBCD - úvaha
« Odpověď #3 kdy: 24-07-2018, 16:01:09 »
Doplnenie k predošlému príspevku.
Úprava v FireDAC.Phys.SQLiteWrapper.TSQLiteValue.GetData:
Kód: Delphi [Vybrat]
  1. function TSQLiteValue.GetData(var AValue: Pointer; var ASize: Integer;
  2. ..
  3.   case AExtDataType of
  4. ..
  5.     etNumber:
  6. ..
  7.       case iType of  
  8. .. // V Blobe je len toľko bajtov, koľko bolo ozaj treba
  9.         SQLITE_BLOB :
  10.           begin // iSize: Zistí veľkosť dát z prvého byte.
  11.           iSize := 2 + PByte( pVal )^ div 2 + PByte( pVal )^ mod 2;
  12.           FillChar( pByte( AValue )^, 34, #0 ); // Vynulovanie BCD
  13. // Presunutie len takého počtu bajtov, ktoré boli uložené
  14.           Move( PByte( pVal )^, pByte( AValue )^, iSize );
  15.           end;
podobne pre TSQLiteValue.SetData
Kód: Delphi [Vybrat]
  1. ..
  2. case AExtDataType of
  3. ..
  4.   etNumber:
  5. ..
  6.     pVal  := AValue;
  7.     iSize := 2 + PByte( pVal )^ div 2 + PByte( pVal )^ mod 2;
  8.     iType := SQLITE_BLOB;
« Poslední změna: 24-07-2018, 16:21:25 od Miroslav Baláž »

Offline miroB

  • Guru
  • *****
  • Příspěvků: 655
  • Karma: 17
    • Verze Delphi: D1,2,3,4,7,2005 .. D Tokyo 10.2.3 Pro C/S, D12.2
Re:FireDAC + SQLite + TBCD - úvaha
« Odpověď #4 kdy: 24-07-2018, 19:03:09 »
Excellent
Rated 1 time
..
  • Ukladanie cez INSERT INTO by potrebovalo vyriešiť.
Vyriešené:  pomocou UDF funkcie StrToBCD
INSERT INTO sa teraz použije takto:
Kód: MySQL [Vybrat]
  1. INSERT INTO MyTable ( TheValue ) VALUES ( StrToBCD( '2.0000000000000000009' ) );
Nie je to možné vyriešiť inak, pretože bez funkcie StrToBCD je to v výlučne v réžii SQLite.
Keď SQLite engina dostane na vloženie Text, strčí ho rovno do premennej s typom SQLITE_TEXT.
Použitie funkcie v procese vkladania, umožní, aby engina odovzdala réžiu FireDAC. No a tam to už máme vyriešené.
PS:
  • dorobil som tiež agregačnú funkciu bcdSUM. Zdá sa, že to celé, pôjde podľa očakávania.
  • ošetril som funkcie na NULL
  • Mená funkcií som vložil do TSQLiteValueDef.CreateForColumn.
    Tak aby vrátili hodnotu typu FLOATTEXT

Poznámka:  Ďalšia z výhod:
  • Keď boli hodnoty uložené ako text, funkcia vracala text dĺžky 15 000 (MaxStrLen). To bolo v dbGride veľmi nepraktické. Výstup BLOBU do dbGridu by som riešil ako SQLiteSPY. To je sympatické. Moje BCD však teraz vystupujú ako dlhé čísla, nie ako Blob.
« Poslední změna: 24-07-2018, 19:20:32 od Miroslav Baláž »

100835

  • Host
Re:FireDAC + SQLite + TBCD - úvaha
« Odpověď #5 kdy: 25-07-2018, 01:45:09 »
Nie je to možné vyriešiť inak, pretože bez funkcie StrToBCD je to v výlučne v réžii SQLite.

To jsem se snazil rict tim "prijdes tim vsak o moznost s nimi pracovat v SQL" (myslel jsem tim konstantni hodnoty v prikazech; mel jsem byt presnejsi, mozna jsem je v nektere soukrome zprave zminil). Ta konverzni funkce je samozrejme treba vsude tam kde se s takovouto konstantni hodnotou pro dany typ sloupce pracuje.

Keď SQLite engina dostane na vloženie Text, strčí ho rovno do premennej s typom SQLITE_TEXT.

Ano, protoze pokud ma sloupec affinity TEXT, coz FLOATTEXT ma, pak se konstantni hodnota v SQL prikazu ulozi do storage class TEXT (ne binarni retezec BCD konvertovany na BLOB), a to jednoduse proto ze SQLite to z pohledu SQL tak vidi (zkratka nevi co s tou hodnotou zamyslis).