This should have scripts that run only once, and only after a database is created. If you are hitting an existing database, these should not run.

Otherwise this causes errors with custom create scripts.

In the following case, we used RH v0.8.0.332.

We recently had a script that failed. The exception handling in DefaultDatabaseMigrator.cs tries to do database.rollback() and then record_script_in_scripts_run_errors_table(...). In our case, the rollback command also failed, and now we didn't get the logging of the original failure, we only see the error caused by database.rollback().

Our fix to find out what had happend was to pull the code from GIT, v0.8.5 and change the exceptionhandling in DefaultDatabaseMigrator.cs. We did the record_script_in_scripts_run_errors_table(...) before the database.rollback(). Ahh... now we got the error and found out that the script had actually timed out.

I can see that insert_script_run_error(...) fallsback to do logging if database can not be written to, but that dosn't help when the code was never run in the firstplace because database.rollback() failed.

The fix:

                catch (Exception ex)
                {
                    record_script_in_scripts_run_errors_table(script_name, sql_to_run, sql_statement, ex.Message, repository_version, repository_path);
                    database.rollback();
                    database.close_connection();
                    throw;
                }

Hi

We are trying to use a custom DatabaseType, inheriting from the SqlServer one.

The problem is that as soon as we specify the custom DatabaseType, we get the following error:

System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.build_session_factory(Action`1 additional_function)
2011-12-14 11:36:00,363 [RemoteServerSchedulerClient_Worker-2] ERROR roundhouse - RoundhousE encountered an error. You were running in a transaction though, so the database should be in the state it was in prior to this piece running. This does not include a drop/create or any creation of a database, as those items can not run in a transaction.
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.build_session_factory(Action`1 additional_function)
   at roundhouse.databases.DefaultDatabase`1.set_repository()
   at roundhouse.databases.AdoNetDatabase.open_connection(Boolean with_transaction)
   at roundhouse.migrators.DefaultDatabaseMigrator.open_connection(Boolean with_transaction)
   at roundhouse.runners.RoundhouseMigrationRunner.run()

It seems that currently the possible database types are hardcoded in NHibernateSessionFactoryBuilder.

Is it possible to add a facility to register a new database type?

I'm trying to setup roundhouse for oracle:

rh.exe --connectionstring="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyOracleHost)(PORT=1521))(CONNECT_DATA=(SID=MyOracleSID)));User Id=MyUserId;Password=MyPassword;Persist Security Info=false" -vf=revision.xml -vx=//commit/@revision -env=UNITTEST -ni --runallanytimescripts --dt=roundhouse.databases.oracle

I have installed the oracle client, but I get the following exception:

StructureMap Exception Code: 207
Internal exception while creating Instance 'c9958e7c-cac9-4d25-a47d-d7eae39b882b' of PluginType roundhouse.databases.Database, rh, Version=0.7.0.281, Culture=neutral, PublicKeyToken=null. Check the inner exception for more details.

No innerexception is shown, no logfile is created and no entries are added to the windows event log.

Any ideas?

RH should use log4net if it is there, but not depend on it. This applies only to the DLL versions of roundhouse (msbuild and lib)

https://gist.github.com/1301411

Is there a way to run an external task, such as aspnet_regsql.exe on a database after it's created? Here's my scenario. We have several systems that have a table that has a foreign key into the aspnet_Users table. Provisioning a new instance would be a little less error prone if there was a way to have RoundhousE create the database, run aspnet_regsql.exe on it, then start creating our tables.

Has anyone other than me had rh.exe get flagged as a security threat by Symantec Endpoint Protection?

In the roundhouse_version table, on an Oracle database, the columns entry_date and modified_date are not filled in (values are NULL).
The dates in the roundhouse_scriptsrun(errors) tables are filled in correctly.

Because the dates are missing, the check of the latest release sometimes fails. Also it is difficult to check the latest installed version like this (unless you order by id desc)

Can this be fixed?

#48 needs to be done first

Bit ironic really :)

We started with an old version of RH on our servers. Now when we get errors, we get the barf that RH cannot insert into the errors table (message is truncated).

Looks like Message field in the ScriptRunErrors table was changed from nvarchar(255) to ntext a while back, but RH doesn't version its own tables so we get the log barf instead of the real error message.

I can take a look at this, unfortunately, it may lead to a state of infinite recursion.

I have a script to turn on the "contained database" feature for SQL Server 2012. It needs to put the db in single user mode to get an exclusive lock.

ALTER DATABASE {{DatabaseName}} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Set db to be "contained"
ALTER DATABASE {{DatabaseName}} SET CONTAINMENT = PARTIAL WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE {{DatabaseName}} SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

The script runs fine, but the first thing Roundhouse does after it finishes is try to run a dynamic statement in the db being upgrade with sp_executesql that does an INSERT INTO RoundhousE.ScriptsRun followed by SELECT SCOPE_IDENTITY(). I get the following error:

could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()]

The process of putting the db in single user mode is closing all open connections and rolling back any open transactions. When I look a SQL trace, the INSERT statement isn't being sent to the db server. I'm guessing Roundhouse is expecting its db connection to be open, but it's not.

I have other ALTER DATABASE scripts in the AlterDatabase folder running before this one that do not generate this error. However, none of them put the db in single user mode or do the rollback immediate.

A small test today led me to believe we can internalize FNH/NH but leave this one public:

NHibernate.Cfg.MappingSchema.*

I'm using 0.8.5.0. I have a script in my function folder that calls "use master" and does a few things to the master. I then have a script that gets executed immediately after that in a different file which should alter the database RH was originally running against, but it is not creating my function. RH does not fail or show any error message.

See scripts below. Essentially 0.sql calls "use master", but 1.sql does not call "use OriginalDB" and I don't think it should, as OriginalDB is dynamic based on the connection string I pass in through the command line.

How do I get 1.sql to execute against the database passed in through the connection string? Maybe upgrade RH? Is there a different place I should put 0.sql so that it gets executed in a different context of 1.sql?

Thanks

In functions\0.sql:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

use master;

--drop login

IF EXISTS (
SELECT NAME
FROM master.dbo.syslogins
WHERE NAME = 'SOME-ASSEMBLY'
)
begin
drop login [SOME-ASSEMBLY]
END
GO
--drop asymetric key
IF EXISTS (
SELECT NAME
FROM sys.asymmetric_keys
WHERE NAME = 'SOME-ASYM-KEY'
)
begin
drop asymmetric key [SOME-ASYM-KEY]
END
GO
--create asymetric key
CREATE ASYMMETRIC KEY [SOME-ASYM-KEY]
FROM EXECUTABLE FILE = 'C:\SomePathToSignedAssembly.dll'
GO
--create login
create login [SOME-ASSEMBLY] from asymmetric key [SOME-ASYM-KEY]
GO
--grant external access to new login
grant unsafe assembly to [SOME-ASSEMBLY]
GO

Then in functions\1.sql

IF EXISTS (
SELECT NAME
FROM sysobjects
WHERE NAME = 'SOMEFUNC'
AND type = 'FS'
)
BEGIN
DROP function SOMEFUNC
END
GO

IF EXISTS (
SELECT NAME
FROM sys.assemblies
WHERE NAME = 'SOMEASSEMBLYNAME'
)
BEGIN
DROP assembly SOMEASSEMBLYNAME
END
GO

CREATE ASSEMBLY SOMEASSEMBLYNAME
FROM 'C:\SomePathToSignedAssembly.dll'
WITH PERMISSION_SET = UNSAFE

Go

CREATE Function [dbo].SOMEFUNC
returns datetime
AS EXTERNAL NAME [SOMEASSEMBLYNAME].[SomeNameSpacedClassName].[SOMEFUNC];
GO

Happening in version 0.8.5.0.

   Attempting to resolve version from C:\tmp\ddl\clean\_BuildInfo.xml using //buildInfo/version.
MSBUILD : warning : Unable to get version from xml file C:\tmp\ddl\clean\_BuildInfo.xml. File doesn't exist. [C:\tmp\ddl\clean\up.proj]
   Migrating (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) from version
   0 to 0.
   Versioning (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) database wi
  th version 0 based on .
MSBUILD : error : RoundhousE encountered an error.\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error : System.ArgumentException: The OracleParameter is already contained by another OracleParameterCollection.\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at System.Data.OracleClient.OracleParameterCollection.Validate(Int32 index, Object value)\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at System.Data.OracleClient.OracleParameterCollection.Add(Object value)\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.setup_database_command(String sql_to_run, ConnectionType connection_type, IEnumerable`1 parameters)
\r [C:\tmp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, IList`1 parameters)\r [C:\tmp\d
dl\clean\up.proj]
MSBUILD : error :    at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 parameters)\r [C:\tmp\ddl\clean\
up.proj]
MSBUILD : error :    at roundhouse.databases.oracle.OracleDatabase.insert_version_and_get_version_id(String repository_path, String repository_version)\r [C:\t
mp\ddl\clean\up.proj]
MSBUILD : error :    at roundhouse.migrators.DefaultDatabaseMigrator.version_the_database(String repository_path, String repository_version)\r [C:\tmp\ddl\clea
n\up.proj]
MSBUILD : error :    at roundhouse.runners.RoundhouseMigrationRunner.run() [C:\tmp\ddl\clean\up.proj]
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018: The "Roundhouse" task failed unexpectedly.\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018: System.ArgumentException: The OracleParameter is already contained by another OracleParameterCollection.\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at System.Data.OracleClient.OracleParameterCollection.Validate(Int32 index, Object value)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at System.Data.OracleClient.OracleParameterCollection.Add(Object value)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.setup_database_command(String sql_to_run, ConnectionType connection_typ
e, IEnumerable`1 parameters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.run_command_with(String sql_to_run, ConnectionType connection_type, ILi
st`1 parameters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.AdoNetDatabase.run_sql(String sql_to_run, ConnectionType connection_type, IList`1 para
meters)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.databases.oracle.OracleDatabase.insert_version_and_get_version_id(String repository_path, String
 repository_version)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.migrators.DefaultDatabaseMigrator.version_the_database(String repository_path, String repository
_version)\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.runners.RoundhouseMigrationRunner.run()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.tasks.Roundhouse.run_the_task()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at roundhouse.tasks.Roundhouse.Microsoft.Build.Framework.ITask.Execute()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()\r
C:\tmp\ddl\clean\up.proj(9,5): error MSB4018:    at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggi
ngContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)
Done Building Project "C:\tmp\ddl\clean\up.proj" (default targets) -- FAILED.

I think the new behavior is bad. Here is why.

Before the "fix", If you wanted an one-time-script to run again, all you had to do was rename it. If you didn't want it to run, it didn't. Now there is no way of telling RH not to run a modified one-time-script. This will cause loads of problems with some of our existing installs.

I propose we go back to the old behavior where the user has control over which scripts get re-executed. Anyone else?

Here is the information from Issue 35:

"Currently, if you set "warnononetimescriptchanges" true, RH will report the altered script and continue running. It will not attempt to execute the updated script.

This patch changes that behavior so that if you set "warnononetimescriptchanges" true, RH reports script alteration, then runs the updated script and records the new script run information. I think this is far more useful, and I gather from my conversations with Rob, it was the expected behavior.

If you have an objection to me merging this in, please give a shout."

Select * from {{DatabaseName}}.sys.database_files will give you the name and physical name of the file and log location so you can reroute during a restore (instead of needing a custom restore script).

When running RH against SQL Server 2000, it only creates one table. And it puts data here...something is wrong.

After installing the RH msbuild package from nuget I'm unable to build my application. The reason is that the roundhouse.dll contains FluentNHibernate. Here's the error:

error CS0433: The type 'FluentNHibernate.Cfg.Fluently' exists in both '...\Source\packages\FluentNHibernate.1.2.0.712\lib\FluentNHibernate.dll' and '...\Source\packages\roundhouse.msbuild.0.8.0.332\lib\roundhouse.dll'
error CS0433: The type 'FluentNHibernate.Cfg.Db.MsSqlConfiguration' exists in both '...\Source\packages\FluentNHibernate.1.2.0.712\lib\FluentNHibernate.dll' and '...\Source\packages\roundhouse.msbuild.0.8.0.332\lib\roundhouse.dll'

Is it possible to internalize FluentNHibernate in RoundhousE with ILMerge? Or reference the FluentNHibernate NuGet package?

An other possibility is that the nuget package doesn't add the roundhouse.dll and roundhouse.tasks.dll to the references of the project. It should do what the normal rh nuget package does, just a a txt file that can easily be removed.

From http://groups.google.com/group/chucknorrisframework/browse_thread/thread/c464e321e1a87f66?hl=en

I just brought down the RefreshDatabase package from Nuget and it appears to be bound to Log4Net 1.2.11.0. When I try to execute the migration, I get some binding errors stating that the roundhouse library is looking for 1.2.10.0. I removed that reference and added one to 1.2.10.0, and everything works now.

SearchAllSubdirectoriesInsteadOfTraverse - Each Migration folder's subdirectories are traversed by default. This option pulls back scripts from the main directory and all subdirectories at once. Defaults to 'false'

So we have RH running for some time. We got a lot of up-scripts. Now we reduce the number of upscripts by starting from scratch with a new Baseline script with current state of the database. Easy for new environments or dev environments where we can just delete existing database and recreate with new roundhouse baseline script. Not so easy with staging and production database with data we want to keep. Any thoughts on how to solve this issue?

It should just return string.Empty

so that its self contained

I would expect that when I enable that option I would:

• get a warning
• the script would be executed
• the hash code would be updated in the database

Instead, I:

• get a warning
• an indication that the script was skipped

I need to have them signed to be able to be included with Visual Studio Extensions and other fun stuff.

• Create database scripts, be sure there's a SPROC in the sprocs folder
• Run Roundhouse, everything runs
• Run Roundhouse, everything is skipped (SPROC hasn't been modified)
• modify SPROC file
• Run Roundhouse, SPROC is executed (as expected)

From now on...

• Run Roundhouse, SPROC is executed. Expected result is that it is skipped. But for each execution, the script is executed and a new record is placed into the ScriptsRun table

Initial Analysis:
I think that the issue is that the get_current_script_hash() function is returning the first entry from the table and it should be returning the last.

I will poke around with this and see if I can get a patch in for you guys, but I thought I would give you the heads up.

The 0003_AppPermissionsWiring.sql script provided in the sample (sample\db\SQLServer\TestRoundhousE\permissions\0003_AppPermissionsWiring.sql) references a field (is_table_type) that is not yet present in the SQL2005 version of sys.types (it was introduced with SQL2008).

The alias IsTableType does not appear in any other script so removing this column from the script should make the sample compatible with SQL2005

Writing the log file out will fail if it can't reach the folder above.

This is going to be a default behavior with a switch of /stoponfirsterror to turn it off.

We'll call this the "Round Robin" feature, aka the "Roundhouse" feature :)

A lot of scripts have debugging-style PRINT statements in them (e.g. usp_GetDate.sql and 003_AppPermissionsWiring.sql from the sample). We need an option to have the output of those PRINT statements show up in console/log output when they're run by RoundhousE.

At least in SQL and Oracle it should just be a matter of handling the InfoMessage event on the connection (it's not on the base class, and I don't know anything about your other databases...) http://www.dotnetcurry.com/ShowArticle.aspx?ID=344

Would it be possible to allow custom configuration options, which than can be used by the token replacer.

We have a requirement where Oracle tables and indexes should be created in custom tablespace, but at the moment I have to hardcode them inside the script.
As we want to do installation to multiple customers, all with different naming conventions, we want to parameterize these inside the scripts:

eg: create table test(n number) tablespace {{tabts}}
;

Where tabts is a custom configuration option. The same goes for index tablespaces.

I can cheat a little bit, for example, I use the indexFolderName and specify the index tablespace name to be used.
This means I need to create multiple install scripts, where files will be put in an unknown directory (depends on the name of the tablespace.

Also, sometimes I want to use more than 1 index tablespace name (for partitioned indexes for example).

Currently I do not see a way to work around this, apart from hardcoding the tablespace names

The directory traversal code is repetitious and obscures important differences in how the various migration directories are handled.

Refactor to an approach that allows the common elements to coalesce and the differences to stand out

Getting this when running from MSBuild and the ServerName property was not set. Revsion 169e773.

StructureMap.StructureMapException was unhandled by user code
  Message=StructureMap Exception Code:  207
Internal exception while creating Instance 'b11c83a7-4593-48f6-a2aa-31c843f44321' of PluginType roundhouse.folders.KnownFolders, roundhouse, Version=0.8.5.0, Culture=neutral, PublicKeyToken=91b86fd44f1f23bc.  Check the inner exception for more details.
  Source=StructureMap
  ErrorCode=207
  StackTrace:
       at StructureMap.Pipeline.LambdaInstance`1.build(Type pluginType, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\LambdaInstance.cs:line 29
       at StructureMap.Pipeline.Instance.createRawObject(Type pluginType, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\Instance.cs:line 103
       at StructureMap.Pipeline.Instance.Build(Type pluginType, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\Instance.cs:line 69
       at StructureMap.Pipeline.ObjectBuilder.ConstructNew(Type pluginType, Instance instance, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\ObjectBuilder.cs:line 39
       at StructureMap.Pipeline.ObjectBuilder.Resolve(Type pluginType, Instance instance, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\ObjectBuilder.cs:line 27
       at StructureMap.BuildSession.CreateInstance(Type pluginType, Instance instance) in c:\code\structuremap\Source\StructureMap\BuildSession.cs:line 153
       at StructureMap.BuildSession.<>c__DisplayClass3.<.ctor>b__1() in c:\code\structuremap\Source\StructureMap\BuildSession.cs:line 34
       at StructureMap.BuildSession.CreateInstance(Type pluginType) in c:\code\structuremap\Source\StructureMap\BuildSession.cs:line 192
       at StructureMap.Container.GetInstance(Type pluginType) in c:\code\structuremap\Source\StructureMap\Container.cs:line 265
       at StructureMap.Container.GetInstance[T]() in c:\code\structuremap\Source\StructureMap\Container.cs:line 155
       at roundhouse.infrastructure.containers.custom.StructureMapContainer.Resolve[TypeToReturn]() in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure\containers\custom\StructureMapContainer.cs:line 16
       at roundhouse.infrastructure.containers.Container.get_an_instance_of[TypeToGet]() in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure\containers\Container.cs:line 17
       at roundhouse.infrastructure.app.ApplicationConfiguraton.initialize_file_log_appender() in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure.app\ApplicationConfiguraton.cs:line 215
       at roundhouse.infrastructure.app.ApplicationConfiguraton.build_the_container(ConfigurationPropertyHolder configuration_property_holder) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure.app\ApplicationConfiguraton.cs:line 154
       at roundhouse.tasks.Roundhouse.run_the_task() in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse.tasks\Roundhouse.cs:line 149
       at roundhouse.tasks.Roundhouse.Microsoft.Build.Framework.ITask.Execute() in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse.tasks\Roundhouse.cs:line 30
       at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
       at Microsoft.Build.BackEnd.TaskBuilder.ExecuteInstantiatedTask(ITaskExecutionHost taskExecutionHost, TaskLoggingContext taskLoggingContext, TaskHost taskHost, ItemBucket bucket, TaskExecutionMode howToExecuteTask, Boolean& taskResult)
  InnerException: System.ArgumentException
       Message=Illegal characters in path.
       Source=mscorlib
       StackTrace:
            at System.IO.Path.CheckInvalidPathChars(String path)
            at System.IO.Path.GetFileName(String path)
            at System.IO.Path.GetFileNameWithoutExtension(String path)
            at roundhouse.infrastructure.filesystem.WindowsFileSystemAccess.get_file_name_without_extension_from(String file_path) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure\filesystem\WindowsFileSystemAccess.cs:line 225
            at roundhouse.infrastructure.app.builders.KnownFoldersBuilder.remove_paths_from(String name, FileSystemAccess file_system) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure.app\builders\KnownFoldersBuilder.cs:line 40
            at roundhouse.infrastructure.app.builders.KnownFoldersBuilder.build(FileSystemAccess file_system, ConfigurationPropertyHolder configuration_property_holder) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure.app\builders\KnownFoldersBuilder.cs:line 23
            at roundhouse.infrastructure.app.ApplicationConfiguraton.<>c__DisplayClass5.<build_items_for_container>b__2(IContext context) in c:\tmp\chucknorris-roundhouse-169e773\product\roundhouse\infrastructure.app\ApplicationConfiguraton.cs:line 170
            at StructureMap.Pipeline.LambdaInstance`1.build(Type pluginType, BuildSession session) in c:\code\structuremap\Source\StructureMap\Pipeline\LambdaInstance.cs:line 25
       InnerException: 

We have one .sql file that rh completely executes but right after rh fails to insert that script's record in the SCRIPTSRUN table (Error is below). However, all other scripts are inserted nicely in that same table and moreover.when I split that .sql file in two, they get both inserted into the table. What is going on here?

2011-11-30 09:32:19,943 [INFO ] - Running 0005_Indexes.sql on localhost - .
2011-11-30 09:32:20,033 [ERROR] - roundhouse.databases.oracle.OracleDatabase with provider System.Data.OracleClient does not provide a facility for recording scripts run at this time.
could not insert: [roundhouse.model.ScriptsRun#5][SQL: INSERT INTO PRODATA_ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)]
2011-11-30 09:32:20,055 [ERROR] - RoundhousE encountered an error.
NHibernate.Exceptions.GenericADOException: could not insert: [roundhouse.model.ScriptsRun#5][SQL: INSERT INTO PRODATA_ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)] ---> System.Data.OracleClient.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
--- End of inner exception stack trace ---
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Action.EntityInsertAction.Execute()
at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
at NHibernate.Engine.ActionQueue.ExecuteActions()
at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)
at NHibernate.Impl.SessionImpl.Flush()
at roundhouse.infrastructure.persistence.Repository.save_or_update[T](T item)
at roundhouse.databases.DefaultDatabase`1.insert_script_run(String script_name, String sql_to_run, String sql_to_run_hash, Boolean run_this_script_once, Int64 version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.record_script_in_scripts_run_table(String script_name, String sql_to_run, Boolean run_this_script_once, Int64 version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String sql_to_run, String script_name, Boolean run_this_script_once, Boolean run_this_script_every_time, Int64 version_id, Environment environment, String repository_version, String repository_path, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(String directory, Int64 version_id, MigrationsFolder migration_folder, Environment migrating_environment, String repository_version, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.log_and_traverse(MigrationsFolder folder, Int64 version_id, String new_version, ConnectionType connection_type)
at roundhouse.runners.RoundhouseMigrationRunner.run()

I have a project, that I would like to use roundhouse on. But it's in subversion, and there are 30-40 databases being updated by the same repository.

The scripts are shared and the structure is shared, but the dll versions are disparate of their various consuming web front ends.

So an xml file per database as to what the version is, would be cumbersome and kind of strange when branching.

My proposition is an alternate versioning scheme where the version you are upgrading to is the highest up script in the directory's serial number. For example if the last script in up is 0181_made_user_change.sql. Then you would be going from version whatever to version 181. So the scripts themselves are the version if you choose a config option.
This makes versioning down much more intuitive. Since the scripts themselves can tell you where you are going to.

I'm working up a pull request for this, but wanted to get thoughts on whether or not it would be appropriate to put in to the main release.

Referencing roundhouse dll directly (instead of just running rh.exe) generates constant fluentNhibernate resolution warnings in the log, even with simple upgrades. I am not using Nhibernate and this warning just adds noise.

[WARN]: Had an error building session factory from merged, attempting unmerged. The error:
FluentNHibernate.Cfg.FluentConfigurationException: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.

---> FluentNHibernate.Cfg.FluentConfigurationException: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.

---> System.TypeLoadException: Could not load type 'roundhouse.databases.sqlserver.orm.VersionMapping' from assembly 'roundhouse, Version=0.8.5.0, Culture=neutral, PublicKeyToken=91b86fd44f1f23bc'.
at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly assembly, String name, Boolean throwOnError, Boolean ignoreCase, ObjectHandleOnStack type)
at System.Reflection.RuntimeAssembly.GetType(String name, Boolean throwOnError, Boolean ignoreCase)
at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.<>c__DisplayClass1f.<build_session_factory>b__1d(MappingConfiguration m)
at FluentNHibernate.Cfg.FluentConfiguration.BuildConfiguration()
--- End of inner exception stack trace ---
at FluentNHibernate.Cfg.FluentConfiguration.BuildConfiguration()
at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
--- End of inner exception stack trace ---
at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.build_session_factory(Action`1 additional_function)

Reproduction:

1. Run RH to execute a known good sproc.
2. Brake the sproc (something like a keyword typo) and run RH.
3. Revert changes and run RH.

Expected:

The sproc should have be re-run but Roundhouse claims it didn't detect any changes. For those of us with Drop-Create scripts, this causes SPs to be dropped and not to be re-created.

When executing a migration, roundhouse constantly has to check the latest version of the script in the scripts run table.

Caching the latest script run information would collapse all of the checks into a single call to the database. For migrations with many unchanged scripts, this should lead to a significant boost in performance.

In Roundhouse 0.7 you cannot run all ALTER DATABASE scripts from within the up folder.
It would be great to introduce an additional folder that allows running these scripts.
Therefore it would be also great to have a way to inject the database name into the script, to be able to change the database name during the build.

Example:

On SQL Server running:
ALTER DATABASE DbName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

results in an exception:

2011-05-24 12:15:32,610 [ERROR] - roundhouse.databases.sqlserver.SqlServerDatabase with provider System.Data.SqlClient does not provide a facility for recording scripts run at this time. could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] 2011-05-24 12:15:32,624 [ERROR] - RoundhousE encountered an error. NHibernate.Exceptions.GenericADOException: could not insert: [roundhouse.model.ScriptsRun][SQL: INSERT INTO RoundhousE.ScriptsRun (version_id, script_name, text_of_script, text_hash, one_time_script, entry_date, modified_date, entered_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje ct stateObj) at System.Data.SqlClient.TdsParserStateObject.WriteSni() at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode) at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Id.IdentityGenerator.InsertSelectDelegate.ExecuteAndExtract(IDbC ommand insert, ISessionImplementor session) at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) --- End of inner exception stack trace --- at NHibernate.Id.Insert.AbstractReturningDelegate.PerformInsert(SqlCommandInfo insertSQL, ISessionImplementor session, IBinder binder) at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session) at NHibernate.Action.EntityIdentityInsertAction.Execute() at NHibernate.Engine.ActionQueue.Execute(IExecutable executable) at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(O bject entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Obje ct entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGenerated OrRequestedId(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient (SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpda te(SaveOrUpdateEvent event) at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(Sa veOrUpdateEvent event) at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event) at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj) at roundhouse.infrastructure.persistence.Repository.save_or_update[T](T item) at roundhouse.databases.DefaultDatabase1.insert_script_run(String
script_name, String sql_to_run, String sql_to_run_hash, Boolean
run_this_script_once, Int64 version_id)
at
roundhouse.migrators.DefaultDatabaseMigrator.record_script_in_scripts_run_t able(String
script_name, String sql_to_run, Boolean run_this_script_once, Int64
version_id)
at roundhouse.migrators.DefaultDatabaseMigrator.run_sql(String
sql_to_run, String script_name, Boolean run_this_script_once, Boolean
run_this_script_every_time, Int64 version_id, Environment environment,
String repository_version, String repository_path)
at
roundhouse.runners.RoundhouseMigrationRunner.traverse_files_and_run_sql(Str ing
directory, Int64 version_id, MigrationsFolder migration_folder,
Environment migrating_environment, String repository_version)
at roundhouse.runners.RoundhouseMigrationRunner.run()`

We use two distinct deployment mechanisms:.

1. During a normal database 'upgrade' deployment we drop all objects (sprocs/views/functions etc through alterDatabase script) and then force RoundhousE to add them. This is working wonderfully.

2. We also a need to do a 'fresh' database deployment to new environments using RoundhousE.

As part of our weekly build, we carry out an 'upgrade' of our blank master and back it up. This is then placed into the RoundhousE folder structure. This backup is fully up-to-date and requires a simple 'post-restore' script running over it and potentially an environmental specific script.

As it is by design, after the database is restored in, it proceeds to continue in normal mode (as it is intended). In this circumstance it drops everything etc, all of which is both unnecessary and time consuming.

It would be highly beneficial for us, and potentially others, for the ability to exclude certain script types or folders (all to be included by default, unless overridden by this switch.

(Using roundhouse.lib 0.8.0.332 from nuget.)

Getting this error when running with initialdevelopment on a local database with trusted connection.

INFO: Running RoundhousE v0.8.0.332 against (local) - Settlers.Specs.
INFO: Looking in ......\CP.Settlers.Migrations for scripts to run.

INFO: Setup, Backup, Create/Restore/Drop

INFO: Creating Settlers.Specs database on (local) server if it doesn't exist.
[WARN]: roundhouse.databases.sqlserver.SqlServerDatabase with provider System.Da
ta.SqlClient does not provide a facility for creating a database at this time.
A transport-level error has occurred when sending the request to the server. (pr
ovider: Shared Memory Provider, error: 0 - No process is on the other end of the
pipe.)
[ERROR]: RoundhousE encountered an error.

If I create the db first, RH can drop it but still ends up not being able to recreate.

Because CREATE DATABASE statement does not return a valid boolean value (like 1) return value from custom create script is always considered as false and thus database is considered as not created.

If one tries to return boolean about success (SELECT 1) the cast will fail in DefaultDatabase. Correct way would be to check if not null and not DbNull.Value, then Convert.ToBoolean().

From: https://groups.google.com/forum/#!msg/chucknorrisframework/oXUSEJvrvz4/Fl4W1rAoJDkJ

I understand that if a sql command fails that the command that caused the failure is logged in: RoundhousE.ScriptsRunErrors, however; it would also be nice to have the sql_statement that caused the failure to be written out to the log file so that it is immediately obvious what caused the failure when the user sends the log files from the installation.

I would propose this simple change in DefaultDatabaseMigrator.run_sql:

                   try
                    {
                        database.run_sql(sql_statement, connection_type);
                    }
                    catch (Exception ex)
                    {
                        Log.bound_to(this).log_an_error_event_containing("Error executing command {0} - {1}", script_name, sql_statement); // please add this line
                        database.rollback();

Does this sound reasonable?

It should convert the logging to a debug mode so one can see what is going on.

It appears that the DryRun switch is not actually used anywhere in the codebase. The only place the option is referenced is in Program.cs where the options are scraped from the command line. Am I missing something?