Запатентованная мечта программиста — часть II

/* id_offset is not required for empty tables, magento tables are not empty */
SET @id_offset = 10000;


/* entities */

INSERT INTO eav_attribute_set (attribute_set_id, attribute_set_name, entity_type_id)
SELECT derived_types.id + @id_offset,
    derived_types.value,
    1 AS magento_product_entity_type
FROM test_single_table AS derived_types
WHERE derived_types.parent_id = 0 AND id != type_id AND type_id IN (
  SELECT id
  FROM test_single_table AS base_types
  WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_attribute_group (attribute_group_id, attribute_set_id, attribute_group_name, attribute_group_code)
SELECT attribute_set_id, attribute_set_id, 'General', 'general'
FROM eav_attribute_set
WHERE attribute_set_id > @id_offset;


/* attributes */

INSERT INTO eav_attribute (attribute_id, attribute_code, frontend_label, backend_table, backend_type, entity_type_id, frontend_input, is_user_defined)
SELECT data_attribute_names.id + @id_offset,
    CONCAT('a_', data_attribute_names.id + @id_offset),
    data_attribute_names.value,
    NULL,
    IF (data_attribute_names.type_id = 2, 'varchar', 'int'),
    1 AS magento_product_entity_type, 'text', 1
FROM test_single_table AS data_attribute_names
WHERE data_attribute_names.parent_id = 0 AND id != type_id AND type_id IN (
  SELECT id
  FROM test_single_table AS base_types
  WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_attribute (attribute_id, attribute_code, frontend_label, backend_table, backend_type, entity_type_id, frontend_input, is_user_defined)
SELECT ref_attribute_names.id + @id_offset,
    CONCAT('a_', ref_attribute_names.id + @id_offset),
    ref_attribute_targets.value,
    'customer_entity_int',
    ref_attribute_names.type_id + @id_offset,
    1 AS magento_product_entity_type, 'text', 1
FROM test_single_table AS ref_attribute_names
    INNER JOIN test_single_table AS ref_attribute_targets ON ref_attribute_targets.id = ref_attribute_names.type_id
WHERE ref_attribute_names.parent_id = 0 AND ref_attribute_names.id != ref_attribute_names.type_id AND ref_attribute_names.type_id NOT IN (
    SELECT id
    FROM test_single_table AS base_types
    WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_entity_attribute (sort_order, attribute_set_id, attribute_group_id, attribute_id, entity_type_id)
SELECT attribute_structure.`order`,
    attribute_structure.parent_id + @id_offset,
    attribute_structure.parent_id + @id_offset,
    attribute_structure.type_id + @id_offset,
    1 AS magento_product_entity_type
FROM test_single_table AS attribute_structure
WHERE attribute_structure.parent_id IN (
    SELECT id
    FROM test_single_table AS possible_enitities
    WHERE parent_id = 0 AND id != type_id AND type_id IN (
      SELECT id
      FROM test_single_table AS base_types
      WHERE parent_id = 0 AND id = type_id
    )
);

INSERT INTO customer_form_attribute (form_code, attribute_id)
SELECT 'adminhtml_customer', attribute_id
FROM eav_attribute
WHERE eav_attribute.attribute_id > @id_offset
UNION ALL
SELECT 'customer_account_create', attribute_id
FROM eav_attribute
WHERE eav_attribute.attribute_id > @id_offset
UNION ALL
SELECT 'customer_account_edit', attribute_id
FROM eav_attribute
WHERE eav_attribute.attribute_id > @id_offset;


INSERT INTO customer_eav_attribute (attribute_id)
SELECT attribute_structure.type_id + @id_offset
FROM test_single_table AS attribute_structure
WHERE attribute_structure.parent_id IN (
    SELECT id
    FROM test_single_table AS possible_enitities
    WHERE parent_id = 0 AND id != type_id AND type_id IN (
      SELECT id
      FROM test_single_table AS base_types
      WHERE parent_id = 0 AND id = type_id
    )
);


/* values */

INSERT INTO customer_entity (entity_id, firstname)
SELECT objects.id + @id_offset, objects.value
FROM test_single_table AS objects
WHERE objects.parent_id = 1 AND objects.id != 1;

INSERT INTO customer_grid_flat (entity_id, name)
SELECT objects.id + @id_offset, objects.value
FROM test_single_table AS objects
WHERE objects.parent_id = 1 AND objects.id != 1;


INSERT INTO customer_entity_varchar (value_id, attribute_id, entity_id, value)
SELECT data_attribute_values.id + @id_offset,
    data_attribute_values.type_id + @id_offset,
    data_attribute_values.parent_id + @id_offset,
    data_attribute_values.value
FROM test_single_table AS data_attribute_values
WHERE parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND type_id IN (
    SELECT id
    FROM test_single_table AS possible_attributes
    WHERE parent_id = 0 AND id != type_id AND type_id = 2
);


INSERT INTO customer_entity_int (value_id, attribute_id, entity_id, value)
SELECT data_attribute_values.id + @id_offset,
    data_attribute_values.type_id + @id_offset,
    data_attribute_values.parent_id + @id_offset,
    data_attribute_values.value
FROM test_single_table AS data_attribute_values
WHERE parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND type_id IN (
    SELECT id
    FROM test_single_table AS possible_attributes
    WHERE parent_id = 0 AND id != type_id AND type_id = 3
);


INSERT INTO customer_entity_int (value_id, attribute_id, entity_id, value)
SELECT ref_attribute_values.id + @id_offset,
    type_ref_attributes.id + @id_offset,
    ref_attribute_values.parent_id + @id_offset,
    ref_attribute_values.type_id + @id_offset
FROM test_single_table AS ref_attribute_values
    INNER JOIN test_single_table AS objects ON objects.id = ref_attribute_values.parent_id
    INNER JOIN test_single_table AS ref_targets ON ref_targets.id = ref_attribute_values.type_id
    INNER JOIN test_single_table AS type_attributes ON type_attributes.parent_id = objects.type_id
    INNER JOIN test_single_table AS type_ref_attributes ON type_ref_attributes.id = type_attributes.type_id
	     AND type_ref_attributes.type_id = ref_targets.type_id
WHERE ref_attribute_values.parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND ref_attribute_values.type_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
);

/* id_offset is not required for empty tables, magento tables are not empty */
SET @id_offset = 10000;


/* entities */

INSERT INTO eav_attribute_set (attribute_set_id, attribute_set_name, entity_type_id)
SELECT derived_types.id + @id_offset,
    derived_types.value,
    4 AS magento_product_entity_type
FROM test_single_table AS derived_types
WHERE derived_types.parent_id = 0 AND id != type_id AND type_id IN (
  SELECT id
  FROM test_single_table AS base_types
  WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_attribute_group (attribute_group_id, attribute_set_id, attribute_group_name, attribute_group_code)
SELECT attribute_set_id, attribute_set_id, 'General', 'general'
FROM eav_attribute_set
WHERE attribute_set_id > @id_offset;


/* attributes */

INSERT INTO eav_attribute (attribute_id, attribute_code, frontend_label, backend_table, backend_type, entity_type_id, frontend_input, is_user_defined)
SELECT data_attribute_names.id + @id_offset,
    CONCAT('a_', data_attribute_names.id + @id_offset),
    data_attribute_names.value,
    NULL,
    IF (data_attribute_names.type_id = 2, 'varchar', 'int'),
    4 AS magento_product_entity_type, 'text', 1
FROM test_single_table AS data_attribute_names
WHERE data_attribute_names.parent_id = 0 AND id != type_id AND type_id IN (
  SELECT id
  FROM test_single_table AS base_types
  WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_attribute (attribute_id, attribute_code, frontend_label, backend_table, backend_type, entity_type_id, frontend_input, is_user_defined)
SELECT ref_attribute_names.id + @id_offset,
    CONCAT('a_', ref_attribute_names.id + @id_offset),
    ref_attribute_targets.value,
    'catalog_product_entity_int',
    ref_attribute_names.type_id + @id_offset,
    4 AS magento_product_entity_type, 'text', 1
FROM test_single_table AS ref_attribute_names
    INNER JOIN test_single_table AS ref_attribute_targets ON ref_attribute_targets.id = ref_attribute_names.type_id
WHERE ref_attribute_names.parent_id = 0 AND ref_attribute_names.id != ref_attribute_names.type_id AND ref_attribute_names.type_id NOT IN (
    SELECT id
    FROM test_single_table AS base_types
    WHERE parent_id = 0 AND id = type_id
);


INSERT INTO eav_entity_attribute (sort_order, attribute_set_id, attribute_group_id, attribute_id, entity_type_id)
SELECT attribute_structure.`order`,
    attribute_structure.parent_id + @id_offset,
    attribute_structure.parent_id + @id_offset,
    attribute_structure.type_id + @id_offset,
    4 AS magento_product_entity_type
FROM test_single_table AS attribute_structure
WHERE attribute_structure.parent_id IN (
    SELECT id
    FROM test_single_table AS possible_enitities
    WHERE parent_id = 0 AND id != type_id AND type_id IN (
      SELECT id
      FROM test_single_table AS base_types
      WHERE parent_id = 0 AND id = type_id
    )
);


/* values */

INSERT INTO eav_entity (entity_id, entity_type_id, attribute_set_id)
SELECT objects.id + @id_offset, 4, objects.type_id + @id_offset
FROM test_single_table AS objects
WHERE objects.parent_id = 1 AND objects.id != 1;


INSERT INTO eav_entity_varchar (value_id, attribute_id, entity_id, entity_type_id, value)
SELECT data_attribute_values.id + @id_offset,
    data_attribute_values.type_id + @id_offset,
    data_attribute_values.parent_id + @id_offset,
    4,
    data_attribute_values.value
FROM test_single_table AS data_attribute_values
WHERE parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND type_id IN (
    SELECT id
    FROM test_single_table AS possible_attributes
    WHERE parent_id = 0 AND id != type_id AND type_id = 2
);


INSERT INTO eav_entity_int (value_id, attribute_id, entity_id, entity_type_id, value)
SELECT data_attribute_values.id + @id_offset,
    data_attribute_values.type_id + @id_offset,
    data_attribute_values.parent_id + @id_offset,
    4,
    data_attribute_values.value
FROM test_single_table AS data_attribute_values
WHERE parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND type_id IN (
    SELECT id
    FROM test_single_table AS possible_attributes
    WHERE parent_id = 0 AND id != type_id AND type_id = 3
);


INSERT INTO eav_entity_int (value_id, attribute_id, entity_id, entity_type_id, value)
SELECT ref_attribute_values.id + @id_offset,
    type_ref_attributes.id + @id_offset,
    ref_attribute_values.parent_id + @id_offset,
    4,
    ref_attribute_values.type_id + @id_offset
FROM test_single_table AS ref_attribute_values
    INNER JOIN test_single_table AS objects ON objects.id = ref_attribute_values.parent_id
    INNER JOIN test_single_table AS ref_targets ON ref_targets.id = ref_attribute_values.type_id
    INNER JOIN test_single_table AS type_attributes ON type_attributes.parent_id = objects.type_id
    INNER JOIN test_single_table AS type_ref_attributes ON type_ref_attributes.id = type_attributes.type_id
	     AND type_ref_attributes.type_id = ref_targets.type_id
WHERE ref_attribute_values.parent_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
)
AND ref_attribute_values.type_id IN (
    SELECT id
    FROM test_single_table AS objects
    WHERE objects.parent_id = 1 AND objects.id != 1
);