Class: QuickETL::DBO

Inherits:
QuickETLObject show all
Defined in:
lib/quick_etl_dbo.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from QuickETLKernel

#command_line_arg, #command_line_array_arg, #command_line_boolean_arg, #default_delimiter, #display_general_info, #display_usage_instructions, #get_array_property, #get_property, #home_dir, #line_value, #load_properties, #parse_qif_files, #project_author, #project_copyright, #project_date, #project_embedded_comment, #project_license, #project_name, #project_version_number, #project_year, #read_as_ascii_lines, #read_lines, #startup, #strip_lines, #tokenize, #usage_instructions, #write_file, #write_lines

Constructor Details

#initialize ⇒ DBO

Returns a new instance of DBO.



19
20
21
 
# File 'lib/quick_etl_dbo.rb', line 19

def initialize()
  @db_filename = "#{home_dir}/#{QuickETL::DATABASE_FILE}"
end

Instance Attribute Details

#db ⇒ Object

Returns the value of attribute db.



17
18
19
 
# File 'lib/quick_etl_dbo.rb', line 17

def db
  @db
end

#db_filename ⇒ Object

Returns the value of attribute db_filename.



17
18
19
 
# File 'lib/quick_etl_dbo.rb', line 17

def db_filename
  @db_filename
end

Instance Method Details

#create_transactions_ddl ⇒ Object 



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/quick_etl_dbo.rb', line 51

def create_transactions_ddl      
  s =  "create table transactions ( \n" 
  s << "  id              INTEGER PRIMARY KEY, \n" 
  s << "  account_owner   TEXT, \n"       
  s << "  account_name    TEXT, \n"  
  s << "  account_type    TEXT, \n"  
  s << "  tran_date       TEXT, \n"  
  s << "  cleared_status  TEXT, \n"  
  s << "  item_number     INTEGER, \n"  
  s << "  amount          REAL, \n" 
  s << "  payee           TEXT, \n"  
  s << "  category        TEXT, \n"         
  s << "  memo            TEXT, \n"  
  s << "  line_number     INTEGER \n" 
  s << ");" 
  s
end

#drop_and_create_database ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/quick_etl_dbo.rb', line 35

def drop_and_create_database
  open_database
  puts @db.execute(drop_transactions_ddl);
  puts @db.execute(create_transactions_ddl);  
  puts "\nDatabase has been created - #{@db_filename}"
  puts "Database schema is shown below:"
  puts ""
  puts `sqlite3 #{@db_filename} VACUUM`      
  puts `sqlite3 #{@db_filename} .schema`
  puts ""
end

#drop_transactions_ddl ⇒ Object 



47
48
49
 
# File 'lib/quick_etl_dbo.rb', line 47

def drop_transactions_ddl   
  "drop table if exists transactions;"
end

#load_database ⇒ Object 



69
70
71
72
73
74
75
 
# File 'lib/quick_etl_dbo.rb', line 69

def load_database
  command_file = "#{home_dir}/#{LOAD_COMMAND_FILE}"
  lines =  Array.new
  lines << ".import #{home_dir}/quick-etl.csv transactions"
  write_lines(command_file, lines)
  puts `sqlite3 #{@db_filename} < #{command_file}`
end

#open_database ⇒ Object 



23
24
25
 
# File 'lib/quick_etl_dbo.rb', line 23

def open_database
  @db = SQLite3::Database.new(@db_filename)
end

#query_all_transactions ⇒ Object 



77
78
79
80
81
82
83
84
 
# File 'lib/quick_etl_dbo.rb', line 77

def query_all_transactions
  command_file = "#{home_dir}/#{QUERY_ALL_FILE}"
  results_file = "#{home_dir}/output.txt"
  lines =  Array.new
  lines << "select * from transactions;"
  write_lines(command_file, lines)
  puts `sqlite3 #{@db_filename} < #{command_file} > #{results_file}`
end

#show_ddl ⇒ Object 



27
28
29
30
31
32
33
 
# File 'lib/quick_etl_dbo.rb', line 27

def show_ddl
  puts "\nThe following DDL (SQL) is used to create database #{@db_filename}"
  puts ""
  puts drop_transactions_ddl
  puts create_transactions_ddl  
  puts ""
end