Re: [sqlite] How good is pragma integrity_check

[Simon Slavin]

On 20 Mar 2014, at 11:33pm, Richard Hipp  wrote:

> On Thu, Mar 20, 2014 at 7:18 PM, Tim Streater  wrote:
> 
>> I had a case where attempts to access a table in a user's db gave "no such
>> table", where 60 mins previously (according to the log) querying that table
>> gave no problems. About a day and a half later the user's machine had what
>> he described as a "bad crash", so I'm suspecting he has a failing disk. 
>> [snip]
> 
> PRAGMA integrity_check catches most things.  PRAGMA quick_check also does a
> good job, and it is much faster.  The only difference is that quick_check
> does not verify that indices agree with tables, whereas a full
> integrity_check does.

All useful as far as SQLite itself goes, and better than nothing.  
Unfortunately, failing hard disks do weird things in weird orders.  And the 
interaction between the physical hard disk and the on-board cache makes it 
impossible to find out what's really on the disk.  There's only one way to 
check whether the whole file is readable from a disk: read the whole file from 
the disk.  Then do an integrity check on the copy you just made.

Practically speaking, it's impossible.  Concentrate on having really good 
backups instead.

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

Re: [sqlite] How good is pragma integrity_check

[Richard Hipp]

On Thu, Mar 20, 2014 at 7:18 PM, Tim Streater  wrote:

> I had a case where attempts to access a table in a user's db gave "no such
> table", where 60 mins previously (according to the log) querying that table
> gave no problems. About a day and a half later the user's machine had what
> he described as a "bad crash", so I'm suspecting he has a failing disk. Due
> to a separate coincidence, this meant the app could not restart.
>
> Now, I want to protect against this or give better warning, and I am
> wondering whether corruption looking like a missing table would be caught
> by the integrity_check pragma.
>

PRAGMA integrity_check catches most things.  PRAGMA quick_check also does a
good job, and it is much faster.  The only difference is that quick_check
does not verify that indices agree with tables, whereas a full
integrity_check does.

There is also PRAGMA foreign_key_check to verify referential integrity.


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


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

[sqlite] How good is pragma integrity_check

[Tim Streater]

I had a case where attempts to access a table in a user's db gave "no such 
table", where 60 mins previously (according to the log) querying that table 
gave no problems. About a day and a half later the user's machine had what he 
described as a "bad crash", so I'm suspecting he has a failing disk. Due to a 
separate coincidence, this meant the app could not restart.

Now, I want to protect against this or give better warning, and I am wondering 
whether corruption looking like a missing table would be caught by the 
integrity_check pragma.

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

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

[Simon Slavin]

On 20 Mar 2014, at 2:19pm, Stefano Ravagni  wrote:

> I know, in facts i use a TEXT field...so you intend datagrid usually 
> understand if the field is or not a DATE field showing the value in 
> auto-formatted way ?

SQLite does not have a DATE datatype and does not understand dates at all.  You 
will need to store your dates as text or numbers, and use text and numbers when 
testing and comparing it.  If storing your data as text (slow but 
human-readable and ready for display) you should use standard form.  If storing 
your data as numbers (faster but harder to debug) I would suggest Julian day 
numbers.

SQLite does provide some functions that will help with this:



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

Re: [sqlite] Assertion fires in SQLite 3.8.4.1 for rather basic query

[Jens Miltner]

Thanks a lot for the fast answer & fix.

-jens


Am 20.03.2014 um 14:41 schrieb Richard Hipp :

> The ticket is now closed.  Thanks for the bug report.
> 
> To confirm:  The simplest fix is merely to compile without -DSQLITE_DEBUG
> which will disable assert() statements, as the problem is an incorrect
> assert().  There is nothing actually wrong with the logic, that we can
> see.  An alternative work-around is to delete or comment-out the offending
> assert() statement.
> 
> The fix on trunk retains the assert().  The assert() is correct as long as
> the right-hand side of IN operators always have two or more values.  In
> other words, the assert() is correct as long as you never have "x IN (?)"
> with exactly one element in the right-hand side.  On trunk, the parser has
> been modified to automatically convert IN and NOT IN operators with exactly
> one element on their right-hand side into == and <> operators.  That change
> makes the assert() correct again, and it results in faster evaluation SQL
> statements that have an IN or NOT IN operator with just a single RHS
> element.
> 
> 
> 
> On Thu, Mar 20, 2014 at 8:27 AM, Richard Hipp  wrote:
> 
>> http://www.sqlite.org/src/info/e39d032577
>> 
>> It appears that the fix will be to simply remove the assert() statement,
>> which is incorrect.  But it will take some time to verify that this is the
>> correct fix and add new test cases, etc.
>> 
>> Your work-around is to simply compile without -DSQLITE_DEBUG (thus
>> disabling all assert() statements) or delete the assert() that is failing.
>> 
>> 
>> On Thu, Mar 20, 2014 at 7:26 AM, Jens Miltner  wrote:
>> 
>>> Hi,
>>> 
>>> I ran into the following problem after updating the SQLite 3.8.4.1:
>>> 
>>> When executing the following (rather basic) SELECT query in a debug build
>>> of sqlite3, this will cause an assertion to fire in
>>> whereLoopAddBtreeIndex() (sqlite3.c, line 13411):
>>> 
>>> SELECT * FROM t1 WHERE(foo_id=5 AND name IN ('foo'));
>>> 
>>> The database schema to reproduce is:
>>> 
>>> CREATE TABLE t1 (id INTEGER PRIMARY KEY, foo_id INTEGER, name
>>> VARCHAR(36), phone VARCHAR(36));
>>> CREATE UNIQUE INDEX t1_udx ON t1(name, foo_id);
>>> CREATE INDEX t1_idx ON t1 (phone, foo_id);
>>> 
>>> 
>>> The assertion that fires is the following:
>>> 
>>>assert( (pNew->wsFlags & WHERE_COLUMN_IN)==0 || iCol<0 );
>>> 
>>> 
>>> This is quite annoying, since it will terminate the app when we build our
>>> app with DEBUG enabled and a similar query is executed.
>>> 
>>> Can you SQLite folks please have a look into this?
>>> 
>>> Thanks,
>>> -jens
>>> 

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

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

[Stefano Ravagni]

I know, in facts i use a TEXT field...so you intend datagrid usually 
understand if the field is or not a DATE field showing the value in 
auto-formatted way ?

If this is the explanation, is not a big problemthanks for reply !

Il 20/03/2014 13.31, Kees Nuyt ha scritto:

On Wed, 19 Mar 2014 14:39:39 -0700 (PDT), Stefano Ravagni
 wrote:


Hello, i'm new in SQLite develop...

i'n a gridview (.NET) i visualize the data type in -MM-DD format

Using others database i ever see data in format which follow culture
setting (italian in this case)... but not in SQLite..

How could i solve ?

SQLite has no type "date".

If you see "-MM-DD", it is stored as type TEXT, in exactly that
format (and that is always correct everywhere, because ISO8601 is the
international standard).

You can use one of the date formatting functions in your select
statement to change the presentation.

http://sqlite.org/lang_datefunc.html



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

Re: [sqlite] Assertion fires in SQLite 3.8.4.1 for rather basic query

[Richard Hipp]

The ticket is now closed.  Thanks for the bug report.

To confirm:  The simplest fix is merely to compile without -DSQLITE_DEBUG
which will disable assert() statements, as the problem is an incorrect
assert().  There is nothing actually wrong with the logic, that we can
see.  An alternative work-around is to delete or comment-out the offending
assert() statement.

The fix on trunk retains the assert().  The assert() is correct as long as
the right-hand side of IN operators always have two or more values.  In
other words, the assert() is correct as long as you never have "x IN (?)"
with exactly one element in the right-hand side.  On trunk, the parser has
been modified to automatically convert IN and NOT IN operators with exactly
one element on their right-hand side into == and <> operators.  That change
makes the assert() correct again, and it results in faster evaluation SQL
statements that have an IN or NOT IN operator with just a single RHS
element.



On Thu, Mar 20, 2014 at 8:27 AM, Richard Hipp  wrote:

> http://www.sqlite.org/src/info/e39d032577
>
> It appears that the fix will be to simply remove the assert() statement,
> which is incorrect.  But it will take some time to verify that this is the
> correct fix and add new test cases, etc.
>
> Your work-around is to simply compile without -DSQLITE_DEBUG (thus
> disabling all assert() statements) or delete the assert() that is failing.
>
>
> On Thu, Mar 20, 2014 at 7:26 AM, Jens Miltner  wrote:
>
>> Hi,
>>
>> I ran into the following problem after updating the SQLite 3.8.4.1:
>>
>> When executing the following (rather basic) SELECT query in a debug build
>> of sqlite3, this will cause an assertion to fire in
>> whereLoopAddBtreeIndex() (sqlite3.c, line 13411):
>>
>> SELECT * FROM t1 WHERE(foo_id=5 AND name IN ('foo'));
>>
>> The database schema to reproduce is:
>>
>> CREATE TABLE t1 (id INTEGER PRIMARY KEY, foo_id INTEGER, name
>> VARCHAR(36), phone VARCHAR(36));
>> CREATE UNIQUE INDEX t1_udx ON t1(name, foo_id);
>> CREATE INDEX t1_idx ON t1 (phone, foo_id);
>>
>>
>> The assertion that fires is the following:
>>
>> assert( (pNew->wsFlags & WHERE_COLUMN_IN)==0 || iCol<0 );
>>
>>
>> This is quite annoying, since it will terminate the app when we build our
>> app with DEBUG enabled and a similar query is executed.
>>
>> Can you SQLite folks please have a look into this?
>>
>> Thanks,
>> -jens
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



-- 
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] Trigger to another database

[SQlite Sqlite]

Hello,
I have two sqlite databases. (db1, db2)
I try to write a trigger in db1 which inserts data to a table in db2.
In the sqlite docu there is a section about "Temp Triggers".
 
"...Except, it is possible to create a TEMP TRIGGER on a table in another 
database."

CREATE TEMP TRIGGER ex1 AFTER INSERT ON main.tab1 BEGIN ...

I tried this but I get always the error
SQL Error: qualified table names are not allowed on INSERT, UPDATE, and DELETE 
statements within triggers
 
If this is not allowed within a trigger, is there a workournd to synchronize 
two tables between different databases?
 
regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:

[Kees Nuyt]

On Wed, 19 Mar 2014 14:39:39 -0700 (PDT), Stefano Ravagni
 wrote:

>Hello, i'm new in SQLite develop...
>
>i'n a gridview (.NET) i visualize the data type in -MM-DD format
>
>Using others database i ever see data in format which follow culture 
>setting (italian in this case)... but not in SQLite..
>
>How could i solve ?

SQLite has no type "date".

If you see "-MM-DD", it is stored as type TEXT, in exactly that
format (and that is always correct everywhere, because ISO8601 is the
international standard).

You can use one of the date formatting functions in your select
statement to change the presentation.

http://sqlite.org/lang_datefunc.html 

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Assertion fires in SQLite 3.8.4.1 for rather basic query

[Richard Hipp]

http://www.sqlite.org/src/info/e39d032577

It appears that the fix will be to simply remove the assert() statement,
which is incorrect.  But it will take some time to verify that this is the
correct fix and add new test cases, etc.

Your work-around is to simply compile without -DSQLITE_DEBUG (thus
disabling all assert() statements) or delete the assert() that is failing.


On Thu, Mar 20, 2014 at 7:26 AM, Jens Miltner  wrote:

> Hi,
>
> I ran into the following problem after updating the SQLite 3.8.4.1:
>
> When executing the following (rather basic) SELECT query in a debug build
> of sqlite3, this will cause an assertion to fire in
> whereLoopAddBtreeIndex() (sqlite3.c, line 13411):
>
> SELECT * FROM t1 WHERE(foo_id=5 AND name IN ('foo'));
>
> The database schema to reproduce is:
>
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, foo_id INTEGER, name VARCHAR(36),
> phone VARCHAR(36));
> CREATE UNIQUE INDEX t1_udx ON t1(name, foo_id);
> CREATE INDEX t1_idx ON t1 (phone, foo_id);
>
>
> The assertion that fires is the following:
>
> assert( (pNew->wsFlags & WHERE_COLUMN_IN)==0 || iCol<0 );
>
>
> This is quite annoying, since it will terminate the app when we build our
> app with DEBUG enabled and a similar query is executed.
>
> Can you SQLite folks please have a look into this?
>
> Thanks,
> -jens
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


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

[sqlite] Documentation improvement request: PRAGMAs

[Simon Slavin]

If I got this right, there are five types of PRAGMA:

A) some return information without making changes
B) some affect the loaded SQLite library: all connections and future 
connections until the library is unloaded
C) some change the behaviour of one connection without changing anything in the 
database, but reopening the database file won't know they've happened
D) some change something saved in the database file, and reopening the database 
file will see the change
E) some do something to the open database, but don't change a queriable setting

Examples:

A) PRAGMA database_list 
B) PRAGMA default_cache_size = Number-of-pages
C) PRAGMA locking_mode
D) PRAGMA application_id = integer
E) PRAGMA incremental_vacuum(N)

The PRAGMA documentation page is often clear on which PRAGMA is in which 
category, but sometimes not.  For instance, the description for

PRAGMA auto_vacuum

says

"Query or set the auto-vacuum status in the database.
[...]
The database connection can be changed between full and incremental autovacuum 
mode at any time."

which suggests first (D) then (C).  My guess is that it's actually (D) and that 
the wording in the second part should be changed.  But I might be wrong.

The descriptions of some PRAGMAs -- for example PRAGMA cache_spill -- are 
unclear.  You have to have some appreciation of how SQLite works to understand 
which category the PRAGMA is in.  I ask that someone familiar with how SQLite 
works internally go through the PRAGMA page and check to see the descriptions 
are clear.

As part of that, or as a separate exercise, or perhaps just for SQLite4, it 
might be nice to devise a category system (you can probably improve on my (A) 
to (E) one) and state explicitly which category each PRAGMA is in on the page.  
Or perhaps split the PRAGMA documentation page into five (or however many it 
is) sections or separate pages.  I learned a useful thing about SQLite by just 
trying to categorise each PRAGMA using my own categories.

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

Re: [sqlite] Assertion fires in SQLite 3.8.4.1 for rather basic query

[Jens Miltner]

Sorry, forgot to mention that our previous version of SQLite was 3.7.17 and we 
did not experience the issue there...

Am 20.03.2014 um 12:26 schrieb Jens Miltner :

> Hi,
> 
> I ran into the following problem after updating the SQLite 3.8.4.1:
> 
> When executing the following (rather basic) SELECT query in a debug build of 
> sqlite3, this will cause an assertion to fire in whereLoopAddBtreeIndex() 
> (sqlite3.c, line 13411):
> 
> SELECT * FROM t1 WHERE(foo_id=5 AND name IN ('foo'));
> 
> The database schema to reproduce is:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, foo_id INTEGER, name VARCHAR(36), 
> phone VARCHAR(36));
> CREATE UNIQUE INDEX t1_udx ON t1(name, foo_id);
> CREATE INDEX t1_idx ON t1 (phone, foo_id);
> 
> 
> The assertion that fires is the following:
> 
>assert( (pNew->wsFlags & WHERE_COLUMN_IN)==0 || iCol<0 );
> 
> 
> This is quite annoying, since it will terminate the app when we build our app 
> with DEBUG enabled and a similar query is executed.
> 
> Can you SQLite folks please have a look into this?
> 
> Thanks,
> -jens
> 
> 

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

[sqlite] Assertion fires in SQLite 3.8.4.1 for rather basic query

[Jens Miltner]

Hi,

I ran into the following problem after updating the SQLite 3.8.4.1:

When executing the following (rather basic) SELECT query in a debug build of 
sqlite3, this will cause an assertion to fire in whereLoopAddBtreeIndex() 
(sqlite3.c, line 13411):

SELECT * FROM t1 WHERE(foo_id=5 AND name IN ('foo'));

The database schema to reproduce is:

CREATE TABLE t1 (id INTEGER PRIMARY KEY, foo_id INTEGER, name VARCHAR(36), 
phone VARCHAR(36));
CREATE UNIQUE INDEX t1_udx ON t1(name, foo_id);
CREATE INDEX t1_idx ON t1 (phone, foo_id);


The assertion that fires is the following:

assert( (pNew->wsFlags & WHERE_COLUMN_IN)==0 || iCol<0 );


This is quite annoying, since it will terminate the app when we build our app 
with DEBUG enabled and a similar query is executed.

Can you SQLite folks please have a look into this?

Thanks,
-jens


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

Re: [sqlite] SQLite auto_vacuum doesn't work?

[Simon Slavin]

On 19 Mar 2014, at 10:53pm, m.d.berce...@gmail.com wrote:

> I'm running the "pragma auto_vacuum = 'full'" a C++ code, then I go to 
> SQLiteSpy, open the database and run pragma auto_vacuum in the query window 
> and the result is 0. 

The syntax is

PRAGMA auto_vacuum = FULL

no quotes or apostrophes.

> I tried "pragma auto_vacuum = 1" and still the same, it doesn't seem to 
> keep the setting.

According to the documentation for the command you need to issue the 'VACUUM' 
command to change the status of an existing database:



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

[sqlite] Date in gridview visualized in YYYY-MM-DD format:

[Stefano Ravagni]

Hello, i'm new in SQLite develop...

i'n a gridview (.NET) i visualize the data type in -MM-DD format

Using others database i ever see data in format which follow culture 
setting (italian in this case)... but not in SQLite..

How could i solve ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite auto_vacuum doesn't work?

[m . d . berceanu]

Hi there,
I'm running the "pragma auto_vacuum = 'full'" a C++ code, then I go to 
SQLiteSpy, open the database and run pragma auto_vacuum in the query window 
and the result is 0. 
I tried "pragma auto_vacuum = 1" and still the same, it doesn't seem to 
keep the setting.
I have some tables that get written and truncated a lot, and the size of 
the database grows out of control if I don't clean it up periodically.
Please help, any suggestions are highly appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] very slow fdsync() calls

[Kees Nuyt]

On Thu, 20 Mar 2014 18:05:48 +1300, Jono Poff
 wrote:

> and I can't easily 
> change the page size of the db either.  

You may have other reasons why you can't do it easily, but

PRAGMA page_size=newpagesize;
VACUUM;

will convert to newpagesize.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] ANN: SQLite Maestro 14.3 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Maestro 14.3, a complete 
Windows GUI solution for SQLite database management. The new version is 
immediately available at

http://www.sqlmaestro.com/products/sqlite/maestro/

Top 10 new features
=

1. Support for WITHOUT ROWID tables.
2. Improved constraint management.
3. Support for various data storage modes.
4. New Nullable Column Checker tool.
5. Enhanced Get SQL Dump wizard.
6. Optimized metadata modifications.
7. Data import from several files to a single table.
8. Advanced Column Builder for text files with fixed column width.
9. Data export to the JSON format.
10. Highlighted required columns in data grids.

Full press-release (with explaining screenshots) is available at:
http://www.sqlmaestro.com/news/company/sqlite_maestro_14_3_released/

Background information:

SQL Maestro Group offers complete database admin and management tools for 
MySQL, Oracle, MS SQL Server, PostgreSQL, SQLite, DB2, Firebird, SQL 
Anywhere and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.


Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com


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