You mean like this:
create table parent
(
id integer primary key,
datatext not null collate nocase unique,
child_idinteger not null references child
);
create index parent_child_id on parent (child_id);
create table child
(
id integer primary key,
I have an application for doing recursive data lookup as part of a larger
query. For example, for each node in a tree, are there any children of the
node that have some property?
In non-recursive settings, one would use a conventional correlated subquery
that references the node id from the ou
wt., 10 wrz 2019 o 04:43 Doug napisał(a):
> What works, please? I saw no answer.
> Doug
>
This:
wt., 10 wrz 2019 o 03:12 Simon Slavin napisał(a):
> On 10 Sep 2019, at 2:09am, Rael Bauer wrote:
>
> > CREATE TRIGGER notes_ai AFTER INSERT ON notes
> > BEGIN
> > update notebooks set notebooks.l
On 10 Sep 2019, at 4:02pm, mailing lists wrote:
> Insertion really slows down after about 100 000 items have been inserted. I
> suppose that the slow down is related to indexing because:
>
> [...]
> c) changing the cache size has only a minor impact
SQLite speed does degrade with table size, b
On Tuesday, 10 September, 2019 09:26, mailing lists
wrote:
>I cannot really put all the inserts into one transaction because in case of
>a failure I loose all the already inserted data. Though I made some tests.
>There is hardly any performance gain anymore when doing 1000 or 10 000
>insertions
On 9/10/19, mailing lists wrote:
> What is the background that index creation is so much faster than insertion
> using indices?
Indexes are maintained in key order. So inserts are happening at
random spots all across the index. For each insert, the system has to
(1) read a 4096-byte page, (2)
While a write transaction is open, SQLite needs to keep the changed pages in
memory. When the size of a transaction (measured in changed pages) exceeds the
available memory, SQLite starts to spill the transaction to disk. The optimal
transaction size would be just before this occurs, but there i
Hi,
I cannot really put all the inserts into one transaction because in case of a
failure I loose all the already inserted data. Though I made some tests. There
is hardly any performance gain anymore when doing 1000 or 10 000 insertions in
one transaction including immediate insertion into indi
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 9/10/19, mailing lists wrote:
> So, the best solution I found so far is to disable indexing while insertion
> and to index the table afterwards
I think that is the best solution. Be sure to also do all of your
inserts (and the CREATE INDEX statements) inside of a transaction.
--
D. Richard
Hartwig,
You have got most of the tricks we know about. Other more experienced
developers may provide a better insight.
We had to moved about 60GB of table data about and we ended up doing
what you have done with one extra bit, we batched the jobs up in
multiples of 10,000 between BEGIN and
BTrees as per concept are aware of sorted load vs random load and will adjust
their node splitting algorithm accordingly (e.g. 90/10 split for ordered and
50/50 for random load). The rationale being that an ordered load tends to
indicate that new data is unlikely or added at the end, whereas a r
I have the following situation:
- in one table relatively small data is inserted (100 bytes per record)
- this table contains three indices
- about 100 million or more records have to be inserted
Insertion really slows down after about 100 000 items have been inserted. I
suppose that the slow do
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 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)
--
Igor Tandetnik
___
sqlite-users mailing list
On 2019/09/10 2:28 PM, Dominique Devienne wrote:
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 i
Extension function (you need to add the wrappers and such) to convert a string
in 'd:h:m:s' into total seconds. d/h/m/s can all be arbitrary floating point
numbers. Omit from the left (that is, the rightmost number is seconds, the
next going left is minutes, then hours, then days. Crappy cod
Dominique Devienne, on Tuesday, September 10, 2019 08:21 AM, wrote...
>
> On Tue, Sep 10, 2019 at 2:20 PM Jose Isaias Cabrera, on
> wrote:
>
> > Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
> > > > Make it a WITHOUT ROWID table:
> > > >
> > > > CREATE TABLE example_table
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
> > >)
Marek Wieckowski, on Tuesday, September 10, 2019 08:08 AM, wrote...
>
> Yes, indeed works. Great, thank you!
>
> Marek
>
>
> On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp, on
>
> > On 9/10/19, Marek Wieckowski, on
> > >
> > > Is there a way in sqlite to ensure (on the database side) that all
> > in
Dominique Devienne, on Tuesday, September 10, 2019 07:53 AM, wrote...
>
> As DRH mentioned recently about a different piece of doc, I suspect that
> doc hasn't been updated in years,
Is that a hint? :-)
josé
___
sqlite-users mailing list
sqlite-users@m
Yes, indeed works. Great, thank you!
Marek
On Tue, Sep 10, 2019 at 9:13 AM Richard Hipp wrote:
> On 9/10/19, Marek Wieckowski wrote:
> >
> > Is there a way in sqlite to ensure (on the database side) that all
> inserts
> > for such a table have to have explicit values for such a column?
>
> Ma
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
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. The 'unixepoch' modifier tells the
internal datetime functions that the provided value is relat
You mean like this:
create table test
(
value text not null
);
insert into test values ('00:00:02.68');
insert into test values ('00:00:00.78');
insert into test values ('00:00:02.31');
insert into test values ('00:00:06.36');
insert into test values ('00:00:08.01');
insert into test va
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
On 9/9/19, Scherer, Thorsten wrote:
>
> Are there any known incompatibilites between Qt and libsqlite versions?
>
I have not heard of any incompatibilities. On the other hand, the
SQLite developers do not use Qt and we do not test against Qt, so this
is not something we track. You might have be
On 9/10/19, Marek Wieckowski wrote:
>
> Is there a way in sqlite to ensure (on the database side) that all inserts
> for such a table have to have explicit values for such a column?
Make it a WITHOUT ROWID table:
CREATE TABLE example_table (
id INTEGER PRIMARY KEY,
description
Hello everybody,
I'll try to describe the issue very brief and will add further info, if
needed/requested.
Prerequesties
Qt 4.8.7 (upgrade is not an option)
built with gcc-7.3.1 for an imx25 board
linux kernel 4.17.19
the need to use "attach" and "detach" insi
Hi,
I have an application where a table has an unique not null int field which
identifies the records. I would like to use this column as a primary key.
However, the value has a meaning from the application point of view. There
should never be a record inserted into this table with some default
(
34 matches
Mail list logo
