Re: [sqlite] Number of open connections

[Richard Hipp]

On 3/11/19, Chris Locke  wrote:
> Does SQLite keep a count of the number of current open connections to the
> database?

No.

SQLite can find out if some other connection has the database open in
WAL mode, or if some other database has an active transaction, because
it needs to know those things.  But there is no counter.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

[Simon Slavin]

On 11 Mar 2019, at 8:32pm, Wout Mertens  wrote:

> Don't listen to me, Simon's answer is way better :)

Your answer was absolutely correct.  I just answered some other stuff too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

[Tim Streater]

On 11 Mar 2019, at 20:21, Simon Slavin  wrote:

> On 11 Mar 2019, at 7:30pm, Tim Streater  wrote:
>
>> What is the maximum size in bytes that a result set may be? And what happens
>> if that size were to be exceeded?
>
> [The following is simplified for clarity. I discuss only worst cases and
> ignore caching.]
>
> SQLite does not prepare an entire result set at once. Instead, it returns one
> row each time you call sqlite3_step(). It's up to your own program to process
> each row as it is returned. Since SQLite does not hold more than one row in
> memory at once, theoretically there's no limit on how many rows it can return
> for a single query.

[snip]

> 

Thanks for that, Simon. That would account for there being nothing on the 
limits.html page about a maximum result set size. My question came up because 
someone, using a language that has an API for a number of flavours of SQL 
(including SQLite), was experiencing a crash when his result set got to about a 
gig in size. So, as seems very often to be the case, it must be the interface 
API code to the SQLite library that was at fault.

@Wout: nothing wrong with your reply, it was the TL:DR; version :-)


-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[Graham Holden]

Monday, March 11, 2019, 6:59:56 PM, E.Pasma  wrote:

> I can confirm that this has nothing to do with the sqlite version,
> as it is so in the tcl binding from the current release (3.28.0.).
> There is no "mode" method or paramater like in the shell.  I'd
> probably work around this by an update in SQL, after the import. Or
> import into a view, with an "instead of insert" trigger to insert
> cleaned data into the table.   
> Sorry, E. Pasma.

Thanks for the confirmation. For what I'm doing (a mostly one-off
data import) a sufficient work-around is to use Tcl to "exec" the
SQLite shell and have it run ".import".

Regards,
Graham Holden



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

[Wout Mertens]

Don't listen to me, Simon's answer is way better :)

Wout.


On Mon, Mar 11, 2019 at 9:22 PM Wout Mertens  wrote:

> There is no fixed limit, and the sqlite API just walks through the
> results, so any memory overrun that happens is due to application level
> code.
>
> Wout.
>
>
> On Mon, Mar 11, 2019 at 8:30 PM Tim Streater  wrote:
>
>> What is the maximum size in bytes that a result set may be? And what
>> happens if that size were to be exceeded?
>>
>>
>> --
>> Cheers  --  Tim
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

[Wout Mertens]

There is no fixed limit, and the sqlite API just walks through the results,
so any memory overrun that happens is due to application level code.

Wout.


On Mon, Mar 11, 2019 at 8:30 PM Tim Streater  wrote:

> What is the maximum size in bytes that a result set may be? And what
> happens if that size were to be exceeded?
>
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Maximum result set size

[Simon Slavin]

On 11 Mar 2019, at 7:30pm, Tim Streater  wrote:

> What is the maximum size in bytes that a result set may be? And what happens 
> if that size were to be exceeded?

[The following is simplified for clarity.  I discuss only worst cases and 
ignore caching.]

SQLite does not prepare an entire result set at once.  Instead, it returns one 
row each time you call sqlite3_step().  It's up to your own program to process 
each row as it is returned.  Since SQLite does not hold more than one row in 
memory at once, theoretically there's no limit on how many rows it can return 
for a single query.

However, some queries may require SQLite to prepare a temporary index.  For 
example, a SELECT with an ORDER BY where there's no useful index.  A large 
temporary index would be saved on disk until it is no longer needed.  In these 
situations, SQLite is limited by the amount of disk space made available to the 
application calling SQLite.

Similarly, you may be calling sqlite3_exec() instead of sqlite3_step().  Or you 
may be calling SQLite through a library which does hold an entire result set in 
memory at one time.  In that case, the maximum size of a result set is limited 
by the amount of memory the application can use, and management of that memory 
takes place outside of SQLite.

Attempts to exceed any of the above limits would lead to result codes such as 
SQLITE_NOMEM or SQLITE_IOERR.  A list of result codes can be found here:



For details on the limits to SQLite's other things such as the most rows a 
table can hold, please see this document:


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Maximum result set size

[Tim Streater]

What is the maximum size in bytes that a result set may be? And what happens if 
that size were to be exceeded?


-- 
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[E.Pasma]

> Op 11 mrt. 2019, om 13:20 heeft Graham Holden  het 
> volgende geschreven:
> 
> I'm using SQLite through Tcl, and am having a problem with the
> sqlite3/Tcl "copy" command (similar to the shell's ".import" command).
> 
> Given "test.csv"
> 1,"aaa","bbb ccc"
> 
> Using the shell, I get the following -- the double-quotes from the CSV
> are not stored in the database:
> 
> create table test ( id integer primary key, str1 text, str2 text ) ;
> .mode csv
> .import test.csv test
> .mode column
> select * from test ;
> 1   aaa bbb ccc
> 
> but using the following Tcl script:
> 
> package require sqlite3
> sqlite3 db :memory:
> db eval "create table test ( id integer primary key, str1 text, str2 text )"
> db copy ignore test test.csv ","
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
> db eval "update test set str1='aaa', str2='bbb ccc'"
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
> 
> produces:
> |1|"aaa"|"bbb ccc":
> |1|aaa|bbb ccc:
> 
> showing the double-quotes are stored in the database.
> 
> Is there a way to not get the double-quotes stored? NOTE: It may be a
> "version thing"... the version of SQLite bundled-in with the copy of
> Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
> Unfortunately, I can't easily switch the Tcl version at the moment
> (but I might have to if it is something that's been fixed).
> 
> On a related note, under Windows, with CR-LF terminated lines in the
> CSV file, the CR also gets stored in the string (that's the reason for
> the ":" in the above test script: the value for 'str2' becomes
> 
>  "bbb ccc"\r
> 
> (with an embedded carriage-return), but I can work around this by
> switching to LF-terminated lines.
> 
> TIA for any help,
> Graham

Hello,
I can confirm that this has nothing to do with the sqlite version, as it is so 
in the tcl binding from the current release (3.28.0.). There is no "mode" 
method or paramater like in the shell.
I'd probably work around this by an update in SQL, after the import. Or import 
into a view, with an "instead of insert" trigger to insert cleaned data into 
the table.
Sorry, E. Pasma.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Number of open connections

[Chris Locke]

Does SQLite keep a count of the number of current open connections to the
database?
On the DB4S mailing list, there is an enquiry (
https://github.com/sqlitebrowser/sqlitebrowser/issues/1798) about
encryption failing due to the database being open.  Was wondering whether a
PRAGMA or function returned the number of open database connections.


Thanks,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive CTE on tree with doubly linked items

[Keith Medcalf]

On Monday, 11 March, 2019 09:42, heribert  wrote:

>it works perfect - but i do not understand why.

See https://sqlite.org/lang_with.html for a description of recursive queries ...


>The 'inital-select' results with the head node - only one result set.

>SELECT *
>   FROM Tree
>   WHERE ParentIDX = (SELECT ParentIDX
>  FROM Tree
>  WHERE ID = 3)
> AND PrevIDX IS NULL
>
>Points SiblingsOf3 after your 'initial-select' to this head node?
>
>Why (and how) iterates your 'recursive-select'?
>
>SELECT Tree.*
>FROM Tree
>JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive CTE on tree with doubly linked items

[heribert]

Thx clemens,

it works perfect - but i do not understand why.

The 'inital-select' results with the head node - only one result set.

SELECT *
  FROM Tree
  WHERE ParentIDX = (SELECT ParentIDX
 FROM Tree
 WHERE ID = 3)
AND PrevIDX IS NULL

Points SiblingsOf3 after your 'initial-select' to this head node?

Why (and how) iterates your 'recursive-select'?

SELECT Tree.*
FROM Tree
JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID

Best regards
heribert



heribert wrote:

I've a tree with doubly linked items. I want to get all siblings of a tree node.

If you want them in order, you have to walk through the linked list:

WITH SiblingsOf3 AS (
   SELECT *
   FROM Tree
   WHERE ParentIDX = (SELECT ParentIDX
  FROM Tree
  WHERE ID = 3)
 AND PrevIDX IS NULL

   UNION ALL

   SELECT Tree.*
   FROM Tree
   JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID
)
SELECT * FROM SiblingsOf3;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive CTE on tree with doubly linked items

[Clemens Ladisch]

heribert wrote:
> I've a tree with doubly linked items. I want to get all siblings of a tree 
> node.

If you want them in order, you have to walk through the linked list:

WITH SiblingsOf3 AS (
  SELECT *
  FROM Tree
  WHERE ParentIDX = (SELECT ParentIDX
 FROM Tree
 WHERE ID = 3)
AND PrevIDX IS NULL

  UNION ALL

  SELECT Tree.*
  FROM Tree
  JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID
)
SELECT * FROM SiblingsOf3;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Difference between ".import" and tcl/sqlite3 "copy"

[Graham Holden]

I'm using SQLite through Tcl, and am having a problem with the
sqlite3/Tcl "copy" command (similar to the shell's ".import" command).

Given "test.csv"
1,"aaa","bbb ccc"

Using the shell, I get the following -- the double-quotes from the CSV
are not stored in the database:

create table test ( id integer primary key, str1 text, str2 text ) ;
.mode csv
.import test.csv test
.mode column
select * from test ;
1   aaa bbb ccc

but using the following Tcl script:

package require sqlite3
sqlite3 db :memory:
db eval "create table test ( id integer primary key, str1 text, str2 text )"
db copy ignore test test.csv ","
db eval "select * from test" { puts "|$id|$str1|$str2:" }
db eval "update test set str1='aaa', str2='bbb ccc'"
db eval "select * from test" { puts "|$id|$str1|$str2:" }

produces:
|1|"aaa"|"bbb ccc":
|1|aaa|bbb ccc:

showing the double-quotes are stored in the database.

Is there a way to not get the double-quotes stored? NOTE: It may be a
"version thing"... the version of SQLite bundled-in with the copy of
Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
Unfortunately, I can't easily switch the Tcl version at the moment
(but I might have to if it is something that's been fixed).

On a related note, under Windows, with CR-LF terminated lines in the
CSV file, the CR also gets stored in the string (that's the reason for
the ":" in the above test script: the value for 'str2' becomes

  "bbb ccc"\r

(with an embedded carriage-return), but I can work around this by
switching to LF-terminated lines.

TIA for any help,
Graham



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive CTE on tree with doubly linked items

[Jean-Luc Hainaut]

Your implementation of trees is that of network databases at the 
pointer-based physical level but definitely not relational. Try this:


create table TREE(
  ID integer not null primary key,
  Parent  integer references TREE on delete ... on update cascade); -- 
Notice the absence of "not null"

create index XTREE on TREE(Parent); -- Only useful for large sets of nodes

That's all.

From this, CTE and non-CTE queries just are easy, elegant and fast. For 
instance extracting the siblings of a note is the translation of their 
intuitive definition: "nodes with the same parent" :


select * from TREE where Parent = 2.

Regards

J-L Hainaut

On 11/03/2019 09:08, heribert wrote:
I've a tree with doubly linked items. I want to get all siblings of a 
tree node (e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can 
not find the solution. I looked for any exemplary solution - but do 
not find some.


DROP TABLE IF EXISTS "Tree";

CREATE TABLE "Tree" (
  "ID" INTEGER,
  "PrevIDX" INTEGER DEFAULT NULL,
  "NextIDX" INTEGER DEFAULT NULL,
  "ParentIDX" INTEGER DEFAULT NULL,
  PRIMARY KEY ("ID"),
  FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
  FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
  FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE
);

INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL);
INSERT INTO "Tree" VALUES (2, NULL, 3, 1);
INSERT INTO "Tree" VALUES (3, 2, 4, 1);
INSERT INTO "Tree" VALUES (4, 3, NULL, 1);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Recursive CTE on tree with doubly linked items

[heribert]

Siblings (in my case) are nodes have the same parent - the NextIDX and 
PrevIDX are only used for ordering sibling nodes. Every node may be 
parent of other nodes. The ParentIDX is the downward ID of the parent node.


Yes, you are right: If i delete a node (parent node) all childs of the 
node will be deleted too and the prev-/next-sibling-node of  the deleted 
"parent node" have to be relinked. I will do this with by updating the 
NextIDX and PrevIDX of the sibling-nodes.


I'm looking for a solution to get a ordered ID list of the siblings (or 
childs of a parent node).


e.g. ordered child list the parent node ID=1 -> 2, 5, 3

1...2
 .
 .
 5...4
  .   6
  .
 3



You might like to consider writing the phrase INTEGER PRIMARY KEY to make ID an alias for 
the rowid, or adding the phrase WITHOUT ROWID to make ID the "true" primary key.

What is your definition of "sibling"? Is it not the set of nodes reachable via the 
PrevIdx and (respecitvely in the case of a circularyl linked list, or) NextIdx links? Or more 
simply, having hte same parent? Or maybe you are looking for "cousins" (same level but 
different parents) too?

Linking each node upwards, but none downwards makes traversal difficult. Also, I am not 
sure what ON DELETE CASCADE on the "parent" link is for, as it will orphan the 
siblings of a deleted node and CASCADE right up to the root of the tree.

SELECT ID FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree WHERE ID=?);

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von heribert
Gesendet: Montag, 11. März 2019 09:08
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] Recursive CTE on tree with doubly linked items

I've a tree with doubly linked items. I want to get all siblings of a tree node 
(e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can not find 
the solution. I looked for any exemplary solution - but do not find some.

DROP TABLE IF EXISTS "Tree";

CREATE TABLE "Tree" (
"ID" INTEGER,
"PrevIDX" INTEGER DEFAULT NULL,
"NextIDX" INTEGER DEFAULT NULL,
"ParentIDX" INTEGER DEFAULT NULL,
PRIMARY KEY ("ID"),
FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE );

INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL); INSERT INTO "Tree" VALUES (2, NULL, 3, 1); INSERT 
INTO "Tree" VALUES (3, 2, 4, 1); INSERT INTO "Tree" VALUES (4, 3, NULL, 1);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Recursive CTE on tree with doubly linked items

[Hick Gunter]

You might like to consider writing the phrase INTEGER PRIMARY KEY to make ID an 
alias for the rowid, or adding the phrase WITHOUT ROWID to make ID the "true" 
primary key.

What is your definition of "sibling"? Is it not the set of nodes reachable via 
the PrevIdx and (respecitvely in the case of a circularyl linked list, or) 
NextIdx links? Or more simply, having hte same parent? Or maybe you are looking 
for "cousins" (same level but different parents) too?

Linking each node upwards, but none downwards makes traversal difficult. Also, 
I am not sure what ON DELETE CASCADE on the "parent" link is for, as it will 
orphan the siblings of a deleted node and CASCADE right up to the root of the 
tree.

SELECT ID FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree WHERE ID=?);

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von heribert
Gesendet: Montag, 11. März 2019 09:08
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] Recursive CTE on tree with doubly linked items

I've a tree with doubly linked items. I want to get all siblings of a tree node 
(e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can not find 
the solution. I looked for any exemplary solution - but do not find some.

DROP TABLE IF EXISTS "Tree";

CREATE TABLE "Tree" (
   "ID" INTEGER,
   "PrevIDX" INTEGER DEFAULT NULL,
   "NextIDX" INTEGER DEFAULT NULL,
   "ParentIDX" INTEGER DEFAULT NULL,
   PRIMARY KEY ("ID"),
   FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
   FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
   FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE );

INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL); INSERT INTO "Tree" VALUES (2, 
NULL, 3, 1); INSERT INTO "Tree" VALUES (3, 2, 4, 1); INSERT INTO "Tree" VALUES 
(4, 3, NULL, 1);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recursive CTE on tree with doubly linked items

[heribert]

I've a tree with doubly linked items. I want to get all siblings of a 
tree node (e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can 
not find the solution. I looked for any exemplary solution - but do not 
find some.


DROP TABLE IF EXISTS "Tree";

CREATE TABLE "Tree" (
  "ID" INTEGER,
  "PrevIDX" INTEGER DEFAULT NULL,
  "NextIDX" INTEGER DEFAULT NULL,
  "ParentIDX" INTEGER DEFAULT NULL,
  PRIMARY KEY ("ID"),
  FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
  FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
  FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE
);

INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL);
INSERT INTO "Tree" VALUES (2, NULL, 3, 1);
INSERT INTO "Tree" VALUES (3, 2, 4, 1);
INSERT INTO "Tree" VALUES (4, 3, NULL, 1);

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] where did my data go ? Re: Import data into a temporary table

[Hick Gunter]

And omitting

.mode csv

is probably messing up the .import

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Samstag, 09. März 2019 10:32
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] where did my data go ? Re: Import data into a 
temporary table

On 7-3-2019 22:45, Eric Tsau wrote:
> Hi,
> Is it possible to add the option of importing data into a temporary table?
> Currently you have to create a temporary table first before importing
> to it, or having to drop the table afterwards.
> .import dump.csv temp.table
> or
> .import dump.csv attach.table
>
> Regards
> Eric
> C:\TEMP>del test.sqlite


C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] where did my data go ? Re: Import data into a temporary table

[Hick Gunter]

Are you aware of the fact that your csv file describes a table containting 
three columns (name a, b, and c) whereas your SQL describes a single column 
named a,b,c with embedded commas in the values too?

Also please note that a temp table is disposed of when the connection is closed.

So what you are seeing is an empty temp table shadowing a main table of the 
same name. Try to avoid giving tables in different databases the same names. 
The exact same statement will mean different things depending on the order of 
attaching the databases unless all names are qualified.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Samstag, 09. März 2019 10:32
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] where did my data go ? Re: Import data into a 
temporary table

On 7-3-2019 22:45, Eric Tsau wrote:
> Hi,
> Is it possible to add the option of importing data into a temporary table?
> Currently you have to create a temporary table first before importing
> to it, or having to drop the table afterwards.
> .import dump.csv temp.table
> or
> .import dump.csv attach.table
>
> Regards
> Eric
> C:\TEMP>del test.sqlite


C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
   "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlar: makeDirectory called with permission bits of file

[Winter, Martin]

Hi,

when extracting files in subdirectories from an sqlar archive, or when 
otherwise using the writefile function to create a file in a subdirectory, the 
makeDirectory function is called with the permission bits of the file. If e.g. 
the file has permission bits 0664, then the directory will have the same bits 
set, which results in a directory in which files cannot be accessed, as the 
execute bits are not set.

In this situation the permission bits of the directory are not explicitly 
given. However it would be much more appropriate to assume a 0755 permission 
for the directory, instead of using those of the file.

In the current code base the relevant code can be found at

https://www.sqlite.org/src/artifact?udc=1=on=e7864c391e14ccaf

line 481ff, where the "mode" variable is used both to create the file and to 
create the directory, if necessary.

Martin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users