Quote from AntC on December 27, 2022, 8:34 pm

For testing purposes, here's a pair of Paradise Sheldrake in my local park. (Terrible photo, but Hugh might appreciate the rarity value. It's a .jpg)

 

 

Quote from AntC on December 28, 2022, 12:51 am

Quote from dandl on December 27, 2022, 1:04 am

The internal failings are:

1. ...
2. Hostility to NULL, to the point of making SQL look good (for handling missing data, outer joins, etc)
3. Hostility to ordered data sets, to the point of making SQL look good (for handling reporting, aggregation, running totals, etc).

Hmm I wonder if @dandl really is also looking for an alternative discussion forum -- one where you can admire (sic!) how SQL handles NULL. I imagine a lot of conversations along the lines

"I see nobody on the road." said Alice.
"I only wish I had such eyes," the King remarked in a fretful tone. "To be able to see Nobody! And at such a distance too!”

===

“Who did you pass on the road?" the King went on, holding out his hand to the Messenger for some more hay.
"Nobody," said the Messenger.
"Quite right," said the King; "this young lady saw him too. So of course Nobody walks slower than you."
"I do my best," the Messenger said in a sullen tone. "I'm sure nobody walks much faster than I do!"
"He can't do that," said the King, "or else he'd have been here first.”

For me, the main attraction of TTM is that it rejects Nullism and all its pomps. Pretty much all my SQL career I'd hated Nulls. When I found TTM, it was like coming home.

(@dandl's objections re ordering I don't understand. Tutorial D supports ordering at the appropriate place -- at the presentation to the user. You want to store running totals and aggregations in the database? Well, maybe a online banking system does for quickly presenting an account enquiry. But I doubt the bank's 'real'/backroom system does that. I hear SBF has recent experience on trends in (not) presenting account liquidity.)

Quote from dandl on December 28, 2022, 1:53 am

Quote from Dave Voorhis on December 27, 2022, 6:10 pm

Quote from dandl on December 27, 2022, 12:56 pm

The strength of a TTM-inspired SQL substitute lies where the data is not and cannot be server-based, and clusters of things may well lie in that domain. But as I said the lack of a hostable type system

If a D is an external sublanguage (like SQL), type-compatibility only needs type-transferability, so values of TypeX in a D can become values of TypeY in the programming language. That's how SQL is usually handled.

If a D is the programming language, then it can specify any type system it likes.

It's only when you want to implement D semantics in some non-D language that contradictions are encountered. But is that a problem, or simply a point where compromise is to be expected?

The first two options are not under consideration. SQL stays, and no-one has take any interest in a whole new language of this kind. But your third option is defined too narrowly.

The only reasonable target would be to add relational features to an existing language, either by extending the language or by finding a compromise type system. As I showed previously, it is possible to add a full implementation of the 9 operators of the fully extended RM to a language such as Java, C#, Python or JS with very small extensions. The key features are:

1. The type system is value types as provided by the host language (so there are no nulls). Compiler may need help.
2. All relations are of type Relation, parameterised on Heading.
3. The compiler is extended to support Heading as a special internal type, and does compile-time checking on Heading (existing common GP languages cannot do this)
4. The relational operators are simple library functions with Relation, Heading and other argument types.
5. Forget about tuples, YAGNI.
6. [The 9 operators are Select, Project, Join, Rename, Union, Negate, Extend, Aggregate, Transitive Closure]

and features akin to NULL

NULLs are abominations everywhere they appear. Modern programming languages shouldn't have them, and it's only legacy compatibility that justifies their retention. At least we can turn them into option types at the point where a D interfaces with a SQL database. There is a good argument for defining a D (or post-D?) type system to have algebraic data types, though.

I carefully did not propose NULL itself, but new TTM needs a solid answer to questions for which the SQL answer is NULL. Missing data? Outer joins?

and ORDER BY are a huge barrier for TTM as it stands.

Tutorial D defines (and Rel simplifies) ORDER, and arguably at the right place -- as a terminal of a relational expression, where it becomes an ordered array. An "ordered relation" is unnecessary.

I have never proposed ordered relations, but new TTM needs needs a solid answer to questions for which the SQL answer is ORDER BY, and ARRAY in TD is not it. My preference is similar to SQL: ordered queries based on an ordering function that return an ordered list in the host language.

I'm using Edge this time, so let's see if it works any better.

Nope. I do choose file, pick a JPG, then Submit. Is there a  step I'm missing?

That should work. Per my posts above, I've installed some updates and tested it on a non-admin account, which works.

Please try again. If it still doesn't work, I'll do some digging.

Will do. Separate thread.

Quote from Hugh on December 28, 2022, 12:13 pm

Quote from AntC on December 27, 2022, 8:34 pm

For testing purposes, here's a pair of Paradise Sheldrake in my local park. (Terrible photo, but Hugh might appreciate the rarity value. It's a .jpg)

 

 

Many thanks, Antc.  Paradise Shelducks are very familiar to me, thanks to my frequent visits to NZ (which seem to have ended now, alas).  They are endemic to your islands.

Quote from Dave Voorhis on December 28, 2022, 2:49 pm

Quote from dandl on December 28, 2022, 1:53 am

Quote from Dave Voorhis on December 27, 2022, 6:10 pm

Quote from dandl on December 27, 2022, 12:56 pm

The strength of a TTM-inspired SQL substitute lies where the data is not and cannot be server-based, and clusters of things may well lie in that domain. But as I said the lack of a hostable type system

If a D is an external sublanguage (like SQL), type-compatibility only needs type-transferability, so values of TypeX in a D can become values of TypeY in the programming language. That's how SQL is usually handled.

If a D is the programming language, then it can specify any type system it likes.

It's only when you want to implement D semantics in some non-D language that contradictions are encountered. But is that a problem, or simply a point where compromise is to be expected?

... But your third option is defined too narrowly.

How so?

The only reasonable target would be to add relational features to an existing language, either by extending the language or by finding a compromise type system.

It's a reasonable target for achieving a LINQ-like/Streams-like facility based on a relational model, but "finding a compromise" -- whether in the type system or elsewhere -- is what I wrote, isn't it?

As I showed previously, it is possible to add a full implementation of the 9 operators of the fully extended RM to a language such as Java, C#, Python or JS with very small extensions. The key features are:

1. The type system is value types as provided by the host language (so there are no nulls). Compiler may need help.
2. All relations are of type Relation, parameterised on Heading.
3. The compiler is extended to support Heading as a special internal type, and does compile-time checking on Heading (existing common GP languages cannot do this)
4. The relational operators are simple library functions with Relation, Heading and other argument types.
5. Forget about tuples, YAGNI.
6. [The 9 operators are Select, Project, Join, Rename, Union, Negate, Extend, Aggregate, Transitive Closure]

Yes, I recall it would need a type-aware shim or other external program to generate code and/or enforce static guarantees.

and features akin to NULL

NULLs are abominations everywhere they appear. Modern programming languages shouldn't have them, and it's only legacy compatibility that justifies their retention. At least we can turn them into option types at the point where a D interfaces with a SQL database. There is a good argument for defining a D (or post-D?) type system to have algebraic data types, though.

I carefully did not propose NULL itself, but new TTM needs a solid answer to questions for which the SQL answer is NULL. Missing data? Outer joins?

For handling missing data in a manner loosely akin to SQL NULL without its pitfalls, option types or zero-or-one relations are fine.

In programming languages, option types are the usual solution to handling missing (or uninitialised) data without the usual NULL pitfalls.

Outer joins can be handled with option types or structural approaches. I used the latter in Rel, giving

p LEFT JOIN q which returns TUPLE {Matched p JOIN q, Missing p NOT MATCHING q}

p RIGHT JOIN q which returns TUPLE {Matched p JOIN q, Missing q NOT MATCHING p}

p FULL JOIN q which returns

      TUPLE {

        Matched p JOIN q, 

        MissingLeft p NOT MATCHING q, 

        MissingRight q NOT MATCHING p

      }

As with all things, there are tradeoffs here too. But NULL?

No.

and ORDER BY are a huge barrier for TTM as it stands.

Tutorial D defines (and Rel simplifies) ORDER, and arguably at the right place -- as a terminal of a relational expression, where it becomes an ordered array. An "ordered relation" is unnecessary.

I have never proposed ordered relations, but new TTM needs needs a solid answer to questions for which the SQL answer is ORDER BY, and ARRAY in TD is not it.

ARRAY in Tutorial D is given pedagogical emphasis to make it clear that the ordered construct is not a relation. That can be hidden, of course, so that ORDER is merely restricted to being the terminal of a relational expression, and returns an ordered construct (which we can call an array) which must be converted back into a relation to be used in a relational expression.

And there's RANK.

My preference is similar to SQL: ordered queries based on an ordering function that return an ordered list in the host language.

Whether based on an ordering function, or EXTEND is used to apply an ordering function to a relation and the new attribute is ORDERed by name, is an ergonomic difference rather than a conceptual one.

DBMS interfaces to host languages generally obtain an order-preserving "result set" from the DBMS. Though "result set" is a bad name; it's more akin to Tutorial D's ARRAY.

Quote from AntC on December 29, 2022, 12:39 am

Quote from Hugh on December 28, 2022, 12:13 pm

Quote from AntC on December 27, 2022, 8:34 pm

For testing purposes, here's a pair of Paradise Sheldrake in my local park. (Terrible photo, but Hugh might appreciate the rarity value. It's a .jpg)

 

Many thanks, Antc.  Paradise Shelducks are very familiar to me, thanks to my frequent visits to NZ (which seem to have ended now, alas).  They are endemic to your islands.

Sadly they're under habitat threat. I used to see them only in the high meadows/streams amongst the Beech forests. Since they're ground-nesting, their eggs and hatchlings are taken by stoats and especially possums. So it's now very common to find them nesting in urban parks. (The park of the photo has a anti-possum-fenced piece of preserved native bush.) Never the less, such an unfamiliar environment must put the birds under duress, with domestic cats, dogs, pedestrians and bicycles. I didn't see that pair produce any chicks this year.

Quote from AntC on December 29, 2022, 12:55 am

Quote from dandl on December 28, 2022, 1:53 am

I carefully did not propose NULL itself, but new TTM needs a solid answer to questions for which the SQL answer is NULL. Missing data? Outer joins?

I'm bemused how a data environment that supported NULL-likes and (presumably) auto-inserted them into Outer joins would differ from SQL. You want cleaner syntax? But TTM "does not prescribe syntax". And anyway most programming interacts via "a LINQ-like/Streams-like facility", as you say; so most programmers are insulated from SQL's ghastly baroque syntax.

What are the benefits of whatever you propose over SQL itself; and what would be persuasive enough for the whole discipline (or a significant sub-sector) to 'retool' to it? The history of the industry is littered with db programming tools/front ends, trying to hide the awfulness of SQL. Most have just died out.

I retain a fondness for System/38, AS/400's db system -- which didn't include NULL, let me point out, and its query language was much more concise. But over the decades IBM introduced a SQL-compatible programming interface (which wasn't very compatible at first because it had a kludge to simulate NULL); then quietly phased out 'native' db access.

Quote from dandl on December 29, 2022, 4:53 am

You really don't read what I write, do you? To make it perfectly clear: I do not and never have proposed an SQL-like NULL. I do say that you need a cogent answer to those familiar with SQL and accustomed to using NULL to solve certain problems. Right now you don't have that, or at least you haven't made it clear what that answer might be.

TTM and the XRM I propose have only value types, which by their nature do not have a null. Some value types have a natural 'no value' (eg datetime, text, blob, relation, enum) . Some do not (boolean, integer, decimal, float). Embedding them into a 'maybe' type might be a solution. Regardless, it's an important aspect of the type system, not the XRM itself.

The benefits are as I said previously: to apply RM/SQL-like data manipulations and queries to data that is not and cannot or should not be stored in a server-side database. As things stand now it is never going to be an alternative to SQL, but it just might be preferred over Linq/Streams given a suitable context.

Quote from AntC on December 29, 2022, 7:28 am

Quote from dandl on December 29, 2022, 4:53 am

You really don't read what I write, do you?

et in terra pax hominibus, bonae voluntatis to you too.

To make it perfectly clear:

Yes please do.

I do not and never have proposed an SQL-like NULL.

Then I suggest you don't use that term at all. Dave also has reacted against the term. NULL is so intimately wound up within SQL, I just don't know what you might mean by a NULL that is not "an SQL-like NULL". For example if I go ... WHERE YourNotNULL == YourNotNULL -- a) is that allowed?; b) does it return a half-defined value (but not FALSE) such that the query will not return any rows, or what?

Also I suggest you don't use the term Outer Join, because that's just as much wound up with SQL's NULL.

Note that for an Option type ... WHERE Nothing == Nothing is allowed, and evaluates simply to TRUE so will return all rows.

I do say that you need a cogent answer to those familiar with SQL and accustomed to using NULL to solve certain problems. Right now you don't have that, or at least you haven't made it clear what that answer might be.

So those extensions Dave showed in Rel make a clear possible answer. They seem entirely natural; I'd expect any TTM-compliant query language to achieve similar. They don't use an Option type, they don't introduce anything NULL-like.

TTM and the XRM I propose have only value types, which by their nature do not have a null. Some value types have a natural 'no value' (eg datetime, text, blob, relation, enum) .

Hmm, again I'm not following: what is the/a "natural 'no value'" for those types? For text I suppose you mean an empty string(?). For enum you want to use the least value(?) -- I don't see anything "natural" in that choice. Furthermore Boolean is an enumerated type, yet you don't think there's a 'no value'. I'm confused.

 

Some do not (boolean, integer, decimal, float). Embedding them into a 'maybe' type might be a solution. Regardless, it's an important aspect of the type system, not the XRM itself.

One of the ugly bits of SQL's Outer Join is that even if the base relation declares a column not Nullable, it (I mean a column with the same name and table prefix as the base) magically becomes Nullable in the result. I'd call that an implicit type coercion. What's worse, the coercion doesn't apply if that column is mentioned in the ON ...  column list for joining. It seems to me an equal/worse 'wot?' if a column magically becomes a Maybe type for some queries but not others. So I prefer the approach Dave presented. (Although really I don't like Outer Join at all.)