Class: Mysql

Inherits:
Object
  • Object
show all
Defined in:
lib/mysql.rb,
lib/mysql/error.rb,
lib/mysql/compat.rb,
lib/mysql/charset.rb,
lib/mysql/protocol.rb,
lib/mysql/constants.rb

Overview

Copyright © 2003-2008 TOMITA Masahiro [email protected]

Defined Under Namespace

Classes: Charset, ClientError, Error, Field, Protocol, ProtocolError, Result, ServerError, SimpleQueryResult, Statement, StatementResult, Time

Constant Summary collapse

VERSION =

Version number of this library

30001
MYSQL_UNIX_PORT =

UNIX domain socket filename

"/tmp/mysql.sock"
MYSQL_TCP_PORT =

TCP socket port number

3306
OPTIONS = 
{
  :connect_timeout         => Integer,
#    :compress                => x,
#    :named_pipe              => x,
  :init_command            => String,
#    :read_default_file       => x,
#    :read_default_group      => x,
  :charset                 => Object,
#    :local_infile            => x,
#    :shared_memory_base_name => x,
  :read_timeout            => Integer,
  :write_timeout           => Integer,
#    :use_result              => x,
#    :use_remote_connection   => x,
#    :use_embedded_connection => x,
#    :guess_connection        => x,
#    :client_ip               => x,
#    :secure_auth             => x,
#    :report_data_truncation  => x,
#    :reconnect               => x,
#    :ssl_verify_server_cert  => x,
}
OPT2FLAG =

:nodoc:

{
#    :compress                => CLIENT_COMPRESS,
  :found_rows              => CLIENT_FOUND_ROWS,
  :ignore_sigpipe          => CLIENT_IGNORE_SIGPIPE,
  :ignore_space            => CLIENT_IGNORE_SPACE,
  :interactive             => CLIENT_INTERACTIVE,
  :local_files             => CLIENT_LOCAL_FILES,
#    :multi_results           => CLIENT_MULTI_RESULTS,
#    :multi_statements        => CLIENT_MULTI_STATEMENTS,
  :no_schema               => CLIENT_NO_SCHEMA,
#    :ssl                     => CLIENT_SSL,
}
Stmt = 
Statement
COM_SLEEP =

Command

0
COM_QUIT = 
1
COM_INIT_DB = 
2
COM_QUERY = 
3
COM_FIELD_LIST = 
4
COM_CREATE_DB = 
5
COM_DROP_DB = 
6
COM_REFRESH = 
7
COM_SHUTDOWN = 
8
COM_STATISTICS = 
9
COM_PROCESS_INFO = 
10
COM_CONNECT = 
11
COM_PROCESS_KILL = 
12
COM_DEBUG = 
13
COM_PING = 
14
COM_TIME = 
15
COM_DELAYED_INSERT = 
16
COM_CHANGE_USER = 
17
COM_BINLOG_DUMP = 
18
COM_TABLE_DUMP = 
19
COM_CONNECT_OUT = 
20
COM_REGISTER_SLAVE = 
21
COM_STMT_PREPARE = 
22
COM_STMT_EXECUTE = 
23
COM_STMT_SEND_LONG_DATA = 
24
COM_STMT_CLOSE = 
25
COM_STMT_RESET = 
26
COM_SET_OPTION = 
27
COM_STMT_FETCH = 
28
CLIENT_LONG_PASSWORD =

Client flag

1
CLIENT_FOUND_ROWS =

new more secure passwords

1 << 1
CLIENT_LONG_FLAG =

Found instead of affected rows

1 << 2
CLIENT_CONNECT_WITH_DB =

Get all column flags

1 << 3
CLIENT_NO_SCHEMA =

One can specify db on connect

1 << 4
CLIENT_COMPRESS =

Don’t allow database.table.column

1 << 5
CLIENT_ODBC =

Can use compression protocol

1 << 6
CLIENT_LOCAL_FILES =

Odbc client

1 << 7
CLIENT_IGNORE_SPACE =

Can use LOAD DATA LOCAL

1 << 8
CLIENT_PROTOCOL_41 =

Ignore spaces before ‘(’

1 << 9
CLIENT_INTERACTIVE =

New 4.1 protocol

1 << 10
CLIENT_SSL =

This is an interactive client

1 << 11
CLIENT_IGNORE_SIGPIPE =

Switch to SSL after handshake

1 << 12
CLIENT_TRANSACTIONS =

IGNORE sigpipes

1 << 13
CLIENT_RESERVED =

Client knows about transactions

1 << 14
CLIENT_SECURE_CONNECTION =

Old flag for 4.1 protocol

1 << 15
CLIENT_MULTI_STATEMENTS =

New 4.1 authentication

1 << 16
CLIENT_MULTI_RESULTS =

Enable/disable multi-stmt support

1 << 17
OPT_CONNECT_TIMEOUT =

Connection Option

0
OPT_COMPRESS = 
1
OPT_NAMED_PIPE = 
2
INIT_COMMAND = 
3
READ_DEFAULT_FILE = 
4
READ_DEFAULT_GROUP = 
5
SET_CHARSET_DIR = 
6
SET_CHARSET_NAME = 
7
OPT_LOCAL_INFILE = 
8
OPT_PROTOCOL = 
9
SHARED_MEMORY_BASE_NAME = 
10
OPT_READ_TIMEOUT = 
11
OPT_WRITE_TIMEOUT = 
12
OPT_USE_RESULT = 
13
OPT_USE_REMOTE_CONNECTION = 
14
OPT_USE_EMBEDDED_CONNECTION = 
15
OPT_GUESS_CONNECTION = 
16
SET_CLIENT_IP = 
17
SECURE_AUTH = 
18
REPORT_DATA_TRUNCATION = 
19
OPT_RECONNECT = 
20
OPT_SSL_VERIFY_SERVER_CERT = 
21
OPTION_MULTI_STATEMENTS_ON =

Server Option

0
OPTION_MULTI_STATEMENTS_OFF = 
1
SERVER_STATUS_IN_TRANS =

Server Status

1
SERVER_STATUS_AUTOCOMMIT = 
1 << 1
SERVER_MORE_RESULTS_EXISTS = 
1 << 3
SERVER_QUERY_NO_GOOD_INDEX_USED = 
1 << 4
SERVER_QUERY_NO_INDEX_USED = 
1 << 5
SERVER_STATUS_CURSOR_EXISTS = 
1 << 6
SERVER_STATUS_LAST_ROW_SENT = 
1 << 7
SERVER_STATUS_DB_DROPPED = 
1 << 8
SERVER_STATUS_NO_BACKSLASH_ESCAPES = 
1 << 9
REFRESH_GRANT =

Refresh parameter

1
REFRESH_LOG = 
1 << 1
REFRESH_TABLES = 
1 << 2
REFRESH_HOSTS = 
1 << 3
REFRESH_STATUS = 
1 << 4
REFRESH_THREADS = 
1 << 5
REFRESH_SLAVE = 
1 << 6
REFRESH_MASTER = 
1 << 7
REFRESH_READ_LOCK = 
1 << 14
REFRESH_FAST = 
1 << 15

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(*args) ⇒ Mysql

:call-seq: new(conninfo, opt={}) new(conninfo, opt={}) {|my| …}

Connect to mysqld. If block is specified then the connection is closed when exiting the block.

Argument

conninfo
String / URI / Hash

Connection information.

If conninfo is String then it’s format must be “mysql://user:password@hostname:port/dbname”. If conninfo is URI then it’s scheme must be “mysql”. If conninfo is Hash then valid keys are :host, :user, :password, :db, :port, :socket and :flag.

opt
Hash

options.

Options

:connect_timeout
Numeric

The number of seconds before connection timeout.

:init_command
String

Statement to execute when connecting to the MySQL server.

:charset
String / Mysql::Charset

The character set to use as the default character set.

:read_timeout

[The timeout in seconds for attempts to read from the server.

:write_timeout
Numeric

The timeout in seconds for attempts to write to the server.

:found_rows
Boolean

Return the number of found (matched) rows, not the number of changed rows.

:ignore_space
Boolean

Allow spaces after function names.

:interactive
Boolean

Allow ‘interactive_timeout’ seconds (instead of ‘wait_timeout’ seconds) of inactivity before closing the connection.

:local_files
Boolean

Enable ‘LOAD DATA LOCAL’ handling.

:no_schema
Boolean

Don’t allow the DB_NAME.TBL_NAME.COL_NAME syntax.

Block parameter

my
Mysql 


123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/mysql.rb', line 123

def initialize(*args)
  @fields = nil
  @protocol = nil
  @charset = nil
  @connect_timeout = nil
  @read_timeout = nil
  @write_timeout = nil
  @init_command = nil
  @affected_rows = nil
  @server_version = nil
  @sqlstate = "00000"
  @param, opt = conninfo(*args)
  @connected = false
  set_option opt
end

Instance Attribute Details

#affected_rows ⇒ Object (readonly)

number of affected records by insert/update/delete.



64
65
66
 
# File 'lib/mysql.rb', line 64

def affected_rows
  @affected_rows
end

#charset ⇒ Object

:nodoc:



63
64
65
 
# File 'lib/mysql.rb', line 63

def charset
  @charset
end

#insert_id ⇒ Object (readonly)

latest auto_increment value.



65
66
67
 
# File 'lib/mysql.rb', line 65

def insert_id
  @insert_id
end

#protocol ⇒ Object (readonly)

Returns the value of attribute protocol.



69
70
71
 
# File 'lib/mysql.rb', line 69

def protocol
  @protocol
end

#query_with_result ⇒ Object

Returns the value of attribute query_with_result.



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

def query_with_result
  @query_with_result
end

#reconnect ⇒ Object

Returns the value of attribute reconnect.



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

def reconnect
  @reconnect
end

#server_status ⇒ Object (readonly)

:nodoc:



66
67
68
 
# File 'lib/mysql.rb', line 66

def server_status
  @server_status
end

#server_version ⇒ Object (readonly)

Returns the value of attribute server_version.



68
69
70
 
# File 'lib/mysql.rb', line 68

def server_version
  @server_version
end

#sqlstate ⇒ Object (readonly)

Returns the value of attribute sqlstate.



70
71
72
 
# File 'lib/mysql.rb', line 70

def sqlstate
  @sqlstate
end

#warning_count ⇒ Object (readonly)

Returns the value of attribute warning_count.



67
68
69
 
# File 'lib/mysql.rb', line 67

def warning_count
  @warning_count
end

Class Method Details

.client_info ⇒ Object Also known as: get_client_info 



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

def client_info
  "5.0.67"
end

.client_version ⇒ Object 



24
25
26
 
# File 'lib/mysql/compat.rb', line 24

def client_version
  50067
end

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

Return

The value that block returns if block is specified. Otherwise this returns Mysql object.



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

def self.connect(*args, &block)
  my = self.new(*args)
  my.connect
  return my unless block
  begin
    return block.call(my)
  ensure
    my.close
  end
end

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



33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/mysql/compat.rb', line 33

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

.init ⇒ Object 



18
19
20
21
22
 
# File 'lib/mysql/compat.rb', line 18

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

Instance Method Details

#client_version ⇒ Object 



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

def client_version
  self.class.client_version
end

#close ⇒ Object

disconnect from mysql.



169
170
171
172
173
174
175
176
177
178
179
 
# File 'lib/mysql.rb', line 169

def close
  if @protocol
    @protocol.synchronize do
      @protocol.reset
      @protocol.send_packet Protocol::QuitPacket.new
      @protocol.close
      @protocol = nil
    end
  end
  return self
end

#connect(*args) ⇒ Object Also known as: real_connect

:call-seq: connect(conninfo, opt={})

connect to mysql server. arguments are same as new().



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/mysql.rb', line 144

def connect(*args)
  param, opt = conninfo(*args)
  set_option opt
  param = @param.merge param
  @protocol = Protocol.new param[:host], param[:port], param[:socket], @connect_timeout, @read_timeout, @write_timeout
  @protocol.synchronize do
    init_packet = @protocol.read_initial_packet
    @server_version = init_packet.server_version.split(/\D/)[0,3].inject{|a,b|a.to_i*100+b.to_i}
    client_flags = CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG | CLIENT_TRANSACTIONS | CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION
    client_flags |= CLIENT_CONNECT_WITH_DB if param[:db]
    client_flags |= param[:flag] if param[:flag]
    unless @charset
      @charset = Charset.by_number(init_packet.server_charset)
      @charset.encoding       # raise error if unsupported charset
    end
    netpw = init_packet.crypt_password param[:password]
    auth_packet = Protocol::AuthenticationPacket.new client_flags, 1024**3, @charset.number, param[:user], netpw, param[:db]
    @protocol.send_packet auth_packet
    @protocol.read            # skip OK packet
  end
  simple_query @init_command if @init_command
  return self
end

#escape_string(str) ⇒ Object

Escape special character in MySQL.

Note

In Ruby 1.8, this is not safe for multibyte charset such as ‘SJIS’. You should use place-holder in prepared-statement.



281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/mysql.rb', line 281

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

#initialize_orig ⇒ Mysql

:call-seq: new(conninfo, opt={}) new(conninfo, opt={}) {|my| …}

Connect to mysqld. If block is specified then the connection is closed when exiting the block.

Argument

conninfo
String / URI / Hash

Connection information.

If conninfo is String then it’s format must be “mysql://user:password@hostname:port/dbname”. If conninfo is URI then it’s scheme must be “mysql”. If conninfo is Hash then valid keys are :host, :user, :password, :db, :port, :socket and :flag.

opt
Hash

options.

Options

:connect_timeout
Numeric

The number of seconds before connection timeout.

:init_command
String

Statement to execute when connecting to the MySQL server.

:charset
String / Mysql::Charset

The character set to use as the default character set.

:read_timeout

[The timeout in seconds for attempts to read from the server.

:write_timeout
Numeric

The timeout in seconds for attempts to write to the server.

:found_rows
Boolean

Return the number of found (matched) rows, not the number of changed rows.

:ignore_space
Boolean

Allow spaces after function names.

:interactive
Boolean

Allow ‘interactive_timeout’ seconds (instead of ‘wait_timeout’ seconds) of inactivity before closing the connection.

:local_files
Boolean

Enable ‘LOAD DATA LOCAL’ handling.

:no_schema
Boolean

Don’t allow the DB_NAME.TBL_NAME.COL_NAME syntax.

Block parameter

my
Mysql

Returns:

  • (Mysql) —

    a new instance of Mysql



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

def initialize(*args)
  @fields = nil
  @protocol = nil
  @charset = nil
  @connect_timeout = nil
  @read_timeout = nil
  @write_timeout = nil
  @init_command = nil
  @affected_rows = nil
  @server_version = nil
  @sqlstate = "00000"
  @param, opt = conninfo(*args)
  @connected = false
  set_option opt
end

#list_fields(table) ⇒ Object

Get field(column) list

Argument

table
String

table name.

Return

Array of Mysql::Field



322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
# File 'lib/mysql.rb', line 322

def list_fields(table)
  @protocol.synchronize do
    begin
      @protocol.reset
      @protocol.send_packet Protocol::FieldListPacket.new(table)
      fields = []
      until Protocol.eof_packet?(data = @protocol.read)
        fields.push Field.new(Protocol::FieldPacket.parse(data))
      end
      return fields
    rescue ServerError => e
      @sqlstate = e.sqlstate
      raise
    end
  end
end

#options(opt, val = nil) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/mysql/compat.rb', line 77

def options(opt, val=nil)
  case opt
  when INIT_COMMAND
    @init_command = val
  when OPT_COMPRESS
    raise ClientError, "not implemented"
  when OPT_CONNECT_TIMEOUT
    @connect_timeout = val
  when OPT_GUESS_CONNECTION
    raise ClientError, "not implemented"
  when OPT_LOCAL_INFILE
    @local_infile = val
  when OPT_NAMED_PIPE
    raise ClientError, "not implemented"
  when OPT_PROTOCOL
    raise ClientError, "not implemented"
  when OPT_READ_TIMEOUT
    @read_timeout = val
  when OPT_USE_EMBEDDED_CONNECTION
    raise ClientError, "not implemented"
  when OPT_USE_REMOTE_CONNECTION
    raise ClientError, "not implemented"
  when OPT_WRITE_TIMEOUT
    @write_timeout = val
  when READ_DEFAULT_FILE
    raise ClientError, "not implemented"
  when READ_DEFAULT_GROUP
    raise ClientError, "not implemented"
  when SECURE_AUTH
    raise ClientError, "not implemented"
  when SET_CHARSET_DIR
    raise ClientError, "not implemented"
  when SET_CHARSET_NAME
    self.charset = val
  when SET_CLIENT_IP
    raise ClientError, "not implemented"
  when SHARED_MEMORY_BASE_NAME
    raise ClientError, "not implemented"
  else
    raise ClientError, "unknown option: #{opt}"
  end
  self
end

#prepare(str, &block) ⇒ Object

Parse prepared-statement. If block is specified then prepared-statement is closed when exiting the block.

Argument

str
String

query string

block

If it is given then it is evaluated with Mysql::Statement object as argument.

Return

Mysql::Statement

Prepared-statement object

The block value if block is given.



264
265
266
267
268
269
270
271
272
273
274
275
 
# File 'lib/mysql.rb', line 264

def prepare(str, &block)
  st = Statement.new self
  st.prepare str
  if block
    begin
      return block.call(st)
    ensure
      st.close
    end
  end
  return st
end

#prepare_query(str, *params) ⇒ Object

:nodoc:



243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/mysql.rb', line 243

def prepare_query(str, *params)  # :nodoc:
  st = prepare(str)
  res = st.execute(*params)
  if st.fields.empty?
    @affected_rows = st.affected_rows
    @insert_id = st.insert_id
    @server_status = st.server_status
    @warning_count = st.warning_count
  end
  st.close
  return res
end

#query(str) ⇒ Object

Execute query string. If params is specified, then the query is executed as prepared-statement automatically.

Argument

str
String

Query.

params

Parameters corresponding to place holder (‘?’) in str.

block

If it is given then it is evaluated with Result object as argument.

Return

Mysql::Result

If result set exist.

nil

If the query does not return result set.

self

If block is specified.

Block parameter

Mysql::Result

Example

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



207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/mysql.rb', line 207

def query(str, *params, &block)
  if params.empty?
    res = simple_query(str, &block)
  else
    res = prepare_query(str, *params, &block)
  end
  if res && block
    yield res
    return self
  end
  return res
end

#quote ⇒ Object

Escape special character in MySQL.

Note

In Ruby 1.8, this is not safe for multibyte charset such as ‘SJIS’. You should use place-holder in prepared-statement.



292
293
294
295
296
297
298
299
300
301
302
 
# File 'lib/mysql.rb', line 292

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

#simple_query(str) ⇒ Object

:nodoc:



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/mysql.rb', line 220

def simple_query(str)  # :nodoc:
  @affected_rows = @insert_id = @server_status = @warning_count = 0
  @protocol.synchronize do
    begin
      @protocol.reset
      @protocol.send_packet Protocol::QueryPacket.new(@charset.convert(str))
      res_packet = @protocol.read_result_packet
      if res_packet.field_count == 0
        @affected_rows, @insert_id, @server_status, @warning_conut =
          res_packet.affected_rows, res_packet.insert_id, res_packet.server_status, res_packet.warning_count
        return nil
      else
        @fields = Array.new(res_packet.field_count).map{Field.new @protocol.read_field_packet}
        @protocol.read_eof_packet
        return SimpleQueryResult.new self, @fields
      end
    rescue ServerError => e
      @sqlstate = e.sqlstate
      raise
    end
  end
end

#statement(&block) ⇒ Object Also known as: stmt_init

:call-seq: statement() statement() {|st| … }

Make empty prepared-statement object. If block is specified then prepared-statement is closed when exiting the block.

Block parameter

st
Mysql::Stmt

Prepared-statement object.

Return

Mysql::Statement

If block is not specified.

The value returned by block

If block is specified.



305
306
307
308
309
310
311
312
313
314
315
 
# File 'lib/mysql.rb', line 305

def statement(&block)
  st = Statement.new self
  if block
    begin
      return block.call(st)
    ensure
      st.close
    end
  end
  return st
end

#store_result ⇒ Object

Raises:



121
122
123
124
 
# File 'lib/mysql/compat.rb', line 121

def store_result
  raise ClientError, "no result set" unless @fields
  Result.new @fields, @stream
end

#use_result ⇒ Object

Raises:



126
127
128
129
 
# File 'lib/mysql/compat.rb', line 126

def use_result
  raise ClientError, "no result set" unless @fields
  Result.new @fields, @stream, false
end