Class: Database::DB

Inherits:
Object
  • Object
show all
Defined in:
lib/bugzyrb/common/db.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(dbname = "bugzy.sqlite") ⇒ DB

Returns a new instance of DB.



10
11
12
13
14
15
16
 
# File 'lib/bugzyrb/common/db.rb', line 10

def initialize dbname="bugzy.sqlite"
  raise "#{dbname} does not exist. Try --help" unless File.exists? dbname
  @db = SQLite3::Database.new(dbname)
  $now = Time.now
  $num = rand(100)
  $default_user = ENV['LOGNAME'] || ENV['USER']
end

Instance Attribute Details

#db ⇒ Object

Returns the value of attribute db.



8
9
10
 
# File 'lib/bugzyrb/common/db.rb', line 8

def db
  @db
end

Instance Method Details

#dummy ⇒ Object 



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/bugzyrb/common/db.rb', line 186

def dummy
   id = $num
   status = "open" 
   severity = "critical" 
   type = "bug" 
   assigned_to = "rahul" 
   start_date = $now
   due_date = $now
   comment_count = 0
   priority = "P1" 
   title = "some title" 
   description = "Some long text fro this bug too" 
   fix = nil #"Some long text" 
   date_created = $now
   date_modified = $now
   created_by = $default_user
   bugs_insert(status, severity, type, assigned_to, start_date, due_date, comment_count, priority, title, description, fix, created_by)
  #bugs_insert(id, status, severity, type, assigned_to, start_date, due_date, comment_count, priority, title, description, fix, date_created, date_modified)
end

#max_bug_id(table = "bugs") ⇒ Object 



106
107
108
109
 
# File 'lib/bugzyrb/common/db.rb', line 106

def max_bug_id table="bugs"
  id = @db.get_first_value( "select max(id) from #{table};")
  return id
end

#run(text) ⇒ Array?

returns many rows 2011-09-21 Please specify results_as_hash or typr_translation before calling as it crashes in some cases and works in others.

Parameters:

Returns:

  • (Array, nil) —

    array if rows, else nil



40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/bugzyrb/common/db.rb', line 40

def run text
  #puts " --- #{text} ---  "
  #@db.type_translation = true # here it works, if i remove then long date is shown
  #@db.results_as_hash = true # 2011-09-21 
  rows = []
  @db.execute( text ) do |row|
    if block_given?
      yield row
    else
      rows << row
    end
  end
  return nil if rows.empty?
  return rows
end

#select(table) ⇒ Array?

returns many rows

Parameters:

  • id (Fixnum) —

    for table with 1:n rows

Returns:

  • (Array, nil) —

    array if rows, else nil



21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/bugzyrb/common/db.rb', line 21

def select table
  #puts " --- #{table} ---  "
  @db.type_translation = true
  rows = []
  @db.execute( "select * from #{table} " ) do |row|
    if block_given?
      yield row
    else
      rows << row
    end
  end
  return nil if rows.empty?
  return rows
end

#select_where(table, *wherecond) ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/bugzyrb/common/db.rb', line 55

def select_where table, *wherecond
  #puts " --- #{table} --- #{wherecond} "
  #@db.type_translation = true # causing errors in sqlite3 columns() not found for Array
  wherestr = nil
  rows = []
  if wherecond and !wherecond.empty?
    fields, values = separate_field_values wherecond
    #wherestr = "" unless wherestr
    wherestr = " where #{fields.join(" and ")} "
    if wherestr
      #puts " wherestr #{wherestr}, #{values} "
      #stmt = @db.prepare("select * from #{table} #{wherestr} ", *values)
      @db.execute( "select * from #{table} #{wherestr}", *values  ) do |row| 
        if block_given?
          yield row
        else
          rows << row
        end
      end
    end
  end
  return nil if rows.empty?
  return rows
end

#separate_field_values(array) ⇒ Object 



174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/bugzyrb/common/db.rb', line 174

def separate_field_values array
  fields = []
  values = []
  array.each_with_index do |f, i| 
    if i % 2 == 0
      fields << "#{f} = ?"
    else
      values << f
    end
  end
  return fields, values
end

#sql_bugs_update_mult(id, *fv) ⇒ Array

update a row from bugs based on id, can give multiple fieldnames and values

Examples:

sql_bugs_update_mult 9, :name, “Roger”, :age, 29, :country, “SWI”

Parameters:

  • id (Fixnum) —

    unique key

Returns:

  • (Array) —

    alternating fieldname and value



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/bugzyrb/common/db.rb', line 146

def sql_bugs_update_mult id, *fv
  fields = []
  values = []
  fv << "date_modified"
  fv << $now.to_s
  fv.each_with_index do |f, i| 
    if i % 2 == 0
      fields << "#{f} = ?"
    else
      values << f
    end
  end

  print( "update bugs set #{fields.join(" ,")} where id = ?", *values, id)
  @db.execute( "update bugs set #{fields.join(" ,")} where id = ?", [*values, id])
end

#sql_comments_insert(id, comment, created_by = $default_user) ⇒ Object 



110
111
112
113
114
115
116
 
# File 'lib/bugzyrb/common/db.rb', line 110

def sql_comments_insert id, comment, created_by = $default_user
  #date_created = date_cr | Time.now
  # 2010-09-12 11:42 added [ ]     due to change in sqlite3 1.3.x
  @db.execute("insert into comments (id, comment, created_by) values (?,?,?)", [id, comment, created_by] ) 
  rowid = @db.get_first_value( "select last_insert_rowid();")
  return rowid
end

#sql_delete_bug(id) ⇒ Object 



122
123
124
125
126
127
128
 
# File 'lib/bugzyrb/common/db.rb', line 122

def sql_delete_bug id
  #message  "deleting #{id}"
  puts  "deleting #{id}"
  @db.execute( "delete from bugs where id = ?", id )
  @db.execute( "delete from comments where id = ?", id )
  @db.execute( "delete from log where id = ?", id )
end

#sql_logs_insert(id, field, log, created_by = $default_user) ⇒ Object 



117
118
119
120
121
 
# File 'lib/bugzyrb/common/db.rb', line 117

def sql_logs_insert id, field, log, created_by = $default_user
  #date_created = date_cr | Time.now
  # 2010-09-12 11:42 added [ ]     due to change in sqlite3 1.3.x
  @db.execute("insert into log (id, field, log, created_by) values (?,?,?,?)", [id, field, log, created_by] ) 
end

#sql_select_rowid(table, id) ⇒ Array

return a single row from table based on rowid

Parameters:

  • table (String) —

    name

  • rowid (Fixnum)

Returns:

  • (Array) —

    resultset (based on arrayfield)



167
168
169
170
171
172
 
# File 'lib/bugzyrb/common/db.rb', line 167

def sql_select_rowid table, id
  @db.results_as_hash = true # 2011-09-21 
  return nil if id.nil? or table.nil?
  row = @db.get_first_row( "select * from #{table} where rowid = ?", id )
  return row
end

#sql_update(table, id, field, value) ⇒ Object

update a row from bugs based on id, giving one fieldname and value

Examples:

sql_update “bugs”, 9, :name, “Roger”

Parameters:

  • id (Fixnum) —

    unique key

  • fieldname (String)
  • value (String) —

    to update



137
138
139
140
 
# File 'lib/bugzyrb/common/db.rb', line 137

def sql_update table, id, field, value
  # 2010-09-12 11:42 added to_s to now, due to change in sqlite3 1.3.x
  @db.execute( "update #{table} set #{field} = ?, date_modified = ? where id = ?", [value,$now.to_s, id])
end

#table_insert_hash(table, hash) ⇒ Fixnum

takes a hash and creates an insert statement for table and inserts data. Advantage is that as we add columns, this will add the column to the insert, so we don’t need to keep modifying in many places.

Parameters:

  • name (String) —

    of table to insert data into

  • values (Hash) —

    to insert, keys must be table column names

Returns:

  • (Fixnum) —

    newly inserted rowid



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/bugzyrb/common/db.rb', line 85

def table_insert_hash table, hash
  str = "INSERT INTO #{table} ("
  qstr = [] # question marks
  fields = [] # field names
  bind_vars = [] # values to insert
  hash.each_pair { |name, val| 
    fields << name
    bind_vars << val
    qstr << "?"
  }
  fstr = fields.join(",")
  str << fstr
  str << ") values ("
  str << qstr.join(",")
  str << ")"
  #puts str
  # 2010-09-12 11:42 removed splat due to change in sqlite3 1.3.x
  @db.execute(str, bind_vars)
  rowid = @db.get_first_value( "select last_insert_rowid();")
  return rowid
end