[sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

2014-10-01 Thread Paul Quinn 
Our existing implementation is failing due to inconsistency between SQLite 
3.7.17 and current 3.8.6. Might have existed in earlier 3.8, but we're just 
upgrading now.

In 3.7, issuing "PRAGMA index_list(tablename)" to retrieve the details of an 
index, sqlite3_column_count() would return 0 for automatic integer primary key 
indexes, basically indicating there was no explicit index created for that 
tablename. But in 3.8, sqlite3_column_count() returns '3' and we can proceed to 
evaluate the primary key index. We noticed the 'name' column for this integer 
primary key index is "" (empty string).

Is this behavior change by design?
We are working around it by checking for the "" (empty string) named index. Is 
there a better way to identify this index returned by the Pragma is the 
automatically create one?

Thanks

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

[sqlite] Interrupt PRAGMA integrity_check

2014-10-01 Thread Paul Quinn 
It appears that the long-running task of 'PRAGMA integrity_check' does not 
respond to being cancelled during operation. We've experimented with 
"sqlite3_interrupt" and "sqlite3_progress_handler" both which say they can 
interrupt long running tasks. Looking at SQLite's implementation it appears the 
cancellation flag is only checked at towards the end of the integrity checking 
task, rather than during its operation.

Is it possible to make the integrity check task interruptible?

Thanks

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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread jose isaias cabrera 


"Igor Tandetnik" wrote...


On 10/1/2014 10:34 AM, Stephan Beal wrote:

You're doing integer math. You need floating point:

select round(1/2,10) as t;


You probably meant  round(1.0/2, 10), or   round(1/2.0, 10)  or similar.


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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread jose isaias cabrera 


"Stephan Beal" wrote...

On Wed, Oct 1, 2014 at 4:34 PM, Stephan Beal  
wrote:



You're doing integer math. You need floating point:

select round(1/2,10) as t;
0.5



And this time with the right copy/paste buffer:

sqlite> select round(1.0/2,10) as t;
0.5
I was going to reply to your original email and ask what version were you 
using.;-)  Yes, this one works.  And thank you all that responded. 


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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread John Hascall 
​You got bit by integer division...

asw-1# {773} *sqlite3*
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> *select round(133.0/122000.0,10) as t;*
0.0010901639
sqlite>


BTW, there are other tools for simple math...​

asw-1# {772} *bc -l*

*scale=10133/122000*
.0010901639


On Wed, Oct 1, 2014 at 9:27 AM, jose isaias cabrera  wrote:

>
> So, I needed to get a really low percentage and I went to the SQlite3
> command prompt and I typed,
>
> select  round(133/122000,10) as t;
>
> that gave me 0.0.  Then, I said, ok, let's try this,
>
> select round(1/2,10) as t;
>
> that also gave me 0.0.  Then I said, h, let me try this,
>
> select round(10/2,10) and that gave me 5.0, which is what I expect.  Is
> there a way that I can calculate some fractional numbers in the sqlite3
> command line?  I am using v3.8.6.  Thanks.
>
> 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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread Stephan Beal 
On Wed, Oct 1, 2014 at 4:34 PM, Stephan Beal  wrote:

> You're doing integer math. You need floating point:
>
> select round(1/2,10) as t;
> 0.5
>

And this time with the right copy/paste buffer:

sqlite> select round(1.0/2,10) as t;
0.5


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread tonyp 

Exactly!

-Original Message- 
From: John Hascall

Sent: Wednesday, October 01, 2014 5:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] CLI dump command ignores view dependencies

I think his point was:

...

But, the existing situation where the first is​ 
illegal but the second is legal has

a certain illogic.

JohnOn Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal  wrote:


On Wed, Oct 1, 2014 at 3:53 PM,  wrote:

> If it indeed does matter, then shouldn’t dropping view a (in the above
> example) also drop view b, automatically?
>

That assumes view 'a' somehow knows that it is the only consumer of 'b',
which it cannot know. Views from other db files, possibly not attached,
might be consumers of 'b'.


--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby
Wolf
___
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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread Igor Tandetnik 

On 10/1/2014 10:34 AM, Stephan Beal wrote:

You're doing integer math. You need floating point:

select round(1/2,10) as t;


You probably meant  round(1.0/2, 10), or   round(1/2.0, 10)  or similar.
--
Igor Tandetnik

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

Re: [sqlite] calculating in the command line interface

2014-10-01 Thread Stephan Beal 
On Wed, Oct 1, 2014 at 4:27 PM, jose isaias cabrera  wrote:

>
> So, I needed to get a really low percentage and I went to the SQlite3
> command prompt and I typed,
>
> select  round(133/122000,10) as t;
>
> that gave me 0.0.  Then, I said, ok, let's try this,
>
> select round(1/2,10) as t;
>
> that also gave me 0.0.  Then I said, h, let me try this,
>

You're doing integer math. You need floating point:

select round(1/2,10) as t;
0.5


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] calculating in the command line interface

2014-10-01 Thread jose isaias cabrera 

So, I needed to get a really low percentage and I went to the SQlite3 command 
prompt and I typed,

select  round(133/122000,10) as t;

that gave me 0.0.  Then, I said, ok, let's try this,

select round(1/2,10) as t;

that also gave me 0.0.  Then I said, h, let me try this,

select round(10/2,10) and that gave me 5.0, which is what I expect.  Is there a 
way that I can calculate some fractional numbers in the sqlite3 command line?  
I am using v3.8.6.  Thanks.

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

Re: [sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread John Hascall 
I think his point was:

illegal to create a view referring to a non-existing table
AND
illegal to later create a situation where you have a view referring to a
non-existing table
is logical

legal to create a view referring to a non-existing table
AND
legal to later create a situation where you have a view referring to a
non-existing table
is logical
​
But, the existing situation where the first is​
​ illegal but the second is legal has
a certain illogic.


John​


On Wed, Oct 1, 2014 at 9:05 AM, Stephan Beal  wrote:

> On Wed, Oct 1, 2014 at 3:53 PM,  wrote:
>
> > If it indeed does matter, then shouldn’t dropping view a (in the above
> > example) also drop view b, automatically?
> >
>
> That assumes view 'a' somehow knows that it is the only consumer of 'b',
> which it cannot know. Views from other db files, possibly not attached,
> might be consumers of 'b'.
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
> those who insist on a perfect world, freedom will have to do." -- Bigby
> Wolf
> ___
> 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] CLI dump command ignores view dependencies

2014-10-01 Thread Stephan Beal 
On Wed, Oct 1, 2014 at 3:53 PM,  wrote:

> If it indeed does matter, then shouldn’t dropping view a (in the above
> example) also drop view b, automatically?
>

That assumes view 'a' somehow knows that it is the only consumer of 'b',
which it cannot know. Views from other db files, possibly not attached,
might be consumers of 'b'.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread tonyp 
On the other hand, creation of a view could ignore the dependencies, and only 
check them at run time.

It's curious that the following sequence seems to behave this way:

create view a as select 1;
create view b as select * from a;
select * from b;
.d
drop view a;
.d
select * from b;--Error: no such table: main.a
create view a as select 3;
select * from b;

---

Apparently, a view can exist without its dependencies, so the order of 
definition should not have to matter.
If it indeed does matter, then shouldn’t dropping view a (in the above example) 
also drop view b, automatically?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread Simon Slavin 

On 1 Oct 2014, at 7:25am, lchis...@paradise.net.nz wrote:

> Any database which has had any maintenance done on its views using any GUI or
> CLI tool could manifest this scenario, and I think it should explicitly
> documented as fixing it is non-trivial.

Nice catch.

It may be possible to fix the shell tool by having it construct a dependency 
graph of entities (tables, indexes, views, triggers) and use that to decide in 
what order to dump the entities.  Otherwise, yes, there's a problem with the 
.dump command and the problem should be documented.

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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

2014-10-01 Thread cl 
Mark Lawrence  wrote:
> > in a directory that contains sqlite3 databases it lists every file in
> > the hierarchy (why, there are thousands) and then fails with:-
> > 
> > Ended searching
> > Available databases:
> > Can't locate object method "new" via package "JSON::XS" at
> > /usr/local/share/perl/5.18.2/App/DBBrowser/Opt.pm line 797.
> 
> Looks like a dependency got missed. I would suggest installing the
> missing JSON::XS package and trying again:
> 
> sudo cpan JSON::XS
> 
Did that, now I get:

Ended searching
Undefined subroutine ::DBBrowser::print_error_message called at
/usr/local/share/perl/5.18.2/App/DBBrowser.pm line 186.

> It is probbaly better that we stop this thread here as it is off-topic
> for the mailing list. But feel free to reply to me privately if you
> still have issues getting it to run.
> 
OK, but I'm using the usenet interface so I can't see your E-Mail
directly.  I'm chris AT isbd.co.uk.

-- 
Chris Green
·

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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

2014-10-01 Thread Mark Lawrence 
On Wed Oct 01, 2014 at 11:16:54AM +0100, c...@isbd.net wrote:
> > 
> Thanks, that looks something like what I'm after.  However what the
> chances are of installing it successfully on a Beaglebone Black I
> don't know!  I'm currently installing it on my desktop machine to try
> (quad core, 8Gb memory, loads of disk) and it appears to be installing
> and compiling half of the world! On a lowly BBB with 512k memory and
> only 4Gb disk in total I think it might be a bit slow.

The Perl ecosystem is very modular and the default installation
includes only very "core" modules. And often the number of dependencies
for a Perl-based application has little to do with the run-time speed,
due to the test-driven culture.

> in a directory that contains sqlite3 databases it lists every file in
> the hierarchy (why, there are thousands) and then fails with:-
> 
> Ended searching
> Available databases:
> Can't locate object method "new" via package "JSON::XS" at
> /usr/local/share/perl/5.18.2/App/DBBrowser/Opt.pm line 797.

Looks like a dependency got missed. I would suggest installing the
missing JSON::XS package and trying again:

sudo cpan JSON::XS

> It sounds as if db-browser could be useful but the user interface is a
> little quirky!

I agree that the interface seems a little quirky. I believe there is a
way to specify a database file directly, but you'll have to read the
documentation yourself to find out how.

It is probbaly better that we stop this thread here as it is off-topic
for the mailing list. But feel free to reply to me privately if you
still have issues getting it to run.

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

[sqlite] CLI dump command ignores view dependencies

2014-10-01 Thread lchishol 
Hi all,

People on this list, and the SQLite documentation, say that the .dump command of
the CLI generates SQL which can be .read to rebuild a database.
What is omitted is the fact that the SQL .read may fail if you have created
views referring to other views and they are output in the SQL in the wrong 
order.

It seems that .dump emits SQL for tables, views, etc in the order they are 
defined.
However, if you have created view A, then view B which refers to A, then drop A
and recreate it, the dumped SQL contains the CREATE VIEW statement for B first,
which fails as A is not yet defined.

I have had this happen in the past, and verified it with a few lines of SQL in
the 3.8.6 CLI.

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE x(a,b);
sqlite> CREATE VIEW va as select a from x;
sqlite> CREATE VIEW vb as select * from va;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a,b);
CREATE VIEW va as select a from x;
CREATE VIEW vb as select * from va;
COMMIT;
sqlite> drop view va;
sqlite> CREATE VIEW va as select b from x;
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE x(a,b);
CREATE VIEW vb as select * from va;
CREATE VIEW va as select b from x;
COMMIT;

It is possible to get around this by editing the SQL file (best), or by saving
the schema separately and reading it several times (with more and more 'table x
already exists' error lines) until everything has been created.

Any database which has had any maintenance done on its views using any GUI or
CLI tool could manifest this scenario, and I think it should explicitly
documented as fixing it is non-trivial.

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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

2014-10-01 Thread cl 
Mark Lawrence  wrote:
> On Wed Oct 01, 2014 at 09:50:16AM +0100, c...@isbd.net wrote:
> > > 
> > Linux (xubuntu),
> > ...
> > in this case I need a command line utility.
> 
> You may be interested in the db-browser *terminal application*
> available from CPAN:
> 
> https://metacpan.org/pod/distribution/App-DBBrowser/bin/db-browser
> 
> It can be installed as follows:
> 
> sudo cpan App::DBBrowser
> 
> After which the "db-browser" script should be in your path:
> 
> db-browser -s $DIRECTORY_CONTAINING_SQLITE_DATABASE
> 
Thanks, that looks something like what I'm after.  However what the
chances are of installing it successfully on a Beaglebone Black I
don't know!  I'm currently installing it on my desktop machine to try
(quad core, 8Gb memory, loads of disk) and it appears to be installing
and compiling half of the world! On a lowly BBB with 512k memory and
only 4Gb disk in total I think it might be a bit slow.

... and on my desktop, now it's built it doesn't seem to work anyway.

On doing:-
db-browser -s .

in a directory that contains sqlite3 databases it lists every file in
the hierarchy (why, there are thousands) and then fails with:-

Ended searching
Available databases:
Can't locate object method "new" via package "JSON::XS" at
/usr/local/share/perl/5.18.2/App/DBBrowser/Opt.pm line 797.


It sounds as if db-browser could be useful but the user interface is a
little quirky!

-- 
Chris Green
·

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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

2014-10-01 Thread Mark Lawrence 
On Wed Oct 01, 2014 at 09:50:16AM +0100, c...@isbd.net wrote:
> > 
> Linux (xubuntu),
> ...
> in this case I need a command line utility.

You may be interested in the db-browser *terminal application*
available from CPAN:

https://metacpan.org/pod/distribution/App-DBBrowser/bin/db-browser

It can be installed as follows:

sudo cpan App::DBBrowser

After which the "db-browser" script should be in your path:

db-browser -s $DIRECTORY_CONTAINING_SQLITE_DATABASE

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

Re: [sqlite] Is there a simple command line data editor for sqlite3?

2014-10-01 Thread cl 
RSmith  wrote:
> On 2014/09/30 19:23, c...@isbd.net wrote:
> > Is there such a thing?
> >
> > Of course I know and use the sqlite3 command line utility but that's
> > just a way of running SQL from the command line.
> >
> > I want some sort of wrapper that runs a SELECT to get the data from a
> > table, presents it to me in editable format and then does an UPDATE or
> > INSERT to put the changed data back.
> >
> Did you have a specific OS in mind?
> 
> Linux, MacOS and Windows all have a myriad of SQLite editors. A simple 
> google would no doubt reveal a lot, but if you say which OS, 
> I'm sure on here we can point out some good ones.
> 
Linux (xubuntu), but I think you missed the important bit - *command
line*.  I know there are lots of neat GUI sqlite3 browsing utilities
for Linux, I use a couple myself (sqlitebrowser and sqliteman) but in
this case I need a command line utility.

-- 
Chris Green
·

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