[sqlite] Summing values by date, returning 0 for missing dates

[R Smith]

On 2016/05/18 12:26 AM, Jonathan wrote:
> Hi Ryan,
> Thanks for the excellent and comprehensive answer; it seems like CTE's 
> are the way to go for this.
> I did a quick google and this tutorial was very helpful (for anyone 
> else newly interested in CTE's - 
> https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
>  
> ) - they're a lot simpler than I was expecting based on the code 
> snippets I've posted here over time. I think I can even get my head 
> around them, though it may take a while to fully grok your response!

I will add a detailed description of what my response CTE does below 
this mail, but any questions you may have regarding CTE's or any other 
SQL functionality, please feel free to ask - we like answering.

>
> Incidentally, you mentioned that this solution didn't have the "fewest 
> iterations" - can I trouble to ask what that would look like? Then I 
> can try working backwards and seeing what the differences are.

Actually, I wrote that while I had another layout, and then decided to 
put in the more complicated but less iterative version (but did not 
amend my statement), so the one I've sent is the already trimmed down 
version - apologies.

>
> (Note: having my data type of "DATE" - it worked just fine as you 
> suspected it would.)
>
> Many thanks again,
> Jonathan

It's our pleasure.

---
CTE 101:

WITH dtRange(minDT,maxDT) AS (
 SELECT MIN(time_date), '2016-01-15 00:00:00' FROM my_table

-- This first CTE table sets up a single line with 2 columns that is
essentially just values
-- we will use later, a kind of SQL version of initializing variables.
-- The variables being initialized here are minDT which is where the
calendar will start, and
-- maxDT, which is where it will end. I use the MIN() aggregate from
my_table for the prior,
-- and for the latter simply added a date by hand namely 15 January
2016. You could specify
-- any dates you like for both.

), dtAll(dt) AS (
 SELECT minDT FROM dtRange
 UNION ALL
 SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT

-- This second CTE basically creates a list of all the dates we are
interested in (whether
-- they exist in my_table or not). It starts by selecting the minDT
from our previously made
-- dtRange CTE, and then adds 1 day repeatedly in the recursive part
of this recursive CTE
-- until we reach the maxDT (again, from the joined dtRange table).

), my_full(time_date, num) AS (
 SELECT time_date, num FROM my_table
 UNION ALL
 SELECT DT, 0 FROM dtAll

-- This CTE does the magic.  We simply add the dates from the
original my_table along with
-- their "num" values first. We then list all the made-up dates from
the longer date list
-- CTE (dtAll) using the UNION, but with Zero values for "num" (so
that it doesn't register
-- in the SUM() aggregate later).

-- NOTE: we could be more efficient here in data-size terms by only
adding dates that
-- doesn't already exist in my_table, but in practice the time taken
for the lookup dwarfs
-- the time added by the aggregate calcs.

)
SELECT date(time_date) as time_date, sum(num) AS sum_num
   FROM my_full
  GROUP BY time_date
  ORDER BY time_date ASC

-- Finally, in the actual SELECT part, we refer the my_full CTE and
SUM() the num values
-- which shows all the aggregates for all dates - which happen to be
Zero for all our
-- "made-up" dates, but have values for all dates found in the
my_table table.

-- It's just like any other programming language with an elaborate
for-loop construct. :)
;


Hope it helped. If you happen to be on Windows you could find the
SQLitespeed DB manager from
http://www.rifin.co.za/software/sqlc/download/
and install it with ticking the option to add the example scripts.
In there are quite a few CTE
examples and tutorials  (stored to your Documents\SQLiteScripts\
folder I think).

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> > Consider this set of integers: 1,3,5,7,42,99,83,11,83,83
> >
> > In this case, there is no subset S1 of size 3 that satisfies your
> criterion.  In an SQL query, the set returned by LIMIT 3 would not be
defined
> uniquely.
> 
> What you've both said is essentially the point I was trying to make.
> 
> 1.  If you want a deterministic portable result for all valid invocations
of
> LIMIT, you need to either constrain it to use with a totally ordered set
(it
> would be an error to use it on something with duplicates) in order to
> guarantee the number of rows specified in the LIMIT argument, or you need
to
> possibly return a different number of rows than the LIMIT argument.
> 
> 2.  Otherwise, if exactly the number of specified rows must be returned
> without other restrictions, then the result is possibly indeterminate.

I agree, with one tiny tweak. The SQL standard already notes that certain
queries of this kind are "implementation-dependent". Here is an example.

"If the  does not contain an , or contains
an  that
does not specify the order of the rows completely, then the rows of the
table have an order that is defined
only to the extent that the  specifies an order and is
otherwise implementation-dependent."

So in option 2 the result should be considered "implementation-dependent"
and might be deterministic (based on information that is not part of the
query) or not.

> The options with point 1 are not only deterministic but fully relational.

Absolutely.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] foreign_key_check mystery

[R Smith]

On 2016/05/18 5:43 PM, James K. Lowden wrote:
> On Wed, 18 May 2016 08:32:24 +0200
> Clemens Ladisch  wrote:
>
>> You get "foreign key mismatch" if you do not have the required
>> indexes, i.e., according to a comment in the source,
>> 1) The named parent key columns do not exist, or
>> 2) The named parent key columns do exist, but are not subject to a
>> UNIQUE or PRIMARY KEY constraint, or
> Thank you, Clemens.  The FK declaration was
>
>  , foreign key (SegName, DbdName) references
>   Segm(Name, DbdName)
>
> but the Segm PK was only Name.
>
> I'm not convinced the requirement that the referenced columns be unique
> is justified, but at least now I understand the message.  In this case
> it uncovered an error, so I got two for the price of one.  :-)

Hi James, I'm interested in this statement. I'm not sure that uniqueness 
is forced, although it has to be an Indexed column, though I could be 
wrong, and thinking about it more... I am surely wrong - which brings me 
to my question: How do you see a parent-child relationship possible 
where the parent is not Unique?

How could you cascade to children who doesn't know who their father is 
specifically and uniquely? Unless perhaps twin fathers are guaranteed to 
update/delete together and simultaneous (which is never enforceable from 
the FK perspective). If you think this possible, there must be some 
mechanism or wisdom I'm deprived of which I would very much like to remedy.


Thanks,
Ryan

[sqlite] foreign_key_check mystery

[James K. Lowden]

On Wed, 18 May 2016 19:06:30 +0200
R Smith  wrote:

> > I'm not convinced the requirement that the referenced columns be
> > unique is justified
> 
> How do you see a parent-child relationship possible where the parent
> is not Unique?

I think I can convince you that uniqueness is a good rule of thumb, but
that enforcing it ahead of RI is undesirable.  But the price of making
me think about it is reading a long answer.  

The referenced table represents the domain of the foreign key
relationship.  When we say 

foreign key R(A) references S(B)

we're saying every value in A appears in B.  We're not saying anything
about B: not its type, not its cardinality.  The statement describes A
only.  

We're also saying something logical, not enforced by the DBMS: that R
is a part of S.  S can exist without R, but R without S is
meaningless.  But that's a modelling question, and I can't think of
another SQLite feature that enforces any aspect of database design.
Why start here of all places?  Was that even the intention?  

So lets's look at my table T and its FK

   , foreign key (SegName, DbdName) references 
Segm(Name, DbdName)

as it happens, Segm was defined with

, Nametext not NULL primary key
, DbdName text not NULL

One day, Segm may have a 2-column PK: primary key (Name, DbdName).
But today, Name uniquely identifies it.  (Note that therefore {Name,
DbdName} also uniquely identifies it!)  T extends Segm, and requires
that its {SegName, DbdName} pair appear in Segm.  

Let's assert that's *correct*, even though Segm.Name is unique today.
What is *wrong* with saying the FK relationship refers to more columns
than are in the domain table's PK? After all, the above assertions are
still true: 

1.  T{SegName, DbdName} must be in Segm{Name, DbdName}
2.  T extends Segm

Even more -- though not required IMO -- Segm{Name, DbdName}is unique
(because Segm{Name} is unique). 

You could probably get me to agree that the relationship is anomalous.
I suppose if Segm.Name is unique, the FK should refer only to it.

In general, though, not every domain is manifested in a table.  One
might have these PKs: 

S {A, B}
R {B}

Now let me assert that R extends S: that is, for any S there could be
an R.  The rule: If an R exists for S, there is only one,
regardless of A.  

If that's logically incoherent, I don't understand why.  

Remember, there could be a missing domain table, say, T {B}, and the
real rule would be that for some T there must be an R.  But T is
missing because it has no non-key attributes, and S serves in its
stead.  

That's where "not sure justified" comes from.  foreign_key_check
nudges the user in the right direction most of the time, and as a
linter I have no argument with it.  However, as implemented, "foreign
key mismatch" prevents reporting of a genune error, namely "FOREIGN KEY
constraint failed".  By my lights that's putting a design
recommendation before a data error, definitely cart before horse.  

I hope that one day FK enforcement becomes a property of the database,
not of the connection.  If that comes to pass, this issue needs careful
consideration.  As things stand, I think it might be better if "foreign
key mismatch" were demoted to a warning.  

--jkl

[sqlite] Sqlite incompatibility with Postgres

[James K. Lowden]

On Wed, 18 May 2016 20:29:26 +1000
"dandl"  wrote:

> > 2.  Otherwise, if exactly the number of specified rows must be
> > returned without other restrictions, then the result is possibly
> > indeterminate.
> 
> I agree, with one tiny tweak. The SQL standard already notes that
> certain queries of this kind are "implementation-dependent". Here is
> an example.
> 
> "If the  does not contain an , or
> contains an  that [...]

Anything implementation-dependent depends on the implementation.  Ergo,
it is not mathematicaly defined.  It has no theoretical basis.  

Darren Duncan  wrote:

> The options with point 1 are not only deterministic but fully
> relational.

Darren, I agree one could define LIMIT to be deterministic.  But, as
you know, deterministic does not imply relational.  

The point I've made consistently is that the input to LIMIT is not a
value in the database.  As such, it couldn't be a relational operator,
and it's no surprise it appears nowhere in the literature.  Crowning it
"fully relational" is stuff and nonsense.  

I personally don't see any value in making it deterministic.  The best
use of LIMIT is to control command-line output while inspecting the
data.  Pretty much every other use invites error.  Deterministic error
doesn't look like much of an improvement to me.  

--jkl

[sqlite] Sqlite incompatibility with Postgres

[James K. Lowden]

On Wed, 18 May 2016 10:41:21 +1000
"dandl"  wrote:

> > You lost me at "subset S of N tuples".  Which relational operator
> > takes N as an argument?
> 
> Restriction determines whether a tuple should be included or not; you
> also need cardinality and less than (for comparing members).

Restriction is applied to the values of the tuple.  The number of
tuples is not a value of the tuple.  

Neither of us is stupid, David.  I've boiled this down to something
very simple.  If you look at it algebraically, I think you'll come to
the same conclusion I have.  

I wouldn't persist except that you're worth convincing.  Andl holds
promise, and seeks higher ground than SQL holds.  Insofar as possible,
if I can I want to help you get it right.  

> For this query: calculate the average of that set of numbers after
> excluding the 2 largest and 2 smallest values. Again, a pure set
> operation.
> 
> A reasonable solution would be to use two subqueries with ORDER BY
> ASC/DESC and LIMIT 2, followed by an aggregation. 

Sadly, no.  If we're talking about a "pure set operation", and the set
is {1, 1, 2}, the "two smallest" is {1, 2} but LIMIT 2 would 
yield {1, 1}.  

Here again, my putative nth() function *does* give the right answer,
simply because it's a function of the values, and not of the number of
values.  

> > There's no debate about the relational operators.

By which I meant: there's no debate about what they do.  

> You might be surprised to learn that there is considerable academic
> uncertainty as to exactly which operators should be included. 

There's no uncertainty.  Some operators are defined in terms of
others.  No suprise: even under De Morgan you don't need OR if you have
NOT and AND.  The redundancy makes the language more expressive.  

LIMIT doesn't belong in this part of the discussion, btw, because it is
not defined relationally.  

> For example, is CTE RECURSIVE relational, or not? 

http://wiki.epfl.ch/provenance2011/documents/foundations%20of%20databases-abiteboul-1995.pdf

Cf. Chapter 14.  Adding recursion changes the language.  It adds power;
if memory serves permits answering second-order queries.  

> What about LEAD and LAG in the windowing functions? What about string
> concatenation as an aggregation operator?

AFAIK there's no debate about those, either.  They can be expressed in
terms of simpler operations, and exist for convenience, such as it is.  

--jkl

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[J Decker]

On Wed, May 18, 2016 at 2:39 AM, Cecil Westerhof  
wrote:
> I would be interested what you find wrong about Git and is better in your
> version control system.
>

git blows; monotone forever!

> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Jonathan Moules]

I've not heard of fossil so this thread piqued my interest; I currently use 
Mercurial where I have a choice.
I don't seem to be able to find much about Fossil v's Mercurial. This blog post 
looked interesting though:
http://www.omiyagames.com/farewell-fossil-version-control/

Despite Mercurial being less ... opaque than Git, I guess many of the points 
remain the same for that comparison.


 On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at etr-usa.com 
wrote  

On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
 
 On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
 cldwesterhof at gmail.com wrote:
 
 I would be interested what you find wrong about Git and is better in 
your
 version control system.
 
 Check the archives of the fossil-users mailing list

Links to a few of the wider-ranging Git vs Fossil threads in recent years:

 https://goo.gl/rVzYTx
 https://goo.gl/8xKoZy
 https://goo.gl/RPJLEq
 https://goo.gl/Gq3Cga

One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
eventually. It?s nearly inevitable when someone brings up Git on the Fossil 
mailing list. :)
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INTEGRITY_CHECK consumes entire system memory

[Jeremy Nicoll]

On Tue, 17 May 2016, at 14:55, Markus Ecker wrote:

> PRAGMA mmap_size=0;

That, according to the documentation, means "turn off memory-mapping"
PROVIDED the database isn't in use already with memory-mapping active.

So, is the database soley in use by your integrity check when this
happens?

What happens if you code a mmap_size that's non-zero, but well less than
the 
size of your machine's RAM?

-- 
Jeremy Nicoll - my opinions are my own.

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> Or we'll answer my original question by breaking down one of the above two
> options.  The documentation for the implementation may simply say that the
> order will be consistent in any one database connection, without ever
saying
> what the order will be.

This is perfectly consistent with Sqlite behaviour in other comparable
situations, and consistent with what the standard says about ORDER BY:
"implementation defined".

> Numerous users of SQLite have assumed this over the years, since if you
> cannot make this assumption you cannot implement cursors or scrolling
windows
> the way they want to, by changing an OFFSET (or remembering the key values
> for the first and last lines) as the user presses line-up, line-down,
page-up
> or page-down.  It's a natural use of SQLite inside any device with a small
> display and I'm sure programmers would be very annoyed if it was difficult
to
> program.  SQLite does what they want even though there's no documentation
> that says it'll work.

Absolutely! Application programmers depend heavily on SQL to do the heavy
lifting in paging, and all the databases I use support it. Andl supports it
in a way that is entirely consistent with relational theory, but many other
purist relational projects have decided not to. [The application programmer
is of course free to further sort the data locally if desired.]

My only point for the original post was that when you are dealing with an
SQL construct that is not defined by the standard, it's helpful if product
maintainers make some attempt to informally implement a common subset;. In
this case Sqlite is mostly compatible with Postgres, but not for LIMIT -1 vs
LIMIT ALL.

> SQLite does break the consistency rule under one circumstance, though it's
> very unlikely that a programmer would trigger it by accident.  It happens
> when you change the setting for
> 
> PRAGMA reverse_unordered_selects = boolean

If you do that presumably you know to expect what you get!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 11:04 AM, Jonathan Moules  
wrote:
> 
> I currently use Mercurial where I have a choice.
> I don't seem to be able to find much about Fossil v's Mercurial.

Best take it up on the Fossil mailing list.

> This blog post looked interesting though:
> http://www.omiyagames.com/farewell-fossil-version-control/

That blog post was pretty thoroughly rebutted on the Fossil mailing list last 
year:

  https://goo.gl/eX2tBO

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Simon Slavin]

On 17 May 2016, at 6:41pm, Harrington, Paul  
wrote:

> I am a big fan of SQLite and the elegance and simplicity of it and fossil. 
> The documentation and support are excellent.
> 
> Given that the team takes a long-term perspective, I would prefer if the s/w 
> was structured in a more modular fashion so that various components were 
> usable as libraries e.g.
> 1) virtual machine 
> 2) lemon
> 3) fossil

You've been looking at the amalgamation copy, right ?  The top and obvious link 
on the download page.

The amalgamation download of SQLite comprises one .h file and one .c file.  
However these are automatically assembled from the real source code, which is 
split up in much the way you describe.  If you want to see the separate source 
files look for "Alternative Source Code Formats" near the end of this page:



Simon.

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Kees Nuyt]

On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
 wrote:

> I would be interested what you find wrong about Git and is better in your
> version control system.

Check the archives of the fossil-users mailing list
fossil-users at lists.fossil-scm.org
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users

Several mailing list archives carry this list, e.g.:
http://www.mail-archive.com/fossil-users at lists.fossil-scm.org/

-- 
Regards,
Kees Nuyt

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Gerald Bauer]

Hello,
> I would be interested what you find wrong about Git and is better in your
> version control system.

   If you google - one of the first hits that come up is:

  Fossil Versus Git [1]. Cheers.

[1] http://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki

[sqlite] missing dlls

[Jack Acheff]

Greetings,


Can you tell me where I can find the files below?

I don't see them on the downloads page. I understand I need them.




sqlite-shell-win32-x86-3070701.zip
sqlite-dll-win32-x86-3070701.zip



Thanks,

Jack Acheff

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Steve Schow]

ps - I had not heard of veracity before and on the surface it looks quite 
interesting as a direct competitor to fossil, but it also looks a bit 
abandoned.  

On May 18, 2016, at 11:38 AM, Steve Schow  wrote:

> Interesting read, thanks!
> 
> I?m new to fossil, but personally I have fallen in love with it over the past 
> month or so I?ve been using it.  
> 
> My reaction to git after several years of dabbling with it here or there has 
> been 180 degrees opposite?not love
> 
> git is a menace
> 
> 
> 
> On May 18, 2016, at 11:04 AM, Jonathan Moules  lightpear.com> wrote:
> 
>> I've not heard of fossil so this thread piqued my interest; I currently use 
>> Mercurial where I have a choice.
>> I don't seem to be able to find much about Fossil v's Mercurial. This blog 
>> post looked interesting though:
>> http://www.omiyagames.com/farewell-fossil-version-control/
>> 
>> Despite Mercurial being less ... opaque than Git, I guess many of the points 
>> remain the same for that comparison.
>> 
>> 
>>  On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at 
>> etr-usa.com wrote  
>> 
>> On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
>>  
>>  On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>>  cldwesterhof at gmail.com wrote:
>>  
>>  I would be interested what you find wrong about Git and is better 
>> in your
>>  version control system.
>>  
>>  Check the archives of the fossil-users mailing list
>> 
>> Links to a few of the wider-ranging Git vs Fossil threads in recent years:
>> 
>> https://goo.gl/rVzYTx
>> https://goo.gl/8xKoZy
>> https://goo.gl/RPJLEq
>> https://goo.gl/Gq3Cga
>> 
>> One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
>> eventually. It?s nearly inevitable when someone brings up Git on the Fossil 
>> mailing list. :)
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> 
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] foreign_key_check mystery

[James K. Lowden]

On Wed, 18 May 2016 08:32:24 +0200
Clemens Ladisch  wrote:

> You get "foreign key mismatch" if you do not have the required
> indexes, i.e., according to a comment in the source,
> 1) The named parent key columns do not exist, or
> 2) The named parent key columns do exist, but are not subject to a
>UNIQUE or PRIMARY KEY constraint, or

Thank you, Clemens.  The FK declaration was 

   , foreign key (SegName, DbdName) references 
Segm(Name, DbdName)

but the Segm PK was only Name.  

I'm not convinced the requirement that the referenced columns be unique
is justified, but at least now I understand the message.  In this case
it uncovered an error, so I got two for the price of one.  :-)

--jkl

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Cecil Westerhof]

?I would be interested what you find wrong about Git and is better in your
version control system.?

-- 
Cecil Westerhof

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Steve Schow]

Interesting read, thanks!

I?m new to fossil, but personally I have fallen in love with it over the past 
month or so I?ve been using it.  

My reaction to git after several years of dabbling with it here or there has 
been 180 degrees opposite?not love

git is a menace



On May 18, 2016, at 11:04 AM, Jonathan Moules  
wrote:

> I've not heard of fossil so this thread piqued my interest; I currently use 
> Mercurial where I have a choice.
> I don't seem to be able to find much about Fossil v's Mercurial. This blog 
> post looked interesting though:
> http://www.omiyagames.com/farewell-fossil-version-control/
> 
> Despite Mercurial being less ... opaque than Git, I guess many of the points 
> remain the same for that comparison.
> 
> 
>  On Wed, 18 May 2016 16:55:15 +0100 Warren Youngwyml at 
> etr-usa.com wrote  
> 
> On May 18, 2016, at 4:43 AM, Kees Nuyt k.nuyt at zonnet.nl wrote:
>  
>  On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>  cldwesterhof at gmail.com wrote:
>  
>  I would be interested what you find wrong about Git and is better in 
> your
>  version control system.
>  
>  Check the archives of the fossil-users mailing list
> 
> Links to a few of the wider-ranging Git vs Fossil threads in recent years:
> 
> https://goo.gl/rVzYTx
> https://goo.gl/8xKoZy
> https://goo.gl/RPJLEq
> https://goo.gl/Gq3Cga
> 
> One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
> eventually. It?s nearly inevitable when someone brings up Git on the Fossil 
> mailing list. :)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite incompatibility with Postgres

[Stefan Evert]

> On 18 May 2016, at 02:41, dandl  wrote:
> 
> Then you are mistaken. 
> 1. Consider the following set S of integers: 1,3,5,7,42,99,83,11.
> 2. Divide it into two subsets such that S1 is of size 3 and all members of
> S1 are larger than those in S2.
> 
> A sort is unnecessary -- there are many algorithms that can do that purely
> based on set logic, the ability to compare members and the ability to
> determine the cardinality of a set.

I think the point is that your query may not have a solution if there are ties, 
i.e. the ordering is not complete:

Consider this set of integers: 1,3,5,7,42,99,83,11,83,83

In this case, there is no subset S1 of size 3 that satisfies your criterion.  
In an SQL query, the set returned by LIMIT 3 would not be defined uniquely.

Best,
Stefan Evert

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> The "problem" is to produce 3 rows where, relationally, the only answers
have
> 2 or 4 rows.  There is no right answer to the problem because there is no
> answer to the problem.

Which is what I said. The solution with 3 rows is unambiguous. You either
resolve this the way the standard does by making it "implementation defined"
or by forcing the query to be unambiguous by adding all the ORDER BY columns
to the SELECT list.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] INTEGRITY_CHECK consumes entire system memory

[Richard Hipp]

On 5/17/16, Markus Ecker  wrote:
> Hello together!
>
> I am struggling with a memory issue of SQLite and therefore searching for
> help. The database of our product has about 70GB of data. When I call the
> "PRAGMA integrity_check" method, the memory consumption of my system
> continuously increases until all of my 16GB of RAM are used.
> The interesting thing is that the memory is not consumed by the processes
> working on the SQLite itself.  Rather it seems that the database file is
> mapped into memory as you can see from the output of RamMap.

If the memory consumption is outside of SQLite, that seem like it is a
problem outside of SQLite, no?

I ran a "PRAGMA quick_check" on your database on Linux and it ran
fine, using very little memory.

When you run "PRAGMA integrity_check", SQLite does have to read every
page of the database file - all 51,929,418 of them.  Perhaps the disk
cache system on your test platform causes that to use a lot of memory.
You might try changing the page size from 1024 to 4096.  I don't think
that will fix your current problem, but it might make things go faster
in general.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Sqlite incompatibility with Postgres

[dandl]

> I am quite certain nevertheless that LIMIT has no relational basis.
> Nothing based on Order By could.  

Then you are mistaken. 
1. Consider the following set S of integers: 1,3,5,7,42,99,83,11.
2. Divide it into two subsets such that S1 is of size 3 and all members of
S1 are larger than those in S2.

A sort is unnecessary -- there are many algorithms that can do that purely
based on set logic, the ability to compare members and the ability to
determine the cardinality of a set.

Another query: calculate the average of that set of numbers after excluding
the 2 largest and 2 smallest values. Again, a pure set operation.

> You lost me at "subset S of N tuples".  Which relational operator takes N
as
> an argument?

Restriction determines whether a tuple should be included or not; you also
need cardinality and less than (for comparing members).

> > Not so. In standard SQL ORDER BY establishes a comparison function
> > between tuples and is part of the DECLARE CURSOR syntax, but the
> > cursor exists regardless.
> 
> Regarding Order By and cursors, I'm referencing CJ Date.  The reason Order
By
> cannot appear in a subquery is that its output is not a table, but a
cursor.
> Whether Order By "establishes a comparison function between tuples" is
> irrelevant; so too does Where.  It's the product that's different.

He's correct as far as that goes. But curiously, ORDER BY LIMIT N could
appear in a subquery because it merely selects a subset -- the actual
ordering is irrelevant.

For this query: calculate the average of that set of numbers after excluding
the 2 largest and 2 smallest values. Again, a pure set operation.

A reasonable solution would be to use two subqueries with ORDER BY ASC/DESC
and LIMIT 2, followed by an aggregation. I don't know if any dialect of SQL
would allow that, but it's relationally valid. BTW Andl does allow it.

> If you accept that Order By can appear only in the outermost query, it's
> clear that it has no relational role at all.  It affects only the order in
> which the rows are returned to the caller.  Other than syntax, how is that
> different from a cursor?

As defined in the SQL standard ORDER BY can only appear that's true, but the
standard does not include LIMIT.

> So it's just a tiny communication optimization?  After all, compared to a
> network round trip, sorting the result (in order to apply LIMIT
> rationally) is usually far more expensive.  I bet no study has ever shown
> LIMIT to improve performance measurably, not that that would justify its
> existence.

[This is a sidetrack but no, in most cases network round-trip is important
enough to warrant a solution, although LIMIT is not the only solution.]

> Then I think you mean you agree!  Because LIMIT is nonrelational, it's
> *undefined*.  We have a long thread here that might be titled "what should
> LIMIT do?"  There's no debate about the relational operators.

You might be surprised to learn that there is considerable academic
uncertainty as to exactly which operators should be included. For example,
is CTE RECURSIVE relational, or not? What about LEAD and LAG in the
windowing functions? What about string concatenation as an aggregation
operator?

There's lots more down this particular wormhole, if you want to pursue it.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

[sqlite] missing dlls

[Richard Hipp]

On 5/18/16, Jack Acheff  wrote:
> Greetings,
>
>
> Can you tell me where I can find the files below?
>
> I don't see them on the downloads page. I understand I need them.

You are probably better off using the latest versions from the
download page, rather than the 5-year-old versions listed below.

>
> sqlite-shell-win32-x86-3070701.zip
> sqlite-dll-win32-x86-3070701.zip
>

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 4:43 AM, Kees Nuyt  wrote:
> 
> On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
>  wrote:
> 
>> I would be interested what you find wrong about Git and is better in your
>> version control system.
> 
> Check the archives of the fossil-users mailing list

Links to a few of the wider-ranging Git vs Fossil threads in recent years:

  https://goo.gl/rVzYTx
  https://goo.gl/8xKoZy
  https://goo.gl/RPJLEq
  https://goo.gl/Gq3Cga

One of those threads didn?t start out as ?Fossil vs Git,? but ended up there 
eventually.  It?s nearly inevitable when someone brings up Git on the Fossil 
mailing list. :)

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Warren Young]

On May 18, 2016, at 4:23 AM, Gerald Bauer  wrote:
> 
>> I would be interested what you find wrong about Git and is better in your
>> version control system.
> 
> [1] http://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki

Also http://fossil-scm.org/xfer/doc/tip/www/quotes.wiki

[sqlite] foreign_key_check mystery

[Clemens Ladisch]

James K. Lowden wrote:
> I seem to be getting a foreign key check anomaly.  I've checked the
> constraint mentioned in the error message (and the other one, just in
> case).  Am I overlooking something, or has this been fixed since 3.8.4.1?
>
> sqlite> pragma foreign_key_check;
> Error: foreign key mismatch - "Field" referencing "Segm"

There are two error messages related to foreign key constraints.

You get "FOREIGN KEY constraint failed" if there is invalid data,

You get "foreign key mismatch" if you do not have the required indexes,
i.e., according to a comment in the source,
1) The named parent key columns do not exist, or
2) The named parent key columns do exist, but are not subject to a
   UNIQUE or PRIMARY KEY constraint, or
3) No parent key columns were provided explicitly as part of the
   foreign key definition, and the parent table does not have a
   PRIMARY KEY, or
4) No parent key columns were provided explicitly as part of the
   foreign key definition, and the PRIMARY KEY of the parent table
   consists of a different number of columns to the child key in
   the child table.

Also see .


Regards,
Clemens

[sqlite] Sqlite incompatibility with Postgres

[Darren Duncan]

On 2016-05-18 2:19 AM, Stefan Evert wrote:
>> On 18 May 2016, at 02:41, dandl  wrote:
>>
>> Then you are mistaken.
>> 1. Consider the following set S of integers: 1,3,5,7,42,99,83,11.
>> 2. Divide it into two subsets such that S1 is of size 3 and all members of
>> S1 are larger than those in S2.
>>
>> A sort is unnecessary -- there are many algorithms that can do that purely
>> based on set logic, the ability to compare members and the ability to
>> determine the cardinality of a set.
>
> I think the point is that your query may not have a solution if there are 
> ties, i.e. the ordering is not complete:
>
> Consider this set of integers: 1,3,5,7,42,99,83,11,83,83
>
> In this case, there is no subset S1 of size 3 that satisfies your criterion.  
> In an SQL query, the set returned by LIMIT 3 would not be defined uniquely.

What you've both said is essentially the point I was trying to make.

1.  If you want a deterministic portable result for all valid invocations of 
LIMIT, you need to either constrain it to use with a totally ordered set (it 
would be an error to use it on something with duplicates) in order to guarantee 
the number of rows specified in the LIMIT argument, or you need to possibly 
return a different number of rows than the LIMIT argument.

2.  Otherwise, if exactly the number of specified rows must be returned without 
other restrictions, then the result is possibly indeterminate.

The options with point 1 are not only deterministic but fully relational.

-- Darren Duncan

[sqlite] Sqlite incompatibility with Postgres

[Simon Slavin]

On 18 May 2016, at 2:05am, dandl  wrote:

>> The "problem" is to produce 3 rows where, relationally, the only answers
> have
>> 2 or 4 rows.  There is no right answer to the problem because there is no
>> answer to the problem.
> 
> Which is what I said. The solution with 3 rows is unambiguous. You either
> resolve this the way the standard does by making it "implementation defined"
> or by forcing the query to be unambiguous by adding all the ORDER BY columns
> to the SELECT list.

Or we'll answer my original question by breaking down one of the above two 
options.  The documentation for the implementation may simply say that the 
order will be consistent in any one database connection, without ever saying 
what the order will be.

Numerous users of SQLite have assumed this over the years, since if you cannot 
make this assumption you cannot implement cursors or scrolling windows the way 
they want to, by changing an OFFSET (or remembering the key values for the 
first and last lines) as the user presses line-up, line-down, page-up or 
page-down.  It's a natural use of SQLite inside any device with a small display 
and I'm sure programmers would be very annoyed if it was difficult to program.  
SQLite does what they want even though there's no documentation that says it'll 
work.

SQLite does break the consistency rule under one circumstance, though it's very 
unlikely that a programmer would trigger it by accident.  It happens when you 
change the setting for

PRAGMA reverse_unordered_selects = boolean

Simon.

[sqlite] Summing values by date, returning 0 for missing dates

[Jonathan]

Hi Ryan,
Thanks for the excellent and comprehensive answer; it seems like CTE's 
are the way to go for this.
I did a quick google and this tutorial was very helpful (for anyone else 
newly interested in CTE's - 
https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
 
) - they're a lot simpler than I was expecting based on the code 
snippets I've posted here over time. I think I can even get my head 
around them, though it may take a while to fully grok your response!

Incidentally, you mentioned that this solution didn't have the "fewest 
iterations" - can I trouble to ask what that would look like? Then I can 
try working backwards and seeing what the differences are.

(Note: having my data type of "DATE" - it worked just fine as you 
suspected it would.)

Many thanks again,
Jonathan

On 13/05/2016 23:25, R Smith wrote:
>
>
> On 2016/05/13 4:56 PM, Jonathan Moules wrote:
>> Hi Simon,
>>Sorry, maybe we're crossing wires, but I'm not sure to what you're 
>> referring. How is defining the type as DATE impeding my attempt to 
>> get a value of 0 for non-existent rows?
>> Ryan's response with a CTE seems to probably be what I want (not had 
>> the opportunity to test it yet - CTE's are entirely new to me), but 
>> looking at it quickly now I see that the type is actually NUMERIC 
>> there rather than my DATE - perhaps that's to what you're referring.
>
> The CTE will work great and there is some literature about CTEs in 
> general we could point you to.  If you just want a quick fix for your 
> situation, just use my example, if you want to understand CTE in 
> general, feel free to ask or google, it's really worth learning since 
> it can do some real magic for you.
>
> As to the date type in my CTE example - apologies, I hadn't even 
> noticed you had it as DATE, I just used NUMERIC since that's what I 
> always do for dates. You can still use it as type DATE and achieve the 
> same results with the CTE (I'm 99% sure - haven't tested it).
>
> Your confusion about what Simon said might be that (I think) perhaps 
> Simon misunderstood what you found weird about the results in the 
> original post and tried to explain why you see that weirdness while 
> you were on about a different weirdness - so you are simply not on the 
> same page.
>
> Either way, good luck with the implementation. One note: The CTE 
> solution will only work after SQLite version 8.3 I think, so if you 
> are using a very old version, it might not work.
>
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users