Re: [sqlite] possible bug in overuse of statement journal

[Jeremy Spiegel]

> For example, if a table has multiple columns with UNIQUE indexes.
But in my case, the table doesn't have any UNIQUE indexes, so it seems that the 
REPLACE statement can't affect more than one row.  This is something that 
sqlite3GenerateConstraintChecks could check.

> The fact that the situation goes away when you get rid of the second NOT NULL 
> is interesting.
I think the situation goes away when I get rid of the NOT NULL because then the 
statement can't be aborted.

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

Re: [sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

On Thu, 18 Feb 2010, P Kishor wrote:

> SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance
> FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid
> WHERE l.llid = '1226038453652'
> GROUP BY l.llid, l.name

   Thank you. Now I know.

Much appreciated,

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

Re: [sqlite] Aggregate From Two-Table SELECT

[P Kishor]

On Thu, Feb 18, 2010 at 1:06 AM, Rich Shepard  wrote:
>   I'd appreciate learning how to correctly write a SELECT statement that
> reports the SUM of one returned column.
>
>   I can select all relevant rows, but don't know where to put the
> SUM(distance) phrase:
>
>   SELECT l.llid, l.name, s.endKM - s.beginKM AS distance
>        FROM lotic AS l, streamlength AS s
>                WHERE l.llid = s.llid and l.llid = '1226038453652';
>
>   I would like SQL to do the work of summing the derived column 'distance'
> for me.
>

SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance
FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid
WHERE l.llid = '1226038453652'
GROUP BY l.llid, l.name



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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Brussels, Brussels-Capital Region, Belgium
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

   I'd appreciate learning how to correctly write a SELECT statement that
reports the SUM of one returned column.

   I can select all relevant rows, but don't know where to put the
SUM(distance) phrase:

   SELECT l.llid, l.name, s.endKM - s.beginKM AS distance
FROM lotic AS l, streamlength AS s
WHERE l.llid = s.llid and l.llid = '1226038453652';

   I would like SQL to do the work of summing the derived column 'distance'
for me.

Rich

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

Re: [sqlite] Anyone able to access a SQLite database within a Tclstarpack?

[Matthew Smith]

Alexey,

 

that gives me a Tcl VFS inside an SQLite database. I'm looking for the reverse: 
I'd like to access an SQLite database within a Tclkit--i.e., within a Tcl VFS.

Is that possible? It seems that SQLite itself cannot "find" the database, even 
though a normal file command can see it, given the same path.
 
> From: pechni...@mobigroup.ru
> To: sqlite-users@sqlite.org
> Date: Wed, 17 Feb 2010 22:51:21 +0300
> Subject: Re: [sqlite] Anyone able to access a SQLite database within a 
> Tclstarpack?
> 
> Hello!
> 
> On Wednesday 17 February 2010 05:57:51 Matthew Smith wrote:
> > SQLiteVFS a C-language routine for access any file system, on which the
> > SQLite database is found. Doesn't appear to give me access to the Tcl VFS.
> 
> No, you may search vfs::sqlite3 package for Tcl.
> 
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
View photos of singles in your area! Browse profiles for FREE
http://clk.atdmt.com/NMN/go/150855801/direct/01/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Anyone able to access a SQLite database within a Tclstarpack?

[Alexey Pechnikov]

Hello!

On Wednesday 17 February 2010 05:57:51 Matthew Smith wrote:
> SQLiteVFS a C-language routine for access any file system, on which the
> SQLite database is found. Doesn't appear to give me access to the Tcl VFS.

No, you may search vfs::sqlite3 package for Tcl.

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

[sqlite] Memory usage – one data base versus tw o smaller ones

[a1rex]

For some reasons it is more convenient for the project to
have a few smaller databases with unrelated data than one containing
everything.  My only concern is RAM memory. 
How much burden/memory overhead an additional database would introduce?
 
Thank you for your input,

Samuel


  __
Ask a question on any topic and get answers from real people. Go to Yahoo! 
Answers and share what you know at http://ca.answers.yahoo.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Two columns in one index, or one column for each index?

[Tim Romano]

You might also see how well INTERSECT performs on your Latitude/Longitude 
query. Put an index on (float) LAT and another on (float) LON and use an 
INTEGER primary key in MYTABLE.

select * from MYTABLE
JOIN
(
select id from MYTABLE
where 
(lat >= 30  and lat <= 33)
INTERSECT
select id from MYTABLE
where
(lon >=-80 and lon <= -55)
) as IDLIST
on IDLIST.id = MYTABLE.id


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

Re: [sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory

[Simon Slavin]

On 17 Feb 2010, at 3:44pm, D. Richard Hipp wrote:

> On Feb 17, 2010, at 6:52 AM, Hick Gunter wrote:
> 
>> Ok I went back to square one and retrieved the amalgamation source  
>> from the recommended distribution. It is still failing at the  
>> "reduce" step before the "shift 627", which is where the  
>> sqlite3Attach() function is called.
>> 
>> [sgi...@sgtxe1 ~]$ asql
>> SQLite version 3.6.22
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> attach 'test.db' as test;
>> Error: unable to resolve operation
>> sqlite>
> 
> The error message text "unable to resolve operation" occurs nowhere in  
> the SQLite source code.

Actually, it's a Java error message.  I don't see any way for SQLite to trigger 
it directly.

So Gunter, how is Java involved in the setup you are debugging under ?  Are you 
perhaps running under a Java virtual machine or using netbeans ?  And can you 
try it again without the Java component ?

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

[sqlite] BUG in FTS3 offsets() function in 3.6.22+ version

[Alexey Pechnikov]

Hello!

See:

sqlite> select snippet(file_text) as offsets from file_text where 
file_text.rowid=7836 and file_text match 'mobigroup';
...://offline.mts.mobigroup.ru/ )
2. Выбрать...
sqlite> select offsets(file_text) as offsets from file_text where 
file_text.rowid=7836 and file_text match 'mobigroup';
Error: database disk image is malformed
sqlite> vacuum;
sqlite> pragma integrity_check;
ok
sqlite> select offsets(file_text) as offsets from file_text where 
file_text.rowid=7836 and file_text match 'mobigroup';
Error: database disk image is malformed

sqlite> select sqlite_version();
3.6.22
sqlite> select sqlite_source_id();
2010-02-04 07:12:10 4ce0faf5b282a64938f11c8541ca47d9c852ecee

Builded from repository http://sqlite.mobigroup.ru/src/home

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

Re: [sqlite] SQLite 3.6.22 ATTACH no longer works for files outside the current working directory

[D. Richard Hipp]

On Feb 17, 2010, at 6:52 AM, Hick Gunter wrote:

> Ok I went back to square one and retrieved the amalgamation source  
> from the recommended distribution. It is still failing at the  
> "reduce" step before the "shift 627", which is where the  
> sqlite3Attach() function is called.
>
>
> [sgi...@sgtxe1 ~]$ asql
> SQLite version 3.6.22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach 'test.db' as test;
> Error: unable to resolve operation
> sqlite>
>


The error message text "unable to resolve operation" occurs nowhere in  
the SQLite source code.  And nobody else is able to reproduce this  
problem.  So it seems likely to us that you are using adulterate  
source code.  Please try again with fresh, unaltered SQLite source  
files.


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] possible bug in overuse of statement journal

[Jay A. Kreibich]

On Wed, Feb 17, 2010 at 12:35:11AM -0800, Jeremy Spiegel scratched on the wall:

> In reading http://www.sqlite.org/tempfiles.html, it sounds like a
> statement journal file should only be created for statements that
> might change multiple rows and which might abort.  It should be
> possible for sqlite to determine that the above "replace into" would
> only affect one row, since it doesn't have any foreign key constraints
> pointing into it.  Does this represent a bug in sqlite, or is it
> just perhaps being overly conservative?

  While I'm not disputing this specific situation, I would point out
  that it doesn't require a foreign key to have a REPLACE statement
  delete more than one row before inserting the new row.
  
  For example, if a table has multiple columns with UNIQUE indexes
  (either automatic from a UNIQUE constraint or manually created),
  and the new row conflicts with different rows on different columns,
  then all conflicting rows will be deleted before the insert is
  allowed to happen.

  The fact that the situation goes away when you get rid of the second
  NOT NULL is interesting.  I wonder if there is some logic that
  activates the multi processing if it detects more than one column
  with any kind of constraint, and not specifically a constraint that
  might cause a conflict on insert.  If this is more of a general
  purpose code path that is used for other things, that might be the
  correct thing to do.
  
   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug in overuse of statement journal

[Jeremy Spiegel]

The following seems to cause a statement journal to be used on every "replace 
into" statement:

> create table T (col1 integer primary key not null, col2 integer not null );
> begin transaction;
> replace into T (col1, col2) values (1,2);
> replace into T (col1, col2) values (3,4);
> ...
> commit transaction;

This is causing a performance problem in my application, which seems to have 
been introduced when upgrading from 3.6.18 to 3.6.19.  It appears that in 
check-in 3f40c142c8 to insert.c, we now call sqlite3MultiWrite to "ensure 
foreign key related processing takes place when rows are deleted from the 
database by REPLACE conflict handling".  It seems like we are just assuming 
that we may hit a foreign key constraint when doing the replace, instead of 
actually checking to see if any foreign key constraints exist.  I can make the 
problem go away by commenting out the sqlite3MultiWrite call, or by removing 
the "not null" constraint on col2.

In reading http://www.sqlite.org/tempfiles.html, it sounds like a statement 
journal file should only be created for statements that might change multiple 
rows and which might abort.  It should be possible for sqlite to determine that 
the above "replace into" would only affect one row, since it doesn't have any 
foreign key constraints pointing into it.  Does this represent a bug in sqlite, 
or is it just perhaps being overly conservative?

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