line |
stmt |
bran |
cond |
sub |
pod |
time |
code |
1
|
|
|
|
|
|
|
package SQL::Abstract::Plugin::Upsert; |
2
|
|
|
|
|
|
|
|
3
|
4
|
|
|
4
|
|
2453
|
use Moo; |
|
4
|
|
|
|
|
13
|
|
|
4
|
|
|
|
|
38
|
|
4
|
|
|
|
|
|
|
with 'SQL::Abstract::Role::Plugin'; |
5
|
|
|
|
|
|
|
|
6
|
|
|
|
|
|
|
sub register_extensions { |
7
|
9
|
|
|
9
|
0
|
5040
|
my ($self, $sqla) = @_; |
8
|
|
|
|
|
|
|
$sqla->clauses_of( |
9
|
|
|
|
|
|
|
'insert' => sub { |
10
|
9
|
|
|
9
|
|
142
|
my ($self, @clauses) = @_; |
11
|
9
|
|
|
|
|
28
|
splice @clauses, -1, 0, 'on_conflict'; |
12
|
9
|
|
|
|
|
39
|
@clauses; |
13
|
|
|
|
|
|
|
} |
14
|
9
|
|
|
|
|
89
|
); |
15
|
|
|
|
|
|
|
$sqla->clause_expander( |
16
|
|
|
|
|
|
|
'insert.on_conflict' => sub { |
17
|
6
|
|
|
6
|
|
1164
|
my ($sqla, $name, $value) = @_; |
18
|
|
|
|
|
|
|
# a 0 is DO NOTHING |
19
|
6
|
100
|
|
|
|
34
|
return (on_conflict => { -do => 'NOTHING' }) unless $value; |
20
|
|
|
|
|
|
|
|
21
|
|
|
|
|
|
|
# if we have keys that aren't prefixed by -, it's { TARGET => { SET_THIS => TO_THIS } } |
22
|
2
|
100
|
66
|
|
|
24
|
if (!grep /^-/, keys %$value and keys %$value == 1) { |
23
|
1
|
|
|
|
|
2
|
my @built; |
24
|
1
|
|
|
|
|
4
|
for my $target (keys %$value) { |
25
|
1
|
|
|
|
|
5
|
$value = { -target => $target, -set => $value->{$target} }; |
26
|
|
|
|
|
|
|
} |
27
|
|
|
|
|
|
|
} |
28
|
2
|
|
|
|
|
20
|
my (undef, $set) = $sqla->expand_clause('update.set', $value->{-set}); |
29
|
2
|
|
|
|
|
12
|
my $target = $sqla->expand_expr({ -list => $value->{-target} }, -ident); |
30
|
2
|
|
|
|
|
401
|
return (on_conflict => { -target => $target, -set => $set }); |
31
|
|
|
|
|
|
|
} |
32
|
9
|
|
|
|
|
272
|
); |
33
|
|
|
|
|
|
|
$sqla->clause_renderer( |
34
|
|
|
|
|
|
|
'insert.on_conflict' => sub { |
35
|
6
|
|
|
6
|
|
7898
|
my ($sqla, $type, $value) = @_; |
36
|
6
|
|
|
|
|
56
|
my @parts; |
37
|
6
|
|
|
|
|
27
|
@parts = { -keyword => 'on conflict' }; |
38
|
6
|
100
|
|
|
|
25
|
if (my $target = $value->{-target}) { |
39
|
2
|
|
|
|
|
8
|
push @parts, '(', $sqla->render_aqt($target), ')'; |
40
|
|
|
|
|
|
|
} |
41
|
6
|
100
|
|
|
|
147
|
if (my $what_to_do = $value->{-do}) { |
42
|
4
|
|
|
|
|
16
|
push @parts, { -keyword => "DO $what_to_do" }; |
43
|
|
|
|
|
|
|
} |
44
|
6
|
100
|
|
|
|
19
|
if (my $set = $value->{-set}) { |
45
|
2
|
|
|
|
|
8
|
push @parts, { -keyword => 'DO UPDATE SET' }; |
46
|
2
|
|
|
|
|
4
|
push @parts, $set; |
47
|
|
|
|
|
|
|
} |
48
|
6
|
|
|
|
|
23
|
$sqla->join_query_parts(' ', @parts); |
49
|
|
|
|
|
|
|
} |
50
|
9
|
|
|
|
|
621
|
); |
51
|
|
|
|
|
|
|
} |
52
|
|
|
|
|
|
|
|
53
|
|
|
|
|
|
|
our $VERSION = '0.01_2'; |
54
|
|
|
|
|
|
|
|
55
|
|
|
|
|
|
|
1; |
56
|
|
|
|
|
|
|
|
57
|
|
|
|
|
|
|
=encoding utf8 |
58
|
|
|
|
|
|
|
|
59
|
|
|
|
|
|
|
=head1 NAME |
60
|
|
|
|
|
|
|
|
61
|
|
|
|
|
|
|
SQL::Abstract::Plugin::Upsert - Upsert (ON CONFLICT) support for SQLA2! |
62
|
|
|
|
|
|
|
|
63
|
|
|
|
|
|
|
=head1 SYNOPSIS |
64
|
|
|
|
|
|
|
|
65
|
|
|
|
|
|
|
# pass this to an SQLA 'insert' |
66
|
|
|
|
|
|
|
{ on_conflict => 0 } |
67
|
|
|
|
|
|
|
# ON CONFLICT DO NOTHING |
68
|
|
|
|
|
|
|
|
69
|
|
|
|
|
|
|
# Do an update |
70
|
|
|
|
|
|
|
{ on_conflict => { id => { name => 'Bob Bobson' } } } |
71
|
|
|
|
|
|
|
# ON CONFLICT (id) DO UPDATE SET name = 'Bob Bobson' |
72
|
|
|
|
|
|
|
|
73
|
|
|
|
|
|
|
# Slightly fancier |
74
|
|
|
|
|
|
|
{ on_conflict => { id => { name => \'name || ' ' || excluded.name } } } |
75
|
|
|
|
|
|
|
# ON CONFLICT (id) DO UPDATE SET name = name || ' ' || excluded.name |
76
|
|
|
|
|
|
|
|
77
|
|
|
|
|
|
|
# More explicit |
78
|
|
|
|
|
|
|
{ on_conflict => { -target => 'id', -set => { name => 'Bob Bobson' } } } |
79
|
|
|
|
|
|
|
# ON CONFLICT (id) DO UPDATE SET name = 'Bob Bobson' |
80
|
|
|
|
|
|
|
|
81
|
|
|
|
|
|
|
=head1 DESCRIPTION |
82
|
|
|
|
|
|
|
|
83
|
|
|
|
|
|
|
This is a work in progress to support upserts in SQLA2. |
84
|
|
|
|
|
|
|
|
85
|
|
|
|
|
|
|
B |
86
|
|
|
|
|
|
|
|
87
|
|
|
|
|
|
|
=head2 Using with DBIx::Class |
88
|
|
|
|
|
|
|
|
89
|
|
|
|
|
|
|
In order to use this with DBIx::Class, you need to add plugins to your Result and ResultSet classes. |
90
|
|
|
|
|
|
|
|
91
|
|
|
|
|
|
|
# In your Result:: Classes (you could also just inherit from it) |
92
|
|
|
|
|
|
|
__PACKAGE__->load_components('Row::SQLA2Support'); |
93
|
|
|
|
|
|
|
|
94
|
|
|
|
|
|
|
# In your ResultSet Classes (you could also just inherit from it) |
95
|
|
|
|
|
|
|
__PACKAGE__->load_components('ResultSet::SQLA2Support') |
96
|
|
|
|
|
|
|
|
97
|
|
|
|
|
|
|
Now you can do the following cool things! |
98
|
|
|
|
|
|
|
|
99
|
|
|
|
|
|
|
=head3 create |
100
|
|
|
|
|
|
|
|
101
|
|
|
|
|
|
|
When making a new Row (like using $rs->create and friends), you can pass in a -on_conflict key which will get passed through to the INSERT for that row. |
102
|
|
|
|
|
|
|
|
103
|
|
|
|
|
|
|
$rs->create({ id => 3, name => 'John', -on_conflict => 0 }); |
104
|
|
|
|
|
|
|
# ON CONFLICT DO NOTHING |
105
|
|
|
|
|
|
|
|
106
|
|
|
|
|
|
|
You can also pass a -upsert key to let us create the correct ON CONFLICT clause to just |
107
|
|
|
|
|
|
|
stomp any existing row. This is safer than the usual find_or_create. This handles |
108
|
|
|
|
|
|
|
composite PKs just fine, by the way. |
109
|
|
|
|
|
|
|
|
110
|
|
|
|
|
|
|
$rs->create({ id => 3, name => 'Bob Bobson', -upsert => 1 }) |
111
|
|
|
|
|
|
|
# ON CONFLICT (id) DO UPDATE SET name = 'Bob Bobson' |
112
|
|
|
|
|
|
|
|
113
|
|
|
|
|
|
|
=head3 populate |
114
|
|
|
|
|
|
|
|
115
|
|
|
|
|
|
|
When doing a multi-insert, you can pass in a second arg after the rows to be passed |
116
|
|
|
|
|
|
|
through to SQLA2; this allows you to do a blanket ON CONFLICT DO NOTHING for the whole bunch of INSERTs. |
117
|
|
|
|
|
|
|
|
118
|
|
|
|
|
|
|
$rs->populate([ |
119
|
|
|
|
|
|
|
# one million rows later |
120
|
|
|
|
|
|
|
], { on_conflict => 0 }) |
121
|
|
|
|
|
|
|
|
122
|
|
|
|
|
|
|
=cut |