1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
#!/usr/bin/perl -w
# Copyright (C) 2014-2018 Miquel Sabaté Solà <mikisabate@gmail.com>
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
# This script allows us to connect with psql properly for the given environment.
#
# The environments that we can use have to be specified in a JSON file. This
# JSON file has to be specified in the "$json" configurable value (by default
# is "db/database.json"). This JSON file has to specify the environments
# available and a set of values. Each environment has to specify the following
# values:
#
# * dbname: the name of the database.
# * host: the host to be used.
# * user: the user that is accessing the database.
# * password: the password for the user@host.
#
# More values can be specified, but they will be ignored by this script. You
# can find an example of this in the examples/database.json file.
#
# You can call this script with the following optional arguments:
#
# -e name
# This argument can be used to specify the environment that we want to
# connect to. If this argument has not been passed, it will take the
# value of the "$default" configurable value ("development" by default).
# Note that this argument can be used in combination with other
# arguments.
#
# -m, --migrate
# This argument can be used to migrate a set of .sql files into the
# database. The path to these files can be set with the "$migrate"
# configurable value (by default it is "db/migrate"). Keep in mind that
# migration files can have the comment "-- ignore." in the beginning of
# the file (other previous comments or blank lines will be ignored). This
# comment tells this script to ignore this migration file.
#
# -b, --backup
# Spit to the STDOUT a backup of the database.
#
# Therefore, this command has the following usage:
#
# $ db [-e name] [[-m | --migrate] | [-b | --backup]]
#
use strict;
use File::Basename;
use Cwd 'abs_path', 'getcwd';
##
# Config values. Ideally you should only modify these values to adapt this
# script to your project.
#
# NOTE: path values should *not* start and/or end with a slash.
# The base path that this script will use. By default it picks the current
# working directory.
my $base = abs_path(getcwd);
# The relative path to the json file (including the name of the json file
# itself).
my $json = 'db/database.json';
# The default environment. This is the environment to be used when no
# environment has been given through the '-e' option.
my $default = 'development';
# The relative path with migration files. Migration files are plain .sql files
# that will be used with the '-m' option.
my $migrate = 'db/migrate';
##
# And here starts the program itself.
# Show the usage string and exit.
sub usage {
print "Usage: db [-e name] [[-m | --migrate] | [-b | --backup]]\n";
print " -e Specify an environment.\n";
print " -m Migrate all the files from the db/migrate directory.\n";
print " -b Backup the proper DB.\n";
exit(1);
}
# Check that a given value has been defined in the given hash.
sub check {
my ($ary, $value) = @_;
if (!defined($ary->{$value})) {
print "ERROR: you haven't defined the '$value' value.\n";
exit(1);
}
}
# Returns true if we should ignore the given migration.
sub should_ignore {
my ($path) = @_;
my $res = 0;
if (open(MIGRATION, $path)) {
while (<MIGRATION>) {
if ($_ =~ /^--\s*ignore\.?/) {
# Ok boss, let's ignore it.
$res = 1;
last;
} elsif ($_ =~ /^\s*$/) {
# It's a blank line, next.
next;
} elsif ($_ =~ /^--/) {
# It's an SQL comment, next.
next;
}
last;
}
close(MIGRATION);
}
return $res;
}
# Apply all the migrations if needed.
sub migrate {
my ($cmd, $path) = @_;
print "Starting migration\n";
opendir(DIR, $path) or die $!;
while (my $file = readdir(DIR)) {
next if ($file =~ m/^\./);
next if ($file !~ m/\.sql$/);
if (should_ignore("$path/$file")) {
print "Ignoring migration: $file\n";
} else {
print "Applying migration: $file\n";
system("$cmd < $path/$file\n");
}
}
closedir(DIR);
}
# Parsing options.
my %opts = ('e', $default, 'm', 0, 'b', 0);
for (my $it = 0; $it < @ARGV; $it++) {
if ($ARGV[$it] eq '-e') {
usage() if (!$ARGV[$it + 1]);
$opts{e} = $ARGV[$it + 1];
$it++;
} elsif ($ARGV[$it] eq '-m' || $ARGV[$it] eq '--migrate') {
$opts{m} = 1;
} elsif ($ARGV[$it] eq '-b' || $ARGV[$it] eq '--backup') {
$opts{b} = 1;
} else {
if ($ARGV[$it] ne '-h' && $ARGV[$it] ne '--help') {
print "Unknown option `$ARGV[$it]'\n\n";
}
usage();
}
}
# Let's fetch the configuration of the environment.
my $config = "$base/$json";
if (!-f $config) {
print "ERROR: the $config file does not exist!\n";
exit(1)
}
open(FILE, $config);
my $file = '';
while (<FILE>) {
my $line = $_;
chomp($line);
$file .= " $line";
}
close(FILE);
# Fetch the values.
$file =~ m/"$opts{e}":\s?({(.*?)})/;
my $contents = $1;
if (!defined($contents)) {
print "The `$opts{e}' environment does not exist.\n";
exit(1);
}
# Setup the command.
my %ary = ();
$ary{$1} = $2 while ($contents =~ /"(\w+)":\s?"([\w\-]*)"/g);
foreach (qw{dbname user host password}) {
check(\%ary, $_);
}
my $opts = "-d $ary{'dbname'} -U $ary{'user'} -h $ary{'host'}";
my $cmd = "PGPASSWORD=$ary{'password'} psql $opts";
# Now execute the command properly.
if ($opts{m}) {
migrate($cmd, "$base/$migrate");
} elsif ($opts{b}) {
my $opts = "-d $ary{'dbname'} -U $ary{'user'}";
system("PGPASSWORD=$ary{'password'} pg_dump $opts");
} else {
system("$cmd");
}
|
