Re: [sqlite] possible bug in overuse of statement journal
> 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
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
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
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?
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?
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
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?
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
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
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
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
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
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