File Coverage

blib/lib/App/JESP.pm
Criterion Covered Total %
statement 154 163 94.4
branch 29 34 85.2
condition 5 9 55.5
subroutine 27 27 100.0
pod 3 3 100.0
total 218 236 92.3


line stmt bran cond sub pod time code
1             package App::JESP;
2             $App::JESP::VERSION = '0.015';
3 8     8   696043 use Moose;
  8         3665402  
  8         61  
4              
5 8     8   62160 use App::JESP::Plan;
  8         43  
  8         346  
6 8     8   3435 use App::JESP::Colorizer;
  8         3287  
  8         300  
7              
8 8     8   3818 use JSON;
  8         60739  
  8         63  
9 8     8   1211 use Class::Load;
  8         20  
  8         320  
10 8     8   8764 use DBI;
  8         113243  
  8         499  
11 8     8   3145 use DBIx::Simple;
  8         33845  
  8         278  
12 8     8   70 use File::Spec;
  8         20  
  8         180  
13 8     8   2527 use IO::Interactive;
  8         45517  
  8         52  
14 8     8   2835 use Log::Any qw/$log/;
  8         51159  
  8         46  
15              
16             # Settings
17             ## DB Connection attrbutes.
18             has 'dsn' => ( is => 'ro', isa => 'Str', required => 1 );
19             has 'username' => ( is => 'ro', isa => 'Maybe[Str]', required => 1);
20             has 'password' => ( is => 'ro', isa => 'Maybe[Str]', required => 1);
21             has 'home' => ( is => 'ro', isa => 'Str', required => 1 );
22              
23             ## JESP Attributes
24             has 'prefix' => ( is => 'ro', isa => 'Str', default => 'jesp_' );
25             has 'driver_class' => ( is => 'ro', isa => 'Str', lazy_build => 1);
26              
27             # Operational stuff
28             has 'get_dbh' => ( is => 'ro', isa => 'CodeRef', default => sub{
29             my ($self) = @_;
30             return sub{
31             return DBI->connect( $self->dsn(), $self->username(), $self->password(),
32             { RaiseError => 1,
33             PrintError => 0,
34             AutoCommit => 1,
35             });
36             };
37             });
38              
39             has 'dbix_simple' => ( is => 'ro', isa => 'DBIx::Simple', lazy_build => 1);
40             has 'patches_table_name' => ( is => 'ro', isa => 'Str' , lazy_build => 1);
41             has 'meta_patches' => ( is => 'ro', isa => 'ArrayRef[HashRef]',
42             lazy_build => 1 );
43              
44              
45             has 'plan' => ( is => 'ro', isa => 'App::JESP::Plan', lazy_build => 1);
46             has 'driver' => ( is => 'ro', isa => 'App::JESP::Driver', lazy_build => 1 );
47              
48             has 'interactive' => ( is => 'ro' , isa => 'Bool' , lazy_build => 1 );
49             has 'colorizer' => ( is => 'ro', isa => 'App::JESP::Colorizer', lazy_build => 1 );
50              
51             has json => ( is => "ro", lazy_build => 1 );
52 3     3   153 sub _build_json { JSON->new->relaxed(1) }
53              
54             sub _build_driver{
55 1     1   3 my ($self) = @_;
56 1         22 return $self->driver_class()->new({ jesp => $self });
57             }
58              
59             sub _build_driver_class{
60 1     1   2 my ($self) = @_;
61 1         20 my $db_name = $self->dbix_simple()->dbh()->{Driver}->{Name};
62 1         14 my $driver_class = 'App::JESP::Driver::'.$db_name;
63 1         9 $log->info("Loading driver ".$driver_class);
64 1         7 Class::Load::load_class( $driver_class );
65 1         84 return $driver_class;
66             }
67              
68             sub _build_plan{
69 4     4   13 my ($self) = @_;
70 4         117 my $file = File::Spec->catfile( $self->home(), 'plan.json' );
71 4 100 66     129 unless( ( -e $file ) && ( -r $file ) ){
72 1         13 die "File $file does not exists or is not readable\n";
73             }
74 3         127 return App::JESP::Plan->new({ file => $file, jesp => $self });
75             }
76              
77             sub _build_dbix_simple{
78 3     3   7 my ($self) = @_;
79 3         62 my $dbh = $self->get_dbh()->();
80 3         23081 my $db = DBIx::Simple->connect($dbh);
81             }
82              
83             sub _build_patches_table_name{
84 2     2   4 my ($self) = @_;
85 2         43 return $self->prefix().'patch';
86             }
87              
88             # Building the meta patches, in SQLite compatible format.
89             sub _build_meta_patches{
90 2     2   6 my ($self) = @_;
91             return [
92 2         61 { id => $self->prefix().'meta_zero', sql => 'CREATE TABLE '.$self->patches_table_name().' ( id VARCHAR(512) NOT NULL PRIMARY KEY, applied_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );' }
93             ];
94             }
95              
96             sub _build_interactive{
97 2     2   4 my ($self) = @_;
98 2         11 return IO::Interactive::is_interactive();
99             }
100              
101             sub _build_colorizer{
102 2     2   5 my ($self) = @_;
103 2         59 return App::JESP::Colorizer->new({ jesp => $self });
104             }
105              
106             sub install{
107 3     3 1 533 my ($self) = @_;
108              
109             # First try to select from $self->patches_table_name
110 3         87 my $dbh = $self->dbix_simple->dbh();
111 3         11 my $patches = eval{
112             $self->_protect_select(
113 3     3   62 sub{ $self->dbix_simple()->query('SELECT '.$dbh->quote_identifier('id').' FROM '.$dbh->quote_identifier($self->patches_table_name)); },
114 3         24 "CANNOT_SELECT_FROM_META");
115             };
116 3 100       324 if( my $err = $@ ){
117 2 50       15 unless( $err =~ /^CANNOT_SELECT_FROM_META\n/ ){
118 0         0 $log->critical("Unexpected error from _protect_select. Run again in verbose mode.");
119 0         0 die $err;
120             }
121 2         13 $log->info("Innitiating meta tables");
122 2         73 $self->_apply_meta_patch( $self->meta_patches()->[0] );
123             }
124 3         19 $log->info("Uprading meta tables");
125             # Select all meta patches and make sure all of mine are applied.
126 3         124 my $applied_patches = { $self->dbix_simple()
127             ->select( $self->patches_table_name() , [ 'id', 'applied_datetime' ] , { id => { -like => $self->prefix().'meta_%' } } )
128             ->map_hashes('id')
129             };
130 3         1858 foreach my $meta_patch ( @{ $self->meta_patches() } ){
  3         377  
131 4 100       15 if( $applied_patches->{$meta_patch->{id}} ){
132 3         21 $log->debug("Patch ".$meta_patch->{id}." already applied on ".$applied_patches->{$meta_patch->{id}}->{applied_datetime});
133 3         11 next;
134             }
135 1         5 $self->_apply_meta_patch( $meta_patch );
136             }
137 3         10 $log->info("Done upgrading meta tables");
138 3         20 return 1;
139             }
140              
141             sub _applied_patches{
142 8     8   14 my ($self) = @_;
143              
144 8         152 my $db = $self->dbix_simple();
145             my $applied_patches_result = $self->_protect_select(
146             sub{
147 8     8   169 $db->select( $self->patches_table_name() , [ 'id', 'applied_datetime' ] , {} , 'applied_datetime' );
148 8         38 }, "ERROR querying meta schema. Did you forget to run 'install'?");
149              
150 7         29 return { $applied_patches_result->map_hashes('id') };
151             }
152              
153             sub status{
154 3     3 1 11 my ($self) = @_;
155 3         68 my $plan_patches = $self->plan()->patches();
156 3         16 $log->debug("Got ".scalar(@$plan_patches)." patches to inspect");
157              
158 3         12 my $applied_patches = $self->_applied_patches();
159              
160 3         472 foreach my $plan_patch ( @$plan_patches ){
161 11 100       278 if( my $applied_patch = delete $applied_patches->{$plan_patch->id()} ){
162 7         150 $plan_patch->applied_datetime( $applied_patch->{applied_datetime} );
163 7         132 $log->info( $self->colorizer->colored('✔︎', "bold green")." '".$plan_patch->id()."' - Applied on ".$plan_patch->applied_datetime() );
164             }else{
165 4         76 $log->info( $self->colorizer->colored('âš ', "bold yellow")." '".$plan_patch->id()."' - Not applied" );
166             }
167             }
168              
169 3         66 my $meta_prefix = $self->prefix().'meta';
170              
171 3         11 my $plan_orphans = [ grep{ $_ !~ /^$meta_prefix/ } keys %$applied_patches ];
  4         25  
172 3 100       11 if( @$plan_orphans ){
173 1         21 $log->warn($self->colorizer()->colored('⚠︎', "bold red")." Got orphan patches (patches in meta table but not in plan): ".join(', ' , @$plan_orphans ) );
174             }
175              
176             return {
177 3         25 plan_patches => $plan_patches,
178             plan_orphans => $plan_orphans,
179             };
180             }
181              
182             sub deploy{
183 5     5 1 2772 my ($self, $options) = @_;
184              
185 5 100       16 defined($options) ||( $options = {} );
186              
187 5         18 $log->info("DEPLOYING DB Patches");
188              
189 5         137 my $db = $self->dbix_simple();
190 5         96 my $patches = $self->plan()->patches();
191              
192 5 100       13 if( $options->{patches} ){
193             # Filter existing patches.
194 1         3 my @patch_ids = @{$options->{patches}};
  1         4  
195 1         3 $log->info("Applying only patches ".join(', ', map{ "'".$_."'" } @patch_ids)." in this order");
  1         7  
196 1         5 my $patches_by_id = { map{ $_->id() => $_ } @$patches };
  4         75  
197 1         2 my @new_patch_list = ();
198 1         3 foreach my $patch_id ( @patch_ids ){
199 1         2 my $patch = $patches_by_id->{$patch_id};
200 1 50       4 unless( $patch ){
201 0         0 die "Cannot find patch '$patch_id' in the plan ".$self->plan()->file().". Check the name\n";
202             }
203 1         3 push @new_patch_list , $patch;
204             }
205 1         3 $patches = \@new_patch_list;
206             }
207              
208 5         12 my $applied_patches = $self->_applied_patches();
209              
210 4         646 my $applied = 0;
211 4         7 foreach my $patch ( @{$patches} ){
  4         8  
212 13 100       309 if( my $applied_patch = $applied_patches->{$patch->id()}){
213 9 100       20 unless( $options->{force} ){
214 4         74 $log->debug("Patch '".$patch->id()."' has already been applied on ".$applied_patch->{applied_datetime}." skipping");
215 4         14 next;
216             }
217 5         92 $log->warn("Patch '".$patch->id()."' has already been applied on ".$applied_patch->{applied_datetime}." but forcing it.");
218             }else{
219 4         123 $log->info("Patch '".$patch->id()."' never applied");
220             }
221 9         36 eval{
222 9         28 $db->begin_work();
223 9 100       398 if( my $already_applied = $db->select( $self->patches_table_name(), '*',
224             { id => $patch->id() } )->hash() ){
225 5         1249 $db->update( $self->patches_table_name(),
226             { applied_datetime => \'CURRENT_TIMESTAMP' },
227             { id => $patch->id() } );
228             } else {
229 4         1723 $db->insert( $self->patches_table_name() , { id => $patch->id() } );
230             }
231 9 100       2532 unless( $options->{logonly} ){
232 5         1068 $self->driver()->apply_patch( $patch );
233             }else{
234 4         613 $log->info("logonly mode. Patch not really applied");
235             }
236 9         40 $db->commit();
237             };
238 9 50       170 if( my $err = $@ ){
239 0         0 $log->error("Got error $err. ROLLING BACK");
240 0         0 $db->rollback();
241 0         0 die "ERROR APPLYING PATCH ".$patch->id().": $err. ".$self->colorizer()->colored("ABORTING", "bold red")."\n";
242             };
243 9         356 $log->info($self->colorizer()->colored("Patch '".$patch->id()."' applied successfully", "green"));
244 9         40 $applied++;
245             }
246 4         74 $log->info($self->colorizer()->colored("DONE Deploying DB Patches", "green"));
247 4         50 return $applied;
248             }
249              
250             # Runs the code to return a DBIx::Simple::Result
251             # or die with the given error message (for humans)
252             #
253             # Mainly this is for testing that a table exists by attemtpting to select from
254             # it. Do NOT use that in any other cases.
255             sub _protect_select{
256 11     11   26 my ( $self, $code , $message) = @_;
257 11         17 my $result = eval{ $code->(); };
  11         20  
258 11 100 66     13680 if( my $err = $@ || $result->isa('DBIx::Simple::Dummy') ){
259 3   33     25 $log->trace("Error doing select: ".( $err || $self->dbix_simple()->error() ) );
260 3         26 die $message."\n";
261             }
262 8         21 return $result;
263             }
264              
265             sub _apply_meta_patch{
266 3     3   8 my ($self, $meta_patch) = @_;
267 3         33 $log->debug("Applying meta patch ".$meta_patch->{id});
268              
269 3         15 my $sql = $meta_patch->{sql};
270 3         70 my $db = $self->dbix_simple();
271              
272 3         14 $log->debug("Doing ".$sql);
273 3         9 eval{
274 3         12 $db->begin_work();
275 3 50       103 $db->dbh->do( $sql ) or die "Cannot do '$sql':".$db->dbh->errstr()."\n";
276 3         1054 $db->insert( $self->patches_table_name() , { id => $meta_patch->{id} } );
277 3         11230 $db->commit();
278             };
279 3 50       457 if( my $err = $@ ){
280 0           $log->error("Got error $err. ROLLING BACK");
281 0           $db->rollback();
282 0           die "ERROR APPLYING META PATCH ".$meta_patch->{id}.": $err. ABORTING\n";
283             };
284             }
285              
286             __PACKAGE__->meta->make_immutable();
287             1;
288              
289             __END__
290              
291             =head1 NAME
292              
293             App::JESP - Just Enough SQL Patches
294              
295             =cut
296              
297             =head1 SYNOPSIS
298              
299             Use the command line utility:
300              
301             jesp
302              
303             Or use from your own program (in Perl):
304              
305             my $jesp = App::JESP->new({
306             interactive => 0, # No ANSI color
307             home => 'path/to/jesphome',
308             dsn => ...,
309             username => ...,
310             password => ...
311             });
312              
313             $jesp->install();
314             $jesp->deploy();
315              
316             =cut
317              
318             =head1 CONFIGURATION
319              
320             All JESP configuration must live in a JESP home directory.
321              
322             This home directory must contain a plan.json file, containing the patching
323             plan for your DB. See plan.json section below for the format of this file.
324              
325             =head2 plan.json
326              
327             This file MUST live in your JESP home directory. It has to contain
328             a json datastructure like this:
329              
330             {
331             "patches": [
332             { "id":"foobar_sql", "sql": "CREATE TABLE foobar(id INT PRIMARY KEY)"},
333             { "id":"foobar_rel", "file": "patches/morefoobar.sql" }
334             { "id":"foobar_abs", "file": "/absolute/path/to/patches/evenmore.sql" },
335             { "id":"a_backfill_script", "file": "path/to/executable/file.sh" },
336             ],
337             }
338              
339             Patches MUST have a unique C<id> in all the plan, and they can either contain raw SQL
340             (C<sql> key), or point to a C<file> (absolute, or relative to the JESP home) containing
341             the SQL.
342              
343             The C<id> is a VARCHAR(512). While it doesn't indicate any ordering, a simple and useful
344             way to keep the IDs unique is to provide a date/timestamp (of when the patch was
345             I<authored>) plus a free form description of the change.
346              
347             The L<JSON> file is parsed with the relaxed flag, which means it can contain trailing
348             commas (and # comments). The trailing commas are particularly useful, since commit diffs
349             and merge conflicts will be contained to the new line that was added.
350              
351             You are encouraged to look in L<https://github.com/jeteve/App-JESP/tree/master/t> for examples.
352              
353             =head1 PATCH TYPES
354              
355             =head1 sql
356              
357             Simply add the SQL statement to execute in your patch structure:
358              
359             {
360             "patches": [
361             ...
362             { "id":"2017-11-02: create table foobar", "sql": "CREATE TABLE foobar(id INT PRIMARY KEY)"}
363             ...
364             }
365              
366             This is convenient for very short SQL statment.
367              
368             =head1 sql files
369              
370             Point to a file that contains SQL statement(s) to be executed. The filename can be either absolute
371             or relative to the directory that contains the plan.
372              
373             {
374             "patches": [
375             ...
376             { "id":"2017-11-08: (rel) more foobar", "file": "patches/morefoobar.sql" }
377             { "id":"2017-11-12: (abs) even more", "file": "/absolute/path/to/patches/evenmore.sql" },
378             ...
379             ]
380             }
381              
382             The directory structure is free, but of course you are encouraged to keep it clean.
383              
384             =head1 script files (Unix only)
385              
386             Point to an EXECUTABLE file. (absolute or relative to the plan directory):
387              
388             {
389             "patches": [
390             ...
391             { "id":"a_backfill_script", "file": "path/to/executable/file.sh" }
392             ...
393             ]
394             }
395              
396             See L<APP::JESP::Driver#apply_script> to see what environment the script is being run in. Note that the script
397             needs to be executable by the current user to be detected as a script.
398              
399             =head1 COMPATIBILITY
400              
401             Compatibility of the meta-schema with SQLite, MySQL and PostgreSQL is guaranteed through automated testing.
402             To see which versions are actually tested, look at the CI build:
403             L<https://travis-ci.org/jeteve/App-JESP/>
404              
405             =head1 DRIVERS
406              
407             This comes with the following built-in drivers:
408              
409             =head2 SQLite
410              
411             Just in case. Note that your patches will be executed in the same connection
412             this uses to manage the metadata.
413              
414             =head2 mysql
415              
416             This will use the `mysql` executable on the disk (will look for it in PATH)
417             to execute your patches, exactly like you would do on the command line.
418              
419             =head2 Pg
420              
421             This will use a new connection to the Database to execute the patches.
422             This is to allow you using BEGIN ; COMMIT; to make your patch transactional
423             without colliding with the Meta data management transaction.
424              
425             =head2 Your own driver
426              
427             Should you want to write your own driver, simply extend L<App::JESP::Driver>
428             and implement any method you like (most likely you will want apply_sql).
429              
430             To use your driver, simply give its class to the constuctor:
431              
432             my $jesp = App::JESP->new({ .., driver_class => 'My::App::JESP::Driver::SpecialDB' });
433              
434             Or if you prefer to build an instance yourself:
435              
436             my $jesp;
437             $jesp = App::JESP->new({ .., driver => My::App::JESP::Driver::SpecialDB->new({ jesp => $jesp, ... ) });
438              
439             =head1 MOTIVATIONS & DESIGN
440              
441             Over the years as a developer, I have used at least three ways of managing SQL patches.
442             The ad-hoc way with a hand-rolled system which is painful to re-implement,
443             the L<DBIx::Class::Migration> way which I didn't like at all, and more recently
444             L<App::Sqitch> which I sort of like.
445              
446             All these systems somehow just manage to do the job, but unless they are very complicated (there
447             are no limits to hand-rolled complications..) they all fail to provide a sensible
448             way for a team of developers to work on database schema changes at the same time.
449              
450             So I decided the world needs yet another SQL patch management system that
451             does what my team and I really really want.
452              
453             Here are some design principles this package is attempting to implement:
454              
455             =over
456              
457             =item Write your own SQL
458              
459             No funny SQL generated from code here. By nature, any ORM will always lag behind its
460             target DBs' features. This means that counting on software to generate SQL statement from
461             your ORM classes will always prevent you from truly using the full power of your DB of choice.
462              
463             With App::JESP, you have to write your own SQL for your DB, and this is a good thing.
464              
465             =item No version numbers
466              
467             App::JESP simply keeps track of which ones of your named patches are applied to the DB.
468             Your DB version is just that: The subset of patches that were applied to it. This participates
469             in allowing several developers to work on different parts of the DB in parallel.
470              
471             =item No fuss patch ordering
472              
473             The order in which patches are applied is important. But it is not important
474             to the point of enforcing exactly the same order on every DB the patches are deployed to.
475             App::JESP applies the named patches in the order it finds them in the plan, only taking
476             into account the ones that have not been applied yet. This allows developer to work
477             on their development DB and easily merge patches from other developers.
478              
479             =item JSON Based
480              
481             This is the 21st century, and I feel like I shouldn't invent my own file format.
482             This uses JSON like everything else.
483              
484             =item Simple but complex things allowed.
485              
486             You will find no complex feature in App::JESP, and we pledge to keep the meta schema
487             simple, to allow for easy repairs if things go wrong.
488              
489             =item Programmable
490              
491             It's great to have a convenient command line tool to work and deploy patches, but maybe
492             your development process, or your code layout is a bit different. If you use L<App::JESP>
493             from Perl, it should be easy to embed and run it yourself.
494              
495             =item What about reverting?
496              
497             Your live DB is not the place to test your changes. Your DB at <My Software> Version N should
498             be compatible with Code at <My Software> Version N-1. You are responsible for testing that.
499              
500             We'll probably implement reverting in the future, but for now we assume you
501             know what you're doing when you patch your DB.
502              
503             =back
504              
505             =head1 METHODS
506              
507             =head2 install
508              
509             Installs or upgrades the JESP meta tables in the database. This is idempotent.
510             Note that the JESP meta table(s) will be all prefixed by B<$this->prefix()>.
511              
512             Returns true on success. Will die on error.
513              
514             Usage:
515              
516             $this->install();
517              
518             =head2 status
519              
520             Returns the list of L<App::JESP::Patch> with their application Datetime if this is known.
521             This will ALSO return the orphans (patches that are recorded as applied but not in the plan).
522              
523             Usage:
524              
525             my $status = $this->status();
526              
527             $status is like:
528             {
529             plan_patches => [ list of App::JESP::Patch ],
530             plan_orphans => [ list of orphan patch IDs ]
531             };
532              
533             =head2 deploy
534              
535             Deploys the unapplied patches from the plan in the database and record
536             the new DB state in the meta schema. Dies if the meta schema is not installed (see install method).
537              
538             Returns the number of patches applied.
539              
540             Usage:
541              
542             print "Applied ".$this->deploy()." patches";
543              
544             Options:
545              
546             =over
547              
548             =item patches [ 'patch_one' , 'patch_two' ]
549              
550             Specify the patches to apply. This is useful in combination with C<force>
551             (to force a data producing patch to run for instance), or with C<logonly>.
552              
553             =item force 1|0
554              
555             Force patches applications, regardless of the fact they have been applied already or not.
556             Note that it does not mean it's ok for the patches to fail. Any failing patch will still
557             terminate the deploy method. This is particularly useful in combination with the 'patches'
558             option where you can choose which patch to apply. Defaults to 0.
559              
560             =item logonly 1|0
561              
562             Only record the application of patches in the metadata, without effectively applying them.
563              
564             =back
565              
566              
567             =head1 DEVELOPMENT
568              
569             =for html <a href="https://travis-ci.org/jeteve/App-JESP"><img src="https://travis-ci.org/jeteve/App-JESP.svg?branch=master"></a>
570              
571             =head1 COPYRIGHT
572              
573             This software is released under the Artistic Licence by Jerome Eteve. Copyright 2016.
574             A copy of this licence is enclosed in this package.
575              
576             =cut