[DC-80] Doctrine/Import/Pgsql.php generates wrong default values for varchar fields Created: 06/Oct/09  Updated: 06/Oct/09  Resolved: 06/Oct/09

Status: Closed
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: 1.2.0-ALPHA2

Type: Bug Priority: Critical
Reporter: Maurice Makaay Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu Linux, PostgreSQL 8.3, PHP 5.2.6



 Description   

We run a postgres database and have generated out base models from the database. In the base models, we see the following issue:

        $this->hasColumn('contractor_type', 'string', 150, array(
             'type' => 'string',
             'length' => '150',
             'fixed' => false,
             'unsigned' => false,
             'notnull' => true,
             'default' => '\'enduser\'::character varying',
             'primary' => false,
             ));

So, the default value is showing some PostgreSQL meta information. We fixed this by changing function listTableColumns():

            if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $description['default'], $matches)) {

                $description['sequence'] = $this->conn->formatter->fixSequenceName($matches[1]);
                $description['default'] = null;
            }
+           elseif (preg_match("/^'(.*)'::character varying$/", $description['default'], $matches)) {
+               $description['default'] = $matches[1];
+           }

            $columns[$val['field']] = $description;





[DC-66] Saving "new Doctrine_Expression('NOW()')" to database doesn't work since r6403 Created: 25/Sep/09  Updated: 06/Oct/09  Resolved: 06/Oct/09

Status: Closed
Project: Doctrine 1
Component/s: Record
Affects Version/s: 1.2.0-ALPHA2
Fix Version/s: 1.2.0-ALPHA2

Type: Bug Priority: Blocker
Reporter: Marcin Gil Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 4
Labels: None
Environment:

MySQL 5.0.51, PHP 5.3


Attachments: Text File timestamp-validation.patch    

 Description   

Doctrine_Record:1469

} else if ($type == 'timestamp' || $type == 'date') {
return strtotime($old) !== strtotime($new);

These new lines cause inability to save NOW() to "date" field.

Change was commited in r6403 branch 1.2

MySQL error is:

SQLSTATE[HY000]: General error: 1364 Field 'date' doesn't have a default value



 Comments   
Comment by Lex Brugman [ 27/Sep/09 ]

Possible fix attached.

Comment by Marcin Gil [ 28/Sep/09 ]

The patch is effective, please apply to SVN.

Thanks!

Comment by Johannes Weber [ 28/Sep/09 ]

I don't want to rewrite all my "NOW()" values! Please fix this ASAP!
thx!





[DC-57] isValueModified returns true for timestamps that appear different but are equal Created: 24/Sep/09  Updated: 15/Feb/10  Resolved: 24/Sep/09

Status: Closed
Project: Doctrine 1
Component/s: Validators
Affects Version/s: 1.0.12, 1.1.4, 1.2.0-ALPHA2
Fix Version/s: 1.0.12, 1.1.4, 1.2.0-ALPHA2

Type: Bug Priority: Major
Reporter: Jonathan H. Wage Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

Doctrine_Record::isValueModified compares timestamps as strings and values that are equal such as 2009-09-02 00:00:00 and 2009-09-02 are treated as modified.

The code should do a second compare on the strtotime value if the strings appear to be unequal.



 Comments   
Comment by oweitman [ 15/Feb/10 ]

Problem is with date-values before Fri, 13 Dec 1901 20:45:54 GMT
and after Tue, 19 Jan 2038 03:14:07 GMT.
For these values strtotime reports false and the field is not updated.

This is my first use of this issue-system, i dont find a button to reopen this issue.





[DC-56] Geographical behavior does not calculate distance Created: 24/Sep/09  Updated: 01/Mar/10  Resolved: 24/Sep/09

Status: Closed
Project: Doctrine 1
Component/s: None
Affects Version/s: 1.0.12, 1.1.4, 1.2.0-ALPHA2
Fix Version/s: 1.0.12, 1.1.4, 1.2.0-ALPHA2

Type: Bug Priority: Major
Reporter: Jonathan H. Wage Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

If the fields name of the latitude or longitude invoker aren't 'latitude' or 'longitude', then the getDistanceQuery doesn't not work.

milesSql = sprintf($sql, $invoker->get('latitude'), $invoker->get('latitude'), $invoker->get('longitude'), '1.1515', 'miles');
...
$kilometersSql = sprintf($sql, $invoker->get('latitude'), $invoker->get('latitude'), $invoker->get('longitude'), '1.1515 * 1.609344', 'kilometers');

Replacing latitude by $latName (previously defined line 74 and 75) resolved this problem.

Here's the fix :

milesSql = sprintf($sql, $invoker->get($latName), $invoker->get($latName), $invoker->get($longName), '1.1515', 'miles');
...
$kilometersSql = sprintf($sql, $invoker->get($latName), $invoker->get($latName), $invoker->get($longName), '1.1515 * 1.609344', 'kilometers');


 Comments   
Comment by Jonathan H. Wage [ 01/Mar/10 ]

This is fixed already in the latest 1.2 version.





[DC-40] Problems with alias in where section Created: 18/Sep/09  Updated: 24/Sep/09  Resolved: 24/Sep/09

Status: Closed
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: 1.2.0-ALPHA2

Type: Bug Priority: Blocker
Reporter: Johannes Weber Assignee: Roman S. Borschel
Resolution: Invalid Votes: 0
Labels: None
Environment:

MAMP



 Description   

I'm using a mysql function to calculate the distance between 2 points. When I'm using this funciton in a doctrine query it works. But when I'm using the functions result in a where clause it dont work:

The Query:
$basicQuery = $query->select('id, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, IF(count(r.id) > 0, AVG(r.rating), 0) as ratingAvg')
->from('ProomsOffer o')
->leftJoin('o.PublicTransports pt')
->leftJoin('o.Unavailabilities ua')
->leftJoin('o.Ratings r')
->leftJoin('o.Bookings b')
->where('distance != 0')
->groupBy('o.id')
->orderBy('distance, ratingAvg');

The Error message:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'm__1' in 'where clause'
#0 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql))
#1 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(790): Doctrine_Connection->execute('SELECT COUNT...', Array)
#2 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Query.php(2037): Doctrine_Connection->fetchAll('SELECT COUNT...', Array)
#3 /Applications/MAMP/htdocs/prooms/cms/application/modules/prooms/controllers/SearchController.php(163): Doctrine_Query->count()
#4 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Action.php(513): Prooms_SearchController->searchAction()
#5 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('searchAction')
#6 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(CMS_Controller_Request), Object(Zend_Controller_Response_Http))
#7 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(372): Zend_Controller_Front->dispatch()
#8 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(241): CMS_Application->_dispatch(Object(Zend_Controller_Front))
#9 /Applications/MAMP/htdocs/prooms/cms/public/index.php(50): CMS_Application->run()
#10

{main}

The getSqlQuery:
SELECT `m`.`id` AS `m_id`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m0`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m1`, IF(COUNT(`m4`.`id`) > 0, AVG(`m4`.`rating`), 0) AS `m41` FROM `module_prooms_offers` `m` LEFT JOIN `module_prooms_offers_public_transport` `m2` ON `m`.`id` = `m2`.`id_offer` LEFT JOIN `module_prooms_offers_unavailability` `m3` ON `m`.`id` = `m3`.`id_offer` LEFT JOIN `module_prooms_offers_ratings` `m4` ON `m`.`id` = `m4`.`id_offer` LEFT JOIN `module_prooms_bookings` `m5` ON `m`.`id` = `m5`.`id_offer` WHERE `m1` != 0 GROUP BY `m`.`id` ORDER BY `m1`, `m4_1`



 Comments   
Comment by Guilherme Blanco [ 24/Sep/09 ]

You must disable PORTABILITY_EXPR in order to get it working correcrly.





[DC-29] ResultCache not working with i18n behavior Created: 16/Sep/09  Updated: 26/Sep/09  Resolved: 24/Sep/09

Status: Resolved
Project: Doctrine 1
Component/s: Caching
Affects Version/s: 1.2.0-ALPHA1
Fix Version/s: 1.2.0-ALPHA2

Type: Bug Priority: Major
Reporter: Jules Bernable Assignee: Jonathan H. Wage
Resolution: Invalid Votes: 0
Labels: None
Environment:

ubuntu 8.04 apache2 php5 with symfony


Attachments: File Doctrine_Ticket_DC29.php    

 Description   

Tested with Doctrine_Cache_APC and Doctrine_Cache_Db with SQLite.

  • The model:
Article:
  actAs:
    Timestampable:
    Sluggable:
      fields:   [date, title]
    I18n:
      fields:       [title, content]
      actAs:
        Sluggable:
          fields:   [title]
          uniqueBy: [lang, title]
  columns:
    title:          string(255)
    date:           date
    content:        clob
  • The symfony code:
frontendConfiguration.class.php
public function configureDoctrine(Doctrine_Manager $manager)
{
  if (sfConfig::get('sf_cache') == true)
  {
    $cacheDriver = new Doctrine_Cache_Apc();
    
    $manager->setAttribute(Doctrine::ATTR_QUERY_CACHE, $cacheDriver);
    $manager->setAttribute(
      Doctrine::ATTR_QUERY_CACHE_LIFESPAN,
      sfConfig::get('app_cache_lifetime', 86000)
    );
    $manager->setAttribute(Doctrine::ATTR_RESULT_CACHE, $cacheDriver);
    $manager->setAttribute(
      Doctrine::ATTR_RESULT_CACHE_LIFESPAN,
      sfConfig::get('app_cache_lifetime', 86000)
    );
  }
}
ArticleTable.class.php
public function fetchAll()
{
  return Doctrine_Query::create()
    ->from('Article a')
    ->leftJoin('a.Translation t')
    ->orderby('a.date DESC')
    ->useQueryCache()->useResultCache()
    ->execute();
}
  • The Result:

500 | Internal Server Error | Doctrine_Record_UnknownPropertyException
Unknown record property / related component "title" on "Article"



 Comments   
Comment by Jonathan H. Wage [ 17/Sep/09 ]

Would it be possible to have a test case included?

Comment by Jules Bernable [ 19/Sep/09 ]

Well, i'm new to Doctrine so I wasn't able to run the test case, but I tried to write one. :-/
Hope this helps...

Comment by Jonathan H. Wage [ 24/Sep/09 ]

The problem is that you define Sluggable on the root model for the fields named title and date. But you said that title is to be I18n so no field exists named title since it was moved to the translation table. You could work around it by creating a getTitle() accessor proxy which gets the title from the translation.

Comment by Jules Bernable [ 25/Sep/09 ]

Yes, but the sfDoctrineRecordI18nFilter class is supposed to handle this for me,
and the fact is that it works fine excepted when the Result_Cache is enabled.
Should I file a bug in the symfony trac then ?

symfony/lib/plugins/sfDoctrinePlugin/lib/record/sfDoctrineRecordI18nFilte.class.php
/**
 * sfDoctrineRecordI18nFilter implements access to the translated properties for
 * the current culture from the internationalized model.
 *
 * @package    symfony
 * @subpackage doctrine
 * @author     Jonathan H. Wage <jonwage@gmail.com>
 * @version    SVN: $Id: sfDoctrineRecordI18nFilter.class.php 11878 2008-09-30 20:14:40Z Jonathan.Wage $
 */
class sfDoctrineRecordI18nFilter extends Doctrine_Record_Filter
{
  public function init()
  {
  }

  /**
   * Implementation of filterSet() to call set on Translation relationship to allow
   * access to I18n properties from the main object.
   *
   * @param Doctrine_Record $record
   * @param string $name Name of the property
   * @param string $value Value of the property
   * @return void
   */
  public function filterSet(Doctrine_Record $record, $name, $value)
  {
    return $record['Translation'][sfDoctrineRecord::getDefaultCulture()][$name] = $value;
  }

  /**
   * Implementation of filterGet() to call get on Translation relationship to allow
   * access to I18n properties from the main object.
   *
   * @param Doctrine_Record $record
   * @param string $name Name of the property
   * @param string $value Value of the property
   * @return void
   */
  public function filterGet(Doctrine_Record $record, $name)
  {
    $culture = sfDoctrineRecord::getDefaultCulture();
    if (isset($record['Translation'][$culture]))
    {
      return $record['Translation'][$culture][$name];
    } else {
      $defaultCulture = sfConfig::get('sf_default_culture');
      return $record['Translation'][$defaultCulture][$name];
    }
  }
}
Comment by Jonathan H. Wage [ 25/Sep/09 ]

Ahhh. I see. I think this could be a problem with serializing and unserializing and the attached filters are not being maintained when unserialized. Not sure, i will have to have a look.

Comment by Jules Bernable [ 26/Sep/09 ]

linked ...
http://trac.symfony-project.org/ticket/7220





[DC-28] Duplicate join condition when using nestedSet Created: 16/Sep/09  Updated: 24/Sep/09  Resolved: 24/Sep/09

Status: Closed
Project: Doctrine 1
Component/s: Nested Set, Query
Affects Version/s: 1.1.4, 1.2.0-ALPHA1
Fix Version/s: 1.1.4, 1.2.0-ALPHA2

Type: Bug Priority: Major
Reporter: Viktoras Assignee: Jonathan H. Wage
Resolution: Fixed Votes: 0
Labels: None


 Description   

When trying to fetch a nested set tree, where a base query uses join, an exception is thrown "'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'", which is caused by incorrect generated SQL query.

How to reproduce:

$query = Doctrine_Query::create()
            ->select('s.name')
            ->from('Test s')
            ->leftJoin('s.Test2 st WITH st.title = ?', array(1));

$tree_table = Doctrine::getTable('Test');
$tree = $tree_table->getTree();

viewing SQL at this point, is:

SELECT t.id AS t__id, t.name AS t__name 
FROM test t 
LEFT JOIN test_2 t2 
    ON t.id = t2.test_id AND (t2.title = ?)

set the query as a base for nestedSet tree:

$tree->setBaseQuery($query);

viewing SQL at this point, is:

SELECT t.id AS t__id, t.name AS t__name, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level 
FROM test t 
LEFT JOIN test_2 t2 
    ON t.id = t2.test_id AND (t2.title = ?) AND (t2.title = ?)

As you can see lft/rgt/level fields were added, which is ok, but also additional LEFT JOIN condition was duplicated - (t2.title = ?) .

These kind of queries are working in 1.0* branch and also seemed to work in 1.1* branch until 5680 revision, which broke it. Although Changeset 5700 seems to must have fixed it, but the SQL is still generated doubled in 1.1* and 1.2* branches.



 Comments   
Comment by Jacek Jędrzejewski [ 16/Sep/09 ]

TestCase from ticket 2105 (which was fixed in rev. 5700) works ok. (BTW. It was my ticket )

Create a failing testcase for that, it will help devs.

Comment by Viktoras [ 16/Sep/09 ]

class Doctrine_Ticket_Dc28_TestCase extends Doctrine_UnitTestCase 
{
    public function prepareTables()
    {
        $this->tables[] = 'Ticket_Dc28_Tree';
        parent::prepareTables();
    }

    public function testQuery()
    {
        try {
            $q = Doctrine_Query::create()
                ->select('a.id, t.lang')
                ->from('Ticket_Dc28_Tree a')
                ->innerJoin('a.Translation t WITH t.name != ?', 'test')
                ;
            $q->execute();
            //echo $q->getSql().PHP_EOL;
            
            $this->assertEqual(
                $q->getSql(), 
                'SELECT t.id AS t__id, t2.id AS t2__id, t2.lang AS t2__lang '.
                'FROM ticket__dc28__tree t '.
                'INNER JOIN ticket__dc28__tree_translation t2 '.
                'ON t.id = t2.id AND (t2.name != ?)'
            );
            
            //echo $q->getSql().PHP_EOL;
            $tree_table = Doctrine::getTable('Ticket_Dc28_Tree');
            $tree = $tree_table->getTree();
            $tree->setBaseQuery($q);
            //echo $q->getSql().PHP_EOL;
            
            $this->assertEqual(
                $q->getSql(), 
                'SELECT t.id AS t__id, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level, t2.id AS t2__id, t2.lang AS t2__lang '.
                'FROM ticket__dc28__tree t '.
                'INNER JOIN ticket__dc28__tree_translation t2 '.
                'ON t.id = t2.id AND (t2.name != ?)'
            );
            
            //$this->pass();
        } catch (Exception $e) {
            $this->fail($e->getMessage());
        }
    }
}

class Ticket_Dc28_Tree extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 255);
    }

    public function setUp()
    {
        $i18n = new Doctrine_Template_I18n(array('fields' => array(0 => 'name')));
        $this->actAs($i18n);
        $this->actAs('NestedSet');
    }
}
Comment by Jonathan H. Wage [ 24/Sep/09 ]

This is now fixed in Doctrine 1.1 and 1.1.

1.0 is not affected.





Generated at Fri Aug 01 16:06:29 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.