[sqlite] DELETEs using a range from an indexed column
I have this schema:
CREATE TABLE members
(uid INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
score INTEGER);
CREATE INDEX members_score_index ON log (score);
I want to delete the 1000 members with the lowest scores. Assume that
it is extremely unlikely for two members to have identical scores.
Also, the 1000 is arbitrary -- it could be more or less on different
days. But each time I do this deletion, it will be a fixed number.
What's the fastest way to do this? I want to lock the database for as
little time as possible. Would it be:
Method A:
* find the 1000th lowest score:
SELECT score FROM members ORDER BY score LIMIT 1 OFFSET 999;
* delete the records equal to or lower than that score
DELETE FROM members WHERE score <= $thousandth_lowest_score;
Or would it be:
Method B:
* find the uids for the 1000 lowest scores:
SELECT uid FROM members ORDER BY score LIMIT 1000;
* delete those records
DELETE FROM members WHERE uid IN ([join $uids ,]);
or:
foreach doomed_uid in $uids do:
DELETE FROM members WHERE uid = $doomed_uid
Or would it be:
Method C:
* delete the records as you find them:
sqlite3_prepare_v2(db, "DELETE FROM members WHERE uid = ?", -1, &stmt_d,
NULL);
sqlite3_prepare_v2(db, "SELECT uid FROM members ORDER BY score LIMIT 1000",
-1, &stmt_q, NULL);
while (sqlite3_step(stmt_q) == SQLITE_ROW) {
int uid = sqlite3_column_int(stmt_q, 0);
sqlite3_bind_int(stmt_d, 0, uid);
sqlite3_step(stmt_d);
sqlite3_reset(stmt_d);
}
sqlite3_finalize(stmt_d);
sqlite3_finalize(stmt_q);
Or perhaps something else entirely?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] infinite looping from sqlite3_close()
On Sep 16, 2008, at 5:19 AM, Sathish R wrote:
> Hi All,
> We are using sqlite3 (version 3.2.1) in our product and we are
> experiencing
> a problem of infinite loop from sqlite3_close(). The problem is
> intermittently reproduced and I am not sure about the exact sequence.
> I have described the details below. is anybody else faced similar
> problem
> before? Can someone please help me here?
>
> we have thread safe enabled.
>
> We collected a core when our process is stuck in infinte loop and
> one thread
> is infinitely looping in the below marked loop within sqlite3_close
> (). So,
> it holds the mutex and some other threads are blocked waiting for that
> mutex.
>
> #ifndef SQLITE_OMIT_GLOBALRECOVER
> {
> sqlite3 *pPrev = pDbList;
> sqlite3OsEnterMutex();
> while( pPrev && pPrev->pNext!=db ){ -> one thread is infinite
> looping in
> this while()
> pPrev = pPrev->pNext;
> }
> if( pPrev ){
> pPrev->pNext = db->pNext;
> }else{
> assert( pDbList==db );
> pDbList = db->pNext;
> }
> sqlite3OsLeaveMutex();
> }
> #endif
>
> I printed the pDbList from core and the list doesn't seem to end
> and the
> link list seems to have become a cyclic one somehow.
>
> (gdb) p pDbList
> $40 = (sqlite3 *) 0x36d00bb8
> (gdb) p pDbList->pNext
> $41 = (sqlite3 *) 0x107b77a8
> (gdb) p pDbList->pNext->pNext
> $42 = (sqlite3 *) 0x107b77a8 -> link list becomes cyclic...no NULL.
>
> Note: I saw that this entire logic of link list and global recovery
> doesn't
> exist in recent 3.6.2 code base. Should I consider upgrading to newer
> libsqlite library to avoid this problem?
Yes. 3.2.1 is over 3 years old now. Hundreds of bugs have been fixed
since then. Odds are that the problem you're experiencing has been
fixed,
or, if not, you stand a much better chance of getting help with it if
using 3.6.2.
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network concurrency question
I would like to use SQLite from a network share. I would like to create a server app that would do all of the writing to the database except for certain tables, one table per client,the clients would write to their own table only. The client drops it's data/instructions into it's own table, the server app would scan all client tables for new data/instructions and then write the data to the main tables of the database. Would this work without concurrency issues? Another question I have is do I understand correctly that an SQLite database, on a network share, has no problems with many readers, the problem starts with many writers. Is this correct? Thanks, TD myhosting.com - Premium Microsoft® Windows® and Linux web and application hosting - http://link.myhosting.com/myhosting ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing schema from mysql
On 9/15/08, Giorgio Sironi <[EMAIL PROTECTED]> wrote: > 2008/9/15 P Kishor <[EMAIL PROTECTED]>: > > > What is wrong with SQL? Dump the db in SQL, and import it into SQLite. > > > I'm not talking about the data, since once the database is in place > there's no need to change it; the problem is the structure: column > types, indexes, auto_increment and so on... the SQL dump is a plain text file... open it in a text editor and delete the data INSERT statements. Can't be easier than that. Else, duplicate the db in MySQL, delete all the data from the dupe, dump the empty db, and voila! Hopefully you don't have to do this everyday, so this shouldn't be too much of a bear. Keep in mind, funky MySQL things may not be supported in SQLite. > > > > -- > > Giorgio Sironi > Piccolo Principe & Ossigeno Scripter > http://www.sourceforge.net/projects/ossigeno > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3 database grows
Scott, > If you're worried about things at the level of kilobytes, > then you may be outside the core target for fts. optimize() > should work reasonably well for that size of dataset. I don't know if and about what I'm worried. I just noticed it and then thought about my poor users, that do use the software all day long with perhaps 1000 del/add actions per day and end with a 1 gb database file and wonder what is going on... And if they wonder I know what they will do to me. And I only saw it growing, the growth not documented and /me wondering, where it all would end. But main concern was that I did something wrong and had an error in my code. Case closed for me. Thanks! -- Holger Lembke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] infinite looping from sqlite3_close()
Hi All,
We are using sqlite3 (version 3.2.1) in our product and we are experiencing
a problem of infinite loop from sqlite3_close(). The problem is
intermittently reproduced and I am not sure about the exact sequence.
I have described the details below. is anybody else faced similar problem
before? Can someone please help me here?
we have thread safe enabled.
We collected a core when our process is stuck in infinte loop and one thread
is infinitely looping in the below marked loop within sqlite3_close(). So,
it holds the mutex and some other threads are blocked waiting for that
mutex.
#ifndef SQLITE_OMIT_GLOBALRECOVER
{
sqlite3 *pPrev = pDbList;
sqlite3OsEnterMutex();
while( pPrev && pPrev->pNext!=db ){ -> one thread is infinite looping in
this while()
pPrev = pPrev->pNext;
}
if( pPrev ){
pPrev->pNext = db->pNext;
}else{
assert( pDbList==db );
pDbList = db->pNext;
}
sqlite3OsLeaveMutex();
}
#endif
I printed the pDbList from core and the list doesn't seem to end and the
link list seems to have become a cyclic one somehow.
(gdb) p pDbList
$40 = (sqlite3 *) 0x36d00bb8
(gdb) p pDbList->pNext
$41 = (sqlite3 *) 0x107b77a8
(gdb) p pDbList->pNext->pNext
$42 = (sqlite3 *) 0x107b77a8 -> link list becomes cyclic...no NULL.
Note: I saw that this entire logic of link list and global recovery doesn't
exist in recent 3.6.2 code base. Should I consider upgrading to newer
libsqlite library to avoid this problem?
Thanks,
Sathish R.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3 database grows
The optimize() function was mainly written for privacy reasons (it
drops all of the unused terms from the index). I'm a little concerned
about promoting it as a general-purpose solution for size and
performance, because for larger databases it can be a VERY expensive
operation. I would rather find ways to make things work so that the
problem optimize() solves isn't such a big problem. For instance, in
this case I'd rather have a way to handle fts deletions that would not
accumulate cruft in the index, in which case things would eventually
reach a steady state. [As I said, I have such a solution, just
haven't gotten it polished and checked in.]
If you're worried about things at the level of kilobytes, then you may
be outside the core target for fts. optimize() should work reasonably
well for that size of dataset.
[You might now ask "What is the core target for fts?" It's
ill-defined, but my job is Google's Gears and Chrome projects, which
should provide some context.]
-scott
On Mon, Sep 15, 2008 at 2:44 PM, Holger Lembke <[EMAIL PROTECTED]> wrote:
> Scott,
>
> Thanks for the "full table scan hints", I'll change my design.
>
>> You're seeing two effects.
>
> I set up a small test. Its a bunch of windows shell scripts (good old dos
> stuff).
>
> http://www.lembke.eu/fts3.zip
>
> For security reasons the sqlite3.exe is missing. Run CREATE than FILL.
> Repeat FILL.
>
> Database will grow from about 3.615 KB and continue growing. After 100 more
> FILLs (40.000 delete+insert actions.) it reaches a size of 85.683 KB.
>
> With your "optimize" trick I start with 5.032 KB ("optimize" grows the
> database from 3.615 KB to 5.032 KB) and end at 6.740 KB after the 100 more
> FILLs and an optimize after each FILL.
>
> Thats very OK for me. :-)
>
> Thanks for the long answer, I think the WIKI page
> http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex should contain the
> "optimize" trick.
>
> --
> Holger Lembke
>
> ___
> 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] Dates & SQLite
True, but it seems to me that "Julian dates" (floating-point numbers) in
Universal Time are the least cumbersome way to go if you want a binary
representation, or character strings in one of the supported formats, if
you want a human-readable one.
You can also use Unix timestamps (integers), but those are slightly more
cumbersome, because they require an aditional keyword ('unixepoch') to
process.
g
-Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Monday, September 15, 2008 11:23 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dates & SQLite
Brown, Daniel <[EMAIL PROTECTED]> wrote:
> Could someone point me to the documentation regarding dates and
> SQLite?
http://sqlite.org/lang_datefunc.html
> I'm having trouble finding anything about what data type I
> should use to store dates in my SQLite tables, should it be a
> numerical type (integer or real) or a string?
Your choice. SQLite doesn't have a dedicated date type, but it provides
built-in functions that can handle a variety of representations. You can
choose which one to standardize on.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fts3 database grows
Scott,
Thanks for the "full table scan hints", I'll change my design.
> You're seeing two effects.
I set up a small test. Its a bunch of windows shell scripts (good old dos
stuff).
http://www.lembke.eu/fts3.zip
For security reasons the sqlite3.exe is missing. Run CREATE than FILL.
Repeat FILL.
Database will grow from about 3.615 KB and continue growing. After 100 more
FILLs (40.000 delete+insert actions.) it reaches a size of 85.683 KB.
With your "optimize" trick I start with 5.032 KB ("optimize" grows the
database from 3.615 KB to 5.032 KB) and end at 6.740 KB after the 100 more
FILLs and an optimize after each FILL.
Thats very OK for me. :-)
Thanks for the long answer, I think the WIKI page
http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex should contain the
"optimize" trick.
--
Holger Lembke
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing schema from mysql
On Mon, Sep 15, 2008 at 11:10:42PM +0200, Giorgio Sironi wrote: > 2008/9/15 P Kishor <[EMAIL PROTECTED]>: > > What is wrong with SQL? Dump the db in SQL, and import it into SQLite. > > I'm not talking about the data, since once the database is in place > there's no need to change it; the problem is the structure: column > types, indexes, auto_increment and so on... http://www.sqlite.org/cvstrac/wiki?p=ConverterTools http://www.sqlite.org/cvstrac/wiki Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing schema from mysql
2008/9/15 P Kishor <[EMAIL PROTECTED]>: > What is wrong with SQL? Dump the db in SQL, and import it into SQLite. I'm not talking about the data, since once the database is in place there's no need to change it; the problem is the structure: column types, indexes, auto_increment and so on... -- Giorgio Sironi Piccolo Principe & Ossigeno Scripter http://www.sourceforge.net/projects/ossigeno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fts3 database grows
On Sun, Sep 14, 2008 at 11:01 AM, Holger Lembke <[EMAIL PROTECTED]> wrote:
> (Hello all, new user... refreshing my rusty sql knowledge)
>
> I have a simple table created with
>
> create virtual table ft using fts3(id, content);
>
> and store id/text-pairs with
>
> delete from ft where (id="theid");
Note that this will involve a full table scan to find the row with
id="theid". fts3 tables have an index on tokens, and an implicit
unique index on docid/rowid, and this can use neither.
If having a text id is important, you could do something like:
CREATE TABLE ft_id(docid INTEGER PRIMARY KEY, id TEXT UNIQUE);
CREATE VIRTUAL TABLE ft USING fts3(content);
Then inserts would be something like:
INSERT INTO ft_id (docid, id) VALUES (null, ?); -- bind "theid" or whatever
INSERT INTO ft (docid, content) VALUES (LAST_INSERT_ROWID(), ?); --
bind "sometext" or whatever
and delete would be something like:
DELETE FROM ft WHERE docid = (SELECT docid FROM ft_id WHERE id = ?);
-- bind "theid" or whatever
DELETE FROM ft_id WHERE id = ?;
> insert into ft values ("theid", "sometext");
>
> To test the table I have 400 files containing old mails. Just an random
> choice of contents. Ids are internally counted up simply, so it is 1 to 400.
> I remove ":" and cr/lf from mails but leave everything else. But the effect
> seems to be content independant.
>
> If I repeat this process multiple times I get a growing database. select
> count(*) from ft and ft_content stays at 400, ft_segdir stays at 30.
>
> But select count(*) from ft_segments is raising, from 756, 1237 to 2100 and
> so on. Ok, that explains the growing of my database file.
You're seeing two effects.
For performance reasons, fts3 stores new updates in new segments (in
ft_segments), and over time merges them together. So index
information doesn't always immediately change to reflect "truth".
Over time as additional updates happen, older segments are merged and
older data is dropped (as newer data supercedes it), so you should
eventually see things stabilize.
Additionally, fts3 implements delete as a sort of negative posting
list. Unfortunately, when implementing things I was not able to think
of a clear way to handle negative posting lists supercede positive
posting lists while still maintaining correctness across segments. I
have an implementation which implements this, but haven't polished it
up enough to check it in.
Over the summer I added an fts3 function called optimize() which can
be used to deal with some of this. If you do something like this:
SELECT optimize(ft) FROM ft LIMIT 1;
fts3 will combine all index data into a single segment, and drop
unnecessary negative posting lists. Also, if you happen to delete all
data from your fts3 table, the index will be cleared out.
Long-term, the ability to have negative posting lists annihilate
positive posting lists is obviously the better solution. I had hoped
to get it in last spring, but unfortunately I keep getting distracted
by other stuff. The main sticking point was that I also wanted to
build an fts_migrate facility to make it easy to migrate data between
differently-defined fts tables (either between fts3 and fts4, or
between fts tables with different schema). No estimate as to
completion point for that.
-scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing schema from mysql
On 9/15/08, Giorgio Sironi <[EMAIL PROTECTED]> wrote: > Hello, > I use a mysql db for my web application and I'd like to support sqlite > (also for testing purposes). > Does a standard way exist to import the table structure from mysql? > (phpmyadmin can export but in what format?) > Or maybe a database-agnostic language to write the schema with so that > mysql/sqlite tables could be generated from. What is wrong with SQL? Dump the db in SQL, and import it into SQLite. As long as you don't have any funky MySQL specific stuff that is not supported in SQLite, you should get a SQLite db. You can compare the two to determine what was lost in translation. > Greetings, > > > -- > Giorgio Sironi > Piccolo Principe & Ossigeno Scripter > http://www.sourceforge.net/projects/ossigeno > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance degradation from 3.6.1 to 3.6.2
Enrique Ramirez wrote:
> List stripped the attachment.
>
> On Mon, Sep 15, 2008 at 12:27 PM, Steve Friedman <[EMAIL PROTECTED]> wrote:
>> Attached is an example program that demonstrates a significant performance
>> degradation when migrating from 3.6.1 to 3.6.2. It can be compiled with
>>
>> gcc -Wall -O3 trial.c sqlite3.o -o trial -lpthread
>>
>> Steve Friedman
>>
I should have expected that. Anyway, here it is...
#include
#include
#include
#include
#include
static const char * const CREATE_TABLE =
"CREATE TABLE IF NOT EXISTS data "
"( a integer)";
static const char * const CREATE_INDEX =
"CREATE INDEX data_index ON data (a)";
static const char * const DEL_SQL =
"DELETE FROM data WHERE rowid in ("
" SELECT rowid FROM data ORDER BY a limit 1)";
static const char * const ADD_SQL =
"INSERT INTO data (a) VALUES (?1)";
void die(const char* msg)
{ printf("%s\n", msg);
exit(1);
}
const int TABLE_COUNT = 5;
const int ITERATIONS = 1;
int main(int argc, char* argv[])
{ int i;
sqlite3* db;
sqlite3_stmt* del_stmt;
sqlite3_stmt* add_stmt;
if (sqlite3_open(":memory:", &db) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_exec(db, CREATE_TABLE, NULL, NULL, NULL) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_exec(db, CREATE_INDEX, NULL, NULL, NULL) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_prepare(db, DEL_SQL, strlen(DEL_SQL), &del_stmt, NULL)
!= SQLITE_OK) die(sqlite3_errmsg(db));
if (sqlite3_prepare(db, ADD_SQL, strlen(ADD_SQL), &add_stmt, NULL)
!= SQLITE_OK) die(sqlite3_errmsg(db));
printf("initializing\n");
for (i = 0; i < TABLE_COUNT; ++i)
{ if (sqlite3_bind_int(add_stmt, 1, rand()) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_step(add_stmt) != SQLITE_DONE) die(sqlite3_errmsg(db));
if (sqlite3_reset(add_stmt) != SQLITE_OK) die(sqlite3_errmsg(db));
}
printf("timing\n");
while (1)
{ struct timeval start, end, diff;
gettimeofday(&start, NULL);
for (i = 0; i < ITERATIONS; ++i)
{ if (sqlite3_step(del_stmt) != SQLITE_DONE)
die(sqlite3_errmsg(db));
if (sqlite3_reset(del_stmt) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_bind_int(add_stmt, 1, rand()) != SQLITE_OK)
die(sqlite3_errmsg(db));
if (sqlite3_step(add_stmt) != SQLITE_DONE)
die(sqlite3_errmsg(db));
if (sqlite3_reset(add_stmt) != SQLITE_OK)
die(sqlite3_errmsg(db));
}
gettimeofday(&end, NULL);
timersub(&end,&start,&diff);
double sec = (double)diff.tv_sec + (double)diff.tv_usec/100.;
printf("%8d per second\n", (int)(100 / sec));
}
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance degradation from 3.6.1 to 3.6.2
List stripped the attachment. On Mon, Sep 15, 2008 at 12:27 PM, Steve Friedman <[EMAIL PROTECTED]> wrote: > Attached is an example program that demonstrates a significant performance > degradation when migrating from 3.6.1 to 3.6.2. It can be compiled with > > gcc -Wall -O3 trial.c sqlite3.o -o trial -lpthread > > Steve Friedman > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- // -- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Importing schema from mysql
Hello, I use a mysql db for my web application and I'd like to support sqlite (also for testing purposes). Does a standard way exist to import the table structure from mysql? (phpmyadmin can export but in what format?) Or maybe a database-agnostic language to write the schema with so that mysql/sqlite tables could be generated from. Greetings, -- Giorgio Sironi Piccolo Principe & Ossigeno Scripter http://www.sourceforge.net/projects/ossigeno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] performance degradation from 3.6.1 to 3.6.2
Attached is an example program that demonstrates a significant performance degradation when migrating from 3.6.1 to 3.6.2. It can be compiled with gcc -Wall -O3 trial.c sqlite3.o -o trial -lpthread Steve Friedman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Dates & SQLite
Brown, Daniel <[EMAIL PROTECTED]> wrote: > Could someone point me to the documentation regarding dates and > SQLite? http://sqlite.org/lang_datefunc.html > I'm having trouble finding anything about what data type I > should use to store dates in my SQLite tables, should it be a > numerical type (integer or real) or a string? Your choice. SQLite doesn't have a dedicated date type, but it provides built-in functions that can handle a variety of representations. You can choose which one to standardize on. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Dates & SQLite
Good morning list, Could someone point me to the documentation regarding dates and SQLite? I'm having trouble finding anything about what data type I should use to store dates in my SQLite tables, should it be a numerical type (integer or real) or a string? Cheers, Daniel Brown | Software Engineer @ EA Canada "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unsubscribe
unsubscribe John Horton Megger Limited Archcliffe Road Dover Kent CT17 9EN England. T +44(0)1304-502100. (Switchboard) T +44(0)1304-502139. (Direct) F +44(0)1304-502306. E [EMAIL PROTECTED] www.megger.com The information contained in this electronic mail message is confidential. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If the reader of this message is not the intended recipient, you are hereby notified that any use, copying, dissemination or disclosure of this information is strictly prohibited. Megger Limited, Registered in England and Wales Number 190137, Registered office Archcliffe Road, Dover, Kent CT17 9EN _ This e-mail has been scanned for viruses by MessageLabs. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Gears for Mac OS X/Safari, now available
On Sun, Sep 14, 2008 at 7:26 PM, P Kishor <[EMAIL PROTECTED]> wrote: > Strangely, it implements FTS2 for full-text search. > http://code.google.com/apis/gears/api_database.html#sqlite_fts Gears uses fts2 because that's what was current at launch. The vacuum-related fts2 design flaw cannot cause problems in Gears, as Gears compiles out vacuum entirely. Since fts3 did not have additional features, it would have been somewhat annoying to force developers to upgrade, and we don't really have a way to enforce an upgrade, so we decided to just leave it be. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE returning SQLITE_DONE even if record is notpresent
"Aravinda babu" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Thanks for the quick response.Suppose one record is there whose ID is > 1 but > i am doing sqlite3_step with ID 2 ? I'm not sure what you mean with "sqlite3_step with ID". Are you talking about a statement like "select * from mytable where ID=2"? It doesn't matter why the resultset is empty, be it because the table is empty or because none of the existing rows satisfy the condition. The handling is the same for all cases. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE returning SQLITE_DONE even if record is not present
Hi Igor, Thanks for the quick response.Suppose one record is there whose ID is 1 but i am doing sqlite3_step with ID 2 ? At that time it didn't found the record.What error it has to return if ID didn't matches ? Thanks, Aravind. On Mon, Sep 15, 2008 at 5:29 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Aravinda babu" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > SQLITE returning SQLITE_DONE even if record is not present. > > > > I hope that the return code will > > be > > > > SQLITE_NOTFOUND but i am getting SQLITE_DONE ? > > > > Why is this happening ... > > This is by design. sqlite3_step returns SQLITE_ROW for each row in the > resultset, and SQLITE_DONE to indicate there are no more rows. So if a > resultset contains N rows, then N calls would return SQLITE_ROW and > (N+1)st returns SQLITE_DONE. An empty resultset is not in any way > special, it follows the same logic with N=0 (so the very first call > returns SQLITE_DONE). This allows client code to handle all resultsets > uniformly. > > SQLITE_NOTFOUND is an obsolete error code that's currently not used for > anything at all. > > Igor Tandetnik > > > > ___ > 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] SQLITE returning SQLITE_DONE even if record is not present
"Aravinda babu" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > SQLITE returning SQLITE_DONE even if record is not present. > > I hope that the return code will > be > > SQLITE_NOTFOUND but i am getting SQLITE_DONE ? > > Why is this happening ... This is by design. sqlite3_step returns SQLITE_ROW for each row in the resultset, and SQLITE_DONE to indicate there are no more rows. So if a resultset contains N rows, then N calls would return SQLITE_ROW and (N+1)st returns SQLITE_DONE. An empty resultset is not in any way special, it follows the same logic with N=0 (so the very first call returns SQLITE_DONE). This allows client code to handle all resultsets uniformly. SQLITE_NOTFOUND is an obsolete error code that's currently not used for anything at all. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE returning SQLITE_DONE even if record is not present
Hi all,
I am new to Sqlite.Please see the below code.
static const char *selectCmd = "SELECT * FROM cert_store_table where
certNumber = :certNumber;" ;
static const char *cmd = "CREATE TABLE cert_store_table ( certNumber
INTEGER primary key , certTypeLen INTEGER , certType TEXT , validFlag TEXT,
certData BLOB)";
returnCode = sqlite3_open(CertStoreDatabaseName,&dbHandle);
if( returnCode != SQLITE_OK )
{
printf("Can't open database: %s\n", sqlite3_errmsg(dbHandle));
sqlite3_close(dbHandle);
}
returnCode = sqlite3_exec(dbHandle, cmd, NULL, NULL, NULL);
if( returnCode!=SQLITE_OK )
{
printf("Can't exec cmd in database: %s\n", sqlite3_errmsg(dbHandle));
sqlite3_close(dbHandle);
}
returnCode = sqlite3_prepare(dbHandle, selectCmd, strlen(selectCmd), &stmt,
&tail);
if( returnCode!=SQLITE_OK )
{
printf("Can't prepare select cmd in database: %s\n",
sqlite3_errmsg(dbHandle));
sqlite3_close(dbHandle);
}
sqlite3_bind_int(stmt, 1, 10);
returnCode = sqlite3_step(stmt);
printf("DEBUG : %d\n",returnCode);
In the above code i didn't added any records in the database.Just i asked to
return the record whose
CertNumber is 10 which was not found.I hope that the return code will be
SQLITE_NOTFOUND but i am getting SQLITE_DONE ?
Why is this happening ...
Thanks in advance,
Waiting for your reply,
Aravind.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
