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
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
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
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
Stefan Sperling wrote:
> > Does valgrind give any clues?
>
> Valgrind does not run on the OpenBSD/sparc64 platform, unfortunately.
Would the address sanitizer (i.e. gcc -fsanitize=address, or
clang -fsanitize=address) work on OpenBSD/sparc64?
Regar
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
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 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
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 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 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 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
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
phrase queries, then perhaps setting
details=column can help speeding up re-indexing (at least
it reduces the index size). See:
https://www.sqlite.org/fts5.html#the_detail_option
Regards
Dominique
___
sqlite-users mailing list
sqlit
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
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
ed or not does not make any difference when SQLite
is built with SQLITE_OMIT_LOOKASIDE.
Is my assumption correct?
Regards
Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/s
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 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 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 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 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 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 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
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 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
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 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
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
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 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
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 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 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 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 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 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 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 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
es?
According to https://sqlite.org/draft/releaselog/3_31_0.html
SQLite-3.31 is scheduled for 2019-12-31.
I have idea how accurate this date is though.
Regards
Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglis
buntu1~18.04.1) 7.4.0
* or clang-6.0.0-1ubuntu2
Dominique
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
x132EA6: open_db (stdio2.h:97)
==6674==by 0x132EA6: runOneSqlLine (shell.c:18261)
==6674==by 0x13CCE4: process_input (shell.c:18365)
==6674==by 0x11DD65: main (shell.c:19123)
==6674==
0|NULL
sqlite>
Line where overflow is detected in shell.c:
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
Richard Hipp wrote:
> On 12/14/19, Dominique Pellé wrote:
> >
> > I'm curious about what kind of change reduces memory per
> > connection.
> >
>
> The branch is here:
> https://www.sqlite.org/src/timeline?r=mini-lookaside-take-2
>
> I'll
emory for page
caching, hence more SQLite page cache hits, hence less I/Os.
In any case, if user can choose at compile time or runtime,
the default behavior does not matter for me.
I'm curious about what kind of change reduces memory per
connection.
Regards
Dominique
__
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 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 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, 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
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 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
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 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 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 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
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
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
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
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 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 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 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 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 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 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
the same page: https://sqlite.org/draft/gencol.html
section 2.1: The value of a VIRTUAL *columns* (-> column) is computed
section 2.3: The value of a generated *columns* (-> column) is always
Regards
Dominique
___
sqlite-users mailing list
sqlite-users@m
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
old pages to accommodate for more recently used
ones. To persist longer, you need to have more memory and/or
use less memory (kill unused programs...). All unused memory
is used to cache files.
Dominique
___
sqlite-users mailing list
sqlite-users@maili
Keith Medcalf wrote:
>
> On Monday, 7 October, 2019 14:58, Dominique Pellé <
> dominique.pe...@gmail.com> wrote:
>
> >Here the allocated size is fixed (always 64 pointers), so alloca does
> >not seem needed.
>
> >I wonder how many other functions could av
Here the allocated size is fixed (always 64 pointers), so alloca does
not seem needed.
I wonder how many other functions could avoid dynamic allocation
like this one (either with a stack array or alloca).
Regards
Dominique
On Mon, Oct 7, 2019 at 10:26 PM Mateusz Wajchęprzełóż
wrote:
>
>
rterMerge(pTask, p, aSlot[i]) : aSlot[i];
}
pList->pList = p;
- sqlite3_free(aSlot);
assert( pTask->pUnpacked->errCode==SQLITE_OK
|| pTask->pUnpacked->errCode==SQLITE_NOMEM
);
return pTask->pUnpacked->errCode;
}
Regards
Dominique
__
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 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 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 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 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 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 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 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 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 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 4:32 PM R Smith wrote:
> > So "fat" tables, with large rows, and lost of inserts/updates, is
> basically
> > the worse case
> > scenario for such WITHOUT ROWID tables. It works, no issue there, and as
> > typical of
> > SQLite is often fast enough for most DB sizes, but it
On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski wrote:
> Yes, indeed works. Great, thank you!
>
Note though that it has performance implications perhaps.
This changes to physical structure of the table, to be stored as an index
basically.
So if you do lots of insertions "in the middle", you c
On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera
wrote:
> Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > Make it a WITHOUT ROWID table:
> > >
> > > CREATE TABLE example_table (
> > >id INTEGER PRIMARY KEY,
> > >description TEXT NOT NULL
> > >)
On Tue, Sep 10, 2019 at 1:27 PM Keith Medcalf wrote:
> Also, note that you have to use the 'unixepoch' modifier with the time
> function so that it knows the value is seconds,
not days, since floats are by default days and integers are by default
> seconds. [...]
In my quick reading of the doc
On Tue, Sep 10, 2019 at 1:05 PM Keith Medcalf wrote:
> insert into test values ('00:00:07.86');
> select value,
>round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
> from test;
>
Thanks! As I guessed, I was indeed missing something.
But IMHO that something is definitely not
In the same vein as my previous post about lack of function acting as the
"opposite" of strftime(),
I'm wondering if it's not time to have a scanf() function as the opposite
of printf().
In the olden days, SQLite didn't have table-valued eponymous-vtable
"functions", so there
was a technical reao
Hi,
There are functions to format numbers as text times and/or dates,
but I can't find equivalent ones to parse those text times/dates back to
numbers.
I wanted to sum durations expressed as HH:MM:SS.SS strings, and I was
expecting
a function parsing such a string into a number of seconds for exa
u open many DB connections, then:
1) each connection uses memory for the schema
2) each connection uses memory for the prepared statement.
3) each connection uses paged cache
You can use sqlite3_soft_heap_limit64() to
limit the total memory use in SQLite. It's a soft limit
so
On Tue, Sep 3, 2019 at 12:03 PM Rob Richardson
wrote:
> I didn't know it is possible to insert multiple rows into a table using a
> command like this.
Added over 7 years ago: See
https://www.sqlite.org/changes.html#version_3_7_11 #1
> Is this just an SQLite feature, or is this part of the SQL
On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin wrote:
> > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION"
> because others threads needs to access to tables.
> SQLite copes very well when you have one connection writing to the
> database and other connections reading. The probl
On Mon, Sep 2, 2019 at 12:04 PM Grincheux <51...@protonmail.ch> wrote:
> What is the best ?
>
> INSERT INTO artists (name) VALUES
> ("Gene Vincent") ...
> ("Moi _ Me");
>
You're missing commas.
And you should not use double-quotes but single-quotes for string-literals.
> I want to insert 1 000
On Mon, Sep 2, 2019 at 12:12 PM Hick Gunter wrote:
> Dimensions are ignored by SQLite. A field defined CHAR(0) can hold any
> length (up to the internal limit) of string. SQlite will only store the
> actual length of the string plus its contents, no space is wasted.
And Gunter wrote "string" a
On Mon, Sep 2, 2019 at 12:08 PM Hick Gunter wrote:
> Back in 2011 I implemented a virtual table using the "fastbit" library by
> John Wu of the Lawrence Berekely National Laboratory. This allowed selects
> of the form
>
> SELECT ... FROM WHERE rowid IN (SELECT rowid FROM
> WHERE );
>
Did it wo
On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch
wrote:
> Hi, I think that SQLite use some bitmap indexes
Not that I know of, but I don't know the full source code. Maybe FTS[345]
do/es,
but SQLite itself only uses BTree-indexes AFAIK.
> and this here might be of interest if not already used/kn
On Sat, Aug 31, 2019 at 12:24 PM Rob Willett
wrote:
> 5. SQLite seems to be able to do anything we want it to. [...]
> Other people seem worried about the 'lack' of some datatypes, we do
> masses of data and date conversations as needed and it's never been a
> speed issue or any issue.
(since
On Thu, Aug 29, 2019 at 2:35 PM Jose Isaias Cabrera
wrote:
> Free Ekanayaka, on Thursday, August 29, 2019 06:40 AM, wrote...
> > See https://dqlite.io for more details.
>
> Can dsqlite be installed on Windows? I went to the site, read the
> README.md file, and could not find any reference of it.
On Tue, Aug 27, 2019 at 11:46 PM Peng Yu wrote:
> I just need the return status of sqldiff (as `cmp -s`). Is there an
> option to suppress all the screen output? Thanks.
>
https://www.sqlite.org/cgi/src/artifact/7b9b7238284f0213
Doesn't look like it does. But redirecting to /dev/null or NUL wou
https://sqlite.org/sqlar/doc/trunk/README.md
On Tue, Aug 27, 2019 at 3:57 PM Peng Yu wrote:
> Hi,
>
> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. Th
1 - 100 of 860 matches
Mail list logo
