The Table Journal checks twice for the existence of the Schema Table (by doing a select count) - once should be enough.

For example, from an approval test

Open connection
Begin transaction
Execute scalar command: select count(*) from [SchemaVersions]
Dispose command
Commit transaction
Dispose transaction
Begin transaction
Execute non query command: print 'script1'
Dispose command
Commit transaction
Dispose transaction
Begin transaction
Execute scalar command: select count(*) from [SchemaVersions]
Dispose command
Commit transaction
Dispose transaction
Begin transaction
Execute non query command: create table [SchemaVersions] (
    [Id] int identity(1,1) not null constraint PK_SchemaVersions_Id primary key,
    [ScriptName] nvarchar(255) not null,
    [Applied] datetime not null
)

It would be nice if DBUp would be capable to upgrade SQLite databases.

It would be nice if there was a feature that allows to revert scripts in the reverse order of execution down to an specific filename.
This would imply providing rollback scripts in an specific folder with the same names than the incremental scripts to allow an easy rollback for instance when a release is cancelled due to issues in the deployment.

We now have:

DbUp
DbUp-postgresql
DbUp-sqlce
DbUp-sqlite
DbUp-oracle (soon)

Should we build/release these separately or just push all 5 packages each release?

The SQLite support package has a dependency on System.Data.SQLite which drags in System.Data.SQLite.EF6 and thence EF. This is an implementation choice (and entirely bloatware if you aren't using EF) and could be easily decoupled / resolved (I think!) if the support pack referenced System.Data.SQLite.Core and System.Data.SQLite.Linq directly instead.

When adding DbUp to a monotouch project, the Linker cannot resolve the type Microsoft.SqlServer.Server.SqlContext type.

This type is used in one place:

internal class SqlContextUpgradeLog : IUpgradeLog
    {
        public void WriteInformation(string format, params object[] args)
        {
            SqlContext.Pipe.Send("INFO:  " + string.Format(format, args));
        }

        public void WriteError(string format, params object[] args)
        {
            SqlContext.Pipe.Send("ERROR: " + string.Format(format, args));
        }

        public void WriteWarning(string format, params object[] args)
        {
            SqlContext.Pipe.Send("WARN:  " + string.Format(format, args));
        }
    }

Perhaps we can compile for mono and use some compilation directives to exclude this class when compiling for mono / monotouch. The mono version of the System.Data doesn't appear to have this type.

Hi

I'm trying to use dbup console. I getting and error that SchemaVersions doesn't exists in my db, which is correct. My understanding is that this table should be auto created, but this doesn't happen. Is my understanding correct or this an issue?

Is it possible to add support so that it will create a new schema if it doesn't exist when checking if the Journal table exists? This is useful if you want to deploy to a new database and keep the migration meta data away from dbo by setting a custom journal table\schema

Most of the providers rely on the SqlScriptExecutor which is MS SqlServer oriented - if there are exceptions when a script is executed they are not handled and logged as provider specific exception - i.e for Firebird they are not handled as FbException - instead they are handled as DbException and only for SqlServer are they handled / logged as the more specific SqlException.

This can be improved, introduce an abstract base class ScriptExecutor and have provider specific implmentations such as SqlServerScriptExecutor/ FirebirdScriptExecutor etc. Those implementations can handle exceptions in a mannor concrete to the provider. This may mean a bit more information can be output in the logs when things go wrong.

i cannot seem to connect to my SQL CE database, the main application can connect to the database and perform all db related functions but DbUp doesn't seem to connect.

i get the following error:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at DbUp.Engine.Transactions.DatabaseConnectionManager.OperationStarting(IUpgradeLog upgradeLog, List1 executedScripts)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:00000000-0000-0000-0000-000000000000

any help would be appreciated.

I would love to be able to use the SqlLite provider on android devices (xamarin projects).

If you try and do this at the moment, it will complain at runtime regarding missing interop assemblies.

I think all we need is a version of the SqlLite DbUp provider that is compiled against the Mono.Data.Sqlite.Portable' nuget package rather than theSystem.Data.SQLite`

We can drop the postgresql package back to net35, the driver has net35 packages

When calling this line upgrader.IsUpgradeRequired(); I get a null reference.

When I add this line to my builder: upgraderBuilder.Configure(c => c.ConnectionManager.UpgradeStarting(new Logger()));
I get an exception, that it should only be called once, but i pass the step of upgrader.IsUpgradeRequired() and the exception occurs when calling upgrader.PerformUpgrade();

Hi

I'm using dbup console in my powershell scripts. I would like to know if the execution was successful or not. This is the feature I'm requesting.

This can be archived with exitcode, maybee there is a more elegant way with powershell, but exit code also work in cmd. My solutions was to modified the Program.cs to look like

        DatabaseUpgradeResult result = null;
        if (!mark)
        {
            result = dbup.PerformUpgrade();
        }
        else
        {
            result = dbup.MarkAsExecuted();
        }

        if (!result.Successful)
        {
            Environment.ExitCode = 1;
        }

This gives me the ability to ask $? in powershell if the run was successful or not. I couldn't get $lastexitcode to work. Don't know why.

The transaction strategy is throwing the null reference exception

SqlCommandReader.IsBeginningOfGo does not cater for whether there is any whitespace after a "go" combination of characters. This version now truncates scripts and fails to deploy where there is a whitespace combination before "go" regardless of what comes after those two characters.

For example, the following is a procedure that got truncated:

CREATE PROCEDURE dbo.GetDetails

    @AccountId uniqueidentifier

AS
BEGIN

SELECT AccountId,
        EstimatedInCents,
        OccupationInCents,
        GovernmentInCents,
-- other statements here

What DbUp executes is this:

CREATE PROCEDURE dbo.GetDetails

    @AccountId uniqueidentifier

AS
BEGIN

SELECT AccountId,
        EstimatedInCents,
        OccupationInCents,

Hi,

We would like to capture all output of the sql statements that are run by DbUp into a file. Do you have any experience on how this can be accomplished with DbUp?

We are typically interested in print statements and the results of select statements.

Using SqlCmd this can be easily done by specifying -o output.log but things seem to be more complicated when using ADO.NET. Subscribing to the SqlInfoMessageEventHandler would already give us the print and error statements but not the results of the select statements.

Do yo have any plans to implement the SqlInfoMessageEventHandler?

Thanks in advance,

Niek.

DbUp.Support.SqlServer.SqlScriptExecutor.Log(System.Data.IDataReader): virtual or abstract members cannot be private
..\Support\SqlServer\SqlScriptExecutor.cs, line 153

The logic in any of the filters in filters.cs fails if the original source of script files is located in a Directory (and, of course, the Directory name is passed in as a parameter). The "scriptNames.Contains(s)" fails because a filter script name does not contain a path, but the script file listings do include paths.

The logic is actually backwards where the longer path+file name string is being checked for a contain within the shorter file name string.

All works well only if a) scripts are in the root of the Project and no Scripts Directory is utilized or b) if the Directory path is prepended onto the filter script file name.

The nuget package for 3.3.0 has a typo in it - the id for the package is "dbup", and it needs to be "DbUp". This was an issue for me when I added DbUp to my own private nuget repo. (As a workaround I simply changed it to "DbUp" and created a "3.3.0.1" that I commited to my own repo).

Reported via NuGet.

Suggest you update your SQL Server Compact dependency to Microsoft.SqlServer.Compact, specifically version 4.0.8876.1 (which is 4.0 SP1) (as the 2 other published version are 4.0 SP1 CTP1 versions)

DbUp should eat it's own dogfood where the SchemaVersion table is concerened.

• Create/modify the SchemaVersion table using database scripts.
• Dynamically inject the SchemaVersion script(s) to run before any user-specified scripts.
• Include script to transformation any existing SchemaVersion data
• Add tests around the upgrade scenario

What will it take to make this happen?

SqlServerExtensions.SqlDatabase(SupportedDatabases, Func< IDbConnection >) has been marked as obsolete.

We are using DbUp to migrate SQL Azure databases with federations. Before we execute any scripts (but after the connection is open), we need to issue as USE FEDERATION command to re-connect to the federation where the script should execute.

Currently, we're doing it likes this:

DeployChanges.To
              .SqlDatabase(() =>
              {
                  SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Reporting"].ConnectionString);

                  connection.StateChange += new System.Data.StateChangeEventHandler(SetFederation);

                  return connection;
              })

Is there a new way to do this? or can we have this marked un-obsoleted?

Michael.

When I upgraded Dbup from 2.0.113 to 2.0.119 I faced an error in the very first dbupgrade,
the problem is relating to generated script to check and create schema:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'[dbo]') Exec('CREATE SCHEMA [dbo]')

shouldn't N'[dbo]' be simply N'dbo'?

Recently have to trace down an issue where DbUp failed to upgrade because it can't write to the App_Data folder (where my scripts resides)

Not sure if this is the right place. But it would be good if DbUp was more tightly intergrated into Octopus Deploy so when a deployment is finished it shows on the task summary screen what scripts were run.

When including comments in scripts such as the one below we are getting a missing end comment mark error.

/****** Object: Table [dbo].[CampaignSessions] Script Date: 11/18/2013 15:46:48 ******/

If you need to add parameters to a migration that you are adding with code then you need to use the provided IDbConnection to create and populate a command, open the connection yourself and execute the command. Then you need to return a dummy string for it to execute for the migration itself, which seems a bit of a hack.

It would be cool if there was a way to instead provide DbUp a populated IDbCommand and get it to execute it for you instead. Maybe another interface to allow this to be opt-in, e.g. ICommandScript or something?

Given this example SQL script:

select a from b
GO;
select b from c

DbUp's regex method of splitting won't cater for GO with the semicolon terminator - meaning it won't split correctly.

Until this is fixed the guidance would be: don't use SQL terminators with GO statements.

If i have sql files spread across folders the naming of the folder influences (breaks) the execution ordering of the files. The list of files should be ordered by their filename only imo.

Manifest resource names make this somewhat difficult as folder separators are replaced with periods however assuming we can say dont include periods in your filenames other than the extension you could write something like this:

    public class FlatSortedEmbeddedScriptProvider : IScriptProvider
    {
        private readonly Assembly _assembly;
        private readonly Func<string, bool> _filter;

        public FlatSortedEmbeddedScriptProvider(Assembly assembly, Func<string, bool> filter)
        {
            _assembly = assembly;
            _filter = filter;
        }

        public IEnumerable<SqlScript> GetScripts(IConnectionManager connectionManager)
        {
            return _assembly
                .GetManifestResourceNames()
                .Where(_filter)
                .OrderBy(x => Path.GetFileNameWithoutExtension(x).Substring(Path.GetFileNameWithoutExtension(x).LastIndexOf('.') + 1))
                .Select(s => SqlScript.FromStream(s, _assembly.GetManifestResourceStream(s)))
                .ToList();
        }
    }

Story

As a DbUp User, I would like to be able to use upgrade scripts from the file system in a multi-level folder structure in order to facilitate better organization of the upgrade scripts.

Explanation

When upgrade scripts are included in the DbUp project as internal resources, the Scripts folder can contain multiple levels of sub-folders to help organize the upgrade scripts. However, when upgrade scripts are provided from a file system folder that does not contain scripts marked as resources, all the scripts must be in the root of the specified folder. Any sub-folders will be ignored.

The path to the file relative to the specified starting point should be used as the "manifest name" for the script. This means that sub-folder names will be significant in the ordering of scripts for processing.

Strangely, we only have this problem in VS2015. If the SQL script filename has a comma in it, and you are using WithScriptsEmbeddedInAssembly(), you get the error:
Invalid option ''; Resource visibility must be either 'public' or 'private'

You mention that this project is under the MIT license on http://dbup.github.com however, I do not see a corresponding License file in the source code explicitly specifying that. Am I looking in the wrong location (License.txt in root) or is this something you can add?

How would you handle Sample/Development Data with DbUp?

Several database providers (Postgres, MySQL and Sqlite) use the SqlScriptExecutor which is currently under the SQL Server namespace. This results in log messages being generated with the words SQL Server in them:

Executing SQL Server script 'App.Data.Up.Scripts.Script-0001.sql'

If there is appetite for the change I'll send a PR.

Thoughts?

Pretty sure that installing the DbUp.SqlLite Nuget package shooudl include this dll - as without it, can;t really do anything :)

My console App have about 2 sec lag after all script to database has been published and application end.
I try to find where this is but with no luck.
How can i change this behavior?

I recently implemented the following in a dev branch of CrmUp: https://github.com/dazinator/CrmUp/wiki/Deployment-using-a-Deployment-Manifest

but not sure if its a feature that you might be interested in? It wouldn't be very difficult for me to adapt it if you were.

Does anyone rely on DbUp being strong named?

What is your scenario for requiring strong naming?

I'm using the latest release on nuget (3.2.1, right?). I set up my builder like this:

            var engine = DeployChanges.To
                .PostgresqlDatabase(_settings.ConnectionStrings[_settings.Connection])
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), (path) => FilterScript(path))
                .LogScriptOutput()
                .LogToConsole()
                .WithTransactionPerScript()
                .WithCustomEnvironment()
                .WithVariablesEnabled()
                .Build();

WithCustomEnvironment() is an extension method in my solution (names changed here manually, please excuse a typo if I made one):

namespace Migrations
{
    using System;
    using System.Collections.Generic;
    using DbUp.Builder;
    public static class UpgradeEngineBuilderExtensions
    {
        public static UpgradeEngineBuilder WithCustomEnvironment(this UpgradeEngineBuilder builder)
        {
            var envChannelName = Environment.GetEnvironmentVariable("CHANNEL_NAME");
            if (string.IsNullOrWhiteSpace(envChannelName))
                envChannelName = "sbcomall";

            var envBlah = Environment.GetEnvironmentVariable("BLAH");
            if (string.IsNullOrWhiteSpace(envBlah ))
                envBlah = "0000";

            var envDeeDah = Environment.GetEnvironmentVariable("DEEDAH");
            if (string.IsNullOrWhiteSpace(envDeeDah))
                envDeeDah= "aaa";


            builder.WithVariables(new Dictionary<string, string> {
                {"CHANNEL_NAME", envChannelName},
                {"BLAH", envBlah},
                {"DEEDAH", envDeeDah},
            });

            return builder;
        }
    }
}

In my SQL scripts, I'm using the variables like this:

-- Function notify_insert()
--   Can be invoked in an insert trigger for any table
--   will notify subscribers of insert event and send JSON of inserted row
CREATE OR REPLACE FUNCTION notify_insert() RETURNS TRIGGER AS $$
DECLARE
BEGIN
    PERFORM (
        pg_notify('$CHANNEL_NAME$',
                  CAST((SELECT row_to_json(row)
                          FROM (SELECT 'insert' AS Command,
                                            $BLAH$ AS Blah,
                                            $DEEDAH$ AS DeeDah,
                                            NEW) row)
                        AS VARCHAR)
        )
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

When I execute the engine, the Npgsql provider complains of an unterminated dollar-quoted string, which is particular to Postgresql syntax. However, it means that the variables weren't replaced (and the exception shows the unreplaced variables on the console).

I tried explicitly running the VariableSubstitutionPreprocessor on the scripts, and the substitutions worked fine (I just printed the contents to the console after running .Process() on each script's content, so I'm not sure I understand why it wouldn't work when a migration is actually running.

Ideas?

When i invoke method PerformUpgrade (DbUp.Engine.UpgradeEngine), and i have script with error, DbUp fails and method PerformUpgrade returned DatabaseUpgradeResult which contain Exception, but info about exception does not provide the name of the failed script.
I'm added some fix, please check it.

Any known issues?
Did anyone test this?

Can drop it back to net35. The driver has support

What I'd really like the is the ability to have DbUp have an option to create the database if it doesn't exist. I tried to solve this by having my first script do this:

0001_InitialCreate.sql

IF NOT EXISTS ( SELECT [Name] FROM sys.databases WHERE [name] = 'MyDatabase' )
BEGIN
    CREATE DATABASE [MyDatabase]
END
GO

USE [MyDatabase]
GO

IF NOT EXISTS ( SELECT * FROM sysobjects where Name='MyTable' and xtype='U')
    CREATE TABLE dbo.MyTable (
        [Id] INT NOT NULL PRIMARY KEY IDENTITY,
        [Name] VARCHAR(50) NOT NULL,
        [CreatedBy] VARCHAR(50) NOT NULL,
        [CreatedDate] DATETIME2(7) NOT NULL DEFAULT SYSDATETIME())
GO

Subsequent scripts look like this:

0002_AddAgeColumnToMyTable.sql

USE [MyDatabase]
GO

ALTER TABLE dbo.MyTable
ADD [Age] INT NULL
GO

This approach works fine if you leave the database name out of the connection string, and just use USE statements in your scripts. The one problem, though, is that it doesn't find the SchemaVersions table so it re-runs every script every time. (although it does actually find the table and insert the scripts into it).

This causes issue when attempting to delete a sql lite database file, after having performed an adhoc executereader in a unit test, as the reader isn't disposed.

I am including a fix for this in my PR (Shortly)

When is the next release to nuget planned?

If you get the source code fresh, then build the solution, you get a build error - this is due to the DbUp.Tests project building before the Postgres project.

I am submitting a PR related to #54 so will include this fix in that PR - or you can just fix it at any point yourself - takes 2 seconds :)

Each provider has it's own project, except ms sql server..
We should pull this out into its own provider

Hi
Great product!

Any plans on implementing functionality for setting the database back to a previous version.