Skip to main content
Topic: Question about fetching data (Read 4507 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Question about fetching data

I've been working on adding an articles module to SimplePortal and realized that the permission system I designed for blocks and pages aren't very suitable when pagination is involved, which is a must when it comes to articles.

It's basically fetching a fixed number of rows from database without the information of whether you should fetch a particular row or not. Like, I need to fetch 5 articles per page but I cannot check if the user is allowed to view the article or not in the database query, only with a PHP function with the information that comes along with the rest of the article information.

Code: [Select]
function sp_get_articles()
{
global $smcFunc;

$request = $smcFunc['db_query']('','
SELECT id_article, permission_set, groups_allowed, groups_denied
FROM {db_prefix}sp_articles',
array()
);
$return = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
if (!sp_allowed_to('article', $row['id_article'], $row['permission_set'], $row['groups_allowed'], $row['groups_denied']))
continue;

$return[] = array(
'id' => $row['id_article'],
);
}
$smcFunc['db_free_result']($request);
}

function sp_allowed_to($type, $id, $set = null, $allowed = null, $denied = null)
{
global $smcFunc, $user_info;
static $cache, $types;

if (!isset($types))
{
$types = array(
'article' => array(
'table' => 'articles',
'id' => 'id_article',
),
'block' => array(
'table' => 'blocks',
'id' => 'id_block',
),
'category' => array(
'table' => 'categories',
'id' => 'id_category',
),
'page' => array(
'table' => 'pages',
'id' => 'id_page',
),
'shoutbox' => array(
'table' => 'shoutboxes',
'id' => 'id_shoutbox',
),
);
}

if (empty($id) || empty($type) || !isset($types[$type]))
return false;

if (!isset($set, $allowed, $denied))
{
$request = $smcFunc['db_query']('','
SELECT permission_set, groups_allowed, groups_denied
FROM {db_prefix}sp_{raw:table}
WHERE {raw:id} = {int:id_item}
LIMIT {int:limit}',
array(
'table' => $types[$type]['table'],
'id' => $types[$type]['id'],
'id_item' => $id,
'limit' => 1,
)
);
list ($set, $allowed, $denied) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
}

$result = false;
$cache_name = md5(implode(':', array($set, $allowed, $denied)));

if (isset($cache[$cache_name]))
$result = $cache[$cache_name];
else
{
switch ($set)
{
case 3:
$result = true;
break;
case 2:
$result = empty($user_info['is_guest']);
break;
case 1:
$result = !empty($user_info['is_guest']);
break;
case 0:
if (!empty($denied) && count(array_intersect($user_info['groups'], explode(',', $denied))) > 0)
$result = false;
elseif (!empty($allowed) && count(array_intersect($user_info['groups'], explode(',', $allowed))) > 0)
$result = true;
break;
default:
break;
}

$cache[$cache_name] = $result;
}

return $result;
}

I really don't want to change the permission system as it's very simple to use as an admin and account for every case I can think of. However, I'd rather change the permission system than fetching all the articles and limiting them with PHP. So I was wondering if anyone here has any ideas on how to add pagination while using the current permission system. Do you think it's possible and viable to make the check that sp_allowed_to() function does in a database query?

Re: Question about fetching data

Reply #1

I think the easiest way in that case would be to just use the first query to do the permission checks using FIND_IN_SET.
I'm not sure at the moment about the aspect of such query, but should be something like:
Code: [Select]
	$request = $smcFunc['db_query']('','
SELECT id_article, permission_set, groups_allowed, groups_denied
FROM {db_prefix}sp_articles
WHERE permission_set = 3
OR (permission_set = 2 AND {raw:is_guest})
OR (permission_set = 1 AND {raw:is_not_guest})
OR (permission_set = 0 AND ((FIND_IN_SET(' . implode(', groups_denied) = 0 OR FIND_IN_SET(', $user_info['groups']) . ', groups_denied)) AND (FIND_IN_SET(' . implode(', groups_allowed) != 0 OR FIND_IN_SET(', $user_info['groups']) . ', groups_allowed)))',
array(
'is_guest' => empty($user_info['is_guest']) ? '1=1' : '1=0',
'is_not_guest' => !empty($user_info['is_guest']) ? '1=1' : '1=0',
)
);

...a bit ugly... lol (and it may kill a lot of furry things :P)
Bugs creator.
Features destroyer.
Template killer.

Re: Question about fetching data

Reply #2

Thanks for the input emanuele. It turns out a bit ugly indeed. It'll probably be a costly query too.

I was considering other options and maybe it's time I added permission profiles that I had planned long ago. I plan on loading all the profiles and finding out the ones that give access to the current user. Then I can use that information and check a single field instead of the three for blocks, articles, pages and shoutboxes. It'll be a pain to code another module though. I guess I have no choice if I want to handle this cleanly.

Re: Question about fetching data

Reply #3

Hey we haz CSS bug with code tag overflowing the page.. Can't get decent CSS coders these days. :P
Master of Expletives: Now with improved family f@&king friendliness! :D

Sources code: making easy front end changes difficult since 1873. :P

Re: Question about fetching data

Reply #4

Oh, and I thought Antechinus had a solution to my problem. :P

And it's broken when large pictures are involved. (ie. http://www.elkarte.net/index.php?topic=492.0)

Re: Question about fetching data

Reply #5

That would be because someone hasn't added the CSS resizing for images in posts. I think it was on my local, and should have been in the last code dump. Someone has missed it anyway (me or Spuds or Norv). Easy fix.
Master of Expletives: Now with improved family f@&king friendliness! :D

Sources code: making easy front end changes difficult since 1873. :P

Re: Question about fetching data

Reply #6

I finally got around to implementing permission profiles and it turned out to be a very good improvement. It's now very easy and efficient to check for permissions and I got rid of tons of duplicate code since all 5 modules in SimplePortal used the old one. I really should do the same for style and display options.

Re: Question about fetching data

Reply #7

QuoteI finally got around to implementing permission profiles and it turned out to be a very good improvement
Awesome ... You have been busy :D