Enumerations are purely presentation layer artifacts that should not be forced into the data storage layer.

I disagree with that particular one, FWIW.

Symbolic (integral) constants are present in many languages, and PostgreSQL supports them too (probably others too). They allow for more compact storage, and implicit enforcement of the closed value-set. Probably a tiny bit faster in equality testing too. Sure the fact they can be displayed nicely is about presentation, but it's much more than that, including integrity and conformance to the data-model.

I really wish SQLite had enums.

Finally, this keeps coming up, that somehow the presentation layer is bad, or inferior, or something. An SQLite DB should be able to present itself nicely w/o any custom application, with built-in knowledge of a mapping between some integrals and some human-friendly names for them.

This also matters for SQLite UI that allow to Insert/Update new data. If you emulate enums, via integrals or texts values, that UI can't (easily) infer the closed nature of the value-set, and make the UI friendly and allowing only those values in the UI. That's the difference between not being able to enter an incorrect value, and getting a CHECK constraint violation after the fact, for example.

Anyways, I know I'm not convincing anyone here, that looks down on presentation (wink to Keith :)).

I really wish SQLite had enums.

I emulate them with either a foreign key to a read-only table or a CHECK clause. (I use triggers to enforce the read-only-ness of that table.) Since the underlying natural type is a small integer anyway, SQLite chooses an efficient implementation without special action; getting more efficient would require sub-word packing, but that imposes quite a lot of its own overhead. Since the checks and support tables are small (if your enum has thousands of unique values in it, it isn't much of an enum!), they don't impose a lot of overhead in terms of DB pages held in memory.

Having presentation of those as enums (within a third party UI such as DBeaver) would be nice. Especially the boolean enum. But it sure isn't critical; it definitely wouldn't either increase the expressivity or impose greater integrity requirements.

I may not have a crystal ball, but I don't see this happening, barring some really convincing arguments later.

What actually justifies adding this and then maintaining it for decades to come? Two tools that will quite possibly disappear long before SQLite's backwards compatibility guarantee ends? It doesn't look particularly simpler than regular CREATE TABLE statements (which are also mostly database-agnostic already). Three of your points in the "why?" section can be rewritten as "A couple other things use it" and the other two are rather weak.

Why should SQLite support two separate methods of defining tables? That doesn't sound very lite to me.

How would this actually be used? Would there be a dedicated C API function? Or would it have to read from a .dbml file? Would SQLite just create and then present the DBML to other tools (and/or users)? You didn't go into how this would work at all.

While I don't disagree with the idea, some notes:

1. SQL is already the original "Database Markup Language". You are now attempting to do a markup of a markup, possibly in favour of unifying the diverging SQL dialects.
2. I don't disagree with such unification ideal, but it is best handled by an abstraction layer rather than the DB engine itself. Something you could be spending your energy on, and once it works flawlessly in the wild, SQLite might have no choice to support it.
3. To Dominique's point about calling presentation layer "bad" - No, it's not bad, it's just not the right tool for the job.

As to your points on "why":

It is simple, flexible and highly human-readable

So is SQL

It is database agnostic, focusing on the essential database structure definition without worrying about the detailed syntaxes of each database

So was SQL originally, but lack of a fully complete standard, leaving a lot open to interpretation (to this day) had seen many dialects born, how will this be prevented with this new markup?

Comes with a free, simple database visualiser at dbdiagram.io Also comes with a free database documentation app at dbdocs.io

There are thousands of free DB softwares out there, including visualizers.

This could bring 3 communities closer in the sense of friendship: SQLite, dbdiagram, DBML - which allows DBML and Sqlite to be more used - in the commercial sense of the word - the advantage here is at the market strategic level, from the greater integration of different products with sqlite

SQLite is already the most used Database Engine in the World, and that's even without "market strategic level" anything and integrations with SQLite is already handled well by anything that cares via the state of the art API.
The only benefit here in terms of market adoption is to DBML and all the work falls to SQLite. I'm sorry but using "friendship", "commercial" and "market strategy" in the same sentence sounds a lot like the promises my Nigerian aunt (whom I never knew existed, much like DBML) made by email after my uncle died and left a fortune in his will.
I'm surprised it got past my spam filter.

Ryan, regarding multiple competing standards -- that horse is already past the post.

https://xkcd.com/927/

I think that it should not be and need not be a part of SQLite itself, but you can make a separate program that can use this format with SQLite, if you think that this is useful. There is no need to be supported internally.

I understand it is important to keep the core of SQLite small. I also understand that SQLite uses the Lemon Parser Generator - although I have no real knowledge or experience of using it.

Here's my question: Could the Lemon Parser be made available from the sqlite3 commandline?

I mean: could a grammar file be provided (in this case for DBML) to sqlite3 at runtime for it to dynamically take a grammar file + input.dbml file to turn that into SQL?

My guess is not: since Lemon (like ANTLR - which I do happen to have some basic experience of) - it will generate code which then needs to be compiled; and lemon itself is not contained within the sqlite code either.

What I'm getting at: maybe there is a cheap (in terms of the size of the SQLite core, not in terms of development or testing necessarily) enhancement request to be had here?

That is: allow a 'hook' that takes 'x' language + a Lemon-defined 'x-grammar' file - to output SQL either as text, or for immediate execution as an alternative language to SQL.

Fair enough. But to be clear: I meant DBML->SQL - via lemon - just because it is a parser generator. (Again, I don't know Lemon - but presumably it goes via an AST of some sort - which could then used to generate SQL).

I didn't mean to go DBML->ByteCode; so it wouldn't byte-code hacking in that sense - rather a DSL->SQL translation.

And I take your point that DBML->SQLite SQL might not be possible either.

My guess is that lemon:

1. Is not shipped at runtime with SQLite.
2. Is designed to build C, rather than dynamically parse x->y

So would be a non-starter in any case.

I see there is already a tool to convert dbml to sql called dbml2sql So this would be re-inventing the wheel in any case.

And if the output of "dbml2sql" has cases where the generated DDL is not compatible with SQLite for any reason - then (probably) it would be better to enhance that tool with (say) a '--sqlite' flag to warn/fix that.

(Again, I don't know Lemon - but presumably it goes via an AST of some sort - which could then used to generate SQL).

lemon doesn't generate anything in terms of output structures. It simply triggers client-supplied code as tokens are parsed, and it's up to that code to generate whatever it wants.

Is not shipped at runtime with SQLite.

It's not. It's one of the tools used by the sqlite build process: https://sqlite.org/lemon.html processes this grammar file to generate sqlite3's main parser.