Re: [sqlite] UTF8 and NUL

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

[sqlite] sqlite3_analyzer Python port

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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.

[sqlite] documentation edits needed

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

Re: [sqlite] UTF8 and NUL

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.

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

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] SQLite 3.22.0: Precompiled Windows 64-bit DLL is 32-bit?

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:

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] UTF8 and NUL

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

[sqlite] missing subquery flattening

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)

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] missing subquery flattening

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 >

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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,

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

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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)

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

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

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

Re: [sqlite] primary key in another column

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

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] primary key in another column

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

Re: [sqlite] primary key in another column

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

Re: [sqlite] primary key in another column

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

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

> 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

Re: [sqlite] primary key in another column

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

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

Re: [sqlite] UTF8 and NUL

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

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

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

Re: [sqlite] UTF8 and NUL

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

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

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

[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

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

Re: [sqlite] primary key in another column

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

Re: [sqlite] UTF8 and NUL

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,

Re: [sqlite] UTF8 and NUL

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

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

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

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

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

Re: [sqlite] UTF8 and NUL

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

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

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

Re: [sqlite] UTF8 and NUL

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

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

Re: [sqlite] UTF8 and NUL

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

[sqlite] Documentation Bug?

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,

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

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] primary key in another column

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]

Re: [sqlite] regression since 3.20.0

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 >

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] regression since 3.20.0

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

Re: [sqlite] UTF8 and NUL

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?

Re: [sqlite] UTF8 and NUL

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.

Re: [sqlite] UTF8 and NUL

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

Re: [sqlite] regression since 3.20.0

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

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

[sqlite] regression since 3.20.0

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

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?