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.



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

def initialize dbname="bugzy.sqlite"
  unless File.exists? dbname
    puts "#{dbname} does not exist. Try 'init' or '-d path' or '--help'" 
    exit -1
  end
  @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.



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

def db
  @db
end

Instance Method Details

#dummy ⇒ Object 



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

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 



108
109
110
111
 
# File 'lib/bugzyrb/common/db.rb', line 108

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:

  • sql (String) —

    statement

Returns:

  • (Array, nil) —

    array if rows, else nil



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

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



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

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 



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

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 



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

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



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

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 



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

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 



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

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 



119
120
121
122
123
 
# File 'lib/bugzyrb/common/db.rb', line 119

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)



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

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



139
140
141
142
 
# File 'lib/bugzyrb/common/db.rb', line 139

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



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

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