Class: Ea::Qea::Infrastructure::TableReader

Inherits:
Object
  • Object
show all
Defined in:
lib/ea/qea/infrastructure/table_reader.rb

Overview

TableReader reads data from a single table in a QEA SQLite database.

This class provides methods to query and retrieve records from database tables with filtering and counting capabilities.

Examples:

Read all records from a table

reader = TableReader.new(db_connection, "t_object")
objects = reader.all

Read with filtering

reader = TableReader.new(db_connection, "t_object")
classes = reader.where("Object_Type = ?", "Class")

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(database, table_name) ⇒ TableReader

Initialize a new table reader

Parameters:

  • database (SQLite3::Database) —

    The database connection

  • table_name (String) —

    The table name to read from

Raises:

  • (ArgumentError) —

    if database or table_name is nil



26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 26

def initialize(database, table_name)
  raise ArgumentError, "database cannot be nil" if database.nil?

  if table_name.nil? || table_name.empty?
    raise ArgumentError,
          "table_name cannot be nil or empty"
  end

  @database = database
  @table_name = table_name
end

Instance Attribute Details

#database ⇒ Object (readonly)

Returns the value of attribute database.



19
20
21
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 19

def database
  @database
end

#table_name ⇒ Object (readonly)

Returns the value of attribute table_name.



19
20
21
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 19

def table_name
  @table_name
end

Instance Method Details

#all(limit: nil, offset: nil) ⇒ Array<Hash>

Read all records from the table

(optional)

Examples:

reader.all
# => [{"Object_ID"=>1, "Name"=>"MyClass", ...}, ...]

With limit

reader.all(limit: 10)
# => Returns first 10 records

Parameters:

  • limit (Integer, nil) (defaults to: nil) —

    Maximum number of records to return

  • offset (Integer, nil) (defaults to: nil) —

    Number of records to skip (optional)

Returns:

  • (Array<Hash>) —

    Array of record hashes



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 61

def all(limit: nil, offset: nil) # rubocop:disable Metrics/MethodLength
  query = "SELECT * FROM #{@table_name}"
  params = []

  if limit
    query += " LIMIT ?"
    params << limit
  end

  if offset
    query += " OFFSET ?"
    params << offset
  end

  @database.execute(query, params)
end

#count ⇒ Integer

Count all records in the table

Returns:

  • (Integer) —

    Total number of records



116
117
118
119
120
121
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 116

def count
  result = @database.execute(
    "SELECT COUNT(*) as count FROM #{@table_name}",
  )
  result.first["count"]
end

#count_where(conditions, *values) ⇒ Integer

Count records matching a WHERE clause

(without the WHERE keyword)

Examples:

reader.count_where("Object_Type = ?", "Class")
# => 42

Parameters:

  • conditions (String) —

    The WHERE clause

  • values (Array) —

    Values for parameterized query placeholders

Returns:

  • (Integer) —

    Number of matching records



133
134
135
136
137
138
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 133

def count_where(conditions, *values)
  query = "SELECT COUNT(*) as count FROM #{@table_name} " \
          "WHERE #{conditions}"
  result = @database.execute(query, values.flatten)
  result.first["count"]
end

#execute_query(sql, params = []) ⇒ Array<Hash>

Execute a custom SQL query on this table

Examples:

reader.execute_query(
  "SELECT Name, COUNT(*) as count FROM #{reader.table_name}
   GROUP BY Name"
)

Parameters:

  • sql (String) —

    The SQL query (should reference the table)

  • params (Array) (defaults to: []) —

    Parameters for the query

Returns:

  • (Array<Hash>) —

    Query results



184
185
186
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 184

def execute_query(sql, params = [])
  @database.execute(sql, params)
end

#exists?(conditions, *values) ⇒ Boolean

Check if any records match the given conditions

(without the WHERE keyword)

Examples:

reader.exists?("Name = ?", "MyClass")
# => true or false

Parameters:

  • conditions (String) —

    The WHERE clause

  • values (Array) —

    Values for parameterized query placeholders

Returns:

  • (Boolean) —

    true if at least one record matches



198
199
200
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 198

def exists?(conditions, *values)
  count_where(conditions, *values).positive?
end

#find_by_pk(primary_key_column, value) ⇒ Hash?

Find a single record by primary key value

Examples:

reader.find_by_pk("Object_ID", 123)
# => {"Object_ID"=>123, "Name"=>"MyClass", ...}

Parameters:

  • primary_key_column (String) —

    The primary key column name

  • value (Object) —

    The primary key value to search for

Returns:

  • (Hash, nil) —

    The matching record hash, or nil if not found



149
150
151
152
153
154
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 149

def find_by_pk(primary_key_column, value)
  query = "SELECT * FROM #{@table_name} " \
          "WHERE #{primary_key_column} = ? LIMIT 1"
  result = @database.execute(query, [value])
  result.first
end

#find_first(conditions, *values) ⇒ Hash?

Find first record matching a WHERE clause

(without the WHERE keyword) or nil if not found

Examples:

reader.find_first("Name = ?", "MyClass")
# => {"Object_ID"=>123, "Name"=>"MyClass", ...}

Parameters:

  • conditions (String) —

    The WHERE clause

  • values (Array) —

    Values for parameterized query placeholders

Returns:

  • (Hash, nil) —

    The first matching record hash,



167
168
169
170
171
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 167

def find_first(conditions, *values)
  query = "SELECT * FROM #{@table_name} WHERE #{conditions} LIMIT 1"
  result = @database.execute(query, values.flatten)
  result.first
end

#select(columns, conditions = nil, *values, limit: nil) ⇒ Array<Hash>

Read records with custom column selection

Examples:

reader.select(["Object_ID", "Name"], "Object_Type = ?", "Class")
# => [{"Object_ID"=>1, "Name"=>"Class1"}, ...]

Parameters:

  • columns (Array<String>) —

    Column names to select

  • conditions (String, nil) (defaults to: nil) —

    Optional WHERE clause

  • values (Array) —

    Values for parameterized query placeholders

  • limit (Integer, nil) (defaults to: nil) —

    Maximum number of records to return

Returns:

  • (Array<Hash>) —

    Array of record hashes with selected columns



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 213

def select(columns, conditions = nil, *values, limit: nil) # rubocop:disable Metrics/MethodLength
  column_list = columns.join(", ")
  query = "SELECT #{column_list} FROM #{@table_name}"

  params = []
  if conditions
    query += " WHERE #{conditions}"
    params = values.flatten
  end

  if limit
    query += " LIMIT ?"
    params << limit
  end

  @database.execute(query, params)
end

#table_exists? ⇒ Boolean

Returns true when the table exists in the database.

Returns:

  • (Boolean) —

    true when the table exists in the database



39
40
41
42
43
44
45
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 39

def table_exists?
  row = @database.get_first_row(
    "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
    [@table_name]
  )
  !row.nil?
end

#where(conditions, *values, limit: nil, offset: nil) ⇒ Array<Hash>

Read records matching a WHERE clause

(without the WHERE keyword) to return (optional)

Examples:

Simple filter

reader.where("Object_Type = ?", "Class")

Multiple conditions

reader.where("Object_Type = ? AND Package_ID = ?", "Class", 5)

With limit

reader.where("Object_Type = ?", "Class", limit: 10)

Parameters:

  • conditions (String) —

    The WHERE clause

  • values (Array) —

    Values for parameterized query placeholders

  • limit (Integer, nil) (defaults to: nil) —

    Maximum number of records

  • offset (Integer, nil) (defaults to: nil) —

    Number of records to skip (optional)

Returns:

  • (Array<Hash>) —

    Array of matching record hashes



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/ea/qea/infrastructure/table_reader.rb', line 96

def where(conditions, *values, limit: nil, offset: nil) # rubocop:disable Metrics/MethodLength
  query = "SELECT * FROM #{@table_name} WHERE #{conditions}"
  params = values.flatten

  if limit
    query += " LIMIT ?"
    params << limit
  end

  if offset
    query += " OFFSET ?"
    params << offset
  end

  @database.execute(query, params)
end