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

._calc_yyyymm(season) ⇒ Object

try match by code and seaons (via league_periods)

todo/fix - move up for reuse (duplicated in league_period etc) - why? why not?



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/sportdb/catalogs/league.rb', line 205

def self._calc_yyyymm( season )
  start_yyyymm =     if season.calendar?
                        "#{season.start_year}01".to_i
                     else
                        "#{season.start_year}07".to_i
                     end

  end_yyyymm   =     if season.calendar?
                        "#{season.end_year}12".to_i
                     else
                        "#{season.end_year}06".to_i
                     end

[start_yyyymm, end_yyyymm]
end

._query_by_code(code:) ⇒ Object 



48
49
50
51
52
53
54
55
 
# File 'lib/sportdb/catalogs/league.rb', line 48

def self._query_by_code( code: )
 execute( <<-SQL )
 SELECT #{self.columns.join(', ')}
 FROM leagues
 INNER JOIN league_codes ON leagues.key  = league_codes.key
 WHERE league_codes.code = '#{code}'
SQL
end

._query_by_code_and_country(code:, country:) ⇒ Object 



57
58
59
60
61
62
63
64
65
 
# File 'lib/sportdb/catalogs/league.rb', line 57

def self._query_by_code_and_country( code:, country: )
 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}'                     
SQL
end

._query_by_code_and_season(code:, start_yyyymm:, end_yyyymm:) ⇒ Object 



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/sportdb/catalogs/league.rb', line 67

def self._query_by_code_and_season( code:, start_yyyymm:,
                                           end_yyyymm: )
  execute( <<-SQL )
  SELECT #{self.columns.join(', ')}
  FROM leagues
  WHERE leagues.key IN 
  (SELECT league_periods.key
   FROM league_periods
   INNER JOIN league_period_codes ON league_periods.id = league_period_codes.league_period_id
   WHERE league_period_codes.code = '#{code}' AND
         league_period_codes.start_yyyymm <=  #{start_yyyymm} AND
         league_period_codes.end_yyyymm  >= #{end_yyyymm}
  )
 SQL
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, season: 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
 
# File 'lib/sportdb/catalogs/league.rb', line 85

def self.match_by_code( code,
                         country: nil,
                         season:  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 = if country.nil? && season.nil?
           ## note: returns empty array if no match and NOT nil
           _query_by_code( code: code )
         elsif country && season.nil?   ## 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 )
            _query_by_code_and_country( code: code, country: country_rec.key )
         elsif season && country.nil?               
            season = Season( season )
            start_yyyymm, end_yyyymm = _calc_yyyymm( season )
            _query_by_code_and_season( code: code, start_yyyymm: start_yyyymm,
                                                   end_yyyymm: end_yyyymm )
         else
             raise ArgumentError, "match_by_code - code and optional country or season expected"
         end

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

.match_by_name(name, country: nil) ⇒ Object 



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

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 



154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/sportdb/catalogs/league.rb', line 154

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