Re: [sqlite] Update the SQLite package in Tcl

[Keith Medcalf]

On Sunday, 19 November, 2017 20:46, Joseph R. Justice  
wrote:

>On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf 
>wrote:

>> On 19 November, 2017 10:50, Joseph R. Justice 
>asked:


>> The same applies for SQLite and anything else that has a stable
>> interface.

>Fair enough, but then we're back to the problem the original poster
>raised, since that's what they're claiming to be doing, if I 
>understand what they wrote correctly.  And, just dropping in a newer 
>DLL with the same name doesn't seem to be working for them.

Just because the name of a file is the same does not mean that the *contents* 
of the file are the same.  For example, you can rename "notepad.exe" to 
"winword.exe".  Copying your renamed "notepad.exe" (which is now called 
"winword.exe") over top of the distribution version of winword.exe in the 
office directory does not magically turn "notepad" into "word".  It just means 
that now when you try and run "Word" that "notepad" runs instead.

Similarly, copying a DLL containing the "standard" sqlite3.dll code over top of 
the dll containing the extension for TCL does not magically convert the 
"standard" sqlite3.dll into a dll containing the Tcl extension.

Have you looked in the teapot to see if there is a teabag containing a later 
version of the TEA extension (the default is only a few versions behind)?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update the SQLite package in Tcl

[Joseph R. Justice]

On Sat, Nov 18, 2017 at 1:06 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?  I see that
> their installations include a lib folder with a sqlite dll in it.  Is it
> sufficient simply to replace that dll with the sqlite 3.21 dll?  I tried
> that but it gave me error messages ("invalid argument, couldn't load dll",
> etc.), so I am guessing there is more to it than that.
>
> Better yet, is there a version of tcl/tk that is considered "official"
> that is kept updated with the latest versions of all these packages so that
> I don't have to wonder what comes packaged with which version of tcl I
> download from where?
>

Have you considered asking the support communities for the ActiveTcl and
MagicSplat binary distributions of Tcl how to update the version of SQLite
each binary distribution uses?  It seems to me this would be a question
right up their alley.

I wish you well with this task.



Joseph
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update the SQLite package in Tcl

[Joseph R. Justice]

On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf  wrote:

> On 19 November, 2017 10:50, Joseph R. Justice  asked:
>


> >I'd think that dropping a newer version of SQLite, compiled as a run-
> >time linkable library, into a pre-existing binary compiled to use a
> >previous version of SQLite would require the ABI for SQLite (compiled
> >for use >as a linkable library) to be identical between versions, or
> >at least backwards compatible (such that a newer version of the
> >library can be used with an application compiled for a previous
> >version of the library to provide the same functionality as the
> >previous version, tho not newer functionality first provided by
> >the newer version of the library).
>
> >But is this a reasonable thing to expect on any platform, and
> >specifically on the Windows platform?
>
> >I don't know that it is, at least in this case.
>
> On Windows at least, unless deliberate action has been taken to ensure
> incompatibility (such as changing the definition of an exported function)
> or using "ordinal" rather than "name" exports, then the answer is yes.  You
> can simply "replace" the DLL with a newer version.  This is why programs
> that were written for windows in the mid-90's still continue to work to
> this day.  The Win32 API has not changed since it was introduced many many
> many many many years ago, and there is no such thing as "Windows Version
> Obsolescence" except where it has been done deliberately and with
> pre-knowledge and malicious intent by the software author.
>
> The same applies for SQLite and anything else that has a stable interface.
>

Fair enough, but then we're back to the problem the original poster raised,
since that's what they're claiming to be doing, if I understand what they
wrote correctly.  And, just dropping in a newer DLL with the same name
doesn't seem to be working for them.



Joseph
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A walIndexAppend() crash after calling "PRAGMA journal_mode"

[advancenOO]

I tracked down a occasional system crash problem of calling "PRAGMA
journal_mode" and found that walIndexRecover()->walIndexAppend() is finally
called before the crash according to my callstack. 

In sqlite3.c, walIndexAppend() can be called after walIndexRecover() only
when nSize, the size of WAL-file, is bigger than WAL_HDRSIZE. But I do not
think this could happen when I call "PRAGMA journal_mode" as the DB
connection is first open at that time. 

I am not familiar with the operations of upper APPs and this crash makes me
confuse. 

I guess there may be two threads access the same db at the same time and
both of threads find the journal_mode is DELETE and then call PRAGMA. The
crash may happen when the second PRAGMA executes as the first thread may
have written something into the db. 
Is it possible? Or, in what other situation could this crash happen?

Thanks a lot.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Good resources for TCL/TK

[jungle boogie]

Thus said Cecil Westerhof on Sat, 18 Nov 2017 14:43:23 +0100

I found the benefits for TCL/TK. But this is a SQLite mailing list, so not
the right place to ask questions if it is not connected to SQLite also.
What would be good resources for TCL/TK?



There's also a pretty active IRC room on freenode, it's #tcl.

Let us know how your experiences go with tcl.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update the SQLite package in Tcl

[Keith Medcalf]

On 19 November, 2017 10:50, Joseph R. Justice  asked:

>I'd think that dropping a newer version of SQLite, compiled as a run-
>time linkable library, into a pre-existing binary compiled to use a
>previous version of SQLite would require the ABI for SQLite (compiled 
>for use >as a linkable library) to be identical between versions, or 
>at least backwards compatible (such that a newer version of the 
>library can be used with an application compiled for a previous 
>version of the library to provide the same functionality as the 
>previous version, tho not newer functionality first provided by 
>the newer version of the library).

>But is this a reasonable thing to expect on any platform, and
>specifically on the Windows platform?  

>I don't know that it is, at least in this case.

On Windows at least, unless deliberate action has been taken to ensure 
incompatibility (such as changing the definition of an exported function) or 
using "ordinal" rather than "name" exports, then the answer is yes.  You can 
simply "replace" the DLL with a newer version.  This is why programs that were 
written for windows in the mid-90's still continue to work to this day.  The 
Win32 API has not changed since it was introduced many many many many many 
years ago, and there is no such thing as "Windows Version Obsolescence" except 
where it has been done deliberately and with pre-knowledge and malicious intent 
by the software author.

The same applies for SQLite and anything else that has a stable interface.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update the SQLite package in Tcl

[Joseph R. Justice]

On Nov 18, 2017 1:22 PM, "Richard Hipp"  wrote:

On 11/18/17, Balaji Ramanathan  wrote:


> Hi,
>
> I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?

On the SQLite download page, you will find both a Pre-release Snapshot
and a "sqlite-autoconf" tarball.  Download either of these.  (I
suggest the Pre-release Snapshot so that you can help us beta test!)

Untar, and cd into the "tea" subdirectory.  Then type:  "./configure;
make install".  That is suppose to install the latest SQLite for TCL.
"TEA" is the "Tcl Extension Architecture".


Reading the original post, plus his response to your first answer, it
appears to me that he actually wants to update a binary package version of
TCL/TK, compiled for the Windows platform, to a newer version of SQLite
without (ideally) recompiling anything, or at least without recompiling the
binary TCL/TK package.

Your instructions are for recompiling TCL/TK with the newer version of
SQLite on a Unix/Linux type platform, AFAICT.

I'd think that dropping a newer version of SQLite, compiled as a run-time
linkable library, into a pre-existing binary compiled to use a previous
version of SQLite would require the ABI for SQLite (compiled for use as a
linkable library) to be identical between versions, or at least backwards
compatible (such that a newer version of the library can be used with an
application compiled for a previous version of the library to provide the
same functionality as the previous version, tho not newer functionality
first provided by the newer version of the library).

But is this a reasonable thing to expect on any platform, and specifically
on the Windows platform?  I don't know that it is, at least in this case.



Joseph
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update the SQLite package in Tcl

[Balaji Ramanathan]

Are there equivalent instructions for Windows?  Thank you.

Balaji Ramanathan

-- Forwarded message --
From: Richard Hipp 
To: SQLite mailing list 
Cc:
Bcc:
Date: Sat, 18 Nov 2017 13:22:45 -0500
Subject: Re: [sqlite] Update the SQLite package in Tcl
On 11/18/17, Balaji Ramanathan  wrote:
> Hi,
>
> I have installed Tcl/Tk from a couple of places on the web (activetcl
> and magicsplat), and I find that neither of them has the latest version of
> sqlite3 as the standard sqlite3 package.  ActiveTcl seems to be linked to
> sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
>
> What do I need to do to get them both up to sqlite 3.21?

On the SQLite download page, you will find both a Pre-release Snapshot
and a "sqlite-autoconf" tarball.  Download either of these.  (I
suggest the Pre-release Snapshot so that you can help us beta test!)

Untar, and cd into the "tea" subdirectory.  Then type:  "./configure;
make install".  That is suppose to install the latest SQLite for TCL.
"TEA" is the "Tcl Extension Architecture".


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confusion about DISTINCT keyword

[Balaji Ramanathan]

This is great information.  Thank you very much for a clear explanation,
Keith.  I guess I have to go back to using CTE's to get what I want in this
case.

Balaji Ramanathan

-- Forwarded message --
From: Keith Medcalf 
To: SQLite mailing list 
Cc:
Bcc:
Date: Sat, 18 Nov 2017 09:16:57 -0700
Subject: Re: [sqlite] Confusion about DISTINCT keyword

Neither.  It has nothing to do with the DISTINCT keyword, which causes only
DISTINCT rows to be returned (duplicates are removed).

You misunderstanding is on the nature of a SCALAR.  A Scalar means ONE
value.  A correlated SCALAR subquery (a correlated subquery embedded as a
column in a select statement) can only return a SINGLE SCALAR result.

It matters not whether your subquery returns 1 or 1,000,000 rows.  Only the
value from the first row is returned.  Once this first row has been
determined the subquery is terminated.  (That is, it always has " LIMIT 1"
no matter what you might specify).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says
a lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL top 3

[Klaus Maas]

Dear David,

could you help me to understand why your query does what it does?

The magic seems to be in this bit where the field country is 
self-referenced:



where c.country = countries.country

Only: I don't understand why this does what we want it to do.

I tried to build a query of my own mimicking what your query is doing 
with methods I am more familiar with:
(using a cross-join (lines 2-4), filtering out the superfluous results 
(line 14), specifying the output fields explicitly (line 1) to exclude 
field country added by the 2nd cross-join partner)


SELECT a.country, a.city, a.population
FROM countries AS a,
 (SELECT DISTINCT country
  FROM countries) AS d
WHERE population >=
  (SELECT min(population)
   FROM (SELECT population
 FROM countries AS c
 WHERE c.country = d.country
 ORDER BY population DESC
 LIMIT 3
    )
  )
  AND a.country = d.country
ORDER BY a.country, a.population DESC;

To me EXPLAIN QUERY PLAN looks rather similar for both.
My query plan has 3 additional lines because of the additional SELECT 
(inserted between line 1 and 2 compared to your query plan).
The other lines are identical (except for the adjusted subquery count, 
of course).
Admittedly, I have VERY little experience interpreting the output of 
EXPLAIN QUERY PLAN.


Klaus

email signature Klaus Maas

On 2017-11-17 22:21, David Raymond wrote:

I think this works even if there are less than 3 cities listed for a country. 
If there's a tie for 3rd it'll show all of them.


create table countries (country text, city text, population int);

insert into countries values ('UK', 'London', '10'), ('UK', 'Birmingham', 9), 
('UK', 'Manchester', '8'), ('UK', 'Podunk', 1), ('USA', 'New York', 10), 
('USA', 'Los Angeles', 9), ('USA', 'Chicago', 8), ('USA', 'Podunk', 1), 
('Canada', 'Podunk', 1);

select * from countries
where population >=
(select min(population) from
   (select population from countries as c
where c.country = countries.country
order by population desc limit 3
   )
)
order by country, population desc;


country   city  population
    
CanadaPodunk1
UKLondon10
UKBirmingham9
UKManchester8
USA   New York  10
USA   Los Angeles   9
USA   Chicago   8


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bart Smissaert
Sent: Friday, November 17, 2017 3:58 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL top 3

Say I have a table like this:

CREATE TABLE COUNTRIES(COUNTRY TEXT, CITY TEXT, POPULATION INTEGER)

What would be the SQL to get 3 cities for each country with the highest 3
populations for
that country? So, for example for the United Kingdom it would show London,
Birmingham, Manchester in that order, and for the USA New York, Los
Angeles, Chicago. So, I would like
these top 3 cities for all countries.

RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected echo when setting locking_mode

[Graham Holden]

I'm not against rationalising the PRAGMA commands, but this wouldn't help with 
the example you gave. "page_size" is more accurately "the page size I would 
like it to be" and so can be "successfully" set at any time (providing it's a 
power of 2). The only time the current setting gets _acted_ upon is for a new 
database or _during_ a VACUUM command. (Unless I've misinterpreted something).
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Simon Slavin  
Date: 19/11/2017  02:07  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Unexpected echo 
when setting locking_mode 


On 19 Nov 2017, at 2:01am, Kees Nuyt  wrote:

> The same happens for 
>   pragma journal_mode=wal;

Perhaps the PRAGMAs should be reviewed for consistency: that all PRAGMAs which 
change values should output their new value.

This might provide a useful piece of diagnostic information for some users.  
For example suppose someone changes the page_size using this:

PRAGMA page_size = 16384;

They change page_size to 16384 but they don’t know that that works only for an 
empty database, or after a VACUUM.  The PRAGMA outputs 4096 and, although they 
don’t yet know why, they do at least know that their change didn’t work 
properly.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does wal-file support MMAP？

[Simon Slavin]

On 19 Nov 2017, at 6:15am, Howard Kapustein  
wrote:

> On 10 Nov 2017, at 8:49am, advancenOO  wrote:
> 
>> hAve you optimised your column orders ?
> What is optimal?

SQLite reads only up to the last column it needs to execute the command.  So 
you put your most frequently used columns at the beginning of the table 
definition.

Have you created indexes ideally suited to your WHERE and ORDER BY clauses ?

We see a lot of people here asking about obscure complicated features of how 
SQLite works who haven’t done basic optimization things that apply to all SQL 
engines.  That’s what’s behind the questions advancenOO and I asked.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users