[sqlite] SQLite 3.7.8 version - insert query speed decreased

Dear all, I'm using sqlite 3.7.6.3 version on my system. And, I'd tried to use 3.7.8 version, because of retrieval query speed of distinct and group by has been increased. Really, it shows improved performance for several scenes in my application. However, insert queries of 1,000 entries to

Re: [sqlite] Still chasing "database schema has changed"

On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater wrote: > On 27 Sep 2011 at 18:15, Richard Hipp wrote: > > > On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater > wrote: > > > >> The databases that get vacuumed tend to have a fair amount of

Re: [sqlite] Still chasing "database schema has changed"

On 27 Sep 2011 at 18:15, Richard Hipp wrote: > On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater wrote: > >> The databases that get vacuumed tend to have a fair amount of traffic in >> and out. So it's good to compress them from time to time. > > Really?

[sqlite] speeding up FTS4

I have CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT, uri_content TEXT, downloaded_on DATETIME DEFAULT CURRENT_TIMESTAMP, feed_history_id INTEGER ); with 46608 rows CREATE TABLE feed_history ( feed_history_id INTEGER

Re: [sqlite] Foreign key mismatch Error

On Tue, 27 Sep 2011 14:58:06 +0400, Madhan Kumar wrote: >Hello.. > In sqlite (version 3.7.7.1), I created two tables with foreign keys >as shown below > >CREATE TABLE [*Employee*] ( > [*EmpId*] INT NOT NULL, > [EmpName] VARCHAR2(50)); > >CREATE TABLE [*Department*] (

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 23:11, Scott Hess wrote: > On Tue, Sep 27, 2011 at 2:07 PM, Patrick Proniewski wrote: > >> On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: >>> gawk has fflush() >> >> none of them is available out of the box on Mac OS X, or FreeBSD. gawk can >> be

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 20:44, Paul Linehan wrote: > 2011/9/27 Patrick Proniewski : > >>> Take a look at a utility called dstat. > >> no, it's linux only. > > But it is written in Python - so it should be relatively > transportable. and it relies on /proc/, Mac OS X does not

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 20:41, Paul Linehan wrote: > 2011/9/27 Patrick Proniewski : > >> That's what I do, but I think using a loop is ugly, and I would like to find >> a way >> to feed data continuously into sqlite. > > I can't see why you would want to do this more than once

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 2:07 PM, Patrick Proniewski wrote: > On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: > > gawk has fflush() > > none of them is available out of the box on Mac OS X, or FreeBSD. gawk can > be installed, but I'd rather use my "while true" loop

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 20:18, Gabor Grothendieck wrote: > gawk has fflush() On 27 sept. 2011, at 20:29, Roger Andersson wrote: > stdbuf? > unbuffer? none of them is available out of the box on Mac OS X, or FreeBSD. gawk can be installed, but I'd rather use my "while true" loop instead of

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : >> Take a look at a utility called dstat. > no, it's linux only. But it is written in Python - so it should be relatively transportable. I've even managed to modify the code myself - and if I can do it, anybody can! 8-) Paul... > patpro

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

Patrick Proniewski writes: > On 27 sept. 2011, at 20:14, David Garfield wrote: > > > Any entry in a pipe could be buffering. In a quick test here, awk is > > buffering. To find the buffering, try using the pieces up to a given > > stage with " | cat " added at the end. If this buffers, you've

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : > That's what I do, but I think using a loop is ugly, and I would like to find > a way > to feed data continuously into sqlite. I can't see why you would want to do this more than once every minute - or do you? If not, ==

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : > That's what I do, but I think using a loop is ugly, and I would like to find a > way to feed data continuously into sqlite. cron Paul... > patpro -- Hmmm a "life": wonder where I can download one of those? lineh...@tcd.ie Mob:

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 11:18 AM, Gabor Grothendieck < ggrothendi...@gmail.com> wrote: > On Tue, Sep 27, 2011 at 2:14 PM, David Garfield > wrote: > > Any entry in a pipe could be buffering. In a quick test here, awk is > > buffering. To find the buffering, try

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 09/27/11 20:14, David Garfield wrote: Any entry in a pipe could be buffering. In a quick test here, awk is buffering. To find the buffering, try using the pieces up to a given stage with " | cat " added at the end. If this buffers, you've found the problem. Unbuffered output is usually

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 20:14, David Garfield wrote: > Any entry in a pipe could be buffering. In a quick test here, awk is > buffering. To find the buffering, try using the pieces up to a given > stage with " | cat " added at the end. If this buffers, you've found > the problem. as far as my

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 2:14 PM, David Garfield wrote: > Any entry in a pipe could be buffering.  In a quick test here, awk is > buffering.  To find the buffering, try using the pieces up to a given > stage with " | cat " added at the end.  If this buffers, you've

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 20:04, Paul Linehan wrote: > 2011/9/27 Patrick Proniewski : > > >> I'm facing a challenging problem. I want to log some data into an SQLite3 DB. >> Data come from a system command (iostat) in an endless steam, one row every >> X seconds: > > > Take a

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

Any entry in a pipe could be buffering. In a quick test here, awk is buffering. To find the buffering, try using the pieces up to a given stage with " | cat " added at the end. If this buffers, you've found the problem. Unbuffered output is usually slower, so it is normally done only to a

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : > I'm facing a challenging problem. I want to log some data into an SQLite3 DB. > Data come from a system command (iostat) in an endless steam, one row every X > seconds: Take a look at a utility called dstat. I've twiddled with the source and

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 18:31, Roger Andersson wrote: > I do not know if tee makes any difference or if it's available on Mac? > http://unixhelp.ed.ac.uk/CGI/man-cgi?tee tee is available, but no more luck here, as it won't allow to disable the buffer. > iostat -d -w 10 disk0 | tee -a logfile >

Re: [sqlite] Still chasing "database schema has changed"

On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater wrote: > > The databases that get vacuumed tend to have a fair amount of traffic in > and out. So it's good to compress them from time to time. > Really? Have you actually measured this to see if it makes a difference? What

Re: [sqlite] Still chasing "database schema has changed"

On 27 Sep 2011 at 12:23, Simon Slavin wrote: > On 27 Sep 2011, at 10:49am, Tim Streater wrote: > >> On 27 Sep 2011 at 00:19, Simon Slavin wrote: >> >>> I believe that VACUUM is one of the statements which counts as changing the >>> schema, because it

Re: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues -anysuggestions?!

Hello again, Just to let you know that I am running the same code on the same OS but with OpenJDK and my issue has now magically disappeared! Confirming Christian's suspicions of the JVM being the root of the issue I think.. Best Regards, Katie, -Original Message- From: Katie Blake

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 09/27/11 07:48, Patrick Proniewski wrote: I though I could easily pipe data into SQLite: iostat -d -w 10 disk0 |\ awk '!/[a-zA-Z]/ {print "INSERT INTO io VALUES(datetime(\"now\",\"localtime\"),"$1","$2","$3");"}' |\ sqlite3 iostat.db but it won't work, because sqlite3 won't record any

[sqlite] 答复: 答复: Is SQLite supporting x64?

Well, I'll check it out then. Thank you, Richard. -邮件原件- 发件人: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-bounces@sqlite. org] 代表 Richard Hipp 发送时间: 2011年9月26日 0:47 收件人: General Discussion of SQLite Database 主题: Re: [sqlite] 答复: Is SQLite supporting x64? 2011/9/25 mmxida

[sqlite] 答复: 答复: Is SQLite supporting x64?

Well, I'll check it out then. Thank you, Richard. -邮件原件- 发件人: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-bounces@sqlite. org] 代表 Richard Hipp 发送时间: 2011年9月26日 0:47 收件人: General Discussion of SQLite Database 主题: Re: [sqlite] 答复: Is SQLite supporting x64? 2011/9/25 mmxida

[sqlite] 答复: 答复: Is SQLite supporting x64?

Thanks for your information, Roger, it helps a lot. -邮件原件- 发件人: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-bounces@sqlite. org] 代表 Roger Binns 发送时间: 2011年9月26日 4:26 收件人: sqlite-users@sqlite.org 主题: Re: [sqlite] 答复: Is SQLite supporting x64? -BEGIN PGP SIGNED MESSAGE-

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

I love C myself...this does what you want I think. Only has 3 processes ever run. And since you're not worried about SQL errors apparently no need to use the sqlite library. A couple of changes to match your iostat output is all that's needed. I assume you know C (a rather large assumption I

Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)

On Tue, Sep 27, 2011 at 8:14 AM, Black, Michael (IS) wrote: > > Richard almost got it. According to this link JFFS doesn't support > MMAP_SHARED. Can another flag be used? > No. The whole point of doing the mmap() is so that different processes accessing the same

Re: [sqlite] Problem with using WAL journal mode in embedded system (disk I/O error)

Richard almost got it. According to this link JFFS doesn't support MMAP_SHARED. Can another flag be used? http://lists.busybox.net/pipermail/uclibc/2006-November/016838.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 Sep 2011, at 1:07pm, Patrick Proniewski wrote: > On 27 sept. 2011, at 13:44, Simon Slavin wrote: > >> If you're using the OS X version, I don't think you need to run iostat as a >> continuous process. Write a shell script with a timed loop which runs >> iostat without the '-w 10'. So

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 13:44, Simon Slavin wrote: > On 27 Sep 2011, at 12:03pm, Patrick Proniewski wrote: > >> You're assuming I'm running Linux, but I'm running Mac OS X Server (or >> FreeBSD by the way), so no /proc here, and iostat is probably working >> differently too. >>

Re: [sqlite] undefined symbol/reference: sqlite3_version. Please!

On Sun, Sep 25, 2011 at 7:04 PM, JFC Morfin wrote: > At 02:44 26/09/2011, Simon Slavin wrote: > > On 26 Sep 2011, at 1:35am, JFC Morfin wrote: >> >> > I have copied the C programe in >> > http://sqlite.org/quickstart.**htmland >> > tried

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 Sep 2011, at 12:03pm, Patrick Proniewski wrote: > You're assuming I'm running Linux, but I'm running Mac OS X Server (or > FreeBSD by the way), so no /proc here, and iostat is probably working > differently too. >

Re: [sqlite] Foreign key mismatch Error

On 27 Sep 2011, at 11:58am, Madhan Kumar wrote: > In sqlite (version 3.7.7.1), I created two tables with foreign keys > as shown below > > CREATE TABLE [*Employee*] ( > [*EmpId*] INT NOT NULL, > [EmpName] VARCHAR2(50)); > > CREATE TABLE [*Department*] ( > [DeptID] INTEGER NOT NULL, >

Re: [sqlite] Still chasing "database schema has changed"

On 27 Sep 2011, at 10:49am, Tim Streater wrote: > On 27 Sep 2011 at 00:19, Simon Slavin wrote: > >> I believe that VACUUM is one of the statements which counts as changing the >> schema, because it does its work by rewriting entire tables and/or indexes. >> So don't do

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 08:31, Baptiste Daroussin wrote: > You don't need awk :) > > iostat -d -w 10 disk0 | while read a b c; do case $a in *[a-zA-Z]*) > continue ;; *) sqlite3 iostat.db "INSERT INTO io > VALUES(datetime('now', 'localtime'), \"$a\", \"$b\", \"$c\");" ;; > esac; done Ok, this

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 08:02, Stephan Beal wrote: > That's a tricky one, it seems. If you're not restricted to shell code, you > could possibly do this using perl, PHP, or similar. You could open a pipe > for iostat, read a line from the pipe, and feed that line to your db (not in > the form of a

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 12:58, Simon Slavin wrote: > On 27 Sep 2011, at 6:48am, Patrick Proniewski wrote: > >> I've tried various solutions with named pipes, file descriptors >> redirections… but none worked, because they all seem to require the data >> steam to end before feeding data into the

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 Sep 2011, at 6:48am, Patrick Proniewski wrote: > I've tried various solutions with named pipes, file descriptors redirections… > but none worked, because they all seem to require the data steam to end > before feeding data into the DB. Most of your problems are caused because you're

[sqlite] Foreign key mismatch Error

Hello.. In sqlite (version 3.7.7.1), I created two tables with foreign keys as shown below CREATE TABLE [*Employee*] ( [*EmpId*] INT NOT NULL, [EmpName] VARCHAR2(50)); CREATE TABLE [*Department*] ( [DeptID] INTEGER NOT NULL, [DeptName] VARCHAR2(20), [*EmpID*] INTEGER NOT NULL

Re: [sqlite] Still chasing "database schema has changed"

On 27 Sep 2011 at 00:19, Simon Slavin wrote: > On 26 Sep 2011, at 11:32pm, Tim Streater wrote: >> But, very often the database on which the error occurs is :memory: - and I >> guess each thread will have its own one of those. > > Are you doing concurrent access to your

Re: [sqlite] Sqlite shell's .import does not work with attached db's, add wiki note?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/26/2011 06:03 PM, yary wrote: > I noticed that the sqlite shell won't ".import" into an attached database: The cause of the error message is a test in the shell code. It prepares this: SELECT * from 'tablename' I have no idea why it puts

Re: [sqlite] Still chasing "database schema has changed"

On 27 Sep 2011 at 00:19, Simon Slavin wrote: > On 26 Sep 2011, at 11:32pm, Tim Streater wrote: > >> For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT. >> Less often, there's VACUUM. Let me have a look at my logs - OK, when I got >> the error today,

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On 27 sept. 2011, at 08:21, Roger Binns wrote: > The easiest solution is to just be patient and accept the data will be a > little delayed. that won't work for me, because my SQL command includes a datetime('now'). Any row input that is delayed won't be recorded with the proper datetime. That's

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

2011/9/27 Patrick Proniewski : > Hello, > > I'm facing a challenging problem. I want to log some data into an SQLite3 DB. > Data come from a system command (iostat) in an endless steam, one row every X > seconds: > >         disk0 >   KB/t tps  MB/s >   4.02 2318 9.09 >   4.00

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/26/2011 10:48 PM, Patrick Proniewski wrote: > but it won't work, because sqlite3 won't record any data until the iostat > command ends. UNIX tools using the standard I/O library will show this. They detect that standard output is not a

Re: [sqlite] feed "endless" data into sqlite, thru a shell script

On Tue, Sep 27, 2011 at 7:48 AM, Patrick Proniewski wrote: > while true; do > ... endless loop, forking iostat for 2 rows of data (-c 2), keeping only the > last row because the first one is an artifact (tail -1). > That's a tricky one, it seems. If you're not restricted to