[sqlite] Freelist count after Vacuum

Hi, Is it a reasonable assumption that immediately following a VACUUM command the freelist_count will always be 0, or maybe 1? Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www:

Re: [sqlite] Freelist count after Vacuum

and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: 09 October 2014 17:29 To: General Discussion of SQLite Database Subject: Re: [sqlite] Freelist count after Vacuum On Thu, Oct 9, 2014 at 12:25 PM, Dave

[sqlite] When to disambiguate column names in queries?

Hi Bill, I'm not sure about the 'sqlite world', most of my sql work is done using a different dbms, but my 'standard' is to always use alias names anytime I have two or more tables in a SELECT. Yes, it can make the sql longer and sometimes it is not strictly necessary (as you say, only needed

[sqlite] Combining two databases into one

Hi, I'm looking at a use case for SQLITE within one of our applications. One potential scenario would be for multiple, asynchronous processes to build their own database. Each one would be populating a different table. At some point it would be 'really useful' to combine all the data into a

Re: [sqlite] Combining two databases into one

need it'. For details see http://www.sqlite.org/lang_select.html#compound Ulrich Am 10.11.2013 18:49, schrieb Dave Wellman: > Hi, > > > > I'm looking at a use case for SQLITE within one of our applications. > One potential scenario would be for multiple, asynchronous pro

Re: [sqlite] Combining two databases into one

Simon, Many thanks for the clarification. Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company

Re: [sqlite] Differentiate between CREATE TABLE failures (Exists vs Other Failures?)

Hi, Why not use the "create table if not exists" syntax, that way any error must be classed as 'other'. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham

[sqlite] Sequential numbers

Hi all, I have some rows in a table (not very many, typically less than 20) and I want to generate a unique, sequential number for each row. In another dbms I've used a row_number function (amongst others) to achieve this but I can't see anything with equivalent functionality in sqlite3. My

Re: [sqlite] Sequential numbers

of SQLite Database Subject: Re: [sqlite] Sequential numbers -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/06/14 13:02, Dave Wellman wrote: > I have some rows in a table (not very many, typically less than 20) > and I want to generate a unique, sequential number for each row. http:

Re: [sqlite] Sequential numbers

, 2014, at 10:47 PM, Dave Wellman <dwell...@ward-analytics.com> wrote: > I need the values to be sequential. Well. if your data set is as small as you mentioned (20 records or less). you could roll your own numbering schema with the simple expedient of attaching a trigger to your table

[sqlite] v3.8.4.3 download

Hi folks, Where can I download older versions of sqlite3, specifically v3.8.4.3 (or at least v3.8.4.x). I don't need the source code, just the windows binaries. I'll also need the docs. I looked at the download page but that only seems to have 3.8.5 and I couldn't see a link to older

[sqlite] Divide by 0 not giving error

Hi all, I've found that an sql request that I expected to fail, but it didn't. On the face of it that is good news but there is a potential downside. I wonder if my expectation is wrong or if this is a bug which so far hasn't been caught. The problem deals with dividing by 0. As far as I

Re: [sqlite] Divide by 0 not giving error

To: General Discussion of SQLite Database Subject: Re: [sqlite] Divide by 0 not giving error On 15 Sep 2014, at 7:50pm, Dave Wellman <dwell...@ward-analytics.com> wrote: > Should trying to divide by 0 result in an error? No. There's no mechanism for reporting a mathematical error in SQL.

Re: [sqlite] Create join and add unique column

Hi, If you can insert into another table then you might want to use a trigger on the target table. I've done that with good effect (only on low volumes though, I don't know what would happen on larger volume and if you have larger volumes). Cheers, Dave Ward Analytics Ltd - information in

[sqlite] SELECT 0 gives TEXT instead of INTEGER

How about something like: WITH RECURSIVE expansion(byte) AS ( SELECT 0 UNION ALL SELECT byte + 1 FROM expansion LIMIT 10 ) SELECT PRINTF('%02d',byte) FROM expansion ; Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192

[sqlite] The Session Extension (future SQLite extension)

My apologies if this has already been raised... When I first read about this I thought (assumed) that 'changes' were meant to be just data changes, i.e. changes to rows where they have been inserted, updated and/or deleted. In my experience a fairly typical journaling function that a number of

[sqlite] Exact content of a column in a row

Hi, Is there a way to extract the content of a column/row in its 'stored' format - i.e. the actual bytes? So that it's 'fairly' easy to read! I have a utility that will look at the hex bytes of any file, but the entire database is (understandably) quite complex. Let me explain the problem

[sqlite] FW: Exact content of a column in a row

: Re: [sqlite] Exact content of a column in a row On Tue, May 28, 2013 at 3:26 PM, kyan <alfasud...@gmail.com> wrote: > > On Tue, May 28, 2013 at 3:06 PM, Dave Wellman > <dwell...@ward-analytics.com > > wrote: > >> Is there a way to extract the content of a col

[sqlite] Concatenating literals with column values

Hi, Running the following SQL does not seem to give consistent results. If I concatenate a literal with a column containing a numeric value then I get expected results. (answers B and E below) If however I concatenate the literal with a numeric literal added to the column, I just get

Re: [sqlite] Concatenating literals with column values

sqlite.org] On Behalf Of Richard Hipp Sent: 31 May 2013 16:46 To: General Discussion of SQLite Database Subject: Re: [sqlite] Concatenating literals with column values On Fri, May 31, 2013 at 11:38 AM, Dave Wellman <dwell...@ward-analytics.com>wrote: > Running the following SQL does not seem

Re: [sqlite] Concatenating literals with column values

qlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman Sent: Friday, May 31, 2013 11:08 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Concatenating literals with column values Hi Richard, Many thanks, that works. Why do I need th

Re: [sqlite] Concatenating literals with column values

[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch Sent: 31 May 2013 17:43 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Concatenating literals with column values Dave Wellman wrote: > Why do I need the "()" around my calculation? <http://www.sqlite.org/lang_ex

[sqlite] Updating a table from itself

Hi, Can someone please point me in the direction of the SQLite syntax structure for updating a table by joining to itself. I need to update one row with the contents of another. Based on my normal database (Teradata) I was looking for something like update t1 from (select c1,c2 from t1)

Re: [sqlite] Updating a table from itself

...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 08 June 2013 20:01 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Updating a table from itself On 6/8/2013 2:51 PM, Dave Wellman wrote: > update t1 from (select c1,c2 from t1) as dt1 set c2 = dt1.c2 where > t1.c1 = dt1.c2 - 1; update t1

Re: [sqlite] Updating a table from itself

in England and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: 09 June 2013 10:25 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Updating a table from itself On Sun, 9 Jun 2013 09:55:30 +0100, "Dave We

Re: [sqlite] Feature request: add support for COMMENT statement

I'd just like to add my penny's worth to this discussion. We use a COMMENT to store information about the version of our tables that are in place on the customer system. Assume that we start with 'v1' of our tables that have 3 columns. For a variety of reasons we might add a 4th column in 'v2'.

Re: [sqlite] Feature request: add support for COMMENT statement

Thanks Clemens, that is probably a workable option (at least for me). As someone else noted, the PRAGMA user_version will not work for us as it is one value per db file and we want to set this per table. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44

[sqlite] GROUP BY syntax

Hi, The following sql was in a recent post which was complaining about performance and it looks like a solution has been provided for that. However, looking at the original SQL I would have expected an error message to be generated for it because there is no "GROUP BY" clause. SELECT

Re: [sqlite] GROUP BY syntax

BY syntax On 6/17/2013 8:36 AM, Dave Wellman wrote: > So I think that what this is saying is that when you execute an > aggregate query without a GROUP BY, the chosen non-aggregate values are random (i.e. > arbitrary). This is true with GROUP BY as well - consider: select field1, field2, s

Re: [sqlite] GROUP BY syntax

General Discussion of SQLite Database Subject: Re: [sqlite] GROUP BY syntax On Mon, Jun 17, 2013 at 12:03 PM, Dave Wellman <dwell...@ward-analytics.com>wrote: > Hi, > > Igor and Richard - thanks for your answers. > > Following up on the example below from Igor, what is the use cas

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

Hi, Where the "row number in the answer set" does come in useful (or at least where I've used it a number of itmes) is when populating a table with the results of a select and including this value as another column in that table. "build a table with the top 10 selling items over the last week"

Re: [sqlite] under the christmas tree

"+lots" for OVER and PARTITION BY! Very useful in my line of work as well. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,