sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.3k stars 804 forks source link

Support MariaDB UUID type #3401

Open mpldr opened 5 months ago

mpldr commented 5 months ago

What do you want to change?

MariaDB has some extensions over standard MySQL, most notably a UUID type, which currently leads to:

internal/datastore/mysql/migrations/schema.sql:4:44: syntax error near "uuid NOT NULL DEFAULT (uuid()), `uploaded` datetime NOT NULL DEFAULT (current_timestamp()), `owner` int NOT NULL, PRIMARY KEY (`id`), INDEX `owner` (`owner`), CONSTRAINT `owner` FOREIGN KEY (`owner`) REFERENCES `irori`.`organisations` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;"

It would be nice to have it supported.

The schema:

-- Create "organisations" table
CREATE TABLE `irori`.`organisations` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `description` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- Create "documents" table
CREATE TABLE `irori`.`documents` (`id` uuid NOT NULL DEFAULT (uuid()), `uploaded` datetime NOT NULL DEFAULT (current_timestamp()), `owner` int NOT NULL, PRIMARY KEY (`id`), INDEX `owner` (`owner`), CONSTRAINT `owner` FOREIGN KEY (`owner`) REFERENCES `irori`.`organisations` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- Create "user" table
CREATE TABLE `irori`.`user` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, `token_sub` varchar(128) NOT NULL, PRIMARY KEY (`id`)) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- Create "user_organisations" table
CREATE TABLE `irori`.`user_organisations` (`user` int NOT NULL, `organisation` int NOT NULL, `permissions` int NOT NULL, INDEX `organisation` (`organisation`), INDEX `user` (`user`), CONSTRAINT `organisation` FOREIGN KEY (`organisation`) REFERENCES `irori`.`organisations` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT `user` FOREIGN KEY (`user`) REFERENCES `irori`.`user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

What database engines need to be changed?

MySQL

What programming language backends need to be changed?

Go

mpldr commented 5 months ago

Since this is in a sense an extension to MySQL, this should probably be accompanied by a warning about this type not being available in all server implementations.

mpldr commented 5 months ago

Okay, I've taken a look, and it seems to be something for github.com/pingcap/tidb's parser, which would probably involve a lot of convincing. Any idea on how to do it better?