DatabaseTestCase and Master-2-Master Replication

[VladSun]

It's me again

Today I've encountered another issue wit DB test cases. I just prepared my DB servers for master-to-master replication (cross master-slave config) and all of my DB tests failed. The reason is obvious - I changed the autoincrement_increment setting to 2 (it was 1), because I have 2 DB nodes and I need each of them to have a separate autoincrement values set.

I looked at the PHPUnit database test case class and the manual, but couldn't find anything helpful about it.
I am already thinking about some hackish workarounds but I still don't like them.

Anyone solved this?

[requinix]

Your test cases failed because... they tried to predict the next auto_increment value?

[VladSun]

Well, I use XML DataSets for both initializing the DB for the current test and for comparing the actual (DB DataSet) result against the expected (XML Dataset).
In some cases the autoincement PK id is the only way to identify a record - e.g. N-to-M relationsships.

[requinix]

Right, but the only way the +2 thing should break your code is if it tries to guess what the auto_increment value was. No code should ever do that. If you need to set up relationships then insert the record in the new place and grab the ID with mysql_insert_id() (or whatever).

The alternative to doing it right would be modifying all the code so that it guesses $num+2 instead of $num+1. Which will break if you ever rearrange databases again.

[VladSun]

Well, it was the obvious workaround. I am trying not to use this approach because it will introduce low level code and hi level code mixture.
Also, as I said - I am using "DB snapshots" in XML format - no simple way to dynamically create it and I don't want to do that.
A typical test case would be:

Code: Select all

class UserOrmTest extends DatabaseTestCase {

  /**
   * @var UserOrm
   */
  protected $object;

  protected function setUp() {
    parent::setUp();
    $this->object = new UserOrm();
  }
  
  /**
   * @return PHPUnit_Extensions_Database_DataSet_IDataSet
   */
  public function getDataSet() {
    return $this->createXMLDataSet(dirname(__FILE__) . '/init.user.data.xml');
  }

  public function testSave() {
    ...
    $this->object->save();
    
    $expected = $this->createXMLDataSet(dirname(__FILE__) . '/after.save.data.xml');
    $actual = $this->getConnection()->createDataSet(array('user', 'role'));
    $this->assertDataSetsEqual($expected, $actual);
  }
}

the XML files look like these:

Code: Select all

<?xml version="1.0" ?>
<dataset>
  <table name="user">
    <column>id</column>
    <column>username</column>
    <column>password</column>
    <column>role_id</column>
    <row>
      <value>1</value>
      <value>user1</value>
      <value>password1</value>
      <value>1</value>
    </row>
    <row>
      <value>2</value>
      <value>user2</value>
      <value>password2</value>
      <value>2</value>
    </row>
  </table>
  <table name="role">
    <column>id</column>
    <column>name</column>
    <row>
      <value>1</value>
      <value>admin</value>
    </row>
    <row>
      <value>2</value>
      <value>operator</value>
    </row>
  </table>
</dataset>

It's clean and clear what the test is doing and I want to keep it this way.

[requinix]

There is no middle ground here. Not that I can see. Either you fix the test so that it doesn't need to know the ID, or you fix the XML so it has the right ones. Saying you don't want to change it is great and all but you keep talking about how it isn't working anymore.

[VladSun]

So ... the only solution is to have "n+1 autoincrement" based tests (testing & staging environment) and don't care about "production"environment tests. Sounds a kind of reasonable, though there might be some naive code trying to guess last_insert_id based on n + 1 logic ...

[requinix]

Where did I say "only run the tests in testing/staging and not in production"? I can't find it. Perhaps you could reread everything I've said and point it out to me.

[VladSun]

It is my conclusion ...

[Benjamin]

This is a community of friends, so let's stay nice please.

Vladsun, I never run tests such as this. Can you explain to me, in dumbed down terms, why the tests need to be ran in such fashion and also why a test would need to confirm the auto_increment id?

[VladSun]

Needed for ORM/DBAL/SQL class tests - no DB mocks allowed.
The AI values need to be predicted for M-to-N, 1:1 and N:1 relationships.

[Eric!]

Color me stupid but that didn't help me much. It doesn't help I've never done this before. Why do you care about the auto increment numbers?

If you have to have different numbers then you'll need some ORM to remap them right? So doesn't that mean your XML needs to be dynamic? Is this what they call an impedance mismatch?

[Weirdan]

The AI values need to be predicted for M-to-N, 1:1 and N:1 relationships.

In fact they don't need to be predicted as surrogate keys are just technicalities. If you take a step back and think about it in a more general way, for any relation you really only care about proper entities being linked, not about ids that facilitate that linking. For DB tests it means that you're better off asserting results of joins where you omit AI ids altogether.

Consider this case:

Code: Select all

insert into companies (name) values
("ACME"),
("Google");
insert into employees (name, ssn, company_id) values
("John Doe", "123456", 1), -- company_id is taken from companies sequence automatically
("Jane Doe", "654321", 2); -- ditto

Here, instead of asserting contents of both tables you could make your assertion work on the result of

Code: Select all

select employees.ssn, companies.name
from employees inner join companies on employees.company_id=companies.id

[VladSun]

In general you are right
But not in my case - I have some DB related code which performs only WRITE operations. The READ operations are performed by 3rd party software (daemon) which doesn't use even a single code of my codebase. I.e. the DB is the mediator. That's why I need to ensure the DB snapshot is just what I expect it to be.

Also, I should have mentioned that it is a legacy code that needs a lot of refactoring and it's a little bit scary even to change simple things there.

[Weirdan]

What kind of queries the daemon makes? If the database is just a mediator, all you need to worry about is that the daemon gets what it needs - and thus you'd probably want to run assertions on daemon query results.