Dirk.Net

To be able to use NHibernate – and as a general purpose and learning exercise –  I wanted to write a script that adds a version column of type integer to all tables in the database, unless such column exists (in Rails the schema_migrations table has a version column for example).

In SQL Server we have the “secret” sproc sp_MSforeachtable for this purpose, but how to bend the stricter syntax of plpgslq to achieve something similar? After a number of hours of trial and error, here’s is a script that actually appears to work, at least with PostgreSQL 9.0 and probably earlier versions:

CREATE OR REPLACE FUNCTION add_version_column_to_all_tables()
RETURNS VOID
AS $$
DECLARE
    my_row    RECORD;
BEGIN      
    FOR my_row IN
        SELECT table_name
        FROM   information_schema.tables
        WHERE  table_schema = ‘public’
       
    LOOP
    IF NOT EXISTS
    (
    SELECT attname FROM pg_attribute WHERE attrelid =
    (SELECT oid FROM pg_class WHERE relname =  my_row.table_name )
     AND attname = ‘version’
     )
     THEN
        EXECUTE(’ALTER TABLE ‘ || my_row.table_name || ‘ ADD COLUMN version int NOT NULL DEFAULT 0;’);
        END IF;
    END LOOP;
END
$$
LANGUAGE plpgsql;

SELECT add_version_column_to_all_tables();

Im sure this script can be optimized, please post any improvements in the columns.

Hope it helps.

If your Apache2 server fails to start after installing SSL (recommended guides are here and here), and get an error in your logs (probably at /var/log/apache2/error.log on Ubuntu) such as

SSL Library Error: 218529960 error:0D0680A8:asn1 encoding routines:ASN1_CHECK_TLEN:wrong tag

SSL Library Error: 218595386 error:0D07803A:asn1 encoding routines:ASN1_ITEM_EX_D2I:nested asn1 error

be sure you have the correct “tags” in your certificate file: It should start with (if PEM-encoded)

-----BEGIN CERTIFICATE-----

and end with

-----END CERTIFICATE-----

including all hyphens (five on each side). The leading hyphens might get eaten when you cut and paste to your server from your local machine.

Here’s a little XML/JSON/YAML/Hash conversion cheat sheet for Ruby:

First, let’s create an XML document:

require ‘rubygems’

require ‘nokogiri’

builder = Nokogiri::XML::Builder.new do |xml|

    xml.root {

      xml.products {

        xml.widget {

          xml.id_ “10″

          xml.name “Awesome widget”

        }

      }

    }

  end

my_xml = builder.to_xml

XML To Hash:

require ‘active_support’ #if you have Rails installed

my_hash = Hash.from_xml(my_xml)

Without Rails/ActiveSupport, have a look at Crack which very fast and will usually give you enough fields. If you have attributes and a text value in the same node however (<person age=”10″>joe</person), you will only get the value back, not the attribute. Update (again): For text nodes (any node that contains a string), crack will return an attributes hash in addition to the text content.

my_hash = Crack::XML.parse(my_xml)

Hash To Object?

Have a look here: http://blog.jayfields.com/2008/01/ruby-hashtomod.html

Hash To JSON:

require ‘json’

my_json = my_hash.to_json

JSON back to Hash:

my_hash = JSON.parse(my_json)

Also have a look at Crack:

my_hash = Crack::JSON.parse(my_json)

Hash To YAML:

my_yaml = my_hash.to_yaml

YAML back to Hash:

my_hash =  YAML::load(my_yaml)

Bonus Points –  Hash to XML:

require ‘xmlsimple’

my_xml = XmlSimple.xml_out(my_hash, {’KeepRoot’ => true})

There is currently no way to preserve the attributes (like <person age=”10″>Joe</person>) with such conversion from Hash to XML.

Here in Thailand I currently get the page below at http://www.rubyonrails.org and http://www.rubyonrails.com. The domain name registration seems to be ok, but there was an update yesterday (April 20th). And it’s a garish parking page that in the colors of Django?! Is it some regional DNS screw-up? Am I mistyping Ruby?

UpdateJust checked via US-based proxy, the issue is confirmed, it’s not a regional Asian DNS snafu.

Update 2The same happened last year and it looks like it happens every year around April 20th, April Fools + 20 or sth.

Things are moving fast in the Rails/Ruby world with the imminent releases of Ruby 1.9.2 and Rails 3.

An essential tool for a smooth transition is RVM by Wayne Seguin (Where can i vote for this guy as Ruby Hero? Ah, it’s here.)

In the first Rails 3 Railscast RVM sounds more like an afterthought and only necessary if you don’t have Ruby 1.8.7+ installed, but it’s advisable to not even try the migration to Rails 3 without installing RVM and a dedicated version of Ruby (can be the same version as your current system Ruby).

While taking the plunge with Rails 3 and its many breaking changes, why not go all the way in a twisted pun sort of way? As Ryan Bates recommends in Railscast 208, it may be time to skip Ruby 1.9.1 and install ruby-head on RVM, which is currently 1.9.2, expected to be released in a few months.

Once you have Rails 3 (currently Beta 3) and Ruby 1.9.2 installed on RVM, perhaps with this nifty script that includes the entire starting “gemset”, you will find that the ruby-debug gem can’t be installed on Ruby 1.9.x, the installer will tell you

Can’t handle 1.9.x yet

and other stuff.

Don’t despair Mark Moseley has created all the necessary gems for you. You can install the ruby 1.9.1 and 1.9.2. compatible debugger and the debug ide with the following commands (thanks to Wayne’s exemplary documentation):

gem install ruby-debug19 — –with-ruby-include=$rvm_path/src/ruby-head/

gem install ruby-debug-ide19 — –with-ruby-include=$rvm_path/src/ruby-head/

Now when you run

rails server –debugger

it still won’t be working, saying

“You need to install ruby-debug to run the server in debugging mode.”

So, as the last step, put this in your Gemfile

gem ‘ruby-debug19′, :require => ‘ruby-debug’

Run ‘bundle install’ if you feel like it, and now the debugger should work. However, the debugger will now start every time you start the server.

To start the server without the debugger, you’ll have to uncomment the line above in the Gemfile again.

Anyone with a workaround for this please post in the comments.

Hope it helps.

P.S. Don’t forget to give back to Open Source!