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:

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

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

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