Doctrine Cache store data into a persistence storage.

Regarding APC, Doctrine use a share storage.
Doctrine is able to cache SQL from DQL, and DOM from SQL.
To do this, Doctrine create a DQL hash, and store the SQL result refer to the hash.

I'm using a server to host two Doctrine project, a preproduction & production Website. In some case, DQL is the same on both project, but the data model definition isn't.

Preproduction convert DQL to SQL using data model definition, and store the SQL result into APC cache refer to the DQL hash.
Production create a DQL hash, this is the same hash as preproduction.. So production instance use the SQL refer to the preproduction..

I'm not sure about the quality of this explanation... But I can add some information is needed.

The solution of this problem is easy. Just add a SALT to any cache id's. It's a Doctrine_Cache problem, not only a Doctrine_Cache_APC problem..
For Query cache it's not really important because this cache is optional, but for result cache.. It's more critical.
The SALT can be define when instantiate the Doctrine_Cache object, it's just an option..

$cacheDriver = new doctrine_Cache_Apc ();
$cacheDriver->setOption ("salt", "domain.tld");

This is the doc exemple on column aggregation inheritance :

Entity:
columns:
username: string(20)
password: string(16)
created_at: timestamp
updated_at: timestamp

User:
inheritance:
extends: Entity
type: column_aggregation
keyField: type
keyValue: 1

Group:
inheritance:
extends: Entity
type: column_aggregation
keyField: type
keyValue: 2

But the keyField (type) is created as VARCHAR(255) so PostgreSQL return an error on applying inheritance condition :

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying = integer
LINE 1: ...D s.application = 'frontend' AND s.lft = 1) AND (s.type = 2)
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I found this with symfony sfFilebasePlugin on sfFilebase:create-root task.

Here's my YAML-file for the model: http://pastie.org/1290649

I'm using the following command to build the whole model: symfony doctrine:build --all --and-load
By this command symfony will create the model using Doctrine.

Please have a closer look at the class BaseTicketHasHardware: http://pastie.org/1290737
If I compare it to the class BaseTicketHasNote I expect a class like this: http://pastie.org/1290765
But Doctrine has created this class: http://pastie.org/1290766

Xcache Cache Driver is not documented at all. Is it working? Is it stable? Can we use it?

The best way I can explain the issue is with code. Please see the below:

<?php

$root = new Test();
$root->name = '1';
$root->save();

// Create root node
$tree = Doctrine::getTable('Test')->getTree();
$tree->createRoot($root);

// Create child node
$child1 = new Test();
$child1->name = '2';
$child1->save();

// Add child
$child1->getNode()->moveAsLastChildOf($root);

// Create child node
$child2 = new Test();
$child2->name = '3';
$child2->save();

// Add child2 as node of child1
$child2->getNode()->moveAsLastChildOf($child1);

// Create child node
$child3 = new Test();
$child3->name = '4';
$child3->save();

// Add child3 as node of child2
$child3->getNode()->moveAsLastChildOf($child2);

// Add another root just to be nice
$root2 = new Test();
$root2->name = '5';
$root2->save();

// Create root node
$tree->createRoot($root2);

/**
 * Now we have the following tree (Each '-' indicates 1 level):
 * 1
 * - 2
 * - - 3
 * - - - 4
 * 5
 */

/**
 * Lets say I want to move node '3' to be a root.
 * With this I assume that all of the current nodes
 * children will be moved with it:
 */
$tree->createRoot(child2);

/**
 * Now the (implied) tree should look like this:
 * 1
 * - 2
 * 3
 * - 4
 * 5
 * 
 * Instead, the tree actually looks like this:
 * 1
 * - 2
 * - - - 4
 * 3
 * 5
 */

/**
 * I will now demostrate incorrect moving back of child nodes.
 */
$child2->getNode()->moveAsLastChildOf($child1);
/**
 * Now the tree should go back to looking like this:
 * 1
 * - 2
 * - - 3
 * - - - 4
 * 5
 * 
 * But the tree now looks like this:
 * 1
 * - 2
 * - - - 4
 * - - 3
 * 5
 */

If I define a base model:

Entity:
connection: other
columns:
name: {}
size: {}

and inherited model:

Box:
inheritance:
extends: Entity
type: column_aggregation

Then file with base definition of Box does not contain bindComponent sentence to bind Box model with connection specified for Entity model.

1. We have two versions of the application on the same server.
2. Second application has an updated database. New field is added to one of the models.
3. When the second app is hit first, query is stored in APC.
4. First app finds cached query and tries to call it. Exception is thrown as it doesn't know anything about the new field yet.

Situation often happens on shared development machine when one developer adds a field but others don't have in their models yet. It also happens on staging server if it's shared with production.

I suspect it only affects queries without explicitly listed fields.

To quickly fix the issue in my symfony project I extended Doctrine_Cache_Apc to implement namespaces (https://gist.github.com/944524). More appropriate place to fix it would be Doctrine_Query_Abstract::calculateQueryCacheHash().

Tree hierarchy hydrators (Doctrine_Collection::toHierarchy and Doctrine_Array_Hierarchy_Driver::hydrateResultSet) are checking wheter the column `level` exists.

The level column can be aliased, and for oracle, it is required to do so. Therefor it is better to check, whether the aliased field level exists.

Patch included in pull request

Example RawSQL:

$q = new Doctrine_RawSql();
    $q->select('{k.*}')
          ->from('SHP_MANDANT_KATEGORIE k')
          ->addComponent('k', 'ShpMandantKategorie k')
          ->where( 'k.id_mandant=' . $this->getIdMandant() )
          ->andWhere( 'k.id_parent=' . $this->getIdMandantkategorie() )
          ->andWhere( 'k.aktiv=1' )
          ->orderBy( 'k.sortorder' ); 

$q->count() generates:

SELECT COUNT(*) as num_results 
FROM (SELECT DISTINCT k.id_mandantkategorie 
              FROM SHP_MANDANT_KATEGORIE k 
              WHERE k.id_mandant=2 AND k.id_parent=1520 AND k.aktiv=1) as results

The illegal Part ist the "as results" at the end...

Ok, so the object relations go like this:

• Comparison
  • [1:N] Product (FK:comparison_id)
    • [1:N] Rules (FK:product_id, FK:option_id)
    • [1:N] Parameters (FK:product_id)
      • [1:N] Options (FK:parameter_id)

The testing code looks like following:

== CODE START ==

$comp = new Application_Model_Comparison();

/* Filling $comp with data */

for ($i = 0; $i < 10; $i++) {

    $product = new Application_Model_Product();

    // Options referenced in Rules
    $options = array();

    for ($j = 0; $j < 10; $j++) {

        $param = new Application_Model_Parameter();

        for ($k = 0; $k < 10; $k++) {

            $option = new Application_Model_Option();

            $param->Options->add($option);

            // Register a single option for this parameter
            if (!isset($options[$j])) {
                $options[$j] = $option;
            }
        }

        $product->Parameters->add($param);
    }

    for ($j = 0; $j < 10; $j++) {
        $rule = new Application_Model_Rule();

        $rule->Option = $options[$j];
        $product->Rules->add($rule);
    }

    $comp->Products->add($product);
}

/**
 * The first save() goes nicely, all objects
 * are created (INSERTed)
 */ 

$comp->save();

// Remove every second product
$pCount = $comp->Products->count();

for ($i = 0; $i < $pCount; $i += 2) {
    $comp->Products->remove($i);
}

/**
 * Fails while trying to save
 *
 * Comparison->Product->Parameter->Option
 * INSERT ... `parameter_id` cannot be NULL
 *
 * Comparison->Product->Rule
 * INSERT ... `product_id` cannot be NULL
 */

$comp->save();

== CODE END ==

The first save() cleans up the relation information in the graph. All child objects are INSERTED instead of UPDATE.

1) I have a Query with APC useResultCache with leftJoin between User and Avatar. (With no avatar for this User).
2) I have au Query without APC useResultCache with leftJoin between Gallery, User and Avatar (Same user than 1))

On the first show of my page => OK
On second show of my page (With APC Cache) error when i call user->getGalleries() :

In Hydrator/RecordDriver.php setLastElement()

$coll is instanceOf Doctrine_Null and count($coll) return 1, so => Crash because ->getLast doesn't exist in Doctrine_Null class

If you add in setLastElement() (Before if (count($coll) > 0) ....

if ($coll instanceOf Doctrine_Null)

It's works.

After enabling:

$manager->setAttribute(Doctrine_Core::ATTR_QUERY_CACHE, $cacheDriver);
(both Memcache and APC)

I get:

Doctrine_Connection_Pgsql_Exception: SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 8 parameters, but prepared statement "pdo_stmt_00000008" requires 16 in /home/taat/www/library/Doctrine/1.2.2/lib/Doctrine/Connection.php on line 1082

Without the cache everything works OK.
RESULT_CACHE works OK too.

Maybe this may help somehow:
http://bugs.php.net/33886

The equal nest relation works incorrectly twice. The SQL it produces is wrong. And even if correct the SQL it still returns wrong data.
In my example below $profile->getFriends() returns incorrect data and even tries to write these wrong data when I do $profile->setEmail("..."); $profile->save()
I'm certain that SQL query in Nest.php is wrong, but not only SQL. Even after fixing SQL the data is still incorrect. I digged through the code and suspect that the problem is in Doctrine_Collection which seems to works with only one referenceField, while in an "equal relationship" this field varies row to row.

Here goes steps to reproduce.

Schema.yml
==========

Profile:
columns:
id:
type: integer
primary: true
autoincrement: true
email:
type: varchar(255)
notnull: true
relations:
Friends:

Fixtures:
======

Profile:
-
id: 1
email: one@example.org
-
id: 2
email: two@example.org
-
id: 3
email: three@example.org
-
id: 4
email: four@example.org

UserFriend:
-
requestor_user_id: 1
receiver_user_id: 2
-
requestor_user_id: 4
receiver_user_id: 1
-
requestor_user_id: 2
receiver_user_id: 3

PHP test
=======
$user = Doctrine_Core::getTable('Profile')->find(1);
var_dump($user->getFriends()->toArray());

Output
======
array(2) {
[0]=>
array(3) {
["id"]=>
string(1) "2"
["email"]=>
string(15) "two@example.org"
["UserFriend"]=>
array(2) {
[0]=>
array(3)

[1]=>
array(3)

}
}
[1]=>
array(3) {
["id"]=>
string(1) "4"
["email"]=>
string(16) "four@example.org"
["UserFriend"]=>
array(1) {
[0]=>
array(3)

}
}
}

As it can be seen from output, the relation "UserFriend" shows insane data:

• sometime there are two records in "UserFriend" - and that's wrong
• user 4 has friendship with 4 - that's also wrong, not in the fixtures.

Sample schema attached.

Now, keeping attached schema in mind.

We have core tables that lots of other tables refer via foreign keys to them, like user, company, etc.

We need to explicitly define foreign keys (like user_id) "definitely same as" User.id on each table. By design, this seems unnecessary, and has load on refactoring processes, ie. type change on User.id.

Doctrine is already parsing foreign relations from schema, It can use parent table field definition (User.id) in refering table fields (user_id) and on the schema user_id can be defined as:

user_id: ~

It would be handy to return a relation array as the result of Doctrine_Table::bind(), because the relation parser's bind() already returns this value.

If I detach a node from the tree using $node->detach(); it set the left and right value to zero without shifting, creating a possible tree corruption.
It still work somehow because inserting a new node will shift the value but keep the hole in the tree.

I've looked to the original commit in Trac (http://trac.doctrine-project.org/changeset/4089) and I don't think the original idea of the commit was to leave a hole.
I've added 3 lines of code (that I copied from the delete function which works properly), the coresponding patch is joigned to the bug ticket

http://www.doctrine-project.org/documentation/manual/1_1/en/caching

When not using a prefix with multiple / yet similay projects a mixup of query caching will occur (for example with sfDoctrineGuard queries). This is very easialy fixed with a prefix - but I only realized after two months - that this feature actually existed. There is no mention of this in the documention.

A mention of the feature

$q = Doctrine_Query::create()
->useResultCache(new Doctrine_Cache_Apc(array('prefix'=>'myproject_')));

Would have helped me and I think it will also help others.

While firing the following query with Doctrine, I did not expect Doctrine to return me a record, however, even though there is a syntax error (in my opinion) it gave me the first record of the table.

$q = \Doctrine_Query::create()
->from('User u')
->where('u.Id =', $userId);

$r = $q->fetchOne();

Note that the question mark in the where method is ommitted. This will return a User model with an Id of 1.

I would expect this to generate an invalid SQL statement (or even fail to pass the DQL validation).

Giving the first record doesn't seem to be appropriate in my opinion.

When a many-tomany relation has been defined on only one of the end of the relation, and ofcourse in the cross-refClass, things get weird. I'll use the user-groups relation (as found in the documentation) as example.

If in the group model has not defined the hasMany, then a user can be instantiated and saved. When instantiated, the relation groups would return an empty array, groups can ben added like:

$u = new User();
var_dump($u->groups);
$u->groups[] = new Group();
var_dump($u->groups);

This would output an empty array, and subsequently an array containing the new group.

However, if a user would be retrieved from the database, and the relation groups would be called, then the following message will appear (which is not helpfull at alllll, I've spend hours figuring it out!):

Uncaught exception 'Doctrine_Record_UnknownPropertyException' with message 'Unknown record property / related component "groups"' .....

I will never forget this, but it's not described in the documentation and the error is not helping either. So either one of these step hvae to be taken. Or even better, making it work without requiring the hasMany in group, to users.

Hi,

I pointed out a strange problem:
I have two tables that are linked with a one to many relation ship.
Each table has some concrete inherited tables.
Here is the schema:

Batch:
  columns:
    code: string
    is_pure: boolean

Flask:
  columns:
    batch_id: integer
    code: string
    quantity: integer
  relations:
    Batch:
      local: batch_id
      foreign: id
      foreignType: many
    
ProducedBatch:
  inheritance:
    type: concrete
    extends: Batch
  columns:
    production_date: date
    
BoughtBatch:
  inheritance:
    type: concrete
    extends: Batch
  columns:
    supplier: string
    
ProducedFlask:
  inheritance:
    type: concrete
    extends: Flask
  columns:
    weight: integer
    
BoughtFlask:
  inheritance:
    type: concrete
    extends: Flask
  columns:
    price: integer

The problem occurs when I try to populate the database.
Here is the data:

ProducedBatch:
  B1:
    code: Batch1

ProducedFlask:
  F1:
    Batch: B1
    code: Flask1

When loading fixtures, I get a

Invalid row key specified: (batch) B1, referred to in (produced_flask) F1

In fact, Doctrine doesn't take into account the primary key inherited from Flask table.
The mapping should be done between parent and child tables.

The only solution to solve this is to set a relationship between each couple of sub-table (ProducedFlask -> ProducedBatch and BoughtFlask -> BoughtFlask).

It would be nice to have a task which you could tell to set to the proper version. If the migrations_version table did not exist yet, it would create the table and set the version.

Explanation:
Most times migrations come as an after thought after the code db is already pushed live. Therefore you have to manually create the migrations table and set it to a version just before the newest migration.

It might also be better to just update the migrate task to check if a migration_version table exists, if not to just run the single migration which is specified.

Please consider the following TestCase

class Doctrine_Ticket_DC464_TestCase extends Doctrine_UnitTestCase
{
    public function prepareTables()
    {
        $this->tables[] = 'Ticket_DC464_Entity';
        $this->tables[] = 'Ticket_DC464_Article';
        $this->tables[] = 'Ticket_DC464_User';
        $this->tables[] = 'Ticket_DC464_Group';
        parent::prepareTables();
    }

    public function testTest()
    {
        $User = new Ticket_DC464_User();

        $Article = new Ticket_DC464_Article();
        $Article->title = 'my first article';
        $Article->Author = $User;

        $Article->save();

        $UserFetched = Doctrine::getTable('Ticket_DC464_User')->find(1);
        $this->assertTrue($UserFetched instanceof Ticket_DC464_User);
       //$this->assertEqual($UserFetched->type, 1);


    }
}



class Ticket_DC464_Entity extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id', 'integer', 11, array('autoincrement' => true, 'primary' => true));
        $this->hasColumn('facultative_field', 'string', 255);
        $this->hasColumn('type', 'string', 255);

        $this->setSubClasses(array(
             'Ticket_DC464_User' =>  array('type' => 1),
             'Ticket_DC464_Group' => array('type' => 2),
        ));

        $this->option('type', 'InnoDB');
    }

    public function setUp()
    {
    }
}

class Ticket_DC464_User extends Ticket_DC464_Entity { }
class Ticket_DC464_Group extends Ticket_DC464_Entity { }

class Ticket_DC464_Article extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id', 'integer', 11, array('autoincrement' => true, 'primary' => true));
        $this->hasColumn('title', 'string', 30);
        $this->hasColumn('author_id', 'integer', 11);

        $this->option('type', 'InnoDB');
    }

    public function setUp()
    {
        $this->hasOne('Ticket_DC464_User as Author', array(
             'local' => 'author_id',
             'foreign' => 'id'));
    }
}

Notice that in the test, the $User instance has no property setted (in fact in this test, the "useless_field" in the model is... useless).

This test fails (the $User object is not saved) because the method UnitOfWork->saveRelatedLocalKeys() - called in saveGraph(), itself called by $Article->save() - returns false because no column has been modified (line 395).

To fix this, the Record->assignInheritanceValues() should be executed in top of isModified(), or for each related object in UnitOfWork->saveRelatedLocalKeys() (and probably UnitOfWork->saveRelatedForeignKeys() too).

I've attached the test.

When executing below query as follows:
$q = Doctrine_Query::create()
->select('bc.id, bbl.printed_name, bbl.rate, bc.name, bc.is_rate_variable')
->from('BillBatchLine bbl, bbl.Bill_Charge bc')
->where('bbl.bill_batch_id = ?', $bill_batch_id);
return $q->execute(array(), Doctrine::HYDRATE_NONE);

SQL generated is:
SELECT b.printed_name AS b_printed_name, b.rate AS brate, b2.id AS b2id, b2.name AS b2name, b2.is_rate_variable AS b2_is_rate_variable FROM Bill_Batch_Lines b LEFT JOIN bill_charges b2 ON b.bill_charge_id = b2.id AND (b2.deleted_at IS NULL) WHERE (b.bill_batch_id = '2' AND b.society_id = '1' AND b2.society_id = '1' AND (b.deleted_at IS NULL))

Basically, columns are ordered based on relationships and not as defined in the SELECT clause

I have found in official docs of MySQL v5.0:

• http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Decimals can be unsigned: DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

Here above is my "schema.yml" file:

 
Project:
  columns:
    id:
      type: integer(4)
      unsigned: true
      primary: true
      autoincrement: true
    salary_range_from:
      type: decimal(12)
      scale: 2
      unsigned: true

After i run:

./symfony doctrine:build --all

my "schema.sql" is:

 
CREATE TABLE `project` (
  `id` INT AUTO_INCREMENT, 
  `salary_range_from` DECIMAL(12, 2), 
  INDEX `company_id_idx` (`company_id`), PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8 ENGINE = INNODB;

$record instanceof Doctrine_Record;
$collection instanceof Doctrine_Collection;

Using the $record object I can find out if it has been modified : $record->isModified().
I can also have this check all the relations also by passing true : $record->isModified(true);

However, the $collection object only support the first level and not the relations of its member records,
$collection->isModified() returns false if one of the entry has one its relations modified (as it should).

The improvement would be to allow this method to accept true, like it's $record counterpart, which would return true instead.

See attached patch.

In class Doctrine_Relation_Parser, in method completeDefinition(), at line 508 (for svn r.7676) there is the code:

                foreach ((array) $this->_table->getIdentifierColumnNames() as $id) {
                    // ?? should this not be $this->_table->getComponentName() ??
                    $column = strtolower($table->getComponentName())
                            . '_' . $id;

The comment on line 509 seems right, since $table is a "temporary" variable created in a previous foreach loop a few lines before (l. 472) and not used anywhere else... (I didn't make a test case though, that's why I tagged it as "Task" rather than "Bug")

I attached a patch, just in case.

G.X