public function testUnionQuery() { //regular user no elevation $user = new User(); $user->username = '******'; $user->firstName = 'aaa'; $user->lastName = 'aaa'; $saved = $user->save(); $this->assertTrue($saved); $aUser = User::getByUsername('aaa'); Yii::app()->user->userModel = $aUser; $mungeIds = AllPermissionsOptimizationUtil::getMungeIdsByUser($aUser); $model = new OwnedSecurableTestItem(); $model->member = 'test4'; $model->owner = $aUser; $this->assertTrue($model->save()); Yii::app()->user->userModel = User::getByUsername('super'); $this->assertEquals(4, OwnedSecurableTestItem::getCount()); $model = new OwnedSecurableTestItem2(); $model->member = 'test4'; $model->owner = $aUser; $this->assertTrue($model->save()); $this->assertEquals(2, OwnedSecurableTestItem2::getCount()); Yii::app()->user->userModel = User::getByUsername('aaa'); $quote = DatabaseCompatibilityUtil::getQuote(); //Test search attribute data across multiple models. $aFakePost = array('member' => 'test4'); $aMetadataAdapter = new SearchDataProviderMetadataAdapter(new OwnedSecurableTestItem(false), $aUser->id, $aFakePost); $bFakePost = array('member' => 'test4'); $bMetadataAdapter = new SearchDataProviderMetadataAdapter(new OwnedSecurableTestItem2(false), $aUser->id, $bFakePost); $modelClassNamesAndSearchAttributeData = array(array('OwnedSecurableTestItem' => $aMetadataAdapter->getAdaptedMetadata()), array('OwnedSecurableTestItem2' => $bMetadataAdapter->getAdaptedMetadata())); $unionSql = RedBeanModelsDataProvider::makeUnionSql($modelClassNamesAndSearchAttributeData, null, false, 2, 7); $compareWhere = "({$quote}ownedsecurabletestitem{$quote}.{$quote}member{$quote} like 'test4%')"; $compareWhere2 = "({$quote}ownedsecurabletestitem2{$quote}.{$quote}member{$quote} like 'test4%')"; $compareSubsetSql = "("; $compareSubsetSql .= "select distinct {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id"; $compareSubsetSql .= ", 'OwnedSecurableTestItem' modelClassName "; $compareSubsetSql .= "from ({$quote}ownedsecurabletestitem{$quote}, {$quote}ownedsecurableitem{$quote}) "; $compareSubsetSql .= "left join {$quote}ownedsecurabletestitem_read{$quote} on "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem_read{$quote}.{$quote}securableitem_id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurableitem{$quote}.{$quote}securableitem_id{$quote} "; $compareSubsetSql .= "and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "') "; $compareSubsetSql .= "where ({$compareWhere}) and ({$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = {$aUser->id} "; $compareSubsetSql .= "OR {$quote}ownedsecurabletestitem_read{$quote}.{$quote}munge_id{$quote} IS NOT NULL) "; // Not Coding Standard $compareSubsetSql .= "and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem{$quote}.{$quote}ownedsecurableitem_id{$quote}"; $compareSubsetSql .= ") "; $compareSubsetSql .= "UNION ("; $compareSubsetSql .= "select distinct {$quote}ownedsecurabletestitem2{$quote}.{$quote}id{$quote} id"; $compareSubsetSql .= ", 'OwnedSecurableTestItem2' modelClassName "; $compareSubsetSql .= "from ({$quote}ownedsecurabletestitem2{$quote}, {$quote}ownedsecurableitem{$quote}) "; $compareSubsetSql .= "left join {$quote}ownedsecurabletestitem2_read{$quote} on "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem2_read{$quote}.{$quote}securableitem_id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurableitem{$quote}.{$quote}securableitem_id{$quote} "; $compareSubsetSql .= "and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "') "; $compareSubsetSql .= "where ({$compareWhere2}) and ({$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = {$aUser->id} "; $compareSubsetSql .= "OR {$quote}ownedsecurabletestitem2_read{$quote}.{$quote}munge_id{$quote} IS NOT NULL) "; // Not Coding Standard $compareSubsetSql .= "and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem2{$quote}.{$quote}ownedsecurableitem_id{$quote}"; $compareSubsetSql .= ") "; $compareSubsetSql .= 'limit 7 offset 2'; $this->assertEquals($compareSubsetSql, $unionSql); //Make sure the sql runs properly. $dataProvider = new RedBeanModelsDataProvider('anId', null, false, $modelClassNamesAndSearchAttributeData); $data = $dataProvider->getData(); //Test results are correct $this->assertEquals(2, count($data)); $this->assertEquals('OwnedSecurableTestItem', get_class($data[0])); $this->assertEquals('OwnedSecurableTestItem2', get_class($data[1])); //Make sure union count query produces the same count. $this->assertEquals(2, $dataProvider->calculateTotalItemCount()); }
public function testMungeForRegularUserMakeSubsetOrCountSqlQuery() { $quote = DatabaseCompatibilityUtil::getQuote(); //regular user no elevation $benny = User::getByUsername('benny'); Yii::app()->user->userModel = $benny; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $mungeIds = ReadPermissionsOptimizationUtil::getMungeIdsByUser($benny); $subsetSql = OwnedSecurableTestItem::makeSubsetOrCountSqlQuery('ownedsecurabletestitem', $joinTablesAdapter, 1, 5, null, null); $compareSubsetSql = "select distinct {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from ({$quote}ownedsecurabletestitem{$quote}, {$quote}ownedsecurableitem{$quote}) "; $compareSubsetSql .= "left join {$quote}ownedsecurabletestitem_read{$quote} on "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem_read{$quote}.{$quote}securableitem_id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurableitem{$quote}.{$quote}securableitem_id{$quote} "; $compareSubsetSql .= "and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "') "; $compareSubsetSql .= "where ({$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = {$benny->id} "; $compareSubsetSql .= "OR {$quote}ownedsecurabletestitem_read{$quote}.{$quote}munge_id{$quote} IS NOT NULL) "; // Not Coding Standard $compareSubsetSql .= "and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem{$quote}.{$quote}ownedsecurableitem_id{$quote}"; $compareSubsetSql .= ' limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //Make sure the sql runs properly. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $data = OwnedSecurableTestItem::getSubset($joinTablesAdapter, 0, 5, null, null); $this->assertEquals(1, count($data)); $this->assertTrue($data[0]->owner->isSame($benny)); //Check using the getCount method produces the same results. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $this->assertEquals(1, OwnedSecurableTestItem::getCount($joinTablesAdapter)); //Add in a where clause and make sure the query still works. $metadataAdapter = new SearchDataProviderMetadataAdapter(new OwnedSecurableTestItem(), $benny->id, array('member' => 'test')); $searchAttributeData = $metadataAdapter->getAdaptedMetadata(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $where = RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $compareWhere = "({$quote}ownedsecurabletestitem{$quote}.{$quote}member{$quote} like 'test%')"; $this->assertEquals($compareWhere, $where); $subsetSql = OwnedSecurableTestItem::makeSubsetOrCountSqlQuery('ownedsecurabletestitem', $joinTablesAdapter, 1, 5, $where, null); $compareSubsetSql = "select distinct {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from ({$quote}ownedsecurabletestitem{$quote}, {$quote}ownedsecurableitem{$quote}) "; $compareSubsetSql .= "left join {$quote}ownedsecurabletestitem_read{$quote} on "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem_read{$quote}.{$quote}securableitem_id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurableitem{$quote}.{$quote}securableitem_id{$quote} "; $compareSubsetSql .= "and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "') "; $compareSubsetSql .= "where ({$compareWhere}) and ({$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = {$benny->id} "; $compareSubsetSql .= "OR {$quote}ownedsecurabletestitem_read{$quote}.{$quote}munge_id{$quote} IS NOT NULL) "; // Not Coding Standard $compareSubsetSql .= "and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem{$quote}.{$quote}ownedsecurableitem_id{$quote}"; $compareSubsetSql .= ' limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //now run query to make sure it actually works. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $where = RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $data = OwnedSecurableTestItem::getSubset($joinTablesAdapter, 0, 5, null, null); $this->assertEquals(1, count($data)); $this->assertTrue($data[0]->owner->isSame($benny)); //Check using the getCount method produces the same results. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $this->assertEquals(1, OwnedSecurableTestItem::getCount($joinTablesAdapter)); //Now give Benny READ on the module. After that he should not need the munge query. Yii::app()->user->userModel = User::getByUsername('super'); $item = NamedSecurableItem::getByName('ZurmoModule'); $item->addPermissions($benny, Permission::READ); $this->assertTrue($item->save()); Yii::app()->user->userModel = User::getByUsername('benny'); //There should not be a 'distinct' in the query or the munge part. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $subsetSql = OwnedSecurableTestItem::makeSubsetOrCountSqlQuery('ownedsecurabletestitem', $joinTablesAdapter, 1, 5, null, null); $compareSubsetSql = "select {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from {$quote}ownedsecurabletestitem{$quote} "; $compareSubsetSql .= ' limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //Make sure the sql runs properly. $data = OwnedSecurableTestItem::getSubset($joinTablesAdapter, 0, 5, null, null); $this->assertEquals(3, count($data)); //Check using the getCount method produces the same results. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $this->assertEquals(3, OwnedSecurableTestItem::getCount($joinTablesAdapter)); //make sure adding a where query is ok without any munge query parts $metadataAdapter = new SearchDataProviderMetadataAdapter(new OwnedSecurableTestItem(), $benny->id, array('member' => 'test')); $searchAttributeData = $metadataAdapter->getAdaptedMetadata(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $where = RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $compareWhere = "({$quote}ownedsecurabletestitem{$quote}.{$quote}member{$quote} like 'test%')"; $this->assertEquals($compareWhere, $where); $subsetSql = OwnedSecurableTestItem::makeSubsetOrCountSqlQuery('ownedsecurabletestitem', $joinTablesAdapter, 1, 5, $where, null); $compareSubsetSql = "select {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from {$quote}ownedsecurabletestitem{$quote} "; $compareSubsetSql .= "where {$compareWhere}"; $compareSubsetSql .= ' limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //now run query to make sure it actually works. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $data = OwnedSecurableTestItem::getSubset($joinTablesAdapter, 0, 5, null, null); $this->assertEquals(3, count($data)); //Check using the getCount method produces the same results. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $where = RedBeanModelDataProvider::makeWhere('OwnedSecurableTestItem', $searchAttributeData, $joinTablesAdapter); $this->assertEquals(3, OwnedSecurableTestItem::getCount($joinTablesAdapter)); //Now remove READ ALLOW for BENNY, and Add READ DENY Yii::app()->user->userModel = User::getByUsername('super'); $item = NamedSecurableItem::getByName('ZurmoModule'); $item->removePermissions($benny, Permission::READ); $this->assertTrue($item->save()); $item->addPermissions($benny, Permission::READ, Permission::DENY); $this->assertTrue($item->save()); Yii::app()->user->userModel = User::getByUsername('benny'); //Now confirm that the query is just on the owner only, no munge. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $mungeIds = ReadPermissionsOptimizationUtil::getMungeIdsByUser($benny); $subsetSql = OwnedSecurableTestItem::makeSubsetOrCountSqlQuery('ownedsecurabletestitem', $joinTablesAdapter, 1, 5, null, null); $compareSubsetSql = "select {$quote}ownedsecurabletestitem{$quote}.{$quote}id{$quote} id "; $compareSubsetSql .= "from ({$quote}ownedsecurabletestitem{$quote}, {$quote}ownedsecurableitem{$quote}) "; $compareSubsetSql .= "where {$quote}ownedsecurableitem{$quote}.{$quote}owner__user_id{$quote} = {$benny->id} "; $compareSubsetSql .= "and {$quote}ownedsecurableitem{$quote}.{$quote}id{$quote} = "; $compareSubsetSql .= "{$quote}ownedsecurabletestitem{$quote}.{$quote}ownedsecurableitem_id{$quote}"; $compareSubsetSql .= ' limit 5 offset 1'; $this->assertEquals($compareSubsetSql, $subsetSql); //Make sure the sql runs properly. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $data = OwnedSecurableTestItem::getSubset($joinTablesAdapter, 0, 5, null, null); $this->assertEquals(1, count($data)); $this->assertTrue($data[0]->owner->isSame($benny)); //Check using the getCount method produces the same results. $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('OwnedSecurableTestItem'); $this->assertEquals(1, OwnedSecurableTestItem::getCount($joinTablesAdapter)); }