This is an archived copy of the forum for reference purposes

Problem with INSERT INTO... (SELECT... in a prepared statement

posted by aris 9 years ago

Hello.

I'm not sure whether or not this will be a flourish related problem. I'm running in trouble with a prepared statement for inserting data which includes a select as well.

Within my groups management application I have defined both 'roles' and 'functions' tables, with the usual 'role_functions' joint table, as well. After the user creates a group, the roles table is filled with some default, predefined roles for that group, each one of these having a predefined ACL with its list of permitted functions, i.e. for 'member' role:

    $default_ACLs['member'] => array(
        'resources' => array('view', 'new', 'edit'),
        'users'     => array('view')
    )

As far as I need the role id to fill the role_functions records, and also to search at the functions table for those actions (actually, 'resources_view', 'resources_new', and so on) to put those ids in the join table, I have split the process in two parts, defining the following two methods in the Role class:

    /**
     * Set default roles and functions for new group
     */
    static public function addInitialRoles( $id_group )
    {
        $stmt = $GLOBALS['db']->prepare("INSERT INTO role_functions (id_role, id_function)
                VALUES (%i, (SELECT id FROM functions WHERE name = '%s'))");

        foreach( $GLOBALS['config']->default_roles as $role_name ) {
            // Add the new role
            $id_role = self::newRole( $role_name, $id_group );

            // Add the role-functions join records
            foreach ($GLOBALS['config']->default_ACLs[$role_name] as $module => $perms_array) {
                foreach ( $perms_array as $action ) {
                    $function_name = $module . '_' . $action;
                    $GLOBALS['db']->query( $stmt, $id_role,  $function_name);
                }
            }
        }
    }

    /**
     * Add a role name to a group
     */
    static public function newRole( $role_name, $id_group )
    {
        try {
            $role = new Role();
            $role->setName( $role_name );
            $role->setIdGroup( $id_group );
            $role->store();
            $id_role = $role->getId();
            return $id_role;

        } catch (fExpectedException $e) {

            return false;
        }
    }

The newRole() method goes fine, and the new role id is successfully returned. However, when It comes to save the list of authorized functions, the prepared statement fails with the following errors:

Warning: vsprintf() [function.vsprintf]: Too few arguments in /usr/local/share/php/flourish/classes/fStatement.php on line 99
Warning: vsprintf() [function.vsprintf]: Too few arguments in /usr/local/share/php/flourish/classes/fStatement.php on line 139

Fatal error: Uncaught exception 'fSQLException' with message 'PostgreSQL error (ERROR: bind message supplies 2 parameters, but prepared statement "fstmt2" requires 0) in {false}' in /usr/local/share/php/flourish/classes/fDatabase.php:421 Stack trace: 
#0 /usr/local/share/php/flourish/classes/fDatabase.php(2198): fDatabase->checkForError(Object(fResult), NULL) 
#1 /usr/local/share/php/flourish/classes/fDatabase.php(2679): fDatabase->performQuery(Object(fStatement), Object(fResult), Array) 
#2 /usr/local/share/php/flourish/classes/fDatabase.php(2580): fDatabase->run(Object(fStatement), 'fResult', Array) 
#3 /usr/local/www/agoravirtual_v6/applications/vomanager/private/models/Role.php(45): fDatabase->query(Object(fStatement), '4', 'resources_view') 
#4 /usr/local/www/applications/manager/private/controllers/groups_new.php(93): Role::addInitialRoles('5')

(Oddly enough, I get a 'Too few arguments' followed by a 'supplies 2 parameters, but "fstmt2" requires 0').

I have succesfully used the INSERT...(SELECT construct in other applications, (albeit "manually"), but it seems that for some reason it doesn't run within a prepared statement.

Am I missing something here? (Of course, if there is another, cleaner, flourish-way to do that, I'll be glad to hear of it.)

Thanks in advance, A.

Can you paste the SQL that you prepared and the data your are trying to use with it?

posted by wbond 9 years ago

Well, while I was looking the code to answer you, it seems I have got it solved:

The statement I was trying to get was the following one (cutting the inessential):

$sql = "INSERT INTO role_functions (id_role, id_function) 
      VALUES (%i, (SELECT id FROM functions WHERE name='%s'))";
$stmt = $GLOBALS['db']->prepare( $sql );
foreach (...) {
      $GLOBALS['db']->query( $stmt, $id_role, $function_name);
      ...
}

It seems that the error was in the single quotes around '%s' (surely because it was copied and pasted from my previous "manual" version). Removing those quotes, it seems to run fine. Now in the PostgreSQL logs I get some records as:

...
Sep 22 20:45:22 postgres[20332]: [51-1] LOG: execute fstmt2: INSERT INTO role_functions 
                (id_role, id_function) VALUES ($1, (SELECT id FROM functions WHERE name=$2))
Sep 22 20:45:22 postgres[20332]: [51-2] DETAIL: parameters: $1 = '3', $2 = 'resources_new'
...

The question about a possible better design remains, anyway. As each one of the predefined roles has four or five modules with two or three actions each, it means around 24 to 45 insertions in the role_functions join table for a group, what looks as a good candidate for prepared statements, although it seems a bit slow. Fortunately enough, this process is required only once for each group creation. (They need to be stored for each group because those permissions are only a initial setup and each group is able to edit them laterly, adding or removing actions.)

Am I missing any Flourish feature offering a better solution to do that?

Thanks again for your help and this very nice library!

posted by aris 9 years ago

Is the name column in your functions table indexed? If not, that could be causing performance issues since the db would have to do a full table scan for each inserted record.

I would definitely try running an EXPLAIN query for your INSERT query to see what Postgres is actually doing behind the scenes.

posted by wbond 9 years ago