File Coverage

blib/lib/SQL/Inserter.pm

Criterion	Covered	Total	%
statement	177	177	100.0
branch	78	78	100.0
condition	36	36	100.0
subroutine	25	25	100.0
pod	5	5	100.0
total	321	321	100.0

line

stmt

bran

cond

sub

pod

time

code

1

package SQL::Inserter;

2

3

5

1041431

use 5.008;

5

46

4

5

35

use strict;

5

57

5

155

5

5

23

use warnings;

5

8

5

251

6

7

5

27

use Carp;

5

29

5

426

8

5

28

use Exporter 'import';

5

10

5

14584

9

10

=head1 NAME

11

12

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

13

14

=head1 VERSION

15

16

Version 0.04

17

18

=cut

19

20

our $VERSION = '0.04';

21

22

our @EXPORT_OK = qw(simple_insert multi_insert_sql);

23

24

=head1 SYNOPSIS

25

26

use SQL::Inserter;

27

28

my $sql = SQL::Inserter->new(

29

dbh => $dbh,

30

table => 'table',

31

cols => [qw/col1 col2.../],

32

buffer => 100? # Default buffer is 100 rows

33

);

34

35

# Pass single or multiple rows of data as an array (fastest method):

36

$sql->insert($col1_row1, $col2_row1, $col1_row2...);

37

38

# Alternatively, pass a single row as a hash, allows SQL code passed as

39

# references instead of values (no need to define cols in constructor):

40

$sql->insert({

41

column1 => $data1,

42

column2 => \'NOW()',

43

...

44

});

45

46

# Since the inserts are buffered, they might not have been executed yet.

47

# You can manually flush the buffer at any time with no argument on insert

48

# (otherwise there is auto-flush on the object's destruction):

49

$sql->insert();

50

51

# There are also functions to just get the SQL statement and its bind vars

52

# similar to SQL::Abstract or SQL::Maker insert, but with much less overhead:

53

my ($sql, @bind) = simple_insert($table, {col1=>$val...});

54

55

# Multi-row variant:

56

my ($sql, @bind) = simple_insert($table, [{col1=>$val1...},{col1=>$val2...},...]);

57

58

# Or, construct an SQL statement with placeholders for a given number of rows:

59

my $sql = multi_insert_sql('table', [qw/col1 col2.../], $num_of_rows);

60

61

=head1 DESCRIPTION

62

63

SQL::Inserter's main lightweight OO interface will let you do L inserts as

64

efficiently as possible by managing a multi-row buffer and prepared statements.

65

66

You only have to select the number of rows for the buffered writes (default is 100)

67

and choose whether to pass your data in arrays (fastest, requires all data to be bind

68

values, will execute the same prepared statement every time the buffer is full) or

69

hashes (allows SQL code apart from plain values).

70

71

It also provides lightweight functions that return the SQL queries to be used manually,

72

similar to C, but much faster.

73

74

C and C variants supported for MySQL/MariaDB.

75

76

Although it is developed for use in our production MySQL/MariaDB, its main functions

77

will work on DBs with similar C multi-row syntax like PostgreSQL.

78

From v0.03, Oracle Database is also supported.

79

80

=head1 EXPORTS

81

82

On request: C C.

83

84

=head1 CONSTRUCTOR

85

86

=head2 C

87

88

my $sql = SQL::Inserter->new(

89

dbh => $dbh,

90

table => $table,

91

cols => \@column_names?,

92

buffer => 100?,

93

duplicates => $ignore_or_update?,

94

null_undef => $convert_undef_to_NULL?,

95

oracle => $oracle_format?

96

);

97

98

Creates an object to insert data to a specific table. Buffering is enabled by default

99

and anything left on it will be written when the object falls out of scope / is destroyed.

Required parameters:

=over 4

=item * C : A L database handle.

=item * C : The name of the db table to insert to. See L if you

are using a restricted word for a table name.

=back

Optional parameters:

=over 4

=item * C : 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 L if you are using any restricted words for

column names.

=item * C : 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.

=item * C : For MySQL, define as C<'ignore'> or C<'update'> to get an

C or C query respectively. See L

for details on the latter.

=item * C : Applies to the hash inserts only. If true, any undefined

values will be converted to SQL's C (similar to the C 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.

=item * C : This is automatically set to true when an Oracle driver is

detected (using C<$dbh-E{Driver}-E{Name}>) and the module will produce the

Oracle C format. Specifying it manually in the constructor will override

the automatic detection - a false value will force the MySQL compatible multi-row

C 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.

=back

=cut

sub new {

18

584772

my $class = shift;

18

82

my %args = @_;

18

33

my $self = {};

18

39

bless($self, $class);

18

224

$self->{dbh} = $args{dbh} || croak("dbh parameter (db handle) required.");

17

181

$self->{table} = $args{table} || croak("table parameter required.");

16

36

$self->{cols} = $args{cols};

16

57

$self->{buffer} = $args{buffer} || 100;

16

36

$self->{dupes} = $args{duplicates};

16

71

$self->{null} = $args{null_undef};

16

33

$self->{oracle} = $args{oracle};

$self->{oracle} = 1

if !defined $self->{oracle}

&& $self->{dbh}->{Driver}->{Name}

16

104

&& $self->{dbh}->{Driver}->{Name} =~ /oracle/i;

16

45

$self->{dupes} = 'oracle' if $self->{oracle};

16

40

if ($self->{dupes}) {

7

34

$self->{ignore} = 1 if $self->{dupes} eq "ignore";

7

22

$self->{update} = 1 if $self->{dupes} eq "update";

}

16

54

$self->_cleanup();

16

57

return $self;

}

=head1 METHODS

=head2 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 C statement

if there was one called, 0 otherwise (buffer not full.

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

=over 4

=item Array mode

Pass the data for one or more rows in a flat array, buffering will work automatically

based on your C settings. Obviously your C<@column_data_array> has to contain

a multiple of the number of C 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 C.

=item Hash mode

Pass a reference to a hash containing the column names & values for a single row

of data. If C 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 C (which can be

automatically converted to C if the C option was set).

=item Flushing the buffer

Calling C 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).

=item 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 (C will be

defined from the array mode). Or you can start with hash mode (so C will be defined

from the very first hash), and after flushing the buffer you can switch to array mode.

=back

=cut

sub insert {

48

7912

my $self = shift;

48

292

return $self->_hash_insert(@_) if $_[0] and ref($_[0]);

# Empty the buffer with no argument

25

78

return $self->{buffer_counter} ? $self->_empty_buffer() : 0 unless @_;

croak("Calling insert without a hash requires cols defined in constructor")

19

151

unless $self->{cols};

croak("Insert arguments must be multiple of cols")

18

29

if scalar(@_) % scalar @{$self->{cols}};

18

1186

croak("Insert was previously called with hash argument (still in buffer)")

16

138

if $self->{hash_buffer};

15

33

my $ret = 0;

15

50

while (@_) {

22

34

my $rows = scalar(@_) / scalar @{$self->{cols}};

22

59

22

48

my $left = $self->{buffer} - $self->{buffer_counter}; # Space left in buffer

22

73

if ($rows > $left) { # Can't fit buffer

7

32

my $max = $left * scalar @{$self->{cols}};

7

15

7

15

push @{$self->{bind}}, splice(@_,0,$max);

7

38

7

18

$self->{buffer_counter} = $self->{buffer};

} else {

15

23

push @{$self->{bind}}, splice(@_);

15

52

15

32

$self->{buffer_counter} += $rows;

}

22

88

$ret = $self->_write_full_buffer() if $self->{buffer_counter} == $self->{buffer};

}

15

76

return $ret;

}

=head2 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 L's database handle method of the same name. See

that method's documentation for details and caveats depending on your DB driver.

=cut

sub last_insert_id {

2

8

my $self = shift;

2

12

$self->_empty_buffer() if $self->{buffer_counter};

2

9

return $self->{dbh}->last_insert_id(@_);

}

=head1 ATTRIBUTES

=head2 C

my $val = $sql->{last_retval}

The return value of the last DBI C is stored in this attribute. On a successful

insert it should contain the number of rows of that statement. Note that an C

call, depending on the buffering, may call C zero, one or more times.

=head2 C

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 C has

been called.

=head2 C

my $count = $sql->{buffer_counter}

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

=head1 FUNCTIONS

=head2 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:

=over 4

=item * C : If true, any undefined values will be converted to SQL's

C (similar to the C 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.

=item * C : For MySQL, define as C<'ignore'> or C<'update'> to get an

C or C query respectively. See L

for details on the latter.

=item * C : Will generate the Oracle C syntax (required for

pre-23c Oracle Databases).

=back

=cut

sub simple_insert {

18

265504

my $table = shift;

18

126

my $fields = shift;

18

20

my $opt = shift;

18

50

my $join = $opt->{oracle} ? "\n" : ",\n";

18

29

my ($placeh, @bind, @cols);

18

34

if (ref($fields) eq 'ARRAY') {

5

23

@cols = keys %{$fields->[0]};

5

19

5

17

my $head = $opt->{oracle} ? _oracle_into_head($table, \@cols) : "";

5

9

my @rows;

5

47

foreach my $f (@$fields) {

9

30

my ($row, @b) = _row_placeholders($f, \@cols, $opt->{null_undef}, $head);

9

23

push @rows, $row;

9

18

push @bind, @b;

}

5

16

$placeh = join($join, @rows);

} else {

13

33

@cols = keys %$fields;

13

26

my $head = $opt->{oracle} ? _oracle_into_head($table, \@cols) : "";

13

44

($placeh, @bind) = _row_placeholders($fields, \@cols, $opt->{null_undef}, $head);

}

return _create_insert_sql(

$table, \@cols, $placeh, $opt->{duplicates}

18

58

), @bind unless $opt->{oracle};

7

15

return _oracle_create_insert_sql($placeh), @bind;

}

=head2 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 C<$table> and an arrayref with the column

names C<\@columns>. See L if you want to quote table or column names.

Optional parameters:

=over 4

=item * C<$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.

=item * C<$variant> : For MySQL, passing C<'ignore'> as the 4th argument returns

an C query. Passing C<'update'> as the argument returns a query

containing an `ON DUPLICATE KEY UPDATE` clause (see L for further details).

Passing C<'oracle'> will create the C syntax for pre-23c Oracle DBs.

=back

=cut

sub multi_insert_sql {

27

17791

my $table = shift;

27

45

my $columns = shift;

27

83

my $num_rows = shift || 1;

27

44

my $variant = shift;

27

174

return unless $table && $columns && @$columns;

22

118

return _oracle_create_insert_sql(

_oracle_insert_into($table, $columns) x $num_rows

)

if $variant && $variant eq 'oracle';

16

83

my $placeholders =

join(",\n", ('(' . join(',', ('?') x @$columns) . ')') x $num_rows);

16

52

return _create_insert_sql($table, $columns, $placeholders, $variant)

}

## Private methods

sub _set_head {

9

17

my $self = shift;

$self->{row_head} =

$self->{oracle}

? _oracle_into_head($self->{table}, $self->{cols})

9

42

: "";

}

sub _hash_insert {

23

38

my $self = shift;

23

37

my $fields = shift;

23

37

my $ret = 0;

croak("Insert was previously called with an array argument (still in buffer)")

23

177

if $self->{buffer_counter} && !$self->{hash_buffer};

22

41

$self->{buffer_counter}++;

22

60

$self->{cols} = [keys %$fields] if !defined($self->{cols});

22

67

$self->_set_head unless defined $self->{row_head};

22

70

my ($row, @bind) = _row_placeholders($fields, $self->{cols}, $self->{null}, $self->{row_head});

22

39

push @{$self->{hash_buffer}}, $row;

22

59

22

32

push @{$self->{bind}}, @bind;

22

60

22

96

$ret = $self->_write_hash_buffer() if $self->{buffer_counter} == $self->{buffer};

22

79

return $ret;

}

sub _write_full_buffer {

14

270

my $self = shift;

$self->{full_buffer_insert} = $self->_prepare_full_buffer_insert()

14

52

if !$self->{full_buffer_insert};

14

104

$self->_execute($self->{full_buffer_insert});

14

48

$self->_cleanup();

14

50

return $self->{last_retval};

}

sub _prepare_full_buffer_insert {

8

16

my $self = shift;

$self->{full_buffer_insert} = $self->{dbh}->prepare(

8

26

multi_insert_sql(map {$self->{$_}} qw/table cols buffer dupes/)

32

81

);

}

sub _empty_buffer {

8

44

my $self = shift;

8

30

return $self->_write_hash_buffer() if $self->{hash_buffer};

5

11

my $rows = scalar(@{$self->{bind}}) / scalar @{$self->{cols}};

5

20

5

17

my $sth = $self->{dbh}->prepare(

multi_insert_sql(

$self->{table},

$self->{cols},

$rows,

$self->{dupes}

)

5

23

);

5

46

$self->_execute($sth);

5

35

$self->_cleanup();

5

16

return $self->{last_retval};

}

sub _write_hash_buffer {

13

23

my $self = shift;

13

43

my $join = $self->{oracle} ? "\n" : ",\n";

13

24

my $placeh = join($join, @{$self->{hash_buffer}});

13

45

my $sth = $self->{dbh}->prepare(

$self->{oracle}

? _oracle_create_insert_sql($placeh)

: _create_insert_sql($self->{table}, $self->{cols}, $placeh, $self->{dupes})

13

117

);

13

99

$self->_execute($sth);

13

36

$self->_cleanup();

13

41

return $self->{last_retval};

}

sub _execute {

32

51

my $self = shift;

32

48

my $sth = shift;

32

136

$self->{row_total} = 0 if !defined($self->{row_total});

32

53

$self->{last_retval} = $sth->execute(@{$self->{bind}});

32

106

32

282

$self->{row_total} += $self->{last_retval} if $self->{last_retval};

}

sub _cleanup {

48

83

my $self = shift;

48

108

$self->{bind} = undef;

48

116

$self->{hash_buffer} = undef;

48

93

$self->{buffer_counter} = 0;

}

sub DESTROY {

18

27594

my $self = shift;

# Empty buffer

18

124

$self->_empty_buffer() if $self->{buffer_counter};

}

## Private functions

sub _oracle_into_head {

15

22

my $table = shift;

15

17

my $cols = shift;

15

91

return "INTO $table(".join(",", @$cols).") VALUES";

}

sub _oracle_insert_into {

6

11

my $table = shift;

6

10

my $cols = shift;

6

16

return _oracle_into_head($table, $cols)."(".join(',', ('?') x @$cols).")\n";

}

sub _oracle_create_insert_sql {

17

31

my $placeh = shift;

17

32

chomp($placeh);

17

75

return "INSERT ALL\n$placeh\nSELECT 1 FROM dual";

}

sub _create_insert_sql {

40

2442

my $table = shift;

40

57

my $columns = shift;

40

62

my $placeh = shift;

40

202

my $variant = shift || "";

40

105

my $ignore = ($variant eq 'ignore') ? ' IGNORE' : '';

40

145

my $cols = join(',', @$columns);

40

92

my $sql = "INSERT$ignore INTO $table ($cols)\nVALUES $placeh";

40

88

$sql .= _on_duplicate_key_update($columns) if $variant eq 'update';

40

285

return $sql;

}

sub _row_placeholders {

52

179467

my $fields = shift;

52

70

my $cols = shift;

52

86

my $null = shift;

52

70

my $head = shift;

52

124

my $sql = $head ? "$head(" : "(";

52

84

my @bind = ();

52

58

my $val;

52

105

foreach my $key (@$cols) {

80

210

$fields->{$key} = \"NULL" if $null && !defined($fields->{$key});

80

154

if (ref($fields->{$key})) {

17

21

$val = ${$fields->{$key}};

17

33

} else {

63

151

$val = "?";

63

109

push @bind, $fields->{$key};

}

80

260

$sql .= "$val,";

}

52

171

chop($sql) if @$cols;

52

221

return "$sql)", @bind;

}

sub _on_duplicate_key_update {

8

3011

my $columns = shift;

return "\nON DUPLICATE KEY UPDATE "

8

28

. join(',', map {"$_=VALUES($_)"} @$columns);

11

85

}

=head1 NOTES

=head2 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 C or C parameter. E.g. for

620							MySQL with columns named C and C you can do:
621
622							cols => [qw/`from` `to`/]
623
624							For PostgreSQL or Oracle you'd do C<[qw/"from" "to"/]>, for SQL Server C<[qw/[from] [to]/]> etc.
625
626							=head2 On duplicate key update
627
628							The C 'update'> option creates an C clause
629							for the query. E.g.:
630
631							my $sql = multi_insert_sql('table_name', [qw/col1 col2/], 2, 'update');
632
633							will produce:
634
635							## INSERT INTO table_name (col1,col2) VALUES (?,?),(?,?) ON DUPLICATE KEY UPDATE col1=VALUES(col1),col2=VALUES(col2)
636
637							Note that as of MySQL 8.0.20 the C in C is deprecated (row alias is
638							used instead), so this functionality might need to be updated some day if C is
639							removed completely.
640
641							=head2 Output whitespace
642
643							No spaces are added to the output string beyond the minimum. However, there is a new
644							line (C<\n>) added for each row of value placeholders - mainly to easily count the
645							number of rows from the string.
646							Also, the C clause is on a new line.
647
648							=head2 Error handling
649
650							The module does not do any error handling on C/C statements,
651							you should use L's C and C.
652
653							=head2 Performance
654
655							The OO interface has minimal overhead. The only consideration is that if your rows
656							do not contain particularly large amounts of data, you may want to increase the buffer
657							size which is at a modest 100 rows.
658
659							Internally, to construct the prepared statements it uses similar logic to the public
660							functions. C is of particular interest as it is a minimalistic function
661							that may replace (similar interface / feature set) the C functions from
662							C or C while being over 40x faster than the former and
663							around 3x faster than the latter. The included C script gives
664							an idea (results on an M1 Pro Macbook):
665
666							Compare SQL::Abstract, SQL::Maker, simple_insert:
667							Rate Abstract Abstract cached Maker Maker cached simple_insert
668							Abstract 4207/s -- -6% -90% -91% -98%
669							Abstract cached 4482/s 7% -- -90% -90% -98%
670							Maker 44245/s 952% 887% -- -4% -76%
671							Maker cached 46205/s 998% 931% 4% -- -75%
672							simple_insert 187398/s 4355% 4081% 324% 306% --
673
674							Compare simple_insert, multi_insert_sql for single row:
675							Rate simple_insert multi_insert_sql
676							simple_insert 190037/s -- -76%
677							multi_insert_sql 797596/s 320% --
678
679							=head1 AUTHOR
680
681							Dimitrios Kechagias, C<< >>
682
683							=head1 BUGS
684
685							Please report any bugs or feature requests either on L (preferred), or on RT
686							(via the email , or L).
687
688							I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
689
690							=head1 GIT
691
692							L
693
694							=head1 CPAN
695
696							L
697
698							=head1 LICENSE AND COPYRIGHT
699
700							Copyright (C) 2023, SpareRoom
701
702							This is free software; you can redistribute it and/or modify it under
703							the same terms as the Perl 5 programming language system itself.
704
705							=cut
706
707							1; # End of SQL::Inserter