A Nano datawarehouse-like datastore

nanodw.rb

# coding: utf-8
require 'sequel'
require 'logger'

module NanoDW

  Sequel.datetime_class = DateTime
  DB = Sequel.connect(ENV['DATABASE_URL'])
  log = Logger.new(STDERR)
  log.progname = 'NanoDW::Models'
  log.level = ENV['DEBUG'] ? Logger::DEBUG : Logger::WARN
  DB.loggers << log

  module Models

    ##
    # Una serie temporal
    class TimeSeries < Sequel::Model

      PIVOT_VIEW_SQL_TEMPLATE = <<-sql
          SELECT *
          FROM crosstab($CT$
                        SELECT row_index::text || f.date::text, -- id unico para el registro
                               row_index, -- indice de la fila en el cuadro
                               f."date", -- fecha de la muestra
                               d.name, -- nombre de la dimension
                               CASE WHEN d.value_type=0 THEN f.value_string
                                    WHEN d.value_type=1 THEN f.value_int::text
                                    WHEN d.value_type=2 THEN f.value_float::text
                                    WHEN d.value_type=3 THEN f.value_date::text
                               END AS value
                        FROM facts f
                        INNER JOIN dimensions d ON d.name = f.dimension_name
                          AND d.time_series_id = f.dimension_time_series_id
                        WHERE d.time_series_id = %d
                        ORDER BY 1,3,4
                        $CT$,
                        'SELECT DISTINCT name
                         FROM dimensions
                         WHERE time_series_id = %d
                         ORDER BY 1')
          AS ct(row_id text,
                row_index int,
                 "date" date,
                 %s)
      sql

      TYPE_MAP = {
        Integer => 'int',
        Float => 'float',
        String => 'text',
        DateTime => 'date'
      }

      one_to_many :dimensions

      ##
      # adds a +Fact+ to this +TimeSeries+
      def add_fact(row_index, date, dimension_name, value)
        d = self.dimensions_dataset.first(:name => dimension_name)
        if d.nil?
          raise "Dimension `#{dimension_name}` does not exist"
        end

        fact = Fact.new(:dimension => d, :date => date, :row_index => row_index)
        fact.value = value
        if fact.valid?
          fact.save
        else
          raise ValueError, "Fact is invalid: #{fact.errors.inspect}"
        end
      end

      def view_name
        "#{self.class.table_name.to_s}_#{name}_pivot".intern
      end

      def create_pivot_view!
        DB.loggers.last.warn("Regenerating pivot view for TimeSeries `#{self.name}`")
        DB.drop_view(self.view_name, :if_exists => true)
        DB.create_view(self.view_name,
                       self.generate_sql_for_pivot_view)
      end

      def generate_sql_for_pivot_view
        ct_defs = self.dimensions.map { |d| "#{d.name} #{TYPE_MAP[d.type]}"}.sort.join(",\n")
        PIVOT_VIEW_SQL_TEMPLATE % [self.id, self.id, ct_defs]
      end
    end

    ##
    # una dimensión perteneciente a una +TimeSeries+
    # ejemplos: indice, apertura, nro de fila
    class Dimension < Sequel::Model
      set_primary_key [:name, :time_series_id]
      many_to_one :time_series, :key => [:time_series_id]
      one_to_many :facts, :key => [:dimension_name, :dimension_time_series_id]

      def after_save
        self.time_series.create_pivot_view!
      end
      def after_destroy
        self.time_series.create_pivot_view!
      end

      TYPE_MAP = {
        0 => String,
        1 => Integer,
        2 => Float,
        3 => DateTime

      }
      REVERSE_TYPE_MAP = Hash[TYPE_MAP.to_a.map(&:reverse)]

      def type=(t)
        self.value_type = REVERSE_TYPE_MAP[t]
      end

      def type
        TYPE_MAP[self.value_type]
      end

      # actualizar el pivot_view de la +TimeSeries+ cuando cambian las dimensiones


    end

    ##
    # una "celda" de una serie temporal
    class Fact < Sequel::Model

      many_to_one :dimension, :key => [:dimension_name, :dimension_time_series_id]

      def validate
        super
        unless self.value.is_a?(self.dimension.type)
          errors.add(:value,
                     "Type mismatch: value is of type #{self.value.class} (should be #{self.dimension.type})")
        end
      end

      def value=(v)
        if self.dimension.type == String
          self.value_string = v
        elsif self.dimension.type == Float
          self.value_float = v
        elsif self.dimension.type == Integer
          self.value_int = v
        elsif self.dimension.type == DateTime
          self.value_date = v
        end
      end

      def value
        if self.dimension.type == String
          self.value_string
        elsif self.dimension.type == Float
          self.value_float
        elsif self.dimension.type == Integer
          self.value_int
        elsif self.dimension.type == DateTime
          self.value_date
        end
      end
    end
  end
end