Questo articolo ci mostra come aggiornare il database Esempio che abbiamo realizzato nei post precedenti,
il cui schema originale è questo:
Per farlo diventare questo:
La nuova versione del nostro database aggiunge alla tabella TbRecipes quattro nuove colonne:
- Title
- IDAuthor
- PublishDate
- Picture
Per dar modo a chi inserisce la ricetta di indicare chi ne è l’autore, per inserire una data di pubblicazione e per aggiungere il titolo della ricetta ed una foto. Vediamo come creare lo script per questa prima modifica:
/*Aggiungiamo alla tabella ricette il campo Titolo, il campo Immagine e l'ID dell'autore e la data di pubblicazione controllando se esistono prima di aggiungerli*/ /*We add to the recipes table the fields title, picture and the Author ID and Publish date checking the existence of the fields before adding.*/ IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'TbRecipes' AND column_name = 'Title') BEGIN ALTER TABLE TbRecipes ADD Title nvarchar(255) NULL END GO IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'TbRecipes' AND column_name = 'Picture') BEGIN ALTER TABLE TbRecipes ADD Picture varbinary(MAX) NULL END GO IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'TbRecipes' AND column_name = 'IDAuthor') BEGIN ALTER TABLE TbRecipes ADD IDAuthor uniqueidentifier NULL END GO IF NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name = 'TbRecipes' AND column_name = 'PublishDate') BEGIN ALTER TABLE TbRecipes ADD PublishDate DateTime NULL END GO
Anche in questo caso, verifichiamo prima l’esistenza della colonna e poi la generiamo, è sempre una procedura di sicurezza, in caso di ripetizione della procedura o di errore, può essere eseguita nuovamente senza alcun problema.
/*Aggiungiamo la tabella autori verificando che non esista*/
/*We add the authors table checking the existence before adding it*/
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TbAuthors]') AND type in ('U')) BEGIN
CREATE TABLE [dbo].[TbAuthors](
[IDAuthor] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NULL,
Picture varbinary(MAX) NULL,
[LastUpdated] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [BINARYDATA]
END
GO
/* Generiamo la Primary Key della nuova tabella*/
/*We generate the Primary Key of the new table*/
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[TbAuthors]') AND name = '[PK_TbAuthors]') BEGIN
PRINT 'CREATE [PK_TbAuthors] ON TbAuthors';
ALTER TABLE [dbo].[TbAuthors] ADD CONSTRAINT [PK_TbAuthors] PRIMARY KEY CLUSTERED ([IDAuthor] ASC) ON [PRIMARY];
END
GO
/*Eliminiamo e Rigeneriamo il Default che inizializza automaticamente la Primary Key delle righe con un nuovo GUID*/
/*We drop and re-create the Default that sets automatically the Primary Key of the records to a new GUID*/
IF EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbAuthors_IDAuthor]') AND parent_object_id = OBJECT_ID('[dbo].[TbAuthors]')) BEGIN
PRINT 'DROP CONSTRAINT [DF_TbAuthors_IDAuthor] ON [TbAuthors]'
ALTER TABLE [dbo].[TbAuthors] DROP CONSTRAINT [DF_TbAuthors_IDAuthor]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints WHERE object_id = OBJECT_ID('[dbo].[DF_TbAuthors_IDAuthor]') AND parent_object_id = OBJECT_ID('[dbo].[TbAuthors]')) BEGIN
PRINT 'ADD CONSTRAINT [DF_TbAuthors_IDAuthor] ON [TbAuthors]'
ALTER TABLE [dbo].[TbAuthors] ADD CONSTRAINT [DF_TbAuthors_IDAuthor] DEFAULT (newid()) FOR [IDAuthor]
END
GO
/*Eliminamo e rigeneriamo la Foreign Key che aggancia gli autori alle ricette*/
/*We derop and re-create the Forign Key to connect the Recipes and the Authors*/
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipes_TbAuthors]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipes]')) BEGIN
PRINT 'DROP CONSTRAINT [FK_TbRecipes_TbAuthors] ON [TbRecipes]'
ALTER TABLE [dbo].[TbRecipes] DROP CONSTRAINT [FK_TbRecipes_TbAuthors]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[dbo].[FK_TbRecipes_TbAuthors]') AND parent_object_id = OBJECT_ID('[dbo].[TbRecipes]')) BEGIN
PRINT 'ADD CONSTRAINT [FK_TbRecipes_TbAuthors] ON [TbRecipes]'
ALTER TABLE [dbo].[TbRecipes] ADD CONSTRAINT [FK_TbRecipes_TbAuthors] FOREIGN KEY([IDAuthor]) REFERENCES [dbo].[TbAuthors] ([IDAuthor])
END
GO
La seconda parte dello script, genera una nuova tabella, TbAuthors, che permetterà di inserire i dati dell’autore di una ricetta e di aggiornarlo all’interno della tabella ricette, ai fini statistici o per effettuare dei filtri o semplicemente per sapere chi è che ha creato una ricetta. In questo caso, eseguiamo uno dopo l’altro tutti i passaggi degli script precedenti, Creazione tabella, Creazione Primary Key, Creazione Default, Creazione Foreign Keys.
Anche in questo caso, anche se non riportato, al termine dello script aggiorneremo la tabella TbVersions, in modo che sia possibile ai programmi che usano il database controllare che si trovi nello stato e nella forma corretta per il loro funzionamento.
Conclusioni
Abbiamo visto come creare uno script per aggiornare un database una volta che è stato creato per modificare una tabella esistente oppure aggiungere nuove tabelle. Prossimamente, vedremo come usare questo database da .Net.
Gli script T-SQL del codice di esempio sono scaricabili al seguente link e sono comprensivi di tutto quello che è spiegato nella breve serie di articoli.
