Forum Delphi.cz

Delphi => FireDAC => Téma založeno: miroB 23-07-2018, 15:01:42

Název: FireDAC + SQLite + TBCD - úvaha
Přispěvatel: miroB 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í
Možné riešenie na základe predošlého:
UDF funkcie, ktoré by mohli pokryť odhadom tak 90% potrieb pre bežné analýzy:
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 (http://author=Miroslav Baláž link=topic=16248.msg100776#msg100776 date=1532201517)
Název: Re:FireDAC + SQLite + TBCD - úvaha
Přispěvatel: Delfin 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 (https://www.sqlite.org/datatype3.html#type_affinity) 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.
Název: Re:FireDAC + SQLite + TBCD - úvaha
Přispěvatel: miroB 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.
Č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;
Název: Re:FireDAC + SQLite + TBCD - úvaha
Přispěvatel: miroB 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;
Název: Re:FireDAC + SQLite + TBCD - úvaha
Přispěvatel: miroB 24-07-2018, 19:03:09
..
  • 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:

Poznámka:  Ďalšia z výhod:
Název: Re:FireDAC + SQLite + TBCD - úvaha
Přispěvatel: Delfin 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).