DB Sandbox

[Matt Edmonds]

This is for Mihaela and the other DB designers:
So Ive been working on the dbInstallSandbox, modeling it off of the
classnotes page and internship2career.
Comparing to these sources, It looks to me as though I have everything
correct, but when I run my dbInstallSandbox.php file, I get this:

EventType could not be updated with specified information.
EventType could not be updated with specified information.
Event could not be updated with specified information.
.....etc.

For every single entry in the Sandbox that should be a set of
information in the table.Of course, this is a user created error
message, so it shouldn't be too hard to debug.
I am assuming that I have the incorrect syntax for the insert data
everywhere, but Ive been looking at this file for a long time, and I
not sure where I went wrong, any ideas?

[Matt Edmonds]

Mihaela,
The solution we discussed worked only for the EventType table and the
Donor table.
Event, donation, Individual and Orginization, (all the tables that
require foreign keys), still do not work. I think there might be
something I need to do regarding the foreign keys as well?

[Sabin, Mihaela]

Before you run dbInstallSandbox.php, you must run the Donate_create_tables.sql script. This is because the database base must be removed and recreated in order to reset primary keys to 1, 2, etc. This is important because if you want to insert an Individual (or Organization) whose foreign key is 1 (or 2), then Donor table must have its first two entries with primary key 1 and 2.

In dbInstallSandbox.php, I see that for Event, Donor, and Donation, you're still including values for the primary keys. See lines 61, 73, 84, 94, 104, 115, 126, 137, 147. You don't have to. The functions __construct( ) for Donor, Event, and Donation objects assign null to the instance variables that correspond to primary keys.

In Donation.php, function __construct( ) is missing and existing constructDonation has $donationID parameter. It should not.

In dbDonation.php, function createTable( ) cannot have DROP TABLE query. This is because the order in which tables are created is different from the order in which they are dropped. The function addDonation( ) cannot have $v->getDonoationID( ) in the sprint( ) call. There should be only three place holders, '%s', %d, and %d. Notice that the string place holder has '' around it.

In dbIndividual.php, function addIndividual( ) must have three place holders: %d, '%s', and '%s' (not two). The first placeholder, %d is necessary because DonorID for the Individual table is PK and FK, and a value for it must be provided by calling $v->getDonorID( ). In dbOrganization.php, the function addOrganization( ) follows the same rule.

Make sure that all %s place holders are enclosed in ''.

In dbInstallSandbox.php, when you specify foreign key values, do not enclose them in ''. This is because they are numbers, not string. For example, we write:
$m = new Donation("3-21-96", 1, 1); //see line 126
Of course, we assume that there is a Donor already with primary key 1 and and Event with primary key 1. I'm not sure if "3-21-96" format works for dates in MySQL. Take a look at the date value in Donate_create_insert.sql. We use "yyyy-month-day", where year has four digits, such as "2011-1-2".

Another example in dbInstallSandbox.php that uses a foreign key value is:
$m = new Individual(3, "Tom", "Jones");//see line 158
However, in order to create an Individual with primary key/foreign key 3, you must have a Donor with primary key 3. And I don't see such Donor. The Donor objects created in dbInstallSandbox are 1, 2, 4, and 5 (see lines 84, 94, 104, and 115).

I recommend that dbInstallSandbox.php first populates with data as shown in Donate_create_insert.sql. If you want to add more data, do it first with the SQL script. I would also recommend that sample data looks as realistic as possible.