Re: [sqlite] UTF8 and NUL

[Gary R. Schmidt]

On 27/01/2018 05:32, Peter Da Silva wrote:

On 1/26/18, 12:31 PM, "sqlite-users on behalf of J Decker" 
 wrote:

ctrl-z was end of file text character in DOS (wrote char 26; not FF)


DOS wasn't an operating system.
  
That will come as a surprise to the people who used DOS/360 and DOS/VSE 
and their various siblings.


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

Re: [sqlite] regression since 3.20.0

[petern]

Confirmed.  3.22 build with -DSQLITE_ENABLE_STAT4 remarkably produces the
other answer:
cdid
1
4
5
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regression since 3.20.0

[Richard Hipp]

On 1/26/18, petern  wrote:
> Kenichi. Nice report.  I pasted your code into my console and do see the
> correct output you expected:
>
> cdid
> 4
> 5

You have to build with -DSQLITE_ENABLE_STAT4, apparently.

>
> sqlite> .version
> SQLite 3.22.0 2018-01-22 18:45:57
> 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
> zlib version 1.2.8
> gcc-4.8.4
>
> Maybe others can try it on their consoles?
> Peter
>
> On Fri, Jan 26, 2018 at 7:18 PM, Kenichi Ishigaki 
> wrote:
>
>> Hi,
>>
>> I've got a regression report from a DBIx::Class perl module maintainer
>> that recent SQLite (3.20.0 and onward) returns a different result from
>> the previous versions.
>>
>> https://rt.cpan.org/Public/Bug/Display.html?id=124227
>>
>> Condition:
>>
>> CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
>> CREATE INDEX cd_idx_genreid ON cd (genreid);
>> INSERT INTO cd  ( cdid, genreid ) VALUES
>>( 1,1 ),
>>( 2, NULL ),
>>( 3, NULL ),
>>( 4, NULL ),
>>( 5, NULL );
>>
>> SQL in question:
>>
>> SELECT cdid
>>   FROM cd me
>> WHERE 2 > (
>>   SELECT COUNT( * )
>> FROM cd rownum__emulation
>>   WHERE
>> (
>>   me.genreid IS NOT NULL
>> AND
>>   rownum__emulation.genreid IS NULL
>> )
>>   OR
>> (
>>   me.genreid IS NOT NULL
>> AND
>>   rownum__emulation.genreid IS NOT NULL
>> AND
>>   rownum__emulation.genreid < me.genreid
>> )
>>   OR
>> (
>>   ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
>> AND rownum__emulation.genreid IS NULL ) )
>> AND
>>   rownum__emulation.cdid > me.cdid
>> )
>> )
>>
>> Expected Result (3.19.3 and prior):
>>
>> 4, 5
>>
>> Current Result (3.20.0 and onward)
>>
>> 1, 4, 5
>>
>> If cd_idx_genreid index is not created, SQLite 3.20.0 and onward also
>> return the expected one.
>>
>> Best regards,
>>
>> Kenichi Ishigaki
>> ___
>> 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
>


-- 
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] regression since 3.20.0

[petern]

Kenichi. Nice report.  I pasted your code into my console and do see the
correct output you expected:

cdid
4
5

sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4

Maybe others can try it on their consoles?
Peter

On Fri, Jan 26, 2018 at 7:18 PM, Kenichi Ishigaki 
wrote:

> Hi,
>
> I've got a regression report from a DBIx::Class perl module maintainer
> that recent SQLite (3.20.0 and onward) returns a different result from
> the previous versions.
>
> https://rt.cpan.org/Public/Bug/Display.html?id=124227
>
> Condition:
>
> CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
> CREATE INDEX cd_idx_genreid ON cd (genreid);
> INSERT INTO cd  ( cdid, genreid ) VALUES
>( 1,1 ),
>( 2, NULL ),
>( 3, NULL ),
>( 4, NULL ),
>( 5, NULL );
>
> SQL in question:
>
> SELECT cdid
>   FROM cd me
> WHERE 2 > (
>   SELECT COUNT( * )
> FROM cd rownum__emulation
>   WHERE
> (
>   me.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid IS NULL
> )
>   OR
> (
>   me.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid IS NOT NULL
> AND
>   rownum__emulation.genreid < me.genreid
> )
>   OR
> (
>   ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
> AND rownum__emulation.genreid IS NULL ) )
> AND
>   rownum__emulation.cdid > me.cdid
> )
> )
>
> Expected Result (3.19.3 and prior):
>
> 4, 5
>
> Current Result (3.20.0 and onward)
>
> 1, 4, 5
>
> If cd_idx_genreid index is not created, SQLite 3.20.0 and onward also
> return the expected one.
>
> Best regards,
>
> Kenichi Ishigaki
> ___
> 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] UTF8 and NUL

[petern]

Maybe you'll still miss my point if I don't explain about this.  I
understand you're talking about UTF8.  However,
you are free to define a user defined function which converts whatever
literal arguments into BLOB space and back again.

eg:  D3KRUTF8() -> TYPED POINTER (BLOB)

where TYPED POINTER is the D3KRUTF8 secret sauce UTF8 encoding that
supports NULs and anything else.

In the D3KRUTF8 BLOB space, any operation whatsoever is possible. Moreover,
queries are free to pass around the D3KRUTF8 BLOB to a nucleus of other
functions which provide a parallel implementation to the familar SUBSTR(),
LIKE(), LENGTH() SQL functions.  What's missing is the ability to overload
the punctuation operators like "||" and "=".

Richard,  why can't UDF's overload '||' (concat()) and '=" (equals())  ?

Peter



On Fri, Jan 26, 2018 at 7:09 PM, petern  wrote:

> BLOB will store UTF8 or any encoding for that matter.   Are you familiar
> with the general concept of operator overloading?
> https://en.wikipedia.org/wiki/Operator_overloading
>
> Arbitrary BLOB types including unterminated strings could be supported as
> first class object through user defined functions and more universal
> operator overloading.  Although they are presently crippled, user named
> BLOB types are already supported.
>
> https://www.sqlite.org/bindptr.html
> https://www.sqlite.org/c3ref/value_subtype.html
>
> For an example of overloading see the LIKE operator:[from
> https://sqlite.org/lang_expr.html]
> -
>  The sqlite3_create_function()
>  interface can be used to
> override the like() function and thereby change the operation of the LIKE
>  operator. When overriding the
> like() function, it may be important to override both the two and three
> argument versions of the like() function. Otherwise, different code may be
> called to implement the LIKE 
> operator depending on whether or not an ESCAPE clause was specified.
> 
>
> BTW, a question for anyone familiar with it.  If one overloads the like()
> function, how can one call the default implementation from the overloaded
> function?
> "like()" isn't an API export, and calling exec() on the same DB handle
> will only reenter the user defined like().
> If calling the default implementation from the overloaded one is
> impossible, I'd say the LIKE overload system has a bug.
>
>
> Peter
>
>
>
>
>
>
>
>
>
> On Fri, Jan 26, 2018 at 5:42 PM, J Decker  wrote:
>
>> char inserts two chars for these... so it's hard to generate a sequence
>> that looks like '1' for length function... inserting a C string that was
>> 'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
>> 'hi\x93\x93\x93\x93\x93\x93' length would be 9.
>>
>> but without bind... this is as much as I can show.
>>
>> insert into test (a) values
>> ('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
>> insert into test (a) values ('hi'||char(0)||'there' );
>> select length(a),a from test;
>> (output to windows terminal)
>> 12|hiÀ––––there
>> 2|hi
>>
>> (in notepad++, )
>> 12|hiÀthere
>> 2|hi
>>
>> (although that is what I would expect.  I wouldn't suggest changing any of
>> that, well ya, ...)
>>
>> 2|hi'||char(0)||'there
>>
>> would look better - but in the select output context there aren't
>> quotes although that does work to preserve data for sqlite backup.
>>
>> On Fri, Jan 26, 2018 at 5:22 PM, petern 
>> wrote:
>>
>> > That's an interesting idea, using BLOBs.  BLOB strings would be more
>> > practical if common SQL scalar operators { || , LIKE, =, <>,...} could
>> be
>> > overloaded with user definable BLOB specific implementations.  At the
>> same
>> > time subtype and pointer type would have to be improved to work in all
>> > cases.  This would be far more general solution than messing up the API
>> > with ubiquitous length argument.
>> >
>> > FYI, here are some current very serious deficiencies with BLOB type
>> system
>> > including detailed test programs:
>> >
>>
>> Blob also is binary, and I'm not dealing with binary, i'm dealing with
>> UTF8
>> Text.  It is a totally different sort of thing than a BLOB would be.
>>
>>
>> >
>> > http://sqlite.1065341.n5.nabble.com/sqlite3-value-
>> > pointer-metadata-is-also-stripped-by-trivial-cross-
>> > join-Defect-tt10.html
>> >
>> > http://sqlite.1065341.n5.nabble.com/Defect-trivial-cross-
>> join-strips-BLOB-
>> > subtype-tt99982.html
>> >
>> > Still waiting for an answer about why BLOB types are lost in a trivial
>> > cross join.  No answer.
>> >
>>
>> That's interesting; I have seen, in the shell, that if the column is blob,
>> it is just not shown.
>>
>>
>> >
>> > Is there a legitimate reason why BLOB type information cannot be passed
>> > through a join?
>> >
>> >
>>
>>
>>
>> > Peter
>> >
>> > On Fri, Jan 26, 2018 at 4:36 PM, Keith 

Re: [sqlite] UTF8 and NUL

[J Decker]

The application is developed in Javascript; blob implies uint8array so it
can be dealth with like - loadImage( blob ); The array is shared with the C
code and can be shared across threads.

the thing I'm storing is a string; and converitng string to uint8array is
kinda silly. since it's just a string.

Another alternaitve would be to encode the NUL as 0xC0 0x80; wihch would
then not look like nul to sqlite.  but if I don't translate all paths from
that, then it's likely to get replaced with 0xFFFD instead.



On Fri, Jan 26, 2018 at 7:09 PM, petern  wrote:

> BLOB will store UTF8 or any encoding for that matter.   Are you familiar
> with the general concept of operator overloading?
> https://en.wikipedia.org/wiki/Operator_overloading
>
> Arbitrary BLOB types including unterminated strings could be supported as
> first class object through user defined functions and more universal
> operator overloading.  Although they are presently crippled, user named
> BLOB types are already supported.
>
> https://www.sqlite.org/bindptr.html
> https://www.sqlite.org/c3ref/value_subtype.html
>
> For an example of overloading see the LIKE operator:[from
> https://sqlite.org/lang_expr.html]
> -
>  The sqlite3_create_function()
>  interface can be used to
> override the like() function and thereby change the operation of the LIKE
>  operator. When overriding the
> like() function, it may be important to override both the two and three
> argument versions of the like() function. Otherwise, different code may be
> called to implement the LIKE 
> operator depending on whether or not an ESCAPE clause was specified.
> 
>
> BTW, a question for anyone familiar with it.  If one overloads the like()
> function, how can one call the default implementation from the overloaded
> function?
> "like()" isn't an API export, and calling exec() on the same DB handle will
> only reenter the user defined like().
> If calling the default implementation from the overloaded one is
> impossible, I'd say the LIKE overload system has a bug.
>
>
> Peter
>
>
>
>
>
>
>
>
>
> On Fri, Jan 26, 2018 at 5:42 PM, J Decker  wrote:
>
> > char inserts two chars for these... so it's hard to generate a sequence
> > that looks like '1' for length function... inserting a C string that was
> > 'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
> > 'hi\x93\x93\x93\x93\x93\x93' length would be 9.
> >
> > but without bind... this is as much as I can show.
> >
> > insert into test (a) values
> > ('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
> > insert into test (a) values ('hi'||char(0)||'there' );
> > select length(a),a from test;
> > (output to windows terminal)
> > 12|hiÀ––––there
> > 2|hi
> >
> > (in notepad++, )
> > 12|hiÀthere
> > 2|hi
> >
> > (although that is what I would expect.  I wouldn't suggest changing any
> of
> > that, well ya, ...)
> >
> > 2|hi'||char(0)||'there
> >
> > would look better - but in the select output context there aren't
> > quotes although that does work to preserve data for sqlite backup.
> >
> > On Fri, Jan 26, 2018 at 5:22 PM, petern 
> > wrote:
> >
> > > That's an interesting idea, using BLOBs.  BLOB strings would be more
> > > practical if common SQL scalar operators { || , LIKE, =, <>,...} could
> be
> > > overloaded with user definable BLOB specific implementations.  At the
> > same
> > > time subtype and pointer type would have to be improved to work in all
> > > cases.  This would be far more general solution than messing up the API
> > > with ubiquitous length argument.
> > >
> > > FYI, here are some current very serious deficiencies with BLOB type
> > system
> > > including detailed test programs:
> > >
> >
> > Blob also is binary, and I'm not dealing with binary, i'm dealing with
> UTF8
> > Text.  It is a totally different sort of thing than a BLOB would be.
> >
> >
> > >
> > > http://sqlite.1065341.n5.nabble.com/sqlite3-value-
> > > pointer-metadata-is-also-stripped-by-trivial-cross-
> > > join-Defect-tt10.html
> > >
> > > http://sqlite.1065341.n5.nabble.com/Defect-trivial-
> > cross-join-strips-BLOB-
> > > subtype-tt99982.html
> > >
> > > Still waiting for an answer about why BLOB types are lost in a trivial
> > > cross join.  No answer.
> > >
> >
> > That's interesting; I have seen, in the shell, that if the column is
> blob,
> > it is just not shown.
> >
> >
> > >
> > > Is there a legitimate reason why BLOB type information cannot be passed
> > > through a join?
> > >
> > >
> >
> >
> >
> > > Peter
> > >
> > > On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf 
> > > wrote:
> > >
> > > >
> > > > I do not understand this at all.
> > > >
> > > > If the definition of a C-String is a "bunch-a-non-zero-byes-
> > > terminated-by-a-zero-byte",
> > > > 

[sqlite] regression since 3.20.0

[Kenichi Ishigaki]

Hi,

I've got a regression report from a DBIx::Class perl module maintainer
that recent SQLite (3.20.0 and onward) returns a different result from
the previous versions.

https://rt.cpan.org/Public/Bug/Display.html?id=124227

Condition:

CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer );
CREATE INDEX cd_idx_genreid ON cd (genreid);
INSERT INTO cd  ( cdid, genreid ) VALUES
   ( 1,1 ),
   ( 2, NULL ),
   ( 3, NULL ),
   ( 4, NULL ),
   ( 5, NULL );

SQL in question:

SELECT cdid
  FROM cd me
WHERE 2 > (
  SELECT COUNT( * )
FROM cd rownum__emulation
  WHERE
(
  me.genreid IS NOT NULL
AND
  rownum__emulation.genreid IS NULL
)
  OR
(
  me.genreid IS NOT NULL
AND
  rownum__emulation.genreid IS NOT NULL
AND
  rownum__emulation.genreid < me.genreid
)
  OR
(
  ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL
AND rownum__emulation.genreid IS NULL ) )
AND
  rownum__emulation.cdid > me.cdid
)
)

Expected Result (3.19.3 and prior):

4, 5

Current Result (3.20.0 and onward)

1, 4, 5

If cd_idx_genreid index is not created, SQLite 3.20.0 and onward also
return the expected one.

Best regards,

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

You are right,  Igor. Clear case of XY problem. I will remove trigger.


Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Igor Tandetnik 
Date: 1/26/18 9:03 PM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:20 PM, Roman Fleysher wrote:
> I think I effectively did as you suggested using triggers. I insert NULL into 
> the ID column to create a row. This triggers the trigger to run update on the 
> table to populate the columns based on the just created ID. Is this what you 
> suggested?

Roughly, though running a single statement at the end seems simpler, and likely 
goes faster, than setting up a trigger.
--
Igor Tandetnik

___
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] UTF8 and NUL

[petern]

BLOB will store UTF8 or any encoding for that matter.   Are you familiar
with the general concept of operator overloading?
https://en.wikipedia.org/wiki/Operator_overloading

Arbitrary BLOB types including unterminated strings could be supported as
first class object through user defined functions and more universal
operator overloading.  Although they are presently crippled, user named
BLOB types are already supported.

https://www.sqlite.org/bindptr.html
https://www.sqlite.org/c3ref/value_subtype.html

For an example of overloading see the LIKE operator:[from
https://sqlite.org/lang_expr.html]
-
 The sqlite3_create_function()
 interface can be used to
override the like() function and thereby change the operation of the LIKE
 operator. When overriding the
like() function, it may be important to override both the two and three
argument versions of the like() function. Otherwise, different code may be
called to implement the LIKE 
operator depending on whether or not an ESCAPE clause was specified.


BTW, a question for anyone familiar with it.  If one overloads the like()
function, how can one call the default implementation from the overloaded
function?
"like()" isn't an API export, and calling exec() on the same DB handle will
only reenter the user defined like().
If calling the default implementation from the overloaded one is
impossible, I'd say the LIKE overload system has a bug.


Peter









On Fri, Jan 26, 2018 at 5:42 PM, J Decker  wrote:

> char inserts two chars for these... so it's hard to generate a sequence
> that looks like '1' for length function... inserting a C string that was
> 'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
> 'hi\x93\x93\x93\x93\x93\x93' length would be 9.
>
> but without bind... this is as much as I can show.
>
> insert into test (a) values
> ('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
> insert into test (a) values ('hi'||char(0)||'there' );
> select length(a),a from test;
> (output to windows terminal)
> 12|hiÀ––––there
> 2|hi
>
> (in notepad++, )
> 12|hiÀthere
> 2|hi
>
> (although that is what I would expect.  I wouldn't suggest changing any of
> that, well ya, ...)
>
> 2|hi'||char(0)||'there
>
> would look better - but in the select output context there aren't
> quotes although that does work to preserve data for sqlite backup.
>
> On Fri, Jan 26, 2018 at 5:22 PM, petern 
> wrote:
>
> > That's an interesting idea, using BLOBs.  BLOB strings would be more
> > practical if common SQL scalar operators { || , LIKE, =, <>,...} could be
> > overloaded with user definable BLOB specific implementations.  At the
> same
> > time subtype and pointer type would have to be improved to work in all
> > cases.  This would be far more general solution than messing up the API
> > with ubiquitous length argument.
> >
> > FYI, here are some current very serious deficiencies with BLOB type
> system
> > including detailed test programs:
> >
>
> Blob also is binary, and I'm not dealing with binary, i'm dealing with UTF8
> Text.  It is a totally different sort of thing than a BLOB would be.
>
>
> >
> > http://sqlite.1065341.n5.nabble.com/sqlite3-value-
> > pointer-metadata-is-also-stripped-by-trivial-cross-
> > join-Defect-tt10.html
> >
> > http://sqlite.1065341.n5.nabble.com/Defect-trivial-
> cross-join-strips-BLOB-
> > subtype-tt99982.html
> >
> > Still waiting for an answer about why BLOB types are lost in a trivial
> > cross join.  No answer.
> >
>
> That's interesting; I have seen, in the shell, that if the column is blob,
> it is just not shown.
>
>
> >
> > Is there a legitimate reason why BLOB type information cannot be passed
> > through a join?
> >
> >
>
>
>
> > Peter
> >
> > On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf 
> > wrote:
> >
> > >
> > > I do not understand this at all.
> > >
> > > If the definition of a C-String is a "bunch-a-non-zero-byes-
> > terminated-by-a-zero-byte",
> > > then how is it possible to have a zero/null byte "embedded" within a
> > > C-Style String?
> > >
> > > Similarly, if a C-Style-Wide-String is defined as a
> > > "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> > > possible to have a zero/null word "embedded" within a
> > C-Style-Wide-String?
> > >
> > > Given that SQLite3 is written in C and uses C-Strings or
> > > C-Style-Wide-Strings, then you cannot have zero/null bytes embedded in
> > > those strings.
> > >
> > > You may of course argue that perhaps SQLite3 should use something other
> > > than C-Style-Strings, however, this is not what seems to be proposed.
> It
> > > seems to be proposing the use of some magical C-Style-String that is
> not
> > > actually a C-Style-String, without explicitly stating this.
> > >
> > > SQLite3 does handle non-C-Ctyle-Strings.  

Re: [sqlite] primary key in another column

[Igor Tandetnik]

On 1/26/2018 6:20 PM, Roman Fleysher wrote:

I think I effectively did as you suggested using triggers. I insert NULL into 
the ID column to create a row. This triggers the trigger to run update on the 
table to populate the columns based on the just created ID. Is this what you 
suggested?


Roughly, though running a single statement at the end seems simpler, and likely 
goes faster, than setting up a trigger.
--
Igor Tandetnik

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

Re: [sqlite] UTF8 and NUL

[J Decker]

char inserts two chars for these... so it's hard to generate a sequence
that looks like '1' for length function... inserting a C string that was
'hi\xc2\x93\x93\x93\x93\x93\x93' length would be 3.
'hi\x93\x93\x93\x93\x93\x93' length would be 9.

but without bind... this is as much as I can show.

insert into test (a) values
('hi'||char(192)||char(150)||char(150)||char(150)||char(150)||'there' );
insert into test (a) values ('hi'||char(0)||'there' );
select length(a),a from test;
(output to windows terminal)
12|hiÀ––––there
2|hi

(in notepad++, )
12|hiÀthere
2|hi

(although that is what I would expect.  I wouldn't suggest changing any of
that, well ya, ...)

2|hi'||char(0)||'there

would look better - but in the select output context there aren't
quotes although that does work to preserve data for sqlite backup.

On Fri, Jan 26, 2018 at 5:22 PM, petern  wrote:

> That's an interesting idea, using BLOBs.  BLOB strings would be more
> practical if common SQL scalar operators { || , LIKE, =, <>,...} could be
> overloaded with user definable BLOB specific implementations.  At the same
> time subtype and pointer type would have to be improved to work in all
> cases.  This would be far more general solution than messing up the API
> with ubiquitous length argument.
>
> FYI, here are some current very serious deficiencies with BLOB type system
> including detailed test programs:
>

Blob also is binary, and I'm not dealing with binary, i'm dealing with UTF8
Text.  It is a totally different sort of thing than a BLOB would be.


>
> http://sqlite.1065341.n5.nabble.com/sqlite3-value-
> pointer-metadata-is-also-stripped-by-trivial-cross-
> join-Defect-tt10.html
>
> http://sqlite.1065341.n5.nabble.com/Defect-trivial-cross-join-strips-BLOB-
> subtype-tt99982.html
>
> Still waiting for an answer about why BLOB types are lost in a trivial
> cross join.  No answer.
>

That's interesting; I have seen, in the shell, that if the column is blob,
it is just not shown.


>
> Is there a legitimate reason why BLOB type information cannot be passed
> through a join?
>
>



> Peter
>
> On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf 
> wrote:
>
> >
> > I do not understand this at all.
> >
> > If the definition of a C-String is a "bunch-a-non-zero-byes-
> terminated-by-a-zero-byte",
> > then how is it possible to have a zero/null byte "embedded" within a
> > C-Style String?
> >
> > Similarly, if a C-Style-Wide-String is defined as a
> > "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> > possible to have a zero/null word "embedded" within a
> C-Style-Wide-String?
> >
> > Given that SQLite3 is written in C and uses C-Strings or
> > C-Style-Wide-Strings, then you cannot have zero/null bytes embedded in
> > those strings.
> >
> > You may of course argue that perhaps SQLite3 should use something other
> > than C-Style-Strings, however, this is not what seems to be proposed.  It
> > seems to be proposing the use of some magical C-Style-String that is not
> > actually a C-Style-String, without explicitly stating this.
> >
> > SQLite3 does handle non-C-Ctyle-Strings.  They are called "blobs".
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> > >-Original Message-
> > >From: sqlite-users [mailto:sqlite-users-
> > >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> > >Sent: Friday, 26 January, 2018 17:18
> > >To: SQLite mailing list
> > >Subject: Re: [sqlite] UTF8 and NUL
> > >
> > >On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
> > >peter.dasi...@flightaware.com> wrote:
> > >
> > >> On 2018-01-26, at 17:05, J Decker  wrote:
> > >> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> > >> > peter.dasi...@flightaware.com> wrote:
> > >> >> Sqlite uses NUL as the string terminator internally, the
> > >published API
> > >> >> specifies has stuff like this all over the place:
> > >>
> > >> >>> In those routines that have a fourth argument, its value is the
> > >number
> > >> of bytes in the parameter. To be clear: the value is the number of
> > >bytes in
> > >> the value, not the number of characters. If the fourth parameter to
> > >> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the
> > >length
> > >> of the string is the number of bytes UP TO THE FIRST ZERO
> > >TERMINATOR.
> > >>
> > >> > You stressed the wrong part there - *IS NEGATIVE*
> > >>
> > >> Why? Passing -1 as the length is a common way to tell sqlite3 to
> > >calculate
> > >> the length itself. It's a documented and widely used part of the
> > >API.
> > >
> > >
> > >Exactly, so on neither side, input or output is there a problem
> > >storing a
> > >length of valid characters.
> > >The deficiency is 1) the command line tool for diagnostics
> > >2) always scanning for a nul in prepare() unless the length is before
> > >that.  It's simple to add an option 

Re: [sqlite] UTF8 and NUL

[petern]

That's an interesting idea, using BLOBs.  BLOB strings would be more
practical if common SQL scalar operators { || , LIKE, =, <>,...} could be
overloaded with user definable BLOB specific implementations.  At the same
time subtype and pointer type would have to be improved to work in all
cases.  This would be far more general solution than messing up the API
with ubiquitous length argument.

FYI, here are some current very serious deficiencies with BLOB type system
including detailed test programs:

http://sqlite.1065341.n5.nabble.com/sqlite3-value-pointer-metadata-is-also-stripped-by-trivial-cross-join-Defect-tt10.html

http://sqlite.1065341.n5.nabble.com/Defect-trivial-cross-join-strips-BLOB-subtype-tt99982.html

Still waiting for an answer about why BLOB types are lost in a trivial
cross join.  No answer.

Is there a legitimate reason why BLOB type information cannot be passed
through a join?

Peter

On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf  wrote:

>
> I do not understand this at all.
>
> If the definition of a C-String is a 
> "bunch-a-non-zero-byes-terminated-by-a-zero-byte",
> then how is it possible to have a zero/null byte "embedded" within a
> C-Style String?
>
> Similarly, if a C-Style-Wide-String is defined as a
> "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> possible to have a zero/null word "embedded" within a C-Style-Wide-String?
>
> Given that SQLite3 is written in C and uses C-Strings or
> C-Style-Wide-Strings, then you cannot have zero/null bytes embedded in
> those strings.
>
> You may of course argue that perhaps SQLite3 should use something other
> than C-Style-Strings, however, this is not what seems to be proposed.  It
> seems to be proposing the use of some magical C-Style-String that is not
> actually a C-Style-String, without explicitly stating this.
>
> SQLite3 does handle non-C-Ctyle-Strings.  They are called "blobs".
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Friday, 26 January, 2018 17:18
> >To: SQLite mailing list
> >Subject: Re: [sqlite] UTF8 and NUL
> >
> >On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
> >peter.dasi...@flightaware.com> wrote:
> >
> >> On 2018-01-26, at 17:05, J Decker  wrote:
> >> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> >> > peter.dasi...@flightaware.com> wrote:
> >> >> Sqlite uses NUL as the string terminator internally, the
> >published API
> >> >> specifies has stuff like this all over the place:
> >>
> >> >>> In those routines that have a fourth argument, its value is the
> >number
> >> of bytes in the parameter. To be clear: the value is the number of
> >bytes in
> >> the value, not the number of characters. If the fourth parameter to
> >> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the
> >length
> >> of the string is the number of bytes UP TO THE FIRST ZERO
> >TERMINATOR.
> >>
> >> > You stressed the wrong part there - *IS NEGATIVE*
> >>
> >> Why? Passing -1 as the length is a common way to tell sqlite3 to
> >calculate
> >> the length itself. It's a documented and widely used part of the
> >API.
> >
> >
> >Exactly, so on neither side, input or output is there a problem
> >storing a
> >length of valid characters.
> >The deficiency is 1) the command line tool for diagnostics
> >2) always scanning for a nul in prepare() unless the length is before
> >that.  It's simple to add an option that could change that behavior;
> >or
> >move the string measuring up to prepare[_v2,_v3,_v4] and even add a
> >V5 that
> >just passes the length passed without a scan.
> >
> >The input is read by a tokenizer that returns in-buffer references to
> >the
> >next SQL token by length.
> >Some tokens can be quoted, and those end up being a copy of the
> >original;
> >but the length of the SQL statement should already be known, so it
> >doesn't
> >need to scan for 0.
> >
> >Once tokenized it's converted into expressions; those expressions
> >(have
> >previously) stored only the char*.  It's not a lot of places to
> >change to
> >include storing the length; which is often known unless the mprintf
> >internals are used; then any token passed through that does not pass
> >%s.
> >So %s cannot be used for UTF8 strings; but rather the literal string
> >fwrite( buf, 1, stringlen,  >treatment as the file was opened with (O_BINARY or not, "b" or "t"
> >specifiers for fopen, or stderr ).
> >
> >fprintf( out, "%s", (string) );
> >is exactly the same as
> >fwrite( out, 1, strlen( string ), string );
> >
> >(Can anyone dispute that?  I doubt that's specified)
> >
> >Other than, the fwrite will include outputing the NUL character and
> >trust
> >the length given to it. \n will still get promoted to \r\n depending
> 

Re: [sqlite] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 4:36 PM, Keith Medcalf  wrote:

>
> I do not understand this at all.
>
> If the definition of a C-String is a 
> "bunch-a-non-zero-byes-terminated-by-a-zero-byte",
> then how is it possible to have a zero/null byte "embedded" within a
> C-Style String?
>
this is about SQL not C.  C is easy \0.  Lots of \\, support. \x00

char thing[] = "Hello\0world";
#define strlen_thing(a)  ( sizeof(a)/(sizeof(a[0])-1 ) )



>
> Similarly, if a C-Style-Wide-String is defined as a
> "bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it
> possible to have a zero/null word "embedded" within a C-Style-Wide-String?
>
> Given that SQLite3 is written in C and uses C-Strings or
> C-Style-Wide-Strings, then you cannot have zero/null bytes embedded in
> those strings.
>
> It certainly can.  It's isolated from the language by working on tokens
and expression pieces for which it has(or can have) the length.



> You may of course argue that perhaps SQLite3 should use something other
> than C-Style-Strings, however, this is not what seems to be proposed.  It
> seems to be proposing the use of some magical C-Style-String that is not
> actually a C-Style-String, without explicitly stating this.
>
>
They don't use c style strings though, the strings passed are SQL, which
only have a single escape for the type of quote it starts with.  If it used
'c style strings' it would behave like MySQL/TSQL but it's not; it's PSQL.

"Hello""World"
'Hello''world'


> SQLite3 does handle non-C-Ctyle-Strings.  They are called "blobs".
>
> It does, and has for a decade or more as char.



> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of J Decker
> >Sent: Friday, 26 January, 2018 17:18
> >To: SQLite mailing list
> >Subject: Re: [sqlite] UTF8 and NUL
> >
> >On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
> >peter.dasi...@flightaware.com> wrote:
> >
> >> On 2018-01-26, at 17:05, J Decker  wrote:
> >> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> >> > peter.dasi...@flightaware.com> wrote:
> >> >> Sqlite uses NUL as the string terminator internally, the
> >published API
> >> >> specifies has stuff like this all over the place:
> >>
> >> >>> In those routines that have a fourth argument, its value is the
> >number
> >> of bytes in the parameter. To be clear: the value is the number of
> >bytes in
> >> the value, not the number of characters. If the fourth parameter to
> >> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the
> >length
> >> of the string is the number of bytes UP TO THE FIRST ZERO
> >TERMINATOR.
> >>
> >> > You stressed the wrong part there - *IS NEGATIVE*
> >>
> >> Why? Passing -1 as the length is a common way to tell sqlite3 to
> >calculate
> >> the length itself. It's a documented and widely used part of the
> >API.
> >
> >
> >Exactly, so on neither side, input or output is there a problem
> >storing a
> >length of valid characters.
> >The deficiency is 1) the command line tool for diagnostics
> >2) always scanning for a nul in prepare() unless the length is before
> >that.  It's simple to add an option that could change that behavior;
> >or
> >move the string measuring up to prepare[_v2,_v3,_v4] and even add a
> >V5 that
> >just passes the length passed without a scan.
> >
> >The input is read by a tokenizer that returns in-buffer references to
> >the
> >next SQL token by length.
> >Some tokens can be quoted, and those end up being a copy of the
> >original;
> >but the length of the SQL statement should already be known, so it
> >doesn't
> >need to scan for 0.
> >
> >Once tokenized it's converted into expressions; those expressions
> >(have
> >previously) stored only the char*.  It's not a lot of places to
> >change to
> >include storing the length; which is often known unless the mprintf
> >internals are used; then any token passed through that does not pass
> >%s.
> >So %s cannot be used for UTF8 strings; but rather the literal string
> >fwrite( buf, 1, stringlen,  >treatment as the file was opened with (O_BINARY or not, "b" or "t"
> >specifiers for fopen, or stderr ).
> >
> >fprintf( out, "%s", (string) );
> >is exactly the same as
> >fwrite( out, 1, strlen( string ), string );
> >
> >(Can anyone dispute that?  I doubt that's specified)
> >
> >Other than, the fwrite will include outputing the NUL character and
> >trust
> >the length given to it. \n will still get promoted to \r\n depending
> >on
> >platform and C library personality.
> >
> >
> >
> >
> >
> >> Therefore:
> >>
> >
> >
> >>
> >> >> It would be a huge push-up to change this, it would break
> >everything,
> >> >> including extensions. I don't think it would be possible until
> >something
> >> >> like sqlite4.
> >>
> >
> >maybe I don't 

Re: [sqlite] UTF8 and NUL

[Keith Medcalf]

I do not understand this at all.  

If the definition of a C-String is a 
"bunch-a-non-zero-byes-terminated-by-a-zero-byte", then how is it possible to 
have a zero/null byte "embedded" within a C-Style String?

Similarly, if a C-Style-Wide-String is defined as a 
"bunch-a-non-zero-words-terminated-by-a-zero-word", then how is it possible to 
have a zero/null word "embedded" within a C-Style-Wide-String?

Given that SQLite3 is written in C and uses C-Strings or C-Style-Wide-Strings, 
then you cannot have zero/null bytes embedded in those strings.

You may of course argue that perhaps SQLite3 should use something other than 
C-Style-Strings, however, this is not what seems to be proposed.  It seems to 
be proposing the use of some magical C-Style-String that is not actually a 
C-Style-String, without explicitly stating this.

SQLite3 does handle non-C-Ctyle-Strings.  They are called "blobs".

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


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of J Decker
>Sent: Friday, 26 January, 2018 17:18
>To: SQLite mailing list
>Subject: Re: [sqlite] UTF8 and NUL
>
>On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
>peter.dasi...@flightaware.com> wrote:
>
>> On 2018-01-26, at 17:05, J Decker  wrote:
>> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
>> > peter.dasi...@flightaware.com> wrote:
>> >> Sqlite uses NUL as the string terminator internally, the
>published API
>> >> specifies has stuff like this all over the place:
>>
>> >>> In those routines that have a fourth argument, its value is the
>number
>> of bytes in the parameter. To be clear: the value is the number of
>bytes in
>> the value, not the number of characters. If the fourth parameter to
>> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the
>length
>> of the string is the number of bytes UP TO THE FIRST ZERO
>TERMINATOR.
>>
>> > You stressed the wrong part there - *IS NEGATIVE*
>>
>> Why? Passing -1 as the length is a common way to tell sqlite3 to
>calculate
>> the length itself. It's a documented and widely used part of the
>API.
>
>
>Exactly, so on neither side, input or output is there a problem
>storing a
>length of valid characters.
>The deficiency is 1) the command line tool for diagnostics
>2) always scanning for a nul in prepare() unless the length is before
>that.  It's simple to add an option that could change that behavior;
>or
>move the string measuring up to prepare[_v2,_v3,_v4] and even add a
>V5 that
>just passes the length passed without a scan.
>
>The input is read by a tokenizer that returns in-buffer references to
>the
>next SQL token by length.
>Some tokens can be quoted, and those end up being a copy of the
>original;
>but the length of the SQL statement should already be known, so it
>doesn't
>need to scan for 0.
>
>Once tokenized it's converted into expressions; those expressions
>(have
>previously) stored only the char*.  It's not a lot of places to
>change to
>include storing the length; which is often known unless the mprintf
>internals are used; then any token passed through that does not pass
>%s.
>So %s cannot be used for UTF8 strings; but rather the literal string
>fwrite( buf, 1, stringlen, treatment as the file was opened with (O_BINARY or not, "b" or "t"
>specifiers for fopen, or stderr ).
>
>fprintf( out, "%s", (string) );
>is exactly the same as
>fwrite( out, 1, strlen( string ), string );
>
>(Can anyone dispute that?  I doubt that's specified)
>
>Other than, the fwrite will include outputing the NUL character and
>trust
>the length given to it. \n will still get promoted to \r\n depending
>on
>platform and C library personality.
>
>
>
>
>
>> Therefore:
>>
>
>
>>
>> >> It would be a huge push-up to change this, it would break
>everything,
>> >> including extensions. I don't think it would be possible until
>something
>> >> like sqlite4.
>>
>
>maybe I don't understand what you're saying 'it' is.
>
>
>> ___
>> 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] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 3:56 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 2018-01-26, at 17:05, J Decker  wrote:
> > On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> > peter.dasi...@flightaware.com> wrote:
> >> Sqlite uses NUL as the string terminator internally, the published API
> >> specifies has stuff like this all over the place:
>
> >>> In those routines that have a fourth argument, its value is the number
> of bytes in the parameter. To be clear: the value is the number of bytes in
> the value, not the number of characters. If the fourth parameter to
> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length
> of the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.
>
> > You stressed the wrong part there - *IS NEGATIVE*
>
> Why? Passing -1 as the length is a common way to tell sqlite3 to calculate
> the length itself. It's a documented and widely used part of the API.


Exactly, so on neither side, input or output is there a problem storing a
length of valid characters.
The deficiency is 1) the command line tool for diagnostics
2) always scanning for a nul in prepare() unless the length is before
that.  It's simple to add an option that could change that behavior; or
move the string measuring up to prepare[_v2,_v3,_v4] and even add a V5 that
just passes the length passed without a scan.

The input is read by a tokenizer that returns in-buffer references to the
next SQL token by length.
Some tokens can be quoted, and those end up being a copy of the original;
but the length of the SQL statement should already be known, so it doesn't
need to scan for 0.

Once tokenized it's converted into expressions; those expressions (have
previously) stored only the char*.  It's not a lot of places to change to
include storing the length; which is often known unless the mprintf
internals are used; then any token passed through that does not pass %s.
So %s cannot be used for UTF8 strings; but rather the literal string
fwrite( buf, 1, stringlen,  Therefore:
>


>
> >> It would be a huge push-up to change this, it would break everything,
> >> including extensions. I don't think it would be possible until something
> >> like sqlite4.
>

maybe I don't understand what you're saying 'it' is.


> ___
> 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] Documentation Bug?

[Christian Müller]

Hello list,


I believe there is a documentation bug in the docs describing the new zipfile 
feature:

https://sqlite.org/zipfile.html

In section 3.2.1. Adding Entries to a Zip Archive
it reads, quote

For example, to add a symbolic link from "link.txt" to "m.txt":

INSERT INTO temp.zip(name, mode, data) VALUES('link.txt', 'lrwx-rw-rw', 
'abcdefghi');


Shouldn't it read m.txt instead of abcdefghi !?


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

Re: [sqlite] primary key in another column

[Roman Fleysher]

yes, I can use a view. 

forEachRow also records what failed. Updating a view requires a trigger, but I 
can compose one with the view.

Thank you for suggestion!

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:50 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

Couldn't you have it access a view which adds the columns by calculation
rather than the raw table? (and if you have some tables that don't need
such a view, create a simple pass through view).

On 1/26/18 6:30 PM, Roman Fleysher wrote:
> No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
> applied to any table. If I modify SELECT inside it to fit specific purpose, 
> forEachRow will use universality.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Richard Damon [rich...@damon-family.org]
> Sent: Friday, January 26, 2018 6:26 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] primary key in another column
>
> One question I have, couldn't you just omit the fileName column from the
> able, and compute it in the select query that is getting the data?
>
> On 1/26/18 6:03 PM, Roman Fleysher wrote:
>> My implementation of  "for Each row" requires all columns to be populated. 
>> It is a dumb thing:
>>
>> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>>
>> The files are images. Example:
>>
>> forEachRow  addImages outputColumn column1 column2
>>
>> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
>> the command given to it with its arguments. Image processing is then a 
>> sequence of these "forEach" commands.
>>
>>
>> Roman
>>
>> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>>> I will use this table as a manager. There will be multiple columns holding 
>>> various file names. The names can be random, but I want humans to be able 
>>> to easily inspect. After table is filled, an operation "for each row"  will 
>>> get files in some columns and produce files in other columns. This is done 
>>> outside of SQLite. "For each row" will process several rows in parallel 
>>> because they are independent. Some operations might fail and will be 
>>> recored in the proper columns. After all the work is done, the manager 
>>> table is discarded.
>> I'm still not sure I understand, but: while you are building out this 
>> manager table, can't you leave fileName column blank, and then right before 
>> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
>> --
>> Igor Tandetnik
> --
> Richard Damon
>
> ___
> 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


--
Richard Damon

___
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] UTF8 and NUL

[Peter Da Silva]

On 2018-01-26, at 17:05, J Decker  wrote:
> On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
> peter.dasi...@flightaware.com> wrote:
>> Sqlite uses NUL as the string terminator internally, the published API
>> specifies has stuff like this all over the place:

>>> In those routines that have a fourth argument, its value is the number of 
>>> bytes in the parameter. To be clear: the value is the number of bytes in 
>>> the value, not the number of characters. If the fourth parameter to 
>>> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length 
>>> of the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.

> You stressed the wrong part there - *IS NEGATIVE*

Why? Passing -1 as the length is a common way to tell sqlite3 to calculate the 
length itself. It's a documented and widely used part of the API. Therefore:

>> It would be a huge push-up to change this, it would break everything,
>> including extensions. I don't think it would be possible until something
>> like sqlite4.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key in another column

[Richard Damon]

Couldn't you have it access a view which adds the columns by calculation 
rather than the raw table? (and if you have some tables that don't need 
such a view, create a simple pass through view).


On 1/26/18 6:30 PM, Roman Fleysher wrote:

No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
applied to any table. If I modify SELECT inside it to fit specific purpose, 
forEachRow will use universality.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

One question I have, couldn't you just omit the fileName column from the
able, and compute it in the select query that is getting the data?

On 1/26/18 6:03 PM, Roman Fleysher wrote:

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:

forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns

The files are images. Example:

forEachRow  addImages outputColumn column1 column2

ForEachRow will loop over the rows (in parallel batches if it can) and apply the command 
given to it with its arguments. Image processing is then a sequence of these 
"forEach" commands.


Roman

On 1/26/2018 5:47 PM, Roman Fleysher wrote:

I will use this table as a manager. There will be multiple columns holding various file names. The 
names can be random, but I want humans to be able to easily inspect. After table is filled, an 
operation "for each row"  will get files in some columns and produce files in other 
columns. This is done outside of SQLite. "For each row" will process several rows in 
parallel because they are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.

I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik

--
Richard Damon

___
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



--
Richard Damon

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

Re: [sqlite] Queries using json_each in a JOIN are not working

[Richard Hipp]

Proper fix is now on trunk and will appear in the next release.

On 1/26/18, Jens Alfke  wrote:
>
>
>> On Jan 26, 2018, at 1:36 PM, Richard Hipp  wrote:
>>
>> For now, please use CROSS JOIN instead of INNER JOIN to force the
>> correct join order.
>
> You're right, that fixed the query. Thanks!
>
> I must admit, I don't understand the significance of join order here. It
> seems to me that the results would be the same whether "itemDS" or
> "categoryDS" is the outer loop; it's only performance that might change.
> Could someone explain?
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] primary key in another column

[Roman Fleysher]

No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
applied to any table. If I modify SELECT inside it to fit specific purpose, 
forEachRow will use universality.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

One question I have, couldn't you just omit the fileName column from the
able, and compute it in the select query that is getting the data?

On 1/26/18 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:
>
> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>
> The files are images. Example:
>
> forEachRow  addImages outputColumn column1 column2
>
> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
> the command given to it with its arguments. Image processing is then a 
> sequence of these "forEach" commands.
>
>
> Roman
>
> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>> I will use this table as a manager. There will be multiple columns holding 
>> various file names. The names can be random, but I want humans to be able to 
>> easily inspect. After table is filled, an operation "for each row"  will get 
>> files in some columns and produce files in other columns. This is done 
>> outside of SQLite. "For each row" will process several rows in parallel 
>> because they are independent. Some operations might fail and will be recored 
>> in the proper columns. After all the work is done, the manager table is 
>> discarded.
> I'm still not sure I understand, but: while you are building out this manager 
> table, can't you leave fileName column blank, and then right before 
> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
> --
> Igor Tandetnik

--
Richard Damon

___
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] primary key in another column

[Richard Damon]

One question I have, couldn't you just omit the fileName column from the 
able, and compute it in the select query that is getting the data?


On 1/26/18 6:03 PM, Roman Fleysher wrote:

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:

forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns

The files are images. Example:

forEachRow  addImages outputColumn column1 column2

ForEachRow will loop over the rows (in parallel batches if it can) and apply the command 
given to it with its arguments. Image processing is then a sequence of these 
"forEach" commands.


Roman

On 1/26/2018 5:47 PM, Roman Fleysher wrote:

I will use this table as a manager. There will be multiple columns holding various file names. The 
names can be random, but I want humans to be able to easily inspect. After table is filled, an 
operation "for each row"  will get files in some columns and produce files in other 
columns. This is done outside of SQLite. "For each row" will process several rows in 
parallel because they are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.

I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik


--
Richard Damon

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

I think I effectively did as you suggested using triggers. I insert NULL into 
the ID column to create a row. This triggers the trigger to run update on the 
table to populate the columns based on the just created ID. Is this what you 
suggested?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 6:10 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:

You said: After table is filled, an operation "for each row"  will... I suggest 
running this UPDATE statement at the end of "table is filled", before "an 
operation will..." part.
--
Igor Tandetnik

___
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] primary key in another column

[Igor Tandetnik]

On 1/26/2018 6:03 PM, Roman Fleysher wrote:

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:


You said: After table is filled, an operation "for each row"  will... I suggest running this UPDATE 
statement at the end of "table is filled", before "an operation will..." part.
--
Igor Tandetnik

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

Re: [sqlite] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 1:21 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Sqlite uses NUL as the string terminator internally, the published API
> specifies has stuff like this all over the place:
>
> > In those routines that have a fourth argument, its value is the number
> of bytes in the parameter. To be clear: the value is the number of bytes in
> the value, not the number of characters. If the fourth parameter to
> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length
> of the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.
>
>
You stressed the wrong part there - *IS NEGATIVE*


> It would be a huge push-up to change this, it would break everything,
> including extensions. I don't think it would be possible until something
> like sqlite4.
>
> ___
> 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] primary key in another column

[Roman Fleysher]

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:

forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns

The files are images. Example:

forEachRow  addImages outputColumn column1 column2 

ForEachRow will loop over the rows (in parallel batches if it can) and apply 
the command given to it with its arguments. Image processing is then a sequence 
of these "forEach" commands.


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:56 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 5:47 PM, Roman Fleysher wrote:
> I will use this table as a manager. There will be multiple columns holding 
> various file names. The names can be random, but I want humans to be able to 
> easily inspect. After table is filled, an operation "for each row"  will get 
> files in some columns and produce files in other columns. This is done 
> outside of SQLite. "For each row" will process several rows in parallel 
> because they are independent. Some operations might fail and will be recored 
> in the proper columns. After all the work is done, the manager table is 
> discarded.

I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik


___
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] primary key in another column

[Igor Tandetnik]

On 1/26/2018 5:47 PM, Roman Fleysher wrote:

I will use this table as a manager. There will be multiple columns holding various file names. The 
names can be random, but I want humans to be able to easily inspect. After table is filled, an 
operation "for each row"  will get files in some columns and produce files in other 
columns. This is done outside of SQLite. "For each row" will process several rows in 
parallel because they are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.


I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik


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

Re: [sqlite] primary key in another column

[Roman Fleysher]

Igor, you are absolutely right.

But

I will use this table as a manager. There will be multiple columns holding 
various file names. The names can be random, but I want humans to be able to 
easily inspect. After table is filled, an operation "for each row"  will get 
files in some columns and produce files in other columns. This is done outside 
of SQLite. "For each row" will process several rows in parallel because they 
are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.

"For each row" is equivalent to SELECT, but it operates on the files 
themselves. This can be implemented within SQLIte by loading extension. I 
investigated this route (and even asked questions on this list) and eventually 
concluded that it is better to do outside because of the way parallel execution 
is done (sometimes sent to a compute cluster grid engine for queueing.) 

This makes no sense from the database point of view: No reason to hold 
redundant data with such a simple algorithm to generate it.


Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:33 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 4:43 PM, Roman Fleysher wrote:
> I would like to use primary key as a way to create unique column entry:
>
> CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)
>
> such that file name is always prefix followed by the ID for the content to be:
>
> ID  fileName
>
> 1   prefix_1
> 2   prefix_2

Why do you want to store redundant data? What's the actual problem this is 
supposed to help you solve? As stated, this looks like an XY problem ( 
http://xyproblem.info/ )

--
Igor Tandetnik

___
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] UTF8 and NUL

[Simon Slavin]

On 26 Jan 2018, at 9:04pm, J Decker  wrote:

> I bet windows command line tools still use it because copy has /B and /A on
> windows 10.

Windows is indeed a problem.  I don't know enough about it to know whether the 
above statement outlines the problem but Windows in general is terrifically 
difficult to troubleshoot characterset problems in.  There are too many 
combinations of user's preferred code-page, application's code-page, keyboard 
set and thereby the characters the keyboard generates, and control panel 
settings for language.

By all means discuss this problem, do testing in the SQLite command-line tool, 
do testing using text files you can hexdump, and other things.  But please 
don't say "Windows acts like this." because what you really mean is "My version 
of Windows acts like this with this App and such-and-such settings.".  If at 
all possible use some other OS which has fewer interacting settings relating to 
characterset.

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

Re: [sqlite] primary key in another column

[Igor Tandetnik]

On 1/26/2018 4:43 PM, Roman Fleysher wrote:

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


Why do you want to store redundant data? What's the actual problem this is 
supposed to help you solve? As stated, this looks like an XY problem ( 
http://xyproblem.info/ )

--
Igor Tandetnik

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

Solved with trigger, but I can not use NOT NULL for the fileName column:

CREATE TRIGGER AAA AFTER INSERT ON A 
BEGIN
  UPDATE A SET fileName = 'prefix'||NEW.id WHERE id=NEW.id;
END;

INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
...

Is that a right solution?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Friday, January 26, 2018 4:43 PM
To: General Discussion of SQLite Database
Subject: [sqlite] primary key in another column

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

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] Queries using json_each in a JOIN are not working

[Jens Alfke]

> On Jan 26, 2018, at 1:36 PM, Richard Hipp  wrote:
> 
> For now, please use CROSS JOIN instead of INNER JOIN to force the
> correct join order.

You're right, that fixed the query. Thanks!

I must admit, I don't understand the significance of join order here. It seems 
to me that the results would be the same whether "itemDS" or "categoryDS" is 
the outer loop; it's only performance that might change. Could someone explain?

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

[sqlite] primary key in another column

[Roman Fleysher]

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

Roman

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

Re: [sqlite] Queries using json_each in a JOIN are not working

[Richard Hipp]

On 1/26/18, Jens Alfke  wrote:
>
> # This is the query that occurs in a bug report I got from a developer; it
> should return two rows but instead returns none:
> SELECT "itemDS".id, "categoryDS".id
> FROM docs AS "categoryDS"
> INNER JOIN docs AS "itemDS"
> ON (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS
> _item
>  WHERE _item.value = "itemDS".id));

For now, please use CROSS JOIN instead of INNER JOIN to force the
correct join order.  I'll investigate further to see if this is
something SQLite ought to be doing automatically.

-- 
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] Convincing SQLITE to use alternate index for count(*)

[Richard Hipp]

On 1/26/18, Deon Brewis  wrote:
> I have a table with 2 indexes:
>
> CREATE TABLE Foo (
> Id Integer,
> GuidId blob PRIMARY KEY
> ) WITHOUT ROWID;
>
> CREATE UNIQUE INDEX FooId ON Foo(Id);
>
> CREATE INDEX FooBar ON Resource(Bar(GuidId));

I think you mistyped something on that last line, and as a
consequence, I have having difficulty decoding your problem.

>
> When I do:
> SELECT COUNT(*) FROM Foo;
>
> The query plan always uses the FooBar index.  But the FooBar index is
> physically bigger on disk than the FooId index. I'd like it to count FooId
> instead.  How can I coerce SQLITE to count FooId instead of FooBar?
>
> I've tried the following:
>
> SELECT COUNT(*) FROM Foo INDEXED BY FooId;  -- ignores the INDEXED BY
> clause
> SELECT COUNT(Id) FROM Foo INDEXED BY FooId; -- uses the right index, but
> filters nulls, so slower
> SELECT COUNT(1) FROM Foo INDEXED BY FooId;  -- uses the right index, but
> slower for some unknown reason??
>
> It seems the "COUNT(*) FROM Foo INDEXED BY FooId" fails to use FooId,
> because FooId is a UNIQUE index. If I make FooId  not unique, it correctly
> uses it.
>
> But surely UNIQUE shouldn't make any difference to this query? Either way,
> that doesn't matter specifically, but I can't seem to find the syntax to
> make it use the smaller index. Any ideas?
>
> - Deon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] UTF8 and NUL

[Peter Da Silva]

Sqlite uses NUL as the string terminator internally, the published API 
specifies has stuff like this all over the place:

> In those routines that have a fourth argument, its value is the number of 
> bytes in the parameter. To be clear: the value is the number of bytes in the 
> value, not the number of characters. If the fourth parameter to 
> sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of 
> the string is the number of bytes UP TO THE FIRST ZERO TERMINATOR.

It would be a huge push-up to change this, it would break everything, including 
extensions. I don't think it would be possible until something like sqlite4.

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

[sqlite] Convincing SQLITE to use alternate index for count(*)

[Deon Brewis]

I have a table with 2 indexes:

CREATE TABLE Foo (
Id Integer,
GuidId blob PRIMARY KEY
) WITHOUT ROWID;

CREATE UNIQUE INDEX FooId ON Foo(Id);

CREATE INDEX FooBar ON Resource(Bar(GuidId));

When I do:
SELECT COUNT(*) FROM Foo;

The query plan always uses the FooBar index.  But the FooBar index is 
physically bigger on disk than the FooId index. I'd like it to count FooId 
instead.  How can I coerce SQLITE to count FooId instead of FooBar?

I've tried the following:

SELECT COUNT(*) FROM Foo INDEXED BY FooId;  -- ignores the INDEXED BY clause
SELECT COUNT(Id) FROM Foo INDEXED BY FooId; -- uses the right index, but 
filters nulls, so slower
SELECT COUNT(1) FROM Foo INDEXED BY FooId;  -- uses the right index, but 
slower for some unknown reason??

It seems the "COUNT(*) FROM Foo INDEXED BY FooId" fails to use FooId, because 
FooId is a UNIQUE index. If I make FooId  not unique, it correctly uses it.

But surely UNIQUE shouldn't make any difference to this query? Either way, that 
doesn't matter specifically, but I can't seem to find the syntax to make it use 
the smaller index. Any ideas?

- Deon

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

Re: [sqlite] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 11:41 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
> >doesn't get 26 either. 0x1a
>
> 26 isn't EOF, it's SUB (substitute). It was used to represent
> untranslatable characters when converting (for example) EBCDIC to ASCII.
>
> I gave up ever using "rt" or "wt"  because it IS EOF; depending on the
system.
I bet windows command line tools still use it because copy has /B and /A on
windows 10.

(interject, edit:  The effect of */a* depends on its position in the
command-line string. When */a* follows *Source*, *copy* treats the file as
an ASCII file and copies data that precedes the first end-of-file character.
https://en.wikipedia.org/wiki/End-of-file

"In Microsoft's DOS  and Windows
 (and in CP/M
 and many DEC
 operating
systems), reading from the terminal will never produce an EOF. Instead,
programs recognize that the source is a terminal (or other "character
device") and interpret a given reserved character or sequence as an
end-of-file indicator; most commonly this is an *ASCII
 Control-Z
**, code 26. *Some
MS-DOS programs, including parts of the Microsoft MS-DOS shell (COMMAND.COM
) and operating-system utility
programs (such as EDLIN ), treat a
Control-Z in a text file as marking the end of meaningful data, and/or
append a Control-Z to the end when writing a text file. This was done for
two reasons:"


... ASCII  Control-Z
, code 26. 
)

I understand probably 0xFF on punch cards was good because you could just
knock out all the holes and make a correction; and that could be an EOF on
other systems.  unless like O_BINARY was used.

so now we just use files as binary and get the length from the system; and
don't expect any transformations on our data.

--
More on my point though

Sqlite results with values with sqlite3_column_text(stmt,n) and
sqlite3_column_bytes(stmt,n) so any data including NUL from bound or
otherwise values is returned.

strcmp() would have an issue.  Even StrNCmp() and really you need a
comparison that inludes length of both strings.
Strlen is used constantly to find lengths of column, table, and function
names for things that should already be known.  It's not like there's a lot
of copying of those; the net effect is more speec; because it's not even
'strlen' that can be auto-intrinsic-inlined, but a fancy function that
sanitizes the length (sqlite3StrLen()).

The commands for SQL
LENGTH, RTRIM, LTRIM, QUOTE that deal with strings...
MySQL returns bytes for length.  Sqlite returns characters.  and all string
functions work on characters, which means sqltie has to understand UTF8
characters

I wouldn't use any of those functions except in a one-of script because
they are non portable.  But they are non conformant because they do support
a basic way of skipping utf characters 0x9X arbitrarily is also not a
valid UTF8 character (it's a continutation that had no leadin length).
SO that makes even the unicode escapes in the range of 0x9X also available
to encode as bytes, kinda OOB with the data.


Invalid characters (overlong and otherwise) should be replaced with FFFD
http://unicode.org/pipermail/unicode/2017-May/005522.html (from this
thread, sort of; was on ill formed utf-8, really the past of this thread
but didn't find it)
https://www.fileformat.info/info/unicode/char/fffd/index.htm
Comments used to replace an incoming character whose value is unknown or
unrepresentable in Unicode
compare the use of U+001A
 as a control
character to indicate the substitute function

(I would have said, "0xFEFF ? ZWNBSP zero width non breaking space(?) EF BB
BF "  but went and searched and found it was different than I thought )
A quick note about UTF8; every byte has one bit off.

My initial impression was that it shouldn't care, being basically a smart
storage engine; since what I put in I could get back out.  Having patched
the input side to escape ' and NUL in string values, I don't need my larger
patch.
But then having looked through so much of the string handling, the overall
effect is still positive.

Then there's internal logging and analysis, which should also escape the
output for strings, there IS a SQL way to include char(0).
can't really change sqlite3_column_text at this point; which means no
matter how much it is enforced and made harder to not count 0 as a
character, it doesn't 

[sqlite] Queries using json_each in a JOIN are not working

[Jens Alfke]

I’m getting very strange behavior from a class of query using `json_each`. The 
intent here is to have a join condition based on one row’s JSON having an array 
containing the other row’s “id” property. The query is horribly non-optimal but 
I think it should return results; but it returns no results. After two hours of 
experimenting with variations of this query, I'm suspecting a bug in SQLite, 
possibly in the json_each function.

(I'm using SQLite 3.22 on macOS 10.13.3.)

# Here’s a minimal data set:
CREATE TABLE docs (id text, body text);
INSERT INTO docs VALUES('ecc:102', 
'{"id":"ecc:102","type":"category","items":["eci:742","eci:743","eci:744"]}');
INSERT INTO docs VALUES('eci:742', '{"id":"eci:742","type":"item"}');
INSERT INTO docs VALUES('eci:743', '{"id":"eci:743","type":"item"}’);

# This is the query that occurs in a bug report I got from a developer; it 
should return two rows but instead returns none:
SELECT "itemDS".id, "categoryDS".id 
FROM docs AS "categoryDS" 
INNER JOIN docs AS "itemDS" 
ON (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item 
 WHERE _item.value = "itemDS".id));

# Let's simplify this by taking out the reference to itemDS in the nested 
select, using a hardcoded string instead:
SELECT "itemDS".id, "categoryDS".id 
FROM docs AS "categoryDS" 
INNER JOIN docs AS "itemDS" 
ON (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item 
 WHERE _item.value = 'eci:742'));
# Nope, still no results.

# Let's preflight the 'items' JSON property:
SELECT "itemDS".rowid, "categoryDS".rowid 
FROM docs AS "itemDS" 
INNER JOIN docs AS "categoryDS" 
 ON json_extract("categoryDS".body, '$.items') NOT NULL
AND (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item 
 WHERE _item.value = 'eci:743'));
# This returns three results, as expected!


The minimal complexity I can narrow the WTF down to is that this query doesn't 
work (returns nothing):
SELECT itemDS.id, categoryDS.id
FROM docs AS "itemDS", docs AS "categoryDS" 
WHERE (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item 
WHERE _item.value = 'eci:742'));

…but this one does work:
SELECT categoryDS.id
FROM docs AS "categoryDS" 
WHERE (EXISTS (SELECT 1 FROM json_each("categoryDS".body, '$.items') AS _item 
WHERE _item.value = 'eci:742'));

I'm willing to believe that there's some subtlety of SQL that causes this 
behavior; but it really does smell like a SQLite bug to me…

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 2:34 PM, "sqlite-users on behalf of J. King" 
 
wrote:
> Do you have a point in making either statement? If you do, I'm really not 
> seeing it.

The point is that apart from CP/M and derivatives like DOS, this kind of 
behavior is strictly a leftover from the '60s. And CP/M only had this 
restriction because it was tremendously resource-constrained. It's not a 
precedent for treating some magic character as an end-of-file marker when 
virtually every operating released since 1970 system (apart from a couple that 
derived from this historical anomaly) has had files with byte-precise size 
metadata.

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

Re: [sqlite] UTF8 and NUL

[J. King]

On 2018-01-26 15:13:46, "Peter Da Silva"  
wrote:


On 1/26/18, 2:11 PM, "sqlite-users on behalf of John McKown" 
 wrote:
​In the distant past (CP/M-80), the filesystem meta data did not 
include the actual _length_ of the data for a text data file.


Since DOS wasn't an OS, then CP/M certainly wasn't.


Do you have a point in making either statement? If you do, I'm really 
not seeing it.


--
J. King

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 2:11 PM, "sqlite-users on behalf of John McKown" 
 wrote:
> ​In the distant past (CP/M-80), the filesystem meta data did not include the 
> actual _length_ of the data for a text data file.

Since DOS wasn't an OS, then CP/M certainly wasn't.
 

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

Re: [sqlite] UTF8 and NUL

[John McKown]

On Fri, Jan 26, 2018 at 1:41 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wr

> On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
> >doesn't get 26 either. 0x1a
>
> 26 isn't EOF, it's SUB (substitute). It was used to represent
> untranslatable characters when converting (for example) EBCDIC to ASCII.
>

​In the distant past (CP/M-80), the filesystem meta data did not include
the actual _length_ of the data for a text data file. The I/O was done in
sectors. The CP/M-80 system, by convention, used 0x1A (26) and an "logical
EOF" indication and the C routines would detect it and report EOF.​ MS-DOS
basically didthe same thing, for compatibility reasons. I am not sure, but
I think that Windows still does this. A quick test with the command "type
x.txt" where "x.txt" contained "abc~def" (where ~ is standing in for 0x1a)
resulted in my seeing "abc". But "notepad x.txt" shows "abc def". So I
guess it depends on how old the Windows app is.


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
>doesn't get 26 either. 0x1a

26 isn't EOF, it's SUB (substitute). It was used to represent untranslatable 
characters when converting (for example) EBCDIC to ASCII.

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

Re: [sqlite] json1 extenstion won't load in command shell

[Richard Hipp]

On 1/26/18, Phil Dagosto  wrote:
> SQLite version 3.22.0 2018-01-22 18:45:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> pragma compile_options;
> COMPILER=gcc-4.8.5 20150623 (Red Hat 4.8.5-11)
> ENABLE_JSON1

The JSON1 extension is built into the shell.  It doesn't need to be
loaded separately.

> THREADSAFE=1
> sqlite> .load ./json1
> Error: error during initialization:
> sqlite> .load ./json1.so
> Error: error during initialization:
>
> I compiled the extension as recommended by the documentation:
>
> gcc -g -fPIC -shared ext/misc/json1.c -o json1.so
>
> Is there some trick to this or something I'm missing?
>
> Thanks in advance
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 10:44 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 1/26/18, 12:40 PM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
> >  reads the bytes and does things with them.  the EOF would get returned
> with fgetc() but not the character.
>
> Fgetc returns an int, not a byte. That EOF is -1, not 0xFF.
>
doesn't get 26 either. 0x1a

>
>
>
> ___
> 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] json1 extenstion won't load in command shell

[Phil Dagosto]

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma compile_options;
COMPILER=gcc-4.8.5 20150623 (Red Hat 4.8.5-11)
ENABLE_JSON1
THREADSAFE=1
sqlite> .load ./json1
Error: error during initialization:
sqlite> .load ./json1.so
Error: error during initialization:

I compiled the extension as recommended by the documentation:

gcc -g -fPIC -shared ext/misc/json1.c -o json1.so

Is there some trick to this or something I'm missing?

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:40 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
>  reads the bytes and does things with them.  the EOF would get returned with 
> fgetc() but not the character.

Fgetc returns an int, not a byte. That EOF is -1, not 0xFF.



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

Re: [sqlite] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 10:35 AM, Tim Streater  wrote:

> On 26 Jan 2018, at 18:12, Keith Medcalf  wrote:
>
> > Actually, EOF (0xFF) *is* part of a text file, and is the byte in an
> ASCII
> > byte-stream that indicates end-of-file.
>
> First I've heard of that. Which systems did that then? EOF is normally
> indicated by the file system, not by file data.
>
> the 't' part of fopen( "xxx", "rt" );

reads the bytes and does things with them.  the EOF would get returned with
fgetc() but not the character.

>
> --
> 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] missing subquery flattening

[Clemens Ladisch]

Mark Brand wrote:
> Shouldn't we expect subquery flattening to happen in V2 below?
>
> -- no flattening
> CREATE VIEW V2 AS
> SELECT * FROM X
> LEFT JOIN (
> SELECT * FROM X
> LEFT JOIN Y ON Y.a = X.a
> ) Z
> ON Z.a = X.a;
>
> -- manually flattened version of V2
> CREATE VIEW V2_FLATTENED AS
> SELECT *
> FROM X
> LEFT JOIN X X2
>ON X2.a = X.a
> LEFT JOIN Y
>ON Y.a = X2.a;

In the general case, left outer joins are not associative.  Apparently,
SQLite does not try to prove the opposite for special cases.


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

Re: [sqlite] UTF8 and NUL

[Tim Streater]

On 26 Jan 2018, at 18:12, Keith Medcalf  wrote:

> Actually, EOF (0xFF) *is* part of a text file, and is the byte in an ASCII
> byte-stream that indicates end-of-file.

First I've heard of that. Which systems did that then? EOF is normally 
indicated by the file system, not by file data.


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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:31 PM, "sqlite-users on behalf of J Decker" 
 
wrote:
> ctrl-z was end of file text character in DOS (wrote char 26; not FF)

DOS wasn't an operating system.
 

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

Re: [sqlite] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 10:22 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 1/26/18, 12:12 PM, "sqlite-users on behalf of Keith Medcalf" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> kmedc...@dessus.com> wrote:
> > Actually, EOF (0xFF) *is* part of a text file, and is the byte in an
> ASCII byte-stream that indicates end-of-file.  In the "old days" the bytes
> following the last-byte in a stream and the end of a storage block
> (sector/cluster/track/cylinder, what have you) were padded with 0xFF so
> you knew you were past the end-of-the-file when you were reading it.
>
> Oh, I remember the messes that existed before stream files became the
> norm. But messes they were, and there's no more reason to support them in a
> Unicode file than there is to support FIELDDATA format.
>
> And if you're going to talk about the block file and paper tape era, don't
> forget that FF also meant a deleted character and should be skipped without
> being counted or accounted for.
>
>
ctrl-z was end of file text character in DOS (wrote char 26; not FF)
EOF is returned as -1 not 0xFF (although signed char looks really similar)
the character 0xFF is 0xC3 0xBF nof 0xFF.



> ___
> 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] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 12:12 PM, "sqlite-users on behalf of Keith Medcalf" 
 
wrote:
> Actually, EOF (0xFF) *is* part of a text file, and is the byte in an ASCII 
> byte-stream that indicates end-of-file.  In the "old days" the bytes 
> following the last-byte in a stream and the end of a storage block 
> (sector/cluster/track/cylinder, what have you) were padded with 0xFF so you 
> knew you were past the end-of-the-file when you were reading it.

Oh, I remember the messes that existed before stream files became the norm. But 
messes they were, and there's no more reason to support them in a Unicode file 
than there is to support FIELDDATA format.

And if you're going to talk about the block file and paper tape era, don't 
forget that FF also meant a deleted character and should be skipped without 
being counted or accounted for.

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

Re: [sqlite] UTF8 and NUL

[Keith Medcalf]

Actually, EOF (0xFF) *is* part of a text file, and is the byte in an ASCII 
byte-stream that indicates end-of-file.  In the "old days" the bytes following 
the last-byte in a stream and the end of a storage block 
(sector/cluster/track/cylinder, what have you) were padded with 0xFF so you 
knew you were past the end-of-the-file when you were reading it.

Just "more modern" Operating Systems are capable of setting the file length 
more accurately than in the past. And "stream" processors now recognize 
"running out of data" as EOF.  Just because it is now thus does not mean it was 
always so. (And, of course, just because a "stream" has no more data to return 
does not necessarily mean that it is at end-of-file, merely that there is no 
more data to return *at the moment* -- perhaps the card reader is jammed or the 
paper-tape broke :) ).

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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva
>Sent: Friday, 26 January, 2018 07:30
>To: SQLite mailing list
>Subject: Re: [sqlite] UTF8 and NUL
>
>On 1/26/18, 8:24 AM, "sqlite-users on behalf of Gary R. Schmidt"
>g...@mcleod-schmidt.id.au> wrote:
>> But how would you differentiate EOF???  (Let me guess, 0.  :-) )
>
>End of file is not part of the contents of the file or a string. It's
>metadata.
>
>___
>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] UTF8 and NUL

[J Decker]

On Fri, Jan 26, 2018 at 5:55 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> What is the goal of this discussion? Changing the string terminator SQLite
> uses? I think it's almost 50 years too late for that, but I'm sure that if
> Unicode and UTF8 had been a thing in 1970 then C would have selected FF as
> the string terminator.
>
> There's so much resistence to handling NUL in command line tools, test and
in the engine itself, I figured there must be a reason; maybe the
Authentication/Encryption that has been added to sqlite by sqlite people
stores meta data after field content; Such content could still be kept, and
isolated from users with an alternative string terminator;  since that
character is never returned to the user it doesn't matter what sqlite uses
internally (other than having previously used something else)  Is probably
a change that is 3.x to 4.x significant though.


> ___
> 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] missing subquery flattening

[Mark Brand]

Hi,

Shouldn't we expect subquery flattening to happen in V2 below?

Mark

CREATE TABLE X ( a PRIMARY KEY, b ) WITHOUT ROWID;
CREATE TABLE Y ( a PRIMARY KEY ) WITHOUT ROWID;
/*
WITH RECURSIVE Q AS (
    SELECT 1 a
    UNION ALL SELECT a + 1 FROM Q
    WHERE a < 10
)
INSERT INTO X (a, b)
SELECT a, CHAR(a%255) FROM Q;
*/
-- flattening as expected
CREATE VIEW V1 AS
    SELECT * FROM X
    LEFT JOIN (
    SELECT X.* FROM X
    ) Z
    ON Z.a = X.a;

-- no flattening
CREATE VIEW V2 AS
    SELECT * FROM X
    LEFT JOIN (
    SELECT * FROM X
    LEFT JOIN Y ON Y.a = X.a
    ) Z
    ON Z.a = X.a;

-- manually flattened version of V2
CREATE VIEW V2_FLATTENED AS
    SELECT *
    FROM X
    LEFT JOIN X X2
   ON X2.a = X.a
    LEFT JOIN Y
   ON Y.a = X2.a;

SELECT sqlite_version();
EXPLAIN QUERY PLAN SELECT * FROM V1;
/*
0|0|0|SCAN TABLE X
0|1|1|SEARCH TABLE X USING PRIMARY KEY (a=?)
*/

EXPLAIN QUERY PLAN SELECT * FROM V2;
/*
1|0|0|SCAN TABLE X
1|1|1|SEARCH TABLE Y USING PRIMARY KEY (a=?)
0|0|0|SCAN TABLE X
0|1|1|SEARCH SUBQUERY 1 AS Z USING AUTOMATIC COVERING INDEX (a=?)
*/

EXPLAIN QUERY PLAN SELECT * FROM V2_FLATTENED;
/*
0|0|0|SCAN TABLE X
0|1|1|SEARCH TABLE X AS X2 USING PRIMARY KEY (a=?)
0|2|2|SEARCH TABLE Y USING PRIMARY KEY (a=?)
*/

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

[sqlite] documentation edits needed

[Mark Brand]

Here are some suggested improvements for the constraints listed under:

    https://www.sqlite.org/optoverview.html, section  10. Subquery 
flattening



3. The subquery is not the right operand of a LEFT JOIN then the 
subquery may not be a join, the FROM clause of the subquery may not 
contain a virtual table, and the outer query may not be an aggregate.
Missing "If" and comma before "then". (This one is particularly hard for 
me to parse as-is.)


15. The outer query is part of a compound select, then the subquery 
may not have a LIMIT clause.

Missing "If.."


16. The outer query is an aggregate, then the subquery may not contain 
ORDER BY.

Missing "If.."



17. The sub-query is a compound SELECT, then...

Missing "If.."



21. The subquery uses LIMIT, then the outer query may not be DISTINCT.

Missing "If.."



22. The subquery may not a recursive CTE. 

Should read "..may not be..".


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

Re: [sqlite] SQLite 3.22.0: Precompiled Windows 64-bit DLL is 32-bit?

[Joe Mistachkin]

Stanley Jacob wrote:
>
> With 3.22.0, this produces "ImportError: DLL load failed: %1 is not a
> valid Win32 application."
> With 3.21.0, there is no error.
> 

Thanks for the report.  It should be the x64 version now.

--
Joe Mistachkin 

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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

On 1/26/18, 8:24 AM, "sqlite-users on behalf of Gary R. Schmidt" 
 wrote:
> But how would you differentiate EOF???  (Let me guess, 0.  :-) )

End of file is not part of the contents of the file or a string. It's metadata. 

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

Re: [sqlite] UTF8 and NUL

[Gary R. Schmidt]

On 27/01/2018 00:55, Peter Da Silva wrote:

What is the goal of this discussion? Changing the string terminator SQLite 
uses? I think it's almost 50 years too late for that, but I'm sure that if 
Unicode and UTF8 had been a thing in 1970 then C would have selected FF as the 
string terminator.
But how would you differentiate EOF???  (Let me guess, 0.  :-) )


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

Re: [sqlite] UTF8 and NUL

[Peter Da Silva]

What is the goal of this discussion? Changing the string terminator SQLite 
uses? I think it's almost 50 years too late for that, but I'm sure that if 
Unicode and UTF8 had been a thing in 1970 then C would have selected FF as the 
string terminator. 

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

[sqlite] SQLite 3.22.0: Precompiled Windows 64-bit DLL is 32-bit?

[Stanley Jacob]

I downloaded the precompiled Windows binary from:
https://sqlite.org/2018/sqlite-dll-win64-x64-322.zip

This DLL causes a "not a valid Win32 application" error when using it
with 64-bit Python 2.7.14. Dependency Walker shows it as an x86 DLL,
not x64.

I tried the previous version from:
https://sqlite.org/2017/sqlite-dll-win64-x64-321.zip
This works as expected and is seen as an x64 DLL.

To reproduce:

Install Python 2.7.14 (64 bit)
In the DLLs subdirectory, replace sqlite3.dll with the downloaded version.
Run python
import sqlite3

With 3.22.0, this produces "ImportError: DLL load failed: %1 is not a
valid Win32 application."
With 3.21.0, there is no error.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_analyzer Python port

[Santiago Gil]

Hi. I have been working on porting the `sqlite3_analyzer` TCL script
to Python. [0]

In the process I built a separate Python 3 module [1] that provides
methods to individually extract the different space-usage stats. It
allows to easily do further processing with those values, for example
to generate graphs. Additionally, it can parse SQLite headers.

I wanted to share it here in hope that it can be useful to someone.

All the code, and the docs, are beta, so I would appreciate any
feedback or bug report.

Santiago


[0]: Script - https://github.com/santigl/py-sqlite-analyzer (depends on [1]).
[1]: Package - https://github.com/santigl/sqliteanalyzer
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8 and NUL

[Clemens Ladisch]

J Decker wrote:
> U+009C 156 String Terminator ST

"ST is used as the closing delimiter of a control string opened by
APPLICATION PROGRAM COMMAND (APC), DEVICE CONTROL STRING (DCS),
OPERATING SYSTEM COMMAND (OSC), PRIVACY MESSAGE (PM), or START OF
STRING (SOS)."


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

Re: [sqlite] UTF8 and NUL

[J Decker]

https://en.wikipedia.org/wiki/List_of_Unicode_characters#Control_codes
Even the Control codes within unicode aren't FF.

U+009C 156 String Terminator ST
literal bytes \xC2\x9c  are string terminator ... Was thinking that like
APC and ST were higher than that... more in the range of 0xF8-0xFF



On Thu, Jan 25, 2018 at 7:57 PM, J Decker  wrote:

> NUL is a valid utf8 character
> but FF is never valid.  (would be like a 36 bit length specification)
> and practically anthing more than F8 is invalid utf8 character.
> Other than BOM
> https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8
> EF BB BF 239 187 191
>
> // EF - 80 | 3b - 80 | 3f
> ( 0xfeff  )
>
>
> Many Windows  programs
> (including Windows Notepad
> ) add the bytes 0xEF,
> 0xBB, 0xBF at the start of any document saved as UTF-8. Th
>
> (Not that BOM is even required, because, it's already ordered bytes)
> --
> But anYway FF could be used as a string terminator instead of 00.  It is
> never legal in any utf-8 sequence.
> (F8,F9,FA,FB,FC,FD,FE,FF)
> F8 would be a 5 byte encoding, but that is more code points than unicode
> has allocated.  It could be potentially useful to permit a little extra
> space in sequences , so I would avoid F8(F9,FA,FB) and stick to FC-FF for
> possible control characters.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users