1

I have this large file as an example data.sql and what it does is create table and insert the data when I run import from phpMyAdmin.

How could one use this file in a module installer?

I've found something but it's for Magento 1, here is the link

Thank you in advance.

Juliano Vargas
  • 2,521
  • 3
  • 25
  • 81

1 Answers1

0

I've spent about a week looking for a nice and clean solution to this, but haven't had any success with anything that Magento provides so I settled on a quite hacky way, but it easy to implement and it works.

Basically you use Magento\Framework\App\DeploymentConfig to read the database credentials from your env.php and create a mysqli connection to your database. Then read the content of your .sql file into and pass it to mysqli::multi_query().

Here is some code to do that in your InstallSchema.php which assumes that you have a file called install.sql in the setup folder of your module:

<?php

namespace Vendor\Module\Setup;


class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface
{


private $deploymentConfig;
private $moduleReader;

public function __construct(
    \Magento\Framework\App\DeploymentConfig $deploymentConfig,
    \Magento\Framework\Module\Dir\Reader $moduleReader
)
{
    $this-&gt;deploymentConfig = $deploymentConfig;
    $this-&gt;moduleReader = $moduleReader;
}

public function install(
    \Magento\Framework\Setup\SchemaSetupInterface $setup,
    \Magento\Framework\Setup\ModuleContextInterface $context
) {
    $setup-&gt;startSetup();


    $setupDir = $this-&gt;moduleReader-&gt;getModuleDir(
        \Magento\Framework\Module\Dir::MODULE_SETUP_DIR,
        'Vendor_Module'
    );

    $sql = \file_get_contents($setupDir . '/install.sql');

    $dbConnection = new \mysqli(
        $this-&gt;deploymentConfig-&gt;get('db/connection/default/host'),
        $this-&gt;deploymentConfig-&gt;get('db/connection/default/username'),
        $this-&gt;deploymentConfig-&gt;get('db/connection/default/password'),
        $this-&gt;deploymentConfig-&gt;get('db/connection/default/dbname')
    );

    $dbConnection-&gt;multi_query($sql);

    $dbConnection-&gt;close();

    $setup-&gt;endSetup();
}



}

Other things I've tried for reference

Reading the sql file line by line and executing them one by one

This doesn't work on all file for obvious reasons. If a query or a comment spreads across multiple lines it doesn't work. If your file is one line per query you could use this approach and use Magento\Framework\DB\Adapter\Pdo\Mysql::query() to execute them. In InstallSchema.php you can get a Magento\Framework\DB\Adapter\Pdo\Mysql object by $setup->getConnection()

Reading the sql file query by query

This adds the complexity of detecting where a query ends and a new one starts, I've not been able to make this work for my specific sql file

Using Magento\Framework\DB\Adapter\Pdo\Mysql::multiQuery()

This method can be used to execute multiple query at once, so theoretically it should work to pass the content of a sql to that method. BUT Magento\Framework\DB\Adapter\Pdo\Mysql::multiQuery() is marked as deprecated since Magento 2.2. Also it didn't work for me. I've tried several sql files which were created by phpMyAdmin with this method and got a lot of syntax errors, although the files were completely valid and importable via other ways (i.e. the way I posted above, mysql cli and phpMyAdmin)

disappointed.moose
  • 83
  • 5