#5942: MSSQL Inserting ARO fails due to invalid SQL generated by CakePHP
------------------------------------+---------------------------------------
Reporter: c_schmitz | Type: Bug
Status: new | Priority: High
Milestone: 1.2.x.x | Component: ACL (Access Control List)
Version: 1.2 Final | Severity: Major
Keywords: mssql ARO c_schmitz | Php_version: PHP 5
Cake_version: 1.2.0.7962 |
------------------------------------+---------------------------------------
I am using MSSQL Server 2005 & PHP5
The following code snippet worked flawlessly in 1.2RC4:
{{{
App::import('Component', 'Acl');
App::import('Core', 'db_acl');
// Let's create the ARO for the new admin user in the
Administrator group
$aro = new Aro();
$parent = $aro->findByAlias('Administrators');
$parentId = $parent['Aro']['id'];
$aro->create();
$aro->save(array(
'model'=>'User',
'foreign_key'=>$newuserid,
'parent_id'=>$parentId,
'alias'=>'User::'.$newuserid));
}}}
In 1.2 final the following queries are created:
{{{
6 SELECT COLUMN_NAME as Field, DATA_TYPE as Type,
COL_LENGTH('aros_acos', COLUMN_NAME) as Length, IS_NULLABLE As [Null],
COLUMN_DEFAULT as [Default], COLUMNPROPERTY(OBJECT_ID('aros_acos'),
COLUMN_NAME, 'IsIdentity') as [Key], NUMERIC_SCALE as Size FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aros_acos' 9
9 15
7 SELECT TOP 1 [Aro].[id] AS [Aro__0], [Aro].[parent_id] AS
[Aro__1], [Aro].[model] AS [Aro__2], [Aro].[foreign_key] AS [Aro__3],
[Aro].[alias] AS [Aro__4], [Aro].[lft] AS [Aro__5], [Aro].[rght] AS
[Aro__6], CONVERT(VARCHAR(20), [Aro].[created], 20) AS [Aro__7],
CONVERT(VARCHAR(20), [Aro].[modified], 20) AS [Aro__8] FROM [aros] AS
[Aro] WHERE [Aro].[alias] = 'Administrators' 1 1
6
8 SELECT [Aco].[id] AS [Aco__54], [Aco].[parent_id] AS [Aco__55],
[Aco].[model] AS [Aco__56], [Aco].[foreign_key] AS [Aco__57],
[Aco].[alias] AS [Aco__58], [Aco].[lft] AS [Aco__59], [Aco].[rght] AS
[Aco__60], CONVERT(VARCHAR(20), [Aco].[created], 20) AS [Aco__61],
CONVERT(VARCHAR(20), [Aco].[modified], 20) AS [Aco__62], [Permission].[id]
AS [Permission__45], [Permission].[aro_id] AS [Permission__46],
[Permission].[aco_id] AS [Permission__47], [Permission].[_create] AS
[Permission__48], [Permission].[_read] AS [Permission__49],
[Permission].[_update] AS [Permission__50], [Permission].[_delete] AS
[Permission__51], CONVERT(VARCHAR(20), [Permission].[created], 20) AS
[Permission__52], CONVERT(VARCHAR(20), [Permission].[modified], 20) AS
[Permission__53] FROM [acos] AS [Aco] JOIN [aros_acos] AS [Permission] ON
([Permission].[aro_id] = 1 AND [Permission].[aco_id] = [Aco].[id]) WHERE 1
= 1 3 3 25
9 SELECT TOP 1 [Aro].[id] AS [Aro__0], [Aro].[rght] AS [Aro__1] FROM
[aros] AS [Aro] WHERE 1 = 1 AND [Aro].[id] = 1 1 1
4
10 INSERT INTO [aros] ([model], [foreign_key], [parent_id], [alias],
[modified], [created], [lft], [rght]) VALUES ('User', 1, 1, 'User::1',
'2008-12-29 16:56:18', '2008-12-29 16:56:18', 0, 0) 1
10
11 SELECT SCOPE_IDENTITY() AS insertID 1 1 1
12 SELECT TOP 1 [Aro].[id] AS [Aro__0], [Aro].[parent_id] AS
[Aro__1], [Aro].[lft] AS [Aro__2], [Aro].[rght] AS [Aro__3] FROM [aros] AS
[Aro] WHERE 1 = 1 AND [Aro].[id] = 3 1 1 2
13 SELECT TOP 1 MAX([rght]) AS [rght] FROM [aros] AS [Aro] WHERE 1 =
1 AND [Aro].[id] <> 3 1 1 4
14 SELECT TOP 1 [Aro].[id] AS [Aro__0], [Aro].[lft] AS [Aro__1],
[Aro].[rght] AS [Aro__2] FROM [aros] AS [Aro] WHERE 1 = 1 AND [Aro].[id] =
1 1 1 2
15 SELECT [Aro].[id] AS [Aro__0] FROM [aros] AS [Aro] WHERE
[Aro].[lft] >= 2 AND NOT ([Aro].[id] = 3) 1 1 6
16 UPDATE [aros] SET [lft] = [Aro].[lft] + 2 WHERE [id] = ('2')
The multi-part identifier "Aro.lft" could not be bound. 33
}}}
Note the last query which is invalid since the field identifiers in the
SET part is invalid. This query is generated by CakePHP.
I am not familiar enough with CakePHP inner workings regarding that so I
don't know how to patch it myself.
If you have any questions please let me know.
--
Ticket URL: <https://trac.cakephp.org/ticket/5942>
CakePHP : The Rapid Development Framework for PHP <https://trac.cakephp.org/>
Cake is a rapid development framework for PHP which uses commonly known design
patterns like ActiveRecord, Association Data Mapping, Front Controller and MVC.
Our primary goal is to provide a structured framework that enables PHP users at
all levels to rapidly develop robust web applications, without any loss to
flexibility.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"tickets cakephp" group.
To post to this group, send email to tickets-cakephp@googlegroups.com
To unsubscribe from this group, send email to
tickets-cakephp+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/tickets-cakephp?hl=en
-~----------~----~----~----~------~----~------~--~---
