Re: [sqlite] Propose minor incompatible API change

If you can change the API so "correctly" written apps don't break and "poorly" written ones work too...yeah rah... The only thing I would ask is a clear example of what "correctly" written isdoes that exist? I can't find it in the documentataion. Plus, a clear example of "poorly"

[sqlite] Compile warning -- signed overflow

Compiling version 3.7.3...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 signed overflow does not occur when assuming

Re: [sqlite] EXTERNAL: Alphabetic Order

See this: http://stackoverflow.com/questions/1188749/how-to-change-the-collation-of-sqlite3-database-to-sort-case-insensitively Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:

[sqlite] Degrees of separation

I need a bit of SQL expertise here...I'm close but not close enough and joins kind of confuse me. Given the following table of 1-degree separations I want to get the 2-degree separations CREATE TABLE know(p1 varchar,p2 varchar); CREATE INDEX know_idx on know(p1,p2); INSERT INTO "know"

Re: [sqlite] [BUG] reading dump file containing lines (regex) '[[:space:]]+/$' gives incorrect result

I duplicated your problem on 3.6.23.1 But it works fine under 3.7.3 SQLite version 3.7.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> PRAGMA foreign_keys=OFF; sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE test( t text ); sqlite> INSERT INTO "test"

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite forBlackfin+uClinux?

oss-compiling SQLite forBlackfin+uClinux? On Thu, 2 Dec 2010 12:38:18 -0600, "Black, Michael (IS)" <michael.bla...@ngc.com> wrote: >If you need threadsafe just add -lpthread to your final link I get an error when using "-lpthread", which I guess makes sense, sinc

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

on behalf of Gilles Ganault Sent: Thu 12/2/2010 12:15 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux? On Thu, 2 Dec 2010 08:06:13 -0600, "Black, Michael (IS)" <michael.bla...@ngc.com> wrote: >You're linking your two .o in

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

, 1 Dec 2010 11:10:54 -0600, "Black, Michael (IS)" <michael.bla...@ngc.com> wrote: >I don't think you need to do the LDFLAGS thing... > >gcc -O2 -fpic -shared -Wl,-soname,sqlite3.so -o sqlite3.so sqlite3.c > >Works fine for me. Though I'm not using the Black

Re: [sqlite] EXTERNAL:Re: Cross-compiling SQLite for Blackfin+uClinux?

From: sqlite-users-boun...@sqlite.org on behalf of Gilles Ganault Sent: Thu 12/2/2010 7:24 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux? On Wed, 1 Dec 2010 11:10:54 -0600, "Black, Michael (IS)" <michael.bla...@ngc.co

Re: [sqlite] Database corrupted

And your version is. Is your app a stand-alone you can share? If you 've discovered such a bug the community would be MUCH appreciative if you could share so it can be fixed (or at least identified to allay concerns we all may have over thread safety). Michael D. Black Senior

Re: [sqlite] Cross-compiling SQLite for Blackfin+uClinux?

I don't think you need to do the LDFLAGS thing... gcc -O2 -fpic -shared -Wl,-soname,sqlite3.so -o sqlite3.so sqlite3.c Works fine for me. Though I'm not using the Blackfin compiler... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] Select fails even though data is in the table.

org > Date: Wednesday, December 1, 2010, 10:15 AM > If you have another thread running > that deletes or modifies the table, > then move the commit to after the select for duplicate to > ensure > transactional integrity. > > On 12/1/2010 8:10 AM, Black, Michael (IS) wrote:

Re: [sqlite] Select fails even though data is in the table.

If you bind the wrong thing the wrong way the return code doesn't matter. Any particular reason you can't just show us the whole code section? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:

Re: [sqlite] Select fails even though data is in the table.

The problem is probably in the bind calls that you are not showing. If you care to share them we may be able to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Some floats of 15 digits or less do not round-trip

There's a difference between GCC and MS Visual Studio 2008 Express. GCC works fine. But Visual Studio shows this problem. The difference comes in this section of code where the rounding error during computation is different. if( realvalue>0.0 ){ while( realvalue>=1e32 && exp<=350 ){

Re: [sqlite] Efficient hash lookup table emulation with SQLite - how?

sqlite> CREATE TABLE t (key TEXT PRIMARY KEY, count INTEGER); sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t WHERE key="key1"),0)+1); sqlite> SELECT * FROM t; key1|1 sqlite> INSERT OR REPLACE INTO t VALUES("key1",coalesce((SELECT count FROM t WHERE

Re: [sqlite] Multilingual query

Depends on what he means by "first". If alphabetically Igor's solution worksbut I'm not sure why you alphabetize singled query result like that. If you want the first ones inserted into the table try this: select * from table2 t1where t1.language = 'spanish' or not exists (select 1

Re: [sqlite] FTS Question

Actually this query might be more correctthe other would match any t/online.de combination -- so we restrict to NEAR/0 separation... select * from contacts where contacts match replace('t-online.de','-',' NEAR/0 '); m...@t-online.de Michael D. Black Senior Scientist Advanced Analytics

Re: [sqlite] FTS Question

I went through this before. How's this look to you? Just replace the dashes with spaces and it works. select * from contacts where contacts match (replace('%t-online.de%','-',' ')); The problem is that the default tokenizer splits the address anyways at the hyphen and there's no way to turn off

Re: [sqlite] EXTERNAL: FTS Question

I went through this before. How's this look to you? Just replace the dashes with spaces and it works. select * from contacts where contacts match (replace('%t-online.de%','-',' ')); The problem is that the default tokenizer splits the address anyways at the hyphen and there's no way to turn

[sqlite] NO_GETTOD

Bug in 3.7.3? gcc -c -DNO_GETTOD sqlite3.c sqlite3.c: In function âunixCurrentTimeInt64â: sqlite3.c:27870: error: âiâ undeclared (first use in this function) sqlite3.c:27870: error: (Each undeclared identifier is reported only once sqlite3.c:27870: error: for each function it appears in.) static

Re: [sqlite] EXTERNAL: Confused by import of NULL values from CSV file

That's not SQLite doing it...it's sqliteman treating it as empy strings. If you want it be NULL after import just update it yourself. UPDATE mytable SET key=NULL where key=''; Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] EXTERNAL:Re: Strange Corruption

The idea that integrity_check is 100% foolproof is wishful thinking. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Pirmin Walthert Sent: Tue 11/16/2010

Re: [sqlite] problem reading a row of data

Try showing us your code...sounds like you're doing something wrong if the query works elsewhere. Here's my simple example that does something similar to what you describe: #include #include "sqlite3.h" main() { sqlite3 *db; sqlite3_stmt *stmt; char *errmsg=NULL;

Re: [sqlite] When is corruption acceptable?

Did you try and do an .export of your database? That would be my first thing to try. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dustin Sallings Sent:

Re: [sqlite] Query help - two similar queries

This also works...a little mod to Igor's... You need to ensure that the players are always listed in the same 1,2 order though. Otherwise the group by won't work. .mode column .width 8 create table Games(id,player1,player2,score); insert into Games values (1,1,2,1); insert into Games

Re: [sqlite] Strange Corruption

ral times one little tiny tiny tiny change already fixes the error. As an .export/.import will change some bits for sure this will already change the situation! Am 16.11.2010 13:53, schrieb Black, Michael (IS): > I thought of another test you should try. > > Do an .export of your original d

Re: [sqlite] EXTERNAL:Re: Strange Corruption

I thought of another test you should try. Do an .export of your original database using 3.6.23.1 and .import it (constructing a new database). Then try your backup. If that works then you're just seeing corruption in the original database that 3.6.23.1 handles (since it created it). If it

Re: [sqlite] Strange corruptions

and deletes) it became "unbackupable" Pirmin Am 12.11.2010 20:30, schrieb Pirmin Walthert: > Am 12.11.2010 14:40, schrieb Pirmin Walthert: >> Am 12.11.2010 14:19, schrieb Black, Michael (IS): >>> Do a "sum" on the files to make sure they are identical. >&

Re: [sqlite] EXTERNAL: Transaction Size

I think there are two answers I've seen: #1 Your mileage may varythe optimum transaction size is dependent on your table structure. There's a trade-off at some point if you need to keep access open to the database. Otherwise just make it one transaction. #2 You may find dropping and

Re: [sqlite] How to cancel subscription of this mail list?

Use the link at the bottom of all messages Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Alex Cheng Sent: Sun 11/14/2010 3:42 AM To:

Re: [sqlite] Strange corruptions

Do a "sum" on the files to make sure they are identical. #1 Show all the files in the directorty #2 How are you copying? Basically...show us ALL the commands and files you are using... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] sqlite] Best way to represent UUID

Thousands doesn't seem like it will be problem...so I'd keep it simple and put it in string form. You'll cut the bytes in half for a blob but it doesn't sound like you need it for just thousands unless you're really memory starved. Michael D. Black Senior Scientist Advanced Analytics

Re: [sqlite] SQLite Memory Usage

My guess is that you have a memory leak... Care to post your code of how you're "creating" the database? You're probably not freeing the SQL, or the statement handle. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] EXTERNAL: sqlite3_step to select and update the same table

I think the quick answer is to use a different database handle for your update. I think you may also be able to do this with WAL mode. http://www.sqlite.org/draft/wal.html I did some searching and couldn't find a definitive answer for doing an update inside a select loop (though I'm sure I've

Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error

Also... Can you write a small example program to duplicate the problem and post it? That's the best way for us to help. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:

Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error

#1 What version of Sqlite and Windows client/server are you using? #2 What language is your application written in? #3 Are all your databases on the same share mount point? #4 Is your share mounted as a drive letter? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop

Re: [sqlite] EXTERNAL:Re: R*Tree module and double precision?

sage- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: 04 November 2010 02:51 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] R*Tree module and double precision? > > I'll send the

Re: [sqlite] EXTERNAL:Re: R*Tree module and double precision?

0 ( 8.00) (lowest bit is 10 ^ 0.903090) 99936.00 99872.00 (64.00) (lowest bit is 10 ^ 1.806180) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: 04 November 2010 02:51 PM To: Gen

Re: [sqlite] R*Tree module and double precision?

nformation Systems From: sqlite-users-boun...@sqlite.org on behalf of jsarc...@nanotronicsimaging.com Sent: Wed 11/3/2010 10:22 AM To: sqlite-users@sqlite.org Subject: EXTERNAL:Re: [sqlite] R*Tree module and double precision? >From: "Black, Michael

Re: [sqlite] R*Tree module and double precision?

I made a patch which seems to do this for you...once in a while it's fun to do a bit of programming like this...I didn't do extensive testing on this so you may want to do some yourself. It's going to just about double the RTREE size of course. SQLite version 3.7.3 Enter ".help" for

Re: [sqlite] 'no such column' error returned in a CASE statement

This works: sqlite> select a,case when a='test' then 'true' else 'false' end from (sele ct 'test' as a) as errval; test|true sqlite> select a,case when a='test' then 'true' else 'false' end from (sele ct 'test2' as a) as errval; test2|false I suppose there's another solution too... Michael

Re: [sqlite] Slow SELECT Statements in Large Database file

You should see a noticeable increase in speed with the index you show. You'll want to make sure your data has been inserted in dted_lat order or dted_lon order. That way all the matching records will be colocated on disk and in memory. Otherwise it's going to do a lot of paging to get your

Re: [sqlite] Sqlite with VS 2008

Thanks for pointing out this project...I may be able to use this in my current effort. Download the amalgamation http://www.sqlite.org/sqlite-amalgamation-3_7_3.zip Download the windows binaries http://www.sqlite.org/sqlitedll-3_7_3.zip Extract the sqlite3.h from the amalgation and the

Re: [sqlite] Dates based on fliter

SQLite version 3.7.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(d date); sqlite> insert into t values('2010-06-01'); sqlite> insert into t values('2010-08-01'); sqlite> insert into t values('2010-09-01'); sqlite> insert into t

Re: [sqlite] DB rotate / incremental

My first question is: Why are you using a database? Sounds to me like grep might do what you want. All you're doing is inserts and selects. Can you show what kind of selects you're doing or your database schema? And why do you need to rotate the database? Why can't you just keep it all

Re: [sqlite] BUG

And...if you drop the "group by" from the view it's correct again sqlite> drop view view_user; sqlite> CREATE VIEW view_user AS ...> SELECT user.id,user_record.* ...> FROM user, user_record ...> WHERE user.id=user_record.user_id ...> ; sqlite> select * from test; 11|76|8|11|A

Re: [sqlite] BUG

To add on...it does NOT work under 3.7.3...so perhaps that's what you're using? I agree this doesn't seem intuitive at all... 4|87|3|4|B 11|76|8|11|A sqlite> select count(*) from test; 1 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] BUG

You failed to say what version you are using. Running your SQL on 3.7.2 works just fine. SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE user ...> ( ...> id INTEGER PRIMARY KEY ...> ); sqlite> INSERT INTO "user"

Re: [sqlite] Bundling sqlite database together with exe file.

Depends what you mean by "noticeable"...but you can test it yourself. Just .dump the database, restart with a new name, and .load it back again. Similar to what you should see if you do it yourself. If you choose option 3 remember that users WILL see the database and be able to copy it

Re: [sqlite] EXTERNAL: SQLite query help pls

Spread the word...that's what these lists are for... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dickie.wild Sent: Tue 10/19/2010 8:03 AM To:

Re: [sqlite] EXTERNAL:Re: EXTERNAL: SQLite query help pls

Sorry...I didn't test before I submitted... sqlite> create table video_files(strPath varchar,strCover varchar); sqlite> insert into video_files values('c:\dir1\dir2\file.txt',''); sqlite> update video_files set strCover=(rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg'); sqlite> select *

Re: [sqlite] EXTERNAL: SQLite query help pls

Actually marbex came up with the best solutionwould work with ANY allowable path characters. UPDATE video_files SET strCover TO (rtrim(strPath,replace(strPath,'\','')) || 'folder.jpg') Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] EXTERNAL: SQLite query help pls

Since there aren't a whole lot of string manipulaion functions (like indexof or such) try this: sqlite> create table t(s varchar); sqlite> create table t2(s varchar); sqlite> insert into t values('C:\richEminem\file.txt'); sqlite> select rtrim(s,'._

Re: [sqlite] EXTERNAL: SQLite bug

http://www.pubbs.net/201009/sqlite/37312-sqlite-os2-update-os2-support-in-sqlite-37x.html I don't see where it was incorporated as the question from Richard was never answered...nor was the problem behavior described...so this may not be relevant at all. Ths obvious question is...can you use

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

I also see where you can set the behavior using procnto -- I'll bet "procnto ~i" will make sqlite behave correctly. Though this is a global change. Anybody who depends on this zeroing though is nuts... http://www.qnx.com/developers/docs/6.4.0/neutrino/lib_ref/m/munmap_flags.html There are

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

3.7.2 - Some WAL tests fail on QNX OS On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote: > I'm not sure but I suspect sqlite is not calling unmap before > extending the area. > That would explain why it still gets zeroed out even with the flags. > > Put a break point in t

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

_____ -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Thursday, October 14, 2010 9:22 PM To: General Discussion of SQ

Re: [sqlite] EXTERNAL:Re: How to optimize a multi-condition query

I see the difference now... So I take it that it's faster just to walk the table once rather than walk the index? Couldn't you just walk the index once? Smaller data space (quite likely), better caching? It might be a wash or worse with the potential of having to retreive other fields

Re: [sqlite] How to optimize a multi-condition query

ior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of luuk34 Sent: Fri 10/15/2010 7:40 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query On 15-10

Re: [sqlite] How to optimize a multi-condition query

I love simple examples like this can help people with understanding things...so I tried this which I thought would do what Hilmar wants...but alaswhat concept am I missing? SQLite version 3.7.2 sqlite> create table c(achr char,bchr char); sqlite> create index c_chr on c(achr,bchr);

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

ut instead the regions are getting synced with the data in disk file (which is full of zeros). Not sure if can happen with mmap() API though? Thanks, Praveen -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

I sent this before...have you tried this? According to the QNX mmap page http://www.qnx.com/developers/docs/6.3.0SP3/neutrino/lib_ref/m/mmap.html MAP_NOINIT When

Re: [sqlite] EXTERNAL:Re: Speed up DELETE of a lot of records

I said this before but never saw a response... Just put your delete inside a select loop so it will always be interruptable. I assume you are talking about writing your own code in C or such? So rather than DELETE FROM TABLE WHERE datetime < oldtime; Do this; SELECT datetime from TABLE

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

According to the QNX mmap page http://www.qnx.com/developers/docs/6.3.0SP3/neutrino/lib_ref/m/mmap.html MAP_NOINIT When specified, the POSIX requirement that the memory be zeroed is relaxed. The physical memory being used for this allocation must have been previously freed with

Re: [sqlite] Speed up DELETE of a lot of records

Rather than doing batch deletes why don't you add a date/time constraint to your selects? Then, you can have a thread which does a lazy delete on the old data. Or, you can create a trigger on inserts which deletes anything older than N-days or such. Or...if all you want is an interruptable

Re: [sqlite] disk IO error after windows resumes from sleep

er windows resumes from sleep Closing the handle before going to sleep sounds like a really sensible thing to do which I hadn't heard of before, I will definitely try that! Thanks for the quick responses from you all, Serena. On Tue, Oct 5, 2010 at 1:16 PM, Black, Michael (IS) <michael.bla..

Re: [sqlite] disk IO error after windows resumes from sleep

Could this also be because you never closed the database handle? So Sqlite thinks it's still open? First time you get an errror do an sqlite3_close() on the old handle. That may solve your problem. Next thing, you should register your app to receive the PBT_APMSUSPEND

Re: [sqlite] EXTERNAL:Re: FTS Question

n more sophisticated behaviour, you would have to implement your own tokenizer and hook it to FTS3. The doc explains how to do this. Best regards, Laurent Dami >-Message d'origine- >De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] De la part de Bl

[sqlite] FTS Question

I'm experimenting with the FTS capability and have a question How do I enter a value in the database that is hyphenated? Seems to be backwards form what I would expectif the content contains a hyphen then only non-hyphenated query returns the correct value whereas the hyphenated one

Re: [sqlite] Getting unique years from a timestamp column

datetime is just this: strftime('%Y-%m-%d %H:%M:%S', ...) >From http://www.sqlite.org/lang_datefunc.html > And that doesn't do what you want...you need strftime to get just the year. Michael D. Black Senior Scientist Advanced Analytics

Re: [sqlite] Getting unique years from a timestamp column

That's cuz' the parameters were backwardsshould be select distinct datetime(,'%Y',date) as year from data; The way it was given "date" was being used as the format and %Y as the time valueergo blank... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman

Re: [sqlite] Getting the next row

If performance of the query is a concern than add an additional field for keeping the "next" and update it with insert/delete triggers. . Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From:

Re: [sqlite] sqlite cannot find the row

Your code is chopped off and I suspect the problem is on the remainder of this line: sprintf(SqlString, "SELECT SrcIp, DstIp, TimeStamp, SrcPort, DstPort, SeqNum For example, did you put single quotes around the ? parameter? Michael D. Black Senior Scientist Advanced Analytics Directorate

Re: [sqlite] Cross Compiling & C Interface Problem

You need to add "-ldl" to LIBS resolve the dynmic linking library calls. It's possible these calls could be in your -lrt which should be moved to the END of your LIBS. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] sqlite cannot find the row

You don't provide enough into to tell what the problem is. This works for me...so what did you do different? Simplify your problem to a complete example like this and we can help better... #include #include "sqlite3.h" int main() { int rc; char

Re: [sqlite] EXTERNAL: Massive performance regression in 3.7.x

Confirmed 3.6.23.1 sqlite> select count(*) from object_formats join page_table on page_table.version=3 and page_table.objectid = object_formats.objectid ; 12366 CPU Time: user 0.019997 sys 0.003000 3.7.0 CPU Time: user 32.269095 sys 0.019997 3.7.2 CPU Time: user 33.606891 sys 0.001000 Fossil

Re: [sqlite] sqlite] SELECT is Slow when run in C++ code compared to SQLite Administrator

Have you timed the entire application instead of just the SQL query? Put both apps inside a system() call and time those. Are you sure they are both compiled with the same optimization? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] In memory database and locking.

up trigger to run every minute? I thought about writing hash code, but thought sqlite or other in memory database would work. The in memory database seems to keep up with the in coming traffic. Hemant Shah E-mail: hj...@yahoo.com --- On Thu, 9/9/10, Black, Michael (IS) <michael.bla...@n

Re: [sqlite] EXTERNAL: In memory database and locking.

Have you considered doing your cleanup during a trigger? I assume you're already using transactions for your inserts. I wouldn't think it would be much slower doing it every insert as you'd be deleting a much smaller set every time. This is really a LOT faster if you just hash your info and then

Re: [sqlite] Sqlite on NAND flash devices...

Well...yeh...but you were complaining about the logs being written to your flash...you gotta' pick your poison. If you keep only the temporary files in memory you should be OK. That's what the compilation flag is for. Keeping your entire database in memory is probably on an option if it's

Re: [sqlite] Sqlite on NAND flash devices...

WAL mode still writes the journal to disk. Perhaps combining this with SQLITE_TEMP_STORE=3 would be the best of all worlds? Then his current situation of writing a 2-minute transaction should work just fine and minimize the # of writes. Michael D. Black Senior Scientist Advanced

Re: [sqlite] Sqlite on NAND flash devices...

Can you keep your database in-memory and just copy it to your flash periodically? That would give you the most control and minimize the # of writes as much as you want. It's good you recognize that you'll burn your flash out quickly with what you're doing now. There's also the

Re: [sqlite] EXTERNAL: Fwd: Conflicting table existence errors after SAVEPOINT & ROLLBACKs

I confirmed this also is a bug in 3.7.2 Interesting thing thoughif you add one .schema statement the problem goes away. I wanted to see what it thought about the schema along the way. So after With bug: SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated

Re: [sqlite] SQL script help.

What language are you writing in? Why do you need to know the # of rows? What are you doing with that information? The select itself does stop (obviously). >From a Windows command prompt you can just do this echo SELECT * FROM myTable WHERE _rowid_ >= 100 AND _rowid_ <= 102 | sqlite3

Re: [sqlite] EXTERNAL: Possible database corruption bug in SQLite

Since I love to debug code and I needed a distraction Problem was introduced on 2009-06-05 update Several SQLITE_CORRUPT_BKPT were inserted (so this corruption may have been going for a while). And here's where the corruption is returned: iPage = get4byte([8+closest*4]);

Re: [sqlite] Help with database corruption?

I confirmed this shows a malformed result on 3.6.23.1 and 3.7.0.1 But it runs just fine under 3.7.1 So apparently whatever bug you triggered has been fixed now. Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] EXTERNAL:Re: What is the most efficient way to gettheclosebynumbers?

My first reaction to all this was...why use SQL to do this? Then after I saw 333450 results I was wondering if that was accurate. Out of 10,000 records there should be 384.62 average name matches The odds of being < 10 with 0-99 position is approx 1*.1 This gives us 384.62*(1-384.62)*0.1

Re: [sqlite] multiple conections for the same database

the other thread? AFAIK, no. Of course until you COMMIT no reader will see your data. But after you commit all already started reading transactions won't see your changes. Reading transaction should be started after COMMIT to see changes. Pavel On Wed, Aug 18, 2010 at 10:50 AM, Black

Re: [sqlite] multiple conections for the same database

Which means the read works on old data until you COMMIT? Is that true? So that COMMIT will allow you to query the changed/new data from the other thread? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems

Re: [sqlite] EXTERNAL: Record corruption on Mac OS X 10.6(SnowLeopard)

I'm not an objective C expert (or even amateur) -- but it looks to me like your use inside the function call is not setting anything in the object but appears to be a cast. So your old success would completely depend on the behavior of the memory allocator and garbage collector and how it

Re: [sqlite] EXTERNAL: Why the deadlock?

I don't know if this is of any use...but it appears that there are some RD/WRLCKs that are not released. In particular 825 and 826 get read/write locks but I don't see them ever UNLCK them. They appear to be related to paging and I'm completely unsure as to how relevan this is. I

Re: [sqlite] EXTERNAL: Record corruption on Mac OS X 10.6 (Snow Leopard)

Can you post your code? In particular the place where it fails and you retry? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dario Napolitano Sent: Mon

Re: [sqlite] severe slowdown for sub-queries, solved in 3.7?

The patch that fixed your problem was on 2010-04-07 I've attached a patch from 3.6.23 to that version. It's 1849 lines of so that's not too bad for QA. I've also attached a context diff too. A bit easier to read if you need QA to look at it. The sqlite3 source is too big for an

Re: [sqlite] severe slowdown for sub-queries, solved in 3.7?

From: Black, Michael (IS) Sent: Tue 8/10/2010 4:41 PM To: General Discussion of SQLite Database Subject: RE:[sqlite] severe slowdown for sub-queries, solved in 3.7? The patch that fixed your problem was on 2010-04-07 I've attached a patch from 3.6.23 to that version. It's 1849 lines of so

Re: [sqlite] EXTERNAL:Re: commit transaction/savepoints on program crash

I've done this before (especially with db's that don't have rollback). Add an update flag. Set it to 2 for the records you update. Then set it to 1 when done. Then set it to 0. 2 -- transaction in progress 1 -- transaction being completed 0 -- transaction completed When you start up the

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

n_keys; 0 Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies Sent: Mon 8/9/2010 9:13 AM To: General Discussion of SQLite Database Subject: E

Re: [sqlite] foreign key the true error - sqlite 3.7.0.1

>From http://www.sqlite.org/foreignkeys.html Although I believe I compiled my shell WITHOUT these defined but I still don't get enforcement of the foreign key constraint. It appears that foreign keys are fully constrained by default. Is there any way in the shell to find out if it's

Re: [sqlite] foreign key error 01

Instead of REFERENCES staff(id) Try REFERENCES staff_01(id) That spelling thing will get you every time...:-) Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on

Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?

Sounds to me like Boyer-Moore is needed http://en.wikipedia.org/wiki/Boyer%E2%80%93Moore_string_search_algorithm And...I would probably pre-load the database table into 26 seperate memory tables to avoid any SQL interactivity at all other than the initial loading. Adding the SQL layer slows

<    2   3   4   5   6   7   8   9   >