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; }); }
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; }
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;
} if ($_SERVER['REQUEST_METHOD'] == 'GET') { if (array_key_exists('id', $_GET)) { // get existing program $query = new Query(new QueryBuilder(SELECT_QUERY, array('tables' => array('program' => array('id', 'name', 'abbrv', 'semester', 'year', 'description')), 'where' => 'id = ?', 'where-params' => array("s:{$_GET['id']}")))); $row = $query->get_row_assoc(); if (is_null($row)) { 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();
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 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; }