Parse the schema when there are INSERT statements

[jmbockhorst]

The schema should be able to be parsed even where there are INSERT statements present. Currently, it crashes when there are any, but it should be able to ignore these statements are parse the rest of the schema.

[duartealexf]

Hi @jmbockhorst currently the lib only parses DDL statements. DML statements are not recognized. I suppose I could ignore them but it's not that simple (but you are welcome to contribute 😉).

What is your use case?

[jmbockhorst]

I am building a vscode extension with a custom editor that lets you create and update sql schema files using an interactive diagram editor. I want it to be able to parse SQL files even if there are insert statements in them (I tend to have an init script have both the schema and initial data in them). So I want it to still parse the rest of the schema. I would be willing to contribute as I didn't see any other library for doing this.

I also would also want to be able to manipulate the json object and write it back to a SQL file string. How feasible would this be in the library's current setup? (Again, I would be willing to contribute for this)

[duartealexf]

Having such a VSCode extension would be awesome!
I realised there are no other libraries doing this... that's why I created it :) The purpose of creating this parser was to allow creating tools like the one you are making and to also (eventually) bootstrap projects/output code. It's nice to know everyone is on the same page :)

Anyways... have a look at this extension... it uses this parser as well.

Right, so thanks for being open to contribute.
If you want to ignore DML statements, see the steps below:

• There's a pre-parser that reads the SQL and splits them into statements. It keeps a line count to output any parse errors in the correct SQL line.
• To ignore DML statements, one would remove them from the statements array, or just not deliver them to the Nearley parser at all. Doing so should not change the parse errors line number, which is adjusted as the statements are Nearley-parsed.

I've never thought about having an SQL output, from a JSON object (what format?). I believe such scope would belong to a separate project, since this is only about taking SQL DDL and output JSON Schema (although there are intermediate formats).

Cheers

[jmbockhorst]

The idea would be along the lines of that other extension, but you would be able to directly edit the ERD diagram and it would reflect in the SQL script. The format of the output would just be a string that could be written back into the vscode TextDocument.

[jmbockhorst]

Maybe we could use something like this: https://github.com/tjvr/nearley-reverse.

[duartealexf]

Maybe... it could work but I believe it's a simpler to transform the compact JSON format to SQL, since it has information that directly reflects the parsed SQL. The JSON Schema would not be optimal because it omits some information (due to JSON Schema being a lot simpler than the SQL).

Using nearley-reverse would require having the parsed AST format back from the compact JSON, which would create some extra steps along the way, but again, it would work.

I thought about it again and I believe it would make sense to have the SQL output in this lib.

Again, feel free to contribute and let me know if you have any questions.

[duartealexf]

Hey @jmbockhorst I will be closing this for now. Let me know if you need any help contributing.