Re: [sqlite] 3.17.0 (bug?): ".mode" command ignoring ".separator" when -init used

[Dâniel Fraga]

On Tue, 14 Feb 2017 02:16:50 -0200
Dâniel Fraga  wrote:

>   The solution was to provide the ".separator ," again after
> ".mode line", but it seems redundant, since I already had specified it
> in the "-init" file.

Sorry, I mean ".mode list", although it should affect ".mode
line" too, according to the changelog.

-- 


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

[sqlite] 3.17.0 (bug?): ".mode" command ignoring ".separator" when -init used

[Dâniel Fraga]

3.17.0 release brings the following change:

In the command-line shell, enhance the ".mode" command so that it
restores the default column and row separators for modes "line",
"list", "column", and "tcl".

***

I use the sqlite command-line shell with the option -init
pointing to a file with the following line:

.separator ,

The problem is that with version 3.17.0 the .mode line
completely ignore the ".separator ," inside the "-init" file.

The solution was to provide the ".separator ," again after
".mode line", but it seems redundant, since I already had specified it
in the "-init" file.

Is this a bug or this is the expected behaviour since 3.17.0?

Thanks!

-- 


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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

Being a database newb I'll defer to you guys, and plan to remove the 
unique keyword, but if the definition were "bad" shouldn't it fail to 
create the table?  Not sure how it could be argued that accepting a 
definition which allegedly leads to a problem < 0.01% of the time is an 
acceptable sequence.


In any case, this thread seems to indicate that at one point primary key 
+ unique resulted in a wasteful doubly indexed, but presumably still 
functional, database.  But at the end it seems to have been confirmed 
that it is optimized out as of 2008...


http://sqlite.1065341.n5.nabble.com/On-UNIQUE-and-PRIMARY-KEY-td41297.html


On 2/13/2017 7:28 PM, Stephen Chrzanowski wrote:

To further Keiths comment, I've never seen a database NOT treat a primary
key as not unique.  Primary Key automatically makes the field have a unique
constraint.

On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf  wrote:


This is probably due to your bad table definition.

  CREATE TABLE messages_priority (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  type TEXT,
  json TEXT,
  options TEXT,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

is kind of nonsensical.  You are declaring some "data" column to be both a
primary key, and autoincrement, and have a unique index at the same time.
You are probgably trying to declare an AUTOINCREMENT (meaning value
inserted is always bigger than any even inserted before) on a rowid
column.  This is pronounced thusly:

id integer primary key autoincrement

Only God (or DRH) knows what is done with the declaration "id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT UNIQUE"


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Jeff B.
Sent: Monday, 13 February, 2017 11:56
To: SQLite mailing list
Subject: Re: [sqlite] Partially "hidden" row, very bizarre state

Should have read up on integrity_check before posting the results... I
re-worked my tool and here is the output:


pragma integrity_check;

*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**



On 2/13/2017 11:58 AM, R Smith wrote:

Could you run and post the output of your tool to the following two
queries please:

pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd
that I wasn't even sure how to summarize it.  First, a little
context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET
4.5 project.  I have a table that is created as follows//

___
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] Faster check: > or !=

[James K. Lowden]

On Mon, 13 Feb 2017 15:32:00 +
jose isaias cabrera  wrote:

> I have a bunch of records to check, and I am wondering which is a
> faster check.  I am attaching a network DB as client, 
> 
> ATTACH 'h:\bkup\test.db' AS client; 

If you're concerned about speed or reliability, it would be far, far
faster to use locally attached storage.  Any difference in how
comparisons are expressed will be dwarfed by that choice.

If you want many clients to share the database from different hosts,
SQLite is not the right tool for that job.  Definitely not fastest, and
also vulnerable to data corruption.  

--jkl

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

Re: [sqlite] BUG: UPDATE with correlated sub-query

[James K. Lowden]

On Tue, 14 Feb 2017 00:49:29 +0700
Dan Kennedy  wrote:

> SQLite updates the first row (with a=1) and sets column "b" to 2.
> But then, when it goes to update the next row, it runs the correlated
> query a second time. And this time it returns 3. So you end up
> setting "b" in the second row to 3 instead of 2.

Thank you for that distillation, Dan.  You saved me the effort of
understanding the problem report.  

> It's surprising, but a consequence of the way SQLite has always
> worked. 

For some value of "worked", yes.  It's another example of the
ramifications of SQLite's nonatomic update.  

Your explanation leaves out fundamental step 0: First, [dbms] isolates
the UPDATE statement, such that no changes made to the database affect
the statement's input during execution.  Your representation "runs the
correlated query a second time" is the correct way to think about it
logically *if* you treat the table as stable during the query's
execution.  

SQLite's nonatomic update deserves its own documentation page.  Users
who understand atomicity correctly recognize it as a bug, both because
it's not how SQL defines UPDATE and because it's not documented.
Not to put too fine a point on it, the claim "Transactions in SQLite
are SERIALIZABLE" is false because -- as you just explained -- the
update transaction is not isolated from *itself*.  

--jkl


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

Re: [sqlite] Could missing indexes ids cause slow queries?

[James K. Lowden]

On Mon, 13 Feb 2017 22:22:24 +
jose isaias cabrera  wrote:

> sqlite> select id from LSOpenProjects where id > 13460 and id <
> sqlite> 115520;
> 13461
> 13462
> 13463
> 13464
> 13758
> 115516
> 115517
> 115518
> 115519
> sqlite>
> 
> As you can see, the id indexing is broken and there is a huge 
> discrepancy in the sequential id.  

That doesn't not show a broken index or any discrepancy.  

I guess you defined id as autoincrement and believe that to guarantee
sequentially generated values. There are many valid reasons observed
values in such a column will not be sequential; DELETE is only one.  

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Stephen Chrzanowski]

To further Keiths comment, I've never seen a database NOT treat a primary
key as not unique.  Primary Key automatically makes the field have a unique
constraint.

On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf  wrote:

>
> This is probably due to your bad table definition.
>
>  CREATE TABLE messages_priority (
>  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
>  type TEXT,
>  json TEXT,
>  options TEXT,
>  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
>
> is kind of nonsensical.  You are declaring some "data" column to be both a
> primary key, and autoincrement, and have a unique index at the same time.
> You are probgably trying to declare an AUTOINCREMENT (meaning value
> inserted is always bigger than any even inserted before) on a rowid
> column.  This is pronounced thusly:
>
> id integer primary key autoincrement
>
> Only God (or DRH) knows what is done with the declaration "id INTEGER NOT
> NULL PRIMARY KEY AUTOINCREMENT UNIQUE"
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jeff B.
> > Sent: Monday, 13 February, 2017 11:56
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
> >
> > Should have read up on integrity_check before posting the results... I
> > re-worked my tool and here is the output:
> >
> >
> > pragma integrity_check;
> >
> > *** in database main ***
> > On tree page 11 cell 0: Rowid 424 out of order
> > wrong # of entries in index sqlite_autoindex_messages_priority_1
> > **2 rows returned**
> >
> >
> >
> > On 2/13/2017 11:58 AM, R Smith wrote:
> > > Could you run and post the output of your tool to the following two
> > > queries please:
> > >
> > > pragma integrity_check;
> > >
> > > select '<'||id||'>',created from messages_priority order by id limit ;
> > >
> > >
> > > Thanks,
> > > Ryan
> > >
> > >
> > > On 2017/02/13 7:32 PM, Jeff B. wrote:
> > >> Hi Everyone,
> > >>
> > >> Sorry for the strange subject, but the situation I'm facing is so odd
> > >> that I wasn't even sure how to summarize it.  First, a little
> > >> context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET
> > >> 4.5 project.  I have a table that is created as follows//
> > >
> > > ___
> > > 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] Partially "hidden" row, very bizarre state

[Keith Medcalf]

This is probably due to your bad table definition.

 CREATE TABLE messages_priority (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
 type TEXT,
 json TEXT,
 options TEXT,
 created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

is kind of nonsensical.  You are declaring some "data" column to be both a 
primary key, and autoincrement, and have a unique index at the same time.  You 
are probgably trying to declare an AUTOINCREMENT (meaning value inserted is 
always bigger than any even inserted before) on a rowid column.  This is 
pronounced thusly:

id integer primary key autoincrement

Only God (or DRH) knows what is done with the declaration "id INTEGER NOT NULL 
PRIMARY KEY AUTOINCREMENT UNIQUE"

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jeff B.
> Sent: Monday, 13 February, 2017 11:56
> To: SQLite mailing list
> Subject: Re: [sqlite] Partially "hidden" row, very bizarre state
> 
> Should have read up on integrity_check before posting the results... I
> re-worked my tool and here is the output:
> 
> 
> pragma integrity_check;
> 
> *** in database main ***
> On tree page 11 cell 0: Rowid 424 out of order
> wrong # of entries in index sqlite_autoindex_messages_priority_1
> **2 rows returned**
> 
> 
> 
> On 2/13/2017 11:58 AM, R Smith wrote:
> > Could you run and post the output of your tool to the following two
> > queries please:
> >
> > pragma integrity_check;
> >
> > select '<'||id||'>',created from messages_priority order by id limit ;
> >
> >
> > Thanks,
> > Ryan
> >
> >
> > On 2017/02/13 7:32 PM, Jeff B. wrote:
> >> Hi Everyone,
> >>
> >> Sorry for the strange subject, but the situation I'm facing is so odd
> >> that I wasn't even sure how to summarize it.  First, a little
> >> context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET
> >> 4.5 project.  I have a table that is created as follows//
> >
> > ___
> > 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] Problem compiling 3.17.0 in MSVS 12

[Bart Smissaert]

Downloaded the latest 3.17.0 (sqlite-autoconf-317.tar.gz
) and replaced
my old 3.16.2 sqlite3.c file in the VC. Now for some reason I get lots of
errors when building a Windows dll.
First one is at line 16116 in this code block:

#define SQLITE_SKIP_UTF8(zIn) {
  if( (*(zIn++))>=0xc0 ){
while( (*zIn & 0xc0)==0x80 ){ zIn++; }
  }
}

Error 1 error C2059: syntax error : 'while'
Line 16116 is actually the one after the line with while.

Have updated to newer versions many times before any seen any problem like
this.
I am sure I must be overlooking something simple, but just can't see it.
Any suggestions?


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

Re: [sqlite] Intercepting execution of sqlite3 command to see final query

[Thomas Nyberg]

On 02/13/2017 04:14 PM, David Raymond wrote:

In the page for expressions (http://www.sqlite.org/lang_expr.html) check out 
the Parameters section and the REGEXP operator section:

"The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by 
default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL 
function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as 
a call to "regexp(Y,X)"."

So whatever you're normally running has probably implemented its own regexp, 
which would need to be loaded to test it yourself.

As to the other part, you  seeing the SQL. The question marks are for bound 
parameters, the question mark means "get the value from the bound variable". The 
statement text stays the same the whole time, only the bindings get updated. So if you're 
looking to log everything that it's doing you'll have to capture it binding those values as 
well.

Hopefully I'm answering that right.



Thank you so much. Yes apparently django (and not the python sqlite3 
module) defines the function. Tim Graham from the django list pointed 
out that it occurs here:


https://github.com/django/django/blob/2f10216f84b55920de25422842a66260219e393f/django/db/backends/sqlite3/base.py#L173

Thanks so much this is very helpful and makes perfect sense!

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

Re: [sqlite] sqlite3_blob_bytes64() ?

[Olivier Mascia]

> Le 13 févr. 2017 à 23:23, Olivier Mascia  a écrit :
> 
> What is the purpose of sqlite3_bind_blob64() and sqlite3_bind_zeroblob64()?
> 
> The documentation says: "The current implementation will only support a 
> string or BLOB length up to (2^31)-1 or 2147483647".
> The default upper limit is even set lower (for good reasons) at 1 thousand 
> millions.
> And, quite rightly I think, there is no sqlite3_blob_size64().

Maybe I should clarify it’s mainly bind_blob64() which puzzles me.
bind_zeroblob64() is different because it uses a rowid parameter that could be 
indeed of that magnitude.



-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

[sqlite] sqlite3_blob_bytes64() ?

[Olivier Mascia]

Dear,

What is the purpose of sqlite3_bind_blob64() and sqlite3_bind_zeroblob64()?

The documentation says: "The current implementation will only support a string 
or BLOB length up to (2^31)-1 or 2147483647".
The default upper limit is even set lower (for good reasons) at 1 thousand 
millions.
And, quite rightly I think, there is no sqlite3_blob_size64().

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

[sqlite] Could missing indexes ids cause slow queries?

[jose isaias cabrera]

Greetings!

I have an old db with lots of data and the one of the main tables has a 
messed up index. For example,


sqlite> select id from LSOpenProjects where id > 13460 and id < 115520;
13461
13462
13463
13464
13758
115516
115517
115518
115519
sqlite>

As you can see, the id indexing is broken and there is a huge 
discrepancy in the sequential id.  The question is, does this cause a 
slow down for queries?  For example, on this query,


BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB  > '2017-02-10 00:00:00'  -- change
  );
END;

There are indexes on id, ProjID, and XtraB:

CREATE INDEX OpPid ON LSOpenProjects (ProjID);
CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB);
CREATE TABLE LSOpenProjects
(
  id integer primary key, ProjID integer,...XtraB...
);

So, can that slow down the INSERT?  I know that I can turn off the 
indexes on the receiving end, and recreate them, but wouldn't that cause 
more slow down?  Just want to try to speed up these updates, since the 
updates lock the main DB and the other users have to wait, and the 
complaints are coming more frequently. :-)  Thanks.


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

Re: [sqlite] Faster check: > or !=

[Simon Slavin]

On 13 Feb 2017, at 9:03pm, jose isaias cabrera  wrote:

>> Can you set an index on id, projId, and Xtrab ?
> Already have one for each.
> 
> CREATE INDEX OpPid ON LSOpenProjects (ProjID);
> CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB);

No.  This does not do the same thing as one index which combines all three 
columns.

Imaging you had a phone book with firstname, surname, phonenumber.
I ask you to look up "Paul Smith".

You have one index with firstname and another index with surname.
You can pick either index but they will not take you to the right entry.
One index can take you straight to "Paul", but then you have to search all the 
"Paul" entries looking for "Smith".  There could be thousands of "Paul" entries.
The other index would take you straight to "Smith" but then you’d have to 
search through them for "Paul".  There might be thousands of "Smiths".

But create an index on (firstname,surname) and then you can go straight to the 
first "Paul Smith", then immediately to all the other people with the same name.

You do not make good indexes by indexing columns individually.  You make good 
indexes by looking at a WHERE clause and making an index which is perfect for 
that clause.

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

Thanks again to everyone for the guidance.  I read through that list of 
corruption candidates and most don't seem to apply.  Only one app 
accesses the db file, it's on a local NTFS partition, there were no 
power failures or hard reboots, no pragmas or non-default access options 
were used.  So I guess that leaves hardware issues.  This software is 
still in the testing state and has been run on about 10 different 
machines for about 4 weeks total.  I guess it would be my luck to hit 
something exceedingly rare with such a small sample size.  In any case, 
I'll take this opportunity to add a bunch of additional code to check 
for issues and try to fix them, or at the very least keep itself from 
infinite looping if things go too wrong.


Jeff

On 2/13/2017 1:26 PM, Simon Slavin wrote:

On 13 Feb 2017, at 7:12pm, Jeff B.  wrote:


The vacuum followed by a reindex seems to have fixed the db (see below).  But 
are there any other theories as to how we got to this point?  Could I have 
possibly caused this or do I just need to build in code to detect problems like 
this and rebuild on the fly?  I'm new to SQLite so I don't have a good feel for 
what is overkill vs what is best practices.

The database was definitely corrupt.  It’s quite difficult to do that.  It 
might happen through using some of the PRAGMAs which tell SQLite not to bother 
doing locking/synching correctly.  It might happen through a power-cut at just 
the wrong moment.  It might happen because your hard disk is faulty and that 
was the first known sign of it failing.  It might happen through broken 
software overwriting part of the database. And, for any of those it might 
happen just once in the lifetime of your program.

But the stark fact is that many users never see this happen ever.

Proper defensive action, as you suggested, is to build the integrity_check 
thing into some monthly/yearly maintenance routine.  Or to do it weekly for a 
while, then monthly, then yearly, until you have confidence in your hardware.

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] Patch for consideration: auto_vacuum slack.

[Scott Hess]

On Mon, Feb 13, 2017 at 12:13 PM, Richard Hipp  wrote:
>
> Scott:  The motivation for your patch seem to be to get auto-vacuum to
> run a little faster.  But if performance is your goal, why not just
> turn auto-vacuum off?  Or, failing that, set it to INCREMENTAL and
> then run "PRAGMA incremental_vacuum" when "PRAGMA freelist_count"
> reaches some threshold?
>

My first implementation did this manually using incremental-vacuum in a
layer above SQLite, because I already had a convenient function which
detected when updates had happened.  For explicit transactions, this is
pretty reasonable, albeit inelegant depending on whether you provide a
Commit() function or have to detect a COMMIT statement using string
operations.  But for auto-commit statements you can't get your incremental
vacuum into the commit scope.  [AFAICT, the commit and update hooks do not
allow running anything against the database.]  This version is certainly
doable if my suggested patch is considered beyond the pale.

I then reimplemented as more of an auto-incremental-vacuum, basically the
same code as I posted except that it keyed off the incremental-vacuum
flag.  It was pretty clean, but I did find myself thinking that it wouldn't
work well if you wrote code assuming the periodic cleaning was happening
automatically, but someone had neglected to setup the slack-sizing pragma.
In that case it would just stop collecting garbage, and your code would
never do it explicitly.  With the auto-vacuum version, it would just fall
back to auto-vacuum-every-time.  [I'm not sure which is actually worse, in
the end.]

Mostly, my motivation was that it felt like there was a gap between the
SQLITE_FCNTL_CHUNK_SIZE feature and auto_vacuum/incremental_vacuum, and
when I looked the gap was pretty clean to fill.  Having it key off of the
actual "Should I vacuum free pages" decision seemed more reasonable than
writing external code which makes educated guesses about what's going on.

I'm not really looking at "Should I rewrite all of the auto-vacuum
databases I can find to use manual vacuum".  I think a change like this
would make auto-vacuum databases behave a bit more like non-auto-vacuum
databases in terms of reuse of free space.

---

WRT your list of reasons for "Why even bother", another consideration to
add is that fragmentation on SSDs may not be the big problem it is on hard
drives.  If you have your page sizes reasonably aligned with the units of
the underlying filesystem and hardware, it may not matter much whether a
particular page is next to other pages which are logically adjacent in the
btree.  Of course, there's a lot of complexity in there, like whether the
OS continues to do read-ahead for SSDs.

That said, AFAICT, there's no code in place to make page placement
decisions based on locality, so I would expect that a
non-auto/incremental-vacuum database being actively updated would also
generate fragmentation.

---

I'm not going to get into the "Why not just use VACUUM".  My experience is
that this is a tough issue with databases in general, and developers often
have troubles wrapping their heads around it.  In my case, I'm thinking of
how to mitigate some concerns developers had with existing code, not how
they might write greenfield code going forward.  [As if they'd even ask my
advice before writing code and shipping it.  They only ask me things after
they've already shipped a problem :-).]

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

Re: [sqlite] Patch for consideration: auto_vacuum slack.

[Stephen Chrzanowski]

I use it, not due to drive space, but because I sometimes check my DB into
a source code repo.


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

> On 2/13/17, Scott Hess  wrote:
> >
> > Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
> > This setting allows client code to signal that auto_vacuum can leave
> pages
> > on the freelist until releasing them would allow a db size change.
>
> This makes me want to ask:  Is anybody still using auto_vacuum?  And
> if they are, should they be?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intercepting execution of sqlite3 command to see final query

[David Raymond]

In the page for expressions (http://www.sqlite.org/lang_expr.html) check out 
the Parameters section and the REGEXP operator section:

"The REGEXP operator is a special syntax for the regexp() user function. No 
regexp() user function is defined by default and so use of the REGEXP operator 
will normally result in an error message. If an application-defined SQL 
function named "regexp" is added at run-time, then the "X REGEXP Y" operator 
will be implemented as a call to "regexp(Y,X)"."

So whatever you're normally running has probably implemented its own regexp, 
which would need to be loaded to test it yourself.

As to the other part, you  seeing the SQL. The question marks are for 
bound parameters, the question mark means "get the value from the bound 
variable". The statement text stays the same the whole time, only the bindings 
get updated. So if you're looking to log everything that it's doing you'll have 
to capture it binding those values as well.

Hopefully I'm answering that right.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Nyberg
Sent: Monday, February 13, 2017 3:12 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Intercepting execution of sqlite3 command to see final query

Hello,

The premise of this is the following: I'm using sqlite3 in a django 
application. I'm having it log the SQL that it issues (once you get 
passed its magical ORM setup), but that command does not work. I.e. I 
have something like the following (the python is not important, but does 
set the stage a little):

 >>> regex = 'NET | INCOME'
 >>> Table.objects.filter(name__iregex=regex).

When I have it print out the SQL sent, I get the following (this is a 
simplified, but equivalent version):

 SELECT *
 FROM "table"
 WHERE "table"."name"
 REGEXP '(?i)' || 'NET | INCOME'
 LIMIT 21

However if I execute that directly against the sqlite database i get the 
following error:

 sqlite3.OperationalError: no such function: REGEXP

I thought that maybe this magic was handled in python's sqlite3 module, 
but if i do it there i get the same error.

Finally I realized it might just be easier to intercept the sqlite3 call 
at the dynamic library level. To do this, I wrote the following code:

test.c
---
#define _GNU_SOURCE

#include 
#include 
#include 

SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
sqlite3_stmt **ppStmt, const char 
**pzTail) {
 int (*original_sqlite3_prepare)(sqlite3*, const char *, int,
 sqlite3_stmt**, const char **);
 original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare");
 printf("\n\n***ORIGINAL SQL QUERY***\n\n\n");
 printf("%s", zSql);
 printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n");
 fflush(stdout);
 return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail);
}
---

I compiled it as follows:

$ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3

When I preload this before running my app, it prints out the following 
(slightly cleaned up to make easier to read by removing a couple 
irrelevant fields):

SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name" 
REGEXP '(?i)' || ?

I'm not sure what to do with this. It's even missing the final part. So 
obviously I'm looking at this in the wrong place. So I guess I have a 
couple questions:

1) How can I sort out what the final query actually is? Possibly I 
should be intercepting other calls or functions?
2) Is this obvious the readers here (thus making my other investigations 
unnecessary) what's going on?

Thanks for any help!

Cheers,
Thomas

P.S. Appologies if this is not appropriate here? Maybe it should be 
posted to sqlite-dev?
___
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] Faster check: > or !=

[jose isaias cabrera]

On 2017-02-13 18:07, Simon Slavin wrote:

On 13 Feb 2017, at 3:32pm, jose isaias cabrera  
wrote:



Any input would be greatly appreciated.


I doubt there's much difference, though the one with the fixed 
timestamp string may be a touch faster.


But the real speedup would come from an index like this:

CREATE INDEX client.OP_IPX ON client.OpenProjects (id,ProjID,XtraB)

Simon.


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

Re: [sqlite] Faster check: > or !=

[jose isaias cabrera]

On 2017-02-13 17:20, Adam DeVita wrote:


How much control do you have?


All of it.


Can you put out your date code into an integer field?
Hmmm... I could.  I have to do some programming to replace the normal 
date with the integer date.



Can you set an index on id, projId, and Xtrab ?

Already have one for each.

CREATE INDEX OpPid ON LSOpenProjects (ProjID);
CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB);
CREATE TABLE LSOpenProjects
(
 id integer primary key, ProjID integer,...
);



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

Re: [sqlite] Patch for consideration: auto_vacuum slack.

[Donald Shepherd]

I use auto-vacuum in my application storage for work.  This was introduced
in about 2013.

The motivation was more political than anything though, as convincing some
as to the introduction requiring jumping through some pretty arbitrary
hoops.  Enabling auto-vacuum was one of those, to mitigate concerns that a
piece of software unused in the immediate development centre would chew up
resources needed by other more business critical processes.

On Tue, 14 Feb 2017 at 07:14 Richard Hipp  wrote:

> On 2/13/17, Scott Hess  wrote:
> >
> > Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
> > This setting allows client code to signal that auto_vacuum can leave
> pages
> > on the freelist until releasing them would allow a db size change.
>
> This makes me want to ask:  Is anybody still using auto_vacuum?  And
> if they are, should they be?
>
> Auto-vacuum was invented way back in 2004, for Motorola, who at the
> time was the worlds leading manufacturer of mobile phones.  This was
> during the heyday of flip-phones, before iPhone or Android.  The
> devices had very little flash memory - total storage capacity was
> measured in megabytes rather than gigabytes.  If storage ran low,
> Motorola wanted to be able to VACUUM the SQLite databases to make them
> smaller.  The problem there is VACUUM requires nearly 2x the size of
> the original database in temp storage, so if you are already low on
> space, VACUUM probably won't work.  The solution was auto-vacuum,
> which keeps the databases at near their minimum size at all times, at
> the cost of some extra database fragmentation, more I/O, and reduced
> performance.
>
> Fast foward 13 years (how long is that in internet-years?) and the
> situation has changed.  Does anybody really care anymore that a
> database file might have a few dozen pages on its freelist?  Or if
> they do care, does anybody lack the temp space sufficient to run a
> real VACUUM?  My impression is that these days people just want the
> database to run fast and with a minimum of I/O and are not overly
> concerned with a few extra freelist pages, which means that
> auto-vacuum should remain turned off.
>
> Scott:  The motivation for your patch seem to be to get auto-vacuum to
> run a little faster.  But if performance is your goal, why not just
> turn auto-vacuum off?  Or, failing that, set it to INCREMENTAL and
> then run "PRAGMA incremental_vacuum" when "PRAGMA freelist_count"
> reaches some threshold?
> --
> 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] Intercepting execution of sqlite3 command to see final query

[Thomas Nyberg]

Hello,

The premise of this is the following: I'm using sqlite3 in a django 
application. I'm having it log the SQL that it issues (once you get 
passed its magical ORM setup), but that command does not work. I.e. I 
have something like the following (the python is not important, but does 
set the stage a little):


>>> regex = 'NET | INCOME'
>>> Table.objects.filter(name__iregex=regex).

When I have it print out the SQL sent, I get the following (this is a 
simplified, but equivalent version):


SELECT *
FROM "table"
WHERE "table"."name"
REGEXP '(?i)' || 'NET | INCOME'
LIMIT 21

However if I execute that directly against the sqlite database i get the 
following error:


sqlite3.OperationalError: no such function: REGEXP

I thought that maybe this magic was handled in python's sqlite3 module, 
but if i do it there i get the same error.


Finally I realized it might just be easier to intercept the sqlite3 call 
at the dynamic library level. To do this, I wrote the following code:


test.c
---
#define _GNU_SOURCE

#include 
#include 
#include 

SQLITE_API int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte,
   sqlite3_stmt **ppStmt, const char 
**pzTail) {

int (*original_sqlite3_prepare)(sqlite3*, const char *, int,
sqlite3_stmt**, const char **);
original_sqlite3_prepare = dlsym(RTLD_NEXT, "sqlite3_prepare");
printf("\n\n***ORIGINAL SQL QUERY***\n\n\n");
printf("%s", zSql);
printf("\n\n***END ORIGINAL SQL QUERY***\n\n\n");
fflush(stdout);
return (*original_sqlite3_prepare)(db, zSql, nByte, ppStmt, pzTail);
}
---

I compiled it as follows:

$ gcc -Wall -fPIC -shared -o mysqlite3_prepare.so test.c -ldl -lsqlite3

When I preload this before running my app, it prints out the following 
(slightly cleaned up to make easier to read by removing a couple 
irrelevant fields):


SELECT "table"."id", "table"."name", FROM "table" WHERE "table"."name" 
REGEXP '(?i)' || ?


I'm not sure what to do with this. It's even missing the final part. So 
obviously I'm looking at this in the wrong place. So I guess I have a 
couple questions:


1) How can I sort out what the final query actually is? Possibly I 
should be intercepting other calls or functions?
2) Is this obvious the readers here (thus making my other investigations 
unnecessary) what's going on?


Thanks for any help!

Cheers,
Thomas

P.S. Appologies if this is not appropriate here? Maybe it should be 
posted to sqlite-dev?

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff Bromberger]

First off, thanks for the help!

Here is the output:

pragma integrity_check;
**Command returned -1**

select '<'||id||'>',created from messages_priority order by id limit 5;
created
<424>2/12/2017 8:07:10 PM
<423>2/12/2017 8:07:09 PM
<425>2/12/2017 8:07:11 PM
<426>2/12/2017 8:07:12 PM
<427>2/12/2017 8:07:13 PM
**5 rows returned**


For anything other than "select" I am calling ExecuteNonQuery() and 
printing its return value (the -1 in this case).  Is there something 
additional I need to get to you for that integrity_check command?


Thanks,
Jeff

On 2/13/2017 11:58 AM, R Smith wrote:
Could you run and post the output of your tool to the following two 
queries please:


pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little 
context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 
4.5 project.  I have a table that is created as follows//


___
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] Faster check: > or !=

[Adam DeVita]

How much control do you have?

Can you put out your date code into an integer field?
Can you set an index on id, projId, and Xtrab ?

regards,
Adam

From: sqlite-users  on behalf of 
jose isaias cabrera 
Sent: February 13, 2017 10:32:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Faster check: > or !=

Greetings!

I have a bunch of records to check, and I am wondering which is a faster
check.  I am attaching a network DB as client,

ATTACH 'h:\bkup\test.db' AS client;

and then do an INSERT based on some logic, and one of those login is
checking against a variable or an actual DB value.  Here are the
different SQL syntaxes:

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB != XtraB  -- change
  );
END;

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB  > '2017-02-10 00:00:00'  -- change
  );
END;

Any input would be greatly appreciated.  Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
=== CONFIDENTIALITY NOTICE: This e-mail and any attachments contain 
information from Lytx, Inc. and/or its affiliates, and are intended solely for 
the use of the named recipient(s). This e-mail may contain confidential 
information of Lytx and its customers. Any dissemination of this e-mail by 
anyone other than an intended recipient is strictly prohibited. If you are not 
a named recipient, you are prohibited from any further viewing of the e-mail or 
any attachments or from making any use of the e-mail or attachments. If you 
believe you have received this e-mail in error, notify the sender immediately 
and permanently delete the e-mail, any attachments, and all copies thereof from 
any drives or storage media and destroy any printouts of the e-mail or 
attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch for consideration: auto_vacuum slack.

[Richard Hipp]

On 2/13/17, Scott Hess  wrote:
>
> Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
> This setting allows client code to signal that auto_vacuum can leave pages
> on the freelist until releasing them would allow a db size change.

This makes me want to ask:  Is anybody still using auto_vacuum?  And
if they are, should they be?

Auto-vacuum was invented way back in 2004, for Motorola, who at the
time was the worlds leading manufacturer of mobile phones.  This was
during the heyday of flip-phones, before iPhone or Android.  The
devices had very little flash memory - total storage capacity was
measured in megabytes rather than gigabytes.  If storage ran low,
Motorola wanted to be able to VACUUM the SQLite databases to make them
smaller.  The problem there is VACUUM requires nearly 2x the size of
the original database in temp storage, so if you are already low on
space, VACUUM probably won't work.  The solution was auto-vacuum,
which keeps the databases at near their minimum size at all times, at
the cost of some extra database fragmentation, more I/O, and reduced
performance.

Fast foward 13 years (how long is that in internet-years?) and the
situation has changed.  Does anybody really care anymore that a
database file might have a few dozen pages on its freelist?  Or if
they do care, does anybody lack the temp space sufficient to run a
real VACUUM?  My impression is that these days people just want the
database to run fast and with a minimum of I/O and are not overly
concerned with a few extra freelist pages, which means that
auto-vacuum should remain turned off.

Scott:  The motivation for your patch seem to be to get auto-vacuum to
run a little faster.  But if performance is your goal, why not just
turn auto-vacuum off?  Or, failing that, set it to INCREMENTAL and
then run "PRAGMA incremental_vacuum" when "PRAGMA freelist_count"
reaches some threshold?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: UPDATE with correlated sub-query

[Dudu Markovitz]

Thanks Dan

I couldn't find a reference to this behaviour in the documentation.
Do you think it would be possible to add a few words?




On Mon, Feb 13, 2017 at 7:49 PM Dan Kennedy  wrote:

> On 02/13/2017 01:44 PM, Dudu Markovitz wrote:
> > Good morning
> >
> > While answering question on stackoverflow
> > I have noticed a bug
> related
> > to UPDATE using correlated sub-query.
> > The demonstration code can be found in the attached file bug_report.sql
> and
> > the results of the demonstration with some comments added are in
> > bug_reports.txt.
> >
> > I'm using SQLite 3.9.2 on windows 64 bit.
>
> It's surprising, but a consequence of the way SQLite has always worked.
> Consider:
>
>CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
>INSERT INTO t1 VALUES(1, 1);
>INSERT INTO t1 VALUES(2, 0);
>
> Then:
>
>UPDATE t1 SET b=(SELECT b+1 FROM t1 WHERE a=1);
>
> In this case, the sub-query is uncorrelated and "b" is set to 2 in both
> rows.
>
> But say you added some other term to the sub-query so that it was
> correlated. It is then executed separately for each row. So SQLite
> updates the first row (with a=1) and sets column "b" to 2. But then,
> when it goes to update the next row, it runs the correlated query a
> second time. And this time it returns 3. So you end up setting "b" in
> the second row to 3 instead of 2.
>
> Something pretty similar is occurring in the example you posted.
>
> Dan.
>
> ___
> 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] Partially "hidden" row, very bizarre state

[Simon Slavin]

On 13 Feb 2017, at 7:12pm, Jeff B.  wrote:

> The vacuum followed by a reindex seems to have fixed the db (see below).  But 
> are there any other theories as to how we got to this point?  Could I have 
> possibly caused this or do I just need to build in code to detect problems 
> like this and rebuild on the fly?  I'm new to SQLite so I don't have a good 
> feel for what is overkill vs what is best practices.

The database was definitely corrupt.  It’s quite difficult to do that.  It 
might happen through using some of the PRAGMAs which tell SQLite not to bother 
doing locking/synching correctly.  It might happen through a power-cut at just 
the wrong moment.  It might happen because your hard disk is faulty and that 
was the first known sign of it failing.  It might happen through broken 
software overwriting part of the database. And, for any of those it might 
happen just once in the lifetime of your program.

But the stark fact is that many users never see this happen ever.

Proper defensive action, as you suggested, is to build the integrity_check 
thing into some monthly/yearly maintenance routine.  Or to do it weekly for a 
while, then monthly, then yearly, until you have confidence in your hardware.

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Richard Hipp]

On 2/13/17, Jeff B.  wrote:
> But are there any other theories as to how we got to this
> point?

Please see https://www.sqlite.org/howtocorrupt.html


-- 
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] Patch for consideration: auto_vacuum slack.

[Scott Hess]

A developer was asking me questions about auto_vacuum I/O characteristics,
because they were worried about "churn", where a page is moved to fill a
freelist gap, then soon enough a new page is allocated anyhow, so the move
wasn't really necessary.  This made me wonder if auto_vacuum recognized
that in some cases SQLITE_FCNTL_CHUNK_SIZE would make the move pointless
(because it wouldn't even save any space).

Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
This setting allows client code to signal that auto_vacuum can leave pages
on the freelist until releasing them would allow a db size change.  I think
this would reduce the number of auto_vacuum moves for many databases.  The
FCNTL and PRAGMA could obviously be integrated into one setting, I haven't
done that in this patch.  Also, the test in autoVacuumCommit() could
certainly be more cleanly combined with the existing code, I left it
distinct to make the logic easier to see.  I'd be happy to adjust the code
to be cleaner and write tests, mostly I didn't want to invest time in that
without floating a prototype to see if it would be an interesting addition.

Thanks,
scott


--- Patch follows ---
Index: src/btree.c
==
--- src/btree.c
+++ src/btree.c
@@ -2810,10 +2810,50 @@
   sqlite3BtreeLeave(p);
   return rc;
 #endif
 }

+/*
+** Change the 'auto-vacuum-slack-pages' property of the database. If auto
vacuum
+** is enabled, this is the number of chunks of slack to allow before
+** automatically running an incremental vacuum.
+*/
+int sqlite3BtreeSetAutoVacuumSlackPages(Btree *p, int autoVacuumSlack){
+#ifdef SQLITE_OMIT_AUTOVACUUM
+  return SQLITE_READONLY;
+#else
+  BtShared *pBt = p->pBt;
+  int rc = SQLITE_OK;
+  u8 cc = (u8)autoVacuumSlack;
+  if( autoVacuumSlack>cc ){
+cc = 0xFF;
+  }
+
+  sqlite3BtreeEnter(p);
+  pBt->autoVacuumSlack = cc;
+  sqlite3BtreeLeave(p);
+  return rc;
+#endif
+}
+
+/*
+** Return the value of the 'auto-vacuum-slack-pages' property.
+*/
+int sqlite3BtreeGetAutoVacuumSlackPages(Btree *p){
+#ifdef SQLITE_OMIT_AUTOVACUUM
+  return 0;
+#else
+  int rc = 0;
+  sqlite3BtreeEnter(p);
+  if( p->pBt->autoVacuum!=0 ){
+rc = p->pBt->autoVacuumSlack;
+  }
+  sqlite3BtreeLeave(p);
+  return rc;
+#endif
+}
+

 /*
 ** Get a reference to pPage1 of the database file.  This will
 ** also acquire a readlock on that file.
 **
@@ -3651,17 +3691,31 @@
 ** i.e. the database has been reorganized so that only the first *pnTrunc
 ** pages are in use.
 */
 static int autoVacuumCommit(BtShared *pBt){
   int rc = SQLITE_OK;
+  int bShouldVacuum = pBt->autoVacuum && !pBt->incrVacuum;
   Pager *pPager = pBt->pPager;
   VVA_ONLY( int nRef = sqlite3PagerRefcount(pPager); )

   assert( sqlite3_mutex_held(pBt->mutex) );
   invalidateAllOverflowCache(pBt);
   assert(pBt->autoVacuum);
-  if( !pBt->incrVacuum ){
+  if( bShouldVacuum && pBt->autoVacuumSlack ){
+Pgno nOrig;/* Database size before freeing */
+Pgno nFree;/* Number of pages on the freelist initially */
+
+nOrig = btreePagecount(pBt);
+nFree = get4byte(&pBt->pPage1->aData[36]);
+bShouldVacuum =
+(nOrig-nFree)/pBt->autoVacuumSlack < nOrig/pBt->autoVacuumSlack;
+/* TODO: When integrating this test with the following code, contrive
to
+** trim to the integral chunk boundary, rather than trimming the
entire free
+** list.
+*/
+  }
+  if( bShouldVacuum ){
 Pgno nFin; /* Number of pages in database after autovacuuming
*/
 Pgno nFree;/* Number of pages on the freelist initially */
 Pgno iFree;/* The next page to be freed */
 Pgno nOrig;/* Database size before freeing */


Index: src/btree.h
==
--- src/btree.h
+++ src/btree.h
@@ -76,10 +76,12 @@
 int sqlite3BtreeSecureDelete(Btree*,int);
 int sqlite3BtreeGetOptimalReserve(Btree*);
 int sqlite3BtreeGetReserveNoMutex(Btree *p);
 int sqlite3BtreeSetAutoVacuum(Btree *, int);
 int sqlite3BtreeGetAutoVacuum(Btree *);
+int sqlite3BtreeSetAutoVacuumSlackPages(Btree *, int);
+int sqlite3BtreeGetAutoVacuumSlackPages(Btree *);
 int sqlite3BtreeBeginTrans(Btree*,int);
 int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster);
 int sqlite3BtreeCommitPhaseTwo(Btree*, int);
 int sqlite3BtreeCommit(Btree*);
 int sqlite3BtreeRollback(Btree*,int,int);

Index: src/btreeInt.h
==
--- src/btreeInt.h
+++ src/btreeInt.h
@@ -410,10 +410,11 @@
   BtCursor *pCursor;/* A list of all open cursors */
   MemPage *pPage1;  /* First page of the database */
   u8 openFlags; /* Flags to sqlite3BtreeOpen() */
 #ifndef SQLITE_OMIT_AUTOVACUUM
   u8 autoVacuum;/* True if auto-vacuum is enabled */
+  u8 autoVacuumSlack;   /* Optional pages of slack for auto-vacuum */
   u8 incrVacuum;/* True if incr-vacuum is enabled */
   u8 bDoTruncate;   

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

The vacuum followed by a reindex seems to have fixed the db (see 
below).  But are there any other theories as to how we got to this 
point?  Could I have possibly caused this or do I just need to build in 
code to detect problems like this and rebuild on the fly?  I'm new to 
SQLite so I don't have a good feel for what is overkill vs what is best 
practices.


Thanks!

Jeff


vacuum
**Command returned 0**

pragma integrity_check
wrong # of entries in index sqlite_autoindex_messages_priority_1
**1 rows returned**

reindex messages_priority
**Command returned 0**

pragma integrity_check
ok
**1 rows returned**

select id,created from messages_priority order by id limit 5
idcreated
4232/12/2017 8:07:09 PM
4242/12/2017 8:07:10 PM
4252/12/2017 8:07:11 PM
4262/12/2017 8:07:12 PM
4272/12/2017 8:07:13 PM
**5 rows returned**

select id,created from messages_priority where id = 424
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**


On 2/13/2017 12:59 PM, Simon Slavin wrote:

On 13 Feb 2017, at 6:55pm, Jeff B.  wrote:


Should have read up on integrity_check before posting the results... I 
re-worked my tool and here is the output:


pragma integrity_check;

*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**

Just like they said, your index is corrupt.  Unfortunately it’s an automatic 
index so you can’t easily remake it.  And you have a password-protected 
database and maybe you can’t use the shell tool on it.

Make a backup copy of the database file, just in case.  Then try the VACUUM 
command.  Then to the integrity_check thing again.

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] Partially "hidden" row, very bizarre state

[R Smith]

On 2017/02/13 8:55 PM, Jeff B. wrote:
Should have read up on integrity_check before posting the results... I 
re-worked my tool and here is the output:



pragma integrity_check;

*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**


Well, that sheds the necessary light. The index have somehow become 
corrupted - not a bad corruption mind you, just an out-of-order node and 
an easy fix by simply running:

REINDEX sqlite_autoindex_messages_priority_1;
 or just:
REINDEX;
you can also drop the automatic index and it should be recreated, or 
create your own index that does the job.


The bigger question is, how did this happen?  Well, there is a myriad 
possibilities from something as trivial as networked file access botch 
(and a list of other possible corruption causes that you can read about 
at https://sqlite.org/howtocorrupt.html) or something as insane as Ra 
hating you and he had a cosmic ray hit your memory just right


If this happened once in years, I wouldn't worry about it, but try to 
work a "pragma integrity_check;" into your maintenance runs and take 
action if it reports any funnies.
If this happens regularly, it indicates a system problem and you should 
start logging activity and run (and log) integrity checks often till you 
find the culprit.


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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

Thank you as well for offering some tips.  I have run the 
integrity_check and the reindex.  The reindex got rid of one of the 2 
errors, but the overall problem still persists:


pragma integrity_check;
*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**

reindex messages_priority
**Command returned 0**

pragma integrity_check;
*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
**1 rows returned**

select id,created from messages_priority order by id limit 5
idcreated
4242/12/2017 8:07:10 PM
4232/12/2017 8:07:09 PM
4252/12/2017 8:07:11 PM
4262/12/2017 8:07:12 PM
4272/12/2017 8:07:13 PM
**5 rows returned**

select id,created from messages_priority where id = 424
**No Rows**

select id,created from messages_priority where id = 423
idcreated
4232/12/2017 8:07:09 PM
**1 rows returned**



On 2/13/2017 11:50 AM, David Raymond wrote:

I vaguely recall a while ago someone finding a small bug when "primary key unique" was used. Since those are 
redundant you should get rid of the "unique" in the id field creation as "primary key" takes care 
of that. (It "shouldn't" make a difference, but don't tempt fate)

Other things: run a pragma integrity_check; to look for problems. And a 
reindex; to try fixing any found index problems, as it sounds to me like an 
index got messed up somewhere along the line. If it was using the bad index for 
ordering, but displaying the id from the actual table that would explain 
showing the record out of order. And if it used that index to look for the 
record to delete then it wouldn't find 424, just whatever else was in there for 
the id. Apparently deleting something else there might have nudged it to 
correctness? I'm not sure why deleting another record would fix the index 
though, weird.

Apart from the "primary key unique" thing I don't have a clue how it would get 
into that state though, so can't help you there.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jeff B.
Sent: Monday, February 13, 2017 12:32 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Partially "hidden" row, very bizarre state

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd
that I wasn't even sure how to summarize it.  First, a little context:
I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5
project.  I have a table that is created as follows:

  CREATE TABLE messages_priority (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  type TEXT,
  json TEXT,
  options TEXT,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

The purpose of my SQLite db is to locally queue messages until they can
be sent to a web API, at which time they are deleted.  My single
consumer thread basically does a "SELECT * FROM messages_priority ORDER
BY id LIMIT 1" and then if the send to the web is successful it deletes
that id.  This has all been working fine for months until I had one
client hit a problem yesterday.  They kept sending the same message over
and over to the web and would never move on to anything else.  Once I
dug in further this is what I found:

(Note that I use a password on the database and I have not been able to
find any db viewers for Windows which support the type of encryption
that Windows SQLite uses, so I had to write my own simple query tool,
the output of which is shown below)

select id,created from messages_priority order by id limit 5
idcreated
4242/12/2017 8:07:10 PM
4232/12/2017 8:07:09 PM
4252/12/2017 8:07:11 PM
4262/12/2017 8:07:12 PM
4272/12/2017 8:07:13 PM
**5 rows returned**

// So here we see the first problem.  424 is listed before 423 even
though I'm specifying an order by id.

select id,created from messages_priority where id = 424
**No Rows**

// Now we see the next problem... even though 424 comes back in the
first query, when I try to specify it in a WHERE clause it is not found.

select id,created from messages_priority where id = 423
idcreated
4232/12/2017 8:07:09 PM
**1 rows returned**

// 423, however, returns just fine that way...

delete from messages_priority where id = 424
**Command returned 0**

// delete doesn't throw an exception or anything, but indicates nothing
was deleted

select id,created from messages_priority order by id limit 1
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// still shows up in a select with no WHERE

delete from messages_priority where id = 423
**Command returned 1**

// attempt to delete 423 is successful

select id,created from messages_priority where id = 424
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// now I can finally query by the id!

delete from messages_priority w

Re: [sqlite] Partially "hidden" row, very bizarre state

[Simon Slavin]

On 13 Feb 2017, at 6:55pm, Jeff B.  wrote:

> Should have read up on integrity_check before posting the results... I 
> re-worked my tool and here is the output:
> 
> 
> pragma integrity_check;
> 
> *** in database main ***
> On tree page 11 cell 0: Rowid 424 out of order
> wrong # of entries in index sqlite_autoindex_messages_priority_1
> **2 rows returned**

Just like they said, your index is corrupt.  Unfortunately it’s an automatic 
index so you can’t easily remake it.  And you have a password-protected 
database and maybe you can’t use the shell tool on it.

Make a backup copy of the database file, just in case.  Then try the VACUUM 
command.  Then to the integrity_check thing again.

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

Should have read up on integrity_check before posting the results... I 
re-worked my tool and here is the output:



pragma integrity_check;

*** in database main ***
On tree page 11 cell 0: Rowid 424 out of order
wrong # of entries in index sqlite_autoindex_messages_priority_1
**2 rows returned**



On 2/13/2017 11:58 AM, R Smith wrote:
Could you run and post the output of your tool to the following two 
queries please:


pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little 
context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 
4.5 project.  I have a table that is created as follows//


___
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] Partially "hidden" row, very bizarre state

[Jeff B.]

First off, thanks for the help!

Here is the output:

pragma integrity_check;
**Command returned -1**

select '<'||id||'>',created from messages_priority order by id limit 5;
created
<424>2/12/2017 8:07:10 PM
<423>2/12/2017 8:07:09 PM
<425>2/12/2017 8:07:11 PM
<426>2/12/2017 8:07:12 PM
<427>2/12/2017 8:07:13 PM
**5 rows returned**


For anything other than "select" I am calling ExecuteNonQuery() and 
printing its return value (the -1 in this case).  Is there something 
additional I need to get to you for that integrity_check command?


Thanks,
Jeff


On 2/13/2017 11:58 AM, R Smith wrote:
Could you run and post the output of your tool to the following two 
queries please:


pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little 
context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 
4.5 project.  I have a table that is created as follows//


___
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] Faster check: > or !=

[Simon Slavin]

On 13 Feb 2017, at 3:32pm, jose isaias cabrera  wrote:

> Any input would be greatly appreciated.

I doubt there’s much difference, though the one with the fixed timestamp string 
may be a touch faster.

But the real speedup would come from an index like this:

CREATE INDEX client.OP_IPX ON client.OpenProjects (id,ProjID,XtraB)

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

Re: [sqlite] Partially "hidden" row, very bizarre state

[R Smith]

Correction: That's "limit 5;" of course on the second query...

On 2017/02/13 7:58 PM, R Smith wrote:
Could you run and post the output of your tool to the following two 
queries please:


pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


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

Re: [sqlite] Partially "hidden" row, very bizarre state

[R Smith]

Could you run and post the output of your tool to the following two 
queries please:


pragma integrity_check;

select '<'||id||'>',created from messages_priority order by id limit ;


Thanks,
Ryan


On 2017/02/13 7:32 PM, Jeff B. wrote:

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little 
context:  I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 
4.5 project.  I have a table that is created as follows//


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

Re: [sqlite] Partially "hidden" row, very bizarre state

[David Raymond]

I vaguely recall a while ago someone finding a small bug when "primary key 
unique" was used. Since those are redundant you should get rid of the "unique" 
in the id field creation as "primary key" takes care of that. (It "shouldn't" 
make a difference, but don't tempt fate)

Other things: run a pragma integrity_check; to look for problems. And a 
reindex; to try fixing any found index problems, as it sounds to me like an 
index got messed up somewhere along the line. If it was using the bad index for 
ordering, but displaying the id from the actual table that would explain 
showing the record out of order. And if it used that index to look for the 
record to delete then it wouldn't find 424, just whatever else was in there for 
the id. Apparently deleting something else there might have nudged it to 
correctness? I'm not sure why deleting another record would fix the index 
though, weird.

Apart from the "primary key unique" thing I don't have a clue how it would get 
into that state though, so can't help you there.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jeff B.
Sent: Monday, February 13, 2017 12:32 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Partially "hidden" row, very bizarre state

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little context:  
I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5 
project.  I have a table that is created as follows:

 CREATE TABLE messages_priority (
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
 type TEXT,
 json TEXT,
 options TEXT,
 created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

The purpose of my SQLite db is to locally queue messages until they can 
be sent to a web API, at which time they are deleted.  My single 
consumer thread basically does a "SELECT * FROM messages_priority ORDER 
BY id LIMIT 1" and then if the send to the web is successful it deletes 
that id.  This has all been working fine for months until I had one 
client hit a problem yesterday.  They kept sending the same message over 
and over to the web and would never move on to anything else.  Once I 
dug in further this is what I found:

(Note that I use a password on the database and I have not been able to 
find any db viewers for Windows which support the type of encryption 
that Windows SQLite uses, so I had to write my own simple query tool, 
the output of which is shown below)

select id,created from messages_priority order by id limit 5
idcreated
4242/12/2017 8:07:10 PM
4232/12/2017 8:07:09 PM
4252/12/2017 8:07:11 PM
4262/12/2017 8:07:12 PM
4272/12/2017 8:07:13 PM
**5 rows returned**

// So here we see the first problem.  424 is listed before 423 even 
though I'm specifying an order by id.

select id,created from messages_priority where id = 424
**No Rows**

// Now we see the next problem... even though 424 comes back in the 
first query, when I try to specify it in a WHERE clause it is not found.

select id,created from messages_priority where id = 423
idcreated
4232/12/2017 8:07:09 PM
**1 rows returned**

// 423, however, returns just fine that way...

delete from messages_priority where id = 424
**Command returned 0**

// delete doesn't throw an exception or anything, but indicates nothing 
was deleted

select id,created from messages_priority order by id limit 1
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// still shows up in a select with no WHERE

delete from messages_priority where id = 423
**Command returned 1**

// attempt to delete 423 is successful

select id,created from messages_priority where id = 424
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// now I can finally query by the id!

delete from messages_priority where id = 424
**Command returned 1**

// and delete it!

So somehow, records 423 and 424 seemed to have gotten "out of order" in 
the database, and until 423 was deleted, nothing could directly find 
424.  Because 424 could never be deleted my consumer kept getting it 
over and over and could never move on.  That makes sense at least...

But I can't imagine how I can work around this in code once the database 
has gotten into this state.  Does this seem like something that could be 
caused by improper inserts or is this possibly a bug in SQLite or the C# 
wrapper?  I don't do anything to change the defaults in terms of 
concurrency or isolation settings.  My understanding from the docs was 
that this should be thread safe without any extra locking on my end.  
Each thread that uses the db has its own connection var and re-uses that 
over and over.  The producer and consumer are separate threads but there 
is only one of each.

This is my code for inserting the rows:

  

Re: [sqlite] BUG: UPDATE with correlated sub-query

[Dan Kennedy]

On 02/13/2017 01:44 PM, Dudu Markovitz wrote:

Good morning

While answering question on stackoverflow
I have noticed a bug related
to UPDATE using correlated sub-query.
The demonstration code can be found in the attached file bug_report.sql and
the results of the demonstration with some comments added are in
bug_reports.txt.

I'm using SQLite 3.9.2 on windows 64 bit.


It's surprising, but a consequence of the way SQLite has always worked. 
Consider:


  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t1 VALUES(2, 0);

Then:

  UPDATE t1 SET b=(SELECT b+1 FROM t1 WHERE a=1);

In this case, the sub-query is uncorrelated and "b" is set to 2 in both 
rows.


But say you added some other term to the sub-query so that it was 
correlated. It is then executed separately for each row. So SQLite 
updates the first row (with a=1) and sets column "b" to 2. But then, 
when it goes to update the next row, it runs the correlated query a 
second time. And this time it returns 3. So you end up setting "b" in 
the second row to 3 instead of 2.


Something pretty similar is occurring in the example you posted.

Dan.

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

[sqlite] Partially "hidden" row, very bizarre state

[Jeff B.]

Hi Everyone,

Sorry for the strange subject, but the situation I'm facing is so odd 
that I wasn't even sure how to summarize it.  First, a little context:  
I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5 
project.  I have a table that is created as follows:


CREATE TABLE messages_priority (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
type TEXT,
json TEXT,
options TEXT,
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

The purpose of my SQLite db is to locally queue messages until they can 
be sent to a web API, at which time they are deleted.  My single 
consumer thread basically does a "SELECT * FROM messages_priority ORDER 
BY id LIMIT 1" and then if the send to the web is successful it deletes 
that id.  This has all been working fine for months until I had one 
client hit a problem yesterday.  They kept sending the same message over 
and over to the web and would never move on to anything else.  Once I 
dug in further this is what I found:


(Note that I use a password on the database and I have not been able to 
find any db viewers for Windows which support the type of encryption 
that Windows SQLite uses, so I had to write my own simple query tool, 
the output of which is shown below)


select id,created from messages_priority order by id limit 5
idcreated
4242/12/2017 8:07:10 PM
4232/12/2017 8:07:09 PM
4252/12/2017 8:07:11 PM
4262/12/2017 8:07:12 PM
4272/12/2017 8:07:13 PM
**5 rows returned**

// So here we see the first problem.  424 is listed before 423 even 
though I'm specifying an order by id.


select id,created from messages_priority where id = 424
**No Rows**

// Now we see the next problem... even though 424 comes back in the 
first query, when I try to specify it in a WHERE clause it is not found.


select id,created from messages_priority where id = 423
idcreated
4232/12/2017 8:07:09 PM
**1 rows returned**

// 423, however, returns just fine that way...

delete from messages_priority where id = 424
**Command returned 0**

// delete doesn't throw an exception or anything, but indicates nothing 
was deleted


select id,created from messages_priority order by id limit 1
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// still shows up in a select with no WHERE

delete from messages_priority where id = 423
**Command returned 1**

// attempt to delete 423 is successful

select id,created from messages_priority where id = 424
idcreated
4242/12/2017 8:07:10 PM
**1 rows returned**

// now I can finally query by the id!

delete from messages_priority where id = 424
**Command returned 1**

// and delete it!

So somehow, records 423 and 424 seemed to have gotten "out of order" in 
the database, and until 423 was deleted, nothing could directly find 
424.  Because 424 could never be deleted my consumer kept getting it 
over and over and could never move on.  That makes sense at least...


But I can't imagine how I can work around this in code once the database 
has gotten into this state.  Does this seem like something that could be 
caused by improper inserts or is this possibly a bug in SQLite or the C# 
wrapper?  I don't do anything to change the defaults in terms of 
concurrency or isolation settings.  My understanding from the docs was 
that this should be thread safe without any extra locking on my end.  
Each thread that uses the db has its own connection var and re-uses that 
over and over.  The producer and consumer are separate threads but there 
is only one of each.


This is my code for inserting the rows:

// use a transaction so we are sure to get the correct row 
id back

using (var transaction = sqlConnection.BeginTransaction())
{
try
{
var sql = String.Format("INSERT INTO 
messages_priority (type, json, options) VALUES (@p0, @p1, @p2)");
using (var command = new SQLiteCommand(sql, 
sqlConnection))

{
command.Parameters.Add(new 
SQLiteParameter("@p0", type));
command.Parameters.Add(new 
SQLiteParameter("@p1", json));
command.Parameters.Add(new 
SQLiteParameter("@p2", options));


command.ExecuteNonQuery();
}

var rowId = sqlConnection.LastInsertRowId;

transaction.Commit();

return rowId;
}
catch
{
// technically shouldn't be anything to roll back 
as we are only doing one thing and if that threw

// an exception then nothing happened...
transaction.Rollback();
throw;
}
}

I'm pretty much stumped here, any thoughts would be greatly

[sqlite] BUG: UPDATE with correlated sub-query

[Dudu Markovitz]

Good morning

While answering question on stackoverflow
I have noticed a bug related
to UPDATE using correlated sub-query.
The demonstration code can be found in the attached file bug_report.sql and
the results of the demonstration with some comments added are in
bug_reports.txt.

I'm using SQLite 3.9.2 on windows 64 bit.

Thanks

Dudu Markovitz
.mode column
.header on
.echo on

select sqlite_version()
;

create table mytable (id int,group_id int,IsHeader int,active int)
;

insert into mytable (id,group_id,IsHeader,active) values
(1,100,1,1)
   ,(2,100,0,0)
   ,(3,100,0,0)
   ,(4,100,0,1)
   ,(5,100,0,1)
;
 
select * from mytable
;

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
   -- and h.group_id = mytable.group_id
)   
;

select * from mytable
;

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
   -- and h.group_id = mytable.group_id
)   
;

select * from mytable
;

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
   -- and h.group_id = mytable.group_id
)   
;

select * from mytable
;

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
  and h.group_id = mytable.group_id
)   
;

select * from mytable
;  

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
  and h.group_id = mytable.group_id
)   
;

select * from mytable
;

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
  and h.group_id = mytable.group_id
)   
;

select * from mytable
;
  


select sqlite_version()
;
sqlite_version()

3.9.2   

create table mytable (id int,group_id int,IsHeader int,active int)
;

insert into mytable (id,group_id,IsHeader,active) values
(1,100,1,1)
   ,(2,100,0,0)
   ,(3,100,0,0)
   ,(4,100,0,1)
   ,(5,100,0,1)
;
 
select * from mytable
;
id  group_idIsHeaderactive
--  --  --  --
1   100 1   1 
2   100 0   0 
3   100 0   0 
4   100 0   1 
5   100 0   1 

/**
*   Update without correlation (note the commented condition) works as expected.
*   All rows, including the header, get the same value.
*   If header active=1 then all rows become active=0.
*   If header active=0 then all rows become active=1.
***/


update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
   -- and h.group_id = mytable.group_id
)   
;

select * from mytable
;
id  group_idIsHeaderactive
--  --  --  --
1   100 1   0 
2   100 0   0 
3   100 0   0 
4   100 0   0 
5   100 0   0 

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
   -- and h.group_id = mytable.group_id
)   
;

select * from mytable
;
id  group_idIsHeaderactive
--  --  --  --
1   100 1   1 
2   100 0   1 
3   100 0   1 
4   100 0   1 
5   100 0   1 

update  mytable

set Active =   (select1-h.Active 
from  mytable as h 
where h.IsHeader = 1
  

[sqlite] Faster check: > or !=

[jose isaias cabrera]

Greetings! 

I have a bunch of records to check, and I am wondering which is a faster
check.  I am attaching a network DB as client, 

ATTACH 'h:\bkup\test.db' AS client; 

and then do an INSERT based on some logic, and one of those login is
checking against a variable or an actual DB value.  Here are the
different SQL syntaxes: 

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB != XtraB  -- change
  );
END; 

BEGIN;
  INSERT OR REPLACE INTO OpenProjects
SELECT * FROM client.OpenProjects
  WHERE id IN
  (
SELECT id FROM client.OpenProjects
WHERE
 client.OpenProjects.id = id AND
 client.OpenProjects.ProjID <= 133560 AND
 client.OpenProjects.XtraB  > '2017-02-10 00:00:00'  -- change
  );
END; 

Any input would be greatly appreciated.  Thanks. 

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

[sqlite] Inconsistent check for symbol SQLITE_DEBUG in amalgamation version 3.16.2

[Ulrich Telle]

In the documentation for the compile time options for debugging

http://sqlite.org/compile.html#debug

the symbol SQLITE_DEBUG is shown without a value. And in most places in
the SQLite source code only the definition status is checked using

#ifdef SQLITE_DEBUG

or

#if defined(SQLITE_DEBUG)

However, at several places the *value* of the symbol is checked using

#if SQLITE_DEBUG

IMHO this is inconsistent with the documentation and probably should be
changed to

#ifdef SQLITE_DEBUG

The following lines are affected in the amalgamation source code of version
3.16.2:

17369
24482
24543
43985
63517
64886
74550
74655
80020
93533
179745

Please consider to fix this (or to adjust the documentation). Thanks.

Regards,

Ulrich

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

Re: [sqlite] Typos in macro usages for MSVC intrinsics

[Richard Hipp]

Good eye, Bert.  Thanks.

On 2/13/17, rhuij...@apache.org  wrote:
>   Hi,
>
> When looking at recent commit
> https://www.sqlite.org/src/info/798fb9d70d2e5f95 I spotted a few cases where
> 'MSCV_VERSION' is checked instead of 'MSVC_VERSION' (The V and C swapped),
> you might want to fix these before release.
>
> Thanks,
>   Bert
>
> ___
> 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] Typos in macro usages for MSVC intrinsics

[rhuijben]

Hi,

When looking at recent commit
https://www.sqlite.org/src/info/798fb9d70d2e5f95 I spotted a few cases where
'MSCV_VERSION' is checked instead of 'MSVC_VERSION' (The V and C swapped),
you might want to fix these before release.

Thanks,
Bert

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

Re: [sqlite] Bug: CREATE TABLE AS with GROUP BY preserves backticks in generated column name

[Cezary H. Noweta]

Hello,

On 2017-02-10 20:34, Alek Storm wrote:

CREATE TABLE t1 (col1 INT);
CREATE TABLE t2 AS SELECT `col1` FROM t1 GROUP BY col1;
.schema t2

CREATE TABLE t2("`col1`" INT);

I expected: CREATE TABLE t2(col1 INT);


This comes from the fact that all TK_COLUMN expressions in SELECT ... 
GROUP BY command are transformed into TK_AGG_COLUMN ones and as such 
their names are taken from ``zSpan'' (i.e. original text of an 
expression). Because the original text contains back quotes, it is 
further enclosed in double quotes. The following patch would help, 
however I'm not sure if an additional checking/validation is not required:


File ``select.c'', foo ``sqlite3ColumnsFromExprList(...)'', change the 
following line:


  if( pColExpr->op==TK_COLUMN && ALWAYS(pColExpr->pTab!=0) ){

into

  if( (pColExpr->op==TK_COLUMN || pColExpr->op==TK_AGG_COLUMN) && 
ALWAYS(pColExpr->pTab!=0) ){



-- best regards

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

Re: [sqlite] Extension Loading

[Green Fields]

>You could create a user-defined function to call LoadExtension(), but
>why do you need to do this from SQL?

A good question, but I am attempting to use a different .Net wrapper for
this project which has not imported sqlite3_enable_load_extension().
The simplest thing would be to import the function myself -  except that I
don't have the C skills to translate the arguments to .Net compatibility.

Your reference and comments re the sql function being disabled is something
I hadn't fully understood. I can see that the library has been compiled with
 ENABLE_LOAD_EXTENSION
 so I guess that
means sql is disabled.
I might have to rethink how I approach this then. Perhaps have another look
at importing the function

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