Class: MobyUtil::DBAccess

Inherits:
Object show all
Includes:
Singleton
Defined in:
lib/tdriver/util/database/access.rb

Constant Summary collapse

DB_TYPE_MYSQL = 
'mysql'
DB_TYPE_SQLITE = 
'sqlite'

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ DBAccess

description

Initialize the singleton connection is maintained as long as the connectivity parameters remain the same this is to avoid constant connect as this takes time



35
36
37
38
 
# File 'lib/tdriver/util/database/access.rb', line 35

def initialize
  @@_connections = {}
  @@_mysql = nil
end

Class Method Details

.affected_rows(dbc) ⇒ Object

description

Retunrs the number of affected rows on the latest sql query on the given MobyUtil::DBConnection

arguments

dbc

MobyUtil::DBConnection
 description: object with the connection details of an open sql connection

returns

Integer

description: number of rows affected

throws

ArgumentError

description: if the argument provided is not the right object type



176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/tdriver/util/database/access.rb', line 176

def self.affected_rows(dbc)

  dbc.check_type( MobyUtil::DBConnection, "Wrong argument type $1 for database connection object (expected $2)" )
  
  # Check for exsting connection for that host and create it if needed
  if !@@_connections.has_key?( dbc.host + dbc.db_type + dbc.database_name ) # make connection ID unique by using host, type and db on the key
    dbc.dbh = connect_db(  dbc.db_type, dbc.host, dbc.username, dbc.password, dbc.database_name )
    @@_connections[ dbc.host + dbc.db_type + dbc.database_name ] = dbc
  end
  result = 0
  if dbc.db_type == DB_TYPE_MYSQL
    result = @@_connections[ dbc.host + dbc.db_type + dbc.database_name ].dbh.affected_rows
  elsif dbc.db_type == DB_TYPE_SQLITE
    result = @@_connections[ dbc.host + dbc.db_type + dbc.database_name ].dbh.changes
  end
  return result
end

.connections ⇒ Object

description

Class Method that returns existing connections



43
44
45
 
# File 'lib/tdriver/util/database/access.rb', line 43

def self.connections()
  return @@_connections
end

.query(dbc, query_string) ⇒ Object

description

Runs an SQL query on the on the given MobyUtil::DBConnection

arguments

dbc

MobyUtil::DBConnection
 description: object with the connection details of an open sql connection

query_string

String
 description: database-specific SQL query (note that mysql and sqlite have slightly different syntax)
 example: "select * from tdriver_locale;"

returns

Array

description: Array of rows returned by the server. Each row is an array of String values.

throws

ArgumentError

description: if the argument provided is not the right object type



68
69
70
71
72
73
74
75
76
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/tdriver/util/database/access.rb', line 68

def self.query( dbc, query_string )
  # Create first instance of this class if it doesn't exist
  self.instance
  
  dbc.check_type( MobyUtil::DBConnection, "Wrong argument type $1 for database connection object (expected $2)" )      
  
  query_string.check_type( String, "Wrong variable type $1 for database query string (expected $2)")
  query_string.not_empty( "Database query string must not be empty string" )

  db_type = dbc.db_type
  host = dbc.host
  username = dbc.username
  password = dbc.password
  database_name = dbc.database_name
  
  # Check creation parameters

  db_type.check_type( String, "Wrong argument type $1 for database type (expected $2)" )

  db_type.validate( [ DB_TYPE_MYSQL, DB_TYPE_SQLITE ], "Unsupported database type $1 (expected $2)" )

  if ( db_type == DB_TYPE_MYSQL )

    host.check_type( String, "Wrong variable type $1 for host (expected $2)" )
    host.not_empty( "Host must not be empty string" )

    username.check_type( String, "Wrong variable type $1 for username (expected $2)" )
    username.not_empty( "Username must not be empty string" )

    password.check_type( String, "Wrong variable type $1 for password (expected $2)")

  end

  database_name.check_type( String, "Wrong variable type $1 for database name (expected $2)" )
  database_name.not_empty( "Database name must not be empty string" )
        
  # Check for exsting connection for that host and create it if needed
  if !@@_connections.has_key?( host + db_type + database_name ) # make connection ID unique by using host, type and db on the key
    dbc.dbh = connect_db( db_type, host, username, password, database_name )
    @@_connections[ host + db_type + database_name ] = dbc
  end
  
  if db_type == DB_TYPE_MYSQL

    begin
      query_result = @@_connections[ host + db_type + database_name ].dbh.query( query_string ) # identical?
    rescue 
      if @@_mysql        
          @@_mysql.close
          @@_mysql=nil      
      end
      #Possible timeout in query attempt to recreate the connection and redo the query
      dbc.dbh = connect_db( db_type, host, username, password, database_name )
      @@_connections[ host + db_type + database_name ] = dbc
      query_result = @@_connections[ host + db_type + database_name ].dbh.query( query_string ) # identical?
    end

  elsif dbc.db_type == DB_TYPE_SQLITE

    query_result = @@_connections[ host + db_type + database_name ].dbh.query( query_string ) # identical?

  end
  
  # Return a uniform set of results as an array of rows, rows beeing an array of values ( Array<Array<String>> )
  result = []

  if db_type == DB_TYPE_MYSQL and !query_result.nil?

    query_result.num_rows.times do |i|

      result << query_result.fetch_row

    end        

  elsif db_type == DB_TYPE_SQLITE and !query_result.nil?

    # Create Array<SQLite3::ResultSet::ArrayWithTypesAndFields<String>> type result
    # it effectively behaves the same as with Array<Array<String>> but the inner Arrays have .fields and .types properties 
    # which return the column name and type for each value on the row (Array) returned.
    while ( row = query_result.next )
      result << row
    end

    # it is essentially a prepare method so we need to call close to free the connection
    query_result.close 

  end

  result

end

Instance Method Details

#close_db ⇒ Object

description

Function closes MySQL connection



197
198
199
200
201
202
203
204
 
# File 'lib/tdriver/util/database/access.rb', line 197

def close_db()
        
  if @@_mysql        
    @@_mysql.close
    @@_mysql=nil      
  end      
  
end