The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
SQL-Inserter-0.04
River stage one • 1 direct dependent • 1 total dependent
1 ++
/ SQL::Inserter

NAME

SQL::Inserter - Efficient buffered DBI inserter and fast INSERT SQL builder

VERSION

Version 0.04

SYNOPSIS

  use SQL::Inserter;

  my $sql = SQL::Inserter->new(
    dbh    => $dbh,
    table  => 'table',
    cols   => [qw/col1 col2.../],
    buffer => 100?   # Default buffer is 100 rows
  );

  # Pass single or multiple rows of data as an array (fastest method):
  $sql->insert($col1_row1, $col2_row1, $col1_row2...);

  # Alternatively, pass a single row as a hash, allows SQL code passed as
  # references instead of values (no need to define cols in constructor):
  $sql->insert({
    column1 => $data1,
    column2 => \'NOW()',
    ...
  });

  # Since the inserts are buffered, they might not have been executed yet.
  # You can manually flush the buffer at any time with no argument on insert
  # (otherwise there is auto-flush on the object's destruction):
  $sql->insert();

  # There are also functions to just get the SQL statement and its bind vars
  # similar to SQL::Abstract or SQL::Maker insert, but with much less overhead:
  my ($sql, @bind) = simple_insert($table, {col1=>$val...});

  # Multi-row variant:
  my ($sql, @bind) = simple_insert($table, [{col1=>$val1...},{col1=>$val2...},...]);

  # Or, construct an SQL statement with placeholders for a given number of rows:
  my $sql = multi_insert_sql('table', [qw/col1 col2.../], $num_of_rows);

DESCRIPTION

SQL::Inserter's main lightweight OO interface will let you do DBI inserts as efficiently as possible by managing a multi-row buffer and prepared statements.

You only have to select the number of rows for the buffered writes (default is 100) and choose whether to pass your data in arrays (fastest, requires all data to be bind values, will execute the same prepared statement every time the buffer is full) or hashes (allows SQL code apart from plain values).

It also provides lightweight functions that return the SQL queries to be used manually, similar to SQL::Abstract::insert, but much faster.

INSERT IGNORE and ON DUPLICATE KEY UPDATE variants supported for MySQL/MariaDB.

Although it is developed for use in our production MySQL/MariaDB, its main functions will work on DBs with similar INSERT INTO multi-row syntax like PostgreSQL. From v0.03, Oracle Database is also supported.

EXPORTS

On request: simple_insert multi_insert_sql.

CONSTRUCTOR

new

  my $sql = SQL::Inserter->new(
    dbh        => $dbh,
    table      => $table,
    cols       => \@column_names?,
    buffer     => 100?,
    duplicates => $ignore_or_update?,
    null_undef => $convert_undef_to_NULL?,
    oracle     => $oracle_format?
  );

Creates an object to insert data to a specific table. Buffering is enabled by default and anything left on it will be written when the object falls out of scope / is destroyed.

Required parameters:

  • dbh : A DBI database handle.

  • table : The name of the db table to insert to. See "NOTES" if you are using a restricted word for a table name.

Optional parameters:

  • cols : The names of the columns to insert. It is required if arrays are used to pass the data. With hashes they are optional (the order will be followed if they are defined). See "NOTES" if you are using any restricted words for column names.

  • buffer : Max number of rows to be held in buffer before there is a write. The buffer flushes (writes contents) when the object is destroyed. Setting it to 1 writes each row separately (least efficient). For small rows you can set buffer to thousands. The default is a (conservative) 100 which works with big data rows.

  • duplicates : For MySQL, define as 'ignore' or 'update' to get an INSERT IGNORE or ON DUPLICATE KEY UPDATE query respectively. See "NOTES" for details on the latter.

  • null_undef : Applies to the hash inserts only. If true, any undefined values will be converted to SQL's NULL (similar to the SQL::Abstract default). The default behaviour will leave an undef as the bind variable, which may either create an empty string in the db or give an error depending on your column type and db settings.

  • oracle : This is automatically set to true when an Oracle driver is detected (using $dbh->{Driver}->{Name}) and the module will produce the Oracle INSERT ALL format. Specifying it manually in the constructor will override the automatic detection - a false value will force the MySQL compatible multi-row INSERT INTO syntax (which should work on Oracle 23c or newer), while a true value will generate the "classic" Oracle syntax even without an Oracle driver detected.

METHODS

insert

  # Fastest array method. Only bind data is passed.
  my $ret = $sql->insert(@column_data_array);

  # Alternative allows SQL code as values in addition to bind variables.
  my $ret = $sql->insert(\%row_data);

  # No parameters will force emptying of buffer (db write).
  my $ret = $sql->insert();

The main insert method. Returns the return value of the last execute statement if there was one called, 0 otherwise (buffer not full.

It works in two main modes, by passing an array or a hashref:

Array mode

Pass the data for one or more rows in a flat array, buffering will work automatically based on your buffer settings. Obviously your @column_data_array has to contain a multiple of the number of cols defined on the constructor.

This is the fastest mode, but it only allows simple bind values. Any undefined values will be passed directly to DBI->execute, which may or may not be what you expect - there will not be any explicit conversion to SQL NULL.

Hash mode

Pass a reference to a hash containing the column names & values for a single row of data. If cols was not defined on the constructor, the columns from the first data row will be used instead. For subsequent rows any extra columns will be disregarded and any missing columns will be considered to have an undef (which can be automatically converted to NULL if the null_undef option was set).

Flushing the buffer

Calling insert with no arguments forces a write to the db, flushing the buffer. You don't have to call this manually as the buffer will be flushed when the object is destroyed (e.g. your object falls out of scope).

Mixing modes

You can theoretically mix modes, but only when the buffer is empty e.g. you can start with the array mode, flush the buffer and continue with hash mode (cols will be defined from the array mode). Or you can start with hash mode (so cols will be defined from the very first hash), and after flushing the buffer you can switch to array mode.

last_insert_id

  # MySQL
  my $id = $sql->last_insert_id;

  # Depending on the driver you might need parameters
  my $id = $sql->last_insert_id($catalog, $schema, $table, $field, \%attr);

Returns the id of the last insert row, if available, after emptying the buffer.

Convenience wrapper around DBI's database handle method of the same name. See that method's documentation for details and caveats depending on your DB driver.

ATTRIBUTES

last_retval

  my $val = $sql->{last_retval}

The return value of the last DBI execute() is stored in this attribute. On a successful insert it should contain the number of rows of that statement. Note that an insert call, depending on the buffering, may call execute() zero, one or more times.

row_total

  my $total = $sql->{row_total}

Basically a running total of the return values, for successful inserts it shows you how many rows were inserted into the database. It will be undef if no execute() has been called.

buffer_counter

  my $count = $sql->{buffer_counter}

Check how many un-inserted data rows the buffer currently holds.

FUNCTIONS

simple_insert

  # Single row
  my ($sql, @bind) = simple_insert($table, \%fieldvals, \%options);

  # Multi-row
  my ($sql, @bind) = simple_insert($table, [\%fieldvals_row1,...], \%options);

Returns the SQL statement and bind variable array for a hash containing the row columns and values. Values are treated as bind variables unless they are references to SQL code strings. E.g. :

  my ($sql, @bind) = simple_insert('table', {foo=>"bar",when=>\"NOW()"});
  ### INSERT INTO table (foo, when) VALUES (?,NOW())

The function also accepts an array of hashes to allow multi-row inserts:

  my ($sql, @bind) = simple_insert('table', [{foo=>"foo"},{foo=>"bar"}]);
  ### INSERT INTO table (foo) VALUES (?),(?)

The first row (element in array) needs to contain the superset of all the columns that you want to insert, if some of your rows have undefined column data.

Options:

  • null_undef : If true, any undefined values will be converted to SQL's NULL (similar to the SQL::Abstract default). The default behaviour will leave an undef as the bind variable, which may either create an empty string in the db or give an error depending on your column type and db settings.

  • duplicates : For MySQL, define as 'ignore' or 'update' to get an INSERT IGNORE or ON DUPLICATE KEY UPDATE query respectively. See "NOTES" for details on the latter.

  • oracle : Will generate the Oracle INSERT ALL syntax (required for pre-23c Oracle Databases).

multi_insert_sql

 my $sql = multi_insert_sql(
     $table,
     \@columns,      # names of table columns
     $num_of_rows?,  # default = 1
     $variant?       # can be set as 'ignore'/'update' on duplicate key (MySQL) or 'oracle'
 );

Builds bulk insert query (single insert is possible too), with ability for ignore/on duplicate key update variants for MySQL.

Requires at least the name of the table $table and an arrayref with the column names \@columns. See "NOTES" if you want to quote table or column names.

Optional parameters:

  • $num_of_rows : By default it returns SQL with bind value placeholders for a single row. You can define any number of rows to use with multi-row bind variable arrays.

  • $variant : For MySQL, passing 'ignore' as the 4th argument returns an INSERT IGNORE query. Passing 'update' as the argument returns a query containing an `ON DUPLICATE KEY UPDATE` clause (see "NOTES" for further details). Passing 'oracle' will create the INSERT ALL syntax for pre-23c Oracle DBs.

NOTES

Using reserved words as object names

If you are using reserved words as table/column names (which is strongly discouraged), just include the appropriate delimiter in the table or cols parameter. E.g. for MySQL with columns named from and to you can do:

 cols => [qw/`from` `to`/]

For PostgreSQL or Oracle you'd do [qw/"from" "to"/], for SQL Server [qw/[from] [to]/] etc.

On duplicate key update

The duplicates => 'update' option creates an ON DUPLICATE KEY UPDATE clause for the query. E.g.:

 my $sql = multi_insert_sql('table_name', [qw/col1 col2/], 2, 'update');

will produce:

 ## INSERT INTO table_name (col1,col2) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE col1=VALUES(col1),col2=VALUES(col2)

Note that as of MySQL 8.0.20 the VALUES in UPDATE is deprecated (row alias is used instead), so this functionality might need to be updated some day if VALUES is removed completely.

Output whitespace

No spaces are added to the output string beyond the minimum. However, there is a new line (\n) added for each row of value placeholders - mainly to easily count the number of rows from the string. Also, the ON DUPLICATE KEY UPDATE clause is on a new line.

Error handling

The module does not do any error handling on prepare/execute statements, you should use DBI's RaiseError and HandleErrror.

Performance

The OO interface has minimal overhead. The only consideration is that if your rows do not contain particularly large amounts of data, you may want to increase the buffer size which is at a modest 100 rows.

Internally, to construct the prepared statements it uses similar logic to the public functions. simple_insert is of particular interest as it is a minimalistic function that may replace (similar interface / feature set) the insert functions from SQL::Abstract or SQL::Maker while being over 40x faster than the former and around 3x faster than the latter. The included bench/benchmark.pl script gives an idea (results on an M1 Pro Macbook):

 Compare SQL::Abstract, SQL::Maker, simple_insert:
                     Rate Abstract Abstract cached Maker Maker cached simple_insert
 Abstract          4207/s       --             -6%  -90%         -91%          -98%
 Abstract cached   4482/s       7%              --  -90%         -90%          -98%
 Maker            44245/s     952%            887%    --          -4%          -76%
 Maker cached     46205/s     998%            931%    4%           --          -75%
 simple_insert   187398/s    4355%           4081%  324%         306%            --
 
 Compare simple_insert, multi_insert_sql for single row:
                      Rate    simple_insert multi_insert_sql
 simple_insert    190037/s               --             -76%
 multi_insert_sql 797596/s             320%               --

AUTHOR

Dimitrios Kechagias, <dkechag at cpan.org>

BUGS

Please report any bugs or feature requests either on GitHub (preferred), or on RT (via the email <bug-sql-inserter at rt.cpan.org>, or https://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Inserter).

I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

GIT

https://github.com/SpareRoom/SQL-Inserter

CPAN

https://metacpan.org/pod/SQL::Inserter

LICENSE AND COPYRIGHT

Copyright (C) 2023, SpareRoom

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