[sqlite] Vacuum results in larger database after running pragma integrity_check

[Ben Newberg]

I've noticed with 3.18.0 that it's possible to make a database increase in
size after running pragma integrity_check (which returns "ok") and then
running vacuum.

Alternatively, vacuuming without running pragma integrity_check first keeps
the database the same size as before.

The page size on the database in question is 1024. The database starts with
no pages in the freelist. I can't reproduce this with 3.17.0.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.

--1) Before the vacuum. Database page count = 3077, and the freelist page
count is 0:
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 52
database page count: 3077
freelist page count: 0
schema cookie:   19
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3008010
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--2) Running a vacuum (without pragma integrity_check) results in the same
size of database: 3077 page count and 0 freelist page count:
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 53
database page count: 3077
freelist page count: 0
schema cookie:   20
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--3) Now running pragma integrity_check which returns "ok", and then
vacuuming. This increases the database page count to 3236:
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 54
database page count: 3236
freelist page count: 0
schema cookie:   21
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309
sqlite>

Here is the full schema:
CREATE TABLE bids_list (report_id INTEGER, price_id INTEGER, premium_id
INTEGER, period_inactive INTEGER DEFAULT (0) CHECK (period_inactive IN (0,
1)));
CREATE TABLE bids_dates (report_id integer primary key, date text, current
integer check (current in (0, 1)));
CREATE INDEX idx_price_id ON bids_list (price_id);
/* No STAT tables available */

Is the pragma fixing something in the index perhaps?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Ben Newberg]

The column can be unique as well, correct?

SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma foreign_keys = 1;
sqlite> create table x (a integer primary key, b integer unique, c text);
sqlite> insert into x (a, b, c) values (1, 11, 'a');
sqlite> insert into x (a, b, c) values (2, 22, 'a');
sqlite> insert into x (a, b, c) values (3, 33, 'b');
sqlite> create table y1 (a integer references x(a));
sqlite> insert into y1 values(1);
sqlite> create table y2 (b integer references x(b));
sqlite> insert into y2 values(11);
sqlite> create table y3 (c text references x(c));
sqlite> insert into y3 values('a');
Error: foreign key mismatch - "y3" referencing "x"
sqlite> insert into y3 values('b');
Error: foreign key mismatch - "y3" referencing "x"
sqlite>

On Mon, Feb 27, 2017 at 3:07 PM, Richard Hipp  wrote:

> On 2/27/17, James K. Lowden  wrote:
> > SQLite requires that foreign keys refer to primary
> > keys.
>
> No it doesn't.  Where did you get that impression?
>
> --
> 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] .timer command missing from CLI ?

[Ben Newberg]

Is that a homebrew version of 3.16.0?

SQLite version 3.16.0 2017-01-02 11:57:58
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite>

3.15.1 was released on 2016-11-04, but it works on that version too.

On Wed, Feb 15, 2017 at 1:09 PM, Simon Slavin  wrote:

> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
> sqlite> .timer off
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
>
>
> It’s still there in the .help command.  What happened ?  Has it been fixed
> in a later version ?
>
> 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] How to circumvent UNIQUE constraint

[Ben Newberg]

what i've done in the past is append a character to the value and make use
of "cast":

update desktops set indexNo = indexNo || '_';
update desktops set indexNo = cast(indexNo as integer) + 1;

then:
insert into desktops values (new row with index = 1);

from the docs, which i hope i'm not misreading:

http://sqlite.org/lang_expr.html#castexpr
"When casting a TEXT value to INTEGER, the longest possible prefix of the
value that can be interpreted as an integer number is extracted from the
TEXT value and the remainder ignored."

has worked for me for years but ymmv


On Mon, Jan 23, 2017 at 11:58 AM, Cecil Westerhof 
wrote:

> 2017-01-23 16:53 GMT+01:00 Clemens Ladisch :
>
> > Cecil Westerhof wrote:
> > >> UPDATE desktops
> > >> SET indexNo = indexNo  + 1
> > >>
> > >> But it does not, it gives:
> > >> Error: UNIQUE constraint failed: desktops.indexNo
> > >
> > > ​It is actually quite simple:
> > > PRAGMA ignore_check_constraints = ON
> >
> > A UNIQUE constraint is not a CHECK constraint.
> >
>
> ​But it works.
>
> --
> Cecil Westerhof
> ___
> 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] Date Formatting from Excel Float Date/Time Representation

[Ben Newberg]

select t0.key, "Issue Type",  strftime('%Y-%m-%d %H:%M:%S', (Updated -
(julianday('1970-01-01') - julianday('1899-12-30'))) + 2440587.5) Updated
from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1
on t0.key = t1.key

where "Last Comment" is not null

order by assignee;

give that a try




On Fri, Jan 6, 2017 at 2:23 PM, Jens Alfke  wrote:

>
> > On Jan 6, 2017, at 12:16 PM, Ed Lipson  wrote:
> >
> > The Updated column is properly reflected as a float, as that is the
> > internal Excel format. What formatting functions can I use to get it to
> > appear as a date time in SQL output, as it appears in Excel? I have tried
> > strftime and date but I don't get any meaningful data.
>
> This doesn’t seem like a question related to SQLite; you’ll need to find
> out the definition of Excel’s internal date/time format. Most numeric time
> formats have much larger numbers since they count in seconds, but the
> values around 42,000 that you showed would be consistent with a number of
> days since 1/1/1900. You can probably use one of the standard library
> functions to convert that into a time_t and print it.
>
> —Jens
> ___
> 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 integrity_check doesn't check constraints without indices

[Ben Newberg]

To be clear, my question about all bets being off sounded more rhetorical
in my head than it came out. I'm perfectly content with the outcome, just
wanted to share an example of something I accidently learned the hard way
when messing with schemas. And thanks to you all I know more about why it
happened.

On Oct 17, 2016 5:21 PM, "Kees Nuyt" <k.n...@zonnet.nl> wrote:

> On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg
> <ben.newb...@gmail.com> wrote:
>
> > But I've seen some strange things when messing with pragma
> writable_schema.
> > It appears all bets are off?
>
> Yes. that's why there's a warning:
> "Warning: misuse of this pragma can easily result in
>  a corrupt database file."
>
> When changing anything in the sqlite_master table you are
> circumventing all mechanisms SQLite has to keep the database
> consistent.
> What you did in your example is creating a table description in
> sqlite_master that doesn't match the physical table in the
> database, so yes, all bets are off.
>
> --
> Regards,
>
> Kees Nuyt
> ___
> 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 integrity_check doesn't check constraints without indices

[Ben Newberg]

First off, my apologies for hijacking this thread.
But I've seen some strange things when messing with pragma writable_schema.
It appears all bets are off?

example: create a table of columns (x, y, z), and fill it with values.
then, modify sqlite_master to take out column z.
let's say later on down the line, you either add a column using the
pragma writable_schema, or the ALTER TABLE.
What happens is the new column is filled with the old column's values:

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> create table t (x, y, z);
sqlite> insert into t values (1, 'a', 'A'), (2, 'b', 'B'), (3, 'c', 'C');
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
3|c|C
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = 'create table t (x, y)' where name =
't';
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
3|c|C  -- we are still seeing column z, until we vaccum:
sqlite> vacuum;
Error: table vacuum_db.t has 2 columns but 3 values were supplied
sqlite> select * from t;
x|y
1|a
2|b
3|c
sqlite> alter table t add column w;
sqlite> insert into t values (4, 'd', 'D');
sqlite> select * from t;
x|y|w
1|a|A
2|b|B
3|c|C
4|d|D
sqlite>

On Mon, Oct 17, 2016 at 4:03 AM, Simon Slavin  wrote:

>
> On 17 Oct 2016, at 8:17am, Torsten Landschoff 
> wrote:
>
> > So much about my attempt to report a bug. If you don't want to believe my
> > report, then don't.
>
> Sorry, just to make it clear, I'm just a fellow-user of SQLite.  I'm not
> on the development team.  And I totally believe what you wrote.  One of
> more of the development team might be figuring out the problem right now.
>
> 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] Determine query type

[Ben Newberg]

Excellent. This is exactly what I was looking for.

Thanks.

On Thu, Aug 6, 2015 at 11:50 AM, Stephan Beal  wrote:

> On Thu, Aug 6, 2015 at 6:46 PM, Ben Newberg  wrote:
>
> > Is there a good way to determine if a sql query is either attempting to
> > modify a database, or simply querying it?
> >
>
> Check the column count of the prepared statement: it will be >0 for a
> select or select-like pragma. Anything else is an update, delete, drop,
> create table/view, non-select-like pragma, or similar.
>
> --
> - 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 at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Determine query type

[Ben Newberg]

Is there a good way to determine if a sql query is either attempting to
modify a database, or simply querying it?

In the past, I have used sqlite3_update_hook with good results, but in this
current project, I am looking for a way to determine the type of query even
before the prepared statement is stepped thru at all. Ie, based on the
query text alone, I want to try to make this determination.

Would Explain / [Query Plan] be useful in determining the type of query?

Thanks all.

[sqlite] CSV excel import

[Ben Newberg]

+1 for the use of R for this task. I use the below if the file is in XLSX
format:

library(sqldf)
library(openxlsx)
df <- read.xlsx("mytable.xlsx", sheet=1, startRow=1, colNames=TRUE)
db <- dbConnect(SQLite(), "mydatabase.db")
dbWriteTable(db, "mytable", df)

On Sat, Aug 1, 2015 at 8:13 AM, Gabor Grothendieck 
wrote:

> Here is how to do it in R.  Download, install and start R and then paste
> the following code into R.
>
> Uncomment the first line (the line starting with  a hash) if you don't
> already have sqldf installed.  This not only installs sqldf but also the
> RSQLite driver and SQLite itself.
>
> The code assumes that mytable.csv is the input file, DF is the table name
> to create and db  is the name of the SQLite database to use (or create).
> Change these as needed.   It will use the first line of the input file as
> the column names and will automatically determine the types of  columns by
> examining the first few data rows of the input.
>
> # install.packages("sqldf")
>
> library(sqldf)
> DF <- read.csv("mytable.csv")
> sqldf(c("attach db as new", "create table new.DF as select * from DF"))
>
>
> On Thu, Jul 30, 2015 at 1:58 PM, Sylvain Pointeau <
> sylvain.pointeau at gmail.com> wrote:
>
> > I understood from the mailing list, that CSV is not a defined format,
> then
> > let's propose another format, well defined, the Excel one (which is in my
> > experience a format to is good every time I had to exchange CSV files).
> >
> > Then why don't you propose an import of CSV from Excel (or similar)?
> > csv(excel)
> >
> > is it possible? in a lot of cases, I cannot use sqlite (executable)
> because
> > of the lack of a good CSV import. It would really great if this could be
> > addressed.
> >
> > Best regards,
> > Sylvain
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks everyone. I will have the programming language do the work on this
one instead of going the trigger route.
On Nov 11, 2014 7:39 PM, "Richard Hipp" <d...@sqlite.org> wrote:

> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik <i...@tandetnik.org>
> wrote:
>
> On 11/11/2014 6:15 PM, Ben Newberg wrote:
> >
> > Looks like a bug to me. The statement works standalone, but not within a
> > trigger.
> >
>
> There are many limitations and restrictions on the statements inside of
> triggers.  See the trigger documentation (
> https://www.sqlite.org/lang_createtrigger.html) for details.  It looks
> like
> I need to add "no CTEs" to the list of restrictions.
>
> This is not a bug - it is an intentional omission.  A lot of extra code
> would need to be added to support this and that is not something we want to
> do right now.
>
>
> --
> 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

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks Simon.

The 10 is just an arbitrary value I chose for this example. The user
actually determines the value at run-time, so this value could be any
integer. I have a way to settle that, if only I could figure out how I can
get this trigger working.

BEN

On Tue, Nov 11, 2014 at 5:41 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 11 Nov 2014, at 11:15pm, Ben Newberg <ben.newb...@gmail.com> wrote:
>
> > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
> > limit 10)
> > INSERT INTO zWeeks (Week) select wk from Weeks;
>
> Just use 10 INSERT commands.
>
> I don't know what's causing your error message, but your code will be
> simpler if you just don't use RECURSIVE unnecessarily.
>
> Simon.
> ___
> 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] Triggers and CTE's

[Ben Newberg]

All,

Is it possible to have CTE's within triggers? The way I read the 'SQL As
Understood By SQLite', one can, but I could be misinterpreting it.

My DDL for my trigger is as follows:

CREATE TRIGGER t_populate_zweeks
AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
BEGIN
DELETE FROM zWeeks;
WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
limit 10)
INSERT INTO zWeeks (Week) select wk from Weeks;
END;

When I run this, I get an error message from sqlite3_errmsg() saying syntax
error near "INSERT". However, when I run the DELETE, WITH and INSERT
statements above separately without the Create Trigger DDL, the query runs
successfully and populates my zWeeks table with values 1 through 10.

Do triggers not support this behavior, or is my syntax incorrect?

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