Re: [sqlite] Another 2 questions about SQLite

On Fri, 12 Jul 2013 16:02:37 -0400 Richard Hipp <d...@sqlite.org> wrote: > On Fri, Jul 12, 2013 at 3:01 PM, Igor Tandetnik <i...@tandetnik.org> > wrote: > > > On 7/12/2013 12:30 PM, James K. Lowden wrote: > > > >> The documented behavior is - if you

Re: [sqlite] SQLite Use of Indexes

On Mon, 8 Jul 2013 15:32:21 -0400 "peter korinis" wrote: > . a CLAIMS table = 43M rows with indices on claim_no and > stateCounty code; and > > . a LINE table = 85M rows with indices on claim_no and HCPCS > (a 5 char text code) > > . Have run

Re: [sqlite] Another 2 questions about SQLite

On Mon, 08 Jul 2013 00:37:55 -0400 Igor Tandetnik wrote: > I don't believe it's SQLite's job to ensure the programmer doesn't > shoot herself in the foot. After all, you don't expect, say, the C++ > compiler to prevent you from destroying an object while another part > of the

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Fri, 05 Jul 2013 02:19:08 +0200 Olaf Schmidt wrote: > Create Table T (ID Integer Primary Key, Item Text) > > Select Count(Lesser.ID), T.ID, T.Item From T As T > Left Outer Join T As Lesser > On T.ID > Lesser.ID > Group By T.ID > Order By

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Fri, 05 Jul 2013 12:38:37 +0200 Gabriel Corneanu wrote: > About complexity: I'm not sure it's NlogN; for each N you need to > count N-1 columns, that's N^2 IMO. You're right if the data aren't sorted. If the data are sorted, to *find* the largest value smaller

Re: [sqlite] Another 2 questions about SQLite

On Thu, 04 Jul 2013 16:08:38 -0400 Igor Tandetnik <i...@tandetnik.org> wrote: > On 7/4/2013 3:15 PM, James K. Lowden wrote: > > This weird case is one of (I would say) misusing the connection. > > IMO SQLite should return an error if prepare is issued on a > > conn

Re: [sqlite] Another 2 questions about SQLite

On Thu, 4 Jul 2013 20:36:10 +0100 Simon Slavin <slav...@bigfraud.org> wrote: > On 4 Jul 2013, at 8:15pm, James K. Lowden <jklow...@schemamania.org> > wrote: > > > It doesn't usually matter, right? The fact that the atomic SELECT > > is spread out acro

Re: [sqlite] DLL Size differences and other info requested

On Thu, 4 Jul 2013 17:36:37 +0200 Philip Bennefall wrote: > Do you have any views on compiling SqLite optimized for speed rather > than size? These days, size is speed. The smaller the code, the better it fits in cache, the faster it runs. The days of unrolling loops to

Re: [sqlite] Another 2 questions about SQLite

On Tue, 2 Jul 2013 11:57:43 +0100 Simon Slavin wrote: > The SELECT statement is fine and consistent. But the SELECT > statement is all of _prepare(), _step(), and _finalize(). Igor is > pointing out that that if you stop before _step() has returned > SQLITE_DONE then you

Re: [sqlite] Another 2 questions about SQLite

On Mon, 01 Jul 2013 23:59:15 -0400 Igor Tandetnik wrote: > > 2. Trying to re-use a single connection to issue a second query > > before finalizing the first one should return an error > > No it should not, and does not. Try it. > > > because the library is being improperly

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Wed, 3 Jul 2013 22:49:51 -0500 "Jay A. Kreibich" wrote: > So anyways, I don't actually care about the actual number of orders, > which is mostly likely what my SQL query returns, I just want the > ranking-- who is first, second, and third. I can get that from an >

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Wed, 03 Jul 2013 11:11:29 +0200 Gabriel Corneanu wrote: > I reply from the web and I can't easily quote. Acknowledged, but it does make the thread more difficult to read. :-/ > I don't really want to argue whether it's a workaround or not. I > understand

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Tue, 02 Jul 2013 10:01:23 +0200 Gabriel Corneanu wrote: > > Ranking the rows requires nothing more than joining the table to > > itself. > Indeed, that's the case. However, I can't imagine this to be > efficient. It's just a pure sql workaround to a counter. I

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Mon, 1 Jul 2013 23:30:10 -0500 "Jay A. Kreibich" wrote: > While rearranging the column order may not functionally change the > answer, a database is not given that flexibility in SQL. For > example, "SELECT *" *must* return the columns in the order they are > defined

Re: [sqlite] Another 2 questions about SQLite

On Mon, 01 Jul 2013 23:00:27 -0400 Igor Tandetnik <i...@tandetnik.org> wrote: > On 7/1/2013 10:33 PM, James K. Lowden wrote: > > Igor Tandetnik <i...@tandetnik.org> wrote: > >> If you change data that a live SELECT statement is iterating over, > >> the

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

On Mon, 01 Jul 2013 13:10:59 +0200 Gabriel Corneanu wrote: > I also needed this for some special update queries. > Without many details, it was some kind of "insert into xxx select > , otherfields from source order by ". > For this case there is a workaround,

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

On Mon, 1 Jul 2013 14:22:53 +0300 "Tony Papadimitriou" wrote: > Just because you can select something doesn't mean you have to be > able to sort by it. Yes, it does. > Can you sort by * (select * by table sort by *)? You have confused syntax with semantics. "select *"

Re: [sqlite] Another 2 questions about SQLite

On Sun, 30 Jun 2013 23:27:23 -0400 Igor Tandetnik wrote: > On 6/30/2013 11:13 PM, Igor Korot wrote: > > Well I will use another statement variable as in the sample code. > > My questions was: if I call delete on the record that was just > > retrieved in another query will

Re: [sqlite] Large Database Windows vs Linux

On Fri, 28 Jun 2013 08:54:07 -0400 "Christopher W. Steenwyk" wrote: > I have a rather large database (11 GB) that has two tables (one with > approximately 500,000 rows and another with approximately 50,000,000 > rows). In this database I am performing a query that joins

Re: [sqlite] request for feature: insert or update

On Thu, 27 Jun 2013 12:05:00 +0200 Patrik Nilsson wrote: > Now I'm doing: "insert or ignore into table_test (id) values (1)" and > then issue an update statement. I think "insert or update" will > increase performance. The "insert or update" statement is an

Re: [sqlite] Prepared statement invariants

On Thu, 13 Jun 2013 21:27:33 -0400 Igor Tandetnik wrote: > On 6/13/2013 9:15 PM, Maxim Khitrov wrote: > > This works and also triggers SQLITE_SCHEMA with v1 interface. I did > > a few more tests and it looks like the schema changes are ignored > > if the statement is in the

Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book

On Thu, 13 Jun 2013 08:13:29 -0400 Richard Hipp wrote: > SELECT > name, > (SELECT COUNT(food_id) FROM foods_episodes WHERE food_id=f.id) count > FROM > foods f > ORDER BY count DESC LIMIT 10; ... > SELECT > name, > (SELECT COUNT(*) FROM foods_episodes WHERE

Re: [sqlite] database design question

On Wed, 12 Jun 2013 13:55:25 +0400 dd wrote: > I am trying minimize queries on Authors table. For every deletion of > record from Books, it needs to check in Authors table. One extra > query execution is there. I am trying minimize that. You might not be aware of what you

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

On Fri, 24 May 2013 13:47:08 +0200 Clemens Ladisch wrote: > Foreign key checks are not intended to prevent user errors but to > prevent programming errors. In other words, your program is > responsible for preventing the user from trying to delete some X that > is still

Re: [sqlite] Best way to implement Find commands

On Fri, 24 May 2013 12:30:58 + (UTC) ven...@intouchmi.com wrote: > I was hoping that by making the exact same query to build a temporary > table, SQLite would go through the same procedure thereby loading the > temp table in the same order that stepping would have generated. If > this hope

Re: [sqlite] Best way to implement Find commands

On Thu, 23 May 2013 18:38:57 -0600 "Keith Medcalf" wrote: > > And, can I depend on SQLite to generate results in the same order > > as the original prepare/step sequence and the temp table > > generation, ie. is the temp table's rowid going to be consistent > > with the

Re: [sqlite] [SQLite.net] DateTime exception when reading

On Wed, 22 May 2013 12:23:12 -0700 "Kevin Keigwin" wrote: > What I don't understand is why the database is so forgiving of dates > being saved, while the SQLiteDataAdapter isn't. As Simon said, "SQLite doesn't have a DateTime type." As far as SQLite is concerned, your

Re: [sqlite] SQLite and integer division 1/2=0

On Sun, 12 May 2013 10:59:39 -0400 Stephen Chrzanowski wrote: > Correct me if I'm wrong, but the reason behind doing the int div > first is due to integer division is faster than float division? In the abstract, it's actually impossible to say that one or the other type of

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

On Tue, 7 May 2013 18:54:48 +0200 Hick Gunter wrote: > The problem with checking the ID of the last inserted row is that the > abstraction for checking it cannot reasonably know that a query is an > insert; > > Essentially doing a string match for "INSERT " in the query

Re: [sqlite] Update statement

On Mon, 6 May 2013 23:53:40 +0100 Simon Slavin wrote: > > How do I create this kind of update statement? > > > > UPDATE T SET a=0.5*(a+b), b=0.5*(b-a); > > > > The RHS should always be used with the values of a and b before the > > assignment. > > > > I think that the

Re: [sqlite] How do sqlite3_reset_last_insert_rowid() or functionalequivalent?

On Mon, 6 May 2013 20:55:01 +0300 Mikael wrote: > Currently in the absence of a sqlite3_reset_last_insert_rowid(), the > closest you can do to figure out what ID was actually inserted by the > most recent SQLite query is: > > * Lock mutex > * Get sqlite*'s last insert

Re: [sqlite] Is there a way to select a precision?

On Mon, 6 May 2013 07:42:43 -0500 "Michael Black" wrote: > Yes...other databases do respect NUMBER(10,2) on SELECT's. ... > Can you simply use round to do what you want? > CREATE TABLE stuff(f number(10,2)); > INSERT INTO "stuff" VALUES(1.0/3.0); > sqlite> select f from

Re: [sqlite] Query optimizer bug?

On Wed, 1 May 2013 07:02:38 -0400 Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 1, 2013 at 12:42 AM, James K. Lowden > <jklow...@schemamania.org>wrote: > > > On Tue, 30 Apr 2013 12:59:17 -0400 > > Richard Hipp <d...@sqlite.org> wrote: > > &

Re: [sqlite] Query optimizer bug?

On Tue, 30 Apr 2013 12:59:17 -0400 Richard Hipp wrote: > http://www.sqlite.org/draft/queryplanner-ng.html Feel free to use this version of the diagram. http://www.sqlite.org/draft/queryplanner-ng.html The directory contains the source file and PDF, too. Commands

Re: [sqlite] How do I write this query

On Mon, 29 Apr 2013 16:14:00 -0500 "Michael Black" wrote: > sqlite> CREATE TABLE foo( a integer, b integer); > sqlite> INSERT INTO foo VALUES(1,1); > sqlite> INSERT INTO foo VALUES(1,2); > sqlite> INSERT INTO foo VALUES(1,3); With 3.7.13 at least, insert into foo

Re: [sqlite] Bug in resolving aliases with parentheses in join

On Sun, 28 Apr 2013 12:23:42 +0200 Lucas Clemente wrote: > SELECT * FROM (t1 AS a) JOIN (t2 AS b) USING(k) WHERE a.k = 1; ... > Looks like this is due to some problem with resolving the alias in > the parantheses. Now, as far as I understand SQL this should just > work

Re: [sqlite] LOW performance with VIEW and ANDROID

On Sat, 27 Apr 2013 07:06:31 -0700 (PDT) Gianni Sassanelli wrote: > I have a slow performance if i USE the view but only when i use it > from ANDROID I don't know if it will help, but the following query should produce the same results and might execute faster: SELECT

Re: [sqlite] Left join help.

On Fri, 26 Apr 2013 14:06:23 +0200 Clemens Ladisch wrote: > When using CROSS where standard SQL allows it (for an actual cartesian > product), the table ordering does not really matter for optimization > purposes because the DB has to do two nested full table scans anyway.

Re: [sqlite] Order of columns in group by statement affects query performance

On Fri, 26 Apr 2013 11:12:15 +0200 Hick Gunter wrote: > It is not unreasonable to assume that in a well designed SQL > Statement the GROUP BY clause will be backed up by the necessary > index and an identical ORDER BY clause That is an entirely unreasonable assumption. Order

Re: [sqlite] Feature request: Support for aarch64

On Thu, 25 Apr 2013 10:30:31 -0400 Richard Hipp wrote: > > The configure script is typically generated by a build machine that > > has autotools installed, and included in a distribution tarball. > > The user unpacks the tarball and runs the configure script. He > > doesn't

Re: [sqlite] Order of columns in group by statement affects query performance

On Thu, 25 Apr 2013 10:29:34 +0200 Hick Gunter wrote: > AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from > hints in the virtual table description). That might be so, in some limited sense. It's obviously false in general because they mean different things and

Re: [sqlite] Feature request: Support for aarch64

On Wed, 24 Apr 2013 19:17:32 -0700 Dimiter 'malkia' Stanev wrote: > > Why keep autoconf output in the source code repository? > > Maybe it's simply to guard from generating different ./configure > files on each machine depending on what autotools were installed (I > have very

Re: [sqlite] SQLite Shell with Custom FTS Tokenizer

On Wed, 24 Apr 2013 19:50:14 -0400 Richard Hipp wrote: > On Wed, Apr 24, 2013 at 7:39 PM, Paul Vercellotti > wrote: > > > Does someone have a clever way of getting a custom tokenizer into a > > build of the sqlite shell, without modifying the shell sources?

Re: [sqlite] Order of columns in group by statement affects query performance

On Wed, 24 Apr 2013 17:46:00 +0100 Simon Slavin wrote: > On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote: > > Note though that the query doesn't have an ORDER BY clause. It > > doesn't request rows in any particular order. SQLite could, in > >

Re: [sqlite] Programming API vs console

On Wed, 24 Apr 2013 07:20:59 -0500 "Jay A. Kreibich" wrote: > > query = wxString::Format( "INSERT INTO playersdrafted VALUES( %d, % > > d, ( SELECT ownerid FROM owners WHERE ownername = \"%s\" AND id = % > > d ), %d, %d, \"%s\" );", player.GetPlayerId(), leagueId, > > const_cast(

Re: [sqlite] Feature request: Support for aarch64

On Tue, 23 Apr 2013 09:26:20 -0400 Richard Hipp wrote: > I really dislike changing autoconf versions since any autoconf upgrade > results in a massive change in the generated "configure" script, > which is annoying to audit before each release, and which results in > exceedingly

Re: [sqlite] Writing in a blob

On Tue, 23 Apr 2013 10:28:35 -0400 Richard Hipp wrote: > In summary: No, a bare SQLite blob does not provide file-system > semantics. But you can write a wrapper library around SQLite that > does provide file-system semantics for large blobs, and doing so > would have many

Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)

On Wed, 17 Apr 2013 15:39:50 +0200 "J Trahair" wrote: > I'm looking for a way to ensure other users aren't allowed to write > to the database during this quarter-second period. That might work for you this time, but it doesn't scale well; historically that's why

Re: [sqlite] correlated subquery in LIMIT/OFFSET?

On Thu, 11 Apr 2013 17:14:43 +0200 Clemens Ladisch wrote: > >> It should come (or be derived) from the current row in the outer > >> query. > > > > Sorry, but inner queries are performed first. > > Sorry, but *correlated* inner queries are performed once for each > record in

Re: [sqlite] no mention of even number of digits for BLOB types

On Thu, 28 Mar 2013 17:55:00 +0100 Stephan Beal wrote: > On Thu, Mar 28, 2013 at 11:33 AM, Francesco Montorsi < > francesco.monto...@embit.it> wrote: > > The docs only say: > > BLOB literals are string literals containing hexadecimal data > > and preceded by a single

Re: [sqlite] Performance with journal_mode = off

On Thu, 28 Mar 2013 08:43:50 -0400 Jeff Archer wrote: > > When you turn off journalling, you save something; when you > > consolidate the activity into a single transaction, you save > > something else. What you're seeing is that the

Re: [sqlite] Ubuntu Linking

On Wed, 27 Mar 2013 09:33:39 -0700 (PDT) "brian_f_john...@yahoo.com" wrote: > I ran: cc -O -c sqlite3.c shell.c  That created .o files after 1 > warning about function exprDup having a memset with constant zero > length. > > I compiled my program:  cc -I$HOME/lstp -O

Re: [sqlite] looking up records with terms in a table

On Tue, 26 Mar 2013 17:14:57 +0100 Gert Van Assche wrote: > SELECT [Sentences] FROM [T1] WHERE [Sentences] LIKE '%GM%'; > > What I would like to do is look for all terms that appear in the > Terms table. Something like this (but of course this does not work): > SELECT

Re: [sqlite] ANN: user-defined functions

On Mon, 25 Mar 2013 08:33:53 + Simon Slavin <slav...@bigfraud.org> wrote: > > On 25 Mar 2013, at 3:54am, James K. Lowden <jklow...@schemamania.org> > wrote: > > > http://www.schemamania.org/sql/sqlite/udf/ > > Nicely done. Thanks for the notes,

[sqlite] ANN: user-defined functions

I've been toying with SQLite's virtual table and user-defined function features, and thought I'd publish what I have so far. http://www.schemamania.org/sql/sqlite/udf/ When I started looking into this, I didn't find many examples around the Web, and there's no HOWTO document on the

Re: [sqlite] Efficient way to store counters

On Wed, 13 Mar 2013 14:19:05 -0400 Igor Tandetnik wrote: > > I'm not sure about SQLite, but in principle the query optimizer can > > often use the base table's index for a derived value. Consider > > > > A join B on A.a = 1 + B.a > > or > > A join B on A.a <

Re: [sqlite] Non-English username in WinRT causes SQLiteException

On Wed, 13 Mar 2013 13:45:55 +0200 Ercan Özdemir wrote: > I have some applications in Windows 8 store using SQLite as database. > I discovered that if there are any non-English character in logged on > username, SQLite couldn't open datase file. What encoding is used for

Re: [sqlite] Efficient way to store counters

On Tue, 12 Mar 2013 21:20:11 -0700 David King wrote: > > > At first I was just doing something like this pseducode: > > > update_counter(k1, k2, count=count+1, expires=now+count*1day) > > > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now > > > +1day) > > >

Re: [sqlite] create view doesn't work

On Tue, 12 Mar 2013 19:57:26 + Simon Slavin wrote: > > Is my rewrite the same as the original? > > No. You can't use this construction: > > > INNER JOIN (Categories INNER JOIN Object_Categories ON > > "Categories"."Category_ID"="Object_Categories"."Category_ID") > >

Re: [sqlite] per-context resources for user-defined function

On Mon, 11 Mar 2013 12:47:02 +0100 Clemens Ladisch <clem...@ladisch.de> wrote: > James K. Lowden wrote: > > I'm not sure how to manage the lifetime of ancillary data for a > > user-defined function added by sqlite3_create_function (). > > http://www.sqlite.org/c

[sqlite] per-context resources for user-defined function

Hello all, I'm not sure how to manage the lifetime of ancillary data for a user-defined function added by sqlite3_create_function (). Consider a median() function. The data are captured on each xStep, then sorted and the median produced in xFinal. Suppose sqlite3_realloc() fails during

Re: [sqlite] Bug on real operations

On Fri, 8 Mar 2013 15:06:31 -0300 Israel Lins Albuquerque wrote: > The problem is not comparisons the problem is when I do something > like this: CREATE TABLE tb (a REAL); > INSERT INTO tb (a) VALUES(0); > UPDATE tb SET a = a + 5.45; > UPDATE tb SET a = a + 16.9; > >

Re: [sqlite] Query Help

On Sun, 10 Mar 2013 20:36:47 +0530 "Navaneeth.K.N" wrote: > select distinct(lower(pattern)) as pattern, id from symbols where > value1 = ?1 or value2 = ?1 group by pattern Igor's answer is correct. I just want to point out what looks like a misunderstanding on your

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

ite-users-requ...@sqlite.org> > wrote: > > > Message: 17 > > Date: Fri, 8 Mar 2013 14:26:06 -0500 > > From: "James K. Lowden" <jklow...@schemamania.org> > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. out

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On Thu, 7 Mar 2013 18:45:23 + Simon Slavin wrote: > what do you think the desired behaviour would be for > > CAST('0.9' AS INTEGER) > > I know what I want. Perhaps this can be fixed in SQLite4. Sorry, but CAST is not a math function. There's probably a

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On Thu, 7 Mar 2013 07:36:25 -0600 "Michael Black" wrote: > Personally I think this behavior is horrid. ... > Why is this behavior allowed now? As Zero Mostel sang: "Tradition!" See your handy atoi() man page. :-) I don't know if that's really why, but it's not as if

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On Thu, 7 Mar 2013 19:20:44 +0100 Petite Abeille wrote: > > In conclusion, if you want to allow affine type conversions on > > INSERT, but not disallow values that cannot be so converted, then > > CHECK(my_column = CAST(my_column AS )) works. And if you want > > to

Re: [sqlite] How can I improve this query?

On Wed, 6 Mar 2013 07:20:51 -0800 (PST) Yuzem wrote: > Given the same schema, I can list all genres with one movie by genre: > SELECT genres, movies FROM genres GROUP BY genres; HAVING count(movie) = 1 The HAVING clause does for aggregates what WHERE does for

Re: [sqlite] Break on cumulative sum

On Tue, 5 Mar 2013 02:24:53 -0600 Nico Williams wrote: > Lies! :) There's one more type of UDF in SQLite3: virtual tables. > You could have a virtual table where selecting with an equality test > for some column is "passing an argument" to a "table function". I > know,

Re: [sqlite] Break on cumulative sum

On Mon, 04 Mar 2013 16:51:35 -0500 Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: > On 04/03/2013 2:20 PM, Petite Abeille wrote: > > On Mar 4, 2013, at 1:32 AM, James K. Lowden > > <jklow...@schemamania.org> wrote: > > > >> What do you have in

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 07:19:11 -0600 "Michael Black" wrote: > One thing I'll point out...the trigger solution is almost linear > performance and constant memory where the single SQL statement will > suffer notably as time goes on in both those measures. Also the > single SQL

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 18:50:35 +0100 Petite Abeille wrote: > Ah? if only? SQLite had analytical functions? oh, well.. What do you have in mind? I've benn adding some user defined functions and am thinking of creating a repository for them. --jkl

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 11:46:26 +0100 Gert Van Assche wrote: > My SQL knowledge is not yet good enough to understand what you > proposed, but I hope one day that I will be able to understand and > replicate myself the code you wrote. I started of with a join on the > table

Re: [sqlite] Break on cumulative sum

On Sat, 2 Mar 2013 14:46:40 +0100 Gert Van Assche wrote: > All, I don't know how to achieve this: I need to put the cumulative > sum in a field, and create a group as soon as that cumulative sum is > over a breakpoint value (10). This was a bit of a challenge because the

Re: [sqlite] Is there a way to create a foreign key in existing table?

On Fri, 1 Mar 2013 20:08:55 -0500 Stephen Chrzanowski wrote: > > I can't think of any reason a foreign key constraint would impact > > the cost of joins in any query. The cost is entirely at update time > > (when you have to enforce the constraint). > > > > Wouldn't you

Re: [sqlite] "SQLite" Pronunciation

On Wed, 27 Feb 2013 18:28:03 -0500 f...@cetussoft.com wrote: > OK, how does one pronounce "SQLite"? "see-kwel-lite"? "ess-kyoo-lite"? > "ess-kyoo-ell-lite"? "see-kwel-ite"? "ess-kyoo-ell-ite"? Or...? I was going to answer S-Q-L-lite because it's definitely "S-Q-L", but I admit to saying

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

On Mon, 25 Feb 2013 23:54:23 +0100 anydacdev anydacdev wrote: > I was wondering what is SQLite's equivalent to: > > MERGE INTO x TGT > USING (SELECT NAME, KEY FROM y) SRC > ON (TGT.key = SRC.key) > WHEN MATCHED THEN > UPDATE SET TGT.NAME = NAME > WHEN NOT MATCHED THEN >

Re: [sqlite] Ongoing SELECT sees INSERTed rows.

On Thu, 21 Feb 2013 10:32:51 -0800 (PST) Peter Aronson wrote: > an outer select loop was reading records, and an inner select loop > was inserting records based on the records found into the same > table.  For every row, this design requires 2 trips to the database. Besides

Re: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

On Tue, 19 Feb 2013 05:37:38 -0800 Frank Chang wrote: > Would anyone know how to use Sqlite to calculate the streakedness of > data? The definition of streakedness is show below. Thank you for > your help. > > [EDIT] From our company's chief software architect, here is

Re: [sqlite] query optimization with "order by" in a view

On Tue, 19 Feb 2013 10:19:26 +0100 "Gabriel Corneanu" wrote: > I included the "order by" in view because it's meant for some > end-users and I wanted to avoid mistakes. ... > Am I doing a mistake?? Well, yes, by including ORDER BY in the view definition. Most DBMSs

Re: [sqlite] creating a summary table

On Mon, 18 Feb 2013 17:02:53 + Paul Sanderson wrote: > nc > 1a > 2a > 3a > 4b > 5b > 3b > 4b > 2b > 3a > 5b > 2b > > > I have a table as above > > I want to create a summary table that shows in the first column

Re: [sqlite] update to limits infomation

On Tue, 5 Feb 2013 01:22:37 + YAN HONG YE wrote: > I hava a table like this: > id,name,score,rank > 1,anna,80,0 > 2,qera,65,0 > 6,kero,90,0 > 10,rosa,95,0 > > what I would like to do is to update the rank position. I have this, The rank can be derived, obviating the

Re: [sqlite] Which C++ data structures might one use to cache a sqlite query result?

On Thu, 24 Jan 2013 07:31:04 -0800 Frank Chang wrote: > Could anyone suggest which C++ data structure to use to cache a sqlite > query result? std::deque, where T is a structure you define based on the result. Any container with O(1) insertion is your friend. :-)

Re: [sqlite] SELECT and UPDATE in single query

On Thu, 24 Jan 2013 16:47:02 +1100 Richard Baron Penman wrote: > How to find which keys have been updated from this query? That's the problem with "limit N", right? It's not based on the data. Not only do you not know which rows were updated, you don't know which ones were

Re: [sqlite] SELECT and UPDATE in single query

On Wed, 23 Jan 2013 21:32:20 -0700 "Keith Medcalf" wrote: > > And then I process it like this, N keys at a time: > > > > SELECT key FROM queue WHERE status=0 LIMIT N; > > BEGIN TRANSACTION; > > for key in keys: > > UPDATE queue SET status=1 WHERE key=key; > > END

<    2   3   4   5   6   7