Zenn
Closed15

entを触ってく(再)

MySQL
ent
nabeyangnabeyang

テーブル名を合わせる

まずent init Customerしてent generate ./ent/schemaするとテーブル名はcustomersになるけど、これをcustomerにする。

./ent/schema/customer.go
+ func (Customer) Annotations() []schema.Annotation {
+	return []schema.Annotation{
+ 		entsql.Annotation{Table: "customer"},
+ 	}
+ }
nabeyangnabeyang

IDを合わせる

まずテーブルのカラム名をcustomer_idにするにはStorageKeyを使う。

./ent/schema/customer.go
+ func (Customer) Fields() []ent.Field {
+	return []ent.Field{
+		field.Int16("id").
+			StorageKey("customer_id"),
+	}
+ }
nabeyangnabeyang

customerのフィールド

./ent/schema/customer.go
// Fields of the Customer.
func (Customer) Fields() []ent.Field {
	return []ent.Field{
		// `customer_id` smallint unsigned NOT NULL AUTO_INCREMENT,
		// PRIMARY KEY (`customer_id`),
		field.Int16("id").
			StorageKey("customer_id"),
		//      `store_id` tinyint unsigned NOT NULL,
		field.Uint8("store_id"),
		// `first_name` varchar(45) NOT NULL,
		field.String("first_name").
			SchemaType(map[string]string{
				dialect.MySQL: "varchar(45)",
			}),
		// `last_name` varchar(45) NOT NULL,
		field.String("last_name").
			SchemaType(map[string]string{
				dialect.MySQL: "varchar(45)",
			}),
		// `email` varchar(50) DEFAULT NULL,
		field.String("email").
			SchemaType(map[string]string{
				dialect.MySQL: "varchar(50)",
			}).
			Nillable().
			Optional(),
		//  `address_id` smallint unsigned NOT NULL,
		field.Int16("address_id"),
		// `active` tinyint(1) NOT NULL DEFAULT '1',
		field.Bool("active").
			Default(true),
		// `create_date` datetime NOT NULL,
		field.Time("create_date").SchemaType(map[string]string{
			dialect.MySQL: "datetime",
		}),
		// `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		field.Time("last_update").
			Default(time.Now).
			UpdateDefault(time.Now),
	}
}

スキーマをダンプすると次の通り。一部異なるが、goコードと合わせるとlast_updateは同等の動きをする。emailのNillable()はgoコードに対するものでOptional()はschemaに効く(DEFAULT NULLになる)。

CREATE TABLE `customer` (
  `customer_id` smallint NOT NULL AUTO_INCREMENT,
  `store_id` tinyint unsigned NOT NULL,
  `first_name` varchar(45) COLLATE utf8mb4_bin NOT NULL,
  `last_name` varchar(45) COLLATE utf8mb4_bin NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `address_id` smallint NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `create_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
nabeyangnabeyang

YEAR型
https://dev.mysql.com/doc/refman/5.6/ja/year.html
次のようにすると作成はできる。

type Year uint8

func (y Year) Value() (driver.Value, error) {
	return uint8(y), nil
}

func (y *Year) Scan(value any) error {
	v, ok := value.(uint8)
	if !ok {
		return errors.New("invalud value for year")
	}
	*y = Year(v)
	return nil
}

func (Year) SchemaType() map[string]string {
	return map[string]string{
		dialect.MySQL: "year",
	}
}

		field.Other("release_year", new(Year)).
			SchemaType(new(Year).SchemaType()).
			Nillable().
			Optional(),

マイグレーション時に次のように出る。

failed creating schema resources: sql/schema: mysql: unknown column type "year" for version "8.0.29"

とりあえず、field.Uint16にするか

nabeyangnabeyang

外部キー

// Fields of the Address.
func (Address) Fields() []ent.Field {
	return []ent.Field{
...
		//	`city_id` smallint UNSIGNED NOT NULL,
		field.Uint16("city_id"),
...
	}
}

// Edges of the Address.
func (Address) Edges() []ent.Edge {
	return []ent.Edge{
		//	CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE
		edge.To("city", City.Type).
			Field("city_id").
			Unique().
			Required().
			StorageKey(edge.Symbol("fk_address_city")).
			Annotations(&entsql.Annotation{
				OnDelete: entsql.Restrict,
			}),
	}
}
nabeyangnabeyang

film_actorテーブル

ent/scheme/film.go
func (Film) Edges() []ent.Edge {
	return []ent.Edge{
...
		edge.To("actors", Actor.Type).StorageKey(edge.Table("film_actor")),
	}
}
ent/scheme/actor.go
func (Actor) Edges() []ent.Edge {
	return []ent.Edge{
		edge.From("films", Film.Type).Ref("actors"),
	}
}
結果
CREATE TABLE `film_actor` (
  `film_id` smallint unsigned NOT NULL,
  `actor_id` smallint unsigned NOT NULL,
  PRIMARY KEY (`film_id`,`actor_id`),
  KEY `film_actor_actor_id` (`actor_id`),
  CONSTRAINT `film_actor_actor_id` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON DELETE CASCADE,
  CONSTRAINT `film_actor_film_id` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

あまり要らないけど、last_updateが追加できない。

nabeyangnabeyang

index貼れない

なんかこんな感じにしてインデックス貼ってくれたら良さそうだけど

CREATE FULLTEXT INDEX `idx_title_description` ON `film_text`(`title`, `description`)`

↓のようにエラーになる。

Tx(xxxx).Exec: query=CREATE INDEX `idx_title_description` ON `film_text`(`title`, `description`) args=[]
...
failed creating schema resources: sql/schema: create index "idx_title_description": Error 1170: BLOB/TEXT column 'description' used in key specification without a key length
nabeyangnabeyang

マイグレーション

INSERT INTO film_actors(film_id, actor_id)
SELECT film_id, actor_id FROM film_actor ORDER BY id;
INSERT INTO film_categories(film_id, category_id)
SELECT film_id, category_id FROM film_category ORDER BY id;
nabeyangnabeyang
INSERT INTO film_actor(film_id, actor_id)
SELECT film_id, actor_id FROM film_actors;
INSERT INTO film_category(film_id, category_id)
SELECT film_id, category_id FROM film_categories;
nabeyangnabeyang

dump

$ mysqldump -h$HOST -uroot -p sakila > sakila-ent.sql #スキーマ+データ
$ mysqldump -h$HOST -uroot -p --opt --routines --no-data --skip-dump-date --skip-add-drop-table sakila > sakila-ent-schema.sql #スキーマのみ
このスクラップは2022/05/23にクローズされました