Ansible
PS DSC
Downloads:
1,026
Downloads of v 0.8.1:
905
Last Update:
26 Nov 2016
Package Maintainer(s):
Software Author(s):
- Aaron Jensen
Tags:
sql-server evolutionary-database database migrations- Software Specific:
- Software Site
- Software License
- Package Specific:
- Package outdated?
- Package broken?
- Contact Maintainers
- Contact Site Admins
- Software Vendor?
- Report Abuse
- Download
Rivet
- 1
- 2
- 3
0.8.1 | Updated: 26 Nov 2016
- Software Specific:
- Software Site
- Software License
- Package Specific:
- Package outdated?
- Package broken?
- Contact Maintainers
- Contact Site Admins
- Software Vendor?
- Report Abuse
- Download
Downloads:
1,026
Downloads of v 0.8.1:
905
Maintainer(s):
Software Author(s):
- Aaron Jensen
Rivet 0.8.1
Legal Disclaimer: Neither this package nor Chocolatey Software, Inc. are affiliated with or endorsed by Aaron Jensen. The inclusion of Aaron Jensen trademark(s), if any, upon this webpage is solely to identify Aaron Jensen goods or services and not for commercial purposes.
- 1
- 2
- 3
All Checks are Passing
3 Passing Tests
Deployment Method: Individual Install, Upgrade, & Uninstall
To install Rivet, run the following command from the command line or from PowerShell:
>
To upgrade Rivet, run the following command from the command line or from PowerShell:
>
To uninstall Rivet, run the following command from the command line or from PowerShell:
>
Deployment Method:
NOTE
This applies to both open source and commercial editions of Chocolatey.
1. Enter Your Internal Repository Url
(this should look similar to https://community.chocolatey.org/api/v2/)
2. Setup Your Environment
1. Ensure you are set for organizational deployment
Please see the organizational deployment guide
2. Get the package into your environment
Option 1: Cached Package (Unreliable, Requires Internet - Same As Community)-
Open Source or Commercial:
- Proxy Repository - Create a proxy nuget repository on Nexus, Artifactory Pro, or a proxy Chocolatey repository on ProGet. Point your upstream to https://community.chocolatey.org/api/v2/. Packages cache on first access automatically. Make sure your choco clients are using your proxy repository as a source and NOT the default community repository. See source command for more information.
- You can also just download the package and push it to a repository Download
-
Open Source
-
Download the package:
Download - Follow manual internalization instructions
-
-
Package Internalizer (C4B)
-
Run: (additional options)
choco download rivet --internalize --source=https://community.chocolatey.org/api/v2/ -
For package and dependencies run:
choco push --source="'INTERNAL REPO URL'" - Automate package internalization
-
Run: (additional options)
3. Copy Your Script
choco upgrade rivet -y --source="'INTERNAL REPO URL'" [other options]See options you can pass to upgrade.
See best practices for scripting.
Add this to a PowerShell script or use a Batch script with tools and in places where you are calling directly to Chocolatey. If you are integrating, keep in mind enhanced exit codes.
If you do use a PowerShell script, use the following to ensure bad exit codes are shown as failures:
choco upgrade rivet -y --source="'INTERNAL REPO URL'"
$exitCode = $LASTEXITCODE
Write-Verbose "Exit code was $exitCode"
$validExitCodes = @(0, 1605, 1614, 1641, 3010)
if ($validExitCodes -contains $exitCode) {
Exit 0
}
Exit $exitCode
- name: Install rivet
win_chocolatey:
name: rivet
version: '0.8.1'
source: INTERNAL REPO URL
state: present
See docs at https://docs.ansible.com/ansible/latest/modules/win_chocolatey_module.html.
chocolatey_package 'rivet' do
action :install
source 'INTERNAL REPO URL'
version '0.8.1'
end
See docs at https://docs.chef.io/resource_chocolatey_package.html.
cChocoPackageInstaller rivet
{
Name = "rivet"
Version = "0.8.1"
Source = "INTERNAL REPO URL"
}
Requires cChoco DSC Resource. See docs at https://github.com/chocolatey/cChoco.
package { 'rivet':
ensure => '0.8.1',
provider => 'chocolatey',
source => 'INTERNAL REPO URL',
}
Requires Puppet Chocolatey Provider module. See docs at https://forge.puppet.com/puppetlabs/chocolatey.
4. If applicable - Chocolatey configuration/installation
See infrastructure management matrix for Chocolatey configuration elements and examples.
Package Approved
This package was approved by moderator gep13 on 26 Nov 2016.
Description
Rivet is a database migration/change management/versioning tool inspired by Ruby on Rails' Migrations. It creates and applies migration scripts for SQL Server databases. Migration scripts describe changes to make to your database, e.g. add a table, add a column, remove an index, etc. Migrations scripts should get added to your version control system so they can be packaged and deployed with your application's code.
Files
LICENSE
NOTICE
RELEASE_NOTES.txt
# 0.8.1 (26 November 2016)
* Removing a custom operation that isn't part of core Rivet.
# 0.8.0
## Enhancements
* Created `Merge-Migration` function for creating cumulative, roll up migrations.
# 0.7.0
## Enhancements
* Fixed: `Add-Index` operation times out when creating new indexes on large table. Added a `Timeout` parameter to control how long to wait for an operation to finish.
* `Add-Index` re-implemented as a C# cmdlet.
# 0.6.1
## Bug Fixes
* `Rename-Column`, `Rename-DataType`, `Rename-Index`, and `Rename-Object` operations didn't properly quote schema and object names.
# 0.6.0
## Enhancements
* Improving verbose output to be more recognizable and include query timings.
* `Convert-Migration.ps1` extra script now puts triggers, constraints, foreign keys, and types into separate files.
* `New-Migration` now increments timestamp if a migration with the same timestamp already exists instead of sleeping for half a second.
* Added format for `Rivet.Migration` objects so they display nicely when running migrations.
* Adding Rivet about help topics.
* Created `Add-RowGuidCol` operation for adding the `rowguidcol` property to a column.
* Created `Remove-RowGuidCol` operation for removing the `rowguidcol` property from a column.
* Created `Stop-Migration` operation for preventing a migration from getting popped/reversed.
* Migrations missing Push-Migration/Pop-Migration functions are no longer allowed and will fail when pushed/popped.
* Migrations with empty Push-Migration/Pop-Migration functions are no longer allowed and will fail when pushed/popped.
* Obsoleted the parameter sets of the `Remove-CheckConstraint`, `Remove-DefaulConstraint`, `Remove-ForeignKey`, `Remove-Index`, `Remove-PrimaryKey`, and `Remove-UniqueKey` operations that use an inferred constraint/index name. These operations now expect the name of the constraint/index to drop with the `Name` parameter.
* Improved object model so that customizing index/constraint names is easier.
* Added `about_Rivet_Cookbook` help topic to showing how to customize index/constraint names.
* Updated and improved the `about_Rivet_Plugins` help topic.
* Obsoleted the `Enable-ForeignKey` and `Disable-ForeignKey` operations. Use the `Enable-Constraint` and `Disable-Constraint` operations instead.
* Renamed the `Enable-CheckConstraint` operation to `Enable-Constraint`, with a backwards-compatible alias.
* Renamed the `Disable-CheckConstraint` operation to `Disable-Constraint`, with a backwards-compatible alias.
* You can now push, pop, or create multiple migrations at once (i.e. `rivet.ps1`'s `Name` parameter now accepts multiple names, IDs, or file names).
* Plug-ins now get passed a `Rivet.Migration` object for the operation being processed.
* Rivet now supports writing custom operations.
## Bug Fixes
* Results from `Invoke-SqlScript` operations cause silent error when formatted as a table.
* Path to rivet.json file not showing in an error message when using implicit path.
# 0.5.1
## Enhancements
* Improving `WhatIf` support: some actions that shouldn't be conditional now ignore `WhatIf` flag.
* Invoke-SqlScript operation no longer splits script into batches, since that is now handled internally when executing all operations.
* Improving verbose output: adding a message for each already-applied migration.
## Bug Fixes
* Get-Migration fails when run from Convert-Migration: it doesn't know the path to use to load migrations from.
# 0.5.0
## Enhancements
* The Add-Schema operation is now idempotent.
* Removed all Write-Host output.
* Rivet now returns OperationResult objects for each query executed by an operation. Default format included (i.e. this output replaces the old Write-Host output).
* Renamed `Invoke-Query` operation to `Invoke-Ddl`.
* Renamed `Rivet.Operations.RawQueryOperation` to `Rivet.Operations.RawDdlOperation`.
* Moved `Rivet.Operations.Operation` object into `Rivet` namespace; so full type name is now `Rivet.Operation`.
# 0.4.0
## Enhancements
* NOCHECK parameter has been added to `Add-ForeignKey` and `Add-CheckConstraint` operations
* `Disable-CheckConstraint` and `Enable-CheckConstraint` functions have been added.
* `Disable-ForeignKey` and `Enable-ForeignKey` functions have been added.
## Bug Fixes
* Convert-Migration.ps1 generates incorrect SQL if a migration removes then re-adds a column.
# 0.3.3
* Improved error message when failing to connect to SQL Server.
* `Add-Index` operation now supports INCLUDE clause.
# 0.3.2
## Bug Fixes
* `Invoke-SqlScript` fails when `NonQuery` switch is used.
# 0.3.1
## Enhancements
* `Get-RivetConfig` is now a publicly exposed function. Use this method to parse a Rivet JSON configuration file. It returns a `Rivet.Configuration.Configuration` object.
# 0.3.0
## Enhancements
* `Get-Migration` now returns a `Rivet.Operations.ScriptFileOperation` object instead of a `Rivet.Operations.RawQueryOperation` for `Invoke-SqlQuery` operations.
## Bug Fixes
* `Invoke-SqlScript` ignoring `CommandTimeout` parameter.
* `Invoke-SqlScript` didn't rollback migration if the script file was not found.
* `Get-Migration` fails if a migration doesn't contain a `Push-Migration` or `Pop-Migration` function.
* `Get-Migratoin` duplicates output of previous migration if a migration is missing a `Push-Migration` or `Pop-Migration` function.
# 0.2.1
## Bug Fixes
* If a database has multipe target databases and no migrations directory, Rivet stops after the first target database.
# 0.2.0
* Databases are now created if they don't exist.
* A single database connection is now re-used when migrating multiple databases, instead of establishing a new connection for each database.
* A database's migrations can now be applied to multiple target databases via the new `TargetDatabases` configuration option. See `about_Rivet_Configuration` for more information.
* Rivet now updates its internal objects using migrations (i.e. it is now self-migrating). It uses (and reserves) migration IDs below 01000000000000. If you have migrations with these IDs, you'll need to give them new IDs and update IDs in any rivet.Migrations table that uses that ID.
* Migration name maximum length increased to 241 characters (the theoretical maximum allowed by Windows).
Rivet\bin\Rivet.dll
md5: C81A0D1C22950BDEA3214E713B02B98E | sha1: F2FFA7FBB3A89FF92F02F48E9F439266137226F6 | sha256: 52B03A67A09992C581CDEBA245DABF03974838FD1D8F850C5E3DA146A0EC7103 | sha512: 5FD788D3356FFBF3506CF45D2392E2B52914B20C3166B7A4BE1F22381C876B00B7FEF2C7F83A029944D3451637CBE354A5EA4DD2230EE415BD15507FAB0EC9B0
Rivet\en-US\about_Rivet.help.txt
TOPIC
about_Rivet
SHORT DESCRIPTION
Rivet is a database migration/change management/versioning tool inspired by
Ruby on Rails' Migrations. It creates and applies migration scripts for SQL
Server databases. Migration scripts describe changes to make to your
database, e.g. add a table, add a column, remove an index, etc. Migrations
scripts should get added to your version control system so they can be
packaged and deployed with your application's code.
Every Rivet migration is really a PowerShell script with a special name and
that contains two migration functions. The migration filename should have
the format `<timestamp>_<description>.ps1`. Timestamp is a unique number
which increases every time a new migration is created. By default, Rivet
uses a timestamp with second precision. `Description` is a short description
of what the migration is doing.
There should be two functions inside a migration, `Push-Migration` and
`Pop-Migration`. The `Push-Migration` function should make changes to the
database. The `Pop-Migration` function should reverse those changes, so you
can put the database back in the state it was in before it was migrated.
Once released, migrations are immutable and should not be changed. Rivet
keeps track of which migrations have been applied against a database. If you
change a migration once it has been applied to a database, it won't get
re-applied because Rivet has already recorded that it was applied. Create a
new migration to make the new change.
All changes made in either `Push-Migration` or `Pop-Migration` are wrapped in
a transaction. If any change fails to get made/applied, all changes are
rolled back.
SEE ALSO
about_Rivet_Configuration
about_Rivet_Intellisense
about_Rivet_Migrations
about_Rivet_Plugins
Rivet\en-US\about_Rivet_Configuration.help.txt
TOPIC
about_Rivet_Configuration
SHORT DESCRIPTION
Explains the Rivet configuration file.
LONG DESCRIPTION
## Overview
Rivet pulls the many settings it needs from a JSON configuration file called
`rivet.json`. By default, Rivet will look in the current directory for the
`rivet.json` file. The `rivet.ps1` script allows you to pass in the path to your
own configuration file.
The `rivet.json` file is written in JSON, because I don't like XML. Make sure all
`\` (i.e. backspace) characters get escaped (e.g. `\\`). Comments are not allowed.
All non-absolute paths in the `rivet.json` file are resolved as relative to the
file itself. For example, if a setting's path is `Databases`, and the
`rivet.json` file is in the `C:\Projects\Rivet\Test` directory, the setting's
value will be resolved to `C:\Projects\Rivet\Test\Databases`.
## Environments
You will most likely have many environments where Rivet will run. At a minimum,
each environment will have a different SQL Server instance. To support multiple
environments, the `rivet.json` file uses a special `Environments` setting, which
is a hash of environments, where the key is the environment's name and the value
is another hash of settings for that environment. These environment settings
override the base/default settings. If an environment doesn't specify a setting,
the base/default setting is used.
{
SqlServerName: '.\Rivet',
DatabasesRoot: 'Databases',
Environments:
{
Production:
{
SqlServerName: 'proddb\Rivet',
}
}
}
In this example, we've defined a `Production` environment which overrides the
`SqlServerName` setting.
## Settings
### CommandTimeout
The amount of time, in seconds, to wait for a command to complete. The default is
30 seconds.
### ConnectionTimeout
The amount of time, in seconds, to wait for a database connection to open. The
default is 15 seconds.
### DatabasesRoot
Rivet assumes a database's migration scripts are stored together. in
`$DatabasesRoot\$DatabaseName\Migrations`. So, `$DatabasesRoot` should point to
the directory which contains the directories for each database. For example,
given this directory structure:
* rivet.json
+ Databases
+ Rivet
+ Migrations
+ RivetTest
+ Migrations
You can see directories for the `Rivet` and `RivetTest` databases under the
`Databases` directory. So, you'll set the `DatabasesRoot` setting to `Databases`.
Rivet assumes there is a one-to-one mapping between the directories under
`DatabasesRoot` and a database on the SQL Server. If this is not the case, and
you'd like to exclude/ignore a directory under `DatabasesRoot`, use the
`IgnoreDatabases` setting.
### Environments
A hash of environments, where they key is the environment's name, and the value is
another hash of settings for that environment. These environment settings override
the base/default settings. If an environment doesn't specify a setting, the
base/default setting is used.
### IgnoreDatabases
A list of database names to ignore/exclude from the Rivet. This is useful if you
have a directory under `DatabasesRoot` that doesn't contain a database's
scripts. Wildcards are allowed, e.g. `Shared*` would exclude all directories
under `DatabasesRoot` that begin with the word `Shared`.
### PluginsRoot
This should point to the directory which contains the directory for which plugins
should be stored. For example, given this directory structure:
* rivet.json
+Databases
+Plugins
PluginsRoot should point to the "Plugins" directory
### SqlServerName
The name of the SQL Server to connect to.
### TargetDatabases
This setting maps database names to lists of target databases. Use this setting if
you need to deploy the same migrations to multiple databases, but don't want to
duplicate the migration. Should be a hashtable whose key is the name of the
database's scripts directory on the file system, and whose value is an array of
target database names those scripts should be applied to, e.g.
{
"TargetDatabases": {
"Database1": [ "Database1", "DAtabase2" ]
}
}
In the above example, scripts in the `Database1` directory will be applied to the
`Database1` *and* `Database2` databases.
## Examples
### Example 1
{
SqlServerName: '.\\Rivet',
DatabasesRoot: 'Databases'
}
This example demonstrates the simplest configuration file. This configuration file
will cause Rivet to connect to the `.\Rivet` SQL Server, and load database scripts
from the `Databases` directory where the `rivet.json` file is located.
### Example 2
{
SqlServerName: '.\Rivet',
DatabasesRoot: 'Databases',
ConnectionTimeout: 5,
CommandTimeout: 300,
IgnoreDatabases: [ 'Shared' ]
}
This example demonstrates how to use all the configuration options. This
configuration file will:
* connect to the local `.\Rivet` SQL Server instance
* load database scripts from the `Databases` directory (which would be in the
same directory as the `rivet.json` file)
* shorten the connection timeout to 5 seconds
* increase the command timeout to 5 minutes
* not add the `Shared` database to the list of databases to manage (i.e. it will
ignore the `$Databases\Shared` directory)
### Example 3
{
SqlServerName: '.\Rivet',
DatabasesRoot: 'Databases',
Environments:
{
UAT:
{
SqlServerName: 'uatdb\Rivet',
IgnoreDatabases: [ 'Preview' ],
CommandTimeout: 300
},
Production:
{
SqlServerName: 'proddb\Rivet',
IgnoreDatabases: [ 'Preview' ],
CommandTimeout: 600
}
}
}
This example demonstrates how to create and use environment-specific settings.
# See Also
rivet.ps1
Rivet\en-US\about_Rivet_Cookbook.help.txt
TOPIC
about_Rivet_Cookbooks
SHORT DESCRIPTION
Contains a bunch of recipes showing how to use Rivet.
LONG DESCRIPTION
## How do I create my own operations?
It is possible to create Rivet operations. Each operation must be in its own file.
The name of the file must match the name of the operation, with a .ps1 extension.
Put your custom operations in the `Operations` directory under the Rivet module.
So, if you want to create a custom `Add-StandardTable` operation, you would create
an `Operations\Add-StandardTable.ps1` file that looked like this:
function Add-StandardTable
{
}
Operations take no parameters. They should return one or more `Rivet.Operation`
objects. See the `Rivet.Operations` namespace for available operations.
## How do I customize constraint/index names?
You can customize constraint/index names using the `Start-MigrationOperation`
plug-in to modify a constraint's name before it gets applied to a database. (See
`about_Rivet_Plugins` for instructions on setting up your plug-ins.)
Each operation that adds/removes a constraint has a `SetConstraintName(string)`
method. You can check what kind of constraint is being added/removed with the
`ConstraintType` property. You can check if an operation is a constraint operation
by testing if it is a `Rivert.Operations.ConstraintOperation`. Here's an example:
The add/remove index operation has a 'SetIndexName(string)' method. You can check
if an operation is an index operation by testing if it is a
`Rivet.Operations.IndexOperation` object.
Here's some sample code demonstrating all this:
function Start-MigrationOperation
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration the operation is part of.
$Migration,
[Parameter(Mandatory=$true)]
[Rivet.Operation]
# The operation which is about to be applied.
$Operation
)
Set-StrictMode -Version 'Latest'
if( $Operation -is [Rivet.Operations.ConstraintOperation] )
{
switch( $Operation.ConstraintType )
{
[Rivet.ConstraintType]::Default
{
$Operation.Name = 'DF_{0}_{1}_{2}' -f $Operation.SchemaName,$Operation.TableName,$Operation.ColumnName
}
[Rivet.ConstraintType]::PrimaryKey
{
$Operation.Name = 'PK_{0}_{1}' -f $Operation.SchemaName,$Operation.TableName
}
[Rivet.ConstraintType]::ForeignKey
{
$Operation.Name = 'FK_{0}_{1}_{2}_{3}' -f $Operation.SchemaName,$Operation.TableName,$Operation.ReferencesSchemaName,$Operation.ReferencesTableName
}
[Rivet.ConstraintType]::Check
{
$Operation.Name = 'CK_{0}_{1}_{2}', $Operation.SchemaName,$Operation.TableName,($Operation.Expression -replace '[A-Za-z0-9]','')
}
[Rivet.ConstraintType]::UniqueKey
{
$Operation.Name = 'UK_{0}_{1}_{2}' -f$Operation.SchemaName,$Operation.TableName,($Operation.ColumnName -join '_')
}
default
{
throw ('Rivet has added a new constraint type that we don''t handle. Please update {0} to handle this new constraint type.' -f $PSCommandPath)
}
}
}
elseif( $Operation -is [Rivet.Operation.IndexOperation] )
{
$prefix = 'IX'
if( $Operation.Unique )
{
$prefix = 'UIX'
}
$Operation.Name = '{0}_{1}_{2}_{3}' -f $prefix,$Operation.SchemaName,$Operation.TableName,($Operation.ColumnName -join '_')
}
}
SEE ALSO
about_Rivet_Plugins
Rivet\en-US\about_Rivet_Intellisense.help.txt
TOPIC
about_Rivet_Intellisense
SHORT DESCRIPTION
Explains how to get Intellisense when writing Rivet migrations.
LONG DESCRIPTION
In order to get Intellisense when writing migrations, you'll need to have PowerShell
***3*** installed, and use the PowerShell Integrated Scripting Environment (i.e.
ISE). You should be able to open a migration in the ISE by right-clicking it and
choosing "Edit".
Once you've got a migration open in the ISE, you'll want to import the Rivet module.
Use the `Import-Rivet.ps1` script:
PS> Import-Rivet.ps1
Make sure you use the path to Rivet in your environment.
Once you've imported Rivet, you can get a list of available migrations by running
this command:
PS> Get-Command -Module Rivet -CommandType Function
Rivet\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
Rivet\en-US\about_Rivet_Plugins.help.txt
TOPIC
about_Rivet_Plugins
SHORT DESCRIPTION
Explains the Rivet plug-in system
LONG DESCRIPTION
Rivet plug-ins allow users to modify migration operations or perform other work
before and after an operation is applied to a database. Examples include:
* Validating that all tables/columns have descriptions.
* Automatically adding created and last updated columns to each new table.
* Validating that any raw SQL doesn't include a `USE` statement.
* Preventing certain operations from being used, e.g. `Rename-Column`.
* Customizing constraint and index names.
There are two plug-in points: before an operation is applied to a database and
after an operation is applied to a database.
In order to cancel a migration, a plug-in *must throw an exception*. For example,
throw ('Operation is invalid: all tables are required to specify a description. Use the -Description parameter to document this table''s purpose.')
To enable plug-ins, you need to configure Rivet so it knows where to look for
them. Set the `plug-insRoot` option to the directory path where you want to put
your plug-ins. For example,
{
"SqlServerName": "example.com\Rivet",
"DatabasesRoot": "Databases",
"PluginsRoot": "Tools\\Rivet\\Plugins"
}
Paths in rivet.json files are relative to the rivet.json file. See
`about_Rivet_Configuration` for more information.
Once you've defined your plug-ins directory, you create the plug-in scripts. To
run code *before* an operation is applied to database, create a
`Start-MigrationOperation.ps1` script in your plug-ins directory. It should look
like this:
function Start-MigrationOperation
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration the operation is part of.
$Migration,
[Parameter(Mandatory=$true)]
[Rivet.Operation]
# The operation which is about to be applied.
$Operation
)
Set-StrictMode -Version 'Latest'
# Your plug-in logic goes here.
}
To run code *after* an operation is applied to a database, create a
`Complete-MigrationOperation.ps1` script in your plug-ins directory. It should
look like this:
function Complete-MigrationOperation
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration the operation is part of.
$Migration,
[Parameter(Mandatory=$true)]
[Rivet.Operation]
# The operation which was just applied.
$Operation
)
Set-StrictMode -Version 'Latest'
# Your plug-in logic goes here.
}
Rivet ships with some sample plug-ins. Look in the `Extras` directory.
Each plug-in should have two parameters: the first is a `Rivet.Migration`
object representing the migration getting run. The second is a
`Rivet.Operation` object representing the specific operation getting run. Each
plug-in will get called for each operation in a migration. You'll get the same
migration object for each operation in that migration.
Each operation in Rivet is represented by a unique C# object that you
can modify. You can't remove operations from being run. Instead, throw an
exception to reject the operation. You are allowed to run additional operations
and/or return additional C# operation objects. See the sample plug-ins in the
Extras directory that ships with Rivet.
SEE ALSO
about_Rivet
about_Rivet_Configuration
about_Rivet_Intellisense
about_Rivet_Migrations
Rivet\en-US\Rivet.dll-Help.xml
<?xml version="1.0" encoding="utf-8"?>
<helpItems schema="maml">
<command:command xmlns:maml="http://schemas.microsoft.com/maml/2004/10" xmlns:command="http://schemas.microsoft.com/maml/dev/command/2004/10" xmlns:dev="http://schemas.microsoft.com/maml/dev/2004/10" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
<command:details><command:name>Add-Index</command:name>
<command:verb>Add</command:verb>
<command:noun>Index</command:noun>
<maml:description><maml:para>Creates a relational index on a specified table.
</maml:para>
</maml:description>
</command:details>
<maml:description><maml:para>Creates a relational index on a specified table. An index can be created before there is data on the table. Relational indexes can be created on tables or views in another database by specifying a qualified database name.
</maml:para>
</maml:description>
<command:syntax><command:syntaxItem><maml:name>Add-Index</maml:name>
<command:parameter required="true" variableLength="true" globbing="false" pipelineInput="False" position="0" aliases="none"><maml:name>TableName</maml:name>
<maml:Description><maml:para>The name of the target table.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Clustered</maml:name>
<maml:Description><maml:para>Creates a clustered index, otherwise non-clustered
</maml:para>
</maml:Description>
<dev:type><maml:name>SwitchParameter</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue>False</dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>ColumnName</maml:name>
<maml:Description><maml:para>The column(s) on which the index is based.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Descending</maml:name>
<maml:Description><maml:para>Optional array of booleans to specify descending switch per column. Length must match `ColumnName`.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">Boolean[]</command:parameterValue>
<dev:type><maml:name>Boolean[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>FileStreamOn</maml:name>
<maml:Description><maml:para>The value of the `FILESTREAM_ON` clause, which controls the placement of filestream data.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Include</maml:name>
<maml:Description><maml:para>Column names to include in the index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Name</maml:name>
<maml:Description><maml:para>The name for the <object type>. If not given, a sensible name will be created.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>On</maml:name>
<maml:Description><maml:para>The value of the `ON` clause, which controls the filegroup/partition to use for the index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Option</maml:name>
<maml:Description><maml:para>An array of index options.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>SchemaName</maml:name>
<maml:Description><maml:para>The schema name of the target table. Defaults to `dbo`.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Timeout</maml:name>
<maml:Description><maml:para>The number of seconds to wait for the add operation to complete. Default is 30 seconds.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">Int32</command:parameterValue>
<dev:type><maml:name>Int32</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Unique</maml:name>
<maml:Description><maml:para>Create a unique index on a table or view.
</maml:para>
</maml:Description>
<dev:type><maml:name>SwitchParameter</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue>False</dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Where</maml:name>
<maml:Description><maml:para>The filter to use when creating a filtered index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
</command:syntaxItem>
</command:syntax>
<command:parameters><command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Clustered</maml:name>
<maml:Description><maml:para>Creates a clustered index, otherwise non-clustered
</maml:para>
</maml:Description>
<command:parameterValue required="false" variableLength="false">SwitchParameter</command:parameterValue>
<dev:type><maml:name>SwitchParameter</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue>False</dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>ColumnName</maml:name>
<maml:Description><maml:para>The column(s) on which the index is based.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Descending</maml:name>
<maml:Description><maml:para>Optional array of booleans to specify descending switch per column. Length must match `ColumnName`.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">Boolean[]</command:parameterValue>
<dev:type><maml:name>Boolean[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>FileStreamOn</maml:name>
<maml:Description><maml:para>The value of the `FILESTREAM_ON` clause, which controls the placement of filestream data.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Include</maml:name>
<maml:Description><maml:para>Column names to include in the index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Name</maml:name>
<maml:Description><maml:para>The name for the <object type>. If not given, a sensible name will be created.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>On</maml:name>
<maml:Description><maml:para>The value of the `ON` clause, which controls the filegroup/partition to use for the index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Option</maml:name>
<maml:Description><maml:para>An array of index options.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String[]</command:parameterValue>
<dev:type><maml:name>String[]</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>SchemaName</maml:name>
<maml:Description><maml:para>The schema name of the target table. Defaults to `dbo`.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="true" variableLength="true" globbing="false" pipelineInput="False" position="0" aliases="none"><maml:name>TableName</maml:name>
<maml:Description><maml:para>The name of the target table.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Timeout</maml:name>
<maml:Description><maml:para>The number of seconds to wait for the add operation to complete. Default is 30 seconds.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">Int32</command:parameterValue>
<dev:type><maml:name>Int32</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Unique</maml:name>
<maml:Description><maml:para>Create a unique index on a table or view.
</maml:para>
</maml:Description>
<command:parameterValue required="false" variableLength="false">SwitchParameter</command:parameterValue>
<dev:type><maml:name>SwitchParameter</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue>False</dev:defaultValue>
</command:parameter>
<command:parameter required="false" variableLength="true" globbing="false" pipelineInput="False" position="named" aliases="none"><maml:name>Where</maml:name>
<maml:Description><maml:para>The filter to use when creating a filtered index.
</maml:para>
</maml:Description>
<command:parameterValue required="true" variableLength="false">String</command:parameterValue>
<dev:type><maml:name>String</maml:name>
<maml:uri /></dev:type>
<dev:defaultValue></dev:defaultValue>
</command:parameter>
</command:parameters>
<command:inputTypes><command:inputType><dev:type><maml:name>None</maml:name>
</dev:type>
<maml:description><maml:para>
</maml:para>
</maml:description>
</command:inputType>
</command:inputTypes>
<command:returnValues><command:returnValue><dev:type><maml:name>Rivet.Operations.AddIndexOperation</maml:name>
</dev:type>
<maml:description><maml:para>
</maml:para>
</maml:description>
</command:returnValue>
</command:returnValues>
<maml:alertSet><maml:alert><maml:para>
</maml:para>
</maml:alert>
</maml:alertSet>
<command:examples><command:example><maml:title>Example 1</maml:title>
<dev:code>Add-Index -TableName Cars -Column Year</dev:code>
<dev:remarks><maml:para>Adds a relational index in 'Year' on the table 'Cars'
</maml:para>
</dev:remarks>
</command:example>
<command:example><maml:title>Example 2</maml:title>
<dev:code>Add-Index -TableName 'Cars' -Column 'Year' -Unique -Option @('IGNORE_DUP_KEY = ON','ALLOW_ROW_LOCKS = OFF')</dev:code>
<dev:remarks><maml:para>Adds an unique relational index in 'Year' on the table 'Cars' with options to ignore duplicate keys and disallow row locks.
</maml:para>
</dev:remarks>
</command:example>
<command:example><maml:title>Example 3</maml:title>
<dev:code>Add-Index -TableName 'Cars' -Column 'Year' -Include 'Model'</dev:code>
<dev:remarks><maml:para>Adds a relational index in 'Year' on the table 'Cars' and includes the column 'Model'
</maml:para>
</dev:remarks>
</command:example>
</command:examples>
<command:relatedLinks></command:relatedLinks>
</command:command>
</helpItems>
Rivet\Extras\Complete-MigrationOperation.ps1
function Complete-MigrationOperation
{
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration the operation is part of.
$Migration,
[Parameter(Mandatory=$true)]
[Rivet.Operation]
# The operation which was just applied.
$Operation
)
Set-StrictMode -Version 'Latest'
if( $Operation -isnot [Rivet.Operations.AddTableOperation] )
{
return
}
$hasRowGuidCol = $Operation.Columns |
Where-Object { $_.DataType -eq [Rivet.DataType]::UniqueIdentifier } |
Where-Object { $_.RowGuidCol } |
Where-Object { $_.Name -eq 'rowguid' }
if( $hasRowGuidCol )
{
Add-Index -ColumnName 'rowguid' -Unique -SchemaName $Operation.SchemaName -TableName $Operation.Name
}
$trigger = @'
ON [{0}].[{1}]
FOR UPDATE
NOT FOR REPLICATION
AS
IF @@ROWCOUNT = 0
RETURN
--<< SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON
--<< Update LastUpdated and SkipBit column on existing record
--<< To bypass the execution of this trigger set SkipBit = 1
IF ( (TRIGGER_NESTLEVEL(@@PROCID) = 1 AND (NOT UPDATE(SkipBit) OR EXISTS(select SkipBit from Inserted where isnull(SkipBit, 0) = 0))) )
BEGIN
UPDATE t1
SET
LastUpdated = GETDATE(),
SkipBit = 0
FROM [{0}].[{1}] t1
INNER JOIN Inserted ON t1.rowguidcol = Inserted.rowguidcol
END
'@ -f $Operation.SchemaName,$Operation.Name
Add-Trigger -SchemaName $Operation.SchemaName -Name ('tr{0}_Activity' -f $Operation.Name) -Definition $trigger
}
Rivet\Extras\Convert-Migration.ps1
<#
.SYNOPSIS
Demonstrates how to use the Rivet object model to convert migrations to standalone SQL scripts.
.DESCRIPTION
Sometimes you can't run your migration scripts directly against a database. In these situations, it is useful to be able to grab the SQL from your migrations and convert them into a different form. This script demonstates how to do that by outputing your migrations into four different files per database: one for schema changes, one for code object changes, one for data, and one for unknown kinds of changes.
.LINK
Merge-Migration
.EXAMPLE
Convert-Migration.ps1 -OutputPath 'F:\BuildOutput\DBScripts'
Demonstrates how to run `Convert-Migration.ps1`.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The directory where the scripts should be output.
$OutputPath,
[Parameter()]
[string]
# The path to the rivet.json file to use. By default, it will look in the current directory.
$ConfigFilePath,
[Parameter()]
[Hashtable]
# Mapping of migration base name (e.g. `20130115142433_CreateTable`) to the person's name who created it.
$Author = @{ },
[string[]]
# A list of migrations to include. Only migrations that match are returned. Wildcards permitted.
$Include,
[string[]]
# Any migrations/files to exclude. Wildcards accepted.
$Exclude,
[DateTime]
# Only get migrations before this date/time.
$Before,
[DateTime]
# Only get migrations after this date/time.
$After
)
Set-StrictMode -Version 'Latest'
& (Join-Path -Path $PSScriptRoot -ChildPath '..\Import-Rivet.ps1' -Resolve)
if( -not (Test-Path -Path $OutputPath -PathType Container) )
{
$null = New-Item -ItemType 'Directory' -Path $OutputPath -Force
}
else
{
Get-ChildItem -Path $OutputPath -File | Remove-Item
}
$getMigrationParams = @{ }
@( 'ConfigFilePath', 'Exclude', 'Include', 'Before', 'After' ) |
Where-Object { $PSBoundParameters.ContainsKey( $_ ) } |
ForEach-Object { $getMigrationParams.$_ = Get-Variable -Name $_ -ValueOnly }
$operations = New-Object 'Collections.ArrayList'
$newTables = New-Object 'Collections.Generic.HashSet[string]'
$opIdx = @{ }
Get-Migration @getMigrationParams |
Merge-Migration |
ForEach-Object {
$migration = $_
$migration.PushOperations |
Where-Object { $_ } |
ForEach-Object {
$op = $_
$schemasScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Schemas.sql' -f $migration.Database)
$schemaScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Schema.sql' -f $migration.Database)
$dependentObjectScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.DependentObject.sql' -f $migration.Database)
$extendedPropertyScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.ExtendedProperty.sql' -f $migration.Database)
$codeObjectScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.CodeObject.sql' -f $migration.Database)
$dataScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Data.sql' -f $migration.Database)
$unknownScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Unknown.sql' -f $migration.Database)
$triggerScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Trigger.sql' -f $migration.Database)
$constraintScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Constraint.sql' -f $migration.Database)
$foreignKeyScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.ForeignKey.sql' -f $migration.Database)
$typeScriptPath = Join-Path -Path $OutputPath -ChildPath ('{0}.Type.sql' -f $migration.Database)
$header = $op.Source | ForEach-Object {
$name = $_.FullName
$by = ''
if( $Author -and $Author.ContainsKey( $name ) )
{
$by = ': {0}' -f $Author[$name]
}
'-- {0}{1}' -f $name,$by
}
$header = $header -join ([Environment]::NewLine)
if( $op -is [Rivet.Operations.AddTableOperation] )
{
$newTables.Add( $op.ObjectName ) | Out-Null
}
$op = $_
$path = switch -Regex ( $op.GetType() )
{
'(Add|Remove|Update)ExtendedProperty'
{
$extendedPropertyScriptPath
break
}
'(Add|Remove|Update)Schema'
{
$schemasScriptPath
break
}
'(Add|Remove|Update)Table'
{
$schemaScriptPath
break
}
'(Add|Remove|Update)Trigger'
{
$triggerScriptPath
break
}
'(Add|Remove|Update)(Index|PrimaryKey|UniqueKey)'
{
$tableName = '{0}.{1}' -f $op.SchemaName,$op.TableName
if( $newTables.Contains( $tableName ) )
{
$schemaScriptPath
}
else
{
$dependentObjectScriptPath
}
break
}
'(Add|Remove)(CheckConstraint|DefaultConstraint)'
{
$constraintScriptPath
break
}
'(Enable|Disable)Constraint'
{
$constraintScriptPath
break
}
'(Add|Remove|Disable|Enable)ForeignKey'
{
$foreignKeyScriptPath
break
}
'(Add|Remove|Update)(DataType|Synonym)'
{
$typeScriptPath
break
}
'Rename(Column|Constraint|Index)?Operation'
{
$schemaScriptPath
}
'(Add|Remove|Update)(CodeObjectMetadata|StoredProcedure|UserDefinedFunction|View)'
{
$codeObjectScriptPath
break
}
'(Add|Remove|Update)Row'
{
$dataScriptPath
break
}
'RawDdl|ScriptFile'
{
Write-Warning ('Generic migration operation found in ''{0}''.' -f $migration.Path)
$unknownScriptPath
break
}
default
{
Write-Error ('Unknown migration operation ''{0}'' in ''{1}''.' -f $op.GetType(),$migration.Path)
return
}
}
if( -not (Test-Path -Path $path -PathType Leaf) )
{
$null = New-Item -Path $path -ItemType 'File' -Force
}
$header | Add-Content -Path $path
$op.ToIdempotentQuery() | Add-Content -Path $path
("GO{0}" -f [Environment]::NewLine) | Add-Content -Path $path
}
}
Rivet\Extras\Start-MigrationOperation.ps1
function Start-MigrationOperation
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration the operation is part of.
$Migration,
[Parameter(Mandatory=$true)]
[Rivet.Operation]
# The operation which is about to be applied.
$Operation
)
Set-StrictMode -Version 'Latest'
$problems = $false
if( ($Operation -is [Rivet.Operations.AddTableOperation]) )
{
if( -not $Operation.Description )
{
Write-Error ('Table {0}''s description not found. Please pass a value to the `Add-Table` function''s `-Description` parameter.' -f $Operation.Name)
$problems = $true
}
Invoke-Command {
smalldatetime 'CreateDate' -NotNull -Default 'getdate()' -Description 'Record created date'
datetime 'LastUpdated' -NotNull -Default 'getdate()' -Description 'Date this record was last updated'
} | ForEach-Object { $Operation.Columns.Add( $_ ) }
$skipRowGuidCol = $Operation.Columns |
Where-Object { $_.DataType -eq [Rivet.DataType]::UniqueIdentifier } |
Where-Object { $_.RowGuidCol }
if( -not $skipRowGuidCol )
{
$Operation.Columns.Add(
(uniqueidentifier 'rowguid' -NotNull -RowGuidCol -Default 'newsequentialid()' -Description 'rowguid column used for replication')
)
}
$Operation.Columns.Add( (bit 'SkipBit' -Default 0 -Description 'Used to bypass custom triggers') )
}
if( ($Operation -is [Rivet.Operations.AddTableOperation]) -or ($Operation -is [Rivet.Operations.UpdateTableOperation]) )
{
('Columns','AddColumns') |
Where-Object { $Operation | Get-Member $_ } |
ForEach-Object { $Operation | Select-Object -ExpandProperty $_ } |
Where-Object { -not $_.Description } |
ForEach-Object {
Write-Error ('Table {0}: column {1}''s description not found. Please supply a value to the {2} function''s `-Description` parameter.' -f $Operation.Name,$_.Name,$_.DataType.ToString().ToLowerInvariant())
$problems = $true
}
('Columns','AddColumns','UpdateColumns') |
Where-Object { $Operation | Get-Member $_ } |
ForEach-Object { $Operation | Select-Object -ExpandProperty $_ } |
ForEach-Object {
if( $_.Identity )
{
if( $_.DataType -ne [Rivet.DataType]::Int )
{
Write-Error ('Table {0}: column {1}: {2} columns can''t be identity columns. Please remove the identity specification or change the column type to int.' -f $Operation.Name,$_.Name,$_.DataType)
$problems = $true
}
$_.Identity.NotForReplication = $true
}
}
}
if( $Operation -is [Rivet.Operations.AddForeignKeyOperation] -or $Operation -is [Rivet.Operations.AddCheckConstraintOperation] )
{
$Operation.NotForReplication = $true
}
if( $Operation -is [Rivet.Operations.AddTriggerOperation] -or $Operation -is [Rivet.Operations.UpdateTriggerOperation] )
{
if( $Operation.Definition -notmatch 'not for replication' )
{
Write-Error ('Trigger {0}: all user-defined triggers must have ''not for replication'' clause specified. Please add the ''not for replication'' clause to your trigger.' -f $_.Name)
$problems = $true
}
}
if( $problems )
{
throw ('There were errors running ''{0}''. Please see previous errors for details.' -f $Operation.GetType().Name)
}
}
Rivet\Formats\Rivet.Migration.format.ps1xml
Rivet\Formats\Rivet.OperationResult.format.ps1xml
Rivet\Formats\RivetMigrationResult-GroupingFormat.format.ps1xml
Rivet\Formats\RivetOperationResult-GroupingFormat.format.ps1xml
Rivet\Functions\Columns\New-BigIntColumn.ps1
function New-BigIntColumn
{
<#
.SYNOPSIS
Creates a column object representing an BigInt datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Migrations' {
BigInt 'MigrationID'
}
## ALIASES
* BigInt
.EXAMPLE
Add-Table 'Migrations' { BigInt 'MigrationID' }
Demonstrates how to create an optional `bigint` column called `MigrationID`.
.EXAMPLE
Add-Table 'Migrations' { BigInt 'ID' -Identity 1 1 }
Demonstrates how to create a required `bigint` column called `ID`, which is used as the table's identity. The identity values will start at 1, and increment by 1.
.EXAMPLE
Add-Table 'Migrations' { BigInt 'MigrationID' -NotNull }
Demonstrates how to create a required `bigint` column called `MigrationID`.
.EXAMPLE
Add-Table 'Migrations' { BigInt 'MigrationID' -Sparse }
Demonstrates how to create a sparse, optional `bigint` column called `MigrationID`.
.EXAMPLE
Add-Table 'Migrations' { BigInt 'MigrationID' -NotNull -Default '0' }
Demonstrates how to create a required `bigint` column called `MigrationID` with a default value of `0`.
.EXAMPLE
Add-Table 'Migrations' { BigInt 'MigrationID' -NotNull -Description 'The number of items currently on hand.' }
Demonstrates how to create a required `bigint` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='Identity')]
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed')]
[Switch]
# The column should be an identity.
$Identity,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=1)]
[int]
# The starting value for the identity.
$Seed,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=2)]
[int]
# The increment between auto-generated identity values.
$Increment,
[Parameter(ParameterSetName='Identity')]
[Parameter(ParameterSetName='IdentityWithSeed')]
[Switch]
# Stops the identity from being replicated.
$NotForReplication,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::BigInt($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::BigInt($Name,'NotNull', $Default, $Description)
}
'Identity'
{
$i = New-Object 'Rivet.Identity' $NotForReplication
[Rivet.Column]::BigInt( $Name, $i, $Description )
}
'IdentityWithSeed'
{
$i = New-Object 'Rivet.Identity' $Seed, $Increment, $NotForReplication
[Rivet.Column]::BigInt( $Name, $i, $Description )
}
}
}
Set-Alias -Name 'BigInt' -Value 'New-BigIntColumn'
Rivet\Functions\Columns\New-BinaryColumn.ps1
function New-BinaryColumn
{
<#
.SYNOPSIS
Creates a column object representing an Binary datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Images' {
Binary 'Bits' 256
}
## ALIASES
* Binary
.EXAMPLE
Add-Table 'Images' { Binary 'Bytes' 256 }
Demonstrates how to create an optional `binary` column with a maximum length of 256 bytes.
.EXAMPLE
Add-Table 'Images' { Binary 'Bytes' 256 -NotNull }
Demonstrates how to create a required `binary` column with maximum length of 256 bytes.
.EXAMPLE
Add-Table 'Images' { Binary 'Bytes' -Max }
Demonstrates how to create an optional `binary` column with the maximum length (2^31 -1 bytes).
.EXAMPLE
Add-Table 'Images' { Binary 'Bytes' -Max -FileStream }
Demonstrates now to create an optional `binary` column with the maximum length, and stores the data in a filestream data container.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[Int]
# The number of bytes the column will hold.
$Size,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$sizetype = New-Object Rivet.CharacterLength $Size
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -eq 'NotNull' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Binary($Name, $sizetype, $nullable, $Default, $Description)
}
Set-Alias -Name 'Binary' -Value 'New-BinaryColumn'
Rivet\Functions\Columns\New-BitColumn.ps1
function New-BitColumn
{
<#
.SYNOPSIS
Creates a column object representing an Bit datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Bit 'IsAvailable'
}
## ALIASES
* Bit
.EXAMPLE
Add-Table 'Items' { Bit 'IsAvailable' }
Demonstrates how to create an optional `bit` column called `IsAvailable`.
.EXAMPLE
Add-Table 'Items' { Bit 'IsAvailable' -NotNull }
Demonstrates how to create a required `bit` column called `IsAvailable`.
.EXAMPLE
Add-Table 'Items' { Bit 'IsAvailable' -Sparse }
Demonstrates how to create a sparse, optional `bit` column called `IsAvailable`.
.EXAMPLE
Add-Table 'Items' { Bit 'IsAvailable' -NotNull -Default '1' }
Demonstrates how to create a required `bit` column called `IsAvailable` with a default value of `1`.
.EXAMPLE
Add-Table 'Items' { Bit 'IsAvailable' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `bit` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Bit($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Bit($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'Bit' -Value 'New-BitColumn'
Rivet\Functions\Columns\New-CharColumn.ps1
function New-CharColumn
{
<#
.SYNOPSIS
Creates a column object representing an Char datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table -State 'Addresses' -Column {
Char 'State' 2
}
## ALIASES
* Char
.EXAMPLE
Add-Table 'Addresses' { Char 'State' 2 }
Demonstrates how to create an optional `char` column with a length of 2 bytes.
.EXAMPLE
Add-Table 'Addresses' { Char 'State' 2 -NotNull }
Demonstrates how to create a required `char` column with length of 2 bytes.
.EXAMPLE
Add-Table 'Addresses' { Char 'State' 2 -Collation 'Latin1_General_BIN' }
Demonstrates now to create an optional `char` column with a custom `Latin1_General_BIN` collation.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[Alias('Length')]
[Int]
# The length of the column, i.e. the number of characters.
$Size,
[Parameter()]
[string]
# Controls the code page that is used to store the data
$Collation,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$Sizetype = $null
$Sizetype = New-Object Rivet.CharacterLength $Size
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -eq 'NotNull' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Char($Name, $Sizetype, $Collation, $nullable, $Default, $Description)
}
Set-Alias -Name 'Char' -Value 'New-CharColumn'
Rivet\Functions\Columns\New-Column.ps1
function New-Column
{
<#
.SYNOPSIS
Creates a column object of an explicit datatype which can be used with the `Add-Table` or `Update-Table` migrations.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Members' {
New-Column 'Birthday' 'datetime'
}
This column is useful for creating columns of custom types or types for which Rivet doesn't have a specific function.
Returns an object that can be used when adding columns or creating tables to get the SQL needed to create that column.
.LINK
New-BigIntColumn
.LINK
New-BinaryColumn
.LINK
New-BitColumn
.LINK
New-CharColumn
.LINK
New-DateColumn
.LINK
New-DateTime2Column
.LINK
New-DateTimeOffsetColumn
.LINK
New-DecimalColumn
.LINK
New-FloatColumn
.LINK
New-HierarchyIDColumn
.LINK
New-IntColumn
.LINK
New-MoneyColumn
.LINK
New-NCharColumn
.LINK
New-NVarCharColumn
.LINK
New-RealColumn
.LINK
New-RowVersionColumn
.LINK
New-SmallDateTimeColumn
.LINK
New-SmallIntColumn
.LINK
New-SmallMoneyColumn
.LINK
New-SqlVariantColumn
.LINK
New-TimeColumn
.LINK
New-TinyIntColumn
.LINK
New-UniqueIdentifierColumn
.LINK
New-VarBinaryColumn
.LINK
New-VarCharColumn
.LINK
New-XmlColumn
.EXAMPLE
Add-Table 'Members' { New-Column 'Birthday' 'datetime' -NotNull }
Demonstrates how to create a required `datetime` column.
.EXAMPLE
Add-Table 'Members' { New-Column 'Birthday' 'float(7)' -NotNull }
Demonstrates that the value of the `DataType` parameter should also include any precision/scale/size specifiers.
.EXAMPLE
Add-Table 'Members' { New-Column 'Birthday' 'datetime' -Sparse }
Demonstrate show to create a nullable, sparse `datetime` column when adding a new table.
.EXAMPLE
Add-Table 'Members' { New-Column 'Birthday' 'datetime' -NotNull -Default 'getdate()' }
Demonstrates how to create a date column with a default value, in this case the current date. (You alwyas use UTC dates, right?) Probably not a great example, setting someone's birthday to the current date. Reasons are left as an exercise for the reader.
.EXAMPLE
Add-Table 'Members' { New-Column 'Birthday' 'datetime' -Description 'The members birthday.' }
Demonstrates how to create an optional date column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The Name of the new column.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The datatype of the new column, including precision/scale/size specifiers.
$DataType,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Optimizes the column storage for null values. Cannot be used with the `NotNull` switch.
$Sparse,
[Parameter(ParameterSetName='NotNull')]
[Switch]
# Makes the column not nullable. Canno be used with the `Sparse` switch.
$NotNull,
[Object]
# A SQL Server expression for the column's default value.
$Default,
[string]
# A description of the column.
$Description
)
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -eq 'NotNull' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
New-Object Rivet.Column $Name,$DataType,$nullable,$Default,$Description
}
Rivet\Functions\Columns\New-DateColumn.ps1
function New-DateColumn
{
<#
.SYNOPSIS
Creates a column object representing an Date datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Members' {
Date 'Birthday'
}
## ALIASES
* Date
.EXAMPLE
Add-Table 'Members' { New-DateColumn 'Birthday' -NotNull }
Demonstrates how to create a required `date` column.
.EXAMPLE
Add-Table 'Members' { Date 'Birthday' -Sparse }
Demonstrate show to create a nullable, sparse `date` column when adding a new table.
.EXAMPLE
Add-Table 'Members' { Date 'Birthday' -NotNull -Default 'get`date`()' }
Demonstrates how to create a `date` column with a default value, in this case the current `date`. (You alwyas use UTC `date`s, right?) Probably not a great example, setting someone's birthday to the current `date`. Reasons are left as an exercise for the reader.
.EXAMPLE
Add-Table 'Members' { Date 'Birthday' -Description 'The members birthday.' }
Demonstrates how to create an optional `date` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Date($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Date($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'Date' -Value 'New-DateColumn'
Rivet\Functions\Columns\New-DateTime2Column.ps1
function New-DateTime2Column
{
<#
.SYNOPSIS
Creates a column object representing an DateTime2 datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Orders' {
DateTime2 'OrderedAt'
}
## ALIASES
* DateTime2
.EXAMPLE
Add-Table 'Orers' { DateTime2 'OrderedAt' }
Demonstrates how to create an optional `datetime2` column.
.EXAMPLE
Add-Table 'Orders' { DateTime2 'OrderedAt' 5 -NotNull }
Demonstrates how to create a required `datetime2` column with 5 digits of fractional seconds precision.
.EXAMPLE
Add-Table 'Orders' { DateTime2 'OrderedAt' -Sparse }
Demonstrate show to create a nullable, sparse `datetime2` column when adding a new table.
.EXAMPLE
Add-Table 'Orders' { DateTime2 'OrderedAt' -NotNull -Default 'getutcdate()' }
Demonstrates how to create a `datetime2` column with a default value. You only use UTC dates, right?
.EXAMPLE
Add-Table 'Orders' { DateTime2 'OrderedAt' -NotNull -Description 'The time the record was created.' }
Demonstrates how to create a `datetime2` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Null')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Position=1)]
[Int]
# The number of decimal digits that will be stored to the right of the decimal point
$Precision,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Null')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$dataSize = $null
if( $PSBoundParameters.ContainsKey( 'Precision' ) )
{
$dataSize = New-Object Rivet.PrecisionScale $Precision
}
$nullable = $PSCmdlet.ParameterSetName
if( $nullable -eq 'Null' -and $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::DateTime2($Name, $dataSize, $nullable, $Default, $Description)
}
Set-Alias -Name 'DateTime2' -Value 'New-DateTime2Column'
Rivet\Functions\Columns\New-DateTimeColumn.ps1
function New-DateTimeColumn
{
<#
.SYNOPSIS
Creates a column object representing an DateTime datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Orders' {
DateTime 'OrderedAt'
}
## ALIASES
* DateTime
.EXAMPLE
Add-Table 'Orers' { DateTime 'OrderedAt' }
Demonstrates how to create an optional `datetime` column.
.EXAMPLE
Add-Table 'Orders' { DateTime 'OrderedAt' 5 -NotNull }
Demonstrates how to create a required `datetime` column with 5 digits of fractional seconds precision.
.EXAMPLE
Add-Table 'Orders' { DateTime 'OrderedAt' -Sparse }
Demonstrate show to create a nullable, sparse `datetime` column when adding a new table.
.EXAMPLE
Add-Table 'Orders' { DateTime 'OrderedAt' -NotNull -Default 'getutcdate()' }
Demonstrates how to create a `datetime` column with a default value. You only use UTC dates, right?
.EXAMPLE
Add-Table 'Orders' { DateTime 'OrderedAt' -NotNull -Description 'The time the record was created.' }
Demonstrates how to create a `datetime` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
if ($PsCmdlet.ParameterSetName -eq 'Nullable')
{
if ($Sparse)
{
New-Column -Name $Name -DataType 'datetime' -Sparse -Default $Default -Description $Description
}
else {
New-Column -Name $Name -DataType 'datetime' -Default $Default -Description $Description
}
}
elseif ($PsCmdlet.ParameterSetName -eq 'NotNull')
{
New-Column -Name $Name -DataType 'datetime' -NotNull -Default $Default -Description $Description
}
}
Set-Alias -Name 'DateTime' -Value 'New-DateTimeColumn'
Rivet\Functions\Columns\New-DateTimeOffsetColumn.ps1
function New-DateTimeOffsetColumn
{
<#
.SYNOPSIS
Creates a column object representing an DateTimeOffset datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Orders' {
DateTimeOffset 'OrderedAt'
}
## ALIASES
* DateTimeOffset
.EXAMPLE
Add-Table 'Orers' { DateTimeOffset 'OrderedAt' }
Demonstrates how to create an optional `datetimeoffset` column.
.EXAMPLE
Add-Table 'Orders' { DateTimeOffset 'OrderedAt' 5 -NotNull }
Demonstrates how to create a required `datetimeoffset` column with a digits of fractional seconds precision.
.EXAMPLE
Add-Table 'Orders' { DateTimeOffset 'OrderedAt' -Sparse }
Demonstrate show to create a nullable, sparse `datetimeoffset` column when adding a new table.
.EXAMPLE
Add-Table 'Orders' { DateTimeOffset 'OrderedAt' -NotNull -Default 'getutcdate()' }
Demonstrates how to create a `datetimeoffset` column with a default value. You only use UTC dates, right?
.EXAMPLE
Add-Table 'Orders' { DateTimeOffset 'OrderedAt' -NotNull -Description 'The time the record was created.' }
Demonstrates how to create a `datetimeoffset` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Null')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Position=1)]
[Int]
# The number of decimal digits that will be stored to the right of the decimal point
$Precision,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Null')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$dataSize = $null
if( $PSBoundParameters.ContainsKey('Precision') )
{
$dataSize = New-Object Rivet.PrecisionScale $Precision
}
$nullable = $PSCmdlet.ParameterSetName
if( $nullable -eq 'Null' -and $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::DateTimeOffset($Name, $dataSize, $nullable, $Default, $Description)
}
Set-Alias -Name 'DateTimeOffset' -Value 'New-DateTimeOffsetColumn'
Rivet\Functions\Columns\New-DecimalColumn.ps1
function New-DecimalColumn
{
<#
.SYNOPSIS
Creates a column object representing a `decimal` data type.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Decimal 'Price'
}
## ALIASES
* Decimal
* Numeric
* New-NumericColumn
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' 5 2 }
Demonstrates how to create an optional `decimal` column called `Price`, with a five-digit precision (prices less than $999.99) and a scale of 2 (2 digits after the `decimal`).
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' -Identity -Seed 1 -Increment 1 }
Demonstrates how to create a required `decimal` column called `Price`, which is used as the table's identity. The identity values will start at 1, and increment by 1. Uses SQL Server's default precision/scale.
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' -NotNull }
Demonstrates how to create a required `decimal` column called `Price`. Uses SQL Server's default precision/scale.
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' -Sparse }
Demonstrates how to create a sparse, optional `decimal` column called `Price`. Uses SQL Server's default precision/scale.
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' -NotNull -Default '0' }
Demonstrates how to create a required `decimal` column called `Price` with a default value of `0`. Uses SQL Server's default precision/scale.
.EXAMPLE
Add-Table 'Items' { Decimal 'Price' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `decimal` column with a description. Uses SQL Server's default precision/scale.
#>
[CmdletBinding(DefaultParameterSetName='Null')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Position=1)]
[Int]
# Maximum total number of decimal digits that will be stored.
$Precision,
[Parameter(Position=2)]
[Int]
# The number of decimal digits that will be stored to the right of the decimal point.
$Scale,
[Parameter(Mandatory=$true,ParameterSetName='Identity')]
[Switch]
# The column should be an identity.
$Identity,
[Parameter(ParameterSetName='Identity')]
[int]
# The starting value for the identity.
$Seed,
[Parameter(ParameterSetName='Identity')]
[int]
# The increment between auto-generated identity values.
$Increment,
[Parameter(ParameterSetName='Identity')]
[Switch]
# Stops the identity from being replicated.
$NotForReplication,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Null')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$dataSize = $null
if( $PSBoundParameters.ContainsKey( 'Precision' ) -and $PSBoundParameters.ContainsKey( 'Scale' ) )
{
$dataSize = New-Object Rivet.PrecisionScale $Precision, $Scale
}
elseif( $PSBoundParameters.ContainsKey( 'Precision' ) )
{
$dataSize = New-Object Rivet.PrecisionScale $Precision
}
elseif( $PSBoundParameters.ContainsKey( 'Scale' ) )
{
throw ('New-DecimalColumn: a scale for column {0} is given, but no precision. Please remove the `-Scale` parameter, or add a `-Precision` parameter with a value.' -f $Name)
}
switch ($PSCmdlet.ParameterSetName)
{
'Null'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Decimal($Name, $dataSize, $nullable, $Default, $Description)
break
}
'NotNull'
{
[Rivet.Column]::Decimal($Name, $dataSize, 'NotNull', $Default, $Description)
break
}
'Identity'
{
if( $PSBoundParameters.ContainsKey('Seed') -and $PSBoundParameters.ContainsKey('Increment') )
{
$i = New-Object 'Rivet.Identity' $Seed,$Increment,$NotForReplication
}
elseif( $PSBoundParameters.ContainsKey('Seed') )
{
$i = New-Object 'Rivet.Identity' $Seed,1,$NotForReplication
}
elseif( $PSBoundParameters.ContainsKey('Increment') )
{
$i = New-Object 'Rivet.Identity' 1,$Increment,$NotForReplication
}
else
{
$i = New-Object 'Rivet.Identity' $NotForReplication
}
[Rivet.Column]::Decimal( $Name, $dataSize, $i, $Description )
break
}
}
}
Set-Alias -Name 'Decimal' -Value 'New-DecimalColumn'
Set-Alias -Name 'Numeric' -Value 'New-DecimalColumn'
Set-Alias -Name 'New-NumericColumn' -Value 'New-DecimalColumn'
Rivet\Functions\Columns\New-FloatColumn.ps1
function New-FloatColumn
{
<#
.SYNOPSIS
Creates a column object representing a `float` datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Float 'Price'
}
## ALIASES
* Float
.EXAMPLE
Add-Table 'Items' { Float 'Price' -Precision 5 }
Demonstrates how to create an optional `float` column called `Price`, with a precision of 5.
.EXAMPLE
Add-Table 'Items' { Float 'Price' -NotNull }
Demonstrates how to create a required `float` column called `Price`. Uses SQL Server's default precision.
.EXAMPLE
Add-Table 'Items' { Float 'Price' -Sparse }
Demonstrates how to create a sparse, optional `float` column called `Price`. Uses SQL Server's default precision.
.EXAMPLE
Add-Table 'Items' { Float 'Price' -NotNull -Default '0.0' }
Demonstrates how to create a required `float` column called `Price` with a default value of `0`. Uses SQL Server's default precision.
.EXAMPLE
Add-Table 'Items' { Float 'Price' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `float` column with a description. Uses SQL Server's default precision.
#>
[CmdletBinding(DefaultParameterSetName='Null')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Position=1)]
[Int]
# Maximum total number of Numeric digits that will be stored
$Precision,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Null')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$dataSize = $null
if ($Precision -gt 0)
{
$dataSize = New-Object Rivet.PrecisionScale $Precision
}
$nullable = $PSCmdlet.ParameterSetName
if( $nullable -eq 'Null' -and $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Float($Name, $dataSize, $nullable, $Default, $Description)
}
Set-Alias -Name 'Float' -Value 'New-FloatColumn'
Rivet\Functions\Columns\New-HierarchyIDColumn.ps1
function New-HierarchyIDColumn
{
<#
.SYNOPSIS
Creates a column object representing an HierarchyID datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'FamilyTree' {
HierarchyID 'Father'
}
## ALIASES
* HierarchyID
.EXAMPLE
Add-Table 'FamilyTree' { HierarchyID 'Father' }
Demonstrates how to create an optional `hierarchyid` column called `Father`.
.EXAMPLE
Add-Table 'FamilyTree' { HierarchyID 'Father' -NotNull }
Demonstrates how to create a required `hierarchyid` column called `Father`.
.EXAMPLE
Add-Table 'FamilyTree' { HierarchyID 'Father' -Sparse }
Demonstrates how to create a sparse, optional `hierarchyid` column called `Father`.
.EXAMPLE
Add-Table 'FamilyTree' { HierarchyID 'Father' -NotNull -Description "The hierarchy ID of this person's father." }
Demonstrates how to create a required `hierarchyid` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::HierarchyID($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::HierarchyID($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'HierarchyID' -Value 'New-HierarchyIDColumn'
Rivet\Functions\Columns\New-IntColumn.ps1
function New-IntColumn
{
<#
.SYNOPSIS
Creates a column object representing an Int datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Int 'Quantity'
}
## ALIASES
* Int
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' }
Demonstrates how to create an optional `int` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' -Identity 1 1 }
Demonstrates how to create a required `int` column called `Quantity`, which is used as the table's identity. The identity values will start at 1, and increment by 1.
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' -NotNull }
Demonstrates how to create a required `int` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' -Sparse }
Demonstrates how to create a sparse, optional `int` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' -NotNull -Default '0' }
Demonstrates how to create a required `int` column called `Quantity` with a default value of `0`.
.EXAMPLE
Add-Table 'Items' { Int 'Quantity' -NotNull -Description 'The number of items currently on hand.' }
Demonstrates how to create a required `int` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='Identity')]
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed')]
[Switch]
# The column should be an identity.
$Identity,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=1)]
[int]
# The starting value for the identity.
$Seed,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=2)]
[int]
# The increment between auto-generated identity values.
$Increment,
[Parameter(ParameterSetName='Identity')]
[Parameter(ParameterSetName='IdentityWithSeed')]
[Switch]
# Stops the identity from being replicated.
$NotForReplication,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Int($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Int($Name,'NotNull', $Default, $Description)
}
'Identity'
{
$i = New-Object 'Rivet.Identity' $NotForReplication
[Rivet.Column]::Int( $Name, $i, $Description )
}
'IdentityWithSeed'
{
$i = New-Object 'Rivet.Identity' $Seed, $Increment, $NotForReplication
[Rivet.Column]::Int( $Name, $i, $Description )
}
}
}
Set-Alias -Name 'Int' -Value 'New-IntColumn'
Rivet\Functions\Columns\New-MoneyColumn.ps1
function New-MoneyColumn
{
<#
.SYNOPSIS
Creates a column object representing an Money datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Money 'Price'
}
## ALIASES
* Money
.EXAMPLE
Add-Table 'Items' { Money 'Price' }
Demonstrates how to create an optional `money` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Money 'Price' -NotNull }
Demonstrates how to create a required `money` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Money 'Price' -Sparse }
Demonstrates how to create a sparse, optional `money` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Money 'Price' -NotNull -Default '0.00' }
Demonstrates how to create a required `money` column called `Price` with a default value of `$0.00`.
.EXAMPLE
Add-Table 'Items' { Money 'Price' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `money` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Money($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Money($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'Money' -Value 'New-MoneyColumn'
Rivet\Functions\Columns\New-NCharColumn.ps1
function New-NCharColumn
{
<#
.SYNOPSIS
Creates a column object representing an NChar datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table -State 'Addresses' -Column {
NChar 'State' 2
}
## ALIASES
* NChar
.EXAMPLE
Add-Table 'Addresses' { NChar 'State' 2 }
Demonstrates how to create an optional `nchar` column with a length of 2 bytes.
.EXAMPLE
Add-Table 'Addresses' { NChar 'State' 2 -NotNull }
Demonstrates how to create a required `nchar` column with length of 2 bytes.
.EXAMPLE
Add-Table 'Addresses' { NChar 'State' 2 -Collation 'Latin1_General_BIN' }
Demonstrates now to create an optional `nchar` column with a custom `Latin1_General_BIN` collation.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[Int]
# Defines the string Size of the fixed-Size string data. Default is 30
$Size,
[Parameter()]
[string]
# Controls the code page that is used to store the data
$Collation,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$Sizetype = $null
$Sizetype = New-Object Rivet.CharacterLength $Size
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -eq 'NotNull' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::NChar($Name, $Sizetype, $Collation, $nullable, $Default, $Description)
}
Set-Alias -Name 'NChar' -Value 'New-NCharColumn'
Rivet\Functions\Columns\New-NVarCharColumn.ps1
function New-NVarCharColumn
{
<#
.SYNOPSIS
Creates a column object representing an NVarChar datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table -Name 'Albums' -Column {
NVarChar 'Name' 50
}
## ALIASES
* NVarChar
.EXAMPLE
Add-Table 'Albums' { NVarChar 'Name' 100 }
Demonstrates how to create an optional `nvarchar` column with a maximum length of 100 bytes.
.EXAMPLE
Add-Table 'Albums' { NVarChar 'Name' 100 -NotNull }
Demonstrates how to create a required `nvarchar` column with maximum length of 100 bytes.
.EXAMPLE
Add-Table 'Albums' { NVarChar 'Name' -Max }
Demonstrates how to create an optional `nvarchar` column with the maximum length (about 2GB).
.EXAMPLE
Add-Table 'Albums' { NVarChar 'Name' 100 -Collation 'Latin1_General_BIN' }
Demonstrates now to create an optional `nvarchar` column with a custom `Latin1_General_BIN` collation.
#>
[CmdletBinding(DefaultParameterSetName='NullSize')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NullSize')]
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NotNullSize')]
[Alias('Length')]
[Int]
# The maximum length of the column, i.e. the number of unicode characters.
$Size,
[Parameter(Mandatory=$true,ParameterSetName='NullMax')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Create an `nvarchar(max)` column.
$Max,
[Parameter()]
[string]
# Controls the code page that is used to store the data
$Collation,
[Parameter(Mandatory=$true,ParameterSetName='NotNullSize')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='NullSize')]
[Parameter(ParameterSetName='NullMax')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$sizeType = $null
if( $PSCmdlet.ParameterSetName -like '*Size' )
{
$sizeType = New-Object Rivet.CharacterLength $Size
}
else
{
$sizeType = New-Object Rivet.CharacterLength @()
}
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -like 'NotNull*' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::NVarChar($Name, $sizeType, $Collation, $nullable, $Default, $Description)
}
Set-Alias -Name 'NVarChar' -Value 'New-NVarCharColumn'
Rivet\Functions\Columns\New-RealColumn.ps1
function New-RealColumn
{
<#
.SYNOPSIS
Creates a column object representing an Real datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
Real 'Price'
}
## ALIASES
* Real
.EXAMPLE
Add-Table 'Items' { Real 'Price' }
Demonstrates how to create an optional `real` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Real 'Price' -NotNull }
Demonstrates how to create a required `real` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Real 'Price' -Sparse }
Demonstrates how to create a sparse, optional `real` column called `Price`.
.EXAMPLE
Add-Table 'Items' { Real 'Price' -NotNull -Default '0.00' }
Demonstrates how to create a required `real` column called `Price` with a default value of `$0.00`.
.EXAMPLE
Add-Table 'Items' { Real 'Price' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `real` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Real($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Real($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'Real' -Value 'New-RealColumn'
Rivet\Functions\Columns\New-RowVersionColumn.ps1
function New-RowVersionColumn
{
<#
.SYNOPSIS
Creates a column object representing an RowVersion datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'WithUUID' {
RowVersion 'ColumnName'
}
## ALIASES
* RowVersion
.EXAMPLE
Add-Table Changes { RowVersion 'Version' }
Demonstrates how to create a table with an optional `rowversion` column.
.EXAMPLE
Add-Table Locations { RowVersion 'LocationID' -RowGuidCol }
Demonstrates how to create a table with an optional `rowversion`, which is used as the RowGuid identifier for SQL Server replication.
.EXAMPLE
Add-Table Locations { RowVersion 'LocationID' -NotNull }
Demonstrates how to create a table with an required `rowversion` column.
.EXAMPLE
Add-Table Locations { RowVersion 'LocationID' -Default 'newid()' }
Demonstrates how to create a table with an optional `rowversion` column with a default value.
.EXAMPLE
Add-Table Locations { RowVersion 'LocationID' -Description 'The unique identifier for this location.' }
Demonstrates how to create a table with an optional `rowversion` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::RowVersion($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::RowVersion($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'RowVersion' -Value 'New-RowVersionColumn'
Rivet\Functions\Columns\New-SmallDateTimeColumn.ps1
function New-SmallDateTimeColumn
{
<#
.SYNOPSIS
Creates a column object representing an SmallDateTime datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Orders' {
SmallDateTime 'OrderedAt'
}
## ALIASES
* SmallDateTime
.EXAMPLE
Add-Table 'Orders' { New-SmallDateTimeColumn 'OrderedAt' -NotNull }
Demonstrates how to create a required `smalldatetime` colum when adding a new table.
.EXAMPLE
Add-Table 'Orders' { SmallDateTime 'OrderedAt' -Sparse }
Demonstrate show to create a nullable, sparse `smalldatetime` column when adding a new table.
.EXAMPLE
Add-Table 'Orders' { SmallDateTime 'OrderedAt' -NotNull -Default 'getutcdate()' }
Demonstrates how to create a `smalldatetime` column with a default value. You only use UTC dates, right?
.EXAMPLE
Add-Table 'Orders' { SmallDateTime 'OrderedAt' -NotNull -Description 'The time the record was created.' }
Demonstrates how to create a `smalldatetime` column a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::SmallDateTime($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::SmallDateTime($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'SmallDateTime' -Value 'New-SmallDateTimeColumn'
Rivet\Functions\Columns\New-SmallIntColumn.ps1
function New-SmallIntColumn
{
<#
.SYNOPSIS
Creates a column object representing an SmallInt datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
SmallInt 'Quantity'
}
## ALIASES
* SmallInt
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' }
Demonstrates how to create an optional `smallint` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' -Identity 1 1 }
Demonstrates how to create a required `smallint` column called `Quantity`, which is used as the table's identity. The identity values will start at 1, and increment by 1.
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' -NotNull }
Demonstrates how to create a required `smallint` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' -Sparse }
Demonstrates how to create a sparse, optional `smallint` column called `Quantity`.
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' -NotNull -Default '0' }
Demonstrates how to create a required `smallint` column called `Quantity` with a default value of `0`.
.EXAMPLE
Add-Table 'Items' { SmallInt 'Quantity' -NotNull -Description 'The number of items currently on hand.' }
Demonstrates how to create a required `smallint` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='Identity')]
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed')]
[Switch]
# The column should be an identity.
$Identity,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=1)]
[int]
# The starting value for the identity.
$Seed,
[Parameter(Mandatory=$true,ParameterSetName='IdentityWithSeed',Position=2)]
[int]
# The increment between auto-generated identity values.
$Increment,
[Parameter(ParameterSetName='Identity')]
[Parameter(ParameterSetName='IdentityWithSeed')]
[Switch]
# Stops the identity from being replicated.
$NotForReplication,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::SmallInt($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::SmallInt($Name,'NotNull', $Default, $Description)
}
'Identity'
{
$i = New-Object 'Rivet.Identity' $NotForReplication
[Rivet.Column]::SmallInt( $Name, $i, $Description )
}
'IdentityWithSeed'
{
$i = New-Object 'Rivet.Identity' $Seed, $Increment, $NotForReplication
[Rivet.Column]::SmallInt( $Name, $i, $Description )
}
}
}
Set-Alias -Name 'SmallInt' -Value 'New-SmallIntColumn'
Rivet\Functions\Columns\New-SmallMoneyColumn.ps1
function New-SmallMoneyColumn
{
<#
.SYNOPSIS
Creates a column object representing an SmallMoney datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Items' {
SmallMoney 'Price'
}
## ALIASES
* SmallMoney
.EXAMPLE
Add-Table 'Items' { SmallMoney 'Price' }
Demonstrates how to create an optional `smallmoney` column called `Price`.
.EXAMPLE
Add-Table 'Items' { SmallMoney 'Price' -NotNull }
Demonstrates how to create a required `smallmoney` column called `Price`.
.EXAMPLE
Add-Table 'Items' { SmallMoney 'Price' -Sparse }
Demonstrates how to create a sparse, optional `smallmoney` column called `Price`.
.EXAMPLE
Add-Table 'Items' { SmallMoney 'Price' -NotNull -Default '0.00' }
Demonstrates how to create a required `smallmoney` column called `Price` with a default value of `$0.00`.
.EXAMPLE
Add-Table 'Items' { SmallMoney 'Price' -NotNull -Description 'The price of the item.' }
Demonstrates how to create a required `smallmoney` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::SmallMoney($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::SmallMoney($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'SmallMoney' -Value 'New-SmallMoneyColumn'
Rivet\Functions\Columns\New-SqlVariantColumn.ps1
function New-SqlVariantColumn
{
<#
.SYNOPSIS
Creates a column object representing an SqlVariant datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'WithSqlVariant' {
SqlVariant 'ColumnName'
}
## ALIASES
* SqlVariant
.EXAMPLE
Add-Table 'WithSqlVar' { SqlVariant 'WhoKnows' }
Demonstrates how to create an optional `sql_variant` column called `WhoKnows`.
.EXAMPLE
Add-Table 'WithSqlVar' { SqlVariant 'WhoKnows' -NotNull }
Demonstrates how to create a required `sql_variant` column called `WhoKnows`.
.EXAMPLE
Add-Table 'WithSqlVar' { SqlVariant 'WhoKnows' -Sparse }
Demonstrates how to create a sparse, optional `sql_variant` column called `WhoKnows`.
.EXAMPLE
Add-Table 'WithSqlVar' { SqlVariant 'WhoKnows' -NotNull -Default '1' }
Demonstrates how to create a required `sql_variant` column called `WhoKnows` with a default value of `1`.
.EXAMPLE
Add-Table 'WithSqlVar' { SqlVariant 'WhoKnows' -NotNull -Description 'The contents of this column are left as an exercise for the reader.' }
Demonstrates how to create a required `sql_variant` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::SqlVariant($Name, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::SqlVariant($Name,'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'SqlVariant' -Value 'New-SqlVariantColumn'
Rivet\Functions\Columns\New-TimeColumn.ps1
function New-TimeColumn
{
<#
.SYNOPSIS
Creates a column object representing an Time datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'WithTime' {
Time 'ColumnName'
}
## ALIASES
* Time
.EXAMPLE
Add-Table 'WithTime' { New-TimeColumn 'CreatedAt' 5 -NotNull }
Demonstrates how to create a required `time` column with a given scale when adding a new table.
.EXAMPLE
Add-Table 'WithTime' { Time 'CreatedAt' -Sparse }
Demonstrate show to create a nullable, sparse `time` column when adding a new table.
.EXAMPLE
Add-Table 'WithTime' { Time 'CreatedAt' -NotNull -Default 'convert(`time`, getutcdate())' }
Demonstrates how to create a `time` column with a default value, in this case the current time. You alwyas use UTC, right?
.EXAMPLE
Add-Table 'WithTime' { Time 'CreatedAt' -NotNull -Description 'The `time` the record was created.' }
Demonstrates how to create a `time` column with a description.
#>
[CmdletBinding(DefaultParameterSetName='Null')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Position=1)]
[Int]
# The number of decimal digits that will be stored to the right of the decimal point.
$Precision,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Null')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$dataSize = $null
if( $PSBoundParameters.ContainsKey('Precision') )
{
$dataSize = New-Object Rivet.PrecisionScale $Precision
}
$nullable = $PSCmdlet.ParameterSetName
if( $nullable -eq 'Null' -and $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Time($Name, $dataSize, $nullable, $Default, $Description)
}
Set-Alias -Name 'Time' -Value 'New-TimeColumn'
Rivet\Functions\Columns\New-TinyIntColumn.ps1
Rivet\Functions\Columns\New-UniqueIdentifierColumn.ps1
function New-UniqueIdentifierColumn
{
<#
.SYNOPSIS
Creates a column object representing an UniqueIdentifier datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'WithUUID' {
UniqueIdentifier 'ColumnName'
}
## ALIASES
* UniqueIdentifier
.EXAMPLE
Add-Table Locations { UniqueIdentifier 'LocationID' }
Demonstrates how to create a table with an optional `uniqueidentifier` column.
.EXAMPLE
Add-Table Locations { UniqueIdentifier 'LocationID' -RowGuidCol }
Demonstrates how to create a table with an optional `uniqueidentifier`, which is used as the RowGuid identifier for SQL Server replication.
.EXAMPLE
Add-Table Locations { UniqueIdentifier 'LocationID' -NotNull }
Demonstrates how to create a table with an required `uniqueidentifier` column.
.EXAMPLE
Add-Table Locations { UniqueIdentifier 'LocationID' -Default 'newid()' }
Demonstrates how to create a table with an optional `uniqueidentifier` column with a default value.
.EXAMPLE
Add-Table Locations { UniqueIdentifier 'LocationID' -Description 'The unique identifier for this location.' }
Demonstrates how to create a table with an optional `uniqueidentifier` column with a default value.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter()]
[Switch]
# Sets RowGuidCol
$RowGuidCol,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::UniqueIdentifier($Name, $RowGuidCol, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::UniqueIdentifier($Name, $RowGuidCol, 'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'UniqueIdentifier' -Value 'New-UniqueIdentifierColumn'
Rivet\Functions\Columns\New-VarBinaryColumn.ps1
function New-VarBinaryColumn
{
<#
.SYNOPSIS
Creates a column object representing an VarBinary datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table 'Images' {
VarBinary 'Bits' 8000
}
## ALIASES
* VarBinary
.EXAMPLE
Add-Table 'Images' { VarBinary 'Bytes' 8000 }
Demonstrates how to create an optional `varbinary` column with a maximum length of 8000 bytes.
.EXAMPLE
Add-Table 'Images' { VarBinary 'Bytes' 8000 -NotNull }
Demonstrates how to create a required `varbinary` column with maximum length of 8000 bytes.
.EXAMPLE
Add-Table 'Images' { VarBinary 'Bytes' -Max }
Demonstrates how to create an optional `varbinary` column with the maximum length (2^31 -1 bytes).
.EXAMPLE
Add-Table 'Images' { VarBinary 'Bytes' -Max -FileStream }
Demonstrates now to create an optional `varbinary` column with the maximum length, and stores the data in a filestream data container.
#>
[CmdletBinding(DefaultParameterSetName='NullSize')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NullSize')]
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NotNullSize')]
[Int]
# The maximum number of bytes the column will hold.
$Size,
[Parameter(Mandatory=$true,ParameterSetName='NullMax')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Creates a `varbinary(max)` column.
$Max,
[Parameter(ParameterSetName='NullMax')]
[Parameter(ParameterSetName='NotNullMax')]
[Switch]
# Stores the varbinary(max) data in a filestream data container on the file system. Requires VarBinary(max).
$FileStream,
[Parameter(Mandatory=$true,ParameterSetName='NotNullSize')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='NullSize')]
[Parameter(ParameterSetName='NullMax')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$sizeType = $null
if( $PSCmdlet.ParameterSetName -like '*Size' )
{
$sizeType = New-Object Rivet.CharacterLength $Size
}
else
{
$sizeType = New-Object Rivet.CharacterLength @()
}
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -like 'NotNull*' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::VarBinary($Name, $sizeType, $FileStream, $nullable, $Default, $Description)
}
Set-Alias -Name 'VarBinary' -Value 'New-VarBinaryColumn'
Rivet\Functions\Columns\New-VarCharColumn.ps1
function New-VarCharColumn
{
<#
.SYNOPSIS
Creates a column object representing an VarChar datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table -Name 'WithVarCharColumn' -Column {
VarChar 'ColumnName' 50
}
## ALIASES
* VarChar
.EXAMPLE
Add-Table 'Albums' { VarChar 'Name' 100 }
Demonstrates how to create an optional `varchar` column with a maximum length of 100 bytes.
.EXAMPLE
Add-Table 'Albums' { VarChar 'Name' 100 -NotNull }
Demonstrates how to create a required `varchar` column with maximum length of 100 bytes.
.EXAMPLE
Add-Table 'Albums' { VarChar 'Name' -Max }
Demonstrates how to create an optional `varchar` column with the maximum length (about 2GB).
.EXAMPLE
Add-Table 'Albums' { VarChar 'Name' 100 -Collation 'Latin1_General_BIN' }
Demonstrates now to create an optional `varchar` column with a custom `Latin1_General_BIN` collation.
#>
[CmdletBinding(DefaultParameterSetName='NullSize')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NullSize')]
[Parameter(Mandatory=$true,Position=1,ParameterSetName='NotNullSize')]
[Int]
# The maximum length of the column, i.e. the number of characters.
$Size,
[Parameter(Mandatory=$true,ParameterSetName='NullMax')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Create a `varchar(max)` column.
$Max,
[Parameter()]
[string]
# Controls the code page that is used to store the data
$Collation,
[Parameter(Mandatory=$true,ParameterSetName='NotNullSize')]
[Parameter(Mandatory=$true,ParameterSetName='NotNullMax')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='NullSize')]
[Parameter(ParameterSetName='NullMax')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
$sizeType = $null
if( $PSCmdlet.ParameterSetName -like '*Size' )
{
$sizeType = New-Object Rivet.CharacterLength $Size
}
else
{
$sizeType = New-Object Rivet.CharacterLength @()
}
$nullable = 'Null'
if( $PSCmdlet.ParameterSetName -like 'NotNull*' )
{
$nullable = 'NotNull'
}
elseif( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::VarChar($Name, $sizeType, $Collation, $nullable, $Default, $Description)
}
Set-Alias -Name 'VarChar' -Value 'New-VarCharColumn'
Rivet\Functions\Columns\New-XmlColumn.ps1
function New-XmlColumn
{
<#
.SYNOPSIS
Creates a column object representing an Xml datatype.
.DESCRIPTION
Use this function in the `Column` script block for `Add-Table`:
Add-Table -Name 'WebConfigs' -Column {
Xml 'WebConfig' -XmlSchemaCollection 'webconfigschema'
}
Remember you have to have already created the XML schema before creating a column that uses it.
## ALIASES
* Xml
.EXAMPLE
Add-Table 'WebConfigs' { Xml 'WebConfig' -XmlSchemaCollection 'webconfigschema' }
Demonstrates how to create an optional `xml` column which uses the `webconfigschema` schema collection.
.EXAMPLE
Add-Table 'WebConfigs' { Xml 'WebConfig' -XmlSchemaCollection 'webconfigschema' -NotNull }
Demonstrates how to create a required `xml` column.
.EXAMPLE
Add-Table 'WebConfigs' { Xml 'WebConfig' -XmlSchemaCollection 'webconfigschema'' -Document }
Demonstrates how to create an `xml` column that holds an entire XML document.
#>
[CmdletBinding(DefaultParameterSetName='Nullable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The column's name.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[string]
# Name of an XML schema collection
$XmlSchemaCollection,
[Parameter()]
[Switch]
# Specifies that this is a well-formed XML document instead of an XML fragment.
$Document,
[Parameter(Mandatory=$true,ParameterSetName='NotNull')]
[Switch]
# Don't allow `NULL` values in this column.
$NotNull,
[Parameter(ParameterSetName='Nullable')]
[Switch]
# Store nulls as Sparse.
$Sparse,
[Parameter()]
[string]
# A SQL Server expression for the column's default value
$Default,
[Parameter()]
[string]
# A description of the column.
$Description
)
switch ($PSCmdlet.ParameterSetName)
{
'Nullable'
{
$nullable = 'Null'
if( $Sparse )
{
$nullable = 'Sparse'
}
[Rivet.Column]::Xml($Name, $Document, $XmlSchemaCollection, $nullable, $Default, $Description)
}
'NotNull'
{
[Rivet.Column]::Xml($Name, $Document, $XmlSchemaCollection, 'NotNull', $Default, $Description)
}
}
}
Set-Alias -Name 'Xml' -Value 'New-XmlColumn'
Rivet\Functions\Connect-Database.ps1
function Connect-Database
{
param(
[Parameter(Mandatory=$true)]
[string]
# The SQL Server instance to connect to.
$SqlServerName,
[Parameter(Mandatory=$true)]
[string]
# The database to connect to.
$Database,
[UInt32]
# The time (in seconds) to wait for a connection to open. The default is 10 seconds.
$ConnectionTimeout = 10
)
Set-StrictMode -Version 'Latest'
$startedAt = Get-Date
if( -not $Connection -or $Connection.DataSource -ne $SqlServerName -or $Connection.State -eq [Data.ConnectionState]::Closed)
{
Disconnect-Database
$connString = 'Server={0};Database=master;Integrated Security=True;Connection Timeout={1}' -f $SqlServerName,$ConnectionTimeout
Set-Variable -Name 'Connection' -Scope 1 -Value (New-Object 'Data.SqlClient.SqlConnection' ($connString)) -Confirm:$False -WhatIf:$false
try
{
$Connection.Open()
}
catch
{
$ex = $_.Exception
while( $ex.InnerException )
{
$ex = $ex.InnerException
}
Write-Error ('Failed to connect to SQL Server ''{0}'' (connection string: {1}). Does this database server exist? ({2})' -f $SqlServerName,$connString,$ex.Message)
return $false
}
}
if( -not ($Connection | Get-Member -Name 'Transaction' ) )
{
$Connection |
Add-Member -MemberType NoteProperty -Name 'Transaction' -Value $null
}
if( $Connection.Database -ne 'master' )
{
$Connection.ChangeDatabase( 'master' )
}
$query = 'select 1 from sys.databases where name=''{0}''' -f $Database
$dbExists = Invoke-Query -Query $query -AsScalar
if( -not $dbExists )
{
Write-Debug -Message ('Creating database {0}.{1}.' -f $SqlServerName,$Database)
$query = 'create database [{0}]' -f $Database
Invoke-Query -Query $query -NonQuery
}
$Connection.ChangeDatabase( $Database )
Write-Debug -Message ('{0,8} (ms) Connect-Database' -f ([int]((Get-Date) - $startedAt).TotalMilliseconds))
return $true
}
Rivet\Functions\Convert-FileInfoToMigration.ps1
function Convert-FileInfoToMigration
{
<#
.SYNOPSIS
Converts a `System.IO.FileInfo` object containing a migration into a `Rivet.Operation` object.
#>
[CmdletBinding()]
[OutputType([Rivet.Migration])]
param(
[Parameter(Mandatory=$true)]
[Rivet.Configuration.Configuration]
# The Rivet configuration to use.
$Configuration,
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[IO.FileInfo]
# The database whose migrations to get.np
$InputObject
)
begin
{
Set-StrictMode -Version 'Latest'
function Clear-Migration
{
('function:Push-Migration','function:Pop-Migration') |
Where-Object { Test-Path -Path $_ } |
Remove-Item -WhatIf:$false -Confirm:$false
}
Clear-Migration
if( $Configuration.PluginsRoot )
{
Import-Plugin -Path $Configuration.PluginsRoot
}
}
process
{
$InputObject |
ForEach-Object {
$dbName = Split-Path -Parent -Path $_.FullName
$dbName = Split-Path -Parent -Path $dbName
$dbName = Split-Path -Leaf -Path $dbName
$m = New-Object 'Rivet.Migration' $_.MigrationID,$_.MigrationName,$_.FullName,$dbName
filter Add-Operation
{
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[object]
# The migration object to invoke.
$Operation,
[Parameter(ParameterSetName='Push',Mandatory=$true)]
[Collections.Generic.List[Rivet.Operation]]
[AllowEmptyCollection()]
$OperationsList,
[Parameter(ParameterSetName='Pop',Mandatory=$true)]
[Switch]
$Pop
)
Set-StrictMode -Version 'Latest'
$Operation |
Where-Object { $_ -is [Rivet.Operation] } |
ForEach-Object {
if( (Test-Path -Path 'function:Start-MigrationOperation') )
{
Start-MigrationOperation -Migration $m -Operation $_
}
$_
if( (Test-Path -Path 'function:Complete-MigrationOperation') )
{
Complete-MigrationOperation -Migration $m -Operation $_
}
} |
Where-Object { $_ -is [Rivet.Operation] } |
ForEach-Object { $OperationsList.Add( $_ ) } |
Out-Null
}
$DBMigrationsRoot = Split-Path -Parent -Path $_.FullName
. $_.FullName
try
{
if( -not (Test-Path -Path 'function:Push-Migration') )
{
throw (@'
Push-Migration function not found. All migrations are required to have a Push-Migration function that contains at least one operation. Here's some sample code to get you started:
function Push-Migration
{
Add-Table 'LetsCreateATable' {
int 'ID' -NotNull
}
}
'@)
}
Push-Migration | Add-Operation -OperationsList $m.PushOperations
if( $m.PushOperations.Count -eq 0 )
{
throw (@'
Push-Migration function is empty and contains no operations. Maybe you''d like to create a table? Here's some sample code to get you started:
function Push-Migration
{
Add-Table 'LetsCreateATable' {
int 'ID' -NotNull
}
}
'@)
}
if( -not (Test-Path -Path 'function:Pop-Migration') )
{
throw (@'
Pop-Migration function not found. All migrations are required to have a Pop-Migration function that contains at least one operation. Here's some sample code to get you started:
function Pop-Migration
{
Remove-Table 'LetsCreateATable'
}
'@)
return
}
Pop-Migration | Add-Operation -OperationsList $m.PopOperations
if( $m.PopOperations.Count -eq 0 )
{
throw (@'
Pop-Migration function is empty and contains no operations. Maybe you''d like to drop a table? Here's some sample code to get you started:
function Pop-Migration
{
Remove-Table 'LetsCreateATable'
}
'@)
}
$m
}
catch
{
Write-RivetError -Message ('Loading migration ''{0}'' failed' -f $m.Path) `
-CategoryInfo $_.CategoryInfo.Category `
-ErrorID $_.FullyQualifiedErrorID `
-Exception $_.Exception `
-CallStack ($_.ScriptStackTrace)
}
finally
{
Clear-Migration
}
} |
Where-Object { $_ -is [Rivet.Migration] }
}
end
{
}
}
Rivet\Functions\Disconnect-Database.ps1
function Disconnect-Database
{
param(
)
if( $Connection -and $Connection.State -ne [Data.ConnectionState]::Closed )
{
$Connection.Close()
}
}
Rivet\Functions\Export-Row.ps1
<#
.SYNOPSIS
Export rows from a database as a migration where those rows get added using the `Add-Row` operation.
.DESCRIPTION
When getting your database working with Rivet, you may want to get some data exported into an initial migration. This script does that.
.EXAMPLE
Export-Row -SqlServerName .\Rivet -DatabaseName 'Rivet' -SchemaName 'rivet' -TableName 'Migrations' -Column 'MigrationID','RunAtUtc'
Demonstrates how to export the `MigrationID` and `RunAtUtc` columns of the `rivet.Migrations` table from the `.\Rivet.Rivet` database
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The SQL Server to connect to.
$SqlServerName,
[Parameter(Mandatory=$true)]
[string]
# The name of the database.
$DatabaseName,
[string]
# The schema of the table.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string[]]
# The columns to export.
$Column,
[string]
# An orderBy clause to use to order the results.
$OrderBy
)
#Require -Version 3
Set-StrictMode -Version Latest
$connectionString = 'Server={0};Database={1};Integrated Security=True;' -f $SqlServerName,$DatabaseName
$connection = New-Object Data.SqlClient.SqlConnection $connectionString
$columnClause = $Column -join ', '
$query = 'select {0} from {1}.{2}' -f $columnClause,$SchemaName,$TableName
if( $OrderBy )
{
$query += ' order by {0}' -f $OrderBy
}
$cmd = New-Object Data.SqlClient.SqlCommand ($query,$connection)
$connection.Open()
try
{
' Add-Row -SchemaName ''{0}'' -TableName ''{1}'' -Column @('
$cmdReader = $cmd.ExecuteReader()
try
{
if( -not $cmdReader.HasRows )
{
return
}
while( $cmdReader.Read() )
{
' @{'
for ($i= 0; $i -lt $cmdReader.FieldCount; $i++)
{
if( $cmdReader.IsDbNull( $i ) )
{
continue
}
$name = $cmdReader.GetName( $i )
$value = $cmdReader.GetValue($i)
if( $value -is [Boolean] )
{
$value = if( $cmdReader.GetBoolean($i) ) { '1' } else { '0' }
}
elseif( $value -is [string] )
{
$value = "'{0}'" -f $value.ToString().Replace("'","''")
}
elseif( $value -is [DAteTime] -or $value -is [Guid] )
{
$value = "'{0}'" -f $value
}
else
{
$value = $value.ToString()
}
' {0} = {1};' -f $name,$value
}
' },'
}
}
finally
{
' )'
$cmdReader.Close()
}
}
finally
{
$cmd.Dispose()
$connection.Close()
}
Rivet\Functions\Get-Migration.ps1
function Get-Migration
{
<#
.SYNOPSIS
Gets the migrations for all or specific databases.
.DESCRIPTION
The `Get-Migration` function returns `Rivet.Migration` objects for all the migrations in all or specific databases. With no parameters, looks in the current directory for a `rivet.json` file and returns all the migrations for all the databases based on that configuration. Use the `ConfigFilePath` to load and use a specific `rivet.json` file.
You can return migrations from specific databases by passing those database names as values to the `Database` parameter.
The `Environment` parameter is used to load the correct environment-specific settings from the `rivet.json` file.
You can filter what migrations are returned using the `Include` or `Exclude` parameters, which support wildcards, and will match any part of the migration's filename, including the ID.
Use the `Before` and `After` parameters to return migrations whose timestamps/IDs come before and after the given dates.
.OUTPUTS
Rivet.Migration.
.EXAMPLE
Get-Migration
Returns `Rivet.Migration` objects for each migration in each database.
.EXAMPLE
Get-Migration -Database StarWars
Returns `Rivet.Migration` objects for each migration in the `StarWars` database.
.EXAMPLE
Get-Migration -Include 'CreateDeathStarTable','20150101000648','20150101150448_CreateRebelBaseTable','*Hoth*','20150707*'
Demonstrates how to get use the `Include` parameter to find migrations by name, ID, or file name. In this case, the following migrations will be returned:
* The migration whose name is `CreateDeathStarTable`.
* The migration whose ID is `20150101000648`.
* The migration whose full name is `20150101150448_CreateRebelBaseTable`.
* Any migration whose contains `Hoth`.
* Any migration created on July 7th, 2015.
.EXAMPLE
Get-Migration -Exclude 'CreateDeathStarTable','20150101000648','20150101150448_CreateRebelBaseTable','*Hoth*','20150707*'
Demonstrates how to get use the `Exclude` parameter to skip/not return certain migrations by name, ID, or file name. In this case, the following migrations will be *not* be returned:
* The migration whose name is `CreateDeathStarTable`.
* The migration whose ID is `20150101000648`.
* The migration whose full name is `20150101150448_CreateRebelBaseTable`.
* Any migration whose contains `Hoth`.
* Any migration created on July 7th, 2015.
#>
[CmdletBinding(DefaultParameterSetName='External')]
[OutputType([Rivet.Migration])]
param(
[Parameter(ParameterSetName='External')]
[string[]]
# The database whose migrations to get.np
$Database,
[Parameter(ParameterSetName='External')]
[string]
# The environment settings to use.
$Environment,
[Parameter(ParameterSetName='External')]
[string]
# The path to the rivet.json file to use. Defaults to `rivet.json` in the current directory.
$ConfigFilePath,
[string[]]
# A list of migrations to include. Matches against the migration's ID or Name or the migration's file name (without extension). Wildcards permitted.
$Include,
[string[]]
# A list of migrations to exclude. Matches against the migration's ID or Name or the migration's file name (without extension). Wildcards permitted.
$Exclude,
[DateTime]
# Only get migrations before this date. Default is all.
$Before,
[DateTime]
# Only get migrations after this date. Default is all.
$After
)
Set-StrictMode -Version 'Latest'
function Clear-Migration
{
('function:Push-Migration','function:Pop-Migration') |
Where-Object { Test-Path -Path $_ } |
Remove-Item -WhatIf:$false -Confirm:$false
}
Clear-Migration
$getRivetConfigParams = @{ }
if( $Database )
{
$getRivetConfigParams['Database'] = $Database
}
if( $ConfigFilePath )
{
$getRivetConfigParams['Path'] = $ConfigFilePath
}
if( $Environment )
{
$getRivetConfigParams['Environment'] = $Environment
}
$Configuration = Get-RivetConfig @getRivetConfigParams
if( -not $Configuration )
{
return
}
$getMigrationFileParams = @{}
@( 'Include', 'Exclude' ) | ForEach-Object {
if( $PSBoundParameters.ContainsKey($_) )
{
$getMigrationFileParams[$_] = $PSBoundParameters[$_]
}
}
Get-MigrationFile -Configuration $Configuration @getMigrationFileParams |
Where-Object {
if( $PSBoundParameters.ContainsKey( 'Before' ) )
{
$beforeTimestamp = [uint64]$Before.ToString('yyyyMMddHHmmss')
if( $_.MigrationID -gt $beforeTimestamp )
{
return $false
}
}
if( $PSBoundParameters.ContainsKey( 'After' ) )
{
$afterTimestamp = [uint64]$After.ToString('yyyyMMddHHmmss')
if( $_.MigrationID -lt $afterTimestamp )
{
return $false
}
}
return $true
} |
Convert-FileInfoToMigration -Configuration $Configuration
}
Rivet\Functions\Get-MigrationFile.ps1
function Get-MigrationFile
{
<#
.SYNOPSIS
Gets the migration script files.
#>
[CmdletBinding(DefaultParameterSetName='External')]
[OutputType([IO.FileInfo])]
param(
[Parameter(Mandatory=$true)]
[Rivet.Configuration.Configuration]
# The configuration to use.
$Configuration,
[Parameter(Mandatory=$true,ParameterSetName='ByPath')]
[string[]]
# The path to a migrations directory to get.
$Path,
[string[]]
# A list of migrations to include. Matches against the migration's ID or Name or the migration's file name (without extension). Wildcards permitted.
$Include,
[string[]]
# A list of migrations to exclude. Matches against the migration's ID or Name or the migration's file name (without extension). Wildcards permitted.
$Exclude
)
Set-StrictMode -Version Latest
$requiredMatches = @{ }
if( $PSBoundParameters.ContainsKey('Include') )
{
foreach( $includeItem in $Include )
{
if( -not [Management.Automation.WildcardPattern]::ContainsWildcardCharacters($includeItem) )
{
$requiredMatches[$includeItem] = $true
}
}
}
$foundMatches = @{ }
Invoke-Command -ScriptBlock {
if( $PSCmdlet.ParameterSetName -eq 'ByPath' )
{
$Path
}
else
{
$Configuration.Databases | Select-Object -ExpandProperty 'MigrationsRoot'
}
} |
ForEach-Object {
Write-Debug -Message $_
if( (Test-Path -Path $_ -PathType Container) )
{
Get-ChildItem -Path $_ -Filter '*_*.ps1'
}
elseif( (Test-Path -Path $_ -PathType Leaf) )
{
Get-Item -Path $_
}
} |
ForEach-Object {
if( $_.BaseName -notmatch '^(\d{14})_(.+)' )
{
Write-Error ('Migration {0} has invalid name. Must be of the form `YYYYmmddhhMMss_MigrationName.ps1' -f $_.FullName)
return
}
$id = [UInt64]$matches[1]
$name = $matches[2]
$_ |
Add-Member -MemberType NoteProperty -Name 'MigrationID' -Value $id -PassThru |
Add-Member -MemberType NoteProperty -Name 'MigrationName' -Value $name -PassThru
} |
Where-Object {
if( -not ($PSBoundParameters.ContainsKey( 'Include' )) )
{
return $true
}
$migration = $_
foreach( $includeItem in $Include )
{
$foundMatch = $migration.MigrationID -like $includeItem -or $migration.MigrationName -like $includeItem -or $migration.BaseName -like $includeItem
if( $foundMatch )
{
$foundMatches[$includeItem] = $true
return $true
}
}
return $false
} |
Where-Object {
if( -not ($PSBoundParameters.ContainsKey( 'Exclude' )) )
{
return $true
}
$migration = $_
$Exclude | Where-Object { $migration.MigrationID -notlike $_ -and $migration.MigrationName -notlike $_ -and $migration.BaseName -notlike $_ }
}
foreach( $requiredMatch in $requiredMatches.Keys )
{
if( -not $foundMatches.ContainsKey( $requiredMatch ) )
{
Write-Error ('Migration ''{0}'' not found.' -f $requiredMatch)
}
}
}
Rivet\Functions\Get-RivetConfig.ps1
function Get-RivetConfig
{
<#
.SYNOPSIS
Gets the configuration to use when running Rivet.
.DESCRIPTION
Rivet will look in the current directory for a `rivet.json` file.
.LINK
about_Rivet_Configuration
.EXAMPLE
Get-RivetConfig
Looks in the current directory for a `rivet.json` file, loads it, and returns an object representing its configuration.
.EXAMPLE
Get-RivetConfig -Path F:\etc\rivet
Demonstrates how to load a custom Rivet configuration file.
#>
[CmdletBinding()]
[OutputType([Rivet.Configuration.Configuration])]
param(
[Parameter()]
[string[]]
# The list of specific database names being operated on.
$Database,
[Parameter()]
# The name of the environment whose settings to return. If not provided, uses the default settings.
$Environment,
[Parameter()]
[string]
# The path to the Rivet configuration file to load. Defaults to `rivet.json` in the current directory.
$Path
)
Set-StrictMode -Version 'Latest'
function Resolve-RivetConfigPath
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[string]
# The path from the rivet config file to resolve.
$Path
)
process
{
if( [IO.Path]::IsPathRooted( $Path ) )
{
return [IO.Path]::GetFullPath( $Path )
}
$Path = Join-Path -Path $configRoot -ChildPath $Path
return [IO.Path]::GetFullPath( $Path )
}
}
filter Get-ConfigProperty
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The name of the property to get.
$Name,
[Switch]
# The configuration value is required.
$Required,
[Parameter(Mandatory=$true,ParameterSetName='AsInt')]
[Switch]
# Set the configuration value as an integer.
$AsInt,
[Parameter(Mandatory=$true,ParameterSetName='AsList')]
[Switch]
# Set the configuration value as a list of strings.
$AsList,
[Parameter(Mandatory=$true,ParameterSetName='AsPath')]
[Switch]
# Set the configuration value as a path.
$AsPath,
[Parameter(Mandatory=$true,ParameterSetName='AsString')]
[Switch]
# Set the configuration value as a string.
$AsString,
[Parameter(Mandatory=$true,ParameterSetName='AsHashtable')]
[Switch]
# Set the configuration value as a hashtable.
$AsHashtable
)
$value = $null
if( $rawConfig | Get-Member -Name $Name )
{
$value = $rawConfig.$Name
}
$env = Get-Environment
if( $env -and ($env | Get-Member -Name $Name))
{
$value = $env.$Name
}
if( -not $value )
{
if( $Required )
{
Write-ValidationError ('setting ''{0}'' is missing.' -f $Name)
}
return
}
switch ($PSCmdlet.ParameterSetName )
{
'AsInt'
{
if( -not ($value -is 'int') )
{
Write-ValidationError -Message ('setting ''{0}'' is invalid. It should be a positive integer.' -f $Name)
return
}
return $value
}
'AsList'
{
return [Object[]]$value
}
'AsPath'
{
$path = $value | Resolve-RivetConfigPath
if( -not (Test-Path -Path $path -PathType Container) )
{
Write-ValidationError ('path {0} ''{1}'' not found.' -f $Name,$path)
return
}
return $path
}
'AsString'
{
return $value
}
'AsHashtable'
{
$hashtable = @{ }
Get-Member -InputObject $value -MemberType NoteProperty |
ForEach-Object { $hashtable[$_.Name] = $value.($_.Name) }
return ,$hashtable
}
}
}
function Write-ValidationError
{
param(
[Parameter(Mandatory=$true,Position=1)]
[string]
# The error message to write.
$Message
)
$envMsg = ''
if( $Environment )
{
$envMsg = 'environment ''{0}'': ' -f $Environment
}
Write-Error -Message ('Invalid Rivet configuration file ''{0}'': {1}{2} See about_Rivet_Configuration for more information.' -f $Path,$envMsg,$Message)
}
function Get-Environment
{
if( $Environment )
{
if( ($rawConfig | Get-Member -Name 'Environments') -and
($rawConfig.Environments | Get-Member -Name $Environment) )
{
$rawConfig.Environments.$Environment
}
}
}
## If there is no $Path defined set $Path to current directory
if( -not $Path )
{
$Path = Get-Location | Select-Object -ExpandProperty 'ProviderPath'
$Path = Join-Path -Path $Path -ChildPath 'rivet.json'
}
if( -not [IO.Path]::IsPathRooted( $Path ) )
{
$Path = Join-Path -Path (Get-Location) -ChildPath $Path
}
$Path = [IO.Path]::GetFullPath( $Path )
## Check for existence of rivet.json
if( -not (Test-Path -Path $Path -PathType Leaf) )
{
Write-Error ('Rivet configuration file ''{0}'' not found.' -f $Path)
return
}
$configRoot = Split-Path -Parent -Path $Path
$rawConfig = Get-Content -Raw -Path $Path | ConvertFrom-Json
if( -not $rawConfig )
{
Write-Error -Message ('Rivet configuration file ''{0}'' contains invalid JSON.' -f $Path)
return
}
if( $Environment -and -not (Get-Environment) )
{
Write-Error ('Environment ''{0}'' not found in ''{1}''.' -f $Environment,$Path)
return
}
$errorCount = $Global:Error.Count
$sqlServerName = Get-ConfigProperty -Name 'SqlServerName' -Required -AsString
$dbsRoot = Get-ConfigProperty -Name 'DatabasesRoot' -Required -AsPath
$connectionTimeout = Get-ConfigProperty -Name 'ConnectionTimeout' -AsInt
if( $connectionTimeout -eq $null )
{
$connectionTimeout = 15
}
$commandTimeout = Get-ConfigProperty -Name 'CommandTimeout' -AsInt
if( $commandTimeout -eq $null )
{
$commandTimeout = 30
}
$pluginsRoot = Get-ConfigProperty -Name 'PluginsRoot' -AsPath
$ignoredDatabases = Get-ConfigProperty -Name 'IgnoreDatabases' -AsList
$targetDatabases = Get-ConfigProperty -Name 'TargetDatabases' -AsHashtable
if( $targetDatabases -eq $null )
{
$targetDatabases = @{ }
}
[Rivet.Configuration.Configuration]$configuration = New-Object 'Rivet.Configuration.Configuration' $Path,$Environment,$sqlServerName,$dbsRoot,$connectionTimeout,$commandTimeout,$pluginsRoot
if( $Global:Error.Count -ne $errorCount )
{
return
}
Invoke-Command {
# Get user-specified databases first
if( $Database )
{
$Database |
Add-Member -MemberType ScriptProperty -Name Name -Value { $this } -PassThru |
Add-Member -MemberType ScriptProperty -Name FullName -Value { Join-Path -Path $configuration.DatabasesRoot -ChildPath $this.Name } -PassThru
}
else
{
# Then get all of them
Get-ChildItem -Path $configuration.DatabasesRoot |
Where-Object { $_.PsIsContainer }
}
} |
Select-Object -Property Name,FullName -Unique |
Where-Object {
if( -not $ignoredDatabases )
{
return $true
}
$dbName = $_.Name
$ignore = $ignoredDatabases | Where-Object { $dbName -like $_ }
return -not $ignore
} |
ForEach-Object {
$dbName = $_.Name
if( $targetDatabases.ContainsKey( $dbName ) )
{
foreach( $targetDBName in $targetDatabases[$dbName] )
{
New-Object 'Rivet.Configuration.Database' $targetDBName,$_.FullName
}
}
else
{
New-Object 'Rivet.Configuration.Database' $dbName,$_.FullName
}
} |
ForEach-Object { $configuration.Databases.Add( $_ ) }
return $configuration
}
Rivet\Functions\Import-Plugin.ps1
function Import-Plugin
{
<#
.SYNOPSIS
Loads any plugins.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
# The path to load the plugins from.
$Path
)
Set-StrictMode -Version 'Latest'
if( -not (Test-Path -Path $Path -PathType Container) )
{
Write-Error ('Plugin path ''{0}'' not found.' -f $Path)
return
}
# Load known plug-ins.
$knownPlugins = @( 'Start-MigrationOperation', 'Complete-MigrationOperation' )
$knownPlugins |
ForEach-Object { Join-Path -Path 'function:' -ChildPath $_ } |
Where-Object { Test-Path -Path $_ } |
Remove-Item -WhatIf:$false
$expectedFunctions = @()
Get-ChildItem -Path $Path -Filter '*.ps1' -File |
ForEach-Object {
$expectedFunctions += $_
. $_.FullName
Join-Path -Path 'function:' -ChildPath $_.BaseName
} |
Where-Object { Test-Path -Path $_ } |
Get-Item |
ForEach-Object {
$_ | Remove-Item -WhatIf:$false
# Re-create the function in script scope.
Invoke-Expression -Command @"
function script:$($_.Name)
{
$($_.Definition)
}
"@
}
$expectedFunctions |
ForEach-Object {
$functionPath = Join-Path -Path 'function:' -ChildPath $_.BaseName
if( -not (Test-Path -Path $functionPath) )
{
Write-Error ('Plugin ''{0}'' not found. Expected file ''{1}'' to implement function ''{0}''.' -f $_.BaseName,$_.FullName)
}
}
}
Rivet\Functions\Initialize-Database.ps1
function Initialize-Database
{
<#
.SYNOPSIS
Intializes the database so that it can be migrated by Rivet.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Rivet.Configuration.Configuration]
$Configuration
)
Set-StrictMode -Version 'Latest'
$who = ('{0}\{1}' -f $env:USERDOMAIN,$env:USERNAME);
$migrationsPath = Join-Path -Path $PSScriptRoot -ChildPath '..\Migrations'
Write-Debug -Message ('# {0}.{1}' -f $Connection.DataSource,$Connection.Database)
Update-Database -Path $migrationsPath -RivetSchema -Configuration $Configuration
}
Rivet\Functions\Invoke-MigrationOperation.ps1
function Invoke-MigrationOperation
{
<#
.SYNOPSIS
Runs the SQL created by a `Rivet.Migration` object.
.DESCRIPTION
All Rivet migrations are described by instances of `Rivet.Migration` objects. These objects eventually make their way here, at which point they are converted to SQL, and executed.
.EXAMPLE
Invoke-Migration -Operation $operation
This example demonstrates how to call `Invoke-Migration` with a migration object.
#>
[CmdletBinding(DefaultParameterSetName='AsReader')]
param(
[Parameter(Mandatory=$true)]
[Rivet.Migration]
# The migration this operation is from.
$Migration,
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[Rivet.Operation]
# The migration object to invoke.
$Operation
)
begin
{
}
process
{
Set-StrictMode -Version 'Latest'
$optionalParams = @{ }
$nonQuery = $false
$asScalar = $false
if( $Operation.QueryType -eq [Rivet.OperationQueryType]::NonQuery )
{
$optionalParams['NonQuery'] = $true
$nonQuery = $true
}
elseif( $Operation.QueryType -eq [Rivet.OperationQueryType]::Scalar )
{
$optionalParams['AsScalar'] = $true
$asScalar = $true
}
$Operation.ToQuery() |
Split-SqlBatchQuery -Verbose:$false |
Where-Object { $_ } |
ForEach-Object {
$batchQuery = $_
$result = $null
$rowsAffected = -1
$rowCount = $null
try
{
if( $Operation -is [Rivet.Operations.RemoveRowOperation] -and $Operation.Truncate )
{
$rowCount = Invoke-Query -Query ('select count(*) from [{0}].[{1}]' -f $Operation.SchemaName,$Operation.TableName) -AsScalar
}
$result = Invoke-Query -Query $batchQuery -Parameter $Operation.Parameters -CommandTimeout $Operation.CommandTimeout @optionalParams
}
catch
{
Write-RivetError -Message ('Migration {0} failed' -f $migrationInfo.FullName) `
-CategoryInfo $_.CategoryInfo.Category `
-ErrorID $_.FullyQualifiedErrorID `
-Exception $_.Exception `
-CallStack ($_.ScriptStackTrace) `
-Query $batchQuery
throw (New-Object ApplicationException 'Migration failed.',$_.Exception)
}
if( $nonQuery )
{
if( $rowCount -eq $null )
{
$rowsAffected = $result
}
}
elseif( $asScalar )
{
if( $result -ne 0 )
{
if( $Operation -is [Rivet.Operations.UpdateCodeObjectMetadataOperation] )
{
$exMsg = "Failed to refresh {0}.{1}" -f $Operation.SchemaName,$Operation.Name
}
elseif( $Operation -is [Rivet.Operations.RenameColumnOperation] )
{
$exMsg = "Failed to rename column {0}.{1}.{2} to {0}.{1}.{3}" -f $Operation.SchemaName,$Operation.TableName,$Operation.Name,$Operation.NewName
}
elseif( $Operation -is [Rivet.Operations.RenameOperation] )
{
$exMsg = "Failed to rename object {0}.{1} to {0}.{2}" -f $Operation.SchemaName,$Operation.Name,$Operation.NewName
}
throw ('{0}: error code {1}' -f $exMsg,$result)
}
}
return New-Object 'Rivet.OperationResult' $Migration,$Operation,$batchQuery,$rowsAffected
}
}
end
{
}
}
Rivet\Functions\Invoke-Query.ps1
filter Invoke-Query
{
<#
.SYNOPSIS
Executes a SQL query against the database.
.DESCRIPTION
The `Invoke-Query` function runs arbitrary queries aginst the database. Queries are split on `GO` statements, and each query is sent individually to the database.
By default, rows are returned as anonymous PsObjects, with properties for each named column returned. Unnamed columns are given arbitrary `ColumnIdx` names, where `Idx` is a number the increments by one for each anonymous column, beginning with 0.
You can return the results as a scalar using the AsScalar parameter.
use the `NonQuery` switch to run non-queryies (e.g. `update`, `insert`, etc.). In this case, the number of rows affected by the query is returned.
Do not use this method to migrate/transform your database, or issue DDL queries! The queries issued by this function happen before the DDL applied by a migration's operations. Use the `Invoke-Ddl` function instead. If you need to dynamically migrate your database based on its state, use this function to query the state of the database, and the other Rivet operations to perform the migration.
You can pipe queries to this method, too!
.LINK
Invoke-Ddl
.EXAMPLE
Invoke-Query -Query 'create table rivet.Migrations( )'
Executes the create table syntax above against the database.
.EXAMPLE
Invoke-Query -Query 'select count(*) from MyTable' -Database MyOtherDatabase
Executes a query against the non-current database. Returns the rows as objects.
.EXAMPLE
'select count(*) from sys.tables' | Invoke-Query -AsScalar
Demonstrates how queries can be piped into `Invoke-Query`. Also shows how a result can be returned as a scalar.
#>
[CmdletBinding(DefaultParameterSetName='AsReader')]
param(
[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
[string]
$Query,
[Parameter()]
[Hashtable]
$Parameter,
[Parameter(Mandatory=$true,ParameterSetName='ExecuteScalar')]
[Switch]
$AsScalar,
[Parameter(Mandatory=$true,ParameterSetName='ExecuteNonQuery')]
[Switch]
$NonQuery,
[UInt32]
# The time in seconds to wait for the command to execute. The default is 30 seconds.
$CommandTimeout = 30
)
Set-StrictMode -Version 'Latest'
$Query |
Split-SqlBatchQuery -Verbose:$false |
Where-Object { $_ } |
ForEach-Object {
$queryBatch = $_
$cmd = New-Object 'Data.SqlClient.SqlCommand' ($queryBatch,$Connection,$Connection.Transaction)
$cmdStartedAt = [DateTime]::UtcNow
try
{
$cmd.CommandTimeout = $CommandTimeout
if( $Parameter )
{
$Parameter.Keys | ForEach-Object {
$name = $_
$value = $Parameter[$name]
if( -not $name.StartsWith( '@' ) )
{
$name = '@{0}' -f $name
}
[void] $cmd.Parameters.AddWithValue( $name, $value )
}
}
if( $PSCmdlet.ParameterSetName -eq 'ExecuteNonQuery' )
{
$cmd.ExecuteNonQuery()
}
elseif( $PSCmdlet.ParameterSetName -eq 'ExecuteScalar' )
{
$cmd.ExecuteScalar()
}
else
{
$cmdReader = $cmd.ExecuteReader()
try
{
if( $cmdReader.HasRows )
{
while( $cmdReader.Read() )
{
$row = @{ }
for ($i= 0; $i -lt $cmdReader.FieldCount; $i++)
{
$name = $cmdReader.GetName( $i )
if( -not $name )
{
$name = 'Column{0}' -f $i
}
$value = $cmdReader.GetValue($i)
if( $cmdReader.IsDBNull($i) )
{
$value = $null
}
$row[$name] = $value
}
New-Object PsObject -Property $row
}
}
}
finally
{
$cmdReader.Close()
}
}
}
finally
{
$cmd.Dispose()
$queryLines = $queryBatch -split ([TExt.RegularExpressions.Regex]::Escape([Environment]::NewLine))
Write-Verbose -Message ('{0,8} (ms) {1}' -f ([int]([DateTime]::UtcNow - $cmdStartedAt).TotalMilliseconds),($queryLines | Select-Object -First 1))
$queryLines | Select-Object -Skip 1 | ForEach-Object { Write-Verbose -Message ('{0} {1}' -f (' ' * 13),$_) }
}
}
}
Rivet\Functions\Invoke-Rivet.ps1
function Invoke-Rivet
{
[CmdletBinding(SupportsShouldProcess=$True)]
param(
[Parameter(Mandatory=$true,ParameterSetName='New')]
[Switch]
# Creates a new migration.
$New,
[Parameter(Mandatory=$true,ParameterSetName='Push')]
[Switch]
# Applies migrations.
$Push,
[Parameter(Mandatory=$true,ParameterSetName='Pop')]
[Parameter(Mandatory=$true,ParameterSetName='PopByCount')]
[Parameter(Mandatory=$true,ParameterSetName='PopByName')]
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Reverts migrations.
$Pop,
[Parameter(Mandatory=$true,ParameterSetName='Redo')]
[Switch]
# Reverts a migration, then re-applies it.
$Redo,
[Parameter(Mandatory=$true,ParameterSetName='New',Position=1)]
[Parameter(ParameterSetName='Push',Position=1)]
[Parameter(Mandatory=$true,ParameterSetName='PopByName',Position=1)]
[ValidateLength(1,241)]
[string[]]
# The name of the migrations to create, push, or pop. Matches against the migration's ID, Name, or file name (without extension). Wildcards permitted.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='PopByCount',Position=1)]
[UInt32]
# The number of migrations to pop. Default is 1.
$Count = 1,
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Pop all migrations
$All,
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Switch]
# Force popping a migration you didn't apply or that is old.
$Force,
[Parameter(ParameterSetName='New',Position=2)]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string[]]
# The database(s) to migrate. Optional. Will operate on all databases otherwise.
$Database,
[Parameter(ParameterSetName='New')]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string]
# The environment you're working in. Controls which settings Rivet loads from the `rivet.json` configuration file.
$Environment,
[Parameter(ParameterSetName='New')]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string]
# The path to the Rivet configuration file. Default behavior is to look in the current directory for a `rivet.json` file. See `about_Rivet_Configuration` for more information.
$ConfigFilePath
)
[Rivet.Configuration.Configuration]$settings = Get-RivetConfig -Database $Database -Path $ConfigFilePath -Environment $Environment
if( -not $settings.Databases )
{
Write-Error (@'
Found no databases to migrate. This can be a few things:
* There are no database directories in ''{0}''. Please create a database directory there or supply an explicit database name with the `Database` parameter.
* You supplied an explicit database name, but that database is on the ignore list. Remove it from the ignore list in ''{1}'' or enter a database name that isn't ignored.
* You supplied an explicit database name, but no directory for migrations exist on the file system (under {0}). Create a migrations directory or enter the name of a database that exists.
'@ -f $settings.DatabasesRoot,$settings.Path)
return
}
try
{
if( $PSCmdlet.ParameterSetName -eq 'New' )
{
$settings.Databases |
Select-Object -ExpandProperty 'MigrationsRoot' -Unique |
ForEach-Object { New-Migration -Name $Name -Path $_ }
return
}
foreach( $databaseItem in $settings.Databases )
{
$databaseName = $databaseItem.Name
$dbMigrationsPath = $databaseItem.MigrationsRoot
$result = Connect-Database -SqlServerName $settings.SqlServerName `
-Database $databaseName `
-ConnectionTimeout $settings.ConnectionTimeout
if( -not $result )
{
continue
}
try
{
Initialize-Database -Configuration $settings
$updateParams = @{
Path = $dbMigrationsPath;
Configuration = $settings;
}
if( -not (Test-Path -Path $dbMigrationsPath -PathType Container) )
{
Write-Warning ('{0} database migrations directory ({1}) not found.' -f $databaseName,$dbMigrationsPath)
continue
}
if( $PSBoundParameters.ContainsKey('Name') )
{
$updateParams.Name = $Name # Join-Path $dbMigrationsPath ("*_{0}.ps1" -f $Name)
}
Write-Debug -Message ('# {0}.{1}' -f $Connection.DataSource,$Connection.Database)
if( $pscmdlet.ParameterSetName -eq 'Push' )
{
Update-Database @updateParams
}
elseif( $pscmdlet.ParameterSetName -eq 'Pop' )
{
Update-Database -Pop -Count 1 -Force:$Force @updateParams
}
elseif( $pscmdlet.ParameterSetName -eq 'PopByName' )
{
Update-Database -Pop -Force:$Force @updateParams
}
elseif( $pscmdlet.ParameterSetName -eq 'PopByCount' )
{
Update-Database -Pop -Count $Count -Force:$Force @updateParams
}
elseif ( $pscmdlet.ParameterSetName -eq 'PopAll' )
{
Update-Database -Pop -All -Force:$Force @updateParams
}
elseif( $pscmdlet.ParameterSetName -eq 'Redo' )
{
Update-Database -Pop -Count 1 @updateParams
Update-Database @updateParams
}
}
catch
{
$firstException = $_.Exception
while( $firstException.InnerException )
{
$firstException = $firstException.InnerException
}
Write-Error ('{0} database migration failed: {1}.' -f $databaseName,$firstException.Message)
}
}
}
finally
{
Disconnect-Database
}
}
Set-Alias -Name rivet -Value Invoke-Rivet
Rivet\Functions\Merge-Migration.ps1
function Merge-Migration
{
<#
.SYNOPSIS
Creates a cumulative set of operations from migration scripts.
.DESCRIPTION
The `Merge-Migration` functions creates a cumulative set of migrations from migration scripts. If there are multiple operations across one or more migration scripts that touch the same database object, those changes are combined into one operation. For example, if you create a table in one migration, add a column in another migrations, then remove a column in a third migration, this function will output an operation that represents the final state for the object: a create table operation that includes the added column and doesn't include the removed column. In environments where tables are replicated, it is more efficient to modify objects once and have that change replicated once, than to have the same object modified multiple times and replicated multiple times.
This function returns `Rivet.Migration` objects. Each object will have zero or more operations in its `PushOperations` property. If there are zero operations, it means the original operation was consolidated into another migration. Each operation has `Source` member on it, which is a list of all the migrations that contributed to that operation.
.OUTPUTS
Rivet.Migration
.EXAMPLE
Get-Migration | Merge-Migration
Demonstrates how to run `Merge-Migration`. It is always used in conjunction with `Get-Migration`.
#>
[CmdletBinding()]
[OutputType([Rivet.Migration])]
param(
[Parameter(ValueFromPipeline=$true)]
[Rivet.Migration[]]
# The path to the rivet.json file to use. By default, it will look in the current directory.
$Migration
)
begin
{
Set-StrictMode -Version 'Latest'
function Get-ColumnIndex
{
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[string]
# The column to check for.
$Name,
[Parameter(Mandatory=$true)]
[Collections.Generic.List[Rivet.Column]]
[AllowEmptyCollection()]
# The column collection to modify
$List
)
$columnIdx = $null
for( $idx = 0; $idx -lt $List.Count; ++$idx )
{
if( $List[$idx].Name -eq $Name )
{
return $idx
}
}
}
filter Add-Column
{
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[Rivet.Column]
# The column to check for.
$Column,
[Parameter(Mandatory=$true)]
[Collections.Generic.List[Rivet.Column]]
[AllowEmptyCollection()]
# The column collection to modify
$List,
[Switch]
# Replace only, don't add.
$ReplaceOnly
)
$columnIdx = Get-ColumnIndex -Name $Column.Name -List $List
if( $columnIdx -eq $null )
{
if( -not $ReplaceOnly )
{
[void] $List.Add( $column )
return $true
}
}
else
{
$null = $List.RemoveAt( $columnIdx )
$List.Insert( $columnIdx, $column )
return $true
}
return $false
}
filter Remove-Column
{
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[string]
# The column to check for.
$Name,
[Parameter(Mandatory=$true)]
[Collections.Generic.List[Rivet.Column]]
[AllowEmptyCollection()]
# The column collection to modify
$List
)
$columnIdx = Get-ColumnIndex -Name $Name -List $List
if( $columnIdx -ne $null )
{
[void] $List.RemoveAt( $columnIdx )
return $true
}
return $false
}
$databaseName = $null
$migrations = New-Object 'Collections.Generic.List[Rivet.Migration]'
[Collections.Generic.Hashset[string]]$preExistingObjects = $null
[Collections.Generic.HashSet[string]]$newTables = $null
[Collections.ArrayList]$operations = $null
function Reset-OperationState
{
Set-Variable -Name 'newTables' -Scope 1 -Value (New-Object 'Collections.Generic.HashSet[string]')
# list of every single operation we encounter across migrations
Set-Variable -Name 'operations' -Scope 1 -Value (New-Object 'Collections.ArrayList')
Set-Variable -Name 'preExistingObjects' -Scope 1 -Value (New-Object 'Collections.Generic.Hashset[string]')
}
}
process
{
#$DebugPreference = 'Continue'
foreach( $currentMigration in $Migration )
{
if( $databaseName -ne $Migration.Database )
{
Reset-OperationState
$databaseName = $Migration.Database
}
function Register-Source
{
[CmdletBinding()]
param(
[Rivet.Operation]
$Operation
)
Set-StrictMode -Version 'Latest'
#$DebugPreference = 'SilentlyContinue'
Write-Debug -Message ('Current Migration Name: <{0}>' -f $migrationName)
Write-Debug -Message ('Operation Type: <{0}>' -f $Operation.GetType().FullName)
Write-Debug -Message ('Start Source Count: <{0}>' -f $Operation.Source.Count)
foreach( $source in $Operation.Source )
{
Write-Debug -Message ('Source Migration Name: <{0}>' -f $source.FullName)
if( $source.FullName -eq $migrationName )
{
return
}
}
$Operation.Source.Add( $currentMigration )
Write-Debug -Message ('End Source Count: <{0}>' -f $Operation.Source.Count)
}
$migrationName = '{0}_{1}' -f $currentMigration.ID,$currentMigration.Name
Write-Debug -Message ('{0}' -f $currentMigration.Path)
$migrations.Add( $currentMigration )
$pushOpIdx = 0
for( $pushOpIdx = 0; $pushOpIdx -lt $currentMigration.PushOperations.Count; ++$pushOpIdx )
{
function Remove-CurrentOperation
{
if( $pushOpIdx -ge $currentMigration.PushOperations.Count )
{
Write-Debug 'WTF?!'
}
$operation = $currentMigration.PushOperations[$pushOpIdx]
$currentMigration.PushOperations.RemoveAt( $pushOpIdx )
Set-Variable -Name 'pushOpIdx' -Scope 1 -Value ($pushOpIdx - 1)
Remove-Operation $operation
}
function Remove-Operation
{
param(
$Operation
)
for( $idx = $operations.Count - 1; $idx -ge 0; --$idx )
{
if( $operations[$idx] -eq $Operation )
{
$operations.RemoveAt( $idx )
return
}
}
}
function Remove-OperationFromMigration
{
param(
[Rivet.Operation]
$Operation
)
$opIdx = -1
$originalMigration = $Operation.Source[0]
$ops = $originalMigration.PushOperations
for( $idx = 0; $idx -lt $ops.Count; ++$idx )
{
if( $ops[$idx] -eq $Operation )
{
$opIdx = $idx
}
}
if( $opIdx -gt -1 )
{
$ops.RemoveAt( $opIdx )
if( $originalMigration -eq $currentMigration )
{
Set-Variable -Name 'pushOpIdx' -Scope 1 -Value ($pushOpIdx - 1)
}
Remove-Operation $Operation
}
}
$op = $currentMigration.PushOperations[$pushOpIdx]
$previousObjectOps = $operations |
Where-Object { $_ } |
Where-Object { Get-Member -InputObject $_ -Name 'ObjectName' } |
Where-Object { Get-Member -InputObject $op -Name 'ObjectName' } |
Where-Object { $_.ObjectName -eq $op.ObjectName }
[void]$operations.Add( $op )
$source = New-Object -TypeName 'Collections.Generic.List[Rivet.Migration]'
$op | Add-Member -Name 'Source' -MemberType NoteProperty -Value $source
Register-Source $op
if( $op -is [Rivet.Operations.AddTableOperation] )
{
[void]$newTables.Add( $op.ObjectName )
continue
}
if( $op -is [Rivet.Operations.RenameColumnOperation] )
{
$tableName = '{0}.{1}' -f $op.SchemaName,$op.TableName
for( $idx = 0 ; $idx -lt $operations.Count; ++$idx )
{
$tableOp = $operations[$idx]
if( $tableOp -isnot [Rivet.Operations.AddTableOperation] )
{
continue
}
if( $tableOp.ObjectName -ne $tableName )
{
continue
}
$originalColumn = $tableOp.Columns | Where-Object { $_.Name -eq $op.Name }
if( $originalColumn )
{
$originalColumn.Name = $op.NewName
Register-Source $tableOp
Remove-CurrentOperation
continue
}
}
}
if( $op -is [Rivet.Operations.RenameOperation] )
{
$objectName = '{0}.{1}' -f $op.SchemaName,$op.Name
for( $idx = 0 ; $idx -lt $operations.Count; ++$idx )
{
$existingOp = $operations[$idx]
if( $existingOp -isnot [Rivet.Operations.AddTableOperation] )
{
continue
}
if( $existingOp.ObjectName -ne $objectName )
{
continue
}
$existingOp.Name = $op.NewName
Register-Source $existingOp
Remove-CurrentOperation
continue
}
}
if( $op -isnot [Rivet.Operations.ObjectOperation] )
{
continue
}
$opTypeName = $op.GetType().Name
$isRemoveOperation = $opTypeName -like 'Remove*'
# If the first action against this object is a removal
if( $isRemoveOperation -and -not $previousObjectOps -and $op -is [Rivet.Operations.ObjectOperation] )
{
[void]$preexistingObjects.Add( $op.ObjectName )
}
foreach( $existingOp in $previousObjectOps )
{
if( $existingOp -eq $op )
{
continue
}
Register-Source $existingOp
if( $isRemoveOperation )
{
for( $idx = 0; $idx -lt $operations.Count; ++$idx )
{
if( $operations[$idx] -eq $existingOp )
{
$operations[$idx] = $null
}
}
$originalMigration = $existingOp.Source[0]
# Remove the original add operation from its migration
Remove-OperationFromMigration -Operation $existingOp
if( $op -is [Rivet.Operations.RemoveTableOperation] )
{
for( $idx = $operations.Count - 1; $idx -ge 0 ; --$idx )
{
$tableOp = $operations[$idx]
if( $tableOp -and ($tableOp | Get-Member 'ObjectName') )
{
Write-Debug $tableOp.ObjectName
}
if( $tableOp -isnot [Rivet.Operations.TableObjectOperation] )
{
continue
}
Write-Debug ' is a table object operation'
$tableOpTableName = '{0}.{1}' -f $tableOp.SchemaName,$tableOp.TableName
if( $op.ObjectName -eq $tableOpTableName )
{
Remove-OperationFromMigration -Operation $tableOp
}
}
}
if( $existingOp -and -not $preexistingObjects.Contains($existingOp.ObjectName) )
{
Remove-CurrentOperation
}
continue
}
elseif( $opTypeName -eq 'UpdateTableOperation' )
{
if( $existingOp -is [Rivet.Operations.AddTableOperation] )
{
$op.AddColumns | Add-Column -List $existingOp.Columns | Out-Null
$op.UpdateColumns | Add-Column -List $existingOp.Columns | Out-Null
$op.RemoveColumns | Remove-Column -List $existingOp.Columns | Out-Null
Remove-CurrentOperation
}
elseif( $existingOp -is [Rivet.Operations.UpdateTableOperation] )
{
if( $op.AddColumns -and $op.AddColumns.Count -gt 0 )
{
# If adding a column that was previously removed, remove the removal
$op.AddColumns |
Select-Object -ExpandProperty 'Name' |
ForEach-Object {
$columnName = $_
$columnIdx = -1
for( $idx = 0; $idx -lt $existingOp.RemoveColumns.Count; ++$idx )
{
if( $existingOp.RemoveColumns[$idx] -eq $columnName )
{
$columnIdx = $idx
break
}
}
if( $columnIdx -ge 0 )
{
$existingOp.RemoveColumns.RemoveAt( $columnIdx )
}
}
# Add new columns to the original operation
for( $colIdx = 0; $colIdx -lt $op.AddColumns.Count; ++$colIdx )
{
Add-Column -Column $op.AddColumns[$colIdx] -List $existingOp.AddColumns | Out-Null
$op.AddColumns.RemoveAt( $colIdx-- )
}
}
# Replace existing column definitions
for( $colIdx = 0; $colIdx -lt $op.UpdateColumns.Count; ++$colIdx )
{
$column = $op.UpdateColumns[$colIdx]
$moved = Add-Column -Column $column -List $existingOp.AddColumns -ReplaceOnly
$moved = $moved -or (Add-Column -Column $column -List $existingOp.UpdateColumns)
if( $moved )
{
$op.UpdateColumns.RemoveAt( $colIdx-- )
}
}
# Remove columns
for( $colIdx = 0; $colIdx -lt $op.RemoveColumns.Count; ++$colIdx )
{
$columnName = $op.RemoveColumns[$colIdx]
# Remove a column we previously added
$removedFromAddedColumns = Remove-Column -List $existingOp.AddColumns -Name $columnName
$removedFromUpdatedColumns = Remove-Column -List $existingOp.UpdateColumns -Name $columnName
$op.RemoveColumns.RemoveAt( $colIdx-- )
if( -not ($removedFromAddedColumns -or $removedFromUpdatedColumns) )
{
[void] $existingOp.RemoveColumns.Add( $columnName )
}
}
if( -not $op.ToQuery() )
{
Remove-CurrentOperation
}
if( -not $existingOp.ToQuery() )
{
Remove-OperationFromMigration -Operation $existingOp
}
}
else
{
Write-Error ('Unhandled operation of type ''{0}''.' -f $existingOp.GetType())
}
continue
}
}
}
}
}
end
{
$migrations.ToArray()
}
}
Rivet\Functions\New-Migration.ps1
function New-Migration
{
<#
.SYNOPSIS
Creates a new migration script.
.DESCRIPTION
Creates a migration script with a given name. The script is prefixed with the current timestamp (e.g. yyyyMMddHHmmss). The script is created in `$Path\$Database\Migrations`.
#>
param(
[Parameter(Mandatory=$true)]
[string[]]
# The name of the migration to create.
$Name,
[Parameter(Mandatory=$true)]
[string]
# The path to the directory where the migration should be saved.
$Path
)
foreach( $nameItem in $Name )
{
$id = $null
$id = [int64](Get-Date).ToString('yyyyMMddHHmmss')
while( (Test-Path -Path $Path -PathType Container) -and `
(Get-ChildItem -Path $Path -Filter ('{0}_*' -f $id) ) )
{
$id++
}
$filename = '{0}_{1}.ps1' -f $id,$nameItem
$importRivetPath = Join-Path -Path $PSScriptRoot -ChildPath '..\Import-Rivet.ps1' -Resolve
$migrationPath = Join-Path -Path $Path -ChildPath $filename
$migrationPath = [IO.Path]::GetFullPath( $migrationPath )
New-Item -Path $migrationPath -Force -ItemType File
$template = @"
<#
Your migration is ready to go! For the best development experience, please
write your migration in the PowerShell 3 ISE. Run the following at a
PowerShell prompt:
PS> ise "{0}"
or right-click the migration in Windows Explorer and choose "Edit".
The PowerShell ISE gives you intellisense, auto-complete, and other features
you may be used to from the Visual Studio IDE. Use this command in the ISE to
import Rivet and get intellisense/auto-complete:
PSISE> {1}
The ISE has a "Show Command" add-on which will let you build your migration
with a GUI. Once you've got Rivet imported, choose View > Show Command Add-on.
When the Show Command Add-on appears, choose 'Rivet' from the module. Click on
a migration operation to build it with the Show Command GUI.
#>
function Push-Migration
{{
}}
function Pop-Migration
{{
}}
"@ -f $migrationPath,$importRivetPath
$template | Set-Content -Path $migrationPath
}
}
Rivet\Functions\New-MigrationObject.ps1
function New-MigrationObject
{
<#
.SYNOPSIS
Creates a new `Rivet.Migration` object, suitable for passing to `Invoke-Migration` function.
.DESCRIPTION
All migrations in Rivet should be represented as an object. Each object should inherit from `Rivet.Migration`. This method returns an empty `Rivet.Migration` object, which is typically used to create migration-specific properties/methods.
.EXAMPLE
$migration = New-MigrationObject
Returns a `Rivet.Migration` object.
#>
[CmdletBinding()]
param(
[Parameter()]
[hashtable]
# The properties on the object.
$Property,
[Parameter(Mandatory=$true)]
[ScriptBlock]
# The script block to execute as the ToQuery method.
$ToQueryMethod
)
$o = New-Object 'Rivet.Migration' '','','',''
$Property.Keys |
ForEach-Object { $o | Add-Member -MemberType NoteProperty -Name $_ -Value $Property.$_ }
$o |
Add-Member -MemberType ScriptMethod -Name 'ToQuery' -Value $ToQueryMethod -PassThru
}
Rivet\Functions\Operations\Add-CheckConstraint.ps1
function Add-CheckConstraint
{
<#
.SYNOPSIS
Add a check constraint to a table.
.DESCRIPTION
Check constraints add validation for data in columns.
.EXAMPLE
Add-CheckConstraint 'Migrations' 'CK_Migrations_MigrationID' 'MigrationID > 0'
Demonstrates how to add a check constraint to a column that requires the value to be greater than 0.
.EXAMPLE
Add-CheckConstraint 'Migrations' 'CK_Migrations_MigrationID' 'MigrationID > 0' -NoCheck
Demonstrates how to add a check constraint to a column without validating the current contents of the table against this check.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the check constraint's table.
$TableName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the check constraint.
$Name,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The expression to use for the constraint.
$Expression,
[Switch]
# Don't use the check constraint when inserting, updating, or deleting rows during replication.
$NotForReplication,
[Switch]
# Specifies that the data in the table is not validated against a newly added CHECK constraint. If not specified, WITH CHECK is assumed for new constraints.
$NoCheck
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddCheckConstraintOperation' $SchemaName, $TableName, $Name, $Expression, $NotForReplication, $NoCheck
}
Rivet\Functions\Operations\Add-DataType.ps1
function Add-DataType
{
<#
.SYNOPSIS
Creates an alias or user-defined type.
.DESCRIPTION
There are three different user-defined data types. The first is an alias, from a name you choose to a system datatype. The second is an assembly type, which uses a type stored in a .NET assembly. The third is a table data type, which create a type for a table.
.LINK
Remove-DataType
.LINK
http://technet.microsoft.com/en-us/library/ms175007.aspx
.EXAMPLE
Add-DataType 'GUID' 'uniqueidentifier'
Demonstrates how to create a new alias data type called `GUID` which aliases the system `uniqueidentifier`.
.EXAMPLE
Add-DataType 'Names' -AsTable { varchar 'Name' 50 } -TableConstraint 'primary key'
Demonstrates how to create a new table-based data type.
.EXAMPLE
Add-DataType 'RivetDateTime' -AssemblyName 'Rivet' -ClassName 'Rivet.RivetDateTime'
Demonstrates how to create a `RivetDateTime` type that references the `Rivet.RivetDateTime` class. The `Rivet` assembly must first be registered using `create assembly`.
#>
[CmdletBinding(DefaultParameterSetName='From')]
param(
[Parameter()]
[string]
# The schema for the type. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the type.
$Name,
[Parameter(Mandatory=$true,Position=1,ParameterSetName='From')]
[string]
# The system type to alias.
$From,
[Parameter(Mandatory=$true,ParameterSetName='Assembly')]
[string]
# The name of the assembly for the type's implementation.
$AssemblyName,
[Parameter(Mandatory=$true,ParameterSetName='Assembly')]
[string]
# The name of the type's class implementation.
$ClassName,
[Parameter(Mandatory=$true,ParameterSetName='AsTable')]
[ScriptBlock]
# A `ScriptBlock` which returns columns for the table.
$AsTable,
[Parameter(ParameterSetName='AsTable')]
[string[]]
# A list of table constraints for a table-based data type.
$TableConstraint
)
Set-StrictMode -Version 'Latest'
if ($PsCmdlet.ParameterSetName -eq 'From')
{
$op = New-Object 'Rivet.Operations.AddDataTypeOperation' $SchemaName, $Name, $From
}
if ($PsCmdlet.ParameterSetName -eq 'Assembly')
{
$op = New-Object 'Rivet.Operations.AddDataTypeOperation' $SchemaName, $Name, $AssemblyName, $ClassName
}
if ($PsCmdlet.ParameterSetName -eq 'AsTable')
{
# Process Column Scriptblock -> Rivet.Column[]
[Rivet.Column[]]$columns = & $AsTable
$op = New-Object 'Rivet.Operations.AddDataTypeOperation' $SchemaName, $Name, $columns, ([string[]]$TableConstraint)
}
return $op
}
Rivet\Functions\Operations\Add-DefaultConstraint.ps1
function Add-DefaultConstraint
{
<#
.SYNOPSIS
Creates a Default constraint to an existing column
.DESCRIPTION
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.
.LINK
Add-DefaultConstraint
.EXAMPLE
Add-DefaultConstraint -TableName Cars -ColumnName Year -Expression '2015'
Adds an Default constraint on column 'Year' in the table 'Cars'
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the target table.
$TableName,
[Parameter()]
[string]
# The schema name of the target table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The column on which to add the default constraint
$ColumnName,
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name,
[Parameter(Mandatory=$true,Position=2)]
[string]
#The default expression
$Expression,
[Switch]
# WithValues
$WithValues
)
Set-StrictMode -Version 'Latest'
if ($PSBoundParameters.containskey("Name"))
{
New-Object 'Rivet.Operations.AddDefaultConstraintOperation' $SchemaName, $TableName, $Expression, $ColumnName, $Name, $WithValues
}
else
{
New-Object 'Rivet.Operations.AddDefaultConstraintOperation' $SchemaName, $TableName, $Expression, $ColumnName, $WithValues
}
}
Rivet\Functions\Operations\Add-Description.ps1
function Add-Description
{
<#
.SYNOPSIS
Adds the `MS_Description` extended property to a table or column.
.DESCRIPTION
The `sys.sp_addextendedproperty` stored procedure is used to set a table/column's description (i.e. the `MS_Description` extended property), but the syntax is weird. This function hides that weirdness from you. You're welcome.
.EXAMPLE
Add-Description -Description 'Whoseit's whatsits table.' -TableName WhoseitsWhatsits
Adds a description (i.e. the `MS_Description` extended property) on the `WhoseitsWhatsits` table.
.EXAMPLE
Add-Description -Description 'Is it a snarfblat?' -TableName WhoseitsWhatsits -ColumnName IsSnarfblat
Adds a description (i.e. the `MS_Description` extended property) on the `WhoseitsWhatsits` table's `IsSnarfblat` column.
.EXAMPLE
Add-Description -Description 'Whoseit's whatsits table.' -TableName WhoseitsWhatsits -ForTable
PowerShell v2.0 doesn't parse the parameters correctly when setting a table name, so you have to explicitly tell it what to do. Upgrade to PowerShell 3!
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The value for the MS_Description extended property.
$Description,
[Alias('Schema')]
[string]
# The schema. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true)]
[Alias('Table')]
[string]
# The name of the table where the extended property is getting set.
$TableName,
[Parameter(ParameterSetName='ForColumn')]
[Alias('Column')]
[string]
# The name of the column where the extended property is getting set.
$ColumnName
)
$optionalArgs = @{ }
if( $ColumnName )
{
$optionalArgs.ColumnName = $ColumnName
}
Add-ExtendedProperty -Name 'MS_Description' `
-Value $Description `
-SchemaName $SchemaName `
-TableName $TableName `
@optionalArgs
}
Rivet\Functions\Operations\Add-ExtendedProperty.ps1
function Add-ExtendedProperty
{
<#
.SYNOPSIS
Adds an extended property for a schema, table, view or column.
.DESCRIPTION
SQL Server has a special stored procedure for adding extended property metatdata about an object. Unfortunately, it has a really clunky interface. This function is an attempt to wrap `sp_addextendedproperty` with a saner interface.
Currently, this function only supports adding properties for schemas, tables, and columns. Submit a patch!
.LINK
Add-Description
.LINK
Remove-Description
.LINK
Remove-ExtendedProperty
.LINK
Update-Description
.LINK
Update-ExtendedProperty
.EXAMPLE
Add-ExtendedProperty -Name 'Deploy' -Value 'TRUE' -SchemaName 'spike'
Adds custom `Deploy` metadata for the `spike` schema.
.EXAMPLE
Add-ExtendedProperty -Name 'Deploy' -Value 'TRUE' -TableName 'Food'
Adds custom `Deploy` metadata on the `Food` table in the `dbo` schema.
.EXAMPLE
Add-ExtendedProperty -Name 'IsEncrypted' -Value 'FALSE' -TableName 'User' -ColumnName 'Password'
Adds custom `IsEncrypted` metadata on the `User` table's `Password` column.
.EXAMPLE
Add-ExtendedProperty -Name 'ContainsPII' -Value 'FALSE' -View 'LoggedInUsers'
Demonstrates how to add custom metadata on the `LoggedInUsers` view
.EXAMPLE
Add-ExtendedProperty -Name 'IsEncrypted' -Value 'FALSE' -View 'LoggedInUsers' -ColumnName 'Password'
Demonstrates how to add custom metadata for a view's column
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the extended property to add.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[AllowNull()]
# The value of the extended property.
$Value,
[Parameter(ParameterSetName='SCHEMA')]
[Parameter(ParameterSetName='TABLE')]
[Parameter(ParameterSetName='TABLE-COLUMN')]
[Parameter(ParameterSetName='VIEW')]
[Parameter(ParameterSetName='VIEW-COLUMN')]
[string]
# The schema of the object.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,ParameterSetName='TABLE')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Table')]
[string]
# The table name.
$TableName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW')]
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Alias('View')]
[string]
# The table name.
$ViewName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Column')]
[string]
# The column name.
$ColumnName
)
Set-StrictMode -Version 'Latest'
if ($PsCmdlet.ParameterSetName -eq "SCHEMA")
{
$op = New-Object 'Rivet.Operations.AddExtendedPropertyOperation' $SchemaName, $Name, $Value
}
if ($PsCmdlet.ParameterSetName -eq "TABLE")
{
$op = New-Object 'Rivet.Operations.AddExtendedPropertyOperation' $SchemaName, $TableName, $Name, $Value, $false
}
if ($PsCmdlet.ParameterSetName -eq "VIEW")
{
$op = New-Object 'Rivet.Operations.AddExtendedPropertyOperation' $SchemaName, $ViewName, $Name, $Value, $true
}
if ($PsCmdlet.ParameterSetName -eq "TABLE-COLUMN")
{
$op = New-Object 'Rivet.Operations.AddExtendedPropertyOperation' $SchemaName, $TableName, $ColumnName, $Name, $Value, $false
}
if ($PsCmdlet.ParameterSetName -eq "VIEW-COLUMN")
{
$op = New-Object 'Rivet.Operations.AddExtendedPropertyOperation' $SchemaName, $ViewName, $ColumnName, $Name, $Value, $true
}
return $op
}
Rivet\Functions\Operations\Add-ForeignKey.ps1
function Add-ForeignKey
{
<#
.SYNOPSIS
Adds a foreign key to an existing table that doesn't have a foreign key constraint.
.DESCRIPTION
Adds a foreign key to a table. The table/column that the foreign key references must have a primary key. If the table already has a foreign key, make sure to remove it with `Remove-ForeignKey`.
.LINK
Add-ForeignKey
.EXAMPLE
Add-ForeignKey -TableName Cars -ColumnName DealerID -References Dealer -ReferencedColumn DealerID
Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table.
.EXAMPLE
Add-ForeignKey -TableName 'Cars' -ColumnName 'DealerID' -References 'Dealer' -ReferencedColumn 'DealerID' -OnDelete 'CASCADE' -OnUpdate 'CASCADE' -NotForReplication
Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table with the options to cascade on delete and update, and also set notforreplication
.EXAMPLE
Add-ForeignKey -TableName Cars -ColumnName DealerID -References Dealer -ReferencedColumn DealerID -NoCheck
Adds a foreign key to the 'Cars' table on the 'DealerID' column that references the 'DealerID' column on the 'Dealer' table without validating the current contents of the table against this key.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table to alter.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string[]]
# The column(s) that should be part of the foreign key.
$ColumnName,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The table that the foreign key references
$References,
[Parameter()]
[string]
# The schema name of the reference table. Defaults to `dbo`.
$ReferencesSchema = 'dbo',
[Parameter(Mandatory=$true,Position=3)]
[string[]]
# The column(s) that the foreign key references
$ReferencedColumn,
[Parameter()]
[string]
# Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
$OnDelete,
[Parameter()]
[string]
# Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.
$OnUpdate,
[Parameter()]
[switch]
# Can be specified for FOREIGN KEY constraints and CHECK constraints. If this clause is specified for a constraint, the constraint is not enforced when replication agents perform insert, update, or delete operations.
$NotForReplication,
[Parameter()]
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name,
[Switch]
# Specifies that the data in the table is not validated against a newly added FOREIGN KEY constraint. If not specified, WITH CHECK is assumed for new constraints.
$NoCheck
)
Set-StrictMode -Version Latest
$source_columns = $ColumnName -join ','
$ref_columns = $ReferencedColumn -join ','
if ($PSBoundParameters.containskey("Name"))
{
New-Object 'Rivet.Operations.AddForeignKeyOperation' $SchemaName, $TableName, $ColumnName, $ReferencesSchema, $references, $ReferencedColumn, $Name, $OnDelete, $OnUpdate, $NotForReplication, $NoCheck
}
else
{
New-Object 'Rivet.Operations.AddForeignKeyOperation' $SchemaName, $TableName, $ColumnName, $ReferencesSchema, $references, $ReferencedColumn, $OnDelete, $OnUpdate, $NotForReplication, $NoCheck
}
}
Rivet\Functions\Operations\Add-PrimaryKey.ps1
function Add-PrimaryKey
{
<#
.SYNOPSIS
Adds a primary key to an existing table that doesn't have a primary key.
.DESCRIPTION
Adds a primary key to a table. If the table already has a primary key, make sure to remove it with `Remove-PrimaryKey`.
.LINK
Remove-PrimaryKey
.EXAMPLE
Add-PrimaryKey -TableName Cars -ColumnName Year,Make,Model
Adds a primary key to the `Cars` table on the `Year`, `Make`, and `Model` columns.
.EXAMPLE
Add-PrimaryKey -TableName Cars -ColumnName Year,Make,Model -NonClustered -Option 'IGNORE_DUP_KEY = ON','DROP_EXISTING=ON'
Demonstrates how to create a non-clustered primary key, with some index options.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string[]]
# The column(s) that should be part of the primary key.
$ColumnName,
[Switch]
# Create a non-clustered primary key.
$NonClustered,
[string[]]
# An array of primary key options.
$Option,
[Parameter()]
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name
)
Set-StrictMode -Version 'Latest'
$columns = $ColumnName -join ','
if ($PSBoundParameters.ContainsKey("Name"))
{
New-Object 'Rivet.Operations.AddPrimaryKeyOperation' $SchemaName, $TableName, $ColumnName, $Name, $NonClustered, $Option
}
else
{
New-Object 'Rivet.Operations.AddPrimaryKeyOperation' $SchemaName, $TableName, $ColumnName, $NonClustered, $Option
}
}
Rivet\Functions\Operations\Add-Row.ps1
function Add-Row
{
<#
.SYNOPSIS
Inserts a row of data in a table.
.DESCRIPTION
To specify which columns to insert into the new row, pass a hashtable as a value to the `Column` parameter. This hashtable should have keys that map to column names, and the value of each key will be used as the value for that column in the row.
.EXAMPLE
Add-Row -SchemaName 'rivet' 'Migrations' @{ ID = 2013093131104 ; Name = 'AMadeUpMigrationDoNotDoThis' ; Who = 'abadbadman' ; ComputerName 'abadbadcomputer' }
Demonstrates how to insert a row into the `rivet.Migrations` table. This is for illustrative purposes only. If you do this yourself, a butterfly loses its wings.
.EXAMPLE
Add-Row 'Cars' @( @{ Make = 'Toyota' ; Model = 'Celica' }, @{ Make = 'Toyota' ; Model = 'Camry' } )
Demonstrates how to insert multiple rows into a table by passing an array of hashtables.
.EXAMPLE
@( @{ Make = 'Toyota' ; Model = 'Celica' }, @{ Make = 'Toyota' ; Model = 'Camry' } ) | New-Row 'Cars'
Demonstrates how to pipe data into `New-Row` to insert a bunch of rows into the database.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ValueFromPipeline=$true)]
[Hashtable[]]
# A hashtable of name/value pairs that map to column names/values that will inserted.
$Column,
[Switch]
# Allow inserting identies.
$IdentityInsert
)
process
{
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddRowOperation' $SchemaName, $TableName, $Column, $IdentityInsert
}
}
Rivet\Functions\Operations\Add-RowGuidCol.ps1
function Add-RowGuidCol
{
<#
.SYNOPSIS
Adds the `rowguidcol` property to a column in a table.
.DESCRIPTION
The `Add-RowGuidCol` operation adds the `rowguidcol` property to a `uniqueidentifier` column in a table. A table can only have one `rowguidcol` column. If a table has an existing `rowguidcol` column, use `Remove-RowGuidCol` to remove it before adding a new one.
The `Add-RowGuidCol` operation was added in Rivet 0.7.
.LINK
https://msdn.microsoft.com/en-us/library/ms190273.aspx
.LINK
Remove-RowGuidCol
.EXAMPLE
Add-RowGuidCol -TableName 'MyTable' -ColumnName 'MyUniqueIdentifier'
Demonstrates how to add the `rowguidcol` property to a column in a table. In this example, the `dbo.MyTable` table's `MyUniqueIdentifier` column will get the propery.
.EXAMPLE
Add-RowGuidCol -SchemaName 'cstm' -TableName 'MyTable' -ColumnName 'MyUniqueIdentifier'
Demonstrates how to add the `rowguidcol` property to a column in a table whose schema isn't `dbo`, in this case the `cstm.MyTable` table's `MyUniqueIdentifier` column will get the property.
#>
[CmdletBinding()]
param(
[string]
# The table's schema. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=0)]
[string]
# The table's name.
$TableName,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the column that should get the `rowguidcol` property.
$ColumnName
)
Set-StrictMode -Version 'Latest'
New-Object -TypeName 'Rivet.Operations.AddRowGuidColOperation' -ArgumentList $SchemaName,$TableName,$ColumnName
}
Rivet\Functions\Operations\Add-Schema.ps1
function Add-Schema
{
<#
.SYNOPSIS
Creates a new schema.
.DESCRIPTION
The `Add-Schema` operation creates a new schema in a database. It does so in an idempotent way, i.e. it only creates the schema if it doesn't exist.
.EXAMPLE
Add-Schema -Name 'rivetexample'
Creates the `rivetexample` schema.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Alias('SchemaName')]
[string]
# The name of the schema.
$Name,
[Alias('Authorization')]
[string]
# The owner of the schema.
$Owner
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddSchemaOperation' $Name, $Owner
}
Rivet\Functions\Operations\Add-StoredProcedure.ps1
function Add-StoredProcedure
{
<#
.SYNOPSIS
Creates a new stored procedure.
.DESCRIPTION
Creates a new stored procedure.
.EXAMPLE
Add-StoredProcedure -SchemaName 'rivet' 'ReadMigrations' 'AS select * from rivet.Migrations'
Creates a stored procedure to read the migrations from Rivet's Migrations table. Note that in real life, you probably should leave my table alone.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the stored procedure.
$Name,
[Parameter()]
[string]
# The schema name of the stored procedure. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The store procedure's definition, which is everything after the `create procedure [schema].[name]` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddStoredProcedureOperation' $SchemaName, $Name, $Definition
}
Rivet\Functions\Operations\Add-Synonym.ps1
function Add-Synonym
{
<#
.SYNOPSIS
Creates a synonym.
.DESCRIPTION
SQL Server lets you create synonyms so you can reference an object with a different name, or reference an object in another database with a local name.
.LINK
http://technet.microsoft.com/en-us/library/ms177544.aspx
.EXAMPLE
Add-Synonym -Name 'Buzz' -TargetObjectName 'Fizz'
Creates a synonym called `Buzz` to the object `Fizz`.
.EXAMPLE
Add-Synonym -SchemaName 'fiz' -Name 'Buzz' -TargetSchemaName 'baz' -TargetObjectName 'Buzz'
Demonstrates how to create a synonym in a different schema. Creates a synonym to the `baz.Buzz` object so that it can referenced as `fiz.Buzz`.
.EXAMPLE
Add-Synonym -Name 'Buzz' -TargetDatabaseName 'Fizzy' -TargetObjectName 'Buzz'
Demonstrates how to create a synonym to an object in a different database.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the synonym.
$Name,
[Parameter()]
[string]
# The name of the schema where the synonym should be created.
$SchemaName = 'dbo',
[Parameter()]
[string]
# The database where the target object is located. Defaults to the current database.
$TargetDatabaseName,
[Parameter()]
[string]
# The scheme of the target object. Defaults to `dbo`.
$TargetSchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=2)]
[string]
# The target object's name the synonym will refer to.
$TargetObjectName
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddSynonymOperation' $SchemaName, $Name, $TargetSchemaName, $TargetDatabaseName, $TargetObjectName
}
Rivet\Functions\Operations\Add-Table.ps1
function Add-Table
{
<#
.SYNOPSIS
Creates a new table in the database.
.DESCRIPTION
The column's for the table should be created and returned in a script block, which is passed as the value of the `Column` parameter. For example,
Add-Table 'Suits' {
Int 'id' -Identity
TinyInt 'pieces -NotNull
VarChar 'color' -NotNull
}
.LINK
bigint
.LINK
binary
.LINK
bit
.LINK
char
.LINK
date
.LINK
datetime
.LINK
datetime2
.LINK
datetimeoffset
.LINK
decimal
.LINK
float
.LINK
hierarchyid
.LINK
int
.LINK
money
.LINK
nchar
.LINK
numeric
.LINK
nvarchar
.LINK
real
.LINK
rowversion
.LINK
smalldatetime
.LINK
smallint
.LINK
smallmoney
.LINK
sqlvariant
.LINK
time
.LINK
tinyint
.LINK
uniqueidentifier
.LINK
varbinary
.LINK
varchar
.LINK
xml
.EXAMPLE
Add-Table -Name 'Ties' -Column { VarChar 'color' -NotNull }
Creates a `Ties` table with a single column for each tie's color. Pretty!
#>
[CmdletBinding(DefaultParameterSetName='AsNormalTable')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$Name,
[string]
# The table's schema. Defaults to 'dbo'.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='AsNormalTable')]
[ScriptBlock]
# A script block that returns the table's columns.
$Column,
[Parameter(Mandatory=$true,ParameterSetName='AsFileTable')]
[Switch]
# Creates a [FileTable](http://msdn.microsoft.com/en-us/library/ff929144.aspx) table.
$FileTable,
[string]
# Specifies the partition scheme or filegroup on which the table is stored, e.g. `ON $FileGroup`
$FileGroup,
[string]
# The filegroup where text, ntext, image, xml, varchar(max), nvarchar(max), and varbinary(max) columns are stored. The table has to have one of those columns. For example, `TEXTIMAGE_ON $TextImageFileGroup`.
$TextImageFileGroup,
[string]
# Specifies the filegroup for FILESTREAM data, e.g. `FILESTREAM_ON $FileStreamFileGroup`.
$FileStreamFileGroup,
[string[]]
# Specifies one or more table options.
$Option,
[string]
# A description of the table.
$Description
)
Set-StrictMode -Version 'Latest'
$columns = & $Column
New-Object 'Rivet.Operations.AddTableOperation' $SchemaName, $Name, $columns, $FileTable, $FileGroup, $TextImageFileGroup, $FileStreamFileGroup, $Option, $Description
$addDescriptionArgs = @{
SchemaName = $SchemaName;
TableName = $Name;
}
if( $Description )
{
Add-Description -Description $Description @addDescriptionArgs
}
$columns |
Where-Object { $_.Description } |
ForEach-Object { Add-Description -Description $_.Description -ColumnName $_.Name @addDescriptionArgs }
}
Rivet\Functions\Operations\Add-Trigger.ps1
function Add-Trigger
{
<#
.SYNOPSIS
Creates a new trigger.
.DESCRIPTION
Creates a new trigger. If updating an existing trigger, use `Remove-Trigger` to remove it first, then `New-Trigger` to re-create it.
.LINK
Remove-Trigger.
.EXAMPLE
Add-Trigger 'PrintMessage' 'ON rivet.Migrations for insert as print ''Migration applied!'''
Creates a trigger that prints a method when a row gets inserted into the `rivet.Migrations` table.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the trigger.
$Name,
[Parameter()]
[string]
# The schema of the trigger.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The body of the trigger. Everything after and including the `ON` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddTriggerOperation' $SchemaName, $Name, $Definition
}
Rivet\Functions\Operations\Add-UniqueKey.ps1
function Add-UniqueKey
{
<#
.SYNOPSIS
Creates a UNIQUE constraint on the specified column and table.
.DESCRIPTION
Creates a UNIQUE constraint on the specified column and table.
You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
.EXAMPLE
Add-UniqueKey -TableName Cars -ColumnName Year
Adds an unique constraint on column 'Year' in the table 'Cars'
.EXAMPLE
Add-UniqueKey -TableName 'Cars' -ColumnName 'Year' -Option @('IGNORE_DUP_KEY = ON','ALLOW_ROW_LOCKS = OFF')
Adds an unique constraint on column 'Year' in the table 'Cars' with specified options
#>
[CmdletBinding()]
param(
[string]
# The schema name of the target table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the target table.
$TableName,
[Parameter(Mandatory=$true,Position=1)]
[string[]]
# The column(s) on which the index is based
$ColumnName,
[Switch]
# Creates a clustered index, otherwise non-clustered
$Clustered,
[Int]
# FillFactor as Integer
$FillFactor,
[string[]]
# An array of index options.
$Option,
[string]
# The value of the `ON` clause, which controls the filegroup/partition to use for the index.
$On,
[Parameter()]
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name
)
Set-StrictMode -Version Latest
## Construct Comma Separated List of Columns
$ColumnClause = $ColumnName -join ','
if ($PSBoundParameters.ContainsKey("Name"))
{
New-Object 'Rivet.Operations.AddUniqueKeyOperation' $SchemaName, $TableName, $ColumnName, $Name, $Clustered, $FillFactor, $Option, $On
}
else
{
New-Object 'Rivet.Operations.AddUniqueKeyOperation' $SchemaName, $TableName, $ColumnName, $Clustered, $FillFactor, $Option, $On
}
}
Rivet\Functions\Operations\Add-UserDefinedFunction.ps1
function Add-UserDefinedFunction
{
<#
.SYNOPSIS
Creates a new user-defined function.
.DESCRIPTION
Creates a new user-defined function.
.EXAMPLE
Add-UserDefinedFunction -SchemaName 'rivet' 'One' 'returns tinyint begin return 1 end'
Creates a user-defined function that returns the number 1.
#> [CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the stored procedure.
$Name,
[Parameter()]
[string]
# The schema name of the stored procedure. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The store procedure's definition. Everything after the `create function [schema].[name]` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object Rivet.Operations.AddUserDefinedFunctionOperation $SchemaName,$Name,$Definition
}
Rivet\Functions\Operations\Add-View.ps1
function Add-View
{
<#
.SYNOPSIS
Creates a new view.
.DESCRIPTION
Creates a new view.
.EXAMPLE
Add-View -SchemaName 'rivet' 'ReadMigrations' 'AS select * from rivet.Migrations'
Creates a view to read all the migrations from Rivet's Migrations table. Don't do this in real life.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the view.
$Name,
[Parameter()]
[string]
# The schema name of the view. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The definition of the view. Everything after the `create view [schema].[name]` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.AddViewOperation' $SchemaName,$Name,$Definition
}
Rivet\Functions\Operations\Disable-Constraint.ps1
function Disable-Constraint
{
<#
.SYNOPSIS
Disable a check of foreign key constraint on a table.
.DESCRIPTION
The `Disable-Constraint` operation disables a check or foreign key constraint on a table. Only check and foreign key constraints can be enabled/disabled.
.LINK
Enable-Constraint
.EXAMPLE
Disable-CheckConstraint 'Migrations' 'CK_Migrations_MigrationID'
Demonstrates how to disable a constraint on a table. In this case, the `CK_Migrations_MigrationID` constraint on the `Migrations` table is disabled. Is it a check constraint? Foreign key constraint? It doesn't matter!
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the constraint's table.
$TableName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the constraint.
$Name
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.DisableConstraintOperation' $SchemaName, $TableName, $Name
}
Set-Alias -Name 'Disable-CheckConstraint' -Value 'Disable-Constraint'
Rivet\Functions\Operations\Disable-ForeignKey.ps1
function Disable-ForeignKey
{
<#
.SYNOPSIS
OBSOLETE. Use `Disable-Constraint` instead.
.DESCRIPTION
OBSOLETE. Use `Disable-Constraint` instead.
.EXAMPLE
Disable-Constraint 'SourceTable' 'FK_SourceID_ReferenceTable'
Demonstrates that `Disable-ForeignKey` is obsolete by showing that you should use `Disable-Constraint` instead.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table to alter.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string[]]
# The column(s) that should be part of the foreign key.
$ColumnName,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The table that the foreign key references
$References,
[Parameter()]
[string]
# The schema name of the reference table. Defaults to `dbo`.
$ReferencesSchema = 'dbo',
[Parameter()]
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name
)
Set-StrictMode -Version 'Latest'
Write-Warning ('Disable-ForeignKey''s is obsolete and will removed in a future version of Rivet. Please use `Disable-Constraint` instead.')
if( -not $PSBoundParameters.ContainsKey('Name') )
{
$Name = New-Object 'Rivet.ForeignKeyConstraintName' $SchemaName, $TableName, $ReferencesSchema, $References | Select-Object -ExpandProperty 'Name'
}
Disable-Constraint -SchemaName $SchemaName -TableName $TableName -Name $Name
}
Rivet\Functions\Operations\Enable-Constraint.ps1
function Enable-Constraint
{
<#
.SYNOPSIS
Enable a check or foreign key constraint.
.DESCRIPTION
The `Enable-Constraint` operation enables a check or foreign key constraint on a table. Only check and foreign key constraints can be enabled/disabled.
.LINK
Disable-Constraint
.EXAMPLE
Enable-Constraint 'Migrations' 'FK_Migrations_MigrationID'
Demonstrates how to disable a constraint on a table. In this case, the `FK_Migrations_MigrationID` constraint on the `Migrations` table is disabled. Is it a check constraint? Foreign key constraint? It doesn't matter!
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the constraint's table.
$TableName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the constraint.
$Name
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.EnableConstraintOperation' $SchemaName, $TableName, $Name, $false
}
Set-Alias -Name 'Enable-CheckConstraint' -Value 'Enable-Constraint'
Rivet\Functions\Operations\Enable-ForeignKey.ps1
function Enable-ForeignKey
{
<#
.SYNOPSIS
OBSOLETE. Use `Enable-Constraint` instead.
.DESCRIPTION
OBSOLETE. Use `Enable-Constraint` instead.
.EXAMPLE
Enable-Constraint 'TAbleName', 'FK_ForeignKeyName'
Demonstrates that `Enable-ForeignKey` is obsolete and you should use `Enable-Constraint` instead.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table to alter.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string[]]
# The column(s) that should be part of the foreign key.
$ColumnName,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The table that the foreign key references
$References,
[Parameter()]
[string]
# The schema name of the reference table. Defaults to `dbo`.
$ReferencesSchema = 'dbo',
[Parameter()]
[string]
# The name for the <object type>. If not given, a sensible name will be created.
$Name
)
Set-StrictMode -Version 'Latest'
Write-Warning ('Enable-ForeignKey''s is obsolete and will removed in a future version of Rivet. Please use `Enable-Constraint` instead.')
if( -not $PSBoundParameters.ContainsKey('Name') )
{
$Name = New-Object 'Rivet.ForeignKeyConstraintName' $SchemaName, $TableName, $ReferencesSchema, $References | Select-Object -ExpandProperty 'Name'
}
Enable-Constraint -SchemaName $SchemaName -TableName $TableName -Name $Name
}
Rivet\Functions\Operations\Invoke-Ddl.ps1
function Invoke-Ddl
{
<#
.SYNOPSIS
Executes a DDL statement against the database.
.DESCRIPTION
The `Invoke-Ddl` function is used to update the structure of a database when none of Rivet's other operations will work.
.EXAMPLE
Invoke-Ddl -Query 'create table rivet.Migrations ( id int not null )'
Executes the create table syntax above against the database.
#>
[CmdletBinding(DefaultParameterSetName='AsReader')]
param(
[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
[string]
$Query
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RawDdlOperation' $Query
}
Rivet\Functions\Operations\Invoke-SqlScript.ps1
function Invoke-SqlScript
{
<#
.SYNOPSIS
Runs a SQL script file as part of a migration.
.DESCRIPTION
The SQL script is split on GO statements, which must be by themselves on a line, e.g.
select * from sys.tables
GO
select * from sys.views
GO
#>
[CmdletBinding(DefaultParameterSetName='AsReader')]
param(
[Parameter(Mandatory=$true)]
[string]
# The path to the SQL script to execute.
$Path,
[Parameter(Mandatory=$true,ParameterSetName='AsScalar')]
[Switch]
$AsScalar,
[Parameter(Mandatory=$true,ParameterSetName='AsNonQuery')]
[Switch]
$NonQuery,
[UInt32]
# The time in seconds to wait for the command to execute. The default is 30 seconds.
$CommandTimeout = 30
)
Set-StrictMode -Version 'Latest'
$invokeMigrationParams = @{
CommandTimeout = $CommandTimeout;
}
if( $pscmdlet.ParameterSetName -eq 'AsScalar' )
{
$invokeMigrationParams.AsScalar = $true
}
elseif( $pscmdlet.ParameterSetName -eq 'AsNonQuery' )
{
$invokeMigrationParams.NonQuery = $true
}
if( -not ([IO.Path]::IsPathRooted( $Path )) )
{
$Path = Join-Path $DBMigrationsRoot $Path
}
if( -not (Test-Path -Path $Path -PathType Leaf) )
{
throw ('SQL script ''{0}'' not found.' -f $Path)
return
}
$Path = Resolve-Path -Path $Path | Select-Object -ExpandProperty 'ProviderPath'
$sql = Get-Content -Path $Path -Raw
New-Object 'Rivet.Operations.ScriptFileOperation' $Path,$sql
}
Rivet\Functions\Operations\Remove-CheckConstraint.ps1
function Remove-CheckConstraint
{
<#
.SYNOPSIS
Removes a check constraint from a table.
.DESCRIPTION
The `Remove-CheckConstraint` operation removes a check constraint from a table. Check constraints add validation for data in columns.
.EXAMPLE
Remove-CheckConstraint 'Migrations' 'CK_Migrations_MigrationID'
Demonstrates how to remove a check constraint from a table. In this case, the `CK_Migrations_MigrationID` constraint will be removed from the `Migrations` table.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the check constraint's table.
$TableName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the check constraint to remove.
$Name
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveCheckConstraintOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-DataType.ps1
function Remove-DataType
{
<#
.SYNOPSIS
Drops a user-defined datatype.
.DESCRIPTION
Handles all three datatypes: alias, CLR, and table. If the datatype is in use, you'll get an error. Make sure to remove/alter any objects that reference the type first.
.LINK
Add-DataType
.LINK
http://technet.microsoft.com/en-us/library/ms174407.aspx
.EXAMPLE
Remove-DataType 'GUID'
Demonstrates how to remove the `GUID` user-defined data type.
.EXAMPLE
Remove-DataType -SchemaName 'rivet' 'GUID'
Demonstrates how to remove a datatype in a schema other than `dbo`.
#>
[CmdletBinding()]
param(
[Parameter()]
[string]
# The name of the type's schema. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the datatype to drop.
$Name
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveDataTypeOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-DefaultConstraint.ps1
function Remove-DefaultConstraint
{
<#
.SYNOPSIS
Removes a default constraint from a table.
.DESCRIPTION
The `Remove-DefaultConstraint` operation removes a default constraint from a table.
.EXAMPLE
Remove-DefaultConstraint 'Cars' -Name 'Cars_Year_DefaultConstraint'
Demonstrates how to remove a default constraint. IN this case, the `Cars_Year_DefaultConstraint` constraint will be removed from the `Cars` table.
#>
[CmdletBinding(DefaultParameterSetName='ByDefaultName')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the target table.
$TableName,
[Parameter()]
[string]
# The schema name of the target table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByDefaultName')]
[string]
# OBSOLETE. Use the `Name` parameter to remove a default constraint.
$ColumnName,
[Parameter(Mandatory=$true,ParameterSetName='ByCustomName')]
[string]
# The name of the default constraint to remove.
$Name
)
Set-StrictMode -Version 'Latest'
if( $PSCmdlet.ParameterSetName -eq 'ByDefaultName' )
{
Write-Warning ('Remove-DefaultConstraint''s ColumnName parameter is obsolete and will be removed in a future version of Rivet. Instead, use the Name parameter to remove a default constraint.')
$Name = New-Object -TypeName 'Rivet.ConstraintName' -ArgumentList $SchemaName, $TableName, $ColumnName, ([Rivet.ConstraintType]::Default) | Select-Object -ExpandProperty 'Name'
}
New-Object 'Rivet.Operations.RemoveDefaultConstraintOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-Description.ps1
function Remove-Description
{
<#
.SYNOPSIS
Removes the `MS_Description` extended property for a table or column.
.DESCRIPTION
The `sys.sp_dropextendedproperty` stored procedure is used to remove a table/column's description (i.e. the `MS_Description` extended property), but the syntax is weird. This function hides that weirdness from you. You're welcome.
.EXAMPLE
Remove-Description -TableName WhoseitsWhatsits
Removes the description (i.e. the `MS_Description` extended property) for the `WhoseitsWhatsits` table.
.EXAMPLE
Remove-Description -TableName WhoseitsWhatsits -ColumnName IsSnarfblat
Removes the description (i.e. the `MS_Description` extended property) for the `WhoseitsWhatsits` table's `IsSnarfblat` column.
#>
[CmdletBinding()]
param(
[Alias('Schema')]
[string]
# The schema. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true)]
[Alias('Table')]
[string]
# The name of the table where the extended property is getting set.
$TableName,
[Parameter(ParameterSetName='ForColumn')]
[Alias('Column')]
[string]
# The name of the column where the extended property is getting set.
$ColumnName
)
Set-StrictMode -Version 'Latest'
$optionalArgs = @{ }
if( $ColumnName )
{
$optionalArgs.ColumnName = $ColumnName
}
Remove-ExtendedProperty -Name 'MS_Description' `
-SchemaName $SchemaName `
-TableName $TableName `
@optionalArgs
}
Rivet\Functions\Operations\Remove-ExtendedProperty.ps1
function Remove-ExtendedProperty
{
<#
.SYNOPSIS
Drops an extended property for a schema, table, or column.
.DESCRIPTION
SQL Server has a special stored procedure for removing extended property metatdata about an object. Unfortunately, it has a really clunky interface. This function is an attempt to wrap `sp_dropextendedproperty` with a saner interface.
Currently, this function only supports dropping properties for schemas, tables, and columns. Submit a patch!
.LINK
Add-Description
.LINK
Add-ExtendedProperty
.LINK
Remove-Description
.LINK
Update-Description
.LINK
Update-ExtendedProperty
.EXAMPLE
Remove-ExtendedProperty -Name 'Deploy' -SchemaName 'spike'
Drops the custom `Deploy` metadata for the `spike` schema.
.EXAMPLE
Remove-ExtendedProperty -Name 'Deploy' -TableName 'Food'
Drops the custom `Deploy` metadata on the `Food` table in the `dbo` schema.
.EXAMPLE
Remove-ExtendedProperty -Name 'IsEncrypted' -TableName 'User' -ColumnName 'Password'
Drops the custom `IsEncrypted` metadata on the `User` table's `Password` column.
.EXAMPLE
Remove-ExtendedProperty -Name 'ContainsPII' -View 'LoggedInUsers'
Demonstrates how to remove custom metadata on the `LoggedInUsers` view
.EXAMPLE
Remove-ExtendedProperty -Name 'IsEncrypted' -View 'LoggedInUsers' -ColumnName 'Password'
Demonstrates how to remove custom metadata for a view's column
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the extended property to add.
$Name,
[Parameter(ParameterSetName='SCHEMA')]
[Parameter(ParameterSetName='TABLE')]
[Parameter(ParameterSetName='TABLE-COLUMN')]
[Parameter(ParameterSetName='VIEW')]
[Parameter(ParameterSetName='VIEW-COLUMN')]
[string]
# The schema of the object.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,ParameterSetName='TABLE')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Table')]
[string]
# The table name.
$TableName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW')]
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Alias('View')]
[string]
# The table name.
$ViewName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Column')]
[string]
# The column name.
$ColumnName
)
Set-StrictMode -Version 'Latest'
if ($PsCmdlet.ParameterSetName -eq "SCHEMA")
{
$op = New-Object 'Rivet.Operations.RemoveExtendedPropertyOperation' $SchemaName, $Name
}
if ($PsCmdlet.ParameterSetName -eq "TABLE")
{
$op = New-Object 'Rivet.Operations.RemoveExtendedPropertyOperation' $SchemaName, $TableName, $Name, $false
}
if ($PsCmdlet.ParameterSetName -eq "VIEW")
{
$op = New-Object 'Rivet.Operations.RemoveExtendedPropertyOperation' $SchemaName, $ViewName, $Name, $true
}
if ($PsCmdlet.ParameterSetName -eq "TABLE-COLUMN")
{
$op = New-Object 'Rivet.Operations.RemoveExtendedPropertyOperation' $SchemaName, $TableName, $ColumnName, $Name, $false
}
if ($PsCmdlet.ParameterSetName -eq "VIEW-COLUMN")
{
$op = New-Object 'Rivet.Operations.RemoveExtendedPropertyOperation' $SchemaName, $ViewName, $ColumnName, $Name, $true
}
return $op
}
Rivet\Functions\Operations\Remove-ForeignKey.ps1
function Remove-ForeignKey
{
<#
.SYNOPSIS
Removes a foreign key from an existing table that has a foreign key.
.DESCRIPTION
Removes a foreign key to a table.
.EXAMPLE
Remove-ForeignKey 'Cars' -Name 'FK_Cars_Year'
Demonstrates how to remove a foreign key that has a name different than Rivet's derived name.
#>
[CmdletBinding(DefaultParameterSetName='ByDefaultName')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByDefaultName')]
[string]
# OBSOLETE. Use the `Name` parameter to specify the foreign key to remove.
$References,
[Parameter(ParameterSetName='ByDefaultName')]
[string]
# OBSOLETE. Use the `Name` parameter to specify the foreign key to remove.
$ReferencesSchema = 'dbo',
[Parameter(Mandatory=$true,ParameterSetName='ByCustomName')]
[string]
# The name of the foreign key to remove.
$Name
)
Set-StrictMode -Version 'Latest'
if( $PSCmdlet.ParameterSetName -eq 'ByDefaultName' )
{
Write-Warning ('Remove-ForeignKey''s References and ReferencesSchema parameters are obsolete and will be removed in a future version of Rivet. Please use the Name parameter remove the foreign key.')
$Name = New-Object -TypeName 'Rivet.ForeignKeyConstraintName' -ArgumentList $SchemaName, $TableName, $ReferencesSchema, $References | Select-Object -ExpandProperty 'Name'
}
New-Object 'Rivet.Operations.RemoveForeignKeyOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-Index.ps1
function Remove-Index
{
<#
.SYNOPSIS
Removes an index from a table.
.DESCRIPTION
The `Remove-Index` operation removes an index from a table.
.EXAMPLE
Remove-Index 'Cars' -Name 'YearIX'
Demonstrates how to drop an index
#>
[CmdletBinding(DefaultParameterSetName='ByDefaultName')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the target table.
$TableName,
[Parameter()]
[string]
# The schema name of the target table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByDefaultName')]
[string[]]
# OBSOLETE. Use the `Name` parameter to remove an index.
$ColumnName,
[Parameter(ParameterSetName='ByDefaultName')]
[Switch]
# OBSOLETE. Use the `Name` parameter to remove an index.
$Unique,
[Parameter(Mandatory=$true,ParameterSetName='ByExplicitName')]
[string]
# The name of the index to remove.
$Name
)
Set-StrictMode -Version 'Latest'
if( $PSCmdlet.ParameterSetName -eq "ByDefaultName" )
{
Write-Warning ('Remove-Index''s ColumnName parameter and Unique switch are obsolete and will be removed in a future version of Rivet. Instead, use the Name parameter to remove an index.')
$Name = New-Object -TypeName 'Rivet.IndexName' -ArgumentList $SchemaName, $TableName, $ColumnName, $Unique | Select-Object -ExpandProperty 'Name'
}
New-Object 'Rivet.Operations.RemoveIndexOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-PrimaryKey.ps1
function Remove-PrimaryKey
{
<#
.SYNOPSIS
Removes a primary key from a table.
.DESCRIPTION
The `Remove-PrimaryKey` operation removes a primary key from a table.
.EXAMPLE
Remove-PrimaryKey 'Cars' -Name 'Car_PK'
Demonstrates how to remove a primary key whose name is different than the derived name Rivet creates for primary keys.
#>
[CmdletBinding(DefaultParameterSetName='ByDefaultName')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByCustomName')]
[string]
# The name of the primary key to remoe.
$Name
)
Set-StrictMode -Version 'Latest'
if( $PSCmdlet.ParameterSetName -eq 'ByDefaultName' )
{
Write-Warning ('Remove-PrimaryKey''s Name parameter will be required in a future version of Rivet. Please use the Name parameter to remove the primary key.')
$Name = New-Object -TypeName 'Rivet.ConstraintName' -ArgumentList $SchemaName, $TableName, @(), ([Rivet.ConstraintType]::PrimaryKey) | Select-Object -ExpandProperty 'Name'
}
New-Object 'Rivet.Operations.RemovePrimaryKeyOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-Row.ps1
function Remove-Row
{
<#
.SYNOPSIS
Removes a row from a table.
.DESCRIPTION
To specify which columns to insert into the new row, pass a hashtable as a value to the `Column` parameter. This hashtable should have keys that map to column names, and the value of each key will be used as the value for that column in the row.
.EXAMPLE
Remove-Row -SchemaName 'rivet' 'Migrations' 'MigrationID=20130913132411'
Demonstrates how to delete a specific set of rows from a table.
.EXAMPLE
Remove-Row 'Cars' -All
Demonstrates how to remove all rows in a table.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='DropSpecificRows')]
[string]
# The condition to use for choosing which rows to remove. This parameter is required, unless you *really* want to
$Where,
[Parameter(Mandatory=$true,ParameterSetName='AllRows')]
[Switch]
# Drop all the rows in the table.
$All,
[Parameter(ParameterSetName='AllRows')]
[Switch]
# Truncate the table instead to delete all the rows. This is faster than using a `delete` statement.
$Truncate
)
Set-StrictMode -Version 'Latest'
if ($PSCmdlet.ParameterSetName -eq 'DropSpecificRows')
{
New-Object 'Rivet.Operations.RemoveRowOperation' $SchemaName, $TableName, $Where
}
elseif ($PSCmdlet.ParameterSetName -eq 'AllRows')
{
if ($Truncate)
{
New-Object 'Rivet.Operations.RemoveRowOperation' $SchemaName, $TableName, $true
}
else
{
New-Object 'Rivet.Operations.RemoveRowOperation' $SchemaName, $TableName, $false
}
}
}
Rivet\Functions\Operations\Remove-RowGuidCol.ps1
function Remove-RowGuidCol
{
<#
.SYNOPSIS
Remove the `rowguidcol` property from a column in a table.
.DESCRIPTION
The `Remove-RowGuidCol` operation removes the `rowguidcol` property from a `uniqueidentifier` column in a table.
The `Remove-RowGuidCol` operation was added in Rivet 0.7.
.LINK
https://msdn.microsoft.com/en-us/library/ms190273.aspx
.LINK
Add-RowGuidCol
.EXAMPLE
Remove-RowGuidCol -TableName 'MyTable' -ColumnName 'MyUniqueIdentifier'
Demonstrates how to remove the `rowguidcol` property from a column in a table. In this example, the `dbo.MyTable` table's `MyUniqueIdentifier` column will lose the propery.
.EXAMPLE
Remove-RowGuidCol -SchemaName 'cstm' -TableName 'MyTable' -ColumnName 'MyUniqueIdentifier'
Demonstrates how to remove the `rowguidcol` property from a column in a table whose schema isn't `dbo`, in this case the `cstm.MyTable` table's `MyUniqueIdentifier` column will lose the property.
#>
[CmdletBinding()]
param(
[string]
# The table's schema. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=0)]
[string]
# The table's name.
$TableName,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the column that should get the `rowguidcol` property.
$ColumnName
)
Set-StrictMode -Version 'Latest'
New-Object -TypeName 'Rivet.Operations.RemoveRowGuidColOperation' -ArgumentList $SchemaName,$TableName,$ColumnName
}
Rivet\Functions\Operations\Remove-Schema.ps1
function Remove-Schema
{
<#
.SYNOPSIS
Removes a schema.
.EXAMPLE
Remove-Schema -Name 'rivetexample'
Drops/removes the `rivetexample` schema.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[Alias('SchemaName')]
[string]
# The name of the schema.
$Name
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveSchemaOperation' $Name
}
Rivet\Functions\Operations\Remove-StoredProcedure.ps1
function Remove-StoredProcedure
{
<#
.SYNOPSIS
Removes a stored procedure.
.DESCRIPTION
Removes a stored procedure. Will throw an exception and rollback the migration if the stored procedure doesn't exist.
By default, the stored procedure is assumed to be in the `dbo` schema. Use the `Schema` parameter to specify a different schema.
You can conditionally delete a stored procedure only if it exists using the `IfExists` switch.
.EXAMPLE
Remove-StoredProcedure -Name MySproc
Removes the `dbo.MySproc` stored procedure.
.EXAMPLE
Remove-StoredProcedure -Name MySproc -SchemaName rivet
Removes the `rivet.MySproc` stored procedure.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The name of the stored procedure to remove/delete.
$Name,
[Parameter()]
[string]
# The schema of the stored procedure. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveStoredProcedureOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-Synonym.ps1
function Remove-Synonym
{
<#
.SYNOPSIS
Drops a synonym.
.DESCRIPTION
Drops an existing synonym. If the synonym doesn't exist, you'll get an error.
.LINK
http://technet.microsoft.com/en-us/library/ms174996.aspx
.EXAMPLE
Remove-Synonym -Name 'Buzz'
Removes the `Buzz` synonym.
.EXAMPLE
Remove-Synonym -SchemaName 'fiz' -Name 'Buzz'
Demonstrates how to remove a synonym in a schema other than `dbo`.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=1)]
[string]
# The name of the synonym to drop.
$Name,
[Parameter()]
[string]
# The name of the synonym's schema. Default to `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveSynonymOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-Table.ps1
function Remove-Table
{
<#
.SYNOPSIS
Removes a table from a database.
.DESCRIPTION
You can't get any of the data back, so be careful.
.EXAMPLE
Remove-Table -Name 'Coffee'
Removes the `Coffee` table from the database.
#>
param(
# The name of the table where the column should be removed.
[Parameter(Mandatory=$true)]
[string]
$Name,
[string]
# The schema of the table where the column should be added. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveTableOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-Trigger.ps1
function Remove-Trigger
{
<#
.SYNOPSIS
Deletes a new trigger.
.DESCRIPTION
Deletes an existing trigger.
.LINK
New-Trigger.
.EXAMPLE
Remove-Trigger 'PrintMessage'
Removes the `PrintMessage` trigger.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the trigger.
$Name,
[Parameter()]
[string]
# The schema of the trigger.
$SchemaName = "dbo"
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveTriggerOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-UniqueKey.ps1
function Remove-UniqueKey
{
<#
.SYNOPSIS
Removes the Unique Constraint from the database
.DESCRIPTION
Removes the Unique Constraint from the database.
.EXAMPLE
Remove-UniqueKey 'Cars' -Name 'YearUK'
Demonstrates how to remove a unique key whose name is different than the name Rivet derives for unique keys.
#>
[CmdletBinding(DefaultParameterSetName='ByDefaultName')]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the target table.
$TableName,
[Parameter()]
[string]
# The schema name of the target table. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByDefaultName')]
[string[]]
# OBSOLETE. Use the `Name` parameter to specify the name of the unique key to remove.
$ColumnName,
[Parameter(Mandatory=$true,Position=1,ParameterSetName='ByExplicitName')]
[string]
# The name of the unique key to remove.
$Name
)
Set-StrictMode -Version 'Latest'
if( $PSCmdlet.ParameterSetName -eq 'ByDefaultName' )
{
Write-Warning ('Remove-UniqueKey''s ColumnName parameter is obsolete and will be removed in a future version of Rivet. Instead, use the Name parameter to remove a unique key.')
$Name = New-Object -TypeName 'Rivet.ConstraintName' -ArgumentList $SchemaName, $TableName, $ColumnName, ([Rivet.ConstraintType]::UniqueKey) | Select-Object -ExpandProperty 'Name'
}
New-Object 'Rivet.Operations.RemoveUniqueKeyOperation' $SchemaName, $TableName, $Name
}
Rivet\Functions\Operations\Remove-UserDefinedFunction.ps1
function Remove-UserDefinedFunction
{
<#
.SYNOPSIS
Removes a user-defined function.
.DESCRIPTION
Removes a user-defined function. Will throw an exception and rollback the migration if the user-defined function doesn't exist.
By default, the user-defined function is assumed to be in the `dbo` schema. Use the `Schema` parameter to specify a different schema.
You can conditionally delete a user-defined function only if it exists using the `IfExists` switch.
.EXAMPLE
Remove-UserDefinedFunction -Name MyFunc
Removes the `dbo.MyFunc` user-defined function.
.EXAMPLE
Remove-UserDefinedFunction -Name MyFunc -SchemaName rivet
Removes the `rivet.MyFunc` user-defined function.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The name of the user-defined function to remove/delete.
$Name,
[Parameter()]
[string]
# The schema of the user-defined function. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveUserDefinedFunctionOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Remove-View.ps1
function Remove-View
{
<#
.SYNOPSIS
Removes a view.
.DESCRIPTION
Removes a view. Will throw an exception and rollback the migration if the view doesn't exist.
By default, the view is assumed to be in the `dbo` schema. Use the `Schema` parameter to specify a different schema.
You can conditionally delete a view only if it exists using the `IfExists` switch.
.EXAMPLE
Remove-View -Name MyView
Removes the `dbo.MyView` view.
.EXAMPLE
Remove-View -Name MyView -SchemaName rivet
Removes the `rivet.MyView` view.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
# The name of the view to remove/delete.
$Name,
[Parameter()]
[string]
# The schema of the view. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RemoveViewOperation' $SchemaName, $Name
}
Rivet\Functions\Operations\Rename-Column.ps1
function Rename-Column
{
<#
.SYNOPSIS
Renames a column.
.DESCRIPTION
SQL Server ships with a stored procedure which is used to rename certain objects. This operation wraps that stored procedure.
Use `Rename-DataType` to rename a data type. Use `Rename-Index` to rename an index. Use `Rename-Object` to rename an object.
.LINK
http://technet.microsoft.com/en-us/library/ms188351.aspx
.LINK
Rename-DataType
.LINK
Rename-Index
.LINK
Rename-Object
.EXAMPLE
Rename-Column -TableName 'FooBar' -Name 'Fizz' -NewName 'Buzz'
Changes the name of the `Fizz` column in the `FooBar` table to `Buzz`.
.EXAMPLE
Rename-Column -SchemaName 'fizz' -TableName 'FooBar' -Name 'Buzz' -NewName 'Baz'
Demonstrates how to rename a column in a table that is in a schema other than `dbo`.
.EXAMPLE
Rename-Column 'FooBar' 'Fizz' 'Buzz'
Demonstrates how to use the short form to rename `Fizz` column in the `FooBar` table to `Buzz`: table name is first, then existing column name, then new column name.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table of the column to rename.
$TableName,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The current name of the column.
$Name,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The new name of the column.
$NewName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RenameColumnOperation' $SchemaName, $TableName, $Name, $NewName
}
Rivet\Functions\Operations\Rename-DataType.ps1
function Rename-DataType
{
<#
.SYNOPSIS
Renames data types.
.DESCRIPTION
This function wraps the `sp_rename` stored procedure, and can be used to rename `USERDATATYPE` types.
Use `Rename-Index` to rename an index. Use `Rename-Column` to rename a column. Use `Rename-Object` to rename an object.
.LINK
http://technet.microsoft.com/en-us/library/ms188351.aspx
.LINK
Rename-Column
.LINK
Rename-Index
.LINK
Rename-Object
.EXAMPLE
Rename-DataType -Name 'FooBar' -NewName 'BarFoo'
Changes the name of the `FooBar` type to `BarFoo`.
.EXAMPLE
Rename-DataType -SchemaName 'fizz' -Name 'Buzz' -NewName 'Baz'
Demonstrates how to rename a data type that is in a schema other than `dbo`.
#>
[CmdletBinding()]
param(
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = "dbo",
[Parameter(Mandatory=$true,Position=0)]
[string]
# The current name of the table.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The new name of the table.
$NewName
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RenameOperation' $SchemaName, $Name, $NewName, 'USERDATATYPE'
}
Rivet\Functions\Operations\Rename-Index.ps1
function Rename-Index
{
<#
.SYNOPSIS
Renames an index.
.DESCRIPTION
SQL Server ships with a stored procedure which is used to rename certain objects. This operation wraps that stored procedure.
Use `Rename-Column` to rename a column. Use `Rename-DataType` to rename a data type. Use `Rename-Object` to rename an object.
.LINK
http://technet.microsoft.com/en-us/library/ms188351.aspx
.LINK
Rename-Column
.LINK
Rename-DataType
.LINK
Rename-Object
.EXAMPLE
Rename-Index -TableName 'FooBar' -Name 'IX_Fizz' -NewName 'Buzz'
Changes the name of the `Fizz` index on the `FooBar` table to `Buzz`.
.EXAMPLE
Rename-Index -SchemaName 'fizz' -TableName 'FooBar' -Name 'IX_Buzz' -NewName 'Fizz'
Demonstrates how to rename an index on a table that is in a schema other than `dbo`.
.EXAMPLE
Rename-Index 'FooBar' 'IX_Fizz' 'Buzz'
Demonstrates how to use the short form to rename the `Fizz` index on the `FooBar` table to `Buzz`: table name is first, then existing index name, then new index name.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table of the index to rename.
$TableName,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The current name of the index.
$Name,
[Parameter(Mandatory=$true,Position=2)]
[string]
# The new name of the index.
$NewName,
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = 'dbo'
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RenameIndexOperation' $SchemaName, $TableName, $Name, $NewName
}
Rivet\Functions\Operations\Rename-Object.ps1
function Rename-Object
{
<#
.SYNOPSIS
Renames objects (e.g. tables, constraints, keys).
.DESCRIPTION
This function wraps the `sp_rename` stored procedure, and can be used to rename objects tracked in `sys.objects`:
* Tables
* Functions
* Synonyms
* Constraints/keys
* Views
* Stored procedures
* Triggers
Use `Rename-Index` to rename an index. Use `Rename-Column` to rename a column. Use `Rename-DataType` to rename a data type.
.LINK
http://technet.microsoft.com/en-us/library/ms188351.aspx
.LINK
Rename-Column
.LINK
Rename-DataType
.LINK
Rename-Index
.EXAMPLE
Rename-Object -Name 'FooBar' -NewName 'BarFoo'
Changes the name of the `FooBar` table to `BarFoo`.
.EXAMPLE
Rename-Object -SchemaName 'fizz' -Name 'Buzz' -NewName 'Baz'
Demonstrates how to rename a table that is in a schema other than `dbo`.
.EXAMPLE
Rename-Object 'FK_Foo_Bar' 'FK_Bar_Foo'
Demonstrates how to use `Rename-Object` without explicit parameters, and how to rename a foreign key.
#>
[CmdletBinding()]
param(
[Parameter()]
[string]
# The schema of the table. Default is `dbo`.
$SchemaName = "dbo",
[Parameter(Mandatory=$true,Position=0)]
[string]
# The current name of the table.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[string]
# The new name of the table.
$NewName
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.RenameOperation' $SchemaName, $Name, $NewName, 'OBJECT'
}
Rivet\Functions\Operations\Stop-Migration.ps1
function Stop-Migration
{
<#
.SYNOPSIS
Stops a migration from getting poppped.
.DESCRIPTION
The `Stop-Migration` operation stops a migration from getting popped. When put in your migration's `Pop-Migration` function, the migration will fail when someone attempts to pop it. Use this operation to mark a migration as irreversible.
`Stop-Migration` was added in Rivet 0.6.
.EXAMPLE
Stop-Migration
Demonstrates how to use use `Stop-Migration`.
.EXAMPLE
Stop-Migration -Message 'The datatabase's flibbers have been upgraed to flobbers. This operation can't be undone. Sorry.'
Demonstrates how to display a message explaining why the migration isn't reversible.
#>
[CmdletBinding()]
param(
[string]
# A message to show that explains why the migrations isn't reversible. Default message is `This migration is irreversible and can't be popped.`.
$Message = 'This migration is irreversible and can''t be popped.'
)
Set-StrictMode -Version 'Latest'
New-Object -TypeName 'Rivet.Operations.IrreversibleOperation' -ArgumentList $Message
}
Rivet\Functions\Operations\Update-CodeObjectMetadata.ps1
Rivet\Functions\Operations\Update-Description.ps1
function Update-Description
{
<#
.SYNOPSIS
Updates the `MS_Description` extended property of a table or column.
.DESCRIPTION
The `sys.sp_updateextendedproperty` stored procedure is used to update a table/column's description (i.e. the `MS_Description` extended property), but the syntax is weird. This function hides that weirdness from you. You're welcome.
.EXAMPLE
Update-Description -Description 'Whoseit's whatsits table.' -TableName WhoseitsWhatsits
Updates the description (i.e. the `MS_Description` extended property) on the `WhoseitsWhatsits` table.
.EXAMPLE
Update-Description -Description 'Is it a snarfblat?' -TableName WhoseitsWhatsits -ColumnName IsSnarfblat
Updates the description (i.e. the `MS_Description` extended property) on the `WhoseitsWhatsits` table's `IsSnarfblat` column.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The value for the MS_Description extended property.
$Description,
[Alias('Schema')]
[string]
# The schema. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true)]
[Alias('Table')]
[string]
# The name of the table where the extended property is getting updated.
$TableName,
[Parameter(ParameterSetName='ForColumn')]
[Alias('Column')]
[string]
# The name of the column where the extended property is getting updated.
$ColumnName
)
Set-StrictMode -Version 'Latest'
$optionalArgs = @{ }
if( $ColumnName )
{
$optionalArgs.ColumnName = $ColumnName
}
Update-ExtendedProperty -Name 'MS_Description' `
-Value $Description `
-SchemaName $SchemaName `
-TableName $TableName `
@optionalArgs
}
Rivet\Functions\Operations\Update-ExtendedProperty.ps1
function Update-ExtendedProperty
{
<#
.SYNOPSIS
Updates an object's extended property.
.DESCRIPTION
SQL Server has a special stored procedure for updating extended property metatdata about an object. Unfortunately, it has a really clunky interface. This function is an attempt to wrap `sp_updateextendedproperty` with a saner interface.
Currently, this function only supports updating properties for schemas, tables, and columns. Submit a patch!
.LINK
Add-Description
.LINK
Add-ExtendedProperty
.LINK
Remove-Description
.LINK
Remove-ExtendedProperty
.LINK
Update-Description
.LINK
Update-ExtendedProperty
.EXAMPLE
Update-ExtendedProperty -Name 'Deploy' -Value 'FALSE' -SchemaName 'spike'
Sets the custom `Deploy` metadata to be `FALSE`.
.EXAMPLE
Update-ExtendedProperty -Name 'Deploy' -Value 'FALSE' -TableName 'Food'
Sets the custom `Deploy` metadata to be `FALSE` on the `Food` table in the `dbo` schema.
.EXAMPLE
Update-ExtendedProperty -Name 'IsEncrypted' -Value 'TRUE' -TableName 'User' -ColumnName 'Password'
Sets the custom `IsEncrypted` metadata to be `TRUE` on the `User` table's `Password` column.
.EXAMPLE
Update-ExtendedProperty -Name 'ContainsPII' -Value 'FALSE' -View 'LoggedInUsers'
Demonstrates how to update custom metadata on the `LoggedInUsers` view
.EXAMPLE
Update-ExtendedProperty -Name 'IsEncrypted' -Value 'FALSE' -View 'LoggedInUsers' -ColumnName 'Password'
Demonstrates how to update custom metadata for a view's column
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the extended property to update.
$Name,
[Parameter(Mandatory=$true,Position=1)]
[AllowNull()]
# The value of the extended property.
$Value,
[Parameter(ParameterSetName='SCHEMA')]
[Parameter(ParameterSetName='TABLE')]
[Parameter(ParameterSetName='TABLE-COLUMN')]
[Parameter(ParameterSetName='VIEW')]
[Parameter(ParameterSetName='VIEW-COLUMN')]
[string]
# The schema of the object.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,ParameterSetName='TABLE')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Table')]
[string]
# The table name.
$TableName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW')]
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Alias('View')]
[string]
# The table name.
$ViewName,
[Parameter(Mandatory=$true,ParameterSetName='VIEW-COLUMN')]
[Parameter(Mandatory=$true,ParameterSetName='TABLE-COLUMN')]
[Alias('Column')]
[string]
# The column name.
$ColumnName
)
Set-StrictMode -Version 'Latest'
$objectName = ''
if ($PsCmdlet.ParameterSetName -eq "SCHEMA")
{
$op = New-Object 'Rivet.Operations.UpdateExtendedPropertyOperation' $SchemaName, $Name, $Value
$objectName = $SchemaName
}
if ($PsCmdlet.ParameterSetName -eq "TABLE")
{
$op = New-Object 'Rivet.Operations.UpdateExtendedPropertyOperation' $SchemaName, $TableName, $Name, $Value, $false
$objectName = '{0}.{1}' -f $SchemaName,$TableName
}
if ($PsCmdlet.ParameterSetName -eq "VIEW")
{
$op = New-Object 'Rivet.Operations.UpdateExtendedPropertyOperation' $SchemaName, $ViewName, $Name, $Value, $true
$objectName = '{0}.{1}' -f $SchemaName,$ViewName
}
if ($PsCmdlet.ParameterSetName -eq "TABLE-COLUMN")
{
$op = New-Object 'Rivet.Operations.UpdateExtendedPropertyOperation' $SchemaName, $TableName, $ColumnName, $Name, $Value, $false
$objectName = '{0}.{1}.{2}' -f $SchemaName,$TableName,$ColumnName
}
if ($PsCmdlet.ParameterSetName -eq "VIEW-COLUMN")
{
$op = New-Object 'Rivet.Operations.UpdateExtendedPropertyOperation' $SchemaName, $ViewName, $ColumnName, $Name, $Value, $true
$objectName = '{0}.{1}.{2}' -f $SchemaName,$ViewName,$ColumnName
}
return $op
}
Rivet\Functions\Operations\Update-Row.ps1
function Update-Row
{
<#
.SYNOPSIS
Updates a row of data in a table.
.DESCRIPTION
To specify which columns in a row to update, pass a hashtable as a value to the `Column` parameter. This hashtable should have keys that map to column names, and the value of each key will be used to update row(s) in the table.
You are required to use a `Where` clause so that you don't inadvertently/accidentally update a column in every row in a table to the same value. If you *do* want to update the value in every row of the database, omit the `Where` parameter and add the `Force` switch.
.EXAMPLE
Update-Row -SchemaName 'rivet' 'Migrations' @{ LastUpdated = (Get-Date -Utc) } -Where 'MigrationID=20130913131104'
Demonstrates how to update the `LastUpdated` date in the `rivet.Migrations` table for the migration with ID `20130913131104`. Don't do this in real life.
.EXAMPLE
Update-Row -SchemaName 'rivet' 'Migrations' @{ LastUpdated = (Get-Date -Utc) } -Force
Demonstrates how to update the `LastUpdated` date *for all rows* in the `rivet.Migrations` table. You *really, really* don't want to do this in real life.
.EXAMPLE
Update-Row 'Migrations' @{ MigrationID = 'MigrationID + 1' } -Raw -Where 'MigrationID=20130101010101'
Demonstrates how to pass a SQL expression as the value for the column to update: use the `-RawColumnValue` switch.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$TableName,
[Parameter()]
[string]
# The schema name of the table. Default is `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[Hashtable]
# A hashtable of name/value pairs that map to column names/values that will be updated.
$Column,
[Switch]
# Don't escape/quote the column value(s).
$RawColumnValue,
[Parameter(Mandatory=$true,Position=2,ParameterSetName='SpecificRows')]
[string]
# A condition to use so that only certain rows are updated. Without a value, you will need to use the `Force` parameter so you don't accidentally update the contents of an entire table.
$Where,
[Parameter(Mandatory=$true,ParameterSetName='AllRows')]
[Switch]
# Updates all the rows in the table.
$All
)
Set-StrictMode -Version 'Latest'
if ($PSCmdlet.ParameterSetName -eq 'SpecificRows')
{
$op = New-Object 'Rivet.Operations.UpdateRowOperation' $SchemaName, $TableName, $Column, $Where, $RawColumnValue
}
elseif ($PSCmdlet.ParameterSetName -eq 'AllRows')
{
$op = New-Object 'Rivet.Operations.UpdateRowOperation' $SchemaName, $TableName, $Column, $RawColumnValue
}
return $op
}
Rivet\Functions\Operations\Update-StoredProcedure.ps1
function Update-StoredProcedure
{
<#
.SYNOPSIS
Updates an existing stored procedure.
.DESCRIPTION
Updates an existing stored procedure.
.LINK
https://msdn.microsoft.com/en-us/library/ms189762.aspx
.EXAMPLE
Update-StoredProcedure -SchemaName 'rivet' 'ReadMigrations' 'AS select * from rivet.Migrations'
Updates a stored procedure to read the migrations from Rivet's Migrations table. Note that in real life, you probably should leave my table alone.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the stored procedure.
$Name,
[Parameter()]
[string]
# The schema name of the stored procedure. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The store procedure's definition, which is everything after the `alter procedure [schema].[name]` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.UpdateStoredProcedureOperation' $SchemaName, $Name, $Definition
}
Rivet\Functions\Operations\Update-Table.ps1
function Update-Table
{
<#
.SYNOPSIS
Adds new columns or alters existing columns on an existing table.
.DESCRIPTION
The `Update-Table` operation adds, updates, and removes columns from a table. Columns are added, then updated, then removed.
The new columns for the table should be created and returned in a script block, which is passed as the value of the `AddColumn` parameter. For example,
Update-Table 'Suits' -AddColumn {
Bit 'HasVest' -NotNull -Default 0
}
The new definitions for existing columns should be created and returned in a script block, which is passed as the value of the `UpdateColumn` parameter. For example,
Update-Table 'Suits' -UpdateColumn {
VarChar 'Color' 256 -NotNull
}
.LINK
bigint
.LINK
binary
.LINK
bit
.LINK
char
.LINK
date
.LINK
datetime
.LINK
datetime2
.LINK
datetimeoffset
.LINK
decimal
.LINK
float
.LINK
hierarchyid
.LINK
int
.LINK
money
.LINK
nchar
.LINK
numeric
.LINK
nvarchar
.LINK
real
.LINK
rowversion
.LINK
smalldatetime
.LINK
smallint
.LINK
smallmoney
.LINK
sqlvariant
.LINK
time
.LINK
tinyint
.LINK
uniqueidentifier
.LINK
varbinary
.LINK
varchar
.LINK
xml
.EXAMPLE
Update-Table -Name 'Ties' -AddColumn { VarChar 'Color' 50 -NotNull }
Adds a new `Color` column to the `Ties` table. Pretty!
.EXAMPLE
Update-Table -Name 'Ties' -UpdateColumn { VarChar 'Color' 100 -NotNull }
Demonstrates how to change the definition of an existing column.
.EXAMPLE
Update-Table -Name 'Ties' -RemoveColumn 'Pattern','Manufacturer'
Demonstrates how to remove columns from a table.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the table.
$Name,
[string]
# The table's schema. Defaults to `dbo`.
$SchemaName = 'dbo',
[Alias('Add')]
[ScriptBlock]
# A script block that returns the new columns to add to a table.
$AddColumn,
[Alias('Update')]
[Alias('Alter')]
[ScriptBlock]
# A script block that returns new column definitions for existing columns
$UpdateColumn,
[Alias('Remove')]
[string[]]
# Columns to remove.
$RemoveColumn
)
Set-StrictMode -Version 'Latest'
$newColumns = @()
if ($AddColumn)
{
[Object[]]$newColumns = & $AddColumn
}
$updatedColumns = @()
if ($UpdateColumn)
{
[Object[]]$updatedColumns = & $UpdateColumn
}
New-Object 'Rivet.Operations.UpdateTableOperation' $SchemaName,$Name,$newColumns,$updatedColumns,$RemoveColumn
foreach ($i in $newColumns)
{
if ($i.Description)
{
Add-Description -Description $i.Description -SchemaName $SchemaName -TableName $Name -ColumnName $i.Name
}
}
foreach ($i in $updatedColumns)
{
if ($i.Description)
{
Update-Description -Description $i.Description -SchemaName $SchemaName -TableName $Name -ColumnName $i.Name
}
}
}
Rivet\Functions\Operations\Update-Trigger.ps1
function Update-Trigger
{
<#
.SYNOPSIS
Updates an existing trigger.
.DESCRIPTION
Updates an existing trigger.
.LINK
https://msdn.microsoft.com/en-us/library/ms176072.aspx
.LINK
Add-Trigger
Remove-Trigger
.EXAMPLE
Update-Trigger 'PrintMessage' 'ON rivet.Migrations for insert as print ''Migration applied!'''
Updates a trigger to prints a method when a row gets inserted into the `rivet.Migrations` table.
#>
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the trigger.
$Name,
[Parameter()]
[string]
# The schema of the trigger.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The body of the trigger. Everything after and including the `ON` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object 'Rivet.Operations.UpdateTriggerOperation' $SchemaName, $Name, $Definition
}
Rivet\Functions\Operations\Update-UserDefinedFunction.ps1
function Update-UserDefinedFunction
{
<#
.SYNOPSIS
Updates an existing user-defined function.
.DESCRIPTION
Updates an existing user-defined function.
.LINK
https://msdn.microsoft.com/en-us/library/ms186967.aspx
.EXAMPLE
Update-UserDefinedFunction -SchemaName 'rivet' 'One' 'returns tinyint begin return 1 end'
Updates a user-defined function to return the number 1.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the stored procedure.
$Name,
[Parameter()]
[string]
# The schema name of the stored procedure. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The store procedure's definition. Everything after the `alter function [schema].[name]` clause.
$Definition
)
New-Object Rivet.Operations.UpdateUserDefinedFunctionOperation $SchemaName,$Name,$Definition
}
Rivet\Functions\Operations\Update-View.ps1
function Update-View
{
<#
.SYNOPSIS
Updates an existing view.
.DESCRIPTION
Updates an existing view.
.LINK
https://msdn.microsoft.com/en-us/library/ms173846.aspx
.EXAMPLE
Update-View -SchemaName 'rivet' 'ReadMigrations' 'AS select * from rivet.Migrations'
Updates a view to read all the migrations from Rivet's Migrations table. Don't do this in real life.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]
# The name of the view.
$Name,
[Parameter()]
[string]
# The schema name of the view. Defaults to `dbo`.
$SchemaName = 'dbo',
[Parameter(Mandatory=$true,Position=1)]
[string]
# The definition of the view. Everything after the `alter view [schema].[name]` clause.
$Definition
)
Set-StrictMode -Version 'Latest'
New-Object Rivet.Operations.UpdateViewOperation $SchemaName,$Name,$Definition
}
Rivet\Functions\Split-SqlBatchQuery.ps1
function Split-SqlBatchQuery
{
<#
.SYNOPSIS
Splits a SQL batch query into individual queries.
.DESCRIPTION
`Split-SqlBatchQuery` takes a batch query and splits it by the `GO` statements it contains. `GO` statements inside comments and strings are ignored. It does not use regular expressions.
If the query has no `GO` statements, you'll get your original query back.
You can pipe SQL batch queries into this function and you'll get runnable queries out the other side.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
[string]
$Query
)
begin
{
}
process
{
Set-StrictMode -Version 'Latest'
$currentQuery = New-Object 'Text.StringBuilder'
$inSingleLineComment = $false
$inMultiLineComment = $false
$inString = $false
$stringCouldBeEnding = $false
$prevChar = $null
$currentChar = $null
$commentDepth = 0
$currentLine = New-Object 'Text.StringBuilder'
function Complete-Line
{
Write-Debug -Message ("inMultiLineComment: {0}; inSingleLineComment: {1}; inString {2}; {3}" -f $inMultiLineComment,$inSingleLineComment,$inString,$currentLine.ToString())
$trimmedLine = $currentLine.ToString().Trim()
if( $trimmedLine -notmatch "^GO\b" )
{
[void]$currentQuery.Append( $currentLine )
}
$currentLine.Length = 0
if( $trimmedLine -match "^GO\b" -or $atLastChar )
{
$currentQuery.ToString()
$currentQuery.Length = 0
}
}
$chars = $Query.ToCharArray()
for( $idx = 0; $idx -lt $chars.Count; ++$idx )
{
$prevChar = $null
if( $idx -gt 1 )
{
$prevChar = $chars[$idx - 1]
}
$currentChar = $chars[$idx]
$nextChar = $null
if( $idx + 1 -lt $chars.Count )
{
$nextChar = $chars[$idx + 1]
}
$atLastChar = $idx -eq $chars.Count - 1
if( $atLastChar )
{
[void]$currentLine.Append( $currentChar )
Complete-Line
continue
}
if( $inMultiLineComment )
{
[void] $currentLine.Append( $currentChar )
if( $prevChar -eq '/' -and $currentChar -eq '*' )
{
Write-Debug -Message ('Entering nested multi-line comment.')
$commentDepth++
continue
}
elseif( $prevChar -eq '*' -and $currentChar -eq '/' )
{
Write-Debug -Message ('Leaving multi-line comment.')
$commentDepth--
$inMultiLineComment = ($commentDepth -gt 0)
}
if( -not $inMultiLineComment )
{
Write-Debug -Message ('Multi-line comment closed.')
}
continue
}
if( $inSingleLineComment )
{
if( $currentChar -eq "`n" )
{
Write-Debug -Message ('Leaving single-line comment.')
$inSingleLineComment = $false
}
else
{
[void] $currentLine.Append( $currentChar )
continue
}
}
if( $inString )
{
if( $stringCouldBeEnding )
{
$stringCouldBeEnding = $false
if( $currentChar -eq "'" )
{
[void] $currentLine.Append( $currentChar )
Write-Debug -Message ('Found escaped quote.')
continue
}
else
{
Write-Debug -Message ('Leaving string.')
$inString = $false
}
}
elseif( $currentChar -eq "'" )
{
[void] $currentLine.Append( $currentChar )
$stringCouldBeEnding = $true
continue
}
else
{
[void]$currentLine.Append( $currentChar )
continue
}
}
if( $prevChar -eq "/" -and $currentChar -eq "*" )
{
Write-Debug -Message ('Entering multi-line comment.')
$inMultiLineComment = $true
$commentDepth++
}
elseif( $prevChar -eq '-' -and $currentChar -eq '-' )
{
Write-Debug -Message ('Entering single-line comment.')
$inSingleLineComment = $true
}
elseif( $currentChar -eq "'" )
{
Write-Debug -Message ('Entering string.')
$inString = $true
}
[void] $currentLine.Append( $currentChar )
if( $currentChar -eq "`n" -or $atLastChar )
{
Complete-Line
}
}
}
end
{
}
}
Rivet\Functions\Test-Migration.ps1
function Test-Migration
{
<#
.SYNOPSIS
Tests if a migration was applied to the database.
.DESCRIPTION
Returns `true` if a migration with the given ID has already been applied. `False` otherwise.
.EXAMPLE
Test-Migration -ID 20120211235838
Returns `True` if a migration with ID `20120211235838` already exists or `False` if it doesn't.
#>
param(
[Parameter(Mandatory=$true)]
[Int64]
$ID,
[Switch]
# Returns the migration info.
$PassThru
)
$query = 'select ID, Name, Who, AtUtc from {0} where ID=@ID' -f $RivetMigrationsTableFullName,$ID
$info = Invoke-Query -Query $query -Parameter @{ ID = $ID } -Verbose:$false
if( $info )
{
Write-Debug -Message ('{0} {1,-35} {2,14:00000000000000}_{3}' -f $info.AtUtc.ToLocalTime().ToString('yyyy-mm-dd HH:mm'),$info.Who,$info.ID,$info.Name)
if( $PassThru )
{
return $info
}
return $true
}
return $false
}
Rivet\Functions\Update-Database.ps1
function Update-Database
{
<#
.SYNOPSIS
Applies a set of migrations to the database.
.DESCRIPTION
By default, applies all unapplied migrations to the database. You can reverse all migrations with the `Down` switch.
.EXAMPLE
Update-Database -Path C:\Projects\Rivet\Databases\Rivet\Migrations
Applies all un-applied migrations from the `C:\Projects\Rivet\Databases\Rivet\Migrations` directory.
.EXAMPLE
Update-Database -Path C:\Projects\Rivet\Databases\Rivet\Migrations -Pop
Reverses all migrations in the `C:\Projects\Rivet\Databases\Rivet\Migrations` directory
#>
[CmdletBinding(DefaultParameterSetName='Push', SupportsShouldProcess=$True)]
param(
[Parameter(Mandatory=$true)]
[Rivet.Configuration.Configuration]
$Configuration,
[Parameter(Mandatory=$true)]
[string[]]
# The path to the migration.
$Path,
[Parameter(Mandatory=$true,ParameterSetName='Pop')]
[Parameter(Mandatory=$true,ParameterSetName='PopByName')]
[Parameter(Mandatory=$true,ParameterSetName='PopByCount')]
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Reverse the given migration(s).
$Pop,
[Parameter(ParameterSetName='Push')]
[Parameter(Mandatory=$true,ParameterSetName='PopByName')]
[string[]]
$Name,
[Parameter(Mandatory=$true,ParameterSetName='PopByCount')]
[UInt32]
# Reverse the given migration(s).
$Count,
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Reverse the given migration(s).
$All,
[Switch]
# Running internal Rivet migrations. This is for internal use only. If you use this flag, Rivet will break when you upgrade. You've been warned!
$RivetSchema,
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Switch]
# Force popping a migration you didn't apply or that is old.
$Force
)
Set-StrictMode -Version 'Latest'
function ConvertTo-RelativeTime
{
param(
[Parameter(Mandatory=$true)]
[DateTime]
# The date time to convert to a relative time string.
$DateTime
)
[TimeSpan]$howLongAgo = (Get-Date) - $DateTime
$howLongAgoMsg = New-Object 'Text.StringBuilder'
if( $howLongAgo.Days )
{
[void] $howLongAgoMsg.AppendFormat('{0} day', $howLongAgo.Days)
if( $howLongAgo.Days -ne 1 )
{
[void] $howLongAgoMsg.Append('s')
}
[void] $howLongAgoMsg.Append(', ')
}
if( $howLongAgo.Days -or $howLongAgo.Hours )
{
[void] $howLongAgoMsg.AppendFormat('{0} hour', $howLongAgo.Hours)
if( $howLongAgo.Hours -ne 1 )
{
[void] $howLongAgoMsg.Append('s')
}
[void] $howLongAgoMsg.Append(', ')
}
if( $howLongAgo.Days -or $howLongAgo.Hours -or $howLongAgo.Minutes )
{
[void] $howLongAgoMsg.AppendFormat('{0} minute', $howLongAgo.Minutes)
if( $howLongAgo.Minutes -ne 1 )
{
[void] $howLongAgoMsg.Append('s')
}
[void] $howLongAgoMsg.Append(', ')
}
[void] $howLongAgoMsg.AppendFormat('{0} second', $howLongAgo.Seconds)
if( $howLongAgo.Minutes -ne 1 )
{
[void] $howLongAgoMsg.Append('s')
}
[void] $howLongAgoMsg.Append( ' ago' )
return $howLongAgoMsg.ToString()
}
$stopMigrating = $false
$popping = ($PSCmdlet.ParameterSetName -like 'Pop*')
$numPopped = 0
$who = ('{0}\{1}' -f $env:USERDOMAIN,$env:USERNAME);
#$matchedNames = @{ }
$byName = @{ }
if( $PSBoundParameters.ContainsKey('Name') )
{
$byName['Include'] = $Name
}
Get-MigrationFile -Path $Path -Configuration $Configuration @byName -ErrorAction Stop |
Sort-Object -Property 'MigrationID' -Descending:$popping |
Where-Object {
if( $RivetSchema )
{
return $true
}
if( [int64]$_.MigrationID -lt 1000000000000 )
{
Write-Error ('Migration ''{0}'' has an invalid ID. IDs lower than 01000000000000 are reserved for internal use.' -f $_.FullName)
$stopMigrating = $true
return $false
}
return $true
} |
Where-Object {
$migration = $null
$preErrorCount = $Global:Error.Count
try
{
$migration = Test-Migration -ID $_.MigrationID -PassThru #-ErrorAction Ignore
}
catch
{
$errorCount = $Global:Error.Count - $preErrorCount
for( $idx = 0; $idx -lt $errorCount; ++$idx )
{
$Global:Error.RemoveAt(0)
}
}
if( $popping )
{
if( $PSCmdlet.ParameterSetName -eq 'PopByCount' -and $numPopped -ge $Count )
{
return $false
}
$numPopped++
$youngerThan = ((Get-Date).ToUniversalTime()) - (New-TimeSpan -Minutes 20)
if( $migration -and ($migration.Who -ne $who -or $migration.AtUtc -lt $youngerThan) )
{
$howLongAgo = ConvertTo-RelativeTime -DateTime ($migration.AtUtc.ToLocalTime())
$confirmQuery = "Are you sure you want to pop migration {0} from database {1} on {2} applied by {3} {4}?" -f $_.FullName,$Connection.Database,$Connection.DataSource,$migration.Who,$howLongAgo
$confirmCaption = "Pop Migration {0}?" -f $_.FullName
if( -not $Force -and -not $PSCmdlet.ShouldContinue( $confirmQuery, $confirmCaption ) )
{
return $false
}
}
$migration
}
else
{
-not ($migration)
}
} |
ForEach-Object {
if( $stopMigrating )
{
return
}
else
{
return $_
}
} |
Convert-FileInfoToMigration -Configuration $Configuration |
ForEach-Object {
$migrationInfo = $_
$migrationInfo.DataSource = $Connection.DataSource
try
{
$Connection.Transaction = $Connection.BeginTransaction()
if( $Pop )
{
$operations = $migrationInfo.PopOperations
$action = 'Pop'
$sprocName = 'RemoveMigration'
}
else
{
$operations = $migrationInfo.PushOperations
$action = 'Push'
$sprocName = 'InsertMigration'
}
if( -not $operations.Count )
{
Write-Error ('{0} migration''s {1}-Migration function is empty.' -f $migrationInfo.FullName,$action)
return
}
$operations | Invoke-MigrationOperation -Migration $migrationInfo
$query = 'exec [rivet].[{0}] @ID = @ID, @Name = @Name, @Who = @Who, @ComputerName = @ComputerName' -f $sprocName
$parameters = @{
ID = [int64]$migrationInfo.ID;
Name = $migrationInfo.Name;
Who = $who;
ComputerName = $env:COMPUTERNAME;
}
Invoke-Query -Query $query -NonQuery -Parameter $parameters | Out-Null
$target = '{0}.{1}' -f $Connection.DataSource,$Connection.Database
$operation = '{0} migration {1} {2}' -f $PSCmdlet.ParameterSetName,$migrationInfo.ID,$migrationInfo.Name
if ($PSCmdlet.ShouldProcess($target, $operation))
{
$Connection.Transaction.Commit()
}
else
{
$stopMigrating = $true
$Connection.Transaction.Rollback()
}
}
catch
{
$Connection.Transaction.Rollback()
$stopMigrating = $true
# TODO: Create custom exception for migration query errors so that we can report here when unknown things happen.
if( $_.Exception -isnot [ApplicationException] )
{
Write-RivetError -Message ('Migration {0} failed' -f $migrationInfo.Path) -CategoryInfo $_.CategoryInfo.Category -ErrorID $_.FullyQualifiedErrorID -Exception $_.Exception -CallStack ($_.ScriptStackTrace)
}
}
finally
{
$Connection.Transaction = $null
}
}
}
Rivet\Functions\Write-RivetError.ps1
function Write-RivetError
{
param(
[Parameter(Mandatory=$true)]
[string]
# The error message to display.
$Message,
[Parameter(Mandatory=$true)]
[Exception]
# The exception being reported.
$Exception,
[Parameter(Mandatory=$true)]
[string]
# The call stack to report.
$CallStack,
[Parameter(Mandatory=$true)]
[string]
# The Category Info
$CategoryInfo,
[Parameter(Mandatory=$true)]
[string]
# The Fully Qualified Error ID
$ErrorID,
[Parameter()]
[string]
# Query, if any
$Query
)
$firstException = $_.Exception
while( $firstException.InnerException )
{
$firstException = $firstException.InnerException
}
if (-not $Query)
{
$Query = "None"
}
Write-Error (@"
[{0}].[{1}] {2}: {3}
{4}
QUERY
=====
{5}
"@ -f $Connection.DataSource,$Connection.Database,$Message,$firstException.Message,($CallStack -replace "`n","`n "), $Query) -ErrorID $ErrorID -Category $CategoryInfo
}
Rivet\Import-Rivet.ps1
<#
.SYNOPSIS
Imports the Rivet module.
.DESCRIPTION
When writing migrations, it can be helpful to get intellisense. In order to do so, you'll need to import Rivet.
.EXAMPLE
Import-Rivet.ps1
Demonstrates how to import the Rivet module.
#>
[CmdletBinding()]
param(
)
#Requires -Version 4
Set-StrictMode -Version Latest
if( (Get-Module Rivet) )
{
Remove-Module Rivet -Verbose:$false -Confirm:$false -WhatIf:$false
}
Import-Module (Join-Path -Path $PSScriptRoot -ChildPath Rivet.psd1 -Resolve) -Verbose:$false
Rivet\LICENSE
Rivet\Migrations\00000000000001_RivetInitializeDatabase.ps1
function Push-Migration
{
Invoke-Ddl -Query @'
if not exists (select * from sys.schemas where name = 'rivet')
exec sp_executesql N'create schema [rivet] authorization [dbo]'
if not exists (select * from
sys.schemas s join
sys.database_principals dp on s.principal_id = dp.principal_id
where
s.name = 'rivet' and
dp.name = 'dbo')
alter authorization on schema::[rivet] to [dbo]
if not exists (select * from
sys.database_permissions dbperms join
sys.schemas s on dbperms.major_id=s.schema_id join
sys.database_principals dbid on dbperms.grantee_principal_id = dbid.principal_id
where
s.name = 'rivet' and
dbid.name = 'public')
grant control on schema::[rivet] to [public]
if object_id('pstep.Migrations', 'U') is not null
alter schema [rivet] transfer [pstep].[Migrations]
if exists (select * from sys.schemas where name = 'pstep')
drop schema [pstep]
if object_id('rivet.Migrations', 'U') is null
begin
create table [rivet].[Migrations] (
[ID] bigint not null,
[Name] nvarchar(50) not null,
[Who] nvarchar(50) not null,
[ComputerName] nvarchar(50) not null,
[AtUtc] datetime not null
)
alter table [rivet].[Migrations] add constraint [MigrationsPK] primary key ( [ID] )
alter table [rivet].[Migrations] add constraint [AtUtcDefault] default (getutcdate()) for [AtUtc]
end
if object_id('rivet.AtUtcDefault', 'D') is not null and object_id('rivet.DF_Migrations_AtUtc', 'D') is null
begin
exec sp_rename 'rivet.AtUtcDefault', 'DF_rivet_Migrations_AtUtc', 'OBJECT'
end
if exists (select * from sys.columns c join
sys.tables t on c.object_id = t.object_id join
sys.types y on c.system_type_id = y.system_type_id
where
schema_name(t.schema_id) = 'rivet' and
t.name = 'Migrations' and
c.name = 'AtUtc' and
y.name = 'datetime')
begin
alter table [rivet].[Migrations] drop constraint [DF_rivet_Migrations_AtUtc]
alter table [rivet].[Migrations] alter column [AtUtc] datetime2(7) not null
alter table [rivet].[Migrations] add constraint [DF_rivet_Migrations_AtUtc] default (GetUtcDate()) for [AtUtc]
end
if object_id('rivet.MigrationsPK', 'PK') is not null and object_id('rivet.PK_rivet_Migrations', 'PK') is null
begin
exec sp_rename 'rivet.MigrationsPK', 'PK_rivet_Migrations', 'OBJECT'
end
if object_id('rivet.Activity', 'U') is null
begin
create table [rivet].[Activity] (
[ID] int identity,
[Operation] nvarchar(4) not null,
[MigrationID] bigint not null,
[Name] nvarchar(50) not null,
[Who] nvarchar(50) not null,
[ComputerName] nvarchar(50) not null,
[AtUtc] datetime2(7) not null
)
alter table [rivet].[Activity] add constraint [PK_rivet_Activity_ID] primary key ([ID])
alter table [rivet].[Activity] add constraint [DF_rivet_Activity_AtUtc] DEFAULT (getutcdate()) FOR [AtUtc]
alter table [rivet].[Activity] with check add constraint [CK_rivet_Activity_Operation] CHECK (([Operation]='Push' OR [Operation]='Pop'))
end
if object_id('rivet.PK_rivet_Activity_ID', 'PK') is not null and object_id('rivet.PK_rivet_Activity', 'PK') is null
begin
exec sp_rename 'rivet.PK_rivet_Activity_ID', 'PK_rivet_Activity', 'OBJECT'
end
if object_id('rivet.InsertMigration', 'P') is null
exec sp_executesql N'
create procedure [rivet].[InsertMigration]
@ID bigint,
@Name varchar(50),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
declare @AtUtc datetime2(7)
select @AtUtc = getutcdate()
insert into [rivet].[Migrations] ([ID],[Name],[Who],[ComputerName],[AtUtc]) values (@ID,@Name,@Who,@ComputerName,@AtUtc)
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values (''Push'',@ID,@Name,@Who,@ComputerName,@AtUtc)
end
'
if object_id('rivet.RemoveMigration', 'P') is null
exec sp_executesql N'
create procedure [rivet].[RemoveMigration]
@ID bigint,
@Name varchar(50),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
delete from [rivet].[Migrations] where [ID] = @ID
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values (''Pop'',@ID,@Name,@Who,@ComputerName,getutcdate())
end
'
'@
}
function Pop-Migration
{
Remove-StoredProcedure -SchemaName 'rivet' -Name 'RemoveMigration'
Remove-StoredProcedure -SchemaName 'rivet' -Name 'InsertMigration'
Remove-Table -SchemaName 'rivet' -Name 'Activity'
Remove-Table -SchemaName 'rivet' -Name 'Migrations'
Remove-Schema -Name 'rivet'
}
Rivet\Migrations\00000000000002_RivetIncreaseMigrationName.ps1
function Push-Migration
{
Update-Table -SchemaName 'rivet' -Name 'Migrations' -UpdateColumn {
nvarchar 'Name' 241 -NotNull
}
Update-Table -SchemaName 'rivet' -Name 'Activity' -UpdateColumn {
nvarchar 'Name' 241 -NotNull
}
Update-StoredProcedure -SchemaName 'rivet' -Name 'InsertMigration' -Definition @'
@ID bigint,
@Name varchar(241),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
declare @AtUtc datetime2(7)
select @AtUtc = getutcdate()
insert into [rivet].[Migrations] ([ID],[Name],[Who],[ComputerName],[AtUtc]) values (@ID,@Name,@Who,@ComputerName,@AtUtc)
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Push',@ID,@Name,@Who,@ComputerName,@AtUtc)
end
'@
Update-StoredProcedure -SchemaName 'rivet' -Name 'RemoveMigration' -Definition @'
@ID bigint,
@Name varchar(241),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
delete from [rivet].[Migrations] where [ID] = @ID
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Pop',@ID,@Name,@Who,@ComputerName,getutcdate())
end
'@
}
function Pop-Migration
{
Update-StoredProcedure -SchemaName 'rivet' -Name 'RemoveMigration' -Definition @'
@ID bigint,
@Name varchar(50),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
delete from [rivet].[Migrations] where [ID] = @ID
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Pop',@ID,@Name,@Who,@ComputerName,getutcdate())
end
'@
Update-StoredProcedure -SchemaName 'rivet' -Name 'InsertMigration' -Definition @'
@ID bigint,
@Name varchar(50),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
declare @AtUtc datetime2(7)
select @AtUtc = getutcdate()
insert into [rivet].[Migrations] ([ID],[Name],[Who],[ComputerName],[AtUtc]) values (@ID,@Name,@Who,@ComputerName,@AtUtc)
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Push',@ID,@Name,@Who,@ComputerName,@AtUtc)
end
'@
Update-Table -SchemaName 'rivet' -Name 'Migrations' -UpdateColumn {
nvarchar 'Name' 50 -NotNull
}
}
Rivet\Migrations\00000000000003_RivetChangeStoredProcedureVarcharParametersToNVarchar.ps1
function Push-Migration
{
Update-StoredProcedure -SchemaName 'rivet' -Name 'InsertMigration' -Definition @'
@ID bigint,
@Name nvarchar(241),
@Who nvarchar(50),
@ComputerName nvarchar(50)
as
begin
declare @AtUtc datetime2(7)
select @AtUtc = getutcdate()
insert into [rivet].[Migrations] ([ID],[Name],[Who],[ComputerName],[AtUtc]) values (@ID,@Name,@Who,@ComputerName,@AtUtc)
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Push',@ID,@Name,@Who,@ComputerName,@AtUtc)
end
'@
Update-StoredProcedure -SchemaName 'rivet' -Name 'RemoveMigration' -Definition @'
@ID bigint,
@Name nvarchar(241),
@Who nvarchar(50),
@ComputerName nvarchar(50)
as
begin
delete from [rivet].[Migrations] where [ID] = @ID
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Pop',@ID,@Name,@Who,@ComputerName,getutcdate())
end
'@
}
function Pop-Migration
{
Update-StoredProcedure -SchemaName 'rivet' -Name 'InsertMigration' -Definition @'
@ID bigint,
@Name varchar(241),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
declare @AtUtc datetime2(7)
select @AtUtc = getutcdate()
insert into [rivet].[Migrations] ([ID],[Name],[Who],[ComputerName],[AtUtc]) values (@ID,@Name,@Who,@ComputerName,@AtUtc)
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Push',@ID,@Name,@Who,@ComputerName,@AtUtc)
end
'@
Update-StoredProcedure -SchemaName 'rivet' -Name 'RemoveMigration' -Definition @'
@ID bigint,
@Name varchar(241),
@Who varchar(50),
@ComputerName varchar(50)
as
begin
delete from [rivet].[Migrations] where [ID] = @ID
insert into [rivet].[Activity] ([Operation],[MigrationID],[Name],[Who],[ComputerName],[AtUtc]) values ('Pop',@ID,@Name,@Who,@ComputerName,getutcdate())
end
'@
}
Rivet\NOTICE
Rivet\RELEASE_NOTES.txt
# 0.8.1 (26 November 2016)
* Removing a custom operation that isn't part of core Rivet.
# 0.8.0
## Enhancements
* Created `Merge-Migration` function for creating cumulative, roll up migrations.
# 0.7.0
## Enhancements
* Fixed: `Add-Index` operation times out when creating new indexes on large table. Added a `Timeout` parameter to control how long to wait for an operation to finish.
* `Add-Index` re-implemented as a C# cmdlet.
# 0.6.1
## Bug Fixes
* `Rename-Column`, `Rename-DataType`, `Rename-Index`, and `Rename-Object` operations didn't properly quote schema and object names.
# 0.6.0
## Enhancements
* Improving verbose output to be more recognizable and include query timings.
* `Convert-Migration.ps1` extra script now puts triggers, constraints, foreign keys, and types into separate files.
* `New-Migration` now increments timestamp if a migration with the same timestamp already exists instead of sleeping for half a second.
* Added format for `Rivet.Migration` objects so they display nicely when running migrations.
* Adding Rivet about help topics.
* Created `Add-RowGuidCol` operation for adding the `rowguidcol` property to a column.
* Created `Remove-RowGuidCol` operation for removing the `rowguidcol` property from a column.
* Created `Stop-Migration` operation for preventing a migration from getting popped/reversed.
* Migrations missing Push-Migration/Pop-Migration functions are no longer allowed and will fail when pushed/popped.
* Migrations with empty Push-Migration/Pop-Migration functions are no longer allowed and will fail when pushed/popped.
* Obsoleted the parameter sets of the `Remove-CheckConstraint`, `Remove-DefaulConstraint`, `Remove-ForeignKey`, `Remove-Index`, `Remove-PrimaryKey`, and `Remove-UniqueKey` operations that use an inferred constraint/index name. These operations now expect the name of the constraint/index to drop with the `Name` parameter.
* Improved object model so that customizing index/constraint names is easier.
* Added `about_Rivet_Cookbook` help topic to showing how to customize index/constraint names.
* Updated and improved the `about_Rivet_Plugins` help topic.
* Obsoleted the `Enable-ForeignKey` and `Disable-ForeignKey` operations. Use the `Enable-Constraint` and `Disable-Constraint` operations instead.
* Renamed the `Enable-CheckConstraint` operation to `Enable-Constraint`, with a backwards-compatible alias.
* Renamed the `Disable-CheckConstraint` operation to `Disable-Constraint`, with a backwards-compatible alias.
* You can now push, pop, or create multiple migrations at once (i.e. `rivet.ps1`'s `Name` parameter now accepts multiple names, IDs, or file names).
* Plug-ins now get passed a `Rivet.Migration` object for the operation being processed.
* Rivet now supports writing custom operations.
## Bug Fixes
* Results from `Invoke-SqlScript` operations cause silent error when formatted as a table.
* Path to rivet.json file not showing in an error message when using implicit path.
# 0.5.1
## Enhancements
* Improving `WhatIf` support: some actions that shouldn't be conditional now ignore `WhatIf` flag.
* Invoke-SqlScript operation no longer splits script into batches, since that is now handled internally when executing all operations.
* Improving verbose output: adding a message for each already-applied migration.
## Bug Fixes
* Get-Migration fails when run from Convert-Migration: it doesn't know the path to use to load migrations from.
# 0.5.0
## Enhancements
* The Add-Schema operation is now idempotent.
* Removed all Write-Host output.
* Rivet now returns OperationResult objects for each query executed by an operation. Default format included (i.e. this output replaces the old Write-Host output).
* Renamed `Invoke-Query` operation to `Invoke-Ddl`.
* Renamed `Rivet.Operations.RawQueryOperation` to `Rivet.Operations.RawDdlOperation`.
* Moved `Rivet.Operations.Operation` object into `Rivet` namespace; so full type name is now `Rivet.Operation`.
# 0.4.0
## Enhancements
* NOCHECK parameter has been added to `Add-ForeignKey` and `Add-CheckConstraint` operations
* `Disable-CheckConstraint` and `Enable-CheckConstraint` functions have been added.
* `Disable-ForeignKey` and `Enable-ForeignKey` functions have been added.
## Bug Fixes
* Convert-Migration.ps1 generates incorrect SQL if a migration removes then re-adds a column.
# 0.3.3
* Improved error message when failing to connect to SQL Server.
* `Add-Index` operation now supports INCLUDE clause.
# 0.3.2
## Bug Fixes
* `Invoke-SqlScript` fails when `NonQuery` switch is used.
# 0.3.1
## Enhancements
* `Get-RivetConfig` is now a publicly exposed function. Use this method to parse a Rivet JSON configuration file. It returns a `Rivet.Configuration.Configuration` object.
# 0.3.0
## Enhancements
* `Get-Migration` now returns a `Rivet.Operations.ScriptFileOperation` object instead of a `Rivet.Operations.RawQueryOperation` for `Invoke-SqlQuery` operations.
## Bug Fixes
* `Invoke-SqlScript` ignoring `CommandTimeout` parameter.
* `Invoke-SqlScript` didn't rollback migration if the script file was not found.
* `Get-Migration` fails if a migration doesn't contain a `Push-Migration` or `Pop-Migration` function.
* `Get-Migratoin` duplicates output of previous migration if a migration is missing a `Push-Migration` or `Pop-Migration` function.
# 0.2.1
## Bug Fixes
* If a database has multipe target databases and no migrations directory, Rivet stops after the first target database.
# 0.2.0
* Databases are now created if they don't exist.
* A single database connection is now re-used when migrating multiple databases, instead of establishing a new connection for each database.
* A database's migrations can now be applied to multiple target databases via the new `TargetDatabases` configuration option. See `about_Rivet_Configuration` for more information.
* Rivet now updates its internal objects using migrations (i.e. it is now self-migrating). It uses (and reserves) migration IDs below 01000000000000. If you have migrations with these IDs, you'll need to give them new IDs and update IDs in any rivet.Migrations table that uses that ID.
* Migration name maximum length increased to 241 characters (the theoretical maximum allowed by Windows).
Rivet\rivet.ps1
<#
.SYNOPSIS
A database migration tool for PowerShell.
.DESCRIPTION
Rivet is a database migration tool for SQL Server. Finally!
This script is the entry point for Rivet. It is used to create a new migration, and apply/revert migrations against a database.
Called without any arguments, Rivet will shows this help topic.
.LINK
about_Rivet
.LINK
about_Rivet_Configuration
.LINK
about_Rivet_Migrations
.EXAMPLE
rivet.ps1 -New 'CreateTableStarships'
Creates a new `CreateTableStarships` migration in all databases.
.EXAMPLE
rivet.ps1 -Push
Applies all migrations.
.EXAMPLE
rivet.ps1 -Push 'CreateTableStarships'
Demonstrates how to apply a named migration. Don't include the timestamp. Wildcards are permitted.
*Be careful with this syntax!* If the named migration(s) depend on other migrations that haven't been run, the migration will fail.
.EXAMPLE
rivet.ps1 -Pop
Reverts the last migration script.
.EXAMPLE
rivet.ps1 -Pop 5
Demonstrates how to revert multiple migrations. The last `-Count` migrations will be popped.
.EXAMPLE
rivet.ps1 -Pop 'AddTable'
Demonstrates how to pop a specific migration. Wildcards supported. Will match either the migration's name or ID.
.EXAMPLE
rivet.ps1 -Redo
Reverts the last migration script, then reapplies its. Equivalent to
rivet.ps1 -Pop
rivet.ps1 -Push
.EXAMPLE
rivet.ps1 -Push -Environment Production
Demonstrates how to migrate databases in a different environment. The `Production` environment should be specified in the `rivet.json` configuration file.
#>
#Requires -Version 3
[CmdletBinding(DefaultParameterSetName='ShowHelp', SupportsShouldProcess=$True)]
param(
[Parameter(Mandatory=$true,ParameterSetName='New')]
[Switch]
# Creates a new migration.
$New,
[Parameter(Mandatory=$true,ParameterSetName='Push')]
[Switch]
# Applies migrations.
$Push,
[Parameter(Mandatory=$true,ParameterSetName='Pop')]
[Parameter(Mandatory=$true,ParameterSetName='PopByCount')]
[Parameter(Mandatory=$true,ParameterSetName='PopByName')]
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Reverts migrations.
$Pop,
[Parameter(Mandatory=$true,ParameterSetName='Redo')]
[Switch]
# Reverts a migration, then re-applies it.
$Redo,
[Parameter(Mandatory=$true,ParameterSetName='New',Position=1)]
[Parameter(ParameterSetName='Push',Position=1)]
[Parameter(Mandatory=$true,ParameterSetName='PopByName',Position=1)]
[ValidateLength(1,241)]
[string[]]
# The name of the migrations to create, push, or pop. Matches against the migration's ID, Name, or file name (without extension). Wildcards permitted.
$Name,
[Parameter(Mandatory=$true,ParameterSetName='PopByCount',Position=1)]
[UInt32]
# The number of migrations to pop. Default is 1.
$Count = 1,
[Parameter(Mandatory=$true,ParameterSetName='PopAll')]
[Switch]
# Pop all migrations
$All,
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Switch]
# Force popping a migration you didn't apply or that is old.
$Force,
[Parameter(ParameterSetName='New',Position=2)]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string[]]
# The database(s) to migrate. Optional. Will operate on all databases otherwise.
$Database,
[Parameter(ParameterSetName='New')]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string]
# The environment you're working in. Controls which settings Rivet loads from the `rivet.json` configuration file.
$Environment,
[Parameter(ParameterSetName='New')]
[Parameter(ParameterSetName='Push')]
[Parameter(ParameterSetName='Pop')]
[Parameter(ParameterSetName='PopByCount')]
[Parameter(ParameterSetName='PopByName')]
[Parameter(ParameterSetName='PopAll')]
[Parameter(ParameterSetName='Redo')]
[string]
# The path to the Rivet configuration file. Default behavior is to look in the current directory for a `rivet.json` file. See `about_Rivet_Configuration` for more information.
$ConfigFilePath
)
Set-StrictMode -Version Latest
if( $PSCmdlet.ParameterSetName -eq 'ShowHelp' )
{
Get-Help $PSCommandPath
return
}
& (Join-Path -Path $PSScriptRoot -ChildPath Import-Rivet.ps1 -Resolve)
Invoke-Rivet @PSBoundParameters
exit $error.Count
Rivet\Rivet.psd1
#
# Module manifest for module 'Rivet'
#
# Generated by: Aaron Jensen
#
# Generated on: 1/25/2013
#
@{
# Script module or binary module file associated with this manifest
RootModule = 'Rivet.psm1'
# Version number of this module.
ModuleVersion = '0.8.1'
# ID used to uniquely identify this module
GUID = '8af34b47-259b-4630-a945-75d38c33b94d'
# Author of this module
Author = 'Aaron Jensen'
# Company or vendor of this module
CompanyName = ''
# Copyright statement for this module
Copyright = 'Copyright 2013 - 2016 Aaron Jensen.'
# Description of the functionality provided by this module
Description = @'
Rivet is a database migration/change management/versioning tool inspired by Ruby on Rails' Migrations. It creates and applies migration scripts for SQL Server databases. Migration scripts describe changes to make to your database, e.g. add a table, add a column, remove an index, etc. Migrations scripts should get added to your version control system so they can be packaged and deployed with your application's code.
'@
# Minimum version of the Windows PowerShell engine required by this module
PowerShellVersion = ''
# Name of the Windows PowerShell host required by this module
PowerShellHostName = ''
# Minimum version of the Windows PowerShell host required by this module
PowerShellHostVersion = ''
# Minimum version of the .NET Framework required by this module
DotNetFrameworkVersion = ''
# Minimum version of the common language runtime (CLR) required by this module
CLRVersion = ''
# Processor architecture (None, X86, Amd64, IA64) required by this module
ProcessorArchitecture = ''
# Modules that must be imported into the global environment prior to importing this module
RequiredModules = @()
# Assemblies that must be loaded prior to importing this module
RequiredAssemblies = @( 'bin\Rivet.dll' )
# Script files (.ps1) that are run in the caller's environment prior to importing this module
ScriptsToProcess = @()
# Type files (.ps1xml) to be loaded when importing this module
#TypesToProcess = ''
# Format files (.ps1xml) to be loaded when importing this module
FormatsToProcess = @(
'Formats\RivetMigrationResult-GroupingFormat.format.ps1xml',
'Formats\RivetOperationResult-GroupingFormat.format.ps1xml',
'Formats\Rivet.Migration.format.ps1xml',
'Formats\Rivet.OperationResult.format.ps1xml'
)
# Modules to import as nested modules of the module specified in ModuleToProcess
NestedModules = @( 'bin\Rivet.dll' )
# Functions to export from this module
FunctionsToExport = @(
'Add-CheckConstraint',
'Add-DataType',
'Add-DefaultConstraint',
'Add-Description',
'Add-ExtendedProperty',
'Add-ForeignKey',
'Add-PrimaryKey',
'Add-Row',
'Add-RowGuidCol',
'Add-Schema',
'Add-StoredProcedure',
'Add-Synonym',
'Add-Table',
'Add-Trigger',
'Add-UniqueKey',
'Add-UserDefinedFunction',
'Add-View',
'Disable-Constraint',
'Enable-Constraint',
'Get-Migration',
'Get-RivetConfig',
'Invoke-Ddl',
'Invoke-Rivet',
'Invoke-SqlScript',
'Merge-Migration',
'New-BigIntColumn',
'New-BinaryColumn',
'New-BitColumn',
'New-CharColumn',
'New-Column',
'New-DateColumn',
'New-DateTime2Column',
'New-DateTimeColumn',
'New-DateTimeOffsetColumn',
'New-DecimalColumn',
'New-FloatColumn',
'New-HierarchyIDColumn',
'New-IntColumn',
'New-MoneyColumn',
'New-NCharColumn',
'New-NVarCharColumn',
'New-RealColumn',
'New-RowVersionColumn',
'New-SmallDateTimeColumn',
'New-SmallIntColumn',
'New-SmallMoneyColumn',
'New-SqlVariantColumn',
'New-TimeColumn',
'New-TinyIntColumn',
'New-UniqueIdentifierColumn',
'New-VarBinaryColumn',
'New-VarCharColumn',
'New-XmlColumn',
'Remove-CheckConstraint',
'Remove-DataType',
'Remove-DefaultConstraint',
'Remove-Description',
'Remove-ExtendedProperty',
'Remove-ForeignKey',
'Remove-Index',
'Remove-PrimaryKey',
'Remove-Row',
'Remove-RowGuidCol',
'Remove-Schema',
'Remove-StoredProcedure',
'Remove-Synonym',
'Remove-Table',
'Remove-Trigger',
'Remove-UniqueKey',
'Remove-UserDefinedFunction',
'Remove-View',
'Rename-Column',
'Rename-DataType',
'Rename-Index',
'Rename-Object',
'Stop-Migration',
'Update-CodeObjectMetadata',
'Update-Description',
'Update-ExtendedProperty',
'Update-Row',
'Update-StoredProcedure',
'Update-Table',
'Update-Trigger',
'Update-UserDefinedFunction',
'Update-View',
'*' # For plug-ins.
)
# Cmdlets to export from this module
CmdletsToExport = '*'
# Variables to export from this module
VariablesToExport = ''
# Aliases to export from this module
AliasesToExport = '*'
# List of all modules packaged with this module
ModuleList = @()
# List of all files packaged with this module
FileList = @()
# Private data to pass to the module specified in ModuleToProcess
PrivateData = @{
PSData = @{
# Tags applied to this module. These help with module discovery in online galleries.
Tags = @('sql-server','evolutionary-database','database','migrations')
# A URL to the license for this module.
LicenseUri = 'http://www.apache.org/licenses/LICENSE-2.0'
# A URL to the main website for this project.
ProjectUri = 'http://get-silk.org'
# A URL to an icon representing this module.
# IconUri = ''
# ReleaseNotes of this module
ReleaseNotes = @'
* Removing a custom operation that isn't part of core Rivet.
'@
} # End of PSData hashtable
} # End of PrivateData hashtable
}
Rivet\Rivet.psm1
$Connection = New-Object Data.SqlClient.SqlConnection
$RivetSchemaName = 'rivet'
$RivetMigrationsTableName = 'Migrations'
$RivetMigrationsTableFullName = '{0}.{1}' -f $RivetSchemaName,$RivetMigrationsTableName
$RivetActivityTableName = 'Activity'
function Test-TypeDataMember
{
[CmdletBinding()]
[OutputType([bool])]
param(
[Parameter(Mandatory=$true)]
[string]
# The type name to check.
$TypeName,
[Parameter(Mandatory=$true)]
[string]
# The name of the member to check.
$MemberName
)
Set-StrictMode -Version 'Latest'
$typeData = Get-TypeData -TypeName $TypeName
if( -not $typeData )
{
# The type isn't defined or there is no extended type data on it.
return $false
}
return $typeData.Members.ContainsKey( $MemberName )
}
if( -not (Test-TypeDataMember -TypeName 'Rivet.OperationResult' -MemberName 'MigrationID') )
{
Update-TypeData -TypeName 'Rivet.OperationResult' -MemberType ScriptProperty -MemberName 'MigrationID' -Value { $this.Migration.ID }
}
$functionRoot = Join-Path -Path $PSScriptRoot -ChildPath 'Functions' -Resolve
$columnRoot = Join-Path -Path $functionRoot -ChildPath 'Columns' -Resolve
$operationsRoot = Join-Path -Path $functionRoot -ChildPath 'Operations' -Resolve
@(
$functionRoot,
$operationsRoot,
$columnRoot
) |
Get-ChildItem -Filter '*-*.ps1' |
Where-Object { $_.BaseName -ne 'Export-Row' } |
ForEach-Object { . $_.FullName }
$privateFunctions = @{
'Connect-Database' = $true;
'Convert-FileInfoToMigration' = $true;
'Disable-ForeignKey' = $true;
'Disconnect-Database' = $true;
'Enable-ForeignKey' = $true;
'Get-MigrationFile' = $true;
'Import-Plugin' = $true;
'Initialize-Database' = $true;
'Invoke-MigrationOperation' = $true;
'Invoke-Query' = $true;
'New-MigrationObject' = $true;
'Split-SqlBatchQuery' = $true;
'Test-Migration' = $true;
'Update-Database' = $true;
'Write-RivetError' = $true;
}
$publicFunctions = Invoke-Command -ScriptBlock {
@(
'Get-Migration',
'Get-RivetConfig',
'Invoke-Rivet'
)
Get-ChildItem -Path $operationsRoot,$functionRoot,$columnRoot -Filter '*.ps1' |
Select-Object -ExpandProperty 'BaseName'
} |
Where-Object { -not $privateFunctions.ContainsKey( $_ ) }
Export-ModuleMember -Function $publicFunctions -Alias '*' -Cmdlet '*'
Rivet\rivet.sample.json
{
SqlServerName: '.\\HS_A',
DatabasesRoot: '.\\Test\\Databases',
DatabaseScriptsRoot: '.\\Test\\Databases',
PluginsRoot: '.\\Plugins',
ConnectionTimeout: 15,
CommandTimeout: 30,
IgnoreDatabases: [ 'Shared' ]
}
Tools\chocolateyInstall.ps1
Tools\chocolateyUninstall.ps1
<#
.SYNOPSIS
Chocolately install script for Silk.
#>
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.[CmdletBinding()]
param(
)
#Requires -Version 4
Set-StrictMode -Version 'Latest'
$ErrorActionPreference = 'Stop'
$moduleName = 'Rivet'
$errorCount = $Global:Error.Count
Write-Verbose -Message ('Error Count: {0}' -f $errorCount)
try
{
$env:PSModulePath -split ';' |
Where-Object { $_ } |
Join-Path -ChildPath $moduleName |
Where-Object { Test-Path -Path $_ -PathType Container } |
Rename-Item -NewName { '{0}{1}' -f $moduleName,[IO.Path]::GetRandomFileName() } -PassThru |
Remove-Item -Recurse -Force
}
finally
{
Write-Verbose -Message ('Error Count: {0}' -f $Global:Error.Count)
for( $idx = $errorCount; $idx -lt $Global:Error.Count; ++$idx )
{
$Global:Error[$idx - $errorCount]
$Global:Error[$idx - $errorCount] | Format-List -Property '*' -Force | Out-String | Write-Verbose -Verbose
}
}
Virus Scan Results
Log in or click on link to see number of positives.
- Rivet.0.8.1.nupkg (0ab4fa7da98c) - ## / 55
- Rivet.dll (52b03a67a099) - ## / 57
In cases where actual malware is found, the packages are subject to removal. Software sometimes has false positives. Moderators do not necessarily validate the safety of the underlying software, only that a package retrieves software from the official distribution point and/or validate embedded software against official distribution point (where distribution rights allow redistribution).
Chocolatey Pro provides runtime protection from possible malware.
Copyright
Copyright 2013 - 2016 Aaron Jensen.
Release Notes
- Removing a custom operation that isn't part of core Rivet.
Dependencies
This package has no dependencies.
Discussion for the Rivet Package
Ground Rules:
- This discussion is only about Rivet and the Rivet package. If you have feedback for Chocolatey, please contact the Google Group.
- This discussion will carry over multiple versions. If you have a comment about a particular version, please note that in your comments.
- The maintainers of this Chocolatey Package will be notified about new comments that are posted to this Disqus thread, however, it is NOT a guarantee that you will get a response. If you do not hear back from the maintainers after posting a message below, please follow up by using the link on the left side of this page or follow this link to contact maintainers. If you still hear nothing back, please follow the package triage process.
- Tell us what you love about the package or Rivet, or tell us what needs improvement.
- Share your experiences with the package, or extra configuration or gotchas that you've found.
- If you use a url, the comment will be flagged for moderation until you've been whitelisted. Disqus moderated comments are approved on a weekly schedule if not sooner. It could take between 1-5 days for your comment to show up.