"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.
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.
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-...
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.
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.
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.
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.
21 comments
[ 0.26 ms ] story [ 73.0 ms ] threadThat line about it being missing in the README was added in 2018: https://github.com/marcobambini/sqlite-createtable-parser/co...
Aside from being transactional, which is the opposite of limited.
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.
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.
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-...
[0] https://sqlite-utils.datasette.io/en/stable/
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.
Also, ideally you should never write parsers at all!
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.
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.