Fazer queries a xml com namespaces no SQL

Imaginemos que temos uma tabela onde guardamos um livro em formato xml numa coluna:

 DECLARE @book TABLE  
 (  
  BookID int,   
  Details xml  
 );  
   
 INSERT INTO @book  
 VALUES (1,   
 '<book id="bk101" xmlns="http://rmlalves.blogspot.com/">  
   <author>Gambardella, Matthew</author>  
   <title>XML Developer''s Guide</title>  
   <genre>Computer</genre>  
   <price>44.95</price>  
   <publish_date>2000-10-01</publish_date>  
   <description>An in-depth look at creating applications with XML.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (2,   
 '<book id="bk102" xmlns="http://rmlalves.blogspot.com/">  
   <author>Ralls, Kim</author>  
   <title>Midnight Rain</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2000-12-16</publish_date>  
   <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (3,   
 '<book id="bk103" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>Maeve Ascendant</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2000-11-17</publish_date>  
   <description>After the collapse of a nanotechnology society in England, the young survivors lay the foundation for a new society.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (4,   
 '<book id="bk104" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>Oberon''s Legacy</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2001-03-10</publish_date>  
   <description>In post-apocalypse England, the mysterious agent known only as Oberon helps to create a new life for the inhabitants of London. Sequel to Maeve Ascendant.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (5,   
 '<book id="bk105" xmlns="http://rmlalves.blogspot.com/">  
   <author>Corets, Eva</author>  
   <title>The Sundered Grail</title>  
   <genre>Fantasy</genre>  
   <price>5.95</price>  
   <publish_date>2001-09-10</publish_date>  
   <description>The two daughters of Maeve, half-sisters, battle one another for control of England. Sequel to Oberon''s Legacy.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (6,   
 '<book id="bk106" xmlns="http://rmlalves.blogspot.com/">  
   <author>Randall, Cynthia</author>  
   <title>Lover Birds</title>  
   <genre>Romance</genre>  
   <price>4.95</price>  
   <publish_date>2000-09-02</publish_date>  
   <description>When Carla meets Paul at an ornithology conference, tempers fly as feathers get ruffled.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (7,   
 '<book id="bk107" xmlns="http://rmlalves.blogspot.com/">  
   <author>Thurman, Paula</author>  
   <title>Splish Splash</title>  
   <genre>Romance</genre>  
   <price>4.95</price>  
   <publish_date>2000-11-02</publish_date>  
   <description>A deep sea diver finds true love twenty thousand leagues beneath the sea.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (8,   
 '<book id="bk108" xmlns="http://rmlalves.blogspot.com/">  
   <author>Knorr, Stefan</author>  
   <title>Creepy Crawlies</title>  
   <genre>Horror</genre>  
   <price>4.95</price>  
   <publish_date>2000-12-06</publish_date>  
   <description>An anthology of horror stories about roaches, centipedes, scorpions and other insects.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (9,   
 '<book id="bk109" xmlns="http://rmlalves.blogspot.com/">  
   <author>Kress, Peter</author>  
   <title>Paradox Lost</title>  
   <genre>Science Fiction</genre>  
   <price>6.95</price>  
   <publish_date>2000-11-02</publish_date>  
   <description>After an inadvertant trip through a Heisenberg Uncertainty Device, James Salway discovers the problems of being quantum.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (10,   
 '<book id="bk110" xmlns="http://rmlalves.blogspot.com/">  
   <author>O''Brien, Tim</author>  
   <title>Microsoft .NET: The Programming Bible</title>  
   <genre>Computer</genre>  
   <price>36.95</price>  
   <publish_date>2000-12-09</publish_date>  
   <description>Microsoft''s .NET initiative is explored in detail in this deep programmer''s reference.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (11,   
 '<book id="bk111" xmlns="http://rmlalves.blogspot.com/">  
   <author>O''Brien, Tim</author>  
   <title>MSXML3: A Comprehensive Guide</title>  
   <genre>Computer</genre>  
   <price>36.95</price>  
   <publish_date>2000-12-01</publish_date>  
   <description>The Microsoft MSXML3 parser is covered in detail, with attention to XML DOM interfaces, XSLT processing, SAX and more.</description>  
 </book>');  
   
 INSERT INTO @book  
 VALUES (12,   
 '<book id="bk112" xmlns="http://rmlalves.blogspot.com/">  
   <author>Galos, Mike</author>  
   <title>Visual Studio 7: A Comprehensive Guide</title>  
   <genre>Computer</genre>  
   <price>49.95</price>  
   <publish_date>2001-04-16</publish_date>  
   <description>Microsoft Visual Studio 7 is explored in depth, looking at how Visual Basic, Visual C++, C#, and ASP+ are integrated into a comprehensive development environment.</description>  
 </book>');   

Tendo esta tabela agora queremos fazer queries sobre o xml, para isso o sql tem um conjunto de métodos de ajuda para podermos aplicar uma expressão xpath à coluna xml.

Em conjunto com esses métodos também temos um helper para indicar à query os xml namespaces a usar.

 WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)  
 SELECT  
   Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,  
   Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,  
   Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,  
   Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price  
 FROM @book  

 WITH XMLNAMESPACES('http://rmlalves.blogspot.com/' as cnt)  
 SELECT  
   Details.value('(/cnt:book/@id)[1]', 'nvarchar(50)') AS BookId,  
   Details.value('(/cnt:book/cnt:author)[1]', 'nvarchar(50)') AS Author,  
   Details.value('(/cnt:book/cnt:title)[1]', 'nvarchar(50)') AS Title,  
   Details.value('(/cnt:book/cnt:price)[1]', 'money') AS Price  
 FROM @book  
 WHERE Details.value('(/cnt:book/cnt:price)[1]', 'money') > 40  

Fonte: Add Namespaces to Queries with WITH XMLNAMESPACES

Usar herança nos parâmetros dos métodos na WebApi

Num dos desenvolvimentos em que estou envolvido surgiu a necessidade de usar herança num dos parâmetros de entrada dum método na WebApi. O que eu quero dizer por usar herança é ter na assinatura do método um tipo base e depois quando os vários componentes chamam o método passam tipos derivados com informação mais especifica.

Ora então temos algo como:

 void Process(BaseEvent baseEvent);   
   
 public class BaseEvent   
 {   
   public int Id { get; set; }  
   public DateTime EventOn { get; set; }  
   public string User { get; set; }  
 }   
   
 public class LocationEvent : BaseEvent  
 {   
   public decimal Latitude { get; set; }  
   public decimal Longitude { get; set; }  
 }   
   
 public class VehicleEvent : BaseEvent  
 {   
   public string LicencePlate { get; set; }  
 }   

O problema surge quando os vários componentes chamam a WebApi, passam os tipos derivados, a WebApi recebe o tipo derivado mas faz sempre deserialize para o tipo base.

Para dar a volta à questão temos de controlar o deserialize através dum JsonConverter e mais uns pózinhos mágicos :)

1. Adicionar o pacote nuget do Newtonsoft.Json

 Install-Package Newtonsoft.Json  

2. Alteramos a classe base para ter o tipo que realmente é

 public class BaseEvent    
 {  
   public string typename { get { return this.GetType().Name; } }  
   
   public int Id { get; set; }  
   public DateTime EventOn { get; set; }  
   public string User { get; set; }  
 }   

3. Criamos um JsonConverter 

 public class BaseEventJsonCreationConverter : JsonConverter  
 {  
   public override bool CanWrite  
   {  
     get  
     {  
       return false;  
     }  
   }  
   
   public override bool CanConvert(Type objectType)  
   {  
     return typeof(BaseEvent).IsAssignableFrom(objectType);  
   }  
   
   private BaseEvent Create(Type objectType, JObject jObject)  
   {  
     var typename = jObject.Value("typename");  
     if (typeof(LocationEvent).Name.Equals(typename))  
     {  
       return new LocationEvent();  
     }  
     else if (typeof(VehicleEvent).Name.Equals(typename))  
     {  
       return new VehicleEvent();  
     }  
     else  
     {  
       return new BaseEvent();  
     }  
   }  
   
   public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)  
   {  
     if (reader.TokenType == JsonToken.Null)  
       return null;  
     // Load JObject from stream   
     JObject jObject = JObject.Load(reader);  
   
     // Create target object based on JObject   
     BaseEvent target = Create(objectType, jObject);  
   
     // Populate the object properties   
     serializer.Populate(jObject.CreateReader(), target);  
   
     return target;  
   }  
   
   public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)  
   {  
     throw new NotImplementedException();  
   }  
 }

4. Decoramos a classe base com JsonConverter

 [JsonConverter(typeof(BaseEventJsonCreationConverter))]  
 public class BaseEvent 
 {  
   . . .   
 }  

E agora a WebApi já faz o deserialize dos objectos correctamente :)


Fonte: Deserialising Json to derived types in Asp.Net Web API

Reconstruir e reorganizar todos os índices duma base de dados

Um dos problemas que muitas vezes encontro é nas bases de dados de desenvolvimento/qualidade tabelas com índices fragmentados, o que faz com que as pesquisas sejam muito lentas e algumas vezes surjam deadlocks.

Uma forma de resolver o problema é ir tabela a tabela, índice a índice e fazer rebuild pelo SQL Server Management Studio. Mas isto é uma tarefa demorada e "chata" o que faz com que muitas vezes na realidade ninguém o faça :)

Outra forma de fazer é ter um script que faz isto automaticamente, este script que mostro de seguida faz isso mesmo. Ele vai tabela a tabela avaliar a fragmentação de cada índice e baseado nas recomendações da Microsoft se a fragmentação for maior que 30% faz um REBUILD senão se for entre 5% e 30% faz REORGAZINE.

NOTA 1: O script pode demorar algum tempo a executar e pode bloquear tabelas.

NOTA 2: "There are no silver bullets" isto ajuda as queries a ficarem mais rápidas ... ou não, nada substitui o bom desenho\implementação da base de dados e se o problema for estrutural não será uma manutenção dos índices que o vai resolver.

-- http://technet.microsoft.com/en-us/library/ms189858.aspx
-- Microsoft Recommends
-- REBUILD IF > 30% fragmented
-- REORGANIZE IF > 5% and < 30% fragmented

DECLARE 
    @schemaName NVARCHAR(255),
    @tableName NVARCHAR(255),
    @indexName NVARCHAR(255),
    @sql NVARCHAR(2000),
    @fragmentation FLOAT;

DECLARE TableCursor CURSOR FOR
SELECT DISTINCT
    schemas.name AS SchemaName,
    tables.name AS TableName,
    indexes.name AS IndexName,
    dm_db_index_physical_stats.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tablename'), NULL, NULL, NULL)
INNER JOIN sys.indexes ON (dm_db_index_physical_stats.OBJECT_ID = indexes.OBJECT_ID AND dm_db_index_physical_stats.index_id = indexes.index_id)
INNER JOIN sys.index_columns ON (indexes.OBJECT_ID = index_columns.OBJECT_ID AND indexes.index_id = index_columns.index_id)
INNER JOIN sys.columns ON (index_columns.OBJECT_ID = columns.OBJECT_ID AND index_columns.column_id = columns.column_id)
INNER JOIN sys.tables ON (indexes.OBJECT_ID = tables.OBJECT_ID)
INNER JOIN sys.schemas ON (tables.schema_id = schemas.schema_id)

OPEN TableCursor
FETCH NEXT FROM TableCursor 
INTO @schemaName, @tableName, @indexName, @fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@fragmentation > 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REBUILD;'
        EXEC (@sql)
    END
    ELSE IF (@fragmentation BETWEEN 5 AND 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REORGANIZE;'
        EXEC (@sql)
    END

    FETCH NEXT FROM TableCursor 
    INTO @schemaName, @tableName, @indexName, @fragmentation
END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

Fonte: Reorganize and Rebuild Indexes

Queries da Entity Framework 5+ a correr na base de dados master

Se criarem um edmx de Entity Framework 5/6 e depois meterem o Sql Profiler a correr verificam que as queries não estão a correr na base de dados "correcta", elas estão a correr todas a partir da master.

Isto não impede em nada o funcionamento das aplicações (pois todas as queries vêm com as tabelas prefixadas da base de dados correcta) mas se tivermos múltiplas bases de dados torna-se complicado filtrar as queries por base de dados no Sql Profiler ...

Como é que se revolve o "problema"? Simples, basta forçar o atributo MultipleActiveResultSets na connection string a false.

Parece que foi uma decisão de desenho sempre que a conection string tem o MultipleActiveResultSets a true todas as queries que passam por essa conexão usam a base de dados master.

Fonte: SQL Server Profiler showing EF queries against master database?