Directly execute SQL (create table, insert into fields...) to create a collection and modify fields

Help
1 
/*
  数据集表: organizations
  用途：存储机构元信息
	
  部分字段需要在 Directus 中进行配置
	- 关系
		m2o
			parent 父级机构
	- 文件
		picture
			logo LOGO图片
*/
CREATE TABLE `organizations` (
	`id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`organization_id` int(10) unsigned NOT NULL,
  `lang` varchar(10) COLLATE utf8mb4_bin NOT NULL DEFAULT 'cn' COMMENT '语言 Dropdown',
	`name` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
	`slug` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '别名',
	`parent` int(10) unsigned DEFAULT NULL COMMENT '父级机构',
	`description` TEXT COLLATE utf8mb4_bin COMMENT '描述',
	`logo` char(36) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'LOGO',
	`logo_option` json DEFAULT NULL COMMENT 'Logo 选项 Checkboxes',
	`website` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '机构网站',
	`name_full_cn` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '中文全称',
	`name_full_en` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '英文全称',
	`link` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL,
	`count` INT (10) UNSIGNED DEFAULT '0',
	`taxonomy` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT 'organization',
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_organization_lang` (`organization_id`,`lang`),
	KEY `organizations_parent_foreign` (`parent`),
  KEY `organizations_logo_foreign` (`logo`),
  CONSTRAINT `organizations_logo_foreign` FOREIGN KEY (`logo`) REFERENCES `directus_files` (`id`) ON DELETE SET NULL,
  CONSTRAINT `organizations_parent_foreign` FOREIGN KEY (`parent`) REFERENCES `organizations` (`id`) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin;

INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'id', NULL, NULL, NULL, NULL, NULL, 0, 1, 1, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'organization_id', NULL, 'input', '{\"min\": 1}', NULL, NULL, 0, 0, 2, 'half', NULL, '机构 ID', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'lang', NULL, 'select-dropdown', '{\"choices\": [{\"text\": \"中文\", \"value\": \"cn\"}, {\"text\": \"英文\", \"value\": \"en\"}]}', 'labels', '{\"choices\": [{\"text\": \"中文\", \"value\": \"cn\"}, {\"text\": \"英文\", \"value\": \"en\"}]}', 0, 0, 3, 'half', NULL, '语言', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name', NULL, NULL, NULL, NULL, NULL, 0, 0, 4, 'full', NULL, '名称', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'slug', NULL, NULL, NULL, NULL, NULL, 0, 0, 5, 'full', NULL, '别名', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'description', NULL, NULL, NULL, NULL, NULL, 0, 0, 7, 'full', NULL, '描述', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'logo_option', NULL, 'select-multiple-checkbox', '{\"choices\": [{\"text\": \"圆形 Logo\", \"value\": \"round_logo\"}, {\"text\": \"增加白色背景\", \"value\": \"bg_white\"}, {\"text\": \"增加黑色背景\", \"value\": \"bg_black\"}, {\"text\": \"额外边框\", \"value\": \"extra_border\"}]}', NULL, NULL, 0, 0, 9, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'website', NULL, NULL, NULL, NULL, NULL, 0, 0, 10, 'full', NULL, '机构网站', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name_full_cn', NULL, NULL, NULL, NULL, NULL, 0, 0, 11, 'full', NULL, '中文全称', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name_full_en', NULL, NULL, NULL, NULL, NULL, 0, 0, 12, 'full', NULL, '英文全称：例如「Stanford University」的全称为「Leland Stanford Junior University」，则需要在这里定义，若全称与机构名称相同，则此处留空', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'link', NULL, NULL, NULL, NULL, NULL, 0, 1, 13, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'count', NULL, NULL, NULL, NULL, NULL, 0, 1, 14, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'taxonomy', NULL, NULL, NULL, NULL, NULL, 0, 1, 15, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'parent', 'm2o', 'select-dropdown-m2o', '{\"template\": \"{{name}}\"}', 'related-values', '{\"template\": \"{{name}}\"}', 0, 0, 6, 'full', NULL, '父级机构', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'logo', 'file', 'file-image', NULL, 'image', NULL, 0, 0, 8, 'full', NULL, '机构 Logo：尺寸必须为正方形', NULL, 0, NULL, NULL, NULL);

INSERT INTO `directus`.`directus_presets` (`bookmark`, `user`, `role`, `collection`, `search`, `layout`, `layout_query`, `layout_options`, `refresh_interval`, `filter`, `icon`, `color`) VALUES (NULL, NULL, NULL, 'organizations', NULL, NULL, '{\"tabular\": {\"fields\": [\"organization_id\", \"lang\", \"name\", \"slug\", \"logo\", \"name_full_cn\", \"name_full_en\"]}}', '{\"tabular\": {\"widths\": {\"lang\": 94.765625, \"logo\": 139.80078125, \"name\": 160, \"slug\": 160, \"organization_id\": 152.375}, \"spacing\": \"comfortable\"}}', NULL, NULL, 'bookmark', NULL);

INSERT INTO `directus`.`directus_relations` (`many_collection`, `many_field`, `one_collection`, `one_field`, `one_collection_field`, `one_allowed_collections`, `junction_field`, `sort_field`, `one_deselect_action`) VALUES ('organizations', 'parent', 'organizations', NULL, NULL, NULL, NULL, NULL, 'nullify');
INSERT INTO `directus`.`directus_relations` (`many_collection`, `many_field`, `one_collection`, `one_field`, `one_collection_field`, `one_allowed_collections`, `junction_field`, `sort_field`, `one_deselect_action`) VALUES ('organizations', 'logo', 'directus_files', NULL, NULL, NULL, NULL, NULL, 'nullify');

I first manually created a collection and field in directus to observe the changes in the database, and found that directus_field and directus_relations had changed. Then I manually deleted the collection and directly executed the above SQL, expecting to get the same effect, but the following errors occurred for the parent, logo and other m2o fields:

af6a524c-9c90-4656-a07a-7054661202b1
af6a524c-9c90-4656-a07a-7054661202b11588×436 33.8 KB

What I want to ask is whether the same effect can be achieved by directly executing SQL？

Thanks for your answers

2 Answers

2
2

I have solved this bug。

After executing the SQL, you need to click collection in the settings, refresh the page, and then click content.

I don’t know the reason, but it’s solved.

I think that's the expected behavior when you create collection or fields using raw SQL, you just need to "configure" them inside the app studio by just clicking on them. Also don't forget to mark this as solved :folded_hands:t2:

 –
3

This most definitely shouldn’t be expected behaviour; creating via SDK or rest also introduces this error. It should be configured as per the payload. Why would I then have to go into every single field with a relation and click / refresh?

If anyone ever solves this, please ping me. It’s been like this for years.