SQLite Forum

SQLite + TCL on-line conference starts in less than 2 days
Login

SQLite + TCL on-line conference starts in less than 2 days

(1) By Richard Hipp (drh) on 2021-11-15 14:30:13 [link] [source]

The SQLite + TCL conference begins on Wednesday morning (2021-11-17). The conference is on-line (Zoom) and free. Sign-up at https://conf.tcl-lang.org/

I will be leading with a review of the enhancements to SQLite that have occurred over the previous 12 months, followed by a discussion of where we are planning to take SQLite in the future. I'll strive to be brief so as to to reserve plenty of time for Q&A. Looking forward to your feedback.

(2) By anonymous on 2021-11-16 21:03:33 in reply to 1 [source]

I've registered (main interest your speech)

I expected to be notified of (in Zoom parlance) of a Meeting ID or Personal Link Name The email confirming my registration provides neither; it states:

For details, go here:

https://thesqliteandtclconference2021.eventbrite.com

To access the online event page, go here:

https://www.eventbrite.com/x/the-sqlite-tcl-conference-st-2021-registration-*

  • = a large integer, which I've suppressed.

How would I join?

(3) By FlightAware_VKD on 2021-11-17 01:53:52 in reply to 2 [link] [source]

Hi there,

Here is the Zoom link to join the conference on 11/17. 

https://flightaware.zoom.us/j/87246055933?pwd=dkxud0pjem5uTjZkdjMyWk01eTZvdz09

Meeting ID 872 4605 5933

Passcode 489840

Thanks and please let us know if you have any more questions.

(4) By anonymous on 2021-11-17 15:40:33 in reply to 3 [link] [source]

I managed to join & saw Richard H's presentation.

I like the enhancement for STRICT mode with table creation. To me, it means that the data tier takes care of itself.

Question: Do STRICT tables enforce lengths of TEXT columns? That is, can I specify a length for TEXT columns and expect it to be enforced?

However, I am baffled by the inclusion of the ANY data type; to me, it appears to undo all that STRICT promises to deliver.

I notice that Boolean is missing from the data STRICT types - I can live with that since Boolean is held as binary (moist of the time) but at times as literals true and false and I've also seen true represented as -1 (negative 1): the bottom line being that the application code needs to handle this.

What about a USER data type where the developer specifies a pattern for the data (like for email ids, GUID's, numeric codes with hyphen for separator, numbers with leading zeros, fractions etc. etc. ?

(5) By Richard Hipp (drh) on 2021-11-17 16:01:16 in reply to 4 [link] [source]

Do STRICT tables enforce lengths of TEXT columns?

It does not. A question back at you → Why do you think that limiting the length of TEXT columns would be useful? What kinds of problems would that solve?

[T]he ANY data type ... appears to undo all that STRICT promises to deliver.

I do not understand that perspective. Can you explain more about how ANY is detrimental?

(8) By anonymous on 2021-11-17 17:23:59 in reply to 5 [link] [source]

Why do you think that limiting the length of TEXT columns would be useful? What kinds of problems would that solve?

SQLite3 enforcing the length of text columns would prevent (or catch) stray errors arising from

  1. (inherited) application code
  2. data imports (stray leading/trailing spaces, or null terminated values)

and would keep the size of databases smaller.

Can you explain more about how ANY is detrimental?

  1. At present (v3.36) all columns are (in essence) of type ANY, meaning that the application code needs to parse or coerce the underlying columns to their appropriate/expected type. The STRICT tables would remove this overhead unless they contained columns of types ANY, in which case, the parsing/coercion overhead is re-introduced.
  2. I cannot envisage any situation when I would need a column of type ANY in a STRICT table. PS: I realise that I do NOT need to use the ANY type in a STRICT table (and thereby I can bypass the parsing/coercion overhead).

(9) By Michael A. Cleverly (cleverly) on 2021-11-17 17:59:33 in reply to 8 [link] [source]

You can enforce both length and/or no leading or trailing white space with CHECK constraints already today (without using a STRICT table), so I don't see why it wouldn't work for a STRICT one too:

CREATE TABLE foo (
    foo INTEGER PRIMARY KEY,
    bar TEXT CHECK(length(bar) <= 40 AND bar=trim(bar))
);

(10) By Tim Streater (Clothears) on 2021-11-17 18:38:00 in reply to 8 [link] [source]

"and would keep the size of databases smaller."

It wouldn't make a scrap of difference to the database size.

VARCHAR and chums are a holdover from the primitive era of punched cards and fixed record lengths.

(11) By Keith Medcalf (kmedcalf) on 2021-11-17 19:32:52 in reply to 10 [link] [source]

VARCHAR and chums are a holdover from the primitive era of punched cards and fixed record lengths.

Sort of. In the olden days record sizes were fixed. They needed to be because there is no way to access record number 9384378 directly unless the records are fixed length (if they are variable length, you either need a fixed record size index of the starting locations or you need to read all 9384377 preceding records to find the one you want -- if the record length is fixed, it is merely a simple offset calculation).

For this reason "CHAR" and "VARCHAR" required the specification of the maximum length of the field so that the maximum record length (a constant) could be computed in order to permit direct access. "VARCHAR" simply had an "extra couple of bytes" added to the equivalent non-VAR form in order to store the length of the field that was used, the rest was GIGO (random padding provided by happenstance). Every field and record was always a fixed size.

Then along came "INDEXED DIRECT" storage (in the 1960's) which allowed actual variable length records and a "side index" into which the starting offset of each record was stored.

Eventually a number of other file formats replaced BDAM and IDAM organization as the computers and storage media increased speed -- once the CPU and STORAGE were twice as efficient, then twice the amount of time could be "spent" in "fart about locating the data" without affecting percieved performance.

Computing has now progressed to the point where it is thousands or millions of times quicker than it was, so now a huge crapload of time and effort can be consumed "farting around to locate the data" without affecting the perceived performance.

While the prior art will still speed things up from a few millisecond response time to a few nanoseconds of response time, except in very rare circumstance, such efficiency is undetectable by the human bags of mostly water operating the systems -- this is why a program which displays "Hello World" has grown from about 20 bytes to now average just under a gigabyte to achieve the same result in the same amount of time.

(6) By Michael A. Cleverly (cleverly) on 2021-11-17 16:29:37 in reply to 4 [link] [source]

For a USER data type, you can achieve that today with CHECK constraints, especially with REGEXP being part of the standard build (apparently; I hadn't realized that before Richard's presentation).

I like ANY because, 1) it gives you the ability to be STRICT on a per-column basis and not just a per-table basis, and 2) those who don't need/want/like it don't have to use it.

(12) By Keith Medcalf (kmedcalf) on 2021-11-17 19:41:12 in reply to 6 [link] [source]

especially with REGEXP being part of the standard build

REGEXP is not part of the standard build although it is included in the CLI.

It is one of the many things that have been added in the wrong place (IMHO).

(7) By ddevienne on 2021-11-17 16:53:27 in reply to 4 [link] [source]

I managed to join & saw Richard H's presentation.

I didn't. Hopefully a link to a video will be posted eventually.

(13) By Gary (1codedebugger) on 2022-01-23 02:11:16 in reply to 7 [link] [source]

Hello, is there a link to the video? I emailed the contact listed in the event description at flightaware last month but received no response yet. Thank you.

(14) By ThanksRyan on 2022-02-05 16:27:43 in reply to 13 [link] [source]

I don't yet see the 2021 playlist available: https://www.youtube.com/user/videotcltk/playlists