imdbpy2sql doesn't create all foreign keys

Issue #17 resolved
Anonymous created an issue

It seems that imdbpy2sql doesn't create all of the foreign keys when creating the tables, indexes, etc. Some of them are there, just not all.

I'm using MySQL with my tables configured as InnoDB (required if you want foreign keys.) SQLalchemy is also involved there too. Imdbpy version 4.9 and MySQL 5.1.

In the file - alchemyadapter.py function: def addForeignKeys(self, mapTables, ifNotExists=True):

I added to the try: except: section: print fkName print "Description: %s" % e

to see why it was failing to create the FKs that I was expecting, and the error is: constrain error: fk_name_id_2 Description: (OperationalError) (1005, "Can't create table 'hdtg.#sql-43e7_44' (errno: 121)") 'ALTER TABLE cast_info ADD CONSTRAINT fk_name_id_2 FOREIGN KEY(person_id) REFERENCES name (id)' ()

11 FKs failed with the same kind of error. After some Googling, I believe this error is due to the FKs needing to be uniquely named per database, and your naming convention is unique per table.

Instead of naming the FK fk_<foreign_table_name><foreign_col_name><col_num>, maybe add the parent table in there as well - that way fk_title_id_2 becomes fk_cast_info_title_id_2 and fk_complete_cast_title_id_2

Hope this helps!

Comments (4)

  1. Davide Alberani repo owner

    Thanks for the bug report.

    There are some known issues with the creation of the indexes. I can't reproduce your situation with MySQL 5.5 (using SQLAlchemy); for sure there are problems with Postgresql. I'm not too sure it's a matter of uniqueness of the keys: this should be already solved.

    Anyway, if possible try with the code from the repository: it should contain some improvements.

    Thanks again, and if you have other information/hint, please let us know.

  2. Andrew D Bate

    Does this mean that imdbpy2sql version 6.2 does not create any foreign keys?

    How can we get this functionality back? Is there an old version of imdbpy2sql where foreign keys are created?

  3. Davide Alberani repo owner

    Hi @Andrew D Bate , the old schema of the database, with foreign keys, should still be available in the https://github.com/alberanid/imdbpy/tree/imdbpy-legacy branch, which is at the moment (mostly) unsupported. In master foreign keys were removed because their creation was problematic with some databases.

    The main problem here is that the plain text data files that the 'sql' data access system parsed, is no longer supported by IMDb since the end of 2017, so you will not have any new data from them. So, we have no real reason to update it.

    IMDb now release a new set of data (that we call 's3', since they were originally distributed only on AWS S3), with fewer data, but easier to parse. You can parse them using the version of IMDbPY in master (will be released after we receive some feedback); see https://github.com/alberanid/imdbpy/blob/master/docs/README.s3.txt

    Hope this helps.

  4. Log in to comment