Re: [sqlite] Seems like a bug in the parser

[Jonathan Ellis]

On 8/23/06, Andrew McCollum <[EMAIL PROTECTED]> wrote:

I find this feature useful, especially in queries which use aggregate
functions, such as the following:

SELECT sum(a) FROM tbl GROUP BY b

The question should be what the compelling reason is to remove a useful
feature.


... And *that* is exactly why Windows will always be full of security holes.

-Jonathan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Loading a mysql file into a sqlite database?

[Jonathan Ellis]

On 8/21/06, John Salerno <[EMAIL PROTECTED]> wrote:

Hi guys. Is it possible to load a sql file that is for a MySQL
database into a SQLite database? Does it just work normally, or would
some tweaking be needed? Or does this just not work?


Usually you will need some tweaking.  One particularly common
incompatibility is the way MySQL and Sqlite specify auto-incremented
primary keys.

-Jonathan

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] creating indexes in attached databases

[Jonathan Ellis]

On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Jonathan Ellis" <[EMAIL PROTECTED]> wrote:
> sqlite> attach 'foo.db' as foo;
> sqlite> create table foo.bar (i int);
> sqlite> create index foo.bar_i on foo.bar(i);


Should be:

   create index foo.bar_i on bar(i);


Thanks, that fixed it.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] indexes with ORDER BY on columns from different tables

[Jonathan Ellis]

On 8/19/06, Gerry Snyder <[EMAIL PROTECTED]> wrote:

Jonathan Ellis wrote:
> (was the "+" some kind of shorthand I'm unfamiliar with?)
>
Yes. It tells sqlite not to use those fields as an index.


Ah, I see.  When I tried "+f.mtime" it gave a "no such column" error,
but that was because f was in the subquery now.

So, now I've tried it with various combinations of + but I still can't
get the file name index used.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] indexes with ORDER BY on columns from different tables

[Jonathan Ellis]

On 8/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Jonathan Ellis" <[EMAIL PROTECTED]> wrote:
> When ordering by columns from two tables, sqlite isn't using the index
> on the first column.
>
> explain query plan SELECT *
> FROM files f, file_info fi
> WHERE f.id = fi.file_id
> ORDER BY f.name, fi.mtime;
>
>
> Is there a workaround?

Try this:

  SELECT * FROM 
  ORDER BY +f.name, +fi.mtime;



Doesn't seem to help, if I've understood correctly:

explain query plan
SELECT * FROM (
   SELECT *
   FROM files f, file_info fi
   WHERE f.id = fi.file_id)
ORDER BY name, mtime;

0|0|TABLE files AS f
1|1|TABLE file_info AS fi WITH INDEX info_by_file

(was the "+" some kind of shorthand I'm unfamiliar with?)

[sqlite] indexes with ORDER BY on columns from different tables

[Jonathan Ellis]

When ordering by columns from two tables, sqlite isn't using the index
on the first column.

explain query plan SELECT *
FROM files f, file_info fi
WHERE f.id = fi.file_id
ORDER BY f.name, fi.mtime;

0|0|TABLE files AS f
1|1|TABLE file_info AS fi WITH INDEX info_by_file

If I drop the secondary order (to simply "ORDER BY f.name") then it
does use the index:

0|0|TABLE files AS f WITH INDEX files_by_name ORDER BY
1|1|TABLE file_info AS fi WITH INDEX info_by_file

Is there a workaround?  Here are sample tables:

CREATE TABLE files (
   id  integer PRIMARY KEY,
   nametext NOT NULL
);
CREATE INDEX files_by_name on files(name);

CREATE TABLE file_info (
   file_id int NOT NULL REFERENCES files,
   mtime   int NOT NULL
);
CREATE INDEX info_by_file on file_info(file_id);

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] creating indexes in attached databases

[Jonathan Ellis]

sqlite> attach 'foo.db' as foo;
sqlite> create table foo.bar (i int);
sqlite> create index foo.bar_i on foo.bar(i);
SQL error: near ".": syntax error
sqlite>

Is there another way to do this?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] 3-way join fails

[Jonathan Ellis]

Thanks, that is a good workaround.

Is this a bug or a "feature?"

On 7/24/06, Joe Wilson <[EMAIL PROTECTED]> wrote:


SQLite is having trouble resolving loans.book_id without an alias...

SELECT * FROM books LEFT OUTER JOIN (
select loans.book_id LBID from users JOIN loans
ON users.name = loans.user_name
) ON books.id = LBID;

--- Jonathan Ellis <[EMAIL PROTECTED]> wrote:

> Not sure what's wrong with this SQL; it works fine with postgresql:
>
> sqlite> SELECT * FROM books LEFT OUTER JOIN (users JOIN loans ON
users.name=
> loans.user_name) ON books.id = loans.book_id;
> SQL error: no such column: loans.book_id

[sqlite] 3-way join fails

[Jonathan Ellis]

Not sure what's wrong with this SQL; it works fine with postgresql:

sqlite> SELECT * FROM books LEFT OUTER JOIN (users JOIN loans ON users.name=
loans.user_name) ON books.id = loans.book_id;
SQL error: no such column: loans.book_id

The table definitions are as follows:

CREATE TABLE books (
   id   integer PRIMARY KEY,
   titletext NOT NULL,
   published_year   char(4) NOT NULL,
   authors  text NOT NULL
);

CREATE TABLE users (
   name varchar(32) PRIMARY KEY,
   emailvarchar(128) NOT NULL,
   password varchar(128) NOT NULL,
   classnametext,
   adminint NOT NULL -- 0 = false
);

CREATE TABLE loans (
   book_id  int PRIMARY KEY REFERENCES books(id),
   user_namevarchar(32) references users(name)
   ON DELETE SET NULL ON UPDATE CASCADE,
   loan_datedate DEFAULT current_timestamp
);