Генерация тестовых товаров Opencart в phpMyAdmin

Дата: 03.03.2019 Просмотров: 326 Комментарии: 0

Следующий SQL-запрос позволит сгенерировать любое количество категорий товаров для тестирования магазина. Единственное, у пользователя БД должен быть доступ к процедурам.

Запрос написан под opencart, при желании можете переделать под ocstore.

DROP PROCEDURE IF EXISTS `random_category`;
DELIMITER $$
CREATE PROCEDURE `random_category` (IN `counter` INT(3), IN `subcat` INT(3), IN `product` INT(4))
BEGIN
    DECLARE category_id int(11);
    DECLARE subcat_id int(11);
    DECLARE product_id int(11);
    DECLARE subcat_counter int(11);
    DECLARE product_counter int(11);
    DECLARE empty_col varchar(11);

    DECLARE parent_id int(11);
    DECLARE top tinyint(1);
    DECLARE top_subcat tinyint(1);
    DECLARE column_col tinyint(1);
    DECLARE sort_order int(3);
    DECLARE status tinyint(1);
    DECLARE date_added datetime;
    DECLARE date_modified datetime;

    DECLARE language_id int(11);
    DECLARE name_col varchar(255);
    DECLARE subcat_name varchar(255);
    DECLARE product_name varchar(255);
    DECLARE description text;
    DECLARE product_description text;
    DECLARE meta_title varchar(255);
    DECLARE meta_description varchar(255);
    DECLARE meta_keyword varchar(255);

    DECLARE store_id int(11);

    SET category_id = 1;
    SET product_id = 1;

    SET empty_col = '';
    SET parent_id = 0;
    SET top = 1;
    SET top_subcat = 0;
    SET column_col = 6;
    SET sort_order = 0;
    SET status = 1;
    SET date_added = NOW();
    SET date_modified = NOW();

    SET language_id = 1;

    SET store_id = 0;

    main_loop: WHILE category_id < counter DO
        SET name_col = CONCAT_WS(' ', 'Category', category_id);
        SET description = CONCAT_WS(' ', 'Lorem ipsum dolor si amet', category_id);
        SET meta_title = CONCAT_WS(' ', 'meta_title', category_id);
        SET meta_description = CONCAT_WS(' ', 'meta_description', category_id);
        SET meta_keyword = CONCAT_WS(' ', 'meta_keyword', category_id);

        INSERT INTO oc_category VALUES (category_id, empty_col, parent_id, top, column_col, category_id, status, date_added, date_modified);
        INSERT INTO oc_category_description VALUES (category_id, language_id, name_col, description, meta_title, meta_description, meta_keyword);
        INSERT INTO oc_category_to_store VALUES (category_id, store_id);

          SET subcat_id = category_id + 1;
          SET subcat_counter = category_id + subcat;

          subcat_loop: WHILE subcat_id < subcat_counter DO
                    SET name_col = CONCAT_WS(' ', 'SubCategory', subcat_id);
                    INSERT INTO oc_category VALUES (subcat_id, empty_col, category_id, top_subcat, column_col, subcat_id, status, date_added, date_modified);
                    INSERT INTO oc_category_description VALUES (subcat_id, language_id, name_col, description, meta_title, meta_description, meta_keyword);
                    INSERT INTO oc_category_to_store VALUES (subcat_id, store_id);

                        SET product_counter = product_id + product;
                        product_loop: WHILE product_id < product_counter DO

                              SET product_name = CONCAT_WS(' ', 'Product', product_id);
                              SET product_description = CONCAT_WS(' ', 'Lorem ipsum dolor si amet', product_id);

                              INSERT INTO oc_product
                                (product_id, model, sort_order, status, date_added, date_modified)
                                VALUES
                                (product_id, product_id, product_id, status, date_added, date_modified);

                              INSERT INTO oc_product_description
                                (product_id, language_id, name, description, meta_title, meta_description)
                                VALUES
                                (product_id, language_id, product_name, product_description, product_name, product_name);

                              INSERT INTO oc_product_to_category VALUES (product_id, subcat_id);
                              INSERT INTO oc_product_to_store VALUES (product_id, store_id);

                              SET product_id = product_id + 1;

                        END WHILE product_loop;

                    SET subcat_id = subcat_id + 1;
          END WHILE subcat_loop;

        SET category_id = subcat_id + 1;

    END WHILE main_loop;
END;$$
DELIMITER ;