VersionInfo Table

[Tom Marien]

Hey,

Any oppinions on how to tackle the problem of the missing primary/
unique index on the versiontable ?

As i now is possible to launch two clients at the same time, with each
of them a different migration 1.

My suggestion is adding the primary or unique constraint in the
existing migration, existing users need a custom migration for it.
But at least new users are protected.

Regards

Tom

[Tom Marien]

Apologies on this one this seems to be fixed by commit
https://github.com/schambers/fluentmigrator/commit/48ef69e277eaf8e12cb6a137568e5e292c5a4cb0,
but i still see issues with this as my versionInfo table is created
without primary key.

I will investigate further

[Tom Marien]

Seems like this was rollbacked by
https://github.com/schambers/fluentmigrator/commit/68a306a3485e21a7b292d0c355dd02ce705a245f#src/FluentMigrator.Runner/Versioning/VersionMigration.cs
commit made by daniellee, any comments on why ;)

[Tom Marien]

Just issued a simple pull to redo the primary key part
https://github.com/schambers/fluentmigrator/pull/209

[Tom Marien]

Abusby,

You closed my pull request on the redo with following comment

"It was rolled for two reasons.

We end up with two different version tables in the wild, ones with
primary keys and ones without which could cause problems when we come
to alter the version table in future.
The is a branch (which i will find in a moment) that corrects this
and adds migration grouping to the table, which if we make this commit
now becomes really difficult to achieve.

I will find the branch and update it this week so that we can merge it
in and correct the issue."

I don't understand the need for yet another discriminating attribute,
at the moment we allready have namespace and profile, why can't we use
one of them, for instance the namespace:

If you first pull in my rollback with namespace request, then we could
alter the VersionMetaTable to load by default from root namespace only
and if a versionMetaTable is found within the specified namespace, it
uses that one for those migrations, it seems easier.

So you could have for instance

Blah.Migrations using dbo.VersionInfo
Blah.Migrations.Base using Base.VersionInfo containing all base tables
(existing tables before starting dev)
Blah.Migrations.Blog using Blog.VersionInfo

Shouldn't that be enough for the framework allready, at the moment by
rolling back the first pull request (and my redo), we are open to
multiple migrations with same version, which i personally consider
much more needed than group based migrations.

This is just my oppinion on the matter. And please keep discussions
together here on the group, as i allready stated a possible solution
for existing versionInfo's, mandatory custom migration.

Regards

Tom

[Tom Marien]

On a side note i like the group idea, but we need to define which is
unique on that table, is it the combination of version, group or only
version, as it changes a lot, the first could have multiple migrations
versioned with 1, only for a different group. In that way you could
add primary key clustered on both fields. (instead of adding an
identity column as you mentioned on the pull request comment)

[Tom Marien]

group is called delta-set in java's dbdeploy

[Shane Walters]

Tom,

My thoughts are if the  column and index must be added, then FM should add it if it automatically without requiring any manual scripts.  This will be the least painful for existing users.

-Shane

On Sun, Dec 18, 2011 at 12:15 PM, Tom Marien <tomm...@gmail.com> wrote:

group is called delta-set in java's dbdeploy

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To post to this group, send email to fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.

[Andrew Busby]

Tom,

1. Your commit does not solve the problem, that current databases that have
been create will not have this fix applied leaving to different types of
version table schemas in the wild
2. Profiles are different because they are applied after migrations and not
versioning
3. When using multiple assemblies, version id collision has to be checked
4. Loading the root namespace is probably not appropriate for people
including migrations in the application dlls,

I agree that this sort of thing should be applied by the framework but the
quick fix is not always the best.

If you still fell that you need to apply this change go ahead but it will
make life more difficult.

As for keeping discussion on the group, that sound good but it more
contextual to have comments attached to pull requests, issues, etc on
Github. People are more likely to browse there are see something relevant
and look at the comments, then find something relevant and then got search
google groups to see if someone has commented.

Andrew

Abusby,

Regards

Tom

--

[Tom Marien]

@andrew I am stubborn not stupid :)
1. Can we agree on the fact that rollback(steps) and rollback to
version needs to follow filters like for instance namespace? If so
apply rollback patch
2. Do we have an idea of how the versiontable should look like in the
end?
-Columns: Version, deltaset, syscreator,syscreated?
-which columns will form the unique clustered key?
3. Make the version migration
4. Create the deltaset feature

I agree on the feature, i am just saying it's a big, so maybe devide
and conquer should be the strategy here :)
I think point two is still not clear, just adding an identity column
will make it azure ready but doesn't solve the same issue as the
original pull solved by adding a primary key did!

Hope i made myself clearer and did't offend anyone

Regards

Tom

[Andrew Busby]

@tom,

Great two stubborn developer on a project, that should mean we end up with a
great code base.

I was not implying that you were stupid, as I currently can't (in the words
of Linus Torvalds)" show you the code" that corrects the issue, I did not
want to be an academic road block.

Anyway on to the actual issue.

1. Yes I agree that the rollback pack needs to be applied and I will go do
it after this.
2. It think that the layout you described is about correct unless anyone has
any modifications, so
Version | DeltaSet | Creator | CreatedDate

I am not sure whether Creator should be the db account from the connection
string of the Windows User Account used to run the migration. I also wonder
whether there should be a column for description, so that the changes that
the migration makes could be listed

3. The unique columns for the migration are Version and DeltaSet the reason
that I suggested an identity column is because databases not using the
DeltaSet attribute will (or should I think) have a deltaset value of null
and at some point and I cannot remember which one, we had an issue with one
of the RDBMS using a composite primay key where one of the values was null.
This was some time ago and might well be fixed now, so it could be a moot
point, I don't know if anyone else (or others) can confirm that this is safe
on all RDBMS

4. If we can agree the direction we can solve the probem

Andrew

-----Original Message-----
From: fluentmigrato...@googlegroups.com
[mailto:fluentmigrato...@googlegroups.com] On Behalf Of Tom
Marien
Sent: 19 December 2011 07:30
To: FluentMigrator Google Group
Subject: Re: VersionInfo Table

Regards

Tom

--

[Tom Marien]

Andrew,

Now we are working together :)

2. If we need creator i would go for SYSTEM_USER in sql, as i hope
people are executing their migrations with dbusers or ntusers, then we
need a SystemMethods.User functionality

As for the description part why not go for namespace, class name
like for instance Blog.Migrations, M0001_AddPerson, i would not favor
any new descriptive attribute on migration
3. I would favor to keep unique key on migration number only, as i
foresee many issue if you don't

If we agree on al of these features i would like to create features
for it in github, with some description of what needs to be done :)

[Andrew Busby]

Tom,

I agree with using SYSTEM_USER

But we should probably scrap the description column.

It would only be useful in a human readable form that users could display to
sys admins or during the migration process through the announcers. I am was
tabling the idea but I do not think it is of much benefit unless, anyone
speaks up. We work with application version numbers, so we know what
database version a customer is using from this.

I am not convinced by unique version numbers only. For example we have a
base migration dll that is applied to all of applications which creates base
tables such as users, groups, permission, etc

We then apply an application migration dll to the database that creates the
application specific tables. At the moment we space our version number
10,000 apart for each dll which means this works with current system.

I can image that if an application supported plugins, like dotnetnuke or
rails app then you would want plugins to use whatever version numbers suited
them, in a safe manor.

What issues do you foresee?

-----Original Message-----
From: fluentmigrato...@googlegroups.com
[mailto:fluentmigrato...@googlegroups.com] On Behalf Of Tom
Marien
Sent: 19 December 2011 19:10
To: FluentMigrator Google Group
Subject: Re: VersionInfo Table

Andrew,

--

[Tom Marien]

Mmm i see your point, but think of the following situation

[Migration(10000,"Security")]

What if i needed to rename the deltasetname to for instance foo ? if
i simply did a migrate:up i would have no collisions on the
versiontable, but i am certain that your migrations are not going to
pass

I see the deltaset merely as a filter, also i suspect the current code
to have more issue with this i got a duplicate version number
exception once ;)

As a remark on your take on version numbering, you should need to let
go versioning like 1,2,3 etc think of it like versioning like
20111219223801, 20111219223802, 20111219223803 in the form of
YYYYMMDDHHMMSS that will get you into less collissions if you are in a
bigger team.

Just my two cents :P

[Gunnar Liljas]

If anything should be changed, it would be to store the version as a string. It's purpose is to provide "uniqueness", not order, so storing it as a number is very inflexible. Storing it as a string would allow any type of necessary conventions to be applied, of course with sensible defaults.

Not saying that MigrationAttribute should change, only the execution log.

Execution order can be handled by an IComparer<Migration>.

/G

[Tom Marien]

@gunnar elaborate please

@abusby Been looking into DBDeploy, looks like they use a primary on
version and deltaset, so we should be fine, identity is not always
supported (see oracle)

Do you think i can start by creating distinct features for it

1 SystemMethods.User
2 VersionMigration
3 Deltaset feature

Tom