July 17, 2006

Can you hear me NOW?!?!

My wife is very disgusted with our packet8 telephone. Part of the issues we're facing are that our Belkin router will block the outbound voice (thinking that it is a DOS attack). In the security log, there was this entry:

07/17/2006 18:35:57 **UDP Flood to Host** 192.168.1.10, 8006->>
209.247.23.73, 62602 (from WAN Outbound)


So, thanks to a technical article I found, there is a support page that references this URL: http://192.168.1.1/firewall_spi_h.stm which allows you to turn off the "SPI and Anti-DoS firewall protection:"

It should be easier to turn off a feature like this than an undocumented feature.

July 6, 2006

It's about time...

Time and space. Two of my favorite things. Time keeps everything from happening all at once. Space keeps everything from happening to you. Time AND Space keep everything from happening all at once to you!
The problem to be solved today is this: Create a .net object that has it's XML serialization saved to an XML column in a database table. Now, have a trigger (add/update) that takes that XML field and denormalizes its content to some additional columns. In this way, you have the denormalized data that you might want to use for joins and filtering without having to parse the XML field over and over.
You'd create a class something like this:
public class Foo {
Guid _id = Guid.NewGuid(); //This is our object's database identifier
DateTime _updated= DateTime.Now; //This was the last time the object was changed
string _description = string.empty;

public Guid Id{ get{return _id;} set{_id = value;} }
public string Description { 
get{return _description;} 
set{_description = value; Update();} }
public DateTime Updated { get{return _updated;} set{} }
private void Update(){ _updated = DateTime.Now; }
}

You remember how to serialize an object as XML, right? Ok, just for reference, here is how we would get our object as XML text:
XmlSerializer serializer = new XmlSerializer(typeof(Foo));
System.Text.StringBuilder sb = new System.Text.StringBuilder();
TextWriter writer = new StringWriter(sb);
serializer.Serialize(writer, this);
writer.Close();
string XMLText = sb.ToString();

You would also have a table that looks like this:
CREATE TABLE [dbo].[TableXML](
[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT 
[XML] [xml] NOT NULL,
[Description] [nvarchar](max) )

Then you'd add a trigger to the table that looks like this:
CREATE TRIGGER [TableXML_InsUpd] ON  [dbo].[TableXML] AFTER INSERT,UPDATE AS 
BEGIN
SET NOCOUNT ON;
IF UPDATE([XML]) -- Only update the table if the XML field was updated.
BEGIN
UPDATE [MWI].[dbo].[TableXML] SET 
[Description] = INSERTED.XML.value('(//Description)[1]','nVarchar(MAX)'),
[Updated] = INSERTED.XML.value('xs:dateTime((//Updated)[1])','DateTime')
FROM INSERTED WHERE [PromotionXML].PromotionId = INSERTED.PromotionId
END
END

With these things in place, you could serialize the class to XML with code that looks like this:
public static void SaveFooDB(Foo foo) {
// Put the XML representation of the object into the database
SqlConnection conn = GetDataConnection(); //you'll have to write GetDataConnection
SqlCommand cmd = new SqlCommand("foo_InsertOrUpdate", conn);
using (cmd) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fooXML", promotion.ToString());
cmd.ExecuteNonQuery();
}
}

The stored procedure "foo_InsertOrUpdate" simply checks if the id already exists in the database and updates the existing record if it does, otherwise it is a new record, so it is inserted. Why SQL server doesn't have this sort of query is a mystery to me. Notice, however that we only have to pass one parameter (the serialized XML from our object) and we extract the Id from that XML.
CREATE PROCEDURE [dbo].[foo_InsertOrUpdate]( @FooXML XML ) AS
BEGIN 
DECLARE @IdUniqueIdentifier
select @Id= @FooXml.value('(//Id)[1]','UniqueIdentifier')
IF Exists (Select FooId from [MWI].[dbo].[FooXML] WHERE Id= @Id) 
BEGIN
UPDATE [MWI].[dbo].[FooXML] SET [XML] = @FooXML WHERE Id= @Id
END
ELSE 
BEGIN 
INSERT INTO [MWI].[dbo].[PromotionXML]([PromotionID],[XML])
VALUES (@PromotionID, @PromotionXML)
END
END

Now you might argue that since we have our insert/update procedure, we could do all the work of populating the additional columns here rather than in the trigger. You are right of course IF all of your table updates come through this procedure. Me, I'd rather not assume that everyone will play nicely, especially since it is possible to update the xml directly and in bulk. So, the trigger works for my needs, your mileage may vary.
Now everything works very well until we introduce another DateTime property in our class. If we (for example) bind that value to a DateTimePicker component, the XML extraction formatting will break. What is up with that?
It turns out that the DateTime format in .NET is far more robust than in many other systems (notibly SQL Server's DateTime). In fact, there is a property called "kind" on .net DateTime objects. The DateTimePicker uses the DateTimeKind.Unspecified kind which doesn't have time zone information. We can remedy this little problem by creating a new DateTime object in the set accessor of our new FooDate Property like this:
DateTime _fooDate = DateTime.Today;

public DateTime FooDate{
get { return _fooDate; }
set { _fooDate = new DateTime(value.Year, value.Month, value.Day, 0, 0, 0, 0, DateTimeKind.Local);}
}

Another thing that we can do here is to strip out the time infomation that is often provided by a calendar picker or other unreliable DateTime mechanism. One of my favorite tricks here is to specify the end of the day when people are picking a date range, so that we don't end up with that annoying "pick the next day really mean the end of today. Do something like this:
DateTime _fooEndDate= DateTime.Today;

public DateTime FooEndDate{
get { return _fooEndDate; }
set { _fooEndDate= new DateTime(value.Year, value.Month, value.Day, 23, 59, 59, 0, DateTimeKind.Local);}
}

And finally (wow, this was a long post) you don't want to put in 999 for the milliseconds if you are going to put this value into SQL server, someplace along the way, the milliseconds are rounded up to tomorrow and our SQL column representation becomes different from the XML representation.

July 5, 2006

U-Turn (Going the other way)

So, here's a way to get back a delimited list of items from a table. In other words, concatenate values into one column (that you can then take together to pass to my parse table function).

--Setup a sample data to demonstrate the concept
declare @Table1 as Table (id int, Item varchar(100))
insert into @Table1 values (1, 'a')
insert into @Table1 values (1, 'b')
insert into @Table1 values (2, 'c')
-- show the table data
select * from @Table1

--retrieve the data from @Table1 as a concatinated list
Select distinct id, (
Select upper(Item)+' ' as [text()] from @Table1 S 
where id = T1.id 
for xml path('')) Items  from @Table1 T1