module Statsample::Database - RDoc Documentation

create_sql(ds,table,charset="UTF8") click to toggle source

Create a sql, basen on a given Dataset

USE:

ds={'id'=>[1,2,3,4,5].to_vector,'name'=>%w{Alex Peter Susan Mary John}.to_vector}.to_dataset
Statsample::Database.create_sql(ds,'names')
 ==>"CREATE TABLE names (id INTEGER,\n name VARCHAR (255)) CHARACTER SET=UTF8;"

# File lib/statsample/converters.rb, line 54
def create_sql(ds,table,charset="UTF8")
  sql="CREATE TABLE #{table} ("
  fields=ds.fields.collect{|f|
      v=ds[f]
      f+" "+v.db_type
  }
  sql+fields.join(",\n ")+") CHARACTER SET=#{charset};"
end

insert(ds, dbh, table) click to toggle source

Insert each case of the Dataset on the selected table

USE:

ds={'id'=>[1,2,3].to_vector, 'name'=>["a","b","c"].to_vector}.to_dataset
dbh = DBI.connect("DBI:Mysql:database:localhost", "user", "password")
Statsample::Database.insert(ds,dbh,"test")

# File lib/statsample/converters.rb, line 39
def insert(ds, dbh, table)
  require 'dbi'            
  query="INSERT INTO #{table} ("+ds.fields.join(",")+") VALUES ("+((["?"]*ds.fields.size).join(","))+")"
  sth=dbh.prepare(query)
  ds.each_array{|c| sth.execute(*c) }
  return true
end

read(dbh,query) click to toggle source

Read a database query and returns a Dataset

USE:

dbh = DBI.connect("DBI:Mysql:database:localhost", "user", "password")
Statsample.read(dbh, "SELECT * FROM test")

# File lib/statsample/converters.rb, line 13
def read(dbh,query)
  require 'dbi'
  sth=dbh.execute(query)
  vectors={}
  fields=[]
  sth.column_info.each {|c|
      vectors[c['name']]=Statsample::Vector.new([])
      vectors[c['name']].name=c['name']
      vectors[c['name']].type= (c['type_name']=='INTEGER' or c['type_name']=='DOUBLE') ? :scale : :nominal
      fields.push(c['name'])
  }
  ds=Statsample::Dataset.new(vectors,fields)
  sth.fetch do |row|
      ds.add_case(row.to_a, false )
  end
  ds.update_valid_data
  ds
end