PHP Query::perform_transaction方法代码示例

function create_course($title, $courseNumber, $coordinator, $instructor, $description, $textbook, $creditHours)
{
    if (is_null($title) || $title == "") {
        page_fail_on_field(BAD_REQUEST, 'title', 'must be non-empty');
    }
    if (is_null($courseNumber) || $courseNumber == "") {
        page_fail_on_field(BAD_REQUEST, 'course_number', 'must be non-empty');
    }
    if (is_null($coordinator)) {
        page_fail_on_field(BAD_REQUEST, 'coordinator', 'must be non-empty');
    }
    if (is_null($instructor) || $instructor == "") {
        page_fail_on_field(BAD_REQUEST, 'instructor', 'must be non-empty');
    }
    if (is_null($description) || $description == "") {
        page_fail_on_field(BAD_REQUEST, 'description', 'must be non-empty');
    }
    if (is_null($textbook) || $textbook == "") {
        page_fail_on_field(BAD_REQUEST, 'textbook', 'must be non-empty');
    }
    if (is_null($creditHours) || $creditHours == "") {
        page_fail_on_field(BAD_REQUEST, 'credit_hours', 'must be non-empty');
    }
    $info = array('table' => 'course', 'fields' => array('title', 'course_number', 'fk_coordinator', 'instructor', 'description', 'textbook', 'credit_hours'), 'values' => array(array("s:{$title}", "s:{$courseNumber}", "i:{$coordinator}", "s:{$instructor}", "s:{$description}", "s:{$textbook}", "s:{$creditHours}")));
    list($code, $json) = Query::perform_transaction(function (&$rollback) use($info) {
        $insert = new Query(new QueryBuilder(INSERT_QUERY, $info));
        if (!$insert->validate_update()) {
            $rollback = true;
            return array(SERVER_ERROR, "{\"success\":false}");
        }
        $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('course' => array('id', 'title', 'fk_coordinator', 'instructor', 'description', 'textbook', 'credit_hours')), 'aliases' => array('course.fk_coordinator' => 'coordinator'), 'where' => 'course.id = LAST_INSERT_ID()')));
        if ($query->is_empty()) {
            $rollback = true;
            return array(SERVER_ERROR, "{\"success\":false}");
        }
        return array(OKAY, json_encode($query->get_row_assoc()));
    });
    http_response_code($code);
    return $json;
}

            page_fail(NOT_FOUND);
        }
        echo json_encode($row);
    } else {
        // create new program
        list($code, $json) = Query::perform_transaction(function (&$rollback) {
            // insert new row for new program
            $insert = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'program', 'fields' => array('name'), 'values' => array(array("l:'New Program'")))));
            if (!$insert->validate_update()) {
                $rollback = true;
                return array(SERVER_ERROR, "an insertion operation failed");
            }
            // grab the new program object
            $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('program' => array('id', 'name', 'abbrv', 'semester', 'year', 'description')), 'where' => 'id = LAST_INSERT_ID()')));
            $row = $query->get_row_assoc();
            if (is_null($row)) {
                $rollback = true;
                return array(SERVER_ERROR, "could not retrieve inserted row");
            }
            // create a default assessment for each criterion that has a general
            // content item
            foreach (range(1, 8) as $critId) {
                $assess = ABETAssessment::create('', $row['id'], null, $critId);
                $assess->add_general_content();
            }
            return array(OKAY, json_encode($row));
        });
        http_response_code($code);
        echo $json;
    }
} else {
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {

if ($_POST['role'] != 'faculty' && $_POST['role'] != 'admin' && $_POST['role'] != 'observer') {
    page_fail_on_field(BAD_REQUEST, 'role', 'role must be one of \'faculty\', \'admin\' or \'observer\'');
}
// perform a transaction that will atomically check the database and do an
// insert
list($code, $json) = Query::perform_transaction(function (&$rollback) {
    // make sure username is not already in use for another user
    $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('userprofile' => 'username'), 'where' => 'username = ? AND id <> ?', 'where-params' => array("s:{$_POST['username']}", "s:{$_SESSION['id']}"), 'limit' => 1)));
    // check select result
    if (!$query->is_empty()) {
        $rollback = true;
        return array(BAD_REQUEST, json_encode(array("error" => "the requested username is unavailable", "errField" => "username")));
    }
    // insert new 'userauth' entity
    $hash = password_hash($_POST['passwd'], PASSWORD_DEFAULT);
    $query = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'userauth', 'fields' => array('passwd', 'role'), 'values' => array(array("s:{$hash}", "s:{$_POST['role']}")))));
    if (!$query->validate_update()) {
        $rollback = true;
        return array(SERVER_ERROR, "{\"success\":false}");
    }
    // insert new 'userprofile' entity with foreign key to the newly created
    // 'userauth' entity; we use the password hash to identify the userauth instance
    $query = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'userprofile', 'fields' => array('fk_userauth', 'username', 'created'), 'select' => array('tables' => array('userauth' => 'id', 1 => array("'{$_POST['username']}'", "NOW()")), 'where' => "passwd = '{$hash}'"))));
    if (!$query->validate_update()) {
        $rollback = true;
        return array(SERVER_ERROR, "{\"success\":false}");
    }
    return array(OKAY, "{\"success\":true}");
});
http_response_code($code);
echo $json;

 function add_worksheet($activityCourse)
 {
     Query::perform_transaction(function (&$rollback) use($activityCourse) {
         // create rubric description
         $rd = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'rubric_description', 'fields' => array(), 'values' => array())));
         if (!$rd->validate_update()) {
             $rollback = true;
             self::fail("fail insert - 'rubric_description'");
         }
         // create rubric
         $rw = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'rubric', 'fields' => array('name', 'fk_description', 'created'), 'values' => array(array("l:'New Rubric'", "l:LAST_INSERT_ID()", "l:NOW()")))));
         if (!$rw->validate_update()) {
             $rollback = true;
             self::fail("fail insert - 'rubric'");
         }
         // we must select the id of the last inserted element
         $li = (new Query(new QueryBuilder(RAW_QUERY, array('query' => 'SELECT LAST_INSERT_ID()'))))->get_row_ordered();
         if (is_null($li)) {
             $rollback = true;
             self::fail("this shouldn't happen");
         }
         $rubricId = $li[0];
         // create rubric results
         $rr = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'rubric_results', 'fields' => array('total_students'), 'values' => array(array("l:0")))));
         if (!$rr->validate_update()) {
             $rollback = true;
             self::fail("fail insert - 'rubric_results'");
         }
         // create assessment_worksheet
         $activity = is_string($activityCourse) ? $activityCourse : "";
         $courseId = is_int($activityCourse) ? $activityCourse : null;
         $aw = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'assessment_worksheet', 'fields' => array('fk_assessment', 'activity', 'fk_course', 'fk_rubric', 'fk_rubric_results', 'created'), 'values' => array(array("l:{$this->id}", "s:{$activity}", "l:{$courseId}", "l:{$rubricId}", "l:LAST_INSERT_ID()", "l:NOW()")))));
         if (!$aw->validate_update()) {
             $rollback = true;
             self::fail("fail insert - 'assessment_worksheet'");
         }
     });
 }

function create_file($gcId)
{
    // we must change the file permissions to rw-r--r-- so that mySQL can
    // read the uploaded file; this allows 'others' to read the file (beware!)
    chmod($_FILES['file']['tmp_name'], 0644);
    // perform update/select operations within a transaction
    list($code, $message) = Query::perform_transaction(function (&$rollback) use($gcId) {
        global $DATETIME_FORMAT;
        // create new file_upload entity
        $insert = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'file_upload', 'fields' => array('file_name', 'file_contents', 'file_comment', 'file_created', 'fk_author', 'fk_content_set'), 'values' => array(array("s:{$_FILES['file']['name']}", "l:LOAD_FILE('{$_FILES['file']['tmp_name']}')", "s:", "l:NOW()", "i:{$_SESSION['id']}", "i:{$gcId}")))));
        if (!$insert->validate_update()) {
            $rollback = true;
            return array(SERVER_ERROR, "failed to insert file_upload");
        }
        // select the newly created row from the DB, along with some info about the user
        $row = (new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('file_upload' => array('id', 'file_name', 'file_comment'), 1 => "DATE_FORMAT(file_created,'{$DATETIME_FORMAT}') file_created", 'userprofile' => array('first_name', 'last_name')), 'joins' => 'INNER JOIN userprofile ON userprofile.id = file_upload.fk_author', 'where' => 'file_upload.id = LAST_INSERT_ID()'))))->get_row_assoc();
        if (is_null($row)) {
            $rollback = true;
            return array(SERVER_ERROR, "could not retrieve inserted row");
        }
        // format the data for the client
        //   id, file_name, file_comment (empty), file_created, author (string)
        $entity = new stdClass();
        $entity->id = $row['id'];
        $entity->file_name = $row['file_name'];
        $entity->file_comment = $row['file_comment'];
        $entity->file_created = $row['file_created'];
        $entity->author = "{$row['first_name']} {$row['last_name']}";
        return array(OKAY, json_encode($entity));
    });
    if ($code != OKAY) {
        page_fail_with_reason($code, $message);
    }
    return $message;
}

function add_comp_row($id)
{
    // 'id' is worksheet id
    return Query::perform_transaction(function (&$rollback) use($id) {
        // select id of rubric_results entity
        $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('rubric_results' => 'id'), 'joins' => array('INNER JOIN assessment_worksheet ON assessment_worksheet.fk_rubric_results = rubric_results.id'), 'where' => 'assessment_worksheet.id = ?', 'where-params' => array("i:{$id}"))));
        if ($query->is_empty()) {
            page_fail(NOT_FOUND);
        }
        $rrId = $query->get_row_ordered()[0];
        // insert new competency_results entity
        $insert = new Query(new QueryBuilder(INSERT_QUERY, array('table' => 'competency_results', 'fields' => array('outstanding_tally', 'expected_tally', 'marginal_tally', 'unacceptable_tally', 'fk_rubric_results'), 'values' => array(array("l:0", "l:0", "l:0", "l:0", "l:{$rrId}")))));
        // select the inserted row and return it
        $comp = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('competency_results' => array('id', 'competency_desc', 'outstanding_tally', 'expected_tally', 'marginal_tally', 'unacceptable_tally', 'pass_fail_type', 'comment')), 'aliases' => array('competency_results.competency_desc' => 'description'), 'where' => 'id = LAST_INSERT_ID()')));
        if ($comp->is_empty()) {
            page_fail(SERVER_ERROR);
        }
        // shouldn't happen
        return json_encode($comp->get_row_assoc());
    });
}

function create_characteristic($level, $shortName, $description, $programSpecifier)
{
    if (is_null($level) || $level == "") {
        page_fail_on_field(BAD_REQUEST, 'level', 'must be non-empty');
    }
    if (is_null($shortName) || $shortName == "") {
        page_fail_on_field(BAD_REQUEST, 'short_name', 'must be non-empty');
    }
    if (is_null($description) || $description == "") {
        page_fail_on_field(BAD_REQUEST, 'description', 'must be non-empty');
    }
    $info = array('table' => 'abet_characteristic', 'fields' => array('level', 'short_name', 'description'), 'values' => array(array("s:{$level}", "s:{$shortName}", "s:{$description}")));
    if (!is_null($programSpecifier) && $programSpecifier != "") {
        $info['fields'][] = 'program_specifier';
        $info['values'][0][] = "s:{$programSpecifier}";
    }
    list($code, $json) = Query::perform_transaction(function (&$rollback) use($info) {
        $insert = new Query(new QueryBuilder(INSERT_QUERY, $info));
        if (!$insert->validate_update()) {
            $rollback = true;
            return array(SERVER_ERROR, "{\"success\":false}");
        }
        $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('abet_characteristic' => array('id', 'level', 'short_name', 'description', 'program_specifier')), 'where' => 'abet_characteristic.id = LAST_INSERT_ID()')));
        if ($query->is_empty()) {
            $rollback = true;
            return array(SERVER_ERROR, "{\"success\":false}");
        }
        return array(OKAY, json_encode($query->get_row_assoc()));
    });
    http_response_code($code);
    return $json;
}

function abet_change_password($user, $passwd, $alwaysUpdate = false)
{
    return Query::perform_transaction(function (&$rollback) use($user, $passwd, $alwaysUpdate) {
        // grab old password and the userauth entity id
        $result = (new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('userauth' => array('id', 'passwd', 'old_passwd')), 'joins' => array('INNER JOIN userprofile ON userauth.id = userprofile.fk_userauth'), 'where' => 'userprofile.username = ?', 'where-params' => array("s:{$user}")))))->get_row_assoc();
        if (is_null($result)) {
            // this shouldn't happen unless the account was deleted
            $rollback = true;
            return false;
        }
        // make sure new password is not the same as the old passwords
        if (!$alwaysUpdate && (password_verify($passwd, $result['passwd']) || password_verify($passwd, $result['old_passwd']))) {
            $rollback = true;
            return false;
        }
        // update password and old password
        $hash = password_hash($passwd, PASSWORD_DEFAULT);
        $query = new Query(new QueryBuilder(UPDATE_QUERY, array('table' => 'userauth', 'updates' => array('passwd' => "s:{$hash}", 'old_passwd' => "s:{$result['passwd']}"), 'where' => "id = {$result['id']}", 'limit' => 1)));
        if (!$query->validate_update()) {
            // this shouldn't really happen
            $rollback = true;
            return false;
        }
        return true;
    });
}