Re: [sqlite] Composite primary key with autoincrement

On Wed, Feb 16, 2011 at 12:33 AM, Simon Slavin wrote: > Or to put it another way, if a column is autoincrement and you don't intend > to mess with the values manually, it's going to be unique anyway, so it makes > a good simple short primary key.  You may have good reasons

Re: [sqlite] Sqlite .NET 4.0 provider available?

On Tue, Feb 15, 2011 at 5:20 PM, Simon Slavin wrote: > > On 15 Feb 2011, at 8:31pm, Matthew Adams wrote: > >> I'm a bit confused with the current level of support for Sqlite on >> .NET.  I've seen both http://sqlite.phxsoftware.com and >>

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

On 16 Feb 2011, at 5:28am, David M. Cotter wrote: > iTunes has "update search results as you type" speed even when you have a > hundred thousand songs and you're searching on a partial string on all meta > data columns. > > how on earth do they do that? > > i'm under the impression it uses

Re: [sqlite] Sqlite .NET 4.0 provider available?

On 16 Feb 2011, at 9:08am, Matthew Adams wrote: > On Tue, Feb 15, 2011 at 5:20 PM, Simon Slavin wrote: > >>> When is official support for .NET 4.0 expected? >> >> 'official' from the SQLite team ? Never. SQLite's official support is for >> two sets of source code

[sqlite] Performance Problem

Hi, I'm using Motorola MC55 device, with 2GB external memory card. For the SQlite Db I have used the following Pragma values PRAGMA cache_size = 16000 PRAGMA temp_store = 2 PRAGMA synchronous = OFF PRAGMA locking_mode = EXCLUSIVE for some performance improvement For insertion of records I

[sqlite] SqLIte on new operating system

Hello All, Can anyone please tell me the steps to be followed for porting the SqLite on other OS? Info provided in the website is not in detail. Should my operating system support VFS?? how to find out if it is supported ? in the help file of my OS there is no info on VFS. Thanks and Best

[sqlite] bug in older version of sqlite

(with apologies for no message threading - my message was accepted but my list membership is still pending, I guess - should I have received an address-confirmation email, or am I awaiting an admin's sign-off on my list membership?) > You should close the cursor after executing PRAGMA

Re: [sqlite] Composite primary key with autoincrement

Quoth Maciej Lotkowski , on 2011-02-16 09:45:45 +0100: > It's not about the real use case. I understand, that having such a > primary key probably doesn't make much sense. I'm asking if it is > technically possible to create a table like > > create table foo ( > id

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

> I'll give you another failure point that most people never see or think of. > > I used to manage numerous Linux systems with RAID-5. One time I had a drive > fail, the spare kicked in, and then during the rebuild a 2nd drive > failed...hosing the RAID (i.e. two failed disks). > > The problem

Re: [sqlite] SqLIte on new operating system

On 16 Feb 2011, at 11:41am, Anil A Kumar wrote: > Can anyone please tell me the steps to be followed for porting the SqLite > on other OS? > Info provided in the website is not in detail. If you have a C compiler (or cross-compiler) for your new OS you might just grab the source code and try

Re: [sqlite] Performance Problem

On Wed, Feb 16, 2011 at 6:13 AM, wrote: > Hi, > > I'm using Motorola MC55 device, with 2GB external memory card. > > For the SQlite Db I have used the following Pragma values > > PRAGMA cache_size = 16000 > PRAGMA temp_store = 2 > PRAGMA synchronous = OFF > PRAGMA

Re: [sqlite] Performance Problem

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

Re: [sqlite] Sqlite .NET 4.0 provider available?

SQLite.NET is being transitioned to sqlite.org ... you'll notice the new site is http://system.data.sqlite.org -- it's being taken over by the fine folks at sqlite.org, specifically Shane Harrelson who has the lead on the project. I've been working with Dr. Hipp and Shane on the transition.

Re: [sqlite] Performance Problem

On the MC55 and MC70 we use with Sqlite 3.5.9: PRAGMA temp_store = MEMORY PRAGMA journal_mode = PERSIST PRAGMA journal_size_limit = 50 On 2/16/2011 5:24 AM, Black, Michael (IS) wrote: > Try this benchmark program and see what numbers you get. You need to compare > to other machines with the

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

On Mon, Feb 14, 2011 at 6:04 PM, Simon Slavin wrote: > > If the data is backed up when SQLite has the files closed, things are fine.   > The really hard part of this comes when you're working with systems that must > be live at all times.  Which is why you'll never see a

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

>> iTunes has "update search results as you type" speed even when you have a >> hundred thousand songs and you're searching on a partial string on all meta >> data columns. >> >> how on earth do they do that? >> >> i'm under the impression it uses CoreData, which in turn uses SQLite under >>

[sqlite] Pivot table from multiple columns

Hi all, I am trying to locate any examples (if possible) that can clue me in on taking column a and appending column b then do a pivot/group by to get the total for each unique values... I'll try to demonstrate the base data: *Column_1* *Column_2* Value AValue C Value B

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

On 16 Feb 2011, at 8:41pm, David M. Cotter wrote: >> For just a hundred thousand tracks, most of which don't have anything in >> subsidiary fields like 'Composer', SQLite can do it that fast itself. > really? > > using substring search using [LIKE '%foo%'] ?? > cuz for me it's taking about 4

Re: [sqlite] FTS slowdown with matchinfo

Dan Kennedy wrote: > > Can you make the database available for download? And > supply the exact query you are using too? I'd like to > know why this is. Thanks. > > Dan. > You can find a tarball of the DB file here: http://www.research.att.com/people/Arizmendi_Iker/geo.db.tgz This query

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

>> SELECT p.piIx FROM playlist p JOIN song s ON p.soID = s.soID WHERE p.plID = >> 99662 AND (s.name LIKE "%love％" OR s.arts LIKE "％love％" OR s.pUSD LIKE >> "％love％" OR s.pbls LIKE "％love％" OR s.genr LIKE "％love％") ORDER BY s.pbls >> ASC, s.name ASC, s.albm ASC, p.piIx ASC > > Good grief, no

Re: [sqlite] Pivot table from multiple columns

OK, this looks like it concatenated Column_1 and Column_2 and returns the count of the new unique concatenated pair? What I am looking for (and I apologize for not being clear) is a list of the unique values (Column 1 and 2 appended) and their count... I'll try to demonstrate the expected example

Re: [sqlite] will FTS speed up [LIKE '%foo%'] searches? or: how does iTunes do it?

On 16 Feb 2011, at 10:46pm, David M. Cotter wrote: >> You make a composite field which has all those fields concatted together, >> either manually or using a TRIGGER or a VIEW. Then you only have to search >> in one field. > but different playlists have different columns. > for example a disc

Re: [sqlite] Pivot table from multiple columns

On 16 February 2011 23:00, Joe Bennett wrote: > OK, this looks like it concatenated Column_1 and Column_2 and returns the > count of the new unique concatenated pair? What I am looking for (and I > apologize for not being clear) is a list of the unique values (Column > 1

[sqlite] foreign key on delete no action

Hi all, I've been implementing foreign keys in some of my older tables, as detailed at: http://www.sqlite.org/foreignkeys.html usually like this: foreign key ChildTable (ChildColumn1, ChildColumn2) references ParentTable (ParentColumn1, ParentColumn2) on delete cascade

Re: [sqlite] foreign key on delete no action

> foreign key ChildTable (ChildColumn1, ChildColumn2) > references ParentTable (ParentColumn1, ParentColumn2) > on delete no action > > I have a few questions: > > 1. What does SQLite do if the action is just left blank? Does it have the > same affect as explicitly writing

[sqlite] Compiler warnings in R-Tree code under Visual Studio Express

Hi, Microsoft Visual Studio Express C++ is available for free from http://www.microsoft.com/express/Downloads/#2010-Visual-CPP I tried to compile SQLite 3.7.5 with SQLITE_ENABLE_RTREE=1 and got the following warnings: sqlite3.c(120736): warning C4244: '=' : conversion from 'double' to 'float',