SQLite a TBCD prakticky + Problém

[Miroslav Baláž]

Skúšam SQLite + FireDAC + TBCD.
Pôvodne ma vystrašila info z odkazu:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#High-Precision_Numbers

Spravil som pokus, viď priložený obrázok.
Tvorba a naplnenie tabuľky v SQL  (qryCreate):

Kód: MySQL [Vybrat]

1. DROP TABLE IF Exists tblBCD;
2. CREATE TABLE tblBCD ( bcd23 Numeric( 64,23 ), bcd25 Numeric( 64,25 ) );
3. Insert into tblBCD ( bcd23, bcd25 ) values
4.  ( '-12345678901234567890,12345678901234567890123456789012345678901',
5.    '-12345678901234567890,123456789012345678901234567890123456789012' );
6. select rowid, bcd23, bcd25 from tblBCD

Na výstupe sú však miesto desatinných oddeľovačov znaky "<". Je vidieť aj na obrázku.
Kontrola cez kód:

Kód: Delphi [Vybrat]

1. uses
2. Data.FmtBcd
3. ..
4. procedure TForm1.FormCreate(Sender: TObject);
5. var
6.   x, y, x2, y2 : TBCD;
7.   s, s2, s3 : String;
8. begin
9.   if dbMain.Connected then
10.     dbMain.Connected   := false;
11.   dbMain.Connected     := true;
12.   qryCreate.ExecSQL;
13.   qryCategories.Active := True;                 // PROBLÉM :
14.   s   := qryCategories.Fields[ 0 ].AsString; // s : obsahuje kompletný záznam, ale ten znak "<" ..
15.   s2  := StringReplace( s, '<', ',', [] );   // s2 je už s čiarkou ..
16.   y   := StrToBcd( s2 );                     // korektné BCD číslo
17.   s3  := BcdToStr( y - 1 );                  // korektný výsledok
18.   x2  := qryCategories.Fields[ 0 ].AsBcd;    // toto zdanlivo funguje, ale Precision je poškodená
19. end;

ešte SQL kód qryCategories:

Kód: MySQL [Vybrat]

1. select   *,rowid from tblBCD

Naplnenie cez kód sa však správa korektne:

Kód: Delphi [Vybrat]

1.   s := '-12345678901234567890,12345678901234567890123456789012';
2.   x2 := StrToBcd( s );                   // BCD číslo
3.   qryCategories.Edit;                    // vloženie do tabuľky
4.   qryCategories.Fields[ 0 ].AsBCD := x2; // vloženie do tabuľky
5.   qryCategories.Post;                    // vloženie do tabuľky
6.   y2 := qryCategories.Fields[ 0 ].AsBCD; // vloženie do tabuľky
7.   x := y2 - 1;                           // VYBRATIE z tabuľky + matem operácia
8.   s3  := BcdToStr( x );                  // koprektný výsledok
9. //    '-12345678901234567891,12345678901234567890123456789012345678901'

Zdá sa, že SQLite + FireDAC dokážu manažovať BCD
Problém je zatiaľ len s vkladaním pomocou SQL - INSERT INTO
Z kódu Delphi sa BCD čísla vložené a vybrané z tabuľky správajú korektne

Problém je zrejme vo FormatSettings, pretože vo Windows mám decimal separator ako čiarku.

ČIASTKOVÝ ZÁVER: BCD je v SQLite vďaka FireDAC použiteľné bez okľuky cez TEXT, či BLOB.
Funguje typ NUMERIC. Zatiaľ vidím jediný väčší problém: napĺňanie BCD cez INSERT INTO..

Apropos: operácie v databáze sa zrejme prevádzajú na Double, pretože matemat operácie dajú presnosť len v rámci Double.
To by vyzeralo na dodatočné UDF funkcie..

[vandrovnik]

A nemá se v tom INSERT použít desetinná tečka?

[Delfin]

Pôvodne ma vystrašila info z odkazu:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#High-Precision_Numbers

Duvod je na miste. Manual rika, ze pro takto precizni hodnoty mas pouzit datovy typ TEXT a mapovani datovych typu FireDAC. SQLite tak precizni hodnotu do datoveho typu REAL neulozi.

[vandrovnik]

Jinak hlodá ve mně zvědavost, z jakého měření padají tak přesná čísla, protože i double by uměl uložit vzdálenost ze Země k Alfa Centauri s přesností na 1 cm 10 m :-)

[Miroslav Baláž]

A nemá se v tom INSERT použít desetinná tečka?

S bodkou to nefungovalo vôbec. S čiarkou tak, ako som napísal.
Celkom verím, že sa vo vnútri procesov bije čiarka s bodkou.
Zrejme ak by som mal Win-y nastavene na US, prebehlo by všetko OK. Preskúmam neskôr.
Pri pokuse s čiarkou dáva Delphi túto hlášku:
'-12345678901234567890.12345678901234567890123456789012 is not a valid BCD value'.
Ak zamením bodku za čiarku, je to OK.
Niekde som tu videl tému, kde sa rieši nastavenie FormatSettings, aby Delphi bralo od štartu generálne bodku ako oddelovač. Alebo použiť funkciu
TryStrToBcd(AValue, Result, FormatSettings)

Pôvodne ma vystrašila info z odkazu:
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Using_SQLite_with_FireDAC#High-Precision_Numbers

Duvod je na miste. Manual rika, ze pro takto precizni hodnoty mas pouzit datovy typ TEXT a mapovani datovych typu FireDAC. SQLite tak precizni hodnotu do datoveho typu REAL neulozi.

Vtip je v tom, že ten Numeric funguje. Je pravdepodobné, že FireDAC interne prehodí typ na blob, alebo text. Funguje nad tým napríklad aj agregačná funkcia SUM. Ibaže zobrazí double. Zatiaľ mi vkladanie funguje len cez Delphi kód. Nie cez SQL. Napríklad pre Numeric( 26,16 ), sa zdá, že pracuje ako s Float/Double.
Ale Numeric( 64,23 ), založí zrejme pole typu Char(34), aspon taky je datasize. Hlási ho však ako ftFMTBcd.

Jinak hlodá ve mně zvědavost, z jakého měření padají tak přesná čísla, protože i double by uměl uložit vzdálenost ze Země k Alfa Centauri s přesností na 1 cm 10 m :-)

Teraz je to ako hobby. Ale plánujem to rozbehnúť. Už viackrát som narazil na problém s nepresnosťou pri sčítaní veľkého množstva údajov. V rámci rôznych štatistík.
Najmä ak ide o rôzne postupy, tak sa výsledky nezhodovali presne.
V bankách furt mali problém s presnosťou. To som však zažil (ako pozorovateľ), pred rokmi, teraz nie:)
PS: Ak by riadne fungoval Extended aj pod 64 bit kódom, ani by ma nenapadlo riešiť nejaké BCD.
Tie obrovské čísla, čo som prezentoval, išli už len pre zaujímavosť až na hranicu toho, čo zvláda BCD.

[Delfin]

Vtip je v tom, že ten Numeric funguje. Je pravdepodobné, že FireDAC interne prehodí typ na blob, alebo text. Funguje nad tým napríklad aj agregačná funkcia SUM. Ibaže zobrazí double. Zatiaľ mi vkladanie funguje len cez Delphi kód. Nie cez SQL. Napríklad pre Numeric( 26,16 ), sa zdá, že pracuje ako s Float/Double.
Ale Numeric( 64,23 ), založí zrejme pole typu Char(34), aspon taky je datasize. Hlási ho však ako ftFMTBcd.

Vecer mrknu na to co se uvnitr deje. Jdu ted vyrobit kure a pak mam nejaky program

[Miroslav Baláž]

Vecer mrknu na to co se uvnitr deje..

Rád sa dozviem niečo praktické na túto tému. Alebo zo zákulisia..

[Delfin]

Vecer mrknu na to co se uvnitr deje..

Rád sa dozviem niečo praktické na túto tému. Alebo zo zákulisia..

Kure bylo dobre Omlouvam se za zpozdeni. Lehce se to vcera protahlo

Ale k tematu, problem je pravdepodobne ve FireDAC (zatim nevim jaky desetinny separator pouziva SQLite, zda je fixni nebo se jeho nastaveni prebere z operacniho systemu). Tak ci tak, ty do SQLite ukladas text, coz je spravne. FireDAC se pak ridi vychozim datovym typem popsanym SQLite pomoci funkce sqlite3_column_type, coz se deje v metode TSQLiteColumn.InternalGetData.

Pokud ulozis hodnotu s desetinnou teckou, pak SQLite popise datovy typ jako SQLITE_FLOAT. Pokud s desetinnou carkou, pak jako SQLITE_TEXT (tak se to alespon deje na mem stroji s nastavenou desetinnou teckou, zatim nevim zda se tim SQLite ridi).

Problem je v tom, ze pak FireDAC v metode TSQLiteValue.GetData pouzije konverzi na zaklade takto popsaneho datoveho typu (zjednodusene):

Kód: Delphi [Vybrat]

1. etNumber:
2. begin
3.   case iType of // iType je SQLITE_TEXT s desetinnou carkou, SQLITE_FLOAT s desetinnou teckou
4.     SQLITE_TEXT: FDStr2BCD(PChar(pVal), iSize, PBcd(AValue)^, '.'); // pracuje se s teckou, byt by se sloupec s hodnotou pouzivajici tecku popsal jako SQLITE_FLOAT
5.     SQLITE_FLOAT: FDDouble2BCD(PDouble(pVal)^, PBcd(AValue)^); // tady prijdes o preciznost
6.   end;
7. end;

K tomu, aby FireDAC mohl prevest hodnotu datoveho typu popsaneho SQLite jako SQLITE_TEXT na BCD vsak v tomto pripade potrebuje pracovat s desetinnou carkou, ne teckou. Cili tohle by mohla byt rychla oprava (zatim nevim o moznych negativnich nasledcich, avsak po vcerejsi davce vina teprve prozkoumavam svet ):

Kód: Delphi [Vybrat]

1. etNumber:
2. begin
3.   case iType of // iType je SQLITE_TEXT s desetinnou carkou, SQLITE_FLOAT s desetinnou teckou
4.     SQLITE_TEXT: FDStr2BCD(PChar(pVal), iSize, PBcd(AValue)^, ','); // ted se pracuje s carkou jako desetinnym oddelovacem
5.     SQLITE_FLOAT: FDDouble2BCD(PDouble(pVal)^, PBcd(AValue)^);
6.   end;
7. end;

Podivam se na to jakym zpusobem se ridi SQLite pri popisu sloupcu a pripadne napisu bug report a zpetnou vazbu sem.

[Delfin]

Na zaklade zbezneho zkoumani mi prijde ze se SQLite snazi konvertovat textove hodnoty do vhodneho formatu aplikaci affinity funkci applyAffinity jenz interne zkousi prevest textove hodnoty desetinnych cisel pomoci funkce sqlite3AtoF:

Kód: C++ [Vybrat]

1. /* if decimal point is present */
2. if( *z=='.' ){
3. ...
4. }

To by vysvetlovalo proc SQLite popise sloupec s textovou hodnotou cisla s desetinnou teckou jako SQLITE_FLOAT, zatimco s carkou jako SQLITE_TEXT. Pokud je tomu tak a ulozis textovou hodnotu s desetinnou teckou, pak ji SQLite prevede a ulozi jako SQLITE_FLOAT. Pokud s carkou, jako SQLITE_TEXT.

[Delfin]

Mohl by sis vsak vytvorit vlastni datovy typ, jenz bude mit affinity TEXT, treba FLOATTEXT a pro nej vytvorit mapovani (kdybys pouzil mapovani primo na TEXT, mohlo by dojit ke kolizi hodnot jenz mely byt ulozeny jako text). Pak muzes pouzit desetinnou tecku, protoze se SQLite nebude snazit o konverzi do SQLITE_FLOAT a FireDAC se stavajicim kodem dokaze prevest SQLITE_TEXT s desetinnou teckou. Tedy napr.:

Kód: Delphi [Vybrat]

1. var
2.   Value: TBcd;
3.   MapRule: TFDMapRule;
4. begin
5.   FDQuery1.FormatOptions.OwnMapRules := True;
6.  
7.   MapRule := FDQuery1.FormatOptions.MapRules.Add;
8.   MapRule.TypeMask := 'FLOATTEXT';
9.   MapRule.TargetDataType := dtFmtBCD;
10.  
11.   FDQuery1.SQL.Text :=
12.     'DROP TABLE IF EXISTS MyTable;' +
13.     'CREATE TABLE MyTable(TheValue FLOATTEXT);' +
14.     'INSERT INTO MyTable (TheValue) VALUES (''-12345678901234567890.12345678901234567890123456789012345678901'');' +
15.     'SELECT TheValue FROM MyTable;';
16.   FDQuery1.Open;
17.  
18.   Value := FDQuery1.Fields[0].AsBCD;
19.   ...
20. end;

Me se totiz moc nechce FireDAC nutit aby zacal konvertovat textove hodnoty s desetinnou carkou. SQLite pouziva tecku a asi bych to tak i nechal. Cili s vyse uvedenym se provede konverze pomoci:

Kód: Delphi [Vybrat]

1. SQLITE_TEXT: FDStr2BCD(PChar(pVal), iSize, PBcd(AValue)^, '.');

[Delfin]

Jinak ve zkratce k cemu doslo. Tim ze jsi vytvoril sloupec typu NUMERIC jsi naznacil SQLite affinity typu NUMERIC. Pri ulozeni hodnoty s desetinnou carkou ji pak SQLite ulozil jako TEXT (pokud bys pouzil tecku, ulozil by SQLite hodnotu jako NUMERIC, cimz bys ztratil presnost).

Vytvorenim sloupce s affinity TEXT docilis toho ze se SQLite prestane snazit o ukladani hodnot s desetinnou teckou jako NUMERIC a zaroven umoznis FireDAC ji korektne prevest na BCD.

[Miroslav Baláž]

Super toto funguje:

Mohl by sis vsak vytvorit vlastni datovy typ, jenz bude mit affinity TEXT, treba FLOATTEXT a pro nej vytvorit mapovani (kdybys pouzil mapovani primo na TEXT, mohlo by dojit ke kolizi hodnot jenz mely byt ulozeny jako text). Pak muzes pouzit desetinnou tecku, protoze se SQLite nebude snazit o konverzi do SQLITE_FLOAT a FireDAC se stavajicim kodem dokaze prevest SQLITE_TEXT s desetinnou teckou. Tedy napr.:

Kód: Delphi [Vybrat]

1. var
2.   Value: TBcd;
3.   MapRule: TFDMapRule;
4. begin
5.   FDQuery1.FormatOptions.OwnMapRules := True;
6.  
7.   MapRule := FDQuery1.FormatOptions.MapRules.Add;
8.   MapRule.TypeMask := 'FLOATTEXT';
9.   MapRule.TargetDataType := dtFmtBCD;
10.  
11.   FDQuery1.SQL.Text :=
12.     'DROP TABLE IF EXISTS MyTable;' +
13.     'CREATE TABLE MyTable(TheValue FLOATTEXT);' +
14.     'INSERT INTO MyTable (TheValue) VALUES (''-12345678901234567890.12345678901234567890123456789012345678901'');' +
15.     'SELECT TheValue FROM MyTable;';
16.   FDQuery1.Open;
17.  
18.   Value := FDQuery1.Fields[0].AsBCD;
19.   ...
20. end;

..

Samozrejme potrebujem k tomu vlastné Custom funkcie, pretože so stringami SQLite matiku neovláda.
Tu je príklad riešenia.
Ako vidieť dá desatinná čiarka tam furt otravuje. Ale funkcia funguje:

Kód: Delphi [Vybrat]

1. // Inicializácia funkcie:
2. FDSQLiteFunction1.DriverLink := FDPhysSQLiteDriverLink1;
3. FDSQLiteFunction1.FunctionName := 'Rozdiel';
4. FDSQLiteFunction1.ArgumentsCount := 2;
5. FDSQLiteFunction1.OnCalculate := FDSQLiteFunction1Calculate;
6. FDSQLiteFunction1.Active := True;
7.  
8. // Event:
9. procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance;  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
10.  
11.   function custStrToBcd( const s: String ): TBCD;
12.     var
13.       p: PChar;
14.     begin
15.     p        := StrScan( PChar( s ), '.' );
16.     if p <> nil then
17.       begin
18.       p^     := ',';
19.       result := StrToBcd( s );
20.       p^     := '.';
21.       end
22.     else
23.       result := StrToBcd( s );
24.     end;
25. begin
26.   AOutput.AsString := BCDToStr( custStrToBcd( AInputs[ 0 ].AsString ) - custStrToBcd( AInputs[ 1 ].AsString ) );
27. end;
28.  

Príslušný SQL:

Kód: MySQL [Vybrat]

1. DROP TABLE IF EXISTS MyTable;
2. CREATE TABLE MyTable(TheValue FLOATTEXT);
3. INSERT INTO MyTable (TheValue) VALUES ( '-12345678901234567890.12345678901234567890123456789012345678901' );
4. SELECT TheValue, Rozdiel( TheValue, '1' ) AS [Odčítané::Text(64)] FROM MyTable;

Výsledok je korektný. Nula pred desatinnou bodkou sa zamení za jedničku. ( prvý vstup je záporné číslo, druhý kladný, preto odčítanie, aby jednička pribudla)

[Delfin]

Samozrejme potrebujem k tomu vlastné Custom funkcie, pretože so stringami SQLite matiku neovláda.

Ano. Jinak to resit nelze. Pokud bys misto TEXT pouzil NUMERIC, ztratil bys v SQLite presnost. Jinak muzes pouzit:

Kód: MySQL [Vybrat]

1. SELECT TheValue, Rozdiel(TheValue, 1) FROM MyTable;

A funkci upravit na:

Kód: Delphi [Vybrat]

1. procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunctionInstance; AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
2. begin
3.   AOutput.AsNumber := AInputs[0].AsNumber - AInputs[1].AsNumber;
4. end;

[Miroslav Baláž]

AsNumber z toho spraví double a .. som v háji:)
Bolo by to celé zbytočné. Alebo som niečo prehliadol?
Bohužiaľ Custom funkcie nepodporujú cast AsBCD.

Inak, pozitívne je, že Custom funkcie SQLite databázu extra nespomaľujú.
Naopak ak funkcia dokáže nahradiť zložitejšiu SQL konštrukciu, tak výpočet sa až násobne zrýchli.
Zopár takých funkcií už mám.

[Delfin]

AsNumber z toho spraví double a .. som v háji:)
Bolo by to celé zbytočné. Alebo som niečo prehliadol?
Bohužiaľ custom funkcie nepodporujú cast AsBCD 

Podporuji. Property AsNumber je typu TBcd (s AsFloat bys o ni prisel).

Inak, pozitívne je, že Custom funkcie SQLite databázu extra nespomaľujú.

FireDAC vytvori UDF pomoci API funkce sqlite3_create_function jiz preda ukazatele na callback funkce. Ty pak SQLite vola kdy je treba. Je tam samozrejme nejaky zpomalujici obal kdy se vyvolava udalost v Delphi, ten vsak verim ze je zanedbatelny.