Re: [sqlite] query optimization

d b wrote: > 1. delete from emp where key = '123'; > 2. delete from emp where key = '123' and name = 'abc'; > > if Key available, execute 1st query. if key and name available, execute 2nd > query. What do you mean with "available"? Regards, Clemens

Re: [sqlite] query optimization

Assuming you are using parameters you may use something like: DELETE FROM emp WHERE key=?1 AND CASE ?2 IS NULL THEN 1 ELSE name=?2; BR 2013/11/18 d b > Hi, > > > I am trying to make single query instead of below two queries. Can > somebody help? > > 1. delete from

Re: [sqlite] EXPLAIN QUERY PLAN

On 18 Nov 2013, at 7:46am, Hick Gunter wrote: > I use EXPLAIN to make sure that my virtual tables' xFilter methods get called > with the proper parameters. > > I guess I'm a geek ;) If you're writing virtual table code, then yes. Simon.

Re: [sqlite] query optimization

Hi Luis, Those are parameters. This is the query after replacing with ?1 and ?2. delete from emp where key = '123' and (case when name = 'abc' is null THEN 1 else name = 'abc' end); It covered "delete from emp where key = '123' and name = 'abc';" but not other query. I tried with "select

Re: [sqlite] Using EXPLAIN to validate vtable xFilter [WAS: EXPLAIN QUERY PLAN]

This is an EXPLAIN QUERY PLAN of one of the statements used to generate a report: sele order from deta - 3 0 0 SCAN TABLE sgb_val_hist_ticket AS tx VIRTUAL TABLE INDEX 1: (~0 rows) 3 1 1 SCAN TABLE tag_141190 AS

Re: [sqlite] query optimization

I might be missing something extraordinarily obvious... but I cannot understand the use case for this logic you have. My first response was to just use "delete from emp where key=123" and be done with it, who cares what the name is, right? But then it dawned on me that you may for some reason

Re: [sqlite] query optimization

Oops, misprint... name won't be null of course, the parameter needs to be null, kindly replace the queries offered like this: delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 )); or in the second form: delete from emp where ( key = ?1 ) AND (( ?2 = '' ) OR (

Re: [sqlite] query optimization

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith wrote: >Oops, misprint... > >name won't be null of course, the parameter needs to be null, kindly replace >the queries offered like this: > > delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 )); > >or in

Re: [sqlite] query optimization

Well this is the reason for my initial misunderstanding - which I then thought I had wrong, but either you have it wrong too... or I had it right in the first place. Ok, less cryptically now: It all depends on whether he has a Column called "name" that might be Null, or whether he has a

Re: [sqlite] query optimization

Hi RSmith, Thanks. Still, I could not delete with single query. create table if not exists emp(key integer not null, name text not null , personaldata text not null, unique(key, name)); insert into emp (key, name, personaldata) values(1, 'a', 'z'); insert into emp (key, name, personaldata)

Re: [sqlite] Using EXPLAIN to validate vtable xFilter [WAS: EXPLAIN QUERY PLAN]

On Mon, Nov 18, 2013 at 11:45 AM, Hick Gunter wrote: > This is an EXPLAIN QUERY PLAN of one of the statements used to generate a > report: > [...] > There are several CASE...END clauses and a UNION (of transaction data) > cross joined to in-memory tables (of configuration

Re: [sqlite] query optimization

Thanks, this explanation makes it easier to understand what you are tryingto achieve. I do not see any binding in your code, so let us assume you are not binding anything and just executing the query, this rework of your code should be the easiest: bool delete_emp(int key, string name = "")

Re: [sqlite] query optimization

Thanks RSmith. It works. But, I am looking for single query for prepared statements. That's the actual struggle for me. On Mon, Nov 18, 2013 at 4:24 PM, d b wrote: > Hi RSmith, > > Thanks. Still, I could not delete with single query. > > > create table if not exists

Re: [sqlite] query optimization

On 11/18/2013 7:24 AM, d b wrote: bool delete_emp(int key, string name = "") { string query = ???; if(name.length() > 0) { //needs to delete specific row. by unique key. } else { //needs to delete rows belongs to

Re: [sqlite] query optimization

DELETE FROM emp WHERE key = '123' AND (name IS NULL OR name = 'abc'); assuming "available" means "is not null ... >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of d b >Sent: Monday, 18 November, 2013 00:46

Re: [sqlite] query optimization

Hi Igor/Keith, I tried with both queries. I expect to delete all rows belongs to key 1. But not deleted. Am I missing something while writing queries? delete from emp where key = 1 and (name='' or name='f'); DELETE FROM emp WHERE key = 1 AND (name IS NULL OR name = 'f');

Re: [sqlite] help writing DELETE with JOIN

okay i figured it out. thank you all so much for your hints and suggestions!! my problem was that my "stal" marked items were the wrong items. once i marked the CORRECT items as stale, the statement worked. also i realized that the old playlist is about to be deleted by the caller of this

[sqlite] Another website typo

http://www.sqlite.org/tempfiles.html , in section "2.7 Materializations Of Views And Subqueries", says: "The rules for when a query and cannot be flattened are very complex and are beyond the scope of this document. " That should probably read, "...when a query _can_ and cannot be flattened...".

[sqlite] Typo in website

http://www.sqlite.org/tempfiles.html says, in section "2.1 Rollback Journals": "The OFF journal mode causes SQLite to the rollback journal." There seems to be a verb missing in that sentence. (Unless you were referencing the meme: "I switched the journal mode to OFF and accidentally the

[sqlite] Another website typo

http://www.sqlite.org/lockingv3.html , in section "5.0 Writing to a database file", says: "Future versions of SQLite might provide a "CHECKPOINT" SQL command that will commit all changes made so far within a transaction but retain the RESERVED lock so that additional changes can be made without

[sqlite] Punctuation in the website

Further in http://www.sqlite.org/tempfiles.html , there is what I think is infelicitous punctuation in section "2.8 Transient Indices": "The new sort algorithm will also use temporary files, but not in the same way as the current implementation, the temporary files for the new implementation will

[sqlite] More website typos

http://www.sqlite.org/howtocorrupt.html in section "2.2.1 Multiple copies of SQLite linked into the same application" says: "But the SQLite developers are aware of at least one commercial product that was release with exactly this bug." that was release --> that was released. and in section

[sqlite] [ANNOUNCEMENT] Updated: sqlite3-3.8.1-1 for Cygwin/Cygwin64

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine Changes since 3.7.17-3 = * Updated to upstream 3.8.1 release. Main new features: * Partial indexes: *

Re: [sqlite] More website typos

Thanks for the documentation bug reports. All will be fixed soon. -- D. Richard Hipp Sent from phone - Excuse brevity On Nov 18, 2013 9:16 AM, "Philip Newton" wrote: > http://www.sqlite.org/howtocorrupt.html in section "2.2.1 Multiple > copies of SQLite linked into the

Re: [sqlite] query optimization

On Mon, 18 Nov 2013 14:08:28 +0200, RSmith wrote: >Well this is the reason for my initial misunderstanding - which I then thought >I had wrong, but either you have it wrong too... or I >had it right in the first place. Ok, less cryptically now: > >It all depends on whether

Re: [sqlite] EXPLAIN QUERY PLAN

"Hick Gunter" I use EXPLAIN to make sure that my virtual tables' xFilter methods get called with the proper parameters. I guess I'm a geek ;) Geek. :-) I guess I am one too. -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Freitag, 15.

Re: [sqlite] query optimization

Thanks RSmith. It works. But, I am looking for single query for prepared statements. That's the actual struggle for me. Ok, but you give code examples that has nothing to do with prepared statements. Giving this one last push, I iwll try to ignore all you have said and simply show the best

Re: [sqlite] More website typos

On Mon, Nov 18, 2013 at 9:09 AM, Philip Newton wrote: > > "The build-in functions used to implement LIKE and GLOB must not have > been overloaded using the sqlite3_create_function() API." > > build-in --> built-int > Brilliant proof-reading. Thanks! Did you actually

[sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

It seems obvious at first glance that WITHOUT ROWID should require a table to have a PRIMARY KEY, but on second glance I don't get it. SQL itself doesn't require tables to have primary keys, so why couldn't an RDBMS have primary key-less tables? Obviously a B-Tree-based table will need *some*

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

I should add that WITHOUT ROWID ended up making a very small difference in bulk-load performance of my application, but a bigger and noticeable difference in incremental operation (where it's most welcomed and desired anyways) performance. Nico -- ___

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

I can also imagine a day when PRAGMA without_rowid_by_default = 1; will cause tables created subsequently to lack implicit rowid columns. It'd be easier to enable that if the selection of columns for the b-tree index were less consequential than requiring a PRIMARY KEY with its attendant

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 5:14 AM, Pepijn Van Eeckhoudt wrote: > I've been looking into the upcoming 'without rowid' feature implementation > to assess if it will have any impact on the OGC GeoPackage specification. > > One of the things I was wondering is what the intended

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

On 2013/11/19 00:45, Nico Williams wrote: Thinking about how SQLite3 would implement WITHOUT ROWID, it seems logical that a set of columns to be used as the primary indexed is required, but that they be unique shouldn't be. ..and... The implied UNIQUE constraint is just an unfortunate

[sqlite] Proper handling of SQLITE_BUSY

Hi, The C code that I use with SQLite is pretty well tested and known to work well so far, but I've ran into some documentation which leads me to suspect there is a problem with my existing code, or a problem with the documentation. >From the documentation:

Re: [sqlite] Proper handling of SQLITE_BUSY

>Since I run all of these statements withing transactions (between >"BEGIN" statements and "COMMIT" / "ROLLBACK" statements"), my >expectation is that SQLITE_BUSY will only ever be returned for >the leading "BEGIN" statement. SQLITE_BUSY may be returned, for example, if the transaction is

Re: [sqlite] SQLite server/file-locking scenario

Thanks again for the responses, very helpful. Taking into account that I can't just read the database file in my program without some sort of locking as was mentioned and explained clearly by multiple people (thank you!), I tried two different implementations to see which would perform

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

On Mon, Nov 18, 2013 at 8:57 PM, RSmith wrote: > Can you honestly use tables without a single unique reference to them? Plain piles of data abound in quantities so voluminous that there's no time to index them (of course, one would not apply SQLite3 to such a dataset, I'm

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

Tomorrow I may describe two real-world cases where logical uniqueness constraints are difficult or extremely difficult to enforce naturally using standard SQL uniqueness constraints. Both are or can be generalizations of the id,attr,val model, one of them using a timeseries approach to permit