A layer over SQL [was Meaning of a relation]
The key TTM principles are:
- a value-based extensible type system
- operators on those types, including comparisons
- composable queries based on the (extended) RA
- updates by relvar MA
- no 'SQL nulls'; nulls as typed values are OK
Othr principles include:
- real and virtual relvars
- database and catalog
- transactions
- constraints.
IMO it is possible to implement this as a non-leaky 'layer over SQL' provided:
- The database is constructed and maintained to conform with the principles
- The type system is replicated as value types in the host language
- Operators are limited to those that can be implemented in SQL
- MA is expressed as shortcuts that can be implemented in SQL.
I know, because I've done it in Andl.
The typical ORM has two jobs: type conversion and SQL generation. It is often found in a space where the database, the ORM and other access paths are owned by different competing interests, and where there is history and sunk cost. I can't solve the political problems, but as a technical de novo approach this would work.
Incidentally, I can't believe we haven't solved the AI problem of DDL and SQL generation. Starting with a pure abstraction such as this one should make it far more possible.
Quote from dandl on January 24, 2021, 12:32 amThe key TTM principles are:
- a value-based extensible type system
- operators on those types, including comparisons
- composable queries based on the (extended) RA
- updates by relvar MA
- no 'SQL nulls'; nulls as typed values are OK
Othr principles include:
- real and virtual relvars
- database and catalog
- transactions
- constraints.
IMO it is possible to implement this as a non-leaky 'layer over SQL' provided:
- The database is constructed and maintained to conform with the principles
- The type system is replicated as value types in the host language
- Operators are limited to those that can be implemented in SQL
- MA is expressed as shortcuts that can be implemented in SQL.
I know, because I've done it in Andl.
That's what D4's Alpha did/does too, as I recall.
Yes, the creation of a stand-alone language is one approach.
ORMs are another approach, which often create a new sublanguage within the host language or use a provided one, like LINQ-to-SQL with Entity Framework.
The typical ORM has two jobs: type conversion and SQL generation. It is often found in a space where the database, the ORM and other access paths are owned by different competing interests, and where there is history and sunk cost. I can't solve the political problems, but as a technical de novo approach this would work.
Yes, it does work.
My "SQL amplifier" approach is rather less ambitious, in that it's not trying to replace SQL with TTM principles, or any guiding principle other than a pragmatic goal of making it easier to use SQL effectively, without the pain of using an ORM and without the pain of not using an ORM.
Incidentally, I can't believe we haven't solved the AI problem of DDL and SQL generation. Starting with a pure abstraction such as this one should make it far more possible.
You may be overestimating what current AI can do. My academic work toward the end of my stint in academia tended to be a bit more involved with AI and machine learning than databases and such, so I have a little "insider" knowledge of the area. Much of what gets reported is more gimmickry and toys (and hype, and misunderstandings) than genuinely useful.
The genuinely useful outputs are relatively un-notable (they're about as much fun as reporting the results of applying statistical models, which they essentially are) and tend to involve automated pattern recognition (where the patterns to be recognised can be "trained" instead of explicitly coded) and a (much less useful, at least currently) converse of pattern generation.
Truly generative AI -- emitting quality code from requirements, for example -- is still a long range goal that's had little success so far, despite industry/media hype that sometimes suggests otherwise.
Also, it's not really an AI problem. It's more of a compiler/transpiler problem.
Yes, the creation of a stand-alone language is one approach.
ORMs are another approach, which often create a new sublanguage within the host language or use a provided one, like LINQ-to-SQL with Entity Framework.
No, I am specifically spelling out the TTM features that might be drawn upon in a 'layer over SQL' that did not expose SQL at the application level. I'm not proposing a language or defending an ORM, just setting out some goals.
You may be overestimating what current AI can do. My academic work toward the end of my stint in academia tended to be a bit more involved with AI and machine learning than databases and such, so I have a little "insider" knowledge of the area. Much of what gets reported is more gimmickry and toys (and hype, and misunderstandings) than genuinely useful.
Yes, I know just how limited current AI is, so take that is a bit tongue in cheek. But please note: it's not just about ANN and ML: custom AI engines now beat humans at virtually every board game. There are lots of ways to skin this cat.
What I intended to say is: don't export the SQL dialect and performance peculiarities to the application level. Smarten up the SQL code generation so it can take a set of application queries expressed in some form other than SQL and generate best possible code for the platform; and then have it look at how those queries are going and try different rewrites continuously to generate better SQL (DDL and DML) as the data content changes. I see no reason why an SQL-AI should not easily beat the best humans at that game.
Actually, come to think of it, that's a real commercial product if it's possible.
Quote from dandl on January 25, 2021, 3:37 amYes, the creation of a stand-alone language is one approach.
ORMs are another approach, which often create a new sublanguage within the host language or use a provided one, like LINQ-to-SQL with Entity Framework.
No, I am specifically spelling out the TTM features that might be drawn upon in a 'layer over SQL' that did not expose SQL at the application level. I'm not proposing a language or defending an ORM, just setting out some goals.
You may be overestimating what current AI can do. My academic work toward the end of my stint in academia tended to be a bit more involved with AI and machine learning than databases and such, so I have a little "insider" knowledge of the area. Much of what gets reported is more gimmickry and toys (and hype, and misunderstandings) than genuinely useful.
Yes, I know just how limited current AI is, so take that is a bit tongue in cheek. But please note: it's not just about ANN and ML: custom AI engines now beat humans at virtually every board game. There are lots of ways to skin this cat.
What I intended to say is: don't export the SQL dialect and performance peculiarities to the application level. Smarten up the SQL code generation so it can take a set of application queries expressed in some form other than SQL and generate best possible code for the platform; and then have it look at how those queries are going and try different rewrites continuously to generate better SQL (DDL and DML) as the data content changes. I see no reason why an SQL-AI should not easily beat the best humans at that game.
Actually, come to think of it, that's a real commercial product if it's possible.
Some years ago, I supervised several dissertation students to develop projects to do the simplest form of that notion, which is to dynamically create and drop indexes in response to data content changes.
It worked, for a reasonable definition of "worked", but wasn't difficult to fool into doing less-than-optimal things.
But there may indeed be some commercial value in a dynamic, "machine learning" SQL generator as an ORM internal. Of course, that's what the DBMS query optimiser is supposed to do itself. You're supposed to be able to express a SQL query how ever you like and the query optimiser should evaluate it optimally.