Ok, this email is quite lengthy, and it might look overwhelmingly complicated, but trust me, it’s not. Just read this entire email from start to finish before you start doing anything, and you’ll see that it’s not complicated (just time-consuming).
In 2.0, instead of executing raw SQL statements, we’re using an ORM framework to handle the query execution. There are several benefits to this approach which I won’t go over here, but let’s just say that the benefits are significant enough that they warrant a total re-write of the way we’re executing our queries.
But more about that later.
The key to our success lies in the order in which we convert the various sections of the game. Here’s a brief outline of that order:
The first thing we need to do is convert the queries, so for this assignment, you don’t have to think about anything other than converting queries. Don’t think about game logic or how we can improve the game while we’re converting the queries. Just think about converting the queries. Treat them as isolated entities. You’ll see what I mean in a minute.
The attached ZIP file contains all the files you’ll need for this assignment. If you haven’t already, go ahead and unzip the file and familiarize yourself with the files.
The “private” folder contains all of the files that you should be familiar with from the current game - with the only notable exception being that the Football Server class has been renamed and extracted to the root.
The “doctrine-models” folder contains all of the new models that we’ll be using in 2.0. Inside this folder is another folder called “generated” which contains all of the models that Doctrine created for us using the 2.0 database. You don’t actually have to worry about the “generated” folder - I just included it so you could have a look - in fact, you should probably delete it before you start the assignment.
There are 4 different types of queries: SELECT, INSERT, UPDATE, and DELETE. I’ve already converted most of the UPDATE queries. Your assignment is to convert the INSERT and DELETE queries. (After I’ve converted the last of the UDPATE queries, I’ll start converting the SELECT queries, and hopefully I’ll have them finished around the same time as you finish the INSERT and DELETE queries.)
You should start with the DELETE queries because they’re the easiest to convert, and they’ll help you get familiar with DQL (Doctrine Query Language), which is what we’ll be using to create the new queries.
I’ve already converted a couple DELETE queries so you can use them as an example:
Open up “FootballServer.php” and scoll all the way down to the bottom of the class and you’ll see one of the DELETE queries I converted. The old query has been commented out (when you’re converting the queries, you should delete the old query. I just commented this one out so you could see what’s been replaced with what.)
Immediately below the old query is a call to a static function, which contains the new query. Open up “doctrine-models/
TblAuctiontemp.php” so you can see the new query.
First of all, the new query is located in a static function within the model of the database table it’s referencing. In this case, since we’re deleting from “tbl_auctiontemp”, the static function is placed inside “TblAuctiontemp.php”. If we were deleting from “tbl_leagues”, then we would’ve created a static function inside “TblLeagues.php”. (Try to name the static functions according to what the individual query is actually doing. And be as verbose as you need to be - we can always re-factor the names later on.)
If you look inside the deleteLeagueAuctionTempValues() function, you’ll see the new query, and as you can see, the new query looks pretty straight-forward. Yeah, the syntax is new, but if you look at it, you can see what it’s doing:
//Setup query.
$objQuery = Doctrine_Query::create();
This creates an instance of the Doctrine query object. (All of your DELETE queries should start like this.)
$objQuery->delete(’Auctiontemp’);
Here, we’re telling Doctrine which table to delete from. Notice that we’re not passing it the exact name of the table. The table name we’re deleting from is “tbl_auctiontemp”, but Doctrine doesn’t use the table names directly. We pass it “Auctiontemp” instead because that’s the name of the “component” that Doctrine has mapped to the table. Don’t worry too much about that - just remember to remove the “tbl_” bit from the table name and capitalize the first letter (and only the first letter) of the component.
$objQuery->where(’intLeagueId = ?’, $intLeagueId);
We use the where() method to tell Doctrine which records to delete. Notice the question mark. Instead of using fnAddSlashes(), we use the question mark, and then Doctrine replaces the question mark with the value of $intLeagueId.
$objQuery->limit(1);
We can also use the limit() method to tell Doctrine to stop executing the query after it’s deleted the record. Be careful when you’re doing your queries… if the old query doesn’t have a LIMIT clause, then make sure you don’t use the limit() method in the new query. And vice versa… if the old query does have a LIMIT clause, then don’t forget to use the limit() method.
//Execute query.
$objQuery->execute();
And finally, we tell Doctrine to execute the query we’ve just built.
So that’s what the new query looks like. Now if you go back to “FootballServer.php”, you can see what we’ve done. Basically, we moved the old query into a static function, and now instead of executing that query in FootballServer.php, we’re just calling the static function, making sure to pass in any variables that the new query might need (in this case, $intLeagueId). Be careful when passing parameters… sometimes it’s a regular variable like $intLeagueId, and other times it’s a member variable, like $this->intLeagueId. Other times, you might want to pass an array to the function instead of multiple paramters. I’ll show you an example of this approach in the INSERT QUERIES section.
I’ve done another example to illustrate how you add multiple where() methods. Open up “private\views\viewPlayPercentages.php” and scroll down to line 766. You should see the old query which has been commented out. Notice that it contains references to two variables. Now look right below the old query and see how we’re passing two variables to the static function.
Now if you open “doctrine-models\TblDefensiveplaypercentages.php”, you can see what we’ve done here. Firstly, we’re passing it two variables instead of one, and then we’re also using two where() methods in the new query. Notice the second where() method. It’s actually called “andWhere()”.
(There’s also another method called “orWhere()” which should be pretty self-explanatory when it’s used.)
If you encounter a query which references a “dynamic” table (you can see an example of a dynamic table in “private\views\viewFormationPercentages.php” around line 866. Notice how the table name is actually a variable? Well, don’t worry about converting those unless you think you can maintain all the logic involved. If you’re unsure, just leave it and I’ll convert it later.
Also, here’s a link to the reference manual for DELETE queries:
http://www.doctrine-project.org/documentation/manual/1_1/en/dql-doctrine-query-language#delete-queries
INSERT QUERIES
INSERT queries are a bit different. Instead of building a “query”, you’re creating an object/record and then “saving” it to the database. I’ve also done an INSERT example to show you how it’s done:
First, open up “FootballServer.php” and scroll down to line 4483 to see the old query. Immediately below the old query is the familiar call to the static function. But notice how we’re passing an array to this one instead multiple variables? You’ll see why we do it that way in a minute.
Now open up “doctrine-models\TblLeaguefixtures.php” and take a look at the first static function. It only takes one array as a parameter to avoid having a shit-load of parameters. Then, inside the static function we use the values of the array to create the query/object/record thingy:
//Setup query.
$objQuery = new TblLeaguefixtures();
Create a new instance of “tbl_leaguefixtures”. Notice how we’re using the full camel-cased name of the Doctrine component.
$objQuery['intLeagueId'] = $arrData['intLeagueId'];
$objQuery['intSeason'] = $arrData['intSeason'];
$objQuery['intWeek'] = $arrData['intWeek'];
$objQuery['intHomeUserId'] = $arrData['intHomeUserId'];
$objQuery['intAwayUserId'] = $arrData['intAwayUserId'];
$objQuery['datSimulationDate'] = $arrData['datSimulationDate'];
$objQuery['booSimulated'] = $arrData['booSimulated'];
$objQuery['booRegularSeason'] = $arrData['booRegularSeason'];
Use the values of the array to tell Doctrine what values to insert into each column.
//Execute query.
$objQuery->save();
And finally, we execute the query (a little differently than how we executed the INSERT query, but more or less the same sort of syntax.)
If you come across any INSERT queries that look too complicated, again, don’t worry about them. I’ll sort those out later. If you want to give the harder ones a go though, feel free - I’ll be quickly skimming over everything you’ve done to make sure everything’s alright, so don’t worry about messing anything up.
To help you with the INSERT queries, take a look at the following link for reference:
http://www.doctrine-project.org/documentation/manual/1_1/en/working-with-models#dealing-with-relations
CONCLUSION
So that’s your assignment. I hope it makes sense. I know it’s quite different to the way you’re used to seeing the queries, but hopefully the examples I’ve shown you and the links to the reference manual will get you on the right track.
The best thing to do is create a project in Komodo (or whatever IDE you’re using) and do a global search for “DELETE FROM” for the DELETE queries and a global search for “INSERT I” for the INSERT queries. Then go through each of them one-by-one.
If you have any questions about anything, just drop me an email or find me online.
I’ll have the rest of the UPDATE queries done this weekend, and hopefully a good chunk of the SELECT queries as well. Once all the queries have been converted, it’ll be so easy to get the server up and running so we can start generating leagues and simulating games. And that’s a HUGE step forward to launching 2.0 - everything after this is cake. 
Thanks man - and good luck!!!
