Sequel: How to get the data type of a column of a Dataset

Question

How can I get the data type of a column of a Dataset. Or more general: How to get the schema of a dataset?

Imagine I have the following situation:

require 'sequel'
DB = Sequel.sqlite()
DB.create_table(:data){
    nvarchar :key
    timestamp :timestamp
    Date :date
}

sel = DB[:data].select(:key, :timestamp)

Now I want to know, which datatype is the column timestamp in my selection.

I would like to get something like Sequel::Dataset#columntype(column).

I made a solution like this:

module Sequel
  class Dataset
    def schema()
      schema = []
      self.db.schema(self).each{|colid, coldef|
        next unless self.columns.include?(colid)
        schema << [colid, coldef]
      }
      schema
    end

    def columntype(colname)
      self.schema.each{|colid, coldef|
        next unless colid == colname
        return coldef[:type]
      }
      raise ArgumentError, "#{colname} not part of #{self.inspect}"
    end
  end
end

p sel.schema #-> [[:key, {:allow_null=>true, :default=>nil, :primary_key=>false,....
p sel.columntype(:timestamp) #-> :datetime
p sel.columntype(:key) #-> :string
p sel.columntype(:date) #-> ArgumentError

But this solution looks a bit wrong and it does not work on joins:

p sel.join(:data).columntype(:timestamp)
#-> `schema': can only parse the schema for a dataset with a single from table (Sequel::Error)

I tried also Dataset#schema_and_table, but without any success:

p sel.schema_and_table(sel.first_source_table) #-> [nil, "data"]

Are there any other ways I did not find in the documentation?

score 8 · Accepted Answer · answered Nov 25 '12 at 16:32

In Sequel, datasets do not know the types of their columns. For anything complicated, the only way to know the type of the column is to run the query (think column aliases, function calls, CTEs, etc.). Even then, you'd have to guess the database type based on the ruby type that Sequel gives you.

You can use Database#schema with a table name to get the schema information for that table. That's the only supported way to get type information from the database in Sequel.

Thanks for the clarification. That was also my impression, but I wanted to be sure about it. — knut, Nov 25 '12 at 21:18

Sequel: How to get the data type of a column of a Dataset

1 Answers1