Re: [sqlite] SQLite Version 3.7.1

2010-08-23 Thread Alexey Pechnikov
Richard, how to set the default value for SQLITE_FCNTL_CHUNK_SIZE? And
is it possible to change this from sqlite3 shell and from tcl
interface?

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] curses vs ncurses

2010-08-23 Thread Yury G. Kudryashov
Hi!

Could you please replace
   AC_SEARCH_LIBS(tgetent, curses, [], [])
by
   AC_SEARCH_LIBS(tgetent, curses ncurses ncursesw, [], [])
in configure.ac? This would help finding libncurses.so on systems lacking 
compatibility libcurses.so symlink.

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


Re: [sqlite] SQLite Version 3.7.1

2010-08-23 Thread Richard Hipp
On Mon, Aug 23, 2010 at 5:50 AM, Alexey Pechnikov pechni...@mobigroup.ruwrote:

 Richard, how to set the default value for SQLITE_FCNTL_CHUNK_SIZE? And
 is it possible to change this from sqlite3 shell and from tcl
 interface?


There is no way to set the default value - the value must be set anew with
each connection.

After we gain experience with this setting, and determine (via actual
measurement) whether or not it is useful in reducing disk fragmentation, we
will consider providing a PRAGMA interface for it.


 --
 Best regards, Alexey Pechnikov.
 http://pechnikov.tel/
 ___
 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] rowid column as a FOREIGN KEY

2010-08-23 Thread inst
Hi,

Recently I've found one problem in SQLite and I think that this is a bug.

Are there any ways to use rowid column in one table as a foreign key in another?
By the rowid I mean internal column that used as an alias for INTEGER
PRIMARY KEY columns.

As described at documents, SQLite supports foreign keys as of version 3.6.19.
When I use a new version of SQLite it doesn't do anything to save a
referential integrity.
I also tried to use utility .genfkey in older version to create
TRIGGERS for my database but it says that there is no such column in
parent table or something like this.

Do anyone encounter this problem or I just did something wrong?

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


Re: [sqlite] Help with database corruption?

2010-08-23 Thread Filip Navara
I can now reliably corrupt the database using standard commands. An
SQL script can be downloaded at the address below that creates a
database and then stresses it until a corruption happens.

http://www.emclient.com/temp/sqlite_corrupt_log.zip

Please help fix the problem or at least confirm that others can
reproduce it using the same script.

Best regards,
Filip Navara

On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara filip.nav...@gmail.com wrote:
 Hello,

 is there anybody willing to help analyze corrupted database for
 possible bug in SQLite?

 It is a database file taken from one of our test machines and it is
 only few days old at most. The database file was only ever accessed
 with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
 synchronous=FULL and incremental vacuum. No power failure happened on
 that machine. Apparently somewhere during the course of execution of
 our application the database free page list become corrupted. This is
 for the third time this week the same error happened, but only this
 time I have a copy of the database file. It is 887 Mb big (although
 the real data consume less than 2 Mb) and so it is rather problematic
 to provide the file. A copy is available at
 http://www.emclient.com/temp/mail_data.zip.

 The database was accessed on Windows machine using SQLite 3.7.0.1
 64-bit build. The following statements are the only ones that were
 executed against the database besides SELECTs and initial schema
 definition:

 INSERT INTO LocalMailContents (
   id, partName, contentType, contentId,
   contentDescription, contentTransferEncoding,
   contentMD5, contentDisposition, contentLanguage,
   contentLocation, partHeader, partBody,
   synchronizationKey, contentLength)
   VALUES
   (@id, @partName, @contentType, @contentId, @contentDescription,
   �...@contenttransferencoding, @contentMD5, @contentDisposition,
   �...@contentlanguage, @contentLocation, @partHeader, @partBody,
   �...@synchronizationkey, @contentLength)
 UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
 WHERE i...@id AND partna...@partname
 UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
 partna...@partname
 UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
 partna...@partname
 DELETE FROM LocalMailContents WHERE id IN (list of oids);
 PRAGMA freelist_count;
 PRAGMA incremental_vacuum(a number between 24 and 4096);

 The error messages produced by pragma integrity_check are

 *** in database main ***
 Main freelist: invalid page number 866828
 Page 396 is never used
 Page 473 is never used
 Page 14780 is never used
 Page 14915 is never used
 Page 153649 is never used
 Page 210894 is never used
 Page 319247 is never used
 Page 397006 is never used
 Page 416545 is never used
 Page 416636 is never used
 Page 416704 is never used
 Page 416705 is never used
 Page 416706 is never used
 Page 416707 is never used
 Page 416708 is never used
 Page 416710 is never used
 Page 416711 is never used
 ...

 I tried to locate the missing freelist pages in the database file and
 they definitely were there at some point, but I am not familiar enough
 with the file format to track the whole freelist and find where the
 corruption exactly happen. All I know is that page 388 is corrupted
 and points to a location outside of the database file.

 Thanks,
 Filip Navara

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


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread Oliver Peters
inst ins...@... writes:

  
 Do anyone encounter this problem or I just did something wrong?
 


If you've an example (CREATE  INSERT/DELETE statements or whatever clarifies
what you do) the possibility that someone can give you a quick and meaningful
answer will increase enormous.

My guess: you did something wrong -
just to test yourself: do you know
 PRAGMA foreign_keys=ON;

?

If not take a look at the documentation (http://www.sqlite.org/foreignkeys.html)

greetings
Oliver

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


Re: [sqlite] Help with database corruption?

2010-08-23 Thread Black, Michael (IS)
I confirmed this shows a malformed result on 3.6.23.1 and 3.7.0.1
 
But it runs just fine under 3.7.1
 
So apparently whatever bug you triggered has been fixed now.
 
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Filip Navara
Sent: Mon 8/23/2010 7:43 AM
To: General Discussion of SQLite Database
Cc: d...@hwaci.com
Subject: EXTERNAL:Re: [sqlite] Help with database corruption?



I can now reliably corrupt the database using standard commands. An
SQL script can be downloaded at the address below that creates a
database and then stresses it until a corruption happens.

http://www.emclient.com/temp/sqlite_corrupt_log.zip

Please help fix the problem or at least confirm that others can
reproduce it using the same script.

Best regards,
Filip Navara

On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara filip.nav...@gmail.com wrote:
 Hello,

 is there anybody willing to help analyze corrupted database for
 possible bug in SQLite?

 It is a database file taken from one of our test machines and it is
 only few days old at most. The database file was only ever accessed
 with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
 synchronous=FULL and incremental vacuum. No power failure happened on
 that machine. Apparently somewhere during the course of execution of
 our application the database free page list become corrupted. This is
 for the third time this week the same error happened, but only this
 time I have a copy of the database file. It is 887 Mb big (although
 the real data consume less than 2 Mb) and so it is rather problematic
 to provide the file. A copy is available at
 http://www.emclient.com/temp/mail_data.zip.

 The database was accessed on Windows machine using SQLite 3.7.0.1
 64-bit build. The following statements are the only ones that were
 executed against the database besides SELECTs and initial schema
 definition:

 INSERT INTO LocalMailContents (
   id, partName, contentType, contentId,
   contentDescription, contentTransferEncoding,
   contentMD5, contentDisposition, contentLanguage,
   contentLocation, partHeader, partBody,
   synchronizationKey, contentLength)
   VALUES
   (@id, @partName, @contentType, @contentId, @contentDescription,
@contentTransferEncoding, @contentMD5, @contentDisposition,
@contentLanguage, @contentLocation, @partHeader, @partBody,
@synchronizationKey, @contentLength)
 UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
 WHERE i...@id AND partna...@partname
 UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
 partna...@partname
 UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
 partna...@partname
 DELETE FROM LocalMailContents WHERE id IN (list of oids);
 PRAGMA freelist_count;
 PRAGMA incremental_vacuum(a number between 24 and 4096);

 The error messages produced by pragma integrity_check are

 *** in database main ***
 Main freelist: invalid page number 866828
 Page 396 is never used
 Page 473 is never used
 Page 14780 is never used
 Page 14915 is never used
 Page 153649 is never used
 Page 210894 is never used
 Page 319247 is never used
 Page 397006 is never used
 Page 416545 is never used
 Page 416636 is never used
 Page 416704 is never used
 Page 416705 is never used
 Page 416706 is never used
 Page 416707 is never used
 Page 416708 is never used
 Page 416710 is never used
 Page 416711 is never used
 ...

 I tried to locate the missing freelist pages in the database file and
 they definitely were there at some point, but I am not familiar enough
 with the file format to track the whole freelist and find where the
 corruption exactly happen. All I know is that page 388 is corrupted
 and points to a location outside of the database file.

 Thanks,
 Filip Navara

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


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


[sqlite] Better way to export sqlite3 data than pipe?

2010-08-23 Thread Peng Yu
Hi,

Since I don't find a command that can directly export the data into a
file, I use pipe to export data from sqlite3 to a tsv file. Is there a
better way to do so?

$ cat main.sh
#!/usr/bin/env bash

rm -rf main.db3
sqlite3 main.db3 '.read main.sql'  main.txt

$ cat main.sql
create table A (name text, position integer);

insert into A values('a', 1);
insert into A values('b', 10);
.separator ','
select * from A;
$ ./main.sh
$ cat main.txt
a,1
b,10


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


Re: [sqlite] Better way to export sqlite3 data than pipe?

2010-08-23 Thread Simon Davies
On 23 August 2010 14:09, Peng Yu pengyu...@gmail.com wrote:
 Hi,

 Since I don't find a command that can directly export the data into a
 file, I use pipe to export data from sqlite3 to a tsv file. Is there a
 better way to do so?

There is an alternative to using pipe:
http://www.sqlite.org/sqlite.html and look for Writing results to a
file on that page.


 $ cat main.sh
 #!/usr/bin/env bash

 rm -rf main.db3
 sqlite3 main.db3 '.read main.sql'  main.txt

 $ cat main.sql
 create table A (name text, position integer);

 insert into A values('a', 1);
 insert into A values('b', 10);
 .separator ','
 select * from A;
 $ ./main.sh
 $ cat main.txt
 a,1
 b,10


 --
 Regards,
 Peng

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


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread inst
Thanks for your answer, Oliver.

 http://www.sqlite.org/foreignkeys.html
Yes, I have already read this before mailing here.

 PRAGMA foreign_keys
Yes, I knew about this PRAGMA.

Ok, I'll try to describe my problem with one example. Imagine we have
to store information about pages and the keywords. Any page may
contain any number of keywords, but any keyword may also belong to any
number of pages. So I'm trying to implement many-to-many relationship.

Here is small copy and paste from my terminal:

SQLite version 3.7.0.1
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite PRAGMA foreign_keys = ON;
sqlite CREATE TABLE pages (
   ... address VARCHAR ( 128 ) DEFAULT '/',
   ... body TEXT DEFAULT ''
   ... );
sqlite CREATE TABLE keywords ( word VARCHAR ( 64 ) NOT NULL UNIQUE );
sqlite CREATE TABLE relations (
   ... page INTEGER NOT NULL,
   ... keyword INTEGER NOT NULL,
   ... FOREIGN KEY ( page ) REFERENCES pages( rowid ) ON UPDATE
CASCADE ON DELETE CASCADE,
   ... FOREIGN KEY ( keyword ) REFERENCES keywords( rowid ) ON UPDATE
CASCADE ON DELETE CASCADE
   ... );
sqlite INSERT INTO pages ( body ) VALUES ( '' );
sqlite INSERT INTO pages ( address ) VALUES ( '/contacts' );
sqlite INSERT INTO keywords ( word ) VALUES ( 'word1' );
sqlite INSERT INTO keywords ( word ) VALUES ( 'word2' );

As for this place all were ok and here is the problem begin:

sqlite INSERT INTO relations VALUES ( 1,2 );
Error: foreign key mismatch
sqlite INSERT INTO relations VALUES ( 1,1 );
Error: foreign key mismatch

Can anyone please tell what I did wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Help with database corruption?

2010-08-23 Thread Filip Navara
I have uploaded a slightly different (and smaller) version of the
script. It triggers the bug in both version 3.7.0.1 and 3.7.1 on my
machine.

Thanks for testing,
F.

On Mon, Aug 23, 2010 at 3:00 PM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
 I confirmed this shows a malformed result on 3.6.23.1 and 3.7.0.1

 But it runs just fine under 3.7.1

 So apparently whatever bug you triggered has been fixed now.


 Michael D. Black
 Senior Scientist
 Advanced Analytics Directorate
 Northrop Grumman Information Systems


 

 From: sqlite-users-boun...@sqlite.org on behalf of Filip Navara
 Sent: Mon 8/23/2010 7:43 AM
 To: General Discussion of SQLite Database
 Cc: d...@hwaci.com
 Subject: EXTERNAL:Re: [sqlite] Help with database corruption?



 I can now reliably corrupt the database using standard commands. An
 SQL script can be downloaded at the address below that creates a
 database and then stresses it until a corruption happens.

 http://www.emclient.com/temp/sqlite_corrupt_log.zip

 Please help fix the problem or at least confirm that others can
 reproduce it using the same script.

 Best regards,
 Filip Navara

 On Fri, Aug 20, 2010 at 5:25 PM, Filip Navara filip.nav...@gmail.com wrote:
 Hello,

 is there anybody willing to help analyze corrupted database for
 possible bug in SQLite?

 It is a database file taken from one of our test machines and it is
 only few days old at most. The database file was only ever accessed
 with SQLite 3.7.0.1. It has page size of 1024, WAL mode,
 synchronous=FULL and incremental vacuum. No power failure happened on
 that machine. Apparently somewhere during the course of execution of
 our application the database free page list become corrupted. This is
 for the third time this week the same error happened, but only this
 time I have a copy of the database file. It is 887 Mb big (although
 the real data consume less than 2 Mb) and so it is rather problematic
 to provide the file. A copy is available at
 http://www.emclient.com/temp/mail_data.zip.

 The database was accessed on Windows machine using SQLite 3.7.0.1
 64-bit build. The following statements are the only ones that were
 executed against the database besides SELECTs and initial schema
 definition:

 INSERT INTO LocalMailContents (
   id, partName, contentType, contentId,
   contentDescription, contentTransferEncoding,
   contentMD5, contentDisposition, contentLanguage,
   contentLocation, partHeader, partBody,
   synchronizationKey, contentLength)
   VALUES
   (@id, @partName, @contentType, @contentId, @contentDescription,
   �...@contenttransferencoding, @contentMD5, @contentDisposition,
   �...@contentlanguage, @contentLocation, @partHeader, @partBody,
   �...@synchronizationkey, @contentLength)
 UPDATE LocalMailContents SET synchronizationk...@synchronizationkey
 WHERE i...@id AND partna...@partname
 UPDATE LocalMailContents SET parthead...@partheader WHERE i...@id AND
 partna...@partname
 UPDATE LocalMailContents SET partbo...@partcontent WHERE i...@id AND
 partna...@partname
 DELETE FROM LocalMailContents WHERE id IN (list of oids);
 PRAGMA freelist_count;
 PRAGMA incremental_vacuum(a number between 24 and 4096);

 The error messages produced by pragma integrity_check are

 *** in database main ***
 Main freelist: invalid page number 866828
 Page 396 is never used
 Page 473 is never used
 Page 14780 is never used
 Page 14915 is never used
 Page 153649 is never used
 Page 210894 is never used
 Page 319247 is never used
 Page 397006 is never used
 Page 416545 is never used
 Page 416636 is never used
 Page 416704 is never used
 Page 416705 is never used
 Page 416706 is never used
 Page 416707 is never used
 Page 416708 is never used
 Page 416710 is never used
 Page 416711 is never used
 ...

 I tried to locate the missing freelist pages in the database file and
 they definitely were there at some point, but I am not familiar enough
 with the file format to track the whole freelist and find where the
 corruption exactly happen. All I know is that page 388 is corrupted
 and points to a location outside of the database file.

 Thanks,
 Filip Navara

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


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

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


Re: [sqlite] Better way to export sqlite3 data than pipe?

2010-08-23 Thread Jay A. Kreibich
On Mon, Aug 23, 2010 at 08:09:51AM -0500, Peng Yu scratched on the wall:
 Hi,
 
 Since I don't find a command that can directly export the data into a
 file, I use pipe to export data from sqlite3 to a tsv file. Is there a
 better way to do so?A

  See .output filename

   -j

-- 
Jay A. Kreibich  J A Y  @  K R E I B I.C H 

Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable. -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread Pavel Ivanov
Make it your rule of thumb: don't ever use rowid, declare your own
column as integer primary key and use it. It will come at no cost
for you and everything else will work much better.
Here is simplified example of your problem and solution:

sqlite pragma foreign_keys=on;
sqlite create table t (n int);
sqlite create table tt (n int);
sqlite create table ttt (a int, b int, foreign key (a) references t
(rowid), foreign key (b) references tt (rowid));
sqlite insert into t values (1);
sqlite insert into tt values (1);
sqlite insert into ttt values (1, 1);
Error: foreign key mismatch
sqlite drop table t;
sqlite drop table tt;
sqlite drop table ttt;
sqlite create table t (id integer primary key, n int);
sqlite create table tt (id integer primary key, n int);
sqlite create table ttt (a int, b int, foreign key (a) references t
(id), foreign key (b) references tt (id));
sqlite insert into t (n) values (1);
sqlite insert into tt (n) values (1);
sqlite insert into ttt values (1, 1);
sqlite delete from t;
Error: foreign key constraint failed
sqlite


Pavel

On Mon, Aug 23, 2010 at 9:23 AM, inst i...@mail.ua wrote:
 Thanks for your answer, Oliver.

 http://www.sqlite.org/foreignkeys.html
 Yes, I have already read this before mailing here.

 PRAGMA foreign_keys
 Yes, I knew about this PRAGMA.

 Ok, I'll try to describe my problem with one example. Imagine we have
 to store information about pages and the keywords. Any page may
 contain any number of keywords, but any keyword may also belong to any
 number of pages. So I'm trying to implement many-to-many relationship.

 Here is small copy and paste from my terminal:

 SQLite version 3.7.0.1
 Enter .help for instructions
 Enter SQL statements terminated with a ;
 sqlite PRAGMA foreign_keys = ON;
 sqlite CREATE TABLE pages (
   ... address VARCHAR ( 128 ) DEFAULT '/',
   ... body TEXT DEFAULT ''
   ... );
 sqlite CREATE TABLE keywords ( word VARCHAR ( 64 ) NOT NULL UNIQUE );
 sqlite CREATE TABLE relations (
   ... page INTEGER NOT NULL,
   ... keyword INTEGER NOT NULL,
   ... FOREIGN KEY ( page ) REFERENCES pages( rowid ) ON UPDATE
 CASCADE ON DELETE CASCADE,
   ... FOREIGN KEY ( keyword ) REFERENCES keywords( rowid ) ON UPDATE
 CASCADE ON DELETE CASCADE
   ... );
 sqlite INSERT INTO pages ( body ) VALUES ( '' );
 sqlite INSERT INTO pages ( address ) VALUES ( '/contacts' );
 sqlite INSERT INTO keywords ( word ) VALUES ( 'word1' );
 sqlite INSERT INTO keywords ( word ) VALUES ( 'word2' );

 As for this place all were ok and here is the problem begin:

 sqlite INSERT INTO relations VALUES ( 1,2 );
 Error: foreign key mismatch
 sqlite INSERT INTO relations VALUES ( 1,1 );
 Error: foreign key mismatch

 Can anyone please tell what I did wrong?
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread inst
 Make it your rule of thumb: don't ever use rowid, declare your own
 column as integer primary key and use it. It will come at no cost
 for you and everything else will work much better.

I just wanted to write less text in my queries.
I can't understand why is it a bad practice to use database-provided features?

I think this is a bug. It should be fixed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Possible database corruption bug in SQLite

2010-08-23 Thread Richard Hipp
A user has provided us with a script that appears to result in SQLite
database corruption.  The problem has existed in all versions of SQLite
going back to 3.6.16 in June of 2009.  A bisect shows that the problem was
injected on 2009-06-17.

The problem appears to be associated with incremental vacuum.  We are still
working to characterize the problem more precisely.

The discovery of this bug so close to the release of version 3.7.1 is a
coincidence.  Nevertheless, we will likely be issuing version 3.7.2 within a
few days in order to fix the problem.  So if you are thinking of upgrading
to 3.7.1, you might want to hold back for a day or two to see what our
investigation of this new problem turns up.

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


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread Pavel Ivanov
 I can't understand why is it a bad practice to use database-provided features?

You can use it when you are selecting. And even in this case you
should use caution because without explicit column declared by you
SQLite can change rowids without notice. And for foreign keys it's
mandatory to reference to real columns.

 I think this is a bug. It should be fixed.

Nope, it's not a bug.

 I just wanted to write less text in my queries.

Where does explicitly declared column containing rowid introduce more
text to your queries? Could you give us examples? I bet in all of them
either you don't understand something or you use non-reliable
techniques which should be ditched at some point anyway if you want to
write easily readable and supportable application.


Pavel

On Mon, Aug 23, 2010 at 9:50 AM, inst i...@mail.ua wrote:
 Make it your rule of thumb: don't ever use rowid, declare your own
 column as integer primary key and use it. It will come at no cost
 for you and everything else will work much better.

 I just wanted to write less text in my queries.
 I can't understand why is it a bad practice to use database-provided features?

 I think this is a bug. It should be fixed.
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] rowid column as a FOREIGN KEY

2010-08-23 Thread Oliver Peters
Pavel Ivanov paiva...@... writes:

 
  I can't understand why is it a bad practice to use database-provided
features?
 
 You can use it when you are selecting. And even in this case you
 should use caution because without explicit column declared by you
 SQLite can change rowids without notice. And for foreign keys it's
 mandatory to reference to real columns.
 
  I think this is a bug. It should be fixed.
 
 Nope, it's not a bug.
 

[...]

Maybe it isn't a bug but the documentation should leave as little possibilities
as feasible for interpretation.

This seems to be a problem of wiggle room - it's the same if we talk about self
reference in connection with foreign keys
(http://thread.gmane.org/gmane.comp.db.sqlite.general/58688). You can't know
that it is only applicable to two different(!) tables only reading the
documentation.

Oliver

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


[sqlite] database writes by multiple processes (on a very old computer)

2010-08-23 Thread Chad Rebuck
I have a php script that goes though a 60,000 row table (currently a 100mb
sqlite 3 format database that could grow to 200mb) and looks for rows that
are missing data.  Then the script retrieves data (whois queries and
traceroute results) and inserts it into the table.  I want to run
many occurrences of this script to populate the database quicker, but I am
getting into trouble with the database being locked constantly if I try to
run only two instances of the script.

The faq indicates concurrency really isn't necessary for most situations
with the overall speeds of today's computers.  However, my server is a 12
year old ibm pc running linux with 64m of memory.   The harddisk speed is
very slow.  Is this going to prohibit running more than one process that
writes to a single database table?

I am new to databases so perhaps there is something very basic I am
overlooking here.  Wanted to give sqlite a real effort before trying mysql.

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


Re: [sqlite] EXTERNAL: Possible database corruption bug in SQLite

2010-08-23 Thread Black, Michael (IS)
Since I love to debug code and I needed a distraction
 
Problem was introduced on 2009-06-05 update
 
Several SQLITE_CORRUPT_BKPT were inserted (so this corruption may have been 
going for a while).
 
And here's where the corruption is returned:
 
iPage = get4byte(aData[8+closest*4]);
printf(iPage=%p aData=%p\n,iPage,aData);
if( iPagemxPage ){
  printf(X7 iPage=%p, closest=%d ,iPage,closest);
  rc = SQLITE_CORRUPT_BKPT;
  goto end_allocate_page;
}

./sqlite3 test.db log.txt
delete
X7 iPage=0x120, closest=0 SQL error near line 297: database disk image is 
malformed
X7 iPage=0x120, closest=0 SQL error near line 301: database disk image is 
malformed
X7 iPage=0x120, closest=0 SQL error near line 303: database disk image is 
malformed
X7 iPage=0x2ba01e1, closest=0 SQL error near line 305: database disk image is 
malformed

With the added printout of iPage it suddenly changes values
 
iPage=0x2136 aData=0x5a04458
iPage=0x1c9c aData=0x5989ac8
iPage=0x1f6e aData=0x5c1c5e8
iPage=0x2136 aData=0x5a04458
iPage=0x120 aData=0x5a80508
X7 iPage=0x120, closest=0 SQL error near line 297: database disk image is 
malformed

The aData where it bombs here is the first time this address has shown up.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Richard Hipp
Sent: Mon 8/23/2010 8:57 AM
To: General Discussion of SQLite Database; sqlite-annou...@sqlite.org
Subject: EXTERNAL:[sqlite] Possible database corruption bug in SQLite



A user has provided us with a script that appears to result in SQLite
database corruption.  The problem has existed in all versions of SQLite
going back to 3.6.16 in June of 2009.  A bisect shows that the problem was
injected on 2009-06-17.

The problem appears to be associated with incremental vacuum.  We are still
working to characterize the problem more precisely.

The discovery of this bug so close to the release of version 3.7.1 is a
coincidence.  Nevertheless, we will likely be issuing version 3.7.2 within a
few days in order to fix the problem.  So if you are thinking of upgrading
to 3.7.1, you might want to hold back for a day or two to see what our
investigation of this new problem turns up.

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


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


Re: [sqlite] database writes by multiple processes (on a very old computer)

2010-08-23 Thread Simon Slavin

On 23 Aug 2010, at 3:51pm, Chad Rebuck wrote:

 I have a php script that goes though a 60,000 row table (currently a 100mb
 sqlite 3 format database that could grow to 200mb) and looks for rows that
 are missing data.  Then the script retrieves data (whois queries and
 traceroute results) and inserts it into the table.  I want to run
 many occurrences of this script to populate the database quicker, but I am
 getting into trouble with the database being locked constantly if I try to
 run only two instances of the script.

Thanks for that description which saves a lot of tedious QA before we can 
figure out what you want.

 The faq indicates concurrency really isn't necessary for most situations
 with the overall speeds of today's computers.  However, my server is a 12
 year old ibm pc running linux with 64m of memory.   The harddisk speed is
 very slow.  Is this going to prohibit running more than one process that
 writes to a single database table?

It depends on which part of the single-process version is the bottleneck.  It 
may be that the slowest element of your task is writing to the database file.  
If that's the case then splitting the job into several different occurrences 
will make no difference, since they all have to write to the same database file 
in the end anyway, and only one process can do that at once.  A slow hard disk 
suggests that this is the case: intensive disk activity like writing to a SQL 
database is probably occupying most of your application's time.

If the task of finding the missing data was taking the majority of the time you 
might get some advantage from using multiple processes: one might operate while 
another was waiting for network activity, for example.

Given the limitations of your hardware you might consider a different way of 
splitting up your application: have one task that finds all entries with 
missing data.  Then have many (parallel ?) processes find that information but 
write it to a long text file.  Then finally have another process that reads the 
textfile and makes changes to the database all in one giant transaction.  That 
might speed things up.

 I am new to databases so perhaps there is something very basic I am
 overlooking here.  Wanted to give sqlite a real effort before trying mysql.

A slow hard disk will cause the same problem here.  In fact, since MySQL is 
larger and more complicated than SQLite, it does more work when writing the 
file to disk, so you may get even worse results with it.  On the other hand 
MySQL makes heavy use of caching, so if you have enough memory free it needs to 
do less disk activity.

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


Re: [sqlite] database writes by multiple processes (on a very old computer)

2010-08-23 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2010 08:21 PM, Chad Rebuck wrote:
 I am new to databases so perhaps there is something very basic I am
 overlooking here.

You may inadvertently be causing the busy errors yourself.  Have you
read this:

  http://www.sqlite.org/lockingv3.html

A simple example is if you have a select query in process 1 from which
you read one result at a time, process and continue with the query:

   for row in (select * from db):
  ... do work with row ...

This will block writes in the second process since the select is always
live.  Workarounds include using WAL mode, gathering all results from a
select before processing them, and using one thread/process to do the
database work dispatching work items to child threads/processes.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxy3lIACgkQmOOfHg372QTTyACgx4qhZSQ5bMjREOYuZBlOG6fM
UbsAnRZDEYZMRcrKUrDiHpPRKs4mhx10
=Af1h
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] database writes by multiple processes (on a very old computer)

2010-08-23 Thread Chad Rebuck
Simon, Roger,

Thanks for the replies.

Simon, I took some of your advice and redesigned my script to use smaller
database files at least for my testing.  However, I still run into this
message after starting the second process and my two processes get stuck so
no inserts/deletes occur after that moment.  I'll also google wal as you
suggest.

PHP Warning:  SQLite3::exec(): database is locked in
...populate-traceroute2.php on line 53.  Line 53 below contains
$smallDb-exec($query2).

Roger, I read the page you provided, but I think I need to read some more
before fully understanding what I've done wrong.  I didn't mention it until
now, but with my sqlite 2 database and an older version of php I didn't see
this issue last week.  I was working with smaller files then but now even
with small files I have the locking issue.

I would appreciate if anyone can guide me a little more on how to correct
this.  Here are the relevant code snippets.  There are probably more
efficient ways to handle this but I am fairly new to php programming and
only sql, so excuse the mess :)


function getTraceroute($smallDb) {


while($Results = $smallDb-query('SELECT * FROM tworklist limit 1'))
{
$entry = $Results-fetchArray(SQLITE3_ASSOC);
if (!($entry)) {  break; }

$query2 = delete from tworklist where rowid=$rowid;
echo result of delete =  . $smallDb-exec($query2) . \n;

$query2 = insert into tresults (rowid,ip,traceroute) values
('$rowid', '$ip','$new');
echo result of update =  . $smallDb-exec($query2) . \n;
}
}


On Mon, Aug 23, 2010 at 4:47 PM, Roger Binns rog...@rogerbinns.com wrote:

 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 On 08/23/2010 08:21 PM, Chad Rebuck wrote:
  I am new to databases so perhaps there is something very basic I am
  overlooking here.

 You may inadvertently be causing the busy errors yourself.  Have you
 read this:

  http://www.sqlite.org/lockingv3.html

 A simple example is if you have a select query in process 1 from which
 you read one result at a time, process and continue with the query:

   for row in (select * from db):
  ... do work with row ...

 This will block writes in the second process since the select is always
 live.  Workarounds include using WAL mode, gathering all results from a
 select before processing them, and using one thread/process to do the
 database work dispatching work items to child threads/processes.

 Roger
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.10 (GNU/Linux)
 Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

 iEYEARECAAYFAkxy3lIACgkQmOOfHg372QTTyACgx4qhZSQ5bMjREOYuZBlOG6fM
 UbsAnRZDEYZMRcrKUrDiHpPRKs4mhx10
 =Af1h
 -END PGP SIGNATURE-
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


[sqlite] SQLite version 3.7.2

2010-08-23 Thread Richard Hipp
SQLite version 3.7.2 is now available on the SQLite website:
http://www.sqlite.org/

SQLite version 3.7.2 fixes a single bug that was discovered just hours after
the release of 3.7.1.  The bug can result in corruption of the database
free-list after an incremental vacuum.  The bug had nothing whatsoever to do
with SQLite version 3.7.1 or any other recent release.  The problem had been
in the code for over a year, since version 3.6.16.  The discovery of the
problem so soon after the release of version 3.7.1 was purely coincidental.

The bug fixed in 3.7.2 can result in database corruption.  However, the
corruption caused by this bug can almost always be fixed simply by running
VACUUM on the database.  And the corruption will only occur in an
incrementally vacuumed database which at some point in time contains
hundreds of unused pages which are slowly released back to the operating
system by multiple calls to the incremental_vacuum PRAGMA.  Even then, one
must be particularly unlucky to hit the right combination of freed pages in
order to trigger the bug.  Hence the problem is quite obscure and was not
noticed for over a year.

Hundreds of lines of code where changed for version 3.7.2, but most of those
changes were to test procedures.  As is the custom with SQLite, not only was
the specific bug fixed, but new tests where put in place to detect and
prevent similar kinds of bugs elsewhere in the code.  We believe that one
should not just fix the bug, but also fix the process that generated the
bug. The only 4 working lines of code were changed for version 3.7.2:

 http://www.sqlite.org/src/fdiff?v1=2dff4076d3c994dcv2=5047fb303cdf6806

Special thanks to Filip Navara for finding and reporting the problem with
incremental vacuum.

Please report any other problems to the sqlite-users@sqlite.org mailing
list, or directly to me.  Thanks.

-- 
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] Precompiled binary for sqlite3_analyzer requires libicui18n.so.44?

2010-08-23 Thread Bob Chapman
The sqlite3_analyzer precompiled binary for linux as found
in sqlite3_analyzer-3.7.1-linux.zip appears to require
libicui18n.so.44. Ubuntu 8.04 LTS has libicui18n.so.38.0 while
Centos 4.8 is way back at libicui18n.so.22.0.  I can run the
analyzer on Windows but would rather not have to transfer stuff
back and forth.

So far, I have not been astute enough to figure out how to build
my own sqlite3_analyzer binary. The amalgamation does not
appear to have the required source and the Makefile does not
have a suitable target. I have made several (pseudo-random ;)
walks through the Fossil repository but have not been smart
enough to locate a build your own sqlite3_analyzer package
for dummies. ;)

And, considering the possibility that building the analyzer
really does require ICU 4.4.x, I've downloaded and (I think ;)
successfully built ICU Release 4.4.1 on Ubuntu 8.04 but I
am reluctant to install it and, perhaps, do the same on the
Centos 4.8 platform if there is an easier simpler way.

Thanks in advance for any assistance and my apologies if my
ignorance has created noise on the list.

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


Re: [sqlite] Precompiled binary for sqlite3_analyzer requires libicui18n.so.44?

2010-08-23 Thread Richard Hipp
There is now a new build of sqlite3_analyzer for linux up that should not
require libicu18n.  Let me know if the new version fails to work for you.

To build sqlite3_analyzer, you have to manually edit Makefile.linux-gcc for
your system.  It is not hard, but nor is it particularly obvious.

On Mon, Aug 23, 2010 at 10:32 PM, Bob Chapman chapman...@gmail.com wrote:

 The sqlite3_analyzer precompiled binary for linux as found
 in sqlite3_analyzer-3.7.1-linux.zip appears to require
 libicui18n.so.44. Ubuntu 8.04 LTS has libicui18n.so.38.0 while
 Centos 4.8 is way back at libicui18n.so.22.0.  I can run the
 analyzer on Windows but would rather not have to transfer stuff
 back and forth.

 So far, I have not been astute enough to figure out how to build
 my own sqlite3_analyzer binary. The amalgamation does not
 appear to have the required source and the Makefile does not
 have a suitable target. I have made several (pseudo-random ;)
 walks through the Fossil repository but have not been smart
 enough to locate a build your own sqlite3_analyzer package
 for dummies. ;)

 And, considering the possibility that building the analyzer
 really does require ICU 4.4.x, I've downloaded and (I think ;)
 successfully built ICU Release 4.4.1 on Ubuntu 8.04 but I
 am reluctant to install it and, perhaps, do the same on the
 Centos 4.8 platform if there is an easier simpler way.

 Thanks in advance for any assistance and my apologies if my
 ignorance has created noise on the list.

 --
 ___
 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