creating a primary key with a composite key specification is failing #396

Closed
dealproc opened this Issue Apr 1, 2013 · 7 comments

Comments

Projects
None yet
2 participants
Contributor

dealproc commented Apr 1, 2013

When I try to create a key by doing something to the following:

Create.PrimaryKey("PK_Name").OnTable("Table").WithSchema("schemaname").Columns(new string[] { "Col1", "Col2" });

I expect to get:

ALTER TABLE "schemaname"."Table" ADD CONSTRAINT "PK_Name" PRIMARY KEY ("Col1", "Col2")

But am getting:

ALTER TABLE "Table" ADD CONSTRAINT "PK_Name" PRIMARY KEY ("Col1", "Col2")

So, to be clear, the issue is that the schema name is not being appended to the ALTER TABLE statement within the generated SQL statements.

@dealproc dealproc referenced this issue Apr 2, 2013

Closed

Bug 396 #397

Contributor

daniellee commented Apr 2, 2013

For which database type? Some databases do not support schemas and for them this behaviour is correct.

Contributor

dealproc commented Apr 2, 2013

This was for Postgres. My apologies... I should have been clearer. I created a pull request for this, but haven't figured out how to link it back to this post.

Contributor

daniellee commented May 15, 2013

I can't recreate this. It works fine for me on Postgres and I can't see anything in the code that looks wrong.

Contributor

dealproc commented May 15, 2013

My apologies... since I did this, I have been head-down working on code nonstop. When you work in public, this works great, but when you work in another schema, say "books", it'll fail.

so, if you have a table called "EmployeeTransaction" with "EmployeeId" and "TransactionId", and it's in schema "books", it will fail, but if it's in "public", it'll work like a charm.

Here's the code that has to go into src\FluentMigrator.Runner\Generators\Postgres\PostgresGenerator.cs

        //bug #396
        public override string Generate(CreateConstraintExpression expression)
        {
            var result = new StringBuilder("");

            const string sql = "ALTER TABLE {0}.{1} ADD CONSTRAINT {2} {3} (";

            var constraintType = (expression.Constraint.IsPrimaryKeyConstraint) ? "PRIMARY KEY" : "CONSTRAINT";
            string[] columns = new string[expression.Constraint.Columns.Count];

            for (int i = 0; i < expression.Constraint.Columns.Count; i++)
            {
                columns[i] = Quoter.QuoteColumnName(expression.Constraint.Columns.ElementAt(i));
            }

            result.Append(string.Format(sql,
                Quoter.QuoteSchemaName(expression.Constraint.SchemaName),
                Quoter.QuoteTableName(expression.Constraint.TableName),
                Quoter.Quote(expression.Constraint.ConstraintName),
                constraintType));

            var first = true;
            foreach (var column in expression.Constraint.Columns)
            {
                if (first)
                    first = false;
                else result.Append(", ");

                result.Append("\"" + column + "\"");
            }

            result.Append(")");

            return result.ToString();
        }

As I stated before, my GIT-fu is not what my HG-fu is, and I struggle with submitting patches back to contribution.

Contributor

dealproc commented May 15, 2013

I opened GitHub for Windows, and it's showing I have an unsynced commit... I'll have to see if I can get this to where it needs to be.

Contributor

daniellee commented May 15, 2013

Ok, I was totally baffled by this as this works for me with the schema. But I guess you are working off an old version of the code. @tommarien fixed this 4 months ago, see this commit

So this:

Create.PrimaryKey("PK_myTable_myNewId").OnTable("TestTable").WithSchema("test").Columns(new string[] { "Id", "myNewId" });

generates this sql:

ALTER TABLE [test].[TestTable] ADD CONSTRAINT [PK_myTable_myNewId] PRIMARY KEY ([Id], [myNewId])

Thanks for putting the effort in, as your code looks very similar and would probably work just as well.

@daniellee daniellee closed this May 15, 2013

Contributor

dealproc commented May 15, 2013

np. having all sorts of issues with git, and I had to give up and get back to coding on main project. I'll update to 1.1 and see how things go at some point in the next few weeks, as time allows.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment