Class: LWAC::MySQLDatabaseConnection

Inherits:
DatabaseConnection show all
Defined in:
lib/lwac/server/db_conn.rb

Overview

TODO

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(config = {}) ⇒ MySQLDatabaseConnection

Returns a new instance of MySQLDatabaseConnection.



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/lwac/server/db_conn.rb', line 43

def initialize(config = {})

  begin
    require 'mysql2'
  rescue LoadError
    $log.fatal "Your current configuration is trying to use the 'mysql2' gem, but it is not installed."
    $log.fatal "To install, run 'gem install mysql2 --version \"~> 0.3\"'"
    raise "Gem not found."
  end


  @transaction        = false
  @transaction_limit  = config[:transaction_limit] || 0
  @transaction_count  = 0
  
  
  @db = Mysql2::Client.new( config )
  @db.query_options.merge!(:as => :array)

end

Class Method Details

.create_database(config) ⇒ Object

MUST yield for schema to be applied



136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/lwac/server/db_conn.rb', line 136

def self.create_database( config )

  # Backup...
  base = config[:database]
  raise "No database name set in MySQL database configuration" if not base
  config[:database] = nil

  # Connect
  db = Mysql2::Client.new( config )

  # Create and use
  db.query("CREATE DATABASE `#{db.escape(base.to_s)}`;")
  db.query("USE `#{db.escape(base.to_s)}`;")

  # Restore
  config[:database] = base  

  # And quit.
  db.close
end

.database_exists?(config) ⇒ Boolean

Returns:

  • (Boolean) 


157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
 
# File 'lib/lwac/server/db_conn.rb', line 157

def self.database_exists?( config )
  exists = false;

  # Backup
  base = config[:database]
  config[:database] = nil;

  # Connect
  db = Mysql2::Client.new(config)

  begin
    db.query("USE `#{db.escape(base.to_s)}`;");
    exists = true
  rescue Mysql2::Error => e
    raise e if not e.to_s =~ /Unknown database/
  end

  # Restore
  config[:database] = base

  # Close
  db.close

  return exists
end

Instance Method Details

#close ⇒ Object 



64
65
66
 
# File 'lib/lwac/server/db_conn.rb', line 64

def close
  @db.close
end

#delete(table_name, where_conditions = "") ⇒ Object

Delete all items from a table



109
110
111
112
 
# File 'lib/lwac/server/db_conn.rb', line 109

def delete(table_name, where_conditions = "")
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0
  return execute("delete from `#{table_name}` #{where_conditions};")
end

#execute(sql, trans = true) ⇒ Object

Execute a raw SQL statement Set trans = false to force and disable transactions



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/lwac/server/db_conn.rb', line 117

def execute(sql, trans=true)
  start_transaction if trans
  end_transaction if @transaction and not trans 

  $log.debug "MySQL: #{sql}"


  # run the query
  #puts "<#{sql.split()[0]}, #{trans}, #{@transaction}>"
  res = @db.query(sql)
  @transaction_count += 1 if @transaction

  # end the transaction if we have called enough statements
  end_transaction if @transaction_count > @transaction_limit

  return res.to_a
end

#insert(table_name, value_hash) ⇒ Object

Run an SQL insert call on a given table, with a hash of data.



70
71
72
73
74
75
76
77
 
# File 'lib/lwac/server/db_conn.rb', line 70

def insert(table_name, value_hash)
  raise "Attempt to insert 0 values into table #{table_name}" if value_hash.length == 0

  escaped_values = [] 
  value_hash.each{|k, v| escaped_values << escape(v) }

  return execute("insert into `#{table_name}` (#{value_hash.keys.join(",")}) values (#{escaped_values.join(",")});")
end

#select(table_name, fields_list, where_conditions = "") ⇒ Object

Select certain fields from a database, with certain where field == value.

Returns a record set (SQlite3)

table_name is the name of the table from which to select. fields_list is an array of fields to return in the record set where_conditions is a string of where conditions. Careful to escape!!



102
103
104
105
 
# File 'lib/lwac/server/db_conn.rb', line 102

def select(table_name, fields_list, where_conditions = "")
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0
  return execute("select #{fields_list.join(",")} from `#{table_name}` #{where_conditions};")
end

#update(table_name, value_hash, where_conditions = "") ⇒ Object

Run an SQL insert call on a given table, with a hash of data.



81
82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/lwac/server/db_conn.rb', line 81

def update(table_name, value_hash, where_conditions = "")
  # Compute the WHERE clause.
  where_conditions = "where #{where_conditions}" if where_conditions.length > 0

  # Work out the SET clause
  escaped_values = []
  value_hash.each{|k, v| 
    escaped_values << "#{k}='#{escape(v)}'" 
  }

  return execute("update `#{table_name}` set #{escaped_values.join(", ")} #{where_conditions};")
end