Class: Mysql

Inherits:
Object
  • Object
show all
Defined in:
lib/mysql.rb,
lib/mysql/charset.rb

Overview

MySQL connection class.

Examples:

my = Mysql.connect('hostname', 'user', 'password', 'dbname')
res = my.query 'select col1,col2 from tbl where id=123'
res.each do |c1, c2|
  p c1, c2
end

Defined Under Namespace

Classes: Charset, Field, Result, ResultBase, Stmt, Time

Constant Summary collapse

VERSION =

Version number of this library

20913
MYSQL_UNIX_PORT =

UNIX domain socket filename

"/tmp/mysql.sock"
MYSQL_TCP_PORT =

TCP socket port number

3306

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initializeMysql

Returns a new instance of Mysql.


85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
# File 'lib/mysql.rb', line 85

def initialize
  @fields = nil
  @protocol = nil
  @charset = nil
  @connect_timeout = nil
  @read_timeout = nil
  @write_timeout = nil
  @init_command = nil
  @sqlstate = "00000"
  @query_with_result = true
  @host_info = nil
  @last_error = nil
  @result_exist = false
  @local_infile = nil
end

Instance Attribute Details

#charsetMysql::Charset

Returns character set of MySQL connection.

Returns:


29
30
31
# File 'lib/mysql.rb', line 29

def charset
  @charset
end

#query_with_resultBoolean

Returns if true, #query return Result.

Returns:


34
35
36
# File 'lib/mysql.rb', line 34

def query_with_result
  @query_with_result
end

Class Method Details

.client_infoString

Returns client version. This value is dummy for MySQL/Ruby compatibility.

Returns:

  • (String)

    client version. This value is dummy for MySQL/Ruby compatibility.


73
74
75
# File 'lib/mysql.rb', line 73

def client_info
  "5.0.0"
end

.client_versionInteger

Returns client version. This value is dummy for MySQL/Ruby compatibility.

Returns:

  • (Integer)

    client version. This value is dummy for MySQL/Ruby compatibility.


79
80
81
# File 'lib/mysql.rb', line 79

def client_version
  50000
end

.escape_string(str) ⇒ String

Escape special character in string.

Parameters:

  • str (String)

Returns:

  • (String)

59
60
61
62
63
64
65
66
67
68
69
# File 'lib/mysql.rb', line 59

def escape_string(str)
  str.gsub(/[\0\n\r\\\'\"\x1a]/) do |s|
    case s
    when "\0" then "\\0"
    when "\n" then "\\n"
    when "\r" then "\\r"
    when "\x1a" then "\\Z"
    else "\\#{s}"
    end
  end
end

.get_client_infoString

Returns client version. This value is dummy for MySQL/Ruby compatibility.

Returns:

  • (String)

    client version. This value is dummy for MySQL/Ruby compatibility.


76
77
78
# File 'lib/mysql.rb', line 76

def client_info
  "5.0.0"
end

.get_client_versionInteger

Returns client version. This value is dummy for MySQL/Ruby compatibility.

Returns:

  • (Integer)

    client version. This value is dummy for MySQL/Ruby compatibility.


82
83
84
# File 'lib/mysql.rb', line 82

def client_version
  50000
end

.initMysql

Make Mysql object without connecting.

Returns:


39
40
41
42
43
# File 'lib/mysql.rb', line 39

def init
  my = self.allocate
  my.instance_eval{initialize}
  my
end

.new(*args) ⇒ Mysql Also known as: real_connect, connect

Make Mysql object and connect to mysqld.

Parameters:

  • args

    same as arguments for #connect.

Returns:


48
49
50
51
# File 'lib/mysql.rb', line 48

def new(*args)
  my = self.init
  my.connect(*args)
end

.quoteString

Escape special character in string.

Parameters:

  • str (String)

Returns:

  • (String)

70
71
72
73
74
75
76
77
78
79
80
# File 'lib/mysql.rb', line 70

def escape_string(str)
  str.gsub(/[\0\n\r\\\'\"\x1a]/) do |s|
    case s
    when "\0" then "\\0"
    when "\n" then "\\n"
    when "\r" then "\\r"
    when "\x1a" then "\\Z"
    else "\\#{s}"
    end
  end
end

Instance Method Details

#affected_rowsInteger

Returns number of affected records by insert/update/delete.

Returns:

  • (Integer)

    number of affected records by insert/update/delete.


282
283
284
# File 'lib/mysql.rb', line 282

def affected_rows
  @protocol ? @protocol.affected_rows : 0
end

#autocommit(flag) ⇒ Mysql

Set autocommit mode

Parameters:

  • flag (Boolean)

Returns:


510
511
512
513
# File 'lib/mysql.rb', line 510

def autocommit(flag)
  query "set autocommit=#{flag ? 1 : 0}"
  self
end

#character_set_nameString

Returns charset name.

Returns:

  • (String)

    charset name


226
227
228
# File 'lib/mysql.rb', line 226

def character_set_name
  @charset.name
end

#client_infoString Also known as: get_client_info

Returns client version.

Returns:

  • (String)

    client version


202
203
204
# File 'lib/mysql.rb', line 202

def client_info
  self.class.client_info
end

#client_versionInteger Also known as: get_client_version

Returns client version.

Returns:

  • (Integer)

    client version


208
209
210
# File 'lib/mysql.rb', line 208

def client_version
  self.class.client_version
end

#closeMysql

Disconnect from mysql.

Returns:


126
127
128
129
130
131
132
# File 'lib/mysql.rb', line 126

def close
  if @protocol
    @protocol.quit_command
    @protocol = nil
  end
  return self
end

#close!Mysql

Disconnect from mysql without QUIT packet.

Returns:


136
137
138
139
140
141
142
# File 'lib/mysql.rb', line 136

def close!
  if @protocol
    @protocol.close
    @protocol = nil
  end
  return self
end

#commitMysql

Commit transaction

Returns:


495
496
497
498
# File 'lib/mysql.rb', line 495

def commit
  query 'commit'
  self
end

#connect(host = nil, user = nil, passwd = nil, db = nil, port = nil, socket = nil, flag = 0) ⇒ Object Also known as: real_connect

Connect to mysqld.

Parameters:

  • host (String / nil) (defaults to: nil)

    hostname mysqld running

  • user (String / nil) (defaults to: nil)

    username to connect to mysqld

  • passwd (String / nil) (defaults to: nil)

    password to connect to mysqld

  • db (String / nil) (defaults to: nil)

    initial database name

  • port (Integer / nil) (defaults to: nil)

    port number (used if host is not 'localhost' or nil)

  • socket (String / nil) (defaults to: nil)

    socket file name (used if host is 'localhost' or nil)

  • flag (Integer / nil) (defaults to: 0)

    connection flag. Mysql::CLIENT_* ORed

Returns:

  • self


110
111
112
113
114
115
116
117
118
119
120
121
# File 'lib/mysql.rb', line 110

def connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, socket=nil, flag=0)
  if flag & CLIENT_COMPRESS != 0
    warn 'unsupported flag: CLIENT_COMPRESS' if $VERBOSE
    flag &= ~CLIENT_COMPRESS
  end
  @protocol = Protocol.new host, port, socket, @connect_timeout, @read_timeout, @write_timeout
  @protocol.authenticate user, passwd, db, (@local_infile ? CLIENT_LOCAL_FILES : 0) | flag, @charset
  @charset ||= @protocol.charset
  @host_info = (host.nil? || host == "localhost") ? 'Localhost via UNIX socket' : "#{host} via TCP/IP"
  query @init_command if @init_command
  return self
end

#errnoInteger

Returns last error number.

Returns:

  • (Integer)

    last error number


231
232
233
# File 'lib/mysql.rb', line 231

def errno
  @last_error ? @last_error.errno : 0
end

#errorString

Returns last error message.

Returns:

  • (String)

    last error message


236
237
238
# File 'lib/mysql.rb', line 236

def error
  @last_error && @last_error.error
end

#escape_string(str) ⇒ Object Also known as: quote

Escape special character in MySQL.

In Ruby 1.8, this is not safe for multibyte charset such as 'SJIS'. You should use place-holder in prepared-statement. return [String]

Parameters:

  • str (String)

193
194
195
196
197
198
# File 'lib/mysql.rb', line 193

def escape_string(str)
  if not defined? Encoding and @charset.unsafe
    raise ClientError, 'Mysql#escape_string is called for unsafe multibyte charset'
  end
  self.class.escape_string str
end

#field_countInteger

Returns number of columns for last query.

Returns:

  • (Integer)

    number of columns for last query


246
247
248
# File 'lib/mysql.rb', line 246

def field_count
  @fields.size
end

#host_infoString Also known as: get_host_info

Returns connection type.

Returns:

  • (String)

    connection type


251
252
253
# File 'lib/mysql.rb', line 251

def host_info
  @host_info
end

#infoString

Returns information for last query.

Returns:

  • (String)

    information for last query


277
278
279
# File 'lib/mysql.rb', line 277

def info
  @protocol && @protocol.message
end

#insert_idInteger

Returns latest auto_increment value.

Returns:

  • (Integer)

    latest auto_increment value


287
288
289
# File 'lib/mysql.rb', line 287

def insert_id
  @protocol ? @protocol.insert_id : 0
end

#kill(pid) ⇒ Mysql

Kill query.

Parameters:

  • pid (Integer)

    thread id

Returns:


299
300
301
302
303
# File 'lib/mysql.rb', line 299

def kill(pid)
  check_connection
  @protocol.kill_command pid
  self
end

#list_dbs(db = nil) ⇒ Array<String>

database list.

Parameters:

  • db (String) (defaults to: nil)

    database name that may contain wild card.

Returns:

  • (Array<String>)

    database list


308
309
310
311
# File 'lib/mysql.rb', line 308

def list_dbs(db=nil)
  db &&= db.gsub(/[\\\']/){"\\#{$&}"}
  query(db ? "show databases like '#{db}'" : "show databases").map(&:first)
end

#list_fields(table, field = nil) ⇒ Mysql::Result

Returns Mysql::Result object that is empty. Use fetch_fields to get list of fields.

Parameters:

  • table (String)

    table name.

  • field (String) (defaults to: nil)

    field name that may contain wild card.

Returns:


422
423
424
425
426
427
428
429
430
431
432
# File 'lib/mysql.rb', line 422

def list_fields(table, field=nil)
  check_connection
  begin
    fields = @protocol.field_list_command table, field
    return Result.new fields
  rescue ServerError => e
    @last_error = e
    @sqlstate = e.sqlstate
    raise
  end
end

#list_processesMysql::Result

Returns containing process list.

Returns:


435
436
437
438
439
440
# File 'lib/mysql.rb', line 435

def list_processes
  check_connection
  @fields = @protocol.process_info_command
  @result_exist = true
  store_result
end

#list_tables(table = nil) ⇒ Array<String>

Note:

for Ruby 1.8: This is not multi-byte safe. Don't use for multi-byte charset such as cp932.

Returns list of table name.

Parameters:

  • table (String) (defaults to: nil)

    database name that may contain wild card.

Returns:

  • (Array<String>)

    list of table name.


445
446
447
448
# File 'lib/mysql.rb', line 445

def list_tables(table=nil)
  q = table ? "show tables like '#{quote table}'" : "show tables"
  query(q).map(&:first)
end

#more_resultsBoolean Also known as: more_results?

Returns true if multiple queries are specified and unexecuted queries exists.

Returns:

  • (Boolean)

    true if multiple queries are specified and unexecuted queries exists.


382
383
384
# File 'lib/mysql.rb', line 382

def more_results
  @protocol.server_status & SERVER_MORE_RESULTS_EXISTS != 0
end

#next_resultBoolean

execute next query if multiple queries are specified.

Returns:

  • (Boolean)

    true if next query exists.


389
390
391
392
393
394
395
396
397
398
399
# File 'lib/mysql.rb', line 389

def next_result
  return false unless more_results
  check_connection
  @fields = nil
  nfields = @protocol.get_result
  if nfields
    @fields = @protocol.retr_fields nfields
    @result_exist = true
  end
  return true
end

#options(opt, value = nil) ⇒ Mysql

Set option for connection.

Available options:

Mysql::INIT_COMMAND, Mysql::OPT_CONNECT_TIMEOUT, Mysql::OPT_READ_TIMEOUT,
Mysql::OPT_WRITE_TIMEOUT, Mysql::SET_CHARSET_NAME

Parameters:

  • opt (Integer)

    option

  • value (Integer) (defaults to: nil)

    option value that is depend on opt

Returns:


152
153
154
155
156
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
182
183
184
185
# File 'lib/mysql.rb', line 152

def options(opt, value=nil)
  case opt
  when Mysql::INIT_COMMAND
    @init_command = value.to_s
#    when Mysql::OPT_COMPRESS
  when Mysql::OPT_CONNECT_TIMEOUT
    @connect_timeout = value
#    when Mysql::GUESS_CONNECTION
  when Mysql::OPT_LOCAL_INFILE
    @local_infile = value
#    when Mysql::OPT_NAMED_PIPE
#    when Mysql::OPT_PROTOCOL
  when Mysql::OPT_READ_TIMEOUT
    @read_timeout = value.to_i
#    when Mysql::OPT_RECONNECT
#    when Mysql::SET_CLIENT_IP
#    when Mysql::OPT_SSL_VERIFY_SERVER_CERT
#    when Mysql::OPT_USE_EMBEDDED_CONNECTION
#    when Mysql::OPT_USE_REMOTE_CONNECTION
  when Mysql::OPT_WRITE_TIMEOUT
    @write_timeout = value.to_i
#    when Mysql::READ_DEFAULT_FILE
#    when Mysql::READ_DEFAULT_GROUP
#    when Mysql::REPORT_DATA_TRUNCATION
#    when Mysql::SECURE_AUTH
#    when Mysql::SET_CHARSET_DIR
  when Mysql::SET_CHARSET_NAME
    @charset = Charset.by_name value.to_s
#    when Mysql::SHARED_MEMORY_BASE_NAME
  else
    warn "option not implemented: #{opt}" if $VERBOSE
  end
  self
end

#pingMysql

Check whether the connection is available.

Returns:


452
453
454
455
456
# File 'lib/mysql.rb', line 452

def ping
  check_connection
  @protocol.ping_command
  self
end

#prepare(str) ⇒ Mysql::Stmt

Parse prepared-statement.

Parameters:

  • str (String)

    query string

Returns:


404
405
406
407
408
# File 'lib/mysql.rb', line 404

def prepare(str)
  st = Stmt.new @protocol, @charset
  st.prepare str
  st
end

#proto_infoInteger Also known as: get_proto_info

Returns protocol version.

Returns:

  • (Integer)

    protocol version


257
258
259
# File 'lib/mysql.rb', line 257

def proto_info
  Mysql::Protocol::VERSION
end

#query(str) {|Mysql::Result| ... } ⇒ Mysql::Result, ... Also known as: real_query

Execute query string.

Examples:

my.query("select 1,NULL,'abc'").fetch  # => [1, nil, "abc"]

Parameters:

  • str (String)

    Query.

Yields:

Returns:


321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
# File 'lib/mysql.rb', line 321

def query(str, &block)
  check_connection
  @fields = nil
  begin
    nfields = @protocol.query_command str
    if nfields
      @fields = @protocol.retr_fields nfields
      @result_exist = true
    end
    if block
      while true
        block.call store_result if @fields
        break unless next_result
      end
      return self
    end
    if @query_with_result
      return @fields ? store_result : nil
    else
      return self
    end
  rescue ServerError => e
    @last_error = e
    @sqlstate = e.sqlstate
    raise
  end
end

#refresh(op) ⇒ Mysql

Flush tables or caches.

Parameters:

  • op (Integer)

    operation. Use Mysql::REFRESH_* value.

Returns:


461
462
463
464
465
# File 'lib/mysql.rb', line 461

def refresh(op)
  check_connection
  @protocol.refresh_command op
  self
end

#reloadMysql

Reload grant tables.

Returns:


469
470
471
# File 'lib/mysql.rb', line 469

def reload
  refresh Mysql::REFRESH_GRANT
end

#rollbackMysql

Rollback transaction

Returns:


502
503
504
505
# File 'lib/mysql.rb', line 502

def rollback
  query 'rollback'
  self
end

#select_db(db) ⇒ Mysql

Select default database

Returns:


475
476
477
478
# File 'lib/mysql.rb', line 475

def select_db(db)
  query "use #{db}"
  self
end

#server_infoString Also known as: get_server_info

Returns server version.

Returns:

  • (String)

    server version


263
264
265
266
# File 'lib/mysql.rb', line 263

def server_info
  check_connection
  @protocol.server_info
end

#server_versionInteger Also known as: get_server_version

Returns server version.

Returns:

  • (Integer)

    server version


270
271
272
273
# File 'lib/mysql.rb', line 270

def server_version
  check_connection
  @protocol.server_version
end

#set_server_option(opt) ⇒ Mysql

Set server option.

Parameters:

  • opt (Integer)

    OPTION_MULTI_STATEMENTS_ON or OPTION_MULTI_STATEMENTS_OFF

Returns:


375
376
377
378
379
# File 'lib/mysql.rb', line 375

def set_server_option(opt)
  check_connection
  @protocol.set_option_command opt
  self
end

#shutdown(level = 0) ⇒ Mysql

shutdown server.

Returns:


482
483
484
485
486
# File 'lib/mysql.rb', line 482

def shutdown(level=0)
  check_connection
  @protocol.shutdown_command level
  self
end

#sqlstateString

Returns sqlstate for last error.

Returns:

  • (String)

    sqlstate for last error


241
242
243
# File 'lib/mysql.rb', line 241

def sqlstate
  @last_error ? @last_error.sqlstate : "00000"
end

#statString

Returns statistics message.

Returns:

  • (String)

    statistics message


489
490
491
# File 'lib/mysql.rb', line 489

def stat
  @protocol ? @protocol.statistics_command : 'MySQL server has gone away'
end

#store_resultMysql::Result

Get all data for last query if query_with_result is false.

Returns:

Raises:

  • (ClientError)

352
353
354
355
356
357
358
# File 'lib/mysql.rb', line 352

def store_result
  check_connection
  raise ClientError, 'invalid usage' unless @result_exist
  res = Result.new @fields, @protocol
  @result_exist = false
  res
end

#thread_idInteger

Returns Thread ID.

Returns:

  • (Integer)

    Thread ID


361
362
363
364
# File 'lib/mysql.rb', line 361

def thread_id
  check_connection
  @protocol.thread_id
end

#use_resultMysql::Result

Use result of query. The result data is retrieved when you use Mysql::Result#fetch.

Returns:


368
369
370
# File 'lib/mysql.rb', line 368

def use_result
  store_result
end

#warning_countInteger

Returns number of warnings for previous query.

Returns:

  • (Integer)

    number of warnings for previous query


292
293
294
# File 'lib/mysql.rb', line 292

def warning_count
  @protocol ? @protocol.warning_count : 0
end