Archive for the 'Lightspeed' Category

18AprNinject (Part 2): Customizing your infrastructure for logging

Yesterday we had a little introduction to Ninject. Today I’d like to examine what’s involved in getting some AOP style logging going for your application. I find that there are only very few places where I’m logging something that steps out of the bounds of being called at the end of the method when things succeed. I’m generally more interested in what happens when things go wrong and that’s when I log the exception. In some cases I’ll be  interested in the actual parameters.

The code I’m going to show you will take care of basic logging needs but if you want more information about what happens inside your method you’re either going to need to extend my implementation or log the call from within your method body.  We’re going to implement logging that when run with the debug level turned on will tell us that a method is going to execute, whether it finished successfully or with an error and if there was an error it will also log the exception. 

To get this thing on the road, on the LightSpeed road that is. We’re going to use NLog to get flexible routing of log messages. We’re first going to create a LightSpeedTarget that is a customized NLog target (route destination) for your log messages.

namespace LoggingDemo.UI.Integration
{
    /// 
    /// This class represents a NLog target that we can reference in the NLog.config file
    /// You can use this to use Lightspeed to log to the database just like a file target etc.
    /// 
    [Target("LightSpeedTarget")]
    public class LightSpeedTarget : TargetWithLayout
    {

        public LightSpeedTarget(){
            Layout = "${message}";
        }

        protected override void Write(LogEventInfo logEvent)
        {
            var logMessage = CompiledLayout.GetFormattedMessage(logEvent);

            var appEvent = new ApplicationEvent
            {
                Sequence = logEvent.SequenceID,
                EventTime = logEvent.TimeStamp,
                Level = logEvent.Level.Name,
                LoggerName = logEvent.LoggerName,
                Message = logMessage
            };

            if (logEvent.Exception != null) appEvent.Exception = logEvent.Exception.ToString();
            if (logEvent.StackTrace != null) appEvent.StackTrace = logEvent.StackTrace.ToString();
            if (logEvent.UserStackFrame != null) appEvent.UserStackFrame = logEvent.UserStackFrame.ToString();

            Repository.Add(appEvent);
            Repository.CompleteUnitOfWork();
        }
    }
}

The class above overrides the TargetWithLayout class from the NLog project. The attribute tells NLog how to find this target. In the constructor I override the default message layout because it was a bit too verbose to my liking.

We then override the Write method where we map the properties to our ApplicationEvent. And lastly we add the event to the repository and commit it.

Now if we want our application to use this we’re going to have to tell it how. Nlog does this by looking for an nlog.config file in your application directory.  So let’s go ahead and add an nlog.config file to our project. I’ll show you the nlog.config file for the application. In my unit test project I’m using the console logger so I can see what’s going on :)

The NLog.config file for the application:

image

My NLog.config file for my test project:

 image

This enables our application to use the target we just created. Moving on now to the Interceptor which is Ninject specific (You can do the same with other DI frameworks).  In Ninject you can tell the kernel to intercept a method on a class and execute some logic before and after invocation of the method. We can tell Ninject to intercept all methods on a class, specific methods or when we say all methods we can still exclude some of them. For our logging example I chose to use all methods on a class. You can do this by decorating the class with an [Intercept] attribute. If you would want a method not to be intercepted you can by decorating that method with an [DoNotIntercept] attribute.

I took the liberty of inheriting of that attribute first.. that makes the rest of my code look a little bit prettier.

The LogMyCallsAttribute:

using Ninject.Core;

namespace LoggingDemo.UI.Interceptors
{
    public class LogMyCallsAttribute : InterceptAttribute
    {
        public LogMyCallsAttribute() : base(typeof(LoggingInterceptor))
        {
        }
    }
}

The LoggingInterceptor:

using System;
using Ninject.Core.Interception;
using Ninject.Core.Logging;

namespace LoggingDemo.UI.Interceptors
{
    public class LoggingInterceptor : SimpleFailureInterceptor
    {
        private readonly ILogger _logger;
        private bool _hasError;

        public LoggingInterceptor(ILogger logger)
        {
            _logger = logger;
            _hasError = false;
        }
        protected override void BeforeInvoke(IInvocation invocation)
        {
            _logger.Debug("About to invoke {0}", MethodNameFor(invocation));
        }

        protected override void OnError(IInvocation invocation, Exception exception)
        {
            _logger.Error(exception, "There was an error invoking {0}.\r\n", MethodNameFor(invocation));
            _hasError = true;
            base.OnError(invocation, exception);
        }

        protected override void AfterInvoke(IInvocation invocation)
        {
            _logger.Debug("invocation of {0} finished {1}.", MethodNameFor(invocation), (_hasError ? "with an error state" : "successfully"));
        }

        private static string MethodNameFor(IInvocation invocation)
        {
            return invocation.Request.Method.Name;
        }
    }
}

This class only overrides a couple of callbacks from its base class the SimpleFailureInterceptor. This is where the actual interception takes place.

using System;
using Ninject.Core;
using Ninject.Core.Interception;

namespace LoggingDemo.UI.Interceptors
{
    public abstract class SimpleFailureInterceptor : IInterceptor
    {

        #region IInterceptor Members

        public virtual void Intercept(IInvocation invocation)
        {
            try
            {
                BeforeInvoke(invocation);
                invocation.Proceed();
            }
            catch (Exception ex)
            {
                OnError(invocation, ex);
            }
            finally
            {
                AfterInvoke(invocation);
            }
        }

        #endregion

         protected virtual void BeforeInvoke(IInvocation invocation)
        {
        }

        protected virtual void AfterInvoke(IInvocation invocation)
        {
        }

        protected virtual void OnError(IInvocation invocation, Exception exception)
        {
            throw exception;
        }
    }
}

This is al the work that is involved in the actual implementation of our logger. Now I’d like to get some confirmation that things actually do work.  Unit testing to the rescue I’d say ;)  

The first thing we’re going to need is way to verify that stuff actually got intercepted. I did that by subclassing the LoggingInterceptor with a LoggingCounterInterceptor in my unit test project.

    public class LoggingCounterInterceptor : LoggingInterceptor
    {
        public int Count { get; private set; }

        public int ErrorCount { get; private set; }

        public void Reset()
        {
            Count = ErrorCount = 0;
        }

        public LoggingCounterInterceptor(ILogger logger) : base(logger)
        {
        }

        protected override void BeforeInvoke(Ninject.Core.Interception.IInvocation invocation)
        {
            Count++;
            base.BeforeInvoke(invocation);
        }

        protected override void OnError(Ninject.Core.Interception.IInvocation invocation, System.Exception exception)
        {
            ErrorCount++;
            base.OnError(invocation, exception);
        }
    }

    //The attribute for testing
    public class LogMyCallsCounterAttribute : InterceptAttribute
    {
        public LogMyCallsCounterAttribute() : base(typeof(LoggingCounterInterceptor))
        {
        }
    }

In the code above we’re just adding 2 counter properties to the interceptor and adding their counts at the appropriate time. Next we’re going to need some kind of service class or something on which we can use our interceptor, enter the InterceptedServiceMock.

    public interface IInterceptedServiceMock
    {
        void MethodWithoutBody();
        void MethodThatThrowsAnException();
    }

    [LogMyCallsCounter]
    public class InterceptedServiceMock : IInterceptedServiceMock
    {
        public virtual void MethodWithoutBody()
        {
            // Nothing to do here
        }

        public virtual void MethodThatThrowsAnException()
        {
            throw new Exception("Because I can.");
        }
    }

The code above has one method that should execute and one method that throws an exception so we can verify things get picked up accordingly. Now all that’s left to do is write the appropriate specs for them and see if they pass :)

    [Context(Description = "Specifies the behavior for the LogMyCallsInterceptor")]
    public class LogMyCallsInterceptorSpec
    {
        private IKernel _kernel;

        [BeforeEach]
        public void Before()
        {
            var inlineModule = new InlineModule(m => m.Bind<IInterceptedServiceMock>().To<InterceptedServiceMock>());

            _kernel = new StandardKernel(new LinFuModule(), new NLogModule(), inlineModule);
        }

        [AfterEach]
        public void After()
        {
            _kernel.Dispose();
        }

        [Specification("All this should do is show the calls in the test runner. It should log to the console")]
        public void ShouldShowCallsInConsole()
        {
            var service = _kernel.Get<IInterceptedServiceMock>();
            IContext context = new StandardContext(_kernel, typeof(IInterceptedServiceMock));

            IRequest request = new StandardRequest(
                context,
                service,
                typeof(InterceptedServiceMock).GetMethod("MethodWithoutBody"),
                new object[0]
            );

            var interceptors = _kernel.GetComponent<IInterceptorRegistry>().GetInterceptors(request);

            var enumerator = interceptors.GetEnumerator();
            enumerator.MoveNext();

            Specify.That(interceptors.Count).Must.Equal(1, "There should be 1 interceptor registered");
            Specify.That(enumerator.Current).Must.Be.InstanceOf(typeof(LoggingCounterInterceptor));

            var interceptor = enumerator.Current as LoggingCounterInterceptor;

            service.MethodWithoutBody();

            Specify.That(interceptor).Must.Not.Be.Null();
            Specify.That(interceptor.Count).Must.Equal(1, "There should be 1 invocation counted.");
        }

        [Specification("Should show valid counts for a number of invocations")]
        public void Should_Show_Correct_Counts_For_Number_Of_Invocations()
        {
            var service = _kernel.Get<IInterceptedServiceMock>();
            IContext context = new StandardContext(_kernel, typeof(IInterceptedServiceMock));

            IRequest request = new StandardRequest(
                context,
                service,
                typeof(InterceptedServiceMock).GetMethod("MethodWithoutBody"),
                new object[0]
            );

            var interceptors = _kernel.GetComponent<IInterceptorRegistry>().GetInterceptors(request);

            var enumerator = interceptors.GetEnumerator();
            enumerator.MoveNext();

            Specify.That(interceptors.Count).Must.Equal(1, "There should be 1 interceptor registered");
            Specify.That(enumerator.Current).Must.Be.InstanceOf(typeof(LoggingCounterInterceptor));

            var interceptor = enumerator.Current as LoggingCounterInterceptor;

            service.MethodWithoutBody();
            service.MethodWithoutBody();
            service.MethodWithoutBody();

            Specify.That(interceptor).Must.Not.Be.Null();
            Specify.That(interceptor.Count).Must.Equal(3, "There should be 3 invocations counted.");
            Specify.That(interceptor.ErrorCount).Must.Equal(0, "There should be no errors counted.");
        }

        [Specification("Should have the correct count of invocations and the correct error count.")]
        public void Should_Have_Correct_Invocation_And_Error_Count()
        {
            var service = _kernel.Get<IInterceptedServiceMock>();
            var context = new StandardContext(_kernel, typeof(IInterceptedServiceMock));

            var request = new StandardRequest(
                context,
                service,
                typeof(InterceptedServiceMock).GetMethod("MethodWithoutBody"),
                new object[0]
            );

            var errorRequest = new StandardRequest(
                context,
                service,
                typeof (InterceptedServiceMock).GetMethod("MethodThatThrowsAnException"),
                new object[0]
            );

            var interceptors = _kernel.GetComponent<IInterceptorRegistry>().GetInterceptors(request);
            var errorInterceptors = _kernel.GetComponent<IInterceptorRegistry>().GetInterceptors(errorRequest);

            var enumerator = interceptors.GetEnumerator();
            enumerator.MoveNext();
            var errorEnumerator = errorInterceptors.GetEnumerator();
            errorEnumerator.MoveNext();

            Specify.That(interceptors.Count).Must.Equal(1, "There should be 1 interceptor registered");
            Specify.That(enumerator.Current).Must.Be.InstanceOf(typeof(LoggingCounterInterceptor));
            Specify.That(errorInterceptors.Count).Must.Equal(1, "There should be 1 error interceptor registered");
            Specify.That(errorEnumerator.Current).Must.Be.InstanceOf(typeof(LoggingCounterInterceptor));

            var interceptor = enumerator.Current as LoggingCounterInterceptor;
            var errorInterceptor = errorEnumerator.Current as LoggingCounterInterceptor;

            service.MethodWithoutBody();
            service.MethodWithoutBody();
            service.MethodWithoutBody();

            try
            {
                service.MethodThatThrowsAnException();
            }
            catch
            {
            }

            Specify.That(interceptor).Must.Not.Be.Null();
            Specify.That(interceptor.Count).Must.Equal(3, "There should be 3 invocations counted.");
            Specify.That(errorInterceptor.Count).Must.Equal(1, "There should be 1 invocation counted.");
            Specify.That(errorInterceptor.ErrorCount).Must.Equal(1, "There should be 1 error counted.");
        }
    }

Testing this got a little messy I have to admit that. Now for a concrete example of how that looks in my application:

    [Service(typeof(IBlogService))]
    [LogMyCalls]
    public class BlogService : DataServiceBase<Blog>, IBlogService

And that’s how I implemented logging for my application :) Needless to say it was the testing that took me the longest to write. And from now on I don’t have to worry anymore about forgetting that logger call. I always have minimal logging going. AOP is usefull in a lot more cases but this seemed like a good and easy example.

Our next post will deal with initializing the modules in your application and their respective behaviors.

kick it on DotNetKicks.com

17AprNinject: Getting all the stuff you’ll need

I have a new project I started and I could reevaluate my toolset :) I decided to take a closer look at Ninject, Moq and NSpecifyMoq has been talked about enough I think, a really nice way of mocking and I will definitely be using more of it. My original plan was to write one big blog post.. but it is going to be far too long, so I’m breaking it up in smaller bite size pieces.

What I did was download the code, have it rest on my pc for about a week, updated again when I really got round to using it. I read the wiki which gives a short introduction on what’s what in Ninject and everybodies jobs. Decided that the wiki was cool for some more theoretical knowledge but I really needed to see more code.

I opened the project and something rare happened: I was actually very happy with the code I found, not always the case I can tell you ;) .  Anyway the gold is in the unit tests, it’s got great test coverage and it shows lots of the possibilities of Ninject. It’s like a great big manual for you to get really advanced with it really quickly.  I was slightly disappointed to see that there was no NLog support for Ninject, that disappointment quickly turned in to joy when I figured out it only took me 20 minutes to plug NLog in. And it was added to the trunk the same day.

Now I don’t want to make this post about which DI framework is better Windsor, Spring, Structuremap or Ninject. I just know that Ninject and I will become good friends over the next couple of months I like it. Ninject outsources its proxy generation to either Castle’s DynamicProxy2 or to LinFu DynamicProxy.  LinFu seemed like an interesting choice after reading this codeproject article. It’s supposedly faster than castle’s dynamic proxy. Luckily Ayende is there to put this in perspective. And I can confirm that the error messages etc castle’s dynamic proxy generates are a lot more useful than LinFu.

Screenshot - LinFuGraph.png

Anyway I’m using LinFu at the moment as the proxy generator for Ninject. I will walk you through a sample application I built that will deal with the following subjects of Ninject, Lightspeed and NLog. For Ninject we will use the dependency injection and it’s interceptor possibilities to implement AOP style logging. For Lightspeed we will create our own logger to plug into the context so that we can log what Lightspeed does too. And we want NLog to log through Lightspeed to our database so we’ll be creating a custom target for NLog as well.

But let’s start at the beginning doing DI with Ninject :) .

The most important thing to remember: MAKE YOUR METHODS VIRTUAL. Once again make your methods virtual otherwise Ninject has a hard time generating proxies for your classes.

Ninject is different from most other DI Containers in that it doesn’t have XML-configuration, its configuration is done through code (wouldn’t be hard to add an XML configuration for it tho but what’s the point). No XML ?? Suits me fine, everybody that ever worked with me will agree : Ivan no like XML editing.  Ninject takes a modular approach by using modules that you pass to a kernel. This is pretty nice that means that an assembly can have a couple of different sets of Modules and all the caller of that assembly needs to do is add the module with the appropriate configuration to their kernel.

Ninject supports DI on constructors, properties, methods and fields: http://dojo.ninject.org/wiki/display/NINJECT/Injection+Patterns.
It basically boils down to decorating the item you want to inject with the attribute [Inject]

Ninject allows you to do a lot of configuration through the use of attributes. But I think I’ll leave it at that for today and continue this tomorrow.
Let’s look at a concrete class from what we’ve seen so far. The class below is the Logger implementation to plug into the LightSpeedContext. We will finish this later on but for now this will be enough to summarize today’s post.

using Ninject.Core;
using Ninject.Core.Logging;

using ILightSpeedLogger = Mindscape.LightSpeed.Logging.ILogger;
using INinjectLogger = Ninject.Core.Logging.ILogger;

namespace LoggingDemo.UI.Integration
{
///
/// This class intercepts logging messages from the LightSpeed context and
/// sends them to our NLog logger.
///
public class LightSpeedLogger : ILightSpeedLogger
{
private readonly INinjectLogger logger = NullLogger.Instance;

public LightSpeedLogger()
{
}

[Inject]
public LightSpeedLogger(INinjectLogger logger)
{
this.logger = logger;
}

#region ILogger Members

public virtual void LogSql(object sql)
{
logger.Info(sql.ToString());
}

public virtual void LogDebug(object text)
{
logger.Debug(text.ToString());
}

#endregion
}
}

Let me know what you think :)

kick it on DotNetKicks.com

09AprUpdated my lightspeed model generator

This week I could start a project that has a database that goes somewhat beyond a blog. This seemed to me like the best way to improve my model generator that I built earlier

And as expected I needed to tweak it a little so that it would work properly, and I added a feature.

Seen as there are 2 files generated for an entity if you add a private field that exists in the database there then the generator will skip it so that you can add more attributes than the ones this script generates for you. If you override a field you also have to put the property in the user file.

I also added support for generating a datacontext. If you want to use the Linq support that Lightspeed is getting then that is a starting point. The same goes for this class with user files and generated file as above, the user file takes precedence.

I hosted this project on google code so you can check it out from there. Or you can download todays version

del.icio.us Tags: ,,

Technorati Tags: ,,

kick it on DotNetKicks.com

09FebUsing Ruby to Generate LightSpeed Models – Part 4

UPDATE: I have an update here with a new version and the location on google code where the project is hosted now.

 

In the previous 3 parts (part 1, part 2, part 3) we talked about getting the meta data out of the database and generating the lightspeed entities in memory.

I won’t put the code listings up in this blog post but instead will give you a link to the complete code :) You can download the express edition of LightSpeed and try it for yourself if you want.

Instead I’ll talk about how you can use the ruby script to generate the models from an existing database.

It adds the generated files to the visual studio project file in the same directory or you can specify one.

Currently it only supports Sql Server 2005. You need to create a project first in visual studio so we have

a project file to add items to.

To use it you have to tell it which database it has to find by putting a database.yml file in your visual studio project directory.

The content of that database.yml file may look as follows

# on windows you can use the following connection string

#

# sqlserver:

#   database: northwind

#   host: localhost

#   username: sa

#   password: MaybePassword123

# on OSX and Linux you can connect to sql server through odbc (iODBC or unixODBC in combination with FreeTDS)

#

# sqlserver:

#   dsn: NorthwindSql

#   username: sa

#   password: MaybePassword123

when you’ve done that you can execute it like shown in the screenshot below:

Terminal-screenshot

I have a database.yml file in the directory and I execute the command ruby < >

on linux and OSX you can chmod +x that path and then you don’t have to type ruby anymore.

That will generate the models for your database provided that your database conforms to the conventions needed for LightSpeed.

The next step would be to open visuals studio and build the project. The files got added in visual studio every entity are 2 files one with the generated code and one empty one that you can use to implement behavior on the model if you would want to.

The first picture shows how the solution explorer looks and the second one shows the results of the build without having touched one file after generation.

In the download there is a folder db which contains a slightly modified script from the Northwind database that comes with the samples for lightspeed. I used that database because it has every type of relationship and the script did conform to the lightspeed conventions :)

LightSpeedGenerateTest

LightSpeedGenerateTestBuildResult

del.icio.us Tags: ,,

Technorati Tags: ,,

kick it on DotNetKicks.com

05FebUsing Ruby to Generate LightSpeed Models – Part 3

First off I’m writing with windows live writer again, ecto wasn’t up to the job. It tried to “clean” my html, granted it was messy but it should leave my text untouched. The whole editing experience wasn’t satisfying enough. And Ecto already seemed like the best blog editor for mac, slim pickings indeed. From my tools I expect foremost that they stay out of my way and it didn’t. I just talked with Simone about looking at making a .NET based client that runs on mono, we’ll see where that plan goes because I don’t really have time to do that for the moment.

In the previous posts in this series (part 1, part 2) we discovered how to connect to the database and how to get the meta data about that database out. Maybe I should also explain why I’m doing this series with LightSpeed instead of ActiveRecord from Castle or SubSonic or Linq2Sql for that matter. I will definitely touch on all those orms in the coming week, but I started with LightSpeed because it’s the easiest ORM I’ve ever used.

This post will deal with actually doing something useful with that meta data. Today we’re going to generate the represenation of the entities and their properties. Tomorrow we’ll deal with actually generating the files from the the in-memory presentation we’re generating today.

We’re going to need 2 classes in addition to the LightSpeedRepository class. One to represent an entity and one to represent a property. The goal is for tomorrow to render the entities as complete as possible with validation attributes etc.

And without further ado here are the specs we’re going to build:

LightSpeedRepository Conversion
- should convert a given table to light speed metadata
- should convert a given table without relations to a light speed entity definition
- should convert a given table with a m:1 relation to a light speed entity definition
- should convert a given table with a 1:m relation to a light speed entity definition
- should convert a given table with a m:n relation to a light speed entity definition

LightSpeedProperty
- should allow for a property to be set
- should return a predicate for booleans
- should return a predicate for booleans
- should return a sql type
- should be a lightSpeed property

LightSpeedEntity
- should have properties, has many, belongs to and through associations
- should create a valid property name if one doesn’t exists already in the through association properties
- should create a valid property name if one doesn’t exists already in the has many properties
- should create a valid property name if one doesn’t exists already in the belongs to properties
- should create a valid property name if one doesn’t exists already in the properties
- should create a valid property name if one already exists in the through association properties
- should create a valid property name if one already exists in the has many properties
- should create a valid property name if one already exists in the belongs to properties
- should create a valid property name if one already exists in the properties
- should create a valid property name if two already exist in the through association properties
- should create a valid property name if two already exist in the has many properties
- should create a valid property name if two already exist in the belongs to properties
- should create a valid property name if two already exist in the properties

Let’s start with looking at the LightSpeedProperty first. The attributes on this class are implemented using some simple metaprogramming. This class will represent a field in a LightSpeed entity and will take care of rendering that properly into the c# file. We actually create the data in the LightSpeedRepository class.

class LightSpeedProperty

  attr_accessor :attributes

  def initialize(params = {})
    @attributes = params
    LightSpeedProperty.create_methods params

  end

  def [](attribute)
    attributes[attribute]
  end

  def self.create_methods(params)

    params.each do |k, v|
      define_method("#{k}=") do |val|
        @attributes[k]= val
      end

      predicate = %w(primary_key foreign_key unique nullable).any? { |o| o === k.to_s }

      define_method(predicate ? "#{k}?" : "#{k}") do
        @attributes[k]
      end

    end
  end

end

In the LightSpeed entity class we describe the actual Entity. I monkey patched Array so that I could ask it the question if it has a particular property. To avoid naming conflicts we check for properties that exist already and otherwise give them a generic new name by appending a number.

class Array

  def has_property?(name)
    exists = false

    each do |hm|
      exists = hm[:name] == name
      break if exists
    end

    exists
  end
end

class LightSpeedEntity
  attr_accessor :properties, :belongs_to, :has_many, :through_associations, :name, :namespace

  def initialize
    @properties = []
    @belongs_to = []
    @has_many = []
    @through_associations =[]
  end

  def create_property_name_from(from, idx=0)
    tname = build_property_name_from from, idx
    idx += 1 #when the property exists try with a higher number
    return create_property_name_from(from, idx) if has_property?(tname)
    tname
  end

  private

    def has_property?(tname)
      properties.has_property? tname or has_many.has_property? tname or belongs_to.has_property? tname or through_associations.has_property? tname
    end

    def build_property_name_from(from, idx)
      if idx == 0
        from
      else
        "#{from}#{idx}"
      end
    end

end

And this brings us to our last class of today the Repository class. We mixin the DB::MetaData module we created yesterday. Define a read_only property entities, make sure we can set a namespace for our generated entities. The first step is to transform the meta data into data that we can use to represent a LightSpeed Entity. The second and last step of today is to generate the entities with the lightspeed meta data. We have to skip the primary key because that is defined by convention in LightSpeed.

class LightSpeedRepository

  include DB::MetaData

  attr_reader :entities
  attr_accessor :namespace

  def initialize()
    @entities = []
    super
  end

  def to_light_speed_meta_data
    tables.collect do |table|
      col_infos = column_info_for table[:name]

      field_infos = col_infos.collect do |col_info|
        {
          :name => col_info[:name].underscore,
          :sql_type => col_info[:sql_type],
          :max_length => col_info[:max_length].to_i,
          :nullable => !col_info[:is_nullable].to_i.zero?,
          :precision => col_info[:precision],
          :foreign_key => foreign_key?(col_info),
          :primary_key => primary_key?(col_info),
          :unique => !col_info[:is_unique].to_i.zero?
        }
      end

      { :table_name => table[:name], :class_name => table[:name].singularize.camelize, :fields => field_infos }
    end
  end

  def generate_entities
    meta_data = to_light_speed_meta_data
    meta_data.each do |md|
      @entities << generate_entity(md)
    end
    @entities
  end

  def generate_entity(meta_data)
    entity = LightSpeedEntity.new
    entity.name = meta_data[:class_name]
    entity.namespace = namespace

    meta_data[:fields].each do |fi|
      prop = LightSpeedProperty.new(fi)

      prop.name = entity.create_property_name_from prop.name.underscore.camelize
      entity.properties << prop unless prop.primary_key?
      entity.belongs_to << generate_belongs_to_relation(meta_data, fi, entity) if prop.foreign_key?

    end

    entity.has_many = generate_has_many_relations meta_data, entity
    generate_through_associations meta_data, entity

    entity
  end

  private

    def generate_belongs_to_relation(meta_data, field_info, entity)
      {
        :name => entity.create_property_name_from(field_info[:name].underscore.humanize.titleize.gsub(/\s/,'')),
        :class_name => get_belongs_to_table(meta_data[:table_name], field_info[:name]).underscore.camelize.singularize
      }
    end

    def generate_has_many_relations(meta_data, entity)
      hms = collect_has_many_relations meta_data[:table_name]
      hms.collect do |hm|
         hm[:name] = entity.create_property_name_from hm[:class_name].pluralize
         hm
      end

    end

    def generate_through_associations(meta_data, entity)
      tas = collect_through_associations(meta_data[:table_name])
      tas.each do |ta|
        ta[:end_tables].each do |et|
          entity.through_associations << {
            :through => ta[:through_table].classify.singularize,
            :class_name => et.camelize.singularize,
            :name => entity.create_property_name_from(et.camelize)
          }
        end
      end
    end
end

Technorati Tags: ,,

del.icio.us Tags: ,,

05FebUsing Ruby to Generate LightSpeed models – Part 2

This is the second post in the series on generating LightSpeed entities with the help from ruby.
In the previous post we connected successfully to the database and were able to execute some sql.

At the end of the series I’ll make the code downloadable.

Today I’d like to talk about the metadata we’ll be needing from the database. We’re going to need a list of tables, we’re going to need to know about the columns of each table. Furthermore we want to exclude the primary keys in the case of LightSpeed. And we also want to know about relationships whether they are has many, belongs to or has many and belongs to many.

I put all this in a separate module because I’ll probably need that meta data for another thing later :) . The above requirements translate in the following spec:

LightSpeedRepository DB::MetaData
- should have meta data
- should resolve the table name from a string
- should resolve the table name from a hash
- should identify a given column as not being a foreign key
- should identify a foreign key given a valid column info
- should not identify a given column as being a primary key
- should identify a given column as being a primary key
- should not identify a table as a join table under the correct conditions
- should identify a table as a join table under the correct conditions
- should return an empty array of has many relations when there are none
- should return the has many relations given a table
- should return the end point tables for a given through association
- should return the through associations

The first thing we’re going to need are the sql statements. At this point I only need the statements for sql 2005 so and these are the ones I used.

def self.sql_statements
      {
        :tables => "SELECT table_name as name FROM information_schema.Tables Where table_type='Base Table' ORDER BY table_name",
        :column_info => "select object_name(c.object_id) as table_name, c.column_id, c.name, type_name(system_type_id) as sql_type, max_length, is_nullable, precision, scale,
              convert(bit,(Select COUNT(*) from sys.indexes as i
                inner join sys.index_columns as ic
                  on ic.index_id = i.index_id and ic.object_id = i.object_id
                inner join sys.columns as c2 on ic.column_id = c2.column_id and i.object_id = c2.object_id
              WHERE i.is_primary_key = 0
                and i.is_unique_constraint = 0 and ic.column_id = c.column_id and i.object_id=c.object_id)) as is_index,
              is_identity,
              is_computed,
              convert(bit,(Select Count(*) from sys.indexes as i inner join sys.index_columns as ic
                  on ic.index_id = i.index_id and ic.object_id = i.object_id
                inner join sys.columns as c2 on ic.column_id = c2.column_id and i.object_id = c2.object_id
              WHERE (i.is_unique_constraint = 1) and ic.column_id = c.column_id and i.object_id=c.object_id)) as is_unique
              from sys.columns as c
              WHERE object_name(c.object_id)  in (select table_name	FROM information_schema.Tables WHERE table_type = 'Base Table')
              order by table_name",
        :primary_keys => "SELECT i.name AS index_name,ic.index_column_id,key_ordinal,c.name AS column_name,TYPE_NAME(c.user_type_id)AS column_type
                    ,is_identity,OBJECT_NAME(i.object_id) as table_name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON
                    i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id
                    AND c.column_id = ic.column_id WHERE i.is_primary_key = 1 order by table_name",
        :foreign_keys => "SELECT f.name AS foreign_key_name, object_name(f.parent_object_id) AS table_name , col_name(fc.parent_object_id, fc.parent_column_id) AS child_id
                    ,object_name (f.referenced_object_id) AS parent_table ,col_name(fc.referenced_object_id, fc.referenced_column_id) AS parent_id FROM sys.foreign_keys AS f
                    INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id where OBJECT_NAME(f.parent_object_id) not in ('sysdiagrams')  order by table_name"
       }
     end

Those statements contain all the data we need an probably a little bit more too, if we add a little metaprogramming we can have ruby generate that metadata data for us :)

    def populate
      DB::MetaData.sql_statements.each do |key, value|
        instance_variable_set("@"+key.to_s, @db.fetch_all(value))
      end
    end

    sql_statements.each_key do |key|
       define_method("#{key}_for") do |table|
         send(key, table).select { |item| item[:table_name] == table_name(table) }
       end unless key == :tables
     end

So now we’ve satisfied our first spec the module now contains all the meta data we need. The rest of the specs require far less code than what we wrote here. Below you’ll find the code needed to satisfy all of the specs. It are just a couple of methods that check some conditions and a couple of predicates we’re going to need later on. The get_endpoint_tables method is the only one that doesn’t explain itself easily. That method returns the table names from tables that are the second level in a has many and belongs to many scenario.

module DB
  module MetaData
    attr_accessor :tables, :primary_keys, :foreign_keys, :column_info

    def initialize
      @db = DB::DbiSqlServer.new
      populate
    end

    def populate
      DB::MetaData.sql_statements.each do |key, value|
        instance_variable_set("@"+key.to_s, @db.fetch_all(value))
      end
    end

    def collect_has_many_relations(table)
      fks = foreign_keys.select { |fk| fk[:parent_table] ==  table_name(table)  }

      fks.collect  do |fk|
        unless fk[:table_name].nil?
          { :table_name => fk[:table_name].underscore, :class_name => fk[:table_name].singularize.underscore.camelize }
        end
       end.compact
    end

    def collect_through_associations(table)
      fks = foreign_keys.select { |fk| fk[:parent_table] ==  table_name(table)  }

      fks.collect do |fk|
          { :through_table => fk[:table_name].underscore, :end_tables => get_endpoint_tables(table, fk[:table_name]) } if join_table?(fk[:table_name])
      end.compact
    end

    def get_endpoint_tables(table, through_table)
      fks = foreign_keys.select { |fk| fk[:table_name] == table_name(through_table) and fk[:parent_table] != table_name(table)  }
      fks.collect { |fk| fk[:parent_table].underscore unless fk[:parent_table].nil?  }.compact
    end

    def get_belongs_to_table(table, column_name)
      fks = foreign_keys.select { |fk|  fk[:table_name] == table_name(table) and fk[:child_id] = column_name }
      return fks[0][:parent_table] if fks.size > 0
      nil
    end

    def join_table?(table)
      fks = foreign_keys_for table_name(table)
      fks.size > 1
    end

    def primary_key?(column_info)
      pks = primary_keys.select { |pk| pk[:table_name] == column_info[:table_name] and pk[:column_name] == column_info[:name]   }

      pks.size > 0
    end

    def foreign_key?(column_info)
      fks = foreign_keys.select { |fk| fk[:table_name] == column_info[:table_name] and fk[:child_id] == column_info[:name]  }
      fks.size > 0
    end

    def table_name(table)
      table.is_a?(Hash) ? table[:name] : table
    end

    def self.sql_statements
      {
        :tables => "SELECT table_name as name FROM information_schema.Tables Where table_type='Base Table' ORDER BY table_name",
        :column_info => "select object_name(c.object_id) as table_name, c.column_id, c.name, type_name(system_type_id) as sql_type, max_length, is_nullable, precision, scale,
              convert(bit,(Select COUNT(*) from sys.indexes as i
                inner join sys.index_columns as ic
                  on ic.index_id = i.index_id and ic.object_id = i.object_id
                inner join sys.columns as c2 on ic.column_id = c2.column_id and i.object_id = c2.object_id
              WHERE i.is_primary_key = 0
                and i.is_unique_constraint = 0 and ic.column_id = c.column_id and i.object_id=c.object_id)) as is_index,
              is_identity,
              is_computed,
              convert(bit,(Select Count(*) from sys.indexes as i inner join sys.index_columns as ic
                  on ic.index_id = i.index_id and ic.object_id = i.object_id
                inner join sys.columns as c2 on ic.column_id = c2.column_id and i.object_id = c2.object_id
              WHERE (i.is_unique_constraint = 1) and ic.column_id = c.column_id and i.object_id=c.object_id)) as is_unique
              from sys.columns as c
              WHERE object_name(c.object_id)  in (select table_name	FROM information_schema.Tables WHERE table_type = 'Base Table')
              order by table_name",
        :primary_keys => "SELECT i.name AS index_name,ic.index_column_id,key_ordinal,c.name AS column_name,TYPE_NAME(c.user_type_id)AS column_type
                    ,is_identity,OBJECT_NAME(i.object_id) as table_name FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON
                    i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id
                    AND c.column_id = ic.column_id WHERE i.is_primary_key = 1 order by table_name",
        :foreign_keys => "SELECT f.name AS foreign_key_name, object_name(f.parent_object_id) AS table_name , col_name(fc.parent_object_id, fc.parent_column_id) AS child_id
                    ,object_name (f.referenced_object_id) AS parent_table ,col_name(fc.referenced_object_id, fc.referenced_column_id) AS parent_id FROM sys.foreign_keys AS f
                    INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id where OBJECT_NAME(f.parent_object_id) not in ('sysdiagrams')  order by table_name"
       }
     end

     sql_statements.each_key do |key|
       define_method("#{key}_for") do |table|
         send(key, table).select { |item| item[:table_name] == table_name(table) }
       end unless key == :tables
     end

  end

end

04FebUsing Ruby to Generate LightSpeed Models – Part 1

This is the first in a multi-part post on a little ruby application I wrote to generate models for LightSpeed.

The ultimate goal is to consume the entities we generate in this series with IronRuby and perform some data access.

Today I’ll post the code I wrote for creating the database connection. At this moment there is only code there to connect tho sql server. But I may want to add providers later if I decide to keep using this code. That’s why some bits are in a separate module.

This are the specs I wrote for the connection manager. The connection manager is the class that reads the database config, gets a connection and executes sql statements. I think this code is pretty simple so I won’t put a line-by-line explanation.

It uses DBI to connect to the database and reads out the results of the executed sql statement. In the next post I’ll talk about getting the metadata that is required from sql server.

DB::DbiSqlServer
- should return a connection
- should say it’s an ODBC connection when a dsn is provided
- should return the correct connection string for an ODBC connection


module DB

	module SqlConnectionManager

		DEFAULT_CONFIG_PATH = File.dirname(__FILE__) + '/../config/database.yml'

		attr_reader :connection_string, :connection

		def initialize(config=DEFAULT_CONFIG_PATH)
		  if config.is_a? Hash
		    initialize_config config
		  else
		    read_config config
	    end
		end

		def read_config(config_path, config_name = 'sqlserver')
			initialize_config(YAML::load(File.open(config_path || DEFAULT_CONFIG_PATH))[config_name])
		end

		def initialize_config(config)
		  @config = config
			@connection=nil
	  end

		def odbc?
		  return true unless @config.nil? || @config['dsn'].nil?
		  false
		end

	end

	class DbiSqlServer
		include SqlConnectionManager

		def connection
			if @connection.nil?
			  @connection = DBI.connect(connection_string, @config['username'], @config['password'])
			end
			@connection
		end

		def connection_string
		  if odbc?
		    "DBI:ODBC:#{@config['dsn']}"
		  else
		    "DBI:ADO:Provider=SQLOLEDB;Data Source=#{@config['host']};Initial Catalog=#{@config['database']};User ID=#{@config['username']};Password=#{@config['password']};"
	    end
	  end

		def fetch_all(sql_statement)
			result = []
			connection.execute sql_statement do |statement|
				while row = statement.fetch do
					r = {}
					row.each_with_name do |val, name|
						r[name.to_sym] = val
					end
					result << r
				end
			end
			result
		end

		def execute_non_query(sql_statement)
		  connection.do sql_statement
		end

	end

end

Recent Flickrs

    Blogroll

    Recent Listening

    Scrobbler