[SQL Constraints]

[lzaiats]

Hi Kris,

I tried to use recess with my "constrained" mysql database and i am
experiencing some problems...

Models:

/**
* !HasMany classesB, Class: classB, Key: classA_id, Through:
classA_x_classB, OnDelete: cascade
*/
classA

/**
* !BelongsTo classA, Class: classA, Key: classB_id, Through:
classA_x_classB
*/
classB

/**
* !BelongsTo classA, Class: classA, Key: classA_id
* !BelongsTo classB, Class: classB, Key: classB_id, OnDelete: cascade
*/
classA_x_classB

The case:

my classA instance ($myClassA_instance) has one classB
($myClassB_instance) related to it through classA_x_classB model. If i
try to remove (and cascade delete to child classB) using:
$myClassA_instance->removeFromClassesB($myClassB_instance), mysql is
throwing a constraint exception because recess is trying to remove the
classB instance before classA_x_classB instance!

I've looked the logic inside Relationship.class.php /
HasManyRelationship.class.php / BelongsToRelationship.class.php to
understand the order the framework is trying to delete the data and i
discovered that it's not respecting the possibility of my tables
having constraints.

I don't know if i am totally right, but i'm stucked!

Can you help me, pleeeeeeeeeeease ;)

LZ!

[Kris Jordan]

Luiz, 

Can you copy the MySQL error string in?

When using removeFromClassesB($myClassB_instance), Recess should only delete the row from classA_x_classB.

Is the problem when you try to delete an instance of class b or when you try and do $a->removeFromB? Or both?

Kris

[lzaiats]

Kris,

Here's the MySQL error: "Cannot delete or update a parent row: a
foreign key constraint fails (`elab/elab_agente_x_endereco`,
CONSTRAINT `elab_agente_x_endereco_ibfk_2` FOREIGN KEY (`id_endereco`)
REFERENCES `elab_endereco` (`id_endereco`))".

Look, it's only cause when i load and try to delete the instance of
classA_x_classB by hand:
$classA_x_classB->classA_id = 1;
$classA_x_classB->classB_id = 1;
if($classA_x_classB->exists()) { $classA_x_classB->delete(); }

Going deep on framework source code i've discovered that:

function removeFrom(Model $model, Model $relatedModel) {
if(!isset($this->through)) {
$foreignKey = $this->foreignKey;
$relatedModel->$foreignKey = '';
$relatedModel->save();
return $model;
} else {
$through = new $this->through;

$localPrimaryKey = Model::primaryKeyName($model);
$localForeignKey = $this->foreignKey;
$through->$localForeignKey = $model->$localPrimaryKey;

$relatedPrimaryKey = Model::primaryKeyName($this->through);
$relatedForeignKey = Model::getRelationship($this->through,
Inflector::toSingular($this->name))->foreignKey;
$through->$relatedForeignKey = $relatedModel->$relatedPrimaryKey;

ATTENTION --> $through->find()->delete(false); <-- HERE YOU DELETE
THE classA_x_classB instance but dont respect the OnDelete: cascade
clause defined at classA_x_classB.class.php (!BelongsTo classB, Class:
classB, Key: classB_id, OnDelete: cascade)
}
}

I think that you need check if i've defined the cascade clause and
call delete() with the correct parameter.

LZ.

On 7 abr, 15:55, Kris Jordan <krisjor...@gmail.com> wrote:
> Luiz,
> Can you copy the MySQL error string in?
>
> When using removeFromClassesB($myClassB_instance), Recess should only delete
> the row from classA_x_classB.
>
> Is the problem when you try to delete an instance of class b or when you try
> and do $a->removeFromB? Or both?
>
> Kris
>

[Kris Jordan]

LZ,

Ok, I think I may have a fix. Try replacing the current onDeleteCascade method in HasManyRelationship with:

function onDeleteCascade(Model $model) {

$related = $this->selectModel($model)->toArray();

if(isset($this->through)) {

$modelPk = Model::primaryKeyName($model);

$queryBuilder = new SqlBuilder();

$queryBuilder

->from(Model::tableFor($this->through))

->equal($this->foreignKey, $model->$modelPk);

$source = Model::sourceFor($model);

$source->executeStatement($queryBuilder->delete(), $queryBuilder->getPdoArguments());

}

foreach($related as $relatedClass) {

$relatedClass->delete();

}

}

Let me know if that solves this problem and I'll submit the fix to Github.

Kris

[lzaiats]

Kris,

We have 2 problems.

1. Constraint violation
2. When deleting a related model using removeFrom() the OnDelete:
cascade is not considered.

Your proposed solution is for HasMany, but the problem itself is
occuring on BelongsTo (because the constraint is causing the error
when deleting the classA_x_classB instance, that has BelongsTo
classB).

Here's the error that recess throws me:

-->
SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or

update a parent row: a foreign key constraint fails (`elab/
elab_agente_x_endereco`, CONSTRAINT `elab_agente_x_endereco_ibfk_2`
FOREIGN KEY (`id_endereco`) REFERENCES `elab_endereco`
(`id_endereco`))

Location: Line 423 of /var/www/html/projects/sm-elaboratory/sm-
elaboratory/trunk/recess/lib/recess/database/pdo/
MysqlDataSourceProvider.class.php

* 419: * @param PdoDataSource $source
* 420: * @return boolean
* 421: */
* 422: function executeSqlBuilder(SqlBuilder $builder, $action,
PdoDataSource $source) {
* 423: return $this->getStatementForBuilder($builder,
$action, $source)->execute();
* 424: }
* 425:
* 426: function beginTransaction() {
* 427: return $this->pdo->beginTransaction();
* 428: }

Call Stack

*
10
PDOStatement->execute()
o called at Line 423 of /var/www/html/projects/sm-
elaboratory/sm-elaboratory/trunk/recess/lib/recess/database/pdo/
MysqlDataSourceProvider.class.php
Called From
o 419: * @param PdoDataSource $source
o 420: * @return boolean
o 421: */
o 422: function executeSqlBuilder(SqlBuilder $builder,
$action, PdoDataSource $source) {
o 423: return $this->getStatementForBuilder($builder,
$action, $source)->execute();
o 424: }
o 425:
o 426: function beginTransaction() {
o 427: return $this->pdo->beginTransaction();
o 428: }
*
9
MysqlDataSourceProvider->executeSqlBuilder( SqlBuilder,
'delete', ModelDataSource )
o called at Line 119 of /var/www/html/projects/sm-
elaboratory/sm-elaboratory/trunk/recess/lib/recess/database/pdo/
PdoDataSource.class.php
Arguments Passed In
Type Value SqlBuilder
SqlBuilder
Member Value
string 'delete'
ModelDataSource
ModelDataSource
Member Value
Called From
o 115: * @param string $action
o 116: * @return boolean
o 117: */
o 118: function executeSqlBuilder(SqlBuilder $builder,
$action) {
o 119: return $this->provider->executeSqlBuilder
($builder, $action, $this);
o 120: }
o 121:
o 122: function executeStatement($statement, $arguments)
{
o 123: $statement = $this->prepareStatement
($statement, $arguments);
o 124: return $statement->execute();
*
8
PdoDataSource->executeSqlBuilder( SqlBuilder, 'delete' )
o called at Line 307 of /var/www/html/projects/sm-
elaboratory/sm-elaboratory/trunk/recess/lib/recess/database/orm/
Model.class.php
Arguments Passed In
Type Value SqlBuilder
SqlBuilder
Member Value
string 'delete'
Called From
o 303: }
o 304:
o 305: $sqlBuilder = $this->assignmentSqlForThisObject
($thisClassDescriptor, false);
o 306:
o 307: return $thisClassDescriptor->getSource()-
>executeSqlBuilder($sqlBuilder, 'delete');
o 308: }
o 309:
o 310: /**
o 311: * Insert row into the data source based on the
values of this instance.
o 312: *
*
7
Model->delete()
o called at Line 94 of /var/www/html/projects/sm-elaboratory/
sm-elaboratory/trunk/recess/lib/recess/database/orm/relationships/
BelongsToRelationship.class.php
Called From
o 90: return $this->augmentSelect($modelSet);
o 91: }
o 92:
o 93: function onDeleteCascade(Model $model) {
o 94: $this->selectModel($model)->delete();
o 95: }
o 96:
o 97: function onDeleteDelete(Model $model) {
o 98: $relatedModel = $this->selectModel($model);
o 99: if($relatedModel != null) {
*
6
BelongsToRelationship->onDeleteCascade( elab_agente_x_endereco )
o called at Line 42 of /var/www/html/projects/sm-elaboratory/
sm-elaboratory/trunk/recess/lib/recess/database/orm/relationships/
Relationship.class.php
Arguments Passed In
Type Value elab_agente_x_endereco
elab_agente_x_endereco
Member Valueid_agente '21'
id_endereco '1'
Called From
o 38: }
o 39:
o 40: switch($this->onDelete) {
o 41: case Relationship::CASCADE:
o 42: $this->onDeleteCascade($model);
o 43: break;
o 44: case Relationship::DELETE:
o 45: $this->onDeleteDelete($model);
o 46: break;
o 47: case Relationship::NULLIFY:
*
5
Relationship->delete( elab_agente_x_endereco )
o called at Line 301 of /var/www/html/projects/sm-
elaboratory/sm-elaboratory/trunk/recess/lib/recess/database/orm/
Model.class.php
Arguments Passed In
Type Value elab_agente_x_endereco
elab_agente_x_endereco
Member Valueid_agente '21'
id_endereco '1'
Called From
o 297: $thisClassDescriptor = self::getClassDescriptor
($this);
o 298:
o 299: if($cascade) {
o 300: foreach($thisClassDescriptor-
>relationships as $relationship) {
o 301: $relationship->delete($this);
o 302: }
o 303: }
o 304:
o 305: $sqlBuilder = $this->assignmentSqlForThisObject
($thisClassDescriptor, false);
o 306:
*
4
Model->delete()
o called at Line 47 of /var/www/html/projects/sm-elaboratory/
sm-elaboratory/trunk/apps/elaboratory/controllers/
ElabAgenteController.class.php
Called From
o 43: $ae = new elab_agente_x_endereco();
o 44: $ae->id_agente = 21;
o 45: $ae->id_endereco = 1;
o 46: if($ae->exists()) {
o 47: $ae->delete();
o 48: }
o 49: }
o 50:
o 51: /** !Route GET */
o 52: function index() {
*
2
ReflectionMethod->invokeArgs()
o called at Line 192 of /var/www/html/projects/sm-
elaboratory/sm-elaboratory/trunk/recess/lib/recess/framework/
controllers/Controller.class.php
Arguments Passed In
Type Value ElabAgenteController
ElabAgenteController
Member Value
array
Array[0]
Called From
o 188: } catch(RecessException $e) {
o 189: throw new RecessException('Error
calling method "' . $methodName . '" in "' . get_class($this) . '".
' . $e->getMessage(), array());
o 190: }
o 191:
o 192: $response = $method->invokeArgs($this,
$callArguments);
o 193: } else {
o 194: throw new RecessException('Error calling
method "' . $methodName . '" in "' . get_class($this) . '". Method
does not exist.', array());
o 195: }
o 196:
o 197: if(!$response instanceof Response) {
*
1
Controller->serve( ElabAgenteController, Array )
o called at Line 41 of /var/www/html/projects/sm-elaboratory/
sm-elaboratory/trunk/recess/lib/recess/Recess.class.php
Arguments Passed In
Type Value Request
Request
Member Valueformat 'json'
headers
Array[9]
Key ValueHOST 'localhost'
USER_AGENT 'Mozilla/5.0 (X11; U; Linux x86_64; pt-BR; rv:
1.9.0.4) Gecko/2008111217 Fedora/3.0.4-1.fc10 Firefox/3.0.4'
ACCEPT 'text/html,application/xhtml+xml,application/xml;q=0.9,*/
*;q=0.8'
ACCEPT_LANGUAGE 'pt-br,pt;q=0.8,en-us;q=0.5,en;q=0.3'
ACCEPT_ENCODING 'gzip,deflate'
ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7'
KEEP_ALIVE '300'
CONNECTION 'keep-alive'
COOKIE 'PHPSESSID=a0jhdfnou9bteu504292c26g84'
resource '/elab_agente/test'
resourceParts
Array[2]
Key Value0 'elab_agente'
1 'test'
method 'GET'
get
Array[0]
post
Array[0]
put
Array[0]
cookies
Array[1]
Key ValuePHPSESSID 'a0jhdfnou9bteu504292c26g84'
meta
Meta
Member Valueapp
ElaboratoryApplication
Member Valuename 'e-Laboratory'
controllersPrefix 'elaboratory.controllers.'
modelsPrefix 'elaboratory.models.'
viewsDir '/var/www/html/projects/sm-elaboratory/sm-elaboratory/
trunk/apps/elaboratory/views/'
routingPrefix '/'
controllerMethod 'test'
controllerMethodArguments
Array[0]
useAssociativeArguments 1
username
password
Called From
o 37:
o 38: // $controller = $pluggedPolicy-
>getControllerFor($request, $routing);
o 39: $controller = $pluggedPolicy->getControllerFor
($request, $apps, $routes);
o 40:
o 41: $response = $controller->serve($request);
o 42:
o 43: $view = $pluggedPolicy->getViewFor($response);
o 44:
o 45: ob_start();
o 46:
*
0
Recess::main( Request )
o called at Line 29 of /var/www/html/projects/sm-elaboratory/
sm-elaboratory/trunk/bootstrap.php
<--

LZ!

On 7 abr, 16:57, Kris Jordan <krisjor...@gmail.com> wrote:
> LZ,
> Ok, I think I may have a fix. Try replacing the current onDeleteCascade
> method in HasManyRelationship with:
>
> function onDeleteCascade(Model $model) {
> $related = $this->selectModel($model)->toArray();
> if(isset($this->through)) {
> $modelPk = Model::primaryKeyName($model);
> $queryBuilder = new SqlBuilder();
> $queryBuilder
> ->from(Model::tableFor($this->through))
> ->equal($this->foreignKey, $model->$modelPk);
>  $source = Model::sourceFor($model);
>  $source->executeStatement($queryBuilder->delete(),
> $queryBuilder->getPdoArguments());}
>
> foreach($related as $relatedClass) {
> $relatedClass->delete();
>
> }
> }
>
> Let me know if that solves this problem and I'll submit the fix to Github.
>
> Kris
>