SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

Segnare oggetti come deprecati usando Extended Properties

Volevo condividere una "tecnica" che ho seguito per segnare come deprecati alcuni oggetti. Innanzitutto, da dove può nascere un'esigenza simile? A dire il vero ci sono varie possibilità, ma le più frequenti sono:
- refactor di codice
- refactor di oggetti
- necessità di aggiornamenti con backward compatibility

Prendendo spunto da quanto altri tool fanno già, ad esempio per scrivere la documentazione di un database, la revisione del database, oppure anche da come SQL Server management Studio imposta le posizioni delle entità di una vista, le descrizioni delle colonne, ecc, ho utilizzato le extended properties. Qui un semplice cenno teorico (vale anche per 2012) sulle extended properties. Per farla breve si tratta di proprietà custom, disponibili in coppie di nome/valore (stringa), riutilizzabili per scopi utente. Questi valori sono totalmente custom e quindi possiamo utilizzare naming convention per rendere "esclusive" le nostre proprietà estese.
Ogni oggetto indicato nella documentazione di cui sopra ha un tab dedicato alle extended properties via designer, ma è sempre possibile gestirle tramite le stored procedure di sistema dedicate:

sp_addextendedproperty - per aggiungere una proprietà estesa
sp_dropextendedproperty - per eliminarla
sp_updateextendedproperty - per aggiornarla

Scenario
Immaginiamo di avere un elenco di stored procedure, funzioni e tipi (prendo i tipi di oggetto "programmabilità" a titolo di esempio) e di voler segnare alcuni di essi come deprecati per le future versioni.
Con il seguente script andiamo a creare, su di un database chiamato Utilities (sostituitelo con il vostro database per provare), la programmabilità di esempio:

USE Utilities;

GO

IF EXISTS(SELECT 1 FROM sys.types T WHERE name = 'tabletype1' AND schema_id = 1)

       DROP TYPE dbo.tabletype1;

GO

CREATE TYPE dbo.tabletype1 AS TABLE (id int, val varchar(30));

GO

 

IF EXISTS(SELECT 1 FROM sys.types T WHERE name = 'tabletype2' AND schema_id = 1)

       DROP TYPE dbo.tabletype2;

GO

CREATE TYPE dbo.tabletype2 AS TABLE (id int, val varchar(30), num decimal(18,2));

GO

 

IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.procedure1'))

       DROP PROC dbo.procedure1;

GO

CREATE PROC dbo.procedure1

AS

BEGIN

       SELECT 1;

END;

GO

 

IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.procedure2'))

       DROP PROC dbo.procedure2;

GO

CREATE PROC dbo.procedure2

AS

BEGIN

       SELECT 2;

END;

GO

 

IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.function1'))

       DROP FUNCTION dbo.function1;

GO

CREATE FUNCTION dbo.function1()

RETURNS int

AS

BEGIN

       RETURN 1;

END;

GO

 

IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.function2'))

       DROP FUNCTION dbo.function2;

GO

CREATE FUNCTION dbo.function2()

RETURNS int

AS

BEGIN

       RETURN 2;

END;

GO

 

Ora, avendo questi oggetti, andiamo a segnare come deprecati tutti quelli con suffisso 2. La naming convention per le extended properties che seguiremo nell'esempio è la seguente:
1) Nome: Utilities_Deprecation_IsDeprecated - Valore: True
2) Nome: Utilities_Deprecation_ValidUntilVersion - Valore: 2.0
3) Nome: Utilities_Deprecation_Message - Valore: This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix

Utilizziamo la sp_addextendedproperty per aggiungere tutte e tre le proprietà:

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_IsDeprecated',

@value = N'True',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'PROCEDURE',  @level1name = 'procedure2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_IsDeprecated',

@value = N'True',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'FUNCTION',  @level1name = 'function2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_IsDeprecated',

@value = N'True',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'TYPE',  @level1name = 'tabletype2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_ValidUntilVersion',

@value = N'2.0',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'PROCEDURE',  @level1name = 'procedure2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_ValidUntilVersion',

@value = N'2.0',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'FUNCTION',  @level1name = 'function2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_ValidUntilVersion',

@value = N'2.0',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'TYPE',  @level1name = 'tabletype2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_Message',

@value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'PROCEDURE',  @level1name = 'procedure2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_Message',

@value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'FUNCTION',  @level1name = 'function2';

GO

EXEC sys.sp_addextendedproperty

@name = N'Utilities_Deprecation_Message',

@value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix',

@level0type = N'SCHEMA', @level0name = 'dbo',

@level1type = N'TYPE',  @level1name = 'tabletype2';

GO

 

Abbiamo segnato gli oggetti, ora, vogliamo tornare la lista degli oggetti deprecati, per dare un report o anche per fare da source ad un'applicazione.. Dipende dalle nostre esigenze.
Una semplice query per mostrare le extended properties senza accedere, oggetto per oggetto, alla GUI relativa, è la seguente:
 

SELECT

         [Schema]    = S.name

       , [Name]      = O.name

       , EP.value

FROM

       sys.extended_properties EP

       JOIN sys.objects O ON O.object_id = EP.major_id

       JOIN sys.schemas S ON O.schema_id = S.schema_id

WHERE

       EP.name IN ('Utilities_Deprecation_IsDeprecated', 'Utilities_Deprecation_ValidUntilVersion', 'Utilities_Deprecation_Message')

UNION ALL

SELECT

         [Schema]    = S.name

       , [Name]      = T.name

       , EP.value

FROM

       sys.extended_properties EP

       JOIN sys.types             T ON T.user_type_id = EP.major_id

       JOIN sys.schemas     S ON T.schema_id = S.schema_id

WHERE

       EP.name IN ('Utilities_Deprecation_IsDeprecated', 'Utilities_Deprecation_ValidUntilVersion', 'Utilities_Deprecation_Message');

 

Schema     Name                  EPName                                                                value                                                  

---------- --------------------- ------------------------------------------ --------------------------------------------------------

dbo        procedure2            Utilities_Deprecation_IsDeprecated        True                                                   

dbo        procedure2            Utilities_Deprecation_ValidUntilVersion   2.0                                                    

dbo        procedure2            Utilities_Deprecation_Message             This object will be removed starting from version 2.0...

dbo        function2             Utilities_Deprecation_IsDeprecated        True                                                   

dbo        function2             Utilities_Deprecation_ValidUntilVersion   2.0                                                    

dbo        function2             Utilities_Deprecation_Message             This object will be removed starting from version 2.0...

dbo        tabletype2            Utilities_Deprecation_IsDeprecated        True                                                   

dbo        tabletype2            Utilities_Deprecation_ValidUntilVersion   2.0                                                    

dbo        tabletype2            Utilities_Deprecation_Message             This object will be removed starting from version 2.0...


Per listare in generale le extended properties c'è anche una funzione chiamata sys.fn_listextendedproperty, ma non consente filtri troppo personalizzati. L'esigenza nell'esempio è particolare e quindi una query dedicata risulta necessaria.

Con questo esempio abbiamo segnato come deprecati alcuni oggetti e ne abbiamo creato una lista che può essere utile ad un'applicazione esterna. Il risultato è molto importante per tenere traccia dei progressivi refactor che necessitano di retrocompatibilità ad esempio.
Con le extended properties è possibile gestire tanti casi personalizzati. Alcuni strumenti di terze parti segnano ad esempio la revisione di un database, il legame con l'eventuale source control, la documentazione, ecc.

In definitiva, esse possono essere un buon punto di partenza per inventarsi qualcosa di utile alla propria attività. 

Stay Tuned! 



Categoria: SQL Server
mercoledì, 21 ago 2013 Ore. 09.13

Messaggi collegati


Statistiche
  • Views Home Page: 539.130
  • Views Posts: 953.626
  • Views Gallerie: 540.586
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003