RE: [sqlite] ORDER BY of UNION?

Okay that works, but is there a way to make it work with the t1 alias? I'm porting an existing application to SQLite and changing all queries to not use aliases may be problematic if you know what I mean. Looking at the syntax page I don't see how using t1 is illegal and yet clearly as doesn't

Re: [sqlite] ORDER BY of UNION?

At 8:59 PM -0800 3/1/06, Boris Popov wrote: I can't seem to get unions to sort properly, SELECT DISTINCT * FROM (SELECT t1.ID FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID FROM PERSON t1) t1 ORDER BY t1.ID DESC results in "no such column: t1.ID" error. How would I go about sorting the result set

Re: [sqlite] Extrange files

On 3/2/06, Paul G <[EMAIL PROTECTED]> wrote: > From: "Nuno Lucas" <[EMAIL PROTECTED]> > > The only problem is that it can be a race condition between the > > closing of the handle and the actual delete command (because you can't > > delete an open file on win world). > > you lie! if the file is

RE: [sqlite] Compound Ops: syntax error?

Okay, false alarm, figured it out myself. For some reason SQLite doesn't like the extra set of parens, so the following works okay, SELECT DISTINCT * FROM (SELECT t1.ID, t1.STREET, t1.HOUSE_NUM FROM GR_ADDRESS t1 WHERE t1.ID = 1 UNION ALL SELECT t1.ID, t1.STREET, t1.HOUSE_NUM FROM GR_ADDRESS

[sqlite] ORDER BY of UNION?

I can't seem to get unions to sort properly, SELECT DISTINCT * FROM (SELECT t1.ID FROM GR_ADDRESS t1 UNION ALL SELECT t1.ID FROM PERSON t1) t1 ORDER BY t1.ID DESC results in "no such column: t1.ID" error. How would I go about sorting the result set in this case? Cheers! -Boris --

Re: [sqlite] Extrange files

- Original Message - From: "Nuno Lucas" <[EMAIL PROTECTED]> To: Sent: Wednesday, March 01, 2006 10:36 PM Subject: Re: [sqlite] Extrange files The only problem is that it can be a race condition between the closing of the handle and the actual delete command

[sqlite] Compound Ops: syntax error?

Not sure if its me, but I just can't figure out why these queries don't work. As far as I can tell looking at the docs all these compound ops are supported: UNION | UNION ALL | INTERSECT | EXCEPT Any ideas? SQL OK: SELECT t1.ID, t1.STREET, t1.HOUSE_NUM FROM GR_ADDRESS t1 SQL NOT OKAY 1: near

Re: [sqlite] Extrange files

On 3/1/06, Ralf Junker <[EMAIL PROTECTED]> wrote: > > >But I do not think that DOS has the ability > >to automatically delete a file when it is closed, so the > >files remain on disk after SQLite has finished with them. > > Just a thought: > > Wouldn't it possible to have SQLite thoughtfully

Re: [sqlite] New columns not recognized by other connections

Blake Ross <[EMAIL PROTECTED]> wrote: > 4. In process 1, execute: ALTER TABLE test ADD bar; > 5. In process 2, execute INSERT INTO test(bar) VALUES(1); > > After executing step 5, you get an SQLITE_ERROR that table "test" has no > column "bar". Re-executing the statement has no effect (i.e. at

Re: [sqlite] Running App state in db?

On Thu, Mar 02, 2006 at 12:18:21AM +0100, Elrond wrote: > I intended to only put the locks as such in the db. > When it comes to a new lock, I'll select all relevant old > locks, that might conflict, handle the conflict check in > app logic and finally insert a new lock record. (all inside > a

Re: [sqlite] Failing Transaction Help.

I think you could set pxdb->pBe->inTrans field to 0 to indicate the transaction is finished. 2006/3/2, nbiggs <[EMAIL PROTECTED]>: > I think that I just found my problem. For some stupid reason, I coded > it so that if a statement failed, then it stopped executing the rest of > the statements.

Re: [sqlite] Running App state in db?

On Wed, Mar 01, 2006 at 12:44:47PM -0600, Jim C. Nasby wrote: [...] > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; >

Re: [sqlite] Running App state in db?

On Wed, Mar 01, 2006 at 04:00:53PM -0600, Jim C. Nasby wrote: > On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > > Well, my locking data isn't as simple as "locked, not > > locked". The resource has ranges that can be locked, and it > > can be locked for reading (shared) and writing

RE: [sqlite] Failing Transaction Help.

I think that I just found my problem. For some stupid reason, I coded it so that if a statement failed, then it stopped executing the rest of the statements. Thus leaving the transaction open and causing my problems. I have since fixed it. -Original Message- From: Rob Lohman

Re: [sqlite] Failing Transaction Help.

If I'm not mistaken you still need to close the transaction. Are you doing an "end transaction" even if a statement fails (ie, a rollback is done)? - Original Message - From: "nbiggs" <[EMAIL PROTECTED]> To: Sent: Wednesday, March 01, 2006 10:24 PM Subject:

Re: [sqlite] Running App state in db?

On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > Well, my locking data isn't as simple as "locked, not > locked". The resource has ranges that can be locked, and it > can be locked for reading (shared) and writing (exclusive). > It's not really fun. Sounds to me like the best bet is to put

Re: [sqlite] Running App state in db?

Elrond. If you dont have any luck with the database way check out ACE Adaptive Communications Environment. That has wrapped all of the code for the likes of mutexes etc. So could save you a bunch of time if you need to go to option 1 especially across multiple OS's

[sqlite] Failing Transaction Help.

In my application, I am using a transaction to insert about 10 records at a time. The problem is that if one of the statements in the transaction fail, commit is not being executed. When I try creating another transaction, I get a constant error message "can not create a transaction within a

Re: [sqlite] How to secure SQLITE

Hakan VELIOGLU said: > Hi, > > I have a server that users are accessing it with ssh and publishing their > web > sites. What I want is a database support with less effor. So, > SQLite is a very good option for me to decrease the management tasks for a > database support. However, I searched the

Re: [sqlite] How to secure SQLITE

> > I have a server that users are accessing it with ssh and publishing their web > sites. What I want is a database support with less effor. So, > SQLite is a very good option for me to decrease the management tasks for a > database support. However, I searched the SQLites's web site for

[sqlite] How to secure SQLITE

Hi, I have a server that users are accessing it with ssh and publishing their web sites. What I want is a database support with less effor. So, SQLite is a very good option for me to decrease the management tasks for a database support. However, I searched the SQLites's web site for security

Re: [sqlite] Running App state in db?

On Wed, Mar 01, 2006 at 01:32:31PM -0600, Jim C. Nasby wrote: > BTW, if you're running everything on a single machine there's lots of > other ways you can do locking that don't involve the database. [...] Well, my locking data isn't as simple as "locked, not locked". The resource has ranges that

Re: [sqlite] Running App state in db?

BTW, if you're running everything on a single machine there's lots of other ways you can do locking that don't involve the database. On Wed, Mar 01, 2006 at 11:20:01AM -0800, w b wrote: > Unfortunately I think that this would lock the whole database within SQLITE > as there is no row level

Re: [sqlite] Running App state in db?

Unfortunately I think that this would lock the whole database within SQLITE as there is no row level locking, so probably not the best way to go forward, unless all of the other applications are only performing reads ? Some othe ideas that might help. Have a field in one of your

Re: [sqlite] Extrange files

On Wed, 1 Mar 2006, RalfJunker wrote: [...] Wouldn't it possible to have SQLite thoughtfully delete all files it creates when closing the database instead of relying on the operating system? [...] Hello, that would probably be possible, but not very efficient as you would have to keep a

Re: [sqlite] Running App state in db?

On Wed, Mar 01, 2006 at 07:38:58PM +0100, Elrond wrote: > > Hi, > > I'm considering to put the state of a running app into an > sqlite db. I want it in a db, so external tools can query > it and know, what the app is doing currently. > > Any hints on how to clean up the db, when the app

[sqlite] Running App state in db?

Hi, I'm considering to put the state of a running app into an sqlite db. I want it in a db, so external tools can query it and know, what the app is doing currently. Any hints on how to clean up the db, when the app crashes? (I have external resources, that I need to "lock", so the idea is to

Re: [sqlite] performance statistics

On Wed, Mar 01, 2006 at 09:25:02AM -0500, [EMAIL PROTECTED] wrote: > I am currently investigating porting my project from postgres to SQLite due > to anticipated performance issues (we will have to start handling lots more > data). My initial speed testing of handling the expanded amount data has

Re: [sqlite] performance statistics

On Wed, Mar 01, 2006 at 05:42:57PM +0100, Denis Sbragion wrote: > Hello Andrew, > > On Wed, March 1, 2006 17:31, Andrew Piskorski wrote: > > Is that in fact true? I am not familiar with how PostgreSQL > > implements the SERIALIZABLE isolation level, but I assume that > > PostgreSQL's MVCC would

Re: [sqlite] performance statistics

On Wed, Mar 01, 2006 at 05:23:05PM +0100, Denis Sbragion wrote: > Insert records as "processing by writer", update them to "ready to be > processed" with a single atomic update after a burst of inserts, update the > status of all "ready to be processed" records to the "to be processed by > reader"

Re: [sqlite] Extrange files

>But I do not think that DOS has the ability >to automatically delete a file when it is closed, so the >files remain on disk after SQLite has finished with them. Just a thought: Wouldn't it possible to have SQLite thoughtfully delete all files it creates when closing the database instead of

Re: [sqlite] performance statistics

Andrew Piskorski <[EMAIL PROTECTED]> wrote: > On Wed, Mar 01, 2006 at 10:53:12AM -0500, [EMAIL PROTECTED] wrote: > > If you use READ COMMITTED isolation (the default in PostgreSQL) > > > If it is a problem, > > then you need to select SERIALIZABLE isolation in PostgreSQL > > in which case the

Re: [sqlite] performance statistics

Quoting [EMAIL PROTECTED]: > > I anticipate 2 bottlenecks... > > 1. My anticipated bottleneck under postgres is that the DB-writing app. > must parse incoming bursts of data and store in the DB. The machine > sending this data is seeing a delay in processing. Debugging has shown > that the

Re: [sqlite] performance statistics

Hello Andrew, On Wed, March 1, 2006 17:31, Andrew Piskorski wrote: > Is that in fact true? I am not familiar with how PostgreSQL > implements the SERIALIZABLE isolation level, but I assume that > PostgreSQL's MVCC would still give some advantage even under > SERIALIZABLE: It should allow the

Re: [sqlite] performance statistics

> My question is not about extending/improving SQLite but about having an > extra tool which helps to optimize the SQL written for SQLite. So SQLite > stays indeed lightweight and fast, but the SQL it is fed with is > automatically optimized. Like I said, the optimizer tool is the programmer. In

Re: [sqlite] performance statistics

On Wed, Mar 01, 2006 at 10:53:12AM -0500, [EMAIL PROTECTED] wrote: > If you use READ COMMITTED isolation (the default in PostgreSQL) > If it is a problem, > then you need to select SERIALIZABLE isolation in PostgreSQL > in which case the MVCC is not going to give you any advantage > over SQLite.

Re: [sqlite] performance statistics

My question is not about extending/improving SQLite but about having an extra tool which helps to optimize the SQL written for SQLite. So SQLite stays indeed lightweight and fast, but the SQL it is fed with is automatically optimized. Ran On 3/1/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > On

Re: [sqlite] performance statistics

Hello DRH, On Wed, March 1, 2006 16:53, [EMAIL PROTECTED] wrote: ... > If you use READ COMMITTED isolation (the default in PostgreSQL) > then your writes are not atomic as seen by the reader. In other ... > then you need to select SERIALIZABLE isolation in PostgreSQL > in which case the MVCC is

Re: [sqlite] performance statistics

[EMAIL PROTECTED] said: > 1. My anticipated bottleneck under postgres is that the DB-writing app. > must parse incoming bursts of data and store in the DB. The machine > sending this data is seeing a delay in processing. Debugging has shown > that the INSERTS (on the order of a few thousand) is

Re: [sqlite] performance statistics

[EMAIL PROTECTED] writes: > PostgreSQL has a much better query optimizer than SQLite. > (You can do that when you have a multi-megabyte memory footprint > budget versus 250KiB for SQLite.) In your particular case, > I would guess you could get SQLite to run as fast or faster > than PostgreSQL by

Re: [sqlite] performance statistics

"Denis Sbragion" <[EMAIL PROTECTED]> wrote: > Furthermore having both a reader > and a writer at the same time the MVCC "better than row level locking" > mechanism might provide you better performances than SQLite, but here the > devil's in the detail. "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

Re: [sqlite] performance statistics

On 3/1/06, Ran <[EMAIL PROTECTED]> wrote: > In light of your answer, I wonder if it is possible to implement such > optimizer that does the hand-optimizing automatically, but of course BEFORE > they are actually being used by SQLite. > > So the idea is not to make SQLite optimizer better, but to

Re: [sqlite] performance statistics

[EMAIL PROTECTED] wrote: > wellThe database and the applications accessing the database are all > located on the same machine, so distribution across multiple machines > doesn't apply here. The system is designed so that only one application > handles all the writes to the DB. Another

Re: [sqlite] performance statistics

Hello Jason, On Wed, March 1, 2006 16:20, [EMAIL PROTECTED] wrote: ... > 1. My anticipated bottleneck under postgres is that the DB-writing app. > must parse incoming bursts of data and store in the DB. The machine > sending this data is seeing a delay in processing. Debugging has shown > that

Re: [sqlite] performance statistics

In light of your answer, I wonder if it is possible to implement such optimizer that does the hand-optimizing automatically, but of course BEFORE they are actually being used by SQLite. So the idea is not to make SQLite optimizer better, but to create a kind of SQL optimizer that gets as input

Re: [sqlite] performance statistics

Hello Serge, On Wed, March 1, 2006 16:11, Serge Semashko wrote: ... > I'm in no way a database expert, but the tests on the benchmarking page > seem a bit trivial and looks like they only test database API (data > fetching throughoutput), but not the engine performance. I would like to > see some

Re: [sqlite] performance statistics

wellThe database and the applications accessing the database are all located on the same machine, so distribution across multiple machines doesn't apply here. The system is designed so that only one application handles all the writes to the DB. Another application handles all the

RE: [sqlite] Extrange files

It works You're great! Thanks a lot -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: dimecres, 1 / març / 2006 16:03 Para: sqlite-users@sqlite.org; [EMAIL PROTECTED] Asunto: Re: [sqlite] Extrange files =?iso-8859-1?Q?Josep_Llu=EDs_Vaquer?= <[EMAIL

Re: [sqlite] performance statistics

Serge Semashko <[EMAIL PROTECTED]> wrote: >> > We started with using sqlite3, but the database has grown now to > something like 1GB and has millions of rows. It does not perform as fast > as we would like, so we looked for alternatives. We tried to convert > it to both mysql and postgresql and

Re: [sqlite] Extrange files

=?iso-8859-1?Q?Josep_Llu=EDs_Vaquer?= <[EMAIL PROTECTED]> wrote: > I'm running DOS, Sqlite version 3.2.8. Attached example files > The mailing list handler automatically strips attachments so they did not get through. The files you are seeing are probably temporary databases that SQLite

Re: [sqlite] performance statistics

[EMAIL PROTECTED] wrote: I am currently investigating porting my project from postgres to SQLite due to anticipated performance issues (we will have to start handling lots more data). My initial speed testing of handling the expanded amount data has suggested that the postgres performance

Re: [sqlite] performance statistics

[EMAIL PROTECTED] wrote: > > I am currently investigating porting my project from postgres to SQLite due > to anticipated performance issues > I do not thing speed should really be the prime consideration here. PostgreSQL and SQLite solve very different problems. I think you should choose the

RE: [sqlite] Extrange files

I'm running DOS, Sqlite version 3.2.8. Attached example files -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: dimecres, 1 / març / 2006 13:32 Para: sqlite-users@sqlite.org Asunto: Re: [sqlite] Extrange files =?iso-8859-1?Q?Josep_Llu=EDs_Vaquer?= <[EMAIL

Re: [sqlite] performance statistics

> All - > > I am currently investigating porting my project from postgres to SQLite due > to anticipated performance issues (we will have to start handling lots more > data). My initial speed testing of handling the expanded amount data has > suggested that the postgres performance will be

[sqlite] performance statistics

All - I am currently investigating porting my project from postgres to SQLite due to anticipated performance issues (we will have to start handling lots more data). My initial speed testing of handling the expanded amount data has suggested that the postgres performance will be unacceptable.

Re: [sqlite] Extrange files

=?iso-8859-1?Q?Josep_Llu=EDs_Vaquer?= <[EMAIL PROTECTED]> wrote: > Hi, I'm new in Sqlite. > > I'm trying to develop an aplication using Sqlite and it started to work > fine. I did a relatively simple Schema and builded a few views to access > easily. The problem started when I tried to

[sqlite] Extrange files

Hi, I'm new in Sqlite. I'm trying to develop an aplication using Sqlite and it started to work fine. I did a relatively simple Schema and builded a few views to access easily. The problem started when I tried to access at one of those views (only in one of those). Each select I do to