PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun May 28, 2017 5:16 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 15 posts ] 
Author Message
PostPosted: Wed Nov 28, 2012 4:45 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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?

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Wed Nov 28, 2012 6:11 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6423
Location: WA, USA
Your test cases failed because... they tried to predict the next auto_increment value?


Top
 Profile  
 
PostPosted: Thu Nov 29, 2012 3:39 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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.

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Thu Nov 29, 2012 4:48 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6423
Location: WA, USA
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.


Top
 Profile  
 
PostPosted: Thu Nov 29, 2012 6:24 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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:

Syntax: [ Download ] [ Hide ]
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:

Syntax: [ Download ] [ Hide ]
<?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.

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Thu Nov 29, 2012 1:15 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6423
Location: WA, USA
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.


Top
 Profile  
 
PostPosted: Fri Nov 30, 2012 4:22 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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 ...

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Fri Nov 30, 2012 4:59 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6423
Location: WA, USA
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.


Top
 Profile  
 
PostPosted: Sat Dec 01, 2012 4:11 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
It is my conclusion ...

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Sat Dec 01, 2012 4:37 am 
Offline
Site Administrator
User avatar

Joined: Sun May 19, 2002 10:24 pm
Posts: 6883
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?

_________________
Image


Top
 Profile  
 
PostPosted: Tue Dec 04, 2012 3:38 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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.

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Tue Dec 04, 2012 8:45 am 
Offline
DevNet Resident

Joined: Sun Jun 14, 2009 3:13 pm
Posts: 1146
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?


Top
 Profile  
 
PostPosted: Tue Dec 04, 2012 9:20 am 
Offline
Moderator
User avatar

Joined: Mon Nov 03, 2003 7:13 pm
Posts: 5975
Location: Odessa, Ukraine
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:
Syntax: [ Download ] [ Hide ]
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
Syntax: [ Download ] [ Hide ]
SELECT employees.ssn, companies.name
FROM employees INNER JOIN companies ON employees.company_id=companies.id
 


Top
 Profile  
 
PostPosted: Tue Dec 04, 2012 10:47 am 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
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.

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
PostPosted: Tue Dec 04, 2012 11:36 am 
Offline
Moderator
User avatar

Joined: Mon Nov 03, 2003 7:13 pm
Posts: 5975
Location: Odessa, Ukraine
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group