Re: [sqlite] is it really ok to allow non-aggregates in an aggregate line?

This is just one replacement for "distinct on" clause, as example. And you can use any sort order for non-aggregate values in your group so some queries are more simple than equal "distinct on" form in other DBMS (PostgreSQL, etc.). The feature is extremely useful for many applications.

[sqlite] Foreign key on different database - possible?

Hi all I am using SQLite 3.7.2 on Fedora 10. I have multiple databases, which I can access concurrently by using the 'attach' command, and then referring to each table using 'database.tablename'. It works well. Now I want to create a foreign key constraint where the parent table is in one

Re: [sqlite] Foreign key on different database - possible?

On Oct 20, 2010, at 3:59 PM, Frank Millman wrote: > Hi all > > I am using SQLite 3.7.2 on Fedora 10. > > I have multiple databases, which I can access concurrently by using > the > 'attach' command, and then referring to each table using > 'database.tablename'. It works well. > > Now I want to

Re: [sqlite] DB access privilege problem...

Quoth forforum , on 2010-10-19 22:32:28 -0700: > I am using sqlite DB and am creating my DB in USB drive(this is my > application requirement), My problem is that. when am creating my DB as a > admin in 1 system and taking the same USB in 2nd system who is having >

Re: [sqlite] Foreign key on different database - possible?

Dan Kennedy wrote: > > On Oct 20, 2010, at 3:59 PM, Frank Millman wrote: > > > Hi all > > > > I am using SQLite 3.7.2 on Fedora 10. > > > > I have multiple databases, which I can access concurrently > by using > > the > > 'attach' command, and then referring to each table using > >

Re: [sqlite] Foreign key on different database - possible?

On Oct 20, 2010, at 4:47 PM, Frank Millman wrote: > Dan Kennedy wrote: >> >> On Oct 20, 2010, at 3:59 PM, Frank Millman wrote: >> >>> Hi all >>> >>> I am using SQLite 3.7.2 on Fedora 10. >>> >>> I have multiple databases, which I can access concurrently >> by using >>> the >>> 'attach' command,

Re: [sqlite] Foreign key on different database - possible?

Quoth Frank Millman , on 2010-10-20 11:47:06 +0200: > Ok, thanks. > > Is there any chance of it being considered for a future release? Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign key". > Frank ---> Drake Wilson

[sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

tested with sqlite-3.7.3 Hello, I'm trying to use the collations with BLOB because I am sorting something else that text. For example I have some types which can not be used as-is with SQLite because these types can be more complex. Then I have a collation which provides the right order. But

Re: [sqlite] Foreign key on different database - possible?

Drake Wilson wrote: > > Quoth Frank Millman , on 2010-10-20 > 11:47:06 +0200: > > Ok, thanks. > > > > Is there any chance of it being considered for a future release? > > Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign > key". > Thanks, Drake. I

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Le 20.10.2010 12:32, Mathieu Schroeter a écrit : I've made a small program with this output (please, look at the attached C code _first_): Mmmh.. sorry but the previous attached code has at least one error and some parts commented. And now I see that without EXPLAIN QUERY PLAN, the comparison

Re: [sqlite] Foreign key on different database - possible?

On 20 Oct 2010, at 12:11pm, Frank Millman wrote: > I am writing a generalised business/accounting application, which is > multi-company. I have got it working using MS SQL Server and PostgreSQL as > databases, and now I want to add sqlite3 as a third option, for small > single-user systems and

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Mathieu Schroeter wrote: > Le 20.10.2010 12:32, Mathieu Schroeter a écrit : >> I've made a small program with this output (please, look at >> the attached C code _first_): > > Mmmh.. sorry but the previous attached code has at least one error > and some parts commented. And

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Le 20.10.2010 14:14, Igor Tandetnik a écrit : > Mathieu Schroeter wrote: >> Le 20.10.2010 12:32, Mathieu Schroeter a écrit : >>> I've made a small program with this output (please, look at the >>> attached C code _first_): >> >> Mmmh.. sorry but the previous attached code

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Quoth Mathieu Schroeter , on 2010-10-20 15:41:51 +0200: > Thanks for your interesting answer. > > The first plan looks the best but it does not explain the ORDER BY. > If I could done this job with pencil and paper, my way will be: > > a) enumerate all entries in lol > b)

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

On Wed, Oct 20, 2010 at 9:41 AM, Mathieu Schroeter wrote: > > The first plan looks the best but it does not explain the ORDER BY. > If I could done this job with pencil and paper, my way will be: > > a) enumerate all entries in lol > b) for each entry look up a corresponding

Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3

>> On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: >> > I have a query which takes 17 minutes to run with 3.7.3 against 800ms >> > with 3.7.2 >> > >> > > Thank you for the report. > > Can you please send your complete schema. The query is useful in > combination

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Le 20.10.2010 15:51, Drake Wilson a écrit : > Quoth Mathieu Schroeter, on 2010-10-20 15:41:51 +0200: >> Thanks for your interesting answer. >> >> The first plan looks the best but it does not explain the ORDER BY. >> If I could done this job with pencil and paper, my way will

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

> It seems legitimate to use the idx_foobar because it is already > sorted.. no? Yes, it is sorted. So for example you have 2 values and you need to put them in order. You know that these values exist somewhere in the index in exact order you need. How would you find the order? You'll need to

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

Le 20.10.2010 16:44, Pavel Ivanov a écrit : >> It seems legitimate to use the idx_foobar because it is already >> sorted.. no? > > Yes, it is sorted. So for example you have 2 values and you need to > put them in order. You know that these values exist somewhere in the > index in exact order you

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

> Yes, okay... I was imagining an index like this: > > /* for one data in a table */ > struct data { >   void *value; >   ... >   const struct idx *idx;  /* ptr on the entry in 'idx_foobar' */ > } *my_data_in_the_table To fix your imagination just ask yourself a question: what if I have several

Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3

On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: > I have a query which takes 17 minutes to run with 3.7.3 against 800ms > with 3.7.2 > > The query is: > > SELECT x.sheep_no, x.registering_flock, x.date_of_registration > FROM sheep x LEFT JOIN > Regarding: "Turning

Re: [sqlite] Collations, BLOB and index - strange output provided by EXPLAIN QUERY PLAN

On 20 Oct 2010, at 4:10pm, Mathieu Schroeter wrote: > /* for one data in a table */ > struct data { > void *value; > ... > const struct idx *idx; /* ptr on the entry in 'idx_foobar' */ > } *my_data_in_the_table You're still thinking too computery. Think of an index the way you'd think

[sqlite] Strange position as a result of ATTACH

I am testing an application written in PHP, so all sqlite access is through PDO. In a particular situation I am scanning a directory for filenames with a '.db' extension and attaching to each database in turn to do something with it. I am using a prepared statement ($astmt) to do the attach,

Re: [sqlite] Strange position as a result of ATTACH

Alan Chandler wrote: > The first time round the loop seems to work fine, but the second time > round the loop, the $astmt->execute(); trying to attach to the file > fails with SQLITE_SCHEMA Any query that changes the schema of the database invalidates all prepared

Re: [sqlite] DB access privilege problem...

On Tue, 19 Oct 2010 22:32:28 -0700 (PDT), forforum wrote: > > Hi, >I am using sqlite DB and am creating my DB in USB drive(this is my > application requirement), My problem is that. when am creating my DB as a > admin in 1 system and taking the same USB in 2nd system

Re: [sqlite] DB access privilege problem...

On 20 Oct 2010, at 8:07pm, Kees Nuyt wrote: > The easiest way to solve your problem is to format the USB > stick as a FAT filesystem, not NTFS. Other than the > "readonly" bit (which is not set on your database file) FAT > doesn't support ownership, nor ACL's. Or just create the database file

[sqlite] Detach says database is locked

Further to my other post related to attaching to databases with PHP PDO, I have now managed to ATTACH OK However, when I come to DETACH, I am getting a Database is locked error when I try and execute it. The only thing happening to that database in between ATTACH and DETACH is a single row

Re: [sqlite] Detach says database is locked

Alan Chandler wrote: > Further to my other post related to attaching to databases with PHP PDO, > I have now managed to ATTACH OK > > However, when I come to DETACH, I am getting a Database is locked error > when I try and execute it. > > The only thing happening to

Re: [sqlite] Detach says database is locked

On 21 Oct 2010, at 12:27am, Alan Chandler wrote: > The only thing happening to that database in between ATTACH and DETACH > is a single row SELECT If you try it without the SELECT does it work ? Does the SELECT return any results ? Simon. ___