A slight mod on my solution makes it work for DST changes too. Again...rowid
must be maintained.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE log(d date);
INSERT INTO "log" VALUES('2011-03-13 01:55');
INSERT INTO "log" VALUES('2011-03-13 01:56');
INSERT INTO "log"
create table t (d default CURRENT_DATE,i number);
insert into t (i) values(1);
select * from t;
2011-04-21|1
Use CURRENT_TIME if you want hours/minutes too.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
Assuming your database ONLY contains the log entries this should workand be
pretty fast too since rowid is already indexed and there areYou no other
lookups.
You can add your own rowid to make this work otherwise. Just do a
max(myrowid)+1 on your insert.
PRAGMA foreign_keys=OFF;
BEGIN
Ummm...are we forgetting about swap space?
If you exceed RAM you hit swap. If you exceed RAM+SWAP you start failing.
Or does sqlite monitor physical memory usage?
So if you exceed RAM you just start slowing down towards a disk-based equialent
database.
Michael D. Black
Senior
That's the nice thing about standards...there's so many to choose from :-)
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Simon
Seems to behave OK for me on 3.7.5 on Windows. What version are you using on
what OS with what compile flags?
You also "said" it didn't work but you didnt' actually what what you did.
Like this...
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
Care to show us your SPL_mallocstr() function?
Sounds like you've corrupted data if that line dies. All it's doing is
checking an array value which is used all over the place in sqlite3.c
If you put a break point there and on the first time it's hit put a watch on
the address for
l
003
I must be doing something fundamentally wrong.;-(
Thanks for the help so far, I really appreciate it.....
Lynton
On 03/04/2011 13:44, Black, Michael (IS) wrote:
> How are you trying to view the ouitput.result.txt (and I"ll note that it'sNOT
> a text file...it's an image
I ran your code with my test file and I get this...which is perfectly correct.
Do you get something different? What makes you think the stream is truncated
in the database?
Also...change SQLITE_STATIC to SQLITE_TRANSIENT...that could be your culprit if
you are still seeing truncation.
Plus
How are you trying to view the ouitput.result.txt (and I"ll note that it'sNOT a
text file...it's an image according to what you said.). What's the size of the
file.
And you should be able to post a COMPLETE example to show your testing. What
you say you want to do has been done by many
You apparently don't understand "strings" in C. Or are you actually reading in
binary data?
#1 Since you said "image" I assume you're reading binaary. So get rid of
buffer[fsize]=0. You don't null terminate binary data and that statement is
1-beyond the end of the array (which is from 0 to
Since it apperas you're running your commit in a separate thread and are
therefore muilti-threaded I do belive you need:
SQLITE3_THREADSAFE=2
>From http://www.sqlite.org/compile.html#threadsafe
To put it another way, SQLITE_THREADSAFE=1 sets the default threading mode to
Serialized.
I don't understand how your column C works...so I'll assume it's pre-known for
now. But here's how to get A/B to work.
drop table t if exists;
create table t(a int,b int,c int);
create trigger trig1 after insert on t
begin
update t set a=(select count(b) from t where b=new.b) where a=0;
end;
Just to help clarify (hopefully) the Unix/Windows "reserved filename".
CON: is similar to Unix's /dev/zero or /dev/null for example -- Files that
already exist and have OS meaning.
stdout is NOT a reserved filename...it's a predefined variable of FILE *. You
cannot say "cp file stdout" on
Wouldn't the addition of "Empty result sets will return SQLITE_DONE on the
first call to sqlite3_step." add some clarity?
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org
Base 10 multiplication is needed. Although money is the main reason for doing
BCD due to cumulative errors it's not the only reason. So I'd recommend adding
decimal*decimal -> decimal just to be complete. It's easy enough to implement
using the + function so could just be noted as "slow".
When you say "All an index does" don't forget that an index is also usually
smaller than the data, thereby increase cache performance and reducing disk
seeks.
For a good chunk of typical uses (large tables with simple lookups) an index is
notably faster.
I'll admit my use of sqtlite3 hasn't
Hmmm...according to my math...
Max 64-bit unsigned integer is
18446744073709551615
Drop the last digit as it can't hold 0-9
1844674407370955161
Make two decimal positions
18446744073709551.61
Now some commas so we can see better
18,446,744,073,709,551.61
That' $18 quadrillion dollars by my
Unless you're running multiple SQLite apps you don't gain anything by using a
DLL. Plus, is your Pocket PC a i386 CPU?
So try downloading the amalgamation and include sqlite3.c and sqlite3.h in your
project.
http://www.sqlite.org/sqlite-amalgamation-3070500.zip
You'll also find the code will
Blob may be better if you need speed -- then no conversion is necessary inside
your Pascal code to/from a string.
But if you want to be able to see and understand your database text is better
(or you have to write a special Pascal program to decode your database to look
at any problems).
Hmmm...the docs do say that...but how do you get that value back out?
Retreiving it as text doesn't work.
You still don't say what you're planning on doing with these number...just
displaying them?
I think the docs may be misleading...here is the comment in sqlite3.c
/*
** Try to convert a
I hope you know what you're doing with trying to preserve that much
significance. Ths first time you stick it in a double or long double variable
you'll lose it. You can use the HPAlib to get 32 digits
http://www.nongnu.org/hpalib/
// Example showing digit loss -- doesn't matter double or
But I thought he said he dropped the indexes (meaning they aren't there during
inserts).
That should make sorting irrelevant.
3 Things.
#1 Test with :memory: database and see what the speed is. That tells you if
it's SQLite or disk I/O as the bottleneck.
#2 Try WAL mode "pragma
Try this.
BEGIN TRANSACTION;
CREATE TABLE sales(SaleDate date,SaleVolume int);
INSERT INTO "sales" VALUES('2010-01-01', 10);
INSERT INTO "sales" VALUES('2010-01-02', 20);
INSERT INTO "sales" VALUES('2011-01-01', 15);
INSERT INTO "sales" VALUES('2011-01-02', 30);
INSERT INTO "sales"
Try CodeBlocks
Cross-platform and works with gcc or MSVC or pretty much whatever.
I put in gcc 64-bit for it.
http://www.codeblocks.org/
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
: Sunday, March 13, 2011 8:40 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Optimising a query with several criteria
Hi Michael, thanks for this. My database is 40 megabytes (and growing
slowly) - is that a reasonable cachesize?
On 13/03/2011 13:07, Black, Michael
You don't say how big your database is.
My guess is when you see the server using a lot of RAM (and exactly how are you
measuring this?) that it's flushing its disk cache. If you're on Unix use
vmstat to see what your OS cache is doing.
So...perhaps if you increase SQLite's internal cache it
You go from 2 minutes to 30 minutes just based on clock speed.
I would imagine you don't have much disk cache on the ARM device, so every time
you read the database it has to hit the file systemouch
Did you benchmark your PC system without using a cached database? Just edit
the
You should learn how to use "explain query plan".
Indexes are a trade-off between insert speed (slow) and select speed (fast).
If you have a static database there is no such thing as too many indexes as
they never would get changed. Most of us live in the world between.
Generally, don't
I was trying to follow the documentation on FTS4 and found what I guess is a
typo.
In several places there is a reference to "document" where I believe it should
be "documents" as the first term doesn't exist in any create statement.
There are several of these in Appendix A
I wasn't aware SQLite's PRNG was not like most others. Good to know.
I guess one could insert their own random() function if you need repeatability
(which is actually a major point of most random number generators). I don't
see the seeding exposed in SQLite so you can restart from a given
SQlite's random() is a pseudo-random (as are most all) so there is no collision
until you get the same value back at which point it just repeats the whole
sequence again.
So the following example should work fine for him. When it collides you've
cycled through the complete range of SQlite's
Try using SQLITE_TRANSIENT instead of STATIC.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of pcun...@fsmail.net
Have you run your test with and without crypto? If Apple can compile 3.6.23.2
to work you should be able to also (might be overly optimistic here but
compilers are 100% deterministic, although not necessartiliy 100% binary match).
Michael D. Black
Senior Scientist
NG Information Systems
No such thing as "close enough" when it comes to different versions.
Can somebody find the 3.6.23.2 amalgamation for this guy?
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org
My experience says the problem/solution is probably simple...FINDING it is the
hard part.
You'll likely change one or two lines of code unless it's a problem with stack
usage on SQlite that has changed.
Anybody done any measurement on SQLite stack usage?
Michael D. Black
Senior Scientist
NG
Well that's one you never mentioned...it works on the version with the SDK?
What version of sqlite comes with that? And why are you upgrading sqlite?
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
only one explanation looking at the numbers. Although it's
well-known that sqlite reads only full pages, if it sometimes does partial
reading, this 5MB/Sec drop for <256 reading can affect linear speed of 25
MB/Sec to end up as 12MB/Sec. But it's just a guess.
Max
On Mon, Feb 28, 2011 at 4:43 P
I think your problem may be that fun() in your eval is being called both from
tcl and from sqlite.
Name your proc fun2 and the problem will probably go away.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
Those numbers make sense to me.
Since count is doing a linear walk throughcorrect?
#1 SSDs more than likely don't do "read ahead" like a disk drive. So what
you're seeing is what read-ahead does for you.
#2 Count is doing a linear scan of the table...Probability of 2K containing the
next
Did you increase the main thread size too?
I would bump them both up by a LOT. A 2X change might not be enough. The
simulator might grow the stack in a different direction so it's just not
visible.
Can you turn on stack checking in the compiler? Or stack-usage?
If it's not the stack
eptual/Multithreading/CreatingThreads/CreatingThreads.html
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Black, Michael (IS) [micha
My first (and only) reaction is "bug in your code" overrunning a char buffer.
If you could show where you do this maybe we can help. Problem is that it
could be another statement doing the overrun too so as much code as you could
share would help.
Michael D. Black
Senior Scientist
NG
You don't say how many cores you have but I assume more than 4. If you're just
single or dual-core I'd say this isn't too surprising.
It's pretty rare that multi-threading gives an N*X performance boost --
especially for CPU or disk bound processes. Simon said most of the reasons but
also
I dont' know the details of the busy handler. Not clear to me that it should
sequentialize the requests.
Perhaps you're better off just using a flag that you could check between your
commit;begin so that if there's a request in the queue you go process it before
continuing.
commit;
if
If I'm not mistaken only WAL mode supports simulaneous read/write.
For any other mode any write function will lock the database.
So...selects may run into a need to wait until a write finishes.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Puneet Kishor [punk.k...@gmail.com]
Sent: Saturday, February 19, 2011 10:21 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] FTS Append?
On Saturday, February 19, 2011 at 10:17 AM, Black, Michael (IS) wrote
I don't know if it works for your data...but you don't need to do all 5.4M in
one batch.
You should test doing it in different batch sizes -- like 1000 at a time (and
let other processes do their thing potentially). That way you won't lock them
out. But I think your other selects need to use
I think I already know the answer to this...but is it possible to append text
to an FTS row without doing some sort of subselect?
Even with the subselect what;s the best way to do this? I seem unable to find
a single-liner that works.
sqlite> create virtual table data using fts4(content
day, February 18, 2011 7:45 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Compiler warnings in R-Tree code under Visual
StudioExpress
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/18/2011 06:32 AM, Black, Michael (IS) wrote:
> You can actually have both you
11 7:53 AM
To: General Discussion of SQLite Database
Cc: Black, Michael (IS)
Subject: EXT :Re: [sqlite] Compiler warnings in R-Tree code under Visual
StudioExpress
On Fri, Feb 18, 2011 at 8:12 AM, Black, Michael (IS)
<michael.bla...@ngc.com<mailto:michael.bla...@ngc.com>> wrote:
I'm o
I'm of the opinion that all such warnings should be permanently fixed. Such
warnings do point to potential problems.
And not by disabling the warning but by fixing the code (explicit casts for
example).
How many people try this and get worried about possible problems? If you
simply fix the
Try this benchmark program and see what numbers you get. You need to compare
to other machines with the same benchmark to see if it's the machine or your
programming/architecture.
The MC55 is a 520Mhz PXA270 so I would expect to see more than a 6X difference
from my 3Ghz box (memory speed is
aud.org]
Sent: Monday, February 14, 2011 5:04 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On 14 Feb 2011, at 8:50pm, Black, Michael (IS) wrote:
> And if you want to talk about data reliability...BACK UP YOUR DATA.
And keep
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On Mon, Feb 14, 2011 at 11:50 PM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:
> And if you want to talk about data reliability...BACK UP YOUR DATA.
> The likely failure
And you think Jim's timings are wrong because..
I've already shown you can get speed just like he's showing.
That's what you get on a good write-cache-enabled drive.
And if you want to talk about data reliability...BACK UP YOUR DATA.
The likely failure points I can think of are:
#1
Also...here's another part of the benchmark which shows triggers aren't as evil
as I thought. Trigger for this example was 2X the manual update.
F:\>d:batch 50 1000 12
using wal mode
using update trigger
Sqlite Version: 3.7.5
Inserting 50 rows using a bulk of 1000
commits per second:
SOLVED!!
Marcus Grimm and I went back forth a number of times trying to figure out why
my benchmarks were so much faster than his.
Found it...
My SATA RAID setup had "Enable Advanced Performance" on by default (I had never
turned it on).
My secondary tests on an IDE drive showed similar
OK...I added your trigger example as option 8. And I had pasted the wrong
version in my last email. My timings were correct. Your example also did
sql_exec instead of using prepare so it will run slower.
I also made this compilable on Unix too.
On Unix my timing matches the run time and
AM, Black, Michael (IS)
> <michael.bla...@ngc.com
>> D:\SQLite>batch 5000 1
>> 360766.6 inserts per sec
>>
>
> Unless I'm missing something, SQLite has to update the first page of the
> database on every commit, to update the change counter. Assuming you are
>
m Wilcoxson [pri...@gmail.com]
Sent: Saturday, February 12, 2011 10:11 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:
> Here's
Are you wrapping your data dump into a "BEGIN" "COMMIT"?
Also...you mention using a select...I assume you are testing first to see if
you need to update or insert?
You may want to look at the REPLACE clause and see if you can use that.
Here's a little benchmark program I wrote to test a
I like this one...but I tested it a bit and the bad part is that doing a select
on t requires a complete table scan. So finding these values is too slow.
I did have to modify a bit to get it to work correctly...now I'll try one of
the other suggestions.
sqlite> create table t (nm integer
-users@sqlite.org
Subject: Re: [sqlite] EXT :Re: Bi-directional unique
On 2/9/2011 4:13 PM, Black, Michael (IS) wrote:
> And what's the advantage of packing like this?
SQLite uses variable-length encoding for integers - smaller values
(nearer to zero, to be precise) get packed into fewer bytes.
this is your turn to decide worth it or not.
Max
On Wed, Feb 9, 2011 at 10:35 PM, Black, Michael (IS) <michael.bla...@ngc.com
> wrote:
> I think I can get away with 31 bits (I'll have to add some error checking
> just in case).
>
> I like this idea as it collapses i
That works too..except you need
create UNIQUE index t_idx on t(m,n);
Otherwise duplicate inserts can exist.
I'll give this one a try too.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
ite Unix backup
http://www.hashbackup.com
On Wed, Feb 9, 2011 at 1:12 PM, Black, Michael (IS)
<michael.bla...@ngc.com>wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> creat
the ordering then you will get the collisions you expect.
On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
I have a need to create a unique bi-directional relationship.
You can think of it as pairings of people who eat dinner together.
create table t(i int, j int);
insert into t(1,2);
insert into t(2,1); << should give an error because the pairing of 1-2 already
exists.
insert into t(3,2); <<
Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Yuzem [naujnit...@gmail.com]
Sent: Monday, February 07, 2011 2:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] EXT : Speed up count(distinct col)
Bl
Test#1
create index tags_index on tags(tag);
You should have an index for any fields you query on like this.
Test#2
Normalize your tags into a separate table so you then store the rowid in your
tags table instead of the string. Your compares will be notably faster using
integers rather than
Unless you have DLL as a requirement I agree with Teg...
Just download the amalgamation and include sqlite3.c and sqlite3.h in your
program -- that's what I do. I try to avoid all non-windows DLLs...they cause
too much headache.
Michael D. Black
Senior Scientist
NG Information Systems
On Sun, 06 Feb 2011 08:22:58 -0500, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> http://www.sqlite.org/c3ref/blob_open.html
> http://www.sqlite.org/c3ref/blob_bytes.html
Mr. Black, is that a tactful hint that the original poster should rewrite
his app in C rather than PHP?
It sounds like you did an sqlite_bind_text instead of sqlite_bind_blob. So
you'll get the correct # of bytes back but length() thinks its text so it
truncates at NULL.
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE
http://www.sqlite.org/c3ref/blob_open.html
http://www.sqlite.org/c3ref/blob_bytes.html
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
Can you "su" as the owner you are are expecting and see if you can delete it?
Maybe the directory permissions are messed up?
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org
Can't represent 2.3 correctly.
You can fix the whole thing by using 100.1 I think...
sqlite> select cast(2.3*100.1 as integer);
230
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From:
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, January 28, 2011 10:44 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Can't delete a row
Me
Me thinkst you need to put singled quotes around $q{BUTTON}.
my $rows = $dbh->do("DELETE FROM contacts WHERE rowid =\'$q{BUTTON}\'");
You also don't appear to be checking for any errorwhich you should be
seeing. I don't Perl/DBI well enough to show how to do that one.
Michael D. Black
My suspicion would be that you have a bug in your code which only shows up on
Solaris.
Something is getting corrupted on the stack.
Do your compilet support stack checking? I think you can use efence on Solaris
http://perens.com/FreeSoftware/ElectricFence/
Michael D. Black
Senior Scientist
Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, January 25, 2011 2:28 PM
To: General Discussion of SQLite Database; Black, Michael
}
} while (err != SQLITE_DONE);
sqlite3_finalize(pStmt);
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
10:51 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:Re: [sqlite] SQLite version 3.7.5 - code freeze
On 01/25/2011 04:10 AM, Black, Michael (IS) wrote:
> Is this if statement meant to be constant? Or should it be comparing the
> requested encoding instead of SQLITE_UTF16
I plugged 3.7.5 code freeze into my app and fairly quickly hit a potential bug.
I'm using a separate thread to commit my transactions and SQLITE_THREADSAFE=2
I never saw a problem in my testing so far with 3.7.3 -- but a few runs after
putting 3.7.5 in I started getting an insert error followed
om: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, January 24, 2011 3:58 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] SQLite version 3.7.5 - code freeze
On Mon, Jan 24, 2011 at 3:
Around line 59981
sqlite3StrAccumAppend(, zStart, zRawSql-zStart);
The zRawSql-zStart is a 64-bit value (on a 64-bit machine) but gets
down-converted to int.
Seems a little more error checking is appropriate to ensure this doesn't exceed
an int.
Would be an awfully long SQL string but
I know some of these may seem minor but for those of us who turn on all
warnings disabling them or explicitly allowing for them would prevent some of
us from having to waste our time looking at them.
Around line 59981 an explicit cast to u16 would prevent a loss-of-precision
warning. And
SQLITE_PRIVATE int sqlite3Utf16ByteLen(const void *zIn, int nChar){
int c;
should be
unsigned int c;
Otherwise I don't think the macros work right (they become constant expression
warnings).
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
Is this if statement meant to be constant? Or should it be comparing the
requested encoding instead of SQLITE_UTF16NATIVE?
SQLITE_PRIVATE int sqlite3Utf16ByteLen(const void *zIn, int nChar){
int c;
unsigned char const *z = zIn;
int n = 0;
if( SQLITE_UTF16NATIVE==SQLITE_UTF16BE ){
Minor item...I reported this before...
Compiling version 3.7.5...this warning has actually been around a while though
I can't find anybody reporting it for sqlite3...
gcc44 -O3 -Wall -fPIC -DNO_GETTOD -c sqlite3.c
sqlite3.c: In function âfkLookupParentâ:
sqlite3.c:55430: warning: assuming
se
Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM...
On 17.01.2011 17:26, Black, Michael (IS) wrote:
> I came up with the logical opposite which I think does what you want
>
> select a.id from thi as a where a.id in (select thi.id from thi where
> a.userid=thi.userid order by timestamp l
se
Subject: EXTERNAL:Re: [sqlite] Help on DELETE FROM...
On 17.01.2011 17:26, Black, Michael (IS) wrote:
> I came up with the logical opposite which I think does what you want
>
> select a.id from thi as a where a.id in (select thi.id from thi where
> a.userid=thi.userid order by timestamp l
The "NOT IN" approach doesn't work..here's sample data using select rather than
delete to show the result.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER,
DataID INTEGER);
INSERT INTO "THI" VALUES(0,10,10,0);
I came up with the logical opposite which I think does what you want
select a.id from thi as a where a.id in (select thi.id from thi where
a.userid=thi.userid order by timestamp limit 100 offset 10);
You just need to set the limit value to some ridiculous number.
Michael D. Black
EXTERNAL:Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22
I just tried the 3.7.4 binary on Linux, and the bug is still around.
:-David
On 01/17/2011 04:25 PM, Black, Michael (IS) wrote:
> This may be the patch that fixes your problem...
> http://www.sqlite.org/src/info/ece641eb89
>
> Was relea
EXTERNAL:Re: [sqlite] JOIN bug in 3.7.2, not in 3.6.22
I just tried the 3.7.4 binary on Linux, and the bug is still around.
:-David
On 01/17/2011 04:25 PM, Black, Michael (IS) wrote:
> This may be the patch that fixes your problem...
> http://www.sqlite.org/src/info/ece641eb89
>
> Was relea
This may be the patch that fixes your problem...
http://www.sqlite.org/src/info/ece641eb89
Was released in 3.7.3
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on
Seem to me that putting it inside a trigger would be the best choice...then you
don't have to worry about it unless you have speed concerns and only want to do
this once a day or such.
CREATE TABLE THI(ID INTEGER PRIMARY KEY, TimeStamp INTEGER, UserID INTEGER,
DataID INTEGER);
create trigger
I don't know if the lack of a semicolon at your END statement could be causing
this? Or is that a typo?
This works just fine in 3.7.4
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From:
I think the answer to this is "no" based on http://www.sqlite.org/faq.html#q6
But a confirmation or other solution would be nice...
I have a shared library being called by a transaction-oriented Java system.
I'm doing periodic commits (every 5 seconds) to speed up processing. However,
I want
501 - 600 of 844 matches
Mail list logo
