Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 1427 lines (1141 sloc) 44.821 kB
e5a7224 @theory Start sqitchtutorial.
authored
1 =head1 Name
2
3 sqitchtutorial - A tutorial introduction to Sqitch
4
5 =head1 Synopsis
6
7 sqitch *
8
9 =head1 Description
10
11 This tutorial explains how to create a sqitch-enabled project, use a VCS for
12 deployment planning, and work with other developers to make sure changes
13 remain in sync and in the proper order.
14
21d71c4 @theory Some tutorial tweaks.
authored
15 We'll start by creating new project from scratch, a fictional antisocial
16 networking site called Flipr. All examples use L<Git|http://git-scm.com/> as
17 the VCS and L<PostgreSQL|http://www.postgresql.org/> as the storage engine,
18 but for the most part you can substitute other VCSes and database engines in
19 the examples as appropriate.
e5a7224 @theory Start sqitchtutorial.
authored
20
21 =head1 Starting a New Project
22
23 Usually the first thing to do when starting a new project is to create a
24 source code repository. So let's do that with Git:
25
26 > mkdir flipr
27 > cd flipr
28 > git init .
29 Initialized empty Git repository in /flipr/.git/
30 > touch README.md
31 > git add .
32 > git commit -am 'Fist post!'
33
34 If you're a Git user and want to follow along the history, the repository
35 used in these examples is L<on GitHub|https://github.com/theory/sqitch-intro>.
36
37 Now that we have a repository, let's get started with Sqitch.
38
d0f4f2c @theory Start to update tutorial based on actual output.
authored
39 > sqitch --engine pg init
e5a7224 @theory Start sqitchtutorial.
authored
40 Created sql/deploy
41 Created sql/revert
d0f4f2c @theory Start to update tutorial based on actual output.
authored
42 Created sql/test
d7370f1 @theory Start work on `write_config()`.
authored
43 Created sqitch.conf
e5a7224 @theory Start sqitchtutorial.
authored
44
8ded6d4 @theory sqitchtutorial =~ s/\Q.ini/.conf/g;
authored
45 Let's have a look at F<sqitch.conf>:
e5a7224 @theory Start sqitchtutorial.
authored
46
d0f4f2c @theory Start to update tutorial based on actual output.
authored
47 [core]
48 engine = pg
49 # plan_file = sqitch.plan
50 # sql_dir = sql
51 # deploy_dir = sql/deploy
52 # revert_dir = sql/revert
53 # test_dir = sql/test
54 # extension = sql
f5a1c8a @theory Show commented-out engine config in tutorial.
authored
55 # [core "pg"]
56 # db_name =
57 # client = psql
58 # sqitch_schema = sqitch
59 # password =
60 # port =
61 # host =
62 # username =
e5a7224 @theory Start sqitchtutorial.
authored
63
64 Pretty simple. It picked up on the fact that we're creating changes for the
65 PostgreSQL engine, thanks to the C<--engine pg> option, and saved it to the
f5a1c8a @theory Show commented-out engine config in tutorial.
authored
66 file. It also wrote a commented-out C<[core "pg"]> section with all the
67 available PostgreSQL engine-specific settings commented out and ready to be
68 edited as appropriate.
69
70 By default, Sqitch will read F<sqitch.conf> in the current directory for
8ded6d4 @theory sqitchtutorial =~ s/\Q.ini/.conf/g;
authored
71 settings. But it will also read F<~/.sqitch/sqitch.conf> for global settings.
e5a7224 @theory Start sqitchtutorial.
authored
72 Since PostgreSQL's C<psql> client is not in the path on my system, let's go
73 ahead an tell it globally where to find the client:
74
8ded6d4 @theory sqitchtutorial =~ s/\Q.ini/.conf/g;
authored
75 sqitch config --user core.pg.client /var/lib/pgsql/bin/psql
e5a7224 @theory Start sqitchtutorial.
authored
76
8ded6d4 @theory sqitchtutorial =~ s/\Q.ini/.conf/g;
authored
77 Have a look at F<~/.sqitch/sqitch.conf> and you'll see this:
e5a7224 @theory Start sqitchtutorial.
authored
78
d0f4f2c @theory Start to update tutorial based on actual output.
authored
79 [core "pg"]
e5a7224 @theory Start sqitchtutorial.
authored
80 client = /var/lib/pgsql/bin/psql
81
82 Back to the repository. Let's commit these changes and start creating the
83 database changes.
84
e41bc5e @theory Add example of first deployment to tutorial.
authored
85 > git add .
86 > git commit -am 'Initialize Sqitch configuration.'
d0f4f2c @theory Start to update tutorial based on actual output.
authored
87 [master (root-commit) a22000d] Initialize Sqitch configuration.
88 1 file changed, 16 insertions(+)
89 create mode 100644 sqitch.conf
e41bc5e @theory Add example of first deployment to tutorial.
authored
90
abfa9e7 @theory Use `sqitch status` throughout.
authored
91 =head1 Our First Deployment
e41bc5e @theory Add example of first deployment to tutorial.
authored
92
93 First, our project will need a database user. This is the application user,
94 who will have only limited access to objects in the database. Run this command:
95
96 > sqitch add-step appuser
d0f4f2c @theory Start to update tutorial based on actual output.
authored
97 Created sql/deploy/appuser.sql
98 Created sql/revert/appuser.sql
99 Created sql/test/appuser.sql
e41bc5e @theory Add example of first deployment to tutorial.
authored
100
abfa9e7 @theory Use `sqitch status` throughout.
authored
101 The C<add-step> command creates deploy and revert scripts that represent a new
102 change step. Now we edit these files. The C<deploy> script's job is to create
103 the user. So we add this to F<sql/deploy/appuser.sql>:
e41bc5e @theory Add example of first deployment to tutorial.
authored
104
105 CREATE ROLE flipr WITH LOGIN;
106
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
107 The C<revert> script's job is to precisely revert the change to the deploy
abfa9e7 @theory Use `sqitch status` throughout.
authored
108 script, so we add this to F<sql/revert/appuser.sql>:
e41bc5e @theory Add example of first deployment to tutorial.
authored
109
110 DROP ROLE flipr;
111
112 Now we can try deploying this change:
113
d0f4f2c @theory Start to update tutorial based on actual output.
authored
114 > createdb flipr_test
54bf216 @theory Better empty tags; show status and log.
authored
115 > sqitch --db-name flipr_test deploy --untracked
abfa9e7 @theory Use `sqitch status` throughout.
authored
116 Adding metadata tables to flipr_test
54bf216 @theory Better empty tags; show status and log.
authored
117 Deploying HEAD+ to flipr_test
f8c7d5a @theory Always have a HEAD+ tag with --with-untracked.
authored
118 + appuser
e41bc5e @theory Add example of first deployment to tutorial.
authored
119
abfa9e7 @theory Use `sqitch status` throughout.
authored
120 First Sqitch created the metadata tables used to track database changes. The
121 structure and name of the metadata tables varies between databases (PostgreSQL
122 uses a schema to namespace its metadata; MySQL and SQLite use a prefix). Next,
123 Sqitch deploys changes. Here it is deploying something called C<HEAD+>.
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
124 Normally you would see a tag here, but since we've just added the files and
125 they are not yet committed to Git, there is no tag. C<HEAD+> is a special tag
126 reserved for use by Sqitch. It's enabled only by the use of the C<--untracked>
127 option, which allows Sqitch to find an deploy untracked changes.
128
129 With this change deployed, if you connect to the database, you'll be able to
130 see the role:
e41bc5e @theory Add example of first deployment to tutorial.
authored
131
abfa9e7 @theory Use `sqitch status` throughout.
authored
132 > psql -d flipr_test -c '\du flipr'
133 List of roles
134 Role name | Attributes | Member of
135 -----------+------------+-----------
136 flipr | | {}
e41bc5e @theory Add example of first deployment to tutorial.
authored
137
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
138 And we can also see how the deployment was recorded via the C<status> command,
139 which reads the metadata tables from the database:
54bf216 @theory Better empty tags; show status and log.
authored
140
141 > sqitch -d flipr_test status
abfa9e7 @theory Use `sqitch status` throughout.
authored
142 # On database flipr_test
143 # Tag: HEAD+
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
144 # Step: appuser
145 # Date: 2012-04-09 18:43:45
54bf216 @theory Better empty tags; show status and log.
authored
146 #
147 Nothing to deploy (up-to-date)
e41bc5e @theory Add example of first deployment to tutorial.
authored
148
abfa9e7 @theory Use `sqitch status` throughout.
authored
149 Let's make sure that we can revert the change:
e41bc5e @theory Add example of first deployment to tutorial.
authored
150
151 > sqitch --db-name flipr_test revert
54bf216 @theory Better empty tags; show status and log.
authored
152 Reverting all changes from flipr_test
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
153 - appuser
e41bc5e @theory Add example of first deployment to tutorial.
authored
154
155 And now the user should be gone:
156
abfa9e7 @theory Use `sqitch status` throughout.
authored
157 > psql -d flipr_test -c '\du flipr'
21d71c4 @theory Some tutorial tweaks.
authored
158 List of roles
abfa9e7 @theory Use `sqitch status` throughout.
authored
159 Role name | Attributes | Member of
160 -----------+------------+-----------
e41bc5e @theory Add example of first deployment to tutorial.
authored
161
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
162 And the status message should reflect as much:
e41bc5e @theory Add example of first deployment to tutorial.
authored
163
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
164 > sqitch -d flipr_test status --untracked
abfa9e7 @theory Use `sqitch status` throughout.
authored
165 # On database flipr_test
54bf216 @theory Better empty tags; show status and log.
authored
166 # Nothing deployed.
167 #
ad88d13 @theory Add example bundling idempotent changes.
authored
168 # Changes not deployed:
54bf216 @theory Better empty tags; show status and log.
authored
169 # * HEAD+
170 # appuser
171 #
abfa9e7 @theory Use `sqitch status` throughout.
authored
172 Use "sqitch deploy --untracked" to deploy these changes
54bf216 @theory Better empty tags; show status and log.
authored
173
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
174 We've again used the C<--untracked> option, otherwise the C<appuser> step would
9359a6e @theory Pod and spelling errors fixed.
authored
175 not appear in the list of "Changes not yet deployed."
e41bc5e @theory Add example of first deployment to tutorial.
authored
176
07542be @arhuman Fix (very few) typos in documentation
arhuman authored
177 We still have a record that the change happened, visible via the C<log>
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
178 command:
54bf216 @theory Better empty tags; show status and log.
authored
179
180 > sqitch -d flipr_test log
181 step appuser reverted
abfa9e7 @theory Use `sqitch status` throughout.
authored
182 By: david
183 Date: 2012-04-09 18:45:47
184 Tag: HEAD+
54bf216 @theory Better empty tags; show status and log.
authored
185
186 step appuser deployed
abfa9e7 @theory Use `sqitch status` throughout.
authored
187 By: david
188 Date: 2012-04-09 18:43:45
189 Tag: HEAD+
b72f83a @theory Add a second deployment.
authored
190
54bf216 @theory Better empty tags; show status and log.
authored
191 Cool. Now let's commit it.
b72f83a @theory Add a second deployment.
authored
192
193 > git add .
194 > git commit -m 'Add app user.'
195 [master 36acafd] Add app user.
196 2 files changed, 2 insertions(+)
197 create mode 100644 sql/deploy/appuser.sql
198 create mode 100644 sql/revert/appuser.sql
199
200 And then deploy again:
201
202 > sqitch --db-name flipr_test deploy
54bf216 @theory Better empty tags; show status and log.
authored
203 Deploying 36acafd to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
204 + appuser
b72f83a @theory Add a second deployment.
authored
205
abfa9e7 @theory Use `sqitch status` throughout.
authored
206 Notice we no longer need the C<--untracked> option. That's because we've
207 committed the step to Git, so Sqitch can read it from the Git history. And now
208 the user should be back:
b72f83a @theory Add a second deployment.
authored
209
abfa9e7 @theory Use `sqitch status` throughout.
authored
210 > psql -d flipr_test -c '\du flipr'
211 List of roles
212 Role name | Attributes | Member of
213 -----------+------------+-----------
214 flipr | | {}
b72f83a @theory Add a second deployment.
authored
215
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
216 When we look at the status, the deployment is tagged with the SHA1 of the
b72f83a @theory Add a second deployment.
authored
217 commit:
218
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
219 > sqitch -d flipr_test status
abfa9e7 @theory Use `sqitch status` throughout.
authored
220 # On database flipr_test
221 # Tag: 36acafd
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
222 # Step: appuser
223 # Date: 2012-04-09 18:52:42
224 #
225 Nothing to deploy (up-to-date)
b72f83a @theory Add a second deployment.
authored
226
abfa9e7 @theory Use `sqitch status` throughout.
authored
227 =head1 More Configuration
228
229 I'm getting a little tired of always having to type C<--db-name flipr_test>,
230 aren't you? Let's just make that the default, shall we?
231
21d71c4 @theory Some tutorial tweaks.
authored
232 > sqitch config core.pg.db_name flipr_test
abfa9e7 @theory Use `sqitch status` throughout.
authored
233
234 Now we can leave it out, unless we need to deploy to another database. Which
235 we will, eventually, but at least our examples will be simpler from here on
236 in, e.g.:
237
238 > sqitch status
239 # On database flipr_test
240 # Tag: 36acafd
241 # Step: appuser
242 # Date: 2012-04-09 18:52:42
243 #
244 Nothing to deploy (up-to-date)
245
246 Yay, that allows things to be a little more concise. Let's make some more
247 changes!
248
54bf216 @theory Better empty tags; show status and log.
authored
249 =head1 Deploy with Dependency
b72f83a @theory Add a second deployment.
authored
250
251 Let's add another deployment, this time to create a table. Our app will need
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
252 users, of course, so we'll create a table for them. First, add the new step:
b72f83a @theory Add a second deployment.
authored
253
21d71c4 @theory Some tutorial tweaks.
authored
254 > sqitch add-step users --requires appuser
255 Created sql/deploy/users.sql
256 Created sql/revert/users.sql
257 Created sql/test/users.sql
b72f83a @theory Add a second deployment.
authored
258
259 Now edit the scripts. In C<sql/deploy/users.sql>, we put:
260
261 -- requires: appuser
262 BEGIN;
263 SET client_min_messages = 'warning';
264
265 CREATE TABLE users (
266 nickname TEXT PRIMARY KEY,
267 password TEXT NOT NULL,
268 timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
269 );
270
271 GRANT SELECT ON users TO flipr;
272 COMMIT;
273
274 A few things to notice here. On the first line, we've declared a dependency on
abfa9e7 @theory Use `sqitch status` throughout.
authored
275 the C<appuser> step. Although that step has already been added and committed,
54bf216 @theory Better empty tags; show status and log.
authored
276 and therefore should always be applied before the C<users> step, it's a good
abfa9e7 @theory Use `sqitch status` throughout.
authored
277 idea to be explicit about dependencies. This becomes especially important when
278 committing a number of steps at one time, such as when merging a patch or a
279 branch.
b72f83a @theory Add a second deployment.
authored
280
281 The syntax of the dependency declaration is simple: Before any other lines,
282 create an SQL C<--> comment that simply uses the word "requires" followed by a
283 colon and then a comma-delimited list of required steps. The requirements may
284 also be listed on separate lines, providing they all start with
285 C<-- requires:>.
286
287 Notice that all of the SQL code is wrapped in a transaction. This is handy for
288 PostgreSQL deployments, because DDLs are transactional. The upshot is that if
54bf216 @theory Better empty tags; show status and log.
authored
289 any part of the deployment fails, the whole step fails. Such may work
290 less-well for database engines that don't support transactional DDLs.
b72f83a @theory Add a second deployment.
authored
291
54bf216 @theory Better empty tags; show status and log.
authored
292 Just before the commit, the C<GRANT> command grants C<SELECT> access on the
abfa9e7 @theory Use `sqitch status` throughout.
authored
293 table to the C<flipr> user created by the C<appuser> step. This is why we
294 need the dependency.
b72f83a @theory Add a second deployment.
authored
295
296 Now for the revert script. Add this to C<sql/revert/users.sql>:
297
298 DROP TABLE users;
299
300 Couldn't be much simpler, right? Let's deploy this bad boy:
301
abfa9e7 @theory Use `sqitch status` throughout.
authored
302 > sqitch deploy --untracked
54bf216 @theory Better empty tags; show status and log.
authored
303 Deploying HEAD+ to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
304 + users
b72f83a @theory Add a second deployment.
authored
305
306 The C<users> table should have been created:
307
308 > psql -d flipr_test -c '\d users'
309 Table "public.users"
310 Column | Type | Modifiers
311 -----------+--------------------------+------------------------
312 nickname | text | not null
313 password | text | not null
314 timestamp | timestamp with time zone | not null default now()
315 Indexes:
316 "users_pkey" PRIMARY KEY, btree (nickname)
317
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
318 Now have a look at the status:
b72f83a @theory Add a second deployment.
authored
319
21d71c4 @theory Some tutorial tweaks.
authored
320 > sqitch status
abfa9e7 @theory Use `sqitch status` throughout.
authored
321 # On database flipr_test
322 # Tag: HEAD+
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
323 # Step: users
abfa9e7 @theory Use `sqitch status` throughout.
authored
324 # Date: 2012-04-09 20:41:31
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
325 #
326 Nothing to deploy (up-to-date)
b72f83a @theory Add a second deployment.
authored
327
07542be @arhuman Fix (very few) typos in documentation
arhuman authored
328 Success! However, we've once again deployed an untracked change with no tags.
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
329 In general, we want tags, so let's revert the change:
b72f83a @theory Add a second deployment.
authored
330
abfa9e7 @theory Use `sqitch status` throughout.
authored
331 > sqitch revert --to 36acafd
54bf216 @theory Better empty tags; show status and log.
authored
332 Reverting HEAD+ from flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
333 - users
b72f83a @theory Add a second deployment.
authored
334
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
335 Note that we've used the C<--to> option to revert only to the C<36acafd> tag,
abfa9e7 @theory Use `sqitch status` throughout.
authored
336 which represents the deployment of the C<appuser> step. (You can see this tag
337 by running C<sqitch log>, in case you don't have your SHA1 hashes memorized).
338 Now commit and deploy again:
0ef5586 @theory Add more deploys and bundle.
authored
339
b72f83a @theory Add a second deployment.
authored
340 > git add .
341 > git commit -am 'Add users table.'
342 [master fa650af] Add users table.
343 2 files changed, 16 insertions(+)
344 create mode 100644 sql/deploy/users.sql
345 create mode 100644 sql/revert/users.sql
abfa9e7 @theory Use `sqitch status` throughout.
authored
346 > sqitch deploy
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
347 Deploying fa650af to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
348 + users
b72f83a @theory Add a second deployment.
authored
349
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
350 Looks good. Check the status:
351
80764e6 @theory Add options to the `status` command.
authored
352 > sqitch status --show tags
abfa9e7 @theory Use `sqitch status` throughout.
authored
353 # On database flipr_test
354 # Tag: fa650af
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
355 # Step: users
abfa9e7 @theory Use `sqitch status` throughout.
authored
356 # Date: 2012-04-09 20:45:23
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
357 #
80764e6 @theory Add options to the `status` command.
authored
358 # Tags:
359 # fa650af - 2012-04-09 20:45:23 - david
360 # 36acafd - 2012-04-09 19:04:50 - david
361 #
b67fb66 @theory Use `status` and `log` commands instead of table output.
authored
362 Nothing to deploy (up-to-date)
b72f83a @theory Add a second deployment.
authored
363
80764e6 @theory Add options to the `status` command.
authored
364 Note the use of C<--show tags>, which adds the "Tags" section to the output,
9359a6e @theory Pod and spelling errors fixed.
authored
365 so that we can use a list of what tags were deployed, when, and by whom.
54bf216 @theory Better empty tags; show status and log.
authored
366
367 =head1 Add Two at Once
368
abfa9e7 @theory Use `sqitch status` throughout.
authored
369 Let's add a couple more steps to add functions for managing users.
b72f83a @theory Add a second deployment.
authored
370
0ef5586 @theory Add more deploys and bundle.
authored
371 > sqitch add-step insert_user --requires users --requires appuser
372 Adding sql/deploy/insert_user.sql
373 -- requires: users, appuser
374 Adding sql/revert/insert_user.sql
375
376 > sqitch add-step change_pass --requires users --requires appuser
377 Adding sql/deploy/change_pass.sql
378 -- requires: users, appuser
379 Adding sql/revert/change_pass.sql
380
54bf216 @theory Better empty tags; show status and log.
authored
381 Here we've taken advantage of the C<--requires> option to have Sqitch write
abfa9e7 @theory Use `sqitch status` throughout.
authored
382 the stubbed C<deploy> file with the C<-- requires:> line already filled in.
383 Have a look:
0ef5586 @theory Add more deploys and bundle.
authored
384
385 > cat sql/deploy/insert_user.sql
386 -- requires: users, appuser
387
388
abfa9e7 @theory Use `sqitch status` throughout.
authored
389 Nice, huh? Yeah, okay, so it's a little thing. Little things matter, am I
390 right? Let's write the code. Here's what C<sql/deploy/insert_user.sql> should
391 look like:
0ef5586 @theory Add more deploys and bundle.
authored
392
393 -- requires: users, appuser
394
54bf216 @theory Better empty tags; show status and log.
authored
395 BEGIN;
396
0ef5586 @theory Add more deploys and bundle.
authored
397 CREATE OR REPLACE FUNCTION insert_user(
398 nickname TEXT,
399 password TEXT
54bf216 @theory Better empty tags; show status and log.
authored
400 ) RETURNS VOID LANGUAGE SQL SECURITY DEFINER AS $$
21d71c4 @theory Some tutorial tweaks.
authored
401 INSERT INTO users VALUES($1, md5($2));
0ef5586 @theory Add more deploys and bundle.
authored
402 $$;
403
21d71c4 @theory Some tutorial tweaks.
authored
404 GRANT EXECUTE ON FUNCTION insert_user(TEXT, TEXT) TO flipr;
405
54bf216 @theory Better empty tags; show status and log.
authored
406 COMMIT;
407
0ef5586 @theory Add more deploys and bundle.
authored
408 And C<sql/revert/insert_user.sql> should look something like this:
409
410 DROP FUNCTION insert_user(TEXT, TEXT);
411
412 Now for C<change_pass>; C<sql/deploy/change_pass.sql> might look like this:
413
414 -- requires: users, appuser
415
54bf216 @theory Better empty tags; show status and log.
authored
416 BEGIN;
417
0ef5586 @theory Add more deploys and bundle.
authored
418 CREATE OR REPLACE FUNCTION change_pass(
419 nick TEXT,
420 oldpass TEXT,
421 newpass TEXT
abfa9e7 @theory Use `sqitch status` throughout.
authored
422 ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
0ef5586 @theory Add more deploys and bundle.
authored
423 BEGIN
424 UPDATE users
425 SET password = md5($3)
426 WHERE nickname = $1
427 AND password = md5($2);
428 RETURN FOUND;
429 END;
430 $$;
431
21d71c4 @theory Some tutorial tweaks.
authored
432 GRANT EXECUTE ON FUNCTION change_pass(TEXT, TEXT, TEXT) TO flipr;
54bf216 @theory Better empty tags; show status and log.
authored
433 COMMIT;
434
abfa9e7 @theory Use `sqitch status` throughout.
authored
435 And of course, its C<revert> script, C<sql/revert/change_pass.sql>, should look
0ef5586 @theory Add more deploys and bundle.
authored
436 something like:
437
438 DROP FUNCTION change_pass(TEXT, TEXT, TEXT);
439
440 Test em out!
441
abfa9e7 @theory Use `sqitch status` throughout.
authored
442 > sqitch deploy --untracked
54bf216 @theory Better empty tags; show status and log.
authored
443 Deploying HEAD+ to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
444 + change_pass
445 + insert_user
0ef5586 @theory Add more deploys and bundle.
authored
446
447 Do we have the functions?
448
449 > psql -d flipr_test -c '\df'
450 List of functions
451 Schema | Name | Result data type | Argument data types | Type
452 --------+-------------+------------------+---------------------------------------+--------
453 public | change_pass | boolean | nick text, oldpass text, newpass text | normal
454 public | insert_user | void | nickname text, password text | normal
455
abfa9e7 @theory Use `sqitch status` throughout.
authored
456 And what's the status?
0ef5586 @theory Add more deploys and bundle.
authored
457
abfa9e7 @theory Use `sqitch status` throughout.
authored
458 > sqitch status --show tags
459 # On database flipr_test
460 # Tag: HEAD+
461 # Step: insert_user
462 # Date: 2012-04-09 20:45:23
463 #
464 # Tags:
465 # HEAD+ - 2012-04-09 20:45:23 - david
466 # fa650af - 2012-04-09 20:45:23 - david
467 # 36acafd - 2012-04-09 19:04:50 - david
468 #
469 Nothing to deploy (up-to-date)
0ef5586 @theory Add more deploys and bundle.
authored
470
471 Looks good. Let's revert, commit, and re-deploy, as usual.
472
abfa9e7 @theory Use `sqitch status` throughout.
authored
473 > sqitch revert --to HEAD
54bf216 @theory Better empty tags; show status and log.
authored
474 Reverting HEAD+ from flipr_test
0ef5586 @theory Add more deploys and bundle.
authored
475 - insert_user
476 - change_pass
477
abfa9e7 @theory Use `sqitch status` throughout.
authored
478 Note the use of C<--to HEAD> to revert untracked changes. We also could have
479 explicitly used C<--to fa650af> to revert to that tag, but C<HEAD> is a nice
480 shortcut. Let's do the commit and re-deploy dance:
0ef5586 @theory Add more deploys and bundle.
authored
481
482 > git add .
21d71c4 @theory Some tutorial tweaks.
authored
483 > git commit -m 'Add `insert_user()` and `change_pass()`.'
0ef5586 @theory Add more deploys and bundle.
authored
484 [master 803e6b8] Add `insert_user()` and `change_pass()`.
485 4 files changed, 25 insertions(+)
486 create mode 100644 sql/deploy/change_pass.sql
487 create mode 100644 sql/deploy/insert_user.sql
488 create mode 100644 sql/revert/change_pass.sql
489 create mode 100644 sql/revert/insert_user.sql
490
abfa9e7 @theory Use `sqitch status` throughout.
authored
491 > sqitch deploy
54bf216 @theory Better empty tags; show status and log.
authored
492 Deploying 803e6b8 to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
493 + change_pass
494 + insert_user
0ef5586 @theory Add more deploys and bundle.
authored
495
abfa9e7 @theory Use `sqitch status` throughout.
authored
496 > sqitch status --show tags
497 # On database flipr_test
498 # Tag: HEAD+
499 # Step: insert_user
500 # Date: 2012-04-09 20:45:23
501 #
502 # Tags:
503 # 803e6b8 - 2012-04-09 21:33:32 - david
504 # fa650af - 2012-04-09 20:45:23 - david
505 # 36acafd - 2012-04-09 19:04:50 - david
506 #
507 Nothing to deploy (up-to-date)
508
509 Great, we're fully up-to-date!
0ef5586 @theory Add more deploys and bundle.
authored
510
511 =head1 Ship It!
512
513 Let's do a first release of our app. Let's call it C<1.0.0-dev1> Since we want
514 to have it go out with deployments tied to the release, let's tag it:
515
516 > git tag v1.0.0-dev1 -am 'Tag v1.0.0-dev1'
517
518 We can test deployment to make sure the tag gets picked up like so:
519
520 > createdb flipr_dev
521 > sqitch --db-name flipr_dev deploy
abfa9e7 @theory Use `sqitch status` throughout.
authored
522 Deploying 36acafd to flipr_dev
21d71c4 @theory Some tutorial tweaks.
authored
523 + appuser
abfa9e7 @theory Use `sqitch status` throughout.
authored
524 Deploying fa650af to flipr_dev
21d71c4 @theory Some tutorial tweaks.
authored
525 + users
526 Deploying 803e6b8/v1.0.0+dev1 to flipr_dev
527 + insert_user
528 + change_pass
0ef5586 @theory Add more deploys and bundle.
authored
529
530 All four steps were deployed, great! Let's have a look at the state:
531
abfa9e7 @theory Use `sqitch status` throughout.
authored
532 > sqitch --db-name flipr_dev status --show tags
533 # On database flipr_dev
534 # Tags: 803e6b8, v1.0.0-dev1
535 # Step: change_pass
536 # Date: 2012-04-09 22:14:00
537 #
538 # Tags:
539 # 803e6b8, v1.0.0-dev1 - 2012-04-09 22:14:00 - david
540 # fa650af - 2012-04-09 22:13:55 - david
541 # 36acafd - 2012-04-09 22:13:53 - david
542 #
543 Nothing to deploy (up-to-date)
0ef5586 @theory Add more deploys and bundle.
authored
544
abfa9e7 @theory Use `sqitch status` throughout.
authored
545 Note that all the tags are included, and that the two tags C<803e6b8> and
546 C<v1.0.0-dev1> actually represent the same point in time. This is handy for
547 development, but is a bit over-the-top for production deployments. We can get
548 around this by bundling up all the steps with the explicit tag, leaving out
549 the individual commit SHA1s:
0ef5586 @theory Add more deploys and bundle.
authored
550
551 > sqitch bundle --tags-only
abfa9e7 @theory Use `sqitch status` throughout.
authored
552 Bundling in bundle/
21d71c4 @theory Some tutorial tweaks.
authored
553 Config written to bundle/sqitch.conf
54bf216 @theory Better empty tags; show status and log.
authored
554 Bundling v1.0.0-dev1
555 - appuser
556 - users
557 - change_pass
558 - insert_user
0ef5586 @theory Add more deploys and bundle.
authored
559 Plan written to bundle/sqitch.plan
560
abfa9e7 @theory Use `sqitch status` throughout.
authored
561 Now we can package up the C<bundle> directory and distribute it. When it gets
562 installed somewhere, users can use Sqitch to deploy to the database. Of
563 course, the Git history is no longer available, so Sqitch will use the
564 C<sqitch.plan> file to figure things out. Let's have a look at it:
0ef5586 @theory Add more deploys and bundle.
authored
565
566 cat bundle/sqitch.plan
567
568 [v1.0.0-dev1]
569 appuser
570 users
571 change_pass
572 insert_user
573
574 Pretty simple. It shows the tag being deployed and the steps that constitute
54bf216 @theory Better empty tags; show status and log.
authored
575 it as a simple list. Notice that the SHA1s for each step are not present; the
0ef5586 @theory Add more deploys and bundle.
authored
576 C<--tags-only> option to the C<bundle> command limits the plan only to tags.
577
578 Let's test deploying it:
579
580 > cd bundle
581 > createdb flipr_prod
582 > sqitch --db-name flipr_prod deploy
54bf216 @theory Better empty tags; show status and log.
authored
583 Deploying v1.0.0-dev1 to flipr_prod
21d71c4 @theory Some tutorial tweaks.
authored
584 + appuser
585 + users
586 + insert_user
587 + change_pass
0ef5586 @theory Add more deploys and bundle.
authored
588
abfa9e7 @theory Use `sqitch status` throughout.
authored
589 Looks much the same as before, eh? But have a look at the status:
0ef5586 @theory Add more deploys and bundle.
authored
590
abfa9e7 @theory Use `sqitch status` throughout.
authored
591 > sqitch --db-name flipr_prod status --show tags
592 # On database flipr_prod
593 # Tag: v1.0.0-dev1
594 # Step: change_pass
595 # Date: 2012-04-09 22:17:38
596 #
597 # Tags:
598 # v1.0.0-dev1 - 2012-04-09 22:17:38 - david
599 #
600 Nothing to deploy (up-to-date)
0ef5586 @theory Add more deploys and bundle.
authored
601
abfa9e7 @theory Use `sqitch status` throughout.
authored
602 No commit SHA1s, just C<v1.0.0-dev1>.
d987aa2 @theory Add a branch, some more changes.
authored
603
604 =head1 Flip Out
605
606 Now that we've got the basics of user management done, let's get to work on
607 the core of our product, the "flip." Since other folks are working on other
608 tasks in the repository, we'll work on a branch, so we can all stay out of
609 each other's way. So let's branch:
610
611 > git checkout -b flips
612 Switched to a new branch 'flips'
613
614 Now we can add a new step to create a table for our flips.
615
616 > sqitch add-step flips --requires users
617 Adding sql/deploy/flips.sql
618 -- requires: users
619 Adding sql/revert/flips.sql
620
621 You know the drill by now. Edit F<sql/deploy/flips.sql>:
622
623 -- requires: users
624 BEGIN;
625 SET client_min_messages = 'warning';
626
627 CREATE TABLE flips (
628 id BIGSERIAL PRIMARY KEY,
629 nickname TEXT NOT NULL REFERENCES users(nickname),
630 body TEXT NOT NULL DEFAULT '' CHECK ( length(body) <= 180 ),
631 timestamp TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
632 );
633
634 GRANT SELECT ON flips TO flipr;
635 COMMIT;
636
637 And edit F<sql/revert/flips.sql>:
638
639 DROP TABLE flips;
640
641 And give it a whirl:
642
643 > sqitch deploy --untracked
644 Deploying HEAD+ to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
645 + flips
d987aa2 @theory Add a branch, some more changes.
authored
646
647 Look good?
648
649 > sqitch status --show tags
650 # On database flipr_test
651 # Tag: HEAD+
652 # Step: flips
653 # Date: 2012-04-10 22:30:02
654 #
655 # Tags:
656 # HEAD+ - 2012-04-10 22:30:02 - david
657 # 803e6b8 - 2012-04-09 21:33:32 - david
658 # fa650af - 2012-04-09 20:45:23 - david
659 # 36acafd - 2012-04-09 19:04:50 - david
660 #
661 Nothing to deploy (up-to-date)
662
663 Yep. Make it so!
664
665 > git add .
666 > git commit -am 'Add flipr table.'
667 [flips 4aa888e] Add flipr table.
668 2 files changed, 18 insertions(+)
669 create mode 100644 sql/deploy/flips.sql
670 create mode 100644 sql/revert/flips.sql
671
672 Ooh, but wait, we forgot to revert the change before committing! Well,
673 no worries, we can update all the tags.
674
675 > sqitch retag
676 Retagging flipr_test
677 . 36acafd
678 . fa650af
679 . 803e6b8
680 + v1.0.0-dev1
681 - HEAD+
682 + 4aa888e
683 Tags: 4 (1 removed, 1 added).
684
685 In the list of tags, C<.> means no change, C<+> means added, and C<-> means
686 removed.
687
688 Oh, it added the C<v1.0.0-dev1> tag. Recall that we never deployed it to the
689 test database before. That's cool, forgot about that. Then it removed the
690 C<HEAD+> tag and added C<4aa888e>. Let's examine the status:
691
692 > sqitch status --show tags
693 # On database flipr_test
694 # Tag: 4aa888e
695 # Step: flips
696 # Date: 2012-04-10 22:30:02
697 #
698 # Tags:
699 # 4aa888e - 2012-04-10 22:30:02 - david
700 # 803e6b8, v1.0.0-dev1 - 2012-04-09 21:33:32 - david
701 # fa650af - 2012-04-09 20:45:23 - david
702 # 36acafd - 2012-04-09 19:04:50 - david
703 #
704 Nothing to deploy (up-to-date)
705
706 Great, looks like the tags are all in sync. That's handy. Of course, it's
707 still a good idea to test C<sqitch revert> and C<sqitch deploy> a few times,
708 just to make sure the changes are always carried out properly, but it's nice
709 to know we don't have to take extra steps just to commit untracked changes.
710
711 =head1 Wash, Rinse, Repeat
712
713 Now comes the time to add functions to manage flips. I'm sure you have things
714 nailed down now. Go ahead and add C<insert_flip> and C<delete_flip> steps
715 and commit them. The C<insert_flip> deploy script might look something like:
716
717 -- requires: flips, appuser
718 BEGIN;
719 CREATE OR REPLACE FUNCTION insert_flip(
720 nickname TEXT,
721 body TEXT
722 ) RETURNS BIGINT LANGUAGE sql SECURITY DEFINER AS $$
723 INSERT INTO flips (body, nickname)
724 VALUES ($1, $2)
725 RETURNING id;
726 $$;
727 GRANT EXECUTE ON FUNCTION insert_flip(TEXT, TEXT) to flipr;
728 COMMIT;
729
730 And the C<delete_flip> deploy script might look something like:
731
732 -- requires: flips, appuser
733 BEGIN;
734 CREATE OR REPLACE FUNCTION delete_flip(
735 flip_id BIGINT
736 ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
737 BEGIN
738 DELETE FROM flips WHERE id = flip_id;
739 RETURN FOUND;
740 END;
741 $$;
742 GRANT EXECUTE ON FUNCTION delete_flip(BIGINT) to flipr;
743 COMMIT;
744
745 The C<revert> scripts are:
746
747 DROP FUNCTION insert_flip(TEXT, TEXT);
748
749 And:
750
751 DROP FUNCTION delete_flip(BIGINT);
752
753 Check the L<example git repository|https://github.com/theory/sqitch-intro> for
f195721 @theory Fix spelling errors.
authored
754 the complete details. Test C<deploy> and C<revert>, commit, and C<retag>. The
755 status should end up looking something like this:
d987aa2 @theory Add a branch, some more changes.
authored
756
757 > sqitch status --show tags
758 # On database flipr_test
759 # Tag: 74c49ef
760 # Step: insert_flip
761 # Date: 2012-04-10 23:00:29
762 #
763 # Tags:
764 # 74c49ef - 2012-04-10 23:00:29 - david
765 # 4aa888e - 2012-04-10 22:30:02 - david
766 # 803e6b8, v1.0.0-dev1 - 2012-04-09 21:33:32 - david
767 # fa650af - 2012-04-09 20:45:23 - david
768 # 36acafd - 2012-04-09 19:04:50 - david
769 #
770 Nothing to deploy (up-to-date)
771
772 Good, we've finished this feature. Time to merge back into C<master>.
773
3f66ce0 @theory Handle merge ordering issue.
authored
774 =head2 Emergency
d987aa2 @theory Add a branch, some more changes.
authored
775
4d27feb @theory Add merge conflict example.
authored
776 Let's do it:
777
778 > git checkout master
779 Switched to branch 'master'
780 > git pull
781 Updating 803e6b8..2fae0b3
782 Fast-forward
783 sql/deploy/delete_list.sql | 15 +++++++++++++++
784 sql/deploy/insert_list.sql | 12 ++++++++++++
785 sql/deploy/lists.sql | 16 ++++++++++++++++
786 sql/revert/delete_list.sql | 1 +
787 sql/revert/insert_list.sql | 1 +
788 sql/revert/lists.sql | 2 ++
789 12 files changed, 91 insertions(+)
790 create mode 100644 sql/deploy/delete_list.sql
791 create mode 100644 sql/deploy/insert_list.sql
792 create mode 100644 sql/deploy/lists.sql
793 create mode 100644 sql/revert/delete_list.sql
794 create mode 100644 sql/revert/insert_list.sql
795 create mode 100644 sql/revert/lists.sql
796
797 Hrm, that's interesting. Looks like someone made some changes to C<master>.
798 Looks like some list support was added. Well, let's see what happens when we
799 merge our changes.
800
801 > git merge --no-ff flips
802 Merge made by the 'recursive' strategy.
803 sql/deploy/delete_flip.sql | 12 ++++++++++++
804 sql/deploy/flips.sql | 16 ++++++++++++++++
805 sql/deploy/insert_flip.sql | 12 ++++++++++++
806 sql/revert/delete_flip.sql | 1 +
807 sql/revert/flips.sql | 2 ++
808 sql/revert/insert_flip.sql | 1 +
809 6 files changed, 44 insertions(+)
810 create mode 100644 sql/deploy/delete_flip.sql
811 create mode 100644 sql/deploy/flips.sql
812 create mode 100644 sql/deploy/insert_flip.sql
813 create mode 100644 sql/revert/delete_flip.sql
814 create mode 100644 sql/revert/flips.sql
815 create mode 100644 sql/revert/insert_flip.sql
816
817 Hey, looks good, everything merged cleanly. Let's have a look at our Git history:
818
819 > git log --oneline
820 5e30f38 Merge branch 'flips'
821 2fae0b3 Merge tag 'lists'
822 2a84155 Merge branch 'lists'
823 9703969 Add `insert_flip()` and `delete_flip()`.
824 675f78d Add `insert_list()` and `delete_list()`.
825 faaa4d0 Add flipr table.
826 591e388 Add `lists` table.
827 803e6b8 Add `insert_user()` and `change_pass()`.
828 fa650af Add users table.
829 36acafd Add app user.
830 e076e84 Initialize Sqitch configuration.
831 9ccc485 Add README text.
832 7b8dd19 First post!
833
8a9890e @theory I kant spel.
authored
834 Whoa, wait, what? Recall that we left off at commit C<803e6b8>. After that we
835 should have our two commits to the "flips" branch and end with a merge commit.
4d27feb @theory Add merge conflict example.
authored
836 But notice here we have some other stuff interleaved. Commit C<591e388> adds a
837 "lists" table before our C<faaa4d0> commit adds the "flips" table. Then there
838 is another interloper commit, C<675f78d>, which adds some functions to insert
839 and delete lists, before our C<9703969> commit adds functions to insert and
840 delete flips. And there, second from the top, is the explanation,
841 C<2fae0b3 Merge branch 'lists'>. Why, someone else was working on another
842 branch and merged stuff into master before we did! Now things are all mixed
843 up.
844
845 Well, the truth is, we got lazy. Those changes when we pulled master from the
846 origin should have raised a red flag. In truth, it's considered a bad practice
847 not to look at what's changed in C<master> before merging in a branch. What one
848 I<should> do is either:
849
850 =over
851
852 =item *
853
854 Rebase the branch from on master before merging. This "rewinds" the branch
855 changes, pulls from C<master>, and then replays the changes back on top of
856 C<master>.
857
858 =item *
859
860 Create a patch and apply I<that> to master. This is the sort of thing you
861 might have to do if you're sending changes to another user, especially if the
862 VCS is not Git.
863
864 =back
865
866 So let's restore things to how they were at master:
867
868 > git reset --hard origin/master
869 HEAD is now at 2fae0b3 Merge tag 'lists'
870
871 That throws out our merge, which thankfully we have not yet pushed. Now let's
872 go back to our branch and rebase it on C<master>:
873
874 > git checkout flips
875 Switched to branch 'flips'
876 > git rebase master
877 First, rewinding head to replay your work on top of it...
878 Applying: Add flipr table.
879 Applying: Add `insert_flip()` and `delete_flip()`.
880
881 Notice that it rewinds the changes since we branched from C<master>, pulls
882 from C<master>, and then re-applies our commits. Look at the Git log to make
883 sure things were applied properly:
884
885 > git log --oneline
886 54d680f Add `insert_flip()` and `delete_flip()`.
887 d07f3dc Add flipr table.
888 2fae0b3 Merge tag 'lists'
889 2a84155 Merge branch 'lists'
890 675f78d Add `insert_list()` and `delete_list()`.
891 591e388 Add `lists` table.
892 803e6b8 Add `insert_user()` and `change_pass()`.
893 fa650af Add users table.
894 36acafd Add app user.
895 e076e84 Initialize Sqitch configuration.
896 9ccc485 Add README text.
897 7b8dd19 Fist post!
898
899 Much better. Now all of our "flips" changes come after the "lists" changes.
900 But wait, we can't merge into C<master> quite yet. Or, at least, it's not a
901 good idea. We need to first make sure that Sqitch deployments still work
3f66ce0 @theory Handle merge ordering issue.
authored
902 properly. Why? Have a look at this:
4d27feb @theory Add merge conflict example.
authored
903
3f66ce0 @theory Handle merge ordering issue.
authored
904 > sqitch status
905 # On database flipr_test
906 # Tag: 74c49ef (unknown)
907 # Step: insert_flip
908 # Date: 2012-04-10 23:00:29
909 #
910 # Unknown tags:
911 # (use "sqitch retag" to fix)
912 # 74c49ef
913 # 28df2ve
914 #
915 # Undeployed changes:
916 # (no conflicts detected; use "sqitch deploy" to deploy the changes)
917 #
918 # 591e388
919 # - lists
920 # 675f78d
921 # - insert_list
922 # - delete_list
923
924 Uh-oh. First of all, the second line tells us that the current tag, C<74c49ef>,
925 is unknown. That's because we rebased from master, so our local commits got
926 new SHA1s. Fortunately, we can fix that problem by re-tagging:
927
928 > sqitch retag
929 Retagging flipr_test
930 . 36acafd
931 . fa650af
932 . 803e6b8
933 . v1.0.0-dev1
934 . 4aa888e
935 - 28df2ve
936 + d07f3dc
937 - 74c49ef
938 + 54d680f
939
940 Tags: 7 (2 removed, 2 added).
4d27feb @theory Add merge conflict example.
authored
941
3f66ce0 @theory Handle merge ordering issue.
authored
942 Looks like it made some important updates. Let's see:
943
944 > sqitch status
945 # On database flipr_test
946 # Tag: 54d680f
947 # Step: insert_flip
948 # Date: 2012-04-10 23:00:29
949 #
950 # Undeployed changes:
951 # (no conflicts detected; use "sqitch deploy" to deploy the changes)
952 #
953 # 591e388
954 # - lists
955 # 675f78d
956 # - insert_list
957 # - delete_list
958
959 Great, now the tag looks right. But we still have undeployed changes. This is
960 because the rebase from C<master> added the "lists" branch changes. They come
c897855 @theory I kant spel.
authored
961 I<before> our "flips" changes in the repository history, which means that,
3f66ce0 @theory Handle merge ordering issue.
authored
962 technically, the the "flips" changes have been applied out-of-order.
963 Fortunately for us, however, there appear to be no conflicts (no mutual
964 dependencies between the "flips" and "lists" changes to muck things up). So we
965 can just take its advice and run C<sqitch deploy>:
966
967 > sqitch deploy --untracked
968 Deploying 591e388 to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
969 + lists
3f66ce0 @theory Handle merge ordering issue.
authored
970 Deploying 675f78d to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
971 + delete_list
972 + insert_list
3f66ce0 @theory Handle merge ordering issue.
authored
973
974 So where does that leave us?
975
976 > sqitch status
977 # On database flipr_test
978 # Tag: 54d680f
979 # Step: insert_flip
980 # Date: 2012-04-10 23:32:43
981 #
982
983 Note that we are still on the C<54d680f> tag but the date has been updated.
984 Sqitch has successfully merged the "lists" changes into the database.
985
c897855 @theory I kant spel.
authored
986 What would have happened if there had been a conflict? In that case, we would
3f66ce0 @theory Handle merge ordering issue.
authored
987 have had to revert the "flips" changes and deployed again, so that the changes
c897855 @theory I kant spel.
authored
988 could be re-applied in the proper order. The commands to do that would have
989 been:
3f66ce0 @theory Handle merge ordering issue.
authored
990
991 > sqitch revert 54d680f^
992 > sqitch deploy
993
994 Note the use of C<^> at the end of C<54d680f^>. That means, "the tag just
995 before C<54d680f>", and is a nice shortcut so that we don't have to troll the
996 history to find that tag. So it would revert to that tag, and them the call to
997 C<sqitch deploy> would have deployed the "lists" changes, followed by the
998 "flips" changes, so that everything would be in the proper order
999
1000 Of course, this is the sort of thing you want to avoid in a production
1001 context. When doing development, you can muck with your change history in any
1002 number of ways. But you really want to get things nailed down before you do a
1003 release, which should have a set plan that does not change -- until the next
1004 release adds new steps -- but never mixes things in.
1005
1006 =head2 Merges Mastered
1007
1008 And now, finally, we can merge into C<master>:
4d27feb @theory Add merge conflict example.
authored
1009
1010 > git checkout master
1011 Switched to branch 'master'
1012 > git merge --no-ff flips
1013 Merge made by the 'recursive' strategy.
1014 sql/deploy/delete_flip.sql | 12 ++++++++++++
1015 sql/deploy/flips.sql | 16 ++++++++++++++++
1016 sql/deploy/insert_flip.sql | 12 ++++++++++++
1017 sql/revert/delete_flip.sql | 1 +
1018 sql/revert/flips.sql | 2 ++
1019 sql/revert/insert_flip.sql | 1 +
1020 6 files changed, 44 insertions(+)
1021 create mode 100644 sql/deploy/delete_flip.sql
1022 create mode 100644 sql/deploy/flips.sql
1023 create mode 100644 sql/deploy/insert_flip.sql
1024 create mode 100644 sql/revert/delete_flip.sql
1025 create mode 100644 sql/revert/flips.sql
1026 create mode 100644 sql/revert/insert_flip.sql
1027
1028 And double-check our work:
1029
1030 > git log --oneline
1031 e161a90 Merge branch 'flips'
1032 54d680f Add `insert_flip()` and `delete_flip()`.
1033 d07f3dc Add flipr table.
1034 2fae0b3 Merge tag 'lists'
1035 2a84155 Merge branch 'lists'
1036 675f78d Add `insert_list()` and `delete_list()`.
1037 591e388 Add `lists` table.
1038 803e6b8 Add `insert_user()` and `change_pass()`.
1039 fa650af Add users table.
1040 36acafd Add app user.
1041 e076e84 Initialize Sqitch configuration.
1042 9ccc485 Add README text.
1043 7b8dd19 Fist post!
1044
3f66ce0 @theory Handle merge ordering issue.
authored
1045 Much much better, a nice clean master now. And because it is now identical to
1046 the "flips" branch, we can just carry on. Tag it so that we can use real tags
1047 for bundling a release later:
1048
1049 > git tag flips -am 'Tag flips merge.'
1050
39dfbf2 @theory Add discussion of revising a function.
authored
1051 =head1 In Place Changes
1052
1053 Uh-oh, someone just noticed that MD5 hashing is not particularly secure. Why?
1054 Have a look at this:
1055
1056 > psql -d flipr_test -c "
1057 SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
1058 SELECT * FROM users;
1059 "
1060 nickname | password | timestamp
1061 ----------+----------------------------------+-------------------------------
1062 foo | 9695da4dd567a19f9b92065f240c6725 | 2012-04-11 23:00:02.135612+00
1063 bar | 9695da4dd567a19f9b92065f240c6725 | 2012-04-11 23:00:02.135612+00
1064
ad88d13 @theory Add example bundling idempotent changes.
authored
1065 If user "foo" ever got access to the database, she could quickly discover that
1066 user "bar" has the same password and thus be able to exploit the account. Not
1067 a great idea. So we need to modify the C<insert_user()> and C<change_pass()>
1068 functions to fix that. How? we'll use
39dfbf2 @theory Add discussion of revising a function.
authored
1069 L<C<pgcrypto>|http://www.postgresql.org/docs/current/static/pgcrypto.html>'s
1070 C<crypt()> function to encrypt passwords with a salt, so that they're all
ad88d13 @theory Add example bundling idempotent changes.
authored
1071 unique. We just add a step to add C<pgcrypto> to the database, and then we can
1072 use it. The deploy script should be:
39dfbf2 @theory Add discussion of revising a function.
authored
1073
ad88d13 @theory Add example bundling idempotent changes.
authored
1074 CREATE EXTENSION pgcrypto;
1075
1076 And the revert script should just C<DROP EXTENSION pgcrypto>. Then we can use
1077 it. But how to deploy the changes to C<insert_user()> and C<change_pass()>?
1078
1079 Normally, modifying functions in database changes is a PITA. You have to
1080 take steps like these:
39dfbf2 @theory Add discussion of revising a function.
authored
1081
1082 =over
1083
1084 =item 1.
1085
ad88d13 @theory Add example bundling idempotent changes.
authored
1086 Copy F<sql/deploy/insert_user.sql> to F<sql/deploy/insert_user_crypt.sql>.
39dfbf2 @theory Add discussion of revising a function.
authored
1087
1088 =item 2.
1089
ad88d13 @theory Add example bundling idempotent changes.
authored
1090 Edit F<sql/deploy/insert_user_crypt.sql> to switch from C<MD5()> to C<crypt()>
1091 and to add a dependency on the C<pgcrypto> step.
39dfbf2 @theory Add discussion of revising a function.
authored
1092
1093 =item 3.
1094
1095 Copy F<sql/deploy/insert_user.sql> to F<sql/revert/insert_user_crypt.sql>.
1096 Yes, copy the original change script to the new revert step.
1097
ad88d13 @theory Add example bundling idempotent changes.
authored
1098 =item 4.
39dfbf2 @theory Add discussion of revising a function.
authored
1099
ad88d13 @theory Add example bundling idempotent changes.
authored
1100 Test the changes to make sure you can deploy and revert the
1101 C<insert_user_crypt> step.
39dfbf2 @theory Add discussion of revising a function.
authored
1102
ad88d13 @theory Add example bundling idempotent changes.
authored
1103 =item 5.
39dfbf2 @theory Add discussion of revising a function.
authored
1104
1105 Now do the same for the C<change_pass> scripts.
1106
1107 =back
1108
ad88d13 @theory Add example bundling idempotent changes.
authored
1109 That doesn't seem I<too> bad. And you can do that with Sqitch, if you want.
1110 But it makes for rather bad VCS management. This is effectively a one-line
1111 change to each function, but we're copying their entire contents to new files.
1112 Hell, the reversion script is I<identical> to the original deploy script.
39dfbf2 @theory Add discussion of revising a function.
authored
1113
1114 Perhaps worse, though, is that it's much more difficult to look at changes in
1115 the VCS. After committing the C<insert_user_crypt> step, what does the diff
1116 look like?
1117
1118 > git diff HEAD^
1119 diff --git a/sql/deploy/insert_user_crypt.sql b/sql/deploy/insert_user_crypt.sql
1120 new file mode 100644
1121 index 0000000..fa8d0c6
1122 --- /dev/null
1123 +++ b/sql/deploy/insert_user_crypt.sql
1124 @@ -0,0 +1,8 @@
ad88d13 @theory Add example bundling idempotent changes.
authored
1125 +-- requires: users, appuser, pgcrypto
39dfbf2 @theory Add discussion of revising a function.
authored
1126 +
1127 +CREATE OR REPLACE FUNCTION insert_user(
1128 + nickname TEXT,
1129 + password TEXT
1130 +) RETURNS VOID LANGUAGE SQL AS $$
1131 + INSERT INTO users values($1, crypt($2, gen_salt('md5')));
1132 +$$;
1133 diff --git a/sql/revert/insert_user_crypt.sql b/sql/revert/insert_user_crypt.sql
1134 new file mode 100644
1135 index 0000000..a7f4e31
1136 --- /dev/null
1137 +++ b/sql/revert/insert_user_crypt.sql
1138 @@ -0,0 +1,8 @@
1139 +-- requires: users, appuser
1140 +
1141 +CREATE OR REPLACE FUNCTION insert_user(
1142 + nickname TEXT,
1143 + password TEXT
1144 +) RETURNS VOID LANGUAGE SQL AS $$
1145 + INSERT INTO users values($1, md5($2));
1146 +$$;
1147
1148 Oy. It looks like two new functions were added, not that we just changed one.
1149 How annoying is that? Of course, if we know the history, we can diff things
1150 without using the Git history, simply by comparing F<sql/deploy/insert_user.sql>
ad88d13 @theory Add example bundling idempotent changes.
authored
1151 to F<sql/deploy/insert_user_crypt.sql>:
39dfbf2 @theory Add discussion of revising a function.
authored
1152
1153 > diff -u sql/deploy/insert_user.sql sql/deploy/insert_user_crypt.sql
1154 --- sql/deploy/insert_user.sql 2012-04-09 14:15:55.000000000 -0700
1155 +++ sql/deploy/insert_user_crypt.sql 2012-04-11 16:27:18.000000000 -0700
1156 @@ -4,5 +4,5 @@
1157 nickname TEXT,
1158 password TEXT
1159 ) RETURNS VOID LANGUAGE SQL AS $$
1160 - INSERT INTO users values($1, md5($2));
1161 + INSERT INTO users values($1, crypt($2, gen_salt('md5')));
1162 $$;
1163
ad88d13 @theory Add example bundling idempotent changes.
authored
1164 That makes much more sense, but requires special knowledge to realize you
1165 can't just C<git diff> to see what changed. Wouldn't it be great if we could
1166 just make the change right in the file, commit it to the VCS, and have the
1167 deployment and reversion "just work"?
1168
1169 =head2 It's a VCS
1170
1171 Sqitch can. Rather than take all the above steps, you can just modify
1172 F<sql/deploy/insert_user.sql> and F<sql/deploy/change_padd.sql> I<in place>.
1173 There is only one requirement for this: All changes to the deployment script,
1174 including the original version, B<must be
1175 L<idempotent|http://en.wikipedia.org/wiki/Idempotence>>. That is, the deploy
1176 script can applied multiple times without changing the result beyond the
1177 initial application. That's it, the only requirement.
1178
1179 Fortunately, our function deployment scripts are already idempotent, thanks to
1180 the use of the C<OR REPLACE> expression. No matter how many times a deployment
1181 script is run, he end result will be the same instance of the function, with
1182 no duplicates or errors.
1183
1184 As a result, there is no need to explicitly add steps. So go ahead. Modify the
1185 script to switch to C<crypt()>. Make this change to
1186 F<sql/deploy/insert_user.sql>:
1187
1188 @@ -1,8 +1,8 @@
1189 --- requires: users, appuser
1190 +-- requires: users, appuser, pgcrypto
1191
1192 CREATE OR REPLACE FUNCTION insert_user(
1193 nickname TEXT,
1194 password TEXT
1195 ) RETURNS VOID LANGUAGE SQL AS $$
1196 - INSERT INTO users values($1, md5($2));
1197 + INSERT INTO users values($1, crypt($2, gen_salt('md5')));
1198 $$;
1199
1200 And make this change to F<sql/deploy/change_pass.sql>:
1201
1202 @@ -1,4 +1,4 @@
1203 --- requires: users, appuser
1204 +-- requires: users, appuser, pgcrypto
1205
1206 CREATE OR REPLACE FUNCTION change_pass(
1207 nick TEXT,
1208 @@ -7,9 +7,9 @@ CREATE OR REPLACE FUNCTION change_pass(
1209 ) RETURNS BOOLEAN LANGUAGE plpgsql AS $$
1210 BEGIN
1211 UPDATE users
1212 - SET password = md5($3)
1213 + SET password = crypt($3, gen_salt('md5'))
1214 WHERE nickname = $1
1215 - AND password = md5($2);
1216 + AND password = crypt($2, password);
1217 RETURN FOUND;
1218 END;
1219 $$;
1220
1221 And then test deployment:
1222
1223 > sqitch deploy --untracked
1224 Deploying HEAD+ to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
1225 + insert_user
1226 + change_pass
ad88d13 @theory Add example bundling idempotent changes.
authored
1227
1228 So, are the changes deployed?
1229
1230 > psql -d flipr_test -c "
1231 DELETE FROM users;
1232 SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
1233 SELECT * FROM users;
1234 "
1235 nickname | password | timestamp
1236 ----------+------------------------------------+-------------------------------
1237 foo | $1$l6OEKyF3$kv5ae7505ROub75d9QKTh/ | 2012-04-12 18:55:43.422211+00
1238 bar | $1$J4NJDgaJ$578i9Lt6b8ohJwi6WhNNO1 | 2012-04-12 18:55:43.422211+00
1239
1240 Awesome, the stored passwords are different now. But can we revert, even
1241 though we haven't written any reversion scripts?
1242
1243 > sqitch revert --to HEAD
1244 Reverting HEAD+ from flipr_test
1245 - change_pass
1246 - insert_user
1247
1248 Did that work, are the C<MD5()> passwords back?
1249
1250 > psql -d flipr_test -c "
1251 DELETE FROM users;
1252 SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t');
1253 SELECT * FROM users;
1254 "
1255 nickname | password | timestamp
1256 ----------+----------------------------------+-------------------------------
1257 foo | 9695da4dd567a19f9b92065f240c6725 | 2012-04-12 18:56:03.292423+00
1258 bar | 9695da4dd567a19f9b92065f240c6725 | 2012-04-12 18:56:03.292423+00
1259
1260 Yes, it works! How, you ask? Simple. Because Sqitch uses your VCS history to
1261 determine what steps to deploy, it can tell that the C<insert_user()> and
1262 C<change_pass()> scripts have changed, and just deploy the new version. When
1263 you revert, it simply retrieves the old version from the history and applies
1264 C<that>, instead. And that should underscore the importance of the idempotence
1265 of in-place modifications to change scripts. Because if the reversion had
1266 failed, or left the newer version still in place, it simply would not work.
1267
1268 This is a valuable feature, and certainly keeps the VCS history cleaner, so
1269 that it's easier to tell what changed in our database functions in a given
1270 commit. But you do have to use it with care. If you have any doubts about
1271 changing something in place, or risk orphaning some object, use the
1272 copy-and-paste approach instead.
1273
1274 So let's go ahead and commit these changes and bring the database up-to-date:
1275
1276 > git commit -m 'Use pgcrypto to encrypt passwords.'
1277 [master eea942d] Use pgcrypto to encrypt passwords.
1278 2 files changed, 3 insertions(+), 3 deletions(-)
1279
1280 > sqitch deploy
1281 Deploying eea942d to flipr_test
21d71c4 @theory Some tutorial tweaks.
authored
1282 + insert_user
1283 + change_pass
ad88d13 @theory Add example bundling idempotent changes.
authored
1284
1285 > sqitch -d flipr_test status
1286 # On database flipr_test
1287 # Tag: eea942d
1288 # Step: change_pass
1289 # Date: 2012-04-12 18:59:26
1290 #
1291 Nothing to deploy (up-to-date)
1292
1293 =head2 What about Bundling?
1294
1295 Of course, this only works if Sqitch is using the VCS history to fetch earlier
1296 versions of deployment scripts to do reversions. Such are obviously not
1297 available in a distributed bundle. Or are they? Let's create a new release and
1298 find out. Let's tag a C<v1.0.0-b1> release and create a new bundle:
1299
1300 > git tag v1.0.0-b1 -am 'Tag v1.0.0-b1'
1301 > sqitch bundle --tags-only
1302 Bundling in bundle/
21d71c4 @theory Some tutorial tweaks.
authored
1303 Config written to bundle/sqitch.plan
ad88d13 @theory Add example bundling idempotent changes.
authored
1304 Bundling v1.0.0-dev1
1305 - appuser
1306 - users
1307 - change_pass
1308 - insert_user
1309 Bundling lists
1310 - insert_list
1311 - delete_list
1312 Bundling flips
1313 - insert_flip
1314 - delete_flip
1315 Bundling v1.0.0-b1
1316 - change_pass_v2
1317 - insert_user_v2
1318 Plan written to bundle/sqitch.plan
1319
1320 Hey, look, the C<change_pass> and C<insert_user> steps are listed twice, but
1321 in the second instance, they have C<_v2> appended to their names. Let's have
1322 a look at the plan:
1323
1324 > cd bundle
1325 > cat sqitch.plan
1326 [v1.0.0-dev1]
1327 appuser
1328 users
1329 change_pass
1330 insert_user
1331
1332 [lists]
1333 insert_list
1334 delete_list
1335
1336 [flips]
1337 insert_flip
1338 delete_flip
1339
1340 [v1.0.0-b1]
1341 change_pass_v2
1342 insert_user_v2
1343
1344 Well that looks pretty much just like the output of the bundling. What if we
1345 deploy it to the C<flip_prod> database, which we built to C<v1.0.0-dev1>
1346 earlier. Here's what it looks like before we deploy the latest changes:
1347
1348 > sqitch -d flipr_prod status
1349 # On database flipr_prod
1350 # Tag: v1.0.0-dev1
1351 # Step: change_pass
1352 # Date: 2012-04-09 22:17:38
1353 #
1354 # Changes not deployed:
1355 # * lists
1356 # insert_list
1357 # delete_list
1358 # * flips
1359 # insert_flip
1360 # delete_flip
1361 # * v1.0.0-b1
1362 # change_pass_v2
1363 # insert_user_v2
1364 #
1365 Use "sqitch deploy" to deploy these changes
1366
1367 So, let's deploy.
1368
1369 > sqitch -d flipr_prod deploy
1370 Deploying lists to flipr_prod
21d71c4 @theory Some tutorial tweaks.
authored
1371 + insert_list
1372 + delete_list
ad88d13 @theory Add example bundling idempotent changes.
authored
1373 Deploying flips to flipr_prod
21d71c4 @theory Some tutorial tweaks.
authored
1374 + insert_flip
1375 + delete_flip
1376 Deploying v1.0.0+b1 to flipr_prod
1377 + change_pass_v2
1378 + insert_user_v2
ad88d13 @theory Add example bundling idempotent changes.
authored
1379
1380 And now the status is:
1381
1382 > sqitch -d flipr_prod status
1383 # On database flipr_prod
1384 # Tag: v1.0.0-dev1
1385 # Step: insert_user_v2
1386 # Date: 2012-04-12 19:14:34
1387 #
1388 Nothing to deploy (up-to-date)
1389
1390 All up-to-date. If necessary, we can also revert:
1391
1392 > sqitch -d flipr_prod revert --to flips
1393 Reverting v1.0.0-b1 from flipr_prod
1394 - insert_user_v2
1395 - change_pass_v2
1396
1397 > sqitch -d flipr_prod status
1398 # On database flipr_prod
1399 # Tag: flips
1400 # Step: delete_flip
1401 # Date: 2012-04-12 19:14:29
1402 #
1403 # Changes not deployed:
1404 # * v1.0.0-b1
1405 # change_pass_v2
1406 # insert_user_v2
1407 #
1408 Use "sqitch deploy" to deploy these changes
1409
1410 Nice, it just works! How? The secret is that, while the VCS history is not
1411 available when deploying from this plan, it was certainly available when the
1412 plan was I<written>. When we ran C<sqitch bundle>, Sqitch fetched all versions
1413 of the deployment scripts from the repository and wrote them out I<as they
1414 existed at the time of each tag>. In other words,
1415 F<sql/deploy/insert_user.sql> was written to the bundle as it existed at the
1416 time of the C<v1.0.0-dev1> tag, and F<sql/deploy/insert_user_v2.sql> was
1417 written as a copy of F<sql/deploy/insert_user.sql> at the time of the
1418 C<v1.0.0-b1> tag.
1419
1420 Put another way: when bundling the changes and writing the plan, Sqitch copied
1421 the entire migration script for each step in time, just as if you had
1422 duplicated code in new migration scripts the old-fashioned way. So you get the
1423 benefit of exploiting the VCS history in you development practices, while
1424 still getting the benefits of explicit, separate steps in the distribution
1425 you ship.
1426
Something went wrong with that request. Please try again.