[jira] Commented: (DDLUTILS-8) MsSqlBuilder can't alter a table to make a column autoincremented (identity)

["Christoffer Hammarström (JIRA)" <ji...@apache.org>]

    [ http://issues.apache.org/jira/browse/DDLUTILS-8?page=comments#action_12322736 ] 

Christoffer Hammarström commented on DDLUTILS-8:
------------------------------------------------

It would be nice with a hook like the alterTable() method in the patch, if you think this is an appropriate way to go.


> MsSqlBuilder can't alter a table to make a column autoincremented (identity)
> ----------------------------------------------------------------------------
>
>          Key: DDLUTILS-8
>          URL: http://issues.apache.org/jira/browse/DDLUTILS-8
>      Project: DdlUtils
>         Type: Bug
>     Reporter: Christoffer Hammarström
>     Assignee: Thomas Dudziak
>  Attachments: DDLUTILS-8-1st-draft.patch, DDLUTILS-8-2nd-draft.patch
>
> A column in an existing table can't be made autoincremented using ALTER TABLE.
> I want to add this capability to MsSqlBuilder, but i'm not sure whether to copy the approach of Microsoft Enterprise Manager, or if there is some better way, and i would like some direction or input.
> I've started by extracting methods alterTable() and alterColumns() from alterDatabase() in SqlBuilder, and i'm overriding alterColumns() in MsSqlBuilder with a check for autoincremented columns in the desiredTable but not in the currentTable.
> The approach used when scripting this change from Microsoft Enterprise Manager is to:
> 1. Drop table constraints
> 2. Create a new replacement table with the name prefixed by 'Tmp_'
> 3. SET IDENTITY_INSERT Tmp_table ON
> 4. Copy the data from the table to the Tmp_table
> 5. Set IDENTITY_INSERT Tmp_table OFF
> 6. DROP TABLE table
> 7. EXECUTE sp_rename N'dbo.Tmp_table', N'table', 'OBJECT'
> 8. Readd table constraints
> An example follows:
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.phones
>     DROP CONSTRAINT FK_phones_users
> GO
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.phones
>     DROP CONSTRAINT DF_phones_phonetype_id
> GO
> CREATE TABLE dbo.Tmp_phones
> (
>     phone_id int NOT NULL IDENTITY (1, 1),
>     number varchar(25) NOT NULL,
>     user_id int NOT NULL,
>     phonetype_id int NOT NULL
> )  ON [PRIMARY]
> GO
> ALTER TABLE dbo.Tmp_phones ADD CONSTRAINT
>     DF_phones_phonetype_id DEFAULT (0) FOR phonetype_id
> GO
> SET IDENTITY_INSERT dbo.Tmp_phones ON
> GO
> IF EXISTS(SELECT * FROM dbo.phones)
>     EXEC('INSERT INTO dbo.Tmp_phones (phone_id, number, user_id, phonetype_id)
> SELECT phone_id, number, user_id, phonetype_id FROM dbo.phones TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_phones OFF
> GO
> DROP TABLE dbo.phones
> GO
> EXECUTE sp_rename N'dbo.Tmp_phones', N'phones', 'OBJECT'
> GO
> ALTER TABLE dbo.phones ADD CONSTRAINT
> PK_phones PRIMARY KEY NONCLUSTERED
> (
>     phone_id,
>     user_id
> ) ON [PRIMARY]
> GO
> ALTER TABLE dbo.phones WITH NOCHECK ADD CONSTRAINT
> FK_phones_users FOREIGN KEY
> (
>     user_id
> ) REFERENCES dbo.users
> (
>     user_id
> )
> GO
> COMMIT

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira