Can I merge an .sql file that runs insert into and a Laravel factory seeder that creates dummy data on 2 only columns?

I have a factory file that creates dummy data for 2 columns: code & barcode. However, I wanted to run an .sql file that replaces data after I run the factory command. With that, I guess a merge will happen and might delete some records, or add new ones. Is that possible to merge the data instead. Like automatic mapping?

Here’s the content of my .sql file:

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 23, 2021 at 12:14 PM
-- Server version: 10.1.28-MariaDB
-- PHP Version: 7.1.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6f20232b302c272e3d2e2c3b2a3d303c2a3b302c23262a213b522f">[email protected]</a>@CHARACTER_SET_CLIENT */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="440b08001b070c051605071001161b1701101b160117110810177904">[email protected]</a>@CHARACTER_SET_RESULTS */;
/*!40101 SET @<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2f60636b706c6063636e7b666061706c6061616a6c7b666061126f">[email protected]</a>@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_zachmatic`
--

--
-- Dumping data for table `products`
--

REPLACE INTO `products` (`id`, `code`, `name`, `description`, `unit`, `cost`, `srp`, `supplier`, `qty_on_hand`, `category`, `delivery_date`, `created_at`) VALUES
(12, 'P-824830', 'magnum-v', '45/90-17 MV-360 tube type tires only', 'Pieces', '', '1246', 'HH All Ventures', 3, 'Tires', '', ''),
(13, 'P-232033', 'magnum-v', '50/100-17 MV-360 tube type tire only', 'Select Product ', '', '1246', 'HH All Ventures', 1, 'Tires', '', ''),
(14, 'P-73032309', 'magnum-v', '60/90-17 MV-360 Tube Type Tire Only', 'Pieces', '', '1341', 'HH All Ventures', 0, 'Tires', '', ''),
(15, 'P-0022252', 'magnum-v', '45/90-17 MV-329 Tube Type with Tube', 'Pieces', '', '1120', 'HH All Ventures', 9, 'Tires', '', ''),
(16, 'P-323694', 'magnum-v', '50/100-17 MV-329 tube type with tube', 'Select Product ', '', '1120', 'HH All Ventures', 0, 'Tires', '', ''),
(17, 'P-023202', 'magnum-v', '60/90-17 MV-329 tube type with tube', 'Pieces', '', '1499', 'HH All Ventures', 0, 'Tires', '', ''),
(18, 'P-4729320', 'magnum-v', '70/90-17 MV-329 tube type with tube', 'Pieces', '', '1678', 'HH All Ventures', 0, 'Tires', '', ''),
(19, 'P-3020323', 'magnum-v', '120/70-13 MV-119C Tubeless', 'Pieces', '', '1829', 'HH All Ventures', 5, 'Tires', '', ''),
(20, 'P-3220830', 'magnum-v', '130/70-13 MV-119C tubeless', 'Pieces', '', '2062', 'HH All Ventures', 0, 'Tires', '', ''),

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products` DROP COLUMN `id`;
ALTER TABLE `products` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

--
-- Drop `code` column to delete old barcodes
-- then re-add `code` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `code`;
ALTER TABLE `products` ADD COLUMN code VARCHAR(255) AFTER id;

--
-- Drop `delivery_date` column to delete old column
-- then re-add `delivery_date` column, then add `barcode` column
--
ALTER TABLE `products` DROP COLUMN `delivery_date`;
ALTER TABLE `products` ADD COLUMN delivery_date DATETIME;

/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="56151e170417150213040905130209151a1f1318026b16191a12">[email protected]</a>_CHARACTER_SET_CLIENT */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="317279706370726574636e6274656e637462647d65620c717e7d75">[email protected]</a>_CHARACTER_SET_RESULTS */;
/*!40101 SET <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="15565a595954415c5a5b4a565a5b5b5056415c5a5b28555a5951">[email protected]</a>_COLLATION_CONNECTION */;

Then my factory file:

<?php

namespace DatabaseFactories;

use AppModelsProduct;
use IlluminateDatabaseEloquentFactoriesFactory;
use Picqer;

class ProductFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Product::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        $code = $this->faker->bothify('PHZM-##########');
        $barcode = $this->generateBarcode($code);
        return [            
            'code' => $code,
            'barcode' => $barcode,
        ];
    }

    private function generateBarcode($code_to_convert) {
        $generator = new PicqerBarcodeBarcodeGeneratorHTML();
        $barcode = $generator->getBarcode($code_to_convert, $generator::TYPE_CODE_128, 1, 15);
        return $barcode;
    }
}

Any help is appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I guess you must create seeder for running this script file like below:

<?php

use IlluminateDatabaseSeeder;

class SqlFileSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {

        $path = public_path('sql/File.sql');
        $sql = file_get_contents($path);
        DB::unprepared($sql);
    }

}

and inside another seeder that your factory calls, you run this seeder too after calling factory

<?php

use IlluminateDatabaseSeeder;

class FooSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Product::factory()->count(20)->create();
        $this->call([SqlFileSeeder::class]);
    }

}

Method 2

Thank you @alirezadp10 for the direction! I’ve managed to solve this by following his answer as well as exporting the products table in phpmyadmin with Update queries instead of default Insert Into as custom.

Screenshot: Export as Custom

  1. Then uncheck the following:
  • Add DROP TABLE / TRIGGER statement
  • Add CREATE VIEW statement

Screenshot: Uncheck these settings

  1. Select Update on the Function to use when dumping data
    dropdown.

Screenshot: Select Update on the dropdown

  1. Then press Go.

After that, I’ve just replaced the field values using some regex in Sublime Text 3.
Link: Regular expression search replace in Sublime Text 2

And renamed the fields to my needs. For @alirezadp10 answer to work, I’ve run php artisan db:seed.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x