Class: CatalogDb::Metal::League

Inherits:
Record show all
Defined in:
lib/sportdb/catalogs/league.rb

Class Method Summary collapse

Methods inherited from Record

_city, _league, _to_city, _to_country, _to_league, database, database=, database?

Methods inherited from BaseRecord

_country, _to_bool, columns, columns=, count, execute, tablename, tablename=

Class Method Details

._build_league(row) ⇒ Object 



36
37
38
39
40
41
42
43
44
45
 
# File 'lib/sportdb/catalogs/league.rb', line 36

def self._build_league( row )
   ## note: cache structs by key (do NOT rebuild duplicates; reuse)
   cache[ row[0] ] ||= Sports::League.new(
                           key: row[0],
                           name: row[1],
                           intl: _to_bool( row[2] ),
                           clubs: _to_bool( row[3] ),
                           country: row[4] ? _to_country( row[4] ) : nil,
                        )
end

._record(key) ⇒ Object

use _record! as name - why? why not?



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/sportdb/catalogs/league.rb', line 17

def self._record( key )  ## use _record! as name - why? why not?
   if (rec = cache[ key ])
     rec   ## return cached
   else  ## query and cache and return
   rows = execute( <<-SQL )
 SELECT #{self.columns.join(', ')}
 FROM leagues
 WHERE leagues.key = '#{key}'
SQL

     ## todo/fix: also assert for rows == 1 AND NOT MULTIPLE records - why? why not?
     if rows.empty?
       raise ArgumentError, "league record with key #{key} not found"
     else
       _build_league( rows[0] )
     end
   end
end

.cache ⇒ Object 



14
 
# File 'lib/sportdb/catalogs/league.rb', line 14

def self.cache() @cache ||= Hash.new; end

.match_by_code(code, country: nil) ⇒ Object 



47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/sportdb/catalogs/league.rb', line 47

def self.match_by_code( code,
                         country: nil )
  ## note: match must for now always include name
  ###  todo/fix: allow special normalize formula for
  ##                 code - why? why not?
  ##              e.g. allow ö1 or ö or such - why? why not?
  code = normalize( code )

  rows = nil
  if country.nil?
      ## note: returns empty array if no match and NOT nil
       rows =  execute( <<-SQL )
  SELECT #{self.columns.join(', ')}
  FROM leagues
  INNER JOIN league_codes ON leagues.key  = league_codes.key
  WHERE league_codes.code = '#{code}'
SQL
  else  ## filter by country
    ## note: also skip international leagues & cups (e.g. champions league etc.) for now - why? why not?

    ## note: country assumes / allows the country key or fifa code for now
    ## note: allow passing in of country struct too
    country_rec = _country( country )

    rows = execute( <<-SQL )
    SELECT #{self.columns.join(', ')}
    FROM leagues
    INNER JOIN league_codes ON leagues.key  = league_codes.key
    WHERE league_codes.code = '#{code}' AND
          leagues.country_key = '#{country_rec.key}'

SQL
  end

   ## wrap results array into struct records
   rows.map {|row| _build_league( row )}
end

.match_by_name(name, country: nil) ⇒ Object 



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
 
# File 'lib/sportdb/catalogs/league.rb', line 85

def self.match_by_name( name,
                         country: nil )
  ## note: match must for now always include name
  name = normalize( unaccent(name) )

  rows = nil
  if country.nil?
      ## note: returns empty array if no match and NOT nil
       rows =  execute( <<-SQL )
  SELECT #{self.columns.join(', ')}
  FROM leagues
  INNER JOIN league_names ON leagues.key  = league_names.key
  WHERE league_names.name = '#{name}'
SQL
  else  ## filter by country
    ## note: also skip international leagues & cups (e.g. champions league etc.) for now - why? why not?

    ## note: country assumes / allows the country key or fifa code for now
    ## note: allow passing in of country struct too
    country_rec = _country( country )

    rows = execute( <<-SQL )
    SELECT #{self.columns.join(', ')}
    FROM leagues
    INNER JOIN league_names ON leagues.key  = league_names.key
    WHERE league_names.name = '#{name}' AND
          leagues.country_key = '#{country_rec.key}'

SQL
  end

   ## wrap results array into struct records
   rows.map {|row| _build_league( row )}
end

.match_by_name_or_code(q, country: nil) ⇒ Object 



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
159
160
161
162
163
 
# File 'lib/sportdb/catalogs/league.rb', line 120

def self.match_by_name_or_code( q,
                         country: nil )
  name = normalize( unaccent(q) )
  code = normalize( q )

  rows = nil
  if country.nil?
      ## note: returns empty array if no match and NOT nil
       rows =  execute( <<-SQL )
  SELECT #{self.columns.join(', ')}
  FROM leagues
  INNER JOIN league_names ON leagues.key  = league_names.key
  WHERE league_names.name = '#{name}'
  UNION
  SELECT #{self.columns.join(', ')}
  FROM leagues
  INNER JOIN league_codes ON leagues.key  = league_codes.key
  WHERE league_codes.code = '#{code}'
SQL
  else  ## filter by country
    ## note: also skip international leagues & cups (e.g. champions league etc.) for now - why? why not?

    ## note: country assumes / allows the country key or fifa code for now
    ## note: allow passing in of country struct too
    country_rec = _country( country )

    rows = execute( <<-SQL )
    SELECT #{self.columns.join(', ')}
    FROM leagues
    INNER JOIN league_names ON leagues.key  = league_names.key
    WHERE league_names.name = '#{name}' AND
          leagues.country_key = '#{country_rec.key}'
    UNION
    SELECT #{self.columns.join(', ')}
    FROM leagues
    INNER JOIN league_codes ON leagues.key  = league_codes.key
    WHERE league_codes.code = '#{code}' AND
          leagues.country_key = '#{country_rec.key}'
SQL
  end

   ## wrap results array into struct records
   rows.map {|row| _build_league( row )}
end