Connecting to SQL Server Express from Ruby

Something I’ve been interested in is creating a dynamic data viewer for SQL Server in Ruby on Rails.  Here’s the steps I had to take to establishing a connection with SQL Server.  I’m using the AdventureWorks2008 database as the test database, and I should also point out that I’m running Ruby in Windows 7.

SQL Server Configuration

Some initial configuration of SQL Server Express is necessary, depending on how you configured SQL Server Express in your initial installation options.

Enable Mixed Mode Authentication

From SQL Server Management Studio, sign in with Windows Authentication and then right-click on the SQLEXPRESS top-level node, then select properties.  Click on Security    and Enable Mixed Mode Authentication:

Enable Remote Connections

Click on the “Connections” page and check “Allow remote connections to this server”:

Create a User Account

Create a user account under Security -> Logins.  I named my account “ruby” with the password “rubyist”.  On connecting the first with SQL Server Authentication, you will be prompted to change your password, so I changed mine to “rubyist1”.

Set the Server Roles

On the same dialog box, click on the Server Roles page and enable the desired roles.

Associate the User with the Desired Databases

On the same page, click on the Server Roles page and map the user to the desired databases.  For example:

Enable Named Pipes and TCP/IP Protocols

Then, in the Sql Server Configuration Manager, enable the Named Pipes and TCP/IP protocols:

Reboot or Restart the Services

After making all these changes, I found it simpler to just reboot my machine.

Test Your Connection in SQL Server Management Studio

After rebooting / restarting the services, open SQL Server Management Studio and try logging in with Sql Server Authentication:

If all the configuration changes went well, you should see the object explorer.  If you continue to have problems, check out this excellent article on dealing with connection issues, which includes steps that I have omitted, such as configuring the firewall, as I’m testing everything locally.

Configure Ruby

Install the tiny_tds gem by opening a command line and typing in:

install gem tiny_tds

Test It!

I use RubyMine for Ruby / Rails development, so we’ll run a few things from the Interactive Ruby Console directly in the RubyMine IDE:

Pay particular attention to the client connection and the dataserver value.

For more information on how tiny_tds works and the options that it supports, refer to the tiny_tds GitHub page.

Why is parent_object_id Actually the Child Object in a Foreign Key Relationship ?

Let’s look at this query, which lists the columns involved in the foreign key relationship of the child table SpecialOfferProduct and the parent table, SpecialOffer (from the AdventureWorks database):

SELECT 
    f.parent_object_id, 
    SCHEMA_NAME(f.schema_id) SchemaName, 
    OBJECT_NAME(f.parent_object_id) TableName, 
    COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName, 
    SCHEMA_NAME(f.schema_id) ReferencedSchemaName, 
    OBJECT_NAME(f.referenced_object_id) ReferencedTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName 
FROM sys.foreign_keys AS f 
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
    INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id 
WHERE 
    OBJECT_NAME (f.parent_object_id) = 'SpecialOfferProduct'

Why are we qualifying the child table name by the parent object?

The reason is in the definition of parent_object_id in sys.objects:

ID of the object to which this object belongs.
0 = Not a child object.

(see TechNet)

meaning that parent_object_id does not reference the parent in the foreign key relationship, it references the owner of the foreign key definition, which is the child table in the relationship.

The moral of the story is, look at the documentation to understand the purpose of the field rather than make assumptions because it uses terminology that just so happens to coincide with a completely different concept.

Obtaining Foreign Key Table-Column References in SQL Server

Borrowing from what others have put together, this query give you the details of foreign keys:

• The referencing schema, table, and column names
• The referenced schema, table, and column names

SELECT 
        f.parent_object_id, 
        SCHEMA_NAME(f.schema_id) SchemaName,
        OBJECT_NAME(f.parent_object_id) TableName,
        COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
        SCHEMA_NAME(ref.schema_id) ReferencedSchemaName,
        OBJECT_NAME(f.referenced_object_id) ReferencedTableName,
        COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName
        FROM sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
        INNER JOIN sys.tables ref ON ref.object_id = f.referenced_object_id
        WHERE OBJECT_NAME (f.referenced_object_id) = 'Employee'

This example queries the foreign key relationships of the table “Employee” in the AdventureWorks sample database that Microsoft provides.  Here’s the results of the query:

This is an important part of the dynamic data explorer that I’m writing as an application example in my upcoming book Imperative to Function Programming for SyncFusion‘s “Succinctly” e-book series.

Microsoft, SQL Server 2012 Express, and Failure

I’m doing a bit of multitasking today, mainly fixing a bunch of Ruby on Rails features for my client while doing some setup stuff on my workstation, like installing the latest version of SQL Server Express.

So I go this download page:

http://www.microsoft.com/en-us/download/details.aspx?id=29062

First thing I notice is how poorly this page correlates the download options with the filenames.   Here’s a screenshot of the download page:

And here’s the descriptions (which of course requires that you click on “Details” to even see.)

Notice that there is nothing that says “this installer is this file”.  You have to figure out that “With Tools” is the “WT” version, etc.

Finally, after 40 minutes of waiting for a 600 MB download (why are things so slow on your systems, Microsoft?) I get this message:

And yet:

It says right there that Windows 7 is supported!!!

So Microsoft, four failures for what should be one simple task.  Nice batting average.

Path to devkit\bin got removed somehow

I went to install the latest json gem this morning and got “make is not recognized as an internal or external command, operable program or batch file.”

Poking around my path, I discovered that c:\railsinstaller\devkit\bin was no longer there!  I have no idea what removed it — the last thing I did on this machine was install the latest version of SmartGit/Hg (which is another story.)

Anyways, adding the path back in fixed the gem install issue.

Function Composition, Function Chaining, Currying, and Partial Functions / Application in F# and Ruby

A while ago I wrote an article comparing / contrasting language elements in Ruby with C# with regards to classes: constructors, destructors, fields, properties, initializers, events, methods, etc.  Ruby however also has a foot in the functional language paradigm, though it requires a certain discipline to ensure that you’re adhering to qualities of a functional programming language such as statelessness and immutability.  However, for purposes of this article, what I am more interested in is exploring how Ruby handles certain concepts that seem to be core to most functional programming languages:

• function composition
• function pipelining (chaining)
• continuation functions
• currying
• partial functions
• partial application

These concepts are all intertwined with each other, and as we’ll see, you can certainly implement these behaviors in Ruby, though not as elegantly as with F# (or other FP languages.)  Incidentally, one of the reasons Ruby gets away with being called an FP language is its support for lambda expressions and the ease in which code blocks yielded to for iteration, application specific handling (like IoC), and so forth.  Again, those are not areas that I’m interested in pursuing in this article because at this point they are part of most imperative languages as well, such as C#, further blurring the lines between imperative, declarative, and functional programming.

…Continue reading this article on The Code Project!

“We Fought The Good Fight”

“‘We fought the good fight; we just didn’t win,’ Boehner told a radio station in his home state of Ohio in reference to GOP efforts to dismantle or defund President Barack Obama’s signature health care reforms and extract deficit reduction concessions around the need to fund the government and raise the federal borrowing limit.” (source)

No, Mr. Boehner, you did not fight the good fight.  Instead, you are a traitor to the American people, causing the suffering of untold Americans.  Here’s an example:

“Our Shutdown Fears Have Been Realized”

To quote from the above article:

“But temporary layoffs for Lorien and 200-plus workers at the resort went into effect on October 1 after the park’s closure. She expects to come back to work after the government resumes, but she won’t get back any of her lost pay because the lodge is owned by a private company.” (my bold text)

I agree with an op-ed piece I had on NPR a couple weeks ago.  Boehner and the rest of the right-wing conservative Tea Party Republicans should be tried as traitors to their country.

 

Function Composition, Function Chaining, Currying, and Partial Functions / Application in F# and Ruby

A new article on function pipelining and function composition in Ruby, attempting to emulate F#’s capability, is here.

List all cucumber step definitions

There’s a lot of implementations out there for how to list the cucumber step definitions in an application.  This is my version, which outputs a sorted list to your browser.  Special thanks to Nat on Testing to get me started with his code.)

I put this code into the file “helpers/step_definition_helper.rb”.

Usage:

In the Rails Console:

include StepDefinitionHelper
step_definitions_to_html

The Code:

# Methods in this helper create a list of existing step definitions which is useful to the developer of features.
module StepDefinitionHelper
  # Outputs all the step definitions to an HTML file, sorted by regex.
  # To use:
  # From the Rails Console:
  # include StepDefinitionHelper
  # step_definitions_to_html
  def step_definitions_to_html
    # Where are your step definitions defined?
    step_definition_dir = "./features/step_definitions"
    # Where do you want output to go:
    outfile = "output.htm"
    steps = get_step_definitions(step_definition_dir)   # get the step definitions
    steps = steps.sort_by {|step| step.regex}           # sort them
    output_step_definitions_as_html(outfile, steps)     # output as HTML
    show_html(outfile) # show the file in the browser
  end

  def get_step_definitions(step_definition_dir)
    steps = []
    Dir.glob(File.join(step_definition_dir,'**/*.rb')).each do |step_file|
      File.new(step_file).read.each_line do |line|
        next unless line =~ /^\s*(?:Given|When|Then)\s+\//
        matches = /(?:Given|When|Then)\s*\/(.*)\/([imxo]*)\s*do\s*(?:$|\|(.*)\|)/.match(line).captures
        matches << step_file
        steps << OpenStruct.new(regex: matches[0], modifiers: matches[1], args: matches[2], source_file: matches[3])
      end
    end
    steps
  end

  def output_step_definitions_as_html(fn, steps)
    f = File.new(fn, "w")
    f << "<table><th>Regex</th><th>Modifiers</th><th>Step Definition Args</th><th>Source file</th>"
    steps.each do |step|
      f << "<tr>"
      f << "<td>#{step.regex}</td>"
      f << "<td>#{step.modifiers}</td>"
      f << "<td>#{step.args}</td>"
      f << "<td>#{step.source_file}</td>"
      f << "</tr>"
    end
    f << "</table>"
  end

  def show_html(outfile)
    require "launchy"
    full_path = 'file:///' + File.join(Rails.root.to_s, outfile)
    Launchy.open(full_path)
  end
end

Generosity Communities: Core Questions

I’ve been working with some folks on the concepts of and around generosity communities. When I explore this concept, I discover that there are some fundamental questions about the premise for a generosity community that I think need to be looked at.  This is the working list of questions that I’ve come up with so far that I believe are important for each participant of any community, not just a generosity community, to explore by themselves and in community groups:

Values

1. What do I value about the other person?  What do I value about myself?
2. When do I “invest” in the other person?  When do I invest in myself?
3. Does the other person feel valued?  Do I feel valued?
4. What prevents me from valuing myself and others?

Authority

1. What are the explicit relationships of authority between us?
2. What are the implicit relationships of authority between us resulting from our life experiences (parenting, prior relationships, social mores, etc.)?
3. How do these relationships of authority influence how we express our needs and hear the needs of others?

Wealth

1. In what ways do I feel wealthy?
2. In what ways do I feel impoverished?

Balance

1. When do I feel out of balance with each other people?
2. How do I address that imbalance currently?
3. How would I, in the future, like to address imbalance?

Health

1. Do I feel “in healthy movement” with the community?
2. Do I feel “the illness of being stuck” with the community?
3. Where am I, and what is needed?
4. How do I get out of the way of myself?

Body / Soul / Spirit

1. Body: What is the physical expression of my community?  For example, meeting places, places where people live, the neighborhood, etc.
2. Soul: What are the qualities of my community?  For example, what are the members passionate about and how is this expressed in the “interests” of the community?  What does community value and what does it disdain?
3. Spirit: How do I describe the identity of the community?  What is its mission statement, its “folk soul”?  (From the psychology dictionary: “a group’s perpetual and fundamental features, morals, norms and values that can’t be explained solely in terms of characteristics of each member.”)

Communication

How can I articulate a spiritually-based value system in such a way that it is capable of entering into a dialog with other conventional value-based systems?  This is an important question for “interfacing” something like a generosity community with, for example, monetary-valuation systems (banks, businesses, the stock market, Wall Street, etc.)

Other Thoughts

We can replace the words “person”, “people”, “community” with other concepts: “partner”, “business”, “co-worker”, “boss”, “child”, and so forth.  These questions are not limited to our relationship with a generosity community but are valuable whenever we are in relationship with something or someone else.

Also, we have many relationships with people and entities (work place, church, charity, grocery store, etc) based on the context of our needs.  What is still a question for me, when I look at the trends of generosity communities, Local Investment Opportunity Networks, income pooling, and crowdfunding, is that these all express the need to fill a void that people are experiencing.  I am still unclear on what exactly that void is that isn’t being filled by our current social structures and how to clearly articulate that void in an objective and concise way.  That will be the topic of further investigation and discussion.