3 ms·

This article is all over the place. Anyway, the author would have been better off writing a query to generate the class definitions directly from the data dict

This article is all over the place. Anyway, the author would have been better off writing a query to generate the class definitions directly from the data dictionary (if available). INFORMATION_SCHEMA (and other proprietary implementations, like the DBA_ views in Oracle or sys views in SQL Server/Sybase) is just perfect for this. An order of magnitude easier than writing a parser.

gmac 4y ago
Yes. Parsing SQL solves some rather specific problems, which the article doesn’t seem terribly clear about. Interrogating the running database to establish schemas and types is exactly what my Postgres/TypeScript library[1] does, for example. [1] https://jawj.github.io/zapatos https://jawj.github.io/zapatos
zasdffaa 4y ago
You've completely missed the point. Author's talking about parsing SQL. That's utterly different from using infoschema to get an existing table structure.
- trollied 4y ago
  No, I have not. The article has no clear direction, hence your comment. The article talks about parsing SQL DDL with the goal of creating classes/structures that represent the tables. You do not need to parse SQL to achieve this. The premise at the start of the article is "parsing SQL so that we can achieve things in code that is not possible in SQL". If that were indeed the case, there would be no such SQL to parse in the first place!
  - zasdffaa 4y ago
    > creating classes/structures that represent the tables AFAICS he's talking about creating AST objects. If the tables don't exist, you can't query the infoschema because the tables don't exist. Someone has to parse the table-definition source code to work out what the tables must eventually look like when turned into structures in the database.
  - chrisjc 4y ago
    Perhaps I missed the point too, but the way I understand the post is that it's demonstrating how to parse DDLs so that a model (AST) of the actions and structures can be extracted. From there an implementation can be defined to create, alter, drop, etc the respective objects and then probably update some kind of manifest that represents the objects and potentially its relationship to others. In most databases that manifest is the INFORMATION_SCHEMA.
gunnarmorling 4y ago
Depending on your specific requirements, querying the data dictionary isn't always sufficient; in particular, when there is a chance for changes to the schema. An example: in case of Debezium's MySQL connector (a change data capture platform), we need to know the table schema in order to interpret incoming change events from the MySQL binlog. Due to the async nature of this process, such event may adhere to an earlier schema version, which is different from how the table looks like in the data dictionary now. For instance, a column's type may have changed between the point in time when that change event was created and now. That's why indeed we also implemented a full MySQL DDL parser for Debezium, as DDL events show up in-stream and thus allow us to react to schema changes at the right moment and update our model of the table structures accordingly. Now, ideally, data dictionaries were revisioned, so that indeed we could query history dictionary entries (with actual change events containing a schema revision id), but I'm not aware of any database which supports that. Touched on this briefly in a talk I did earlier this year for Andy Pavlo's database group at CMU [1]. [1] https://speakerdeck.com/gunnarmorling/open-source-change-data-capture-with-debezium?slide=28 https://speakerdeck.com/gunnarmorling/open-source-change-dat...

gmac 4y ago

Yes. Parsing SQL solves some rather specific problems, which the article doesn’t seem terribly clear about. Interrogating the running database to establish schemas and types is exactly what my Postgres/TypeScript library[1] does, for example. [1] https://jawj.github.io/zapatos https://jawj.github.io/zapatos

zasdffaa 4y ago

You've completely missed the point. Author's talking about parsing SQL. That's utterly different from using infoschema to get an existing table structure.

trollied 4y ago
No, I have not. The article has no clear direction, hence your comment. The article talks about parsing SQL DDL with the goal of creating classes/structures that represent the tables. You do not need to parse SQL to achieve this. The premise at the start of the article is "parsing SQL so that we can achieve things in code that is not possible in SQL". If that were indeed the case, there would be no such SQL to parse in the first place!
- zasdffaa 4y ago
  > creating classes/structures that represent the tables AFAICS he's talking about creating AST objects. If the tables don't exist, you can't query the infoschema because the tables don't exist. Someone has to parse the table-definition source code to work out what the tables must eventually look like when turned into structures in the database.
- chrisjc 4y ago
  Perhaps I missed the point too, but the way I understand the post is that it's demonstrating how to parse DDLs so that a model (AST) of the actions and structures can be extracted. From there an implementation can be defined to create, alter, drop, etc the respective objects and then probably update some kind of manifest that represents the objects and potentially its relationship to others. In most databases that manifest is the INFORMATION_SCHEMA.

gunnarmorling 4y ago

Depending on your specific requirements, querying the data dictionary isn't always sufficient; in particular, when there is a chance for changes to the schema. An example: in case of Debezium's MySQL connector (a change data capture platform), we need to know the table schema in order to interpret incoming change events from the MySQL binlog. Due to the async nature of this process, such event may adhere to an earlier schema version, which is different from how the table looks like in the data dictionary now. For instance, a column's type may have changed between the point in time when that change event was created and now. That's why indeed we also implemented a full MySQL DDL parser for Debezium, as DDL events show up in-stream and thus allow us to react to schema changes at the right moment and update our model of the table structures accordingly. Now, ideally, data dictionaries were revisioned, so that indeed we could query history dictionary entries (with actual change events containing a schema revision id), but I'm not aware of any database which supports that. Touched on this briefly in a talk I did earlier this year for Andy Pavlo's database group at CMU [1]. [1] https://speakerdeck.com/gunnarmorling/open-source-change-data-capture-with-debezium?slide=28 https://speakerdeck.com/gunnarmorling/open-source-change-dat...