Re: [sqlite] Find key,value duplicates but with differing values

[Roman Fleysher]

It is hard for me to tell which is index, which is value and so forth in your 
example, but how about this single select:

SELECT DISTINCT key, value FROM theTable;

This lists all distinct key-value possibilities. Or,

SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) 
GROUP BY key, value HAVING count() > 1;

This lists all key-value pairs with more than one value for the key. 

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Thursday, October 11, 2018 12:23 PM
To: SQLite mailing list
Subject: Re: [sqlite] Find key,value duplicates but with differing values

Maybe

...
group by partId, name
having count(distinct xmd.value) > 1;

?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dominique Devienne
Sent: Thursday, October 11, 2018 12:00 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Find key,value duplicates but with differing values

I can find duplicates fine:

select xmd.partId, parts.title, xmd.name,
   count(*) "#dupplicates",
   group_concat(xmd.value) "values",
   group_concat(xmd.idx) "indexes"
  from extra_meta_data xmd
  join parts on parts.id = xmd.partId
  group by partId, name
 having "#dupplicates" > 1;

but most actual duplicates have the same value, so are harmless.
so I'd like to select only the xmd.name rows which have differing values.

Knowing that there can be more than 2 duplicates, i.e. a slef-join is not
enough I think.
Any hints on how to go about this problem please? Thanks, --DD
___
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] Find key,value duplicates but with differing values

[Roman Fleysher]

 You are correct. Value should/ could be inside count(), but not in group.




Sent from my T-Mobile 4G LTE Device


 Original message 
From: R Smith 
Date: 10/11/18 4:29 PM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Find key,value duplicates but with differing values

On 2018/10/11 9:53 PM, Roman Fleysher wrote:
> It is hard for me to tell which is index, which is value and so forth in your 
> example, but how about this single select:
>
> SELECT DISTINCT key, value FROM theTable;
>
> This lists all distinct key-value possibilities. Or,
>
> SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable)
> GROUP BY key, value HAVING count() > 1;
>
> This lists all key-value pairs with more than one value for the key.

If I may - this won't work directly as-is since the query:

SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable)
GROUP BY key, value HAVING count() > 1;

will group by Key,Value and then output which Key-value pairs exhibit
more than one instance (count) - which is physically impossible since
counting the duplicate values from a distinct set is like asking the
number of genders among American presidents before 2018

The answer is always 1.


If, in the outer query, you Select for (and group by) Key only, then it
works.


>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of David Raymond [david.raym...@tomtom.com]
> Sent: Thursday, October 11, 2018 12:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Find key,value duplicates but with differing values
>
> Maybe
>
> ...
> group by partId, name
> having count(distinct xmd.value) > 1;
>
> ?
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Dominique Devienne
> Sent: Thursday, October 11, 2018 12:00 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Find key,value duplicates but with differing values
>
> I can find duplicates fine:
>
> select xmd.partId, parts.title, xmd.name,
> count(*) "#dupplicates",
> group_concat(xmd.value) "values",
> group_concat(xmd.idx) "indexes"
>from extra_meta_data xmd
>join parts on parts.id = xmd.partId
>group by partId, name
>   having "#dupplicates" > 1;
>
> but most actual duplicates have the same value, so are harmless.
> so I'd like to select only the xmd.name rows which have differing values.
>
> Knowing that there can be more than 2 duplicates, i.e. a slef-join is not
> enough I think.
> Any hints on how to go about this problem please? Thanks, --DD
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] INSERT OR REPLACE and foreign keys

[Roman Fleysher]

Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which 
servers as a foreign key. But I noticed a strange behavior: If the parent 
record existed, then replace mechanism replaces it, but the records from 
children tables are deleted. The foreign key is set up to cascade on delete, as 
I think it should. So it seems that "replace" is implemented as "delete then 
insert" rather than "update". Is that normal and expected? Am I doing something 
wrong?

Thank you,

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

[sqlite] nested foreign keys

[Roman Fleysher]

Dear SQLiters,

I am trying to set up what I would call "nested foreign keys":

create grandParent (id PRIMARY KEY )
create parent (id PRIMARY KEY REFERENCES grandParent(id))
create child (id PRIMARY KEY REFERENCES parent(id))

SQLite complains. Does it mean that grand children are not allowed?

Thank you,

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

[sqlite] nested foreign keys

[Roman Fleysher]

Dear SQLIters,

I am trying to set up what I would call "nested foreign keys":

create grandParent( id PRIMARY KEY)
create parent (id PRIMARY KEY REFERENCES grandParent(id))

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

Re: [sqlite] nested foreign keys

[Roman Fleysher]

The statements work. Insertion fails.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Yip [dw...@peach-bun.com]
Sent: Thursday, October 25, 2018 12:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

These statements worked for me:


CREATE TABLE grandparent (id INTEGER PRIMARY KEY);

CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));

CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));


The foreign key constraints work as you'd expect also.


What are you doing and what error are you seeing?


- David

On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am trying to set up what I would call "nested foreign keys":
>
> create grandParent (id PRIMARY KEY )
> create parent (id PRIMARY KEY REFERENCES grandParent(id))
> create child (id PRIMARY KEY REFERENCES parent(id))
>
> SQLite complains. Does it mean that grand children are not allowed?
>
> Thank you,
>
> Roman
> ___
> 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] nested foreign keys

[Roman Fleysher]

I found the cause of my issue.

I have two columns as foreign key in child, which reference corresponding pair 
in parent. But, I was setting up the references separately, not as a pair. And 
it looked like it can not work.

It works because the pair of columns in parent is PRIMARY KEY and thus has 
unique index -- the only requirement for foreign keys to work.

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Thursday, October 25, 2018 1:03 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

No, it means that you did not specify the whatisness of grandParent, parent, or 
child; and/or, you have not enabled foreign_keys.

https://sqlite.org/lang_createtable.html
https://sqlite.org/pragma.html#pragma_foreign_keys

NB:  I have compiled the CLI with foreign key enforcement ON be default.  The 
default distributions usually have foreign keys enforcement turned off, 
because, well, who wants a database that enforces referential integrity?  (All 
kidding aside, the reason that foreign key enforcement is OFF by default is to 
maintain backward compatibility with older versions of SQLite that "parsed" 
such constraints but did not allow for enforcement of them).

SQLite version 3.26.0 2018-10-23 13:48:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table grandParent (id PRIMARY KEY );
sqlite> create table parent (id PRIMARY KEY REFERENCES grandParent(id));
sqlite> create table child (id PRIMARY KEY REFERENCES parent(id));
sqlite> insert into parent values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into child values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into grandparent values (1);
sqlite> insert into parent values (1);
sqlite> insert into child values (1);
sqlite> delete from parent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from child where id=1;
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from parent where id=1;
sqlite> delete from grandparent where id=1;
sqlite>

---
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 Roman Fleysher
>Sent: Wednesday, 24 October, 2018 22:30
>To: General Discussion of SQLite Database
>Subject: [sqlite] nested foreign keys
>
>Dear SQLiters,
>
>I am trying to set up what I would call "nested foreign keys":
>
>create grandParent (id PRIMARY KEY )
>create parent (id PRIMARY KEY REFERENCES grandParent(id))
>create child (id PRIMARY KEY REFERENCES parent(id))
>
>SQLite complains. Does it mean that grand children are not allowed?
>
>Thank you,
>
>Roman
>___
>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] Grouping guidance

[Roman Fleysher]

Hypothesis can never be proven. It can only be rejected with data contradicting 
it at hand.

"..the quickest way ..." implies someone else corrects you. 


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Don V Nielsen [donvniel...@gmail.com]
Sent: Thursday, December 13, 2018 4:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Grouping guidance

Two thumbs up on Igor. They say the quickest way to the correct answer is
to post the wrong answer on the internet. Hypothesis just proven.

On Thu, Dec 13, 2018 at 3:00 PM Igor Tandetnik  wrote:

> On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> > On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> >> Making a mountain out of a mole hill, but isn't the solution more
> complex
> >> that that? The description has to be Foo & Bar. But if given the
> following,
> >> then the simple answer dies.
> >
> > select Request from MyTable group by Request
> > having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;
>
> ... and sum(Description not in ['Foo', 'Bar']) = 0
>
> Add conditions to taste.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811&sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3D&reserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811&sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] add constant to INTEGER PRIMARY KEY

[Roman Fleysher]

Dear SQLiters,

I would like to update a column declared as INTEGER PRIMARY KEY. This column is 
parent to a column of another table:

table1 ( t INTEGER PRIMARY KEY NOT NULL);

table2 (t INTEGER PRIMARY KEY NOT NULL REFERENCES table1(t) ON DELETE CASCADE 
ON UPDATE CASCADE);

I keep PRAGMA foreign_keys = 'yes'; so that when I update table1, table2 also 
gets updated. Values in column t are positive and negative integers. When I try 
to add a constant I get constraint violation:

UPDATE table1 SET t =  t + 8000;


Error: UNIQUE constraint failed: table1.t


But, if I subtract a positive constant, the update succeeds.


I understand that as I add or subtract a constant, numbers become those that 
already exist. However, by the end of the UPDATE, all numbers will still be 
unique.


It looks like the UPDATE happens in some order and if I can force the order of 
update depending on the sign of the constant, it will always succeed as I need. 
But UPDATE has no ordering mechanism because it makes no sense to have one.


Does it mean I am doing it incorrectly? How to do I update? Bracketing with 
BEGIN .. COMMIT did not help.



Thank you,


Roman

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

Re: [sqlite] add constant to INTEGER PRIMARY KEY

[Roman Fleysher]

Thank you, Keith, for suggestion.

The "t" is time index, with respect to the start of a clock. Since clock can 
only move forward, it is unique. I need to shift the start of the clock 

My clock is on raster of 10 milliseconds, so are the shifts. Thus I can use 
your method to shift twice: by (constant - 5) then by 5.

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Thursday, December 13, 2018 6:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] add constant to INTEGER PRIMARY KEY

You cannot do that.  The PRIMARY KEY is required to be unique at each "step" 
along the way, especially as this is the parent in a foreign key relationship.  
Letting alone why anyone would want to do such a thing (which is beyond my 
ken), you simply have to make sure that your values are unique.

constant = select (max(t)-min(t))*47 from table1
update table1 set t = t + constant;
update table1 set t = t - constant + (my modification);

which will work provided that (max(t)-min(t))*47 + max(t) is not greater than 
the value that can be stored in a 64-bit signed integer and
(max(t)-min(t))*47 + min(t) is not less that the value that can be stored in a 
64-bit unsigned integer
and that your new t's will all fit in a 64-bit unsigned integer.

---
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 Roman Fleysher
>Sent: Thursday, 13 December, 2018 15:39
>To: General Discussion of SQLite Database
>Subject: [sqlite] add constant to INTEGER PRIMARY KEY
>
>Dear SQLiters,
>
>I would like to update a column declared as INTEGER PRIMARY KEY. This
>column is parent to a column of another table:
>
>table1 ( t INTEGER PRIMARY KEY NOT NULL);
>
>table2 (t INTEGER PRIMARY KEY NOT NULL REFERENCES table1(t) ON DELETE
>CASCADE ON UPDATE CASCADE);
>
>I keep PRAGMA foreign_keys = 'yes'; so that when I update table1,
>table2 also gets updated. Values in column t are positive and
>negative integers. When I try to add a constant I get constraint
>violation:
>
>UPDATE table1 SET t =  t + 8000;
>
>
>Error: UNIQUE constraint failed: table1.t
>
>
>But, if I subtract a positive constant, the update succeeds.
>
>
>I understand that as I add or subtract a constant, numbers become
>those that already exist. However, by the end of the UPDATE, all
>numbers will still be unique.
>
>
>It looks like the UPDATE happens in some order and if I can force the
>order of update depending on the sign of the constant, it will always
>succeed as I need. But UPDATE has no ordering mechanism because it
>makes no sense to have one.
>
>
>Does it mean I am doing it incorrectly? How to do I update?
>Bracketing with BEGIN .. COMMIT did not help.
>
>
>
>Thank you,
>
>
>Roman
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712215292&sdata=pl14VeTl%2FTVBtEOD1azIWzSspGJDv25VndQwblaiQjM%3D&reserved=0



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712225300&sdata=k%2BFBzAh8eaHmNC66MKK1BDyTPzJGBunVXhji5CJKy0M%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] select within transaction

[Roman Fleysher]

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

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

Re: [sqlite] select within transaction

[Roman Fleysher]

Thank you! I did not know (or forgot) about ".bail on"

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Friday, June 14, 2019 3:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] select within transaction

How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue 
processing lines. So if you get rid of the "or rollback" then you'll get the 
error message and won't have to worry about it continuing on to the next lines 
in the input file despite there having been an error. And since you explicitly 
started a transaction it will leave the transaction open, and then when the CLI 
closes it will rollback the uncommitted transaction.


-Original Message-
From: sqlite-users  On Behalf Of 
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

Roman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752&sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752&sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select within transaction

[Roman Fleysher]

Thank you,  Adrian. I think this is reason changes() exist.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Adrian Ho 
Date: 6/15/19 12:25 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API 
(https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Ftclsqlite.html&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3D&reserved=0),
 then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug?

[Roman Fleysher]

Dear SQLiters,


I can not figure out what I am doing wrong. In testing, I simplified to the 
following:

CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);

SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY 
refVolume;

refVolume   CAST(10*max(cosSquared) AS INT)
--  ---
2   9
3   9
4   9
5   9
.
31  9
32  9
33  9

That is, we see that for refVolumes between 2 and 33, the value of the CAST() 
is always 9. Thus, I expect the following statement to output the same list of 
refVolumes. But it does not:

SELECT DISTINCT refVolume FROM cosSquared
 WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM 
cosSquared);

refVolume
--
2

What am I doing wrong? I am using version 3.16.

Thank you for your help,

Roman

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

Re: [sqlite] Is this a bug?

[Roman Fleysher]

Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry [smith.bar...@gmail.com]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this a bug?

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] rowid as foreign key

[Roman Fleysher]

Dear SQLiters,

Is it possible to link two tables using rowid, the implicit column? I tried and 
it did not work, so I presume the answer to my question is "no".

Thank you,

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

Re: [sqlite] rowid as foreign key

[Roman Fleysher]

I am sorry, I did not ask the question correctly. I omitted "... link two 
tables, using foreign key...". 

I now see last sentence on http://sqlite.org/lang_createtable.html which states 
that it is not possible.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Gwendal Roué [gwendal.r...@gmail.com]
Sent: Monday, July 24, 2017 3:17 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

> Le 24 juil. 2017 à 20:58, Roman Fleysher  a 
> écrit :
>
> Dear SQLiters,
>
> Is it possible to link two tables using rowid, the implicit column? I tried 
> and it did not work, so I presume the answer to my question is "no".

Hello Roman,

The answer is yes. For example :

$ sqlite3 /tmp/db.sqlite
sqlite> CREATE TABLE foo (c);
sqlite> CREATE TABLE bar (c);
sqlite> INSERT INTO foo (c) VALUES ('foo');
sqlite> INSERT INTO bar (c) VALUES ('bar');
sqlite> SELECT foo.rowid, foo.c, bar.rowid, bar.c FROM foo, bar WHERE foo.rowid 
= bar.rowid;
1|foo|1|bar

Gwendal Roué

___
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] rowid as foreign key

[Roman Fleysher]

Thank you, Keith. I realize this. I have a (parent) table that does not 
explicitly define primary key --- a unique identifier of the row. I need to 
temporarily create a child table that "extends" the row, adds columns to it. I 
wanted to use foreign keys on internal rowid column to link them. Perhaps I 
will use ALTER TABLE mechanism.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Monday, July 24, 2017 6:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

Yes, but to use the rowid or a table in a foreign key declaration you need to 
specify it explicitly in the table definition declaring it as the INTEGER 
PRIMARY KEY.  (and exactly INTEGER PRIMARY KEY, although you can also add the 
AUTOINCREMENT keyword if you need to ensure unique values for some reason).

eg:  CREATE TABLE test (rowid INTEGER PRIMARY KEY, data text);

You need to do this because if you do not then the rowid is merely an internal 
identifier for the row in the underlying table b-tree structure and is not 
"stable" across vacuum or dump/load operations.

---
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 Roman Fleysher
>Sent: Monday, 24 July, 2017 12:58
>To: General Discussion of SQLite Database
>Subject: [sqlite] rowid as foreign key
>
>Dear SQLiters,
>
>Is it possible to link two tables using rowid, the implicit column? I
>tried and it did not work, so I presume the answer to my question is
>"no".
>
>Thank you,
>
>Roman
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] command shell .timeout

[Roman Fleysher]

Dear SQLiters,

I am using sqlit3 command shell. It has ".timeout" command. What is the 
difference between:

.timeout MS
PRAGMA busy_timeout = milliseconds;

I am getting "database is locked" when accessing the same file from multiple 
concurrent shells and trying to set timeouts to avoid this.

(By the way, PRAGMA busy_timeout = milliseconds; prints new timeout to screen. 
I would expect printing only if new value is not provided.)

Thank you

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

Re: [sqlite] command shell .timeout

[Roman Fleysher]

Thank you, Richard.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Thursday, August 03, 2017 3:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] command shell .timeout

On 8/3/17, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] transfer records with foreign key

[Roman Fleysher]

Dear SQLiters,

I have two tables linked by a foreign key, linkID. I need to transfer content 
of these two tables into two corresponding tables in another database 
preserving the link. However, the second database already has records and 
numeric value of linkID can not be preserved. Nor its value is important 
because it simply links the tables. How do I INSERT into two tables, preserving 
the link while allowing for value of linkID to change. LinkID is defined as 
INTEGER PRIMARY KEY.

It looks to me that I should create a temporary view on the both tables in the 
second database and try to use INSTEAD OF trigger. Is that the right way? Or I 
have to create a temp table that maps old linkID to new linkID, somehow.

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

Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Roman Fleysher]

Dear Richard,
Dear SQLiters,

This is not clear to me as well.

If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I 
want to create a TEMPORARY trigger that upon insert in db1.t does something 
with db2.t.  Because:

TEMP triggers are not subject to the same-database rule. A TEMP trigger is 
allowed to query or modify any table in any ATTACH-ed database.

I need to be able to specify db2.t in the body of the trigger. But this is not 
allowed, right? Then how could it work? I mean it does not. Should it work?

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Friday, June 09, 2017 9:23 AM
To: SQLite mailing list
Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT 
Statements Within Triggers

On 6/9/17, Mark Brand  wrote:
>
>
> On 09/06/17 14:47, Richard Hipp wrote:
>> The documentation has been updated to clarify the ambiguity and to
>> hopefully make it easier to understand.
>
> Thanks. The exception for non-TEMP triggers is something I was hoping
> for too:
>
>> For non-TEMP triggers, the table to be modified or queried must exist
>> in the same database as the table or view to which the trigger is
>> attached. TEMP triggers are not subject to the same-database rule. A
>> TEMP trigger is allowed to query or modify any table in any ATTACH
>> -ed database.
>
> But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?

The . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 schema is relaxed for TEMP triggers.

>
>
> create table t (x);
> insert into t values (0);
> create temp view v as select * from t;
>
> create temp trigger trg1 instead of update on temp.v
> begin
>  update temp.t set x = new.x;
> end;
>
> Error: near line 5: qualified table names are not allowed on INSERT,
> UPDATE, and DELETE statements within triggers
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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] PRAGMA optimize; == no busy handler?

[Roman Fleysher]

Dear SQLiters,

Vacuuming seems to belong to a different thread, but let me say that it is not 
always warranted. Vacuuming may change/reassign ROWIDs. If you have two 
databases (backup and production?) that used to be linked via such a key, it 
will break.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jens Alfke [j...@mooseyard.com]
Sent: Wednesday, October 04, 2017 6:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?

> On Oct 4, 2017, at 10:30 AM, Richard Hipp  wrote:
>
> The PRAGMA optimize command is our effort to move further in the
> direction of a "smart" SQLite that always automatically "does the
> right thing" with respect to gathering and using database statistics.

That’s a great move. Along the same lines, it would be nice if SQLite could 
vacuum once in a while without being asked (like a good housemate ;) What I’m 
doing now is based on some advice I read in a blog post*:

// If this fraction of the database is composed of free pages, vacuum it
static const float kVacuumFractionThreshold = 0.25;
// If the database has many bytes of free space, vacuum it
static const int64_t kVacuumSizeThreshold = 50 * MB;

// After creating a new database:
exec("PRAGMA auto_vacuum=incremental”);

// Just before closing a database:
exec("PRAGMA optimize");
int64_t pageCount = intQuery("PRAGMA page_count");
int64_t freePages = intQuery("PRAGMA freelist_count");
if ((pageCount > 0 && (float)freePages / pageCount >= 
kVacuumFractionThreshold)
|| (freePages * kPageSize >= kVacuumSizeThreshold)) {
exec("PRAGMA incremental_vacuum");
}

(To forestall any retorts that “you don’t need to vacuum because SQLite will 
reuse the free space later”: Yes, you do need to, on a space-constrained device 
like a phone. Otherwise your app never reclaims any storage back to the OS for 
use by other apps, and you get customer issues like “I deleted all my old junk 
from the app but it’s still using 10GB of storage, please help my phone is out 
of space”…)

—Jens

* https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/
___
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] XOR operator

[Roman Fleysher]

The point is that terminology is chosen for a reason and can not be dismissed. 
"Flexibly typed" means it is typed. It means SQLite knows how many bytes: 
without knowing it would not be able to establish equality "IS".  Flexibly 
means columns can contain values of mixed types,  but each value still has a 
type. And this is a very very big advantage of SQLite.

Perhaps longer term is "flexibly strongly typed". Perhaps because "typed" 
implies "strongly" (what is a weak type?), strongly is redundant.

Roman




 Original message 
From: R Smith 
Date: 10/8/17 9:38 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] XOR operator

On 2017/10/06 6:03 PM, Richard Hipp wrote:
> On 10/6/17, R Smith  wrote:
>> I'd also like to see a Unary NOT operator, such that you can say: a = !b
> In SQL and SQLite that would be:  a = NOT b

Apologies, I thought it obvious from the context that I meant a binary
operation, not a Boolean operation NOT.

i.e. 0xA (base16) = 1010 (base2) so that NOT 0xA = 0101 = 0x5... so if a
= 0xA then !a = 0x5, but that only works IF we are restricted to "a"
being 1 byte in size, which brings us to the following point:

>
>> But, I guess that's only feasible in a strongly typed language.
> (1) I object to the characterization of SQLite not being "strongly
> typed".  SQLite is "flexibly typed" in the sense that it provides the
> application with a lot of flexibility with regard to what datatypes
> are allowed to be stored in a particular column or participate in an
> operation.  Other SQL database engines are "rigidly typed".  Those
> other SQL implementations are much more judgmental about what you can
> and cannot do with your data.
>
> (2) Why is rigid typing required in order to implement boolean negation?

Answering (2): A strongly typed language that defines
INT/UINT/WORD/INT64/etc. as specifically a 32-bit or 64-bit
signed/unsigned representation, or "Byte" as a 8-bit unsigned
representation will be sensible to say a = not b; where a and b are both
typed as BYTE values. but if you don't know how many bits are "meant" to
be in "a", how to determine how many bits must be negated / "notted" /
changed to produce the result of "NOT b" in the way described up there.

If for example a = 0xA then !a might be 0x5 for a nibble, but it will be
0xF5 for a byte, 0xFFF5 for a WORD, 0xFF5 for a 32bit INT, etc. etc.

It's often used in masking bit flag sequences. a = (a & !0x3) would see
"a" being switched so that it's LSB's 0 and 1 gets switched off while
leaving the others in tact. Yes, I could have just said a = (a & (0xFF -
0x03)) or even work out what that result is and go a = (a & 0xFC), but
if the bits that get switched off lives in a variable (b), then a = (a &
!b) is just so much more sensible / elegant. I'm even ok with syntax
like a = (a & (not b))... but that's not how SQLite works, or can work,
unless it becomes strongly typed.


As to (1)... Cool, call it flexibly typed then, I'm ambivalent to the
terminology, my point is about the variable sizes not being set in stone.


___
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] Energy consumption of SQLite queries

[Roman Fleysher]

Dear Ali,

A couple of comments. Indeed lots of energy is transferred into heat, but not 
all. Therefore, using temperature (after calibrating specific heat coefficient 
of the device ) is not a good method. Some energy is radiated as visible and 
invisible light and hard to catch it all. Some as vibration. Some energy is 
used to flip the bits on the disk. So to speak internal energy. 

Thus, monitoring input power is the only way. However, since many jobs are 
running, the results will be indeed inconsistent. Some people, therefore, 
erroneously conclude that the question is not answerable. This is not true.

A properly crafted research proposal could get funding needed to accomplish 
this fine goal. I expect 1 million US dollars for 5 years should be close to 
sufficient. Make sure you measure how long SQLIte performs the task, record 
power consumption in that period. Then run machine for the same period without 
SQLIte. Difference in power consumption is what was due to  SQLite, controlling 
for the other processes. Obviously, caching and other things already mentioned, 
will affect the numbers. Thus, you need to properly randomize these trials, 
playing with their durations. You will have to perform many of these (therefore 
5 year long project) to average out all fluctuations. 

Given complexity of the project, you should consider getting initial funding to 
design it in the first place and obtain preliminary data (and necessary 
equipment) to justify and ensure future success. It appears, given your initial 
email, that such funding is well underway towards being secured. Be sure to 
control temperature and humanity in the room, because cooling fans also consume 
energy, which depends on their speed and viscosity of the air. The tidal forces 
(of the moon) will affect friction in bearings of all moving parts (fans, 
disks). Be sure to either co-vary for them or randomize experiments for 
different phases of the moon. DO NOT MOVE computer while experiment is running. 
Coriolis force will affect friction in all rotating parts as well.

In summary, this is a perfectly doable experiment, if carefully planned and 
executed. Radio astronomy easily reaches sensitivities of 10^{-9}. You can do 
it too!

At conclusion of the 5 year research period, SQLite will be much different from 
what it is today. So will kernels of operating systems, hardware etc. 
Therefore, at conclusion of the research, you will have answered how much power 
was consumed by SQLite 5 years ago. I am sure this will be very valuable piece 
of information then, after all the money and efforts are spent. Because of this 
short delay (5 years is short on the astronomical time scale) and because of 
the experience you gained by conclusion of the project, I am rather certain you 
will be able to obtain additional funding to continue and refine the answer to 
the newer version of SQLite available then. The future is in your hands!


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Ali Dorri [alidorri...@gmail.com]
Sent: Tuesday, November 21, 2017 4:49 PM
To: Robert Oeffner
Cc: SQLite mailing list
Subject: Re: [sqlite] Energy consumption of SQLite queries

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner  wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri 
>> To: SQLite mailing list 
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> > ail.com>
>> Content-Type: text/plain; charset="UTF-8"
>>
>> Dear All,
>>
>> I am doing a research on the energy consumed by a query in SQLite. I have
>> a
>> program which fills a database with blocks of data. Then, it attempts to
>> remove some data from

[sqlite] primary key in another column

[Roman Fleysher]

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

Roman

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

Solved with trigger, but I can not use NOT NULL for the fileName column:

CREATE TRIGGER AAA AFTER INSERT ON A 
BEGIN
  UPDATE A SET fileName = 'prefix'||NEW.id WHERE id=NEW.id;
END;

INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
...

Is that a right solution?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Friday, January 26, 2018 4:43 PM
To: General Discussion of SQLite Database
Subject: [sqlite] primary key in another column

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

Roman

___
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] primary key in another column

[Roman Fleysher]

Igor, you are absolutely right.

But

I will use this table as a manager. There will be multiple columns holding 
various file names. The names can be random, but I want humans to be able to 
easily inspect. After table is filled, an operation "for each row"  will get 
files in some columns and produce files in other columns. This is done outside 
of SQLite. "For each row" will process several rows in parallel because they 
are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.

"For each row" is equivalent to SELECT, but it operates on the files 
themselves. This can be implemented within SQLIte by loading extension. I 
investigated this route (and even asked questions on this list) and eventually 
concluded that it is better to do outside because of the way parallel execution 
is done (sometimes sent to a compute cluster grid engine for queueing.) 

This makes no sense from the database point of view: No reason to hold 
redundant data with such a simple algorithm to generate it.


Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:33 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 4:43 PM, Roman Fleysher wrote:
> I would like to use primary key as a way to create unique column entry:
>
> CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)
>
> such that file name is always prefix followed by the ID for the content to be:
>
> ID  fileName
>
> 1   prefix_1
> 2   prefix_2

Why do you want to store redundant data? What's the actual problem this is 
supposed to help you solve? As stated, this looks like an XY problem ( 
http://xyproblem.info/ )

--
Igor Tandetnik

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:

forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns

The files are images. Example:

forEachRow  addImages outputColumn column1 column2 

ForEachRow will loop over the rows (in parallel batches if it can) and apply 
the command given to it with its arguments. Image processing is then a sequence 
of these "forEach" commands.


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:56 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 5:47 PM, Roman Fleysher wrote:
> I will use this table as a manager. There will be multiple columns holding 
> various file names. The names can be random, but I want humans to be able to 
> easily inspect. After table is filled, an operation "for each row"  will get 
> files in some columns and produce files in other columns. This is done 
> outside of SQLite. "For each row" will process several rows in parallel 
> because they are independent. Some operations might fail and will be recored 
> in the proper columns. After all the work is done, the manager table is 
> discarded.

I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik


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

Re: [sqlite] primary key in another column

[Roman Fleysher]

I think I effectively did as you suggested using triggers. I insert NULL into 
the ID column to create a row. This triggers the trigger to run update on the 
table to populate the columns based on the just created ID. Is this what you 
suggested?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 6:10 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:

You said: After table is filled, an operation "for each row"  will... I suggest 
running this UPDATE statement at the end of "table is filled", before "an 
operation will..." part.
--
Igor Tandetnik

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
applied to any table. If I modify SELECT inside it to fit specific purpose, 
forEachRow will use universality.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

One question I have, couldn't you just omit the fileName column from the
able, and compute it in the select query that is getting the data?

On 1/26/18 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:
>
> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>
> The files are images. Example:
>
> forEachRow  addImages outputColumn column1 column2
>
> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
> the command given to it with its arguments. Image processing is then a 
> sequence of these "forEach" commands.
>
>
> Roman
>
> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>> I will use this table as a manager. There will be multiple columns holding 
>> various file names. The names can be random, but I want humans to be able to 
>> easily inspect. After table is filled, an operation "for each row"  will get 
>> files in some columns and produce files in other columns. This is done 
>> outside of SQLite. "For each row" will process several rows in parallel 
>> because they are independent. Some operations might fail and will be recored 
>> in the proper columns. After all the work is done, the manager table is 
>> discarded.
> I'm still not sure I understand, but: while you are building out this manager 
> table, can't you leave fileName column blank, and then right before 
> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
> --
> Igor Tandetnik

--
Richard Damon

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

yes, I can use a view. 

forEachRow also records what failed. Updating a view requires a trigger, but I 
can compose one with the view.

Thank you for suggestion!

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:50 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

Couldn't you have it access a view which adds the columns by calculation
rather than the raw table? (and if you have some tables that don't need
such a view, create a simple pass through view).

On 1/26/18 6:30 PM, Roman Fleysher wrote:
> No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
> applied to any table. If I modify SELECT inside it to fit specific purpose, 
> forEachRow will use universality.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Richard Damon [rich...@damon-family.org]
> Sent: Friday, January 26, 2018 6:26 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] primary key in another column
>
> One question I have, couldn't you just omit the fileName column from the
> able, and compute it in the select query that is getting the data?
>
> On 1/26/18 6:03 PM, Roman Fleysher wrote:
>> My implementation of  "for Each row" requires all columns to be populated. 
>> It is a dumb thing:
>>
>> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>>
>> The files are images. Example:
>>
>> forEachRow  addImages outputColumn column1 column2
>>
>> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
>> the command given to it with its arguments. Image processing is then a 
>> sequence of these "forEach" commands.
>>
>>
>> Roman
>>
>> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>>> I will use this table as a manager. There will be multiple columns holding 
>>> various file names. The names can be random, but I want humans to be able 
>>> to easily inspect. After table is filled, an operation "for each row"  will 
>>> get files in some columns and produce files in other columns. This is done 
>>> outside of SQLite. "For each row" will process several rows in parallel 
>>> because they are independent. Some operations might fail and will be 
>>> recored in the proper columns. After all the work is done, the manager 
>>> table is discarded.
>> I'm still not sure I understand, but: while you are building out this 
>> manager table, can't you leave fileName column blank, and then right before 
>> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
>> --
>> Igor Tandetnik
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

You are right,  Igor. Clear case of XY problem. I will remove trigger.


Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Igor Tandetnik 
Date: 1/26/18 9:03 PM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:20 PM, Roman Fleysher wrote:
> I think I effectively did as you suggested using triggers. I insert NULL into 
> the ID column to create a row. This triggers the trigger to run update on the 
> table to populate the columns based on the just created ID. Is this what you 
> suggested?

Roughly, though running a single statement at the end seems simpler, and likely 
goes faster, than setting up a trigger.
--
Igor Tandetnik

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

[sqlite] exit status of command shell

[Roman Fleysher]

Dear SQLiters,


I am using sqlite3 shell from bash scripts and I stumbled on what I think is 
incorrect exit code on error. In the first scenario, on error the exit code is 
1 -- expected, in the second it is 0 -- unexpected. The error message is the 
same in both. Is that normal?


echo -e "ww; \n.exit" | sqlite3

Error: near line 1: near "ww": syntax error

echo $?

1


echo -e "ww" | sqlite3

Error: near line 1: near "ww": syntax error

echo $?

0

Thank you,

Roman

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

Re: [sqlite] exit status of command shell

[Roman Fleysher]

Thank you, Peter, for confirming. I am using somewhat outdated version.

I think this is a relatively minor issue. It surfaced for me because I mostly 
use bash shell to access database and rely on error codes to report status. As 
a work around I now always add ".exit" at the end of the SQL statement.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
petern [peter.nichvolo...@gmail.com]
Sent: Thursday, April 05, 2018 3:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] exit status of command shell

Roman.  That's a good one.  It affects the command status of well formed
SQL as well:

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
0

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter

On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] probably recursive?

[Roman Fleysher]

Dear SQLiters,

I have trouble solving this problem, maybe it is impossible?

I have a table with two columns x and y, both integers. Imagine they are 
coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
dots, and all y's that have more than nY dots. Both conditions must be 
simultaneous in the following sense:

If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
deletion, y=3 which previously had more than nY dots no longer passes the 
threshold and thus y=3 must be deleted too. This could cause deletion of some 
other x, etc. At the end, number of dots on all vertical lines must be more 
than nX and number of dots on all horizontal lines must be more than nY.

Could this be achieved with SQLite?

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

Re: [sqlite] probably recursive?

[Roman Fleysher]

With recursive route, I am thinking I need to build deleteList(x,y). But I can 
not come up with a way to use deleteList only once in the FROM after UNION and 
not in subqueries , as required by WITH RECURSIVE. Assuming pairsTable(x,y) is 
the input table: 

WITH RECURSIVE deleteList(x, y) AS 
( SELECT NULL, NULL
  UNION
  SELECT x, y FROM pairsTable 
  WHERE x IN (SELECT x FROM (SELECT x, y FROM pairsTable 
   EXCEPT 
   SELECT x, y FROM 
deleteList WHERE x IS NOT NULL)
   GROUP BY x HAVING count(x) < 25)
)
SELECT x, y FROM deleteList;



Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Tuesday, May 01, 2018 10:27 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

My initial thought on this would be recursive on delete triggers. You're 
limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really 
big cascades wouldn't fully complete. You can raise the limit, but 
mathematically speaking there's still going to be a limit then.

Will have to think about the recursive CTE route later.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Tuesday, May 01, 2018 8:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] probably recursive?

That depends on what you mean by "Could this be achieved in SQLite?".

There is no query (in any SQL engine) that can depend on a sub-query
that is itself dependent on the outcome of the main query. This is what
makes recursion beautiful, but then there is also no CTE (or other query
in any SQL engine) that can recurse over multiple states of data (i.e.
query data in one single query to reflect results from both before and
after a delete in the source table), nor can a CTE be updated or deleted
from, its data must persist atomically (with some exceptions when using
non-deterministic functions, like random).

These are not so much "inabilities" of SQL engines, but more due to
explicit SQL and set-algebra rules.

So this is not possible in a single query.

You can of course "achieve" it using any SQL engine by constructing a
temporary table, and then repeatedly run a DELETE query for all x values
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x)
< nX, rinse, repeat until  both SELECT y HAVING COUNT(x) < nX and SELECT
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this
would be painfully slow next to a simple software algorithm that
prunes/resolves a 2-dimensional array - exponentially worse so for
larger grid sizes.


On 2018/05/01 2:45 AM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have trouble solving this problem, maybe it is impossible?
>
> I have a table with two columns x and y, both integers. Imagine they are 
> coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
> dots, and all y's that have more than nY dots. Both conditions must be 
> simultaneous in the following sense:
>
> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
> deletion, y=3 which previously had more than nY dots no longer passes the 
> threshold and thus y=3 must be deleted too. This could cause deletion of some 
> other x, etc. At the end, number of dots on all vertical lines must be more 
> than nX and number of dots on all horizontal lines must be more than nY.
>
> Could this be achieved with SQLite?
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] probably recursive?

[Roman Fleysher]

Agree. Thank you.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 01, 2018 12:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

On 1 May 2018, at 5:34pm, Roman Fleysher  wrote:

> With recursive route, I am thinking I need to build deleteList(x,y).

Rather than actually delete rows, if you can, insert a new column in the table 
of all points.  It starts with every row set to TRUE.  When you decide a row 
doesn't count the value gets set to FALSE.

This will be faster than doing the processing and file handling involved in 
deleting rows.

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] probably recursive?

[Roman Fleysher]

Pairs (x,y) do not repeat.

Actual x and y are positive integers, but I do not see how being positive can 
be relevant. Integer is important for sorting/comparison.


Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 5:23 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Is there a uniqueness constraint on your initial data? Can the same coordinate 
be listed multiple times?

Is there a requirement that X > 0 and Y > 0?

> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>
>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>
>> I just realised that
>
> That was intended to be personal email.  Apologies, everyone.
>
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Roman Fleysher]

Dear Barry,

The statement about the square is not obvious to me. The requirements on counts 
in x and y are different.

I also imagine answer could be two or several non-overlapping  "rectangles". 
"Rectangles" will not be densely filled with dots, they might have empty spots 
either because the points were never on the list or were eliminated.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 6:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher  
> wrote:
>
> Pairs (x,y) do not repeat.
>
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
>
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
>
> Is there a requirement that X > 0 and Y > 0?
>
>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>>
>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>>
>>> I just realised that
>>
>> That was intended to be personal email.  Apologies, everyone.
>>
>> 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
> ___
> 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] probably recursive?

[Roman Fleysher]

nX is a number, the smallest allowed count. There are two conditions,  count of 
dots along horizontal line and count of dots along verticals.



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Barry Smith 
Date: 5/1/18 7:40 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] probably recursive?

Ah my bad, I misunderstood the initial condition. nX is a function of X. My 
statements were only true if nX=X. Well, sorry about the noise.

> On 2 May 2018, at 8:20 am, Roman Fleysher  
> wrote:
>
> Dear Barry,
>
> The statement about the square is not obvious to me. The requirements on 
> counts in x and y are different.
>
> I also imagine answer could be two or several non-overlapping  "rectangles". 
> "Rectangles" will not be densely filled with dots, they might have empty 
> spots either because the points were never on the list or were eliminated.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Well those constraints simplify your problem.
>
> In the resultant dataset, the largest X and Y values will be equal, and the 
> largest X will have and entry for every coordinate from (X, 1) to (X, X). 
> Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
> (Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
> points outside that square will be culled, all points on and inside the 
> square will be kept.
>
> Since you know that, you now have a one dimensional problem to solve. It 
> still seems a little recursive to me, but it should be easier because you 
> only need to find a single number (which you can then plug into a delete 
> statement).
>
> If my statement about the square is not obvious to prove in your head I can 
> try write a proof for that but I'm not much good at proofs.
>
>> On 2 May 2018, at 7:27 am, Roman Fleysher  
>> wrote:
>>
>> Pairs (x,y) do not repeat.
>>
>> Actual x and y are positive integers, but I do not see how being positive 
>> can be relevant. Integer is important for sorting/comparison.
>>
>>
>> Roman
>>
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>>
>> Is there a uniqueness constraint on your initial data? Can the same 
>> coordinate be listed multiple times?
>>
>> Is there a requirement that X > 0 and Y > 0?
>>
>>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>>>
>>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>>>
>>>> I just realised that
>>>
>>> That was intended to be personal email.  Apologies, everyone.
>>>
>>> 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
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Roman Fleysher]

Thank you Cezary and others who commented.

For some reason, I did not receive email from Cezary, only comments on it.

I was under impression that RECURSIVE can not be used in sub-query. I see that 
it can.

But, most importantly, could you elaborate more on how it works. I agree it is 
n-to-n problem. But the solution merges all data into a single cell with all 
pairs, which is counter to relational solution.

I ask for details, if possible, because the actual problem  that I have to 
solve is a bit more complicated: I have two of such lists good(x,y) and 
bad(x,y) with a coupling condition that if x is removed from one list, it must 
be removed from the other. This is easy to add for ones who understand how it 
works. 

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
E.Pasma [pasm...@concepts.nl]
Sent: Friday, May 04, 2018 10:35 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Cezary H. Noweta wrote:
>>
>>> At the beginning I would like to agree with that the problem is
>>> iterative rather then recursive one. However
R. Smith wrote:
>
>> LOL, that might be the hackiest query I ever seen, but kudos mate,
>> that's bloody marvellous!

Cezary, thanks for the diverting solution. I've been looking into
solving sudokus along the same lines. A function GROUP_SPLIT as an
inverse of GROUP_CONCAT would be handy here. Below is the query as it
looks when there was such a function, or actually a (function like)
virtual table with column elem. It is exactly half the size.
Nevertheless the fun is to achieve the goal purely in SQL.
Thanks, E Pasma.

CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 + 10)
% 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT (random()
% 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1 FROM cte
WHERE n < 100) SELECT x, y FROM cte;

WITH
   params(nx, ny) AS (SELECT 6, 8),
   main(elem, rest) AS (
 SELECT NULL, (
   WITH
 state(clock, points, xaxis, yaxis, nxmin, nxmax, nymin,
nymax) AS (
   SELECT
 0,
 (SELECT GROUP_CONCAT(x || ' ' || y) FROM points),
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM
points GROUP BY x)), (SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT
y, COUNT(y) n FROM points GROUP BY y)), (SELECT MIN(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MAX(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MIN(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y)), (SELECT MAX(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y))
   UNION ALL
   SELECT
 (clock + 1) % 3,
 CASE clock WHEN 0 THEN
 (SELECT GROUP_CONCAT(x || ' ' || y) FROM (
   SELECT
 CAST(elem AS INTEGER) x,
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
   FROM GROUP_SPLIT (state.points)
 )
 WHERE (x NOT IN (
   SELECT x FROM (
 SELECT
   CAST(elem AS INTEGER) x,
   CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
   ) WHERE n < (SELECT nx FROM params)
 )) AND (y NOT IN (
   SELECT y FROM (
 SELECT
   CAST(elem AS INTEGER) y,
   CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.yaxis)
   ) WHERE n < (SELECT ny FROM params)
 ))) ELSE points END,
 CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM (
   SELECT
 CAST(elem AS INTEGER) x
   FROM GROUP_SPLIT (state.points)
 ) GROUP BY x)) ELSE xaxis END,
 CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y, COUNT(y) n FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
   FROM GROUP_SPLIT (state.points)
 ) GROUP BY y)) ELSE yaxis END,
 CASE clock WHEN 2 THEN
 (SELECT MIN(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
 )) ELSE nxmin END,
 CASE clock WHEN 2 THEN
 (SELECT MAX(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
 )) ELSE nxmax END,
 CASE clock WHEN 2 THEN
 (SELECT MIN(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.yaxis)
 )) ELSE nymin END,
 CASE clock WHEN 2 THEN
 (SELECT MAX(n) FROM (
   SELECT
 CAST

Re: [sqlite] 3.24 draft - upsert

[Roman Fleysher]

Why does SQLite have to follow what PostgreSQL does? I thought SQLite is the 
leader.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp 
Date: 5/9/18 5:48 AM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] 3.24 draft - upsert

On 5/9/18, Olivier Mascia  wrote:
> About:
>
> "Column names in the expressions of a DO UPDATE refer to the original
> unchanged value of the column, before the attempted INSERT. To use the value
> that would have been inserted had the constraint not failed, add the special
> "excluded." table qualifier to the column name."
>
> Why using 'excluded' wording for this?

Because that is what PostgreSQL does.  I also thought that "new" would
have been a better choice, but they didn't consult me.  :-)

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

[sqlite] clocks in SQLite

[Roman Fleysher]

Dear SQLIters,

I use datetime('now') to record when a job gets started and stopped. As soon as 
a job stops, new is started. Thus stop of the previous should be within a 
second of the start of the next, as you see in the first few lines. But then 
the clock jumps.

Job 1847 ends at 16:44:11 and job 1852 starts at 18:47:46 (and actually 
subsequently dies as evidenced by its stop being NULL). Next job, 2283, is 
started BEFORE job 1852. RunID is INTEGER PRIMARY KEY and for this purpose is 
auto incrementing. Jobs are also running on other nodes, therefore runID is not 
contiguous for this node.


runID  hostname   start   stop
-- -- --- ---
1841   loginnode4 2018-05-14 07:53:42 2018-05-14 10:05:41
1843   loginnode4 2018-05-14 10:05:41 2018-05-14 12:18:05
1845   loginnode4 2018-05-14 12:18:05 2018-05-14 14:30:50
1847   loginnode4 2018-05-14 14:30:50 2018-05-14 16:44:11
1852   loginnode4 2018-05-14 18:47:56
2283   loginnode4 2018-05-15 18:18:59

Could it indicate other issues than the clock itself? It is highly unlikely 
that clock happened to jump forward at the time when 1852 was finishing (at 
16:44:11). Time of start of 2283 looks correct, agrees with my watch, because I 
started this job manually.

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

Re: [sqlite] clocks in SQLite

[Roman Fleysher]

Thank you for pointing the 24 hours. I did not notice the day change. 

Now, I have no idea how this can happen. I will investigate more. 

Roman


From: Graham Holden [sql...@aldurslair.com]
Sent: Tuesday, May 15, 2018 3:39 PM
To: Roman Fleysher
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] clocks in SQLite

Tuesday, May 15, 2018, 7:49:32 PM, Roman wrote:

> Job 1847 ends at 16:44:11 and job 1852 starts at 18:47:46 (and
> actually subsequently dies as evidenced by its stop being NULL).

How do you KNOW that your program didn't spend 2 hours 3 minutes
either not noticing job 1847 had finished, or deciding what to do next
once it had noticed? Do you KNOW, say, that THAT process didn't die
and was restarted?

> Next job, 2283, is started BEFORE job 1852. RunID is INTEGER PRIMARY
> KEY and for this purpose is auto incrementing. Jobs are also running
> on other nodes, therefore runID is not contiguous for this node.

Job 2283 starts NEARLY 24 hours AFTER job 1852!

> runID  hostname   start   stop
> -- -- --- ---
> 1841   loginnode4 2018-05-14 07:53:42 2018-05-14 10:05:41
> 1843   loginnode4 2018-05-14 10:05:41 2018-05-14 12:18:05
> 1845   loginnode4 2018-05-14 12:18:05 2018-05-14 14:30:50
> 1847   loginnode4 2018-05-14 14:30:50 2018-05-14 16:44:11
> 1852   loginnode4 2018-05-14 18:47:56
> 2283   loginnode4 2018-05-15 18:18:59

> Could it indicate other issues than the clock itself? It is highly
> unlikely that clock happened to jump forward at the time when 1852
> was finishing (at 16:44:11). Time of start of 2283 looks
> correct, agrees with my watch, because I started this job manually.

> Roman

Regards,
Graham Holden


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

[sqlite] select * where is/like ?

[Roman Fleysher]

Dear SQLiters,

I have a table with examID column. When I try to select specific ID I have to 
use "like" instead of "=". Why is that? Why does not last SQL query produce 
nothing?

SQLite version 3.8.8.3 2015-02-25 13:29:11
sqlite> select examID, typeof(examID) from mainDB.Exam where mainDB.Exam.examID 
like '30';
examID  typeof(examID)
--  --
30  integer   
sqlite> select examID, typeof(examID) from mainDB.Exam where mainDB.Exam.examID 
like 30;
examID  typeof(examID)
--  --
30  integer   
sqlite> select 'a'||examID||'a', typeof(examID) from mainDB.Exam where 
mainDB.Exam.examID like 30;
'a'||examID||'a'  typeof(examID)
  --
a30a  integer   
sqlite> select 'a'||examID||'a', typeof(examID) from mainDB.Exam where 
mainDB.Exam.examID=30;
sqlite>

Thank you,

Roman

[sqlite] select * where is/like ?

[Roman Fleysher]

CREATE TABLE Exam(
  examID  TEXT PRIMARY KEY NOT NULL


I will also add, as most inexperienced programmers do, "but it worked before". 
The database was created with older version of SQLITE and the code worked with 
the same older version. Could it matter?

Thank you,

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 19, 2015 1:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?

On 19 May 2015, at 5:51am, Roman Fleysher  
wrote:

> Why does not last SQL query produce nothing?

What is the schema (the CREATE TABLE command) for your Exam table ?

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

[sqlite] select * where is/like ?

[Roman Fleysher]

I guess not good:

sqlite> PRAGMA integrity_check;
integrity_check  
-
row 37 missing from index sqlite_autoindex_Exam_1
row 38 missing from index sqlite_autoindex_Exam_1
row 39 missing from index sqlite_autoindex_Exam_1
row 40 missing from index sqlite_autoindex_Exam_1
row 41 missing from index sqlite_autoindex_Exam_1
row 42 missing from index sqlite_autoindex_Exam_1
row 43 missing from index sqlite_autoindex_Exam_1
row 44 missing from index sqlite_autoindex_Exam_1
row 45 missing from index sqlite_autoindex_Exam_1
row 46 missing from index sqlite_autoindex_Exam_1
row 47 missing from index sqlite_autoindex_Exam_1
row 48 missing from index sqlite_autoindex_Exam_1
row 49 missing from index sqlite_autoindex_Exam_1
row 50 missing from index sqlite_autoindex_Exam_1
row 51 missing from index sqlite_autoindex_Exam_1
row 52 missing from index sqlite_autoindex_Exam_1


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 19, 2015 11:20 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?

On 19 May 2015, at 4:07pm, Roman Fleysher  
wrote:

> CREATE TABLE Exam(
>  examID  TEXT PRIMARY KEY NOT NULL

Works fine for me.  I tested inserting as text, integer and real in case they 
did something weird to LIKE.

SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Exam(
   ...>  examID  TEXT PRIMARY KEY NOT NULL);

sqlite> INSERT INTO Exam VALUES ('30');
sqlite> SELECT * FROM Exam;
30
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text

sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text
sqlite>

sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30.0);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam;
a30.0a|text

Is your database corrupt ?  Can you run the PRAGMA on it ?

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

[sqlite] select * where is/like ?

[Roman Fleysher]

Now I have two questions:

1. I created database from scratch using new version of SQLITE and PRAGMA 
integrity_check; produces "missing index" as before. How could I broke it if 
all indexing is done on insertion?

2. Replacing "=" with "IS" in the original WHERE solved the problem. However, 
manual says "=" and "IS" are identical except when treating "NULL". What is the 
difference?

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Roman Fleysher 
[roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 19, 2015 11:23 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?

I guess not good:

sqlite> PRAGMA integrity_check;
integrity_check
-
row 37 missing from index sqlite_autoindex_Exam_1
row 38 missing from index sqlite_autoindex_Exam_1
row 39 missing from index sqlite_autoindex_Exam_1
row 40 missing from index sqlite_autoindex_Exam_1
row 41 missing from index sqlite_autoindex_Exam_1
row 42 missing from index sqlite_autoindex_Exam_1
row 43 missing from index sqlite_autoindex_Exam_1
row 44 missing from index sqlite_autoindex_Exam_1
row 45 missing from index sqlite_autoindex_Exam_1
row 46 missing from index sqlite_autoindex_Exam_1
row 47 missing from index sqlite_autoindex_Exam_1
row 48 missing from index sqlite_autoindex_Exam_1
row 49 missing from index sqlite_autoindex_Exam_1
row 50 missing from index sqlite_autoindex_Exam_1
row 51 missing from index sqlite_autoindex_Exam_1
row 52 missing from index sqlite_autoindex_Exam_1


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 19, 2015 11:20 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select * where is/like ?

On 19 May 2015, at 4:07pm, Roman Fleysher  
wrote:

> CREATE TABLE Exam(
>  examID  TEXT PRIMARY KEY NOT NULL

Works fine for me.  I tested inserting as text, integer and real in case they 
did something weird to LIKE.

SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Exam(
   ...>  examID  TEXT PRIMARY KEY NOT NULL);

sqlite> INSERT INTO Exam VALUES ('30');
sqlite> SELECT * FROM Exam;
30
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text

sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
a30a|text
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
a30a|text
sqlite>

sqlite> DELETE FROM Exam;
sqlite> INSERT INTO Exam VALUES (30.0);
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID 
like 30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam where Exam.examID=30;
sqlite> select 'a'||examID||'a', typeof(examID) from Exam;
a30.0a|text

Is your database corrupt ?  Can you run the PRAGMA on it ?

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

[sqlite] select * where is/like ?

[Roman Fleysher]

On 19 May 2015, at 4:43pm, Roman Fleysher  
wrote:

> Now I have two questions:
>
> 1. I created database from scratch using new version of SQLITE and PRAGMA 
> integrity_check; produces "missing index" as before.

Are you telling us that you have a sequence of commands which, done entirely 
inside the SQLite shell tool, produce a corrupt database ?  If so, please 
please please try to find a short set of commands which produce the error and 
post them here.


I am only using shell for all my SQLite "programming". Therefore, yes, shell 
commands produce corrupt database. I guess, I am changing subject of the email. 
But last "news" on the old subject is that "IN" comparison also fails. I will 
try to come up with a short set of commands.

Thank you,

Roman

[sqlite] index broken by insert

[Roman Fleysher]

Dear SQLiters,

I do  not really know what info to provide for sufficient information. I use 
SQLite shell only for all create/insert manipulations. This insert below causes 
PRAGMA integrity_check; to report missing index (what appears to be on every 
inserted row):

SQLite version 3.8.8.3 2015-02-25 13:29:11

PRAGMA foreign_keys=ON; 
ATTACH DATABASE 'demographics.sqlite' AS demo; 
INSERT OR IGNORE INTO subject(subjectID, dob, gender) 
   SELECT subjectID, dob, gender FROM demo.demographics;


The subject table is defined as:

CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


Please let me know what other info might be useful to debug, including 
debugging on my end! Integrity check on demographics.sqlite is OK.

Thank you for your help,


Roman

[sqlite] index broken by insert

[Roman Fleysher]

"Confirmation" of a bug:

When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to 
integrity failure. This is indicative of either bug in old integrity check or 
new insert. Please let me know what useful info/contribution I can make.

Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows are 
being inserted), all identical  with first and last reading:

row 1 missing from index sqlite_autoindex_Exam_1
...
row 31 missing from index sqlite_autoindex_Exam_1

In addition, I have a trigger that updates Exam table when subject table is 
updated (not on insert). I presume that trigger should not be triggered. Not 
knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the 
relationship between my subject() and Exam() tables might be relevant for the 
problem.

Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Roman Fleysher 
[roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 19, 2015 12:25 PM
To: General Discussion of SQLite Database
Subject: [sqlite] index broken by insert

Dear SQLiters,

I do  not really know what info to provide for sufficient information. I use 
SQLite shell only for all create/insert manipulations. This insert below causes 
PRAGMA integrity_check; to report missing index (what appears to be on every 
inserted row):

SQLite version 3.8.8.3 2015-02-25 13:29:11

PRAGMA foreign_keys=ON;
ATTACH DATABASE 'demographics.sqlite' AS demo;
INSERT OR IGNORE INTO subject(subjectID, dob, gender)
   SELECT subjectID, dob, gender FROM demo.demographics;


The subject table is defined as:

CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


Please let me know what other info might be useful to debug, including 
debugging on my end! Integrity check on demographics.sqlite is OK.

Thank you for your help,


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

[sqlite] index broken by insert

[Roman Fleysher]

Yes, I can provide full schema and data set. As far as I remember this mailing 
list does not accept attachments. Would that be OK to send in body of email? It 
is not that big.

Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com]
Sent: Tuesday, May 19, 2015 1:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] index broken by insert

Hi, Roman,

On Tue, May 19, 2015 at 1:23 PM, Roman Fleysher
 wrote:
> "Confirmation" of a bug:
>
> When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to 
> integrity failure. This is indicative of either bug in old integrity check or 
> new insert. Please let me know what useful info/contribution I can make.
>
> Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows 
> are being inserted), all identical  with first and last reading:
>
> row 1 missing from index sqlite_autoindex_Exam_1
> ...
> row 31 missing from index sqlite_autoindex_Exam_1
>
> In addition, I have a trigger that updates Exam table when subject table is 
> updated (not on insert). I presume that trigger should not be triggered. Not 
> knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the 
> relationship between my subject() and Exam() tables might be relevant for the 
> problem.

Can you post a complete schema of you database? What tables are made?
What indexes? Some test data to use?

Thank you.

>
> Roman
>
>
> 
> From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces 
> at mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleysher at 
> einstein.yu.edu]
> Sent: Tuesday, May 19, 2015 12:25 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] index broken by insert
>
> Dear SQLiters,
>
> I do  not really know what info to provide for sufficient information. I use 
> SQLite shell only for all create/insert manipulations. This insert below 
> causes PRAGMA integrity_check; to report missing index (what appears to be on 
> every inserted row):
>
> SQLite version 3.8.8.3 2015-02-25 13:29:11
>
> PRAGMA foreign_keys=ON;
> ATTACH DATABASE 'demographics.sqlite' AS demo;
> INSERT OR IGNORE INTO subject(subjectID, dob, gender)
>SELECT subjectID, dob, gender FROM demo.demographics;
>
>
> The subject table is defined as:
>
> CREATE TABLE gender(
>   gender  TEXT PRIMARY KEY NOT NULL,
>   description TEXT
> );
>
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
> gender');
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
> gender');
>
> -- force dob -mm-dd or NULL
> CREATE TABLE subject(
>   subjectID  INT PRIMARY KEY NOT NULL,
>   dobTEXT CHECK (dob IS date(dob)),
>   gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
>   race   TEXT,
>   handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
> );
>
>
> Please let me know what other info might be useful to debug, including 
> debugging on my end! Integrity check on demographics.sqlite is OK.
>
> Thank you for your help,
>
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] index broken by insert

[Roman Fleysher]

-- Dear SQLiters,

-- Here is schema first, table is below, 
-- followed by offending statement.
-- you can copy and paste the entire body
-- my comments are SQL compatible

-
-- STEP 1 --
-- create gender and handedness tables to fix possible values
-- then create subject table
-
CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

CREATE TABLE handedness(
  handedness  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


-- STEP 2 --
CREATE TABLE MRIMetric(
  metricName  TEXT PRIMARY KEY NOT NULL,
  description TEXT,
  units   TEXT
);


-- STEP 3 --
-- examType.examType = name of the table where
-- to find exam specifics
CREATE TABLE ExamType(
  examTypeTEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO ExamType (examType, description) VALUES ('MRIExam', 'any 
MRI exam');
INSERT OR IGNORE INTO ExamType (examType, description) VALUES 
('demographicExam', 'view on subject table');
INSERT OR IGNORE INTO ExamType (examType)  VALUES ('educationExam');

-- assume date -mm-dd 
CREATE TABLE Exam(
  examID  TEXT PRIMARY KEY NOT NULL,
  subjectID   INTEGER NOT NULL REFERENCES subject(subjectID) ON UPDATE CASCADE 
ON DELETE CASCADE,
  examTypeTEXT NOT NULL REFERENCES examType(examType) ON UPDATE CASCADE,
  commentsTEXT,
  dateTEXT CHECK (date IS date(date)),
  age INTEGER
);

-
-- create triggers to compute age on various updates and inserts
-
-- automatically compute age at the time of exam when exam is inserted
CREATE TRIGGER ageCalculatorOnExamInsert AFTER INSERT ON Exam FOR EACH ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
  + (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.examID = NEW.examID;
END;

-- automatically compute age at the time of exam when exam is updated
CREATE TRIGGER ageCalculatorOnExamUpdate AFTER UPDATE ON Exam FOR EACH ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
  + (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.examID = NEW.examID;
END;

-- automatically compute age at the time of exam when subject.DOB is updated
CREATE TRIGGER ageCalculatorOnSubjectUpdate AFTER UPDATE ON subject FOR EACH 
ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', e.date) - strftime('%Y', NEW.dob) -1
  + (strftime('%m-%d', e.date) >= strftime('%m-%d', NEW.dob)),
e.age)
FROM Exam e
WHERE Exam.examID = e.examID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.subjectID = NEW.subjectID;
END;

-- STEP 4 --
CREATE TABLE MRIExam(
  examID   TEXT NOT NULL REFERENCES Exam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  ResearchName TEXT,
  MRN  INTEGER,
  pipeline TEXT,
  DICOMFolder  TEXT,
  PRIMARY KEY (examID)
);

CREATE TABLE MRIExamAccession(
  examID  TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  accession   INTEGER,
  UNIQUE (examID, accession)
);

CREATE INDEX MRIExamAccessionIndex ON MRIExamAccession(examID);

-- STEP 5 --
CREATE TABLE badMRIMetric(
  examID  TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  metricName  TEXT NOT NULL REFERENCES MRIMetric(metricName) ON UPDATE CASCADE,
  reason  TEXT, 
  PRIMARY KEY (examID, metricName)
);

-
-- STEP 6 --
-- create wave table
-
CREATE TABLE aboutWave(
  waveID  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO aboutWave (waveID, description) VALUES ('time1', 'first 
scan');

-- force uniqueness of subject+examType+wave.
-- can not have several instances of one exam type within a wave
-- given examID can be assigned to several waves
CREATE TABLE wave(
  subjectID   INT  NOT NULL REFERENCES s

[sqlite] index broken by insert

[Roman Fleysher]

Dear Richard,
Dear Simon,
Dear SQLiters,

It is such a pleasure to deal with smart people. Pure joy. How quickly Simon 
figured out the problem and how Richard narrowed it down and fixing it.

Thank you for the work around. I implemented my own,  stupid,  work around: 
execute offending  insert using 5 year old version of sqlite that we have on 
our system.

How will i know the patch/new version is available? By checking ticket link?

Thank you,

Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp 
Date: 05/19/2015 3:37 PM (GMT-05:00)
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] index broken by insert

On 5/19/15, Roman Fleysher  wrote:
> CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN
>   INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID,
> NEW.subjectID, 'demographicExam');

Your workaround is to change the insert into examID to cast it to type
TEXT.  Like this:

.. VALUES(New.subjectID, CAST(New.subjectID AS TEXT), 'demographicExam');

This should be automatic.  We are testing the bug fix now.  The
work-around is simply to get you going until we can get a patch
release out.

>   INSERT INTO wave (subjectID, examType, waveID, examID) VALUES
> (NEW.subjectID, 'demographicExam', 'time1', NEW.subjectID);
> END;
>

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] index broken by insert

[Roman Fleysher]

Dear Richard, Simon, SQLiters,

I hope you did not take my comment to mean that my stupid solution outsmarts 
your smart solution. I wanted to say that my work is not delayed and that I am 
trying within the tools/knowledge I have to solve my problems.

Substantially, I would like to understand if CAST() modification that you 
propose should actually be the correct SQL code that I should adopt permanetly. 
We like implicit conversions, but as C++ is more strict than C with regard to 
casting, should it be my better practice to always explicitly cast?

Thank you,

Roman


From: Roman Fleysher
Sent: Tuesday, May 19, 2015 3:48 PM
To: General Discussion of SQLite Database
Subject: RE: [sqlite] index broken by insert

Dear Richard,
Dear Simon,
Dear SQLiters,

It is such a pleasure to deal with smart people. Pure joy. How quickly Simon 
figured out the problem and how Richard narrowed it down and fixing it.

Thank you for the work around. I implemented my own,  stupid,  work around: 
execute offending  insert using 5 year old version of sqlite that we have on 
our system.

How will i know the patch/new version is available? By checking ticket link?

Thank you,

Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp 
Date: 05/19/2015 3:37 PM (GMT-05:00)
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] index broken by insert

On 5/19/15, Roman Fleysher  wrote:
> CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN
>   INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID,
> NEW.subjectID, 'demographicExam');

Your workaround is to change the insert into examID to cast it to type
TEXT.  Like this:

.. VALUES(New.subjectID, CAST(New.subjectID AS TEXT), 'demographicExam');

This should be automatic.  We are testing the bug fix now.  The
work-around is simply to get you going until we can get a patch
release out.

>   INSERT INTO wave (subjectID, examType, waveID, examID) VALUES
> (NEW.subjectID, 'demographicExam', 'time1', NEW.subjectID);
> END;
>

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Dear SQLiters,

PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. 
Does it check other CHECK constraints specified in the column definition?

Thank you,

Roman

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Thank you, Simon. I saw that foreign keys must be checked separately. 

I wanted to check the behavior and set up a test database. I use (for now) 
SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' did 
not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that expected?

Thank you,

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 6:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 10 Sep 2015, at 11:06pm, Roman Fleysher  
wrote:

> PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. 
> Does it check other CHECK constraints specified in the column definition?

I'm 90% sure it does not.  You should see this one though:

<https://www.sqlite.org/pragma.html#pragma_foreign_key_check>

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

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Thank you, Simon.

Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You 
can bump up 90% of being sure it does not to 100%. It does not. Is there a way 
to do it, other than export the data out and try to re-insert it?

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 7:28 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 11 Sep 2015, at 12:04am, Roman Fleysher  
wrote:

> I wanted to check the behavior and set up a test database. I use (for now) 
> SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' 
> did not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that 
> expected?

Yeah.  That's expected.  The INT PRIMARY KEY is a special case.  It makes up 
its own number.  I forget what it does if you explicitly supply NULL as a value 
but it is a special case.

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

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

OK, Thank you.

Meanwhile (again) I check that PRAGMA integrity_check='yes' did not disable 
TEXT NOT NULL. Is that a bug in 3.8.8.3?

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 7:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 11 Sep 2015, at 12:32am, Roman Fleysher  
wrote:

> Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You 
> can bump up 90% of being sure it does not to 100%. It does not. Is there a 
> way to do it, other than export the data out and try to re-insert it?

I can't think of one.  I would argue that in SQLite4 (or some future version of 
SQLite3) "PRAGMA integrity_check" should check constraints as well as 
consistency.

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

[sqlite] bug in PRAGMA ignore_check_constraints?

[Roman Fleysher]

Dear SQLiters,

I am trying to temporarily disable CHECK constraint given in columns of table 
definition. As far as I understand, 

PRAGMA ignore_check_constraints='yes';

should do it. However this example demonstrates that it is not:

CREATE TABLE subject(
  subjectID  INT,
  gender TEXT NOT NULL
);


CREATE TABLE input(
  subjectID  INT,
  gender TEXT
);

INSERT INTO input(subjectID, gender) VALUES (1, 'female');
INSERT INTO input(subjectID, gender) VALUES (2, 'male');
INSERT INTO input(subjectID) VALUES (3);

PRAGMA ignore_check_constraints='yes';
INSERT INTO subject (rowid) SELECT rowid FROM input;

Error: NOT NULL constraint failed: subject.gender

I am using SQLite 3.8.11.1. Am I misusing the PRAGMA?

Thank you,

Roman

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Thank you Richard!

Roman

From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, September 10, 2015 7:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 9/10/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> PRAGMA integrity_check is described to check UNIQUE and NOT NULL
> constraints. Does it check other CHECK constraints specified in the column
> definition?
>

Apparently it does not.  I thought it did.  I'll add that to my list
of things to do.
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bug in PRAGMA ignore_check_constraints?

[Roman Fleysher]

I just downloaded and tested using 3.8.11.1.  It and 3.8.8.3 have the same 
behavior -- do not disable.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, September 10, 2015 7:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug in PRAGMA ignore_check_constraints?

On 9/10/15, Richard Hipp  wrote:
> On 9/10/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> I am trying to temporarily disable CHECK constraint given in columns of
>> table definition. As far as I understand,
>>
>> PRAGMA ignore_check_constraints='yes';
>
> PRAGMA ignore_check_constraints=YES;  -- no quotes.
>

I say that - turns out quotes don't matter.  PRAGMA
ignore_check_constraints='yes'; works too.  What version of SQLite are
you running?

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] datetime in CHECK

[Roman Fleysher]

Dear SQLiters,

I am trying to use CHECK constraint is column of a table to enforce datetime 
format and this works:

AcquisitionDateTEXT CHECK (AcquisitionDate IS date(AcquisitionDate))

when I insert '2015-08-10'. But this

AcquisitionDateTime  TEXT CHECK (AcquisitionDateTime IS 
datetime(AcquisitionDateTime))

when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670' fails. 
Why? How to do it properly?

Thank you,

Roman

[sqlite] datetime in CHECK

[Roman Fleysher]

Thank you. I did not notice loss of fractional seconds. I now see in the manual 
that datetime()  is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), with 
capital "S" rather than lower "f" at the end that I expected.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Cecil Westerhof [cldwester...@gmail.com]
Sent: Wednesday, May 04, 2016 5:37 PM
To: SQLite mailing list
Subject: Re: [sqlite] datetime in CHECK

2016-05-04 22:43 GMT+02:00 Roman Fleysher :

> Dear SQLiters,
>
> I am trying to use CHECK constraint is column of a table to enforce
> datetime format and this works:
>
> AcquisitionDateTEXT
> ??
> CHECK (AcquisitionDate IS date(AcquisitionDate))
>
> when I insert '2015-08-10'. But this
>
> AcquisitionDateTime  TEXT CHECK (AcquisitionDateTime IS
> datetime(AcquisitionDateTime))
>
> when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670'
> fails. Why?


?That is because datetime is not more precise as seconds. When you execute:
SELECT datetime('2015-08-10T17:19:37.670')
you get:
2015-08-10 17:19:37


How to do it properly?
>

?Do not enter the part after seconds (.670).

If that is important, executing:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2015-08-10T17:19:37.670')
gives:
2015-08-10 17:19:37.670

So you could use:
?

??CHECK (AcquisitionDate IS strftime('%Y-%m-%d %H:%M:%f',
AcquisitionDate))?

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

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Roman Fleysher]

Dear Richard,
Dear SQLiters,

Thank you, Simon, for sending the link. I would like to offer several comments 
on the podcast. 

1. Why SQLite is popular.

Instead of describing how I selected SQLite to solve our DB needs, I will 
recount story of Sony, its introduction of transistor radio that I read in 
Innovator's Dilemma by Clayton Christensen. (Very good book and author, I 
recommend.)

First transistor radios were poor in sound quality compared to those based on 
vacuum tubes. But they were lite (misspelled intentionally), and small. They 
were bought by teenagers, because they were cheap and portable. The big radio 
manufacturers did not even consider transistor radios as competitors because 
traditional competition was based on sound quality, not portability. Over the 
years, transistor technology improved and all vacuum radio manufacturers 
disappeared.

Richard said: "We do not compete against Oracle, we compete against fopen()." 
This is true, just like transistors. But SQLite displaced many big DBs and now 
Oracle etc have smaller market share. If I apply ideas of Innovator's Dilemma, 
their market share will continue to shrink. (I am not an MBA, I am a physicist, 
could be wrong but looks reasonable.)

2. Job to do

This is related to 1, and to ideas I read in Clayton Christensen books. 

Many SQL databases are very similar in what they can do, performance etc. Thus 
SQLite wins, just like Sony's first transistors, because it does NOT compete 
with them. It can not handle huge write concurrency or optimize for similar 
requests over history. Instead, it is easy to install and use. Its column 
types, affinity, makes SQLite suitable for both relational and 
entity?attribute?value models. 

It turns out that many "customers" simply do not need the functionality and 
optimization offered by big DBs. Instead, like teenagers, they need 
portability, ease of use and set up. This solves the job. Big DBs are overkill 
for such "small" jobs, requiring a lot of learning and expense. But there are a 
LOT of these small jobs and SQLite solves them admirably.

3. Code rewrite, robustness, licensing

Code rewrite or static linking make the final product more robust. Robustness 
simplifies support and debugging. Robustness attracts users. We all want OUR 
thing to work and if our thing depends on SQLite, we want SQLite to be robust. 
And thus, the SQLite licensing.

4. Fossil and other in-house software

Writing your own code is driven by the lack of needed features in available 
products. In the beginning, Ford had to build its own metallurgy plant to 
ensure quality of metal. This and 3 above are integration of what is not good 
enough to make it good together. Over the years, metallurgy industry matured 
and Ford closed this division. 

There are many other aspects in the podcast that I would like to comment. Even 
when Richard tells the story and many elements look accidental, they all fit 
into the timeline of unfolding disruptive innovation. 

SQLite was and is a disruptive innovation. SQLite is not a toy. 

Thank you for making it.


Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Saturday, May 14, 2016 4:17 PM
To: SQLite mailing list
Subject: [sqlite] Podcast with Dr Hipp: SQLite history, success and funding

Those interested in SQLite might like to listen to



Play on the page or download as an MP3.

Unusual information on Dr Hipp's early career, SQLite history, HWACI, and how 
come SQLite is free but the developers still manage to afford food and 
somewhere to sleep.

Question to ponder before you listen: Many of you know about tiny devices which 
incorporate SQLite but what do you think the biggest one is ?

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

[sqlite] replace "\n" with nothing

[Roman Fleysher]

Dear SQLiters,

I made a mistake and inserted a new line char, "\n" in the middle of a text. I 
now would like to replace it with nothing. Something like:

replace(columnName, '\n','')

But this will interpret "\n" literally, as two symbols. How do I do it?

Thank you,

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

Re: [sqlite] replace "\n" with nothing

[Roman Fleysher]

Thank you! Worked!

Roman

From: sqlite-users-boun...@mailinglists.sqlite.org 
[sqlite-users-boun...@mailinglists.sqlite.org] on behalf of R Smith 
[rsm...@rsweb.co.za]
Sent: Tuesday, July 05, 2016 3:13 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] replace "\n" with nothing

replace(columname, char(10), '');

Sometimes, depending on your OS's interpretation of '\n', it might
actually be char(13)+char(10) or such (that's hex 0x0D and 0x0A). Get
the HEX() from such a line to be sure.


On 2016/07/05 9:00 PM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I made a mistake and inserted a new line char, "\n" in the middle of a text. 
> I now would like to replace it with nothing. Something like:
>
> replace(columnName, '\n','')
>
> But this will interpret "\n" literally, as two symbols. How do I do it?
>
> Thank you,
>
> Roman
> ___
> 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 binary with Math Functions for OS-X?

[Roman Fleysher]

Can't you count how many rows there are and then sort by the variable of 
interest, limiting output to half the count, all within SQL?

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Ronald Gombach 
Date: 11/21/16 7:12 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite binary with Math Functions for OS-X?

Is there a pre-c binary of SQLIte available for down load that includes a math 
library. I particularly need the “median” function.

If not, can someone point me to instructions on compilation command line to 
include the math library (OS-X).

Thanks for any info you can share.

Ron Gombach
ron...@gombach.com 
The Gombach Group
Morrisville, PA
215-295-6555

___
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] extension to run bash

[Roman Fleysher]

Dear SQLites,

I am using exclusively sqlite3 shell for all the processing and may need 
ability to run bash commands and assign result to a column. For example:

UPDATE  result SET nRows =` wc -l fileNames` ;

Here I used `` as would be in bash for command substitution. This would run wc 
command (word count), count number of lines in each file listed in column 
fileNames and update the row correspondingly.

As far as I understand I should be able to write loadable extension to 
accomplish this. My questions are:

1. Given that I use sqlite3 shell exclusively, does this path makes sense? If 
path should be different, what is it?
2. If loadable extension is good way to go, is there an example that I could 
use given that I have zero knowledge of sqlite's internals?

3. Maybe mixing SQL and shell commands (different syntaxes) is linguistically 
inappropriate and thus difficult?

Thank you,

Roman


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

Re: [sqlite] extension to run bash

[Roman Fleysher]

Yes, Richard, this is exactly what I mean.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher  wrote:
> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would run
> wc command (word count), count number of lines in each file listed in column
> fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this.

No, You cannot do exactly what you describe with a loadable extension.

But you could, perhaps, create a loadable extension that implements a
new system() SQL function like this:

   UPDATE result SET nRows = system('wc -l ' || fileNames);

Note that || is the SQL string concatenation operator.  You didn't
say, but I'm guessing that fileNames is a column in the result table.
--
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] extension to run bash

[Roman Fleysher]

I know it is not magic. It is SQLite developers' brain and effort. But it looks 
like magic to me.

Yes, this is example I see how I could use.

I am still thinking if this is what I really need. I am working with images and 
need, among other things, some summary measures to be placed into tables for 
analysis.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:44 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher  wrote:
> Yes, Richard, this is exactly what I mean.
>

Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c
extension as a prototype from which to develop yours.

--
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] extension to run bash

[Roman Fleysher]

I am not even sure myself this is the right path.

I have table with file names and need operations to be performed on columns  
(i.e. on files). Results, numeric or new file names,  are to be recorded in a 
column. I see two ways:

From bash script, make list of rows, run commands, load results to sqlite. Or

From sqlite, use extension to run commands for each row.

Both have issues.

Roman




 Original message 
From: Richard Hipp 
Date: 1/11/17 7:23 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Bob Friesenhahn  wrote:
>
> For anyone thinking that it is a good idea to embed shell
> functionality in the SQL interpreter, it makes the SQL interpreter
> much less secure for untrusted inputs.

Nobody is suggesting that this become a standard feature of the
language.  Roman wants a "loadable extension", a separate shared
library that will only be used by Roman himself, and then under
carefully controlled circumstances.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3.17.0 does not read updated DB

[Roman Fleysher]

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

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

Re: [sqlite] 3.17.0 does not read updated DB

[Roman Fleysher]

My mistake: I do not update DB. I rename (unix mv) the DB.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

Roman
___
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] Developing a SQLite3 DB remotely

[Roman Fleysher]

I do not have big experience in the area, but have some.

I think that light weight use is not the right thing to ask. I have seen NFS 
delays of 20 seconds: file was created on one machine and showed up on another 
after 20 seconds. This depends on how heavy OTHER things are, not how heavy 
SQLite access is.

GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk 
inodes rather than files ). I never tested this (we have GPFS) and do not know 
about other file systems.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, March 23, 2017 2:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski  wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart 
from that I can’t think of anything you haven’t mentioned.  I do more 
complicated things by using SQLite as a back end to a web-facing system without 
problems.

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] Developing a SQLite3 DB remotely

[Roman Fleysher]

No. I was not aware of these tools. Are any of them good? Maintained?

I am mostly using sqlite3 shell from bash scripts. Do you know if some of them 
are suitable replacements?

Is this off the topic of the  original  question?

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Joshua J. Kugler [jos...@azariah.com]
Sent: Thursday, March 23, 2017 6:16 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

Are you aware options for true network access?

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

--
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
jos...@azariah.com - Jabber: pedah...@gmail.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
___
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] WITH inside trigger in 3.16.2

[Roman Fleysher]

Dear SQLiters,

I am trying to create a trigger with body:

WITH ...
DELETE FROM ...

and it does not seem to work (Error: near "DELETE": syntax error). But I can 
execute the body itself without errors.

Does it mean that WITH clause is not supported within trigger? I use SQLite 
version 3.16.22.

Thank you,

Roman


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

Re: [sqlite] WITH inside trigger in 3.16.2

[Roman Fleysher]

My apology, I can not read. http://sqlite.org/lang_createtrigger.html clearly 
states that CTE is not supported in triggers.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 09, 2017 4:59 PM
To: General Discussion of SQLite Database
Subject: [sqlite] WITH inside trigger in 3.16.2

[This sender failed our fraud detection checks and may not be who they appear 
to be. Learn about spoofing at http://aka.ms/LearnAboutSpoofing]

Dear SQLiters,

I am trying to create a trigger with body:

WITH ...
DELETE FROM ...

and it does not seem to work (Error: near "DELETE": syntax error). But I can 
execute the body itself without errors.

Does it mean that WITH clause is not supported within trigger? I use SQLite 
version 3.16.22.

Thank you,

Roman


___
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] SAVEPOINT with multiple databases

[Roman Fleysher]

Dear SQLiters,

I think I came to a point where I need to learn SAVEPOINTs.

I am trying to understand documentation if creation and release of save points 
covers all presently attached databases, that is those before save point is 
created? Is attaching a database just a command that will sit on the 
transaction stack as any other, INSERT/UPDATE/DELETE?

Thank you,

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

Re: [sqlite] SAVEPOINT with multiple databases

[Roman Fleysher]

Oh,  now i understand. Thank you,  Simon.


Roman



 Original message 
From: Simon Slavin 
Date: 5/16/17 5:35 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] SAVEPOINT with multiple databases


On 16 May 2017, at 10:09pm, Roman Fleysher  
wrote:

> I think I came to a point where I need to learn SAVEPOINTs.
>
> I am trying to understand documentation if creation and release of save 
> points covers all presently attached databases, that is those before save 
> point is created? Is attaching a database just a command that will sit on the 
> transaction stack as any other, INSERT/UPDATE/DELETE?

It doesn’t matter when a database is attached to a connection.  A savepoint is 
something which affects the current transaction for a connection, which is 
comprised of the INSERT/UPDATE/DELETE operations you mentioned.

One SQLite connection:
Has access to at least one database, maybe more.
Has none or one current transaction.

One transaction:
Has zero or more savepoints.

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] SAVEPOINT with multiple databases

[Roman Fleysher]

Thank you, David. Now it totally makes sense to me. I realize this is SQL not 
SQLite question. 

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Wednesday, May 17, 2017 12:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] SAVEPOINT with multiple databases

One other minor thing of note is that attaching and detaching can only happen 
outside of a transaction. So you can't add or remove attached databases in the 
middle of a transaction, and transaction commit/rollback, or savepoint 
release/rollback will never leave you with a different set of attached 
databases than before that statement.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Roman Fleysher
Sent: Wednesday, May 17, 2017 12:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] SAVEPOINT with multiple databases

Oh,  now i understand. Thank you,  Simon.


Roman



 Original message 
From: Simon Slavin 
Date: 5/16/17 5:35 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] SAVEPOINT with multiple databases


On 16 May 2017, at 10:09pm, Roman Fleysher  
wrote:

> I think I came to a point where I need to learn SAVEPOINTs.
>
> I am trying to understand documentation if creation and release of save 
> points covers all presently attached databases, that is those before save 
> point is created? Is attaching a database just a command that will sit on the 
> transaction stack as any other, INSERT/UPDATE/DELETE?

It doesn’t matter when a database is attached to a connection.  A savepoint is 
something which affects the current transaction for a connection, which is 
comprised of the INSERT/UPDATE/DELETE operations you mentioned.

One SQLite connection:
Has access to at least one database, maybe more.
Has none or one current transaction.

One transaction:
Has zero or more savepoints.

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
___
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] General R*Tree query

[Roman Fleysher]

Since coordinate system is spherical, how do you tell that RA=23:59 and 
RA=00:01 are next to each other using usual comparisons?

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: Tuesday, December 17, 2013 3:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] General R*Tree query

On 12/18/2013 12:49 AM, Brian T. Carcich wrote:
> I'm working on an SQLite solution to get at star catalogs; they are usually
> searched via Right Ascension (RA), Declination (DEC), and magnitude (mag).
>   RA,DEC is a spherical coordinate system to specify a star position on-sky;
> magnitude is related to star brightness.
>
> What I have so far is here:
>
> https://github.com/drbitboy/Tycho2_SQLite_RTree
>
>
> I started with the Tycho-2 star catalog.  It comprises 2.5 million stars in
> a flat ASCII, fixed-width catalog file (actually two files but ignore that
> for now), and an index file (map) of ~10k small RA-DEC regions, with an
> average of ~250 stars in each region.  The regions do not overlap, and all
> the stars in any one region are in contiguous lines in the catalog file.
>
> The index file does not implement any grouping or sorting by magnitude.
>   Each index region refers to
>
> A) a contiguous region on-sky with defined by a min-max RA pair and a
> min-max DEC pair.
>
> B)  a contiguous range of the lines (stars) in the flat file that are
> within that region.
>
> So the data in the index file are a reasonable starting point for an R*Tree
> in SQLite3.  I put the index file data into the virtual table using the RA
> and DEC limits for each region as the two min-max pairs of columns in the
> table, and the index table, referenced by the primary key of the virtual
> table, contains the starting and ending+1 indices (offsets actually) of the
> stars in the flat catalog file for each region.
>
> So I use the R*Tree module to get a fast lookup into the index table,
> returning index regions that overlap an input RA and DEC min-max pair, then
> step through the catalog lines for each of those regions.
>
> Here's my question:  is there any advantage to skipping the index step and
> putting the star catalog data into the virtual table itself?  One advantage
> is that I could include the magnitude in the rtree table.
>
> The reason I ask is that rtree table uses min-max pairs, but each star is a
> point so the min and max are equal for each star.  Would that break any
> implicit R*Tree rules or negate any efficiencies?

Points are fine. So long as (max>=min) for all dimensions.
R-tree will return SQLITE_CONSTRAINT if you try to insert
a record for which this is not the case.

I guess in theory storing each individual star in the
r-tree might be more efficient. Difficult to say if it
would be significant though. The r-tree and other virtual
table code is not as optimized as the core, so even if
it seems better in theory it might not be in practice.

Adding the magnitude to the r-tree structure as an extra
dimension that is never queried sounds like it might make
the r-tree structure less efficient though. R-tree will
make some effort to place records with similar magnitudes
on the same node, which won't help the query but will
presumably cause some reduction in the localization that
does matter.

Is it working now? How many more stars do you have data for?

Dan.





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


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

Re: [sqlite] General R*Tree query

[Roman Fleysher]

Perhaps this is a weird way for me to get deeper knowledge of R trees, and 
because I vaguely remember that Tyco refers to a specific epoch in which 
coordinates are defined, but would it be possible to search R tree using a 
cone, i.e. stars within a cone of certain degree around given star? This would 
require a trigonometric calculation before comparison can be made but can be 
done in a single comparison.

Or, since RA and DEC coordinates are not area preserving (nor distance) -- i.e. 
angle between stars at DEC =0 is bigger than angle between stars at DEC=80 when 
they are the same delta RA apart -- then maybe instead of defining rectangular 
FOV in RA and DEC one should be defining rectangular FOV in DEC, sin(RA)? Then 
one would not need two searches.

The goal is to find neighbors to a given star defined roughly by some metric? 
Since there's nothing magical in RA , DEC coordinates the metric could use some 
other coordinates?

Roman





 Original message 
From: "Brian T. Carcich" 
Date: 12/18/2013 1:24 AM (GMT-05:00)
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] General R*Tree query


On Tue, Dec 17, 2013 at 3:57 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

>
> Since coordinate system is spherical, how do you tell that RA=23:59 and
> RA=00:01 are next to each other using usual comparisons?


I don't; usual comparisons won't work so I do two comparisons:

I am usually looking for stars within a convex field of view (FOV),
typically a frustum with a rectangular footprint, so I determine if and
where RA=0=360 crosses that footprint, and break the FOV into two pieces,
from 0<=RA<=loRA and hiRA<=RA<=360, so loRA becomes hira in one search and
hiRA become lora in the other.

There are only three cases:  zero, one or two crossings.  Zero crossings
means I can do everything in one SELECT; one crossing means either one of
the poles is in the FOV and I search RA=0 to 360; DEC=someDEC to +/-90, or
the FOV touches RA=0(=360) from one side or the other, which reduces to the
zero case; two crossings means the poles are not in the FOV and I can do
two searches as mentioned above, from 0 up to someLowRA and from 360 down
to someHighRA.

There are some edge cases but that is basically it.

I actually handle "two or more crossings" cases the same as two cases, even
though I don't think "more" can happen with a convex FOV footprint.  For
any edge (segment of the great circle between two vertices) of the FOV that
crosses RA=0, which is easily determined since I have the vertices in XYZ
coordinates, I insert a vertex in the edge at the crossing, and then
recurse with subsets of vertices split across RA=0.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?

[Roman Fleysher]

Others on the list can give you a better advise. Mine:

If you do not need id column in OpenProjects, get rid of it and make ProjID the 
primary key. Otherwise, create index on ProjID. Either way create index on 
ProjID in OpenJobs. Better, to make ProjID a foreign key in OpenJobs to refer 
to ProjID in OpenProjects.

Multi-table solution is a bad one, even of SQLite can handle it (I think it 
can).

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of jose isaias cabrera [cabr...@wrc.xerox.com]
Sent: Wednesday, January 29, 2014 5:42 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Keeping Track of Records of IDs in one table. Possible?

Greetings!

I have two tables: OpenProjects and OpenJobs.  OpenJobs have jobs that
belong to one unique project (ProjID).  OpenProjects are projects that have
one project fathers a bunch of jobs.  The design below is found in a localDB
on the user's PC and also on a SharedDB file in a server that another 8
users also connect.  The SharedDB handles the uniqueness of the project ids
as well as the job ids. Also, please excuse my bad design, as this started
out of frustration and need to keep track of projects, etc.

The tables are created this way:
CREATE TABLE OpenProjects (id integer primary key, ProjID integer,
createDnT, unique(id));
CREATE TABLE OpenJobs (id integer primary key, ProjID integer, Task,
unique(id));

OpenProjects has data as such,
1,1,'2011-10-31 23:24:14'
2,2,'2012-12-27 10:56:43'
...
...
123999,123999,'2013-11-26 10:08:53'

lots more data, but, just showing you a little bit...

OpenJobs has data as such,
1,1,'a'
2,1,'b'
3,1,'c'
4,1,'d'
5,2,'z'
6,2,'c'
7,2,'f'
8,1,'g'
9,2,'h'
...
...
1000222,123999,'a'

Lots more data, but just showing you an idea of the sample...

I know I can do,

select * from OpenJobs where ProjID = 2;

and get all the OpenJobs ids that belong to project id 2.  But, this is
taking a long time.Long time means people are complaining and, it should
be much faster, as this is a very small DB (less than 2G).  What I would
like to do, but it does not seem possible or I don't know how to do, is to
have a table that has just the Jobs that belong to one project in one
record.  Now, the number of records in a project varies, so it is not static
(as this may involve multiple languages that have the same tasks, but the
uniqueness is the job id and the language).  I know I can create a table for
each project and push the ids to that table, i.e..

CREATE TABLE p1 (id integer primary key, unique(id));
INSERT OR REPLACE INTO p1 VALUES(select id from OpenJobs where ProjID=1);
CREATE TABLE p2 (id integer primary key, unique(id));
INSERT OR REPLACE INTO p2 VALUES(select id from OpenJobs where ProjID=2)
...
etc.

but that would make the DB with lots of tables and I don't know if Sqlite
can handle millions of tables.  So, is there a way to have one table where
one id (record) has multiple integers as well as variable in number?

Thanks for your help.

josé

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


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

[sqlite] INSERT several rows

[Roman Fleysher]

Dear SQLIters,

Is ability to insert multiple rows in one go a feature of a newer versions:

INSERT INTO myTable (designation) VALUES ('LoResFA_only'), ('HiResFA_only');

or this a wrong statement?

I have SQLite version 3.7.2.

Thank you,

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

Re: [sqlite] INSERT several rows

[Roman Fleysher]

OK, thank you, Igor! I presume there is no way to get documentation for older 
versions -- no point in keeping.

Roman



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Thursday, April 03, 2014 4:48 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] INSERT several rows

On 4/3/2014 4:23 PM, Roman Fleysher wrote:
> Is ability to insert multiple rows in one go a feature of a newer versions:
>
> INSERT INTO myTable (designation) VALUES ('LoResFA_only'), ('HiResFA_only');

Support for this syntax was introduced relatively recently. I don't
remember which version exactly, but likely 3.8.something .
--
Igor Tandetnik

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


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

Re: [sqlite] INSERT several rows

[Roman Fleysher]

Thank you for the links, Richard!

I am part of the Einstein Aging Study. The software (in internet years) should 
be as old as the people we study (in calendar years).


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, April 03, 2014 5:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] INSERT several rows

On Thu, Apr 3, 2014 at 4:52 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> OK, thank you, Igor! I presume there is no way to get documentation for
> older versions -- no point in keeping.
>

The source code to the historical documentation is online:
http://www.sqlite.org/docsrc/timeline?y=ci

Legacy documentation is not available online.  You would have to download
it and compile it yourself.

An archive of the documentation for 3.7.2 is available at
http://www.sqlite.org/sqlite_docs_3_7_2.zip

Wouldn't it be easier just to upgrade to SQLite 3.8.4.3?  Why do you feel
like you need to stick with 3.7.2 which is 3.5 years old (what is that in
internet years? 100?)

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] isBad table bad design?

[Roman Fleysher]

Dear SQLiters,

Could some help if this is bad design or my lack of knowledge of SQL:

I have a table that keeps track of bad metrics from exams: 

badMetric(examID, metric, reason)

I want to filter-out bad ones and keep only good ones from the user request:

request(examID, metric).

I thought:

SELECT request.examID, request.metric FROM request, badMetric
WHERE NOT (request.examID = badMetric.examID AND request.metric = 
badMetric.metric)

This produces empty list. Is it because the badMetric is actually empty and 
"FROM request, badMetric" produces cross product of not-empty with empty tables 
which is an empty table?

Is this a bad design and I should add a quality column:
 
badMetric(examID, metric, reason, quality)

What could be other solutions?

Thank you,


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

Re: [sqlite] isBad table bad design?

[Roman Fleysher]

Life is beautiful ! when you know what you are doing...

Thank you, Richard.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, April 03, 2014 6:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] isBad table bad design?

On Thu, Apr 3, 2014 at 6:32 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> Could some help if this is bad design or my lack of knowledge of SQL:
>
> I have a table that keeps track of bad metrics from exams:
>
> badMetric(examID, metric, reason)
>
> I want to filter-out bad ones and keep only good ones from the user
> request:
>
> request(examID, metric).
>


SELECT eximID, metric FROM request
EXCEPT SELECT eximID, metric FROM badMetric;


>
> I thought:
>
> SELECT request.examID, request.metric FROM request, badMetric
> WHERE NOT (request.examID = badMetric.examID AND request.metric =
> badMetric.metric)
>
> This produces empty list. Is it because the badMetric is actually empty
> and "FROM request, badMetric" produces cross product of not-empty with
> empty tables which is an empty table?
>
> Is this a bad design and I should add a quality column:
>
> badMetric(examID, metric, reason, quality)
>
> What could be other solutions?
>
> Thank you,
>
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] SQLite and GPFS

[Roman Fleysher]

Dear SQLiters,

I understand that SQLite relies on the underlying filesystem to perform flushes 
to disk. This is a problem in network file system (NFS) when a disk is mounted 
on several nodes of a compute cluster and SQLite is ran on them. Essentially, 
NFS disallows running SQLite concurrently on several nodes accessing the same 
database. 

What about GPFS? As I read its description, it promises to flush and report 
correctly.

Thank you,

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

Re: [sqlite] Sequential numbers

[Roman Fleysher]

> Example:
>
>
> CREATE TABLE aaa (i, seqnr);
> INSERT INTO "aaa" VALUES(10,NULL);
> INSERT INTO "aaa" VALUES(20,NULL);
> INSERT INTO "aaa" VALUES(50,NULL);
> INSERT INTO "aaa" VALUES(30,NULL);
> INSERT INTO "aaa" VALUES(20,NULL);
>
> UPDATE aaa  SET seqnr=(SELECT count() FROM aaa smaller where
> smaller.rowid <= aaa.rowid);
>
> select * from aaa;
> i|seqnr
> 10|1
> 20|2
> 50|3
> 30|4
> 20|5
>
>
> Regards
>
> Rob Golsteijn

In my opinion, the dispenser (the code that generates the insert commands) is 
the one that should be assigning the sequence numbers. It is this code only 
that knows which goes first, especially if we are talking about multithreaded 
computation. ROWID is "order as inserted by SQLite" not "order as inserted by 
dispenser". Order in which SQLite inserts is important for SQLite and its 
developers.

Roman

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

[sqlite] Beginning database question

[Roman Fleysher]

I am new to database and SQLite too. I found this in archive. What if I would 
like GUI, where I would go?


Thank you,


Roman


I assume when you say "discrete" you actually mean "bracketed" as there are
lots more than 10 heights and weights.

I don't know what Excel has to do with this unless you programmed the whole
thing in Excel already?

Is speed really a concern?  This does sound like a fairly trivial
programming effort in most any language.

The database approach would be nice if you have bracketed weights as a
simple SQL query like this should work:

Create table teams(heightlow,heighthigh,weightlow,weighthigh,team);
Insert into hw values(100,110,60,62,1);  (100-110lbs 60-62", Team#1)

Select team from teams where h > = heightlow and h < heighthigh and w >=
weightlow and w < weighthigh;

Do you want a GUI around this too?

-Original Message-
From: sqlite-users-bounces at 
sqlite.org
[mailto:sqlite-users-bounces at 
sqlite.org] On 
Behalf Of Carl Gross
Sent: Tuesday, April 16, 2013 3:28 AM
To: sqlite-users at 
sqlite.org
Subject: [sqlite] Beginning database question

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects,
but I'm having trouble determining exactly how.  I'm hoping someone in this
list may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10
discrete weights,' and each height/weight combination corresponds to one of
two 'teams.'  All of this information is hardcoded into my program.  My
program asks a user to enter his own height and weight, and the program
will output the user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be
beneficial in terms of coding efficiency, processing speed, memory usage,
or any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to
tell me how to do something.  I'm looking for someone to assure me that
SQLite (rather than a simple spreadsheet) is right for me, and to hopefully
steer me towards some documentation that may be beneficial to me.

Thanks,

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

[sqlite] sqlite shell: arrow keys

[Roman Fleysher]

Dear SQLiters,

I am new to SQLite and learning it (and SQL) using shell. It would make life 
easier if arrow keys on keyboard could be used to scroll through command 
history and along command for editing. Is there a way to enable this?

Thank you,

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

[sqlite] table with check

[Roman Fleysher]

Dear SQLiters,

I am using sqlite shell, I believe version 3.7.16.2. I created a table with 
CHECK condition as:

CREATE TABLE subject(
  subjectID  INTEGER PRIMARY KEY,
  handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL) 
);

in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail 
otherwise. But:

INSERT INTO subject (subjectID,"qqq");

actually inserts qqq. Am I doing something wrong? I read manual that newer 
versions of sqlite should enforce CHECKs.

Thank you,

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

Re: [sqlite] table with check

[Roman Fleysher]

Thank you, both 

typeof(handedness)='null'

and 

handedness is null

work. I see the problem and agree.

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Peter Aronson [pbaron...@att.net]
Sent: Friday, May 17, 2013 3:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] table with check

The "OR NULL" doesn't work the way you think -- it's going to make the whole
expression null, which apparently check constraints treat the same as not
false.  What you want there is "OR typeof(handedness)='null'".

Peter

- Original Message 
> From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Fri, May 17, 2013 12:19:21 PM
> Subject: [sqlite] table with check
>
> Dear SQLiters,
>
> I am using sqlite shell, I believe version 3.7.16.2. I created a table with
>CHECK condition as:
>
> CREATE TABLE subject(
>   subjectID  INTEGER PRIMARY KEY,
>   handedness TEXT CHECK (handedness='Left' OR handedness='Right' OR NULL)
> );
>
> in hopes to be able to insert only "Right", "Left" or nothing "", i.e. fail
>otherwise. But:
>
> INSERT INTO subject (subjectID,"qqq");
>
> actually inserts qqq. Am I doing something wrong? I read manual that newer
>versions of sqlite should enforce CHECKs.
>
> Thank you,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] autoincrement and primary key

[Roman Fleysher]

Dear SQLiters,

I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
column, I would like uniqueness to be enforced, but if NULL is supplied, I 
would like the operation to fail instead of advancing key to a new integer. 
Here is my failed attempt:

CREATE TABLE qqq (
  id INTEGER PRIMARY KEY   
);

CREATE TRIGGER qqqIdTrigger BEFORE INSERT ON qqq WHEN new.id IS NULL
  BEGIN 
select RAISE (FAIL, roma);
  END;

INSERT INTO qqq (id) VALUES (20);
INSERT INTO qqq (id) VALUES (NULL);

I tried typeof(new.id)='null' in the WHEN clause, both triggers register, but 
do not raise fail. Triggering when id is too high (WHEN new.id >20) works. I 
thought that when id being inserted is NULL it is autoincremented before 
trigger is called. But then, it must trigger the too-high version when passing 
threshold --- it did not.

I could replace INTEGER primary key by TEXT primary key and auto increment will 
go away. How can I keep integer?

Thank you,

Roman


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

Re: [sqlite] autoincrement and primary key

[Roman Fleysher]

Thank you, INTEGER -> INT solved the problem. According to manual, this will 
make search slower, but I give data integrity more weight for now.

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [i...@tandetnik.org]
Sent: Monday, May 20, 2013 4:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] autoincrement and primary key

On 5/20/2013 4:17 PM, Roman Fleysher wrote:
> I would like to use INTEGER PRIMARY KEY, but I would like to disable its 
> implicit AUTOINCREMENT feature. Namely, if INSERT specifies value of the 
> column, I would like uniqueness to be enforced, but if NULL is supplied, I 
> would like the operation to fail instead of advancing key to a new integer.

Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than
INTEGER). This way, it is not an alias for ROWID but a column in its own
right, and doesn't get assigned a value automatically.
--
Igor Tandetnik

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


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

Re: [sqlite] Serialize an in-memory database

[Roman Fleysher]

Read section on URI Filemanes, particularly mode for memory databases:

 http://www.sqlite.org/c3ref/open.html

DB Connection in backup API does not have to point to a file, it can point to 
in-memory database if URIs are enabled.

(I learned it from someone else on the list, i use SQLite for less than a 
month.)

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Petite Abeille [petite.abei...@gmail.com]
Sent: Wednesday, June 05, 2013 3:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Serialize an in-memory database

On Jun 5, 2013, at 9:10 PM, Philip Bennefall  wrote:

> Yes, I have seen the backup API. But I would like to avoid the disk file 
> entirely and just serialize to and from memory.

Lateral thinking… write your db to tmpfs…

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


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

Re: [sqlite] Feature request: add support for COMMENT statement

[Roman Fleysher]

Dear SQLiters,

I can not add solutions, since I am a physicist designing database for the 
first time, but I would like to add questions...

Object-relational mapping (ORM) is a new and interesting concept for me that I 
learned. I will read about it more. However, I do not understand why new 
functionality of SQLite is needed. Why can't the mapper use a special table of 
three columns (I will use example from Alexey):

tableName | columnName  |  Rule

user   |  username  | TITLE
user   | login   |   KEY
user   |  roles  |  LIST:role


(This table describes all classes, "user" being one of them. Perhaps another 
table is needed to keep track of instances of the classes.)

My question is (an it intersects with another topic discussed recently: 
security) how safe it is to store SQL instructions to be executed in such 
tables, say in the rule column? Name of some scripts to be executed? Class  is 
a collection of data and methods to operate them. Database is clearly designed 
to store data, and relations. What about operations? Is it, in general, a good 
idea to store SQL statements and script names to be called even if security is 
not an issue?

Can someone recommend an ORM? What are the pros and cons of using them? If this 
list is inappropriate for such discussion, please also let me know and I will 
refrain.


Thank you,

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
Sent: Friday, June 14, 2013 12:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Feature request: add support for COMMENT statement

Hello!

> Suppose you have it. What would you do with it? > What's the use case?

> --> Igor Tandetnik


The COMMENTs can be used to store any information for interchanging
with application and external systems. As example, ORM (object
relational mappers) may need some additional metainfo for all mapped
columns:  


My web-apps use metainfo like to:


user
{
  username TITLE
  loginKEY
  password PASSWORD
  rolesLIST:role
  note HIDDEN
  modified TIMESTAMP
  author   ID:user
  isactive ACTUAL
}


Where

user - table name

username, login,... - table fields

TITLE, KEY, PASSWORD - definition for rules to process tables fields
in web application and in console (import/export utils). The database
schema (including tables, indicies, FTS indicies tables, etc.) is
generated by this metainfo too. So I have the high-level domain
specific data definition language and low-level application file
format as SQLite database. Of course, it's usefull to have auto
generated schema with records versioning for all tables, fast search
for all key/title fields using FTS extension (my patches add snowball
tokenizers support), fast search for lists of identifiers (using FTS
extension too), import/export utilities, JSON routes, etc.

Unfortunately, now we need additional external [plain-text] file with
metainfo or additional table with non-trivial and non-standard mapping
between database objects and own metainfo table records.



> SQLite saves comments in table/view/index/trigger definitions:

>

> sqlite> create table t(x /* :-) */); > sqlite> .schema > CREATE TABLE t(x
/* :-) */);

>

> Regards, > Clemens


Bad idea. The schema definition can't be modified!

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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