By tostinni on

I still struggle with my oracle DB schema and face a problem.

I'm trying to display correctly the first Drupal page and it comes with this query (user.module):

$result = db_query('SELECT DISTINCT(p.perm) FROM {role} r INNER JOIN {permission} p ON p.rid = r.rid INNER JOIN {users_roles} ur ON ur.rid = r.rid WHERE ur.uid = %d', $account->uid);

"perm" is defined as a text column in MySQL/PgSQL schema, so I convert it to a CLOB in Oracle. But Oracle doesn't support DISTINCT clause with CLOB column.

Any idea of what could I do ?
I thought converting it into a VARCHAR2(4000) but I don't like very much the idea (I'm not sure it'll be big enough to fit a lot of permissions...).
I also don't like very much storing all permission in a column with coma to separate them, but I don't think this is a possibility for the moment, am I wrong ? :D
Any more thoughts ?

From Oracle doc :

You cannot specify a LOB column in a SELECT... DISTINCT or SELECT...
UNIQUE statement or in a join. However, you can specify a LOB attribute of an
object type column in a SELECT... DISTINCT statement or in a query that uses
the UNION or MINUS set operator if the column's object type has a MAP or
ORDER function defined on it.

⋅ Categories: Drupal 4.7.x

Comments

the_other_mac’s picture

the_other_mac commented

Obviously I can't judge your own case. But here's an example set of permissions from a real site: a:1:{s:5:"roles";a:1:{s:18:"authenticated user";i:2;}}. That site doesn't have an especially complex set of permissions, that user belongs to 2 roles (which is superfluous since one is a superset of the other). But I find it hard to imagine how it could approach 4000 characters. Don't forget that users aren't assigned permissions individually for each activity, they're assigned roles, and the roles are assigned permissions for activities.

If there was a chance of extremely long strings, the Drupal developer could have chosen MySQL's LONGTEXT (which is more equivalent to CLOB).

Good to hear someone's working on an Oracle conversion.