Category Archives: Code

Code examples.

F#

AttemptIt – An F# computation expression for Railway Oriented Programming

For a while now I’ve been advancing my F# knowledge in various ways, one of those ways was to create a computation expression. As well as that I also wanted to release a useful nuget package and to add more open source software to the world.

While learning F# I found this article on railway oriented programming which I found quite effective in my day to day development. Finding no existing code/nuget packages that already had this I decided to create me own.

AttemptIt

The ReadMe file is vary basic for now but it should give you the general idea I’m trying for. Essentially an “attempt” represents a computation that is either a success or a failure; this can then be evaluated in to a “result”.

The syntax for now should let you bind from other attempts and bind from other results.

A quick reminder of the syntax:

//Basic usage
let doThis = attempt {
    let! x = someFunc()
    let! y = someOtherFunc y
    return! finalFunc y
}

//Catching Exceptions
let exceptionHandler (e:exn) -> Fail e.Message
let tryThis = Attempt.Catch exceptionHandler doThis

//Evaluating
let result = Attempt.Run tryThis

The plans for the not so distant future are:

  • Add Async helpers (Async<Attempt<‘a,’b>> gets award to work with I find)
  • Extend the syntax (for, while etc)
  • Add customer operators (check out “doublemap” in the railway programming link)
  • Get Travis-CI building this
  • Add more tests

This is also my second publicly available F# project and any comments on the code would be grateful.

F#

Keeping an F# console window open

I’m starting out in F# 3.0 and playing with a console app. Annoyingly the window keeps closing but on various videos it’s still open.

To get round this (like in a C# console app) you need to read from the input.

Like so

System.Console.ReadLine() |> ignore

This will wait until we enter something from the console and then ignore the result.

Code

UnitOfWork Attribute for WebAPI

In one of my previous post I mentioned the UnitOfWork concept and created an attribute for asp.net MVC which creates a transaction and wraps an entire request inside.  The MVC attribute closed it’s transaction after the view has rendered, this runs on the OnResultExecuted method.

In WebApi how ever there is no method that is fired after the response has been serialised. This may not seem like a problem; however, if you are returning a collection as IEnumerable<> from an ApiController or anything that lazily executes from you then then unit of work will closes it’s transaction BEFORE you’ve pulled back your data. So far the only way I’ve found to get around this problem is instead to return an IList<> which forces the execution.

Below is a the UniOfWork attribute I’ve been using for WebApi, it also rolls back the transaction if an exception occurs as oppose to the MVC way.

 

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = true, Inherited = true)]
  public sealed class UnitOfWorkFilter : ActionFilterAttribute, IExceptionFilter
  {
    public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
    {
      var unitOfWork = (IUnitOfWork)GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(IUnitOfWork));

      unitOfWork.EndTransaction();
    }

    public override void OnActionExecuting(HttpActionContext actionExecutingContext)
    {
      var unitOfWork = (IUnitOfWork)GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(IUnitOfWork));
      unitOfWork.BeginTransaction();
    }

    public Task ExecuteExceptionFilterAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)
    {
      var unitOfWork = (IUnitOfWork)GlobalConfiguration.Configuration.DependencyResolver.GetService(typeof(IUnitOfWork));
      unitOfWork.RollBack();
      return Task.Run(() => { }, cancellationToken);
    }
  }
Asp.Net MVC Code

Bundling single & multiple files in a view for MVC 3/4

If you have been following the development of asp.net mvc 4 you’ll be aware of the new bundling/minification feature; I won’t go in to detail how it works or how to use it as there are plenty of other sources for that.

I’m working on an existing MVC 3 project that is quite large has has a mixture of css, less and js dotted about with a minification system that works ok; so I was looking for a way to use the the new bundling feature in the existing project. Luckily the feature is available on nuget via the Microsoft.Web.Optimization package and there is a project called Bundle Transformer that extends the functionality.

The problem arises in registering a new bundle, the existing organisation doesn’t allow the use of the dynamic folder bundle and the front end devs want to avoid writing back end code as much as possible.

To solve this problem I developed a small bit of code that allows you to register a bundle right in the view (this is assuming you are following the Bundle Transformer documentation and have a “BundleConfig.cs” file in the App_Start folder):

 private static void RegisterBundle(string virtualPath, bool css, params string[] files)
    {
      var existing = BundleTable.Bundles.GetBundleFor(virtualPath);
      if (existing != null)
      {
        return;
      }

      var newBundle = new Bundle(virtualPath);
      foreach (var file in files)
      {
        newBundle.Include(file);
      }

      var nullOrderer = new NullOrderer();
      newBundle.Orderer = nullOrderer;

      if (css)
      {
        var cssTransformer = new CssTransformer();
        var cssMinifier = new CssMinify();
        newBundle.Transforms.Add(cssTransformer);
        newBundle.Transforms.Add(cssMinifier);
      }
      else
      {
        var jsTransformer = new JsTransformer();
        var jsMinifier = new JsMinify();
        newBundle.Transforms.Add(jsTransformer);
        newBundle.Transforms.Add(jsMinifier);
      }

      BundleTable.Bundles.Add(newBundle);
    }

    public static IHtmlString RegisterScript(string virtualPath, params string[] files)
    {
      RegisterBundle(virtualPath, false, files);
      return Scripts.Render(virtualPath);
    }

    public static IHtmlString RegisterStyle(string virtualPath, params string[] files)
    {
      RegisterBundle(virtualPath, true, files);
      return Styles.Render(virtualPath);
    }

    public static IHtmlString RegisterScriptUrl(string virtualPath, params string[] files)
    {
      RegisterBundle(virtualPath, false, files);
      return Scripts.Url(virtualPath);
    }

    public static IHtmlString RegisterStyleUrl(string virtualPath, params string[] files)
    {
      RegisterBundle(virtualPath, true, files);
      return Styles.Url(virtualPath);
    }

And now the front end devs can register a bundle in the view:

@BundleConfig.RegisterStyle("~/css/common-loggedin",
    "~/Content/less/global.less",
   "~/Content/css/transforms.css",
   "~/Content/css/ui-darkness/jquery-ui.custom.css",
   "~/Content/css/jquery.qtip.css" ,
   "~/Content/css/chosen.css" 
   )
	<link rel="stylesheet" href="@BundleConfig.RegisterStyleUrl("~/css/print","~/Content/less/print.less")" media="print" />

 @BundleConfig.RegisterScript("~/script/application",
        "~/Content/js/application.js"  , 
        "~/Content/js/thap/notifications.js"   
    )

The first parameter defines the bundle name, the rest are the files you want to include.

Code Python

Project Euler: Problem 4

Problem:

A palindromic number reads the same both ways. The largest palindrome made from the product of two 2-digit numbers is 9009 = 91 99.

Find the largest palindrome made from the product of two 3-digit numbers.

Source: http://projecteuler.net/problem=4


Solution:

Before we can being to solved the problem above we first must answer the questions raised by it. In other words:

  • Palindromic Number?

 

Palindromic Number?

A palindromic number a number that is a palindrome, which is just a word or phrase that is the same in either direction.

 

Code:

Source: https://bitbucket.org/TWith2Sugars/project-euler/src/dbd1afaa7fc6/python/4.py

def isPalindrome(n):
    return (n==n[::-1])

def findLargestPalindrome():
    largest = 0
    for x in range(100,1000):
        for y in range(100,1000):
            product = x*y

            if product > largest and isPalindrome(str(product)):
                largest = product
    return largest

print(findLargestPalindrome())

 

IsPalindrome

This method is what is responsible for checking if a number is a palindrome; the “[::-1]” is what takes the parameter and reverses it. That’s it!

 

Asp.Net MVC Code

Unit of Work, NHibnernate and Asp.Net MVC

In a previous post I mentioned for some projects I’m using a combination of NHibernate, unit of work and asp.net mvc; this post will give an example of how I set this up for a project and how to get mvc to automatically create instances of UoW, wrap our code in transactions and clean up after it’s self via ActionFilterAttributes. Some of the code I have is a modified version of this but probably not as nice.

Here is a list of the interfaces that’ll be created:

And classes that implement them:

And finally the mvc Attribute: UnitOfWorkFilter.

This maybe(is) a lot of work for for something that seems pretty straight forward but it allows the code to be decoupled and makes testing much easier.  For example a project I’ve been working on uses this workflow and has a windows service to do some back end processing; by creating a RequestState to work with a windows service the rest of the code doesn’t need changing (apart from the filter obviously.)

I’ll leave the code further down in the post so things don’t get messy.

Actually using this setup.

This post makes the assumption that your data access is all via nhibernate and you’re using the repository pattern or something similar. Your repositories will need access to the IActiveSessionManager. If you’re using a DI like Ninject it should pass it through for you.

In your repository you can access the nhibernate session like so:

this.activeSessionManager.GetActiveSession();

I personally have a property to do this for me:

    public ISession Session
    {
      get
      {
        return this.activeSessionManager.GetActiveSession();
      }
    }

From here on do what ever queries you need, all the repositories in the will share the same session during the same request.

Applying the attribute

You can either have the UnitOfWorkFilter attached to each controller/method or in the global.asax you can register is as a global filter.

 [UnitOfWorkFilter]
  public class UserController : Controller
{
}

or

    public static void RegisterGlobalFilters(GlobalFilterCollection filters)
    {
      filters.Add(new UnitOfWorkFilter());

    }

That’s it!.

How it all fits together

Imagine you have a controller that gets you a list of users (UserController).

  1. You issue a GET to the controller action
  2. The UnitOfWorkFilter kicks in and starts a new transaction from the ActiveSessionManager (The same manager your repo’s use)
  3. The ActiveSessionManager checks for/creates a new nhibernate session to work with
  4. Your repos use this ActiveSessionManager/NHibernate session to get its data
    1. If you have more than one repo in the controller action then no new sessions are created, the same one is shared.
  5. The controller returns the users
  6. The UnitOfWorkFilter then commits the transaction and closes the current session
    1. If something goes wrong like an exception the the UnitOfWorkFilter rollsback the transaction – preserving the integrity of the database

Below is the code to implement this.

IUnitOfWork

  public interface IUnitOfWork : IDisposable
  {

    void BeginTransaction();

    void EndTransaction();

    void RollBack();
  }

IRequestState

  public interface IRequestState
  {
    T Get<T>(string key);

    void Store(string key, object something);
  }

ISessionProvider

The ISession is the NHibernate session.

  public interface ISessionProvider
  {
    ISession Create();
  }

IActiveSessionManager

  public interface IActiveSessionManager: IDisposable
  {
    ISession GetActiveSession();

    void ClearActiveSession();

    bool HasActiveSession { get; }
  }

UnitOfWork

/// <summary>
  /// An nhibernate specific implementation of the unit of work concept
  /// </summary>
  public class UnitOfWork : IUnitOfWork
  {
    private readonly IActiveSessionManager sessionManager;

    private ITransaction transactionScope;

    public UnitOfWork(IActiveSessionManager sessionManager)
    {
      this.sessionManager = sessionManager;
    }

    public void Dispose()
    {
      if (this.transactionScope != null)
      {
        this.transactionScope.Dispose();
      }

      this.sessionManager.Dispose();
    }

    public void BeginTransaction()
    {
      if (this.transactionScope != null)
      {
        throw new InvalidOperationException("Transaction already started; nested transactions are not supported");
      }

      this.transactionScope = this.sessionManager.GetActiveSession().BeginTransaction();
    }

    public void EndTransaction()
    {
      if (this.transactionScope == null)
      {
        throw new InvalidOperationException("No active transaction found");
      }

      if (!this.transactionScope.IsActive)
      {
        throw new InvalidOperationException("This transaction is no longer active");
      }

      try
      {
        this.transactionScope.Commit();
      }
      catch (Exception ex1)
      {
          this.RollBack();
      }
    }

    public void RollBack()
    {
      this.transactionScope.Rollback();
    }
  }

AspNetRequestState

  public class AspNetRequestState : IRequestState
  {
    public T Get<T>(string key)
    {
      return (T)HttpContext.Current.Items[key];
    }

    public void Store(string key, object something)
    {
      HttpContext.Current.Items[key] = something;
    }
  }

SessionProvider

Note that the session factory has not been configured, this is left up you to configure.

 public class SessionProvider : ISessionProvider
  {
    private static ISessionFactory sessionFactory;

    public SessionProvider()
    {
      if (sessionFactory == null)
      {
	    // Do your mhibernate configuration here
        var configration = new Configuration();
        sessionFactory = configration.BuildSessionFactory();
      }
    }

    public ISession Create()
    {
      return sessionFactory.OpenSession();
    }
  }

ActiveSessionManager

public class ActiveSessionManager : IActiveSessionManager
  {
    private const string sessionKey = "_currentSession";

    private readonly IRequestState requestState;

    private readonly ISessionProvider sessionProvider;

    public ActiveSessionManager(IRequestState requestState, ISessionProvider sessionProvider)
    {
      this.requestState = requestState;
      this.sessionProvider = sessionProvider;
    }

    public ISession GetActiveSession()
    {
      if (this.Current == null)
      {
        var newSession= this.sessionProvider.Create();
        this.Current = newSession;
        return newSession;
      }

      return this.Current;
    }

    public void ClearActiveSession()
    {
      this.Current = null;
    }

    public bool HasActiveSession
    {
      get { return this.Current != null; }
    }

    protected virtual ISession Current
    {
      get
      {
        return requestState.Get<ISession>(sessionKey);
      }
      set
      {
        requestState.Store(sessionKey, value);
      }
    }

    public void Dispose()
    {
      var session = this.Current;
      if(session != null)
      {
        session.Dispose();
        this.ClearActiveSession();
      }
    }
  }

 

UnitOfWorkFilter

 

public class UnitOfWorkFilter : ActionFilterAttribute
  {
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
      var unitOfWork = DependencyResolver.Current.GetService<IUnitOfWork>();
      unitOfWork.BeginTransaction();
    }

    public override void OnResultExecuted(ResultExecutedContext filterContext)
    {
      var unitOfWork = DependencyResolver.Current.GetService<IUnitOfWork>();
      unitOfWork.EndTransaction();
    }
  }
Asp.Net MVC Code

How to enable CORS in Asp.Net

CORS is a method of allowing calls from one domain to another that would otherwise be forbidden due to same origin policy. There are many ways to enable this to allow asp.net to respond to these types of request and the method below allows calls from any domain. This might not be right for every application but it’s a quick way to get started.

In the apps web.config add the following section:

  <system.webServer>
    <httpProtocol>
      <customHeaders>
        <add name="Access-Control-Allow-Origin" value="*" />
      </customHeaders>
    </httpProtocol>
Code

Asynchronous Database Auditing

I needed to create an extensive db audit system for a project at work, little bit of searching lead me to these two articles:

  1. Trigger Based Audit
  2. Centralized Asynchronous Auditing
Initially I tried using just the trigger based auditing but that had some performance issues; so my solution was to create a script (3 scripts actually) that combined the trigger generating code of the first article with the service based asynchronous writing of the second article.

How it works

NOTE: This post will get out of date at some point so it’s best to get the scripts from the bitbucket repo: https://bitbucket.org/ThapLtd/async-database-audit

Setting up the Audit Database

The first script creates the database the records the audit information and sets up a service broker that listens to a queue for a message from another database (we’ll set this up in another script). This script will generate a guid that you’ll need to take note of for later:
USE master

IF DB_ID('[AUDITDB]') IS NULL
	CREATE DATABASE [AUDITDB]		

-- enable service broker
ALTER DATABASE [AUDITDB] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [AUDITDB] SET TRUSTWORTHY ON

GO

USE [AUDITDB] 	

GO
-- get service broker guid for [AUDITDB].
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
SELECT	service_broker_guid
FROM	sys.databases
WHERE	database_id = DB_ID()

GO

IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
	DROP TABLE dbo.MasterAuditTable

GO
-- Master Audit Table
CREATE TABLE MasterAuditTable
(
	AuditID BIGINT IDENTITY(1,1) NOT NULL,
	DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
	DatabaseName nvarchar(128),
	TableName nvarchar(128),
	PrimaryKeyField nvarchar(1000),
	PrimaryKeyValue nvarchar(1000),
	FieldName nvarchar(128),
	OldValue nvarchar(1000),
	NewValue nvarchar(1000),
	UpdateDate datetime DEFAULT (GetDate()),
	UserName nvarchar(128)
)

GO

IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
	DROP TABLE dbo.AuditDialogs

GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
	DbId INT NOT NULL,
	DialogId UNIQUEIDENTIFIER NOT NULL
)

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_WriteAuditData

GO

-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
	DECLARE @msgBody XML
	DECLARE @dlgId uniqueidentifier

	WHILE(1=1)
	BEGIN
		BEGIN TRANSACTION
		BEGIN TRY

			-- insert messages into audit table one message at a time
			;RECEIVE top(1)
					@msgBody	= message_body,
					@dlgId		= conversation_handle
			FROM	dbo.TargetAuditQueue

			-- exit when the whole queue has been processed
			IF @@ROWCOUNT = 0
			BEGIN
				IF @@TRANCOUNT > 0
				BEGIN
					ROLLBACK;
				END
				BREAK;
			END 

			DECLARE @DatabaseName NVARCHAR(128), @TableName NVARCHAR(128),
					@DMLType CHAR(1),
					@PrimaryKeyField nvarchar(1000), @PrimaryKeyValue nvarchar(1000),
					@FieldName nvarchar(128), @OldValue nvarchar(1000), @NewValue nvarchar(1000),
					@UpdateDate datetime, @UserName nvarchar(128)

			-- xml datatype and its capabilities rock
			SELECT	@DatabaseName = T.c.query('/AuditMsg/DatabaseName').value('.[1]', 'NVARCHAR(128)'),
					@TableName = T.c.query('/AuditMsg/TableName').value('.[1]', 'NVARCHAR(128)'),
					@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
					@PrimaryKeyField = T.c.query('/AuditMsg/PrimaryKeyField').value('.[1]', 'NVARCHAR(1000)'),
					@PrimaryKeyValue = T.c.query('/AuditMsg/PrimaryKeyValue').value('.[1]', 'NVARCHAR(1000)'),
					@FieldName = T.c.query('/AuditMsg/FieldName').value('.[1]', 'NVARCHAR(128)'),
					@OldValue = T.c.query('/AuditMsg/OldValue').value('.[1]', 'NVARCHAR(1000)'),
					@NewValue = T.c.query('/AuditMsg/NewValue').value('.[1]', 'NVARCHAR(1000)'),
					@UpdateDate = T.c.query('/AuditMsg/UpdateDate').value('.[1]', 'datetime'),
					@UserName = T.c.query('/AuditMsg/UserName').value('.[1]', 'NVARCHAR(128)')
			FROM	@msgBody.nodes('/AuditMsg') T(c)

			INSERT INTO dbo.MasterAuditTable(DatabaseName, TableName, DMLType, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)
			SELECT @DatabaseName, @TableName, @DMLType, @PrimaryKeyField, @PrimaryKeyValue, @FieldName, @OldValue, @NewValue, @UpdateDate, @UserName

			-- No need to close the conversation because auditing never ends
			-- you can end conversations if you want periodicaly with a scheduled job
			-- END CONVERSATION @dlgId

			IF @@TRANCOUNT > 0
			BEGIN
				COMMIT;
			END
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK;
			END
			-- insert error into the AuditErrors table
			INSERT INTO AuditErrors (
					ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
					ErrorSeverity, ErrorState, AuditedData)
			SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
					ERROR_SEVERITY(), ERROR_STATE(), @msgBody
		END CATCH;
	END
END

GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
	DROP QUEUE dbo.TargetAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
	WITH	STATUS=ON,
	ACTIVATION (
		PROCEDURE_NAME = usp_WriteAuditData,	-- sproc to run when the queue receives a message
		MAX_QUEUE_READERS = 50,					-- max concurrently executing instances of sproc
		EXECUTE AS 'dbo' );

-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
		AUTHORIZATION dbo
	ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
Just replace all instances of AUDITDB with the name you want to use.

Creating/Modifying the database to Audit

NOTE: If you are modifying an existing database then you may need to kill all connections to run this script, this happened to me a few times.
The second script sets up the database to allow it to talk to the audit database you just setup; creates the store procedure which will send a message to the audit db queue and create a table to log any auditing problems
USE master
GO

IF DB_ID('[DBToAudit]') IS NULL
	CREATE DATABASE [DBToAudit]

-- enable service broker
ALTER DATABASE [DBToAudit] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [DBToAudit] SET TRUSTWORTHY ON

GO
USE [DBToAudit]

GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
	DROP QUEUE InitiatorAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]

GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message] 
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the initiator queue 
CREATE QUEUE dbo.InitiatorAuditQueue

-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender] 
	AUTHORIZATION dbo
	ON QUEUE dbo.InitiatorAuditQueue	-- no contract means service can only be the initiator

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_SendAuditData

GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
	@AuditedData XML
)
AS
BEGIN
	BEGIN TRY
		IF @AuditedData IS NULL
			RETURN
		DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
		SELECT @dlgIdExists = 1

		-- Check if our database already has a dialog id that was previously used
		-- Why reusing conversation dialogs is a good this is explaind here
		-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
		-- very well
		SELECT	@dlgId = DialogId
		FROM	[AUDITDB].dbo.AuditDialogs AD 
		WHERE	AD.DbId = DB_ID()
		IF 	@dlgId IS NULL
		BEGIN 
			SELECT @dlgIdExists = 0
		END

		-- Begin the dialog, either with existing or new Id
		BEGIN DIALOG @dlgId
			FROM SERVICE    [//Audit/DataSender]											   
			TO SERVICE      '//Audit/DataWriter', 
							-- this is a [AUDITDB] Service Broker Id (change it to yours)
							'A688B601-A575-4458-9D7C-1607CD7F540D'
			ON CONTRACT     [//Audit/Contract]
		WITH ENCRYPTION = OFF;

		-- add our db's dialog to AuditDialogs table if it doesn't exist yet
		IF @dlgIdExists = 0
		BEGIN 
			INSERT INTO [AUDITDB].dbo.AuditDialogs(DbId, DialogId)
			SELECT	DB_ID(), @dlgId
		END
		--SELECT @AuditedData

		-- Send our data to be audited
		;SEND ON CONVERSATION @dlgId	
		MESSAGE TYPE [//Audit/Message] (@AuditedData)
	END TRY
	BEGIN CATCH
		INSERT INTO AuditErrors (
				ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage, 
				ErrorSeverity, ErrorState, AuditedData)
		SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), 
				ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
	END CATCH
END

GO
Replace all instances of DBToAudit with the name of your database to audit, replace all instances of AUDITDB with the name of the audit database you set up previously and finally replace the GUID (on line 100 or near it) with the GUID generated by the first script.

Generating Triggers

The final script is generates a trigger for each table that in turn generates a script to pull the changes made during an insert/update/delete operation and sends it to the store procedure generated in the second script. There is nothing to do here but run it 🙂

NOTE: This script hasn’t been adapted to ignore tables that store binary information, if you know how to do this then feel free to fork /submit a patch.


DECLARE @sql varchar(max), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'

WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = ''
SELECT @sql = @sql+
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on [' + @TABLE_NAME+ '] for insert, update, delete

as

SET NOCOUNT ON

  declare @bit           int,
          @field         int,
          @maxfield      int,
          @char          int,
          @fieldname     varchar(128),
          @TableName     varchar(128),
          @PKCols        varchar(1000),
          @sql           varchar(2000),
          @UpdateDate    varchar(21),
          @UserName      varchar(128),
          @Type          char(1),
          @PKFieldSelect varchar(1000),
          @PKValueSelect varchar(1000)

  select @TableName = '''+@TABLE_NAME+'''

  select @UserName = system_user,
         @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' +
                       convert(varchar(12), getdate(), 114)

  if exists (select *
             from   inserted)
    if exists (select *
               from   deleted)
      select @Type = ''U''
    else
      select @Type = ''I''
  else
    select @Type = ''D''

  select *
  into   #ins
  from   inserted

  select *
  into   #del
  from   deleted

  select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME +
                   '' = d.'' +
                          c.COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKFieldSelect = COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKValueSelect = coalesce(@PKValueSelect + ''+'', '''') +
                                 ''isnull(convert(nvarchar(100), coalesce(i.'' +
                                                  COLUMN_NAME + '',d.'' +
                          COLUMN_NAME +
                                 '')),'''''''')''
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  if @PKCols is null
    begin
        raiserror(''no PK on table %s'',
                  16,
                  -1,
                  @TableName)

        return
    end

  select @field = 0,
         @maxfield = max(ORDINAL_POSITION)
  from   INFORMATION_SCHEMA.COLUMNS
  where  TABLE_NAME = @TableName

  while @field < @maxfield
    begin
        select @field = min(ORDINAL_POSITION)
        from   INFORMATION_SCHEMA.COLUMNS
        where  TABLE_NAME = @TableName
               and ORDINAL_POSITION > @field

        select @bit = ( @field - 1 )% 8 + 1

        select @bit = power(2, @bit - 1)

        select @char = ( ( @field - 1 ) / 8 ) + 1

        if substring(COLUMNS_UPDATED(), @char, 1) & @bit > 0
            or @Type in ( ''I'', ''D'' )
          begin
              select @fieldname = COLUMN_NAME
              from   INFORMATION_SCHEMA.COLUMNS
              where  TABLE_NAME = @TableName
                     and ORDINAL_POSITION = @field

             	SELECT @sql =''
             DECLARE @auditBody XML
             	
             	SELECT  @auditBody =
		''''
			'+DB_NAME()+'
			'' + @TableName + ''
			'' + @Type + ''
			'' + @PKFieldSelect + ''
			''''+'' + @PKValueSelect + ''+''''
			'' + @fieldname + ''
			''''+isnull(convert(nvarchar(1000),d.'' + @fieldname + ''), '''''''') + ''''
			''''+isnull(convert(nvarchar(1000),i.'' + @fieldname + ''), '''''''') + ''''
			'' + @UpdateDate + ''
			'' + @UserName+ ''
		''''
		
	''

    SELECT @sql = @sql + '' from #ins i full outer join #del d''

    SELECT @sql = @sql + @PKCols

    SELECT @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' +
                  @fieldname +
                         '' is not null)''

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' +
                  @fieldname +
                         '' is null)

				EXEC dbo.usp_SendAuditData @auditBody
                         ''
    exec( @sql)
end
end 
'


SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'
END
Code Python

Project Euler: Problem 3

Problem:

The prime factors of 13195 are 5, 7, 13 and 29.

What is the largest prime factor of the number 600851475143?

Source: http://projecteuler.net/index.php?section=problems&id=3


Solution:

Before we can being to solved the problem above we first must answer the questions raised by it. In other words:

  • What is a prime number?
  • What is a factor?
  • What is a prime factor?
  • How do we find the prime factors for a number?

 

What is a prime number?

A prime number is any natural number that can only be divided by 1 or itself. E.G. 7 is a prime number because it can only be divided by 1 or 7 while 10 is not a prime number because it’s divisors are 1,2,5,10.

 

What is a factor?

A factor (also known as a divisor) is any number that that can divide in to another number and not leave a remainder. In the previous section about prime numbers you can see the for 10 it’s factors are:

  • 1
  • 2
  • 5
  • 10

 

What is a prime factor?

A prime factor is any factor that is also a prime number.

 

How do we find the prime factors for a number?

The method of finding prime factors is called Prime Factorisation.  It involves taking a number and dividing it by the smallest prime possible, then taking what’s remaining and dividing that by the smallest prime possible; keep doing this until the remainder is a prime. This Khans Academy video offers a great introduction to this concept.

 

Code:

Source: https://bitbucket.org/TWith2Sugars/project-euler/src/8892d0a39946/python/3.py

def primeNumbers(n):
    # Found this example on Wikipedia: http://en.wikipedia.org/wiki/Sieve_of_Eratosthenes
    # Create a candidate list within which non-primes will be
    # marked as None; only candidates below sqrt(n) need be checked. 
    candidates = list(range(n+1))

    # Square root of n
    fin = int(n**0.5)

    # Loop over the candidates, marking out each multiple.
    for i in xrange(2, fin+1):
        if candidates[i]:
            candidates[2*i::i] = [None] * (n//i - 1)

    # Filter out non-primes and return the list.
    return [i for i in candidates[2:] if i]

def primeFactors(n):
    # Generators all of the prime factors for n

    # The largest number that a prime factor could be
    maxPossiblePrimeNumber = int(n**0.5)

    # List of all prime numbers up to the square root of n
    primes = primeNumbers(maxPossiblePrimeNumber)

    while n > 1:
        for p in primes:
            # This is a prime factor
            if n % p == 0:
                # Set n to be the result of the division of n by the current prime number (p)
                n = n / p
                yield p

maxPrime = max(primeFactors(600851475143))
print(maxPrime)

The code is broken down in to two parts:

  • Generating Prime Numbers (primeNumbers)
  • Performing Prime Factorisation (primeFactors)

 

Generating Prime Numbers

There are many methods of generating prime numbers and in my search I’ve decided to use a method called Sieve of Eratosthenes which is pretty efficient are generating prime numbers below 100. It also comes with a python example that we can use. You’ll see the code below.

 

Performing Prime Factorisation

This function just implemented the method of finding prime factors as mentioned earlier, there is probably a more efficient/mathematical way of doing this but for now it’s a good start.

 

 

Code Python

Project Euler: Problem 2

Right, here is my attempt at problem 2 of project euler.

Problem: http://projecteuler.net/index.php?section=problems&id=2

source: https://bitbucket.org/TWith2Sugars/project-euler/changeset/3ec9f237dbb9

def fib(n):
    evenSum, a, b = 0, 0, 1
    while a < n:
        a, b = b, a+b
        if b % 2 == 0:
		    evenSum += b

    return evenSum;

result = fib(4000000)
print(result)

Update:

Thanks to Mohammad’s advice the code has been update to avoid pointlessly assigning variables.

def fib(n):
    evenSum, a, b = 0, 1, 2
    while a < n:
        if b % 2 == 0:
		    evenSum += b
        a, b = b, a+b
    return evenSum;

result = fib(4000000)
print(result)