Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...
> -Original Message- > Behalf Of J Decker ... > wrote: > > > Dave. The documentation contains many such catch-all > statements which do ... > > The current decision tree of the particular catch-all > documentation comment ... > > int sqlite3ValueBytes(sqlite3_value *pVal, u8 enc){ ... > it's valueBytes that has the biggest impact > ... > sqlite3_value_bytes >- invalidates sqlite3_value_text16*() result (always) > > sqlite3_value_bytes16 > - invalidates sqlite3_value_text() result (always) > - invalidates sqlite3_value_text16*() result if the format > does not match > defined SQLITE_UTF16NATIVE > ... Now I can see it! Thanks, guys - Clemens, Peter, d3ck0r - for your perspicacious insights! I am slightly aglow with the enlightenment; I think I'll unplug the Xmas tree for the rest of the evening, and bask instead in the illumnation your have so kindly imbued upon me. I think there will be enough light for me to do some reading of vdbemem.c! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...
On Wed, Dec 13, 2017 at 1:25 PM, petern wrote: > Dave. The documentation contains many such catch-all statements which do > not reflect a full decision tree. The usual cover story will either be (I > paraphrase) : 1. "that's an implementation detail" or 2. "it might change > later, so the documentation can only make a short blanket statement". > > It is far more likely that spelling and grammatical errors you report of > the documentation will be corrected. > > The current decision tree of the particular catch-all documentation comment > you found is in vdbemem.c at the comment and function body listing below. > Ultimately there is only the source code. Getting used to reading it for > yourself will probably save you a lot of time. > > int sqlite3ValueBytes(sqlite3_value *pVal, u8 enc){ > Mem *p = (Mem*)pVal; > assert( (p->flags & MEM_Null)==0 || (p->flags & (MEM_Str|MEM_Blob))==0 ); > if( (p->flags & MEM_Str)!=0 && pVal->enc==enc ){ > return p->n; > } > if( (p->flags & MEM_Blob)!=0 ){ > if( p->flags & MEM_Zero ){ > return p->n + p->u.nZero; > }else{ > return p->n; > } > } > if( p->flags & MEM_Null ) return 0; > return valueBytes(pVal, enc); > it's valueBytes that has the biggest impact sqlite3_value_blob sqlite3_value_text sqlite3_value_text16 sqlite3_value_text16be sqlite3_value_text16le sqlite3_value_bytes - invalidates sqlite3_value_text16*() result (always) sqlite3_value_bytes16 - invalidates sqlite3_value_text() result (always) - invalidates sqlite3_value_text16*() result if the format does not match defined SQLITE_UTF16NATIVE if the value type is a blob, sqlite3_value_bytes() and sqlite3_value_bytes16() will do no conversion, and will not invalidate blob result. for both; if the value was NULL there is no invalidation; and 0 will be returned if sqlite3_value_blob is used and the original value type is NOT a blob, it returns sqlite3_value_text(); so bytes16 will invalidate the result. (rephrased) sqlite3_value_blob() on a column that is not a blob has the potential of being invalidated by bytes16(). } > > Peter > > On Wed, Dec 13, 2017 at 8:38 AM, dave wrote: > > > I have a question regarding the API documention at > > http://sqlite.org/c3ref/value_blob.html, which states: > > "... the pointer returned from sqlite3_value_blob(), .. can be > invalidated > > by a subsequent call to sqlite3_value_bytes(), ..." > > Is that statement still true? I ask because I notice that the source of > > many of the extensions in 'sqlite/ext' seem to violate that advice. > > > > I first noticed this when I was recently working on fileio.c (e.g. line > 73 > > vs 77), but grepping through the source I find many other cases where the > > pointer is retrieved via *_blob() or *.text() BEFORE invoking > > sqlite3_value_bytes(). E.g these source and line numbers: > > fts2_tokenizer.c:71, 72 > > fts3_expr.c:1248, 1249 > > fts3_tokenizer.c:78, 79 > > fts3_tokenize_vtab.c:347, 348 > > fts3_write.c:5290, 5291 > > fts5_index.c:6270, 6271 > > fts5_storage.c:735, 736 > > fts5_tcl.c:547 > > fts5_test_tok.c:375, 376 > > fts5_vocab.c:607, 608; 612, 613; 616, 617 > > (I stopped grepping at this point; this list is not comprehensive). > > > > Anyway, just wondered if the api documentation's advice is maybe > > out-of-date > > with current reality. Thoughts/comments? > > > > Cheers! > > -dave > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On Wednesday, 13 December, 2017 13:35, Lifepillar wrote: >I am implementing an extension for manipulating IEEE754 decimal >numbers. Numbers are stored as blobs using a standard encoding. >Numbers that are mathematically equal may have different >representations, (e.g., 1.0 may have mantissa 10 and exponent -1 >while 1.00 may have mantissa 100 and exponent -2). You have stated something that is impossible, or at least self-contradictory. Unless, of course, you are talking about the "decimal" formats of IEEE754-2008 and not the standard (far more common) "binary" formats. You cannot have an IEEE754 (binary) number stored in denormalized format *except* in the circumstance where the exponent indicates that it is a denormalized number. There are only two valid exponents to indicate that the number is denormalized, and all denormalized numbers are only used to represent numbers between +/- (0 and epsilon). All other numbers stored in IEEE754 floating point format are required, by the standard, to be normalized. (That is, where the MSB is 1 and that 1 is not stored as part of the significand). So in order for the numbers to be IEEE754 floating point, the number "1.0" (no matter the number of trailing 0's you choose to display) must always be stored with a mantissa of 0.5 and an exponent of 1. Although a mantissa of 0.25 with exponent 2 evaluates also to the number 1.0, in IEEE754 format it must always have a mantissa of 0.5 and exponent of 1. Note the above two paragraphs only apply to binary IEE754-2008 numbers. These are the only kind of "floating point" presently understood by SQLite3. However, if you are talking about the "decimal" IEEE754 then you can indeed have different representations of the same "value". Some "values" can have about 800 different representations of the same value. (Note that the solution below would work even if the blobs were arbitrary precision IBM GDAS floating point numbers, or any other kind of floating point number, pretty much). To answer your question however, I would recommend that you consider writing a function that return the "value" in a supported format. My personal recommendation would be IEEE754-2008 binary64 (that is, the standard double precision floating point format supported by SQLite3). You would tag this function as being CONSTANT/DETERMINISTIC. You could then create an index on the result. CREATE INDEX decimal64blob_to_binary64 ON MyTable (ConvertDecimal64toBinary64(binary64blob_field)); and when you search the index ala: SELECT * FROM MyTable where ConvertDecimal64toBinary64(binary64blob_field) between 47.0 and 47.1; you will use the index (I believe). And you need not specify conversion to string format nor deal with the vagaries of strings. You just have to deal with the standard binary floating point limitations. On the other hand however if you do NOT need binary64 at all, then there was a minor change discussed a while back by someone else where you can "change" the default floating-point number format from binary64 to decimal64 and then compile your own custom version of SQLite3 ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...
Dave. The documentation contains many such catch-all statements which do not reflect a full decision tree. The usual cover story will either be (I paraphrase) : 1. "that's an implementation detail" or 2. "it might change later, so the documentation can only make a short blanket statement". It is far more likely that spelling and grammatical errors you report of the documentation will be corrected. The current decision tree of the particular catch-all documentation comment you found is in vdbemem.c at the comment and function body listing below. Ultimately there is only the source code. Getting used to reading it for yourself will probably save you a lot of time. int sqlite3ValueBytes(sqlite3_value *pVal, u8 enc){ Mem *p = (Mem*)pVal; assert( (p->flags & MEM_Null)==0 || (p->flags & (MEM_Str|MEM_Blob))==0 ); if( (p->flags & MEM_Str)!=0 && pVal->enc==enc ){ return p->n; } if( (p->flags & MEM_Blob)!=0 ){ if( p->flags & MEM_Zero ){ return p->n + p->u.nZero; }else{ return p->n; } } if( p->flags & MEM_Null ) return 0; return valueBytes(pVal, enc); } Peter On Wed, Dec 13, 2017 at 8:38 AM, dave wrote: > I have a question regarding the API documention at > http://sqlite.org/c3ref/value_blob.html, which states: > "... the pointer returned from sqlite3_value_blob(), .. can be invalidated > by a subsequent call to sqlite3_value_bytes(), ..." > Is that statement still true? I ask because I notice that the source of > many of the extensions in 'sqlite/ext' seem to violate that advice. > > I first noticed this when I was recently working on fileio.c (e.g. line 73 > vs 77), but grepping through the source I find many other cases where the > pointer is retrieved via *_blob() or *.text() BEFORE invoking > sqlite3_value_bytes(). E.g these source and line numbers: > fts2_tokenizer.c:71, 72 > fts3_expr.c:1248, 1249 > fts3_tokenizer.c:78, 79 > fts3_tokenize_vtab.c:347, 348 > fts3_write.c:5290, 5291 > fts5_index.c:6270, 6271 > fts5_storage.c:735, 736 > fts5_tcl.c:547 > fts5_test_tok.c:375, 376 > fts5_vocab.c:607, 608; 612, 613; 616, 617 > (I stopped grepping at this point; this list is not comprehensive). > > Anyway, just wondered if the api documentation's advice is maybe > out-of-date > with current reality. Thoughts/comments? > > Cheers! > -dave > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 13 Dec 2017, at 8:34pm, Lifepillar wrote: > But, (correct me if > I am wrong), if I index the blob column directly, comparisons are > based on memcpy(), which in my case is not what I want. Is it > possible to create an index that somehow uses a custom comparison > function instead? E.g., I have a deccmp(x,y) function that returns > -1 if xy. Can I define an index based on > that? As Dr H wrote, it can’t be done. Either store a normalised (numeric) version of the number, or store both the BLOB and a normalised version. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 12/13/17, Lifepillar wrote: > > if I index the blob column directly, comparisons are > based on memcpy(), which in my case is not what I want. Is it > possible to create an index that somehow uses a custom comparison > function instead? No. SQLite always uses memcmp() to compare BLOBs. You can add a collating function for strings, but not for BLOBs. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to index data based on custom comparisons?
I am implementing an extension for manipulating IEEE754 decimal numbers. Numbers are stored as blobs using a standard encoding. Numbers that are mathematically equal may have different representations, (e.g., 1.0 may have mantissa 10 and exponent -1 while 1.00 may have mantissa 100 and exponent -2). Since I am going to perform point and range queries on decimal columns, I'd like to have them indexed. So far, I have been able to create an expression index based on a decstr() function that converts a decimal into a string, which I can use in queries like the following: select decstr(d) from T where decstr(d) = '1.2345'; (Btw, if I use `like`, as in `decstr(d) like '1.2%'`, the index is not used. Does it depend on my data, or can't the optimizer use an index with a pattern matching condition?) Anyway, string-based comparisons are limited. But, (correct me if I am wrong), if I index the blob column directly, comparisons are based on memcpy(), which in my case is not what I want. Is it possible to create an index that somehow uses a custom comparison function instead? E.g., I have a deccmp(x,y) function that returns -1 if xy. Can I define an index based on that? Thanks, Life. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...
dave wrote: > "... the pointer returned from sqlite3_value_blob(), .. can be invalidated > by a subsequent call to sqlite3_value_bytes(), ..." > Is that statement still true? The returned pointer is invalidated when you call a function that forces SQLite to convert the value into another type. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime kind stored as undefined
On 13 Dec 2017, at 11:51am, Michał Niegrzybowski wrote: > I have a table which has a column of type DateTime in my code I insert > there an actual UTC Date (which is not the same as my local time). When I > want to gather previously added record, my record contains date in his > DateTime column, but this DateTime is a localtime with kind specified to > 'undefined' instead of 'UTC'. Can you tell us the datatype of that column ? If you want to post the CREATE TABLE command, that’s fine too. Can you use the SQLite shell tool to look at the values stored in that field without System.Data.SqLite getting in the way ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DateTime kind stored as undefined
Hi All, I encounter a problem with inserting a DateTime.UtcNow to a database, after upgrade from System.Data.SqLite.x64 ver. 1.0.76 to System.Data.SqLite.Core ver. 1.0.106. I have a table which has a column of type DateTime in my code I insert there an actual UTC Date (which is not the same as my local time). When I want to gather previously added record, my record contains date in his DateTime column, but this DateTime is a localtime with kind specified to 'undefined' instead of 'UTC'. My codebase is in C#. I try to specify DateTimeKind in connection string or DateTimeFormat as ticks but they aren't works. Is this a bug in SqLite(I suppose yes), or maybe I have to do something extra to make it works? Best Regards, Michał Niegrzybowski ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seasonal syntax
On Dec 12, 2017, at 10:24 AM, Simon Slavin wrote: > > Santa Clause: SELECT name,hobbies,address FROM people WHERE > behaviour=‘nice’ I think you mean SELECT name,address CASE behaviour WHEN ‘nice' THEN hobbies ELSE 'coal' END FROM people ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] API, sqlite3_value_bytes and sqlite3_value_blob...
I have a question regarding the API documention at http://sqlite.org/c3ref/value_blob.html, which states: "... the pointer returned from sqlite3_value_blob(), .. can be invalidated by a subsequent call to sqlite3_value_bytes(), ..." Is that statement still true? I ask because I notice that the source of many of the extensions in 'sqlite/ext' seem to violate that advice. I first noticed this when I was recently working on fileio.c (e.g. line 73 vs 77), but grepping through the source I find many other cases where the pointer is retrieved via *_blob() or *.text() BEFORE invoking sqlite3_value_bytes(). E.g these source and line numbers: fts2_tokenizer.c:71, 72 fts3_expr.c:1248, 1249 fts3_tokenizer.c:78, 79 fts3_tokenize_vtab.c:347, 348 fts3_write.c:5290, 5291 fts5_index.c:6270, 6271 fts5_storage.c:735, 736 fts5_tcl.c:547 fts5_test_tok.c:375, 376 fts5_vocab.c:607, 608; 612, 613; 616, 617 (I stopped grepping at this point; this list is not comprehensive). Anyway, just wondered if the api documentation's advice is maybe out-of-date with current reality. Thoughts/comments? Cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] 2D query
select group_concat(members,'. ')||'.' from (select sex||': '||group_concat(name) as members from people group by sex); group_concat(members,'. ')||'.' --- F: Alex,Jane. M: Alex,John. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Valentin Davydov Gesendet: Mittwoch, 13. Dezember 2017 14:57 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] 2D query Hi, all! Given the following table: CREATE TABLE people(name,sex); INSERT INTO people VALUES("Alex","F"); INSERT INTO people VALUES("Alex","M"); INSERT INTO people VALUES("Jane","F"); INSERT INTO people VALUES("John","M"); How to construct a query which returns coalesced sex but individual names, such as "F: Alex, Jane. M: Alex, John."? Sincerely, Valentin Davydov. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2D query
On 12/13/17, Valentin Davydov wrote: > Given the following table: > > CREATE TABLE people(name,sex); > INSERT INTO people VALUES("Alex","F"); > INSERT INTO people VALUES("Alex","M"); > INSERT INTO people VALUES("Jane","F"); > INSERT INTO people VALUES("John","M"); > > How to construct a query which returns coalesced sex but individual names, > such as "F: Alex, Jane. M: Alex, John."? SELECT sex || ': ' || group_concat(name, ", ") FROM people GROUP BY sex; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2D query
select sex, group_concat(name, ', ') from people group by sex; (And don't forget to use single quotes for string literals) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Valentin Davydov Sent: Wednesday, December 13, 2017 8:57 AM To: SQLite mailing list Subject: [sqlite] 2D query Hi, all! Given the following table: CREATE TABLE people(name,sex); INSERT INTO people VALUES("Alex","F"); INSERT INTO people VALUES("Alex","M"); INSERT INTO people VALUES("Jane","F"); INSERT INTO people VALUES("John","M"); How to construct a query which returns coalesced sex but individual names, such as "F: Alex, Jane. M: Alex, John."? Sincerely, Valentin Davydov. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 2D query
Hi, all! Given the following table: CREATE TABLE people(name,sex); INSERT INTO people VALUES("Alex","F"); INSERT INTO people VALUES("Alex","M"); INSERT INTO people VALUES("Jane","F"); INSERT INTO people VALUES("John","M"); How to construct a query which returns coalesced sex but individual names, such as "F: Alex, Jane. M: Alex, John."? Sincerely, Valentin Davydov. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite have official development testing tool?
On 12/13/17, advancenOO wrote: > I am interested in the source code of sqlite and I want to make some change > to it. > I wondering if sqlite has official development testing to do a full > functional check or evaluate performance lose of my code? > > I have found sqlspeedtest1,8 and sqlthreadtest3,4. Is there any other > testing I should make? https://www.sqlite.org/testing.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does sqlite have official development testing tool?
I am interested in the source code of sqlite and I want to make some change to it. I wondering if sqlite has official development testing to do a full functional check or evaluate performance lose of my code? I have found sqlspeedtest1,8 and sqlthreadtest3,4. Is there any other testing I should make? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users