One scenario that we do encounter from time to time on our large scale test system is where we discover after the event that two models we have listed separately in the database are actually one and the same girl. After making absolutely sure that we are talking about the same person (and hopefully making a note in the Distinguishing Marks field in the model record about how we confirmed that), we need to make it clear in our own minds what we're going to do.

This involves making a decision on which of the two model records we're actually going to keep and then checking the one that we intend to delete for any pertinent information, such as biographical data, which is not contained within the record we intend to keep. We can of course easily use the Model Manager (see Chapter 6, wacsmodelmgr - The Wacs Model Manager) to do this.

In the example we're going to work with we've discovered that Dianne from Sapphic Erotica who is our model number 163 is also Lena from ATK Galleria who is our model number 2474. We've taken the decision to retain her lower model number of 163 and delete the new addition, namely 2474. We've already copied across the extra biographical information that we had on the “Lena” record, and in fact we decided that we preferred the headshot we had of her from ATK Galleria and so modified that too. These are all very simple to do using the Model Manager, so we'll not cover the exact steps taken again here.

The first step we're going to take is to move the identity record for Lena over to Dianne so that future references to Lena on ATK Galleria automatically summon up Dianne's record. We do this with the following SQL command:

SQL> update idmap set imodelno = 163 where imodelno = 2474;

1 row updated.

SQL>

For the next step we're going to change the sets that we have marked as having Lena in to being marked as having Dianne in. This is actually quite easy to do because Wacs doesn't depend on the name that we have in a set record in anyway - it's merely a matter of convenience. Even after Lena's records have become Dianne's, there is no problem with her still being called Lena there. In someways that is desirable because it retains the connection with what she was called on the site where those sets came from. In other ways, it's very confusing as in “Who is this Lena girl who keeps appearing in Dianne's sets?”. We'd definitely recommend adding the name Lena to Dianne's aliases list in the model record because the top of the model page will then say about Dianne “Sometimes also known as Lena”. If you want to change the title of the set, you can easily do this with the Info Manager (see the section called “Managing Additional Information”). As a byproduct of changing the name using the Info Manager, the other fields in the set database that have model info in them will also be updated.

SQL> update assoc set amodelno = 163 where amodelno = 2474;

11 rows updated.

SQL>

While these two updates have changed most of the user visible entries, the are actually three more changes we could have to make. There are in fact five database tables that make reference to model numbers and all of them do need to be updated to match each other. These are:

This is where you have to know your database. The last thing you want to do is to leave a record lying around refering to something that no longer exists - that's extremely bad practice and is almost bound to cause some web application to fall over. Most databases simply won't let you do this - it's known as enforcing referential integrity - but unfortnately MySQL 5 in it's standard form is not one of them. This means you could easily delete a model who still has some download records or tags (individual elements of a search) still attached to them. Allowing this to happen is just storing up horrors for later.

As a general rule, it's best to try and change the model numbers in each and every database table that might contain them and just accept that in some cases there may well be nothing to do. An update that does nothing is really not a problem to any SQL version we've ever encountered. So we proceed with the next three steps on the basis that it's quite possible that they'll do nothing. Just understand that it's better to execute commands that often seem to do nothing than to end up with the chaos caused by connections left hanging. If you know your database well, and know it reliably enforces referential integrity, then you could skip those you know have no entries in them but we don't recommend this.

So we reach the third step, that where we move the download records, and remembering that these are an identification of which set is which in a portable way even when we have no plans to use the download system. In fact, it's good practice for commercial sites to allocate download records to their own sets to ease future server-to-server migrations and the like. Anyway, here is the SQL we need for this:

SQL> update download set dmodelno = 163 where dmodelno = 2474;

14 rows updated.

SQL>

Now on to the fourth and fifth steps, which we'll do together which are most likely not to find anything, updating the tag and connections records:

SQL> update tag set tmodelno = 163 where tmodelno = 2474;

4 rows updated.

SQL> update conn set cmodelno = 163 where cmodelno = 2474;

0 rows updated.

SQL>

So as you can see, in the above example it turned out that we did actually have Lena tagged in no less than four different saved searches, so it's just as well we updated them. The final step is to delete the old model record and complete the model merge process. It's good practice to include a commit at the end of the process - on a good relational database like Oracle, none of the other steps will have actually have been done to the database itself until that final commit is done. The beauty of this is that all of the actions will be taken at exactly the same time and no one browsing the Wacs site at the time will find a half-moved state - the change will literally happen in an instant despite the fact that we've been checking and composing the queries for probably a couple of minutes at least - quite possibly longer if you're reading this document at the same time. The alternative to using commit is to type rollback; which will undo all the changes. Unfortunately in MySQL 5 the commit and rollback functions, while present, don't work quite as they should. We can only hope that they will improve with time and further development.

SQL> delete from models where modelno = 2474;

1 row deleted.

SQL> commit;

Commit complete.

SQL> quit

So there we are. The two model records for SE's Dianne and ATK's Lena are now one and the same and we have a better quality entry for this lovely Ukrainian blonde in our database. All in all a good result.