date:20180501

nX is a number, the smallest allowed count. There are two conditions,  count of 
dots along horizontal line and count of dots along verticals.



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Barry Smith 
Date: 5/1/18 7:40 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] probably recursive?

Ah my bad, I misunderstood the initial condition. nX is a function of X. My 
statements were only true if nX=X. Well, sorry about the noise.

> On 2 May 2018, at 8:20 am, Roman Fleysher  
> wrote:
>
> Dear Barry,
>
> The statement about the square is not obvious to me. The requirements on 
> counts in x and y are different.
>
> I also imagine answer could be two or several non-overlapping  "rectangles". 
> "Rectangles" will not be densely filled with dots, they might have empty 
> spots either because the points were never on the list or were eliminated.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Well those constraints simplify your problem.
>
> In the resultant dataset, the largest X and Y values will be equal, and the 
> largest X will have and entry for every coordinate from (X, 1) to (X, X). 
> Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
> (Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
> points outside that square will be culled, all points on and inside the 
> square will be kept.
>
> Since you know that, you now have a one dimensional problem to solve. It 
> still seems a little recursive to me, but it should be easier because you 
> only need to find a single number (which you can then plug into a delete 
> statement).
>
> If my statement about the square is not obvious to prove in your head I can 
> try write a proof for that but I'm not much good at proofs.
>
>> On 2 May 2018, at 7:27 am, Roman Fleysher  
>> wrote:
>>
>> Pairs (x,y) do not repeat.
>>
>> Actual x and y are positive integers, but I do not see how being positive 
>> can be relevant. Integer is important for sorting/comparison.
>>
>>
>> Roman
>>
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>>
>> Is there a uniqueness constraint on your initial data? Can the same 
>> coordinate be listed multiple times?
>>
>> Is there a requirement that X > 0 and Y > 0?
>>
 On 2 May 2018, at 3:35 am, Simon Slavin  wrote:

 On 1 May 2018, at 6:28pm, Simon Slavin  wrote:

 I just realised that
>>>
>>> That was intended to be personal email.  Apologies, everyone.
>>>
>>> Simon.
>>> ___
>>> 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
>> ___
>> 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
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

Ah my bad, I misunderstood the initial condition. nX is a function of X. My 
statements were only true if nX=X. Well, sorry about the noise.

> On 2 May 2018, at 8:20 am, Roman Fleysher  
> wrote:
> 
> Dear Barry,
> 
> The statement about the square is not obvious to me. The requirements on 
> counts in x and y are different.
> 
> I also imagine answer could be two or several non-overlapping  "rectangles". 
> "Rectangles" will not be densely filled with dots, they might have empty 
> spots either because the points were never on the list or were eliminated.
> 
> Roman
> 
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
> 
> Well those constraints simplify your problem.
> 
> In the resultant dataset, the largest X and Y values will be equal, and the 
> largest X will have and entry for every coordinate from (X, 1) to (X, X). 
> Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
> (Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
> points outside that square will be culled, all points on and inside the 
> square will be kept.
> 
> Since you know that, you now have a one dimensional problem to solve. It 
> still seems a little recursive to me, but it should be easier because you 
> only need to find a single number (which you can then plug into a delete 
> statement).
> 
> If my statement about the square is not obvious to prove in your head I can 
> try write a proof for that but I'm not much good at proofs.
> 
>> On 2 May 2018, at 7:27 am, Roman Fleysher  
>> wrote:
>> 
>> Pairs (x,y) do not repeat.
>> 
>> Actual x and y are positive integers, but I do not see how being positive 
>> can be relevant. Integer is important for sorting/comparison.
>> 
>> 
>> Roman
>> 
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>> 
>> Is there a uniqueness constraint on your initial data? Can the same 
>> coordinate be listed multiple times?
>> 
>> Is there a requirement that X > 0 and Y > 0?
>> 
 On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
 
 On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
 
 I just realised that
>>> 
>>> That was intended to be personal email.  Apologies, everyone.
>>> 
>>> Simon.
>>> ___
>>> 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
>> ___
>> 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
> ___
> 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] probably recursive?

In you initial email, what is n? Some real number between zero and one?

> On 2 May 2018, at 8:37 am, Abroży Nieprzełoży 
>  wrote:
> 
> I think Barry mean that you can represent the (x,y) pair as a single
> number like (max(X)-min(X))*(Y-min(Y))+X-min(X) or so, but I don't see
> how it would be helpful.
> 
> 2018-05-02 0:20 GMT+02:00, Roman Fleysher:
>> Dear Barry,
>> 
>> The statement about the square is not obvious to me. The requirements on
>> counts in x and y are different.
>> 
>> I also imagine answer could be two or several non-overlapping  "rectangles".
>> "Rectangles" will not be densely filled with dots, they might have empty
>> spots either because the points were never on the list or were eliminated.
>> 
>> Roman
>> 
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 6:12 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>> 
>> Well those constraints simplify your problem.
>> 
>> In the resultant dataset, the largest X and Y values will be equal, and the
>> largest X will have and entry for every coordinate from (X, 1) to (X, X).
>> Likewise the largest Y will have an entry for every coordinate from (1, Y)
>> to (Y, Y). Basically you'll have two lines from the axes, drawing a square.
>> All points outside that square will be culled, all points on and inside the
>> square will be kept.
>> 
>> Since you know that, you now have a one dimensional problem to solve. It
>> still seems a little recursive to me, but it should be easier because you
>> only need to find a single number (which you can then plug into a delete
>> statement).
>> 
>> If my statement about the square is not obvious to prove in your head I can
>> try write a proof for that but I'm not much good at proofs.
>> 
>>> On 2 May 2018, at 7:27 am, Roman Fleysher
>>> wrote:
>>> 
>>> Pairs (x,y) do not repeat.
>>> 
>>> Actual x and y are positive integers, but I do not see how being positive
>>> can be relevant. Integer is important for sorting/comparison.
>>> 
>>> 
>>> Roman
>>> 
>>> 
>>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>>> behalf of Barry Smith [smith.bar...@gmail.com]
>>> Sent: Tuesday, May 01, 2018 5:23 PM
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] probably recursive?
>>> 
>>> Is there a uniqueness constraint on your initial data? Can the same
>>> coordinate be listed multiple times?
>>> 
>>> Is there a requirement that X > 0 and Y > 0?
>>> 
> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
> 
> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
> 
> I just realised that
 
 That was intended to be personal email.  Apologies, everyone.
 
 Simon.
 ___
 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
>>> ___
>>> 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
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

2018-05-01 Thread Abroży Nieprzełoży

I think Barry mean that you can represent the (x,y) pair as a single
number like (max(X)-min(X))*(Y-min(Y))+X-min(X) or so, but I don't see
how it would be helpful.

2018-05-02 0:20 GMT+02:00, Roman Fleysher:
> Dear Barry,
>
> The statement about the square is not obvious to me. The requirements on
> counts in x and y are different.
>
> I also imagine answer could be two or several non-overlapping  "rectangles".
> "Rectangles" will not be densely filled with dots, they might have empty
> spots either because the points were never on the list or were eliminated.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Well those constraints simplify your problem.
>
> In the resultant dataset, the largest X and Y values will be equal, and the
> largest X will have and entry for every coordinate from (X, 1) to (X, X).
> Likewise the largest Y will have an entry for every coordinate from (1, Y)
> to (Y, Y). Basically you'll have two lines from the axes, drawing a square.
> All points outside that square will be culled, all points on and inside the
> square will be kept.
>
> Since you know that, you now have a one dimensional problem to solve. It
> still seems a little recursive to me, but it should be easier because you
> only need to find a single number (which you can then plug into a delete
> statement).
>
> If my statement about the square is not obvious to prove in your head I can
> try write a proof for that but I'm not much good at proofs.
>
>> On 2 May 2018, at 7:27 am, Roman Fleysher
>> wrote:
>>
>> Pairs (x,y) do not repeat.
>>
>> Actual x and y are positive integers, but I do not see how being positive
>> can be relevant. Integer is important for sorting/comparison.
>>
>>
>> Roman
>>
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>> behalf of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>>
>> Is there a uniqueness constraint on your initial data? Can the same
>> coordinate be listed multiple times?
>>
>> Is there a requirement that X > 0 and Y > 0?
>>
 On 2 May 2018, at 3:35 am, Simon Slavin  wrote:

 On 1 May 2018, at 6:28pm, Simon Slavin  wrote:

 I just realised that
>>>
>>> That was intended to be personal email.  Apologies, everyone.
>>>
>>> Simon.
>>> ___
>>> 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
>> ___
>> 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
> ___
> 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] probably recursive?

Dear Barry,

The statement about the square is not obvious to me. The requirements on counts 
in x and y are different.

I also imagine answer could be two or several non-overlapping  "rectangles". 
"Rectangles" will not be densely filled with dots, they might have empty spots 
either because the points were never on the list or were eliminated.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 6:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher  
> wrote:
>
> Pairs (x,y) do not repeat.
>
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
>
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
>
> Is there a requirement that X > 0 and Y > 0?
>
>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>>
>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>>
>>> I just realised that
>>
>> That was intended to be personal email.  Apologies, everyone.
>>
>> Simon.
>> ___
>> 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
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher  
> wrote:
> 
> Pairs (x,y) do not repeat.
> 
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
> 
> 
> Roman
> 
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
> 
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
> 
> Is there a requirement that X > 0 and Y > 0?
> 
>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>> 
>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>> 
>>> I just realised that
>> 
>> That was intended to be personal email.  Apologies, everyone.
>> 
>> Simon.
>> ___
>> 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
> ___
> 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] probably recursive?

Pairs (x,y) do not repeat.

Actual x and y are positive integers, but I do not see how being positive can 
be relevant. Integer is important for sorting/comparison.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 5:23 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Is there a uniqueness constraint on your initial data? Can the same coordinate 
be listed multiple times?

Is there a requirement that X > 0 and Y > 0?

> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>
>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>
>> I just realised that
>
> That was intended to be personal email.  Apologies, everyone.
>
> Simon.
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

Is there a uniqueness constraint on your initial data? Can the same coordinate 
be listed multiple times?

Is there a requirement that X > 0 and Y > 0?

> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
> 
>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>> 
>> I just realised that
> 
> That was intended to be personal email.  Apologies, everyone.
> 
> Simon.
> ___
> 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

[sqlite] Bug in transitive closure extension?

2018-05-01 Thread Charles Leifer

Hi all,

I'm noticing a bug in the transitive closure extension in the latest
version of SQLite.

Reproducing requires the closure extension, which I compiled:

gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so

SQL to populate db:

.load closure
-- create category table with self-referential parent fk.
CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL,
"parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category"
("id"));
CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category" ("parent_id");

-- create closure table vtable
CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING
transitive_closure (idcolumn=id, parentcolumn=parent_id,
tablename=category);

-- populate some data for a book catalog
INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1
INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2
INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3
INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); -- 4
INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5

-- get hard scifi and all its parents:
SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth"
FROM "category" AS "t1"
INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
WHERE ("t2"."id" = 4);

-- results:
-- 1|books||3
-- 2|fiction|1|2
-- 3|scifi|2|1
-- 4|hard scifi|3|0

-- get relations of "hard scifi" specifying depth > 0.
SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth"
FROM "category" AS "t1"
INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
WHERE (("t2"."id" = 4) AND ("t2"."depth" > 0));

-- results:
-- 1|books||3
-- 2|fiction|1|2
-- 3|scifi|2|1


-- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction"
-- get grand-parent category for hard-scifi (depth=2)
SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth"
FROM "category" AS "t1"
INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root")
WHERE (("t2"."id" = 4) AND ("t2"."depth" = 2));

It seems that when we query for equality on the categoryclosure id *and*
depth, xBestIndex runs into trouble?

Thanks,

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

2018-05-01 Thread David Burgess

For those of you who use SQLite to prepare CSV for import/open into
Excel beware of this problem:

"Text","Next bit is a reference id","A001"
"text","same again","0009"

On Windows, In the second row, 3rd column Excel will remove the
leading zeroes, if the file has an extension of .csv
The same content in a file with the extension .txt is processed as
csv, but without the removal of the leading zeroes from text values.

This is value translation not formatting. Nightmare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?



On 5/1/18, 1:42 PM, "sqlite-users on behalf of R Smith" 
 wrote:
My point is that CSV was not necessarily "meant" to be what you say. Who 
exactly "meant" for it to be that? Because the official stuff makes no 
such claim or mention.

Bah. Existential shenanigans. There's probably some pseudo-Latin or German term 
for what I meant.

Your point is however more towards the WHY question, and no, I am not 
sure. But, you know, it's Microsoft - after they made "locales" they 
probably wanted someone to use it, so they bullied Excel division into 
it. :)

From what I've heard the Excel division is the least bulliable part of the 
company. Didn't they maintain their own C compiler for a while to cut down on 
dependencies on the rest of Microsoft? 

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 2018/05/01 8:21 PM, Peter Da Silva wrote:

On 5/1/18, 1:15 PM, "sqlite-users on behalf of R Smith"
wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva
wrote:

> CSV is an interchange format, it's for software to communicate with other software, so the syntax needs to be independent of the locale since you don't know if the sender and recipient are in the same locale. Field separator is syntax, so the locale settings should not have any effect on it.

That is a very bold and idealistic claim.

I'm not claiming what you think I'm claiming. All I'm saying is that CSV is meant to
communicate with software, not humans. Locales are there for humans. Using "the
locale says list separates are semicolons" as a justification for sometimes using
semicolons in an interchange format instead of commas doesn't make sense.

My point is that CSV was not necessarily "meant" to be what you say. Who
exactly "meant" for it to be that? Because the official stuff makes no
such claim or mention.

That said, I'm agreeing, it is nowadays very much used as a data
exchange, no modern users read CSV files for a hobby, and Excel (or any
other software) using the locale for "List separator" inside CSV files
should probably turn a new page and stop doing that - I feel it will be
better, I'm just not as convinced as you that my opinion on this is
ultimate.

Do you actually have a reference for that being the reason for Excel using
semicolons?

I have zero proof that it is WHY they do it, but I have proof that it IS
what they do - the verification is rather easy, you can change your
windows locale setting for List separator and see for yourself. (The
record separator in turn affects the records, but mostly that is CRLF in
all locales). You might need to restart Excel after changing locale
settings.

Your point is however more towards the WHY question, and no, I am not
sure. But, you know, it's Microsoft - after they made "locales" they
probably wanted someone to use it, so they bullied Excel division into
it. :)

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 5/1/18, 1:15 PM, "sqlite-users on behalf of R Smith" 
 wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva  
wrote:

> CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

That is a very bold and idealistic claim.

I'm not claiming what you think I'm claiming. All I'm saying is that CSV is 
meant to communicate with software, not humans. Locales are there for humans. 
Using "the locale says list separates are semicolons" as a justification for 
sometimes using semicolons in an interchange format instead of commas doesn't 
make sense. Do you actually have a reference for that being the reason for 
Excel using semicolons?

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?


On 1 May 2018, at 6:43pm, Peter Da Silva  wrote:


CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.


That is a very bold and idealistic claim. Not even the person who wrote 
the currently used CSV standard (RFC4180 anyway) claims such things - in 
fact he is very quick to point out that it isn't a standard but more of 
a "commonly used format" which did not have official documentation and 
so he decided to write up some.  But I am on your side of this war - I 
feel it "should" be standard, it's just that since it isn't a standard, 
it's hard to lord it over others doing differently.


There have been very many custom implementations (complete with 
documentation) for many of the CSV formats around, but it never really 
progressed to a standard - which is why I assume so much trouble exists.


Probably my favourite line from RFC4180 is to Implementors: "be 
conservative in what you do, be liberal in what you accept from others".



While on the subject, when I was doing the converter mentioned earlier, 
I had researched the CSV thing trying to figure out a rule-book (with 
very little success), but I did make this little test CSV file handy to 
push through all the CSV importers/readers to see who does it how.


I managed to find it now again and thought I would post it for fun. I 
have a result set that is correct according to RFC4180 (and yes, SQLite 
gets it right) but as a fun exercise to the reader, put this text in a 
file named something.csv and open it in some CSV readers.


For more fun, try to predict the outcome before starting.

What I have found is that there are nearly as many differences in the 
interpretation as there are CSV interpreters.



csv file--
Item,Value1, Value 2
1,This is a non-quoted string.,"This is a ""quoted"" one"
2,"This contains a comma ',' and some single quotes.",This one doesn't
3,"Magic disappearing value after quoted value" This text should be 
hidden, This text must be visible.

4,"This is all on one line","This string goes
over
3 lines."
5, "The value to my right is empty, see how Excel gets this wrong!",
6, The,End
eof--

PS: According to the RFC, the last record of the last row may or may not 
contain an ending . I always add one since it makes appending the 
file easier later.


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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 1 May 2018, at 6:43pm, Peter Da Silva  wrote:

> CSV is an interchange format, it's for software to communicate with other 
> software, so the syntax needs to be independent of the locale since you don't 
> know if the sender and recipient are in the same locale. Field separator is 
> syntax, so the locale settings should not have any effect on it.

The problem is not a file format with semicolons in.  The problem is calling it 
"CSV" or allowing such files to be saved with ".csv" extensions.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

To another post hating on Excel - Excel has many flaws, but this is not 
one of them, it's a fault of the list-separator setting in the Windows 
OS on which the Excel runs.

CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

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

Re: [sqlite] probably recursive?

On 1 May 2018, at 6:28pm, Simon Slavin  wrote:

> I just realised that

That was intended to be personal email.  Apologies, everyone.

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

Re: [sqlite] probably recursive?

On 1 May 2018, at 1:45am, Roman Fleysher  wrote:

> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
> deletion, y=3 which previously had more than nY dots no longer passes the 
> threshold and thus y=3 must be deleted too. This could cause deletion of some 
> other x, etc. At the end, number of dots on all vertical lines must be more 
> than nX and number of dots on all horizontal lines must be more than nY.

I just realised that this is an image-processing problem, and could be 
completed ridiculously quickly by defining a filter in a GPU language.  If you 
have access to people working in image recognition, you might usefully consult 
them about how big a dataset they could support, or just show them the problem 
in general.

I would prototype it using a JavaScript canvas.  Once the initial data is 
written to the canvas you can use API calls to see what colour a pixel is, so 
the whole thing could be completed without needing further access to the SQLite 
table.  I would bet that any up-to-date browser could solve the problem in 
JavaScript fast enough for still images.  Applying it to successive frames of a 
moving image might be a different matter.

Not helpful for a SQLite solution, I'm afraid.

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

Re: [sqlite] probably recursive?

Agree. Thank you.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 01, 2018 12:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

On 1 May 2018, at 5:34pm, Roman Fleysher  wrote:

> With recursive route, I am thinking I need to build deleteList(x,y).

Rather than actually delete rows, if you can, insert a new column in the table 
of all points.  It starts with every row set to TRUE.  When you decide a row 
doesn't count the value gets set to FALSE.

This will be faster than doing the processing and file handling involved in 
deleting rows.

Simon.
___
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] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 2018/05/01 4:20 PM, Simon Slavin wrote:

On 1 May 2018, at 3:01pm, Olivier Mascia wrote:

My question was more generic, even though it didn't look that way: the well-known and (maybe too)
much-used software tool named Excel tend to encourage people to export "CSV" files which
are actually "SCSV" files (semi-colon separated values). So the need to script some data
happens regularly.

What kind of insanity would lead someone to invent semi-colon delimited format
when CSV already existed ? I bet it was a badly-thought-out solution for
European numeric formats which use a comma as a decimal point. Tell whoever
uses that format to stop.

*Sigh* If only...

There are only a couple of Countries using semi-colon list separators as
far as I am aware, but the problem is that one of those is Holland - not
exactly some third-World stow-away island that can be ignored. A lot of
the things we make had to be Holland-friendly and so allowing
semi-colon-happy CSV files became the norm.

I have successfully made a simple converter to read semi-colon CSV files
(in fact, to detect which separator is used), and convert if needed,
which does the trick, though it makes some important assumptions. (I'm
willing to share if interested, but it's a Windoze CLI, mail me direct
if needed).

To another post hating on Excel - Excel has many flaws, but this is not
one of them, it's a fault of the list-separator setting in the Windows
OS on which the Excel runs... It's also a flaw of software anyone may
make, where it will produce one sort of output in one Country and a
different kind in another, if you bother to use the locale settings.
Also, the doubled-double-Quote char escaping is the law of the CSV
standard, not of Excel, and backslash escaping is by no means more
common or more or less correct. All of CSV and all of SQL use doubled-up
quotes to escape strings - which must make it vastly more common than
anything else.

To make matters worse, some enlightened Dutchmen realise this semi-colon
insanity and so cleverly set their own computer's locale to use the
comma in stead of the semi-colon. So now you cannot trust that it will
use the semi-colon either - you HAVE to manually check and act accordingly.

Some other countries have other problems, like setting the decimal
separator to a comma - imagine that in a CSV file - completely breaking
it unless you cleverly Quote all the numeric values too, or ignore that
local setting too Just silly.

I agree - someone needs to be shot for this. I just can't really figure
out who.

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

Re: [sqlite] probably recursive?

On 1 May 2018, at 5:34pm, Roman Fleysher  wrote:

> With recursive route, I am thinking I need to build deleteList(x,y).

Rather than actually delete rows, if you can, insert a new column in the table 
of all points.  It starts with every row set to TRUE.  When you decide a row 
doesn't count the value gets set to FALSE.

This will be faster than doing the processing and file handling involved in 
deleting rows.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

2018-05-01 Thread Luuk



On 1-5-2018 16:20, Simon Slavin wrote:
> On 1 May 2018, at 3:01pm, Olivier Mascia  wrote:
>
>> My question was more generic, even though it didn't look that way: the 
>> well-known and (maybe too) much-used software tool named Excel tend to 
>> encourage people to export "CSV" files which are actually "SCSV" files 
>> (semi-colon separated values). So the need to script some data happens 
>> regularly.
> What kind of insanity would lead someone to invent semi-colon delimited 
> format when CSV already existed ?  I bet it was a badly-thought-out solution 
> for European numeric formats which use a comma as a decimal point.  Tell 
> whoever uses that format to stop.
>
> Seems like the best way to solve this would be to write a converter for 
> Windows which converts SCSV to CSV.  Then it could be used by all Excel users 
> instead of just SQLite users.  Take a look at
>
> 
>
> 
>
> and hack up a solution.  I'd do it myself but I don't use Windows.
>
> Simon.
>

You 'forgot' this link:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_windows8-mso_2010/sep-support-for-older-excel/293076a3-6593-4ceb-8167-d29aa3418773

;)


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

Re: [sqlite] probably recursive?

With recursive route, I am thinking I need to build deleteList(x,y). But I can 
not come up with a way to use deleteList only once in the FROM after UNION and 
not in subqueries , as required by WITH RECURSIVE. Assuming pairsTable(x,y) is 
the input table: 

WITH RECURSIVE deleteList(x, y) AS 
( SELECT NULL, NULL
  UNION
  SELECT x, y FROM pairsTable 
  WHERE x IN (SELECT x FROM (SELECT x, y FROM pairsTable 
   EXCEPT 
   SELECT x, y FROM 
deleteList WHERE x IS NOT NULL)
   GROUP BY x HAVING count(x) < 25)
)
SELECT x, y FROM deleteList;

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Tuesday, May 01, 2018 10:27 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

My initial thought on this would be recursive on delete triggers. You're 
limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really 
big cascades wouldn't fully complete. You can raise the limit, but 
mathematically speaking there's still going to be a limit then.

Will have to think about the recursive CTE route later.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Tuesday, May 01, 2018 8:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] probably recursive?

That depends on what you mean by "Could this be achieved in SQLite?".

There is no query (in any SQL engine) that can depend on a sub-query
that is itself dependent on the outcome of the main query. This is what
makes recursion beautiful, but then there is also no CTE (or other query
in any SQL engine) that can recurse over multiple states of data (i.e.
query data in one single query to reflect results from both before and
after a delete in the source table), nor can a CTE be updated or deleted
from, its data must persist atomically (with some exceptions when using
non-deterministic functions, like random).

These are not so much "inabilities" of SQL engines, but more due to
explicit SQL and set-algebra rules.

So this is not possible in a single query.

You can of course "achieve" it using any SQL engine by constructing a
temporary table, and then repeatedly run a DELETE query for all x values
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x)
< nX, rinse, repeat until  both SELECT y HAVING COUNT(x) < nX and SELECT
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this
would be painfully slow next to a simple software algorithm that
prunes/resolves a 2-dimensional array - exponentially worse so for
larger grid sizes.

On 2018/05/01 2:45 AM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have trouble solving this problem, maybe it is impossible?
>
> I have a table with two columns x and y, both integers. Imagine they are 
> coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
> dots, and all y's that have more than nY dots. Both conditions must be 
> simultaneous in the following sense:
>
> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
> deletion, y=3 which previously had more than nY dots no longer passes the 
> threshold and thus y=3 must be deleted too. This could cause deletion of some 
> other x, etc. At the end, number of dots on all vertical lines must be more 
> than nX and number of dots on all horizontal lines must be more than nY.
>
> Could this be achieved with SQLite?
>
> Roman
> ___
> 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
___
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] How to Handle BigInt

2018-05-01 Thread Paul Sanderson

Perhaps, but that is only part of the story, and all of that is hidden from
the user and is only relevant in terms of how the number is stored on disk.
You can define a column as int, smallint, largeint, bigint, etc and,
irrespective of which you use, SQLite will save the data to disk
transparently using the smallest on disk format possible for the value you
have saved.

For the record there is another possibility for the values 0 and 1 where
SQLite uses no storage at all, other than the type byte in the serial types
array.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 1 May 2018 at 17:05, Gerry Snyder  wrote:

> From the docs:
>
> *INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
> bytes depending on the magnitude of the value.
>
> So perhaps you should have said " SQLite integers are all up to 64 bit."
>
> Gerry
>
> On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson <
> sandersonforens...@gmail.com
> > wrote:
>
> > SQLite integers are all 64 bit - I don't about postgress, so unless
> > postgress allows integers bigger than 64 bit, and you use them, you
> should
> > be OK with your table definitions above.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-
> > Forensic-Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 1 May 2018 at 16:29, dmp  wrote:
> >
> > > Hello,
> > >
> > > Given a conversion from a database table that contains BigInt, long,
> > > field from PostgreSQL to a SQLite similar table.
> > >
> > > CREATE TABLE postgresqltypes (
> > >   data_type_id serial,
> > >   bigInt_type bigint)
> > >
> > > CREATE TABLE sqlitetypes (
> > >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> > >   int_type INTEGER)
> > >
> > > How to store, for values outside range for Integer,
> > > String or Real?
> > >
> > > danap.
> > >
> > > ___
> > > 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
> >
> ___
> 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] How to Handle BigInt

2018-05-01 Thread Gerry Snyder

From the docs:

*INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.

So perhaps you should have said " SQLite integers are all up to 64 bit."

Gerry

On Tue, May 1, 2018 at 8:56 AM, Paul Sanderson  wrote:

> SQLite integers are all 64 bit - I don't about postgress, so unless
> postgress allows integers bigger than 64 bit, and you use them, you should
> be OK with your table definitions above.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 1 May 2018 at 16:29, dmp  wrote:
>
> > Hello,
> >
> > Given a conversion from a database table that contains BigInt, long,
> > field from PostgreSQL to a SQLite similar table.
> >
> > CREATE TABLE postgresqltypes (
> >   data_type_id serial,
> >   bigInt_type bigint)
> >
> > CREATE TABLE sqlitetypes (
> >   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   int_type INTEGER)
> >
> > How to store, for values outside range for Integer,
> > String or Real?
> >
> > danap.
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Handle BigInt

2018-05-01 Thread Paul Sanderson

SQLite integers are all 64 bit - I don't about postgress, so unless
postgress allows integers bigger than 64 bit, and you use them, you should
be OK with your table definitions above.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 1 May 2018 at 16:29, dmp  wrote:

> Hello,
>
> Given a conversion from a database table that contains BigInt, long,
> field from PostgreSQL to a SQLite similar table.
>
> CREATE TABLE postgresqltypes (
>   data_type_id serial,
>   bigInt_type bigint)
>
> CREATE TABLE sqlitetypes (
>   data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   int_type INTEGER)
>
> How to store, for values outside range for Integer,
> String or Real?
>
> danap.
>
> ___
> 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

[sqlite] How to Handle BigInt

2018-05-01 Thread dmp

Hello,

Given a conversion from a database table that contains BigInt, long,
field from PostgreSQL to a SQLite similar table.

CREATE TABLE postgresqltypes (
  data_type_id serial,
  bigInt_type bigint)

CREATE TABLE sqlitetypes (
  data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
  int_type INTEGER)

How to store, for values outside range for Integer,
String or Real?

danap.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

Having tried to write a generic clean HANDLES ALL CSV reader for speedtables, I 
kind of want to burn Excel with nuclear fire, but that's a side issue. :)

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

2018-05-01 Thread dave

> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Olivier Mascia
> 
> Considering:
> 
> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv');
> 
> Is there any way to teach the csv extension to use ';' 
> instead of ',' as the column delimiter, getting away from the 
> strict RFC4180 definition?
 
While on the topic of Excel generated CSV, I had occaision to do this a
year-and-a-half ago, so let me forewarn you of a couple other things:
*  quoting; excel will sometimes enclose a field in quotes, and sometimes
not, depending on the content therein
*  quoting; excel uses 'double quoting' for 'escaping'; i.e. two quotation
marks in a row are interpreted as a quote char, rather than the arguably
more common backslash style escaping
*  multi-line fields;  your excel document can have cells which contain
multiple lines (i.e. embedded carriage returns)

Anyway, all this led me in my case to write a state machine to read in a
logical 'line' of text, and crack it into fields.

That being said, just now taking a peek at the current implementaion in the
sqlite codebase: 

ext\misc\csv.c:197

Is a commment

/* Read a single field of CSV text.  Compatible with rfc4180 and extended
** with the option of having a separator other than ",".

So maybe that implementation is already enhanced to accommodate those cases,
but the capability is simply not exposed through parameters to the
CREATE VIRTUAL TABLE CSV (...)

So maybe one would just need to modify the csvtabCreate to process some
additional parameters and propagate those settings to the implementation.

-dave


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

Re: [sqlite] probably recursive?

2018-05-01 Thread David Raymond

My initial thought on this would be recursive on delete triggers. You're 
limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really 
big cascades wouldn't fully complete. You can raise the limit, but 
mathematically speaking there's still going to be a limit then.

Will have to think about the recursive CTE route later.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Tuesday, May 01, 2018 8:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] probably recursive?

That depends on what you mean by "Could this be achieved in SQLite?".

There is no query (in any SQL engine) that can depend on a sub-query 
that is itself dependent on the outcome of the main query. This is what 
makes recursion beautiful, but then there is also no CTE (or other query 
in any SQL engine) that can recurse over multiple states of data (i.e. 
query data in one single query to reflect results from both before and 
after a delete in the source table), nor can a CTE be updated or deleted 
from, its data must persist atomically (with some exceptions when using 
non-deterministic functions, like random).

These are not so much "inabilities" of SQL engines, but more due to 
explicit SQL and set-algebra rules.

So this is not possible in a single query.

You can of course "achieve" it using any SQL engine by constructing a 
temporary table, and then repeatedly run a DELETE query for all x values 
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x) 
< nX, rinse, repeat until  both SELECT y HAVING COUNT(x) < nX and SELECT 
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this 
would be painfully slow next to a simple software algorithm that 
prunes/resolves a 2-dimensional array - exponentially worse so for 
larger grid sizes.

On 2018/05/01 2:45 AM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have trouble solving this problem, maybe it is impossible?
>
> I have a table with two columns x and y, both integers. Imagine they are 
> coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
> dots, and all y's that have more than nY dots. Both conditions must be 
> simultaneous in the following sense:
>
> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
> deletion, y=3 which previously had more than nY dots no longer passes the 
> threshold and thus y=3 must be deleted too. This could cause deletion of some 
> other x, etc. At the end, number of dots on all vertical lines must be more 
> than nX and number of dots on all horizontal lines must be more than nY.
>
> Could this be achieved with SQLite?
>
> Roman
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

On 1 May 2018, at 3:01pm, Olivier Mascia  wrote:

> My question was more generic, even though it didn't look that way: the 
> well-known and (maybe too) much-used software tool named Excel tend to 
> encourage people to export "CSV" files which are actually "SCSV" files 
> (semi-colon separated values). So the need to script some data happens 
> regularly.

What kind of insanity would lead someone to invent semi-colon delimited format 
when CSV already existed ?  I bet it was a badly-thought-out solution for 
European numeric formats which use a comma as a decimal point.  Tell whoever 
uses that format to stop.

Seems like the best way to solve this would be to write a converter for Windows 
which converts SCSV to CSV.  Then it could be used by all Excel users instead 
of just SQLite users.  Take a look at

and hack up a solution.  I'd do it myself but I don't use Windows.

Simon.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

2018-05-01 Thread Olivier Mascia

> Le 1 mai 2018 à 14:00, Simon Slavin  a écrit :
> 
>> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv');
>> 
>> Is there any way to teach the csv extension to use ';' instead of ',' as the 
>> column delimiter, getting away from the strict RFC4180 definition?
> 
> The source code for the csv extension is in the archive.  Make your own 
> version called 'ssv'.
> 
> Or, if you can rely on strings in the file not having commas in them, use a 
> simple shell tool to replace all commas with semicolons.

Thanks a lot for the answer Simon, even though I knew of these solutions. :)
My question was more generic, even though it didn't look that way: the 
well-known and (maybe too) much-used software tool named Excel tend to 
encourage people to export "CSV" files which are actually "SCSV" files 
(semi-colon separated values). So the need to script some data happens 
regularly. Very easy for programmers like you and me. A bit harder for people 
just trained enough for some simple sqlite command-line interactions, they 
might not have the right tool / script-languages knowledge to do it easily or 
more importantly to do it right.
Nothing really important.  Just wanted to trigger some thinking.

Again, thank you.
-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] probably recursive?


That depends on what you mean by "Could this be achieved in SQLite?".

There is no query (in any SQL engine) that can depend on a sub-query 
that is itself dependent on the outcome of the main query. This is what 
makes recursion beautiful, but then there is also no CTE (or other query 
in any SQL engine) that can recurse over multiple states of data (i.e. 
query data in one single query to reflect results from both before and 
after a delete in the source table), nor can a CTE be updated or deleted 
from, its data must persist atomically (with some exceptions when using 
non-deterministic functions, like random).


These are not so much "inabilities" of SQL engines, but more due to 
explicit SQL and set-algebra rules.


So this is not possible in a single query.

You can of course "achieve" it using any SQL engine by constructing a 
temporary table, and then repeatedly run a DELETE query for all x values 
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x) 
< nX, rinse, repeat until  both SELECT y HAVING COUNT(x) < nX and SELECT 
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this 
would be painfully slow next to a simple software algorithm that 
prunes/resolves a 2-dimensional array - exponentially worse so for 
larger grid sizes.



On 2018/05/01 2:45 AM, Roman Fleysher wrote:

Dear SQLiters,

I have trouble solving this problem, maybe it is impossible?

I have a table with two columns x and y, both integers. Imagine they are 
coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
dots, and all y's that have more than nY dots. Both conditions must be 
simultaneous in the following sense:

If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
deletion, y=3 which previously had more than nY dots no longer passes the 
threshold and thus y=3 must be deleted too. This could cause deletion of some 
other x, etc. At the end, number of dots on all vertical lines must be more 
than nX and number of dots on all horizontal lines must be more than nY.

Could this be achieved with SQLite?

Roman
___
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] Configuring csv extension to use '; ' as column delimiter instead of ',' ?