Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as c; " possible whatsoever?

Is this some mental exercise? Why can't you do this in the calling code rather than some funky SQL select? Or add a custom function? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mikael Sent: Saturday, July 20, 2013 8:54 AM

Re: [sqlite] Understanding how data is stored and the index is managed

One more test I would is first principles. Load 1200 records and just do "select * from items" -- you aren't going to get any faster than that. Then add the index query. You should find a performance knee as you add records (try adding them in powers of 2). To test I would use "select * from items

Re: [sqlite] What number(2) means?

You're right that SQLite will ignore any field specifications... But it will not "treat the value as real". It will store whatever you give it. The system is typeless. http://www.sqlite.org/datatypes.html This is different than most other database systems. SQLite version 3.7.16.2 2013-04-12

Re: [sqlite] GUI for SQLite

Free doesn't necessarily mean open source http://www.valentina-db.com/en/get-free-studio -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni Sent: Wednesday, June 26, 2013 10:26 AM To: General Discussion of SQLite

Re: [sqlite] First Day of Week Inconsistency

Oops...forgot to add what you want. %U The week number of the current year as a decimal number, range 00 to 53, starting with the first Sunday as the first day of week 01. See also %V and %W. And this one: %V The ISO 8601:1988 week number of

Re: [sqlite] First Day of Week Inconsistency

Those are unrelated questions.day of week has nothing to with the start of a week. >From the standard strftime man page which has been around for decades. %w The day of the week as a decimal, range 0 to 6, Sunday being 0. See also %u. %W The week

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

Or to get all the non-integer records. select * from qqq where typeof(field1) <> 'integer'; Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, June 08, 2013 9:58 AM To: rsm...@rsweb.co.za;

Re: [sqlite] Strange table behaviour after text import with sqlite3.exe

What makes you think field1 gets turned into a zero? Fields are really typeless in SQLite3 Your .dump should look like this: SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2]

Re: [sqlite] Limit of attached databases

Oops...make that an unsigned int. Change this declaration #if SQLITE_MAX_ATTACHED>30 typedef __uint128_t yDbMask; -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black Sent: Tuesday, June 04, 2013 9:51 AM To:

Re: [sqlite] Limit of attached databases

Gcc does have a __int128_t and __uint128_t available if you're on 64-bit and have a current enough gcc (I'm using 4.4.4 and this works on Linux and Windows) Looks like a fairly easy change in the code. Unless somebody already knows that this won't work? main() { __uint128_t i128;

Re: [sqlite] Row_number?

Or perhaps this is better since it is your example: sqlite> create table people(id,name); sqlite> insert into people values(5,'Chris'); sqlite> insert into people values(12,'Arthur'); sqlite> insert into people values(23,'Bill'); sqlite> insert into people values(34,'Ron'); sqlite> insert into

Re: [sqlite] Row_number?

Do you want rowid perhaps for a guaranteed one-to-one mapping to the row regardless of the query? Or are you looking for a repeatable one-up counter for the query results? select rowid,id,name from people ORDER BY name; If you want a one-up counter automagically you can create another table

Re: [sqlite] numeric string quotation db2 to db3

So why don't you use SQLMaestro to import, then export it again. I would think that would quote it correctly. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Spora Sent: Friday, May 31, 2013 1:01 PM To:

Re: [sqlite] numeric string quotation

I think you showed us the insert you do and not the .dump result. You need to ensure your inserted values are single-quoted. sqlite> create table test(alfa char(5) not null default ''); sqlite> insert into test values(01000); sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE

Re: [sqlite] Concatenating literals with column values

Your statement doesn't even work in MySQLas || is a logical operator there. And Oracle complains about your original query: SQL> select stepid ,'STEPID'||stepid ,stepid+5 ,'STEPID'||stepid+5,'STEPID'||5 from seqtable; select stepid ,'STEPID'||stepid ,stepid+5 ,'STEPID'||stepid+5,'STEPID'||5

Re: [sqlite] query help

I think this does what you want. create table tab (num1 int unique,num2 int); insert into tab values(1,3); insert into tab values(2,3); insert into tab values(3,2); insert into tab values(4,1); insert into tab values(5,11); insert into tab values(6,3); insert into tab values(7,9); sqlite>

Re: [sqlite] Problem with index on multiple columns

Do you have to have an autoincrement column? You can implement a non-primary key column in a trigger that fills itself from the rowid after insert giving you the same thing. Mike ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Problem with index on multiple columns

Indeed would seem so. Remove the primary key and idx_test is used. This is on 3.7.16.2 CREATE TABLE test( id INTEGER, class INTEGER NOT NULL); CREATE INDEX idx_test ON TEST(class,id); EXPLAIN QUERY PLAN SELECT * FROM test WHERE id IN (0,1) AND class IN (3,4);

Re: [sqlite] Possible bug in type conversion prior to comparison

conversion prior to comparison On 13 May 2013, at 5:08pm, Michael Black <mdblac...@yahoo.com> wrote: > Would appear it's not doing any casting to promote values but just promoting > everything to float. I should have asked you for (1,2,20) as well and we could see whether it outputs '10' or '10.0

Re: [sqlite] Possible bug in type conversion prior to comparison

essage- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, May 13, 2013 11:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Possible bug in type conversion prior to comparison On 13 May 2013, at 4:57pm, Michael

Re: [sqlite] Possible bug in type conversion prior to comparison

Seems to me the SQL standard makes no distinction between columns and literals does it? Why should literals be ignored? Oracle gives the right answer too for example(contrary to what somebody said earlier). create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); insert into numtypes values (1,

Re: [sqlite] SQLite and integer division 1/2=0

PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you would not have known to turn it on (default would have to be OFF for backwards compatibility). Changing to float math everywhere might hurt some embedded systems. MYSQL was never designed to work on embedded systems. So

Re: [sqlite] Is there a way to select a precision?

06, 2013 8:03 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a way to select a precision? On Mon, 6 May 2013 07:42:43 -0500 "Michael Black" <mdblac...@yahoo.com> wrote: > Yes...other databases do respect NUMBER(10,2) on SELECT's. ... > Can you simply use rou

Re: [sqlite] Anyone can decipher this so i can try to figure out how to debug it? Thanks

It's a lousy error message from some application which ay be using sqlite underneath the covers. Sqlite isn't even mentioned in the error so not sure why you think it's involved. It's getting an error on its own rename code and has nothing to do with sqlite. And it's not telling you what the

Re: [sqlite] Is there a way to select a precision?

What you are talking about would be feature creep for SQLite. Yes...other databases do respect NUMBER(10,2) on SELECT's. SQLite is "light weight" and does no such magic for you. So it does take an extra step. You'll note that SQLite does provide a GUI for you to play with. If it did it would

Re: [sqlite] How people test db failures

There are several ways people handle errors. #1 Path of least resistanceyou assume all is well and ignore thembad idea but too common #2 Catch as catch can...you put in error handling as you experience themalso a bad idea as it's too similar to #1 #3 Catch with careful thought about

Re: [sqlite] How do I write this query

sqlite> CREATE TABLE foo( a integer, b integer); sqlite> INSERT INTO foo VALUES(1,1); sqlite> INSERT INTO foo VALUES(1,2); sqlite> INSERT INTO foo VALUES(1,3); sqlite> INSERT INTO foo VALUES(2,1); sqlite> INSERT INTO foo VALUES(2,2); sqlite> INSERT INTO foo VALUES(2,3); sqlite> CREATE TABLE bar( a

Re: [sqlite] Cygwin compilation error

What version gcc do you have? Mine works. D:\SQLite>gcc --version gcc (GCC) 4.7.3 20121102 (prerelease) D:\SQLite>gcc -o shgcc.exe shell.c sqlite3.c D:\SQLite>shgcc SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>

Re: [sqlite] sqlite3_column_blob does not return the size of the blob?

http://www.sqlite.org/c3ref/column_blob.html sqlite3_column_bytes should do what you need. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni Sent: Thursday, April 18, 2013 7:02 AM To: sqlite-users@sqlite.org

Re: [sqlite] RTree Documentation Error?

It would also be nice if 3.3 showed the "expected output" too. Would be a bit easier for the beginner to ensure they are sane. Also would've prevented this GIGO error me thinkst. Michael Black ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] RTree Documentation Error?

On Behalf Of Richard Hipp Sent: Wednesday, April 17, 2013 8:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] RTree Documentation Error? On Wed, Apr 17, 2013 at 8:41 AM, Michael Black <mdblac...@yahoo.com> wrote: > I think the point is that this query that produc

Re: [sqlite] RTree Documentation Error?

o_index WHERE minX>=-81.08 AND maxX<=-80.58 AND minY>=35.00 AND maxY<=35.44; id 3 Michael Black ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help speed up query

Have you tried increase sqlite cache size and page size? pragma cache_size; pragma page_size; Try making page_size=4096 Then make cache_size*page_size as big as your database file. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On

Re: [sqlite] Beginning database question

I assume when you say "discrete" you actually mean "bracketed" as there are lots more than 10 heights and weights. I don't know what Excel has to do with this unless you programmed the whole thing in Excel already? Is speed really a concern? This does sound like a fairly trivial programming

Re: [sqlite] correlated subquery in LIMIT/OFFSET?

, April 11, 2013 8:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] correlated subquery in LIMIT/OFFSET? Michael Black wrote: > > However, it appears that SQLite does not allow correlated subqueries > > in the LIMIT/OFFSET clauses of a scalar subquery: > > > >

Re: [sqlite] correlated subquery in LIMIT/OFFSET?

Instead of "select t.x" don't you really want "select x from t" ?? That seems to work at least syntactically. I assume you just want the offset to come from a single-row table? Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On

Re: [sqlite] 64bit compatibility warnings

Which is why...IMHOto avoid all the repeats of this question in the future (and from the past)one should simply to do the cast to int and put a comment on the line that says " % int always fits in an int". Too bad one can't cast to the type of a variable though in C. And if IBuf is an

Re: [sqlite] SQL Logic error or missing database

How about just posting your COMPLETE code example for your console app. Some of us can't import a VS2012 project I just sent you a complete example that works. Are you saying this doesn't work for you? #include #include "sqlite3.h" main() { sqlite3 *oDatabase; int returnValue;

Re: [sqlite] SQL Logic error or missing database

This works for me under Visual Studio 2010. I couldn't seem to get a file uri to work at all either. #include #include "sqlite3.h" main() { sqlite3 *oDatabase; int returnValue; returnValue = sqlite3_open_v2("D:/SQlite/testing.db", , SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL); if

Re: [sqlite] SQL Logic error or missing database

Also change the last arg of open to NULL instead of "". -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie Sent: Friday, April 05, 2013 7:54 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQL Logic error or missing

Re: [sqlite] SQL Logic error or missing database

Sqlitge3_close() might be your problem that's masking the real error. You can't close what never got opened. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Collie Sent: Friday, April 05, 2013 7:54 AM To:

Re: [sqlite] TCL Test failures on ARM

What's the ulimit for file sizes on your system? You might be hitting that. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bk Sent: Monday, April 01, 2013 1:16 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] TCL Test

Re: [sqlite] How to achieve fastest possible write performance for a strange and limited case

I think many people would tell you not to store your images in your database. Just store a filepath to them. That will speed things up quite a bit and even possibly prevent having to use an SSD. With the filepath your processing apps can use file locking too if you need it. -Original

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

I can tell you the explain plan is notably different between those two inserts. This is with 3.7.14.1 If only I knew more about the details of what the plan meansone thing I noted is that keyinfo is opened twice for write on Test#2 Test 1: addr opcode p1p2p3p4

Re: [sqlite] Efficient way to store counters

You're simply missing the where clause on your update so you're updating the entire database every time you do an insert. update_counter(k1,k2 count=count+1,expires=now+count*1day) where field1=k1 and field2=k2; And a (k1,k2) index would help that update a lot.

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

o: General Discussion of SQLite Database Subject: Re: [sqlite] Windows-Specific 2-c-files Amalgamation? On Mon, Mar 11, 2013 at 1:33 PM, Michael Black <mdblac...@yahoo.com> wrote: > Richard...he split is missing the 5 split files. I thought the small size > was suspicious. > Fixe

Re: [sqlite] Windows-Specific 2-c-files Amalgamation?

Richard...he split is missing the 5 split files. I thought the small size was suspicious. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, March 11, 2013 11:42 AM To: General Discussion of SQLite

Re: [sqlite] Bug on real operations

me 22.3499948593433 (something like that) and when declaring double a = 22.35 => gdb give me 22.34999 Em 08/03/2013, às 14:11, Michael Black <mdblac...@yahoo.com> escreveu: > Nobody should expect float comparisons like that to work. > If they do they're asking for trouble. >

Re: [sqlite] Bug on real operations

And didn't we go through this a while ago... Sqlite3's precision is 14...sometimes 15 sqlite> select round(22.35-(5.45+16.9),15); 4.0e-15 sqlite> select round(22.35-(5.45+16.9),14); 0.0 So this also works: sqlite> select round(22.35 - (5.45 + 16.9),14), 22.35 = round((5.45 + 16.9),14),

Re: [sqlite] Bug on real operations

And...the right way to do this which should work on all databases...rounding any answers to the max precision you asked for. sqlite> SELECT round(22.35 - (5.45 + 16.9),2), 22.35 = round((5.45 + 16.9),2), round((5.45 + 16.9) - 22.35,2); 0.0|1|0.0 -Original Message- From:

Re: [sqlite] Bug on real operations

Nobody should expect float comparisons like that to work. If they do they're asking for trouble. All you're seeing is what the database is letting you see. Their "0" is not really "0". Try this in your friendly C compiler main() { double d = 22.35-(5.45+16.9); printf("%f\n",d);

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

Personally I think this behavior is horrid. Is there some scenario where this wouldn't be a latent bug? Here's a patch against 3.7.14.1 which behaves the way it should IMHO Though I think it should actually throw an error when not parseable correctly. Why is this behavior allowed now? Old:

Re: [sqlite] Break on cumulative sum

One thing I'll point out...the trigger solution is almost linear performance and constant memory where the single SQL statement will suffer notably as time goes on in both those measures. Also the single SQL statement doesn't seem like a good idea if the data is coming in real time. He never

Re: [sqlite] Break on cumulative sum

Re: [sqlite] Break on cumulative sum Michael, this is working perfectly! I learned a lot with your code. Thanks a lot for your help. gert 2013/3/2 Michael Black <mdblac...@yahoo.com> > I think your "K" row was a typo on the CumulativeValue? > > CREATE TABLE [

Re: [sqlite] Break on cumulative sum

I think your "K" row was a typo on the CumulativeValue? CREATE TABLE [Test] (TextField, ValueField, CumulativeValue, BreakPoint DEFAULT 10, GroupName); CREATE TABLE [MyGroup](GroupName); insert into [MyGroup] values(1); create trigger trig1 before insert on [Test] when 10 <= (select

Re: [sqlite] locked database returning SQLITE_IOERR, not SQLITE_BUSY

This was covered a few days ago...the error handling is not thread safe apparently(why not???) so you need to wrap the call and error check with a mutex. int rc=SQLITE_OK; char *errmsg=NULL; sqlite3_mutex_enter(sqlite_db_mutex(db)); rc=sqlite3_blobopen(.); if(rc!=SQLITE_OK) {

Re: [sqlite] Thread sync issue

Are you misreading that lineit's not worded very well. To rephrase in the positive sense instead of the negative. "provided every thread has its own db connection" -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ashok

Re: [sqlite] import TXT file

I wrote an import util a while ago which might help you out as it's a bit more picky and gives more intelligent error messages than .import does. http://www.mail-archive.com/sqlite-users@sqlite.org/msg70182.html Show us some example lines and we can help a lot better.

Re: [sqlite] got selected items ok but can't update using them

You need the selects in each = expression to replace the variables there. Something like this if I translated your query correctly. UPDATE stmnts SET itm=(SELECT itm FROM std_itms where ID = std_id) where ID=(SELECT alias_id from alias_itms); -Original Message- From:

Re: [sqlite] SQLite error near "16": syntax error

You're inserting a lot of stringsso you have to put single quotes around all the string fields on your insert. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mikkelzuuu Sent: Tuesday, February 19, 2013 7:16 AM To:

Re: [sqlite] Is it possible to use SQLITE to calculate the streakedness of numeric data?

Does this get you started? It calculates a running standard deviation over a window. With a bit more effort you can add a running average and other calculations on the window. Do you have a reference for this streakedness measurement? I couldn't find one. I used this library extension from

Re: [sqlite] MIN(x), MAX(x) confusion

Probably because you're storing your numbers as text. How are you inserting them? Do a .dump from the shell and you'll see it in the record dump. So '7' > '20' but 7 < 20 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of

Re: [sqlite] ranking in a view

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2013-February/04 4367.html -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jeff Archer Sent: Saturday, February 09, 2013 7:01 AM To: SQLite-user.org Subject:

Re: [sqlite] Strange eviction from Linux page cache

I re-ran my test with a 33MB database. Using the shell to .dump the file doesn't fill the cache. But my testro program does. If you open the database with the shell it clears the cache again (it's opening it read/write). ls -l insert.db -rw-r--r-- 1 mblack users 35016704 Feb 7 10:54 insert.db

Re: [sqlite] Strange eviction from Linux page cache

Would it be any use to you to have a separate process which mmaps the file? Seems to me that would probably keep all the pages in cache constantly. I just did a local test on my NFS setup and the file appears to cache just fine. Does yours behave differently? #include #include int main(int

Re: [sqlite] Strange eviction from Linux page cache

Nothing to do with SQLite. NFS won't use cache by default. You have to mount it with the "fsc" option. https://access.redhat.com/knowledge/docs/en-US/Red_Hat_Enterprise_Linux/6/ht ml/Storage_Administration_Guide/fscachenfs.html -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Deletion slow?

If you don't put a COMMIT on your mysql example I don't think you're comparing apples-to-apples. I'm guessing your Rasberry PI sdcard isn't exactly a high-speed performer http://jalada.co.uk/2012/05/20/raspberry-pi-sd-card-benchmark.html How long does it take you to import your database for

Re: [sqlite] Deletion slow?

I made a test database using your table and this main() { int i; for(i=0;i<1;++i) { printf("insert into trip(key) values(%d);\n",i); } } The deleted all keys < 200. time sqlite3 trip.db 'delete from trip where key < 200' real0m0.004s user0m0.001s sys

Re: [sqlite] SQL query

BF95-1571-7CE5 file1 40546-4667-5A69-6478-FC97-6F27-840D-7D62 file1 50546-4667-5A69-6478-FC97-6F27-840D-7D62 file1 60546-4667-5A69-6478-FC97-6F27-840D-7D62 file1 701EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 file1 801EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 On 30 January 2013 22:14,

Re: [sqlite] SQL query

'set' is a reserved word. I get an error running your select statement. Change it. SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table files(file,setid,hash); sqlite> insert into files

Re: [sqlite] SQL query

sqlite> create table t(name,num,ref); sqlite> insert into t values('file1',0,'ABCD'); sqlite> insert into t values('file1',1,'ABCD'); sqlite> insert into t values('file1',3,'EF01'); sqlite> insert into t values('file2',0,'BCE2'); sqlite> insert into t values('file2',2,'BCE2'); sqlite> insert into

Re: [sqlite] Sqlite ubuntu 12.10 compile/install

Use the amalgamation: cc -O -c sqlite3.c Then link it into your program. Most of us recommend avoiding shared libraries. If you need special features you may have to set some flags. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On

Re: [sqlite] faster query - help

The real answer is try both and see which is faster. My guess is #1 is probably faster since I don't think there's an easy way to limit the left-hand side of a left-join operation to do #2 without touching all the music_file records, is there? The join would have to match on file_id so would hit

Re: [sqlite] bug report: out of memory error for most operations on a large database

How much free disk space do you have? Your temp tables might be exceeding capacity. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ammon_lymphater Sent: Thursday, January 24, 2013 1:41 PM To: sqlite-users@sqlite.org Subject:

Re: [sqlite] Sum of various rows

create table test (id,invoice,transfer,price); insert into test values(10,500,200,0); insert into test values(200,300,300,200); insert into test values(334,3000,200,3000); select sum(invoice),sum(transfer),sum(price) from test where id in (10,200,334); 3800|700|3200 Michael Black

Re: [sqlite] Facing "SQLite ERROR : unable to open database file" error

Is there some reason there's the rather poor "unable to open" message without the actual error message? Strerror coming to mind if it hasn't disappeared under foot at those places? I see these questions on the list fairly often and they would all be answered with an intelligent error

Re: [sqlite] Need help with query

You're structure is bad for future growth (i.e. multiple languages) as the query gets really nasty really quickly. You should normalize this data and your query will never change. pragma foreign_keys=ON; CREATE TABLE buttons ( ID integer primary key autoincrement, Key1 varchar

Re: [sqlite] Concurrent read performance

What about using 2 or more databases? Wayne Bradney wrote: >>>All access in SQLite is serialized. Apologies if I'm missing something >>>fundamental here, but that's not what I'm seeing with a file-backed database >>>when shared cache is OFF.My test has a single table

Re: [sqlite] Concurrent read performance

Also...does increasing cache_size help? Are you able to use a RAM disk? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney Sent: Saturday, January 12, 2013 11:39 AM To: sqlite-users@sqlite.org Subject: [sqlite]

Re: [sqlite] Concurrent read performance

Did you try read-uncommitted? Sounds promising... 2.2.1 http://www.sqlite.org/sharedcache.html PRAGMA read_uncommitted = ; -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wayne Bradney Sent: Saturday, January 12, 2013 11:39

Re: [sqlite] FTS questions

ve the FTS will not found it. Am I wrong? about the backslash/slash if I have a path like this and the user search for עברית did the FTS will find it? c:\folders\video\עברית about the creation I was confuse it not temporary its virtual so ignore my question Thanks On Sat, Jan 12, 2013 at 5:58 PM, Mic

Re: [sqlite] FTS questions

I'm not sure I understand your problem. Why do you care what language it is? Aren't you just wanting to tokenize on backslash? Simple way is to replace all spaces in the path with another char (e.g. '_') then replace all backslashes with a space. Then you can just use the default tokenizer and

Re: [sqlite] How to decrease IO usage

Increase your cache size? Default is 2000*page_size http://www.sqlite.org/pragma.html#pragma_cache_size -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bebel Sent: Wednesday, January 09, 2013 5:00 PM To:

Re: [sqlite] Archive SQLite Database?

You can also just copy the entire file (best to do when app is not running). So you'd only be down for as long as it takes to copy it. If your database is updated frequently .backup may never finish. Then you can archive from the copy and delete the archived rows from the active DB in a

Re: [sqlite] Archive SQLite Database?

Tou haven't said what "really slow" means. Could be multiple solutions to that problem. 30min for 3-5MB sounds a bit ridiculous. What kind of select are you doing to archive and what indexes are on the tables? -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] SQLite4 Performance

Thanks...progress...now we get a SIGBUS later on... Program received signal SIGBUS, Bus error. 0x0046ce4c in treeShmalloc (pDb=0x6c9728, bAlign=0, nByte=25, pRc=0x7fffd784) at src/lsm_tree.c:668 668 pNext->iNext = 0; pRc looks suspiciously like it's ready to overflow

Re: [sqlite] 1.1GB database - 7.8 million records

database - 7.8 million records -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 31/12/12 10:35, Michael Black wrote: > One transaction like you did is best. > > I recently ran a test which ran pretty well with a commit every 1M > records. Doing every 100,000 records slowed things down

Re: [sqlite] 1.1GB database - 7.8 million records

One transaction like you did is best. I recently ran a test which ran pretty well with a commit every 1M records. Doing every 100,000 records slowed things down dramatically. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of

Re: [sqlite] SQLite4 Performance

Do we still get to report bugs? I checked out the fossil repository fossil clone http://www.sqlite.org/src4 sqlite4.fossil I tried my insert test and ran into a problem. I'm running Redhat 5.7 gcc 4.4.4 This program dies (showing inserts/sec) 123536 113110 110154 107018 105489 100335

Re: [sqlite] Write performance question for 3.7.15

Referencing the C program I sent earlierI've found a COMMIT every 1M records does best. I had an extra zero on my 100,000 which gives the EKG appearance. I averaged 25,000 inserts/sec over 50M records with no big knees in the performance (there is a noticeable knee on the commit though around

Re: [sqlite] Write performance question for 3.7.15

I wrote a C program doing your thing (with random data so each key is unique) I see some small knees at 20M and 23M -- but nothing like what you're seeing as long as I don't do the COMMIT. Seems the COMMIT is what's causing the sudden slowdown. When doing the COMMIT I see your dramatic slowdown

Re: [sqlite] Fwd: Write performance question for 3.7.15

Perhaps the rowid index cache gets too big? I assume you don't have any indexes of your own? Does the knee change if you say, double your cache_size? Default should be 2000; pragma cache_size=4000; -Original Message- From: sqlite-users-boun...@sqlite.org

Re: [sqlite] malloc failures on ubuntu

Of course that doesn't guarantee you're executing the same code. Only a static binary or a MD5Sum of all code in the libraries involved can ensure that. So the question is, what's different? Maybe if somebody could post a static binary for him to test that might help narrow things down to see

Re: [sqlite] table backup

I don't see in the thread where you say what journal mode you're running in or your synchronous setting. That may explain your problem and be easily fixable. The other thing is to compile your system with stack protection and see if that can trap the problem. If it's just stack corruption you

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

back in replies. So that was breaking the topic threading. Michael Black -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault Sent: Sunday, December 09, 2012 5:14 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Subjec