File Coverage

blib/lib/DB/SQL/Migrations/Advanced.pm
Criterion Covered Total %
statement 12 87 13.7
branch 0 10 0.0
condition n/a
subroutine 4 14 28.5
pod 4 4 100.0
total 20 115 17.3


line stmt bran cond sub pod time code
1             package DB::SQL::Migrations::Advanced;
2 1     1   67619 use Mojo::Base -base;
  1         189784  
  1         7  
3              
4 1     1   709 use DBIx::MultiStatementDo;
  1         652092  
  1         44  
5 1     1   10 use File::Basename;
  1         2  
  1         84  
6 1     1   737 use File::Slurp;
  1         25966  
  1         1640  
7              
8             our $VERSION = '0.0.1';
9              
10             has 'applied_migrations' => sub {
11             my $self = shift;
12              
13             my $sth = $self->dbh->prepare(sprintf('SELECT * FROM %s', $self->table));
14              
15             $sth->execute;
16              
17             # Example result:
18             # { 'migration_name_1.sql' => { date_applied => '2019-09-30', name => 'migration_name.sql', batch => 3 } }
19             my $applied_migrations = $sth->fetchall_hashref('name');
20              
21             $sth->finish;
22              
23             return $applied_migrations;
24             };
25              
26             has 'batch' => sub {
27             my $self = shift;
28              
29             my $sth = $self->dbh->prepare(sprintf('SELECT `batch` FROM %s ORDER BY `batch` DESC LIMIT 1', $self->table));
30              
31             $sth->execute;
32              
33             my @rows = $sth->fetchrow_array;
34             my $batch = $rows[0];
35              
36             $sth->finish;
37              
38             return $batch;
39             };
40              
41             has 'dbh';
42              
43             has 'folder' => sub { 'db_migrations' };
44              
45             has 'migration_files_in_order' => sub {
46             my $self = shift;
47            
48             my $dir = $self->folder;
49             my @migration_files_in_order = sort <$dir/*.sql>;
50              
51             return \@migration_files_in_order;
52             };
53              
54             has 'pending_migrations' => sub {
55             my $self = shift;
56             my @pending_migrations;
57              
58             foreach my $migration_file (@{ $self->migration_files_in_order }) {
59             my $migration_key = $self->_migration_key($migration_file);
60              
61             push(@pending_migrations, $migration_file) if (!$self->applied_migrations->{$migration_key});
62             }
63              
64             return \@pending_migrations;
65             };
66              
67             has 'rollback' => sub { 0 };
68              
69             has 'steps' => sub { 1 };
70              
71             has 'table' => sub { 'schema_migrations' };
72              
73             sub apply {
74 0     0 1   my $self = shift;
75              
76 0           my @pending_migrations = @{ $self->pending_migrations };
  0            
77              
78 0 0         if (scalar(@pending_migrations)) {
79 0           foreach my $migration (@pending_migrations) {
80 0           print "Appling migration $migration\t\t";
81              
82 0           $self->_run_migration($self->_sql($migration));
83 0           $self->_insert_into_schema_migrations($migration);
84              
85 0           print "OK\n";
86             }
87              
88 0           print "\nDONE\n";
89              
90 0           return 1;
91             }
92              
93 0           print "Nothing to migrate\n";
94              
95 0           return 1;
96             }
97              
98             sub create_migrations_table {
99 0     0 1   my $self = shift;
100              
101 0           my $table_name = $self->table;
102              
103 0           my $sql = "CREATE TABLE IF NOT EXISTS $table_name (
104             `name` varchar(255) NOT NULL,
105             `date_applied` datetime NOT NULL,
106             `batch` int(11) NOT NULL DEFAULT '0',
107             PRIMARY KEY (`name`)
108             );
109             ";
110              
111 0           $self->dbh->do($sql);
112             }
113              
114             sub revert {
115 0     0 1   my $self = shift;
116              
117 0           my $batch = $self->batch;
118 0           my $steps = $self->steps;
119              
120 0           while ($steps--) {
121 0           print "Reverting all migrations for batch #$batch\n";
122              
123 0           my $sth = $self->dbh->prepare(sprintf('SELECT `name` FROM %s WHERE `batch` = ? ORDER BY `date_applied` DESC',
124             $self->table,
125             ));
126              
127 0           $sth->execute($batch);
128              
129             # Example result:
130             # [['migration_3.sql'], ['migration_2.sql']]
131 0           my $revert_migrations = $sth->fetchall_arrayref;
132              
133 0           $sth->finish;
134              
135 0           foreach my $row (@$revert_migrations) {
136 0           my $migration = $row->[0];
137              
138 0           print "Reverting migration $migration\t\t";
139              
140 0           $self->_revert_migration($self->folder . '/'. $migration);
141 0           $self->_remove_from_schema_migrations($migration);
142              
143 0           print "OK\n";
144             }
145            
146 0           $batch--;
147             }
148              
149 0           print "\nDONE\n";
150             }
151              
152             sub run {
153 0     0 1   my $self = shift;
154              
155 0           $self->create_migrations_table();
156              
157 0 0         if ($self->rollback) {
158 0           return $self->revert;
159             }
160              
161 0           return $self->apply;
162             }
163              
164             sub _insert_into_schema_migrations {
165 0     0     my ($self, $filename) = @_;
166              
167 0           my $migration_key = $self->_migration_key($filename);
168              
169 0           $self->dbh->do(sprintf('INSERT INTO %s (`name`, `date_applied`, `batch`) VALUES (?, NOW(), ?)',
170             $self->table,
171             ), undef, $migration_key, $self->batch + 1);
172              
173 0           $self->dbh->commit;
174             }
175              
176             sub _migration_key {
177 0     0     my ($self, $filename) = @_;
178              
179             # Use filename for the key
180 0           my ($migration_key, $directories, $suffix) = fileparse($filename);
181              
182 0           return $migration_key;
183             }
184              
185             sub _revert_migration {
186 0     0     my ($self, $filename) = @_;
187              
188 0           my $sql = $self->_sql($filename);
189              
190 0           $self->_run_migration($sql);
191             }
192              
193             sub _remove_from_schema_migrations {
194 0     0     my ($self, $filename) = @_;
195              
196 0           my $migration_key = $self->_migration_key($filename);
197              
198 0           $self->dbh->do(sprintf('DELETE FROM %s WHERE `name` = ?', $self->table), undef, $migration_key);
199              
200 0           $self->dbh->commit;
201             }
202              
203             sub _run_migration {
204 0     0     my ($self, $sql) = @_;
205              
206 0 0         if (!$sql) {
207 0           print "Got empty sql. Skipping\n";
208              
209 0           return;
210             }
211              
212 0           my $batch = DBIx::MultiStatementDo->new(
213             dbh => $self->dbh,
214             rollback => 0
215             );
216              
217 0           $batch->dbh->{AutoCommit} = 0;
218 0           $batch->dbh->{RaiseError} = 1;
219              
220             eval {
221 0           $batch->do( $sql );
222 0           $batch->dbh->commit;
223 0           1
224 0 0         } or do {
225 0           print "FAILED\n";
226 0           print "$@ \n";
227 0           eval { $batch->dbh->rollback };
  0            
228              
229 0           die "Exiting due to failed migrations \n";
230             };
231             }
232              
233             sub _sql {
234 0     0     my ($self, $filename) = @_;
235              
236 0           my $sql = read_file($filename);
237 0           my ($up, $down) = split("-- DOWN\n", $sql);
238              
239 0 0         if ($self->rollback) {
240 0           return $down;
241             }
242              
243 0           return $up;
244             }
245              
246             1;
247              
248             =encoding utf8
249              
250             =head1 NAME
251              
252             DB::SQL::Migrations::Advanced - apply/rollback migrations from a directory
253              
254             =head1 SYNOPSIS
255              
256             use DB::SQL::Migrations::Advanced;
257             my $migrator = DB::SQL::Migrations::Advanced->new(
258             dbh => $some_db_handle,
259             folder => $some_path,
260             );
261              
262             $migrator->run;
263              
264             use DB::SQL::Migrations::Advanced;
265             my $migrator = DB::SQL::Migrations::Advanced->new(
266             dbh => $some_db_handle,
267             folder => $some_path,
268             rollback => 1,
269             steps => 3,
270             );
271              
272             $migrator->run;
273              
274             =head1 DESCRIPTION
275              
276             L<DB::SQL::Migrations::Advanced> provides a nice way to apply migrations from a directory.
277             It has the ability to also rollback migrations.
278              
279             =head1 ATTRIBUTES
280              
281             L<DB::SQL::Migrations::Advanced> inherits all attributes from
282             L<Mojo::Base> and implements the following new ones.
283              
284             =head2 applied_migrations
285              
286             Tell list of already applied migrations.
287              
288             =head2 batch
289              
290             Tell value for the last batch of migrations.
291              
292             =head2 dbh
293              
294             A database handler. (required)
295              
296             =head2 folder
297              
298             Tell name of the folder that holds the migration files.
299             Defaults to 'db_migrations'.
300              
301             =head2 migration_files_in_order
302              
303             Migration files in alphabetical order.
304              
305             =head2 pending_migrations
306              
307             Migrations that needs to be applied.
308              
309             =head2 rollback
310              
311             Tell if you are looking to rollback some migrations.
312             Defaults to 0.
313              
314             =head2 steps
315              
316             In case you are looking to rollback mirations, you can specify how many steps.
317             Defaults to 1.
318              
319             =head2 table
320              
321             The name of the table that records the applied migrations.
322             Defaults to 'schema_migrations'.
323              
324             =head1 METHODS
325              
326             L<DB::SQL::Migrations::Advanced> inherits all methods from
327             L<Mojo::Base> and implements the following new ones.
328              
329             =head2 apply
330              
331             $migrator->apply
332              
333             Apply all pending migrations.
334              
335             =head2 create_migrations_table
336              
337             $migrator->create_migrations_table
338              
339             Create migrations table if not exists.
340              
341             =head2 revert
342              
343             $migrator->revert
344              
345             Revert migrations.
346              
347             =head2 run
348              
349             $migrator->run
350              
351             Run migrations.
352             Will decide if it needs to apply/rollback based on given attributes.
353              
354             =head2 _insert_into_schema_migrations
355              
356             $migrator->_insert_into_schema_migrations($filename)
357              
358             Record migration filename as being applied.
359              
360             =head2 _migration_key
361              
362             my $migration_key = $migrator->_migration_key($filename)
363              
364             Retrieve the migration_key for a filename.
365              
366             =head2 _revert_migration
367              
368             $migrator->_revert_migration($filename)
369              
370             Revert migrations from filename.
371              
372             =head2 _remove_from_schema_migrations
373              
374             $migrator->_remove_from_schema_migrations($filename)
375              
376             Remove migration filename from table.
377              
378             =head2 _run_migration
379              
380             $migrator->_run_migration($sql)
381              
382             Run an sql.
383              
384             =head2 _sql
385              
386             my $sql = $migrator->_sql($filename)
387              
388             Retrieve the sql that needs to be run from a migration filename, based on apply/rollback.
389              
390              
391             =head1 AUTHOR
392              
393             Adrian Crisan, <adrian.crisan88@gmail.com>
394              
395             =cut