Mojo::mysql::Database::Role::LoadDataInfile - Easy load data infile support for Mojo::mysql
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');
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.
LOAD DATA INFILE
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.
LOAD DATA LOCAL INFILE
# 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.
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.
$rows
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"; } });
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;
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 for more information on the below options, and possibly more up-to-date information.
$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).
LOW_PRIORITY
LOAD DATA
This cannot be true when "concurrent" is true.
true
$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.
CONCURRENT
This cannot be true when "low_priority" is true.
$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.
REPLACE
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.
LOCAL
$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.
$db->load_data_infile(table => 'people', rows => $rows, partition => ['p0', 'p1', 'p2']);
Adds the PARITION clause along with the provided partitions to insert into.
PARITION
See Partitioned Table Support for more information.
$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.
CHARACTER SET
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.
utf8
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.
$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.
SET
See Input Preprocessing for more examples of how "set" can be used.
"rows" correspond to the rows to be inserted. "rows" can be passed either an arrayref of "hashrefs", or an arrayref of "arrayrefs".
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.
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" specifies the names of the columns to set in the table. Different values may be provided depending on whether "rows" contains "hashrefs" or "arrayrefs".
# 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' }
# 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".
Adam Hopkins <srchulo@cpan.org>
Copyright 2019- Adam Hopkins
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Mojolicious
Mojo::mysql
Mojo::mysql::Database
Text::CSV_XS
Text::CSV_PP
To install Mojo::mysql::Database::Role::LoadDataInfile, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Mojo::mysql::Database::Role::LoadDataInfile
CPAN shell
perl -MCPAN -e shell install Mojo::mysql::Database::Role::LoadDataInfile
For more information on module installation, please visit the detailed CPAN module installation guide.