CoCalc provides the best real-time collaborative environment for Jupyter Notebooks, LaTeX documents, and SageMath, scalable from individual users to large groups and classes!

1
# coding: ascii-8bit
2
# Copyright (C) 2008-2012 TOMITA Masahiro
3
# mailto:[email protected]
4

5
# MySQL connection class.
6
# @example
7
#  my = RbMysql.connect('hostname', 'user', 'password', 'dbname')
8
#  res = my.query 'select col1,col2 from tbl where id=123'
9
#  res.each do |c1, c2|
10
#    p c1, c2
11
#  end
12
class RbMysql
13

14
  require "rbmysql/constants"
15
  require "rbmysql/error"
16
  require "rbmysql/charset"
17
  require "rbmysql/protocol"
18
  require "rbmysql/packet.rb"
19
  begin
20
    require "mysql/ext.so"
21
  rescue LoadError
22
  end
23

24
  VERSION            = 20913               # Version number of this library
25
  MYSQL_UNIX_PORT    = "/tmp/mysql.sock"   # UNIX domain socket filename
26
  MYSQL_TCP_PORT     = 3306                # TCP socket port number
27

28
  # @return [RbMysql::Charset] character set of MySQL connection
29
  attr_reader :charset
30
  # @private
31
  attr_reader :protocol
32

33
  # @return [Boolean] if true, {#query} return {RbMysql::Result}.
34
  attr_accessor :query_with_result
35

36
  class << self
37
    # Make RbMysql object without connecting.
38
    # @return [RbMysql]
39
    def init
40
      my = self.allocate
41
      my.instance_eval{initialize}
42
      my
43
    end
44

45
    # Make RbMysql object and connect to mysqld.
46
    # @param args same as arguments for {#connect}.
47
    # @return [RbMysql]
48
    def new(*args)
49
      my = self.init
50
      my.connect(*args)
51
    end
52

53
    alias real_connect new
54
    alias connect new
55

56
    # Escape special character in string.
57
    # @param [String] str
58
    # @return [String]
59
    def escape_string(str)
60
      str.gsub(/[\0\n\r\\\'\"\x1a]/) do |s|
61
        case s
62
        when "\0" then "\\0"
63
        when "\n" then "\\n"
64
        when "\r" then "\\r"
65
        when "\x1a" then "\\Z"
66
        else "\\#{s}"
67
        end
68
      end
69
    end
70
    alias quote escape_string
71

72
    # @return [String] client version. This value is dummy for MySQL/Ruby compatibility.
73
    def client_info
74
      "5.0.0"
75
    end
76
    alias get_client_info client_info
77

78
    # @return [Integer] client version. This value is dummy for MySQL/Ruby compatibility.
79
    def client_version
80
      50000
81
    end
82
    alias get_client_version client_version
83
  end
84

85
  def initialize
86
    @fields = nil
87
    @protocol = nil
88
    @charset = nil
89
    @connect_timeout = nil
90
    @read_timeout = nil
91
    @write_timeout = nil
92
    @init_command = nil
93
    @sqlstate = "00000"
94
    @query_with_result = true
95
    @host_info = nil
96
    @last_error = nil
97
    @result_exist = false
98
    @local_infile = nil
99
  end
100

101
  # Connect to mysqld.
102
  # @param [String / nil] host hostname mysqld running
103
  # @param [String / nil] user username to connect to mysqld
104
  # @param [String / nil] passwd password to connect to mysqld
105
  # @param [String / nil] db initial database name
106
  # @param [Integer / nil] port port number (used if host is not 'localhost' or nil)
107
  # @param [String / Socket / nil] socket socket file name (used if host is 'localhost' or nil), or an existing ::Socket instance
108
  # @param [Integer / nil] flag connection flag. RbMysql::CLIENT_* ORed
109
  # @return self
110
  def connect(host=nil, user=nil, passwd=nil, db=nil, port=nil, socket=nil, flag=0)
111
    if flag & CLIENT_COMPRESS != 0
112
      warn 'unsupported flag: CLIENT_COMPRESS' if $VERBOSE
113
      flag &= ~CLIENT_COMPRESS
114
    end
115
    @protocol = Protocol.new host, port, socket, @connect_timeout, @read_timeout, @write_timeout
116
    @protocol.authenticate user, passwd, db, (@local_infile ? CLIENT_LOCAL_FILES : 0) | flag, @charset
117
    @charset ||= @protocol.charset
118
    @host_info = (host.nil? || host == "localhost") ? 'Localhost via UNIX socket' : "#{host} via TCP/IP"
119
    query @init_command if @init_command
120
    return self
121
  end
122
  alias real_connect connect
123

124
  # Disconnect from mysql.
125
  # @return [RbMysql] self
126
  def close
127
    if @protocol
128
      @protocol.quit_command
129
      @protocol = nil
130
    end
131
    return self
132
  end
133

134
  # Disconnect from mysql without QUIT packet.
135
  # @return [RbMysql] self
136
  def close!
137
    if @protocol
138
      @protocol.close
139
      @protocol = nil
140
    end
141
    return self
142
  end
143

144
  # Set option for connection.
145
  #
146
  # Available options:
147
  #   RbMysql::INIT_COMMAND, RbMysql::OPT_CONNECT_TIMEOUT, RbMysql::OPT_READ_TIMEOUT,
148
  #   RbMysql::OPT_WRITE_TIMEOUT, RbMysql::SET_CHARSET_NAME
149
  # @param [Integer] opt option
150
  # @param [Integer] value option value that is depend on opt
151
  # @return [RbMysql] self
152
  def options(opt, value=nil)
153
    case opt
154
    when RbMysql::INIT_COMMAND
155
      @init_command = value.to_s
156
      #    when RbMysql::OPT_COMPRESS
157
    when RbMysql::OPT_CONNECT_TIMEOUT
158
      @connect_timeout = value
159
      #    when RbMysql::GUESS_CONNECTION
160
    when RbMysql::OPT_LOCAL_INFILE
161
      @local_infile = value
162
      #    when RbMysql::OPT_NAMED_PIPE
163
      #    when RbMysql::OPT_PROTOCOL
164
    when RbMysql::OPT_READ_TIMEOUT
165
      @read_timeout = value.to_i
166
      #    when RbMysql::OPT_RECONNECT
167
      #    when RbMysql::SET_CLIENT_IP
168
      #    when RbMysql::OPT_SSL_VERIFY_SERVER_CERT
169
      #    when RbMysql::OPT_USE_EMBEDDED_CONNECTION
170
      #    when RbMysql::OPT_USE_REMOTE_CONNECTION
171
    when RbMysql::OPT_WRITE_TIMEOUT
172
      @write_timeout = value.to_i
173
      #    when RbMysql::READ_DEFAULT_FILE
174
      #    when RbMysql::READ_DEFAULT_GROUP
175
      #    when RbMysql::REPORT_DATA_TRUNCATION
176
      #    when RbMysql::SECURE_AUTH
177
      #    when RbMysql::SET_CHARSET_DIR
178
    when RbMysql::SET_CHARSET_NAME
179
      @charset = Charset.by_name value.to_s
180
      #    when RbMysql::SHARED_MEMORY_BASE_NAME
181
    else
182
      warn "option not implemented: #{opt}" if $VERBOSE
183
    end
184
    self
185
  end
186

187
  # Escape special character in MySQL.
188
  #
189
  # In Ruby 1.8, this is not safe for multibyte charset such as 'SJIS'.
190
  # You should use place-holder in prepared-statement.
191
  # @param [String] str
192
  # return [String]
193
  def escape_string(str)
194
    if not defined? Encoding and @charset.unsafe
195
      raise ClientError, 'RbMysql#escape_string is called for unsafe multibyte charset'
196
    end
197
    self.class.escape_string str
198
  end
199
  alias quote escape_string
200

201
  # @return [String] client version
202
  def client_info
203
    self.class.client_info
204
  end
205
  alias get_client_info client_info
206

207
  # @return [Integer] client version
208
  def client_version
209
    self.class.client_version
210
  end
211
  alias get_client_version client_version
212

213
  # Set charset of MySQL connection.
214
  # @param [String / RbMysql::Charset] cs
215
  def charset=(cs)
216
    charset = cs.is_a?(Charset) ? cs : Charset.by_name(cs)
217
    if @protocol
218
      @protocol.charset = charset
219
      query "SET NAMES #{charset.name}"
220
    end
221
    @charset = charset
222
    cs
223
  end
224

225
  # @return [String] charset name
226
  def character_set_name
227
    @charset.name
228
  end
229

230
  # @return [Integer] last error number
231
  def errno
232
    @last_error ? @last_error.errno : 0
233
  end
234

235
  # @return [String] last error message
236
  def error
237
    @last_error && @last_error.error
238
  end
239

240
  # @return [String] sqlstate for last error
241
  def sqlstate
242
    @last_error ? @last_error.sqlstate : "00000"
243
  end
244

245
  # @return [Integer] number of columns for last query
246
  def field_count
247
    @fields.size
248
  end
249

250
  # @return [String] connection type
251
  def host_info
252
    @host_info
253
  end
254
  alias get_host_info host_info
255

256
  # @return [Integer] protocol version
257
  def proto_info
258
    RbMysql::Protocol::VERSION
259
  end
260
  alias get_proto_info proto_info
261

262
  # @return [String] server version
263
  def server_info
264
    check_connection
265
    @protocol.server_info
266
  end
267
  alias get_server_info server_info
268

269
  # @return [Integer] server version
270
  def server_version
271
    check_connection
272
    @protocol.server_version
273
  end
274
  alias get_server_version server_version
275

276
  # @return [String] information for last query
277
  def info
278
    @protocol && @protocol.message
279
  end
280

281
  # @return [Integer] number of affected records by insert/update/delete.
282
  def affected_rows
283
    @protocol ? @protocol.affected_rows : 0
284
  end
285

286
  # @return [Integer] latest auto_increment value
287
  def insert_id
288
    @protocol ? @protocol.insert_id : 0
289
  end
290

291
  # @return [Integer] number of warnings for previous query
292
  def warning_count
293
    @protocol ? @protocol.warning_count : 0
294
  end
295

296
  # Kill query.
297
  # @param [Integer] pid thread id
298
  # @return [RbMysql] self
299
  def kill(pid)
300
    check_connection
301
    @protocol.kill_command pid
302
    self
303
  end
304

305
  # database list.
306
  # @param [String] db database name that may contain wild card.
307
  # @return [Array<String>] database list
308
  def list_dbs(db=nil)
309
    db &&= db.gsub(/[\\\']/){"\\#{$&}"}
310
    query(db ? "show databases like '#{db}'" : "show databases").map(&:first)
311
  end
312

313
  # Execute query string.
314
  # @param [String] str Query.
315
  # @yield [RbMysql::Result] evaluated per query.
316
  # @return [RbMysql::Result] If {#query_with_result} is true and result set exist.
317
  # @return [nil] If {#query_with_result} is true and the query does not return result set.
318
  # @return [RbMysql] If {#query_with_result} is false or block is specified
319
  # @example
320
  #  my.query("select 1,NULL,'abc'").fetch  # => [1, nil, "abc"]
321
  def query(str, &block)
322
    check_connection
323
    @fields = nil
324
    begin
325
      nfields = @protocol.query_command str
326
      if nfields
327
        @fields = @protocol.retr_fields nfields
328
        @result_exist = true
329
      end
330
      if block
331
        while true
332
          block.call store_result if @fields
333
          break unless next_result
334
        end
335
        return self
336
      end
337
      if @query_with_result
338
        return @fields ? store_result : nil
339
      else
340
        return self
341
      end
342
    rescue ServerError => e
343
      @last_error = e
344
      @sqlstate = e.sqlstate
345
      raise
346
    end
347
  end
348
  alias real_query query
349

350
  # Get all data for last query if query_with_result is false.
351
  # @return [RbMysql::Result]
352
  def store_result
353
    check_connection
354
    raise ClientError, 'invalid usage' unless @result_exist
355
    res = Result.new @fields, @protocol
356
    @result_exist = false
357
    res
358
  end
359

360
  # @return [Integer] Thread ID
361
  def thread_id
362
    check_connection
363
    @protocol.thread_id
364
  end
365

366
  # Use result of query. The result data is retrieved when you use RbMysql::Result#fetch.
367
  # @return [RbMysql::Result]
368
  def use_result
369
    store_result
370
  end
371

372
  # Set server option.
373
  # @param [Integer] opt {RbMysql::OPTION_MULTI_STATEMENTS_ON} or {RbMysql::OPTION_MULTI_STATEMENTS_OFF}
374
  # @return [RbMysql] self
375
  def set_server_option(opt)
376
    check_connection
377
    @protocol.set_option_command opt
378
    self
379
  end
380

381
  # @return [Boolean] true if multiple queries are specified and unexecuted queries exists.
382
  def more_results
383
    @protocol.server_status & SERVER_MORE_RESULTS_EXISTS != 0
384
  end
385
  alias more_results? more_results
386

387
  # execute next query if multiple queries are specified.
388
  # @return [Boolean] true if next query exists.
389
  def next_result
390
    return false unless more_results
391
    check_connection
392
    @fields = nil
393
    nfields = @protocol.get_result
394
    if nfields
395
      @fields = @protocol.retr_fields nfields
396
      @result_exist = true
397
    end
398
    return true
399
  end
400

401
  # Parse prepared-statement.
402
  # @param [String] str query string
403
  # @return [RbMysql::Stmt] Prepared-statement object
404
  def prepare(str)
405
    st = Stmt.new @protocol, @charset
406
    st.prepare str
407
    st
408
  end
409

410
  # @private
411
  # Make empty prepared-statement object.
412
  # @return [RbMysql::Stmt] If block is not specified.
413
  def stmt_init
414
    Stmt.new @protocol, @charset
415
  end
416

417
  # Returns RbMysql::Result object that is empty.
418
  # Use fetch_fields to get list of fields.
419
  # @param [String] table table name.
420
  # @param [String] field field name that may contain wild card.
421
  # @return [RbMysql::Result]
422
  def list_fields(table, field=nil)
423
    check_connection
424
    begin
425
      fields = @protocol.field_list_command table, field
426
      return Result.new fields
427
    rescue ServerError => e
428
      @last_error = e
429
      @sqlstate = e.sqlstate
430
      raise
431
    end
432
  end
433

434
  # @return [RbMysql::Result] containing process list
435
  def list_processes
436
    check_connection
437
    @fields = @protocol.process_info_command
438
    @result_exist = true
439
    store_result
440
  end
441

442
  # @note for Ruby 1.8: This is not multi-byte safe. Don't use for multi-byte charset such as cp932.
443
  # @param [String] table database name that may contain wild card.
444
  # @return [Array<String>] list of table name.
445
  def list_tables(table=nil)
446
    q = table ? "show tables like '#{quote table}'" : "show tables"
447
    query(q).map(&:first)
448
  end
449

450
  # Check whether the  connection is available.
451
  # @return [RbMysql] self
452
  def ping
453
    check_connection
454
    @protocol.ping_command
455
    self
456
  end
457

458
  # Flush tables or caches.
459
  # @param [Integer] op operation. Use RbMysql::REFRESH_* value.
460
  # @return [RbMysql] self
461
  def refresh(op)
462
    check_connection
463
    @protocol.refresh_command op
464
    self
465
  end
466

467
  # Reload grant tables.
468
  # @return [RbMysql] self
469
  def reload
470
    refresh RbMysql::REFRESH_GRANT
471
  end
472

473
  # Select default database
474
  # @return [RbMysql] self
475
  def select_db(db)
476
    query "use #{db}"
477
    self
478
  end
479

480
  # shutdown server.
481
  # @return [RbMysql] self
482
  def shutdown(level=0)
483
    check_connection
484
    @protocol.shutdown_command level
485
    self
486
  end
487

488
  # @return [String] statistics message
489
  def stat
490
    @protocol ? @protocol.statistics_command : 'MySQL server has gone away'
491
  end
492

493
  # Commit transaction
494
  # @return [RbMysql] self
495
  def commit
496
    query 'commit'
497
    self
498
  end
499

500
  # Rollback transaction
501
  # @return [RbMysql] self
502
  def rollback
503
    query 'rollback'
504
    self
505
  end
506

507
  # Set autocommit mode
508
  # @param [Boolean] flag
509
  # @return [RbMysql] self
510
  def autocommit(flag)
511
    query "set autocommit=#{flag ? 1 : 0}"
512
    self
513
  end
514

515
  private
516

517
  def check_connection
518
    raise ClientError::ServerGoneError, 'MySQL server has gone away' unless @protocol
519
  end
520

521
  # @!visibility public
522
  # Field class
523
  class Field
524
    # @return [String] database name
525
    attr_reader :db
526
    # @return [String] table name
527
    attr_reader :table
528
    # @return [String] original table name
529
    attr_reader :org_table
530
    # @return [String] field name
531
    attr_reader :name
532
    # @return [String] original field name
533
    attr_reader :org_name
534
    # @return [Integer] charset id number
535
    attr_reader :charsetnr
536
    # @return [Integer] field length
537
    attr_reader :length
538
    # @return [Integer] field type
539
    attr_reader :type
540
    # @return [Integer] flag
541
    attr_reader :flags
542
    # @return [Integer] number of decimals
543
    attr_reader :decimals
544
    # @return [String] default value
545
    attr_reader :default
546
    alias :def :default
547

548
    # @private
549
    attr_accessor :result
550

551
    # @attr [Protocol::FieldPacket] packet
552
    def initialize(packet)
553
      @db, @table, @org_table, @name, @org_name, @charsetnr, @length, @type, @flags, @decimals, @default =
554
        packet.db, packet.table, packet.org_table, packet.name, packet.org_name, packet.charsetnr, packet.length, packet.type, packet.flags, packet.decimals, packet.default
555
      @flags |= NUM_FLAG if is_num_type?
556
      @max_length = nil
557
    end
558

559
    # @return [Hash] field information
560
    def hash
561
      {
562
        "name"       => @name,
563
        "table"      => @table,
564
        "def"        => @default,
565
        "type"       => @type,
566
        "length"     => @length,
567
        "max_length" => max_length,
568
        "flags"      => @flags,
569
        "decimals"   => @decimals
570
      }
571
    end
572

573
    # @private
574
    def inspect
575
      "#<RbMysql::Field:#{@name}>"
576
    end
577

578
    # @return [Boolean] true if numeric field.
579
    def is_num?
580
      @flags & NUM_FLAG != 0
581
    end
582

583
    # @return [Boolean] true if not null field.
584
    def is_not_null?
585
      @flags & NOT_NULL_FLAG != 0
586
    end
587

588
    # @return [Boolean] true if primary key field.
589
    def is_pri_key?
590
      @flags & PRI_KEY_FLAG != 0
591
    end
592

593
    # @return [Integer] maximum width of the field for the result set
594
    def max_length
595
      return @max_length if @max_length
596
      @max_length = 0
597
      @result.calculate_field_max_length if @result
598
      @max_length
599
    end
600

601
    attr_writer :max_length
602

603
    private
604

605
    def is_num_type?
606
      [TYPE_DECIMAL, TYPE_TINY, TYPE_SHORT, TYPE_LONG, TYPE_FLOAT, TYPE_DOUBLE, TYPE_LONGLONG, TYPE_INT24].include?(@type) || (@type == TYPE_TIMESTAMP && (@length == 14 || @length == 8))
607
    end
608

609
  end
610

611
  # @!visibility public
612
  # Result set
613
  class ResultBase
614
    include Enumerable
615

616
    # @return [Array<RbMysql::Field>] field list
617
    attr_reader :fields
618

619
    # @param [Array of RbMysql::Field] fields
620
    def initialize(fields)
621
      @fields = fields
622
      @field_index = 0             # index of field
623
      @records = []                # all records
624
      @index = 0                   # index of record
625
      @fieldname_with_table = nil
626
      @fetched_record = nil
627
    end
628

629
    # ignore
630
    # @return [void]
631
    def free
632
    end
633

634
    # @return [Integer] number of record
635
    def size
636
      @records.size
637
    end
638
    alias num_rows size
639

640
    # @return [Array] current record data
641
    def fetch
642
      @fetched_record = nil
643
      return nil if @index >= @records.size
644
      @records[@index] = @records[@index].to_a unless @records[@index].is_a? Array
645
      @fetched_record = @records[@index]
646
      @index += 1
647
      return @fetched_record
648
    end
649
    alias fetch_row fetch
650

651
    # Return data of current record as Hash.
652
    # The hash key is field name.
653
    # @param [Boolean] with_table if true, hash key is "table_name.field_name".
654
    # @return [Hash] current record data
655
    def fetch_hash(with_table=nil)
656
      row = fetch
657
      return nil unless row
658
      if with_table and @fieldname_with_table.nil?
659
        @fieldname_with_table = @fields.map{|f| [f.table, f.name].join(".")}
660
      end
661
      ret = {}
662
      @fields.each_index do |i|
663
        fname = with_table ? @fieldname_with_table[i] : @fields[i].name
664
        ret[fname] = row[i]
665
      end
666
      ret
667
    end
668

669
    # Iterate block with record.
670
    # @yield [Array] record data
671
    # @return [self] self. If block is not specified, this returns Enumerator.
672
    def each(&block)
673
      return enum_for(:each) unless block
674
      while rec = fetch
675
        block.call rec
676
      end
677
      self
678
    end
679

680
    # Iterate block with record as Hash.
681
    # @param [Boolean] with_table if true, hash key is "table_name.field_name".
682
    # @yield [Hash] record data
683
    # @return [self] self. If block is not specified, this returns Enumerator.
684
    def each_hash(with_table=nil, &block)
685
      return enum_for(:each_hash, with_table) unless block
686
      while rec = fetch_hash(with_table)
687
        block.call rec
688
      end
689
      self
690
    end
691

692
    # Set record position
693
    # @param [Integer] n record index
694
    # @return [self] self
695
    def data_seek(n)
696
      @index = n
697
      self
698
    end
699

700
    # @return [Integer] current record position
701
    def row_tell
702
      @index
703
    end
704

705
    # Set current position of record
706
    # @param [Integer] n record index
707
    # @return [Integer] previous position
708
    def row_seek(n)
709
      ret = @index
710
      @index = n
711
      ret
712
    end
713
  end
714

715
  # @!visibility public
716
  # Result set for simple query
717
  class Result < ResultBase
718
    # @private
719
    # @param [Array<RbMysql::Field>] fields
720
    # @param [RbMysql::Protocol] protocol
721
    def initialize(fields, protocol=nil)
722
      super fields
723
      return unless protocol
724
      @records = protocol.retr_all_records fields
725
      fields.each{|f| f.result = self}  # for calculating max_field
726
    end
727

728
    # @private
729
    # calculate max_length of all fields
730
    def calculate_field_max_length
731
      max_length = Array.new(@fields.size, 0)
732
      @records.each_with_index do |rec, i|
733
        rec = @records[i] = rec.to_a if rec.is_a? RawRecord
734
        max_length.each_index do |j|
735
          max_length[j] = rec[j].length if rec[j] && rec[j].length > max_length[j]
736
        end
737
      end
738
      max_length.each_with_index do |len, i|
739
        @fields[i].max_length = len
740
      end
741
    end
742

743
    # @return [RbMysql::Field] current field
744
    def fetch_field
745
      return nil if @field_index >= @fields.length
746
      ret = @fields[@field_index]
747
      @field_index += 1
748
      ret
749
    end
750

751
    # @return [Integer] current field position
752
    def field_tell
753
      @field_index
754
    end
755

756
    # Set field position
757
    # @param [Integer] n field index
758
    # @return [Integer] previous position
759
    def field_seek(n)
760
      ret = @field_index
761
      @field_index = n
762
      ret
763
    end
764

765
    # Return specified field
766
    # @param [Integer] n field index
767
    # @return [RbMysql::Field] field
768
    def fetch_field_direct(n)
769
      raise ClientError, "invalid argument: #{n}" if n < 0 or n >= @fields.length
770
      @fields[n]
771
    end
772

773
    # @return [Array<RbMysql::Field>] all fields
774
    def fetch_fields
775
      @fields
776
    end
777

778
    # @return [Array<Integer>] length of each fields
779
    def fetch_lengths
780
      return nil unless @fetched_record
781
      @fetched_record.map{|c|c.nil? ? 0 : c.length}
782
    end
783

784
    # @return [Integer] number of fields
785
    def num_fields
786
      @fields.size
787
    end
788
  end
789

790
  # @!visibility private
791
  # Result set for prepared statement
792
  class StatementResult < ResultBase
793
    # @private
794
    # @param [Array<RbMysql::Field>] fields
795
    # @param [RbMysql::Protocol] protocol
796
    # @param [RbMysql::Charset] charset
797
    def initialize(fields, protocol, charset)
798
      super fields
799
      @records = protocol.stmt_retr_all_records @fields, charset
800
    end
801
  end
802

803
  # @!visibility public
804
  # Prepared statement
805
  # @!attribute [r] affected_rows
806
  #   @return [Integer]
807
  # @!attribute [r] insert_id
808
  #   @return [Integer]
809
  # @!attribute [r] server_status
810
  #   @return [Integer]
811
  # @!attribute [r] warning_count
812
  #   @return [Integer]
813
  # @!attribute [r] param_count
814
  #   @return [Integer]
815
  # @!attribute [r] fields
816
  #   @return [Array<RbMysql::Field>]
817
  # @!attribute [r] sqlstate
818
  #   @return [String]
819
  class Stmt
820
    include Enumerable
821

822
    attr_reader :affected_rows, :insert_id, :server_status, :warning_count
823
    attr_reader :param_count, :fields, :sqlstate
824

825
    # @private
826
    def self.finalizer(protocol, statement_id)
827
      proc do
828
        begin
829
          protocol.gc_stmt statement_id
830
        rescue => e
831
          elog("finalize method for Stmt failed", error: e)
832
        end
833
      end
834
    end
835

836
    # @private
837
    # @param [RbMysql::Protocol] protocol
838
    # @param [RbMysql::Charset] charset
839
    def initialize(protocol, charset)
840
      @protocol = protocol
841
      @charset = charset
842
      @statement_id = nil
843
      @affected_rows = @insert_id = @server_status = @warning_count = 0
844
      @sqlstate = "00000"
845
      @param_count = nil
846
      @bind_result = nil
847
    end
848

849
    # @private
850
    # parse prepared-statement and return {RbMysql::Stmt} object
851
    # @param [String] str query string
852
    # @return self
853
    def prepare(str)
854
      close
855
      begin
856
        @sqlstate = "00000"
857
        @statement_id, @param_count, @fields = @protocol.stmt_prepare_command(str)
858
      rescue ServerError => e
859
        @last_error = e
860
        @sqlstate = e.sqlstate
861
        raise
862
      end
863
      ObjectSpace.define_finalizer(self, self.class.finalizer(@protocol, @statement_id))
864
      self
865
    end
866

867
    # Execute prepared statement.
868
    # @param [Object] values values passed to query
869
    # @return [RbMysql::Stmt] self
870
    def execute(*values)
871
      raise ClientError, "not prepared" unless @param_count
872
      raise ClientError, "parameter count mismatch" if values.length != @param_count
873
      values = values.map{|v| @charset.convert v}
874
      begin
875
        @sqlstate = "00000"
876
        nfields = @protocol.stmt_execute_command @statement_id, values
877
        if nfields
878
          @fields = @protocol.retr_fields nfields
879
          @result = StatementResult.new @fields, @protocol, @charset
880
        else
881
          @affected_rows, @insert_id, @server_status, @warning_count, @info =
882
            @protocol.affected_rows, @protocol.insert_id, @protocol.server_status, @protocol.warning_count, @protocol.message
883
        end
884
        return self
885
      rescue ServerError => e
886
        @last_error = e
887
        @sqlstate = e.sqlstate
888
        raise
889
      end
890
    end
891

892
    # Close prepared statement
893
    # @return [void]
894
    def close
895
      ObjectSpace.undefine_finalizer(self)
896
      @protocol.stmt_close_command @statement_id if @statement_id
897
      @statement_id = nil
898
    end
899

900
    # @return [Array] current record data
901
    def fetch
902
      row = @result.fetch
903
      return row unless @bind_result
904
      row.zip(@bind_result).map do |col, type|
905
        if col.nil?
906
          nil
907
        elsif [Numeric, Integer, Fixnum].include? type
908
          col.to_i
909
        elsif type == String
910
          col.to_s
911
        elsif type == Float && !col.is_a?(Float)
912
          col.to_i.to_f
913
        elsif type == RbMysql::Time && !col.is_a?(RbMysql::Time)
914
          if col.to_s =~ /\A\d+\z/
915
            i = col.to_s.to_i
916
            if i < 100000000
917
              y = i/10000
918
              m = i/100%100
919
              d = i%100
920
              h, mm, s = 0
921
            else
922
              y = i/10000000000
923
              m = i/100000000%100
924
              d = i/1000000%100
925
              h = i/10000%100
926
              mm= i/100%100
927
              s = i%100
928
            end
929
            if y < 70
930
              y += 2000
931
            elsif y < 100
932
              y += 1900
933
            end
934
            RbMysql::Time.new(y, m, d, h, mm, s)
935
          else
936
            RbMysql::Time.new
937
          end
938
        else
939
          col
940
        end
941
      end
942
    end
943

944
    # Return data of current record as Hash.
945
    # The hash key is field name.
946
    # @param [Boolean] with_table if true, hash key is "table_name.field_name".
947
    # @return [Hash] record data
948
    def fetch_hash(with_table=nil)
949
      @result.fetch_hash with_table
950
    end
951

952
    # Set retrieve type of value
953
    # @param [Numeric / Fixnum / Integer / Float / String / RbMysql::Time / nil] args value type
954
    # @return [RbMysql::Stmt] self
955
    def bind_result(*args)
956
      if @fields.length != args.length
957
        raise ClientError, "bind_result: result value count(#{@fields.length}) != number of argument(#{args.length})"
958
      end
959
      args.each do |a|
960
        raise TypeError unless [Numeric, Fixnum, Integer, Float, String, RbMysql::Time, nil].include? a
961
      end
962
      @bind_result = args
963
      self
964
    end
965

966
    # Iterate block with record.
967
    # @yield [Array] record data
968
    # @return [RbMysql::Stmt] self
969
    # @return [Enumerator] If block is not specified
970
    def each(&block)
971
      return enum_for(:each) unless block
972
      while rec = fetch
973
        block.call rec
974
      end
975
      self
976
    end
977

978
    # Iterate block with record as Hash.
979
    # @param [Boolean] with_table if true, hash key is "table_name.field_name".
980
    # @yield [Hash] record data
981
    # @return [RbMysql::Stmt] self
982
    # @return [Enumerator] If block is not specified
983
    def each_hash(with_table=nil, &block)
984
      return enum_for(:each_hash, with_table) unless block
985
      while rec = fetch_hash(with_table)
986
        block.call rec
987
      end
988
      self
989
    end
990

991
    # @return [Integer] number of record
992
    def size
993
      @result.size
994
    end
995
    alias num_rows size
996

997
    # Set record position
998
    # @param [Integer] n record index
999
    # @return [void]
1000
    def data_seek(n)
1001
      @result.data_seek(n)
1002
    end
1003

1004
    # @return [Integer] current record position
1005
    def row_tell
1006
      @result.row_tell
1007
    end
1008

1009
    # Set current position of record
1010
    # @param [Integer] n record index
1011
    # @return [Integer] previous position
1012
    def row_seek(n)
1013
      @result.row_seek(n)
1014
    end
1015

1016
    # @return [Integer] number of columns for last query
1017
    def field_count
1018
      @fields.length
1019
    end
1020

1021
    # ignore
1022
    # @return [void]
1023
    def free_result
1024
    end
1025

1026
    # Returns RbMysql::Result object that is empty.
1027
    # Use fetch_fields to get list of fields.
1028
    # @return [RbMysql::Result]
1029
    def result_metadata
1030
      return nil if @fields.empty?
1031
      Result.new @fields
1032
    end
1033
  end
1034

1035
  # @!visibility public
1036
  # @!attribute [rw] year
1037
  #   @return [Integer]
1038
  # @!attribute [rw] month
1039
  #   @return [Integer]
1040
  # @!attribute [rw] day
1041
  #   @return [Integer]
1042
  # @!attribute [rw] hour
1043
  #   @return [Integer]
1044
  # @!attribute [rw] minute
1045
  #   @return [Integer]
1046
  # @!attribute [rw] second
1047
  #   @return [Integer]
1048
  # @!attribute [rw] neg
1049
  #   @return [Boolean] negative flag
1050
  # @!attribute [rw] second_part
1051
  #   @return [Integer]
1052
  class Time
1053
    # @param [Integer] year
1054
    # @param [Integer] month
1055
    # @param [Integer] day
1056
    # @param [Integer] hour
1057
    # @param [Integer] minute
1058
    # @param [Integer] second
1059
    # @param [Boolean] neg negative flag
1060
    # @param [Integer] second_part
1061
    def initialize(year=0, month=0, day=0, hour=0, minute=0, second=0, neg=false, second_part=0)
1062
      @date_flag = !(hour && minute && second)
1063
      @year, @month, @day, @hour, @minute, @second, @neg, @second_part =
1064
        year.to_i, month.to_i, day.to_i, hour.to_i, minute.to_i, second.to_i, neg, second_part.to_i
1065
    end
1066
    attr_accessor :year, :month, :day, :hour, :minute, :second, :neg, :second_part
1067
    alias mon month
1068
    alias min minute
1069
    alias sec second
1070

1071
    # @private
1072
    def ==(other)
1073
      other.is_a?(RbMysql::Time) &&
1074
        @year == other.year && @month == other.month && @day == other.day &&
1075
        @hour == other.hour && @minute == other.minute && @second == other.second &&
1076
        @neg == neg && @second_part == other.second_part
1077
    end
1078

1079
    # @private
1080
    def eql?(other)
1081
      self == other
1082
    end
1083

1084
    # @return [String] "yyyy-mm-dd HH:MM:SS"
1085
    def to_s
1086
      if @date_flag
1087
        sprintf "%04d-%02d-%02d", year, mon, day
1088
      elsif year == 0 and mon == 0 and day == 0
1089
        h = neg ? hour * -1 : hour
1090
        sprintf "%02d:%02d:%02d", h, min, sec
1091
      else
1092
        sprintf "%04d-%02d-%02d %02d:%02d:%02d", year, mon, day, hour, min, sec
1093
      end
1094
    end
1095

1096
    # @return [Integer] yyyymmddHHMMSS
1097
    def to_i
1098
      sprintf("%04d%02d%02d%02d%02d%02d", year, mon, day, hour, min, sec).to_i
1099
    end
1100

1101
    # @private
1102
    def inspect
1103
      sprintf "#<#{self.class.name}:%04d-%02d-%02d %02d:%02d:%02d>", year, mon, day, hour, min, sec
1104
    end
1105

1106
  end
1107

1108
end
1109

1110