Import from SQL schema

[7ute]

A good complement would be to be able to import from a SQL schema / dump, the same way one can export to SQL.
It could help bootstraping the creation of a schema, or just visualize an existing database.

[Finestwork]

Heyaaa! Thank you for this suggestion. I will consider and discuss it with the other contributors. 🙏

[7ute]

If that can help, here's a definition parser written in TS that can help extracting the fields/relations from the SQL DDL/dump.
https://github.com/duartealexf/sql-ddl-to-json-schema

[7ute]

Many thanks @DefinitelyNotAnAssassin and @Finestwork for that fast implementation!

Works well one the DDL is cleaned up, but with a fresh dump from PhpMyAdmin, I encountered a couple bugs.
I don't know if you'd rather me open a couple new issues on this repo, but I'll summarize them here.
For each test, you can create a .sql file containing the SQL dumps snippets below :

• When a contraint is named after the foreign key, the relation is not created (here "second_first_id_foreign")

# -- test-fail1.sql - Constraint named after foreign key: Relation not created
CREATE TABLE `first` (`id` bigint(20) UNSIGNED NOT NULL, `label` varchar(255) DEFAULT NULL);
CREATE TABLE `second` (`id` bigint(20) UNSIGNED NOT NULL, `first_id` bigint(20) DEFAULT NULL, `label` varchar(255) DEFAULT NULL);
ALTER TABLE `first` ADD PRIMARY KEY (`id`);
ALTER TABLE `second` ADD PRIMARY KEY (`id`), ADD KEY `second_first_id_foreign` (`first_id`);
ALTER TABLE `second` ADD CONSTRAINT `second_first_id_foreign` FOREIGN KEY (`first_id`) REFERENCES `first` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

• When the constraint doesn't have any action, or is missing the update action, an error is thrown.

# -- test-fail2.sql - Missing OnUpdate action : Error -> TypeError: Cannot read properties of undefined (reading 'action')
CREATE TABLE `first` (`id` bigint(20) UNSIGNED NOT NULL, `label` varchar(255) DEFAULT NULL);
CREATE TABLE `second` (`id` bigint(20) UNSIGNED NOT NULL, `first_id` bigint(20) DEFAULT NULL, `label` varchar(255) DEFAULT NULL);
ALTER TABLE `first` ADD PRIMARY KEY (`id`);
ALTER TABLE `second` ADD PRIMARY KEY (`id`), ADD KEY `second_first_id_foreign` (`first_id`);
ALTER TABLE `second` ADD CONSTRAINT `second_ibfk_17f1cf36` FOREIGN KEY (`first_id`) REFERENCES `first` (`id`) ON DELETE CASCADE;

For this one, it comes from src/renderer/src/utilities/ImportHelper.ts:113 :

constraints: {
    onDelete: fk.reference.on[0].action.toUpperCase(),
    onUpdate: fk.reference.on[1].action.toUpperCase(), /* <~~ action is undefined if the "ON UPDATE" clause is missing */
},

Here's a valid version of the above

# -- test-valid.sql - Valid
CREATE TABLE `first` (`id` bigint(20) UNSIGNED NOT NULL, `label` varchar(255) DEFAULT NULL);
CREATE TABLE `second` (`id` bigint(20) UNSIGNED NOT NULL, `first_id` bigint(20) DEFAULT NULL, `label` varchar(255) DEFAULT NULL);
ALTER TABLE `first` ADD PRIMARY KEY (`id`);
ALTER TABLE `second` ADD PRIMARY KEY (`id`), ADD KEY `second_first_id_foreign` (`first_id`);
ALTER TABLE `second` ADD CONSTRAINT `second_ibfk_17f1cf36` FOREIGN KEY (`first_id`) REFERENCES `first` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;