Module: Statsample::Database

Defined in:
lib/statsample/converters.rb

Overview

Create and dumps Datasets on a database

Class Method Summary collapse

Class Method Details

.create_sql(ds, table, charset = "UTF8") ⇒ Object

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;"



54
55
56
57
58
59
60
61
 
# 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) ⇒ Object

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")



39
40
41
42
43
44
45
 
# 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) ⇒ Object

Read a database query and returns a Dataset

USE:

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



13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# 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') ? :numeric : :object
      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