[sqlite] [ANN] Release: NanoStore 1.0 for Mac and iOS

NanoStore 1.0 © Webbo, L.L.C., 2010. All rights reserved. September 21, 2010 Today, Webbo is pleased to announce the release of NanoStore: http://sourceforge.net/projects/nanostore/ NanoStore is a Cocoa wrapper for SQLite, a C library that implements an embeddable SQL database engine. With

Re: [sqlite] Trouble with constraints and triggers

On 23/09/2010 3:15 p.m., Igor Tandetnik wrote: > Josh Gibbs wrote: >> CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, >> Subject TEXT); >> CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES >> Message(message_id) ON DELETE CASCADE, recipient_ID

Re: [sqlite] Trouble with constraints and triggers

Josh Gibbs wrote: > CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, > Subject TEXT); > CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES > Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES > Recipient(recipient_id)); > CREATE

[sqlite] Trouble with constraints and triggers

Hi all, I'm hoping someone can assist me with a problem I'm having creating a cascading delete operation as well as a constraint. This table is an example of the layout of my data: CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT, Subject TEXT); CREATE TABLE MessageRecipient

Re: [sqlite] Performance problems and large memory size

On Wed, Sep 22, 2010 at 10:19:27PM +0400, Max Vlasov scratched on the wall: > 1024 for everything except Windows. The Windows filesystem module > > attempts to match the page size to the minimum write block of the > > filesystem. For a typical NTFS volume, that's usually 4K. > > > Jay,

Re: [sqlite] Performance problems and large memory size

On Tue, Sep 21, 2010 at 10:24 AM, "Richard Wähnelt" wrote: > Hello there, > > I hope, someone can help me with the problem I'm having. > > The whole picture: > I'm running a .NET 3.5 application using System.Data.SQLite as provider. > Inserting Data happens via Entity Framework

Re: [sqlite] Strange cache behavior

> One real world example is a full table rereading > (rescanning) if a table occasionally has the size from cache_size +1  to > maybe 1.5*cache_size. For default sqlite cache size it's rereading of 2M to > 3M tables. Not so great disadvantage to change the algorithm. Yes, whenever one finds

Re: [sqlite] Performance problems and large memory size

1024 for everything except Windows. The Windows filesystem module > attempts to match the page size to the minimum write block of the > filesystem. For a typical NTFS volume, that's usually 4K. > > Jay, small correction, the default page_size on windows is still 1024 (checked it), there's

Re: [sqlite] Strange cache behavior

On Wed, Sep 22, 2010 at 7:12 PM, Pavel Ivanov wrote: > > Is it ok for cache to behave like this or some optimization is possible > to > > fix this? > > For this particular case I believe you can do some optimization by > making your own implementation of cache. > Also I

Re: [sqlite] Performance problems and large memory size

On Wed, Sep 22, 2010 at 04:30:46PM +0200, Michele Pradella scratched on the wall: > ok thank you, usually how big is the default page_size? 1024 for everything except Windows. The Windows filesystem module attempts to match the page size to the minimum write block of the filesystem. For

Re: [sqlite] Strange cache behavior

> Is it ok for cache to behave like this or some optimization is possible to > fix this? For this particular case I believe you can do some optimization by making your own implementation of cache. Also I believe such "strange" behavior of cache is pretty much explainable. Remember that standard

[sqlite] Strange cache behavior

Hi, playing with my admin about cache size (there was a question today related to the cache size), noticed a strange thing with cache. It's Windows, initially it was v3.6.10, but the same is for 3.7.2 I sometimes mentioned that I can track vfs requests for every select in my admin. In this case

Re: [sqlite] Performance problems and large memory size

ok I think the default is 1024. So for 2000 of cache size: (100+1024)*2000=2,2MB Il 22/09/2010 16.30, Michele Pradella ha scritto: >ok thank you, usually how big is the default page_size? > > Il 22/09/2010 16.17, Jay A. Kreibich ha scritto: >> On Wed, Sep 22, 2010 at 12:02:33PM +0200,

Re: [sqlite] Performance problems and large memory size

ok thank you, usually how big is the default page_size? Il 22/09/2010 16.17, Jay A. Kreibich ha scritto: > On Wed, Sep 22, 2010 at 12:02:33PM +0200, Michele Pradella scratched on the > wall: >>I have a question about "PRAGMA cache_size" >> if I use the default value(2000) and I use the

Re: [sqlite] Import and export databases from/to SQL dump files

On Wed, 22 Sep 2010, Nimish Nayak wrote: > I saw the shell.c file in src and figured out how exactly it exports > databases to SQL dump files and implemented the same in C. But i could not > find anything for importing the databases to SQL dump files. I've not looked at the source code, but

[sqlite] Import and export databases from/to SQL dump files

Hi I am trying to understand how to Import and export databases from/to SQL dump files I saw the shell.c file in src and figured out how exactly it exports databases to SQL dump files and implemented the same in C.But i could not find anything for importing the databases to SQL dump files So

Re: [sqlite] Performance problems and large memory size

On Wed, Sep 22, 2010 at 12:02:33PM +0200, Michele Pradella scratched on the wall: > I have a question about "PRAGMA cache_size" > if I use the default value(2000) and I use the default value for the > page size, what is the max memory size sqlite can reach in a request? The *cache* can grow

Re: [sqlite] Performance problems and large memory size

> if I use the default value(2000) and I use the default value for the > page size, what is the max memory size sqlite can reach in a request? I believe for Windows it's about 2Mb per database with shared cache mode on and 2Mb per connection with shared cache mode off. But "default page size"

Re: [sqlite] group_concat + distinct

afaik "group_concat(distinct a, ',')" is not allowed. "group_concat(distinct a)" or "group_concat(a, ',')" do work. It is mentioned somewhere in the docs. Jan Am 22.09.2010 14:00, schrieb Wiktor Adamski: > SQLite version 3.7.2 > Enter ".help" for instructions > Enter SQL statements terminated

[sqlite] group_concat + distinct

SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> select group_concat(distinct a) from t; sqlite> select group_concat(distinct a, ',') from t; Error: DISTINCT aggregates must have exactly one argument Both queries

Re: [sqlite] Keeping a table ordered

On 22 Sep 2010, at 12:34pm, Ian Hardingham wrote: > Great, thanks Simon. No problem. Understanding indexes is easy if you imagine trying to find the records yourself. > Just how fast will my Select be? About 110, possibly 130 depending. Your question is meaningless: depends on hardware,

Re: [sqlite] Keeping a table ordered

Great, thanks Simon. Just how fast will my Select be? Will it be order(n) with the number of records being returned? Thanks, Ian On 22/09/2010 12:32, Simon Slavin wrote: > On 22 Sep 2010, at 11:22am, Ian Hardingham wrote: > >> I have the following table: >> >> infPlayTable (id INTEGER

Re: [sqlite] Keeping a table ordered

On 22 Sep 2010, at 11:22am, Ian Hardingham wrote: > I have the following table: > > infPlayTable (id INTEGER PRIMARY KEY AUTOINCREMENT, infId INTEGER, name > TEXT NOT NULL UNIQUE, score REAL) > > I often need to do the following: > > SELECT name, score FROM infPlayTable WHERE infId = 670

[sqlite] Keeping a table ordered

Hey guys. I have the following table: infPlayTable (id INTEGER PRIMARY KEY AUTOINCREMENT, infId INTEGER, name TEXT NOT NULL UNIQUE, score REAL) I often need to do the following: SELECT name, score FROM infPlayTable WHERE infId = 670 ORDER BY score DESC What is the syntax for the index I

Re: [sqlite] Performance problems and large memory size

I have a question about "PRAGMA cache_size" if I use the default value(2000) and I use the default value for the page size, what is the max memory size sqlite can reach in a request? Il 21/09/2010 19.31, Pavel Ivanov ha scritto: >> Is Sqlite somewhere caching data? If so, how do I disable it

Re: [sqlite] Overflow Page

> From: slav...@bigfraud.org > Date: Wed, 22 Sep 2010 10:30:27 +0100 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Overflow Page > > Is that perhaps the last page in the list ? According to > > http://www.sqlite.org/fileformat.html#overflow_page_chains > > the value is undefined

Re: [sqlite] Overflow Page

On 22 Sep 2010, at 8:45am, Scott Weigand wrote: >> Starts on a page boundary. The first four bytes are the >> next overflow page number (or 0 for the last page in a chain). > > Thanks Dan. Have you ever come across a database page that starts with > 0x100? The database file is roughly

Re: [sqlite] Overflow Page

> > Starts on a page boundary. The first four bytes are the > next overflow page number (or 0 for the last page in a chain). Thanks Dan. Have you ever come across a database page that starts with 0x100? The database file is roughly 328KiB, so there is no way that this page points to

Re: [sqlite] Overflow Page

On Sep 22, 2010, at 12:23 PM, Scott Weigand wrote: > > Hello List, > Apologies if this is the wrong list to post to. Can anyone tell me > if an overflow page is encapsulated within a B-Tree page or if it > starts on a standard page boundary and has its first 4 bytes as the > next overflow