The most useles answer would be: Yes; run a recursive CTE query and if it does
not terminate, then there are loops ;)
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Shane Dev
Gesendet: Mittwoch, 20. Dezember 2017 22:32
An:
Yannick Duchêne wrote:
>I wonder is there is a way with SQLite3, to reuse a often used and
>moderately long common table expression without the need to copy/paste
>it in every query text it is used in.
If you do not need dynamic parameters, use a view.
Otherwise, you have to write out the subquery
Hi David,
Yes, parent and child are integers. I hadn't thought of building a string
path, very clever. After a little testing, I have not found a case where
the queries fail.
On 20 December 2017 at 23:18, David Raymond
wrote:
> Well, if your parent and child are going to be integers, then you c
On 12/20/17, R Smith wrote:
> I've never known Analyze to consume significant time
ANALYZE runs in O(N) steps, where N is the number of rows in the
table. It does a single scan through each index being analyzed, from
beginning to end.
CREATE INDEX, on the other hand, requires O(NlogN) steps.
A
The tarball you pointed me to appears to build OK on Linux PPC. Thanks.
From: drhsql...@gmail.com on behalf of Richard Hipp
Sent: Wednesday, December 20, 2017 3:49:45 PM
To: SQLite mailing list
Cc: Lee, Greg
Subject: Re: [sqlite] Macro expansion of B0 on Linux P
On 5/3/17, Lee, Greg wrote:
> When I try to build sqlite 3.18.0 on a Linux PPC system, I get a
> compile-time error.
Can you please try again using the latest trunk version of SQLite and
let me know if you are still encountering problems. You can download
a tarball of the latest trunk checkin fr
Indeed, a preprocessor may be an option, since all the queries are stored in
text files (which are loaded by an application or directly used with
copy/paste).
If that's better or not than a function added per‑connection, it’s a matter
of taste. I will have to decide.
--
Sent from: http://sqlite
Yannick Duchêne Sent: Wednesday, December 20, 2017 5:23 PM
>I wonder is there is a way with SQLite3, to reuse a often used and
>moderately long common table expression without the need to copy/paste it in
>every query text it is used in.
I use a C preprocessor for this and pass the queries throug
Hi there,
I wonder is there is a way with SQLite3, to reuse a often used and
moderately long common table expression without the need to copy/paste it in
every query text it is used in.
I know there is a way to add a function to SQLite connexions using DB APIs,
like that of Python. Still, as much
Well, if your parent and child are going to be integers, then you can do some
magic with strings. (This is with the assumption that an edge can't go from and
to the same node)
Here's something to get the non-looping paths:
with recursive x (parent, path, child) as (
select parent, cast(parent a
Hello,
I have an edges table -
sqlite> .sch edges
CREATE TABLE edges(parent, child);
sqlite> select * from edges;
parent child
1 2
1 3
2 4
3 1
4 5
5 2
Here we have two cycles -
1) 1 => 3 => 1 (length 1)
2) 2 => 4 => 5 => 2 (length 3)
Cycles cause recursive
On 2017/12/20 11:01 AM, Dominique Devienne wrote:
"extremely efficient" is a bit exaggerated IMHO.
More space efficient definitely, see serial types 8 and 8 in [1], so only
the record's header section need to be consulted,
w/o the need to decode the whole record or decode the varint [2], thus m
On 2017/12/20 7:27 PM, Simon Slavin wrote:
On 20 Dec 2017, at 5:19pm, David Raymond wrote:
I have often thought that a "create analyzed index" statement would be nice
addition though to do the two at the same time.
Perhaps not as useful as you might think, because most people create indexes
Order of those two create index statements isn't going to matter, neither is
going to speed up the execution of the other.
If it was a database in WAL mode with other people reading from the database
while you're making the indexes, then start with the one that would be used
more, as people wil
On 20 Dec 2017, at 5:19pm, David Raymond wrote:
> I have often thought that a "create analyzed index" statement would be nice
> addition though to do the two at the same time.
Perhaps not as useful as you might think, because most people create indexes
while their tables are empty. ANALYZE
Analyze has to be run separately. Create Index doesn't gather any stats while
it's running and doesn't update any of the sqlite_stat1-4 tables. Create Index
is all you need to run for the index to be created, populated, and used.
Analyze gives the query planner a more accurate view of how useful
Great explanation, thanks
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Hick Gunter
Sent: Wednesday, December 20, 2017 10:41 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] create index implies analyze?
CREATE INDE
Hick Gunter Sent: Wednesday, December 20, 2017 10:51 AM
>You are not showing the definition of data. Some table constraints (e.g.
>UNIQUE or PRIMARY KEY) are implemented via an index, so creating a superflous
>index that duplicates an automatically created index only serves to waste
>space in t
On 20 Dec 2017, at 3:38pm, Nelson, Erik - 2
wrote:
> Assuming that table 'data' is completely constructed, does index creation
> order have any performance ramifications?
>
> For example, would it be reasonable to assume that the order of these two
> statements has no performance impact? (
You are not showing the definition of data. Some table constraints (e.g. UNIQUE
or PRIMARY KEY) are implemented via an index, so creating a superflous index
that duplicates an automatically created index only serves to waste space in
the file and time to maintain.
It is possible that creating t
CREATE INDEX will populate the index with references to all of the rows in the
table. Bulk loads may run considerably faster if no indices are present at load
time (not yet created or dropped beforehand), but created right after the data
has been inserted. If you run INSERT or UPDATE statements
Assuming that table 'data' is completely constructed, does index creation order
have any performance ramifications?
For example, would it be reasonable to assume that the order of these two
statements has no performance impact? (all fields are integer in this case)
create unique index data_ind
If I am finished making inserts into a table, does the act of creating an index
also populate the index? That is, do I need to separately run 'analyze'?
My impression was that there was no need to call analyze unless rows had been
inserted after index creation.
Just seen this
Each temporary table and index is given its own page cache which can store a
maximum number of database pages determined by the
SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is
500 pages.) The maximum number of database pages in the page cache is the s
On 19 Dec 2017, at 8:37pm, zakari wrote:
> pasting some logs, Im declaring again this happening only the first time,
> afterwards working without problem.
> 2017-12-17 15:16:23 - execute
> 2017-12-17 15:17:20 - executed
>
> 2017-12-19 14:53:35 - execute
> 2017-12-19 14:54:32 - executed
>
> 20
hi all,
I have exactly the same problem with topic :
http://sqlite.1065341.n5.nabble.com/SELECT-query-first-run-is-VERY-slow-td33100i20.html
--
The dbase sitting on linux server, Im accessing the dbase with PDO object.
-connected
-prepare the statement
-execute
here makes =>1min lag, *only the
On 19/12/2017 13:55, Richard Hipp wrote:
On 12/18/17, Lee, Greg wrote:
I am still seeing the problem on Power 8 and others report the problem
persists on Power 9. Please see the spack github issue. I also attached a
configure/make output if that helps.
So, what you are saying, then, is that "
The problem appears to come from the inclusion of editline/readline.h, which
ultimately includes termios.h. I'm not sure who should "fix" this, but below is
a simple reproducer demonstrating the error. In Spack, we have a patch to
sqlite's shell.c that applies the undef trick that I use in the r
I promised to share my fruits with Tcl. I took some time (very busy with a
lot of Tcl things), but here is one example.
I had some problems with the temperature of my CPU. So I wrote a systemd
service (with Tcl) to log the temperature every minute to a SQLite
database. And wrote the following scri
Now I using the following -
CREATE TABLE nodes(id integer primary key, description text);
CREATE TABLE edges(parent references nodes not null, child references nodes
not null check (parent<>child), primary key(parent, child));
This seems to prevent the insertion of duplicate and parent=child reco
On Tue, Dec 19, 2017 at 6:05 PM, Simon Slavin wrote:
> On 19 Dec 2017, at 4:15pm, Dinu wrote:
> > 3) "Deleted" bit field - presumably the "soft delete" as you call it;
> If you do try this, the 'bit' column should be declared as INTEGER and the
> values stored should be 0 and 1. SQLite is extre
Nice solution!
CREATE TABLE edges(parent references nodes, child references nodes check
(parent<>child));
seems to be an equivalent but shorter statement.
On 20 December 2017 at 07:49, Simon Slavin wrote:
>
>
> Yes !
>
> CREATE TABLE edges(
> parent INTEGER references nodes,
> child IN
32 matches
Mail list logo