sqlite3_exec without ubiqitous text conversions

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_exec without ubiqitous text conversions

Barone Ashura
Hello,

I have been working on an application based on SQLite for 2 years now, and
recently we started running some performance profiling to check if there
are areas where we can squeeze some extra performance.

SQlite query execution is used almost exclusively through sqlite3_exec, and
the implementation of callbacks.

One of the areas of interest we identified is that every column is always
passed to the callback as a c-char-string. Then we need to convert that
string back to the intended data type. Apparently a consistent amount of
time is spent doing this.

I started looking into SQLite code and realized that int and real values
retrieved from database, is actually fecthed as binary value, then
converted to string, passed to the callback, and in the callback we convert
it back to its original data type (as we keep track of 'true' datatype for
each column).

I decided to try skipping these conversions, by creating a custom
implementation of sqlite3_exec, which does the following in the inside
'step' loop:

                if (rc == SQLITE_ROW) {
                    azVals = azCols;
                    for (i = 0; i < nCol; i++) {
                        azVals[i] = (char *)sqlite3_column_text(pStmt, i);
                        int col_type = sqlite3_column_type(pStmt, i);
                        switch (col_type)
                        {
                            case SQLITE_INTEGER:
                            case SQLITE_FLOAT:
                            {
                                azVals[i] = (char*)columnMem(pStmt, i);
                                columnMallocFailure(pStmt);
                                break;
                            }
                            default:
                            {
                                azVals[i] = (char
*)sqlite3_column_text(pStmt, i);
                                break;
                            }
                        }
                        if (!azVals[i] && sqlite3_column_type(pStmt, i) !=
SQLITE_NULL) {
                            sqlite3OomFault(db);
                            goto exec_out;
                        }
                    }
                }

Instead of ALWAYS converting to text, in case of INTEGER or FLOAT columns
types, I fetch the address of the data  through columnMem, put it in the
azVals array, and go on. The callback knows which is the expected data type
for the column, reads and copies data in the destination variable through a
simple assignment.

I am aware that forcing a 'typed' pointer into a generic char* pointer
(azVals[x]), rings a looooot of alarm bells; I am, as well, aware that I
could avoid sqlite3_exec, and call sqlite3_step myself.
As of now the application is running smoothly and faster as far as satabase
access is concerned.

I am writing here to get opinions about other potential pitfalls or
oversights in this approach, as well as reason why I should not proceed on
this path.

Thanks in advance
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Hick Gunter
What you are doing is a very bad idea indeed.

a) you are circumventing the intended interface
b) you are breaking encapsulation, because columnMem returns a pointer to an internal type, which is useless to you, unless you have made public all the SQLite internals
c) you are assuming that type conversion of a Mem type will conserve the original fields
d) you are duplicating work by calling sqlite3_column_text() twice, unless the type is numerical
e) you are duplicating work by calling sqlite3_column_type() twice, instead of using the value returned from the first call
f) you are using the result of a function call that is documented to be undefined in the exact context you are using it in

IMHO, you would be much better off attempting to master the official SQLite API.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 13:14
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] sqlite3_exec without ubiqitous text conversions

Hello,

I have been working on an application based on SQLite for 2 years now, and recently we started running some performance profiling to check if there are areas where we can squeeze some extra performance.

SQlite query execution is used almost exclusively through sqlite3_exec, and the implementation of callbacks.

One of the areas of interest we identified is that every column is always passed to the callback as a c-char-string. Then we need to convert that string back to the intended data type. Apparently a consistent amount of time is spent doing this.

I started looking into SQLite code and realized that int and real values retrieved from database, is actually fecthed as binary value, then converted to string, passed to the callback, and in the callback we convert it back to its original data type (as we keep track of 'true' datatype for each column).

I decided to try skipping these conversions, by creating a custom implementation of sqlite3_exec, which does the following in the inside 'step' loop:

                if (rc == SQLITE_ROW) {
                    azVals = azCols;
                    for (i = 0; i < nCol; i++) {
                        azVals[i] = (char *)sqlite3_column_text(pStmt, i);
                        int col_type = sqlite3_column_type(pStmt, i);
                        switch (col_type)
                        {
                            case SQLITE_INTEGER:
                            case SQLITE_FLOAT:
                            {
                                azVals[i] = (char*)columnMem(pStmt, i);
                                columnMallocFailure(pStmt);
                                break;
                            }
                            default:
                            {
                                azVals[i] = (char *)sqlite3_column_text(pStmt, i);
                                break;
                            }
                        }
                        if (!azVals[i] && sqlite3_column_type(pStmt, i) !=
SQLITE_NULL) {
                            sqlite3OomFault(db);
                            goto exec_out;
                        }
                    }
                }

Instead of ALWAYS converting to text, in case of INTEGER or FLOAT columns types, I fetch the address of the data  through columnMem, put it in the azVals array, and go on. The callback knows which is the expected data type for the column, reads and copies data in the destination variable through a simple assignment.

I am aware that forcing a 'typed' pointer into a generic char* pointer (azVals[x]), rings a looooot of alarm bells; I am, as well, aware that I could avoid sqlite3_exec, and call sqlite3_step myself.
As of now the application is running smoothly and faster as far as satabase access is concerned.

I am writing here to get opinions about other potential pitfalls or oversights in this approach, as well as reason why I should not proceed on this path.

Thanks in advance
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Barone Ashura
Thanks for the response
you gave me the kind of answer I was looking for.


a) you are circumventing the intended interface
>

I know, I am aware, and that is exactly what I wanted to do; I Wrote to the
mailing list to decide if I will really do it or not


> b) you are breaking encapsulation, because columnMem returns a pointer to
> an internal type, which is useless to you, unless you have made public all
> the SQLite internals
>

The change is not indended for distribution, it is going to be a change in
our codebase. The Internal type goes down to a struct having a union at its
base address, such union contains either a double or a int64 value, for
REAL and INTEGER values respectively (unless I wrongly assume that the
internal representation of Mem pointer, is, under specific circumstances
(which I havent yet stumpled upon in testing), inconstistent with the type
reported by sqlite3_column_type. This means that I have no need to make all
SQLite internals public. As I said I already keep track of the base type of
each column in my sqlite database; in all the testing performed, I never
failed to identify the correct datatype for the pointer. I cannot exclude
that there are situation where I will fail, and that is why I posted here.

c) you are assuming that type conversion of a Mem type will conserve the
> original fields
>

This is the real 'unknown' to me issue, could you please elaborate a little
more? Which are the original fields you are referring to? Which type
conversions of a mem type are you referring to?


> d) you are duplicating work by calling sqlite3_column_text() twice, unless
> the type is numerical
> e) you are duplicating work by calling sqlite3_column_type() twice,
> instead of using the value returned from the first call
>

True, will be fixed... I focused too much on trying out the consistency of
the change and the lack of regressions.


> f) you are using the result of a function call that is documented to be
> undefined in the exact context you are using it in
>

 Which is the function you are referring to? which context are you
referring to?


> IMHO, you would be much better off attempting to master the official
> SQLite API.
>

Opinion acknowledged and taken in serious consideration, Nothing (except
eventual development time constraints) prevents me from embracing your
advice.

Thanks again
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Hick Gunter
f) There are exactly 2 documented functions in your code. Did you not read their documentation???

See https://sqlite.org/c3ref/column_blob.html

" After a type conversion, the result of calling sqlite3_column_type() is undefined, though harmless. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion."

b) Breaking encapsulation includes referencing internal .h files in your own code. You should be using sqlite3.h *only*

You do realise that declared types are not enforced in SQLite? If somehow a real with a non-integer value got inserted into an integer field, the type of the returned value would still be real, but you would be expecting integer. And silently reading the bits of the double value as an integer, which would probalby cause a practically untetectable error.

c) There is no guarantee that the internal fields referring to a previous state of a Mem structure are preserved across conversions.

I do hope you are statically linking your modified SQLite code to your application and not installing it as a shared image. In the latter case, some unsuspecting application might inadvertently stumble across your version of the interface, which no longer conforms to the published interface, and cause failures there. That would probably cause some really rave reviews. "I just installed XXX on my phone and it died" is not a viable recommendation.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 14:51
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Thanks for the response
you gave me the kind of answer I was looking for.


a) you are circumventing the intended interface
>

I know, I am aware, and that is exactly what I wanted to do; I Wrote to the mailing list to decide if I will really do it or not


> b) you are breaking encapsulation, because columnMem returns a pointer
> to an internal type, which is useless to you, unless you have made
> public all the SQLite internals
>

The change is not indended for distribution, it is going to be a change in our codebase. The Internal type goes down to a struct having a union at its base address, such union contains either a double or a int64 value, for REAL and INTEGER values respectively (unless I wrongly assume that the internal representation of Mem pointer, is, under specific circumstances (which I havent yet stumpled upon in testing), inconstistent with the type reported by sqlite3_column_type. This means that I have no need to make all SQLite internals public. As I said I already keep track of the base type of each column in my sqlite database; in all the testing performed, I never failed to identify the correct datatype for the pointer. I cannot exclude that there are situation where I will fail, and that is why I posted here.

c) you are assuming that type conversion of a Mem type will conserve the
> original fields
>

This is the real 'unknown' to me issue, could you please elaborate a little more? Which are the original fields you are referring to? Which type conversions of a mem type are you referring to?


> d) you are duplicating work by calling sqlite3_column_text() twice,
> unless the type is numerical
> e) you are duplicating work by calling sqlite3_column_type() twice,
> instead of using the value returned from the first call
>

True, will be fixed... I focused too much on trying out the consistency of the change and the lack of regressions.


> f) you are using the result of a function call that is documented to
> be undefined in the exact context you are using it in
>

 Which is the function you are referring to? which context are you referring to?


> IMHO, you would be much better off attempting to master the official
> SQLite API.
>

Opinion acknowledged and taken in serious consideration, Nothing (except eventual development time constraints) prevents me from embracing your advice.

Thanks again
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Barone Ashura
I really wish to keep the argument polite and constructive, so please dont
get me wrong if I reply, I just want to understand what you are referring
to, realize and evaluate which is the best way to go for me, not for the
sqlite library, that's why I writing to the sqlite library.

Il giorno mar 30 lug 2019 alle ore 15:50 Hick Gunter <[hidden email]> ha
scritto:

> f) There are exactly 2 documented functions in your code. Did you not read
> their documentation???
>
> See https://sqlite.org/c3ref/column_blob.html
>
> " After a type conversion, the result of calling sqlite3_column_type() is
> undefined, though harmless. Future versions of SQLite may change the
> behavior of sqlite3_column_type() following a type conversion."
>

which type conversion are you referring to? the second one erroneously
called in the example code? I do understand the 'type conversion' to be one
of the six functions listed in the page you linked (which was read).
The statement before the one you quoted specifically says:  "The return
value of sqlite3_column_type() can be used to decide which of the first six
interface should be used to extract the column value". Isnt this exactly
what I want to be doing? Read the column type from the statement, according
to the return value call the relevant extraction function. The
documentation describes 'automatic conversions' being performed if I am
trying to extract a datatype that is different from the internal datatype.
But this is exactly what I want to stay away from, unless, of course I am
missing something, which I would very like understand.


> b) Breaking encapsulation includes referencing internal .h files in your
> own code. You should be using sqlite3.h *only*
>

I am not referencing ANY internal.h file in my own code. I am just using
the amalgamation distribution, and I am writing code in sqlite.c, not in my
own source files, so nobody outside of sqlite.c calls any function or uses
any datatype that is not declared in sqlite.h.



> You do realise that declared types are not enforced in SQLite? If somehow
> a real with a non-integer value got inserted into an integer field, the
> type of the returned value would still be real, but you would be expecting
> integer. And silently reading the bits of the double value as an integer,
> which would probalby cause a practically untetectable error.
>

True, agreed. This is the reason why the types of each value being inserted
into, updated and read from the database is strictly enforced outside of
sqlite. To state in a cear way: SQLite might not enforce declared types, by
the application around sqlite has been specifically designed to enforce
different types.


>
> c) There is no guarantee that the internal fields referring to a previous
> state of a Mem structure are preserved across conversions.
>

why do you refer to a "previous state of a mem structure"? the code is
being executed inside a custom sqlite_exec function, which mirrors the
behaviour of the provided sqlite_exec function, except for the conversions
to/from text (which return the pointer to the internal data type, without
any conversion in case of Integer and float value), WAY before calling the
statement finalization. The callback itself that must be provided to
sql3_exec, is called before finalizing and freeing the memory allocated by
the statement execution.


> I do hope you are statically linking your modified SQLite code to your
> application and not installing it as a shared image. In the latter case,
> some unsuspecting application might inadvertently stumble across your
> version of the interface, which no longer conforms to the published
> interface, and cause failures there. That would probably cause some really
> rave reviews. "I just installed XXX on my phone and it died" is not a
> viable recommendation.
>

Again good point, but we are 'lucky' here as well... sqlite amalgamation is
compiled into the application and not dinamically linked.

I know that what I am doing is hazardous, rings alarm bells, 'dirty' (if
you want to call it dirty), I know that it could be done in several
different ways, it's just that sqlite3_exec does already an excellent job
in wrapping prapared statemets, stepping and finalizing the statement
itself; it does return an array of stringified column values, and that's
what I would like to avoid (in the case of integers and floats).

Looking forward to reading more insights, hints, tips, suggestions,
arguments, advice, whatever.

Thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_exec without ubiqitous text conversions

Bernardino Ramos
In reply to this post by Barone Ashura
Hi!

I use a simple trick:

A clone of the sqlite3_exec that passes the sqlite3_stmt as an argument
to the callback function.

So no conversion is made and we can use the sqlite3_column... functions
directly on the retrieved row.

Happy coding!


On 2019-07-30 05:00, [hidden email] wrote:

> Send sqlite-users mailing list submissions to
> [hidden email]
>
>    6. sqlite3_exec without ubiqitous text conversions (Barone Ashura)
>
>
> ------------------------------
>
> Message: 6
> Date: Tue, 30 Jul 2019 13:13:52 +0200
> From: Barone Ashura <[hidden email]>
> To: [hidden email]
> Subject: [sqlite] sqlite3_exec without ubiqitous text conversions
> Message-ID:
> <[hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> Hello,
>
> I have been working on an application based on SQLite for 2 years now,
> and
> recently we started running some performance profiling to check if
> there
> are areas where we can squeeze some extra performance.
>
> SQlite query execution is used almost exclusively through sqlite3_exec,
> and
> the implementation of callbacks.
>
> One of the areas of interest we identified is that every column is
> always
> passed to the callback as a c-char-string. Then we need to convert that
> string back to the intended data type. Apparently a consistent amount
> of
> time is spent doing this.
>
> I started looking into SQLite code and realized that int and real
> values
> retrieved from database, is actually fecthed as binary value, then
> converted to string, passed to the callback, and in the callback we
> convert
> it back to its original data type (as we keep track of 'true' datatype
> for
> each column).
>
> I decided to try skipping these conversions, by creating a custom
> implementation of sqlite3_exec, which does the following in the inside
> 'step' loop:
>
>                 if (rc == SQLITE_ROW) {
>                     azVals = azCols;
>                     for (i = 0; i < nCol; i++) {
>                         azVals[i] = (char *)sqlite3_column_text(pStmt,
> i);
>                         int col_type = sqlite3_column_type(pStmt, i);
>                         switch (col_type)
>                         {
>                             case SQLITE_INTEGER:
>                             case SQLITE_FLOAT:
>                             {
>                                 azVals[i] = (char*)columnMem(pStmt, i);
>                                 columnMallocFailure(pStmt);
>                                 break;
>                             }
>                             default:
>                             {
>                                 azVals[i] = (char
> *)sqlite3_column_text(pStmt, i);
>                                 break;
>                             }
>                         }
>                         if (!azVals[i] && sqlite3_column_type(pStmt, i)
> !=
> SQLITE_NULL) {
>                             sqlite3OomFault(db);
>                             goto exec_out;
>                         }
>                     }
>                 }
>
> Instead of ALWAYS converting to text, in case of INTEGER or FLOAT
> columns
> types, I fetch the address of the data  through columnMem, put it in
> the
> azVals array, and go on. The callback knows which is the expected data
> type
> for the column, reads and copies data in the destination variable
> through a
> simple assignment.
>
> I am aware that forcing a 'typed' pointer into a generic char* pointer
> (azVals[x]), rings a looooot of alarm bells; I am, as well, aware that
> I
> could avoid sqlite3_exec, and call sqlite3_step myself.
> As of now the application is running smoothly and faster as far as
> satabase
> access is concerned.
>
> I am writing here to get opinions about other potential pitfalls or
> oversights in this approach, as well as reason why I should not proceed
> on
> this path.
>
> Thanks in advance
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Barry Smith
In reply to this post by Barone Ashura
Why do it this way?

Why not write your own custom_sqlite3_exec(...) that uses the standard,
stable, documented interfaces?

custom_sqlite3_exec(...) could call prepare / step / finalize, and use the
standard sqlite3_column_* interfaces to fill a result array. This would be
very little work and could be a drop in replacement of sqlite3_exec. As a
bonus, you can have a proper signature of void* for your callback function
(since it's no longer receiving an array of strings, char** is a lie which
will confuse anyone working on the project in future). Another
maintainability bonus: The name will alert any consumers that this isn't
part of the standard sqlite3 interface.

An assumption you've made may be incorrect: You say that callers know what
data type to expect so that they know how to cast the results (I assume
this is based on the declared column affinity?), but SQLite is not a
strictly typed database. So the actual data might not match what they're
expecting; by going the route you've chosen the exact way you pick up these
errors might be quite far from the source of the error.

A sane improvement to the interface would be for the consumer of
custom_sqlite3_exec(...) to pass in an array of types that they are
expecting; checking that the types match inside of your custom function
would be fairly trivial (and performant) and you could have properly
defined behaviour for what happens if the types from the database don't
match the exepcted types. Some ideas are: Use SQLite type coercion (just
call sqlite3_int even if it's a string); fail; or skip the record.

On Tue, 30 Jul 2019 at 07:40, Barone Ashura <[hidden email]> wrote:

> I really wish to keep the argument polite and constructive, so please dont
> get me wrong if I reply, I just want to understand what you are referring
> to, realize and evaluate which is the best way to go for me, not for the
> sqlite library, that's why I writing to the sqlite library.
>
> Il giorno mar 30 lug 2019 alle ore 15:50 Hick Gunter <[hidden email]> ha
> scritto:
>
> > f) There are exactly 2 documented functions in your code. Did you not
> read
> > their documentation???
> >
> > See https://sqlite.org/c3ref/column_blob.html
> >
> > " After a type conversion, the result of calling sqlite3_column_type() is
> > undefined, though harmless. Future versions of SQLite may change the
> > behavior of sqlite3_column_type() following a type conversion."
> >
>
> which type conversion are you referring to? the second one erroneously
> called in the example code? I do understand the 'type conversion' to be one
> of the six functions listed in the page you linked (which was read).
> The statement before the one you quoted specifically says:  "The return
> value of sqlite3_column_type() can be used to decide which of the first six
> interface should be used to extract the column value". Isnt this exactly
> what I want to be doing? Read the column type from the statement, according
> to the return value call the relevant extraction function. The
> documentation describes 'automatic conversions' being performed if I am
> trying to extract a datatype that is different from the internal datatype.
> But this is exactly what I want to stay away from, unless, of course I am
> missing something, which I would very like understand.
>
>
> > b) Breaking encapsulation includes referencing internal .h files in your
> > own code. You should be using sqlite3.h *only*
> >
>
> I am not referencing ANY internal.h file in my own code. I am just using
> the amalgamation distribution, and I am writing code in sqlite.c, not in my
> own source files, so nobody outside of sqlite.c calls any function or uses
> any datatype that is not declared in sqlite.h.
>
>
>
> > You do realise that declared types are not enforced in SQLite? If somehow
> > a real with a non-integer value got inserted into an integer field, the
> > type of the returned value would still be real, but you would be
> expecting
> > integer. And silently reading the bits of the double value as an integer,
> > which would probalby cause a practically untetectable error.
> >
>
> True, agreed. This is the reason why the types of each value being inserted
> into, updated and read from the database is strictly enforced outside of
> sqlite. To state in a cear way: SQLite might not enforce declared types, by
> the application around sqlite has been specifically designed to enforce
> different types.
>
>
> >
> > c) There is no guarantee that the internal fields referring to a previous
> > state of a Mem structure are preserved across conversions.
> >
>
> why do you refer to a "previous state of a mem structure"? the code is
> being executed inside a custom sqlite_exec function, which mirrors the
> behaviour of the provided sqlite_exec function, except for the conversions
> to/from text (which return the pointer to the internal data type, without
> any conversion in case of Integer and float value), WAY before calling the
> statement finalization. The callback itself that must be provided to
> sql3_exec, is called before finalizing and freeing the memory allocated by
> the statement execution.
>
>
> > I do hope you are statically linking your modified SQLite code to your
> > application and not installing it as a shared image. In the latter case,
> > some unsuspecting application might inadvertently stumble across your
> > version of the interface, which no longer conforms to the published
> > interface, and cause failures there. That would probably cause some
> really
> > rave reviews. "I just installed XXX on my phone and it died" is not a
> > viable recommendation.
> >
>
> Again good point, but we are 'lucky' here as well... sqlite amalgamation is
> compiled into the application and not dinamically linked.
>
> I know that what I am doing is hazardous, rings alarm bells, 'dirty' (if
> you want to call it dirty), I know that it could be done in several
> different ways, it's just that sqlite3_exec does already an excellent job
> in wrapping prapared statemets, stepping and finalizing the statement
> itself; it does return an array of stringified column values, and that's
> what I would like to avoid (in the case of integers and floats).
>
> Looking forward to reading more insights, hints, tips, suggestions,
> arguments, advice, whatever.
>
> Thanks
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Hick Gunter
In reply to this post by Barone Ashura
Sorry if I came across impolite or destructive. The way you are attempting to do what you feel you need to raises a whole bunch of red flags, which I have been trying to explain, based on 40+ years of coding experience from assembler upwards (including COBOL, FORTRAN, BASIC, FORTH, PASCAL, C, lex, yacc and Perl).

As others have pointed out, it is safe and reasonable to write your own replacement for sqlite3_exec(), as long as you call it something else and stick to the documented interface.

Your diagnosis ("lots of time is wasted converting numeric data to text and back again") is correct, I only happen to think that your proposed therapy might easily kill the patient (and maybe some bystanders too).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 16:40
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

I really wish to keep the argument polite and constructive, so please dont get me wrong if I reply, I just want to understand what you are referring to, realize and evaluate which is the best way to go for me, not for the sqlite library, that's why I writing to the sqlite library.

Il giorno mar 30 lug 2019 alle ore 15:50 Hick Gunter <[hidden email]> ha
scritto:

> f) There are exactly 2 documented functions in your code. Did you not
> read their documentation???
>
> See https://sqlite.org/c3ref/column_blob.html
>
> " After a type conversion, the result of calling sqlite3_column_type()
> is undefined, though harmless. Future versions of SQLite may change
> the behavior of sqlite3_column_type() following a type conversion."
>

which type conversion are you referring to? the second one erroneously called in the example code? I do understand the 'type conversion' to be one of the six functions listed in the page you linked (which was read).
The statement before the one you quoted specifically says:  "The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value". Isnt this exactly what I want to be doing? Read the column type from the statement, according to the return value call the relevant extraction function. The documentation describes 'automatic conversions' being performed if I am trying to extract a datatype that is different from the internal datatype.
But this is exactly what I want to stay away from, unless, of course I am missing something, which I would very like understand.


> b) Breaking encapsulation includes referencing internal .h files in
> your own code. You should be using sqlite3.h *only*
>

I am not referencing ANY internal.h file in my own code. I am just using the amalgamation distribution, and I am writing code in sqlite.c, not in my own source files, so nobody outside of sqlite.c calls any function or uses any datatype that is not declared in sqlite.h.



> You do realise that declared types are not enforced in SQLite? If
> somehow a real with a non-integer value got inserted into an integer
> field, the type of the returned value would still be real, but you
> would be expecting integer. And silently reading the bits of the
> double value as an integer, which would probalby cause a practically untetectable error.
>

True, agreed. This is the reason why the types of each value being inserted into, updated and read from the database is strictly enforced outside of sqlite. To state in a cear way: SQLite might not enforce declared types, by the application around sqlite has been specifically designed to enforce different types.


>
> c) There is no guarantee that the internal fields referring to a
> previous state of a Mem structure are preserved across conversions.
>

why do you refer to a "previous state of a mem structure"? the code is being executed inside a custom sqlite_exec function, which mirrors the behaviour of the provided sqlite_exec function, except for the conversions to/from text (which return the pointer to the internal data type, without any conversion in case of Integer and float value), WAY before calling the statement finalization. The callback itself that must be provided to sql3_exec, is called before finalizing and freeing the memory allocated by the statement execution.


> I do hope you are statically linking your modified SQLite code to your
> application and not installing it as a shared image. In the latter
> case, some unsuspecting application might inadvertently stumble across
> your version of the interface, which no longer conforms to the
> published interface, and cause failures there. That would probably
> cause some really rave reviews. "I just installed XXX on my phone and
> it died" is not a viable recommendation.
>

Again good point, but we are 'lucky' here as well... sqlite amalgamation is compiled into the application and not dinamically linked.

I know that what I am doing is hazardous, rings alarm bells, 'dirty' (if you want to call it dirty), I know that it could be done in several different ways, it's just that sqlite3_exec does already an excellent job in wrapping prapared statemets, stepping and finalizing the statement itself; it does return an array of stringified column values, and that's what I would like to avoid (in the case of integers and floats).

Looking forward to reading more insights, hints, tips, suggestions, arguments, advice, whatever.

Thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Barone Ashura
In reply to this post by Barry Smith
> Why do it this way?



Why not write your own custom_sqlite3_exec(...) that uses the standard,
> stable, documented interfaces?


Because sometimes I take wrong turns despite my best intentions :)
But I usually smell it, when I am taking wrong turns, and that is why I can
up here asking
for advice, insights and enlightment, so I can u-turn, go back at the
crossroads, and take another direction :)



> custom_sqlite3_exec(...) could call prepare / step / finalize, and use the
> standard sqlite3_column_* interfaces to fill a result array. This would be
> very little work and could be a drop in replacement of sqlite3_exec. As a
> bonus, you can have a proper signature of void* for your callback function
> (since it's no longer receiving an array of strings, char** is a lie which
> will confuse anyone working on the project in future). Another
> maintainability bonus: The name will alert any consumers that this isn't
> part of the standard sqlite3 interface.
>

defining a new callback was one of the option for further development.
The different typed callback would certainly be void* for the reason you
mentioned.


> An assumption you've made may be incorrect: You say that callers know what
> data type to expect so that they know how to cast the results (I assume
> this is based on the declared column affinity?), but SQLite is not a
> strictly typed database. So the actual data might not match what they're
> expecting; by going the route you've chosen the exact way you pick up these
> errors might be quite far from the source of the error.
>

This is the thing that I need to get a perfect hold of. When could it
happen that
'data might not match what they're expecting'. A small description of the
application context and
where and how SQLite kicks in. The usage of SQLite is completely wrapped
into functions specific for
each query being designed and executed, and data from query is returned in
typed structures to the callers.
Callers are ALWAYS local to the application domain, and there is no
possibility (except in the case of poor
programming and security, which can always be) for the users (either
application users or 'library' users)
to try and execute a custom written SQL statement. The make a long story
short, I am developing the full stack of
datatypes (C structs and/or c++ classes), functions, queries and their
middle function calls that define which
services are available to access data contained in our SQLITE database.

I will try to place here a simple example, supposing we have a simple table
as the following:


CREATE TABLE VALUES (

id INTEGER PRIMARY KEY ASC,

value_int INTEGER,

value_double REAL,

value_text TEXT,

);


and that I want to execute the following simple query:

SELECT * FROM VALUES;

For this very specific query, are there circumstances where the call
to sqlite3_column_type, for column 'value_int', returns a result different
from
SQLITE_INTEGER, and/or the union located at the base address of Mem
returned by columnMem, is being filled as double or even worse, the whole
Mem structure is filled as 'TEXT'? (We can rotate the question for the 3
value_XXX fields).

Usage of SQLITE functions or casts in the queried results could very likely
tamper with the 'expected' datatype. Is this one case
case where the assumption is incorrect?


> A sane improvement to the interface would be for the consumer of
> custom_sqlite3_exec(...) to pass in an array of types that they are
> expecting; checking that the types match inside of your custom function
> would be fairly trivial (and performant) and you could have properly
> defined behaviour for what happens if the types from the database don't
> match the exepcted types. Some ideas are: Use SQLite type coercion (just
> call sqlite3_int even if it's a string); fail; or skip the record.
>

Suggestion Acknowledged :)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_exec without ubiqitous text conversions

Barone Ashura
In reply to this post by Bernardino Ramos
Suggestion Acknowledged :)

Il giorno mar 30 lug 2019 alle ore 18:56 <[hidden email]> ha scritto:

> Hi!
>
> I use a simple trick:
>
> A clone of the sqlite3_exec that passes the sqlite3_stmt as an argument
> to the callback function.
>
> So no conversion is made and we can use the sqlite3_column... functions
> directly on the retrieved row.
>
> Happy coding!
>
>
> On 2019-07-30 05:00, [hidden email] wrote:
> > Send sqlite-users mailing list submissions to
> >       [hidden email]
> >
> >    6. sqlite3_exec without ubiqitous text conversions (Barone Ashura)
> >
> >
> > ------------------------------
> >
> > Message: 6
> > Date: Tue, 30 Jul 2019 13:13:52 +0200
> > From: Barone Ashura <[hidden email]>
> > To: [hidden email]
> > Subject: [sqlite] sqlite3_exec without ubiqitous text conversions
> > Message-ID:
> >       <
> [hidden email]>
> > Content-Type: text/plain; charset="UTF-8"
> >
> > Hello,
> >
> > I have been working on an application based on SQLite for 2 years now,
> > and
> > recently we started running some performance profiling to check if
> > there
> > are areas where we can squeeze some extra performance.
> >
> > SQlite query execution is used almost exclusively through sqlite3_exec,
> > and
> > the implementation of callbacks.
> >
> > One of the areas of interest we identified is that every column is
> > always
> > passed to the callback as a c-char-string. Then we need to convert that
> > string back to the intended data type. Apparently a consistent amount
> > of
> > time is spent doing this.
> >
> > I started looking into SQLite code and realized that int and real
> > values
> > retrieved from database, is actually fecthed as binary value, then
> > converted to string, passed to the callback, and in the callback we
> > convert
> > it back to its original data type (as we keep track of 'true' datatype
> > for
> > each column).
> >
> > I decided to try skipping these conversions, by creating a custom
> > implementation of sqlite3_exec, which does the following in the inside
> > 'step' loop:
> >
> >                 if (rc == SQLITE_ROW) {
> >                     azVals = azCols;
> >                     for (i = 0; i < nCol; i++) {
> >                         azVals[i] = (char *)sqlite3_column_text(pStmt,
> > i);
> >                         int col_type = sqlite3_column_type(pStmt, i);
> >                         switch (col_type)
> >                         {
> >                             case SQLITE_INTEGER:
> >                             case SQLITE_FLOAT:
> >                             {
> >                                 azVals[i] = (char*)columnMem(pStmt, i);
> >                                 columnMallocFailure(pStmt);
> >                                 break;
> >                             }
> >                             default:
> >                             {
> >                                 azVals[i] = (char
> > *)sqlite3_column_text(pStmt, i);
> >                                 break;
> >                             }
> >                         }
> >                         if (!azVals[i] && sqlite3_column_type(pStmt, i)
> > !=
> > SQLITE_NULL) {
> >                             sqlite3OomFault(db);
> >                             goto exec_out;
> >                         }
> >                     }
> >                 }
> >
> > Instead of ALWAYS converting to text, in case of INTEGER or FLOAT
> > columns
> > types, I fetch the address of the data  through columnMem, put it in
> > the
> > azVals array, and go on. The callback knows which is the expected data
> > type
> > for the column, reads and copies data in the destination variable
> > through a
> > simple assignment.
> >
> > I am aware that forcing a 'typed' pointer into a generic char* pointer
> > (azVals[x]), rings a looooot of alarm bells; I am, as well, aware that
> > I
> > could avoid sqlite3_exec, and call sqlite3_step myself.
> > As of now the application is running smoothly and faster as far as
> > satabase
> > access is concerned.
> >
> > I am writing here to get opinions about other potential pitfalls or
> > oversights in this approach, as well as reason why I should not proceed
> > on
> > this path.
> >
> > Thanks in advance
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Olivier Mascia
In reply to this post by Barone Ashura
> Le 31 juil. 2019 à 10:03, Barone Ashura <[hidden email]> a écrit :
>
> CREATE TABLE "VALUES" (
> id INTEGER PRIMARY KEY ASC,
> value_int INTEGER,
> value_double REAL,
> value_text TEXT
> );
>
> and that I want to execute the following simple query:
>
> SELECT * FROM "VALUES";
>
> For this very specific query, are there circumstances where the call
> to sqlite3_column_type, for column 'value_int', returns a result different
> from
> SQLITE_INTEGER,

Yes.
Nothing stops any piece of your own programming or anyone using the CLI to do:

INSERT INTO "VALUES"(value_int) VALUES('something');


The sqlite3_column_type for the column value_int will tell you SQLITE_TEXT because:

    See: https://www.sqlite.org/c3ref/column_blob.html

    "The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column."

The "initial data type" is NOT the one declared in the schema. It is the type of the actual stored value in that particular column of that particular row.  SQLite is mostly type agnostic regarding storage.  The declared type in the schema only serves as a guideline for some automatic conversion where applicable.  SQLite calls this: 'affinity'.

    See: https://www.sqlite.org/datatype3.html#type_affinity

INSERT INTO "VALUES"(value_int) VALUES(123);
and
INSERT INTO "VALUES"(value_int) VALUES('123');

will both actually stores a SQLITE_INTEGER because the column has a type whose name starts with INT and the string '123' can safely be converted from ascii to integer and reciprocal.

But INSERT INTO "VALUES"(value_int) VALUES('something');
will actually store the string 'something' because it can't be converted back and forth between text and integer.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

Barone Ashura
Thanks!

this helped me a lot in understanding the red flags.
I think I have read the 'type affinity' documentation pages quite a few
times (including this morning).

Il giorno mer 31 lug 2019 alle ore 12:05 Olivier Mascia <[hidden email]> ha
scritto:

> > Le 31 juil. 2019 à 10:03, Barone Ashura <[hidden email]> a
> écrit :
> >
> > CREATE TABLE "VALUES" (
> > id INTEGER PRIMARY KEY ASC,
> > value_int INTEGER,
> > value_double REAL,
> > value_text TEXT
> > );
> >
> > and that I want to execute the following simple query:
> >
> > SELECT * FROM "VALUES";
> >
> > For this very specific query, are there circumstances where the call
> > to sqlite3_column_type, for column 'value_int', returns a result
> different
> > from
> > SQLITE_INTEGER,
>
> Yes.
> Nothing stops any piece of your own programming or anyone using the CLI to
> do:
>
> INSERT INTO "VALUES"(value_int) VALUES('something');
>
>
> The sqlite3_column_type for the column value_int will tell you SQLITE_TEXT
> because:
>
>     See: https://www.sqlite.org/c3ref/column_blob.html
>
>     "The sqlite3_column_type() routine returns the datatype code for the
> initial data type of the result column."
>
> The "initial data type" is NOT the one declared in the schema. It is the
> type of the actual stored value in that particular column of that
> particular row.  SQLite is mostly type agnostic regarding storage.  The
> declared type in the schema only serves as a guideline for some automatic
> conversion where applicable.  SQLite calls this: 'affinity'.
>
>     See: https://www.sqlite.org/datatype3.html#type_affinity
>
> INSERT INTO "VALUES"(value_int) VALUES(123);
> and
> INSERT INTO "VALUES"(value_int) VALUES('123');
>
> will both actually stores a SQLITE_INTEGER because the column has a type
> whose name starts with INT and the string '123' can safely be converted
> from ascii to integer and reciprocal.
>
> But INSERT INTO "VALUES"(value_int) VALUES('something');
> will actually store the string 'something' because it can't be converted
> back and forth between text and integer.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sqlite3_exec without ubiqitous text conversions

James K. Lowden
In reply to this post by Olivier Mascia
On Wed, 31 Jul 2019 12:05:05 +0200
Olivier Mascia <[hidden email]> wrote:

> Nothing stops any piece of your own programming or anyone using the
> CLI to do:
>
> INSERT INTO "VALUES"(value_int) VALUES('something');

Not nothing, just nothing automatic.  

        value_int INTEGER not NULL
                check( typeof(value_int) = 'integer')

will do nicely.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users