Champions League full database

[Maria A. Flores]

Hi all,

First of all, thank you for this great effort. I'm new, both to Ruby and opensport, so sorry if my question is silly.

I had some problems with activerecord and json gems when running sportdb in the latest version of Ruby, so I managed to install and use rvm in order to run it in Ruby 1.9 and now is working.

When I run sportdb new all, I'm getting a lot of warnings about missing info:

...

[info] skipping line (no match found) ...

...

[warn] city with key wil missing

Finally, the process ends with the error:

[fatal] event reader - record for team key >moskva< not found

But, if I want to build a new database with only the info about Champions League, including historic data (all the data available in https://github.com/openfootball/europe-champions-league),

would you point me to the correct procedure to do it?

Thank you!

[Maria A. Flores]

More info on the subject, I have successfully created the historical world cup database using the instructions in the thread:

https://groups.google.com/forum/#!topic/opensport/jYNVDF_QSJA

But I'm not able to do the same with the Champions League data, using:

world  'world.db', setup: 'countries'

football 'clubs'

#football 'at-austria',     setup: 'clubs'

#football 'de-deutschland', setup: 'clubs'

#football 'eng-england',    setup: 'clubs'

#football 'es-espana',      setup: 'clubs'

#football 'it-italy',       setup: 'clubs'

#football 'fr-france',      setup: 'clubs'

#football 'ch-confoederatio-helvetica', setup: 'clubs'

football 'clubs', setup: 'history'

football 'europe-champions-league', setup: 'history'

[Gerald Bauer]

Hello,

Welcome to football.db.

> [fatal] event reader - record for team key >moskva< not found

You might be missing the russian teams / clubs. See the /ru-russia
repo [1] it includes in teams.txt:

moskva, Moskva, city: Moscow

Cheers.

[1] https://github.com/openfootball/ru-russia/blob/master/teams.txt

[Maria A. Flores]

Hello Gerald,

Thank you for your quick reply.

I'm trying to build a Champions League database, but it seems that I'm missing something.

I'm using this Datafile:

world  'world.db', setup: 'countries'

football 'clubs'

football 'at-austria',     setup: 'clubs'

football 'de-deutschland', setup: 'clubs'

football 'eng-england',    setup: 'clubs'

football 'es-espana',      setup: 'clubs'

football 'it-italy',       setup: 'clubs'

football 'fr-france',      setup: 'clubs'

football 'ch-confoederatio-helvetica', setup: 'clubs'

football 'ru-russia'

football 'europe-champions-league'

football 'europe-champions-league', setup: 'history'

Now I get this error:

[fatal] event reader - record for team key >praha< not found

As I'm getting the Czech Republic clubs in the generic "clubs", maybe the problem is that there isn't a club called Praha (it seems that there are more than one club in Praha city).

Also, how could I import only the teams from Russia data, instead of all the data?

Kind regards

[Maria A. Flores]

Hello again and sorry for the double posts,

I solved this problem and other similars changing the keys for some teams in the data files (id for Spara Praha was spartapraha instead of praha, the same for St.Petersburg, and so on).

But now, taking a look at the logs, I realised that some world cities have problems in the format of the txt, so I tried to solved it, but it seems that the task is taking me too much time. I don't know really what is the problem, the import just stops importing this line and other similar:

Pex̌awar|Pishōr|Pishāwar [Peshawar|Pekhawar], 1_218_773, m:1.8m  ## Khyber Pakhtunkhwa

h,  Hovedstaden [Greater Copenhagen|Capital Region], Hillerød, 2_561 km², 1_714_486   ## use h - why? why not? in common use?

sj, Sjælland [Zealand],                              Sorø,     7_273 km²,   817_907   ## use sl - why? why not? in common use? 

I could avoid the error commenting or deleting these lines, but for sure this is not a solution. Any ideas?

Also, I've tried to use the prebuilt db in 2014 and then importing the new Champions League data using:

sportdb --dbname football.db update --include 'europe-champions-league'

I tried importing all the data and got this error:

*** error: SQLite3::ConstraintException: NOT NULL constraint failed: persons.country_id: INSERT INTO "persons" ("key", "name", "created_at", "updated_at") VALUES (?, ?, ?, ?)

Then, I deleted the previous data, up to 2013-2014 competition, and the problem I'm facing is this one, which I have no idea how to tackle it:

[info] parsing data (fixture) 'europe-champions-league!/2013-14/cl' (europe-champions-league/2013-14/cl.yml)...

*** [DEPRECATED] old event config format w/ yaml, please use new plain text format >europe-champions-league/2013-14/cl.yml<

[info]   build_title_table_for - deprecated API - use TitleMapper.new instead

nil

[warn]   !!!! no round match found for date 2013-09-19T12:00:00+00:00

*** error: 70277522327500 is out of range for ActiveRecord::Type::Integer with limit 4

Any clues about how to continue?

Thank you

[Gerald Bauer]

Hello,
Thanks for trying and not giving up. Hard to tell without running
a build myself. I will try to run a build later this week

> *** error: 70277522327500 is out of range for ActiveRecord::Type::Integer with limit 4

Hard to tell. I recommend using the -vebose switch / flag for more
details about the error (e.g. is it the thread id / tid for log? or
something). Sorry I have no clue. Needs more details.

Again thanks for trying. Keep it up. Cheers.

[Gerald Bauer]

Hello,

I've updated / fixed the two broken club keys e.g. moskva and praha
in the /europe-champions-league repo. Now everything works as
advertised e.g. use the cl.rb [1] datafile.

Try:

$ sportdb new cl

That's it. Good luck. Cheers.

[1] https://github.com/openfootball/datafile/blob/master/cl.rb

[Maria A. Flores]

Hi Gerald,

Wow, it works, no more errors. Thanks a lot!

Do you think there is a way to import the old data too? I mean, the data from 1950's in the "archive" folder:

https://github.com/openfootball/europe-champions-league/tree/master/archive

Kind regards

[Gerald Bauer]

Hello,

> Wow, it works, no more errors. Thanks a lot!

Great to hear it. Thanks for posting an update.

> Do you think there is a way to import the old data too?
> I mean, the data from 1950's in the "archive" folder:

Sorry this data in the archive folder is a work-in-progress I
started some time ago. It's not yet ready for processing. If anyone
wants to help out, more than welcome ;-)

Cheers.

[Maria A. Flores]

Hello,

I'd dare to ask about the archive data... there is a lot of work to do?

About the imported seasons, I'm trying to do some queries. There is some documentation about the tables and fields?

My first attempt is to try and get the winners for each season, but I can't see a clear way to do this. Must I use the rounds.title field and filter the games with title "Final"? Or there is a cleaner way to do it?

Kind regards

[Gerald Bauer]

> I'd dare to ask about the archive data... there is a lot of work to do?

Yes, a lot. You might start with one season to see how it works.

> There is some documentation about the tables and fields?

Use something like SQLite Studio to browse (the live) table
structure. An offline version is online ->
http://openfootball.github.io/schema

> My first attempt is to try and get the winners for each season, but I can't see a clear way to do this. Must I use the rounds.title field and filter the games with title "Final"? Or there is a cleaner way to do it?

For getting started with SQL queries for now there's only this doc
thanks to Joe Kampschmidt
-> http://openfootball.github.io/docs/sql-queries-intro.html

Good luck. Cheers.