Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't use new created columns in SQL - Statement in Up() Migration [DNET811] #746

Closed
firebird-issue-importer opened this issue Mar 14, 2018 · 7 comments

Comments

@firebird-issue-importer

Submitted by: Andreas Patock (apatock)

Votes: 1

If I have the following migration:

public partial class AddDeviceType : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.DeviceType",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(nullable: false, maxLength: 128),
ManufacturerId = c.Int(nullable: false),
})
.PrimaryKey(t => http://t.Id)
.ForeignKey("dbo.Manufacturer", t => t.ManufacturerId, cascadeDelete: true)
.Index(t => t.ManufacturerId);

        AddColumn\("dbo\.Device", "TempDeviceTypeId", c =\> c\.Int\(\)\);
        AddColumn\("dbo\.Device", "DeviceTypeId", c =\> c\.Int\(nullable: false\)\);
        CreateIndex\("dbo\.Device", "DeviceTypeId"\);
        AddForeignKey\("dbo\.Device", "DeviceTypeId", "dbo\.DeviceType", "Id", cascadeDelete: true\);

        Sql\("UPDATE \\"Device\\" d SET d\.\\"DeviceTypeId\\" = \(SELECT t\.\\"Id\\" from \\"DeviceType\\" t where t\.\\"ManufacturerId\\" = d\.\\"ManufacturerId\\" ORDER BY t\.\\"Id\\" ROWS 1\)"\);
    \}

I can a Column unknown "DeviceTypeId" exception when running the migration. This can found on many examples in the internet and is working with mssql.

I had the idea to do this in two migrations, but the "DeviceTypeId" is a not null field and so the first migration throws an exception also.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2018

Commented by: @cincuranet

It's probably because of transactions.

Can you script this migration and paste it here?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2018

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 15, 2018

Commented by: Andreas Patock (apatock)

Do you mean this?

ALTER TABLE "Device" DROP CONSTRAINT "FK_ManDevManId"
DROP INDEX "IX_DevManId"
CREATE TABLE "DeviceType" (
"Id" INT NOT NULL,
"Name" VARCHAR(128) NOT NULL,
"ManufacturerId" INT NOT NULL
)
ALTER TABLE "DeviceType" ADD CONSTRAINT "PK_DeviceType" PRIMARY KEY ("Id")
EXECUTE BLOCK
AS
BEGIN
if (not exists(select 1 from rdb$generators where rdb$generator_name = 'GEN_IDENTITY')) then
begin
execute statement 'create sequence GEN_IDENTITY';
end
END
CREATE OR ALTER TRIGGER "DeviceType_TRIG" ACTIVE BEFORE INSERT ON "DeviceType"
AS
BEGIN
if (new."Id" is null) then
begin
new."Id" = next value for GEN_IDENTITY;
end
END
CREATE INDEX "IX_DevTypManId" ON "DeviceType"("ManufacturerId")
ALTER TABLE "Device" ADD "DeviceTypeId" INT DEFAULT 0 NOT NULL
CREATE INDEX "IX_DevDevTypId" ON "Device"("DeviceTypeId")
ALTER TABLE "Device" ADD CONSTRAINT "FK_DevTypDevDevTypId" FOREIGN KEY ("DeviceTypeId") REFERENCES "DeviceType" ("Id") ON DELETE CASCADE
ALTER TABLE "Device" DROP "ManufacturerId"
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'SwitchSimulation' ROWS 1), 'SwitchSimulation')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Arista' ROWS 1), 'Arista')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Cisco' ROWS 1), 'Cisco')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Simulation' ROWS 1), 'Simulation')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'MulticastStreamer' ROWS 1), 'MulticastStreamer')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Embrionix' ROWS 1), 'Embrionix')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Imagine' ROWS 1), 'Imagine')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'S-A-M' ROWS 1), 'S-A-M')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Lawo' ROWS 1), 'Lawo')
UPDATE or INSERT INTO "DeviceType"("Id", "Name", "ManufacturerId") VALUES((SELECT d."Id" from "DeviceType" d where d."Name" = 'SwitchSimulation' ROWS 1), 'SwitchSimulation', (SELECT m."Id" from "Manufacturer" m where m."Name" = 'SwitchSimulation' ROWS 1))

The Error is:

Dynamic SQL Error
SQL error code = -204
Table unknown
DeviceType
At line 1, column 108

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 8, 2018

Commented by: @cincuranet

For not null columns the default value is generated (even if you haven't specified it), see "ALTER TABLE "Device" ADD "DeviceTypeId" INT DEFAULT 0 NOT NULL ". The whole migration is executed in a transaction, the update doesn't see yet the table, because of how DDL and DML inside same transaction work in Firebird.

Not sure provider can do much here. I think the best bet is to split this to two migrations.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 8, 2018

Modified by: @cincuranet

status: In Progress [ 3 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 8, 2018

Modified by: @cincuranet

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 8, 2018

Modified by: @cincuranet

status: Reopened [ 4 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants