NAME

    Mojo::mysql::Database::Role::LoadDataInfile - Easy load data infile
    support for Mojo::mysql

STATUS

SYNOPSIS

      use Mojo::mysql;
      use Mojo::mysql::Database::Role::LoadDataInfile;
    
      my $mysql   = Mojo::mysql->new(...);
      my $results = $mysql->db->load_data_infile(
        table => 'people',
        rows => [
          {
            name => 'Bob',
            age  => 23,
          },
          {
            name => 'Alice',
            age  => 25,
          },
        ],
      );
    
      print $results->affected_rows . " affected rows\n";
    
      # use promises for non-blocking queries
      my $promise = $mysql->db->load_data_infile_p(
        table => 'people',
        rows => [
          {
            name => 'Bob',
            age  => 23,
          },
          {
            name => 'Alice',
            age  => 25,
          },
        ],
      );
    
      $promise->then(sub {
        my $results = shift;
        print $results->affected_rows . " affected rows\n";
      })->catch(sub {
        my $err = shift;
        warn "Something went wrong: $err";
      });
    
    
      # apply the LoadDataInfile role to your own database_class
      use Mojo::mysql::Database::Role::LoadDataInfile database_class => 'MyApp::Database';
    
      $mysql->database_class('MyApp::Database');
      my $results = $mysql->db->load_data_infile(...);
    
    
      # don't auto apply the role to Mojo::mysql::Database and do it yourself
      $mysql->db->with_roles('+LoadDataInfile')->load_data_infile(...);
    
      # or
    
      Role::Tiny->apply_roles_to_package('Mojo::mysql::Database', 'Mojo::mysql::Database::Role::LoadDataInfile');

DESCRIPTION

    Mojo::mysql::Database::Role::LoadDataInfile is a role that makes
    synchronous and asynchronous LOAD DATA INFILE queries easy with your
    "database_class" in Mojo::mysql.

    This module currently only supports LOAD DATA LOCAL INFILE, meaning the
    file used for LOAD DATA INFILE is on the same computer where your code
    is running, not the database server.
    Mojo::mysql::Database::Role::LoadDataInfile generates a temporary file
    for you locally on the computer your code is running on.

IMPORT OPTIONS

 database_class

      # apply the LoadDataInfile role to your own database_class
      use Mojo::mysql::Database::Role::LoadDataInfile database_class => 'MyApp::Database';
    
      $mysql->database_class('MyApp::Database');
      my $results = $mysql->db->load_data_infile(...);

    "database_class" allows you to apply
    Mojo::mysql::Database::Role::LoadDataInfile to your own database class
    instead of the default Mojo::mysql::Database.

METHODS

 load_data_infile

      my $results = $db->load_data_infile(table => 'people', rows => $rows);
      print $results->affected_rows . " affected rows\n";

    Execute a blocking LOAD DATA INFILE query and return a
    Mojo::mysql::Results instance. A temporary file is used to store the
    data in $rows and then is sent to MySQL. The file is deleted once the
    query is complete. You can also append a callback to perform a
    non-blocking operation.

      my $results = $db->load_data_infile(table => 'people', rows => $rows, sub {
        my ($db, $err, $results) = @_;
    
        if ($err) {
            print "LOAD DATA INFILE failed: $err\n";
        } else {
          print $results->affected_rows . " affected rows\n";
        }
      });

 load_data_infile_p

      my $promise = $db->load_data_infile_p(table => 'people', rows => $rows);

    Same as "load_data_infile", but performs all operations non-blocking
    and returns a Mojo::Promise object instead of accepting a callback.

      $db->load_data_infile_p(table => 'people', rows => $rows)->then(sub {
        my $results = shift;
        print $results->affected_rows . " affected rows\n";
        ...
      })->catch(sub {
        my $err = shift;
        ...
      })->wait;

 options

    These are the options that can be passed to both "load_data_infile" and
    "load_data_infile_p". Unless stated otherwise, options may be combined.

    See LOAD DATA SYNTAX
    <https://dev.mysql.com/doc/refman/5.7/en/load-data.html> for more
    information on the below options, and possibly more up-to-date
    information.

  low_priority

      $db->load_data_infile(table => 'people', rows => $rows, low_priority => 1);

    Adds the LOW_PRIORITY modifier to the query, which means that the
    execution of the LOAD DATA statement is delayed until no other clients
    are reading from the table. This affects only storage engines that use
    only table-level locking (such as MyISAM, MEMORY, and MERGE).

    This cannot be true when "concurrent" is true.

  concurrent

      $db->load_data_infile(table => 'people', rows => $rows, concurrent => 1);

    Adds the CONCURRENT modifier to the query, which means that for MyISAM
    tables that satisfy the condition for concurrent inserts (that is, it
    contains no free blocks in the middle), other threads can retrieve data
    from the table while LOAD DATA is executing.

    This cannot be true when "low_priority" is true.

  replace

      $db->load_data_infile(table => 'people', rows => $rows, replace => 1);

    Adds the REPLACE modifier to the query, which means that rows that have
    the same value for a primary key or unique index as an existing row
    will replace the existing row.

    This cannot be true when "ignore" is true.

    If neither "replace" nor "ignore" is specified, the default is "ignore"
    since this module uses the LOCAL modifier.

  ignore

      $db->load_data_infile(table => 'people', rows => $rows, ignore => 1);

    Adds the REPLACE modifier to the query, which means that rows that
    duplicate an existing row on a unique key value are discarded.

    This cannot be true when "replace" is true.

    If neither "ignore" nor "replace" is specified, the default is "ignore"
    since this module uses the LOCAL modifier.

  partition

      $db->load_data_infile(table => 'people', rows => $rows, partition => ['p0', 'p1', 'p2']);

    Adds the PARITION clause along with the provided partitions to insert
    into.

    See Partitioned Table Support
    <https://dev.mysql.com/doc/refman/5.7/en/load-data.html#load-data-partitioning-support>
    for more information.

  character_set

      $db->load_data_infile(table => 'people', rows => $rows, character_set => 'utf8', tempfile_open_mode => '>:encoding(UTF-8)');

    Adds the CHARACTER SET clause, which specifies the encoding that MySQL
    will use to interpret the data.

    The default is utf8, which matches with the default of
    "tempfile_open_mode". If you provide "character_set", you must also
    provide "tempfile_open_mode". The encodings should match between these
    two.

  tempfile_open_mode

      $db->load_data_infile(table => 'people', rows => $rows, character_set => 'utf8', tempfile_open_mode => '>:encoding(UTF-8)');

    Sets the mode when opening the temporary file.

    The default is ">:encoding(UTF-8)", which matches with the default of
    "character_set". If you provide "tempfile_open_mode", you must also
    provide "character_set". The encodings should match between these two.

  set

      $db->load_data_infile(table => 'people', rows => $rows, set => [
          {insert_time => 'NOW()'},
          {update_time => 'NOW()'},
      ]);

    The SET clause can be used in several different ways, such as to supply
    values not derived from the input file. It accepts an arrayref of
    hashes, where the key of each hash is the column to set and the value
    is the expression to set it to.

    See Input Preprocessing
    <https://dev.mysql.com/doc/refman/5.7/en/load-data.html#load-data-input-preprocessing>
    for more examples of how "set" can be used.

  rows

    "rows" correspond to the rows to be inserted. "rows" can be passed
    either an arrayref of "hashrefs", or an arrayref of "arrayrefs".

   hashrefs

      my $rows = [
        { name => 'Bob', age => 23 },
        { name => 'Alice', age => 27 },
      ];
      $db->load_data_infile(table => 'people', rows => $rows);

    If the items are "hashrefs" and "columns" is not provided, the keys
    from the first hashref will be used for "columns" and will be used as
    both the MySQL column names, and the key names to get values from the
    hashrefs.

   arrayrefs

      my $rows = [
        ['Bob', 23],
        ['Alice', 27],
      ];
    
      # columns required when using arrayrefs
      my $columns = ['name', 'age'];
      $db->load_data_infile(table => 'people', rows => $rows, columns => $columns);

    If the items are "arrayrefs", "columns" must be provided, and the order
    of the column names in columns must match with the order of the values
    in each arrayref in "rows".

    See "columns" for more advance columns options.

  columns

    "columns" specifies the names of the columns to set in the table.
    Different values may be provided depending on whether "rows" contains
    "hashrefs" or "arrayrefs".

   rows contains hashrefs

      # will use keys of first hashref in $rows for columns if columns is not provided
      $db->load_data_infile(table => 'people', rows => $rows);
    
      # strings in $columns will be used as keys to access values of the hashrefs
      # and also as the column names in MySQL
      my $columns = ['name', 'age'];
      $db->load_data_infile(table => 'people', rows => $rows, columns => $columns);
    
      # you can map hash keys to their correpsonding names in the table
      my $columns = [
        'name',
        { hash_age => 'column_age' },
      ];
      $db->load_data_infile(table => 'people', rows => $rows, columns => $columns);

    If "columns" is not provided, "rows" must contain hashrefs, and the
    keys of the first hashref will be used as the columns.

    You may pass two types of values in "columns" when "hashrefs" are used
    in "rows":

      * You may pass strings, which will be used as the keys to access the
      values of the hashrefs and the column names.

      * Or, you may also pass hashes with a single key value pair, where
      the key is the name of the key in the hash, and the value is the name
      of the corresponding column in the table:

        { key_name => 'column_name' }

   rows contains arrayrefs

      # columns must be in the same order as their corresponding values in $rows
      my $columns = ['name', 'age'];
      $db->load_data_infile(table => 'people', rows => $rows, columns => $columns);

    If "rows" contains "arrayrefs", "columns" is required and its values
    should be in the same order as the corresponding values in the
    arrayrefs pasesed to "rows".

AUTHOR

    Adam Hopkins <srchulo@cpan.org>

COPYRIGHT

    Copyright 2019- Adam Hopkins

LICENSE

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself.

SEE ALSO

      * Mojolicious

      * Mojo::mysql

      * Mojo::mysql::Database

      * Text::CSV_XS

      * Text::CSV_PP