[sqlite] how can I find source of sqlite V 2.x

[Russell]

Hi all,

My project had to use Qt 3.3.8, which supports sqlite V2 only. How can I get 
the source of V2?
BR
Russell

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

Re: [sqlite] Major memory leak

[Dimitri]

Hi,

> I'm thinking whether this is a memory leak or not sort of depends on
> your definition.  If a process is designed to remain open for long
> periods of time with little activity, and it ends up taking up 1
> gigabyte of memory, that looks an awful lot like a leak to me.  There
> are likely to be at least three instances of this application running,
> and after they all run for a month, they're likely to be consuming 5
> gigabytes of memory.  This is not acceptable.  If SQLite's sorted
> query is taking up 2.5 megabytes of memory every time this piece of
> the application is invoked, I need to know how to ensure that that
> memory is released.

Most probably SQLite does release malloc'ed memory using free(). Note however 
that free() merely notifies the C runtime the free'd piece of memory is not 
used anymore. The C runtime does not necessarily release this piece of memory 
to the system. That would be inefficient. As a result, the process appears to 
be still using the memory. That's why tools such as 'top' on Unix are not 
necessarily appropriate to detect memory leaks, they show memory still being 
used by a process, although the program has called free(). The C runtime might 
give memory back to the system when the system is short on memory, or unused 
memory may be swapped to disk. This is a C runtime issue, not an SQLite issue.

As already explained, it could indeed be that the memory footprint is a 
problem for you, but a memory footprint problem is not a memory leak:
http://en.wikipedia.org/wiki/Memory_leak

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

Re: [sqlite] Major memory leak

[Scott Hess]

If the maximum memory footprint is too large, then you should arrange
to have a smaller memory footprint.  For instance, you can use PRAGMA
cache_size to reduce the footprint there, use PRAGMA temp_store to
make sure you aren't storing temporary tables in memory, call
sqlite3_release_memory() to release memory if it's using too much, use
sqlite3_soft_heap_limit() to provide a soft limit on the footprint,
use sqlite3_enable_shared_cache() to let your different threads share
memory.

"Memory leak" is a pretty specific thing, it means that the program no
longer references memory in a way that will allow it to release the
memory.  You mention that the memory footprint is too large - it would
be really helpful if you put up a database and query which
demonstrated what you're describing, and the constraints you _wish_
things to operate under.  Then people can make specific
recommendations.

-scott


On Mon, Mar 24, 2008 at 2:34 PM, Rob Richardson <[EMAIL PROTECTED]> wrote:
>  I'm thinking whether this is a memory leak or not sort of depends on
>  your definition.  If a process is designed to remain open for long
>  periods of time with little activity, and it ends up taking up 1
>  gigabyte of memory, that looks an awful lot like a leak to me.  There
>  are likely to be at least three instances of this application running,
>  and after they all run for a month, they're likely to be consuming 5
>  gigabytes of memory.  This is not acceptable.  If SQLite's sorted
>  query is taking up 2.5 megabytes of memory every time this piece of
>  the application is invoked, I need to know how to ensure that that
>  memory is released.
>
>  Here's a brief description of the application.  My company, Rad-Con,
>  Inc., is a major supplier of annealing furnaces and related equipment
>  and software to metal processors worldwide.  The application monitors
>  the annealing process on a customer's site.  There could be well over
>  a hundred annealing bases.  The applicaton's first screen displays an
>  overview of all of the bases, whether they have furnaces, if the
>  furnaces are turned on, and so on.  A user can double-click on base to
>  see details.  A button on the detail screen calls up a trend display.
>  Trend data is stored in SQLite database files, one per base.  The
>  application executes the query I described to find when the last row
>  was written to the table, and uses that to calculate the times that
>  will be displayed on the graph.  Then, the application reads the
>  entire table and plots the data.  When the user is finished, he closes
>  the trend screen.  My requirement is to ensure that the amount of
>  memory allocated to my application before the trend screen is
>  displayed and after the trend screen is closed is the same.  If more
>  memory is allocated after it is closed, that is a leak, by my
>  definition.
>
>
>  RobR
>
>
>
>
>  On 3/23/08, Christian Smith <[EMAIL PROTECTED]> wrote:
>  > On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:
>  > > My SQLite library is built from the single translation unit
>  > > sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
>  > >
>  > > I do not have valgrind, but circumstantial evidence that this is a
>  > > SQLite problem is strong.  When stepping through my code, I see that
>  > > my application's memory jumps by over 2.5 megabytes when the
>  > > sqlite3_step() method is called when using either the sorted query or
>  > > the query using max().  The unsorted query doesn't show any memory
>  > > jump.  Also, the difference in memory consumption before this part of
>  > > the code is executed and after it is left is the same size as the jump
>  > > in memory when sqlite3_step() is called.
>  >
>  >
>  > When doing a sorted query, the result set is formed in a temporary database
>  > somewhere defined by the environment. In your case, it sounds like the
>  > temporary database is memory based. Once the result set is done with, 
> SQLite
>  > may return the memory to the OS using free, but that will show under the
>  > process's virtual memory footprint.
>  >
>  > You can tell SQLite to use a disk based temporary database using:
>  > http://sqlite.org/pragma.html#pragma_temp_store
>  >
>  > Using this, your memory usage will probably be more stable.
>  >
>  > However, this certainly isn't a memory leak.
>  >
>  >
>  > >
>  > > RobR
>  > >
>  >
>  > Christian
>  > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>
>
>  --
>  Please do not copy or forward this message or any attachments without
>  my permission.  Remember, asking permission is a great way to get me
>  to visit your site!
>
>
> ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___

[sqlite] Lit

[drh]

"Rob Richardson" <[EMAIL PROTECTED]> wrote:
> I'm thinking whether this is a memory leak or not sort of depends on
> your definition.  If a process is designed to remain open for long
> periods of time with little activity, and it ends up taking up 1
> gigabyte of memory, that looks an awful lot like a leak to me.  There
> are likely to be at least three instances of this application running,
> and after they all run for a month, they're likely to be consuming 5
> gigabytes of memory.  This is not acceptable.  If SQLite's sorted
> query is taking up 2.5 megabytes of memory every time this piece of
> the application is invoked, I need to know how to ensure that that
> memory is released.

The test harness for SQLite counts the number of mallocs and frees
and prints an error if the two do not match.  This happens every
time we run a test.  We also run the test suite through valgrind 
prior to each release. The test scripts provide close to 100% test 
coverage.  About 70% of the SQLite source code is devoted to testing.

Because of the extensive testing outlined above, memory leaks in
SQLite have never been a problem.  Historically, whenever somebody
comes forward with claims of memory leaks in SQLite, it generally
works out to be memory leaks in their application.

Based on prior experience and our extensive tests, claims that
SQLite leaks 2.5MB of RAM every time it sorts are generally met
with great skepticism.  I won't say that it is impossible that
SQLite is leaking memory, though it does seem unlikely.  Certainly
the claim that SQLite leaks 2.5MB on every sort is an extraordinary
one, and extradinary claims require extraordinary proof.

If you would like to submit a script or a short program that
appears to cause SQLite to leak memory, we will be happy to
look into the problem.  But until I see an actual demonstration
of the problem, I'm going to assume that the problem is really
outside of SQLite and pay the matter no further attention.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Major memory leak

[Rob Richardson]

I'm thinking whether this is a memory leak or not sort of depends on
your definition.  If a process is designed to remain open for long
periods of time with little activity, and it ends up taking up 1
gigabyte of memory, that looks an awful lot like a leak to me.  There
are likely to be at least three instances of this application running,
and after they all run for a month, they're likely to be consuming 5
gigabytes of memory.  This is not acceptable.  If SQLite's sorted
query is taking up 2.5 megabytes of memory every time this piece of
the application is invoked, I need to know how to ensure that that
memory is released.

Here's a brief description of the application.  My company, Rad-Con,
Inc., is a major supplier of annealing furnaces and related equipment
and software to metal processors worldwide.  The application monitors
the annealing process on a customer's site.  There could be well over
a hundred annealing bases.  The applicaton's first screen displays an
overview of all of the bases, whether they have furnaces, if the
furnaces are turned on, and so on.  A user can double-click on base to
see details.  A button on the detail screen calls up a trend display.
Trend data is stored in SQLite database files, one per base.  The
application executes the query I described to find when the last row
was written to the table, and uses that to calculate the times that
will be displayed on the graph.  Then, the application reads the
entire table and plots the data.  When the user is finished, he closes
the trend screen.  My requirement is to ensure that the amount of
memory allocated to my application before the trend screen is
displayed and after the trend screen is closed is the same.  If more
memory is allocated after it is closed, that is a leak, by my
definition.


RobR


On 3/23/08, Christian Smith <[EMAIL PROTECTED]> wrote:
> On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:
> > My SQLite library is built from the single translation unit
> > sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
> >
> > I do not have valgrind, but circumstantial evidence that this is a
> > SQLite problem is strong.  When stepping through my code, I see that
> > my application's memory jumps by over 2.5 megabytes when the
> > sqlite3_step() method is called when using either the sorted query or
> > the query using max().  The unsorted query doesn't show any memory
> > jump.  Also, the difference in memory consumption before this part of
> > the code is executed and after it is left is the same size as the jump
> > in memory when sqlite3_step() is called.
>
>
> When doing a sorted query, the result set is formed in a temporary database
> somewhere defined by the environment. In your case, it sounds like the
> temporary database is memory based. Once the result set is done with, SQLite
> may return the memory to the OS using free, but that will show under the
> process's virtual memory footprint.
>
> You can tell SQLite to use a disk based temporary database using:
> http://sqlite.org/pragma.html#pragma_temp_store
>
> Using this, your memory usage will probably be more stable.
>
> However, this certainly isn't a memory leak.
>
>
> >
> > RobR
> >
>
> Christian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Please do not copy or forward this message or any attachments without
my permission.  Remember, asking permission is a great way to get me
to visit your site!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance under load

[John Stanton]

Sam Carleton wrote:
> On Mon, Mar 24, 2008 at 4:11 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
> 
>>  Try the math over:
>>
>>  24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.
>>
>>  100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).
>>
>>  Of course, this is on average.  A real website getting "100K hits per
>>  day" would generally expect 10x to 15x this rate during heavy times.
>>
>>  That's still not ~69 hits/sec, however.
> 
> 
> yep, I was off by a factor of 60.
> 
> 
>> > As I am developing the software, is there anything I need to keep in
>> > mind to help optimize the database usage to achieve the million hits a
>> > day the "Appropriate Uses for SQLite"
>>
>>  The big thing is that locks are exclusive across the whole database,
>>  so an application needs to get it, do what it needs, and get out.
>>  A clean database design and proper use of indexes on critical columns
>>  (for queries) as well as transactions (for updates) are most likely the
>>  first places to look.
> 
> 
> So can SQLite can keep up with the load of 40 to 50 viewing stations
> going at one time?  Or am I better off looking at other options?  If I
> should look at other options, what would you recommend?  The
> application is used at events, each event having it's own set of
> images and data.  What I really like about SQLite is that it is file
> based and I can keep the data for each event with the images for that
> event, allowing my customer an easy way to back things up at the end
> of the event.  I also like the cost, too;)
> 
> Sam
This is a bit like asking "how long is a piece of string".  If you are 
doing short transactions and have a well conceived database and 
efficient server side software (using things like PHP and CGI are 
detrimental) then it should work.  If you do a lot of inserts and 
updates or have SQL which results in row searches then it will not work.
> ___
> 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] performance under load

[John Stanton]

My performance measurements using a modest server and a more efficient 
www server than Apache gives an Sqlite capability of 30-50 hits per 
second using simple SQL selects.  Think of that as a reasonable upper limit.

Jay A. Kreibich wrote:
> On Mon, Mar 24, 2008 at 03:55:12PM -0400, Sam Carleton scratched on the wall:
> 
> 
>>>From the web site's "Appropriate Uses for SQLite" it says that "any
>>site that gets fewer than 100K hits/day should work fine with SQLite".
>> I did the math and that looks to be around 69 hits a second.
> 
> 
>   Try the math over:
> 
>   24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.
> 
>   100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).
> 
>   Of course, this is on average.  A real website getting "100K hits per
>   day" would generally expect 10x to 15x this rate during heavy times.
> 
>   That's still not ~69 hits/sec, however.
> 
> 
>>As I am developing the software, is there anything I need to keep in
>>mind to help optimize the database usage to achieve the million hits a
>>day the "Appropriate Uses for SQLite"
> 
> 
>   The big thing is that locks are exclusive across the whole database,
>   so an application needs to get it, do what it needs, and get out.
>   A clean database design and proper use of indexes on critical columns
>   (for queries) as well as transactions (for updates) are most likely the
>   first places to look.
> 
>-j
> 

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

Re: [sqlite] [FreeBSD] Can read but can't write?

[Gilles Ganault]

On Mon, 24 Mar 2008 21:57:52 +0100, Gilles Ganault
<[EMAIL PROTECTED]> wrote:
>   I'm having the following issue trying to access an SQLite 3.3.17
>database from PHP+PDO under FreeBSD 6.3: I can read, but can't write,
>with no error reported by PHP or /var/log/messages.

Found what it was: Even if a file is set to 664 and owned by the right
user, the _directory_ in which the file lives has precedence. In this
case, I just chowned it to root:www, and chmoded it to 664:

[/usr/local/share/asterisk/agi-bin]# ll
drwxrwxr-x  3 root  www  512 Mar 24 22:05 .
drwxr-xr-x  9 root  wheel512 Mar 14 08:05 ..
-rw-rw-r--  1 www   www 3072 Mar 24 22:05 test.sqlite

Learned something new today. Hope this helps.

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

Re: [sqlite] Command to create a database in sqlite

[John Stanton]

sqlite3_open.

Mozaharul Haque wrote:
> Hi,
> 
> A silly asking. I found the command to create a table but what about to
> create a database?
> 
> The table would be stored in the sqlite_master table.
> 
> And how do I refer (full path) to the database  using application like
> Basic4ppc 6.05.
> 
> 
> Please help.
> 
> 
> regards,
> 
> Mozaharul Haque
> 
> ___
> 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] [FreeBSD] Can read but can't write?

[Gilles Ganault]

Hello

I'm having the following issue trying to access an SQLite 3.3.17
database from PHP+PDO under FreeBSD 6.3: I can read, but can't write,
with no error reported by PHP or /var/log/messages.

I've tried both Apache and Lighttpd as web server, to no avail.

Here's some information:

1. echo exec('id');
=> uid=80(www) gid=80(www) groups=80(www)

2. The PHP script and the SQLite database are owned by www:www:


[/usr/local/www/apache22/data]# ll
drwxr-xr-x  2 root  wheel   512 Mar 24 19:52 .
drwxr-xr-x  6 root  wheel   512 Mar 24 18:56 ..
-rw-r--r--  1 www   www2463 Mar 24 20:00 test.php

[/usr/local/share/asterisk/agi-bin]# ll
drwxr-xr-x  3 root  wheel512 Mar 24 18:38 .
drwxr-xr-x  9 root  wheel512 Mar 14 08:05 ..
-rw-rw-r--  1 www   www 3072 Mar 24 18:37 test.sqlite


3. And here's the code:


//GOOD $dbh = new PDO("sqlite:test.sqlite");
//GOOD $dbh = new PDO("sqlite:/tmp/test.sqlite");
$dbh = new
PDO("sqlite:/usr/local/share/asterisk/agi-bin/test.sqlite");

$time = time();
$current = date("Y-m-d H:i:s",$time);
$sql = "INSERT INTO mytable VALUES (NULL,'$current')";
print "$sql";
$dbh->exec($sql);

$sql = "SELECT * FROM mytable";
foreach($dbh->query($sql) as $row) {
print $row['name'] . "\n";
}

$dbh = null;


I don't understand why test.php can read, but cannot write. Could it
be that SQLite does something that requires 777 access to the
directory where the database file lives? Something else?

Thank you.

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

Re: [sqlite] performance under load

[Sam Carleton]

On Mon, Mar 24, 2008 at 4:11 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote:
>   Try the math over:
>
>   24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.
>
>   100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).
>
>   Of course, this is on average.  A real website getting "100K hits per
>   day" would generally expect 10x to 15x this rate during heavy times.
>
>   That's still not ~69 hits/sec, however.

yep, I was off by a factor of 60.

>  > As I am developing the software, is there anything I need to keep in
>  > mind to help optimize the database usage to achieve the million hits a
>  > day the "Appropriate Uses for SQLite"
>
>   The big thing is that locks are exclusive across the whole database,
>   so an application needs to get it, do what it needs, and get out.
>   A clean database design and proper use of indexes on critical columns
>   (for queries) as well as transactions (for updates) are most likely the
>   first places to look.

So can SQLite can keep up with the load of 40 to 50 viewing stations
going at one time?  Or am I better off looking at other options?  If I
should look at other options, what would you recommend?  The
application is used at events, each event having it's own set of
images and data.  What I really like about SQLite is that it is file
based and I can keep the data for each event with the images for that
event, allowing my customer an easy way to back things up at the end
of the event.  I also like the cost, too;)

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

Re: [sqlite] performance under load

[Jay A. Kreibich]

On Mon, Mar 24, 2008 at 03:55:12PM -0400, Sam Carleton scratched on the wall:

> >From the web site's "Appropriate Uses for SQLite" it says that "any
> site that gets fewer than 100K hits/day should work fine with SQLite".
>  I did the math and that looks to be around 69 hits a second.

  Try the math over:

  24 hours * 60 minutes * 60 seconds = 86,400 seconds/day.

  100,000 hits/day  /  86,400 seconds/day = 1.1574 hits/sec (on average).

  Of course, this is on average.  A real website getting "100K hits per
  day" would generally expect 10x to 15x this rate during heavy times.

  That's still not ~69 hits/sec, however.

> As I am developing the software, is there anything I need to keep in
> mind to help optimize the database usage to achieve the million hits a
> day the "Appropriate Uses for SQLite"

  The big thing is that locks are exclusive across the whole database,
  so an application needs to get it, do what it needs, and get out.
  A clean database design and proper use of indexes on critical columns
  (for queries) as well as transactions (for updates) are most likely the
  first places to look.

   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance under load

[drh]

"Sam Carleton" <[EMAIL PROTECTED]> wrote:
> My application is an apache based kiosk system that displays images.
> The SQLite database is used by PHP to track user info, who is logged
> in and what they have selected.  SQLite is NOT managing anything about
> the files.  I have a few customers that are running with 40 to 50
> kiosks.
> 
> >From the web site's "Appropriate Uses for SQLite" it says that "any
> site that gets fewer than 100K hits/day should work fine with SQLite".
>  I did the math and that looks to be around 69 hits a second.

Actually, it works out to about 69 hits per *minute* or
about 1.16 hits per second.


> Considering the SQLite database is used on page loads and when users
> tag images, but NOT when requesting images, I believe that 69 hits a
> second is more then fast enough.  Am I correct or is there something
> else I need to be taking into consideration?
> 
> As I am developing the software, is there anything I need to keep in
> mind to help optimize the database usage to achieve the million hits a
> day the "Appropriate Uses for SQLite"
> 
> Sam
> ___
> 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] performance under load

[Sam Carleton]

My application is an apache based kiosk system that displays images.
The SQLite database is used by PHP to track user info, who is logged
in and what they have selected.  SQLite is NOT managing anything about
the files.  I have a few customers that are running with 40 to 50
kiosks.

>From the web site's "Appropriate Uses for SQLite" it says that "any
site that gets fewer than 100K hits/day should work fine with SQLite".
 I did the math and that looks to be around 69 hits a second.
Considering the SQLite database is used on page loads and when users
tag images, but NOT when requesting images, I believe that 69 hits a
second is more then fast enough.  Am I correct or is there something
else I need to be taking into consideration?

As I am developing the software, is there anything I need to keep in
mind to help optimize the database usage to achieve the million hits a
day the "Appropriate Uses for SQLite"

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

[sqlite] Issue with LEFT JOIN, index, and literal in ON clause

[Daniel Ring]

The following behavior when using LEFT OUTER JOIN with an indexed column
and literals in the ON clause is, I'm pretty sure, wrong.  SQLite seems
to convert the join to an INNER JOIN in some cases.

The capture is from SQLite 3.4.2, but I get the same results with 3.5.7.
 I also copied the raw SQL at the end for your copy-and-pasting pleasure.

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> CREATE TABLE "aaa" (
   ...>   'id' integer NOT NULL PRIMARY KEY,
   ...>   'o_id' integer UNSIGNED NOT NULL,
   ...>   'o_type' integer NOT NULL
   ...> );
sqlite>
sqlite> CREATE TABLE 'bbb' (
   ...>   'id' integer NOT NULL PRIMARY KEY
   ...> );
sqlite>
sqlite> CREATE TABLE 'ccc' (
   ...>   'id' integer NOT NULL PRIMARY KEY
   ...> );
sqlite>
sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1);
sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2);
sqlite>
sqlite> INSERT INTO bbb ('id') VALUES (3);
sqlite> INSERT INTO ccc ('id') VALUES (4);
sqlite>
sqlite> SELECT aaa.id, bbb.id, ccc.id
   ...>   FROM aaa
   ...> LEFT OUTER JOIN bbb
   ...>   ON (aaa.o_id=bbb.id AND aaa.o_type=1)
   ...> LEFT OUTER JOIN ccc
   ...>   ON (aaa.o_id=ccc.id AND aaa.o_type=2);
1|3|
2||4
sqlite>

This is correct, but add an index and...

sqlite> CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type');
sqlite>
sqlite> SELECT aaa.id, bbb.id, ccc.id
   ...>   FROM aaa
   ...> LEFT OUTER JOIN bbb
   ...>   ON (aaa.o_id=bbb.id AND aaa.o_type=1)
   ...> LEFT OUTER JOIN ccc
   ...>   ON (aaa.o_id=ccc.id AND aaa.o_type=2);
1|3|
sqlite>

I expect the second SELECT to produce the same results as the first
(presumably faster).

So how can I rewrite the query to compensate?

sqlite> INSERT INTO ccc ('id') VALUES (3);
sqlite> INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2);
sqlite>

sqlite> SELECT aaa.id, bbb.id, ccc.id
   ...>   FROM aaa
   ...> LEFT OUTER JOIN bbb
   ...>   ON (aaa.o_id=bbb.id)
   ...> LEFT OUTER JOIN ccc
   ...>   ON (aaa.o_id=ccc.id)
   ...>   WHERE
   ...> (aaa.o_type=1 AND bbb.id IS NOT NULL) OR
   ...> (aaa.o_type=2 AND ccc.id IS NOT NULL);
1|3|3
2||4
3|3|3
sqlite>

This is the solution I saw elsewhere on this list, and it works, but I
want exactly one of the joined tables to be non-null.

sqlite> SELECT aaa.id, bbb.id, ccc.id
   ...>   FROM aaa
   ...> LEFT OUTER JOIN bbb
   ...>   ON (aaa.o_id=bbb.id AND (aaa.o_type=1 OR 0))
   ...> LEFT OUTER JOIN ccc
   ...>   ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0));
1|3|
2||4
3||3
sqlite>

This is what I want, but the extra 0 makes me sad.

So what am I doing wrong?

Daniel Ring



--
CREATE TABLE "aaa" (
  'id' integer NOT NULL PRIMARY KEY,
  'o_id' integer UNSIGNED NOT NULL,
  'o_type' integer NOT NULL
);

CREATE TABLE 'bbb' (
  'id' integer NOT NULL PRIMARY KEY
);

CREATE TABLE 'ccc' (
  'id' integer NOT NULL PRIMARY KEY
);

INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (1, 3, 1);
INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (2, 4, 2);

INSERT INTO bbb ('id') VALUES (3);
INSERT INTO ccc ('id') VALUES (4);

SELECT aaa.id, bbb.id, ccc.id
  FROM aaa
LEFT OUTER JOIN bbb
  ON (aaa.o_id=bbb.id AND aaa.o_type=1)
LEFT OUTER JOIN ccc
  ON (aaa.o_id=ccc.id AND aaa.o_type=2);

CREATE INDEX 'aaa_o_type' ON 'aaa' ('o_type');

SELECT aaa.id, bbb.id, ccc.id
  FROM aaa
LEFT OUTER JOIN bbb
  ON (aaa.o_id=bbb.id AND aaa.o_type=1)
LEFT OUTER JOIN ccc
  ON (aaa.o_id=ccc.id AND aaa.o_type=2);

INSERT INTO ccc ('id') VALUES (3);
INSERT INTO aaa ('id', 'o_id', 'o_type') VALUES (3, 3, 2);

SELECT aaa.id, bbb.id, ccc.id
  FROM aaa
LEFT OUTER JOIN bbb
  ON (aaa.o_id=bbb.id AND aaa.o_type=1*(aaa.id/aaa.id))
LEFT OUTER JOIN ccc
  ON (aaa.o_id=ccc.id AND aaa.o_type=2*(aaa.id/aaa.id));

SELECT aaa.id, bbb.id, ccc.id
  FROM aaa
LEFT OUTER JOIN bbb
  ON (aaa.o_id=bbb.id AND (aaa.o_type=1 OR 0))
LEFT OUTER JOIN ccc
  ON (aaa.o_id=ccc.id AND (aaa.o_type=2 OR 0));
--

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

Re: [sqlite] Performance of bulk insertion

[Griggs, Donald]

Hi, Mahalakshmi,
 
Regarding: "..Am I doing right or it's a lengthy process.If so suggest
some other way for inserting the records?..."

I must confess I have not spent much time looking at the details of your
email, but you will want to be sure to use a single transaction to
insert many rows in your table if performance is an issue:

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

You'll likely want to use a transaction for at least 100 rows at a time
-- or perhaps even for all 4.

After completing the insert, you may find you wish to use the CREATE
INDEX command to speed up queries.







This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fprintf(stderr,...) wrote over my database.........

[Ken]

Sounds like the file was closed then re-opened, But the db was opened in 
between assigning the db file number to STDERR. 

Your app then happily used fprintf(STDERR, " blah blah blah" );   writing into 
the DB file.

Solutions:   
  1. Don't use hard coded stderr.
  2. Dont use freopen. (why are you re-opening a STDERR in the first place?)
  3. Rebuild your logging routines. to open a specific log file.
  4. Use syslog ? (if your on a *nix).

HTH,
Ken
  

Mark Gilbert <[EMAIL PROTECTED]> wrote: Anyone seen this ?

- My app is running happily like it has for years.  Something happens..

- Result is that my logging which normally goes to a completely 
different file (connected to stderr with freopen) ends up connected 
to the SQLite database file, and writes all over the start of the 
file..

- Database corrupt

Anyone have any wisdom on this ?

thanks

___
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] fprintf(stderr,...) wrote over my database.........

[Mark Gilbert]

Anyone seen this ?

- My app is running happily like it has for years.  Something happens..

- Result is that my logging which normally goes to a completely 
different file (connected to stderr with freopen) ends up connected 
to the SQLite database file, and writes all over the start of the 
file..

- Database corrupt

Anyone have any wisdom on this ?

thanks

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

[sqlite] SQLITE3_OPEN returns SQLITE_NOMEM

[sqlite]

Dear All,

I have just started using the SQLite Db in my applications. My application
runs under MIPS processor. I have generated the Sqlite3.dll and Sqlite3.lib
file for the MIPS processor and it is geting compiled. When i use
Sqlit3_Open function to open a DB, it fails with the error message
SQLITE_NOMEM. But the same code is running fine in the Windows mode. How
should i rectify this? Can anyone help me in this regard.

Thanks
Kartthi
-- 
View this message in context: 
http://www.nabble.com/SQLITE3_OPEN-returns-SQLITE_NOMEM-tp16254109p16254109.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Rowid After Sorting

[Mahalakshmi.m]

1)
SELECT rowid,Id,Name FROM MyTable ORDER BY Name;

Rowid   Id  Name

 

4 4  aaa

3 3  bbb

2 2  xxx

1 1  zzz


2)
"create table Temp as select Name from Mytable order by Name;" 
RowidId  Name

1 4  aaa

2 3  bbb

3 2  xxx

4 1  zzz


I wish to perform all operations in my code with sorted order of the Name
field.

So, to find the index of a name, which is in sorted order, I need the Rowid
to be changed as shown in case 2 rather than in case 1.








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

[sqlite] Performance of bulk insertion

[Mahalakshmi.m]

Hi,

I am working in 3.3.6

The table schema of my code is as follows.



"CREATE TABLE ALBUMARTIST(

AlbumArtistId INTEGER PRIMARY KEY NOT NULL,

AlbumArtistName TEXT NOT NULL COLLATE
NOCASE,UNIQUE(AlbumArtistName));"

 

"CREATE TABLE ARTIST(

ArtistId INTEGER PRIMARY KEY NOT NULL,

ArtistName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(ArtistName));"


"CREATE TABLE ALBUM(

AlbumId INTEGER PRIMARY KEY NOT NULL,

AlbumName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(AlbumName));"




 "CREATE TABLE MUSIC(

Id INTEGER PRIMARY KEY NOT NULL,

Track TEXT NOT NULL,

URL TEXT NOT NULL,

Album_Id INTEGER,

Artist_Id INTEGER,

AlbumArtist_Id INTEGER);"


 "CREATE TABLE PLAYLIST (

PlayListId INTEGER PRIMARY KEY NOT NULL,

PlayListTrack TEXT,

PlayListUrl TEXT);"

 

"CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW  

BEGIN

DELETE FROM ALBUM WHERE AlbumId = old.Album_Id and not exists
(SELECT Id FROM MUSIC WHERE Album_Id = old.Album_Id); 

DELETE FROM ARTIST WHERE ArtistId = old.Artist_Id and not exists
(SELECT Id FROM MUSIC WHERE Artist_Id = old.Artist_Id);

 DELETE FROM ALBUMARTIST WHERE AlbumArtistId = old.AlbumArtist_Id
and not exists (SELECT Id FROM MUSIC WHERE AlbumArtist_Id =
old.AlbumArtist_Id);

 DELETE FROM TRACKLIST WHERE PlayListUrl = old.URL and not exists
(SELECT Id FROM MUSIC WHERE URL = old.URL);

END;"

 

 My database has 4 records.

My need is to add all the Track  in the Table MUSIC to the Table
TRACKLIST.For that i have used the following code.

 

 while(u32_PlayListRecordCount < TotalRecordCount)
 {

   if ( sqlite3_prepare(pst_SqliteCallback->db," SELECT Track, URL FROM
MUSIC LIMIT 100 OFFSET ? ; ",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }
   else
   {
sqlite3_bind_int(pst_PlayListPrepareStmt,1, u32_OffsetValue);

 

Now I will step and store all the 100 results in one Buffer.

   }
  
  if(e_ReturnStatus == SQLITE_DB_SUCCESS)
  {
   if ( sqlite3_prepare(pst_SqliteCallback->db,"INSERT INTO TRACKLIST
(PlayListTypeId,PlayListTrack,PlayListUrl) VALUES
(?,?,?);",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }

 

   else
   {
for(u32_Offset = 0;u32_Offset < gu32_PlayListindex;u32_Offset++)

Here gu32_PlayListindex  is the count of  "SELECT count(*) FROM MUSIC LIMIT
100 OFFSET ? ;"


{
 sqlite3_bind_int(pst_PlayListPrepareStmt,1,PlayListIndex);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,2,st_PlayList[u32_Offset].PlayList
TrackBuffer,-1,SQLITE_STATIC);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,3,st_PlayList[u32_Offset].PlayList
UrlBuffer,-1,SQLITE_STATIC);
u32_Return = sqlite3_step(pst_PlayListPrepareStmt);
sqlite3_reset(pst_PlayListPrepareStmt);
 }
u32_Return = sqlite3_finalize(pst_PlayListPrepareStmt);
u32_ PlayListRecordCount = u32_ PlayListRecordCount +
gu32_PlayListindex;
   }
  }
  else
  {
   return SQLITE_DB_ERROR;
  }
 }

Am I doing right or it's a lengthy process.If so suggest some other way for
inserting the records.
 

But  this takes very long time to insert all the Tracks into
playlist.Totally I need to insert 4 records.

 

Is there anyother way to insert huge number of data in to another table..

I want to insert  each record after getting the Track from MUSIC select
statement.Can we use Triggers for solving this.

Can anyone help to solve this .

 

Thanks & Regards,

Mahalakshmi.M


 

 

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

Re: [sqlite] Help with sqlite3_blob_open()

[A.J.Millan]

That work!

Thanks a lot.

Adolfo.

- Original Message - 
From: <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Monday, March 24, 2008 1:48 PM
Subject: Re: [sqlite] Help with sqlite3_blob_open()


> "A.J.Millan" <[EMAIL PROTECTED]> wrote:
>> Hello all:
>>
>> Using Windows XP:
>>
>> D:\Z\Zator5>sqlite3 zdb1
>> SQLite version 3.5.4
>> Enter ".help" for instructions
>> sqlite> .tables
>> AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
>> AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
>> AgEfKlv AgPdIDt AgVtPre FreqUse blb
>>
>> sqlite> .schema blb
>> CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
>> sqlite>
>>
>> I'm having troubles with this pseudo code:
>>
>> > begin transaction
>>
>> > some successful operations on tables atm and blb
>>
>> > sqlite3_blob* pBlob;
>>
>> > // open Blob for read/write access
>> > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1,
>> > );
>>
>> Here res = 1, and I get the following error:
>>
>> SQL error: no such table: D:\Z\Zator5\zDB1.blb
>>
>
> The "database name" is not the same thing as the name of the
> file tht contains the database.  The database name is the name
> assigned to the database when you ATTACH it.  Or, for the original
> database, then name is "main".  Or for TEMP tables, the name is
> "temp".
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Help with sqlite3_blob_open()

[drh]

"A.J.Millan" <[EMAIL PROTECTED]> wrote:
> Hello all:
> 
> Using Windows XP:
> 
> D:\Z\Zator5>sqlite3 zdb1
> SQLite version 3.5.4
> Enter ".help" for instructions
> sqlite> .tables
> AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
> AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
> AgEfKlv AgPdIDt AgVtPre FreqUse blb
> 
> sqlite> .schema blb
> CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
> sqlite>
> 
> I'm having troubles with this pseudo code:
> 
> > begin transaction
> 
> > some successful operations on tables atm and blb
> 
> > sqlite3_blob* pBlob;
> 
> > // open Blob for read/write access
> > int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, 
> > );
> 
> Here res = 1, and I get the following error:
> 
> SQL error: no such table: D:\Z\Zator5\zDB1.blb
> 

The "database name" is not the same thing as the name of the
file tht contains the database.  The database name is the name
assigned to the database when you ATTACH it.  Or, for the original
database, then name is "main".  Or for TEMP tables, the name is
"temp".

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] Help with sqlite3_blob_open()

[A.J.Millan]

Hello all:

Using Windows XP:

D:\Z\Zator5>sqlite3 zdb1
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .tables
AgEfHolder  AgEfemerAgVtHolder  AgVtos  Usr lnk
AgEfIDt AgPdHolder  AgVtIDt Block   atm prm
AgEfKlv AgPdIDt AgVtPre FreqUse blb

sqlite> .schema blb
CREATE TABLE 'blb' (ID INTEGER PRIMARY KEY, Nm INTEGER);
sqlite>

I'm having troubles with this pseudo code:

> begin transaction

> some successful operations on tables atm and blb

> sqlite3_blob* pBlob;

> // open Blob for read/write access
> int res = sqlite3_blob_open (dbPtr, "D:\\Z\\zDB1", "blb", "nm", Id, 1, 
> );

Here res = 1, and I get the following error:

SQL error: no such table: D:\Z\Zator5\zDB1.blb

Neither I have luck with:

> int res = sqlite3_blob_open (dbPtr, "zDB1", "blb", "nm", Id, 1, );

D:\Z\ is my actual (working) directory

Do is there some that I'm missing or misunderstood?

Thanks for any idea.

Adolfo.

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

Re: [sqlite] Is it safe to ...

[drh]

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello,
> Is it safe to use this algorithm:
> 
> open_db
> fork()
> sql_do() // both parent and child executes sql statements
> close_db
> 
> I am not familiar with locking mechanism and I am afraid that if
> parent and child will use the same DB handlers it can cause of DB
> corruptions
> 

It is not safe to carry an open SQLite database connection
across a fork.  The documentation says this somewhere, IIRC,
but I don't remember exactly where.  I should probably state
this fact in the documentation for sqlite3_open()...

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] Command to create a database in sqlite

[Igor Tandetnik]

"Mozaharul Haque" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> A silly asking. I found the command to create a table but what about
> to create a database?

Just give a file name to sqlite3_open, then create a table. If the file 
didn't exist, it will be created at this point.

Igor Tandetnik 



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

[sqlite] Help needed: using sqlite with j2me (MIDP)

[Amit Akela]

  
Hi,

I am trying to compile sqlite with a java program (basically for J2ME - MIDP 
application). for this purpose I have downloaded the amalgamation version of 
the code (3.5.x). But There are some problems.

1.  how to mix a C/C++ code with j2me code.
2.  how do i provide the java.sql.* classes in a j2me program because the 
j2me libraries does not support java.sql. If any body has done this 
then please help me.


With Regards

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

[sqlite] Command to create a database in sqlite

[Mozaharul Haque]

Hi,

A silly asking. I found the command to create a table but what about to
create a database?

The table would be stored in the sqlite_master table.

And how do I refer (full path) to the database  using application like
Basic4ppc 6.05.


Please help.


regards,

Mozaharul Haque

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