Page 1 of 1

DatabaseTestCase and Master-2-Master Replication

Posted: Wed Nov 28, 2012 3:45 pm
by 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?

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Wed Nov 28, 2012 5:11 pm
by requinix
Your test cases failed because... they tried to predict the next auto_increment value?

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Thu Nov 29, 2012 2:39 am
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Thu Nov 29, 2012 3:48 am
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Thu Nov 29, 2012 5:24 am
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Thu Nov 29, 2012 12:15 pm
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Fri Nov 30, 2012 3:22 pm
by 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 ...

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Fri Nov 30, 2012 3:59 pm
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Sat Dec 01, 2012 3:11 am
by VladSun
It is my conclusion ...

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Sat Dec 01, 2012 3:37 am
by 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?

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Tue Dec 04, 2012 2:38 am
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Tue Dec 04, 2012 7:45 am
by 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?

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Tue Dec 04, 2012 8:20 am
by Weirdan
VladSun wrote: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

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Tue Dec 04, 2012 9:47 am
by 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.

Re: DatabaseTestCase and Master-2-Master Replication

Posted: Tue Dec 04, 2012 10:36 am
by 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.