en-US/about_Rivet_Migrations.help.txt

TOPIC
    about_Rivet_migrations
     
SHORT DESCRIPTION
    Explains how to write Rivet migrations.
     
LONG DESCRIPTION
    ## Variables
       
    You can use the following special variables in your `Push-Migration` and
    `Pop-Migration` functions:
     
     * `$DBScriptRoot`: the path to the script root directory of the database you're
       migrating.
     
    ## Tables
       
    The following functions are available for managing tables:
      
     * Add-Table
     * Remove-Table
         
    The `Add-(DataType)Column`'s `Column` parameter is a script block that should
    return columns as column objects, e.g.,
     
        Add-Table Customer {
            Int 'ID' -Identity
            NVarChar 'Name' -NotNull
            Int 'ZipCode' -NotNull
        }
     
    ## Columns
     
     * Update-Table
     * Remove-Column
     
    ## Code Objects
       
    The following functions will remove objects of the specified type. Use the
    `IfExists` flag to only delete the object if it exists.
     
     * Remove-StoredProcedure
     * Remove-UserDefinedFunction
     * Remove-View
     
    ## Executing Code Object Scripts
     
    Stored procedures, user-defined functions, views and other database objects are
    usually stored in external script files, which are executed against the database
    to create/update the object. In some cases, it can take a long time to run all
    your code object scripts, so it can be useful to included updated/new scripts in a
    migration. Rivet assumes scripts for object types are stored under
    `$DBScriptRoot` in directories with the following names:
     
     * Stored Procedures
     * User-Defined Functions
     * Views
     
    Under these directories, scripts should be stored per-object in files named after
    the object. For example, if you have stored procedure `InsertIntoFoo`, it should
    be saved in `$DBScriptRoot\Stored Procedures\InsertIntoFoo.sql`. If your script
    is in a schema other than `dbo`, the file's name should be prefixed with the
    schema. For example, if your stored procedure `InsertIntoFoo` is in the `bar`
    schema, it should be saved in `$DBScriptRoot\Stored
    Procedures\bar.InsertIntoFoo.sql`.
     
    Use these functions to run the script for a code object:
     
        Set-StoredProcedure -Name <string> [-Schema <string>]
         
        Set-UserDefinedFunction -Name <string> [-Schema <string>]
         
        Set-View -Name <string> [-Schema <string>]
     
    To execute an arbitrary SQL script, use `Invoke-SqlScript`:
     
        Invoke-SqlScript -Path <string>
     
    If the `Path` argument is a relative path, the full path to the SQL script is
    resolved from the directory of the migration script. For example, if your
    database's migrations directory is
    `C:\Projects\Rivet\Databases\RivetTest\Migrations`, this path:
     
        Invoke-SqlScript -Path ..\Miscellaneous\CreateDesertedIsland.sql
     
    would resolve to
    `C:\Projects\Rivet\Databases\RivetTest\Miscellaneous\CreatedDesertedIsland.sql`.
     
    ## Raw SQL
     
    When none of the functions above will do the trick, use the `Invoke-Query`
    function to execute raw SQL:
     
        Invoke-Query -Query <string>
     
    You can use a PowerShell here string for longer queries and so you don't have to
    escape quotes:
     
        Invoke-Query -Query @'
            -- SQL goes here. You don't have to escape single quotes.
        '@ # '@ must be the first two characters on the line to close the string.
         
SEE ALSO
    Add-Description
    Add-Table
    Invoke-Query
    Invoke-SqlScript
    New-Column
    Remove-Column
    Remove-Description
    Remove-StoredProcedure
    Remove-Table
    Remove-UserDefinedFunction
    Remove-View
    Set-StoredProcedure
    Set-UserDefinedFunction
    Set-View
    Update-Description
    Update-Table