mozilla/datazilla — pull request #35 by jeads (2012-06-09 21:41:55+00:00)
Title: Loadtestdata refactor

I did some research and decided to go ahead and refactor `load_test_data()` to make use of MySQL's `INSERT ... ON DUPLICATE KEY UPDATE` for all reference data. I was concerned about taking this approach because of the frequency of updates but I think there is a compelling argument for it and we need to get rid of the call to `get_reference_data()`.

This branch removes the call to `get_reference_data()` from `load_test_data()`. All of the `get_*_id()` methods have been converted to use an `ON DUPLICATE KEY UPDATE` strategy. The reference data is first inserted and the associated id is then selected.

I used the `manage.py` command, `transfer_data`, to test every line of code changed. I inserted several thousand data structures into `test_1_perftest` to test entries into a database already containing data and I also created a new project, jeads, to test the insertion of data into an empty schema.

I identified some `UNIQUE KEY` indexes that needed the test_id incorporated in them in the reference data tables and a `VARCHAR()` field that needed to be reduced in size to facilitate a joint `UNIQUE KEY`. These changes are reflected in schema_perftest.sql. I also ran the following SQL statements to update the existing development databases with the schema changes:

`ALTER TABLE test ADD UNIQUE (name, version)`
`ALTER TABLE pages CHANGE COLUMN url url VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL`
`ALTER TABLE pages ADD UNIQUE (test_id, url)`
`ALTER TABLE aux_data DROP INDEX name_UNIQUE, ADD UNIQUE INDEX test_id_name_UNIQUE (test_id, name)`

These statements were run on all databases on s4n3 and s4n4 that have a contenttype of perftest.

I think another improvement to load_test_data() that should be implemented is `START TRANSACTION` at the beginning of the function and a single `COMMIT` at the end. This should be implemented in a try/except, if any errors are raised we could do an explicit `ROLLBACK`. Before implementing this I would like to confirm this is the correct approach for TokuDB. This will prevent partial data being loaded when a data structure is provided that is incomplete but well formed json. This should be researched carefully before implementing.