On Tue, Sep 10, 2019 at 4:35 PM Igor Tandetnik wrote:
> On 9/10/2019 7:05 AM, Keith Medcalf wrote:
> > select value,
> > round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
> >from test;
>
> Another possibility: strftime('%s', '1970-01-01 ' || value)
>
I ended up needing %
On Tue, Sep 10, 2019 at 5:07 PM Hick Gunter wrote:
> So it really depends on the order of adding records more than the presence
> or absence of a rowid.
>
True. I'm making the conjecture that w/ rowid tables tend to be ordered
(via implicit or explicit integer auto-increment rowids), while w/o r
On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch wrote:
> Peng Yu wrote:
> > Is there a better way to just return an exit status of 0 for
> > a sqlite3 DB file and 1 otherwise?
>
> Extract the magic header string from a known DB file:
>
> dd bs=16 count=1 < some.db > sqlite3-signature
>
> Then y
On Thu, Sep 19, 2019 at 10:20 AM Rowan Worth wrote:
> On Thu, 19 Sep 2019 at 16:03, Dominique Devienne
> > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch
> > > Peng Yu wrote:
> > > > Is there a better way to just return an exit status of 0 for
> > &
On Thu, Sep 19, 2019 at 1:13 PM Gwendal Roué wrote:
> I am looking at the snapshot experimental APIs
>
How long do experimental APIs remain experimental?
Snapshot is over 3.75 years old now. Will it ever graduate to a fully
supported API?
As far as I understood the doc, a snapshot remains vali
On Thu, Sep 19, 2019 at 6:15 PM Hick Gunter wrote:
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Fredrik Larsen
> Gesendet: Donnerstag, 19. September 2019 17:29
> An: SQLite mailing list
> Betreff: Re: [sqlite] [EXT
On Fri, Sep 20, 2019 at 12:33 PM Hick Gunter wrote:
> The dialogue from the stackoverflow discussion shows this quite clearly.
>
Shows what clearly Gunter? I'm not sure to follow. I've read the SO post,
and I don't get your point.
We can observe GROUP BY works ASCending only as of now. Why it c
On Sat, Sep 21, 2019 at 10:17 PM Fredrik Larsen wrote:
> [...] But fixing issues in less than a day of reporting? [...]
>
That's not unusual at all for SQLite. Either it gets "fixed" quickly, or it
doesn't.
The hard part is making the case with Richard (and Dan) about the merit of
the
change, e
On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf wrote:
> On Sunday, 29 September, 2019 01:28, Gwendal Roué
> wrote:
> >Those N reader connections allow concurrent database reads. Those "reads"
> are
> >generally wrapped in a deferred transaction which provides snapshot
> >isolation.
>
> No, it pro
On Mon, Sep 30, 2019 at 2:07 PM Keith Medcalf wrote:
> On Monday, 30 September, 2019 02:06, Dominique Devienne <
> ddevie...@gmail.com> wrote:
> >On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf
> wrote:
> >> On Sunday, 29 September, 2019 01:28, Gwendal Roué <
On Tue, Oct 29, 2019 at 8:38 AM Thomas Kurz wrote:
> We recently had a discussion about date/time support, but also other
> suggestions, which sooner or later end up at the point "cannot be done,
> would break backward compatibility". (See also: "Backward compatibility vs.
> new features (was: Re
On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf wrote:
> On Tuesday, 29 October, 2019 23:05, Simon Slavin
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf wrote:
>
> >> Before you change anything, I think that is incorrect for the various
> >> datetime functions. I think they SHOULD be permi
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote:
> On 10/29/19, Keith Medcalf wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions. I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether tho
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote:
> On 10/29/19, Keith Medcalf wrote:
> >
> > Before you change anything, I think that is incorrect for the various
> > datetime functions. I think they SHOULD be permitted in CHECK
> constraints
> > and in generated always columns, whether tho
On Wed, Oct 30, 2019 at 11:12 AM Richard Hipp wrote:
> [...] But using a VIRTUAL generated column as a PRIMARY KEY would be an
> issue.
>
FWIW, I depend on this feature in Oracle, and it works. Both my PRIMARY and
FOREIGN keys
are VIRTUAL columns, which combine two stored columns. Oracle even al
On Thu, Oct 31, 2019 at 10:57 AM Thomas Kurz wrote:
> I'm using a database with 5 hierarchically strcutured tables using foreign
> keys. The largest table contains about 230'000 entries. My problem is that
> deleting in this database is extremely slow:
>
> pragma foreign_keys=on;
> pragma journal
On Thu, Oct 31, 2019 at 4:10 PM Thomas Kurz wrote:
> Yes, but I'd expect that MariaDB has to do the same, but takes clearly
> less than 1 minute instead of 88 minutes... :confused:
>
Are we comparing apples to oranges here?
SQLite runs on the local machine. While MariaDB is client-server, so th
On Tue, Nov 5, 2019 at 10:01 AM Wout Mertens wrote:
> On Mon, Nov 4, 2019 at 10:26 PM Jens Alfke wrote:
>
> > I don't have a practical use for the branching features, though they're
> cool, but I'm salivating at the thought of a 2x speedup.
> > With all the work that's put into eking out small p
On Fri, Nov 8, 2019 at 9:20 PM Keith Medcalf wrote:
> [...] The optimizer is prone to calculating things more often than it
> needs to, and is difficult to force to "materialize" things.
Since your expensive function needs to be calculated for every row of the
> table anyway, it would be better
On Sat, Nov 9, 2019 at 1:20 PM Mario M. Westphal wrote:
> Thanks to all the friendly people who commented on my question. Much
> appreciated :-)
>
> I was able to solve this with a small trick:
> I created a small 'state' struct with a rowid and the result (float) for
> that row.
>
Sounds like y
As can be seen below, the last query fail, despite the one before it
succeeding.
Yet the second argument is constant, thus it would seem "natural" for it to
work as well.
Could the error be "relaxed", when the non-first argument(s) to aggregate
functions are constant?
Thanks, --DD
C:\Users\ddevie
In searches, https://github.com/mackyle/sqlite comes up first,
but given that Fossil has good/better interop with GIT these days,
why not an official mirror?
Also, mirrors are for the normal repo, while ability to refer to an
amalgamation
in one's project by directly linking to it via a GIT submo
On Fri, Nov 15, 2019 at 4:22 PM Jose Isaias Cabrera
wrote:
> Dominique Devienne, on Friday, November 15, 2019 09:02 AM, wrote...
>
> Have you tried this,
> sqlite> select group_concat(distinct id || ', ') from t;
> 1, ,2, ,4, ,7,
>
> The only problem is tha
On Mon, Nov 18, 2019 at 10:41 AM Shawn Wagner
wrote:
> There is an official github mirror of the fossil repository:
> https://github.com/sqlite/sqlite
Thanks. I thought there was one, but search below didn't find it:
https://www.google.com/search?q=sqlite+github+official+mirror
It's weird the
On Mon, Nov 18, 2019 at 2:41 PM Clemens Ladisch wrote:
> Tobias Leupold wrote:
> Apparently, authors or 'private' file formats do not bother to register
> their IDs.
>
Indeed, there's little point, as those are rarely "public".
I tend to chose a 4 letter prefix related to the kind of app/report
On Tue, Nov 19, 2019 at 2:00 AM Peter da Silva wrote:
>
> > If you stick to lower or upper case letters, could encode up to 6 chars
> in the app_id. --DD
>
> The return of RADIX-50.
>
> https://en.wikipedia.org/wiki/DEC_Radix-50
Thanks! I might go with this going forward. --DD
PS: I tend to pr
On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy wrote:
> > [...] Why is thread_B blocked when doing a read just because SQLite is
> writing to another table? [...]
> > Is this the expected behavior or am I doing something stupid in my code.
> And if so, what to check?
>
> This should only happen if y
Obviously it's ugly to use concatenation and char() to format a string
literal with tabs and newlines.
Is there a better way? Why doesn't printf() support newlines and tabs like
it's C cousin? --DD
PS: Built-in printf() also doesn't support positional params, to "emulate"
newline with printf( '%1$
On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp wrote:
> On 11/29/19, Laurent wrote:
> >
> > Could you perhaps explain what pos means and what the [isTerminal] =
> 0 rows mean.
>
> New check-in enhances the output to include a comment in the SQL
> before the encoding of each production rule. This
On 06.12.2019 10:33, Shawn Wagner wrote:
> Does running an ANALYZE have any impact?
>
On Fri, Dec 6, 2019 at 11:06 AM radovan5 wrote:
> Yes it has and I get correct plan. Did not use analyze before or pragma
> optimize
> but I see I would have to. Thank you for reminding me to this. Just in my
>
On Fri, Dec 6, 2019 at 2:21 PM Richard Hipp wrote:
> On 12/6/19, Keith Medcalf wrote:
> >
> > Perhaps the optimizer could make a "left join" that is not actually an
> outer
> > join into a "cross join" and preserve the nesting order ... ?
>
> It could do that, but that would kind of defeat the w
On Thu, Dec 12, 2019 at 1:47 PM test user
wrote:
> How can I secure user supplied SQL statements in a single process?
>
The one mechanism SQLite has is the authorizer [1].
Whether that's good enough for you, that's for you to determine. --DD
[1] https://www.sqlite.org/c3ref/set_authorizer.html
On Sat, Dec 14, 2019 at 2:27 PM Richard Hipp wrote:
> QUESTION: Should this feature be default-on or default-off?
>
> What's more important to you? 0.25% fewer CPU cycles or about 72KB
> less heap space used per database connection?
>
Backward compatibility. I.e. if I change nothing in my buil
On Mon, Jan 6, 2020 at 8:30 AM Dan Kennedy wrote:
> On 6/1/63 13:44, Yongheng Chen wrote:
> > We found a test case that hangs Sqlite:
>
> Thanks for all the work you've been doing on SQLite!
>
Indeed. But...
The frequency of all these fuzzer related emails has reached a point IMHO
that it's "po
On Mon, Jan 6, 2020 at 2:36 PM Jose Isaias Cabrera
wrote:
> Dominique Devienne, on Monday, January 6, 2020 07:51 AM, wrote...
> [...] it's "polluting" a bit this ML.
>
> This is why we are all so different. I, actually enjoy the "pollution"
> because I
On Mon, Jan 6, 2020 at 3:49 PM Domingo Alvarez Duarte
wrote:
> I understand the original point of view of this thread, but I'm glad
> that we have those submissions here because it makes me aware of
> people/tools/technics/patterns that can help in other projects.
>
I completely agree. Yet at th
On Fri, Jan 10, 2020 at 4:30 PM Richard Hipp wrote:
> length() on a BLOB should show the number of bytes in the BLOB.
>
> length() on a string should show the number of *characters* (not
> bytes) in the string up through but not including the first
> zero-character. It is possible to have additio
On Fri, Jan 10, 2020 at 7:03 PM Richard Hipp wrote:
> On 1/10/20, Dominique Devienne wrote:
> > There's no way at all, to know the length of a text column with embedded
> > NULLs?
>
> You can find the true length of a string in bytes from C-code using
> the sql
On Mon, Jan 13, 2020 at 11:07 AM Keith Medcalf wrote:
> On Monday, 13 January, 2020 02:27, Dominique Devienne
> wrote:
> >> I'd vote for a lengthof(col) that's always O(1) for both text and blob
>
> So what should lengthof(something) return the number of bytes i
On Mon, Jan 13, 2020 at 1:10 PM Keith Medcalf wrote:
> If the register object contains "text" and you cast it to a blob (remove the
> text affinity) you are left with just the bag-o-bytes, and length() will
> return the size of the bag encoded in the register. If the data in the
> register is
On Tue, Jan 14, 2020 at 9:35 AM Wout Mertens wrote:
> On Mon, Jan 13, 2020 at 10:45 PM James K. Lowden
> This is a trade-off between schema simplicity, storage layout and speed of
> some operations. I'd
> argue that in this particular case, a JSON field is beneficial for
> simplicity, speed and
On Tue, Jan 14, 2020 at 2:57 PM Jean-Baptiste Gardette
wrote:
> SELECT * FROM t1 GROUP BY a HAVING b > 1;
>
> Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE
> clause by the optimizer ?
My question would be why you wouldn't write it as a WHERE clause in
the first pl
I like Lua's way to graphically visualize releases at
https://www.lua.org/versions.html
Makes it very easy to get a sense of the frequency. Any chance SQLite
would do that, perhaps with "major" (excluding the leading 3.)
releases on one side, and minor ones on the other?
It's been a quarter since
On Wed, Jan 15, 2020 at 4:54 PM R Smith wrote:
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> >> (2) Assume the data is a JSON array of pairs. The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").
No
On Sun, Jan 19, 2020 at 9:47 AM x wrote:
> Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to
> know the name of the schema that tbl belongs to. What’s the easiest way to
> do this?
Set an authorizer. Requires to write code though, cannot be done in SQL.
https://www.sqli
Hi. Looks like 3.31 (congrats on the release) does not include that
small extension in the amalgamation. Could it please? Uuids are fairly
common in many schemas, so native support "by default" would
standardize support for them in the SQLite ecosystem. Thanks, --DD
PS: And we'd be able to retire
On Mon, Jan 27, 2020 at 11:19 PM Richard Hipp wrote:
> How can I fix this? What alternative word can I use in place of
> "serverless" to mean "without a server"?
Don't. I'm with Warren, Jens, Stephen on this one.
Keep it, but make a new sqlite.org/serverless doc page,
and link to it when you re
BEFORE 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
AFTER 3.30.1 2019-10-10 20:19:45
18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2
Every 18 to 24 months we upgrade SQLite in a large commercial software suite.
Such a recent upgrade s
On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp wrote:
> On 1/30/20, Dominique Devienne wrote:
> > My first question would be to ask whether there's a pragma or
> > compile-time option to get back to the old behavior?
>
> Did you try "PRAGMA legacy_alter_table=ON;&quo
On Thu, Jan 30, 2020 at 3:38 PM Graham Holden wrote:
> Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne
> wrote:
> > The strange thing though, is that I can't repro on a small example.
> > Despite using not_there in the trigger, and doing DML and ALTER TABLE,
On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
> On 2/1/20, Thomas Kurz wrote:
> > Does this mean there will be no possibility to prevent inserting a string
> > into an integer column anymore?
> >
> > create table x (x integer check (typeof(x) == 'integer'));
> > insert into x values ('1');
On Mon, Feb 3, 2020 at 5:35 PM Richard Hipp wrote:
> On 2/3/20, Dominique Devienne wrote:
> > On Sun, Feb 2, 2020 at 12:50 AM Richard Hipp wrote:
>
> This is the SQL:
>
> CREATE TABLE t1(x INT CHECK(typeof(x)=='integer'));
> INSERT INTO t1(x) VALUES(
On Mon, Feb 3, 2020 at 6:42 PM James K. Lowden wrote:
> Do you want to force applications to "pre-convert" values the DBMS can
> convert implicitly?
Yes, that's exactly what I want James.
I want the enforce the bind-value type to be an exact match for the
column value type.
I could before, via C
On Tue, Feb 4, 2020 at 5:38 PM Doug wrote:
> > You're twisting my point here. I obviously want the reverse,
> > I want the database types to "drive" the binding done. 1-to-1.
> > Because even if binding a different type would work, via SQLite's
> > own implicit conversion, I don't want that, becau
On Thu, Feb 6, 2020 at 9:32 AM Hick Gunter wrote:
> >Of course, it may be that the writer of the VTable should know what they are
> >doing and generate a VTable definition that is consistent with how their
> >cursor methods return data, however ... this will omit the OP_Affinity if no
> >>colum
On Thu, Feb 6, 2020 at 11:08 PM Simon Slavin wrote:
> On 6 Feb 2020, at 9:23pm, Rael Bauer wrote:
> > Is a foreign key field automatically indexed, or will it benefit from a
> > separately created index?
> No indexes for either lookup are automatically created.
The shell's .expert command might
On Wed, Feb 12, 2020 at 9:02 PM Eric Grange wrote:
> [...] This is completely safe vs SQL injection, and IME quite efficient. [...]
I disagree that this is efficient enough. I'd much rather have native support in
SQLite for array binding, in the public API, than this. That public
API could wrap
w
Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
are the $n*Code variables supposed to be expanded with a numeric
value, and there's a little issue in the doc generation? Thanks, --DD
4. Primary Result Code List
The $nPrimCode result codes ...
5. Extended Result Code List
The
On Wed, Feb 26, 2020 at 11:09 AM Dominique Devienne wrote:
> Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
> are the $n*Code variables supposed to be expanded with a numeric
> value, and there's a little issue in the doc generation? Thanks, --DD
Thanks for t
On Sat, Feb 29, 2020 at 1:42 PM Shawn Wagner
wrote:
> To import a csv file with headers into an existing table, you can use
> .import '| tail -n +2 yourfile.csv' yourtable
> to skip the header line.
On unix. And by shell’ing out to native tools, so not portable.
The cli ought to have something
On Sun, Mar 1, 2020 at 10:58 PM mailing lists wrote:
> Are there any other solutions / possibilities?
I thought someone more knowledgeable than I about Window Functions [1]
would answer,
but since nobody mentioned them so far, I'll do it, as I believe this
is the "SQL native" way
to achieve what
On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote:
> select group_concat(value) from (select distinct value from test order by
> value);
But is that guaranteed to be ordered correctly "forever" instead of by
"happenstance"
from current implementation details? My point was that the Window
Functi
On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf wrote:
> Well, in theory an order by in a nested select means that the result of the
> operation is an ordered projection and not merely a set of rows.
> For this particular case (a nested select with an order by and the outer
> query with an aggregat
Hi,
I'm interested in LSM1 [1] as an alternative to SQLite, since in a
particular use-case,
I'm using SQLite mostly as a key-value store, and write performance is
particularly important,
in addition to MVCC. Sounds like it could be an excellent fit here,
and the fact it comes from
the SQLite team
On Thu, Mar 5, 2020 at 12:35 PM Dan Kennedy wrote:
> On 5/3/63 16:11, Dominique Devienne wrote:
> > I'm interested in LSM1 [1] as an alternative to SQLite [...]
>
> [...], I don't think it's too bad of an implementation. The
> automated tests are reasonably good
On Wed, Mar 11, 2020 at 12:03 PM Justin Ng wrote:
> -- Query 3
> SELECT
> COALESCE(
> (SELECT 'hello'),
> ABS(-9223372036854775808)
> );
> [...]. It should short-circuit and not evaluate ABS()
Interestingly, found this as well:
https://github.com/AnyhowStep/tsql/issues/233
SQLite COAL
On Fri, Mar 13, 2020 at 1:05 AM Keith Medcalf wrote:
> Uck. That is the most horrible looking thing I have ever seen in my
> life. Good luck with it.
>
I truly hope Keith you'll continue making your tremendous contributions to
the SQLite community.
Things will inevitably move over to the Foru
I hope that's only a temporary situation... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reposting to the ML, maybe I'll have more luck there, than in the forum?
--DD
-- Forwarded message -
From: ddevienne
Date: Tue, Mar 17, 2020 at 5:09 PM
Subject: [sqlite-forum] Convert datetime string to second since Epoch with
millisecond precision
To:
Forum post by ddevienne o
701 - 769 of 769 matches
Mail list logo