On Fri, Nov 30, 2018 at 2:10 PM Simon Walter wrote:
> How does one use WHERE x IN (?) with a prepared statement? What is the
> correct way to do this?
>
You cannot do it. Must use WHERE x IN (?, ?, ?), i.e. an explicit and
known in advance
number of bind placeholders. Or not use binding at
On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote:
> On 11/30/18, Simon Walter wrote:
> > Thanks Dominique,
> >
> > Much appreciated. I can now stop pulling out my hair. I will do
> > something with sprintf.
>
> See https://www.sqlite.org/carray.html
Right. Any table-valued function would do
On Mon, Nov 19, 2018 at 4:34 PM Simon Slavin wrote:
> On 19 Nov 2018, at 1:58pm, Charles Hudson wrote:
>
> > I come from an ANSI SQL client / server background (Oracle, MS SQL) but
> am interested in finding a SQL database to install on an old Macintosh G3
> Power PC that is running OS 9.2.
>
On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin wrote:
> On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> albert.banaszkiew...@tomtom.com> wrote:
>
> > ExecuteInTransaction(writeDb1, KCreateTable);
>
> I can't answer your question but the above line shows a misunderstanding
> of SQL.
On Tue, Jan 8, 2019 at 11:04 AM Dominique Devienne
wrote:
>
> On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote:
>>
>> Can someone confirm whether this is a bug ?
>
>
> My guess is that it works as "designed", even if this is surprising...
>
> I believ
I was just surprised by this behavior, see below.
Googling it, seems like SQL Server has a setting the change the behavior in
that case.
Is this standard SQL behavior, as implemented in SQLite?
Not complaining, just asking whether I can depend on it, or not.
Thanks, --DD
On Mon, Dec 31, 2018 at 10:31 PM Keith Medcalf wrote:
> >I don't think the interrupt call will actually terminate a step that
> >is actually being processed, but only mark that no more steps should
> >happen. In other words, I don't think SQLite is spending time
> >checking a flag to stop in the
On Wed, Jan 2, 2019 at 1:47 PM Richard Damon
wrote:
> On 12/30/18 6:10 PM, Richard Green wrote:
> const char *const zName; // Note extra space
>
> Then that is declaring that zName is an immutable pointer to a immutable
> string/character, which is actually likely true, as the code shouldn't
>
On Tue, Jan 8, 2019 at 10:50 AM Eric Grange wrote:
> Thanks.
>
> I think I may have encountered a "real" bug while ordering in a subquery.
> I have simplified it in the following exemples:
>
> select json_group_array(o) from (
>select json_object(
> 'id', sb.id
>) o
>from (
>
On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens wrote:
> AFAIK, your best bet is to put a file db on a ramdisk (tmpfs).
That's not a very portable solution, and a work-around at best.
I don't see anything technical that would prevent WAL to work for
":memory:".
"Shared-memory" "in-process" is
According to [1] WAL mode does not apply to in-memory databases.
But that's an old post, and not quite authoritative when not from the
official SQLite docs.
I'd like to benefit from the MVCC of WAL mode, but for an in-memory
database,
with different threads, each with its own connection,
On Tue, Dec 18, 2018 at 11:13 PM Richard Hipp wrote:
> On 12/18/18, Dominique Devienne wrote:
> > https://blade.tencent.com/magellan/index_en.html
> >
> > Sounds to me it's more related to a "remote callable" program like
> Chrome,
> > than SQLite
On Wed, Dec 19, 2018 at 11:14 AM Richard Hipp wrote:
> > Could there be a way to make shadow tables off-limit to arbitrary SQL?
>
> That is one of the things that the new SQLITE_DBCONFIG_DEFENSIVE
> option does - it makes shadow tables read-only so that they cannot be
> corrupted by SQL.
>
May
On Wed, Mar 27, 2019 at 1:02 PM Simon Slavin wrote:
> On 27 Mar 2019, at 11:48am, Thomas Kurz wrote:
> Locking by rows is a slow operation. You first have to lock the entire
> database, then lock the row, then release the database. And each of those
> locks is a test-and-lock operation.
On Mon, Mar 25, 2019 at 8:35 AM Захар Малиновский <
zakhar.malinovs...@gmail.com> wrote:
> I would like to know is there a way to see if this proposal rejected or
> something? Is this a write ML to propose with patch?
>
Looks like Richard implemented what you need?
On Mon, Mar 25, 2019 at 4:07 PM Richard Hipp wrote:
> Please try the latest trunk check-in and let use know whether or not it
> fixes your issue.
>
For reference: https://www.sqlite.org/src/info/f11c89595dc65f89
___
sqlite-users mailing list
On Tue, Mar 26, 2019 at 3:35 PM Wout Mertens wrote:
> Hi amazing list,
>
> what would be the best way to answer these, given `CREATE TABLE foo(id
> TEXT, json JSON);` and json is always a json object:
>
>- all rows with a given key bar
> - SELECT * FROM foo WHERE json_extract(json,
Up to now, we were doing 2-tables full-outer-join using the classic
emulation, since SQLite lacks support for that join.
But now we are doing it with 3 tables, and it gets ugly fast IMHO.
https://stackoverflow.com/questions/12759087/full-outer-join-in-sqlite-on-4-tables
I "think" the reason
On Wed, Apr 3, 2019 at 2:31 PM Lifepillar wrote:
> [I hope that this kind of announcement is not off-topic here]
>
Not at all, IMHO.
> SQLite3 Decimal is an extension implementing exact decimal arithmetic
> for SQLite3. It is currently unfinished and under development.
I'm curious, what was
On Thu, Mar 28, 2019 at 10:59 AM R Smith wrote:
> Maybe even, if possible, This query updates these tables: x1, x2, x3...
> etc. (some of which might hide behind an FK relation or Trigger) but I
> know this is pushing my luck. :)
>
What I ended-up doing is introspecting the VDBE program of
On Thu, Mar 28, 2019 at 1:16 PM Clemens Ladisch wrote:
> Jeffrey Walton wrote:
> > When working in the Linux terminal we can clear the scrollback with
> > the 'clear' command; and we can delete all history and scrollback with
> > the 'reset' command. I am not able to do the same within the
On Thu 28 Mar 2019 at 08:07, Olivier Mascia wrote:
>
> > Le 27 mars 2019 à 18:04, siscia a écrit :
> >
> > I would like to propose a function (named `sqlite3_stmt_action` for the
> sake
> > of discussion) that allow to understand if a specific statement is
> either a
> > SELECT, UPDATE, DELETE
On Thu, Mar 28, 2019 at 1:35 PM Hick Gunter wrote:
> IMHO the sqlite3_set_authorizer() interface already does a pretty decent
> job of providing the requested information:
>
True, but only if you are fully in control, because authorizer do not
"stack". There's only one, you can't get to restore
On Mon, Apr 1, 2019 at 7:15 AM Shane Dev wrote:
> [...]. By "dynamically changing table", I meant the number of columns and
> rows could could change
> after the dependant view was created. it appears this is impossible using
> only SQL
>
It's possible using a virtual table, which years ago a
On Wed, Feb 27, 2019 at 2:18 PM Jose Isaias Cabrera
wrote:
> Thanks. This is exactly what I needed. So, there is really no JOIN here,
> or is the "from t outer_t, z outer_z" a JOIN like statement? Where can I
> read more about this? And yes, your assessment of t(a, idate) and z(f,
> idate)
On Wed, Feb 27, 2019 at 3:42 PM Jose Isaias Cabrera
wrote:
> Thanks, David. I actually like the comma (,) than the words (JOIN,
> etc). Less wordy and, to me, more logically flow-y.
>
Just the reverse as myself. I much prefer explicit join-on, to separate
filtering from join-conditions in the
Hi,
I've reread [1] about the "server mode" of SQLite,
but noticed that a) it's not been touched in almost a year [2],
and b) it's not mentioned in https://www.sqlite.org/serverless.html
It's also limited to same-process clients and synchronous=off,
making it "not safe" for production use I
On Wed, Feb 27, 2019 at 9:20 PM Keith Medcalf wrote:
> [...] As such, except in OUTER joins, you do not even have to have the ON
> expression related to the table(s) which have been seen so far or even
> those in the join expression ...
because ON is merely a syntactic substitute for WHERE and
On Thu, Feb 28, 2019 at 12:14 AM Richard Hipp wrote:
> On 2/27/19, lnksz wrote:
> > 2) If not, a mention in the docs under disadvantages would be a
> nice information
>
> Docs have now been updated.
Hi. I don't see it in https://www.sqlite.org/src/timeline.
Did I miss it? Or is that doc in
On Thu, Feb 28, 2019 at 10:16 AM Richard Hipp wrote:
> On 2/28/19, Dominique Devienne wrote:
> > On Thu, Feb 28, 2019 at 12:14 AM Richard Hipp wrote:
> >> Docs have now been updated.
> >
> > Hi. I don't see it in https://www.sqlite.org/src/timeline.
> > D
On Wed, Mar 20, 2019 at 5:13 PM Захар Малиновский <
zakhar.malinovs...@gmail.com> wrote:
> Here I include patch file with the changes: fossil diff --from trunk .\src\
> shell.c.in > patch-updateOnlyNewer.patch
> (see patch file patch-updateOnlyNewer.patch)
>
Copy/paste it inline to your message.
On Mon, Feb 18, 2019 at 11:48 AM R Smith wrote:
> Note that in a transaction without WAL mode ... your query may or may not
> "see" data that is older,
>
Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter.
If you are inside a transaction, you are inside it. And will see the
On Mon, Feb 18, 2019 at 9:58 AM Arun - Siara Logics (cc)
wrote:
> [...]. Is it possible to read specific row(s) from a table within same
> db (using row ids) from inside a User defined Deterministic scalar function
> (C API)?
>
Yes it is possible, but then your UDF is unlikely to be
On Mon, Feb 18, 2019 at 2:16 PM Arun - Siara Logics (cc)
wrote:
> Thank you, for the detailed advice, info and the pointer. Is there a
> faster way to query the table using row id, that is, skip the query parsing
> and planner? I still need the page cache feature and allow for concurrent
>
On Mon, Feb 18, 2019 at 12:25 PM Richard Hipp wrote:
> On 2/18/19, Arun - Siara Logics (cc) wrote:
> > If known, kindly point me to an existing open source implementation.
>
> https://www.sqlite.org/src/file/ext/misc/eval.c
Looks like the code was updated to use sqlite3_realloc64() despite
On Mon, Feb 18, 2019 at 1:05 PM R Smith wrote:
> If not, I apologize, and is very interested in where then
> "read_uncommitted" becomes useful?
>
read_uncommitted is about changes in *other* transactions than your own.
You can always see your own changes, in *your* current transaction.
On Mon, Feb 18, 2019 at 4:38 PM Richard Damon
wrote:
> Remember the query plan is determined when that statement is compiled,
> which is BEFORE you do the binding of the parameters, so the plan can not
> depend on the value of parameters. There is no later attempt to optimize
> once the values
On Mon, Feb 11, 2019 at 1:11 PM Clemens Ladisch wrote:
> Peter da Silva wrote:
> > I am pretty sure that the code is not legal C
>
> Indeed; C99 and C11 say in 6.3.2.2:
> | The (nonexistent) value of a void expression (an expression that has
> | type void) shall not be used in any way [...]
>
On Mon, Feb 11, 2019 at 11:16 AM Jonas Bülow wrote:
> > Just tried to update my sqlite version from 3.24 to 3.27.1 and the
> > compiler complained about a void function returning a value. I don't know
> > about C, but in C++ this is undefined behaviour and the clang compiler
> > sometimes
On Mon, Feb 11, 2019 at 11:31 AM Jonas Bülow wrote:
> Sorry, I missed some information. It is the MSVC v15.5 compiler that
> complains:
>
> sqlite3.c(58167): error C2220: warning treated as error - no 'object' file
> generated [c:\work\sqlite-amalgamation-3270100\sqlite3.vcxproj]
>
On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf wrote:
> sqlite> select ... from tab left join tab as tab2 on 0 ...
>
Can someone please educate me on this {{ on 0 }} join "condition" ?
I'm not following what the intended meaning is... Must have one, since OP
"expects 1 row with one column
On Tue, Apr 9, 2019 at 5:08 AM Joshua Thomas Wise <
joshuathomasw...@gmail.com> wrote:
> SQLite3 uses manifest typing, which is great and provides a ton of
> flexibility. However, due to implicit casting rules, many operations can
> accidentally result in a different value than what was desired.
On Mon, Apr 8, 2019 at 7:58 PM Jim Dossey wrote:
> I think I found an error in the documentation here:
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> <
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> >
>
> It defines the
On Tue, Apr 9, 2019 at 9:41 AM Richard Hipp wrote:
> On 4/9/19, Dominique Devienne wrote:
> >>
> >> It defines the table and view:
> >> CREATE TABLE t1(a INT, b TEXT, c REAL);
> >> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
> >&g
On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer wrote:
> I was wondering if there were any plans to support wildcard paths?
>
The main issue here IMHO is that there's no official standard, AFAIK.
> Postgres v12 release looks like it has a pretty sophisticated jsonpath
> type.
SQLite does
On Mon, Apr 15, 2019 at 11:37 AM Lullaby Dayal
wrote:
> [...]. But I fail to run the .selftest command from my sqlite3 prompt. I
> got the error:
> unknown command or invalid arguments error.
>
That code dates back to July 2017. So you must have a very old version.
> I am a newbie in SQLite.
On Fri, Apr 12, 2019 at 4:51 PM x wrote:
> I’m still confused by utf strings. [... I want to scan the string to
> count the number of occurrences of a certain character. [...]
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode
On Wed, Jun 12, 2019 at 6:45 PM Richard Hipp wrote:
> On 6/12/19, James K. Lowden wrote:
> > 1. Prior art. I can't think of a single programming language that
> > displays -0.0 without jumping through hoops.
>
> Prints -0.0 as "-0.0" or just "-0": glibc, Tcl, Python, Javascript
>
> Prints
I trying to reconcile two manually maintained schemas,
one that's custom and drives the database code at runtime,
and another that's the SQLite (DDL) used to instantiate the DBs.
Differences exist, because we humans at not that good at rigour.
These are fairly large and old schemas, > 200 tables,
On Sun, Jun 16, 2019 at 9:02 PM Simon Slavin wrote:
> On 16 Jun 2019, at 7:35pm, Amirouche Boubekki <
> amirouche.boube...@gmail.com> wrote:
>
> > Isn't this a use-case of LSM extension?
>
> It would seem a very good thing to do using LSM, but I can find
> documentation for LSM only in SQLite4,
On Thu, May 23, 2019 at 7:39 AM Keith Medcalf wrote:
> You can check if what you need is available on a connection and either
> load it if needed or just abort:
>
> sqlite> select * from pragma_function_list order by 1, 2;
> name builtin
> -
On Thu, May 23, 2019 at 12:37 PM Simon Slavin wrote:
> On 23 May 2019, at 3:55am, Keith Medcalf wrote:
> > Technically, COLLATE only works on TEXT. Most people declare their own
> types as binary blobs and the programmer has to keep track of what is in
> there and how to work with it.
>
> So
On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
wrote:
> I have been working network for a long time, and I have never seen any
> application that takes "zeroed left-filled" IP addresses. Just sharing...
> Thanks.
>
Works for me with a .001 at least, as shown below. But that wasn't really
On Thu, May 23, 2019 at 5:14 PM Jen Pollock wrote:
> This is getting pretty far off topic, but I think at least some tools
> will interpret values with leading zeroes as octal, which means 001 is
> the same as 1, but 010 isn't 10, it's 8.
>
Good catch! That's indeed what's happening. Win7 BTW.
On Thu, May 23, 2019 at 11:09 AM Keith Medcalf wrote:
> >Keith, as can be seen below, those pragma_*list eponymous vtables are
> >you referring to as not built-in.
>
> Actually they are. They are the eponymous vtables for the corresponding
> pragma's:
>
> pragma function_list;
> pragma
On Fri, May 10, 2019 at 4:40 AM Warren Young wrote:
> Bedrock is based in part on the Paxos algorithm, another major advance in
> distributed computing, and another of Leslie Lamport’s brainchildren:
>
> https://en.wikipedia.org/wiki/Paxos_(computer_science)
In the same vein, there's also
On Tue, May 21, 2019 at 5:36 PM Shawn Wagner
wrote:
> I have a handy script that can handle that sort of input with extra spaces
> (With the --strip option), and other stuff that csv .import doesn't always
> deal well with:
>
> https://github.com/shawnw/useful_sqlite_extensions/tree/master/tools
On Fri, Apr 26, 2019 at 7:36 PM Jens Alfke wrote:
> We are using SQLite blobs to store some structured values, and need
> control over how they are collated in queries, i.e. memcmp is not the
> correct ordering. We’ve registered a custom collating function, but
> unfortunately it doesn’t get
On Mon, Apr 29, 2019 at 9:49 PM Russ Cox wrote:
> On Mon, Apr 29, 2019 at 3:28 PM Richard Hipp wrote:
> For what it's worth, it was not clear to me until just now that the article
> existed to push back on a general "asserts considered harmful" notion. I
> was reading it as primarily
On Mon, Jul 15, 2019 at 6:01 AM J. King wrote:
> On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd <
> donald.sheph...@gmail.com> wrote:
> >sqlite> create table x(a int, b text, c real, d blob, e vartext,
> fgarbage);
> >sqlite> pragma table_info(x);
> >0|a|int|0||0
> >1|b|text|0||0
>
On Mon, Jul 15, 2019 at 7:26 AM Jens Alfke wrote:
> > On Jul 14, 2019, at 10:05 AM, ardi wrote:
> > Do you have any recommendation for saving the inmemory db in a safe way?
>
> If you’re going to keep your data in memory, there’s no good reason to use
> SQLite at all. Just define custom model
On Mon, Jul 15, 2019 at 8:24 AM Barry wrote:
> For performance reasons I've had to write custom code where I have changing
> data that I need to keep 'indices' on. I found it very difficult to get it
> right, and very fragile. If I didn't need the performance, I would much
> rather have used an
On Tue, Aug 13, 2019 at 10:58 AM Ling, Andy
wrote:
> > This is what I would call "forward compatibility": You expect an old
> application
> > to be able to read file formats of a future version. Do you have an
> example
> > where there is really required?
>
> I have an Android app that lets you
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.
On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote:
> I need some help writing some queries for a MTM relationship. The example
> tables are:
>
> author table books table author_books table
> author_id PKbook_isbn PKa_b_id PK
> author_fnamebook_title
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne
wrote:
> select author.*, books.*
> from author_books
> join author on author.author_id = author_books.author_id
> join books on books.book_isbn = author_books.book_isbn
>
Which can also be written:
select author.*,
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
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
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
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 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
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"
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
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
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
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
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
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: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
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
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
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
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
First, is sqlite_master part of the "public API" of SQLite?
I would think so, since many example refer to it.
I was thus looking for a Doc page about it,
but a quick Google search didn't turn up a "direct" result.
For example, the values sqlite_master.type can take, I found only outside
(Not sure it's strictly information schema related, but here goes anyway).
SQLite has grown in the recent months better support for extracting
an information schema, thanks to new pragmas and especially their
eponymous vtable versions, allowing them to be mixed in queries with
sqlite_master.
But
On Mon, Aug 5, 2019 at 10:37 AM Simon Slavin wrote:
> On 5 Aug 2019, at 9:25am, Dominique Devienne wrote:
> <
> https://www.sqlite.org/fileformat2.html#storage_of_the_sql_database_schema
> >
> <https://sqlite.org/faq.html#q7>
> I think the first of those could
Hi Richard, and others,
A neat little program with added in the context of Fossil SCM,
which asserts the version of the SQLite library used, and which
compile time option were used to compile it (statically or not, for that
matter).
See https://fossil-scm.org/fossil/info/350c627a52908458
I
On Wed, Jul 31, 2019 at 8:37 PM Tony Papadimitriou wrote:
> Recently CTRL-C was improved to abort the query and stay in the CLI. This
> is very good.
>
FWIW, SQliteSpy uses the escape key (ESC) to abort a run-away query (Hello
incorrect recursive CTE!)
I'd prefer CTRL-C to keep killing the
On Mon, Aug 5, 2019 at 2:03 PM Simon Slavin wrote:
> On 5 Aug 2019, at 11:54am, Dominique Devienne wrote:
>
> > What other sqlite_* tables are you talking about?
> > sqlite_master is AFAIK the only table with a "fixed-name", that's part
> of the "public API&q
On Mon, Aug 5, 2019 at 11:37 AM Simon Slavin wrote:
> On 5 Aug 2019, at 10:34am, Dominique Devienne wrote:
>
> > Thus my suggestion that an explicit page of its own is
> warranted, properly indexed then.
> Given that there are other sqlite_* tables which are documented, per
On Thu, Aug 1, 2019 at 5:02 PM Olivier Mascia wrote:
> > Le 1 août 2019 à 14:49, Tammisalo Toni a
> écrit :
> > I have an application which is using sqlite database in WAL mode. There
> is a need for
> > periodic backups to a remote site without obstructing the normal
> operation. Both read
> >
On Thu, Aug 1, 2019 at 5:44 PM Jens Alfke wrote:
> > On Jul 31, 2019, at 5:02 AM, Hick Gunter wrote:
> > SQLite stores rows in a compressed format that requires decoding. To
> access the nth field, all the fields that come before it need to be decoded.
>
> My understanding is that it’s just a
On Thu, Jul 18, 2019 at 9:11 PM Keith Medcalf wrote:
> Except in SQLite where as a documented behavioural anomaly maintained for
> backwards compatibility it simply means "UNIQUE" (for ROWID tables). And
> UNIQUE indexes may have NULL components. This is because despite your
> wishing that
On Wed, Jul 24, 2019 at 2:55 AM Justin Olbrantz
wrote:
> [...] my virtual table will be held completely in memory. What should I do
> with the
> estimatedCost value from xBestIndex? According to the documentation this
> should be an approximation of the number of disk accesses for the query,
>
On Wed, Jul 24, 2019 at 10:45 AM Hick Gunter wrote:
> The speed of a virtual table depends on the backing store and software
> used to implement it.
>
[DD] Sure. virtual-tables can also access the disk and do expensive things.
[DD] I did say "example given" for my fast-pure-memory-no-decoding
On Wed, Jul 24, 2019 at 3:09 PM Hick Gunter wrote:
> With the current interface, the xBestIndex function has the possibility of
> returning "effort" and "result set size" separately, instead of just an
> aggregate "effort" (which was at the time documented to assume "result set
> size").
>
On Tue, Jul 16, 2019 at 4:44 AM Adrian Ho wrote:
> On 15/7/19 11:25 PM, Nelson, Erik - 2 wrote:
> > Dominique Devienne wrote on Monday, July 15, 2019 2:41 AM
> >> That's when you reach for virtual tables (and their "virtual indices").
> >> I.e. you kee
601 - 700 of 765 matches
Mail list logo
