[sqlite] PRAGMA foreign_key_list

[Peter Haworth]

The documentation for the above command on the SQLite web site is silent
about the output of the above PRAGMA.  I can guess at some of it but does
anyone have a full description of it?

Thanks,
Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: database is locked

[JohnG]

On 06/18/2014 05:17 PM, JohnG wrote:

gelmjw@voyager /var/www/sqlite3/finviz $ sqlite3 -version
3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
gelmjw@voyager /var/www/sqlite3/finviz $

gelmjw@voyager /var/www/sqlite3/finviz $ uname -a
Linux voyager 3.2.0-23-generic #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 
2012 x86_64 x86_64 x86_64 GNU/Linux

gelmjw@voyager /var/www/sqlite3/finviz $


gelmjw@voyager /var/www/sqlite3/finviz $ cat finviz.init
.stats on
.timer on
.mode columns
.headers on
select load_Extension('libsqlitefunctions.so');
gelmjw@voyager /var/www/sqlite3/finviz $ fuser finviz.db <= no one 
is using the file
gelmjw@voyager /var/www/sqlite3/finviz $ cd /var/www/sqlite3/finviz/; 
sqlite3 -init finviz.init finviz.db

-- Loading resources from finviz.init
load_Extension('libsqlitefunctions.so')
---

Memory Used: 75064 (max 76472) bytes
Number of Outstanding Allocations:   101 (max 102)
Number of Pcache Overflow Bytes: 1024 (max 1024) bytes
Number of Scratch Overflow Bytes:0 (max 0) bytes
Largest Allocation:  64000 bytes
Largest Pcache Allocation:   1024 bytes
Largest Scratch Allocation:  0 bytes
Lookaside Slots Used:0 (max 0)
Successful lookaside attempts:   0
Lookaside failures due to size:  0
Lookaside failures due to OOM:   0
Pager Heap Usage:1696 bytes
Page cache hits: 0
Page cache misses:   0
Schema Heap Usage:   0 bytes
Statement Heap/Lookaside Usage:  1664 bytes
Fullscan Steps:  0
Sort Operations: 0
Autoindex Inserts:   0
CPU Time: user 0.00 sys 0.004000
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables <=
fvall  v110   v120   v130   v140   v160   v170
sqlite> delete from v110; <=
Memory Used: 13570240 (max 1249828600) bytes
Number of Outstanding Allocations:   4425 (max 977900)
Number of Pcache Overflow Bytes: 5090048 (max 123800) bytes
Number of Scratch Overflow Bytes:0 (max 0) bytes
Largest Allocation:  8388608 bytes
Largest Pcache Allocation:   1272 bytes
Largest Scratch Allocation:  0 bytes
Lookaside Slots Used:0 (max 0)
Successful lookaside attempts:   0
Lookaside failures due to size:  0
Lookaside failures due to OOM:   0
Pager Heap Usage:5091288 bytes
Page cache hits: 974845
Page cache misses:   974845
Schema Heap Usage:   14960 bytes
Statement Heap/Lookaside Usage:  1296 bytes
Fullscan Steps:  0
Sort Operations: 0
Autoindex Inserts:   0
CPU Time: user 5.016314 sys 19.457216
Error: database is locked <=
sqlite> < =I was stuck here

Why? No one was using finviz.db beforehand. Who locked it?


Now, I open another terminal

gelmjw@voyager /var/www/sqlite3/finviz $ fuser finviz.db
/var/www/sqlite3/finviz/finviz.db: 26235
gelmjw@voyager /var/www/sqlite3/finviz $ ps aux |grep 26235
gelmjw   26235 23.5 30.7 1280028 1245304 pts/1 S+   16:53   0:24 
sqlite3 -init finviz.init finviz.db
gelmjw   26250  0.0  0.0  13588   896 pts/2S+   16:55   0:00 grep 
--colour=auto 26235

gelmjw@voyager /var/www/sqlite3/finviz $
===
Well, that is me trying to 'delete from v110;' !!!

So, I am keeping me from deleting?

How do I clear this lock condition?

Regards;
John Gelm



I apologize for reposting this.  The suggestion to run sqlite3 <[file] 
will only return to the Linux command line. I was debugging and needed 
access to the sqlite3 interactive command line.


Please review the lines above annotated with <=.

The questions are:

Why was there the "Error: database is locked"? The error was 
reproducible by using .quit and then re entering the interactive mode.


Is there a BUG? , or

How should I have cleared the error without rebooting the system?

Regards;
John Gelm





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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

On 20/6/2014 8:08 PM, Clemens Ladisch wrote:

Not without changing the SQLite code.

A non-leaf R-tree node must store the extents covered by all its
children, so these are (n-dimensional) rectangles.  At the moment,
SQLite assumes that user data has exactly the same format, so such
a change would not be trivial.



Thanks Clemens - I was afraid that might be the case.  I guess that's a 
project for a different time and day.


Best Regards,
Mohit.


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

Re: [sqlite] another challenging query

[David Cotter]

you guys are flippin' gods ya know that right?

thanks!

On Jun 19, 2014, at 10:26 PM, Igor Tandetnik  wrote:

> On 6/20/2014 1:20 AM, David M. Cotter wrote:
>> i want to delete from the table all records with plID = 1, but ONLY those 
>> that have a corresponding record where plID == 851090 and where that 
>> record's soID matches the one where plID = 1
> 
> delete from MyTable where plID = 1 and soID in (
>  select t2.soID from MyTable t2 where t2.plID = 851090
> );
> 
> -- 
> Igor Tandetnik

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

[sqlite] another challenging query

[David M. Cotter]

here's a table:



i want to delete from the table all records with plID = 1, but ONLY those that 
have a corresponding record where plID == 851090 and where that record's soID 
matches the one where plID = 1

so the query should delete rows 8-12, but leave 1-2 intact (and also leave 3-7)

there may be many other plID numbers

is there some really great reference that y'all go to for crazy queries like 
this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] R-Tree Storage Optimization for Points

[Clemens Ladisch]

Mohit Sindhwani wrote:
> I was wondering if there is a way that we could save space on the
> R-Tree storage if the item being inserting is just a single point
> (such that x1=x2 and y1=y2).

Not without changing the SQLite code.

A non-leaf R-tree node must store the extents covered by all its
children, so these are (n-dimensional) rectangles.  At the moment,
SQLite assumes that user data has exactly the same format, so such
a change would not be trivial.


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

Re: [sqlite] R-Tree Storage Optimization for Points

[Mohit Sindhwani]

Hello All...

On 20/6/2014 3:01 AM, Wolfgang Enzinger wrote:


I stand corrected. Should have tried this before:

sqlite> INSERT INTO abc VALUES(2,30,20);
Error: constraint failed

Note to self: r-tree is about *ranges* in 1 to 5 dimensions.



Coming back to the original problem again... I was wondering if there is 
a way that we could save space on the R-Tree storage if the item being 
inserting is just a single point (such that x1=x2 and y1=y2).


Thanks for the answers thus far.

Best Regards,
Mohit.

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

Re: [sqlite] another challenging query

[RSmith]

On 2014/06/20 07:20, David M. Cotter wrote:

i want to delete from the table all records with plID = 1, but ONLY those that 
have a corresponding record where plID == 851090 and where that record's soID 
matches the one where plID = 1

so the query should delete rows 8-12, but leave 1-2 intact (and also leave 3-7)

there may be many other plID numbers

is there some really great reference that y'all go to for crazy queries like 
this?


Hi David,

Igor answered the query question very well I think (should work as you asked for), as to the other question about where to go - 
well, this list is just such a resource and there are many web resources with details about querying and the intricacies of SQL, 
some of them run by the very people posting here.


What you do need though, is to get into the whole SQL thing and learn a bit more about Relational Databasing in general I think. The 
reason I think this, is that it is clear that you think your questions or intended queries were supposed to be "hard" - but to be 
quite honest, they are rather "light" or "easy" (as Igor elegantly showed) compared to the querying that mostly make the list folk 
scratch their heads.


All this means is that I think you will be amazed at finding out what all you can do with SQL querying (not just for SQLite) and 
that this usually can save a programmer an immense amount of time and/or programming once the data is in the correct formats and 
tables and the SQL can do most of the work for you.


Typical resources on the web include the very "SQL as Understood by SQLite" 
pages from our own beloved SQLite's web pages over at:
www.sqlite.org.

Further maybe some SQL explanations from w3-Schools over at:
http://www.w3schools.com/sql/
(I really like this one since it has an editor thing where you can type your 
queries and see their effects right away).

Also from one of our list members, a great resource on more technically weird 
and wonderful SQL over at:
http://www.schemamania.org/

Conclusion: SQL is awesome in all its flavours - it will save you immense 
amounts of development time - get into it!


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