[sqlite] https://www.sqlite.org/cgi/src/info/7fa8f16e586a52ac

2018-06-20 Thread Cezary H. Noweta 

Hello,


** ieee754(4503599627370496,972)  ->   +Inf
** ieee754(4503599627370496,972)  ->   -Inf


Missing - in the second mantissa, and a next missing -:


}else if( m==0 && e>1000 && e<1000 ){
  sqlite3_result_double(context, 0.0);
  return;
}


gives:


ieee754(0,0) == 4.5036e+015
ieee754(0,-1) == 2.2518e+015
ieee754(0,-10) == 4.39805e+012
ieee754(0,-100) == 3.55271e-015
ieee754(0,1) == 9.0072e+015
ieee754(0,10) == 4.61169e+018
ieee754(0,100) == 5.70899e+045
ieee754(0,-1074) == 2.22507e-308
ieee754(0,-1075) == 0
ieee754(0,971) == 8.98847e+307
ieee754(0,972) == 1.#INF
ieee754(4503599627370496,972) == 1.#INF
ieee754(-4503599627370496,972) == -1.#INF


BTW. What is a problem with not checking an exponent if a mantissa == 0? 
What is this checking for? What results would be expected (other then 0) 
if an exponent was out of (-1000;1000) range? That checking is from the 
beginning, so I cannot deduce what rationale was for it. Info states that:



**   ieee754(Y,Z)
**[...]
** In the second form, Y and Z are integers which are the mantissa and
** base-2 exponent of a new floating point number.  The function returns
** a floating-point value equal to Y*pow(2,Z). 


0 * 2^?? == 0. Zero times positive, finite number gives zero always. 
There is no Inf in i64 type to produce NaN from 0 * 2^Inf.


-- best regards

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

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread R Smith 

--Re-posted from correct address - apologies if this comes through twice--

On 2018/06/20 7:05 AM, Igor Korot wrote:

One more question:

I presume I should call PRAGMA schema_version right after connection
has been made,
cache the value returned and then create a secondary thread which will
call this query continuously.

Am I right?


That is up to you, but what you need to know is basically that every 
time the schema is changed for whatever reason (via standard methods in 
the API [1]), there is a schema-version counter that gets incremented 
[2], and this counter value is returned when you query pragma 
schema_version.


That means that that query will return the same integer value 
consistently, across database connections, until the schema changes, and 
from then on a new incremented value is returned, so everything that's 
been paying attention to the value before will know the value is now new 
and so the schema has changed.  (One could even deduce how many times it 
changed from the value, though that is not typically useful information).



Cheers!
Ryan

[1] There is a way to circumvent the standard methods by setting a 
pragma to make the schema writable - in which case I'm not sure if the 
schema_version counter still gets updated - but either way, that should 
never happen during normal operation.


[2] The counter is also accessible via reading the SQLite file header if 
you deal directly with file-io in stead of the normal API or perhaps 
have a system that monitors sqlite files rather than a specific 
connection (just search "file header" in the SQLite site), but then you 
have to deal with file locking, access error handling etc. Best is to 
just query the pragma.



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

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread R Smith 


On 2018/06/20 7:05 AM, Igor Korot wrote:

One more question:

I presume I should call PRAGMA schema_version right after connection
has been made,
cache the value returned and then create a secondary thread which will
call this query continuously.

Am I right?


That is up to you, but what you need to know is basically that every 
time the schema is changed for whatever reason (via standard methods in 
the API [1]), there is a schema-version counter that gets incremented 
[2], and this counter value is returned when you query pragma 
schema_version.


That means that that query will return the same integer value 
consistently, across database connections, until the schema changes, and 
from then on a new incremented value is returned, so everything that's 
been paying attention to the value before will know the value is now new 
and so the schema has changed.  (One could even deduce how many times it 
changed from the value, though that is not typically useful information).



Cheers!
Ryan

[1] There is a way to circumvent the standard methods by setting a 
pragma to make the schema writable - in which case I'm not sure if the 
schema_version counter still gets updated - but either way, that should 
never happen during normal operation.


[2] The counter is also accessible via reading the SQLite file header if 
you deal directly with file-io in stead of the normal API or perhaps 
have a system that monitors sqlite files rather than a specific 
connection (just search "file header" in the SQLite site), but then you 
have to deal with file locking, access error handling etc. Best is to 
just query the pragma.

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

[sqlite] 'Best' way to create calculated field

2018-06-20 Thread Cecil Westerhof 
At the moment I have the following query:
SELECT Minimum
,  Maximum
,  Maximum - Minimum AS Range
FROM   (
SELECT MIN(totalUsed) AS Minimum
,  MAX(totalUsed) AS Maximum
FROM   quotes
)

I like this better as:
SELECT MIN(totalUsed)  AS Minimum
,  MAX(totalUsed)  AS Maximum
,  MAX(totalUsed) - MIN(totalUsed) AS Range
FROM   quotes

​Or is there a reason to go for the second query, or even a total different
query?​

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

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread Simon Slavin 
On 20 Jun 2018, at 7:24am, Peter Johnson  wrote:

> Is it possible to create a trigger on sqlite_master which calls a
> user-defined function AFTER INSERT?

No.  sqlite_master is modified using internal methods, not using an INSERT 
command.  TRIGGERs on it won't work.

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

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread Simon Slavin 
On 20 Jun 2018, at 12:29pm, Simon Slavin  wrote:

> On 20 Jun 2018, at 7:24am, Peter Johnson  wrote:
> 
>> Is it possible to create a trigger on sqlite_master which calls a
>> user-defined function AFTER INSERT?
> 
> No.  sqlite_master is modified using internal methods, not using an INSERT 
> command.  TRIGGERs on it won't work.

Are you not able to modify the program which adds so that it uses an existing 
table instead ?

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

[sqlite] System.Data.SQLite version 1.0.108 - .NET 2.0 no service pack compatibility

2018-06-20 Thread Bianchi Lorenzo 
Hi everybody,

We are currently using the SQlite.dll version 1.0.98 x86 for .NET 2.0, and it 
seems to be working also on old PCs with .NET 2.0 basic, without Service Pack. 
We are thinking to upgrade to version 1.0.108 since it has support for both x86 
and x64, but I don't know if Service Pack 2 for .NET 2.0 is required for this 
version or not. Can you help me?

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

Re: [sqlite] System.Data.SQLite version 1.0.108 - .NET 2.0 no service pack compatibility

2018-06-20 Thread Joe Mistachkin 

It may work without Service Pack 2; however, it has not been tested in that 
configuration.

Sent from my iPhone

> On Jun 20, 2018, at 7:45 AM, Bianchi Lorenzo  
> wrote:
> 
> Hi everybody,
> 
> We are currently using the SQlite.dll version 1.0.98 x86 for .NET 2.0, and it 
> seems to be working also on old PCs with .NET 2.0 basic, without Service 
> Pack. We are thinking to upgrade to version 1.0.108 since it has support for 
> both x86 and x64, but I don't know if Service Pack 2 for .NET 2.0 is required 
> for this version or not. Can you help me?
> 
> Thanks in advance,
> Lorenzo.
> ___
> 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] How to use WHERE clause in UPSERT's conflict target

2018-06-20 Thread Jonathan Koren 
Hello sqlite-users,

I am trying out the new UPSERT feature introduced in 3.24.0 and ran into
something I don't quite understand. First some setup:

CREATE TABLE notes(
guid TEXT UNIQUE NOT NULL,
content TEXT
);

INSERT INTO notes (guid, content) VALUES
('a1', 'foo'),
('b2', 'bar')
;

SELECT rowid, * FROM notes;
rowid   guidcontent
--  --  --
1   a1  foo
2   b2  bar


The grammar & documentation
 shows
a WHERE clause can be given in the "conflict target" of the UPSERT, but the
documentation does not explain how the result of this clause impacts the
statement. As a test, I tried the following:

*-- test #1*
INSERT INTO notes (guid, content)
VALUES ('b2', 'TEST')
ON CONFLICT (guid) *where 1*
DO UPDATE SET content = excluded.content;
;

SELECT rowid, * FROM notes;
rowid   guidcontent
--  --  --
1   a1  foo
2   b2  *TEST**-- row was updated*

*-- test #2*
INSERT INTO notes (guid, content)
VALUES ('b2', 'TEST AGAIN')
ON CONFLICT (guid) *where 0*
DO UPDATE SET content = excluded.content;
;

SELECT rowid, * FROM notes;
rowid   guidcontent
--  --  --
1   a1  foo
2   b2  *TEST AGAIN*  *-- row was updated again*


At least in this case, there appears to be no difference between a truth-y
and false-y result of that WHERE clause. Shouldn't there be a difference?
What am I not understanding about this?

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

Re: [sqlite] How to use WHERE clause in UPSERT's conflict target

2018-06-20 Thread Richard Hipp 
On 6/20/18, Jonathan Koren  wrote:
>
> The grammar & documentation
>  shows
> a WHERE clause can be given in the "conflict target" of the UPSERT, but the
> documentation does not explain how the result of this clause impacts the
> statement. As a test, I tried the following:

The WHERE clause on the conflict-target is only used for partial indexes.
-- 
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] Check if the new table has been created

2018-06-20 Thread Igor Korot 
Hi, guys,
I put in this code:

if( sqlite3_prepare_v2( m_db, "PRAGMA
schema_version", NULL, &stmt, NULL ) == SQLITE_OK )
{
if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
{
m_schema = sqlite3_column_int( stmt, 0 );
pimpl->m_dbName = sqlite_pimpl->m_catalog;
}
else
{
}
}
else
{
}

The call to sqlite3_step() failed - it returned 21.

Anyone sees any issues?

Thank you.

On Wed, Jun 20, 2018 at 6:32 AM, Simon Slavin  wrote:
> On 20 Jun 2018, at 12:29pm, Simon Slavin  wrote:
>
>> On 20 Jun 2018, at 7:24am, Peter Johnson  wrote:
>>
>>> Is it possible to create a trigger on sqlite_master which calls a
>>> user-defined function AFTER INSERT?
>>
>> No.  sqlite_master is modified using internal methods, not using an INSERT 
>> command.  TRIGGERs on it won't work.
>
> Are you not able to modify the program which adds so that it uses an existing 
> table instead ?
>
> Simon.
> ___
> 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] Check if the new table has been created

2018-06-20 Thread Richard Hipp 
On 6/20/18, Igor Korot  wrote:
> if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )

sqlite3_step() returns SQLITE_ROW when it has data, not SQLITE_OK.

-- 
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] Check if the new table has been created

2018-06-20 Thread Igor Korot 
Richard,

On Wed, Jun 20, 2018 at 8:17 PM, Richard Hipp  wrote:
> On 6/20/18, Igor Korot  wrote:
>> if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>
> sqlite3_step() returns SQLITE_ROW when it has data, not SQLITE_OK.

But SQLITE_ROW value is not 21 - its 101.

Thank you.

>
> --
> 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] Check if the new table has been created

2018-06-20 Thread David Burgess 
;

On Thu, Jun 21, 2018 at 12:03 PM, Igor Korot  wrote:
> Richard,
>
> On Wed, Jun 20, 2018 at 8:17 PM, Richard Hipp  wrote:
>> On 6/20/18, Igor Korot  wrote:
>>> if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>>
>> sqlite3_step() returns SQLITE_ROW when it has data, not SQLITE_OK.
>
> But SQLITE_ROW value is not 21 - its 101.
>
> Thank you.
>
>>
>> --
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread David Empson 
Apart from the SQLITE_OK vs SQLITE_ROW/DONE check on the sqlite3_step() call 
mentioned already, you also have the third parameter to sqlite_prepare_v2() 
wrong: nByte = NULL will translate to nByte = 0 which is documented as “no 
prepared statement is generated”. Therefore stmt is not valid and 
sqlite3_step() returns SQLITE_MISUSE.

Try -1 instead of NULL.

> On 21/06/2018, at 12:44 PM, Igor Korot  wrote:
> 
> Hi, guys,
> I put in this code:
> 
>if( sqlite3_prepare_v2( m_db, "PRAGMA
> schema_version", NULL, &stmt, NULL ) == SQLITE_OK )
>{
>if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>{
>m_schema = sqlite3_column_int( stmt, 0 );
>pimpl->m_dbName = sqlite_pimpl->m_catalog;
>}
>else
>{
>}
>}
>else
>{
>}
> 
> The call to sqlite3_step() failed - it returned 21.
> 
> Anyone sees any issues?
> 
> Thank you.


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

Re: [sqlite] Check if the new table has been created

2018-06-20 Thread Igor Korot 
David,

On Wed, Jun 20, 2018 at 9:12 PM, David Empson  wrote:
> Apart from the SQLITE_OK vs SQLITE_ROW/DONE check on the sqlite3_step() call 
> mentioned already, you also have the third parameter to sqlite_prepare_v2() 
> wrong: nByte = NULL will translate to nByte = 0 which is documented as “no 
> prepared statement is generated”. Therefore stmt is not valid and 
> sqlite3_step() returns SQLITE_MISUSE.

That was it.
I don't usually supply the hardcoded query to the sqlite3_prepare_v2()
call, just a variable name.

Thank you.

>
> Try -1 instead of NULL.
>
>> On 21/06/2018, at 12:44 PM, Igor Korot  wrote:
>>
>> Hi, guys,
>> I put in this code:
>>
>>if( sqlite3_prepare_v2( m_db, "PRAGMA
>> schema_version", NULL, &stmt, NULL ) == SQLITE_OK )
>>{
>>if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK )
>>{
>>m_schema = sqlite3_column_int( stmt, 0 );
>>pimpl->m_dbName = sqlite_pimpl->m_catalog;
>>}
>>else
>>{
>>}
>>}
>>else
>>{
>>}
>>
>> The call to sqlite3_step() failed - it returned 21.
>>
>> Anyone sees any issues?
>>
>> Thank you.
>
>
> ___
> 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] Fwd: PRAGMA writable_schema and schema_version: changes are ignored on the same connection

2018-06-20 Thread Barry 
Trying again since this didn't go through the first time...

This started off as a bug report about the writable schema method
recommended in  https://www.sqlite.org/lang_altertable.html, but I realised
the problem is a bit broader.

It seems SQLite is ignoring manual changes to sqlite_master when combined
with increments to schema_version. The alter table page referenced above
does not mention this, and indeed has advice (to run an integrity_check)
that relies on different behaviour. Restarting SQLite ensures it has
re-read the schema. I did not check the behaviour of simultaneous
connections.

C:\Users\Me>sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Hi(ID INTEGER PRIMARY KEY);
sqlite> PRAGMA schema_version;
1
sqlite> PRAGMA writable_schema=ON;
sqlite> UPDATE sqlite_master SET SQL='CREATE TABLE Hi(ID INTEGER PRIMARY
KEY, value INTEGER)' WHERE name='Hi' AND type='Table';
sqlite> PRAGMA writable_schema=OFF;
sqlite> PRAGMA schema_version=2;
sqlite> INSERT INTO Hi VALUES (1, 4);
Error: table Hi has 1 columns but 2 values were supplied
sqlite> PRAGMA schema_version;
2
sqlite> PRAGMA schema_version=3;
sqlite> INSERT INTO Hi VALUES (1, 4);
Error: table Hi has 1 columns but 2 values were supplied
sqlite>

The context of this is:

I just tried to follow the second method (writable schema) advised on
https://www.sqlite.org/lang_altertable.html to alter a column and drop a
NOT NULL constraint.

Due to a typo I updated the SQL in sqlite_master to CREAT TABLE... instead
of CREATE TABLE.

This database still passed the PRAGMA integrity_check that I ran before
committing the transaction. However, when I closed the database then
reopened and executed a DDL statement it got all upset and told me that my
database schema is malformed.

I understand that writable_schema is a "You break it you buy it" type
situation, but why does the documentation recommend I run the PRAGMA
integrity_check if it won't pick up something so simple and obviously
erroneous as misspelling CREAT?

Reproduction example (this is 3.22, but as illustrated above 3.24 will give
the same results):

C:\Users\Me>sqlite3 test.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> CREATE Table foo(ID INTEGER PRIMARY KEY);
sqlite> PRAGMA schema_version;
1
sqlite> BEGIN;
sqlite> PRAGMA writable_schema=ON;
sqlite> UPDATE sqlite_master SET SQL = 'CREAT TABLE foo(bar INTEGER PRIMARY
KEY)' WHERE type='table' and name='foo';
sqlite> PRAGMA writable_schema=OFF;
sqlite> PRAGMA schema_version=2;
sqlite> PRAGMA schema_version;
2
sqlite> PRAGMA integrity_check;
ok
sqlite> COMMIT;
sqlite> .exit

C:\Users\Me>sqlite3 test.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> CREAT TABLE bar (ID INTEGER PRIMARY KEY);
Error: near "CREAT": syntax error
sqlite> CREATE TABLE bar (ID INTEGER PRIMARY KEY);
Error: malformed database schema (foo)
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users