Re: [sqlite] SQLJet - pure Java implementation of SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Fred Williams wrote:
> I say there is no known translation that
> would allow the three SQLite, "Small, Fast, Reliable" adjectives to
> translate into any regurgitated language output, with the exception of
> compiling SQLite source with a C++ compiler :-)

If you read Bernstein's retrospective on qmail, one of his suggestions is to
write code in a higher level language (especially more secure in the sense
that C isn't such as preventing buffer & integer overflows etc) that is then
transcoded to C.  (Some conspiracy theorists looking at his code claimed he
actually did do this :-)

So in theory given unlimited CPU and memory it should be possible to take
the SQLite C code and turn it into something higher level and then turn that
back into the more verbose platforms (.NET, Java).  The big advantage of
this approach (after the upfront work) is that keeping up with SQLite
progress is easy.

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

iEYEARECAAYFAkqCTmwACgkQmOOfHg372QSCUwCeKD+V+e7yjYgbcF9e+lXhAbbE
U9cAoIzXPblugsswnhbgnmTNYSrkdMj7
=VYQ3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLJet - pure Java implementation of SQLite

[Fred Williams]

Having had the unfortunate opportunity to use a couple of language
translators as well as spending about six fruitless months developing one
which in the end was no better, I say there is no known translation that
would allow the three SQLite, "Small, Fast, Reliable" adjectives to
translate into any regurgitated language output, with the exception of
compiling SQLite source with a C++ compiler :-)

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Roger Binns
Sent: Tuesday, August 11, 2009 7:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLJet - pure Java implementation of SQLite


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alexander Kitaev wrote:
> Not to depend on native SQLite binaries or
> opaque NestedVM code,

As a matter of interest what problem exactly do you have with NestedVM?
It's output is indeed opaque (not human comprehensible) but the same is true
of Java source versus bytecode.  In both cases the input source is readable.

It would also be interesting if anyone has built something that comprehends
the SQLite C source and then does the conversion into other languages based
on that.  It would make updates a lot easier, the generation of instrumented
and test code easier, and the search for issues or optimisations easier.

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

iEYEARECAAYFAkqCCWgACgkQmOOfHg372QQXqwCeJ4pqKa89vcCAxTQOelMyoPU6
cuQAoK6Feey6AL3pdzMgv983tn8Yg1ML
=TKoq
-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

Re: [sqlite] UPDATE 11 times slower than SELECT?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nikolaus Rath wrote:
> Is it normal that UPDATE still takes more than 11 times as much time as
> SELECT, or should I be able to get better performance?

The UPDATE time also includes the time waiting to acquire file locks.  If
you have concurrent activity it could take a while.  If you want to break
out the lock time then restructure your sql to be:

   BEGIN IMMEDIATE; UPDATE ... ; COMMIT

The BEGIN will then show up in apswtrace separately as the transaction wait
time.  Also note that these times are wall clock times so heavy threading
could cause a lot of variance.

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

iEYEARECAAYFAkqCOIAACgkQmOOfHg372QSWYgCfVRDkPdWcjJooIrUjsIM2iLLw
bWQAnjC2SDgdtE87DcMbjXQjRN2F38fU
=lLYM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE 11 times slower than SELECT?

[Nikolaus Rath]

"Igor Tandetnik"  writes:
> Nikolaus Rath wrote:
>> When running apswtrace on my Python program, I got the following
>> output:
>>
>> LONGEST RUNNING - AGGREGATE
>>
>> 16638  11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=?
>> WHERE id=? 16638   0.938 SELECT s3key FROM inode_s3key WHERE inode=?
>> AND offset=?
>>
>> i.e. both statements were executed 16638 times, but execution of the
>> first took 11 seconds in total and execution of the second only 0.9
>> seconds.
>
> Select likely gets data from in-memory cache. Update has to physically 
> write to disk. Memory is much faster than disk.

Why does it have write to the disk? With PRAGMA synchronous = off,
SQLite should return as soon as the data is in the kernel cache,
shouldn't it?

> If at all possible, group write operations together into a transaction.

Not possible here, unfortunately. The queries do not run sequentially
but are triggered by external events.


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] UPDATE 11 times slower than SELECT?

[Igor Tandetnik]

Nikolaus Rath wrote:
> When running apswtrace on my Python program, I got the following
> output:
>
> LONGEST RUNNING - AGGREGATE
>
> 16638  11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=?
> WHERE id=? 16638   0.938 SELECT s3key FROM inode_s3key WHERE inode=?
> AND offset=?
>
> i.e. both statements were executed 16638 times, but execution of the
> first took 11 seconds in total and execution of the second only 0.9
> seconds.

Select likely gets data from in-memory cache. Update has to physically 
write to disk. Memory is much faster than disk.

If at all possible, group write operations together into a transaction.

Igor Tandetnik 



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

[sqlite] UPDATE 11 times slower than SELECT?

[Nikolaus Rath]

Hi,

When running apswtrace on my Python program, I got the following output:

LONGEST RUNNING - AGGREGATE

 16638  11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? WHERE id=?
 16638   0.938 SELECT s3key FROM inode_s3key WHERE inode=? AND offset=?

i.e. both statements were executed 16638 times, but execution of the
first took 11 seconds in total and execution of the second only 0.9
seconds.

I was slightly surprised by this. The UPDATE statement just writes one
integer and 2 reals. There is an index on the id column, and I have
activated PRAGMA temp_store = 2 and PRAGMA synchronous = off.

Is it normal that UPDATE still takes more than 11 times as much time as
SELECT, or should I be able to get better performance?


Best,

   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

Re: [sqlite] SQLJet - pure Java implementation of SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alexander Kitaev wrote:
> Not to depend on native SQLite binaries or
> opaque NestedVM code, 

As a matter of interest what problem exactly do you have with NestedVM?
It's output is indeed opaque (not human comprehensible) but the same is true
of Java source versus bytecode.  In both cases the input source is readable.

It would also be interesting if anyone has built something that comprehends
the SQLite C source and then does the conversion into other languages based
on that.  It would make updates a lot easier, the generation of instrumented
and test code easier, and the search for issues or optimisations easier.

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

iEYEARECAAYFAkqCCWgACgkQmOOfHg372QQXqwCeJ4pqKa89vcCAxTQOelMyoPU6
cuQAoK6Feey6AL3pdzMgv983tn8Yg1ML
=TKoq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Busy Handler in Shared Cache Mode?

[Nikolaus Rath]

Hi,

There has been a bit more discussion about this on
http://code.google.com/p/apsw/issues/detail?id=59. I guesss Roger would
be quite interested in the test case you mentioned. I have now decided
to simply deactivate shared cache again. It seems the benefits aren't
worth it in my case.

As a side note: I think that my own program may actually suffer from
problems with the Python GIL rather than SQLite locking. I discovered
then when I ran in single threaded mode, the total execution time
reduces by a factor of about 2.3...

Best,
Nikolaus


"Edzard Pasma"  writes:
> Hi Nikolaus,
>
> Interesting to read your findings. I assume you finally disallowed
> read_uncommitted. Trying to explain why the wait times can get longer,
> I come to two reasons.
>
> - the time slots where a read-lock is obtained become smaller. 
> This can be significant if there are a lot of transactions with little
> time in between. With the busy handling by polling, a reader may mis
> slots. This will sure be improved by Unlock Notification.
>
> - the time slots where a read-lock can not be obtained become longer.
> This can cause problems if there are long-running transaction. The
> Unlock Notification feature is not going to help here.
>
> I still like to share an observation from my own test, which
> reproduces the first case. I start four reading threads and one
> writer. Each thread performs a fixed number of cycles. Apart from the
> wait times, also the overall elapsed time is measured. Indeed the
> maximum wait-time gets bigger if shared cache mode is enabled.
> Interestingly, this does not apply to the elapsed time, which is still
> reduced. Thus, an increase of the maximum wait-time is not necessarily
> a worse performance. By the way, this was measured on a
> single-processor system.
>
> Hope this is useful to know.
>
> Best regards, Edzard.
>
> On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus
> Rath wrote:
>> Hello,
>>
>> I have program that continuously runs several threads (about 4 to
>> 10)
>> which concurrently read and write to an SQLite database. From
>> http://www.sqlite.org/sharedcache.html it seems to me that I should
>> be
>> using shared cache mode.
>>
>> Until now, I have set the busy timeout to 500 msec and never had to
> deal
>> with SQLITE_BUSY errors. However, there seems to be no corresponding
>> function for the SQLITE_LOCKED errors that are generated in shared 
> cache
>> mode. So I changed the code manually sleep for a random amount (0 to
> 100
>> msec) and then try to execute the statement again if it encounters
>> SQLITE_LOCKED. But now the threads are often waiting for more than 1
>> second before they finally get their query executed.
>>
>> I suspect this is because the random sleep is wasting a lot of time,
>> while without shared cache (and with enabled busy timeout) a thread
>> blocks and gets revived as soon as the lock on the database is
>> released.
>>
>>
>> How can I avoid this problem? Is there a way to set a busy timeout
>> for
>> SQLITE_LOCKED as well? Or a more clever method instead of sleeping
>> for
>> random amounts? Or should I just avoid using shared cache mode?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


   -Nikolaus

-- 
 »Time flies like an arrow, fruit flies like a Banana.«

  PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6  02CF A9AD B7F8 AE4E 425C

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

[sqlite] SQLJet - pure Java implementation of SQLite

[Alexander Kitaev]

Hello All,

I'd like to invite those users who program in Java to take a look at our
new project - SQLJet.

SQLJet was started as part of another projects that has to work with
SQLite database (SVNKit). Not to depend on native SQLite binaries or
opaque NestedVM code, we've implemented part of SQLite core
functionality in Java, and later moved that code to a separate project
which we called SQLJet.

SQLJet is an independent open source implementation of the SQLite core
functionality; SQLJet provides API to read and modify SQLite database
schema and data, but not to execute SQL queries yet.

SQLJet project home page is at http://sqljet.com/ - you'll find mailing
list, other information on SQLJet library and, of course, library itself
there.

We appreciate any feedback you may have on SQLJet library and we would
be glad to prioritize further library development accordingly to your
ideas and requests.

Thanks,
--
Alexander Kitaev,
TMate Software,
http://svnkit.com/ - Java [Sub]Versioning Library!
http://sqljet.com/ - Java SQLite Library!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help building SQLite project, please

[Radcon Entec]

This isn't directly related to SQLite, but rather to how I am converting the 
source code into a DLL.  I hope someone here can help me understand what's 
happening.

It appears that my previous problem was caused by some problem within the 
ancient, unbelievably slow and ugly library we have been using to talk to our 
SQLite database.  It appears that that library will not allow any data to be 
written into any previously existing table.  

So, I wanted to write a little demonstration program that would add data to an 
existing table, using nothing but plain ordinary functions from the sqlite 
library.  I created a dialog-based application with a button.  When I click the 
button, a message box appears.  I added a call to sqlite3_open().  When I start 
my application, even before my dialog's constructor or InitiInstance() get 
called, I get an error message: "Application failed to initialize properly" and 
an error code of 0xc07b.  When I click OK, I get another saying "Win32 
error: Path Not Found".

I checked the other place in our code where we rely on SQLite: an ActiveX 
control that displays a graph.  Instead of the graph, I got the blank box with 
a red X.  Microsoft's Dependency Walker tool showed me there was a problem with 
the sqlite3_v3 DLL I was using.  The DLL's icon was red, and the icon for every 
function in the DLL was red.  On our test computer, Dependency Walker shows 
sqlite3_v3 DLL as normal, with green icons.  When I copy the DLL from the test 
computer onto mine, the ActiveX control behaves normally, but I still get the 
0xc07b error with my demo program.  

On the test computer, the bottom pane of Dependency Walker tells me that 
sqlite3_v3's subsystem is "Win32 console" and its base is 0x6090.  But on 
my computer, the tool tells me that sqlite3_v3's subsystem is "Win32 GUI" and 
its base is 0x1000.  That looks strange, because of course there's no GUI 
used in SQLite.  

That looks like a problem, but I don't know what's causing it.  Can anyone 
suggest what setting I might have to change to get back to a console subsystem 
for my library?

Thanks very much!

RobR



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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[Mark Richards]

D. Richard Hipp , On 8/11/2009 16:39:
> program is using LinuxThreads or NPTL for its threading.  (SQLite has
> to know which is used because there are serious bugs in LinuxThreads
> that SQLite has to work around.)  So pthreads gets used once, by
> SQLite, even if you don't do any threading in your application.
> Probably something about that pthread_create() call is messing up the
> exec.
>
> If you app does not use threads, then by all means compile with
> SQLITE_THREADSAFE=0 because that makes SQLite run faster.
>
Would it help to know, where threading is enabled in the library, if 
pthread_create() fails?  Is there a function that can tell me the status?

Or, is there a switch that will force sqlite to use native thread 
library instead?

Fortunately my app isn't forking out SqLite tasks.  It does fork itself 
to run as a daemon, tho.  All my tests were done *before *forking.  I 
tried the same test cases after the fork and got the same results.

/m

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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[D. Richard Hipp]

On Aug 11, 2009, at 4:28 PM, Mark Richards wrote:

> D. Richard Hipp , On 8/11/2009 16:02:
>>
>> Perhaps pthreads is going goofy.  Please recompile with -
>> DSQLITE_THREADSAFE=0 and see if that helps.
>>
> BINGO.
>
> env   CC="gcc-cris -mlinux -isystem $EROOT/include"CPP="gcc-cris
> -mlinux -E -isystem $EROOT/include"   CXX="g++-cris -mlinux -xc++
> -isystem $EROOT/include"   LDFLAGS="-L$EROOT/lib"
> OBJCOPY="objcopy-cris"   LD="ld-cris -mcrislinux"   AR="ar-cris"
> RANLIB="ranlib-cris"   NM="nm-cris"   STRIP="strip-cris"  ./configure
> --host=cris-axis-linux-gnu
> --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu
> --enable-static=yes --enable-shared=yes  --disable-threadsafe -- 
> disable-tcl
>
>
> Works like a charm.
>
> How can I help find the issue with my test case?

On linux, SQLite has to start a thread in order to figure out if the  
program is using LinuxThreads or NPTL for its threading.  (SQLite has  
to know which is used because there are serious bugs in LinuxThreads  
that SQLite has to work around.)  So pthreads gets used once, by  
SQLite, even if you don't do any threading in your application.
Probably something about that pthread_create() call is messing up the  
exec.

If you app does not use threads, then by all means compile with  
SQLITE_THREADSAFE=0 because that makes SQLite run faster.

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

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[Mark Richards]

D. Richard Hipp , On 8/11/2009 16:02:
>
> Perhaps pthreads is going goofy.  Please recompile with -
> DSQLITE_THREADSAFE=0 and see if that helps.
>
BINGO.

env   CC="gcc-cris -mlinux -isystem $EROOT/include"CPP="gcc-cris 
-mlinux -E -isystem $EROOT/include"   CXX="g++-cris -mlinux -xc++ 
-isystem $EROOT/include"   LDFLAGS="-L$EROOT/lib"   
OBJCOPY="objcopy-cris"   LD="ld-cris -mcrislinux"   AR="ar-cris"   
RANLIB="ranlib-cris"   NM="nm-cris"   STRIP="strip-cris"  ./configure 
--host=cris-axis-linux-gnu  
--prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu 
--enable-static=yes --enable-shared=yes  --disable-threadsafe --disable-tcl


Works like a charm.

How can I help find the issue with my test case?

/m


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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[D. Richard Hipp]

On Aug 11, 2009, at 2:53 PM, Mark Richards wrote:
>
> When sqlite3_open() is called as above, got returns -1 (forever).  -1
> returning from a NONBLOCK read is accepted behaviour, but pretty  
> quickly
> the read() should give some data.  Instead it returns -1 each time.   
> (If
> I allow fd[0] to be in blocking mode, the read() never returns).
>
> Thinking that perhaps the
>
> A mangled stdout came to mind, but this does not appear to be the  
> case.
> But maybe sqlite_open() or other internals diddle with stdout?


SQLite shouldn't be messing with stdin or stdout.

We've done things like this before.  We know it works.

Perhaps pthreads is going goofy.  Please recompile with - 
DSQLITE_THREADSAFE=0 and see if that helps.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[Mark Richards]

Yes, tried that.  No change in result.

Assuming sqlite does a |F_GETFD| then restores when done.
I built in some testing to see if the file descriptor was being mangled, 
and cannot detect any difference.

Test:

 flags = fcntl(fd[0], F_GETFL, 0);

 char *cFlagMsg;
 cFlagMsg=calloc(255,sizeof(char));
 snprintf(cFlagMsg,255,"%s","FLAGS: ");
 if (flags & O_NONBLOCK)
 strcat(cFlagMsg,"O_NONBLOCK ");
 if (flags & O_APPEND)
 strcat(cFlagMsg,"O_APPEND ");
 if (flags & O_DSYNC)
 strcat(cFlagMsg,"O_DSYNC ");
 if (flags & O_RSYNC)
 strcat(cFlagMsg,"O_RSYNC ");
 if (flags & O_SYNC)
 strcat(cFlagMsg,"O_SYNC ");



Results:
 // before change to O_NONBLOCK
 GETFL Flags= 0
 // after change to O_NONBLOCK
 GETFL Flags= 2048
 FLAGS: O_NONBLOCK


I inserted this code in place of my original calls to open and close:


 sqlite3 *db;
 char *zErrMsg = 0;
 int rc;

 rc = sqlite3_open("/etc/solarwave/aem.db", );
 if( rc )
 {
 WriteSyslogMessage("Can't open database: %s\n", 
sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 }
 rc = sqlite3_exec(db, "SELECT * FROM dbsensors", NULL, 0, );
 if( rc!=SQLITE_OK )
 {
 WriteSyslogMessage("SQL error: %s\n", zErrMsg);
 sqlite3_free(zErrMsg);
 }
 sqlite3_close(db);


(Note: WriteSyslogMessage() issued a system("logger 'string'"); call)

Same issue.  -1 returned during read().


/m


D. Richard Hipp , On 8/11/2009 15:38:
> On Aug 11, 2009, at 2:53 PM, Mark Richards wrote:
>
>
>> Environment:
>>  Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris
>> unknown
>>
>> Sqlite:
>>  Sqlite: sqlite-3.6.14
>>./configure --host=cris-axis-linux-gnu
>>   --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu
>> --enable-static=yes --enable-shared=yes --disable-dynamic-extensions
>>
>> Application:
>>  My application runs against the shared library built as above.
>>
>> Code in my application fails after calling sqlite3_open().
>>
>> This synopsis is of a function designed to spawn a shell, execute a
>> command, and read back the result via a pipe of stdout.  It works
>> fine,
>> until sqlite3_open() is called anywhere PRIOR.
>>
>> prior sqlite3_open() call:
>>  sqlite3 *dbf;
>>  sqlite3_open("/path/to/my.db",);
>>
>>
>>  fflush(stdout);
>>  pipe(fd);
>>  pid = fork();
>>  if (pid == 0)
>>  {
>>  dup2(fd[1], STDOUT_FILENO);
>>  dup2(fd[1], STDERR_FILENO);
>>  close(fd[0]);
>>  
> Have you tried called sqlite3_close() here to see if that helps?  All
> of SQLite's file descriptors are FD_CLOEXEC, but who knows
>
>
>>  execl("/bin/sh", "sh", "-c", "echo 123", 0);
>>  }
>>  if (-1 == (flags = fcntl(fd[0], F_GETFL, 0)))
>>  flags = 0;
>>  fcntl(fd[0], F_SETFL, flags | O_NONBLOCK);
>>
>>  .. within a loop:
>>
>>  got=read(fd[0], buf, sizeof(buf));
>>  if (got>-1)
>>  {
>>  snprintf(cValue,4,"%s",buf);
>>  break;
>>  }
>>  // got ==-1
>>  
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] resource temporarily unavailable after sqlite3_open()

[D. Richard Hipp]

On Aug 11, 2009, at 2:53 PM, Mark Richards wrote:

> Environment:
> Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris  
> unknown
>
> Sqlite:
> Sqlite: sqlite-3.6.14
>   ./configure --host=cris-axis-linux-gnu
>  --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu
> --enable-static=yes --enable-shared=yes --disable-dynamic-extensions
>
> Application:
> My application runs against the shared library built as above.
>
> Code in my application fails after calling sqlite3_open().
>
> This synopsis is of a function designed to spawn a shell, execute a
> command, and read back the result via a pipe of stdout.  It works  
> fine,
> until sqlite3_open() is called anywhere PRIOR.
>
> prior sqlite3_open() call:
> sqlite3 *dbf;
> sqlite3_open("/path/to/my.db", );
>
>
> fflush(stdout);
> pipe(fd);
> pid = fork();
> if (pid == 0)
> {
> dup2(fd[1], STDOUT_FILENO);
> dup2(fd[1], STDERR_FILENO);
> close(fd[0]);

Have you tried called sqlite3_close() here to see if that helps?  All  
of SQLite's file descriptors are FD_CLOEXEC, but who knows

> execl("/bin/sh", "sh", "-c", "echo 123", 0);
> }
> if (-1 == (flags = fcntl(fd[0], F_GETFL, 0)))
> flags = 0;
> fcntl(fd[0], F_SETFL, flags | O_NONBLOCK);
>
> .. within a loop:
>
> got=read(fd[0], buf, sizeof(buf));
> if (got>-1)
> {
> snprintf(cValue,4,"%s",buf);
> break;
> }
> // got ==-1

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] resource temporarily unavailable after sqlite3_open()

[Mark Richards]

I also (just) tried 3.6.17.  Same issue.

/m


Mark Richards , On 8/11/2009 14:53:
> Environment:
>   Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown
>
> Sqlite:
>   Sqlite: sqlite-3.6.14
> ./configure --host=cris-axis-linux-gnu
>--prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu
> --enable-static=yes --enable-shared=yes --disable-dynamic-extensions
>
> Application:
>   My application runs against the shared library built as above.
>
> Code in my application fails after calling sqlite3_open().
>
> This synopsis is of a function designed to spawn a shell, execute a
> command, and read back the result via a pipe of stdout.  It works fine,
> until sqlite3_open() is called anywhere PRIOR.
>
> prior sqlite3_open() call:
>   sqlite3 *dbf;
>   sqlite3_open("/path/to/my.db",);
>
>
>   fflush(stdout);
>   pipe(fd);
>   pid = fork();
>   if (pid == 0)
>   {
>   dup2(fd[1], STDOUT_FILENO);
>   dup2(fd[1], STDERR_FILENO);
>   close(fd[0]);
>   execl("/bin/sh", "sh", "-c", "echo 123", 0);
>   }
>   if (-1 == (flags = fcntl(fd[0], F_GETFL, 0)))
>   flags = 0;
>   fcntl(fd[0], F_SETFL, flags | O_NONBLOCK);
>
>   .. within a loop:
>
>   got=read(fd[0], buf, sizeof(buf));
>   if (got>-1)
>   {
>   snprintf(cValue,4,"%s",buf);
>   break;
>   }
>   // got ==-1
>
>
>
> When sqlite3_open() is called as above, got returns -1 (forever).  -1
> returning from a NONBLOCK read is accepted behaviour, but pretty quickly
> the read() should give some data.  Instead it returns -1 each time.  (If
> I allow fd[0] to be in blocking mode, the read() never returns).
>
> Thinking that perhaps the
>
> A mangled stdout came to mind, but this does not appear to be the case.
> But maybe sqlite_open() or other internals diddle with stdout?
>
>
> ___
> 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] resource temporarily unavailable after sqlite3_open()

[Mark Richards]

Environment:
 Linux axis 2.6.19 #9 PREEMPT Mon Apr 6 15:44:03 EDT 2009 cris unknown

Sqlite:
 Sqlite: sqlite-3.6.14
   ./configure --host=cris-axis-linux-gnu 
  --prefix=/AEMDEV/83+/devboard-R2_10/target/cris-axis-linux-gnu 
--enable-static=yes --enable-shared=yes --disable-dynamic-extensions

Application:
 My application runs against the shared library built as above.

Code in my application fails after calling sqlite3_open().

This synopsis is of a function designed to spawn a shell, execute a 
command, and read back the result via a pipe of stdout.  It works fine, 
until sqlite3_open() is called anywhere PRIOR.

prior sqlite3_open() call:
 sqlite3 *dbf;
 sqlite3_open("/path/to/my.db", );


 fflush(stdout);
 pipe(fd);
 pid = fork();
 if (pid == 0)
 {
 dup2(fd[1], STDOUT_FILENO);
 dup2(fd[1], STDERR_FILENO);
 close(fd[0]);
 execl("/bin/sh", "sh", "-c", "echo 123", 0);
 }
 if (-1 == (flags = fcntl(fd[0], F_GETFL, 0)))
 flags = 0;
 fcntl(fd[0], F_SETFL, flags | O_NONBLOCK);

 .. within a loop:

 got=read(fd[0], buf, sizeof(buf));
 if (got>-1)
 {
 snprintf(cValue,4,"%s",buf);
 break;
 }
 // got ==-1



When sqlite3_open() is called as above, got returns -1 (forever).  -1 
returning from a NONBLOCK read is accepted behaviour, but pretty quickly 
the read() should give some data.  Instead it returns -1 each time.  (If 
I allow fd[0] to be in blocking mode, the read() never returns).

Thinking that perhaps the

A mangled stdout came to mind, but this does not appear to be the case.  
But maybe sqlite_open() or other internals diddle with stdout?


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

Re: [sqlite] Cannot insert records into a table after dropping and recreating it

[Simon Slavin]

On 11 Aug 2009, at 4:50pm, Radcon Entec wrote:

> At startup, the application creates three tables.  If the file  
> previously existed, the create table queries fail.  My code checks  
> the error message, and if it indicates that the table previously  
> existed, it ignores the error.

Use the 'IF NOT EXISTS' form:

CREATE TABLE IF NOT EXISTS myTable ...

That way you don't need to have your own code to filter out just that  
one error.

> There is a fourth table that is handled differently.  This table  
> will be created at startup, used, and then dropped.  So, my code  
> drops the table, then creates it, and then adds data to it.  (The  
> final drop is not yet implemented.)  Just in case the table got left  
> behind for some reason, my code drops the table before creating it.   
> Any errors from the drop are ignored, although there is an exception  
> handler there and I have verified that the hander is not being  
> executed.


As a temporary fix to explore your problem, try executing the same  
commands in the command-line tool and see if it gives you any error  
messages.

As a permanent fix I assume you want this table to be blank.  So the  
best/fastest thing to do would be to drop the table if it exists, then  
always create a new one with that name.  Use the form of 'DROP TABLE'  
which includes 'IF EXISTS':

DROP TABLE IF EXISTS fourthTable;
CREATE TABLE fourthTable ...

This gives you two commands which should both always execute without  
errors.  That means you can implement proper error-trapping and pay  
attention to the errors instead of ignoring them.

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

Re: [sqlite] Cannot insert records into a table after dropping and recreating it

[Ken]

--- On Tue, 8/11/09, Radcon Entec  wrote:

> From: Radcon Entec 
> Subject: [sqlite] Cannot insert records into a table after dropping and 
> recreating it
> To: sqlite-users@sqlite.org
> Date: Tuesday, August 11, 2009, 10:50 AM
> Greetings!
> 
> I have an application that uses an SQLite database file
> that may or may not exist when the application starts. 
> At startup, the application creates three tables.  If
> the file previously existed, the create table queries
> fail.  My code checks the error message, and if it
> indicates that the table previously existed, it ignores the
> error.  
> 
> There is a fourth table that is handled differently. 
> This table will be created at startup, used, and then
> dropped.  So, my code drops the table, then creates it,
> and then adds data to it.  (The final drop is not yet
> implemented.)  Just in case the table got left behind
> for some reason, my code drops the table before creating
> it.  Any errors from the drop are ignored, although
> there is an exception handler there and I have verified that
> the hander is not being executed.
> 
> If the database did not exist before the application runs,
> the temporary table is created and populated as
> expected.  But if the database did exist before the
> application runs, the temporary table is dropped and created
> as expected, but I cannot write any data into it. 
> There are no errors generated, but there is no data in the
> table, either.  
> 
> I am inserting data into the table by building an insert
> query and calling sqlite3_exec().  
> 
> Am I leaving out a step that makes my newly created table
> unwritable?
> 
> Thanks very much!
> 
> RolbR
> 
> 

Have you verified that your drop and recreate isn't being called again (maybe 
after doing the inserts)???

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

[sqlite] Cannot insert records into a table after dropping and recreating it

[Radcon Entec]

Greetings!

I have an application that uses an SQLite database file that may or may not 
exist when the application starts.  At startup, the application creates three 
tables.  If the file previously existed, the create table queries fail.  My 
code checks the error message, and if it indicates that the table previously 
existed, it ignores the error.  

There is a fourth table that is handled differently.  This table will be 
created at startup, used, and then dropped.  So, my code drops the table, then 
creates it, and then adds data to it.  (The final drop is not yet implemented.) 
 Just in case the table got left behind for some reason, my code drops the 
table before creating it.  Any errors from the drop are ignored, although there 
is an exception handler there and I have verified that the hander is not being 
executed.

If the database did not exist before the application runs, the temporary table 
is created and populated as expected.  But if the database did exist before the 
application runs, the temporary table is dropped and created as expected, but I 
cannot write any data into it.  There are no errors generated, but there is no 
data in the table, either.  

I am inserting data into the table by building an insert query and calling 
sqlite3_exec().  

Am I leaving out a step that makes my newly created table unwritable?

Thanks very much!

RolbR



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

Re: [sqlite] [Delphi] Reading list of tables?

[Simon Slavin]

On 11 Aug 2009, at 11:25am, Gilles Ganault wrote:

> I'd like to fill a ComboBox widget with the list of tables in a
> database file, using the free Aducom wrapper.
>
> Would someone have some code handy on how to extract this information
> from a SQLite database, ie. the equivalent for the CLI command
> ".tables".

SELECT sql FROM sqlite_master WHERE type='table'

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

[sqlite] [Delphi] Reading list of tables?

[Gilles Ganault]

Hello

I'd like to fill a ComboBox widget with the list of tables in a
database file, using the free Aducom wrapper.

Would someone have some code handy on how to extract this information
from a SQLite database, ie. the equivalent for the CLI command
".tables".

Thank you.

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

Re: [sqlite] Busy Handler in Shared Cache Mode?

[Edzard Pasma]

More detailed testing revealed that it is not a reader but the writer who 
observes a long wait time. The writer appears to get unlocked after all 
reader-threads have completed. This must be a case of writer starvation. I had 
not realized that shared cache mode is, by default, prone to this. The good 
news is that Unlock Notification should handle it 
(http://www.sqlite.org/unlock_notify.html). 

--- edz...@volcanomail.com wrote:

From: "Edzard Pasma" 
To: "General Discussion of SQLite Database" 
Cc: 
Subject: Re: [sqlite] Busy Handler in Shared Cache Mode?
Date: Mon, 10 Aug 2009 04:25:32 -0700

Hi Nikolaus,

Interesting to read your findings. I assume you finally disallowed 
read_uncommitted. Trying to explain why the wait times can get longer, I come 
to two reasons.

- the time slots where a read-lock is obtained become smaller. 
This can be significant if there are a lot of transactions with little time in 
between. With the busy handling by polling, a reader may mis slots. This will 
sure be improved by Unlock Notification.

- the time slots where a read-lock can not be obtained become longer.
This can cause problems if there are long-running transaction. The Unlock 
Notification feature is not going to help here.

I still like to share an observation from my own test, which reproduces the 
first case. I start four reading threads and one writer. Each thread performs a 
fixed number of cycles. Apart from the wait times, also the overall elapsed 
time is measured. Indeed the maximum wait-time gets bigger if shared cache mode 
is enabled. Interestingly, this does not apply to the elapsed time, which is 
still reduced. Thus, an increase of the maximum wait-time is not necessarily a 
worse performance. By the way, this was measured on a single-processor system.

Hope this is useful to know.

Best regards, Edzard.

On Fri, Aug 7, 2009 at 1:45 PM, Nikolaus Rath wrote:
> Hello,
>
> I have program that continuously runs several threads (about 4 to 10)
> which concurrently read and write to an SQLite database. From
> http://www.sqlite.org/sharedcache.html it seems to me that I should be
> using shared cache mode.
>
> Until now, I have set the busy timeout to 500 msec and never had to 
deal
> with SQLITE_BUSY errors. However, there seems to be no corresponding
> function for the SQLITE_LOCKED errors that are generated in shared 
cache
> mode. So I changed the code manually sleep for a random amount (0 to 
100
> msec) and then try to execute the statement again if it encounters
> SQLITE_LOCKED. But now the threads are often waiting for more than 1
> second before they finally get their query executed.
>
> I suspect this is because the random sleep is wasting a lot of time,
> while without shared cache (and with enabled busy timeout) a thread
> blocks and gets revived as soon as the lock on the database is
> released.
>
>
> How can I avoid this problem? Is there a way to set a busy timeout for
> SQLITE_LOCKED as well? Or a more clever method instead of sleeping for
> random amounts? Or should I just avoid using shared cache mode?
___
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