[sqlite] Error when matching column names in a view

Any comments would be appreciated, SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table one (id integer primary key); sqlite> create table two (id integer primary key); sqlite> select one.id, two.id from one join two where one.id=two.id; sqlite> create view three as select one.

Re: [sqlite] Join on same table and performance.

=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > > I downloaded your database and the query above was indeed slow. > > But then I ran "ANALYZE" so that SQLite can gather statistics > > on the various indices, then reran the query. This time, SQLite > > was able to use the ANALYZE r

Re: [sqlite] Join on same table and performance.

=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > > create table Media (id integer, key, value, source integer); > and the indexes: > create unique index key_idx on Media (id,key,source); > create index prop_idx on Media (key,value); > create index source_idx on Media (key,source);

Re: [sqlite] Join on same table and performance.

I downloaded your database and the query above was indeed slow. But then I ran "ANALYZE" so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The result

Re: [sqlite] Join on same table and performance.

Friday, March 24, 2006, 2:33:36 PM, Tobias Rundström wrote: > [...] > The schema is this: > create table Media (id integer, key, value, source integer); > and the indexes: > create unique index key_idx on Media (id,key,source); > create index prop_idx on Media (key,value); > create index source_i

Re: [sqlite] Scrolling thru an index

In SQLite these can be combined into one query that gets the desired rows. select * from mytable where Name >= ( select Name from mytable where Name < 'Sprenkle' order by Name desc limit 1 offset 50)order by Name limit 101; This query works as expected in SQLite so it should be

Re: [sqlite] Join on same table and performance.

=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > > create table Media (id integer, key, value, source integer); > and the indexes: > create unique index key_idx on Media (id,key,source); > create index prop_idx on Media (key,value); > create index source_idx on Media (key,source);

Re: [sqlite] Join on same table and performance.

El 24-03-2006, a las 16:08, [EMAIL PROTECTED] escribió: Elcin Recebli <[EMAIL PROTECTED]> wrote: Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (i

Re: [sqlite] Join on same table and performance.

Elcin Recebli <[EMAIL PROTECTED]> wrote: > Hi. > > You're joining the table with itself using 'id'. However, there's no index > just on that field. I'm not sure how exactly SQLite utilises indices, but it > might be unable to use index on (id,key,source) triple to optimise > calculation of "m1.

Re: [sqlite] Join on same table and performance.

Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of "m1.id = m2.id". Does this sound sensible? Cheers. ---

[sqlite] Join on same table and performance.

Hello, I guess this subject is a bit worn out. But I am having scalabillity problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle insanely huge media libraries, playlists and clients. Our goal is to be able to run medialibs with 50.000 files without problem. Our backend is SQ

Re: [sqlite] Scrolling thru an index

> In SQLite these can be combined into one query that gets the desired rows. > > select * from mytable where Name >= ( > select Name from mytable where Name < 'Sprenkle' order by Name > desc limit 1 offset 50) > order by Name limit 101; > > This query works as expected in SQLite so

Re: [sqlite] Scrolling thru an index

JP wrote: Jay Sprenkle wrote: My application is geared towards users who want to find a specific name in a list of names, and then want to have the possibility to scroll backwards or forwards. For example, if I search for "Sprenkle" I want to show the user a window with "Sprenkle" in the mid

RE: [sqlite] no errors,but not executed: Stored procedures in triggers

When I create trigger by giving select sp_dosomethingfunc(); THE TRIGGER IS GETTING CREATED without any errors & stmts within main r executed, but stmts within this custom function- > sp_dosomethingfunc(sqlite3_context *context, int argc,sqlite3_value **argv)-- r not excuted. Also what is that

Re: [sqlite] Stored procedures in triggers

Is there any chance that CREATE TEMP TABLE will be available within triggers? The idea is that those tables can be used only within the triggers themselves. Thanks, Ran On 3/24/06, Ralf Junker <[EMAIL PROTECTED]> wrote: > > > >Would it be useful to have recursive DELETE triggers > >even without

Re: [sqlite] Stored procedures in triggers

>Would it be useful to have recursive DELETE triggers >even without recursive INSERT or UPDATE triggers? Recursive DELETE triggers would certainly be usefull and have in fact already been asked for on this list occasionally. They would allow to move referential integrity of hierarchical data o

Re: [sqlite] Re: concers about database size

On Wed, Mar 22, 2006 at 07:35:32PM +0100, Daniel Franke wrote: > > I can tell you that even 750M rows wouldn't be a huge deal for PostgreSQL, > > and 20G of data is nothing. Though your table would take somewhere > > around 30G due to the higher per-row overhead in PostgreSQL; I'm not > > really su

Re: [sqlite] no errors,but not executed: Stored procedures in triggers

On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > My problem is not with cascading or using recursive triggers. > Actually, > I am using "sqlite3_create_function" to execute some queries(like > insert or update etc.) but the control does not pass to the custom > function at all, ie.,

[sqlite] no errors,but not executed: Stored procedures in triggers

My problem is not with cascading or using recursive triggers. Actually, I am using "sqlite3_create_function" to execute some queries(like insert or update etc.) but the control does not pass to the custom function at all, ie., say a user-defined function sp_dosomethingfunc(sqlite3_context *cont

RE: [sqlite] Stored procedures in triggers

> > Thoughts? Would making recursive triggers an error rather > than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. > I'm also looking at making DELETE triggers recursive. I can > do th

Re: [sqlite] Stored procedures in triggers

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Cascading triggers are supported [in SQLite], recursive triggers are not. > That is, if you have an insert trigger on table A which, say, inserts > into table B, and there's an insert trigger on table B, it will run. But > if this latter trigger turn

[sqlite] Re: Stored procedures in triggers

A small correction: I was wrong about SQLite not supporting cascading triggers. Cascading triggers are supported, recursive triggers are not. That is, if you have an insert trigger on table A which, say, inserts into table B, and there's an insert trigger on table B, it will run. But if this la

Re: [sqlite] "Safe" maximum numbers of tables?

Tito Ciuro <[EMAIL PROTECTED]> wrote: > Hello, > > I was reading the FAQ and I came across this statement: > > > > In practice, SQLite must read and parse the original SQL of all > > table and index declarations everytime a new database file is > > opened, so for the best performance of sqli

Re: [sqlite] Stored procedures in triggers

See the email of Igor Tandetnik from 18-Dec-2005: "Vishal Kashyap" wrote > Is their any way we can write simple stored procedures or functions > in sqlite. If yes please do guide me I need this functionality in one > of my open source project. Not in the usual sense, meaning some language that ge

Re: AW: [sqlite] Bad performance with large joins?

"Christian Schwarz" <[EMAIL PROTECTED]> writes: >> Practically one such line for each table and each column. > > Why on each column? I just took the existing DB-import-script from that project. But ... > For example, when your where-clause contains columns A, B and C (in > this order) you should

[sqlite] Stored procedures in triggers

 Hi, Can you tell me how to create a stored procedure in an sqlite3 database and use the same in a trigger? Please provide an example (as complete as possible). In the stored procedure I need to execute few queries on some tables. Can you tell me how to do that also? Any help is deeply

AW: [sqlite] Bad performance with large joins?

> >> > Have you tried creating indexes on your rows. > >> > [..] > >> > I suggest you add indexes on text_val > >> > >> Yes. I use > >> > >> create index text_val_idx on geodb_textdata(text_val); > >> > > > > This index seems pretty useless. You're querying against > > geodb_textdata.loc_id and g

[sqlite] SQLite: which platforms are supported?

Hi, We consider using SQLite for some of our applications and I would like to ask whether there are existing cases of using SQLite on the following OSes: - Mac - Windows - Linux and with following architectures - x86 - x86_64 - ia64 I just would like to get some information about whether there

Re: AW: [sqlite] Bad performance with large joins?

"Christian Schwarz" <[EMAIL PROTECTED]> writes: >> > Have you tried creating indexes on your rows. >> > [..] >> > I suggest you add indexes on text_val >> >> Yes. I use >> >> create index text_val_idx on geodb_textdata(text_val); >> > > This index seems pretty useless. You're querying against > g

AW: [sqlite] Bad performance with large joins?

> > Have you tried creating indexes on your rows. > > [..] > > I suggest you add indexes on text_val > > Yes. I use > > create index text_val_idx on geodb_textdata(text_val); > This index seems pretty useless. You're querying against geodb_textdata.loc_id and geodb_textdata.text_type. So you sho

Re: [sqlite] Bad performance with large joins?

I will research on that one! On Fri, 2006-03-24 at 09:57 +0100, Steffen Schwigon wrote: > Roger <[EMAIL PROTECTED]> writes: > > Have you tried creating indexes on your rows. > > [..] > > I suggest you add indexes on text_val > > Yes. I use > > create index text_val_idx on geodb_textdata(text_v

Re: [sqlite] Bad performance with large joins?

Roger <[EMAIL PROTECTED]> writes: > Have you tried creating indexes on your rows. > [..] > I suggest you add indexes on text_val Yes. I use create index text_val_idx on geodb_textdata(text_val); I just experimented with dropping/recreating indexes and it sometimes even feels a bit faster *with

Re: [sqlite] Bad performance with large joins?

Have you tried creating indexes on your rows. I am working with a particularly large database, with more than 40 000 Records, i had a timeout problem, but as soon as i created indexes on my key rows, the speed was amazing and i use complex queries especially for my reports. I suggest you add index

[sqlite] Bad performance with large joins?

Hi! I'm trying to use the database of the OpenGeoDB project (http://opengeodb.hoppe-media.com/index.php?FrontPage_en) with SQLite. Simple example queries from opengeodb work ok, like SELECT text_val FROM geodb_textdata WHERE text_type=50010 /* NAME */ AND loc_id=27431;