Re: [sqlite] Missing data on SELECT

2017-11-02 Thread Keith Medcalf 

Well that is a totally different thing.  Of course, in your sample data id-10 
DOES NOT exist in ProjectMine, so you will never see id=10 no matter what you 
do.

select id from ProjectsMine where id not in (select id from ProjectsALL);

or

select id from ProjectsMine
except
select id from ProjectsAll;



---
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 jose isaias cabrera
>Sent: Thursday, 2 November, 2017 22:26
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>You're right.  Apologies.  The right SELECT would be,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.ProjID > 3 AND
>   cl.XtraB != ls.XtraB
>);
>
>To answer your question, and a long story made short, this is a
>SELECT to
>see which items have changed between two DBs. id=10 existed in
>ProjectsALL,
>but an error caused the deletion of id=10.  However, this id 10 lives
>in
>another DB (ProjectsMine).  I would like the SELECT to tell me that
>id=10
>which exists in ProjectsMine, needs to be updated and PUSHED to
>ProjectsALL
>because it does not exists there.  Maybe, I am going to have to do
>two
>SELECTS, one that tells me which ProjID exists in ProjectsMine, but
>donot
>exists in ProjectsAll.  These must be INSERTED into ProjectsALL.  And
>then
>do the SELECT above to get which have changed the XtraB date.  I hope
>this
>explains everything.  Thanks for the help.
>
>-Original Message-
>From: Keith Medcalf
>Sent: Thursday, November 2, 2017 8:26 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>I think your query is in error.  Amongst other things, tou have the
>same
>condition listed twice:
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND  DUPLICATE
>   cl.XtraB != ls.XtraB
>);
>
>You cannot return a result which does not exist, and if you delete
>id=10
>from the ProjectsALL table there will be no project in either table
>that has
>id=10.  How do you expect to return an id of 10 when that id does no
>exist?
>
>What exactly, in English, is it that you are trying to achieve (ie,
>what is
>the PROBLEM STATEMENT for which you are trying to find a solution)?
>
>---
>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 jose isaias cabrera
>>Sent: Thursday, 2 November, 2017 16:02
>>To: SQLite mailing list
>>Subject: [sqlite] Missing data on SELECT
>>
>>
>>Greetings.
>>
>>Apologies for the long email, or long set of data.  I have these two
>>tables in two different database files, but for easy setup, I have
>>place them in the same DB.  Having these data,
>>
>>
>>CREATE TABLE ProjectsALL
>>(
>>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>>);
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(1,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(2,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(3,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(4,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(5,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(6,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(7,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(8,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(9,'id0','A','ccc','2017-10-05 10:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(10,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(11,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(12,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(13,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(14,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(15,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(16,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO Pro

Re: [sqlite] Missing data on SELECT

2017-11-02 Thread Simon Slavin 


On 3 Nov 2017, at 4:32am, jose isaias cabrera  wrote:

> It's a long story, but what I would like to SELECT is the id or ids that have 
> different XTraB between ProjectsMine and ProjectsALL, and also list the id, 
> or ids, that exists in ProjectsMine and not on ProjectsALL.

Do it as two separate SELECTs.  You can use UNION to merge the results if you 
want, but these are really two separate things that can be dealt with in two 
steps.

Simon.

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

Re: [sqlite] Missing data on SELECT

2017-11-02 Thread jose isaias cabrera 


It's a long story, but what I would like to SELECT is the id or ids that 
have different XTraB between ProjectsMine and ProjectsALL, and also list the 
id, or ids, that exists in ProjectsMine and not on ProjectsALL. This is do 
do a push to ProjectsAll of those ids from ProjectsMine.


-Original Message- 
From: Jim Dodgen

Sent: Thursday, November 2, 2017 11:43 PM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT

You are inserting rows with ProjID but not the primary key "id"  which is
rowid and automatically created. I think it is not a good practice to
use rowid's like you are doing.  If you had inserted the id it would be a
different story.

*Jim Dodgen*







On Thu, Nov 2, 2017 at 5:26 PM, Keith Medcalf  wrote:



I think your query is in error.  Amongst other things, tou have the same
condition listed twice:

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND  DUPLICATE
   cl.XtraB != ls.XtraB
);

You cannot return a result which does not exist, and if you delete id=10
from the ProjectsALL table there will be no project in either table that
has id=10.  How do you expect to return an id of 10 when that id does no
exist?

What exactly, in English, is it that you are trying to achieve (ie, what
is the PROBLEM STATEMENT for which you are trying to find a solution)?

---
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 jose isaias cabrera
>Sent: Thursday, 2 November, 2017 16:02
>To: SQLite mailing list
>Subject: [sqlite] Missing data on SELECT
>
>
>Greetings.
>
>Apologies for the long email, or long set of data.  I have these two
>tables in two different database files, but for easy setup, I have
>place them in the same DB.  Having these data,
>
>
>CREATE TABLE ProjectsALL
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-05 10:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(10,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(11,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(12,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(13,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(14,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(15,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(16,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(17,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(18,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(19,'id0','A','ccc','2017-10-05 10:30:19');
>
>
>CREATE TABLE ProjectsMine
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsMine (Pr

Re: [sqlite] Missing data on SELECT

2017-11-02 Thread jose isaias cabrera 


You're right.  Apologies.  The right SELECT would be,

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
  cl.id = ls.id AND
  cl.login = 'id0' AND
  cl.ProjID > 3 AND
  cl.XtraB != ls.XtraB
);

To answer your question, and a long story made short, this is a SELECT to 
see which items have changed between two DBs. id=10 existed in ProjectsALL, 
but an error caused the deletion of id=10.  However, this id 10 lives in 
another DB (ProjectsMine).  I would like the SELECT to tell me that  id=10 
which exists in ProjectsMine, needs to be updated and PUSHED to ProjectsALL 
because it does not exists there.  Maybe, I am going to have to do two 
SELECTS, one that tells me which ProjID exists in ProjectsMine, but donot 
exists in ProjectsAll.  These must be INSERTED into ProjectsALL.  And then 
do the SELECT above to get which have changed the XtraB date.  I hope this 
explains everything.  Thanks for the help.


-Original Message- 
From: Keith Medcalf

Sent: Thursday, November 2, 2017 8:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT


I think your query is in error.  Amongst other things, tou have the same 
condition listed twice:


SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
  cl.id = ls.id AND
  cl.login = 'id0' AND
  cl.id = ls.id AND  DUPLICATE
  cl.XtraB != ls.XtraB
);

You cannot return a result which does not exist, and if you delete id=10 
from the ProjectsALL table there will be no project in either table that has 
id=10.  How do you expect to return an id of 10 when that id does no exist?


What exactly, in English, is it that you are trying to achieve (ie, what is 
the PROBLEM STATEMENT for which you are trying to find a solution)?


---
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 jose isaias cabrera
Sent: Thursday, 2 November, 2017 16:02
To: SQLite mailing list
Subject: [sqlite] Missing data on SELECT


Greetings.

Apologies for the long email, or long set of data.  I have these two
tables in two different database files, but for easy setup, I have
place them in the same DB.  Having these data,


CREATE TABLE ProjectsALL
(
 id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(4,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(5,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(6,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(7,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(8,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(9,'id0','A','ccc','2017-10-05 10:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(10,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(11,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(12,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(13,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(14,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(15,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(16,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(17,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(18,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
(19,'id0','A','ccc','2017-10-05 10:30:19');


CREATE TABLE ProjectsMine
(
 id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
(1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
(2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
(3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
(4,'id2','A','ccc','2017-09-0

Re: [sqlite] Missing data on SELECT

2017-11-02 Thread Jim Dodgen 
You are inserting rows with ProjID but not the primary key "id"  which is
rowid and automatically created. I think it is not a good practice to
use rowid's like you are doing.  If you had inserted the id it would be a
different story.

*Jim Dodgen*







On Thu, Nov 2, 2017 at 5:26 PM, Keith Medcalf  wrote:

>
> I think your query is in error.  Amongst other things, tou have the same
> condition listed twice:
>
> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> (
>cl.id = ls.id AND
>cl.login = 'id0' AND
>cl.id = ls.id AND  DUPLICATE
>cl.XtraB != ls.XtraB
> );
>
> You cannot return a result which does not exist, and if you delete id=10
> from the ProjectsALL table there will be no project in either table that
> has id=10.  How do you expect to return an id of 10 when that id does no
> exist?
>
> What exactly, in English, is it that you are trying to achieve (ie, what
> is the PROBLEM STATEMENT for which you are trying to find a solution)?
>
> ---
> 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 jose isaias cabrera
> >Sent: Thursday, 2 November, 2017 16:02
> >To: SQLite mailing list
> >Subject: [sqlite] Missing data on SELECT
> >
> >
> >Greetings.
> >
> >Apologies for the long email, or long set of data.  I have these two
> >tables in two different database files, but for easy setup, I have
> >place them in the same DB.  Having these data,
> >
> >
> >CREATE TABLE ProjectsALL
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(9,'id0','A','ccc','2017-10-05 10:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(10,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(11,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(12,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(13,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(14,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(15,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(16,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(17,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(18,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(19,'id0','A','ccc','2017-10-05 10:30:19');
> >
> >
> >CREATE TABLE ProjectsMine
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, pr

Re: [sqlite] deadlock between query and insert in-memory db

2017-11-02 Thread Keith Medcalf 

Please clarify (for the second time):

>I tested the in-memory with multi-thread (but inserts/deletes are in
>a lock)

What does "with multi-thread" mean?


---
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 Wang, Peter (Xu)
>Sent: Thursday, 2 November, 2017 19:23
>To: sqlite-users@mailinglists.sqlite.org
>Cc: wangxu198...@gmail.com
>Subject: [sqlite] deadlock between query and insert in-memory db
>
>Can anyone help me on this?
>
>Thanks
>Peter
>
>From: Wang, Peter (Xu)
>Sent: Tuesday, October 31, 2017 4:19 PM
>To: 'sqlite-users@mailinglists.sqlite.org' us...@mailinglists.sqlite.org>
>Cc: wangxu198...@gmail.com
>Subject: Segfault when query again in-memory db
>
>Hi
>I am trying to provide a asychnonous queue based on sqlite3 in Python
>Current, I already passted the file based tests with my queue
>When switching file to in-memory db, I keep meeting a "segfault"
>issue when running the same tests test suite
>
>Can anyone help me out of this situation?
>
>
>I tested the in-memory with multi-thread (but inserts/deletes are in
>a lock)
>Here is the trace of the core file:
>
>(gdb) bt
>#0  sqlite3_value_type (pVal=0x0) at sqlite3.c:72512
>#1  0x7fda2a34fd86 in sqlite3_column_type (pStmt=0x7fda08004178,
>i=) at sqlite3.c:73318
>#2  0x7fda2a607987 in _pysqlite_fetch_one_row
>(self=self@entry=0x7fda2a204b20) at /build/python2.7-
>1tJBSB/python2.7-2.7.12/Modules/_sqlite/cursor.c:352
>#3  0x7fda2a6089c1 in _pysqlite_query_execute
>(self=0x7fda2a204b20, multiple=, args=)
>at /build/python2.7-1tJBSB/python2.7-
>2.7.12/Modules/_sqlite/cursor.c:711
>#4  0x004b0cb3 in PyObject_Call () at
>../Objects/abstract.c:2546
>#5  0x004ce5d0 in PyEval_CallObjectWithKeywords () at
>../Python/ceval.c:4219
>#6  0x7fda2a609106 in pysqlite_connection_execute
>(self=, args=('SELECT _id, data FROM queue_default
>ORDER BY _id ASC LIMIT 1', ()))
>at /build/python2.7-1tJBSB/python2.7-
>2.7.12/Modules/_sqlite/connection.c:1262
>#7  0x004cb945 in call_function (oparg=,
>pp_stack=0x7fda19ff9fc0) at ../Python/ceval.c:4350
>#8  PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#9  0x004c2765 in PyEval_EvalCodeEx () at
>../Python/ceval.c:3582
>#10 0x004ca8d1 in fast_function (nk=0, na=,
>n=, pp_stack=0x7fda19ffa1d0, func=0x7fda2a83c050>)
>at ../Python/ceval.c:4445
>#11 call_function (oparg=, pp_stack=0x7fda19ffa1d0) at
>../Python/ceval.c:4370
>#12 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#13 0x004c9d8f in fast_function (nk=,
>na=, n=, pp_stack=0x7fda19ffa320,
>func=) at
>../Python/ceval.c:4435
>#14 call_function (oparg=, pp_stack=0x7fda19ffa320) at
>../Python/ceval.c:4370
>#15 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#16 0x004c2765 in PyEval_EvalCodeEx () at
>../Python/ceval.c:3582
>#17 0x004ca099 in fast_function (nk=1, na=,
>n=, pp_stack=0x7fda19ffa530, func=0x7fda2a841758>)
>at ../Python/ceval.c:4445
>#18 call_function (oparg=, pp_stack=0x7fda19ffa530) at
>../Python/ceval.c:4370
>#19 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#20 0x004c2765 in PyEval_EvalCodeEx () at
>../Python/ceval.c:3582
>#21 0x004de8b8 in function_call.lto_priv () at
>../Objects/funcobject.c:523
>#22 0x004b0cb3 in PyObject_Call () at
>../Objects/abstract.c:2546
>#23 0x004c6ad1 in ext_do_call (nk=,
>na=, flags=, pp_stack=0x7fda19ffa7e8,
>func=) at
>../Python/ceval.c:4664
>#24 PyEval_EvalFrameEx () at ../Python/ceval.c:3026
>#25 0x004c9d8f in fast_function (nk=,
>na=, n=, pp_stack=0x7fda19ffa930,
>func=) at
>../Python/ceval.c:4435
>#26 call_function (oparg=, pp_stack=0x7fda19ffa930) at
>../Python/ceval.c:4370
>#27 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#28 0x004c9d8f in fast_function (nk=,
>na=, n=, pp_stack=0x7fda19ffaa80,
>---Type  to continue, or q  to quit---
>func=) at
>../Python/ceval.c:4435
>#29 call_function (oparg=, pp_stack=0x7fda19ffaa80) at
>../Python/ceval.c:4370
>#30 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
>#31 0x004c2765 in PyEval_EvalCodeEx () at
>../Python/ceval.c:3582
>#32 0x004de6fe in function_call.lto_priv () at
>../Objects/funcobject.c:523
>#33 0x004b0cb3 in PyObject_Call () at
>../Objects/abstract.c:2546
>#34 0x004f492e in instancemethod_call.lto_priv () at
>../Objects/classobject.c:2602
>#35 0x004b0cb3 in PyObject_Call () at
>../Objects/abstract.c:2546
>#36 0x004ce5d0 in PyEval_CallObjectWithKeywords () at
>../Python/ceval.c:4219
>#37 0x00597e32 in t_bootstrap () at
>../Modules/threadmodule.c:620
>#38 0x7fda2d77f6ba in start_thread (arg=0x7fda19ffb700) at
>pthread_create.c:333
>#39 0x7fda2d4b53dd in clone () at
>../sysdeps/unix/sysv/linux/x86_64/clone.S:109
>
>Here is the python trace:
>
>(gdb) py-bt
>Traceba

[sqlite] deadlock between query and insert in-memory db

2017-11-02 Thread Wang, Peter (Xu) 
Can anyone help me on this?

Thanks
Peter

From: Wang, Peter (Xu)
Sent: Tuesday, October 31, 2017 4:19 PM
To: 'sqlite-users@mailinglists.sqlite.org' 

Cc: wangxu198...@gmail.com
Subject: Segfault when query again in-memory db

Hi
I am trying to provide a asychnonous queue based on sqlite3 in Python
Current, I already passted the file based tests with my queue
When switching file to in-memory db, I keep meeting a "segfault" issue when 
running the same tests test suite

Can anyone help me out of this situation?


I tested the in-memory with multi-thread (but inserts/deletes are in a lock)
Here is the trace of the core file:

(gdb) bt
#0  sqlite3_value_type (pVal=0x0) at sqlite3.c:72512
#1  0x7fda2a34fd86 in sqlite3_column_type (pStmt=0x7fda08004178, 
i=) at sqlite3.c:73318
#2  0x7fda2a607987 in _pysqlite_fetch_one_row 
(self=self@entry=0x7fda2a204b20) at 
/build/python2.7-1tJBSB/python2.7-2.7.12/Modules/_sqlite/cursor.c:352
#3  0x7fda2a6089c1 in _pysqlite_query_execute (self=0x7fda2a204b20, 
multiple=, args=)
at /build/python2.7-1tJBSB/python2.7-2.7.12/Modules/_sqlite/cursor.c:711
#4  0x004b0cb3 in PyObject_Call () at ../Objects/abstract.c:2546
#5  0x004ce5d0 in PyEval_CallObjectWithKeywords () at 
../Python/ceval.c:4219
#6  0x7fda2a609106 in pysqlite_connection_execute (self=, 
args=('SELECT _id, data FROM queue_default ORDER BY _id ASC LIMIT 1', ()))
at 
/build/python2.7-1tJBSB/python2.7-2.7.12/Modules/_sqlite/connection.c:1262
#7  0x004cb945 in call_function (oparg=, 
pp_stack=0x7fda19ff9fc0) at ../Python/ceval.c:4350
#8  PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#9  0x004c2765 in PyEval_EvalCodeEx () at ../Python/ceval.c:3582
#10 0x004ca8d1 in fast_function (nk=0, na=, n=, pp_stack=0x7fda19ffa1d0, func=)
at ../Python/ceval.c:4445
#11 call_function (oparg=, pp_stack=0x7fda19ffa1d0) at 
../Python/ceval.c:4370
#12 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#13 0x004c9d8f in fast_function (nk=, na=, n=, pp_stack=0x7fda19ffa320,
func=) at ../Python/ceval.c:4435
#14 call_function (oparg=, pp_stack=0x7fda19ffa320) at 
../Python/ceval.c:4370
#15 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#16 0x004c2765 in PyEval_EvalCodeEx () at ../Python/ceval.c:3582
#17 0x004ca099 in fast_function (nk=1, na=, n=, pp_stack=0x7fda19ffa530, func=)
at ../Python/ceval.c:4445
#18 call_function (oparg=, pp_stack=0x7fda19ffa530) at 
../Python/ceval.c:4370
#19 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#20 0x004c2765 in PyEval_EvalCodeEx () at ../Python/ceval.c:3582
#21 0x004de8b8 in function_call.lto_priv () at 
../Objects/funcobject.c:523
#22 0x004b0cb3 in PyObject_Call () at ../Objects/abstract.c:2546
#23 0x004c6ad1 in ext_do_call (nk=, na=, 
flags=, pp_stack=0x7fda19ffa7e8,
func=) at ../Python/ceval.c:4664
#24 PyEval_EvalFrameEx () at ../Python/ceval.c:3026
#25 0x004c9d8f in fast_function (nk=, na=, n=, pp_stack=0x7fda19ffa930,
func=) at ../Python/ceval.c:4435
#26 call_function (oparg=, pp_stack=0x7fda19ffa930) at 
../Python/ceval.c:4370
#27 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#28 0x004c9d8f in fast_function (nk=, na=, n=, pp_stack=0x7fda19ffaa80,
---Type  to continue, or q  to quit---
func=) at ../Python/ceval.c:4435
#29 call_function (oparg=, pp_stack=0x7fda19ffaa80) at 
../Python/ceval.c:4370
#30 PyEval_EvalFrameEx () at ../Python/ceval.c:2987
#31 0x004c2765 in PyEval_EvalCodeEx () at ../Python/ceval.c:3582
#32 0x004de6fe in function_call.lto_priv () at 
../Objects/funcobject.c:523
#33 0x004b0cb3 in PyObject_Call () at ../Objects/abstract.c:2546
#34 0x004f492e in instancemethod_call.lto_priv () at 
../Objects/classobject.c:2602
#35 0x004b0cb3 in PyObject_Call () at ../Objects/abstract.c:2546
#36 0x004ce5d0 in PyEval_CallObjectWithKeywords () at 
../Python/ceval.c:4219
#37 0x00597e32 in t_bootstrap () at ../Modules/threadmodule.c:620
#38 0x7fda2d77f6ba in start_thread (arg=0x7fda19ffb700) at 
pthread_create.c:333
#39 0x7fda2d4b53dd in clone () at 
../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Here is the python trace:

(gdb) py-bt
Traceback (most recent call first):
  File "/home/user/Documents/persist-queue/persistqueue/sqlbase.py", line 140, 
in _select
def _select(self, *args):
  File "/home/user/Documents/persist-queue/persistqueue/sqlqueue.py", line 49, 
in _pop
row = self._select()
  File "/home/wangp11/Documents/persist-queue/persistqueue/sqlqueue.py", line 
67, in get
pickled = self._pop()
  File "/home/user/Documents/persist-queue/tests/test_sqlqueue.py", line 105, 
in consumer
x = m_queue.get(block=True)
  File "/usr/lib/python2.7/threading.py", line 754, in run
self.__target(*self.__args, **self.__kwargs)
  File "/usr/lib/python2.7/threading.py", line 801, in __bootstrap_inner
self.run()
  File "/usr/lib/python2.7/threading.py", line 774, in

Re: [sqlite] Missing data on SELECT

2017-11-02 Thread Keith Medcalf 

I think your query is in error.  Amongst other things, tou have the same 
condition listed twice:

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND  DUPLICATE
   cl.XtraB != ls.XtraB
);

You cannot return a result which does not exist, and if you delete id=10 from 
the ProjectsALL table there will be no project in either table that has id=10.  
How do you expect to return an id of 10 when that id does no exist?

What exactly, in English, is it that you are trying to achieve (ie, what is the 
PROBLEM STATEMENT for which you are trying to find a solution)?

---
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 jose isaias cabrera
>Sent: Thursday, 2 November, 2017 16:02
>To: SQLite mailing list
>Subject: [sqlite] Missing data on SELECT
>
>
>Greetings.
>
>Apologies for the long email, or long set of data.  I have these two
>tables in two different database files, but for easy setup, I have
>place them in the same DB.  Having these data,
>
>
>CREATE TABLE ProjectsALL
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-05 10:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(10,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(11,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(12,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(13,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(14,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(15,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(16,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(17,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(18,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(19,'id0','A','ccc','2017-10-05 10:30:19');
>
>
>CREATE TABLE ProjectsMine
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-15 10:30:19');
>
>running this SELECT,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND
>   cl.XtraB != ls.XtraB
>);
>
>would give, 9.  Now let's delete record 10 on the ProjectsALL table,
>
>delete from ProjectsAll where id=10;
>
>after deleting record 10 and running the same SELECT above,
>
>sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>   ...> (

Re: [sqlite] [EXTERNAL] sqlite_master structure

2017-11-02 Thread Wout Mertens 
Don't forget about this handy pragma:

PRAGMA schema.table_info(table-name);

This pragma returns one row for each column in the named table.
Columns in the result set include the column name, data type, whether
or not the column can be NULL, and the default value for the column.
The "pk" column in the result set is zero for columns that are not
part of the primary key, and is the index of the column in the primary
key for columns that are part of the primary key.

The table named in the table_info pragma can also be a view.



On 11/2/17, Hick Gunter  wrote:
> If an entry refers to an index, the field "name" will contain the name of
> the index, while the field "tbl_name" contains the name of the table the
> index refers to.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
> Auftrag von Igor Korot
> Gesendet: Donnerstag, 02. November 2017 04:37
> An: Discussion of SQLite Database ;
> General Discussion of SQLite Database 
> Betreff: [EXTERNAL] [sqlite] sqlite_master structure
>
> Hi, ALL,
> I see that sqlite_master have 2 fields: name and tbl_name. It looks like
> they have the same value in my case.
>
> Is there a scenario when those 2 are different?
> And what should I check for the "table name"?
>
> Thank you.
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing data on SELECT

2017-11-02 Thread jose isaias cabrera 

Greetings.

Apologies for the long email, or long set of data.  I have these two tables in 
two different database files, but for easy setup, I have place them in the same 
DB.  Having these data,


CREATE TABLE ProjectsALL
(
  id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(4,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(5,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(6,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(7,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(8,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(9,'id0','A','ccc','2017-10-05 10:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(10,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(11,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(12,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(13,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(14,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(15,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(16,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(17,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(18,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES 
(19,'id0','A','ccc','2017-10-05 10:30:19');


CREATE TABLE ProjectsMine
(
  id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(4,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(5,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(6,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(7,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(8,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES 
(9,'id0','A','ccc','2017-10-15 10:30:19');

running this SELECT,

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND
   cl.XtraB != ls.XtraB
);

would give, 9.  Now let's delete record 10 on the ProjectsALL table,

delete from ProjectsAll where id=10;

after deleting record 10 and running the same SELECT above,

sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
   ...> (
   ...>cl.id = ls.id AND
   ...>cl.login = 'id0' AND
   ...>cl.id = ls.id AND
   ...>cl.XtraB != ls.XtraB
   ...> );
9

I still get 9.  I also want 10 to be part of the result.  I know it's no longer 
there, but I would also like to have it as part of the result because this part 
of the select cl.XtraB != ls.XtraB matches.  Any easy way to do this?  In other 
words, when I run the SELECT, I want the the items that match the SELECT and 
any items that are not in the ALL table.  Thanks.

josé

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

[sqlite] Typo on VFS webpage

2017-11-02 Thread Luke Shumaker 
On https://www.sqlite.org/vfs.html
in the paragraph about test_quota.c, in one instance
it says "quote groups" instead of "quota groups"
 ^ ^

-- 
Happy hacking,
~ Luke Shumaker
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread yaro 
Thanks Clemens and Dan,
The issue is resolved, it was caused by not setting the URI Filename flag.

Yaro



--
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] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Keith Medcalf 

Fascinating.  

What is the Operating System and Version (eg Linux  
  or Windows  ); 
SQLite version; interface (C or .snot)?  

Does this only affect trips through the platform localtime function within 
SQLite (that is, if you store GMT/UTC by leaving out the 'localtime' modifier) 
do you still get regionalized formatting coming back?

Or are you providing a misformed timestamp produced by some regional aware 
function (ie, not forcing a format/region override when retrieving the 
timestamp-string -- which is a pretty common application level error in my 
experience)?

Note that I cannot get the builtin 'now' or 'localtime' modifiers to return an 
inconsistent (regionalized) format, but I haven't tried real hard either.

---
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 Rajat Pal
>Sent: Thursday, 2 November, 2017 06:24
>To: sqlite-users@mailinglists.sqlite.org
>Cc: Joy George Chittilapailly; Minghua Feng; Rohit Udasi
>Subject: [sqlite] SQLite DB - DateTime field values are not
>consistent
>
>Hi Team,
>
>
>
>We have identified an issue with SQLite database which seems to be a
>potential bug.
>
>
>
>In SQLite database, All the values of datetime fields are saved as
>text without any format check. We can save any text value in the
>datetime column. This behavior is creating problem for us because for
>different regional settings,  datetime values are getting saved in
>different format.
>
>
>
>For example, if the regional format is English(United States), the
>value is saved as "2017-09-21 00:00:00" but if the regional format is
>Finnish(Finland), the value is saved as "2017-10-27 00.00.00". So
>some dates have colon(:) as time separator and some dates have dot
>(.) as time separator.
>
>
>
>Below query can illustrate the problem:
>
>
>
>Create Table Table1(TestDate DATETIME);
>
>
>
>Insert Into Table1 values (datetime('now','localtime'));
>
>
>
>insert into Table1 values ("2017-11-01 17:31:41");
>
>
>
>insert into Table1 values ("2017-11-01 17.32.41");
>
>
>
>insert into Table1 values ("Testing");
>
>
>
>select * from Table1;
>
>
>
>Can you please let us know if there is any way we can force the
>datetime field to have the datetime values in same format only.
>
>
>
>Regards,
>
>Rajat Pal
>___
>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 Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread yaro 
Dan,
Your question was well composed and illuminating. After going through my
code I realised that I didn't enable URI Filename in any of the ways you
mentioned, my guide to using URI Filename is 
https://www.sqlite.org/inmemorydb.html
  

May be that is the cause of my problem.

Yaro.



--
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] How to do rollback & rollforward?

2017-11-02 Thread Richard Hipp 
On 11/2/17, Robert M. Münch  wrote:
> Hi, following scenario:
>
> Our app has a very exhaustive undo system. There you can, as long as you
> don't change anything, go back in time, take a look at your data and than go
> forward again and get back all changes you have done.
>
> I think the VERSION extension might help us here. On the other hand,
> SAVEPOINTs look very good too as they are named. But I don't see a way how I
> can go back to a SAVEPOINT and then forward again. Would this be possible
> somehow?

Did you see this document: https://www.sqlite.org/undoredo.html

Do the ideas talked about there help in any way?

-- 
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] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread Dan Kennedy 

On 11/02/2017 08:50 PM, yaro wrote:

Hello,
I have found the cause of the weird behaviour. The in Shared Cache In-Memory
DB isn't persisted, rather the problem is due to a temp file named "file"
that isn't deleted after my application closes. Whenever I delete this file
then everything works fine.

 From my search this file ought to be auto deleted by SQLite when closed but
it isn't. Could anyone please provide suggests on how to fix this, my PC has
win7.


How are you enabling URI filenames?

By building with -DSQLITE_USE_URI, or by enabling them globally at 
runtime using sqlite3_config(), or by passing the SQLITE_OPEN_URI flag 
to sqlite3_open_v2()?


Dan.

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

[sqlite] How to do rollback & rollforward?

2017-11-02 Thread Robert M. Münch 
Hi, following scenario:

Our app has a very exhaustive undo system. There you can, as long as you don't 
change anything, go back in time, take a look at your data and than go forward 
again and get back all changes you have done.

I think the VERSION extension might help us here. On the other hand, SAVEPOINTs 
look very good too as they are named. But I don't see a way how I can go back 
to a SAVEPOINT and then forward again. Would this be possible somehow?

The other idea is to take the named SAVEPOINTs idea and implement something 
like this manually with change-sets etc. that can be played back and forth. I'm 
not totally sure how to best tackle this.

If using VERSION extension and storing the changes inside the DB (while 
excluding the tracking for the change-set-table) this should even give us a 
persistent undo system, right?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread Clemens Ladisch 
yaro wrote:
> the problem is due to a temp file named "file" that isn't deleted after my 
> application closes.

SQLite does not create temp files named "file".


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

Re: [sqlite] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Tim Streater 
On 02 Nov 2017, at 12:23, Rajat Pal  wrote:

> In SQLite database, All the values of datetime fields are saved as text
> without any format check. We can save any text value in the datetime column.
> This behavior is creating problem for us because for different regional
> settings,  datetime values are getting saved in different format.

As sqlite has no DATETIME field, what you actually get is a TEXT field in which 
you can store anything.

> For example, if the regional format is English(United States), the value is
> saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland),
> the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as
> time separator and some dates have dot (.) as time separator.

In whatever language you use to call sqlite, you should write a general 
function that can take anything that looks like a date/time and convert it to 
seconds since the epoch (or any other fixed historical moment), then store that 
value as an integer using sqlite. If you are using PHP, the strtotime() 
function does a very good job for that purpose.

Then when you retreive it, convert it to a time/date in the form that the local 
user expects.


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

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread David Raymond 
I think the combo is needed as with just the time function not null constraint 
you can just insert in plain ol' integer values.

sqlite> select julianday(7);
julianday(7)
7.0

sqlite> select datetime(2);
datetime(2)
-4713-11-26 12:00:00

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, November 02, 2017 10:19 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not 
consistent

On 11/2/17, David Raymond  wrote:
> For basic level you can use a check constraint
>
> create table Table1 (
>   TestDate DATETIME
> check (TestDate like '-__-__ __:__:__'
>and datetime(TestDate) is not null)
> );
>
> That should check that it's in the correct format, and the second part
> should check that there's no garbage in there.

How about this approach:

  CREATE TABLE table1(
  TestDate DATETIME NOT NULL
  CHECK(julianday(TestDate) IS NOT NULL)
  )

All of the date/time functions return a NULL if you give them an
invalid date/time as input.  So checking for a NULL result is a good
way to validate the date/time.  This goes further than the LIKE
pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
ij:kl:mn' which the LIKE pattern would apparently accept.

-- 
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] [EXTERNAL] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread nomad 
On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote:
> On 11/2/17, David Raymond  wrote:
> > For basic level you can use a check constraint
> >
> > create table Table1 (
> >   TestDate DATETIME
> > check (TestDate like '-__-__ __:__:__'
> >and datetime(TestDate) is not null)
> > );
> >
> > That should check that it's in the correct format, and the second part
> > should check that there's no garbage in there.
> 
> How about this approach:
> 
>   CREATE TABLE table1(
>   TestDate DATETIME NOT NULL
>   CHECK(julianday(TestDate) IS NOT NULL)
>   )
> 
> All of the date/time functions return a NULL if you give them an
> invalid date/time as input.  So checking for a NULL result is a good
> way to validate the date/time.  This goes further than the LIKE
> pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
> ij:kl:mn' which the LIKE pattern would apparently accept.

That approach is also not quite sufficient to ensure that the input is
actually a -mm-dd hh:mm::ss format, because the SQLite datetime
functions also accept HH:MM, 'now', integers/float...

sqlite> select julianday(2017323.32);
julianday(2017323.32)
-
2017323.32

To be really sure the input conforms to a particular date/time format
you need to round-trip it:

CREATE TABLE Table1(
TestDate DATETIME NOT NULL,
CONSTRAINT valid_datetime CHECK(
TestDate = COALESCE(
datetime( julianday(TestDate) ),
TestDate || x'
)
)
);

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

Re: [sqlite] [EXTERNAL] Re: SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Hick Gunter 
My point is that there is no datetime magic performed for TEXT fields.

If you feel the database should handle it, go ahead and write triggers/check 
constraints.

If you feel the application should handle it, make it convert to and from just 
one single format (we use 64bit numerical timestamps in UTC).

In either case, it is not a "bug" in SQlite.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Stephen Chrzanowski
Gesendet: Donnerstag, 02. November 2017 14:45
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] SQLite DB - DateTime field values are not 
consistent

As Hick mentioned, you could get your application to deal with this, or, if you 
want it at the SQLite level, you could get into triggers to analyze the data 
before its being inserted or updated.

For me, it depends on how you want to look at the issue as far as what part of 
the entire application scope should be defending the data.  IMO, it should be 
the database that defends its data that it holds, while others state that the 
database should be just raw data.  Both aspects (IMO) are absolutely valid, but 
its a matter of thought and how you (And your team?) decide to ensure your data 
remains safe.

On Thu, Nov 2, 2017 at 8:23 AM, Rajat Pal  wrote:

> Hi Team,
>
>
>
> We have identified an issue with SQLite database which seems to be a
> potential bug.
>
>
>
> In SQLite database, All the values of datetime fields are saved as
> text without any format check. We can save any text value in the
> datetime column. This behavior is creating problem for us because for
> different regional settings,  datetime values are getting saved in different 
> format.
>
>
>
> For example, if the regional format is English(United States), the
> value is saved as "2017-09-21 00:00:00" but if the regional format is
> Finnish(Finland), the value is saved as "2017-10-27 00.00.00". So some
> dates have colon(:) as time separator and some dates have dot (.) as
> time separator.
>
>
>
> Below query can illustrate the problem:
>
>
>
> Create Table Table1(TestDate DATETIME);
>
>
>
> Insert Into Table1 values (datetime('now','localtime'));
>
>
>
> insert into Table1 values ("2017-11-01 17:31:41");
>
>
>
> insert into Table1 values ("2017-11-01 17.32.41");
>
>
>
> insert into Table1 values ("Testing");
>
>
>
> select * from Table1;
>
>
>
> Can you please let us know if there is any way we can force the
> datetime field to have the datetime values in same format only.
>
>
>
> Regards,
>
> Rajat Pal
> ___
> 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


___
 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] [EXTERNAL] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Richard Hipp 
On 11/2/17, David Raymond  wrote:
> For basic level you can use a check constraint
>
> create table Table1 (
>   TestDate DATETIME
> check (TestDate like '-__-__ __:__:__'
>and datetime(TestDate) is not null)
> );
>
> That should check that it's in the correct format, and the second part
> should check that there's no garbage in there.

How about this approach:

  CREATE TABLE table1(
  TestDate DATETIME NOT NULL
  CHECK(julianday(TestDate) IS NOT NULL)
  )

All of the date/time functions return a NULL if you give them an
invalid date/time as input.  So checking for a NULL result is a good
way to validate the date/time.  This goes further than the LIKE
pattern above, as it inhibits nonsense dates such as 'abcd-ef-gh
ij:kl:mn' which the LIKE pattern would apparently accept.

-- 
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] [EXTERNAL] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread David Raymond 
For basic level you can use a check constraint

create table Table1 (
  TestDate DATETIME
check (TestDate like '-__-__ __:__:__'
   and datetime(TestDate) is not null)
);

That should check that it's in the correct format, and the second part should 
check that there's no garbage in there.

Hmm, if TestDate is nullable I think it needs slightly more...

create table Table2 (
  TestDate DATETIME
check (TestDate like '-__-__ __:__:__'
  and not (TestDate is not null and datetime(TestDate) is null))
);

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Thursday, November 02, 2017 8:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not 
consistent

There is no "date" datatype in SQLite, an das you yourself attest, SQLite is 
returning exactly whatever was originally inserted.

It is up to your application to define the allowed format for storing datetimes 
and to provide conversion between the chosen storage format and the 
presentation to the user.

Hint: You could write a user defined function and use that in a check 
constraint to enforce your chosen format.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rajat Pal
Gesendet: Donnerstag, 02. November 2017 13:24
An: sqlite-users@mailinglists.sqlite.org
Cc: Joy George Chittilapailly ; Minghua Feng 
; Rohit Udasi 
Betreff: [EXTERNAL] [sqlite] SQLite DB - DateTime field values are not 
consistent

Hi Team,



We have identified an issue with SQLite database which seems to be a potential 
bug.



In SQLite database, All the values of datetime fields are saved as text without 
any format check. We can save any text value in the datetime column. This 
behavior is creating problem for us because for different regional settings,  
datetime values are getting saved in different format.



For example, if the regional format is English(United States), the value is 
saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland), 
the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as 
time separator and some dates have dot (.) as time separator.



Below query can illustrate the problem:



Create Table Table1(TestDate DATETIME);



Insert Into Table1 values (datetime('now','localtime'));



insert into Table1 values ("2017-11-01 17:31:41");



insert into Table1 values ("2017-11-01 17.32.41");



insert into Table1 values ("Testing");



select * from Table1;



Can you please let us know if there is any way we can force the datetime field 
to have the datetime values in same format only.



Regards,

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

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread yaro 
Hello,
I have found the cause of the weird behaviour. The in Shared Cache In-Memory
DB isn't persisted, rather the problem is due to a temp file named "file"
that isn't deleted after my application closes. Whenever I delete this file
then everything works fine.

From my search this file ought to be auto deleted by SQLite when closed but
it isn't. Could anyone please provide suggests on how to fix this, my PC has
win7.

Yaro.



--
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] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Stephen Chrzanowski 
As Hick mentioned, you could get your application to deal with this, or, if
you want it at the SQLite level, you could get into triggers to analyze the
data before its being inserted or updated.

For me, it depends on how you want to look at the issue as far as what part
of the entire application scope should be defending the data.  IMO, it
should be the database that defends its data that it holds, while others
state that the database should be just raw data.  Both aspects (IMO) are
absolutely valid, but its a matter of thought and how you (And your team?)
decide to ensure your data remains safe.

On Thu, Nov 2, 2017 at 8:23 AM, Rajat Pal  wrote:

> Hi Team,
>
>
>
> We have identified an issue with SQLite database which seems to be a
> potential bug.
>
>
>
> In SQLite database, All the values of datetime fields are saved as text
> without any format check. We can save any text value in the datetime
> column. This behavior is creating problem for us because for different
> regional settings,  datetime values are getting saved in different format.
>
>
>
> For example, if the regional format is English(United States), the value
> is saved as "2017-09-21 00:00:00" but if the regional format is
> Finnish(Finland), the value is saved as "2017-10-27 00.00.00". So some
> dates have colon(:) as time separator and some dates have dot (.) as time
> separator.
>
>
>
> Below query can illustrate the problem:
>
>
>
> Create Table Table1(TestDate DATETIME);
>
>
>
> Insert Into Table1 values (datetime('now','localtime'));
>
>
>
> insert into Table1 values ("2017-11-01 17:31:41");
>
>
>
> insert into Table1 values ("2017-11-01 17.32.41");
>
>
>
> insert into Table1 values ("Testing");
>
>
>
> select * from Table1;
>
>
>
> Can you please let us know if there is any way we can force the datetime
> field to have the datetime values in same format only.
>
>
>
> Regards,
>
> Rajat Pal
> ___
> 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] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Hick Gunter 
There is no "date" datatype in SQLite, an das you yourself attest, SQLite is 
returning exactly whatever was originally inserted.

It is up to your application to define the allowed format for storing datetimes 
and to provide conversion between the chosen storage format and the 
presentation to the user.

Hint: You could write a user defined function and use that in a check 
constraint to enforce your chosen format.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Rajat Pal
Gesendet: Donnerstag, 02. November 2017 13:24
An: sqlite-users@mailinglists.sqlite.org
Cc: Joy George Chittilapailly ; Minghua Feng 
; Rohit Udasi 
Betreff: [EXTERNAL] [sqlite] SQLite DB - DateTime field values are not 
consistent

Hi Team,



We have identified an issue with SQLite database which seems to be a potential 
bug.



In SQLite database, All the values of datetime fields are saved as text without 
any format check. We can save any text value in the datetime column. This 
behavior is creating problem for us because for different regional settings,  
datetime values are getting saved in different format.



For example, if the regional format is English(United States), the value is 
saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland), 
the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as 
time separator and some dates have dot (.) as time separator.



Below query can illustrate the problem:



Create Table Table1(TestDate DATETIME);



Insert Into Table1 values (datetime('now','localtime'));



insert into Table1 values ("2017-11-01 17:31:41");



insert into Table1 values ("2017-11-01 17.32.41");



insert into Table1 values ("Testing");



select * from Table1;



Can you please let us know if there is any way we can force the datetime field 
to have the datetime values in same format only.



Regards,

Rajat Pal
___
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] SQLite DB - DateTime field values are not consistent

2017-11-02 Thread Rajat Pal 
Hi Team,

 

We have identified an issue with SQLite database which seems to be a potential 
bug.

 

In SQLite database, All the values of datetime fields are saved as text without 
any format check. We can save any text value in the datetime column. This 
behavior is creating problem for us because for different regional settings,  
datetime values are getting saved in different format.

 

For example, if the regional format is English(United States), the value is 
saved as "2017-09-21 00:00:00" but if the regional format is Finnish(Finland), 
the value is saved as "2017-10-27 00.00.00". So some dates have colon(:) as 
time separator and some dates have dot (.) as time separator.

 

Below query can illustrate the problem:

 

Create Table Table1(TestDate DATETIME);

 

Insert Into Table1 values (datetime('now','localtime'));

 

insert into Table1 values ("2017-11-01 17:31:41");

 

insert into Table1 values ("2017-11-01 17.32.41");

 

insert into Table1 values ("Testing");

 

select * from Table1;

 

Can you please let us know if there is any way we can force the datetime field 
to have the datetime values in same format only.

 

Regards,

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

[sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

2017-11-02 Thread yaro 
Hello,
I built TCl 8.6 with SQLite extension (3.2.0) which I embedded into a C++
application. Because I need SQLite in both TCL and C++ I defined the SQLite
macro of (the extension) to export all the SQLite C API's. So the the built
SQLite DLL library TCL extension exports both the TCL interface as well as
the C++ interface.

While using this SQLite DLL in C++ I created a shared-cache in-memory db
which I once mistakenly did not close. This in-memory db remains in memory
even after I restart/rebuild the C++ application. I know this because I
tried to create the same table with the same connect as before and I get
error "table ... already exists" from SQLite.

I wonder why this is so, because I thought that SQLite will free all its
resources everytime the application closes?

Yaro



--
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] [EXTERNAL] sqlite_master structure

2017-11-02 Thread Hick Gunter 
If an entry refers to an index, the field "name" will contain the name of the 
index, while the field "tbl_name" contains the name of the table the index 
refers to.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Korot
Gesendet: Donnerstag, 02. November 2017 04:37
An: Discussion of SQLite Database ; 
General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] sqlite_master structure

Hi, ALL,
I see that sqlite_master have 2 fields: name and tbl_name. It looks like they 
have the same value in my case.

Is there a scenario when those 2 are different?
And what should I check for the "table name"?

Thank you.
___
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