4 ms·

A C parser for CREATE and ALTER TABLE SQLite statements

58 points by marcobambini 3y ago

kyberias 3y ago
So this is more than just on parser for those statements. Very confusing.
gfody 3y ago
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?
tedunangst 3y ago
The section that says sqlite doesn't support drop column links to documentation that says it's supported?
- simonw 3y ago
  ALTER TABLE DROP COLUMN is pretty new, it was added in https://www.sqlite.org/changes.html#version_3_35_0 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/commit/bdeb8502acf67c29a788824a393d49a6b4350e76#diff-b335630551682c19a781afebcf4d07bf978fb1f8ac04c6bf87428ed5106870f5R142 https://github.com/marcobambini/sqlite-createtable-parser/co...
- breeze1990 3y ago
  It's newly added and even so, it has limited support if you come from e.g. MySQL world.
  - masklinn 3y ago
    In what sense does DROP COLUMN have limited support compared to mysql? Aside from being transactional, which is the opposite of limited.
    - dkjaudyeqooe 3y ago
      "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."
      - masklinn 3y ago
        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.
        evanelias 3y ago
        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.
simonw 3y ago
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 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-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...
- cjohnson318 3y ago
  Porting this to Python is left as an exercise to the reader.
  - masklinn 3y ago
    It's two files, you can probably just cffi it in.
- oefrha 3y ago
  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.
- indigo945 3y ago
  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.
- reportgunner 3y ago
  Have you checked sqlite-utils[0] ? [0] https://sqlite-utils.datasette.io/en/stable/ https://sqlite-utils.datasette.io/en/stable/
crabbone 3y ago
What was the reason for a hand-written lexer and parser vs using Flex / Bison?
- gnulinux 3y ago
  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!
  - jcranmer 3y ago
    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.
    - crabbone 3y ago
      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.

58 points by marcobambini 3y ago

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

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?

tedunangst 3y ago

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

simonw 3y ago
ALTER TABLE DROP COLUMN is pretty new, it was added in https://www.sqlite.org/changes.html#version_3_35_0 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/commit/bdeb8502acf67c29a788824a393d49a6b4350e76#diff-b335630551682c19a781afebcf4d07bf978fb1f8ac04c6bf87428ed5106870f5R142 https://github.com/marcobambini/sqlite-createtable-parser/co...
breeze1990 3y ago
It's newly added and even so, it has limited support if you come from e.g. MySQL world.
- masklinn 3y ago
  In what sense does DROP COLUMN have limited support compared to mysql? Aside from being transactional, which is the opposite of limited.
  - dkjaudyeqooe 3y ago
    "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."
    - masklinn 3y ago
      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.
      - evanelias 3y ago
        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.

simonw 3y ago

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 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-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...

cjohnson318 3y ago
Porting this to Python is left as an exercise to the reader.
- masklinn 3y ago
  It's two files, you can probably just cffi it in.
oefrha 3y ago
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.
indigo945 3y ago
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.
reportgunner 3y ago
Have you checked sqlite-utils[0] ? [0] https://sqlite-utils.datasette.io/en/stable/ https://sqlite-utils.datasette.io/en/stable/

crabbone 3y ago

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

gnulinux 3y ago
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!
- jcranmer 3y ago
  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.
  - crabbone 3y ago
    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.