Rails 2 + SQL server: How to make table that is supposed to have multiple timestamps?

Question

Hi I am currently working on a Rails 2 project that uses Microsoft SQL Server. I am about to implement the delayed_job gem, which allows background processes. In order to do this, I must create a table that would look like this in a migration:

class CreateDelayedJobs < ActiveRecord::Migration
  def self.up
    create_table :delayed_jobs, :force => true do |table|
      table.integer  :priority, :default => 0      # Allows some jobs to jump to the front of the queue
      table.integer  :attempts, :default => 0      # Provides for retries, but still fail eventually.
      table.text     :handler                      # YAML-encoded string of the object that will do work
      table.text     :last_error                   # reason for last failure (See Note below)
      table.datetime :run_at                       # When to run. Could be Time.zone.now for immediately, or sometime in the future.
      table.datetime :locked_at                    # Set when a client is working on this object
      table.datetime :failed_at                    # Set when all retries have failed (actually, by default, the record is deleted instead)
      table.string   :locked_by                    # Who is working on this object (if locked)
      table.timestamps
    end

    add_index :delayed_jobs, [:priority, :run_at], :name => 'delayed_jobs_priority'
  end

  def self.down
    drop_table :delayed_jobs  
  end
end

Notice that there are 3 datetime columns. However, I must do this in pure SQL using SQL Server syntax. According to W3 schools:

timestamp   Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

CREATE TABLE delayed_jobs
{
    id uniqueidentifier,
    priority int,
    attempts int,
    handler text,
    last_error text,
    run_at timestamp,
    locked_at timestamp,
    failed_at timestamp,
    locked_by varchar(255)
};

How can I add go around this single timestamp limitation?
How do I add indexes?

score 1 · Accepted Answer · answered Jan 25 '13 at 16:23

1

Just a heads-up, you're gonna get nailed for mentioning W3 Schools here. :-) It's an evil site. Anyway, you can have as many timestamp columns as you need. They can hold the system time or any other timestamp you want. There are no limitations.

For the indexes, use:

CREATE INDEX index_name
ON table_name (column_name)

OR

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

answered Jan 25 '13 at 16:23

N1tr0

485
2
6
24

so for this case, the migration wants `CREATE NONCLUSTERED INDEX delayed_jobs_priority ON delayed_jobs (priority,run_at);` right? there are 2 columns to be indexed. i'm not sure what clustered/nonclustered mean – bigpotato Jan 25 '13 at 16:30
Check this out for the clustered/nonclustered explanation: http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean – N1tr0 Jan 25 '13 at 17:05
Here's another good discussion in it: http://stackoverflow.com/questions/7605707/clustered-vs-non-clustered – N1tr0 Jan 25 '13 at 17:13
apparently w3 was correct with having only one timestamp column. i just used datetime instead. – bigpotato Jan 25 '13 at 19:51
I guess I used the wrong term to describe the 'timestamp' field. True, only one field allowed to track when a row is inserted but you can have as many DateTime fields as you wish. :-) – N1tr0 Jan 25 '13 at 20:07

Rails 2 + SQL server: How to make table that is supposed to have multiple timestamps?

1 Answers1