Re: [sqlite] converting unix10 and unix13 dates in the same column

[David Raymond]

When you have a base expression in the CASE, then it compares each of the WHEN 
values to that base value.
So in your situation there you have it written like

if dt = (unix10and13.dt < 100)
then...
if dt = (unix10and13.dt > 100)
then...

I think if you get rid of the dt immediately after CASE then you should be good 
to go on that part.

For the second part I think it would be something like

select case when unix10and13.dt < 100 then DateTime(unix10and13.dt, 
'unixepoch') else null end as unix10,
case when unix10and13.dt > 100 then DateTime(unix10and13.dt / 1000, 
'unixepoch') else null end as unix13
from unix10and13;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Paul Sanderson
Sent: Thursday, September 29, 2016 9:14 AM
To: General Discussion of SQLite Database
Subject: [sqlite] converting unix10 and unix13 dates in the same column

I have a table with dates in different formats, either 10 digit or 13
digit unix dates

1234345087123
1234567890
1432101234
1456754323012

I want a sql query that will convert both dates, I tried this

SELECT CASE dt
  WHEN (unix10and13.dt < 100)
 THEN DateTime(unix10and13.dt, 'unixepoch')
  WHEN (unix10and13.dt > 100)
THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
  ELSE dt
  END AS converted
FROM unix10and13

But this returns the original values - i.e. the else portion is being
evaluated but one of the previous expressions should evaluate to true
surely? Any ideas why this is failing?



I am also interested (because I tried and failed) in coding a second
query that would return all four rows but in two columns each with
either a unix10 or 13 date in the correct column - something like
this:

unix10,  unix13
   ,1234345087123
1234567890,
1432101234,
   ,1456754323012

any suggestions to achieve this approach?



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
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] converting unix10 and unix13 dates in the same column

[Paul Sanderson]

I have a table with dates in different formats, either 10 digit or 13
digit unix dates

1234345087123
1234567890
1432101234
1456754323012

I want a sql query that will convert both dates, I tried this

SELECT CASE dt
  WHEN (unix10and13.dt < 100)
 THEN DateTime(unix10and13.dt, 'unixepoch')
  WHEN (unix10and13.dt > 100)
THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
  ELSE dt
  END AS converted
FROM unix10and13

But this returns the original values - i.e. the else portion is being
evaluated but one of the previous expressions should evaluate to true
surely? Any ideas why this is failing?



I am also interested (because I tried and failed) in coding a second
query that would return all four rows but in two columns each with
either a unix10 or 13 date in the correct column - something like
this:

unix10,  unix13
   ,1234345087123
1234567890,
1432101234,
   ,1456754323012

any suggestions to achieve this approach?



Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] converting unix10 and unix13 dates in the same column

[Keith Medcalf]

You query is incorrect.  It should be:

SELECT CASE
  WHEN (unix10and13.dt < 100)
 THEN DateTime(unix10and13.dt, 'unixepoch')
  WHEN (unix10and13.dt > 100)
THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
  ELSE dt
  END AS converted
FROM unix10and13;

When your case, you are using the CASE  WHEN  ...

So, the THEN clauses are comparing the value of DT to the result on (dt < 
100) or (dt > 100).  The results of the expression are always 1 
or 0, which never equals DT, so the THEN clause is never executed and the ELSE 
is always taken.


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Thursday, 29 September, 2016 07:14
> To: General Discussion of SQLite Database
> Subject: [sqlite] converting unix10 and unix13 dates in the same column
> 
> I have a table with dates in different formats, either 10 digit or 13
> digit unix dates
> 
> 1234345087123
> 1234567890
> 1432101234
> 1456754323012
> 
> I want a sql query that will convert both dates, I tried this
> 
> SELECT CASE dt
>   WHEN (unix10and13.dt < 100)
>  THEN DateTime(unix10and13.dt, 'unixepoch')
>   WHEN (unix10and13.dt > 100)
> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
>   ELSE dt
>   END AS converted
> FROM unix10and13
> 
> But this returns the original values - i.e. the else portion is being
> evaluated but one of the previous expressions should evaluate to true
> surely? Any ideas why this is failing?
> 
> 
> 
> I am also interested (because I tried and failed) in coding a second
> query that would return all four rows but in two columns each with
> either a unix10 or 13 date in the correct column - something like
> this:
> 
> unix10,  unix13
>,1234345087123
> 1234567890,
> 1432101234,
>,1456754323012
> 
> any suggestions to achieve this approach?
> 
> 
> 
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> ___
> 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] converting unix10 and unix13 dates in the same column

[Paul Sanderson]

All sorted now thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] converting unix10 and unix13 dates in the same column

[Tim Streater]

On 29 Sep 2016 at 14:14, Paul Sanderson  wrote: 

> I have a table with dates in different formats, either 10 digit or 13
> digit unix dates
>
> 1234345087123
> 1234567890
> 1432101234
> 1456754323012

Are these strings or numbers? What is your SQL to get these into the table and 
what is the schema for dt?

(Me, I convert all dates into seconds-since-the-epoch, never had a problem).



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

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

[Dominique Devienne]

On Thu, Sep 29, 2016 at 2:37 PM, James K. Lowden 
wrote:

> On Fri, 23 Sep 2016 16:35:07 +
> Quan Yong Zhai  wrote:
>
> > Quote <<
> > A "row value" is an ordered list of two or more scalar values. In
> > other words, a "row value" is a vector.>>
> >
> > A ?row value? is a tuple, not a vector. When your using a tuple, you
> > know how many items in it, and the type of each item of it.
>
> That's correct, and addresses Dominique's point, albeit obliquely:
> to use a row-value as a list-argument to IN would be to confuse rows
> and columns.  A row-value has 1 or more columns, but only one row.
>
> It would be nice to use row-values correctly in IN:
>
> select * from T where (a,b) IN ( (1, 'a'), (2, 'b') )
>
> Is that valid?
>

After reading http://modern-sql.com/use-case/select-without-from
and given
http://sqlite.org/draft/rowvalue.html#search_against_multi_column_keys
it seems to me that it should be, albeit with this tiny variation:

  select * from T where (a,b) IN ( values (1, 'a'), (2, 'b') )

But not tested yet. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] converting unix10 and unix13 dates in the same column

[Paul Sanderson]

ah OK - being dull thank you
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 29 September 2016 at 14:29, Keith Medcalf  wrote:
> You query is incorrect.  It should be:
>
> SELECT CASE
>   WHEN (unix10and13.dt < 100)
>  THEN DateTime(unix10and13.dt, 'unixepoch')
>   WHEN (unix10and13.dt > 100)
> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
>   ELSE dt
>   END AS converted
> FROM unix10and13;
>
> When your case, you are using the CASE  WHEN  ...
>
> So, the THEN clauses are comparing the value of DT to the result on (dt < 
> 100) or (dt > 100).  The results of the expression are always 
> 1 or 0, which never equals DT, so the THEN clause is never executed and the 
> ELSE is always taken.
>
>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Paul Sanderson
>> Sent: Thursday, 29 September, 2016 07:14
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] converting unix10 and unix13 dates in the same column
>>
>> I have a table with dates in different formats, either 10 digit or 13
>> digit unix dates
>>
>> 1234345087123
>> 1234567890
>> 1432101234
>> 1456754323012
>>
>> I want a sql query that will convert both dates, I tried this
>>
>> SELECT CASE dt
>>   WHEN (unix10and13.dt < 100)
>>  THEN DateTime(unix10and13.dt, 'unixepoch')
>>   WHEN (unix10and13.dt > 100)
>> THEN DateTime(unix10and13.dt / 1000, 'unixepoch')
>>   ELSE dt
>>   END AS converted
>> FROM unix10and13
>>
>> But this returns the original values - i.e. the else portion is being
>> evaluated but one of the previous expressions should evaluate to true
>> surely? Any ideas why this is failing?
>>
>>
>>
>> I am also interested (because I tried and failed) in coding a second
>> query that would return all four rows but in two columns each with
>> either a unix10 or 13 date in the correct column - something like
>> this:
>>
>> unix10,  unix13
>>,1234345087123
>> 1234567890,
>> 1432101234,
>>,1456754323012
>>
>> any suggestions to achieve this approach?
>>
>>
>>
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786
>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
>> Toolkit
>> -Forensic Toolkit for SQLite
>> email from a work address for a fully functional demo licence
>> ___
>> 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] SQLite 3.15.0 scheduled for 2016-10-14

[Dominique Devienne]

On Thu, Sep 22, 2016 at 9:43 PM, Darren Duncan 
wrote:

> On 2016-09-22 12:16 PM, Petite Abeille wrote:
>
>>
>> On Sep 22, 2016, at 9:04 PM, Richard Hipp  wrote:
>>>
>>> (https://www.sqlite.org/draft/releaselog/3_15_0.html).
>>>
>>
>> Oh! Row Values! Nice! :)
>>
>> https://www.sqlite.org/draft/rowvalue.html
>>
>
> I second that, its a valuable feature to have.
>

For those interested, I ran by chance on this article that might have been
the origin of row values in SQLite:

From
http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference
:

> We also discussed whether or not SQLite can cope with search conditions
> like(col1, col2) < (val1, val2)—it can’t.
> Here Richard was questioning the motivation to have that, so I gave him an
> elevator-pitch version of my
> “Pagination Done The PostgreSQL Way” talk. I think he got “excited” about
> this concept to avoid offset
> at all and I’m curious to see if he can make it work in SQLite faster than
> I’m adding SQLite content to Use The Index, Luke:)


That was 2 years ago :).--DD

PS: “Pagination Done The PostgreSQL Way” talk:
http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way
http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] converting unix10 and unix13 dates in the same column

[Keith Medcalf]

Note that if you want the extra precision to show as fractional seconds you 
have to ensure floating point is passed (particularly if column dt is of 
integer affinity) and use strftime() rather than datetime() so you can specify 
the format string explicitly using %f to get SS.SSS.  

Datetime() uses %S for the seconds part which ignores the fractional seconds.

SELECT CASE
  WHEN (unix10and13.dt < 100)
 THEN strftime('%Y-%m-%d %H:%M:%f', unix10and13.dt, 'unixepoch')
  WHEN (unix10and13.dt > 100)
THEN strftime('%Y-%m-%d %H:%M:%f', unix10and13.dt / 1000.0, 'unixepoch')
  ELSE dt
  END AS converted
FROM unix10and13;

2009-02-11 09:38:07.123
2009-02-13 23:31:30.000
2015-05-20 05:53:54.000
2016-02-29 13:58:43.012

Personally I would also use '%Y-%m-%d %H:%M:%f Z' for the format string as well 
to make sure that it cannot be misinterpreted down the line -- most parsers 
will be able to properly handle strings of the format '2009-02-11 09:38:07.123 
Z' -- but it is the humans reading the string that might get confused too.

Paul didn't ask these questions ... but just for completeness.

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Thursday, 29 September, 2016 07:51
> To: SQLite mailing list
> Subject: Re: [sqlite] converting unix10 and unix13 dates in the same
> column
> 
> All sorted now 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] Regarding the effects of the COMMIT keyword

[Jim Borden]

I found the following snippet from https://www.sqlite.org/lockingv3.html
 
➢ The SQL command "COMMIT" does not actually commit the changes to disk. It 
just turns autocommit back on. Then, at the conclusion of the command, the 
regular autocommit logic takes over and causes the actual commit to disk to 
occur.
 
Does that mean that the actual commit happens after the execution of “COMMIT” 
(i.e. it is in a sense asynchronous)?

Could that explain some odd behavior that I am seeing?  I have two connections 
to a database file and reports that writing to one and then immediately 
querying from another causes the second connection to show the entry as 
missing.  I was working under the assumption that after COMMIT finished that 
everything would be visible to all connections but is this a false assumption?  
The suspiciousness is also due in part to this only happening when:
 
1)   There are more than ~1200 entries in the table
2)   The conclusion of the write and the read happen <= ~500ms apart (not 
very accurate timing, going by log timestamps)
 
Further queries after the initial one return the entry as expected.  Also, if 
either of the above conditions are not met then even the initial one is ok.  
The database file is operating in WAL mode on version 3.8.10.2 (technically it 
is SQLCipher) with no pragma modifications.  All access is on the same physical 
disk coming from the same process, but with two different threads (one writing 
and one reading).  Really I’d just like to know if I’m barking up the wrong 
tree here trying to explain why this odd behavior occurs.


Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Clemens Ladisch]

Jim Borden wrote:
> I found the following snippet from https://www.sqlite.org/lockingv3.html
>
>   The SQL command "COMMIT" does not actually commit the changes to disk.
>   It just turns autocommit back on. Then, at the conclusion of the
>   command, the regular autocommit logic takes over and causes the actual
>   commit to disk to occur.
>
> Does that mean that the actual commit happens after the execution of
> “COMMIT” (i.e. it is in a sense asynchronous)?

No, "at the conclusion" happens _before_ the execution has finished.

> I have two connections to a database file and reports that writing to
> one and then immediately querying from another causes the second
> connection to show the entry as missing.  [...]
> The database file is operating in WAL mode

In WAL mode, the writer and the reader do not block each other.  This
implies that the reader sees the old state of the database.

What exactly does "immediately" mean?  Is there some synchronization
mechanism that actually ensures that the second transaction is started
_after_ the first one has finished?  (And when doing multiple SELECTs,
it's easy to have them in a single transaction because the first one
isn't finalized early enough.)

What is the PRAGMA synchronous setting?


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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Jim Borden]

I am using multiple threads, but in this instance just 2 inside of one process. 
 I do not change any PRAGMA settings other than user_version and journal_mode.  
The two connections differ only by the fact that one is read only and one is 
read-write. It’s possible that I’ve forgotten a finalize somewhere, etc, but 
unlikely because that stuff gets processed in a pretty centralized way when the 
object holding the sqlite3 object is disposed (C# term).

But in the end, my original understanding should hold that after the COMMIT 
execution finishes all data should be immediately visible to other connections 
from that point forward?

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 16:52, "sqlite-users on behalf of Simon Slavin" 
 wrote:


On 29 Sep 2016, at 8:39am, Jim Borden  wrote:

> I found the following snippet from https://www.sqlite.org/lockingv3.html
> 
> ➢ The SQL command "COMMIT" does not actually commit the changes to disk. 
It just turns autocommit back on. Then, at the conclusion of the command, the 
regular autocommit logic takes over and causes the actual commit to disk to 
occur.
> 
> Does that mean that the actual commit happens after the execution of 
“COMMIT” (i.e. it is in a sense asynchronous)?

No.  It happens before the API call you're doing finishes.  Remember that 
the SQLite library is just a collection of procedures which you run inside your 
program.  SQLite does not run in a different thread or on a different computer 
while your program does its own thing.

>  I have two connections to a database file and reports that writing to 
one and then immediately querying from another causes the second connection to 
show the entry as missing.

Are you using multiple processes or threads ?

Are you using any PRAGMAs which look like they might speed up SQLite 
operations ?

Are the two connections accessing the file using identical file 
specifications ?

Are you forgetting to finalize a _prepare,_step,_finalize sequence ?

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] Regarding the effects of the COMMIT keyword

[Jim Borden]

There is a web API, and the application flow is sending a PUT request, which 
stores the data and then returns a successful status.  After that status is 
received, a GET request is sent.  The way the write connection works is that 
everything is pumped through a single thread and all other threads must wait 
their turn while the single thread executes the work one by one (the exception 
being, as I found out painfully a few months ago, stepping through prepared 
statements).  If a transaction is entered, then any further transactions 
requested by the transaction are handled immediately as save points.  Only when 
the outermost transaction commits may the next work item be executed.  So by 
the time the successful HTTP response code is returned the data should be saved 
and committed.  That is why it is so puzzling that the next request (which does 
not use explicit transactions, just executes a few SELECT statements) returns 
404.  To me it seemed obvious that it was seeing an old state, but the question 
was why is it seeing an old state?

PRAGMA synchronous is 2 (NORMAL, I believe?)

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 
 

On 2016/09/29 16:51, "sqlite-users on behalf of Clemens Ladisch" 
 
wrote:

Jim Borden wrote:
> I found the following snippet from https://www.sqlite.org/lockingv3.html
>
>   The SQL command "COMMIT" does not actually commit the changes to disk.
>   It just turns autocommit back on. Then, at the conclusion of the
>   command, the regular autocommit logic takes over and causes the actual
>   commit to disk to occur.
>
> Does that mean that the actual commit happens after the execution of
> “COMMIT” (i.e. it is in a sense asynchronous)?

No, "at the conclusion" happens _before_ the execution has finished.

> I have two connections to a database file and reports that writing to
> one and then immediately querying from another causes the second
> connection to show the entry as missing.  [...]
> The database file is operating in WAL mode

In WAL mode, the writer and the reader do not block each other.  This
implies that the reader sees the old state of the database.

What exactly does "immediately" mean?  Is there some synchronization
mechanism that actually ensures that the second transaction is started
_after_ the first one has finished?  (And when doing multiple SELECTs,
it's easy to have them in a single transaction because the first one
isn't finalized early enough.)

What is the PRAGMA synchronous setting?


Regards,
Clemens
___
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] Regarding the effects of the COMMIT keyword

[Hick Gunter]

>I am using multiple threads, but in this instance just 2 inside of one 
>process.  I do not change any PRAGMA settings other than user_version and 
>journal_mode.  The two >connections differ only by the fact that one is read 
>only and one is read-write. It’s possible that I’ve forgotten a finalize 
>somewhere, etc, but unlikely because that stuff gets >processed in a pretty 
>centralized way when the object holding the sqlite3 object is disposed (C# 
>term).
>
>But in the end, my original understanding should hold that after the COMMIT 
>execution finishes all data should be immediately visible to other connections 
>from that point >forward?
>

No. After COMMIT finishes, any TRANSACTION that is STARTED LATER willl see the 
new data.

Reading "stale" data (i.e. the DB state at the beginning of a transaction) is 
at least almost always caused by indvertently leaving a transaction open. 
Setting the journal mode to WAL hides this problem, because the writer is no 
longer blocked by the reader's transaction. Disable WAL mode and you will 
probably find that the writer will find that the "database is locked". When 
this occurs, you can use then sqlite3_next_stmt() interface (on the readonly 
connection) to locate any unfinished statements. If these are prepared via 
sqlite3_prepare_V2, you can retrieve the SQL using sqlite3_sql().


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Clemens Ladisch]

Hick Gunter wrote:
> Reading "stale" data (i.e. the DB state at the beginning of a transaction)
> is at least almost always caused by indvertently leaving a transaction
> open. Setting the journal mode to WAL hides this problem, because the
> writer is no longer blocked by the reader's transaction. Disable WAL mode
> and you will probably find that the writer will find that the "database is
> locked".

Alternatively, put explicit BEGIN/COMMITs around all reads; then you'll
see if you accidentally try to nest transactions.


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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Simon Slavin]

On 29 Sep 2016, at 8:39am, Jim Borden  wrote:

> I found the following snippet from https://www.sqlite.org/lockingv3.html
> 
> ➢ The SQL command "COMMIT" does not actually commit the changes to disk. It 
> just turns autocommit back on. Then, at the conclusion of the command, the 
> regular autocommit logic takes over and causes the actual commit to disk to 
> occur.
> 
> Does that mean that the actual commit happens after the execution of “COMMIT” 
> (i.e. it is in a sense asynchronous)?

No.  It happens before the API call you're doing finishes.  Remember that the 
SQLite library is just a collection of procedures which you run inside your 
program.  SQLite does not run in a different thread or on a different computer 
while your program does its own thing.

>  I have two connections to a database file and reports that writing to one 
> and then immediately querying from another causes the second connection to 
> show the entry as missing.

Are you using multiple processes or threads ?

Are you using any PRAGMAs which look like they might speed up SQLite operations 
?

Are the two connections accessing the file using identical file specifications ?

Are you forgetting to finalize a _prepare,_step,_finalize sequence ?

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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Simon Slavin]

On 29 Sep 2016, at 8:59am, Jim Borden  wrote:

> There is a web API

If you're using a conventional server as the front end to your web service 
(e.g. Apache, with your code written in PHP/Python/C/whatever) then the server 
spawns a new process to handle each incoming request.  So it's possible for two 
calls to execute at the same time and you do have to worry about 
multiprocessing.

However, there is a question of how SQLite connections are maintained.  Does 
the web service open some connections when it is started and maintain them 
throughout its life, or does it create a new connection to answer each PUT or 
GET ?  The answer is important because a savepoint is handled by a specific 
connection.  Close the connection and your savepoint vanishes.

Also, depending on how your code is written the server may be sending back the 
acknowledgement for the PUT first, and then doing the database operations while 
your program is already moving on to do something else.  But that would make it 
impossible to return a different HTTP response code if the PUT fails.  It's 
more likely that the PUT operation waits until the database connection is 
finished before returning its HTTP response code, to allow it to report errors.

In which case I don't see how your problem could occur.  But someone else might.

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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Jim Borden]

It’s less complicated than a web service.  There is no “server” per se, only a 
lightweight listener object that can accept and respond to HTTP requests (C# 
HttpListener class).  The short explanation is that the library I develop 
(Couchbase Lite) has a replication function that allows it to communicate with 
an endpoint that implements the CouchDB sync protocol.  As part of that, in 
order to enable device to device replication, there is also a listener 
component distributed as an optional part of Couchbase Lite which embeds a 
listener for REST requests into the process.  So yes, multiple threads are at 
play here, but in the scenario there would be three at most:  The thread that 
requested the PUT, the thread that wrote to the database, and the thread the 
requested the GET.  All write requests to the database are moderated through 
the write connection thread and will block the calling thread until the write 
operation is complete.  So, as you noted, this is why I am able to return the 
correct status for any given operation (almost, aside from this odd 404 issue). 
Also, this situation is relatively rare which makes it more annoying.  

All of the code is in one process compiled together (the listener, the storage 
API, the app, etc), and written all in C# with interop calls to C.  

Thanks for your input.  The clarification about COMMIT was enough for me to 
focus my attention elsewhere and has been helpful.

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 17:32, "sqlite-users on behalf of Simon Slavin" 
 wrote:


On 29 Sep 2016, at 8:59am, Jim Borden  wrote:

> There is a web API

If you're using a conventional server as the front end to your web service 
(e.g. Apache, with your code written in PHP/Python/C/whatever) then the server 
spawns a new process to handle each incoming request.  So it's possible for two 
calls to execute at the same time and you do have to worry about 
multiprocessing.

However, there is a question of how SQLite connections are maintained.  
Does the web service open some connections when it is started and maintain them 
throughout its life, or does it create a new connection to answer each PUT or 
GET ?  The answer is important because a savepoint is handled by a specific 
connection.  Close the connection and your savepoint vanishes.

Also, depending on how your code is written the server may be sending back 
the acknowledgement for the PUT first, and then doing the database operations 
while your program is already moving on to do something else.  But that would 
make it impossible to return a different HTTP response code if the PUT fails.  
It's more likely that the PUT operation waits until the database connection is 
finished before returning its HTTP response code, to allow it to report errors.

In which case I don't see how your problem could occur.  But someone else 
might.

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] SQLite 3.15.0 scheduled for 2016-10-14

[James K. Lowden]

On Fri, 23 Sep 2016 16:35:07 +
Quan Yong Zhai  wrote:

> Quote <<
> A "row value" is an ordered list of two or more scalar values. In
> other words, a "row value" is a vector.>>
> 
> A ?row value? is a tuple, not a vector. When your using a tuple, you
> know how many items in it, and the type of each item of it.

That's correct, and addresses Dominique's point, albeit obliquely:
to use a row-value as a list-argument to IN would be to confuse rows
and columns.  A row-value has 1 or more columns, but only one row.

It would be nice to use row-values correctly in IN:

select * from T where (a,b) IN ( (1, 'a'), (2, 'b') )

Is that valid? 

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

Re: [sqlite] Regarding the effects of the COMMIT keyword

[Jim Borden]

Thanks for all the input, all of your comments put me on exactly the right 
track.  I was too focused on the behavior of the writes and I didn’t consider 
the behavior of the reads.  I reviewed the logs again and it turns out there 
was a longer running query that surrounded the entire PUT / GET sequence, and 
since the same connection was being used for both queries (long, and GET) the 
GET was being held back on the outer result set.  To add to the confusion, 
another one of these long queries had just started before the previous one 
finished and so it appeared that it ran and finished quickly after the GET when 
in reality it was another starting and the previous one finishing.

Jim Borden
Software Engineer

jim.bor...@couchbase.com
 

On 2016/09/29 18:09, "sqlite-users on behalf of Clemens Ladisch" 
 
wrote:

Hick Gunter wrote:
> Reading "stale" data (i.e. the DB state at the beginning of a transaction)
> is at least almost always caused by indvertently leaving a transaction
> open. Setting the journal mode to WAL hides this problem, because the
> writer is no longer blocked by the reader's transaction. Disable WAL mode
> and you will probably find that the writer will find that the "database is
> locked".

Alternatively, put explicit BEGIN/COMMITs around all reads; then you'll
see if you accidentally try to nest transactions.


Regards,
Clemens
___
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