Re: [sqlite] Update the SQLite package in Tcl
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
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
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"
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
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
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
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
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
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
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
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？
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
