Skip to content
This repository
Newer
Older
100644 789 lines (493 sloc) 20.811 kb
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
1 =head1 Name
2
3 sqitch - SQL Change Management
4
5 =head1 Synopsis
6
7 sqitch [<options>] <command> [<command-options>] [<args>]
8
9 =head1 Description
10
11 Sqitch is a VCS-aware SQL change management application. What makes it
12 different from your typical
13 L<migration|Module::Build::DB>-L<style|DBIx::Migration> approaches? A few
14 things:
15
16 =begin comment
17
18 Eventually move to L<sqitchintro>?
19
20 =end comment
21
22 =over
23
24 =item No opinions
25
26 Sqitch is not integrated with any framework, ORM, or platform. Rather, it is a
27 standalone change management system with no opinions on your database or
28 development choices.
29
30 =item Native scripting
31
32 Changes are implemented as scripts native to your selected database engine.
33 Writing a L<PostgreSQL|http://postgresql.org/> application? Write SQL scripts
34 for L<C<psql>|http://www.postgresql.org/docs/current/static/app-psql.html>.
35 Writing a L<MySQL|http://mysql.com/>-backed app? Write SQL scripts for
36 L<C<mysql>|http://dev.mysql.com/doc/refman/5.6/en/mysql.html>.
37
38 =item VCS integration
39
40 Sqitch likes to use your VCS history to determine in what order to execute
41 changes. No need to keep track of execution order; your VCS already tracks
42 information sufficient for Sqitch to figure it out for you.
43
44 =item Dependency resolution
45
46 Deployment steps can declare dependencies on other deployment steps. This
47 ensures proper order of execution, even when you've committed changes to your
48 VCS out-of-order.
49
50 =item No numbering
51
52 Change deployment is managed either by maintaining a plan file or, more
53 usefully, your VCS history. As such, there is no need to number your changes,
54 although you can if you want. Sqitch doesn't much care what you name your
55 changes.
56
57 =item Bundling
58
59 Rely on your VCS history for deployment but have Sqitch bundle up changes for
60 distribution. Sqitch can read your VCS history and write out a plan file along
61 with the appropriate deployment and reversion scripts. Once the bundle is
62 installed on a new system, Sqitch can use the plan file to deploy or the
63 changes in the proper order.
64
65 =item Reduced Duplication
66
67 If you're using a VCS to track your changes, you don't have to duplicate
68 entire change scripts for simple changes. As long as the changes are
69 L<idempotent|http://en.wikipedia.org/wiki/Idempotence>, you can change
70 your code directly, and Sqitch will know it needs to be updated.
71
72 =back
73
74 =begin comment
75
76 Eventually move to L<sqitchtutorial> or L<sqitchintro> or some such.
77
78 =end comment
79
80 =head2 Terminology
81
82 =over
83
84 =item C<step>
85
86 A named unit of change. A step name must be used in the file names of its
87 deployment and a reversion scripts. It may also be used in a test script file
88 name.
89
90 =item C<tag>
91
92 A known deployment state with a list one or more steps that define the tag. A
93 tag also implies that steps from previous tags in the plan have been applied.
94 Think of it is a version number or VCS revision. A given point in the plan may
95 have one or more tags.
96
97 =item C<state>
98
99 The current state of the database. This is represented by the most
100 recently-deployed steps and tags. If the state of the database is the same as
101 the most recent tag, then it is considered "up-to-date".
102
103 =item C<plan>
104
105 A list of one or more tags and associated steps that define the order of
106 deployment execution. Sqitch reads the plan to determine what steps to execute
107 to change the database from one state to another. The plan may be represented
108 by a L<Plan File> or by VCS history.
109
110 =item C<deploy>
111
112 The act of deploying database changes to reach a tagged deployment point.
113 Sqitch reads the plan, checks the current state of the database, and applies
114 all the steps necessary to change the state to the specified tag.
115
116 =item C<revert>
117
118 The act of reverting database changes to reach an earlier tagged deployment
119 point. Sqitch checks the current state of the database, reads the plan, and
120 reverts all steps necessary to return the state to an earlier tag.
121
122 =back
123
124 =head1 Options
125
126 --plan-file FILE Path to a deployment plan file.
127 --engine ENGINE Database engine.
128 --client PATH Path to the engine command-line client.
129 -d --db-name NAME Database name.
130 -u --username USER Database user name.
131 --host HOST Database server host name.
132 --port PORT Database server port number.
133 --sql-dir DIR Path to directory with deploy and revert scripts.
134 --deploy-dir DIR Path to directory with SQL deployment scripts.
135 --revert-dir DIR Path to directory with SQL reversion scripts.
136 --test-dir DIR Path to directory with SQL test scripts.
137 --extension EXT SQL script file name extension.
138 --dry-run Execute command without making any changes.
017f3cc2 »
2012-04-30 Add --etc-path.
139 --etc-path Print the path to the etc directory and exit.
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
140 --quiet Quiet mode with non-error output suppressed.
141 --verbose Increment verbosity.
142 --version Print the version number and exit.
143 --help Print a usage statement and exit.
144 --man Print the complete documentation and exit.
145
146 =head1 Options Details
147
148 =over
149
150 =item C<--plan-file>
151
152 sqitch --plan-file plan.conf
153
154 Path to the deployment plan file. Defaults to F<./sqitch.plan>. If this file
155 is not present, Sqitch will attempt to read from VCS files. If no supported
156 VCS system is in place, an exception will be thrown. See L</Plan File> for a
157 description of its structure.
158
159 =item C<--engine>
160
161 sqitch --engine pg
162
163 The database engine to use. Supported engines include:
164
165 =over
166
167 =item * C<pg> - L<PostgreSQL|http://postgresql.org/>
168
169 =item * C<mysql> - L<MySQL|http://mysql.com/>
170
171 =item * C<sqlite> - L<SQLite|http://sqlite.org/>
172
173 =back
174
175 =item C<--client>
176
177 sqitch --client /usr/local/pgsql/bin/psql
178
179 Path to the command-line client for the database engine. Defaults to a client
180 in the current path named appropriately for the specified engine.
181
182 =item C<-d>
183
184 =item C<--db-name>
185
186 sqitch --db-name widgets
187 sqitch -d bricolage
188
189 Name of the database. For some engines, such as
190 L<PostgreSQL|http://postgresql.org/> and L<MySQL|http://mysql.com/>, the
191 database must already exist. For others, such as L<SQLite|http://sqlite.org/>,
192 the database will be automatically created on first connect.
193
194 =item C<-u>
195
196 =item C<--user>
197
198 =item C<--username>
199
200 sqitch --username root
201 sqitch --user postgres
202 sqitch -u Mom
203
204 User name to use when connecting to the database. Does not apply to all engines.
205
206 =item C<--host>
207
208 sqitch --host db.example.com
209
210 Host name to use when connecting to the database. Does not apply to all
211 engines.
212
213 =item C<--port>
214
215 sqitch --port 7654
216
217 Port number to connect to. Does not apply to all engines.
218
219 =item C<--sql-dir>
220
221 sqitch --sql-dir migrations/
222
223 Path to directory containing deployment, reversion, and test SQL scripts. It
224 should contain subdirectories named C<deploy>, C<revert>, and (optionally)
225 C<test>. These may be overridden by C<--deploy-dir>, C<--revert-dir>, and
226 C<--test-dir>. Defaults to C<./sql>.
227
228 =item C<--deploy-dir>
229
230 sqitch --deploy-dir db/up
231
232 Path to a directory containing SQL deployment scripts. Overrides the value
233 implied by C<--sql-dir>.
234
235 =item C<--revert-dir>
236
237 sqitch --revert-dir db/up
238
239 Path to a directory containing SQL reversion scripts. Overrides the value
240 implied by C<--sql-dir>.
241
242 =item C<--test-dir>
243
244 sqitch --test-dir db/t
245
246 Path to a directory containing SQL test scripts. Overrides the value implied
247 by C<--sql-dir>.
248
249 =item C<--extension>
250
251 sqitch --extension ddl
252
253 The file name extension on deployment, reversion, and test SQL scripts.
254 Defaults to C<sql>.
255
256 =item C<--dry-run>
257
258 sqitch --dry-run
259
260 Execute the Sqitch command without making any actual changes. This allows you
261 to see what Sqitch would actually do, without doing it. Implies a verbosity
262 level of 1; add extra C<--verbose>s for greater verbosity.
263
017f3cc2 »
2012-04-30 Add --etc-path.
264 =item C<--etc-path>
265
266 sqitch --etc-path
267
268 Print out the path to the Sqtich F<etc> directory and exit. This is the
269 directory where the system-wide configuration file lives, as well as
270 deployment script templates.
271
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
272 =item C<--quiet>
273
274 Suppress normal output messages. Error messages will still be emitted to
275 C<STDERR>. Overrides any value specified by C<--verbose>.
276
277 =item C<--verbose>
278
279 sqitch --verbose --verbose
280
281 A value between 0 and 3 specifying how verbose Sqitch should be. Unless
282 C<--quiet> is specified, the default is 1, meaning that Sqitch will output
283 basic status messages as it does its thing. Values of 2 and 3 each cause
284 greater verbosity. Ignored if C<--quiet> is specified.
285
286 =item C<--help>
287
288 sqitch --help
289
290 Outputs a brief description of the options supported by C<sqitch> and exits.
291
292 =item C<--man>
293
294 sqitch --man
295
296 Outputs this documentation and exits.
297
298 =item C<-V>
299
300 =item C<--version>
301
302 sqitch --version
303
304 Outputs the program name and version and exits.
305
306 =back
307
308 =head1 Sqitch Commands
309
310 =over
311
35c1f8fd »
2012-04-26 Link to `sqitch-init` and `sqitch-config`.
312 =item L<C<init>|sqitch-init>
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
313
5d5b3651 »
2012-04-26 Update mention of init in main doc.
314 Create the plan file and deployment script directories if they do not already
315 exist. This command is useful for starting a new Sqitch project.
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
316
317 =item C<status>
318
319 Output information about the current status of the deployment, including a
320 list of tags, deployments, and dates in chronological order. If any deploy
321 scripts are not currently deployed, they will be listed separately. Options:
322
323 =over
324
325 =item C<-s>
326
327 =item C<--show>
328
329 How much information to show. This option may be passed without a C<-s> or
330 C<--show>. Possible values include:
331
332 =over
333
334 =item C<latest>
335
336 Show only the latest change. This is the default.
337
338 =item C<tags>
339
340 Show a list of all deployed tags.
341
342 =item C<steps>
343
344 Show a list of all deployed steps.
345
346 =back
347
348 =item C<format>
349
350 The format to use to display tags or steps, if either are specified via the
351 C<--show> option. The value may be any one of:
352
353 =over
354
355 =item C<oneline>
356
357 =item C<table>
358
359 =back
360
361 =back
362
363 =item C<log>
364
365 Output the complete change history in reverse chronological order. Options:
366
367 =over
368
369 =item C<-t>
370
371 =item C<--tag>
372
373 Show only changes for the specified tag. This option may be passed without a
374 C<-t> or C<--tag>.
375
376 =item C<-s>
377
378 =item C<--step>
379
380 Show only the specified step.
381
382 =item C<-n>
383
384 =item C<--max-count>
385
386 Limit the number of changes to show.
387
388 =item C<--skip>
389
390 Skip the specified number changes before starting to show the log output.
391
392 =item C<--since>
393
394 Show changes more recent than a specific date.
395
396 =item C<--until>
397
398 Show changes older than a specific date.
399
400 =item C<--user>
401
402 Limit the changes output to ones where the user name matches the specified
403 pattern (regular expression).
404
405 =item C<--reverse>
406
407 Output the changes in reverse sort order.
408
409 =item C<--format>
410
411 The format to use. The value may be any one of:
412
413 =over
414
415 =item C<short>
416
417 =item C<oneline>
418
419 =item C<full>
420
421 =item C<table>
422
423 =back
424
425 =back
426
427 =item C<check>
428
429 Sanity check the deployment scripts. Checks include:
430
431 =over
432
433 =item *
434
435 Make sure all deployment scripts have complementary reversion scripts.
436
437 =item *
438
439 Make sure no deployment script appears more than once in the plan file.
440
441 =back
442
443 =item C<add-step>
444
445 Add a new step. The specified name will be used to create stub deployment and
446 reversion scripts. The contents of these scripts must then be edited to carry
447 out the actual changes to the database. Options:
448
449 =over
450
451 =item C<-n>
452
453 =item C<--named>
454
455 The name of the step. Must be a valid step name. This option may be passed
456 without a C<-n> or C<--named>.
457
458 =item C<-r>
459
460 =item C<--requires>
461
462 Name of a step that is required by the new step.
463
464 =item C<-c>
465
466 =item C<--conflicts>
467
468 Name of a step that conflicts with the new step.
469
470 =back
471
472 =item C<deploy>
473
474 Deploy changes. Configuration properties may be specified under the
475 C<[deploy]> section of the configuration file, or via C<sqitch config>:
476
477 sqitch config deploy.$property $value
478
479 Options and configuration properties:
480
481 =over
482
483 =item C<--to>
484
485 Step or tag to deploy up to. Defaults to the latest tag or to the VCS C<HEAD>
486 commit. Property name: C<deploy.to>.
487
488 =item C<-a>
489
490 =item C<--untracked>
491
492 Also deploy any steps that have been added to the F<deploy> directory but have
493 not yet been added to the plan or committed to the VCS. Useful when developing
494 new deployment steps but have not yet committed them.
495
496 =back
497
498 =item C<revert>
499
500 Revert changes. Configuration properties may be specified under the
501 C<[revert]> section of the configuration file, or via C<sqitch config>:
502
503 sqitch config revert.$property $value
504
505 Options and configuration properties:
506
507 =over
508
509 =item C<--to>
510
511 Tag to revert to. Defaults to reverting all changes. Property name:
512 C<revert.to>.
513
514 =back
515
516 =item C<test>
517
518 Test changes. All SQL scripts in C<--test-dir> will be run.
519 [XXX Not sure whether to have subdirectories for tests and expected output and
520 to diff them, or to use some other approach.]
521
35c1f8fd »
2012-04-26 Link to `sqitch-init` and `sqitch-config`.
522 =item L<C<config>|sqitch-config>
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
523
35c1f8fd »
2012-04-26 Link to `sqitch-init` and `sqitch-config`.
524 Get and set project, user, or system Sqitch options. By default, the options
525 will be written to the local configuration file, F<sqitch.conf>
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
526
527 =item C<bundle>
528
529 Bundle up deployment and reversion scripts and write out a plan file.
530 Configuration properties may be specified under the C<[bundle]> section of the
531 configuration file, or via C<sqitch config bundle.$property $value> command.
532 Options and configuration properties:
533
534 =over
535
536 =item C<--from>
537
538 Tag to start the plan from. All tags and steps prior to that tag will not be
539 included in the plan, and their change scripts Will be omitted from the bundle
540 directory. Useful if you've rejiggered your deployment steps to start from a
541 point later in your VCS history than the beginning of time. Property name:
542 C<bundle.from>.
543
544 =item C<--to>
545
546 Tag with which to end the plan. No steps or tags after that tag will be
547 included in the plan, and their change scripts will be omitted from the bundle
548 directory. Property name: C<bundle.to>.
549
550 =item C<--tags-only>
551
552 Write the plan file with deployment targets listed under VCS tags, rather than
553 individual commits. Property name: C<bundle.tags_only>.
554
555 =item C<--destdir>
556
557 Specify a destination directory. The plan file and C<deploy>, C<revert>, and
558 C<test> directories will be written to it. Defaults to "bundle". Property
559 name: C<bundle.destdir>.
560
561 =back
562
eca963ff »
2012-04-26 Mention `sqitch help` in sqitch doc.
563 =item L<C<help>|sqitch-help>
564
565 Show help for a specific command or, if no command is specified, show the same
566 documentation as C<--help>.
567
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
568 =back
569
570 =head1 Configuration
571
b7dfb7d3 »
2012-04-26 Move config docs out of sqitch.pod.
572 Sqitch configuration can be set up on a project, user, or system-wide basis.
573 The format of the configuration file, named F<sqitch.conf>, is the same as for
574 L<git>.
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
575
576 Here's an example of a configuration file that might be useful checked into a
577 VCS for a project that deploys to PostgreSQL and stores its deployment scripts
578 with the extension F<ddl> under the C<migrations> directory. It also wants
579 bundle to be created in the F<_build/sql> directory, and to deploy starting
580 with the "gamma" tag:
581
582 [core]
583 engine = pg
584 db = widgetopolis
585 sql_dir = migrations
586 extension = ddl
587
588 [revert]
589 to = gamma
590
591 [bundle]
592 from = gamma
593 tags_only = yes
594 dest_dir = _build/sql
595
d7370f15 »
2012-04-25 Start work on `write_config()`.
596 And here's an example of useful configuration in F<~/.sqitch/sqitch.conf>, to
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
597 point to system-specific engine information:
598
599 [core.pg]
600 client = /usr/local/pgsql/bin/psql
601 username = theory
602
603 [core.mysql]
604 client = /usr/local/mysql/bin/mysql
605 username = root
606
607 [core.sqlite]
608 client = /usr/local/bin/sqlite3
609
b7dfb7d3 »
2012-04-26 Move config docs out of sqitch.pod.
610 Various commands read from the configuration file and adjust their operation
611 accordingly. See L<sqitch-config> for a list.
fad0c76b »
2012-04-24 Move docs to `sqitch.pm` and handle no command issue.
612
613 =head1 Plan File
614
615 A plan file describes the deployment tags and scripts to be run against a
616 database. In general, if you use a VCS, you probably won't need a plan file,
617 since your VCS history should be able to provide all the information necessary
618 to derive a deployment plan. However, if you really do need to maintain a plan
619 file by hand, or just want to better understand the file as output by the
620 C<bundle> command, read on.
621
622 =head2 Format
623
624 The contents of the plan file are plain text encoded as UTF-8. It is divided
625 up into sections that denote deployment states. Each state has a bracketed,
626 space-delimited list of one or more tags to identify it, followed by any
627 number of deployment steps. Here's an example of a plan file with a single
628 state and a single step:
629
630 [alpha]
631 users_table
632
633 The state has one tag, named "alpha", and one step, named "users_table".
634 A state may of course have many steps. Here's an expansion:
635
636 [root alpha]
637 users_table
638 insert_user
639 update_user
640 delete_user
641
642 This state has two tags, "root" and "alpha", and four steps, "users_table",
643 "insert_user", "update_user", and "delete_user".
644
645 Most plans will have multiple states. Here's a longer example with three
646 states:
647
648 [root alpha]
649 users_table
650 insert_user
651 update_user
652 delete_user
653
654 [beta]
655 widgets_table
656 list_widgets
657
658 [gamma]
659 ftw
660
661 Using this plan, to deploy to the "beta" tag, the "root"/"alpha" state steps
662 must be deployed, as must the "beta" steps. To then deploy to the "gamma" tag,
663 the "ftw" step must be deployed. If you then choose to revert to the "alpha"
664 tag, then the "gamma" step ("ftw") and all of the "beta" steps will be
665 reverted in reverse order.
666
667 Using this model, steps cannot be repeated between states. One can repeat
668 them, however, if the contents for a file in a given tag can be retrieved from
669 a VCS. An example:
670
671 [alpha]
672 users_table
673
674 [beta]
675 add_widget
676 widgets_table
677
678 [gamma]
679 add_user
680
681 [44ba615b7813531f0acb6810cbf679791fe57bf2]
682 widgets_created_at
683
684 [HEAD epsilon master]
685 add_widget
686
687 This example is derived from a Git log history. Note that the "add_widget"
688 step is repeated under the state tagged "beta" and under the last state.
689 Sqitch will notice the repetition when it parses this file, and then, if it is
690 applying all changes, will fetch the version of the file as of the "beta" tag
691 and apply it at that step, and then, when it gets to the last tag, retrieve
692 the deployment file as of its tags and apply it. This works in reverse, as
693 well, as long as the changes in this file are always
694 L<idempotent|http://en.wikipedia.org/wiki/Idempotence>.
695
696 =head2 Grammar
697
698 Here is the EBNF Grammar for the plan file:
699
700 plan-file = { <state> | <empty-line> | <comment> }* ;
701
702 state = <tags> <steps> ;
703
704 tags = "[" <taglist> "]" <line-ending> ;
705 taglist = <name> | <name> <white-space> <taglist> ;
706
707 steps = { <step> | <empty-line> | <line-ending> }* ;
708 step = <name> <line-ending> ;
709
710 empty-line = [ <white-space> ] <line-ending> ;
711 line-ending = [ <comment> ] <EOL> ;
712 comment = [ <white-space> ] "#" [ <string> ] ;
713
714 name = ? non-white space characters ? ;
715 white-space = ? white space characters ? ;
716 string = ? non-EOL characters ? ;
717
718 =head1 See Also
719
720 The original design for Sqitch was sketched out in a number of blog posts:
721
722 =over
723
724 =item *
725
726 L<Simple SQL Change Management|http://justatheory.com/computers/databases/simple-sql-change-management.html>
727
728 =item *
729
730 L<VCS-Enabled SQL Change Management|http://justatheory.com/computers/databases/vcs-sql-change-management.html>
731
732 =item *
733
734 L<SQL Change Management Sans Duplication|http://justatheory.com/computers/databases/sql-change-management-sans-redundancy.html>
735
736 =back
737
738 Other tools that do database change management include:
739
740 =over
741
742 =item L<Rails migrations|http://guides.rubyonrails.org/migrations.html>
743
744 Numbered migrations for L<Ruby on Rails|http://rubyonrails.org/>.
745
746 =item L<Module::Build::DB>
747
748 Numbered changes in pure SQL, integrated with Perl's L<Module::Build> build
749 system. Does not support reversion.
750
751 =item L<DBIx::Migration>
752
753 Numbered migrations in pure SQL.
754
755 =item L<Versioning|http://www.depesz.com/2010/08/22/versioning/>
756
757 PostgreSQL-specific dependency-tracking solution by
758 L<depesz|http://www.depesz.com/>.
759
760 =back
761
762 =head1 Author
763
764 David E. Wheeler <david@justatheory.com>
765
766 =head1 License
767
768 Copyright (c) 2012 iovation Inc.
769
770 Permission is hereby granted, free of charge, to any person obtaining a copy
771 of this software and associated documentation files (the "Software"), to deal
772 in the Software without restriction, including without limitation the rights
773 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
774 copies of the Software, and to permit persons to whom the Software is
775 furnished to do so, subject to the following conditions:
776
777 The above copyright notice and this permission notice shall be included in all
778 copies or substantial portions of the Software.
779
780 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
781 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
782 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
783 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
784 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
785 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
786 SOFTWARE.
787
788 =cut
Something went wrong with that request. Please try again.