Re: [sqlite] Schedule Up - 26th Annual Tcl/Tk Conference (Tcl'2019)
Richard Hipp, on Monday, September 23, 2019 05:10 PM, wrote... > I am presenting an 8-hour tutorial on SQLite internals on Tuesday > (2019-11-05). Have you signed up for that yet? :-) Yes, I saw that. I am in Rochester, NY. Texas is a bit far. But, next conferece 5 hours or less, I will sign up. ;-) Go teach them. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?
On 2019-09-23 1:22 p.m., Simon Slavin wrote: Can I get an answer to my 'Subject' header ? Is the parameter to the PRAGMA a string parameter ? If so, can the documentation be changed to use apostrophes as delimiters ? Using double quotes in the documentation seems really weird. If, on the other hand, the PRAMGA is actually looking for double-quotes after the equals sign, is this for a reason ? I agree. All documentation should show the more standard/correct single quotes for strings when that works, and only show double quotes when those are required. This would help avoid a lot of confusion. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - macOS
> On Sep 23, 2019, at 9:53 AM, Pierre Clouthier
> wrote:
>
> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
That isn't necessary. SQLite defaults to UTF-8.
In most cases SQLite doesn't interpret the byte sequences in a string. It just
knows it's using an 8-bit character set and leaves it at that. So if the text
is messed up in the database, it's because it was entered in messed-up form.
(Some components do work with non-ASCII text, like FTS, depending on how you've
built/configured SQLite, and those do interpret UTF-8 sequences.)
—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schedule Up - 26th Annual Tcl/Tk Conference (Tcl'2019)
On 9/23/19, Jose Isaias Cabrera wrote: > > confere...@tclcommunityassociation.org, on Monday, September 23, 2019 04:20 > PM, wrote... > >> [ NEWS >> * Our keynote speaker is [Will Duquette](https://github.com/wduquette) >> talking about "Tcl, Rust, and the Death of Rube Goldberg" > > Dr. Hipp, I would have placed you keynote speaker before this guy. ;-) > Heck, at least also add a little note: "Ah, yes, and Dr. Hipp would be doing > a little som'ng, som'ng." :-) I've already done an TCL-conference keynote. Maybe even twice - I forget. Will is a great speaker and will do a terrific job. I am presenting an 8-hour tutorial on SQLite internals on Tuesday (2019-11-05). Have you signed up for that yet? :-) -- 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] Why "UTF-8" and not 'UTF-8' ?
On Monday, 23 September, 2019 14:09, Richard Hipp :
>In any event, you are correct that the behavior can now be disabled
>and should be for new applications. But we need to leave it turned on
>by default for legacy.
Nevertheless, the point is that even if you purportedly turn the behaviour off,
it is not turned off:
That is, if you compile with DQS set to 0, the statement
pragma encoding = "UTF-8"
is still accepted without error.
SQLite version 3.30.0 2019-09-23 19:37:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma encoding="UTF-8";
sqlite> .dbconfig
enable_fkey on
enable_trigger on
enable_view on
fts3_tokenizer off
load_extension on
no_ckpt_on_close off
enable_qpsg off
trigger_eqp off
reset_database off
defensive off
writable_schema off
legacy_alter_table off
dqs_dml off
dqs_ddl off
If fact the dqs_ddl setting seems not to do anything at all. dqs_dml seems to
work however.
sqlite> create table x (x default "DFLT");
sqlite> insert into x default values;
sqlite> select * from x;
DFLT
sqlite> insert into x values ("DFLT");
Error: no such column: DFLT
sqlite>
Now, if you created a legacy database with dqs_ddl turned on and opened it in a
version of SQLite3 that has dqs_ddl turned off, should parsing a currently
malformed schema produce an error? However, the create with incorrect quotes
should be an error if at the time it is made dqs_ddl is turned off.
sqlite> create view v as select "TEST";
sqlite> select * from v;
Error: no such column: TEST
sqlite>
Should this not toss an error when the ddl creating the view is parsed? Hard
to say because a view is merely a stored statement and I don't think it is
parsed (as in prepared) at create view time, however it does definitely throw
an error when executed (prepared), in accordance with the dqs_dml setting.
Does the dqs_ddl setting need to be passed to something else, like the parser
generator?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schedule Up - 26th Annual Tcl/Tk Conference (Tcl'2019)
confere...@tclcommunityassociation.org, on Monday, September 23, 2019 04:20 PM, wrote... > [ NEWS > * Our keynote speaker is [Will Duquette](https://github.com/wduquette) > talking about "Tcl, Rust, and the Death of Rube Goldberg" Dr. Hipp, I would have placed you keynote speaker before this guy. ;-) Heck, at least also add a little note: "Ah, yes, and Dr. Hipp would be doing a little som'ng, som'ng." :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?
On 23 Sep 2019, at 9:09pm, Richard Hipp wrote: > In any event, you are correct that the behavior can now be disabled > and should be for new applications. Can I get an answer to my 'Subject' header ? Is the parameter to the PRAGMA a string parameter ? If so, can the documentation be changed to use apostrophes as delimiters ? Using double quotes in the documentation seems really weird. If, on the other hand, the PRAMGA is actually looking for double-quotes after the equals sign, is this for a reason ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schedule Up - 26th Annual Tcl/Tk Conference (Tcl'2019)
Hello SQLite Users, fyi ... 26th Annual Tcl/Tk Conference (Tcl'2019) https://www.tcl-lang.org/community/tcl2019/ November 05 - 08, 2019 Crowne Plaza Houston River Oaks 2712 Southwest Freeway, 77098 Houston, Texas, USA [ NEWS * Our keynote speaker is [Will Duquette](https://github.com/wduquette) talking about "Tcl, Rust, and the Death of Rube Goldberg" * Registration is open. Please have a look at https://www.tcl-lang.org/community/tcl2019/register.html * The tutorials are known. See https://www.tcl-lang.org/community/tcl2019/tutorials.html ] And our schedule for this year is now available at https://www.tcl-lang.org/community/tcl2019/schedule.html This is the last general mail for the year. Conference Committee * Andreas Kupries * Arjen MarkusDeltares * Brian Griffin Mentor - A Siemens Business * Gerald Lester KnG Consulting LLC * Joe Mistachkin Mistachkin Systems * Ronald Fox CAEN Technologies NSCL @ Michigan State University Contact Information tclconfere...@googlegroups.com Tcl'2019 would like to thank those who are sponsoring the conference: * FlightAware * Mentor - A Siemens Business * Noumena Corp ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?
On 9/23/19, Keith Medcalf wrote: > > due to a > longstanding bug in SQLite3 you can use identifier quotes around strings I don't think "bug" is quite the right word here, as the behavior was deliberate. The use of double-quotes for strings was put in in a (perhaps misguided) attempt to be more compatible with MySQL 3.x, which was the predominant SQL engine when SQLite was first being designed. Maybe "mis-feature" would be a better word. In any event, you are correct that the behavior can now be disabled and should be for new applications. But we need to leave it turned on by default for legacy. -- 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] Why "UTF-8" and not 'UTF-8' ?
It should be pragma encoding = 'UTF-8'; UTF-8 is a character string, not an identifier. And yes, due to a longstanding bug in SQLite3 you can use identifier quotes around strings -- if the identifier quotes do not resolve to an identifier they fallback to a string. You can now "turn this bug off" but the turning off does not seem to affect usage in most DDL, which will still carry on accepting double-quoted strings in most places. >-Original Message- >From: sqlite-users On >Behalf Of Simon Slavin >Sent: Monday, 23 September, 2019 11:28 >To: SQLite mailing list >Subject: [sqlite] Why "UTF-8" and not 'UTF-8' ? > >The documentation for PRAGMA includes this command: > >PRAGMA encoding = "UTF-8"; > >Why is it "UTF-8" and not 'UTF-8' ? Why is it double quotes rather than >a normally-delimited string ? Should either of these work ? > >PRAGMA encoding = UTF-8;PRAGMA encoding = 'UTF-8' >___ >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] SQLite - macOS
> On Sep 23, 2019, at 1:25 PM, Simon Slavin wrote:
>
>> On 23 Sep 2019, at 5:53pm, Pierre Clouthier
>> wrote:
>>
>> Can anyone explain how to write UTF-8 in SQLite on the Mac?
>>
>> We use this statement:
>>
>> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
>
> This is not a SQLite problem, but a problem with escaping the quotes in
> language you're using to execute sqlite3 calls.
>
> What programming language or script system are you using ? Will it accept
> any other way to escape the quote marks ?
>
> It should be possible to find a line of code which works on all platforms
> your compiler supports.
>
My guess is that it isn’t a language issues, as it sounds like they are working
on a multi-platform program, but that they are reading in a file that isn’t
UTF-8 and trying to insert it directly (and getting a Unicode coding error
because the data isn’t UTF-8), or the file IS UTF-8 but they don’t think it is
so they run a conversion on it to convert to UTF-8 from some other character
set, and that is throwing an unrecognized character error.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?
On September 23, 2019 1:27:54 p.m. EDT, Simon Slavin wrote: >The documentation for PRAGMA includes this command: > >PRAGMA encoding = "UTF-8"; > >Why is it "UTF-8" and not 'UTF-8' ? Why is it double quotes rather >than a normally-delimited string ? Should either of these work ? > >PRAGMA encoding = UTF-8;PRAGMA encoding = 'UTF-8' >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users It's worth noting, too, that barring changed compile-time settings, the default encoding is UTF-8 to begin with. -- J. King ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why "UTF-8" and not 'UTF-8' ?
The documentation for PRAGMA includes this command: PRAGMA encoding = "UTF-8"; Why is it "UTF-8" and not 'UTF-8' ? Why is it double quotes rather than a normally-delimited string ? Should either of these work ? PRAGMA encoding = UTF-8;PRAGMA encoding = 'UTF-8' ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - macOS
Hi,
On Mon, Sep 23, 2019 at 11:58 AM Pierre Clouthier
wrote:
>
> Can anyone explain how to write UTF-8 in SQLite on the Mac?
>
> We use this statement:
>
> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
You should probably use this inside
#ifdef _WINDOWS #endif
Thank you.
>
> This works fine on Windows, but on macOS the data is not being stored
> correctly.
>
> The data to be written (passed to SQLite), is formatted in UTF-8.
>
> For example, 'é' is U+00E9, which in UTF-8 is 0xC3A9. However, in the
> macOS version of the database, it is converted to 0xEFBFBD, which is
> U+FFFD, which doesn't make sense.
>
> --
> Progeny Genealogy Inc. 902–681–3102
> Progeny helps you tell the Story of Your Family ™
>
>
> ___
> 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] SQLite - macOS
On 23 Sep 2019, at 5:53pm, Pierre Clouthier
wrote:
> Can anyone explain how to write UTF-8 in SQLite on the Mac?
>
> We use this statement:
>
> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
This is not a SQLite problem, but a problem with escaping the quotes in
language you're using to execute sqlite3 calls.
What programming language or script system are you using ? Will it accept any
other way to escape the quote marks ?
It should be possible to find a line of code which works on all platforms your
compiler supports.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - macOS
Not sure what you code is doing, and you aren’t showing how you are getting
your 'é' into SQLite, but U+FFFD is the standard character for malformed data,
so something somewhere is complaining about converting something into UTF-8.
Also, you CAN’T have a UTF-8 value like 0xC3A9, as that is not a byte value.
U+00E9 expressed in UTF-8 would be 0xC3, 0xA9 (Two separate bytes), if you try
to talk about it in some bigger word you are no longer talking UTF-8, and
introducing all sorts of machine specifies (Big vs Little Endian, word size,
etc).
> On Sep 23, 2019, at 12:53 PM, Pierre Clouthier
> wrote:
>
> Can anyone explain how to write UTF-8 in SQLite on the Mac?
>
> We use this statement:
>
> sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
> This works fine on Windows, but on macOS the data is not being stored
> correctly.
>
> The data to be written (passed to SQLite), is formatted in UTF-8.
>
> For example, 'é' is U+00E9, which in UTF-8 is 0xC3A9. However, in the macOS
> version of the database, it is converted to 0xEFBFBD, which is U+FFFD, which
> doesn't make sense.
>
> --
> Progeny Genealogy Inc. 902–681–3102
> Progeny helps you tell the Story of Your Family ™
>
>
> ___
> 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] SQLite - macOS
Can anyone explain how to write UTF-8 in SQLite on the Mac?
We use this statement:
sqlite3_exec("PRAGMA encoding = \"UTF-8\";")
This works fine on Windows, but on macOS the data is not being stored
correctly.
The data to be written (passed to SQLite), is formatted in UTF-8.
For example, 'é' is U+00E9, which in UTF-8 is 0xC3A9. However, in the
macOS version of the database, it is converted to 0xEFBFBD, which is
U+FFFD, which doesn't make sense.
--
Progeny Genealogy Inc. 902–681–3102
Progeny helps you tell the Story of Your Family ™
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] COALESCE() ignores LIMIT 0 clause in subquery?
Fix checked in at https://sqlite.org/src/info/82e5dcf5c1d500ed
On 9/22/19, Justin Ng wrote:
> Is this the appropriate place to discuss this?
>
> The below examples are expected to return 3.
> The first example returns 4, the second returns 3.
>
> It seems like LIMIT 0 is ignored by COALESCE().
> https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/0
> CREATE TABLE "myTable" (
> "myColumn" INT PRIMARY KEY
> );
> INSERT INTO
> "myTable"("myColumn")
> VALUES
> (4);
>
> SELECT
> COALESCE(
> (
> SELECT
> "myTable"."myColumn" AS "myTable--myColumn"
> FROM
> "myTable"
> LIMIT
> 0
> OFFSET
> 0
> ),
> 3
> );
>
> Adding WHERE FALSE does the trick.
> https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/1
> CREATE TABLE "myTable" (
> "myColumn" INT PRIMARY KEY
> );
> INSERT INTO
> "myTable"("myColumn")
> VALUES
> (4);
>
> SELECT
> COALESCE(
> (
> SELECT
> "myTable"."myColumn" AS "myTable--myColumn"
> FROM
> "myTable"
> WHERE
> FALSE
> LIMIT
> 0
> OFFSET
> 0
> ),
> 3
> );
>
>
> ___
> 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
[sqlite] COALESCE() ignores LIMIT 0 clause in subquery?
Is this the appropriate place to discuss this?
The below examples are expected to return 3.
The first example returns 4, the second returns 3.
It seems like LIMIT 0 is ignored by COALESCE().
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/0
CREATE TABLE "myTable" (
"myColumn" INT PRIMARY KEY
);
INSERT INTO
"myTable"("myColumn")
VALUES
(4);
SELECT
COALESCE(
(
SELECT
"myTable"."myColumn" AS "myTable--myColumn"
FROM
"myTable"
LIMIT
0
OFFSET
0
),
3
);
Adding WHERE FALSE does the trick.
https://www.db-fiddle.com/f/7YWZ5naLUfAHgNmh93Yo44/1
CREATE TABLE "myTable" (
"myColumn" INT PRIMARY KEY
);
INSERT INTO
"myTable"("myColumn")
VALUES
(4);
SELECT
COALESCE(
(
SELECT
"myTable"."myColumn" AS "myTable--myColumn"
FROM
"myTable"
WHERE
FALSE
LIMIT
0
OFFSET
0
),
3
);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 tokenize unicode61 does not remove diacritics correctly?
So, how to handle, figure this Uni bug out? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Group-by and order-by-desc does not work as expected
On Sat, Sep 21, 2019 at 10:17 PM Fredrik Larsen wrote: > [...] But fixing issues in less than a day of reporting? [...] > That's not unusual at all for SQLite. Either it gets "fixed" quickly, or it doesn't. The hard part is making the case with Richard (and Dan) about the merit of the change, especially if it's not a bug-fix like here, but a missed optimization. They care a lot about performance, and do try to respond to community input, even though they participate very little in this ML in such request-for-changes threads. If we named SQLite optimizations, that new one would be the Fredrik's :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
