From ddlutils-dev-return-85-apmail-db-ddlutils-dev-archive=db.apache.org@db.apache.org Wed Sep 07 21:05:35 2005 Return-Path: Delivered-To: apmail-db-ddlutils-dev-archive@www.apache.org Received: (qmail 59511 invoked from network); 7 Sep 2005 21:05:35 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 7 Sep 2005 21:05:35 -0000 Received: (qmail 31321 invoked by uid 500); 7 Sep 2005 21:05:35 -0000 Delivered-To: apmail-db-ddlutils-dev-archive@db.apache.org Received: (qmail 31299 invoked by uid 500); 7 Sep 2005 21:05:34 -0000 Mailing-List: contact ddlutils-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ddlutils-dev@db.apache.org Delivered-To: mailing list ddlutils-dev@db.apache.org Received: (qmail 31286 invoked by uid 99); 7 Sep 2005 21:05:34 -0000 X-ASF-Spam-Status: No, hits=0.2 required=10.0 tests=UPPERCASE_25_50 X-Spam-Check-By: apache.org Received: from [192.87.106.226] (HELO ajax.apache.org) (192.87.106.226) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 07 Sep 2005 14:05:34 -0700 Received: from ajax.apache.org (ajax.apache.org [127.0.0.1]) by ajax.apache.org (Postfix) with ESMTP id 4733A31C for ; Wed, 7 Sep 2005 23:05:33 +0200 (CEST) Message-ID: <459326882.1126127133258.JavaMail.jira@ajax.apache.org> Date: Wed, 7 Sep 2005 23:05:33 +0200 (CEST) From: "Thomas Dudziak (JIRA)" To: ddlutils-dev@db.apache.org Subject: [jira] Commented: (DDLUTILS-8) MsSqlBuilder can't alter a table to make a column autoincremented (identity) In-Reply-To: <1865282113.1125484224397.JavaMail.jira@ajax.apache.org> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N [ http://issues.apache.org/jira/browse/DDLUTILS-8?page=3Dcomments#actio= n_12322884 ]=20 Thomas Dudziak commented on DDLUTILS-8: --------------------------------------- Sure, go ahead. I've extracted the alterTable method for you in SqlBuilder. But could you enhance the test case such that * it is useable with all builders, and * it also tests indices and foreign keys ? > MsSqlBuilder can't alter a table to make a column autoincremented (identi= ty) > -------------------------------------------------------------------------= --- > > Key: DDLUTILS-8 > URL: http://issues.apache.org/jira/browse/DDLUTILS-8 > Project: DdlUtils > Type: Bug > Reporter: Christoffer Hammarstr=C3=B6m > 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 T= ABLE. > I want to add this capability to MsSqlBuilder, but i'm not sure whether t= o copy the approach of Microsoft Enterprise Manager, or if there is some be= tter way, and i would like some direction or input. > I've started by extracting methods alterTable() and alterColumns() from a= lterDatabase() in SqlBuilder, and i'm overriding alterColumns() in MsSqlBui= lder with a check for autoincremented columns in the desiredTable but not i= n the currentTable. > The approach used when scripting this change from Microsoft Enterprise Ma= nager 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, phonetyp= e_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 --=20 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