nick.recoil.org

When writing migrations, different databases have different syntaxes for certain aspects of table construction. SQLite doesn’t require you to specify the length of a key added to a column of type TEXT, but MySQL does.

If you’re using different databases for development and production, then writing one migration which copes with both databases is important. This can be done with the following code placed inside the migration file:

1
2
3
4
5
6

if ActiveRecord::Base.connection.adapter_name == "SQLite"
  add_index(:assets, :notes)
elsif ActiveRecord::Base.connection.adapter_name == "MySQL"
  execute "CREATE INDEX index_assets_on_notes ON assets (notes(50))"
end

I’ve placed a page under code which details a little plugin I’ve been working on for Mephisto. It’s designed to make links in your templates which need to point to the latest software release always valid. It scans a directory according to a shell glob expression, and gives back a block with the file path, name and version as local variables.

The shell glob expression can be passed in via the liquid template, as per normal with mephisto plugins, or it can be passed in as a special tag attached to the page which is being rendered. This allows per-page links, rather than per-template, and gives greater flexibility.

More information is on the dynamic file link page.

Within ActiveRecord model objects, two methods are provided to allow arbitrary execution of SQL. These are find_by_sql() and count_by_sql(). These are fine for when you’re attempting some particularly tricky SQL SELECT or SELECT COUNT statements, but what happens when you have a tricky DELETE statement you’d also like to craft by hand? There’s no corresponding delete_by_sql()

It turns out you cannot use the aforementioned find_by_sql or count_by_sql to execute a DELETE statement, as they expect the returned data set to have columns defined, and will barf if you don’t have a SELECT. Matt Biddulph pointed me in the right direction, which was to use connection.execute(), or in this case, connection.delete(), from within the model object. These methods are a part of ActiveRecord::Base, so you don’t need to specify the full receiver if you’re already within a class inheriting from it.

In my current rails project, I have a deeply nested set of tables with one set to acts_as_tree, so deleting the top level object, and letting rails propagate the dependent deletions via object instantiation and a call to delete, would take hours. Unfortunately, if you have multiple levels of dependency, you cannot take advantage of the much quicker :dependent => :delete_all, as this will not propagate down your chain of dependent tables.

Instead, the approach I took was to implement a delete_contents() method in the top tier object, and have that delete everything in the dependent tables, without the need for instantiation. Imagine you have the following dependency hierarchy:

Top tier -> Tier 1 -> Tier 2 -> Tier 3

You can implement the following method to achieve a single-statement deletion of all dependent contents:

1
2
3
4
5
6
7
8
9
10

class TopTier < ActiveRecord::Base
  def delete_contents
    connection.delete("DELETE tier1, tier2, tier3
                       FROM tier1
                       LEFT JOIN tier2 ON (tier2.tier1_id = tier1.id)
                       LEFT JOIN tier3 ON (tier3.tier2_id = tier2.id)
                       WHERE tier1.top_tier_id = #{self.id}")
  end
end

connection.delete() will return the number of rows affected. It’s also worth looking at foreign key constraints in MySQL. This removes much of the pain of maintaining referential integrity yourself, but is not supported by all database systems and table type combinations.

Idling away in the #rubyonrails IRC channel sometimes makes you privy to tricks and tips concerning Ruby and Rails that you didn't already know. One such tip is the Ruby Gems documentation WEBrick incantation:

gem_server

which will magically invoke a local WEBrick server on port 8808. This instance lists all of your locally installed Ruby Gems and allow you to quickly and efficiently browse the rdoc contents for them. Very helpful indeed.

Just a quick post to point out an invaluable article on repairing the rails console in Mac OS X 10.4. It involves fetching libreadline and the Ruby readline extension, and reinstalling over the top of your existing environment. Quick, painless and very useful.

I'm just about to deploy the first internal application written in Ruby on Rails for Framestore. This will hopefully mark a nice change in my group towards using frameworks such as RoR and Django for database-driven web applications.

The application in this case is the primary archiving tool for the company's long-term storage of assets. The code is a mixture of Perl for the scanning and writing data to DTF2 or SAIT tapes, and a web interface for browsing and searching on the data, and generating requests for the Data Operations team on behalf of the various artists and producers working on internal projects.

This web interface was previously written in PHP, in a relatively nice way, but utilised no frameworks or modules other than the Pear MySQL class. It was also rapidly growing out of control, as the database grew in size, and the complexity of queries was growing with the functionality of the application.

Since we had the luxury of a ground-up rewrite of the Perl code over the past month or so, I've instigated a schema change to bring the database in line with ActiveRecord's expectations, which has made a huge difference to the ease in which I can sketch out the Rails code. The only odd problem I had was related to tables which logically needed no primary key, as they represented the 'zero-or-one' part of a 'one-to-zero-or-one' table mapping. In my case, a file may be part of a sequence, but it may not, so there are file entries, along with optional sequence entries.

Within the SequenceEntry model, I had to set the primary key to be the same as the foreign key, as ActiveRecord demands some sort of unique way of distinguishing records.

1
2
3
4
5

  class SequenceEntry < ActiveRecord::Base
      belongs_to ::file_entry
      set_primary_key :file_entry_id
  end

One of the more annoying aspects of ActiveRecord I can't seem to get away from is the double SQL query you seem to need when you're looking at pagination of complex queries. I have a block which looks like:

1
2
3
4
5
6
7
8

file_count = FileEntry.count_by_sql("SELECT COUNT(*) FROM file_entries
                 LEFT JOIN directory_entries ON directory_entries.id =
                           file_entries.directory_entry_id
                 LEFT JOIN runs ON directory_entries.run_id = runs.id
                 LEFT JOIN tapes ON runs.tape_id = tapes.id
                 WHERE filename LIKE '#{search_pattern}'
                 AND tapes.barcode = '#{barcode}'")

which returns the number of entries I will be fetching, and then the subsequent find() query, but since each query takes of the order of 20 seconds, it's making the user wait double the amount of time that should be necessary.

The only other thing I'm finding a little difficult is where to place the code that cleans up form input variables before they are passed to the find() statements. It may either be a case of using a helper object, or possibly the prepend_before_filter, but the DRY principle has got me seeking an elegant cross-method solution.