A C parser for CREATE and ALTER TABLE SQLite statements | Hacker News Reader

A C parser for CREATE and ALTER TABLE SQLite statements

github.com

58 pointsmarcobambini21 comments

Huh, weirdly enough I've been hoping for exactly this kind of thing - albeit easily called from Python.

My use-case is that with SQLite there are aspects of the table - things like generated columns - which aren't yet available via the regular SQLite PRAGMAs for introspecting tables.

For those things, the only way to introspect them is to read the CREATE TABLE statement out of the "sqlite_master" table and parse it.

But you need a very robust parser!

I've been investigating tree-sitter recently for this, notes here: https://til.simonwillison.net/python/tree-sitter

If anyone needs an implementation of that advanced ALTER TABLE mechanism (where you create a new table with the new schema, copy the data cross from the old one and then swap the names) my sqlite-utils CLI tool and Python library implements that, as described here: https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...

Porting this to Python is left as an exercise to the reader.

It's two files, you can probably just cffi it in.

An SQLite extension could be implemented on top of this parser, converting the C functions into SQLite functions. Then it could be fairly easily called from any language.

At that point, it seems like it might be faster to just fork SQLite and add the PRAGMA that you need, instead of duplicating a part of SQLite's functionality in a non-future-proof way.

Have you checked sqlite-utils[0] ?

[0] https://sqlite-utils.datasette.io/en/stable/

simonw wrote sqlite-utils, so I’m sure he has checked it. It’s also mentioned in gp.

> But you need a very robust parser!

Making it robust should not be hard, should it? In general, the difficult part of parsing is the handling of erroneous input: generating good error messages and error recovery (not getting into the woods after seeing one error). This parser only has to parse valid SQL, and one statement at a time, so it needs neither.

don't you need a full parse even just for create and alter statements since you could always encounter an "as" followed by arbitrarily complex sql?

What was the reason for a hand-written lexer and parser vs using Flex / Bison?

I say this all the time. As someone who wrote many many parsers before, flex/bison is not worth it. Parsing is a solved problem and -- depending on your style -- nothing other than recursive descent or parsing combinators is worth it, imho. Always hand write your parsers.

Also, ideally you should never write parsers at all!

Flex and bison don't really give you much advantage over hand-writing your parser, and they have an interface which is actually kind of painful to use (they assume that your program will only have a single parser, ever, and everything is global variables with names like yytable). Virtually the entire infrastructure you have to provide yourself--you don't even have the ability to get autogenerated token enums or AST trees, so if your lexer is pretty close to trivial, it's more work to get it working with flex.

Note that some other parser frameworks do provide some more functionality that may tip the calculation for the worth in other directions--I've used the Python-based ply parser generator a couple of times, though I'd still prefer a framework that really did location tracking (i.e., file:line:col information) for you out of the box.

Are you kidding me "not much advantage"?

You write mountains of C code where you manipulate arrays of characters every now and then interpreting them as strings. This is like one of the most dangerous things you could do in C, and will take you forever to write and to iron out all the cases where you accidentally read past the missing null terminator. And you are liberated from this madness by using a generator.

It's not even about the quality of the resulting parser (it's also rare that parsing is the bottleneck in larger programs that need to parse something, so the question of shift-reduces vs recursive etc. aren't worth answering really). It's more about confidence that the program won't crash on a different computer with different memory allocator.

> everything is global variables

This is like information from the 90s... That's not the case for a long time. You can still do it like this, but re-entrant parsers have been a thing for a long time... I've written multithreaded parsers using Flex / Bison.

Again, don't get me wrong, I'm not saying that Flex / Bison are the best combo. I prefer DCGs because it's easier for me to think in that way, for example. But the benefit of having popular tools with decades of track record and easily available outweighs the benefits of hand-written parser, unless you are somehow very good and into writing parsers. The code in question doesn't show any such ability / intention. To the point that I was questioning if the code may have been generated and later "prettyfied" by hand. I mean, it's not awful, but it's not like it's doing it in some sort of a remarkable way... It just looks like a lot of tedious labor.

The section that says sqlite doesn't support drop column links to documentation that says it's supported?

ALTER TABLE DROP COLUMN is pretty new, it was added in https://www.sqlite.org/changes.html#version_3_35_0 3.35.0 in March 2021.

That line about it being missing in the README was added in 2018: https://github.com/marcobambini/sqlite-createtable-parser/co...

It's newly added and even so, it has limited support if you come from e.g. MySQL world.

In what sense does DROP COLUMN have limited support compared to mysql?

Aside from being transactional, which is the opposite of limited.

"SQLite stores the schema as plain text in the sqlite_schema table. The DROP COLUMN command (and all of the other variations of ALTER TABLE as well) modify that text and then attempt to reparse the entire schema. The command is only successful if the schema is still valid after the text has been modified. In the case of the DROP COLUMN command, the only text modified is that the column definition is removed from the CREATE TABLE statement. The DROP COLUMN command will fail if there are any traces of the column in other parts of the schema that will prevent the schema from parsing after the CREATE TABLE statement has been modified."

You can't drop a column which is still in use, I fail to see the issue with that.

I might see lamenting the lack of CASCADE but I've come to be less than fond of that in postgres, I don't consider that a feature.

And since mysql does not have DROP COLUMN ... CASCADE does it just... destroy any dependent when you try to drop a column being depended on? That sounds like the opposite of a feature.

No, that's not how it works. If the column is used in a foreign key constraint, MySQL will prevent you from dropping the column. In that case, the ALTER statement will immediately fail with an error and take no action.

Otherwise, assuming there's no FK and it proceeds:

If the dropped column is used in any indexes, the index definitions are adjusted to no longer be keyed on that column. And if there's a single-column index on that dropped column, the index is also dropped. It's pretty much what you would always want to happen, without forcing you to needlessly indicate how the indexes need to be adjusted.

It doesn't automatically adjust any views, triggers, procs, funcs, etc that reference the column though.

So this is more than just on parser for those statements. Very confusing.

Reply on news.ycombinator.com