/usr/share/doc/python-migrate/html/versioning.html is in python-migrate 0.8.2-3ubuntu1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Database schema versioning workflow — SQLAlchemy Migrate 0.7.3.dev documentation</title>
<link rel="stylesheet" href="_static/default.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: './',
VERSION: '0.7.3.dev',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<link rel="top" title="SQLAlchemy Migrate 0.7.3.dev documentation" href="index.html" />
<link rel="next" title="Database schema migrations" href="changeset.html" />
<link rel="prev" title="Credits" href="credits.html" />
</head>
<body>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="changeset.html" title="Database schema migrations"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="credits.html" title="Credits"
accesskey="P">previous</a> |</li>
<li><a href="index.html">SQLAlchemy Migrate 0.7.3.dev documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body">
<div class="section" id="database-schema-versioning-workflow">
<h1>Database schema versioning workflow<a class="headerlink" href="#database-schema-versioning-workflow" title="Permalink to this headline">¶</a></h1>
<p>SQLAlchemy migrate provides the <a class="reference internal" href="api.html#module-migrate.versioning" title="migrate.versioning: Database version and repository management"><tt class="xref py py-mod docutils literal"><span class="pre">migrate.versioning</span></tt></a> API that is
also available as the <a class="reference internal" href="#command-line-usage"><em>migrate</em></a> command.</p>
<p>Purpose of this package is frontend for migrations. It provides commands to
manage migrate <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> and database selection as well as script
versioning.</p>
<div class="section" id="project-setup">
<h2>Project setup<a class="headerlink" href="#project-setup" title="Permalink to this headline">¶</a></h2>
<div class="section" id="create-a-change-repository">
<span id="create-change-repository"></span><h3>Create a change repository<a class="headerlink" href="#create-a-change-repository" title="Permalink to this headline">¶</a></h3>
<p>To begin, we’ll need to create a <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> for our project.</p>
<p>All work with repositories is done using the <a class="reference internal" href="#command-line-usage"><em>migrate</em></a> command. Let’s create our project’s repository:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> migrate create my_repository <span class="s2">"Example project"</span>
</pre></div>
</div>
<p>This creates an initially empty <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> relative to current
directory at <tt class="file docutils literal"><span class="pre">my_repository/</span></tt> named <cite>Example project</cite>.</p>
<p>The <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> directory contains a sub directory <tt class="file docutils literal"><span class="pre">versions</span></tt> that
will store the <a class="reference internal" href="changeset.html#changeset-system"><em>schema versions</em></a>, a configuration file
<tt class="file docutils literal"><span class="pre">migrate.cfg</span></tt> that contains <a class="reference internal" href="#repository-configuration"><em>repository configuration</em></a> and a script <a class="reference internal" href="#id1"><em>manage.py</em></a> that has the same functionality as the
<a class="reference internal" href="#command-line-usage"><em>migrate</em></a> command but is preconfigured with
repository specific parameters.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Repositories are associated with a single database schema, and store
collections of change scripts to manage that schema. The scripts in a
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> may be applied to any number of databases. Each
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> has an unique name. This name is used to identify the
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> we’re working with.</p>
</div>
</div>
<div class="section" id="version-control-a-database">
<h3>Version control a database<a class="headerlink" href="#version-control-a-database" title="Permalink to this headline">¶</a></h3>
<p>Next we need to declare database to be under version control. Information on a
database’s version is stored in the database itself; declaring a database to be
under version control creates a table named <strong>migrate_version</strong> and associates
it with your <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a>.</p>
<p>The database is specified as a <a class="reference external" href="http://www.sqlalchemy.org/docs/core/engines.html#database-urls">SQLAlchemy database url</a>.</p>
<p>The <em class="xref std std-option">version_control</em> command assigns a specified database with a
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a>:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python my_repository/manage.py version_control sqlite:///project.db my_repository
</pre></div>
</div>
<p>We can have any number of databases under this <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository’s</em></a> version control.</p>
<p>Each schema has a <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> that SQLAlchemy Migrate manages. Each change
script applied to the database increments this version number. You can retrieve
a database’s current <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a>:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python my_repository/manage.py db_version sqlite:///project.db my_repository
<span class="go">0</span>
</pre></div>
</div>
<p>A freshly versioned database begins at version 0 by default. This assumes the
database is empty or does only contain schema elements (tables, views,
constraints, indices, ...) that will not be affected by the changes in the
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a>. (If this is a bad assumption, you can specify the
<a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> at the time the database is put under version control, with the
<em class="xref std std-option">version_control</em> command.) We’ll see that creating and applying change
scripts changes the database’s <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> number.</p>
<p>Similarly, we can also see the latest <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> available in a
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> with the command:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python my_repository/manage.py version my_repository
<span class="go">0</span>
</pre></div>
</div>
<p>We’ve entered no changes so far, so our <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> cannot upgrade a
database past version 0.</p>
</div>
<div class="section" id="project-management-script">
<h3>Project management script<a class="headerlink" href="#project-management-script" title="Permalink to this headline">¶</a></h3>
<p id="id1">Many commands need to know our project’s database url and <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a>
path - typing them each time is tedious. We can create a script for our project
that remembers the database and <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> we’re using, and use it to
perform commands:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> migrate manage manage.py --repository<span class="o">=</span>my_repository --url<span class="o">=</span>sqlite:///project.db
<span class="gp">$</span> python manage.py db_version
<span class="go">0</span>
</pre></div>
</div>
<p>The script <tt class="file docutils literal"><span class="pre">manage.py</span></tt> was created. All commands we perform with it are
the same as those performed with the <a class="reference internal" href="#command-line-usage"><em>migrate</em></a> tool,
using the <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> and database connection entered above. The
difference between the script <tt class="file docutils literal"><span class="pre">manage.py</span></tt> in the current directory and
the script inside the repository is, that the one in the current directory has
the database URL preconfigured.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Parameters specified in manage.py should be the same as in <a class="reference internal" href="api.html#versioning-api"><em>versioning
api</em></a>. Preconfigured parameter should just be omitted from
<a class="reference internal" href="#command-line-usage"><em>migrate</em></a> command.</p>
</div>
</div>
</div>
<div class="section" id="making-schema-changes">
<h2>Making schema changes<a class="headerlink" href="#making-schema-changes" title="Permalink to this headline">¶</a></h2>
<p>All changes to a database schema under version control should be done via
change scripts - you should avoid schema modifications (creating tables, etc.)
outside of change scripts. This allows you to determine what the schema looks
like based on the version number alone, and helps ensure multiple databases
you’re working with are consistent.</p>
<div class="section" id="create-a-change-script">
<h3>Create a change script<a class="headerlink" href="#create-a-change-script" title="Permalink to this headline">¶</a></h3>
<p>Our first change script will create a simple table</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">account</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s">'account'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'login'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">40</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'passwd'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">40</span><span class="p">)),</span>
<span class="p">)</span>
</pre></div>
</div>
<p>This table should be created in a change script. Let’s create one:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py script <span class="s2">"Add account table"</span>
</pre></div>
</div>
<p>This creates an empty change script at
<tt class="file docutils literal"><span class="pre">my_repository/versions/001_Add_account_table.py</span></tt>. Next, we’ll
edit this script to create our table.</p>
</div>
<div class="section" id="edit-the-change-script">
<h3>Edit the change script<a class="headerlink" href="#edit-the-change-script" title="Permalink to this headline">¶</a></h3>
<p>Our change script predefines two functions, currently empty:
<tt class="xref py py-func docutils literal"><span class="pre">upgrade()</span></tt> and <tt class="xref py py-func docutils literal"><span class="pre">downgrade()</span></tt>. We’ll fill those in:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">MetaData</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">account</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span>
<span class="s">'account'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'login'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">40</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'passwd'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">40</span><span class="p">)),</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="n">account</span><span class="o">.</span><span class="n">create</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="n">account</span><span class="o">.</span><span class="n">drop</span><span class="p">()</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">The generated script contains * imports from sqlalchemy and migrate. You
should tailor the imports to fit your actual demand.</p>
</div>
<p>As you might have guessed, <tt class="xref py py-func docutils literal"><span class="pre">upgrade()</span></tt> upgrades the database to the next
version. This function should contain the <a class="reference internal" href="changeset.html#changeset-system"><em>schema changes</em></a> we want to perform (in our example we’re creating a
table).</p>
<p><tt class="xref py py-func docutils literal"><span class="pre">downgrade()</span></tt> should reverse changes made by <tt class="xref py py-func docutils literal"><span class="pre">upgrade()</span></tt>. You’ll
need to write both functions for every change script. (Well, you don’t <em>have</em>
to write downgrade, but you won’t be able to revert to an older version of the
database or test your scripts without it.) If you really don’t want to support
downgrades it is a good idea to raise a <tt class="xref py py-class docutils literal"><span class="pre">NotImplementedError</span></tt> or some
equivalent custom exception. If you let <tt class="xref py py-func docutils literal"><span class="pre">downgrade()</span></tt> pass silently you
might observe undesired behaviour for subsequent downgrade operations if
downgrading multiple <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">versions</em></a>.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">As you can see, <strong>migrate_engine</strong> is passed to both functions. You should
use this in your change scripts, rather than creating your own engine.</p>
</div>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">You should be very careful about importing files from the rest of your
application, as your change scripts might break when your application
changes. Read more about <a class="reference internal" href="#writing-scripts-with-consistent-behavior">writing scripts with consistent behavior</a>.</p>
</div>
</div>
<div class="section" id="test-the-change-script">
<h3>Test the change script<a class="headerlink" href="#test-the-change-script" title="Permalink to this headline">¶</a></h3>
<p>Change scripts should be tested before they are committed. Testing a script
will run its <tt class="xref py py-func docutils literal"><span class="pre">upgrade()</span></tt> and <tt class="xref py py-func docutils literal"><span class="pre">downgrade()</span></tt> functions on a specified
database; you can ensure the script runs without error. You should be testing
on a test database - if something goes wrong here, you’ll need to correct it by
hand. If the test is successful, the database should appear unchanged after
<tt class="xref py py-func docutils literal"><span class="pre">upgrade()</span></tt> and <tt class="xref py py-func docutils literal"><span class="pre">downgrade()</span></tt> run.</p>
<p>To test the script:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py <span class="nb">test</span>
<span class="go">Upgrading... done</span>
<span class="go">Downgrading... done</span>
<span class="go">Success</span>
</pre></div>
</div>
<p>Our script runs on our database (<tt class="file docutils literal"><span class="pre">sqlite:///project.db</span></tt>, as specified in
<tt class="file docutils literal"><span class="pre">manage.py</span></tt>) without any errors.</p>
<p>Our <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository’s</em></a> <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> is:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py version
<span class="go">1</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Due to #41 the database must be exactly one <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> behind the
<a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repository</em></a> <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a>.</p>
</div>
<div class="admonition warning" id="production-testing-warning">
<p class="first admonition-title">Warning</p>
<p>The <em class="xref std std-option">test</em> command executes actual scripts, be sure you are <em>NOT</em>
doing this on production database.</p>
<p>If you need to test production changes you should:</p>
<blockquote class="last">
<div><ol class="arabic simple">
<li>get a dump of your production database</li>
<li>import the dump into an empty database</li>
<li>run <em class="xref std std-option">test</em> or <em class="xref std std-option">upgrade</em> on that copy</li>
</ol>
</div></blockquote>
</div>
</div>
<div class="section" id="upgrade-the-database">
<h3>Upgrade the database<a class="headerlink" href="#upgrade-the-database" title="Permalink to this headline">¶</a></h3>
<p>Now, we can apply this change script to our database:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py upgrade
<span class="go">0 -> 1...</span>
<span class="go">done</span>
</pre></div>
</div>
<p>This upgrades the database (<tt class="file docutils literal"><span class="pre">sqlite:///project.db</span></tt>, as specified when we
created <tt class="file docutils literal"><span class="pre">manage.py</span></tt> above) to the latest available <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a>. (We
could also specify a version number if we wished, using the <em class="xref std std-option">--version</em>
option.) We can see the database’s <a class="reference internal" href="glossary.html#term-version"><em class="xref std std-term">version</em></a> number has changed, and our
table has been created:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py db_version
<span class="go">1</span>
<span class="gp">$</span> sqlite3 project.db
<span class="go">sqlite> .tables</span>
<span class="go">account migrate_version</span>
<span class="go">sqlite> .schema account</span>
<span class="go">CREATE TABLE account (</span>
<span class="go"> id INTEGER NOT NULL,</span>
<span class="go"> login VARCHAR(40),</span>
<span class="go"> passwd VARCHAR(40),</span>
<span class="go"> PRIMARY KEY (id)</span>
<span class="go">);</span>
</pre></div>
</div>
<p>Our account table was created - success!</p>
</div>
<div class="section" id="modifying-existing-tables">
<h3>Modifying existing tables<a class="headerlink" href="#modifying-existing-tables" title="Permalink to this headline">¶</a></h3>
<p>After we have initialized the database schema we now want to add another Column
to the <cite>account</cite> table that we already have in our schema.</p>
<p>First start a new <a class="reference internal" href="glossary.html#term-changeset"><em class="xref std std-term">changeset</em></a> by the commands learned above:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py script <span class="s2">"Add email column"</span>
</pre></div>
</div>
<p>This creates a new <a class="reference internal" href="glossary.html#term-changeset"><em class="xref std std-term">changeset</em></a> template. Edit the resulting script
<tt class="file docutils literal"><span class="pre">my_repository/versions/002_Add_email_column.py</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">migrate_engine</span><span class="p">)</span>
<span class="n">account</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'account'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">emailc</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="s">'email'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">128</span><span class="p">))</span>
<span class="n">emailc</span><span class="o">.</span><span class="n">create</span><span class="p">(</span><span class="n">account</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">migrate_engine</span><span class="p">)</span>
<span class="n">account</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'account'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">account</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email</span><span class="o">.</span><span class="n">drop</span><span class="p">()</span>
</pre></div>
</div>
<p>As we can see in this example we can (and should) use SQLAlchemy’s schema
reflection (autoload) mechanism to reference existing schema objects. We could
have defined the table objects as they are expected before upgrade or downgrade
as well but this would have been more work and is not as convenient.</p>
<p>We can now apply the changeset to <tt class="file docutils literal"><span class="pre">sqlite:///project.db</span></tt>:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py upgrade
<span class="go">1 -> 2...</span>
<span class="go">done</span>
</pre></div>
</div>
<p>and get the following expected result:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> sqlite3 project.db
<span class="go">sqlite> .schema account</span>
<span class="go">CREATE TABLE account (</span>
<span class="go"> id INTEGER NOT NULL,</span>
<span class="go"> login VARCHAR(40),</span>
<span class="go"> passwd VARCHAR(40), email VARCHAR(128),</span>
<span class="go"> PRIMARY KEY (id)</span>
<span class="go">);</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="writing-change-scripts">
<h2>Writing change scripts<a class="headerlink" href="#writing-change-scripts" title="Permalink to this headline">¶</a></h2>
<p>As our application evolves, we can create more change scripts using a similar
process.</p>
<p>By default, change scripts may do anything any other SQLAlchemy program can do.</p>
<p>SQLAlchemy Migrate extends SQLAlchemy with several operations used to change
existing schemas - ie. <tt class="docutils literal"><span class="pre">ALTER</span> <span class="pre">TABLE</span></tt> stuff. See <a class="reference internal" href="changeset.html#changeset-system"><em>changeset</em></a> documentation for details.</p>
<div class="section" id="writing-scripts-with-consistent-behavior">
<h3>Writing scripts with consistent behavior<a class="headerlink" href="#writing-scripts-with-consistent-behavior" title="Permalink to this headline">¶</a></h3>
<p>Normally, it’s important to write change scripts in a way that’s independent of
your application - the same SQL should be generated every time, despite any
changes to your app’s source code. You don’t want your change scripts’ behavior
changing when your source code does.</p>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p><strong>Consider the following example of what NOT to do</strong></p>
<p>Let’s say your application defines a table in the <tt class="file docutils literal"><span class="pre">model.py</span></tt> file:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="o">*</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'mytable'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="p">)</span>
</pre></div>
</div>
<p>... and uses this file to create a table in a change script:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">migrate</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">import</span> <span class="nn">model</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">model</span><span class="o">.</span><span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">model</span><span class="o">.</span><span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="n">model</span><span class="o">.</span><span class="n">table</span><span class="o">.</span><span class="n">drop</span><span class="p">()</span>
</pre></div>
</div>
<p>This runs successfully the first time. But what happens if we change the
table definition in <tt class="file docutils literal"><span class="pre">model.py</span></tt>?</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="o">*</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'mytable'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">42</span><span class="p">)),</span>
<span class="p">)</span>
</pre></div>
</div>
<p>We’ll create a new column with a matching change script</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">migrate</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">import</span> <span class="nn">model</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">model</span><span class="o">.</span><span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="n">model</span><span class="o">.</span><span class="n">table</span><span class="o">.</span><span class="n">create</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">(</span><span class="n">migrate_engine</span><span class="p">):</span>
<span class="n">model</span><span class="o">.</span><span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">migrate_engine</span>
<span class="n">model</span><span class="o">.</span><span class="n">table</span><span class="o">.</span><span class="n">drop</span><span class="p">()</span>
</pre></div>
</div>
<p>This appears to run fine when upgrading an existing database - but the
first script’s behavior changed! Running all our change scripts on a new
database will result in an error - the first script creates the table based
on the new definition, with both columns; the second cannot add the column
because it already exists.</p>
<p>To avoid the above problem, you should use SQLAlchemy schema reflection as
shown above or copy-paste your table definition into each change script
rather than importing parts of your application.</p>
<div class="last admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Sometimes it is enough to just reflect tables with SQLAlchemy instead
of copy-pasting - but remember, explicit is better than implicit!</p>
</div>
</div>
</div>
<div class="section" id="writing-for-a-specific-database">
<h3>Writing for a specific database<a class="headerlink" href="#writing-for-a-specific-database" title="Permalink to this headline">¶</a></h3>
<p>Sometimes you need to write code for a specific database. Migrate scripts can
run under any database, however - the engine you’re given might belong to any
database. Use engine.name to get the name of the database you’re working with</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="o">*</span>
<span class="gp">>>> </span><span class="kn">from</span> <span class="nn">migrate</span> <span class="kn">import</span> <span class="o">*</span>
<span class="go">>>></span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///:memory:'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">engine</span><span class="o">.</span><span class="n">name</span>
<span class="go">'sqlite'</span>
</pre></div>
</div>
</div>
<div class="section" id="writings-sql-scripts">
<h3>Writings .sql scripts<a class="headerlink" href="#writings-sql-scripts" title="Permalink to this headline">¶</a></h3>
<p>You might prefer to write your change scripts in SQL, as .sql files, rather
than as Python scripts. SQLAlchemy-migrate can work with that:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> python manage.py version
<span class="go">1</span>
<span class="gp">$</span> python manage.py script_sql postgresql
</pre></div>
</div>
<p>This creates two scripts
<tt class="file docutils literal"><span class="pre">my_repository/versions/002_postgresql_upgrade.sql</span></tt> and
<tt class="file docutils literal"><span class="pre">my_repository/versions/002_postgresql_downgrade.sql</span></tt>, one for each
<em>operation</em>, or function defined in a Python change script - upgrade and
downgrade. Both are specified to run with PostgreSQL databases - we can add
more for different databases if we like. Any database defined by SQLAlchemy may
be used here - ex. sqlite, postgresql, oracle, mysql...</p>
</div>
</div>
<div class="section" id="command-line-usage">
<span id="id2"></span><h2>Command line usage<a class="headerlink" href="#command-line-usage" title="Permalink to this headline">¶</a></h2>
<p><strong class="command">migrate</strong> command is used for API interface. For list of commands and
help use:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> migrate --help
</pre></div>
</div>
<p><strong class="command">migrate</strong> command executes <a class="reference internal" href="api.html#migrate.versioning.shell.main" title="migrate.versioning.shell.main"><tt class="xref py py-func docutils literal"><span class="pre">main()</span></tt></a> function.
For ease of usage, generate your own <a class="reference internal" href="#id1"><em>project management script</em></a>, which calls <a class="reference internal" href="api.html#migrate.versioning.shell.main" title="migrate.versioning.shell.main"><tt class="xref py py-func docutils literal"><span class="pre">main</span></tt></a> function with keywords arguments. You may want
to specify <cite>url</cite> and <cite>repository</cite> arguments which almost all API functions
require.</p>
<p>If api command looks like:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> migrate downgrade URL REPOSITORY VERSION <span class="o">[</span>--preview_sql|--preview_py<span class="o">]</span>
</pre></div>
</div>
<p>and you have a project management script that looks like</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">migrate.versioning.shell</span> <span class="kn">import</span> <span class="n">main</span>
<span class="n">main</span><span class="p">(</span><span class="n">url</span><span class="o">=</span><span class="s">'sqlite://'</span><span class="p">,</span> <span class="n">repository</span><span class="o">=</span><span class="s">'./project/migrations/'</span><span class="p">)</span>
</pre></div>
</div>
<p>you have first two slots filed, and command line usage would look like:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">#</span> preview Python script
<span class="gp">$</span> migrate downgrade 2 --preview_py
<span class="gp">#</span> downgrade to version 2
<span class="gp">$</span> migrate downgrade 2
</pre></div>
</div>
<div class="versionchanged">
<p><span class="versionmodified">Changed in version 0.5.4: </span>Command line parsing refactored: positional parameters usage</p>
</div>
<p>Whole command line parsing was rewriten from scratch with use of OptionParser.
Options passed as kwargs to <a class="reference internal" href="api.html#migrate.versioning.shell.main" title="migrate.versioning.shell.main"><tt class="xref py py-func docutils literal"><span class="pre">main()</span></tt></a> are now
parsed correctly. Options are passed to commands in the following priority
(starting from highest):</p>
<ul class="simple">
<li>optional (given by <em class="xref std std-option">--some_option</em> in commandline)</li>
<li>positional arguments</li>
<li>kwargs passed to <a class="reference internal" href="api.html#migrate.versioning.shell.main" title="migrate.versioning.shell.main"><tt class="xref py py-func docutils literal"><span class="pre">migrate.versioning.shell.main()</span></tt></a></li>
</ul>
</div>
<div class="section" id="python-api">
<h2>Python API<a class="headerlink" href="#python-api" title="Permalink to this headline">¶</a></h2>
<p>All commands available from the command line are also available for
your Python scripts by importing <a class="reference internal" href="api.html#module-migrate.versioning.api" title="migrate.versioning.api: External API for :mod:`migrate.versioning`"><tt class="xref py py-mod docutils literal"><span class="pre">migrate.versioning.api</span></tt></a>. See the
<a class="reference internal" href="api.html#module-migrate.versioning.api" title="migrate.versioning.api: External API for :mod:`migrate.versioning`"><tt class="xref py py-mod docutils literal"><span class="pre">migrate.versioning.api</span></tt></a> documentation for a list of functions;
function names match equivalent shell commands. You can use this to
help integrate SQLAlchemy Migrate with your existing update process.</p>
<p>For example, the following commands are similar:</p>
<p><em>From the command line</em>:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="gp">$</span> migrate <span class="nb">help help</span>
<span class="go">/usr/bin/migrate help COMMAND</span>
<span class="go"> Displays help on a given command.</span>
</pre></div>
</div>
<p><em>From Python</em></p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">migrate.versioning.api</span>
<span class="n">migrate</span><span class="o">.</span><span class="n">versioning</span><span class="o">.</span><span class="n">api</span><span class="o">.</span><span class="n">help</span><span class="p">(</span><span class="s">'help'</span><span class="p">)</span>
<span class="c"># Output:</span>
<span class="c"># %prog help COMMAND</span>
<span class="c">#</span>
<span class="c"># Displays help on a given command.</span>
</pre></div>
</div>
</div>
<div class="section" id="experimental-commands">
<span id="repository-configuration"></span><h2>Experimental commands<a class="headerlink" href="#experimental-commands" title="Permalink to this headline">¶</a></h2>
<p>Some interesting new features to create SQLAlchemy db models from existing
databases and vice versa were developed by Christian Simms during the
development of SQLAlchemy-migrate 0.4.5. These features are roughly documented
in a <a class="reference external" href="http://groups.google.com/group/migrate-users/browse_thread/thread/a5605184e08abf33#msg_85c803b71b29993f">thread in migrate-users</a>.</p>
<p>Here are the commands’ descriptions as given by <tt class="docutils literal"><span class="pre">migrate</span> <span class="pre">help</span> <span class="pre"><command></span></tt>:</p>
<ul class="simple">
<li><tt class="docutils literal"><span class="pre">compare_model_to_db</span></tt>: Compare the current model (assumed to be a
module level variable of type sqlalchemy.MetaData) against the
current database.</li>
<li><tt class="docutils literal"><span class="pre">create_model</span></tt>: Dump the current database as a Python model to
stdout.</li>
<li><tt class="docutils literal"><span class="pre">make_update_script_for_model</span></tt>: Create a script changing the old
Python model to the new (current) Python model, sending to stdout.</li>
</ul>
<p>As this sections headline says: These features are <em>EXPERIMENTAL</em>. Take the
necessary arguments to the commands from the output of <tt class="docutils literal"><span class="pre">migrate</span>
<span class="pre">help</span> <span class="pre"><command></span></tt>.</p>
</div>
<div class="section" id="id3">
<h2>Repository configuration<a class="headerlink" href="#id3" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy-migrate <a class="reference internal" href="glossary.html#term-repository"><em class="xref std std-term">repositories</em></a> can be configured in their
<tt class="file docutils literal"><span class="pre">migrate.cfg</span></tt> files. The initial configuration is performed by the
<cite>migrate create</cite> call explained in <a class="reference internal" href="#create-change-repository"><em>Create a change repository</em></a>. The following options are available currently:</p>
<ul>
<li><p class="first"><em class="xref std std-option">repository_id</em> Used to identify which repository this database is
versioned under. You can use the name of your project.</p>
</li>
<li><p class="first"><em class="xref std std-option">version_table</em> The name of the database table used to track the
schema version. This name shouldn’t already be used by your project. If this
is changed once a database is under version control, you’ll need to change
the table name in each database too.</p>
</li>
<li><p class="first"><em class="xref std std-option">required_dbs</em> When committing a change script, SQLAlchemy-migrate
will attempt to generate the sql for all supported databases; normally, if
one of them fails - probably because you don’t have that database installed -
it is ignored and the commit continues, perhaps ending successfully.
Databases in this list MUST compile successfully during a commit, or the
entire commit will fail. List the databases your application will actually be
using to ensure your updates to that database work properly. This must be a
list; example: <cite>[‘postgres’, ‘sqlite’]</cite></p>
</li>
<li><p class="first"><em class="xref std std-option">use_timestamp_numbering</em> When creating new change scripts, Migrate
will stamp the new script with a version number. By default this is
latest_version + 1. You can set this to ‘true’ to tell Migrate to use the UTC
timestamp instead.</p>
<div class="versionadded">
<p><span class="versionmodified">New in version 0.7.2.</span></p>
</div>
</li>
</ul>
</div>
<div class="section" id="customize-templates">
<span id="custom-templates"></span><h2>Customize templates<a class="headerlink" href="#customize-templates" title="Permalink to this headline">¶</a></h2>
<p>Users can pass <tt class="docutils literal"><span class="pre">templates_path</span></tt> to API functions to provide customized
templates path. Path should be a collection of templates, like
<tt class="docutils literal"><span class="pre">migrate.versioning.templates</span></tt> package directory.</p>
<p>One may also want to specify custom themes. API functions accept
<tt class="docutils literal"><span class="pre">templates_theme</span></tt> for this purpose (which defaults to <cite>default</cite>)</p>
<p>Example:</p>
<div class="highlight-console"><div class="highlight"><pre><span class="go">/home/user/templates/manage $ ls</span>
<span class="go">default.py_tmpl</span>
<span class="go">pylons.py_tmpl</span>
<span class="go">/home/user/templates/manage $ migrate manage manage.py --templates_path=/home/user/templates --templates_theme=pylons</span>
</pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified">New in version 0.6.0.</span></p>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<h3><a href="index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Database schema versioning workflow</a><ul>
<li><a class="reference internal" href="#project-setup">Project setup</a><ul>
<li><a class="reference internal" href="#create-a-change-repository">Create a change repository</a></li>
<li><a class="reference internal" href="#version-control-a-database">Version control a database</a></li>
<li><a class="reference internal" href="#project-management-script">Project management script</a></li>
</ul>
</li>
<li><a class="reference internal" href="#making-schema-changes">Making schema changes</a><ul>
<li><a class="reference internal" href="#create-a-change-script">Create a change script</a></li>
<li><a class="reference internal" href="#edit-the-change-script">Edit the change script</a></li>
<li><a class="reference internal" href="#test-the-change-script">Test the change script</a></li>
<li><a class="reference internal" href="#upgrade-the-database">Upgrade the database</a></li>
<li><a class="reference internal" href="#modifying-existing-tables">Modifying existing tables</a></li>
</ul>
</li>
<li><a class="reference internal" href="#writing-change-scripts">Writing change scripts</a><ul>
<li><a class="reference internal" href="#writing-scripts-with-consistent-behavior">Writing scripts with consistent behavior</a></li>
<li><a class="reference internal" href="#writing-for-a-specific-database">Writing for a specific database</a></li>
<li><a class="reference internal" href="#writings-sql-scripts">Writings .sql scripts</a></li>
</ul>
</li>
<li><a class="reference internal" href="#command-line-usage">Command line usage</a></li>
<li><a class="reference internal" href="#python-api">Python API</a></li>
<li><a class="reference internal" href="#experimental-commands">Experimental commands</a></li>
<li><a class="reference internal" href="#id3">Repository configuration</a></li>
<li><a class="reference internal" href="#customize-templates">Customize templates</a></li>
</ul>
</li>
</ul>
<h4>Previous topic</h4>
<p class="topless"><a href="credits.html"
title="previous chapter">Credits</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="changeset.html"
title="next chapter">Database schema migrations</a></p>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="_sources/versioning.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<h3>Quick search</h3>
<form class="search" action="search.html" method="get">
<input type="text" name="q" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="changeset.html" title="Database schema migrations"
>next</a> |</li>
<li class="right" >
<a href="credits.html" title="Credits"
>previous</a> |</li>
<li><a href="index.html">SQLAlchemy Migrate 0.7.3.dev documentation</a> »</li>
</ul>
</div>
<div class="footer">
© Copyright 2011, Evan Rosson, Jan Dittberner, Domen Kožar, Chris Withers.
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.2.
</div>
</body>
</html>
|