Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database

[Hick Gunter]

What is this tbllog table?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von bhandari_nikhil
Gesendet: Donnerstag, 01. August 2019 07:39
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Error in recover sqlite3 database

My sqlite3 database keeps on increasing in memory (although it is limited by 
memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it ? 
BTW, even after a reboot of the device, we land up in the same situation after 
some time. Is there some problem with the journal ?



--
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


___
 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

[sqlite] Error in recover sqlite3 database

[bhandari_nikhil]

My sqlite3 database keeps on increasing in memory (although it is limited by
memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');   
  

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it
? BTW, even after a reboot of the device, we land up in the same situation
after some time. Is there some problem with the journal ?



--
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

Re: [sqlite] Floating point literals

[Simon Slavin]

On 1 Aug 2019, at 12:55am, Keith Medcalf  wrote:

> Columns declared with no affinity behave as if they had been declared with 
> BLOB infinity and v/v.

Okay, so leaving out the affinity just lets it default to BLOB.  That clarifies 
and simplifies things.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Keith Medcalf]

On Wednesday, 31 July, 2019 17:29, Simon Slavin  wrote:

>On 31 Jul 2019, at 11:58pm, Keith Medcalf  wrote:

>> it depends on the application of affinity.  If you are storing the
>floating point value in a column that does not have an affinity (ie,
>no conversions are performed), then it is stored exactly (except for
>NaN). Application of affinity (ie, real) will cause the -0.0 to be
>stored as the integer 0 and thus the sign will be lost on retrieval
>(as well as the conversion of NaN to NULL).

>

>in section 3.2 states "Every table column has a type affinity (one of
>BLOB, TEXT, INTEGER, REAL, or NUMERIC)"

Section 3 states:

(Historical note: The "BLOB" type affinity used to be called "NONE". But that 
term was easy to confuse with "no affinity" and so it was renamed.)

>Do you feel that your demonstration (which I find convincing) agrees
>or disagrees with that statement ?  You seem to have found a sixth
>column affinity: none.  Certainly columns declared with no affinity
>do not behave the same as any of the five documented affinities.

It agrees.  Columns declared with no affinity behave as if they had been 
declared with BLOB infinity and v/v.  That means that they do not attempt to 
convert whatever is presented to be stored as something else.  What is 
presented is stored as presented.

A column affinity (other than BLOB) means that for affinity X if the thing 
being stored looks like X and quacks like X and can be losslessly converted to 
X, then store X (which includes storing floating values that will fit in an 
integer as integers in order to save space).  Affinity BLOB means to not be 
doing that -- store what you is given.

>Should the documentation be updated ?

No.  Though maybe it should be put on the quirks page for those very few things 
that need to distinguish between -0.0 and +0.0

>What happens if you add a line to your demo code
>
> for row in db.execute('select x from x ORDER BY x'): print row
>
>Does -0.0 get sorted before or with 0.0 ?  I'd do it myself but I
>don't know Python.

No. -0.0 and 0.0 sort equal, as they should because they are equal.  Mostly.

-- 
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] Explicit "read transaction" with journal_mode=WAL.

[Jean-Christophe Deschamps]

Simon,

Consider a bank which takes an audit every day at 
close-of-business.  This might be declared to be 5pm.  However, 
accounts are continued to be debited and credited all night, due to 
interest being added, ATM transactions, etc..  Nevertheless, the audit 
needs to see a snapshot as of 5pm.


Of course, no bank would be using SQLite for this purpose, because a 
bank would be using a server/client DBMS.  But you get the idea.


I don't believe this can be any close to a real-world scenario, 
client/server architecture set aside.


Being able to issue and process a BEGIN SHARED IMMEDIATE for the read 
lock be in place at exactly 5pm, without ever missing a transaction 
performed from elsewhere at 04:59:59:999.99 nor including a transaction 
commited at 05:00:00:0.001 seems to be an impossible task in practice.


One may find it uncomfortable to ignore the delay between BEGIN is 
issued and when the next SELECT gets the lock set, but in fact you 
would never know either the delay between your program issuing BEGIN 
SHARED IMMEDIATE and the precise moment the lock is actually setup, 
unless under a low-load real-time OS providing explicit garantees on 
various exec times.  And even there, I'm not that sure.


From my remote/naive viewpoint, this is a misuse of a RDBMS relying on 
DIY.  When you want/need to be sure what you are going to read is ante 
 the only serious way is to include a precise enough 
timestamp in data rows and limit the select using it.


Indeed if you would want to do that and if you need to be just on time, 
you would rather use rock-solid:


select  from  ... where timestamp between 
 and 


I still fail to imagine a useful use case for such feature.

As Igor shown, A==B and A!=B are indiscernable.

Further in the thread the argument of "symetry" between BEGIN SHARED 
IMMEDIATE and BEGIN IMMEDIATE is only a surface view, because the arrow 
of time is one-way.  A real symetry would be a BEGIN IMMEDIATE TO BE 
COMMITED BEFORE  but that clearly doesn't make any sense.


JcD

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Simon Slavin]

On 31 Jul 2019, at 11:58pm, Keith Medcalf  wrote:

> it depends on the application of affinity.  If you are storing the floating 
> point value in a column that does not have an affinity (ie, no conversions 
> are performed), then it is stored exactly (except for NaN). Application of 
> affinity (ie, real) will cause the -0.0 to be stored as the integer 0 and 
> thus the sign will be lost on retrieval (as well as the conversion of NaN to 
> NULL).



in section 3.2 states "Every table column has a type affinity (one of BLOB, 
TEXT, INTEGER, REAL, or NUMERIC)"

Do you feel that your demonstration (which I find convincing) agrees or 
disagrees with that statement ?  You seem to have found a sixth column 
affinity: none.  Certainly columns declared with no affinity do not behave the 
same as any of the five documented affinities.

Should the documentation be updated ?

What happens if you add a line to your demo code

 for row in db.execute('select x from x ORDER BY x'): print row

Does -0.0 get sorted before or with 0.0 ?  I'd do it myself but I don't know 
Python.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Donald Shepherd]

Thanks, that's an interesting wrinkle that I don't remember being raised in
previous discussions but if known it should be mentioned up front as
many/most use affinities.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:58, Keith Medcalf  wrote:

> False, as it depends on the application of affinity.  If you are storing
> the floating point value in a column that does not have an affinity (ie, no
> conversions are performed), then it is stored exactly (except for NaN).
> Application of affinity (ie, real) will cause the -0.0 to be stored as the
> integer 0 and thus the sign will be lost on retrieval (as well as the
> conversion of NaN to NULL).
>
> >>> import apsw
> >>> import math
> >>> db = apsw.Connection('')
> >>> db.execute('create table x(x)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=-0.0)
>
> >>> db.execute('drop table x');
> >>> db.execute('create table x(x real)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=0.0)
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Donald Shepherd
> >Sent: Wednesday, 31 July, 2019 16:50
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Floating point literals
> >
> >That's not correct, verified several times by my own testing and
> >re-verified on the recent discussion about -0.0 on this mailing list.
> >
> >If you store -0.0 as a double, it will be stored as an integer as a
> >space-saving mechanism.  That integer is 0.  When you retrieve the
> >value as
> >a double it will be 0.0.  The sign has been stripped.
> >
> >Regards,
> >Donald Shepherd.
> >
> >On Thu, 1 Aug 2019 at 08:47, Keith Medcalf 
> >wrote:
> >
> >>
> >> The -0.0 is only for conversion to text.  Otherwise -0.0 is
> >preserved both
> >> on input and output (including input text conversions).  It is only
> >the
> >> conversion of -0.0 TO text that drops the sign.  NaN becomes a NULL
> >(ie, a
> >> double is not stored, a NULL value is stored).  Everything else is
> >> preserved including Inf and -Inf.
> >>
> >> --
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> >> >Sent: Wednesday, 31 July, 2019 15:34
> >> >To: sqlite-users@mailinglists.sqlite.org
> >> >Subject: Re: [sqlite] Floating point literals
> >> >
> >> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
> >> >> I understand you can *retrieve* a non-quantized value using
> >> >sqlite3_column_double(), but I don't see a way to set one without
> >> >having to printf() the floating point value.
> >> >
> >> >sqlite3_bind_double
> >> >
> >> >> Can this be done using sqlite3_bind_* interfaces, or do they
> >> >quantize as well?
> >> >
> >> >Yes. No; except that I seem to recall it mentioned that NaN is
> >> >treated as SQL NULL, and negative zero is normalized to positive
> >> >zero.
> >> >
> >> >> The goal is to copy the straight 8-byte (or precision-extended
> >4-
> >> >byte) IEEE value into the column into the database (where the
> >column
> >> >is defined as a FLOAT) without having to build a SQL statement
> >that
> >> >has an obscene number of digits in each floating point field.
> >> >
> >> >That's precisely what bound parameters and sqlite3_bind_X
> >functions
> >> >are for.
> >> >--
> >> >Igor Tandetnik
> >> >
> >> >
> >> >___
> >> >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
>
>
>
> ___
> sqlite-us

Re: [sqlite] Floating point literals

[Keith Medcalf]

False, as it depends on the application of affinity.  If you are storing the 
floating point value in a column that does not have an affinity (ie, no 
conversions are performed), then it is stored exactly (except for NaN).  
Application of affinity (ie, real) will cause the -0.0 to be stored as the 
integer 0 and thus the sign will be lost on retrieval (as well as the 
conversion of NaN to NULL).

>>> import apsw
>>> import math
>>> db = apsw.Connection('')
>>> db.execute('create table x(x)');
>>> db.execute('insert into x values (?)', (math.nan,))
>>> db.execute('insert into x values (?)', (math.inf,))
>>> db.execute('insert into x values (?)', (-math.inf,))
>>> db.execute('insert into x values (?)', (0.0,))
>>> db.execute('insert into x values (?)', (-0.0,))
>>> for row in db.execute('select x from x'): print row
...
Row(x=None)
Row(x=inf)
Row(x=-inf)
Row(x=0.0)
Row(x=-0.0)

>>> db.execute('drop table x');
>>> db.execute('create table x(x real)');
>>> db.execute('insert into x values (?)', (math.nan,))
>>> db.execute('insert into x values (?)', (math.inf,))
>>> db.execute('insert into x values (?)', (-math.inf,))
>>> db.execute('insert into x values (?)', (0.0,))
>>> db.execute('insert into x values (?)', (-0.0,))
>>> for row in db.execute('select x from x'): print row
...
Row(x=None)
Row(x=inf)
Row(x=-inf)
Row(x=0.0)
Row(x=0.0)

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Donald Shepherd
>Sent: Wednesday, 31 July, 2019 16:50
>To: SQLite mailing list
>Subject: Re: [sqlite] Floating point literals
>
>That's not correct, verified several times by my own testing and
>re-verified on the recent discussion about -0.0 on this mailing list.
>
>If you store -0.0 as a double, it will be stored as an integer as a
>space-saving mechanism.  That integer is 0.  When you retrieve the
>value as
>a double it will be 0.0.  The sign has been stripped.
>
>Regards,
>Donald Shepherd.
>
>On Thu, 1 Aug 2019 at 08:47, Keith Medcalf 
>wrote:
>
>>
>> The -0.0 is only for conversion to text.  Otherwise -0.0 is
>preserved both
>> on input and output (including input text conversions).  It is only
>the
>> conversion of -0.0 TO text that drops the sign.  NaN becomes a NULL
>(ie, a
>> double is not stored, a NULL value is stored).  Everything else is
>> preserved including Inf and -Inf.
>>
>> --
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-Original Message-
>> >From: sqlite-users [mailto:sqlite-users-
>> >boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
>> >Sent: Wednesday, 31 July, 2019 15:34
>> >To: sqlite-users@mailinglists.sqlite.org
>> >Subject: Re: [sqlite] Floating point literals
>> >
>> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
>> >> I understand you can *retrieve* a non-quantized value using
>> >sqlite3_column_double(), but I don't see a way to set one without
>> >having to printf() the floating point value.
>> >
>> >sqlite3_bind_double
>> >
>> >> Can this be done using sqlite3_bind_* interfaces, or do they
>> >quantize as well?
>> >
>> >Yes. No; except that I seem to recall it mentioned that NaN is
>> >treated as SQL NULL, and negative zero is normalized to positive
>> >zero.
>> >
>> >> The goal is to copy the straight 8-byte (or precision-extended
>4-
>> >byte) IEEE value into the column into the database (where the
>column
>> >is defined as a FLOAT) without having to build a SQL statement
>that
>> >has an obscene number of digits in each floating point field.
>> >
>> >That's precisely what bound parameters and sqlite3_bind_X
>functions
>> >are for.
>> >--
>> >Igor Tandetnik
>> >
>> >
>> >___
>> >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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Donald Shepherd]

That's not correct, verified several times by my own testing and
re-verified on the recent discussion about -0.0 on this mailing list.

If you store -0.0 as a double, it will be stored as an integer as a
space-saving mechanism.  That integer is 0.  When you retrieve the value as
a double it will be 0.0.  The sign has been stripped.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:47, Keith Medcalf  wrote:

>
> The -0.0 is only for conversion to text.  Otherwise -0.0 is preserved both
> on input and output (including input text conversions).  It is only the
> conversion of -0.0 TO text that drops the sign.  NaN becomes a NULL (ie, a
> double is not stored, a NULL value is stored).  Everything else is
> preserved including Inf and -Inf.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> >Sent: Wednesday, 31 July, 2019 15:34
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: Re: [sqlite] Floating point literals
> >
> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
> >> I understand you can *retrieve* a non-quantized value using
> >sqlite3_column_double(), but I don't see a way to set one without
> >having to printf() the floating point value.
> >
> >sqlite3_bind_double
> >
> >> Can this be done using sqlite3_bind_* interfaces, or do they
> >quantize as well?
> >
> >Yes. No; except that I seem to recall it mentioned that NaN is
> >treated as SQL NULL, and negative zero is normalized to positive
> >zero.
> >
> >> The goal is to copy the straight 8-byte (or precision-extended 4-
> >byte) IEEE value into the column into the database (where the column
> >is defined as a FLOAT) without having to build a SQL statement that
> >has an obscene number of digits in each floating point field.
> >
> >That's precisely what bound parameters and sqlite3_bind_X functions
> >are for.
> >--
> >Igor Tandetnik
> >
> >
> >___
> >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] Floating point literals

[Keith Medcalf]

The -0.0 is only for conversion to text.  Otherwise -0.0 is preserved both on 
input and output (including input text conversions).  It is only the conversion 
of -0.0 TO text that drops the sign.  NaN becomes a NULL (ie, a double is not 
stored, a NULL value is stored).  Everything else is preserved including Inf 
and -Inf.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
>Sent: Wednesday, 31 July, 2019 15:34
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Floating point literals
>
>On 7/31/2019 5:15 PM, Eric Reischer wrote:
>> I understand you can *retrieve* a non-quantized value using
>sqlite3_column_double(), but I don't see a way to set one without
>having to printf() the floating point value.
>
>sqlite3_bind_double
>
>> Can this be done using sqlite3_bind_* interfaces, or do they
>quantize as well?
>
>Yes. No; except that I seem to recall it mentioned that NaN is
>treated as SQL NULL, and negative zero is normalized to positive
>zero.
>
>> The goal is to copy the straight 8-byte (or precision-extended 4-
>byte) IEEE value into the column into the database (where the column
>is defined as a FLOAT) without having to build a SQL statement that
>has an obscene number of digits in each floating point field.
>
>That's precisely what bound parameters and sqlite3_bind_X functions
>are for.
>--
>Igor Tandetnik
>
>
>___
>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] Floating point literals

[Donald Shepherd]

Plus (as Igor noted) -0.0 returns as 0.0.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:41, Keith Medcalf  wrote:

>
> sqlite3_bind_double and sqlite3_column_double will round trip IEEE
> floating point values EXCEPT for NaN.  NaN will be stored as a NULL.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Eric Reischer
> >Sent: Wednesday, 31 July, 2019 15:15
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Floating point literals
> >
> >Is there a way to pass binary representations of floating point
> >numbers to
> >a SQL query?  If sqlite's internal representation of floating point
> >numbers is 8-byte IEEE doubles, it would be convenient to be able to
> >pass
> >the literal value of a float or double to the underlying SQL parser
> >without suffering the quantization that occurs with printf()'ing
> >floating
> >point values.
> >
> >One way I've accomplished this in the past with other interfaces is
> >to
> >interpret a hex value as a binary literal that can be interpreted as
> >a raw
> >4-byte or 8-byte IEEE floating-point value (either via a union or
> >other
> >compiler trick).  I understand you can *retrieve* a non-quantized
> >value
> >using sqlite3_column_double(), but I don't see a way to set one
> >without
> >having to printf() the floating point value.
> >
> >Can this be done using sqlite3_bind_* interfaces, or do they quantize
> >as
> >well?  The documentation isn't clear on this.  The goal is to copy
> >the
> >straight 8-byte (or precision-extended 4-byte) IEEE value into the
> >column
> >into the database (where the column is defined as a FLOAT) without
> >having
> >to build a SQL statement that has an obscene number of digits in each
> >floating point field.
> >
> >Thanks in advance.
> >___
> >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] Floating point literals

[Keith Medcalf]

sqlite3_bind_double and sqlite3_column_double will round trip IEEE floating 
point values EXCEPT for NaN.  NaN will be stored as a NULL.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Eric Reischer
>Sent: Wednesday, 31 July, 2019 15:15
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Floating point literals
>
>Is there a way to pass binary representations of floating point
>numbers to
>a SQL query?  If sqlite's internal representation of floating point
>numbers is 8-byte IEEE doubles, it would be convenient to be able to
>pass
>the literal value of a float or double to the underlying SQL parser
>without suffering the quantization that occurs with printf()'ing
>floating
>point values.
>
>One way I've accomplished this in the past with other interfaces is
>to
>interpret a hex value as a binary literal that can be interpreted as
>a raw
>4-byte or 8-byte IEEE floating-point value (either via a union or
>other
>compiler trick).  I understand you can *retrieve* a non-quantized
>value
>using sqlite3_column_double(), but I don't see a way to set one
>without
>having to printf() the floating point value.
>
>Can this be done using sqlite3_bind_* interfaces, or do they quantize
>as
>well?  The documentation isn't clear on this.  The goal is to copy
>the
>straight 8-byte (or precision-extended 4-byte) IEEE value into the
>column
>into the database (where the column is defined as a FLOAT) without
>having
>to build a SQL statement that has an obscene number of digits in each
>floating point field.
>
>Thanks in advance.
>___
>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] Quick way to determine optimal page size?

[Tony Papadimitriou]

Tens of databases (from a few MBs to almost GB), so it's good to keep them 
at their minimum size (for disk and backup savings).


I often save several megabytes by going to the 'right' size, eg., just today 
I went from ~110MB down to ~80MB in one of them ('vacuum'ed before and after 
so it's just the page size making this difference).  Sometimes, very small 
page sizes give best results, sometimes the other way around.


Some databases do well in the same page size as new data is added, but for 
some others you need to recalculate as their content changes.
Still, you can't know in advance which ones can do better unless you 
actually try it.  And, that's the main problem.
I have to try with ~100 DBs to get a significant benefit in just a few of 
them (about 5-10), until next time.


Anyway, I thought I'd ask.

-Original Message- 
From: David Raymond

Sent: Wednesday, July 31, 2019 10:48 PM
To: SQLite mailing list
Subject: Re: [sqlite] Quick way to determine optimal page size?

Not that I'm aware of no. How much of a difference are you seeing for your 
database size depending on the page size you try?


-Original Message-
From: sqlite-users  On Behalf 
Of Tony Papadimitriou

Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database 


Subject: [sqlite] Quick way to determine optimal page size?

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size 
and each database to determine which one produces the smallest file, is 
there some quicker way?


Thanks.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

Quote: Connection 2 just happened to write lots of data and commit before
connection 1 obtained a read transaction
Quote: if SELECT on Connection 1 just happens to beat the write on
Connection 2

- The order in the example is exact, not a guess what might happen.
- Each step runs in a single thread on an event loop and is awaited, and
assumed to return with OK.
- The read transaction at the start is definite (supposing BEGIN READ
existing).


All I am saying is on "Connection 1: Get read transaction" could be:

BEGIN READ

NOT

BEGIN; SELECT * FROM some_table;


Quote: Why again do you care how BEGIN behaves

Im just suggesting that if you can obtain a write transaction with a single
trip over the FFI and an explicit command "BEGIN IMMEDIATE" the same could
be true for "read transactions"

I care because I think its a better API design which would then allow
better higher level libraries.






On Wed, Jul 31, 2019 at 9:46 PM Igor Tandetnik  wrote:

> On 7/31/2019 12:32 PM, test user wrote:
> > In some runtimes, the scheduling of functions is unpredictable, so
> although
> > you will not have a `sleep 5` in the code, the runtime can produce this
> > effect on loaded systems or with programs with long running sync
> functions.
> >
> >
> > An example of how you might use this:
> > - Connection 1: Get a read transaction.
> >
> > - Connection 2: Get a write transaction, write a lot of data, commit.
> > - Connection 2: SELECT report summary B.
> >
> > - Connection 1: SELECT report summary A,
> >
> > - Diff A and B to see what changed.
>
> Suppose you discovered that B and A are in fact the same. How do you know
> whether that occurred because a) "get a read transaction" is "broken" in
> that it doesn't actually acquire the lock as you expected, or because b)
> Connection 2 just happened to write lots of data and commit before
> connection 1 obtained a read transaction?
>
> In other words, in your example A == B is possible even if BEGIN worked
> the way you expect it to work, and grabbed a read lock immediately.
> Similarly, A != B is possible with BEGIN working the way it does now, if
> SELECT on Connection 1 just happens to beat the write on Connection 2. It's
> a matter of timing and scheduling, which you yourself posit is
> unpredictable.
>
> So, since both A==B and A!=B are possible with either behavior of BEGIN,
> why again do you care how BEGIN behaves?
> --
> Igor Tandetnik
>
> ___
> 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] Floating point literals

[Igor Tandetnik]

On 7/31/2019 5:15 PM, Eric Reischer wrote:

I understand you can *retrieve* a non-quantized value using 
sqlite3_column_double(), but I don't see a way to set one without having to 
printf() the floating point value.


sqlite3_bind_double


Can this be done using sqlite3_bind_* interfaces, or do they quantize as well?


Yes. No; except that I seem to recall it mentioned that NaN is treated as SQL 
NULL, and negative zero is normalized to positive zero.


The goal is to copy the straight 8-byte (or precision-extended 4-byte) IEEE 
value into the column into the database (where the column is defined as a 
FLOAT) without having to build a SQL statement that has an obscene number of 
digits in each floating point field.


That's precisely what bound parameters and sqlite3_bind_X functions are for.
--
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Floating point literals

[Simon Slavin]

On 31 Jul 2019, at 10:15pm, Eric Reischer  wrote:

> Is there a way to pass binary representations of floating point numbers to a 
> SQL query?  If sqlite's internal representation of floating point numbers is 
> 8-byte IEEE doubles, it would be convenient to be able to pass the literal 
> value of a float or double to the underlying SQL parser without suffering the 
> quantization that occurs with printf()'ing floating point values.

Rather than including the values in the text of the query:

INSERT INTO A (B) VALUES (3.1)

use a parameter marker:

INSERT INTO A (B) VALUES (?)

and bind the value to that parameter:



int sqlite3_bind_double(sqlite3_stmt*, int, double);

This does not involve any rendering of the value into a string.  However, since 
the C double is not necessarily a representation of IEEE value, you cannot rely 
on values being passed entirely unchanged.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Richard Damon]

If, and it is possible for there to be sequencing to enforce it, You KNOW that 
step 1, get a read transaction (which the OP presumes implies includes getting 
the lock) occurs before step 2, and thus step 4 seeing changes from step 3 says 
something is wrong.

Yes, if you only can use the database to communicate, this may not be possible, 
but if the program does the get a read transaction itself, and only after 
getting the confirmation starts the process that does the write, it can 
positively know that it ‘started’ the read transaction before the write 
transaction was started. 

SQLite (I believe) documents that the BEGIN statement doesn’t set the read lock 
until the select, so its behavior is as documented, just not as the OP desires. 
For them, it is desired that the beginning of the transaction also gets the 
lock, so the their API point of begin a transaction marks the point that the 
read data is locked to.

One option, that they are pursuing (but seems to have been denied) is to have 
SQLite have a variant of BEGIN that includes getting the read lock. Their other 
option is to just do a dummy SELECT on the database to force getting the lock. 
The callers to their API won’t be able to tell the difference, except maybe a 
bit of execution time.

> On Jul 31, 2019, at 4:46 PM, Igor Tandetnik  wrote:
> 
>> On 7/31/2019 12:32 PM, test user wrote:
>> In some runtimes, the scheduling of functions is unpredictable, so although
>> you will not have a `sleep 5` in the code, the runtime can produce this
>> effect on loaded systems or with programs with long running sync functions.
>> An example of how you might use this:
>> - Connection 1: Get a read transaction.
>> - Connection 2: Get a write transaction, write a lot of data, commit.
>> - Connection 2: SELECT report summary B.
>> - Connection 1: SELECT report summary A,
>> - Diff A and B to see what changed.
> 
> Suppose you discovered that B and A are in fact the same. How do you know 
> whether that occurred because a) "get a read transaction" is "broken" in that 
> it doesn't actually acquire the lock as you expected, or because b) 
> Connection 2 just happened to write lots of data and commit before connection 
> 1 obtained a read transaction?
> 
> In other words, in your example A == B is possible even if BEGIN worked the 
> way you expect it to work, and grabbed a read lock immediately. Similarly, A 
> != B is possible with BEGIN working the way it does now, if SELECT on 
> Connection 1 just happens to beat the write on Connection 2. It's a matter of 
> timing and scheduling, which you yourself posit is unpredictable.
> 
> So, since both A==B and A!=B are possible with either behavior of BEGIN, why 
> again do you care how BEGIN behaves?
> -- 
> Igor Tandetnik
> 
> ___
> 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] Floating point literals

[Eric Reischer]

Is there a way to pass binary representations of floating point numbers to 
a SQL query?  If sqlite's internal representation of floating point 
numbers is 8-byte IEEE doubles, it would be convenient to be able to pass 
the literal value of a float or double to the underlying SQL parser 
without suffering the quantization that occurs with printf()'ing floating 
point values.


One way I've accomplished this in the past with other interfaces is to 
interpret a hex value as a binary literal that can be interpreted as a raw 
4-byte or 8-byte IEEE floating-point value (either via a union or other 
compiler trick).  I understand you can *retrieve* a non-quantized value 
using sqlite3_column_double(), but I don't see a way to set one without 
having to printf() the floating point value.


Can this be done using sqlite3_bind_* interfaces, or do they quantize as 
well?  The documentation isn't clear on this.  The goal is to copy the 
straight 8-byte (or precision-extended 4-byte) IEEE value into the column 
into the database (where the column is defined as a FLOAT) without having 
to build a SQL statement that has an obscene number of digits in each 
floating point field.


Thanks in advance.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Igor Tandetnik]

On 7/31/2019 12:32 PM, test user wrote:

In some runtimes, the scheduling of functions is unpredictable, so although
you will not have a `sleep 5` in the code, the runtime can produce this
effect on loaded systems or with programs with long running sync functions.


An example of how you might use this:
- Connection 1: Get a read transaction.

- Connection 2: Get a write transaction, write a lot of data, commit.
- Connection 2: SELECT report summary B.

- Connection 1: SELECT report summary A,

- Diff A and B to see what changed.


Suppose you discovered that B and A are in fact the same. How do you know whether that occurred 
because a) "get a read transaction" is "broken" in that it doesn't actually 
acquire the lock as you expected, or because b) Connection 2 just happened to write lots of data 
and commit before connection 1 obtained a read transaction?

In other words, in your example A == B is possible even if BEGIN worked the way 
you expect it to work, and grabbed a read lock immediately. Similarly, A != B 
is possible with BEGIN working the way it does now, if SELECT on Connection 1 
just happens to beat the write on Connection 2. It's a matter of timing and 
scheduling, which you yourself posit is unpredictable.

So, since both A==B and A!=B are possible with either behavior of BEGIN, why 
again do you care how BEGIN behaves?
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quick way to determine optimal page size?

[Simon Slavin]

On 31 Jul 2019, at 8:28pm, Tony Papadimitriou  wrote:

> Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and 
> each database to determine which one produces the smallest file, is there 
> some quicker way?

It might be faster to

make a new file,
set page size,
ATTACH the old file,

and use the

INSERT INTO ... (SELECT * FROM)

syntax to make new database files rather than repeatedly use VACUUM to 
reorganise the old one.  Once you know which pagesize gave the best result, you 
can delete the old one.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quick way to determine optimal page size?

[David Raymond]

Not that I'm aware of no. How much of a difference are you seeing for your 
database size depending on the page size you try?


-Original Message-
From: sqlite-users  On Behalf Of 
Tony Papadimitriou
Sent: Wednesday, July 31, 2019 3:29 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] Quick way to determine optimal page size?

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and 
each database to determine which one produces the smallest file, is there some 
quicker way?

Thanks.
___
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] Quick way to determine optimal page size?

[Tony Papadimitriou]

Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and 
each database to determine which one produces the smallest file, is there some 
quicker way?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Double CTRL-C in shell get you completely out

[Tony Papadimitriou]

Recently CTRL-C was improved to abort the query and stay in the CLI.  This is 
very good.

However, if (accidentally, e.g., key bounce) a second CTRL-C is entered, it 
will escape back to the console.

Could it be changed so that either:

1. Only CTRL-D (Linux) or CTRL-Z (Windows) is used to exit the CLI

-- or --

2. If doing CTRL-C at the CLI prompt when nothing’s running to first confirm 
(like ‘Are you sure?’ with a No default, so that an explicit ‘Y’ has to be 
entered)?

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Olivier Mascia]

> Le 31 juil. 2019 à 18:53, Keith Medcalf  a écrit :
> 
> I believe the idea would be to permit syntax something like:
> 
> BEGIN IMMEDIATE [SHARED|[UPDATE]] [TRANSACTION]

Keith, I mostly share your view and I like the fact that the proposal uses 
SHARED and not READ or anything like that because the goal is not to propose 
some "read-only transaction".

Yet, I fail to understand why:

1) you wrote it in the above way with [SHARED|[UPDATE]] syntax instead of:

> BEGIN IMMEDIATE [SHARED|UPDATE] [TRANSACTION]

and 2) why it would even matter to make provision for the optional UPDATE token.

BEGIN IMMEDIATE [TRANSACTION]
is the current semantic (immediately get RESERVED lock, as a first *write* 
would do within a DEFERRED TRANSACTION)

BEGIN IMMEDIATE SHARED [TRANSACTION]
would be the new semantic (immediately get SHARED lock, as a first *read* would 
do within a DEFERRED TRANSACTION)

Forgetting the UPDATE sugar might make the syntax change simpler.
The key issue around this discussion, is probably that it's a syntactic and 
semantic change that would not be available in older versions.

Applications which have proxy code to begin transactions can very easily 
attempt some:
SELECT ROWID FROM SQLITE_MASTER LIMIT 0
right after successfully running BEGIN [DEFERRED] [TRANSACTION]
to emulate that IMMEDIATE SHARED feature.

Oh by the way, the syntax might be the following alternative, closer to the 
current syntax diagrams of SQLite.  I don't know if syntax compatibility 
provisions with other SQL implementations (and some of its 'standards') would 
dictate yours or this one.

BEGIN [DEFERRED|SHARED|IMMEDIATE|EXCLUSIVE] [TRANSACTION]

In the end, it surely is not a very necessary feature.
:)
—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Richard Hipp]

On 7/31/19, Thomas Kurz  wrote:
> Would it be possible for you to give some feedback (just an estimation)
> whether or not a suggestion might be considered?

Low probability at this time.

The suggestion does not provide any new capability, but it is
something that we would need to test and maintain for the next 31
years.  So it has a high cost and low benefit.
-- 
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] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Wednesday, 31 July, 2019 10:21, Simon Slavin  wrote:

>But I think the BEGIN command can be retrofitted without breaking
>backward compatibility.  The words WRITE and IMMEDIATE should be seen
>as options.  WRITE means that you want a write lock as well as a read
>lock.  IMMEDIATE means that you want it now, rather than when the
>first command of the transaction requires a lock.  Use neither,
>either, or both.  And EXCLUSIVE gets parsed as IMMEDIATE WRITE.  Thus
>existing programs continue to do the same thing they always did.

BEGIN [DEFERRED] [TRANSACTION] is the current default and means defer acquiring 
locks until they are required.
BEGIN IMMEDIATE [TRANSACTION] is currently implemented and acquires a SHARED 
and an INTENT lock immediately.
BEGIN EXCLUSIVE [TRANSACTION] is currently implemented and acquires an 
EXCLUSIVE lock immediately.

I believe the idea would be to permit syntax something like:

BEGIN IMMEDIATE [SHARED|[UPDATE]] [TRANSACTION]

which would allow the specification of the type of lock to immediately acquire, 
either a simple shared lock, or the shared and intent locks as is currently 
done.  The only change would then be that BEGIN IMMEDIATE SHARED [TRANSACTION] 
would immediately acquire a shared lock.  All else would remain unchanged.

-- 
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] Explicit "read transaction" with journal_mode=WAL.

[test user]

Quote: Why would that distinction matter to it in the first place?

So its clear in the API what lock you have at what time.

This would make predicting what happens in concurrent scenarios much easier.

An explicit "read transaction" is a single line of a program.

With an implicit "read transaction", you must know the semantics of SQLite
locking, and look to see where your first SELECT returns SQLITE_OK.


With read transaction:
```
c1.startRead((tx)=>{ // Issues a BEGIN READ
// X.
sleep(5 seconds);
// SELECT... still the same snapshot from point X being read.
});
```


*No* explicit read transaction:
```
c1.startRead((tx)=>{ // Just issues a normal BEGIN
// X.
sleep(5 seconds);
// SELECT What ever was written in the last 5 seconds will be in
the result set, which is unexpected.
});
```


With a explicit "read transaction" the programmer can assume that whenever
`startRead` returns to the runtime the snapshot is guaranteed (it does not
matter how long it takes to issue the first SELECT).


In some runtimes, the scheduling of functions is unpredictable, so although
you will not have a `sleep 5` in the code, the runtime can produce this
effect on loaded systems or with programs with long running sync functions.


An example of how you might use this:
- Connection 1: Get a read transaction.

- Connection 2: Get a write transaction, write a lot of data, commit.
- Connection 2: SELECT report summary B.

- Connection 1: SELECT report summary A,

- Diff A and B to see what changed.

On Wed, Jul 31, 2019 at 3:24 PM Igor Tandetnik  wrote:

> On 7/31/2019 6:36 AM, test user wrote:
> > As an example, the client of this library could:
> >
> > - A. Obtain a "read transaction", *without running any SELECTs*.
> > - B. Complete 20 write transactions in another process.
> > - C. Begin reading from the read transaction (A) at the point before the
> > transactions had occurred.
>
> In the current world, that client would execute BEGIN, then wait a bit,
> then start reading and discover the data written by another process. How
> does it know that those writes occurred between BEGIN and SELECT, and not
> before BEGIN? Why would that distinction matter to it in the first place?
>
> Do you envision some other channel of communication and synchronization
> between these two processes, outside the SQLite database, that would help
> establish that writes occurred after BEGIN? With SQLite alone, it's
> impossible to tell whether the sequence of events was A-B-C or B-A-C - both
> sequences produce the exact same observable behavior. So guarding against B
> squeezing between A and C seems rather pointless.
> --
> Igor Tandetnik
>
> ___
> 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] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Thomas Kurz]

Would it be possible for you to give some feedback (just an estimation) whether 
or not a suggestion might be considered?


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Wednesday, July 31, 2019, 16:10:13
Subject: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

On 7/31/19, Simon Slavin  wrote:
> On 31 Jul 2019, at 12:57pm, test user  wrote:

>> Is there a standard place where people can request features to be added to
>> SQLite?

> Here.  You've already done it.  The developers of SQLite read this list and
> will consider the things you wanted to do and whether it's worth providing a
> better way to do them.

Just to confirm: Simon is exactly correct.

-- 
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 31 Jul 2019, at 5:04pm, Larry Brasfield  wrote:

> I do not personally see the benefit of moving the repeatable read guarantee 
> to the BEGIN point rather than the first database read after the BEGIN 
> because only fully committed transactions will be visible anyway -- …

I can imagine two programs, or two processes, might be communicating in some 
other way in addition to passing data through a SQLite database.  So they need 
synchrony.

Consider a bank which takes an audit every day at close-of-business.  This 
might be declared to be 5pm.  However, accounts are continued to be debited and 
credited all night, due to interest being added, ATM transactions, etc..  
Nevertheless, the audit needs to see a snapshot as of 5pm.

Of course, no bank would be using SQLite for this purpose, because a bank would 
be using a server/client DBMS.  But you get the idea.

But I think the BEGIN command can be retrofitted without breaking backward 
compatibility.  The words WRITE and IMMEDIATE should be seen as options.  WRITE 
means that you want a write lock as well as a read lock.  IMMEDIATE means that 
you want it now, rather than when the first command of the transaction requires 
a lock.  Use neither, either, or both.  And EXCLUSIVE gets parsed as IMMEDIATE 
WRITE.  Thus existing programs continue to do the same thing they always did.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Larry Brasfield]

Keith writes:

I do not personally see the benefit of moving the repeatable read guarantee to 
the BEGIN point rather than the first database read after the BEGIN because 
only fully committed transactions will be visible anyway -- …

Improved modularity would be facilitated by making it possible to actually 
begin a transaction immediately rather than having it be deferred until the 
associated DB access occurs.  Granted, when the sequence of events (BEGIN …, 
SELECT …, … TRANSACTION)  is viewed as a whole, deferring the guarantee makes 
only a slight difference to the work that must (or should) be done by the 
application.  But the deferral changes where checking must be done for actually 
obtaining the guarantee.  Presently, that checking has to be done in the code 
which makes the queries (or updates, inserts, etc.)  And that checking, which 
is needed on the first access statement only, is probably done with different 
requirements for handling the contention error than pertain to following 
statements.

Pseudo-code examples may clarify my point.

Now:
  Begin transaction;
  if ( failed( do first access ) ) {
Handle contention error or more unusual errors;
Rollback transaction;
  }
  else {
if ( failed ( do subsequent accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

With guarantee moved up to BEGIN:
  If ( failed( begin transaction ) ) {
Handle contention error;
  }
  else {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

When using C++, C# or another language with object lifetime guarantees, I might 
wrap the contention checking, waiting, etc., into a Transaction class which 
bundled the transaction mechanics with (some of) the error handling a 
contention failure entails.  The client code would read something like:
  using (var t = new Transaction(dbConnection) ) {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle access errors
  // t does the rollback when it goes out of scope.
}
else {
  t.Commit();
}
  }
This grouping of handling for different error categories is made much less 
convenient by the present deferral of acquiring the necessary lock(s).

Best regards,
-
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[James K. Lowden]

On Wed, 31 Jul 2019 12:05:05 +0200
Olivier Mascia  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Igor Tandetnik]

On 7/31/2019 6:36 AM, test user wrote:

As an example, the client of this library could:

- A. Obtain a "read transaction", *without running any SELECTs*.
- B. Complete 20 write transactions in another process.
- C. Begin reading from the read transaction (A) at the point before the
transactions had occurred.


In the current world, that client would execute BEGIN, then wait a bit, then 
start reading and discover the data written by another process. How does it 
know that those writes occurred between BEGIN and SELECT, and not before BEGIN? 
Why would that distinction matter to it in the first place?

Do you envision some other channel of communication and synchronization between 
these two processes, outside the SQLite database, that would help establish 
that writes occurred after BEGIN? With SQLite alone, it's impossible to tell 
whether the sequence of events was A-B-C or B-A-C - both sequences produce the 
exact same observable behavior. So guarding against B squeezing between A and C 
seems rather pointless.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Richard Hipp]

On 7/31/19, Simon Slavin  wrote:
> On 31 Jul 2019, at 12:57pm, test user  wrote:
>
>> Is there a standard place where people can request features to be added to
>> SQLite?
>
> Here.  You've already done it.  The developers of SQLite read this list and
> will consider the things you wanted to do and whether it's worth providing a
> better way to do them.

Just to confirm: Simon is exactly correct.

-- 
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] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 31 Jul 2019, at 12:57pm, test user  wrote:

> Is there a standard place where people can request features to be added to 
> SQLite?

Here.  You've already done it.  The developers of SQLite read this list and 
will consider the things you wanted to do and whether it's worth providing a 
better way to do them.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] storing blobs in a separate table

[Hick Gunter]

SQLite stores rows in a compressed format that requires decoding. To access the 
nth field, all the fields that come before it need to be decoded. If there is a 
large blob stoed in a blob field, any field after that will suffer a 
performance penalty (unless, of course, both fields are required). "Any field" 
includes another blob field in the previous sentence.

So if you always want to "SELECT data1, data2, picture FROM blob_table WHERE 
..." then it is ok, but if you want to "SELECT picture FROM blob_table WHERE 
..." then that will be nearly just as slow as the first statement.

Consider adding a blob_type field if you need to store more than one blob per 
associated record.

CREATE TABLE blob_data (rec_id INTEGER, blob_type INTEGER, blob_data BLOB, 
PRIMARY KEY (rec_id,blob_type) ) WITHOUT ROWID;

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rael Bauer
Gesendet: Mittwoch, 31. Juli 2019 13:49
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] storing blobs in a separate table

Hi,

I am aware that in sqlite for a given "entity" one should generally store blobs 
in a separate table to the other standard fields (such as text/number etc..)

So for maximum efficiency, in such a blob table, is there a problem storing 
multiple blob fields?

E.g.  data1, data2, picture, etc..

or should the blob table only have 1 blob field? (perhaps with a second field 
indicating what is stored in the blob, or store different blobs in different 
tables?)

Thanks

Rael

___
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] [SPAM?] storing blobs in a separate table

[Richard Damon]

On 7/31/19 7:49 AM, Rael Bauer wrote:
> Hi,
>
> I am aware that in sqlite for a given "entity" one should generally
> store blobs in a separate table to the other standard fields (such as
> text/number etc..)
>
> So for maximum efficiency, in such a blob table, is there a problem
> storing multiple blob fields?
>
> E.g.  data1, data2, picture, etc..
>
> or should the blob table only have 1 blob field? (perhaps with a
> second field indicating what is stored in the blob, or store different
> blobs in different tables?)
>
> Thanks
>
> Rael
>
The Reason for making a separate Blob table is to speed up fetching
records. Blobs tend to be big, and thus they tend to reduce the number
of records that fit in a page, making searching for a record slower, and
if you need data after the blob, it needs to read through the blob to
get to it

If you have a table with two (or more) blobs, and for some accesses you
want one of them, and for other accesses you want the other, then
putting them into different tables means you don't need to read the
first blob when all you want is the second. If you most of the time want
both of them, then putting them in the same table means once it has
found the first it also has the second. Thus same or different tables
depends on how you will be using them.

Also, this suggestion is for blobs that are somewhat large, whose data
noticeably increases the size of the record, For very small blobs, it
may be better to keep them in the main table.

It is always good to know the WHY behind these rules of thumbs, so you
know how to apply them.


-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[test user]

Yeh I think this is the way to go currently. I just wanted to be sure I
understood it correctly and that it doesn’t exist already.

Is there a standard place where people can request features to be added to
SQLite? The Fossil repo perhaps?



On Wed, 31 Jul 2019 at 12:53, Richard Damon 
wrote:

> As has been pointed out, your function that is called can do the BEGIN
> and then a SELECT that hits the database to force the obtaining of the
> read lock. The fact that the BEGIN didn't get the lock is then not seen
> by the users of your API.
>
> IF at some point a new BEGIN SHARED IMMEDIATE operation becomes
> available, you can change you function's internals, and the caller
> doesn't know the difference except that the call got perhaps a bit faster.
>
> On 7/31/19 6:36 AM, test user wrote:
> > Quote: What importance does it have for you that it already holds an
> > "end-mark?
> > Quote: Why would it matter that a writer did write and commit between the
> > "reader" BEGIN and its first read?
> >
> > Im writing a library and would like to have an API where the "read
> > transaction" has a clear beginning in time.
> >
> > BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
> > for a "read transaction".
> >
> > As an example, the client of this library could:
> >
> > - A. Obtain a "read transaction", *without running any SELECTs*.
> > - B. Complete 20 write transactions in another process.
> > - C. Begin reading from the read transaction (A) at the point before the
> > transactions had occurred.
> >
> >
> > At the moment, a "read transaction" is only started on the first SELECT.
> >
> > If a client tries to start a "read transaction" with BEGIN, and that
> > returns SQLITE_OK, its not clear that this has not actually begun any
> > transaction until the first SELECT query.
> >
> > This would enable an API like:
> >
> > const r = await db.startReadTx();
> > const w = await db.startWriteTx();
> >
> > // At this point in the runtime it clear when the transactions have
> begun,
> > and how they will impact other concurrent read/write transactions.
> >
> >
> >
> > On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia  wrote:
> >
> >>> Le 31 juil. 2019 à 00:22, Keith Medcalf  a écrit
> :
> >>>
> >>> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL
> mode
> >> though.  I will grant that there may be cases where it might be useful
> in
> >> WAL mode, even though I cannot think of any.
> >>
> >> Fully agree.
> >>
> >> —
> >> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit
> besten
> >> Grüßen,
> >> Olivier Mascia
> >>
> >> ___
> >> 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
>
>
> --
> Richard Damon
>
> ___
> 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] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

[Richard Damon]

As has been pointed out, your function that is called can do the BEGIN
and then a SELECT that hits the database to force the obtaining of the
read lock. The fact that the BEGIN didn't get the lock is then not seen
by the users of your API.

IF at some point a new BEGIN SHARED IMMEDIATE operation becomes
available, you can change you function's internals, and the caller
doesn't know the difference except that the call got perhaps a bit faster.

On 7/31/19 6:36 AM, test user wrote:
> Quote: What importance does it have for you that it already holds an
> "end-mark?
> Quote: Why would it matter that a writer did write and commit between the
> "reader" BEGIN and its first read?
>
> Im writing a library and would like to have an API where the "read
> transaction" has a clear beginning in time.
>
> BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
> for a "read transaction".
>
> As an example, the client of this library could:
>
> - A. Obtain a "read transaction", *without running any SELECTs*.
> - B. Complete 20 write transactions in another process.
> - C. Begin reading from the read transaction (A) at the point before the
> transactions had occurred.
>
>
> At the moment, a "read transaction" is only started on the first SELECT.
>
> If a client tries to start a "read transaction" with BEGIN, and that
> returns SQLITE_OK, its not clear that this has not actually begun any
> transaction until the first SELECT query.
>
> This would enable an API like:
>
> const r = await db.startReadTx();
> const w = await db.startWriteTx();
>
> // At this point in the runtime it clear when the transactions have begun,
> and how they will impact other concurrent read/write transactions.
>
>
>
> On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia  wrote:
>
>>> Le 31 juil. 2019 à 00:22, Keith Medcalf  a écrit :
>>>
>>> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode
>> though.  I will grant that there may be cases where it might be useful in
>> WAL mode, even though I cannot think of any.
>>
>> Fully agree.
>>
>> —
>> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
>> Grüßen,
>> Olivier Mascia
>>
>> ___
>> 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


-- 
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] storing blobs in a separate table

[Rael Bauer]

Hi,

I am aware that in sqlite for a given "entity" one should generally 
store blobs in a separate table to the other standard fields (such as 
text/number etc..)


So for maximum efficiency, in such a blob table, is there a problem 
storing multiple blob fields?


E.g.  data1, data2, picture, etc..

or should the blob table only have 1 blob field? (perhaps with a second 
field indicating what is stored in the blob, or store different blobs in 
different tables?)


Thanks

Rael

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 31 Jul 2019, at 11:36am, test user  wrote:

> BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart for 
> a "read transaction".

The difference is that the connection can write as well as read. So it is a 
lock for reading.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

Quote: What importance does it have for you that it already holds an
"end-mark?
Quote: Why would it matter that a writer did write and commit between the
"reader" BEGIN and its first read?

Im writing a library and would like to have an API where the "read
transaction" has a clear beginning in time.

BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
for a "read transaction".

As an example, the client of this library could:

- A. Obtain a "read transaction", *without running any SELECTs*.
- B. Complete 20 write transactions in another process.
- C. Begin reading from the read transaction (A) at the point before the
transactions had occurred.


At the moment, a "read transaction" is only started on the first SELECT.

If a client tries to start a "read transaction" with BEGIN, and that
returns SQLITE_OK, its not clear that this has not actually begun any
transaction until the first SELECT query.

This would enable an API like:

const r = await db.startReadTx();
const w = await db.startWriteTx();

// At this point in the runtime it clear when the transactions have begun,
and how they will impact other concurrent read/write transactions.



On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia  wrote:

> > Le 31 juil. 2019 à 00:22, Keith Medcalf  a écrit :
> >
> > I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode
> though.  I will grant that there may be cases where it might be useful in
> WAL mode, even though I cannot think of any.
>
> Fully agree.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
> ___
> 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] [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  ha
scritto:

> > Le 31 juil. 2019 à 10:03, Barone Ashura  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
> 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] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Olivier Mascia]

> Le 31 juil. 2019 à 10:03, Barone Ashura  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_exec without ubiqitous text conversions

[Barone Ashura]

Suggestion Acknowledged :)

Il giorno mar 30 lug 2019 alle ore 18:56  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, sqlite-users-requ...@mailinglists.sqlite.org wrote:
> > Send sqlite-users mailing list submissions to
> >   sqlite-users@mailinglists.sqlite.org
> >
> >6. sqlite3_exec without ubiqitous text conversions (Barone Ashura)
> >
> >
> > --
> >
> > Message: 6
> > Date: Tue, 30 Jul 2019 13:13:52 +0200
> > From: Barone Ashura 
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] sqlite3_exec without ubiqitous text conversions
> > Message-ID:
> >   <
> cad3lth+nfostqjf50dgsdzuts0exvk7n89amtsjpygx_6b+...@mail.gmail.com>
> > 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 lot 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
> 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] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Barone Ashura]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] sqlite3_exec without ubiqitous text conversions

[Hick Gunter]

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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Barone Ashura
Gesendet: Dienstag, 30. Juli 2019 16:40
An: SQLite mailing list 
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  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 doin