search

ariga / entimport

A tool for generating Ent schema from SQL schema
Apache License 2.0
184 stars 55 forks source link

schema import failed - mysql: unknown attribute "STORED GENERATED" #47

Open idc77 opened 9 months ago

idc77 commented 9 months ago

When trying to run entimport on a shopware 6 mysql database, I'm met with the following error:

schema import failed - mysql: unknown attribute "STORED GENERATED"

I have never encountered a "STORED GENERATED" type before. Searching, I found: https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

They seem to be some kind of virtual field. Or something that extracts field of a json document.

Shopware version 6.5.8.6

I can do a database dump, if you need it, it's 235 tables or 290k.

grep STORED sw6-20240305.sql 
  `document_number` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`config`,_utf8mb4'$.documentNumber'))) STORED,
  `order_date` date GENERATED ALWAYS AS (cast(`order_date_time` as date)) STORED,
  `reversed` varchar(500) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`keyword`)) STORED,
  `referenced_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.id'))),0x00)) STORED,
  `referenced_version_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.version_id'))),0x00)) STORED,
grep GENERATED sw6-20240305.sql
  `document_number` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`config`,_utf8mb4'$.documentNumber'))) STORED,
  `order_date` date GENERATED ALWAYS AS (cast(`order_date_time` as date)) STORED,
  `amount_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `amount_net` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.netPrice'))) VIRTUAL,
  `position_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.positionPrice'))) VIRTUAL,
  `tax_status` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.taxStatus'))) VIRTUAL,
  `shipping_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`shipping_costs`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `total_price` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `unit_price` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.unitPrice'))) VIRTUAL,
  `quantity` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.quantity'))) VIRTUAL,
  `unit_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.unitPrice'))) VIRTUAL,
  `total_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `reversed` varchar(500) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`keyword`)) STORED,
  `referenced_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.id'))),0x00)) STORED,
  `referenced_version_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.version_id'))),0x00)) STORED,