[sqlite] ticket 3007
Any thoughts on ticket 3007, to disable journalling by passing an omit journal flag to the sqlite3_open_v2 interface? This would have the I/O load for writes and probably double the througput. http://www.sqlite.org/cvstrac/tktview?tn=3007 Thanks, Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3StrICmp
On 04-Apr-2008, at 2:15 PM, Nicolas Williams wrote: > Right, except for the thing about multiple columns with the same name > being OK. "AS" >> 2. I need to use stricmp for comparing column names. I'd rather use >> the same comparison that sqlite3 uses for comparing column NAMES. > > Why can't you use strcasecmp()? Nothing, if you can guarantee me that: strcasecmp( a, b ) == sqlite3StrICmp( a, b ) and stricmp( a, b ) == sqlite3StrICmp( a, b ) ...for all a, and all b, in the present version of sqlite3 and for all future versions of sqlite3, for all our current platforms and all platforms we'll ever deploy to. Otherwise, I'd rather use the same code sqlite3 uses. > IMO a SQLite-specific version of strcasecmp() is only really > valuable if > it can deal with user-defined collations. Otherwise what's the point? > You already have straight strcasecmp() implementations elsewhere (even > ones aware of UTF-8 and UTF-16). See above. I'm not discounting the value of a comparison that deals with collations at all. That would be very useful. It's just not what I'm currently accessing sqlite3 internals for. I'm currently accessing them to find named columns. (I'd rather have a linker error than changed behaviour.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 01:43:24PM -0700, Steven Fisher wrote:
> On 04-Apr-2008, at 1:17 PM, Nicolas Williams wrote:
> > Sure there is:
> >
> >const char *sqlite3_column_decltype(sqlite3_stmt*,int);
> >int sqlite3_column_type(sqlite3_stmt*, int iCol);
>
> This would be useful but, again, that's not at all what I want. I'm
> looking for column NAMES, not contents.
const char *sqlite3_column_name(sqlite3_stmt*, int N);
const void *sqlite3_column_name16(sqlite3_stmt*, int N);
> Maybe it'd be better to explain this with psuedo code.
>
> This is what I want to do:
>
>sqlite3_prepare_v2( db, "SELECT ColumnA,ColumnB FROM ATable;", -1,
> , );
>int column_a_idx = sqlite3_column_index( stmt, "ColumnA" );
>int column_b_idx = sqlite3_column_index( stmt, "ColumnB" );
>while ( sqlite3_step( db ) == SQLITE_ROW ) {
> sqlite3_column_text( stmt, column_a_idx, avalue );
> sqlite3_column_text( stmt, column_b_idx, bvalue );
>}
>sqlite3_fianlize( stmt );
You can have multiple columns with the same name:
sqlite> create table foo(a,b);
sqlite> insert into foo values (1, 2);
sqlite> .header on
sqlite> select a as c, b as c from foo;
c|c
1|2
sqlite>
So sqlite3_column_index(stmt, "ColumnA") isn't likely to be what you
really want. sqlite3_column_name() seems much more appropriate.
> I'm avoiding hard an expectation here that column a is in position 0,
> and column b in position 1. This doesn't matter for such a simple
> query, but for larger queries future proofing the code from changes to
> queries is just good practice.
>
> This code won't run, though, because sqlite3_column_index doesn't
> exist. I need to write my own. That means I need to replace
> sqlite3_column_index with find_column, which is defined something like
> this:
>
> int find_column( sqlite3_stmt * stmt, const char * name )
> {
>int count = sqlite3_column_count( stmt );
>for ( int i = 0; i < count; i++ ) {
> const char * column = sqlite3_column_name( stmt, i );
> if ( stricmp( column, name ) == 0 )
>return i;
>}
>return -1;
> }
Right, except for the thing about multiple columns with the same name
being OK.
> There's two problems here:
> 1. I need to define something to find the column at all. There's a way
> to find binding indexes by name, so why not columns? I understand the
> need to avoid code bloat, but surely a way to future proof code by not
> having to hard-coding column positions is worth the size delta.
See above.
> 2. I need to use stricmp for comparing column names. I'd rather use
> the same comparison that sqlite3 uses for comparing column NAMES.
Why can't you use strcasecmp()?
IMO a SQLite-specific version of strcasecmp() is only really valuable if
it can deal with user-defined collations. Otherwise what's the point?
You already have straight strcasecmp() implementations elsewhere (even
ones aware of UTF-8 and UTF-16).
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 04-Apr-2008, at 1:17 PM, Nicolas Williams wrote:
> On Fri, Apr 04, 2008 at 01:06:58PM -0700, Steven Fisher wrote:
>>> It's not necessarily the same as strcasecmp(). You can have per-
>>> column collations.
>>
>> Column names, not column contents. :) I don't like to have my C code
>> rely on the order of columns from a query. You can avoid depending on
>> parameter ordering with sqlite3_bind_parameter_index, but there
>> doesn't seem to be an equivalent for result columns.
>
> Sure there is:
>
>const char *sqlite3_column_decltype(sqlite3_stmt*,int);
>int sqlite3_column_type(sqlite3_stmt*, int iCol);
This would be useful but, again, that's not at all what I want. I'm
looking for column NAMES, not contents.
Maybe it'd be better to explain this with psuedo code.
This is what I want to do:
sqlite3_prepare_v2( db, "SELECT ColumnA,ColumnB FROM ATable;", -1,
, );
int column_a_idx = sqlite3_column_index( stmt, "ColumnA" );
int column_b_idx = sqlite3_column_index( stmt, "ColumnB" );
while ( sqlite3_step( db ) == SQLITE_ROW ) {
sqlite3_column_text( stmt, column_a_idx, avalue );
sqlite3_column_text( stmt, column_b_idx, bvalue );
}
sqlite3_fianlize( stmt );
I'm avoiding hard an expectation here that column a is in position 0,
and column b in position 1. This doesn't matter for such a simple
query, but for larger queries future proofing the code from changes to
queries is just good practice.
This code won't run, though, because sqlite3_column_index doesn't
exist. I need to write my own. That means I need to replace
sqlite3_column_index with find_column, which is defined something like
this:
int find_column( sqlite3_stmt * stmt, const char * name )
{
int count = sqlite3_column_count( stmt );
for ( int i = 0; i < count; i++ ) {
const char * column = sqlite3_column_name( stmt, i );
if ( stricmp( column, name ) == 0 )
return i;
}
return -1;
}
There's two problems here:
1. I need to define something to find the column at all. There's a way
to find binding indexes by name, so why not columns? I understand the
need to avoid code bloat, but surely a way to future proof code by not
having to hard-coding column positions is worth the size delta.
2. I need to use stricmp for comparing column names. I'd rather use
the same comparison that sqlite3 uses for comparing column NAMES.
The first problem could be fixed by adding sqlite3_column_index, the
second by adding sqlite3_stricmp. The first would (probably?) increase
the size of sqlite3 slightly, the second would only make an internal
function publicly available.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 01:06:58PM -0700, Steven Fisher wrote: > > It's not necessarily the same as strcasecmp(). You can have per- > > column collations. > > Column names, not column contents. :) I don't like to have my C code > rely on the order of columns from a query. You can avoid depending on > parameter ordering with sqlite3_bind_parameter_index, but there > doesn't seem to be an equivalent for result columns. Sure there is: const char *sqlite3_column_decltype(sqlite3_stmt*,int); int sqlite3_column_type(sqlite3_stmt*, int iCol); Now, you have to parse the decltype to get the collation. In any case, sqlite3StrICmp() doesn't take a collation name. So sqlite3StrICmp() would not make a good public interface. I think something like: const char *sqlite3_column_text_collation(sqlite3_stmt*, int iCol); int sqlite3_textcmp(const char *collation, const char *s1, const char *s2); int sqlite3_textcmp16(const char *collation, const char *s1, const char *s2); would be better. I think I could volunteer to write something like that (I can do copyright assignment and all that) if the project would welcome it. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 04-Apr-2008, at 12:54 PM, Nicolas Williams wrote: > On Fri, Apr 04, 2008 at 12:48:05PM -0700, Steven Fisher wrote: >> On 03-Apr-2008, at 11:22 PM, Matthew L. Creech wrote: >>> We need to either rename it so >>> that it's part of the library's exported API, or do something >>> different in tclsqlite.c. >> >> I would really like to have a few of sqlite3's internal functions >> available to client applications in a straightforward manner. >> sqlite3StrICmp is the top one on my list, though I could see >> sqlite3StrNICmp and sqlite3IsNumber being useful as well. >> >> When comparing column names in my code, for instance, it makes a lot >> of sense to be able to use the same code for comparison that sqlite3 >> uses. Sure, it's probably the same as stricmp/strcasecmp, but will it >> always be so? Probably, but it'd be more future-proof just to use the >> same code. > > It's not necessarily the same as strcasecmp(). You can have per- > column > collations. Column names, not column contents. :) I don't like to have my C code rely on the order of columns from a query. You can avoid depending on parameter ordering with sqlite3_bind_parameter_index, but there doesn't seem to be an equivalent for result columns. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 12:48:05PM -0700, Steven Fisher wrote: > On 03-Apr-2008, at 11:22 PM, Matthew L. Creech wrote: > > We need to either rename it so > > that it's part of the library's exported API, or do something > > different in tclsqlite.c. > > I would really like to have a few of sqlite3's internal functions > available to client applications in a straightforward manner. > sqlite3StrICmp is the top one on my list, though I could see > sqlite3StrNICmp and sqlite3IsNumber being useful as well. > > When comparing column names in my code, for instance, it makes a lot > of sense to be able to use the same code for comparison that sqlite3 > uses. Sure, it's probably the same as stricmp/strcasecmp, but will it > always be so? Probably, but it'd be more future-proof just to use the > same code. It's not necessarily the same as strcasecmp(). You can have per-column collations. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On 03-Apr-2008, at 11:22 PM, Matthew L. Creech wrote: > We need to either rename it so > that it's part of the library's exported API, or do something > different in tclsqlite.c. I would really like to have a few of sqlite3's internal functions available to client applications in a straightforward manner. sqlite3StrICmp is the top one on my list, though I could see sqlite3StrNICmp and sqlite3IsNumber being useful as well. When comparing column names in my code, for instance, it makes a lot of sense to be able to use the same code for comparison that sqlite3 uses. Sure, it's probably the same as stricmp/strcasecmp, but will it always be so? Probably, but it'd be more future-proof just to use the same code. I include sqliteInt.h in a small c file just sqlite3StrICmp, in fact. It would be nice not to have to do this. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(1)
I think the main hit to be avoided is in reading all of the interior and leaf pages into the page cache. Once you've done that the additional cost of actually processing the contents of those pages is going to be really small. -scott On Fri, Apr 4, 2008 at 9:48 AM, Noah Hart <[EMAIL PROTECTED]> wrote: > Questions to the SQLite maintainers... > > The docs tell us that ... > ** The page headers looks like this: > ** > ** OFFSET SIZE DESCRIPTION > ** 0 1 Flags. 1: intkey, 2: zerodata, 4: leafdata, > 8: leaf > ** 1 2 byte offset to the first freeblock > ** 3 2 number of cells on this page > > Since the count of cells in use stored in for each btree page? > Wouldn't it be pretty easy to optimize count(*) by > > > count = 0 > Btree_MOVE_TO_FIRST_ENTRY > while not Btree_END_OF_TREE > count += NUMBER_OF_ENTRIES_ON_THIS_CHILD_PAGE > Btree_MOVE_TO_NEXT_CHILD_PAGE > return count; > > > With large rows contents lengths, the savings would be minimal > However even with rows contents lengths around 100, the savings would be > 10x > > Regards -- Noah > > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess > Sent: Friday, April 04, 2008 9:15 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Count(1) > > What I meant when I said "full table scan" is that it has to read at > least something for every single row in the table. So the following > are going to be the same: > > SELECT COUNT(*) FROM t; > SELECT COUNT(rowid) FROM t; > > It won't have to scan any overflow pages, but it will have to hit all > the leaf nodes. > > You could certainly do a full scan on an index other than the rowid. > It might involve much less reading if the indexed items are small > relative to the overall row. Not sure if SQLite does this > optimization for you or not (I don't think it much matters - it's > still going to bel O(N), just with a lower constant). > > -scott > > > On Fri, Apr 4, 2008 at 8:19 AM, Samuel Neff <[EMAIL PROTECTED]> > wrote: > > Scott, > > > > Is it really a full table scan or just an index scan (at least in the > case > > where no data is needed from the table as in the original sample that > had no > > join or where clause). > > > > Thanks, > > > > Sam > > > > > > > > On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > > > > A little bit more info: SELECT COUNT(*) is implemented as a full > > > table scan, so SQLite is visiting every row in the table, which > will > > > get slower and slower as the table gets bigger and the database > > > fragments. This differs from many database engines (which > implement > > > an optimization for this) Doing the trigger thing means that it > only > > > visits the specific row that contains the count. > > > > > > -scott > > > > > > > > -- > > - > > We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) > developer. > > Position is in the Washington D.C. metro area. Contact > > [EMAIL PROTECTED] > > > > > > ___ > > 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 > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. If you > are not the addressee or authorized to receive this for the addressee, you > must not use, copy, disclose, or take any action based on this message or any > information herein. If you have received this message in error, please advise > the sender immediately by reply e-mail and delete this message. Thank you for > your cooperation. > > > > > ___ > 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] Count(1)
On Fri, Apr 04, 2008 at 09:14:52AM -0700, Scott Hess scratched on the wall: > What I meant when I said "full table scan" is that it has to read at > least something for every single row in the table. So the following > are going to be the same: > > SELECT COUNT(*) FROM t; > SELECT COUNT(rowid) FROM t; These are extremely similar, but not the same. SELECT COUNT(*) FROM t; uses a version of "count" that expects zero arguments. The core of the operation looks like this: 3 OpenRead 0 2 000 4 SetNumColumns 0 0 000 5 Rewind 0 8 000 6 AggStep0 0 1 count(0) 00 7 Next 0 6 000 8 Close 0 0 000 9 AggFinal 1 0 0 count(0) 00 10SCopy 1 2 000 11ResultRow 2 1 000 12Halt 0 0 000 In specific, the "main loop" is just AggStep and Next over and over. It just walks the B-Tree and never deals with row records. SELECT COUNT(rowid) FROM t; uses a version of "count" that expects one argument. In this case, the value of "rowid". The core of that operation looks like this: 4 OpenRead 0 2 000 5 SetNumColumns 0 0 000 6 Rewind 0 10000 7 Rowid 0 3 000 8 AggStep0 3 1 count(1) 01 9 Next 0 7 000 10Close 0 0 000 11AggFinal 1 1 0 count(1) 00 12SCopy 1 3 000 13ResultRow 3 1 000 14Halt 0 0 000 In this case there is an extra step in the loop, as the rowid is fetched and passed to count. As I understand it, that rowid value is taken directly out of the B-Tree, however, so you still don't need to read the actual row-record data. -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] Count(1)
Questions to the SQLite maintainers... The docs tell us that ... ** The page headers looks like this: ** ** OFFSET SIZE DESCRIPTION ** 0 1 Flags. 1: intkey, 2: zerodata, 4: leafdata, 8: leaf ** 1 2 byte offset to the first freeblock ** 3 2 number of cells on this page Since the count of cells in use stored in for each btree page? Wouldn't it be pretty easy to optimize count(*) by count = 0 Btree_MOVE_TO_FIRST_ENTRY while not Btree_END_OF_TREE count += NUMBER_OF_ENTRIES_ON_THIS_CHILD_PAGE Btree_MOVE_TO_NEXT_CHILD_PAGE return count; With large rows contents lengths, the savings would be minimal However even with rows contents lengths around 100, the savings would be 10x Regards -- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess Sent: Friday, April 04, 2008 9:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Count(1) What I meant when I said "full table scan" is that it has to read at least something for every single row in the table. So the following are going to be the same: SELECT COUNT(*) FROM t; SELECT COUNT(rowid) FROM t; It won't have to scan any overflow pages, but it will have to hit all the leaf nodes. You could certainly do a full scan on an index other than the rowid. It might involve much less reading if the indexed items are small relative to the overall row. Not sure if SQLite does this optimization for you or not (I don't think it much matters - it's still going to bel O(N), just with a lower constant). -scott On Fri, Apr 4, 2008 at 8:19 AM, Samuel Neff <[EMAIL PROTECTED]> wrote: > Scott, > > Is it really a full table scan or just an index scan (at least in the case > where no data is needed from the table as in the original sample that had no > join or where clause). > > Thanks, > > Sam > > > > On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > > A little bit more info: SELECT COUNT(*) is implemented as a full > > table scan, so SQLite is visiting every row in the table, which will > > get slower and slower as the table gets bigger and the database > > fragments. This differs from many database engines (which implement > > an optimization for this) Doing the trigger thing means that it only > > visits the specific row that contains the count. > > > > -scott > > > > > -- > - > We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. > Position is in the Washington D.C. metro area. Contact > [EMAIL PROTECTED] > > > ___ > 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 CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 08:48:53AM -0400, D. Richard Hipp wrote: > The way we build the TCL interface that is on the download > page is that the TCL interface code becomes part of the > amalgamation and the whole thing is compiled as a single > translation unit. I cannot imagine why anyone would want > to do it differently. I can, since I wanted to do it differently when integrating SQLite3 into Solaris. There's several reasons: 1a) Shared libraries are a way of life in Solaris (and other OSes), and avoiding object code duplication is, specifically, a goal. 1b) Patches to libsqlite3 need only patch that, not also libtclsqlite3, and even if libtclsqlite3 needs patching, the lack of object code duplication will make the patches smaller (though perhaps also less compressible). 2) In the very unlikely (but not infeasible) event that one should have an application that uses Tcl and C interfaces to access the same SQLite DB then all hell will break loose if libtclsqlite has its own internal copy of libsqlite -- there will be two distinct copies of libsqlite object code in one process accessing the same DB. 3) Bindings of SQLite3 for other languages don't have the same luxury of calling private libsqlite functions. On principle neither should the TCL bindings. If nothing else it will help keep the libtclsqlite code clean and separable. (2) is not so farfetched. I used to maintain a proprietary (now open source) product called UName*It that did just this, though with a different, also proprietary DB. The current maintainers have expressed a desire to use SQLite instead, which would result in (2) unless they did even more surgery to UName*It than they thought they'd have to. >But just yesterday I had a chat conversation > with an engineer at Novell/SuSE and he could not understand > why anybody would want to do it my way - since that would > me there were two complete copies of the SQLite library on > disk. Different strokes for different folks, I guess... We have that view as well. It's quite prevalent. The problem is that as more operating systems bundle SQLite your notion of embeddable gets tested. Does embeddable mean that the object code is statically linked into the application? Or that the DB isn't a networked DB? IMO: the latter. I strongly advise that libtclsqlite3 should have no dependencies on private interfaces in libsqlite3. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(1)
What I meant when I said "full table scan" is that it has to read at least something for every single row in the table. So the following are going to be the same: SELECT COUNT(*) FROM t; SELECT COUNT(rowid) FROM t; It won't have to scan any overflow pages, but it will have to hit all the leaf nodes. You could certainly do a full scan on an index other than the rowid. It might involve much less reading if the indexed items are small relative to the overall row. Not sure if SQLite does this optimization for you or not (I don't think it much matters - it's still going to bel O(N), just with a lower constant). -scott On Fri, Apr 4, 2008 at 8:19 AM, Samuel Neff <[EMAIL PROTECTED]> wrote: > Scott, > > Is it really a full table scan or just an index scan (at least in the case > where no data is needed from the table as in the original sample that had no > join or where clause). > > Thanks, > > Sam > > > > On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > > > A little bit more info: SELECT COUNT(*) is implemented as a full > > table scan, so SQLite is visiting every row in the table, which will > > get slower and slower as the table gets bigger and the database > > fragments. This differs from many database engines (which implement > > an optimization for this) Doing the trigger thing means that it only > > visits the specific row that contains the count. > > > > -scott > > > > > -- > - > We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. > Position is in the Washington D.C. metro area. Contact > [EMAIL PROTECTED] > > > ___ > 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] Count(1)
On Fri, Apr 04, 2008 at 11:19:53AM -0400, Samuel Neff wrote: > Is it really a full table scan or just an index scan (at least in the case > where no data is needed from the table as in the original sample that had no > join or where clause). Either way it's O(N) instead of O(1), which is what the original poster expected. Not fetching the column data will hardly help the poster. I think the trigger workaround is just fine. It might be nice if SQLite just optimized this anyways (e.g., keeping the row count in sqlite_master or some other sqlite_* table), mostly to avoid the FAQ, and to help porting to SQLite. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Fri, Apr 04, 2008 at 08:48:53AM -0400, D. Richard Hipp wrote: > This has never been a problem for the prebuilt binaries on > the website. Neither this wasn't any problem for earlier sources (including 3.5.6). > Anyway, you can fix the problem by either using the > precompiled binaries, or downloading the latest from > CVS. Thanks, the patch seems to be working. Binaries are good solution for Windows (and perhaps for Mac too) - but for Linux rarely it is the case. You know: "dependencies" - especially, when somebody prefers "stable" releases, thus having older libraries, than the ones used for pre-built binaries. -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
On Apr 4, 2008, at 11:11 AM, Dennis Cote dennis.cote-at-. | sqlite| wrote: > Why do you need two transactions in parallel? In general only one > connection can have a transaction open on a database at any time. > Locking is used to serialize transactions. Even with two connections, > you can't have two active transactions. The second will stall waiting > for the first to complete. You're right. I was thinking that using my own synchronization was suboptimal, rather than using SQLite's built-in synchronization that exists for transaction management. I was also thinking that I'd be able to process data coming in and execute the INSERTs in parallel transactions then only be blocked at the COMMIT, but of course I had forgotten about the coarse-grain locking that SQLite uses, so it wouldn't work the way I was thinking it would anyway. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(1)
On Fri, Apr 04, 2008 at 11:19:53AM -0400, Samuel Neff scratched on the wall: > Scott, > > Is it really a full table scan or just an index scan (at least in the case > where no data is needed from the table as in the original sample that had no > join or where clause). I wondered about this myself, and did some digging last night. "count(*)" is translated into a zero-argument version of "count", so no actual column data is required if it is applied directly to a whole, real table. From the VDBE codes, it looks like it is just walks the B-tree of the table (which is the same as an index scan), but never actually reads the rows. 'Next' is called, but 'Column' is not. -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] Count(1)
Scott, Is it really a full table scan or just an index scan (at least in the case where no data is needed from the table as in the original sample that had no join or where clause). Thanks, Sam On Thu, Apr 3, 2008 at 4:12 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > A little bit more info: SELECT COUNT(*) is implemented as a full > table scan, so SQLite is visiting every row in the table, which will > get slower and slower as the table gets bigger and the database > fragments. This differs from many database engines (which implement > an optimization for this) Doing the trigger thing means that it only > visits the specific row that contains the count. > > -scott > > -- - We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer. Position is in the Washington D.C. metro area. Contact [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
Fin Springs wrote:
> Is it possible to open multiple connections to an in-memory database?
>
I'm pretty sure the answer is no.
> I have an application that gets a db handle with
> sqlite3_open(":memory"). If another thread in the application were to
> make that same call, would it get the same handle, another handle to
> the same in-memory database, or a handle to a new in-memory database?
>
> So far, I have been using a mutex in my application to share the
> single handle, since concurrent accesses will be rare. However, I
> would like to be able to support 2 transactions in parallel, which I
> don't think I can do with a single connection - I get a nested
> transaction error if I try (which makes sense).
>
Why do you need two transactions in parallel? In general only one
connection can have a transaction open on a database at any time.
Locking is used to serialize transactions. Even with two connections,
you can't have two active transactions. The second will stall waiting
for the first to complete.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to an in-memory database
Is it possible to open multiple connections to an in-memory database?
I have an application that gets a db handle with
sqlite3_open(":memory"). If another thread in the application were to
make that same call, would it get the same handle, another handle to
the same in-memory database, or a handle to a new in-memory database?
So far, I have been using a mutex in my application to share the
single handle, since concurrent accesses will be rare. However, I
would like to be able to support 2 transactions in parallel, which I
don't think I can do with a single connection - I get a nested
transaction error if I try (which makes sense).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
Fin Springs <[EMAIL PROTECTED]> wrote:
> Is it possible to open multiple connections to an in-memory database?
No.
> I have an application that gets a db handle with
> sqlite3_open(":memory"). If another thread in the application were to
> make that same call, would it get the same handle, another handle to
> the same in-memory database, or a handle to a new in-memory database?
A handle to a new in-memory database.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to an in-memory database
Is it possible to open multiple connections to an in-memory database?
I have an application that gets a db handle with
sqlite3_open(":memory"). If another thread in the application were to
make that same call, would it get the same handle, another handle to
the same in-memory database, or a handle to a new in-memory database?
So far, I have been using a mutex in my application to share the
single handle, since concurrent accesses will be rare. However, I
would like to be able to support 2 transactions in parallel, which I
don't think I can do with a single connection - I get a nested
transaction error if I try (which makes sense).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update Trigger
Mahalakshmi.m wrote: > > "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT > NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));" > > ArtistId ArtistName YomiArtistName > 10bbb BBB > 11xxx XXX > 12aaa AAA > > "CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT > NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));" > > AlbumId AlbumName YomiAlbumName > 20zzz ZZZ > 21ccc CCC > 22bbb BBB > > "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT > NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id > INTEGER,AlbumArtist_Id INTEGER);" > > Id Track YomiTrack URL Album_Id Artist_Id > AlbumArtist_Id > 1 trak1 TRAK1 c:/trak1 22 10 1 > 2 song SONG c:/song 21 11 2 > 3 abcABC c:/abc23 12 3 > > Now I want to Update the AlbumName or ArtistName for all the records in > MUSIC table . How can I do.If I update All the Records to one New AlbumName > Then the rest of the AlbumName should be deleted. > If you want to update the AlbumName field, you must do that with an update statement running on the Album table, not the Music table, since that is where the AlbumName field is stored. You haven't said what you want to update the AlbumName or ArtistName to. You probably have a condition, that you also haven't described, that selects which records in the table to update. Generally it will look something like this. update Album set AlbumName = (select some_value using music table) where AlbumId in (select some records using the music table ); If this isn't what you are looking for, you will have to describe your problem in more detail (i.e. what you are trying to do, an example of before and after data, etc.) before anyone can provide more assistance. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any way to get more information than "constraint failed" when a constraint fails?
Is there any way to get more information from SQLite when a constraint
fails, particularly which field caused the constraint to fail?
Ideally the error message should list the field name that caused the
constraint to fail, the bad value, and even the constraint itself.
This is what I get...
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t(a int check(typeof(a) = 'int'));
sqlite> insert into t values ('1');
SQL error: constraint failed
sqlite>
This is what would be nice
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\>sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> create table t(a int check(typeof(a) = 'int'));
sqlite> insert into t values ('1');
SQL error: constraint failed: text value '1' failed to pass constraint
(typeof(a) = 'int')
sqlite>
I first reported this when I started working with SQLite.
http://www.sqlite.org/cvstrac/tktview?tn=2258
Thanks,
Sam
--
-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Apr 3, 2008, at 10:46 PM, Zbigniew Baniewski wrote: > I'm sorry to confirm the problem described at http://tinyurl.com/ > 29wc8x > > #v+ > $ tclsh8.5 > % package require sqlite3 > couldn't load file "/usr/lib/sqlite3/libtclsqlite3.so.0": > /usr/lib/sqlite3/libtclsqlite3.so.0: undefined symbol: sqlite3StrICmp > #v- > > Does there exist any cure? > http://www.sqlite.org/cvstrac/chngview?cn=4965 This has never been a problem for the prebuilt binaries on the website. Somebody must have built their own binary that separates the SQLite library from the TCL library and puts them in two separate shared libraries. (Perhaps the configure/make script does this.) The way we build the TCL interface that is on the download page is that the TCL interface code becomes part of the amalgamation and the whole thing is compiled as a single translation unit. I cannot imagine why anyone would want to do it differently. But just yesterday I had a chat conversation with an engineer at Novell/SuSE and he could not understand why anybody would want to do it my way - since that would me there were two complete copies of the SQLite library on disk. Different strokes for different folks, I guess... Anyway, you can fix the problem by either using the precompiled binaries, or downloading the latest from CVS. 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] Reset auto increment / truncate
Excellent. Exactly what I was looking for. Thank you very much indeed. > To: sqlite-users@sqlite.org > From: [EMAIL PROTECTED] > Date: Fri, 4 Apr 2008 07:37:17 -0400 > Subject: Re: [sqlite] Reset auto increment / truncate > > "uk webdev" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] > > I've searched high and low and I cannot find any information on how > > to reset an auto increment value to 1. There appears to be no tuncate > > command and the usual alter table command for Mysql is also invalid. > > Are you talking about INTEGER PRIMIARY KEY AUTOINCREMENT field? Run this > statement: > > delete from sqlite_sequence where name='YourTableName'; > > For more details, see http://sqlite.org/autoinc.html > -- > With best wishes, > Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Get Hotmail on your mobile. Text MSN to 63463 now! http://mobile.uk.msn.com/pc/mail.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reset auto increment / truncate
"uk webdev" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've searched high and low and I cannot find any information on how > to reset an auto increment value to 1. There appears to be no tuncate > command and the usual alter table command for Mysql is also invalid. Are you talking about INTEGER PRIMIARY KEY AUTOINCREMENT field? Run this statement: delete from sqlite_sequence where name='YourTableName'; For more details, see http://sqlite.org/autoinc.html -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reset auto increment / truncate
Hi, I've searched high and low and I cannot find any information on how to reset an auto increment value to 1. There appears to be no tuncate command and the usual alter table command for Mysql is also invalid. I have read in various places that "delete from" will reset the auto increent value although I have found this not to be the case. Surely this is possible, but how ? Any help would be greatly appreciated. Thanks David _ Amazing prizes every hour with Live Search Big Snap http://www.bigsnapsearch.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update Trigger
Hi, I am having 4 records in my database. I am using Joins method. My Table Looks like: "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));" ArtistIdArtistName YomiArtistName 10 bbb BBB 11 xxx XXX 12 aaa AAA "CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));" AlbumId AlbumName YomiAlbumName 20 zzz ZZZ 21 ccc CCC 22 bbb BBB "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id INTEGER,AlbumArtist_Id INTEGER);" Id TrackYomiTrack URL Album_Id Artist_Id AlbumArtist_Id 1 trak1TRAK1 c:/trak1 22 10 1 2 song SONG c:/song 21 11 2 3 abc ABC c:/abc23 12 3 Now I want to Update the AlbumName or ArtistName for all the records in MUSIC table . How can I do.If I update All the Records to one New AlbumName Then the rest of the AlbumName should be deleted. Can I use Update Trigger I tried but not working. Please help to solve this. Thanks & Regards, Mahalakshmi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper use of sqlite-amalgamation.
On Thu, Apr 3, 2008 at 7:11 PM, Amit <[EMAIL PROTECTED]> wrote: > > Ok that is good to know. I will play around with the source > distribution and try to figure out how to get it to work with python > 2.5. According to the python 2.5 documentation, to build Python with > sqlite3, I need the libraries plus the header files. Installing the > source distribution installs the header files as well? Or do I need to > copy them to some standard location like /usr/local/include? > Correct - from the source tarball, doing configure/make/make install will build the amalgamation into a library and install it with the headers (there are only 2). No idea about python integration, though. -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 & TCL: "undefined symbol: sqlite3StrICmp"
On Thu, Apr 3, 2008 at 10:46 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > I'm sorry to confirm the problem described at http://tinyurl.com/29wc8x > > #v+ > $ tclsh8.5 > % package require sqlite3 > couldn't load file "/usr/lib/sqlite3/libtclsqlite3.so.0": > /usr/lib/sqlite3/libtclsqlite3.so.0: undefined symbol: sqlite3StrICmp > #v- > > Does there exist any cure? Nothing simple, unfortunately. It looks like that function is built with static linkage as part of the amalgamation, so it's inaccessible to modules outside of libsqlite3.so. We need to either rename it so that it's part of the library's exported API, or do something different in tclsqlite.c. It's the only internal function this is a problem with, by the way - you can check "nm -D libtclsqlite3.so" for all 'U' (undefined) symbols. -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proper use of sqlite-amalgamation.
On Fri, 04 Apr 2008 07:42:50 +0200 Dimitri <[EMAIL PROTECTED]> wrote: > Hi, > > > I just had several questions regarding SQLite. While at the download > > page, it states that sqlite-amalgamation is the "preferred" way of > > acquiring SQLite code. So I went ahead and downloaded the latest > > version (3.5.7). I get 3 source files and I am stuck. I tried reading > > the documentation but I guess I couldn't find any mention of how to > > use this "amalgamated" source code. So I searched the mailing list and > > realized that I was simply a gcc command away. I go ahead and compile > > it using: > > There's a link in the download page: > http://www.sqlite.org/download.html > that points to: > http://www.sqlite.org/amalgamation.html > Yes I did follow that link but it didn't really provide much information on how to use the file. Amit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.5.7 Compile Fails On sqlite3.c
On Thu, Apr 3, 2008 at 7:05 PM, Robert L Cochran <[EMAIL PROTECTED]> wrote: > Here is what I did: > > tar -xvzf sqlite-3.5.7.tar.gz > cd sqlite-3.5.7 > mkdir bld > cd !$ > ../configure --prefix=/usr/local/sqlite-3.5.7 --disable-tcl > --enable-threadsafe > make > Yeah, this was reported & fixed in CVS shortly after the 3.5.7 release: http://www.sqlite.org/cvstrac/chngview?cn=4890 -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
