Re: [sqlite] speeding up row by row lookup in a large db
>> Just wanted to say publicly that DBD::SQLite is the greatest thing >> since, well, SQLite. Thanks for making our lives easy. > > +1 $count++; from me, too. We talk so much about speed and versions only because we use DBD::SQLite so heavily. BTW, I've switched to the amalgamation package, with SQLite updated to 3.6.11 manually. Works very well so far, though I haven't tried callbacks on the Mac so far (where the test suite segfaults). Best, Stefan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Step Query
Hi,
I am new to sqlite and C.
I have not been able to write a code which would read row by row using
sqlite3_step.
Could anybody guide me please.
Dan Kennedy-4 wrote:
>
> On Tue, 2007-06-19 at 10:58 +0530, anand chugh wrote:
>> Hi
>>
>> I am having code like this:
>>
>>rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
>>if( rc!=SQLITE_OK ){
>> return rc;
>>}
>>sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
>>sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);
>>
>>while( sqlite3_step(pStmt)==SQLITE_ROW )
>> {
>> *pnBlob = sqlite3_column_bytes(pStmt, 0);
>> *pzBlob = (unsigned char *)malloc(*pnBlob);
>> memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);
>>}
>>
>> sqlite3_finalize(pStmt);
>>
>> My question here is do I need to do sqlite3_finalize(pStmt); after
>> every sqlite3_step() to free all memory allocated by
>> sqlite3_step().
>
> No. Exactly one sqlite3_finalize() for each sqlite3_prepare(). In
> this respect the code above is fine.
>
> It's not SQLite related, but if the SQL statement returns more
> than one row, the malloc() in the while loop will cause a memory
> leak.
>
> Dan.
>
>> Does calling finalize at end will free all memory
>> allocated by all steps statements?
>>
>> Example shown http://www.sqlite.org/cvstrac/wiki?p=BlobExample does
>> same , it calls finalize after every step.
>>
>> My Program shows some Memory Leaks(Virtual Bytes).
>>
>> Please clarify.
>>
>> Anand
>>
>> -
>> To unsubscribe, send email to sqlite-users-unsubscr...@sqlite.org
>> -
>>
>
>
> -
> To unsubscribe, send email to sqlite-users-unsubscr...@sqlite.org
> -
>
>
>
--
View this message in context:
http://www.nabble.com/Step-Query-tp11188705p22677241.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
Re: [sqlite] problems with shared cache?
-Original Message- On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald wrote: > >> However, when I ask the user to send me their deck, I find that: >> >> sqlite> pragma integrity_check; >> integrity_check >> --- >> ok >> sqlite> select id, count(id) from cards group by id having >> count(id) >>> 1; >> sqlite> >> >> Any ideas? > > Obviously, that user is not playing with a full deck. ;-) > = From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes Sent: Tuesday, March 24, 2009 2:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] problems with shared cache? Sorry, my application's files are called decks, and I unwittingly used the wrong terminology. Any ideas about the problem? Damien, I, if anyone, should apologize -- was just an attempt at humor using an English language idiom metaphor for card games. I'm afraid I don't know enough to help with your shared cache problem (posted 19 March), sorry. It's fortunate, I suppose, that your particular application didn't really need it. Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
Not sure if it will make a difference, but in your trigger stuff you explicitly coded null for the primary key value. Have you tried changing that so that you don't specify the primary key field at all? I can't remember from the previous post, but I think it was (or should be) set up as autoincrement. I think SQLite allows using multiple nulls for the primary key, but according to their docs, it is non-standard and it says something about "this may change in the future". Maybe you are getting caught in the middle of a change that is going to occur across multiple revisions of SQLite. Jim On 3/24/09, Damien Elmes wrote: > Sorry, my application's files are called decks, and I unwittingly used > the wrong terminology. > > Any ideas about the problem? > > On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald > wrote: >> >>> However, when I ask the user to send me their deck, I find that: >>> >>> sqlite> pragma integrity_check; >>> integrity_check >>> --- >>> ok >>> sqlite> select id, count(id) from cards group by id having >>> count(id) 1; >>> sqlite> >>> >>> Any ideas? >> >> Obviously, that user is not playing with a full deck. ;-) >> >> >> ___ >> 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 > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question about shared cache
Hello list I have been trying to understand how shared cache works under the covers, and how a custom VFS should behave when shared cache is enabled - can anyone confirm this assumption: Journal file handles (opened with SQLITE_OPEN_EXCLUSIVE) are shared between database connections opened on the same DB filename. Access to a journal file handle is serialised through the BTShared mutex of the owning database - there is no explicit locking performed on journal files. Cheers, Dave. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accent Insensitive search
Hi all, Is there a way to do an accent/diacritic insensitive search in sqlite? For example I want to a query to find " `a blanc " with the search term "a blanc". Thanks ... -deech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about shared cache
On Mar 25, 2009, at 12:09 AM, Dave Toll wrote: > Hello list > > > > I have been trying to understand how shared cache works under the > covers, and how a custom VFS should behave when shared cache is > enabled > - can anyone confirm this assumption: > > > > Journal file handles (opened with SQLITE_OPEN_EXCLUSIVE) are shared > between database connections opened on the same DB filename. Access > to a > journal file handle is serialised through the BTShared mutex of the > owning database - there is no explicit locking performed on journal > files. That is correct. SQLite will serialize calls on a single file-handle (sqlite3_file*). You do not need a mutex for each file-handle in the OS layer. Dan. > > > > > Cheers, > > Dave. > > > > ___ > 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] UNION with results distinct on a particular column?
Hi, I'm hoping someone here can help me out with a query. I have multiple tables, each with the same schema. For example: = Table A: = 1|"xxx" 2|"yyy" 3|"zzz" = = Table B: = 1|"xxx222" 3|"zzz222" 5|"www" = I'd like a SELECT statement that yields: = Result: = 1|"xxx" 2|"yyy" 3|"zzz" 5|"www" = In other words, I want the UNION of all the input tables, but if there are multiple results that have the same value in the first column, the first table's value should take precedence. This seems like a common scenario, so I'm probably missing something trivial. :) But so far, the only way I've figured out to do this is with something like: SELECT * FROM (SELECT 1 AS precedence, col1, col2 FROM A UNION SELECT 2 AS precedence, col1, col2 FROM B ORDER BY col1 ASC, precedence DESC) GROUP BY precedence ORDER BY col1 ASC; (Just an example, I've got several other columns that have to be sorted on, and there can be any number of tables). This seems to do what I want, but it takes an order of magnitude longer than the inner SELECTs do on their own (i.e. without the GROUP BY which eliminates rows with duplicate 'col1' values). Any ideas on how I could do this more efficiently? Thanks! -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UNION with results distinct on a particular column?
Two possible solutions:
A) pure sql ... (warning: untested) Start with all the possible col1
values, and then left join to the other tables, and pick the first
col2 found.
select col1,
coalesce( A.col2, B.col2, C.col2 ) as col2
from (
select distinct col1 from (
select col1 from A
union
select col1 from B
union
select col1 from C
) as u
) as tmp
left join A using (col1)
left join B using (col1)
left join C using (col1)
order by col1
;
B) This is potentially very easy at the application level ... here's a
perl/DBI example (also untested):
my %pairs = map {
%{ $dbh->selectall_hashref("select col1, col2 from $_", 'col1') }
} reverse qw/ A B C /;
# This next line is optional, if you want the hash values to be col2's
instead of hashrefs:
$_=$_->{col2} for values %pairs;
--david
On Tue, Mar 24, 2009 at 5:36 PM, Matthew L. Creech wrote:
> Hi,
>
> I'm hoping someone here can help me out with a query. I have multiple
> tables, each with the same schema. For example:
>
> =
> Table A:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> =
>
> =
> Table B:
> =
> 1|"xxx222"
> 3|"zzz222"
> 5|"www"
> =
>
> I'd like a SELECT statement that yields:
>
> =
> Result:
> =
> 1|"xxx"
> 2|"yyy"
> 3|"zzz"
> 5|"www"
> =
>
> In other words, I want the UNION of all the input tables, but if there
> are multiple results that have the same value in the first column, the
> first table's value should take precedence.
>
> This seems like a common scenario, so I'm probably missing something
> trivial. :) But so far, the only way I've figured out to do this is
> with something like:
>
> SELECT * FROM
> (SELECT 1 AS precedence, col1, col2 FROM A UNION
> SELECT 2 AS precedence, col1, col2 FROM B
> ORDER BY col1 ASC, precedence DESC)
> GROUP BY precedence
> ORDER BY col1 ASC;
>
> (Just an example, I've got several other columns that have to be
> sorted on, and there can be any number of tables). This seems to do
> what I want, but it takes an order of magnitude longer than the inner
> SELECTs do on their own (i.e. without the GROUP BY which eliminates
> rows with duplicate 'col1' values). Any ideas on how I could do this
> more efficiently?
>
> Thanks!
>
> --
> Matthew L. Creech
> ___
> 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] Step Query
vinod1 wrote:
> I am new to sqlite and C.
>
> I have not been able to write a code which would read row by row using
> sqlite3_step.
>
> Could anybody guide me please.
>
>
Hi,
This code is equivalent to the very old callback style code shown at
http://www.sqlite.org/quickstart.html.
It should provide the same results using the newer prepare/step/finalize
set of calls that are discussed at http://www.sqlite.org/cintro.html.
Hopefully it provides a complete, if somewhat basic, intro to the use of
the preferred C API functions.
#include
#include
int main(int argc, const char *argv[]){
sqlite3 *db;
sqlite3_stmt *stmt;
int rc = 0;
int col, cols;
if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
}else{
// open the database file
rc = sqlite3_open(argv[1], &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
}else{
// prepare the SQL statement from the command line
rc = sqlite3_prepare_v2(db, argv[2], -1, &stmt, 0);
if( rc ){
fprintf(stderr, "SQL error: %d : %s\n", rc, sqlite3_errmsg(db));
}else{
cols = sqlite3_column_count(stmt);
// execute the statement
do{
rc = sqlite3_step(stmt);
switch( rc ){
case SQLITE_DONE:
break;
case SQLITE_ROW:
// print results for this row
for( col=0; colhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
On 25/03/2009 1:16 AM, Griggs, Donald wrote: > > > -Original Message- > > > On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald > wrote: >>> However, when I ask the user to send me their deck, I find that: >>> >>> sqlite> pragma integrity_check; >>> integrity_check >>> --- >>> ok >>> sqlite> select id, count(id) from cards group by id having >>> count(id) 1; >>> sqlite> >>> >>> Any ideas? >> Obviously, that user is not playing with a full deck. ;-) >> > > = > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes > Sent: Tuesday, March 24, 2009 2:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] problems with shared cache? > > Sorry, my application's files are called decks, and I unwittingly used the > wrong terminology. > > Any ideas about the problem? > > > Damien, > > I, if anyone, should apologize -- was just an attempt at humor using an > English language idiom metaphor for card games. Presumably the terminology "decks" refers to decks of punched cards. It might have been more pertinent to remark on the presumed longevity of the application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problems with shared cache?
No, it's a flashcard application. http://ichi2.net/anki/ On Wed, Mar 25, 2009 at 9:30 AM, John Machin wrote: > On 25/03/2009 1:16 AM, Griggs, Donald wrote: >> >> >> -Original Message- >> >> >> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald >> wrote: However, when I ask the user to send me their deck, I find that: sqlite> pragma integrity_check; integrity_check --- ok sqlite> select id, count(id) from cards group by id having count(id) > 1; sqlite> Any ideas? >>> Obviously, that user is not playing with a full deck. ;-) >>> >> >> = >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Damien Elmes >> Sent: Tuesday, March 24, 2009 2:52 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] problems with shared cache? >> >> Sorry, my application's files are called decks, and I unwittingly used the >> wrong terminology. >> >> Any ideas about the problem? >> >> >> Damien, >> >> I, if anyone, should apologize -- was just an attempt at humor using an >> English language idiom metaphor for card games. > > Presumably the terminology "decks" refers to decks of punched cards. It > might have been more pertinent to remark on the presumed longevity of > the application. > > > > ___ > 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] problems with shared cache?
I can define the primary key column as not null if you think that will help, but dumping the table reveals the ids are being assigned sequential integers. On Tue, Mar 24, 2009 at 11:34 PM, Jim Wilcoxson wrote: > Not sure if it will make a difference, but in your trigger stuff you > explicitly coded null for the primary key value. Have you tried > changing that so that you don't specify the primary key field at all? > I can't remember from the previous post, but I think it was (or should > be) set up as autoincrement. > > I think SQLite allows using multiple nulls for the primary key, but > according to their docs, it is non-standard and it says something > about "this may change in the future". Maybe you are getting caught > in the middle of a change that is going to occur across multiple > revisions of SQLite. > > Jim > > > On 3/24/09, Damien Elmes wrote: >> Sorry, my application's files are called decks, and I unwittingly used >> the wrong terminology. >> >> Any ideas about the problem? >> >> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald >> wrote: >>> However, when I ask the user to send me their deck, I find that: sqlite> pragma integrity_check; integrity_check --- ok sqlite> select id, count(id) from cards group by id having count(id) > 1; sqlite> Any ideas? >>> >>> Obviously, that user is not playing with a full deck. ;-) >>> >>> >>> ___ >>> 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 >> > > > -- > Software first. Software lasts! > ___ > 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
