<commit_before># coding: UTF-8

class Table < Sequel::Model(:user_tables)

  # App constants
  PRIVATE = 0
  PUBLIC  = 1
  CARTODB_COLUMNS = %W{ cartodb_id created_at updated_at the_geom }
  THE_GEOM_WEBMERCATOR = :the_geom_webmercator
  THE_GEOM = :the_geom
  RESERVED_COLUMN_NAMES = %W{ oid tableoid xmin cmin xmax cmax ctid }

  # Ignore mass-asigment on not allowed columns
  self.strict_param_setting = false

  # Allowed columns
  set_allowed_columns(:privacy, :tags)

  attr_accessor :force_schema, :import_from_file,:import_from_url, :import_from_table_copy,
                :importing_SRID, :importing_encoding, :temporal_the_geom_type


  ## Callbacks
  def validate
    super
    errors.add(:user_id, 'can\'t be blank') if user_id.blank?
    errors.add(:privacy, 'has an invalid value') if privacy != PRIVATE && privacy != PUBLIC
    validates_unique [:name, :user_id], :message => 'is already taken'
  end

  def before_validation
    self.privacy ||= PRIVATE
    super
  end

  def before_create
    self.database_name = owner.database_name
    update_updated_at

    #import from file
    if import_from_file.present? or import_from_url.present? or import_from_table_copy.present?
      if import_from_file.present?
        hash_in = ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, :logger => ::Rails.logger,
          "username" => owner.database_username, "password" => owner.database_password,
          :import_from_file => import_from_file, :debug => (Rails.env.development?)
        ).symbolize_keys

        importer = CartoDB::Importer.new hash_in
        importer_result_name = importer.import!.name
      end

      #import from URL
      if import_from_url.present?
        importer = CartoDB::Importer.new ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, :logger => ::Rails.logger,
          "username" => owner.database_username, "password" => owner.database_password,
          :import_from_url => import_from_url, :debug => (Rails.env.development?)
        ).symbolize_keys
        importer_result_name = importer.import!.name
      end

      #Import from copying another table
      if import_from_table_copy.present?
        # ensure unique name
        uniname = get_valid_name(self.name)
        owner.in_database.run("CREATE TABLE #{uniname} AS SELECT * FROM #{import_from_table_copy}")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(the_geom)")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(#{THE_GEOM_WEBMERCATOR})")
        owner.in_database.run("UPDATE #{uniname} SET created_at = now()")
        owner.in_database.run("UPDATE #{uniname} SET updated_at = now()")
        owner.in_database.run("ALTER TABLE #{uniname} ALTER COLUMN created_at SET DEFAULT now()")
        set_trigger_the_geom_webmercator
        importer_result_name = uniname
      end

      self[:name] = importer_result_name
      schema = self.schema(:reload => true)

      owner.in_database do |user_database|
        # If we already have a cartodb_id column let's rename it to an auxiliary column
        aux_cartodb_id_column = nil
        if schema.present? && schema.flatten.include?(:cartodb_id)
           aux_cartodb_id_column = "cartodb_id_aux_#{Time.now.to_i}"
           user_database.run("ALTER TABLE #{self.name} RENAME COLUMN cartodb_id TO #{aux_cartodb_id_column}")
        end

        # When tables are created using ogr2ogr they are added a ogc_fid primary key
        # In that case:
        #  - If cartodb_id already exists, remove ogc_fid
        #  - If cartodb_id does not exist, remove the primary key constraint and treat ogc_fid as the auxiliary column
        if schema.present? && schema.flatten.include?(:ogc_fid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "ogc_fid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN ogc_fid")
          end
        end
        if schema.present? && schema.flatten.include?(:gid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "gid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN gid")
          end
        end

        user_database.run("ALTER TABLE #{self.name} ADD COLUMN cartodb_id SERIAL")

        # If there's an auxiliary column, copy and restart the sequence to the max(cartodb_id)+1
        # Do this before adding constraints cause otherwise we can have duplicate key errors
        if aux_cartodb_id_column.present?
          user_database.run("UPDATE #{self.name} SET cartodb_id = CAST(#{aux_cartodb_id_column} AS INTEGER)")
          user_database.run("ALTER TABLE #{self.name} DROP COLUMN #{aux_cartodb_id_column}")
          cartodb_id_sequence_name = user_database["SELECT pg_get_serial_sequence('#{self.name}', 'cartodb_id')"].first[:pg_get_serial_sequence]
          max_cartodb_id = user_database["SELECT max(cartodb_id) FROM #{self.name}"].first[:max] 
          
          # only reset the sequence on real imports. 
          # skip for duplicate tables as they have totaly new names, but have aux_cartodb_id columns         
          if max_cartodb_id
            user_database.run("ALTER SEQUENCE #{cartodb_id_sequence_name} RESTART WITH #{max_cartodb_id+1}") 
          end  
        end

        user_database.run("ALTER TABLE #{self.name} ADD PRIMARY KEY (cartodb_id)")

        if schema.nil? || !schema.flatten.include?(:created_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN created_at timestamp DEFAULT NOW()")
        end
        if schema.nil? || !schema.flatten.include?(:updated_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN updated_at timestamp DEFAULT NOW()")
        end
      end
      set_the_geom_column!
    else
      create_table_in_database!
      if !self.temporal_the_geom_type.blank?
        self.the_geom_type = self.temporal_the_geom_type
        self.temporal_the_geom_type = nil
      end
      set_the_geom_column!(self.the_geom_type)
    end

    super
  rescue => e
    CartoDB::Logger.info "table#create error", "#{e.inspect}"      
    unless self.name.blank?
      $tables_metadata.del key
      owner.in_database(:as => :superuser).run("DROP TABLE IF EXISTS #{self.name}")
    end

    if @import_from_file
      @import_from_file = URI.escape(@import_from_file) if @import_from_file =~ /^http/
      open(@import_from_file) do |res|
        filename = "#{File.basename(@import_from_file).split('.').first}_#{Time.now.to_i}#{File.extname(@import_from_file)}"
        @import_from_file = File.new Rails.root.join('public', 'uploads', 'failed_imports', filename), 'w'
        @import_from_file.write res.read.force_encoding('utf-8')
        @import_from_file.close
      end

      Airbrake.notify(
        :error_class   => "Import Error",
        :error_message => "Import Error: #{e.message}",
        :parameters    => {
          :database  => database_name,
          :username  => owner.database_username,
          :temp_file => @import_from_file.path
        }
      )
    end

    raise e
  end

  def after_save
    super
    manage_tags
    move_metadata_if_needed
  end

  def after_create
    super
    User.filter(:id => user_id).update(:tables_count => :tables_count + 1)
    owner.in_database(:as => :superuser).run("GRANT SELECT ON #{self.name} TO #{CartoDB::TILE_DB_USER};")
    set_trigger_update_updated_at
    @force_schema = nil
    $tables_metadata.multi do
      $tables_metadata.hset key, "user_id", user_id
      $tables_metadata.hset key, "privacy", PRIVATE
    end
  end

  def before_destroy
    $tables_metadata.del key
  end

  def after_destroy
    super
    Tag.filter(:user_id => user_id, :table_id => id).delete
    User.filter(:id => user_id).update(:tables_count => :tables_count - 1)
    owner.in_database(:as => :superuser) do |user_database|
      begin
        user_database.run("DROP SEQUENCE IF EXISTS cartodb_id_#{oid}_seq")
      rescue => e
        CartoDB::Logger.info "Table#after_destroy error", "maybe table #{self.name} doesn't exist: #{e.inspect}"      
      end
      user_database.run("DROP TABLE IF EXISTS #{self.name}")
    end
  end
  ## End of Callbacks

  def name=(value)
    return if value == self[:name] || value.blank?
    new_name = get_valid_name(value)
    owner.in_database.rename_table(name, new_name) unless new?

    # Do not keep track of name changes until table has been saved
    @name_changed_from = self.name if !new? && self.name.present?
    self[:name] = new_name
  end

  def tags=(value)
    self[:tags] = value.split(',').map{ |t| t.strip }.compact.delete_if{ |t| t.blank? }.uniq.join(',')
  end

  def infowindow=(value)
    $tables_metadata.hset(key, 'infowindow', value)
  end

  def infowindow
    $tables_metadata.hget(key, 'infowindow')
  end
  
  def map_metadata=(value)
    $tables_metadata.hset(key, 'map_metadata', value)
  end

  def map_metadata
    $tables_metadata.hget(key, 'map_metadata')
  end  

  def private?
    $tables_metadata.hget(key, "privacy").to_i == PRIVATE
  end

  def public?
    !private?
  end

  def privacy=(value)
    if value == "PRIVATE" || value == PRIVATE || value == PRIVATE.to_s
      self[:privacy] = PRIVATE
      unless new?
        owner.in_database(:as => :superuser).run("REVOKE SELECT ON #{self.name} FROM #{CartoDB::PUBLIC_DB_USER};")
        $tables_metadata.hset key, "privacy", PRIVATE
      end
    elsif value == "PUBLIC" || value == PUBLIC || value == PUBLIC.to_s
      self[:privacy] = PUBLIC
      unless new?
        $tables_metadata.hset key, "privacy", PUBLIC
        owner.in_database(:as => :superuser).run("GRANT SELECT ON #{self.name} TO #{CartoDB::PUBLIC_DB_USER};")
      end
    end
  end

  def key
    Table.key(database_name, name)
  rescue
    nil
  end

  def self.key(db_name, table_name)
    "rails:#{db_name}:#{table_name}"
  end

  def rows_counted
    @rows_counted ||= owner.in_database[name.to_sym].count
  end
  
  # returns table size in bytes
  def table_size
    @table_size ||= owner.in_database["SELECT pg_relation_size('#{self.name}') as size"].first[:size] / 2
  end  
  
  # TODO: make predictable. Alphabetical would be better
  def schema(options = {})
    first_columns     = []
    middle_columns    = []
    last_columns      = []
    owner.in_database.schema(self.name, options.slice(:reload)).each do |column|
      next if column[0] == THE_GEOM_WEBMERCATOR
      col_db_type = column[1][:db_type].starts_with?("geometry") ? "geometry" : column[1][:db_type]
      col = [ column[0],
        (options[:cartodb_types] == false) ? col_db_type : col_db_type.convert_to_cartodb_type,
        col_db_type == "geometry" ? "geometry" : nil,
        col_db_type == "geometry" ? the_geom_type : nil
      ].compact

      # Make sensible sorting for UI
      case column[0]
        when :cartodb_id
          first_columns.insert(0,col)
        when :the_geom
          first_columns.insert(1,col)  
        when :created_at, :updated_at
          last_columns.insert(-1,col)
        else
          middle_columns << col
      end
    end
    
    # sort middle columns alphabetically
    middle_columns.sort! {|x,y| x[0].to_s <=> y[0].to_s}
    
    # group columns together and return
    (first_columns + middle_columns + last_columns).compact
  end

  def insert_row!(raw_attributes)
    primary_key = nil
    owner.in_database do |user_database|
      schema = user_database.schema(name.to_sym, :reload => true).map{|c| c.first}
      attributes = raw_attributes.dup.select{ |k,v| schema.include?(k.to_sym) }
      if attributes.keys.size != raw_attributes.keys.size
        raise CartoDB::InvalidAttributes, "Invalid rows: #{(raw_attributes.keys - attributes.keys).join(',')}"
      end
      begin
        primary_key = user_database[name.to_sym].insert(attributes.except(THE_GEOM).convert_nulls)
      rescue Sequel::DatabaseError => e        
        message = e.message.split("\n")[0]
        
        # If the type don't match the schema of the table is modified for the next valid type
        invalid_value = (m = message.match(/"([^"]+)"$/)) ? m[1] : nil
        invalid_column = if invalid_value
          attributes.invert[invalid_value] # which is the column of the name that raises error
        else
          if m = message.match(/PGError: ERROR:  value too long for type (.+)$/)
            if candidate = schema(:cartodb_types => false).select{ |c| c[1].to_s == m[1].to_s }.first
              candidate[0]
            end
          end
        end
        
        if new_column_type = get_new_column_type(invalid_column)
          user_database.set_column_type self.name.to_sym, invalid_column.to_sym, new_column_type
          retry
        else
          raise e
        end
      end
    end
    update_the_geom!(raw_attributes, primary_key)
    primary_key
  end

  def update_row!(row_id, raw_attributes)
    rows_updated = 0
    owner.in_database do |user_database|
      schema = user_database.schema(name.to_sym, :reload => true).map{|c| c.first}
      attributes = raw_attributes.dup.select{ |k,v| schema.include?(k.to_sym) }
      if attributes.keys.size != raw_attributes.keys.size
        raise CartoDB::InvalidAttributes, "Invalid rows: #{(raw_attributes.keys - attributes.keys).join(',')}"
      end
      if !attributes.except(THE_GEOM).empty?
        begin
          rows_updated = user_database[name.to_sym].filter(:cartodb_id => row_id).update(attributes.except(THE_GEOM).convert_nulls)
        rescue Sequel::DatabaseError => e
          # If the type don't match the schema of the table is modified for the next valid type
          message = e.message.split("\n")[0]
          invalid_value = message.match(/"([^"]+)"$/)[1]
          invalid_column = attributes.invert[invalid_value] # which is the column of the name that raises error
          if new_column_type = get_new_column_type(invalid_column)
            user_database.set_column_type self.name.to_sym, invalid_column.to_sym, new_column_type
            retry
          else
            raise e
          end
        end
      else
        if attributes.size == 1 && attributes.keys == [THE_GEOM]
          rows_updated = 1
        end
      end
    end
    update_the_geom!(raw_attributes, row_id)
    rows_updated
  end

  def add_column!(options)
    raise CartoDB::InvalidColumnName if RESERVED_COLUMN_NAMES.include?(options[:name]) || options[:name] =~ /^[0-9_]/
    type = options[:type].convert_to_db_type
    cartodb_type = options[:type].convert_to_cartodb_type
    owner.in_database.add_column name.to_sym, options[:name].to_s.sanitize, type
    return {:name => options[:name].to_s.sanitize, :type => type, :cartodb_type => cartodb_type}
  rescue => e
    if e.message =~ /^PGError/
      raise CartoDB::InvalidType, e.message
    else
      raise e
    end
  end

  def drop_column!(options)
    raise if CARTODB_COLUMNS.include?(options[:name].to_s)
    owner.in_database.drop_column name.to_sym, options[:name].to_s
  end

  def modify_column!(options)
    new_name = options[:name] || options[:old_name]
    new_type = options[:type] ? options[:type].try(:convert_to_db_type) : schema(:cartodb_types => false).select{ |c| c[0] == new_name.to_sym }.first[1]
    cartodb_type = new_type.try(:convert_to_cartodb_type)
    
    owner.in_database do |user_database|
      if options[:old_name] && options[:new_name]
        raise CartoDB::InvalidColumnName if options[:new_name] =~ /^[0-9_]/ || RESERVED_COLUMN_NAMES.include?(options[:new_name])
        raise if CARTODB_COLUMNS.include?(options[:old_name].to_s)
        user_database.rename_column name.to_sym, options[:old_name].to_sym, options[:new_name].sanitize.to_sym
        new_name = options[:new_name].sanitize
      end
      if options[:type]
        column_name = (options[:new_name] || options[:name]).sanitize
        raise if CARTODB_COLUMNS.include?(column_name)
        begin
          user_database.set_column_type name.to_sym, column_name.to_sym, new_type
        rescue => e
          message = e.message.split("\n").first
          if message =~ /cannot be cast to type/
            begin
              user_database.transaction do
                random_name = "new_column_#{rand(10)*Time.now.to_i}"
                user_database.add_column name.to_sym, random_name, new_type
                user_database.run("UPDATE #{name} SET #{random_name}=cast(#{column_name} as #{new_type})")
                user_database.drop_column name.to_sym, column_name.to_sym
                user_database.rename_column name.to_sym, random_name, column_name.to_sym
              end
            rescue
              user_database.transaction do
                random_name = "new_column_#{rand(10)*Time.now.to_i}"
                user_database.add_column name.to_sym, random_name, new_type
                user_database.drop_column name.to_sym, column_name.to_sym
                user_database.rename_column name.to_sym, random_name, column_name.to_sym
              end
            end
          else
            raise e
          end
        end
      end
    end
    return {:name => new_name, :type => new_type, :cartodb_type => cartodb_type}
  end

  def records(options = {})
    rows  = []
    records_count = rows_counted
    page, per_page = CartoDB::Pagination.get_page_and_per_page(options)
    order_by_column = options[:order_by] || "cartodb_id"
    mode = (options[:mode] || 'asc').downcase == 'asc' ? 'asc' : 'desc'
    filters = options.slice(:filter_column, :filter_value).reject{|k,v| v.blank?}.values
    where = "WHERE (#{filters.first})|| '' ILIKE '%#{filters.second}%'" if filters.present?

    owner.in_database do |user_database|
      columns_sql_builder = <<-SQL
      SELECT array_to_string(ARRAY(SELECT '#{name}' || '.' || c.column_name
        FROM information_schema.columns As c
        WHERE table_name = '#{name}'
        AND c.column_name <> 'the_geom_webmercator'
        ), ',') AS column_names
      SQL

      column_names = user_database[columns_sql_builder].first[:column_names].split(',')
      if the_geom_index = column_names.index("#{name}.the_geom")
        #if the geometry type of the table is POINT we send the data,
        #if not we will send an string and will have to be requested on demand
        if user_database["SELECT type from geometry_columns where f_table_name = '#{name}'
             and f_geometry_column = 'the_geom'"].first[:type] == "POINT"
          column_names[the_geom_index] = "ST_AsGeoJSON(the_geom,6) as the_geom"
        else
          column_names[the_geom_index] = "'GeoJSON' as the_geom"
        end
      end
      select_columns = column_names.join(',')

      # If we force to get the name from an schema, we avoid the problem of having as
      # table name a reserved word, such 'as'
      rows = user_database["SELECT #{select_columns} FROM #{name} #{where} ORDER BY #{order_by_column} #{mode} LIMIT #{per_page} OFFSET #{page}"].all
      records_count = user_database["SELECT COUNT(cartodb_id) as total_rows FROM #{name} #{where} "].get(:total_rows)
    end
    {
      :id         => id,
      :name       => name,
      :total_rows => records_count,
      :rows       => rows
    }
  end

  def record(identifier)
    row = nil
    owner.in_database do |user_database|
      select = if schema.flatten.include?(THE_GEOM)
        schema.select{|c| c[0] != THE_GEOM }.map{|c| c[0] }.join(',') + ",ST_AsGeoJSON(the_geom,6) as the_geom"
      else
        schema.map{|c| c[0] }.join(',')
      end
      # If we force to get the name from an schema, we avoid the problem of having as
      # table name a reserved word, such 'as'
      row = user_database["SELECT #{select} FROM public.#{name} WHERE cartodb_id = #{identifier}"].first
    end
    raise if row.nil?
    row
  end

  def run_query(query)
    owner.run_query(query)
  end

  def georeference_from!(options = {})
    if !options[:latitude_column].blank? && !options[:longitude_column].blank?
      set_the_geom_column!("point")
            
      owner.in_database do |user_database|
        user_database.run(<<-GEOREF
        UPDATE #{self.name} 
        SET the_geom = 
          ST_GeomFromText(
            'POINT(' || #{options[:longitude_column]} || ' ' || #{options[:latitude_column]} || ')',#{CartoDB::SRID}
        ) 
        WHERE 
        CAST(#{options[:longitude_column]} AS text) ~ '^(([-+]?(([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]+)?))|[-+]?180)$' 
        AND   
        CAST(#{options[:latitude_column]} AS text)  ~ '^(([-+]?(([0-9]|[1-8][0-9])(\.[0-9]+)?))|[-+]?90)$'
        GEOREF
        )
      end
      schema(:reload => true)
    else
      raise InvalidArgument
    end
  end

  def set_trigger_the_geom_webmercator
    owner.in_database(:as => :superuser).run(<<-TRIGGER
      DROP TRIGGER IF EXISTS update_the_geom_webmercator_trigger ON #{self.name};
      CREATE OR REPLACE FUNCTION update_the_geom_webmercator() RETURNS trigger AS $update_the_geom_webmercator_trigger$
        BEGIN
              NEW.#{THE_GEOM_WEBMERCATOR} := ST_Transform(NEW.the_geom,#{CartoDB::GOOGLE_SRID});
              RETURN NEW;
        END;
      $update_the_geom_webmercator_trigger$ LANGUAGE plpgsql VOLATILE COST 100;

      CREATE TRIGGER update_the_geom_webmercator_trigger
      BEFORE INSERT OR UPDATE OF the_geom ON #{self.name}
         FOR EACH ROW EXECUTE PROCEDURE update_the_geom_webmercator();
TRIGGER
    )
  end

  def the_geom_type
    $tables_metadata.hget(key,"the_geom_type") || "point"
  end

  def the_geom_type=(value)
    the_geom_type_value = case value.downcase
      when "point"
        "point"
      when "line"
        "multilinestring"
      else
        value !~ /^multi/ ? "multi#{value.downcase}" : value.downcase
    end
    raise CartoDB::InvalidGeomType unless CartoDB::VALID_GEOMETRY_TYPES.include?(the_geom_type_value)
    if owner.in_database.table_exists?(name)
      $tables_metadata.hset(key,"the_geom_type",the_geom_type_value)
    else
      self.temporal_the_geom_type = the_geom_type_value
    end
  end

  def to_csv
    csv_zipped = nil
    table_name = "csv_export_temp_#{self.name}"
    file_name = "#{self.name}_export"
    csv_file_path = Rails.root.join('tmp', "#{file_name}.csv")
    zip_file_path  = Rails.root.join('tmp', "#{file_name}.zip")
    FileUtils.rm_rf(Dir.glob(csv_file_path))
    FileUtils.rm_rf(Dir.glob(zip_file_path))      
        
    owner.in_database do |user_database|      
      # Setup data export table
      user_database.run("DROP TABLE IF EXISTS #{table_name}")
      export_schema = self.schema.map{|c| c.first} - [THE_GEOM]
      export_schema += ["ST_AsGeoJSON(the_geom, 6) as the_geom"] if self.schema.map{|c| c.first}.include?(THE_GEOM)
      user_database.run("CREATE TABLE #{table_name} AS SELECT #{export_schema.join(',')} FROM #{self.name}")

      # Configure Postgres COPY command for dumping to CSV
      db_configuration = ::Rails::Sequel.configuration.environment_for(Rails.env)
      host     = db_configuration['host'] ? "-h #{db_configuration['host']}" : ""
      port     = db_configuration['port'] ? "-p #{db_configuration['port']}" : ""
      username = db_configuration['username']
      command  = "COPY (SELECT * FROM #{table_name}) TO STDOUT WITH DELIMITER ',' CSV QUOTE AS '\\\"' HEADER"
      
      # Execute CSV dump and log
      cmd = "#{`which psql`.strip} #{host} #{port} -U#{username} -w #{database_name} -c\"#{command}\" > #{csv_file_path}"      
      system cmd
      #CartoDB::Logger.info "Converted #{table_name} to CSV", cmd            
      
      # remove table whatever happened
      user_database.run("DROP TABLE #{table_name}")
      
      # Compress output
      # TODO: Move to ZLib, this is silly
      # http://jimneath.org/2010/01/04/cryptic-ruby-global-variables-and-their-meanings.html
      if $?.success?
        Zip::ZipFile.open(zip_file_path, Zip::ZipFile::CREATE) do |zipfile|
          zipfile.add(File.basename(csv_file_path), csv_file_path)
        end
        return File.read(zip_file_path)      
      end  
    end
  ensure
    # Always cleanup files    
    FileUtils.rm_rf(Dir.glob(csv_file_path))
    FileUtils.rm_rf(Dir.glob(zip_file_path))      
  end

  def to_shp
    shp_files_name = "#{self.name}_export"
    all_files_path = Rails.root.join('tmp', "#{shp_files_name}.*")
    shp_file_path  = Rails.root.join('tmp', "#{shp_files_name}.shp")
    zip_file_path  = Rails.root.join('tmp', "#{shp_files_name}.zip")
    pgsql2shp_bin  = `which pgsql2shp`.strip
    FileUtils.rm_rf(Dir.glob(all_files_path))
    
    # Configure pgsql to shp arguments
    db_configuration = ::Rails::Sequel.configuration.environment_for(Rails.env)
    host     = db_configuration['host'] ? "-h #{db_configuration['host']}" : ""
    port     = db_configuration['port'] ? "-p #{db_configuration['port']}" : ""
    username = db_configuration['username']

    # build command and execute
    cmd = "#{pgsql2shp_bin} #{host} #{port} -u #{username} -f #{shp_file_path} #{database_name} #{self.name}"    
    system cmd
    #CartoDB::Logger.info "Converted #{self.name} to SHP", cmd            

    # Compress output
    # TODO: Move to ZLib, this is silly
    # http://jimneath.org/2010/01/04/cryptic-ruby-global-variables-and-their-meanings.html
    if $?.success?
      Zip::ZipFile.open(zip_file_path, Zip::ZipFile::CREATE) do |zipfile|
        Dir.glob(Rails.root.join('tmp',"#{shp_files_name}.*").to_s).each do |f|
          zipfile.add(File.basename(f), f)
        end
      end
      return File.read(zip_file_path)      
    end
  ensure
    # Always cleanup
    FileUtils.rm_rf(Dir.glob(all_files_path))
  end

  def self.find_all_by_user_id_and_tag(user_id, tag_name)
    fetch("select user_tables.*,
                    array_to_string(array(select tags.name from tags where tags.table_id = user_tables.id),',') as tags_names
                        from user_tables, tags
                        where user_tables.user_id = ?
                          and user_tables.id = tags.table_id
                          and tags.name = ?
                        order by user_tables.id DESC", user_id, tag_name)
  end

  def self.find_by_identifier(user_id, identifier)
    col = (identifier =~ /\A\d+\Z/ || identifier.is_a?(Fixnum)) ? 'id' : 'name'
    
    table = fetch("SELECT *, array_to_string(array(
                     SELECT tags.name FROM tags WHERE tags.table_id = user_tables.id ORDER BY tags.id),',') AS tags_names
                   FROM user_tables WHERE user_tables.user_id = ? AND user_tables.#{col} = ?", user_id, identifier).first
    raise RecordNotFound if table.nil?
    table
  end
  
  def self.find_by_subdomain(subdomain, identifier)
    Table.find_by_identifier(user.id, identifier) if User.find(:username => subdomain)
  end

  def oid
    @oid ||= owner.in_database["SELECT '#{self.name}'::regclass::oid"].first[:oid]
  end

<commit_msg>fix embed map. closes #138<commit_after># coding: UTF-8

class Table < Sequel::Model(:user_tables)

  # App constants
  PRIVATE = 0
  PUBLIC  = 1
  CARTODB_COLUMNS = %W{ cartodb_id created_at updated_at the_geom }
  THE_GEOM_WEBMERCATOR = :the_geom_webmercator
  THE_GEOM = :the_geom
  RESERVED_COLUMN_NAMES = %W{ oid tableoid xmin cmin xmax cmax ctid }

  # Ignore mass-asigment on not allowed columns
  self.strict_param_setting = false

  # Allowed columns
  set_allowed_columns(:privacy, :tags)

  attr_accessor :force_schema, :import_from_file,:import_from_url, :import_from_table_copy,
                :importing_SRID, :importing_encoding, :temporal_the_geom_type


  ## Callbacks
  def validate
    super
    errors.add(:user_id, 'can\'t be blank') if user_id.blank?
    errors.add(:privacy, 'has an invalid value') if privacy != PRIVATE && privacy != PUBLIC
    validates_unique [:name, :user_id], :message => 'is already taken'
  end

  def before_validation
    self.privacy ||= PRIVATE
    super
  end

  def before_create
    self.database_name = owner.database_name
    update_updated_at

    #import from file
    if import_from_file.present? or import_from_url.present? or import_from_table_copy.present?
      if import_from_file.present?
        hash_in = ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, :logger => ::Rails.logger,
          "username" => owner.database_username, "password" => <PASSWORD>.database_password,
          :import_from_file => import_from_file, :debug => (Rails.env.development?)
        ).symbolize_keys

        importer = CartoDB::Importer.new hash_in
        importer_result_name = importer.import!.name
      end

      #import from URL
      if import_from_url.present?
        importer = CartoDB::Importer.new ::Rails::Sequel.configuration.environment_for(Rails.env).merge(
          "database" => database_name, :logger => ::Rails.logger,
          "username" => owner.database_username, "password" => <PASSWORD>.database_password,
          :import_from_url => import_from_url, :debug => (Rails.env.development?)
        ).symbolize_keys
        importer_result_name = importer.import!.name
      end

      #Import from copying another table
      if import_from_table_copy.present?
        # ensure unique name
        uniname = get_valid_name(self.name)
        owner.in_database.run("CREATE TABLE #{uniname} AS SELECT * FROM #{import_from_table_copy}")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(the_geom)")
        owner.in_database.run("CREATE INDEX ON #{uniname} USING GIST(#{THE_GEOM_WEBMERCATOR})")
        owner.in_database.run("UPDATE #{uniname} SET created_at = now()")
        owner.in_database.run("UPDATE #{uniname} SET updated_at = now()")
        owner.in_database.run("ALTER TABLE #{uniname} ALTER COLUMN created_at SET DEFAULT now()")
        set_trigger_the_geom_webmercator
        importer_result_name = uniname
      end

      self[:name] = importer_result_name
      schema = self.schema(:reload => true)

      owner.in_database do |user_database|
        # If we already have a cartodb_id column let's rename it to an auxiliary column
        aux_cartodb_id_column = nil
        if schema.present? && schema.flatten.include?(:cartodb_id)
           aux_cartodb_id_column = "cartodb_id_aux_#{Time.now.to_i}"
           user_database.run("ALTER TABLE #{self.name} RENAME COLUMN cartodb_id TO #{aux_cartodb_id_column}")
        end

        # When tables are created using ogr2ogr they are added a ogc_fid primary key
        # In that case:
        #  - If cartodb_id already exists, remove ogc_fid
        #  - If cartodb_id does not exist, remove the primary key constraint and treat ogc_fid as the auxiliary column
        if schema.present? && schema.flatten.include?(:ogc_fid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "ogc_fid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN ogc_fid")
          end
        end
        if schema.present? && schema.flatten.include?(:gid)
          if aux_cartodb_id_column.nil?
            aux_cartodb_id_column = "gid"
          else
            user_database.run("ALTER TABLE #{self.name} DROP COLUMN gid")
          end
        end

        user_database.run("ALTER TABLE #{self.name} ADD COLUMN cartodb_id SERIAL")

        # If there's an auxiliary column, copy and restart the sequence to the max(cartodb_id)+1
        # Do this before adding constraints cause otherwise we can have duplicate key errors
        if aux_cartodb_id_column.present?
          user_database.run("UPDATE #{self.name} SET cartodb_id = CAST(#{aux_cartodb_id_column} AS INTEGER)")
          user_database.run("ALTER TABLE #{self.name} DROP COLUMN #{aux_cartodb_id_column}")
          cartodb_id_sequence_name = user_database["SELECT pg_get_serial_sequence('#{self.name}', 'cartodb_id')"].first[:pg_get_serial_sequence]
          max_cartodb_id = user_database["SELECT max(cartodb_id) FROM #{self.name}"].first[:max] 
          
          # only reset the sequence on real imports. 
          # skip for duplicate tables as they have totaly new names, but have aux_cartodb_id columns         
          if max_cartodb_id
            user_database.run("ALTER SEQUENCE #{cartodb_id_sequence_name} RESTART WITH #{max_cartodb_id+1}") 
          end  
        end

        user_database.run("ALTER TABLE #{self.name} ADD PRIMARY KEY (cartodb_id)")

        if schema.nil? || !schema.flatten.include?(:created_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN created_at timestamp DEFAULT NOW()")
        end
        if schema.nil? || !schema.flatten.include?(:updated_at)
          user_database.run("ALTER TABLE #{self.name} ADD COLUMN updated_at timestamp DEFAULT NOW()")
        end
      end
      set_the_geom_column!
    else
      create_table_in_database!
      if !self.temporal_the_geom_type.blank?
        self.the_geom_type = self.temporal_the_geom_type
        self.temporal_the_geom_type = nil
      end
      set_the_geom_column!(self.the_geom_type)
    end

    super
  rescue => e
    CartoDB::Logger.info "table#create error", "#{e.inspect}"      
    unless self.name.blank?
      $tables_metadata.del key
      owner.in_database(:as => :superuser).run("DROP TABLE IF EXISTS #{self.name}")
    end

    if @import_from_file
      @import_from_file = URI.escape(@import_from_file) if @import_from_file =~ /^http/
      open(@import_from_file) do |res|
        filename = "#{File.basename(@import_from_file).split('.').first}_#{Time.now.to_i}#{File.extname(@import_from_file)}"
        @import_from_file = File.new Rails.root.join('public', 'uploads', 'failed_imports', filename), 'w'
        @import_from_file.write res.read.force_encoding('utf-8')
        @import_from_file.close
      end

      Airbrake.notify(
        :error_class   => "Import Error",
        :error_message => "Import Error: #{e.message}",
        :parameters    => {
          :database  => database_name,
          :username  => owner.database_username,
          :temp_file => @import_from_file.path
        }
      )
    end

    raise e
  end

  def after_save
    super
    manage_tags
    move_metadata_if_needed
  end

  def after_create
    super
    User.filter(:id => user_id).update(:tables_count => :tables_count + 1)
    owner.in_database(:as => :superuser).run("GRANT SELECT ON #{self.name} TO #{CartoDB::TILE_DB_USER};")
    set_trigger_update_updated_at
    @force_schema = nil
    $tables_metadata.multi do
      $tables_metadata.hset key, "user_id", user_id
      $tables_metadata.hset key, "privacy", PRIVATE
    end
  end

  def before_destroy
    $tables_metadata.del key
  end

  def after_destroy
    super
    Tag.filter(:user_id => user_id, :table_id => id).delete
    User.filter(:id => user_id).update(:tables_count => :tables_count - 1)
    owner.in_database(:as => :superuser) do |user_database|
      begin
        user_database.run("DROP SEQUENCE IF EXISTS cartodb_id_#{oid}_seq")
      rescue => e
        CartoDB::Logger.info "Table#after_destroy error", "maybe table #{self.name} doesn't exist: #{e.inspect}"      
      end
      user_database.run("DROP TABLE IF EXISTS #{self.name}")
    end
  end
  ## End of Callbacks

  def name=(value)
    return if value == self[:name] || value.blank?
    new_name = get_valid_name(value)
    owner.in_database.rename_table(name, new_name) unless new?

    # Do not keep track of name changes until table has been saved
    @name_changed_from = self.name if !new? && self.name.present?
    self[:name] = new_name
  end

  def tags=(value)
    self[:tags] = value.split(',').map{ |t| t.strip }.compact.delete_if{ |t| t.blank? }.uniq.join(',')
  end

  def infowindow=(value)
    $tables_metadata.hset(key, 'infowindow', value)
  end

  def infowindow
    $tables_metadata.hget(key, 'infowindow')
  end
  
  def map_metadata=(value)
    $tables_metadata.hset(key, 'map_metadata', value)
  end

  def map_metadata
    $tables_metadata.hget(key, 'map_metadata')
  end  

  def private?
    $tables_metadata.hget(key, "privacy").to_i == PRIVATE
  end

  def public?
    !private?
  end

  def privacy=(value)
    if value == "PRIVATE" || value == PRIVATE || value == PRIVATE.to_s
      self[:privacy] = PRIVATE
      unless new?
        owner.in_database(:as => :superuser).run("REVOKE SELECT ON #{self.name} FROM #{CartoDB::PUBLIC_DB_USER};")
        $tables_metadata.hset key, "privacy", PRIVATE
      end
    elsif value == "PUBLIC" || value == PUBLIC || value == PUBLIC.to_s
      self[:privacy] = PUBLIC
      unless new?
        $tables_metadata.hset key, "privacy", PUBLIC
        owner.in_database(:as => :superuser).run("GRANT SELECT ON #{self.name} TO #{CartoDB::PUBLIC_DB_USER};")
      end
    end
  end

  def key
    Table.key(database_name, name)
  rescue
    nil
  end

  def self.key(db_name, table_name)
    "rails:#{db_name}:#{table_name}"
  end

  def rows_counted
    @rows_counted ||= owner.in_database[name.to_sym].count
  end
  
  # returns table size in bytes
  def table_size
    @table_size ||= owner.in_database["SELECT pg_relation_size('#{self.name}') as size"].first[:size] / 2
  end  
  
  # TODO: make predictable. Alphabetical would be better
  def schema(options = {})
    first_columns     = []
    middle_columns    = []
    last_columns      = []
    owner.in_database.schema(self.name, options.slice(:reload)).each do |column|
      next if column[0] == THE_GEOM_WEBMERCATOR
      col_db_type = column[1][:db_type].starts_with?("geometry") ? "geometry" : column[1][:db_type]
      col = [ column[0],
        (options[:cartodb_types] == false) ? col_db_type : col_db_type.convert_to_cartodb_type,
        col_db_type == "geometry" ? "geometry" : nil,
        col_db_type == "geometry" ? the_geom_type : nil
      ].compact

      # Make sensible sorting for UI
      case column[0]
        when :cartodb_id
          first_columns.insert(0,col)
        when :the_geom
          first_columns.insert(1,col)  
        when :created_at, :updated_at
          last_columns.insert(-1,col)
        else
          middle_columns << col
      end
    end
    
    # sort middle columns alphabetically
    middle_columns.sort! {|x,y| x[0].to_s <=> y[0].to_s}
    
    # group columns together and return
    (first_columns + middle_columns + last_columns).compact
  end

  def insert_row!(raw_attributes)
    primary_key = nil
    owner.in_database do |user_database|
      schema = user_database.schema(name.to_sym, :reload => true).map{|c| c.first}
      attributes = raw_attributes.dup.select{ |k,v| schema.include?(k.to_sym) }
      if attributes.keys.size != raw_attributes.keys.size
        raise CartoDB::InvalidAttributes, "Invalid rows: #{(raw_attributes.keys - attributes.keys).join(',')}"
      end
      begin
        primary_key = user_database[name.to_sym].insert(attributes.except(THE_GEOM).convert_nulls)
      rescue Sequel::DatabaseError => e        
        message = e.message.split("\n")[0]
        
        # If the type don't match the schema of the table is modified for the next valid type
        invalid_value = (m = message.match(/"([^"]+)"$/)) ? m[1] : nil
        invalid_column = if invalid_value
          attributes.invert[invalid_value] # which is the column of the name that raises error
        else
          if m = message.match(/PGError: ERROR:  value too long for type (.+)$/)
            if candidate = schema(:cartodb_types => false).select{ |c| c[1].to_s == m[1].to_s }.first
              candidate[0]
            end
          end
        end
        
        if new_column_type = get_new_column_type(invalid_column)
          user_database.set_column_type self.name.to_sym, invalid_column.to_sym, new_column_type
          retry
        else
          raise e
        end
      end
    end
    update_the_geom!(raw_attributes, primary_key)
    primary_key
  end

  def update_row!(row_id, raw_attributes)
    rows_updated = 0
    owner.in_database do |user_database|
      schema = user_database.schema(name.to_sym, :reload => true).map{|c| c.first}
      attributes = raw_attributes.dup.select{ |k,v| schema.include?(k.to_sym) }
      if attributes.keys.size != raw_attributes.keys.size
        raise CartoDB::InvalidAttributes, "Invalid rows: #{(raw_attributes.keys - attributes.keys).join(',')}"
      end
      if !attributes.except(THE_GEOM).empty?
        begin
          rows_updated = user_database[name.to_sym].filter(:cartodb_id => row_id).update(attributes.except(THE_GEOM).convert_nulls)
        rescue Sequel::DatabaseError => e
          # If the type don't match the schema of the table is modified for the next valid type
          message = e.message.split("\n")[0]
          invalid_value = message.match(/"([^"]+)"$/)[1]
          invalid_column = attributes.invert[invalid_value] # which is the column of the name that raises error
          if new_column_type = get_new_column_type(invalid_column)
            user_database.set_column_type self.name.to_sym, invalid_column.to_sym, new_column_type
            retry
          else
            raise e
          end
        end
      else
        if attributes.size == 1 && attributes.keys == [THE_GEOM]
          rows_updated = 1
        end
      end
    end
    update_the_geom!(raw_attributes, row_id)
    rows_updated
  end

  def add_column!(options)
    raise CartoDB::InvalidColumnName if RESERVED_COLUMN_NAMES.include?(options[:name]) || options[:name] =~ /^[0-9_]/
    type = options[:type].convert_to_db_type
    cartodb_type = options[:type].convert_to_cartodb_type
    owner.in_database.add_column name.to_sym, options[:name].to_s.sanitize, type
    return {:name => options[:name].to_s.sanitize, :type => type, :cartodb_type => cartodb_type}
  rescue => e
    if e.message =~ /^PGError/
      raise CartoDB::InvalidType, e.message
    else
      raise e
    end
  end

  def drop_column!(options)
    raise if CARTODB_COLUMNS.include?(options[:name].to_s)
    owner.in_database.drop_column name.to_sym, options[:name].to_s
  end

  def modify_column!(options)
    new_name = options[:name] || options[:old_name]
    new_type = options[:type] ? options[:type].try(:convert_to_db_type) : schema(:cartodb_types => false).select{ |c| c[0] == new_name.to_sym }.first[1]
    cartodb_type = new_type.try(:convert_to_cartodb_type)
    
    owner.in_database do |user_database|
      if options[:old_name] && options[:new_name]
        raise CartoDB::InvalidColumnName if options[:new_name] =~ /^[0-9_]/ || RESERVED_COLUMN_NAMES.include?(options[:new_name])
        raise if CARTODB_COLUMNS.include?(options[:old_name].to_s)
        user_database.rename_column name.to_sym, options[:old_name].to_sym, options[:new_name].sanitize.to_sym
        new_name = options[:new_name].sanitize
      end
      if options[:type]
        column_name = (options[:new_name] || options[:name]).sanitize
        raise if CARTODB_COLUMNS.include?(column_name)
        begin
          user_database.set_column_type name.to_sym, column_name.to_sym, new_type
        rescue => e
          message = e.message.split("\n").first
          if message =~ /cannot be cast to type/
            begin
              user_database.transaction do
                random_name = "new_column_#{rand(10)*Time.now.to_i}"
                user_database.add_column name.to_sym, random_name, new_type
                user_database.run("UPDATE #{name} SET #{random_name}=cast(#{column_name} as #{new_type})")
                user_database.drop_column name.to_sym, column_name.to_sym
                user_database.rename_column name.to_sym, random_name, column_name.to_sym
              end
            rescue
              user_database.transaction do
                random_name = "new_column_#{rand(10)*Time.now.to_i}"
                user_database.add_column name.to_sym, random_name, new_type
                user_database.drop_column name.to_sym, column_name.to_sym
                user_database.rename_column name.to_sym, random_name, column_name.to_sym
              end
            end
          else
            raise e
          end
        end
      end
    end
    return {:name => new_name, :type => new_type, :cartodb_type => cartodb_type}
  end

  def records(options = {})
    rows  = []
    records_count = rows_counted
    page, per_page = CartoDB::Pagination.get_page_and_per_page(options)
    order_by_column = options[:order_by] || "cartodb_id"
    mode = (options[:mode] || 'asc').downcase == 'asc' ? 'asc' : 'desc'
    filters = options.slice(:filter_column, :filter_value).reject{|k,v| v.blank?}.values
    where = "WHERE (#{filters.first})|| '' ILIKE '%#{filters.second}%'" if filters.present?

    owner.in_database do |user_database|
      columns_sql_builder = <<-SQL
      SELECT array_to_string(ARRAY(SELECT '#{name}' || '.' || c.column_name
        FROM information_schema.columns As c
        WHERE table_name = '#{name}'
        AND c.column_name <> 'the_geom_webmercator'
        ), ',') AS column_names
      SQL

      column_names = user_database[columns_sql_builder].first[:column_names].split(',')
      if the_geom_index = column_names.index("#{name}.the_geom")
        #if the geometry type of the table is POINT we send the data,
        #if not we will send an string and will have to be requested on demand
        if user_database["SELECT type from geometry_columns where f_table_name = '#{name}'
             and f_geometry_column = 'the_geom'"].first[:type] == "POINT"
          column_names[the_geom_index] = "ST_AsGeoJSON(the_geom,6) as the_geom"
        else
          column_names[the_geom_index] = "'GeoJSON' as the_geom"
        end
      end
      select_columns = column_names.join(',')

      # If we force to get the name from an schema, we avoid the problem of having as
      # table name a reserved word, such 'as'
      rows = user_database["SELECT #{select_columns} FROM #{name} #{where} ORDER BY #{order_by_column} #{mode} LIMIT #{per_page} OFFSET #{page}"].all
      records_count = user_database["SELECT COUNT(cartodb_id) as total_rows FROM #{name} #{where} "].get(:total_rows)
    end
    {
      :id         => id,
      :name       => name,
      :total_rows => records_count,
      :rows       => rows
    }
  end

  def record(identifier)
    row = nil
    owner.in_database do |user_database|
      select = if schema.flatten.include?(THE_GEOM)
        schema.select{|c| c[0] != THE_GEOM }.map{|c| c[0] }.join(',') + ",ST_AsGeoJSON(the_geom,6) as the_geom"
      else
        schema.map{|c| c[0] }.join(',')
      end
      # If we force to get the name from an schema, we avoid the problem of having as
      # table name a reserved word, such 'as'
      row = user_database["SELECT #{select} FROM public.#{name} WHERE cartodb_id = #{identifier}"].first
    end
    raise if row.nil?
    row
  end

  def run_query(query)
    owner.run_query(query)
  end

  def georeference_from!(options = {})
    if !options[:latitude_column].blank? && !options[:longitude_column].blank?
      set_the_geom_column!("point")
            
      owner.in_database do |user_database|
        user_database.run(<<-GEOREF
        UPDATE #{self.name} 
        SET the_geom = 
          ST_GeomFromText(
            'POINT(' || #{options[:longitude_column]} || ' ' || #{options[:latitude_column]} || ')',#{CartoDB::SRID}
        ) 
        WHERE 
        CAST(#{options[:longitude_column]} AS text) ~ '^(([-+]?(([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]+)?))|[-+]?180)$' 
        AND   
        CAST(#{options[:latitude_column]} AS text)  ~ '^(([-+]?(([0-9]|[1-8][0-9])(\.[0-9]+)?))|[-+]?90)$'
        GEOREF
        )
      end
      schema(:reload => true)
    else
      raise InvalidArgument
    end
  end

  def set_trigger_the_geom_webmercator
    owner.in_database(:as => :superuser).run(<<-TRIGGER
      DROP TRIGGER IF EXISTS update_the_geom_webmercator_trigger ON #{self.name};
      CREATE OR REPLACE FUNCTION update_the_geom_webmercator() RETURNS trigger AS $update_the_geom_webmercator_trigger$
        BEGIN
              NEW.#{THE_GEOM_WEBMERCATOR} := ST_Transform(NEW.the_geom,#{CartoDB::GOOGLE_SRID});
              RETURN NEW;
        END;
      $update_the_geom_webmercator_trigger$ LANGUAGE plpgsql VOLATILE COST 100;

      CREATE TRIGGER update_the_geom_webmercator_trigger
      BEFORE INSERT OR UPDATE OF the_geom ON #{self.name}
         FOR EACH ROW EXECUTE PROCEDURE update_the_geom_webmercator();
TRIGGER
    )
  end

  def the_geom_type
    $tables_metadata.hget(key,"the_geom_type") || "point"
  end

  def the_geom_type=(value)
    the_geom_type_value = case value.downcase
      when "point"
        "point"
      when "line"
        "multilinestring"
      else
        value !~ /^multi/ ? "multi#{value.downcase}" : value.downcase
    end
    raise CartoDB::InvalidGeomType unless CartoDB::VALID_GEOMETRY_TYPES.include?(the_geom_type_value)
    if owner.in_database.table_exists?(name)
      $tables_metadata.hset(key,"the_geom_type",the_geom_type_value)
    else
      self.temporal_the_geom_type = the_geom_type_value
    end
  end

  def to_csv
    csv_zipped = nil
    table_name = "csv_export_temp_#{self.name}"
    file_name = "#{self.name}_export"
    csv_file_path = Rails.root.join('tmp', "#{file_name}.csv")
    zip_file_path  = Rails.root.join('tmp', "#{file_name}.zip")
    FileUtils.rm_rf(Dir.glob(csv_file_path))
    FileUtils.rm_rf(Dir.glob(zip_file_path))      
        
    owner.in_database do |user_database|      
      # Setup data export table
      user_database.run("DROP TABLE IF EXISTS #{table_name}")
      export_schema = self.schema.map{|c| c.first} - [THE_GEOM]
      export_schema += ["ST_AsGeoJSON(the_geom, 6) as the_geom"] if self.schema.map{|c| c.first}.include?(THE_GEOM)
      user_database.run("CREATE TABLE #{table_name} AS SELECT #{export_schema.join(',')} FROM #{self.name}")

      # Configure Postgres COPY command for dumping to CSV
      db_configuration = ::Rails::Sequel.configuration.environment_for(Rails.env)
      host     = db_configuration['host'] ? "-h #{db_configuration['host']}" : ""
      port     = db_configuration['port'] ? "-p #{db_configuration['port']}" : ""
      username = db_configuration['username']
      command  = "COPY (SELECT * FROM #{table_name}) TO STDOUT WITH DELIMITER ',' CSV QUOTE AS '\\\"' HEADER"
      
      # Execute CSV dump and log
      cmd = "#{`which psql`.strip} #{host} #{port} -U#{username} -w #{database_name} -c\"#{command}\" > #{csv_file_path}"      
      system cmd
      #CartoDB::Logger.info "Converted #{table_name} to CSV", cmd            
      
      # remove table whatever happened
      user_database.run("DROP TABLE #{table_name}")
      
      # Compress output
      # TODO: Move to ZLib, this is silly
      # http://jimneath.org/2010/01/04/cryptic-ruby-global-variables-and-their-meanings.html
      if $?.success?
        Zip::ZipFile.open(zip_file_path, Zip::ZipFile::CREATE) do |zipfile|
          zipfile.add(File.basename(csv_file_path), csv_file_path)
        end
        return File.read(zip_file_path)      
      end  
    end
  ensure
    # Always cleanup files    
    FileUtils.rm_rf(Dir.glob(csv_file_path))
    FileUtils.rm_rf(Dir.glob(zip_file_path))      
  end

  def to_shp
    shp_files_name = "#{self.name}_export"
    all_files_path = Rails.root.join('tmp', "#{shp_files_name}.*")
    shp_file_path  = Rails.root.join('tmp', "#{shp_files_name}.shp")
    zip_file_path  = Rails.root.join('tmp', "#{shp_files_name}.zip")
    pgsql2shp_bin  = `which pgsql2shp`.strip
    FileUtils.rm_rf(Dir.glob(all_files_path))
    
    # Configure pgsql to shp arguments
    db_configuration = ::Rails::Sequel.configuration.environment_for(Rails.env)
    host     = db_configuration['host'] ? "-h #{db_configuration['host']}" : ""
    port     = db_configuration['port'] ? "-p #{db_configuration['port']}" : ""
    username = db_configuration['username']

    # build command and execute
    cmd = "#{pgsql2shp_bin} #{host} #{port} -u #{username} -f #{shp_file_path} #{database_name} #{self.name}"    
    system cmd
    #CartoDB::Logger.info "Converted #{self.name} to SHP", cmd            

    # Compress output
    # TODO: Move to ZLib, this is silly
    # http://jimneath.org/2010/01/04/cryptic-ruby-global-variables-and-their-meanings.html
    if $?.success?
      Zip::ZipFile.open(zip_file_path, Zip::ZipFile::CREATE) do |zipfile|
        Dir.glob(Rails.root.join('tmp',"#{shp_files_name}.*").to_s).each do |f|
          zipfile.add(File.basename(f), f)
        end
      end
      return File.read(zip_file_path)      
    end
  ensure
    # Always cleanup
    FileUtils.rm_rf(Dir.glob(all_files_path))
  end

  def self.find_all_by_user_id_and_tag(user_id, tag_name)
    fetch("select user_tables.*,
                    array_to_string(array(select tags.name from tags where tags.table_id = user_tables.id),',') as tags_names
                        from user_tables, tags
                        where user_tables.user_id = ?
                          and user_tables.id = tags.table_id
                          and tags.name = ?
                        order by user_tables.id DESC", user_id, tag_name)
  end

  def self.find_by_identifier(user_id, identifier)
    col = (identifier =~ /\A\d+\Z/ || identifier.is_a?(Fixnum)) ? 'id' : 'name'
    
    table = fetch("SELECT *, array_to_string(array(
                     SELECT tags.name FROM tags WHERE tags.table_id = user_tables.id ORDER BY tags.id),',') AS tags_names
                   FROM user_tables WHERE user_tables.user_id = ? AND user_tables.#{col} = ?", user_id, identifier).first
    raise RecordNotFound if table.nil?
    table
  end
  
  def self.find_by_subdomain(subdomain, identifier)
    if user = User.find(:username => subdomain)      
      Table.find_by_identifier(user.id, identifier)
    end  
  end

  def oid
    @oid ||= owner.in_database["SELECT '#{self.name}'::regclass::oid"].first[:oid]
  end


{'fraction_non_alphanumeric': 0.11861800666913672, 'fraction_numerical': 0.0035383475361244906, 'mean_word_length': 2.887720561757292, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 121, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7276992', 'n_tokens_mistral': 18137, 'n_tokens_neox': 17654, 'n_words': 3720}