From df28d6c5e6bea55f6845a4baec250b1b6e8a986d Mon Sep 17 00:00:00 2001 From: moodler Date: Tue, 17 Dec 2002 04:41:18 +0000 Subject: [PATCH] Contains database functions split-off from moodlelib, just so you can see what I'm doing ... Some functions have been extended and cleaned up ... I need to do more testing on it all before checking in all the other changed files like moodlelib.php and setup.php --- lib/database.php | 622 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 622 insertions(+) create mode 100644 lib/database.php diff --git a/lib/database.php b/lib/database.php new file mode 100644 index 0000000000..4ec59405f0 --- /dev/null +++ b/lib/database.php @@ -0,0 +1,622 @@ +Execute("$command"); + + if ($result) { + if ($feedback) { + echo "

".get_string("success")."

"; + } + return true; + } else { + if ($feedback) { + echo "

".get_string("error")."

"; + } + return false; + } +} + +function modify_database($sqlfile) { +/// Assumes that the input text file consists of a number +/// of SQL statements ENDING WITH SEMICOLONS. The semicolons +/// MUST be the last character in a line. +/// Lines that are blank or that start with "#" are ignored. +/// Only tested with mysql dump files (mysqldump -p -d moodle) + + global $CFG; + + if (file_exists($sqlfile)) { + $success = true; + $lines = file($sqlfile); + $command = ""; + + while ( list($i, $line) = each($lines) ) { + $line = chop($line); + $length = strlen($line); + + if ($length && substr($line, 0, 1) <> "#") { + if (substr($line, $length-1, 1) == ";") { + $line = substr($line, 0, $length-1); // strip ; + $command .= $line; + $command = str_replace("prefix_", $CFG->prefix, $command); // Table prefixes + if (! execute_sql($command)) { + $success = false; + } + $command = ""; + } else { + $command .= $line; + } + } + } + + } else { + $success = false; + echo "

Tried to modify database, but \"$sqlfile\" doesn't exist!

"; + } + + return $success; +} + + +function record_exists($table, $field="", $value="", $field2="", $value2="", $field3="", $value3="") { +/// Returns true or false depending on whether the specified record exists + + global $CFG; + + if ($field and $value) { + $select = "WHERE $field = '$value'"; + if ($field2 and $value2) { + $select .= " AND $field2 = '$value2'"; + if ($field3 and $value3) { + $select .= " AND $field3 = '$value3'"; + } + } + } + + return record_exists_sql("SELECT * FROM $CFG->prefix$table $select LIMIT 1"); +} + + +function record_exists_sql($sql) { +/// Returns true or false depending on whether the specified record exists +/// The sql statement is provided as a string. + + global $db; + + $rs = $db->Execute($sql); + if (!$rs) return false; + + if ( $rs->RecordCount() ) { + return true; + } else { + return false; + } +} + + +function count_records($table, $field="", $value="", $field2="", $value2="", $field3="", $value3="") { +/// Get all the records and count them + + global $CFG; + + if ($field and $value) { + $select = "WHERE $field = '$value'"; + if ($field2 and $value2) { + $select .= " AND $field2 = '$value2'"; + if ($field3 and $value3) { + $select .= " AND $field3 = '$value3'"; + } + } + } + + return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select"); +} + +function count_records_sql($sql) { +/// Get all the records and count them +/// The sql statement is provided as a string. + + global $db; + + $rs = $db->Execute("$sql"); + if (!$rs) return 0; + + return $rs->fields[0]; +} + +function get_record($table, $field, $value, $field2="", $value2="", $field3="", $value3="") { +/// Get a single record as an object + + global $CFG; + + $select = "WHERE $field = '$value'"; + + if ($field2 and $value2) { + $select .= " AND $field2 = '$value2'"; + if ($field3 and $value3) { + $select .= " AND $field3 = '$value3'"; + } + } + + return get_record_sql("SELECT * FROM $CFG->prefix$table $select"); +} + +function get_record_sql($sql) { +/// Get a single record as an object +/// The sql statement is provided as a string. + + global $db; + + $rs = $db->Execute("$sql"); + if (!$rs) return false; + + if ( $rs->RecordCount() == 1 ) { + return (object)$rs->fields; + } else { + return false; + } +} + +function get_records($table, $field="", $value="", $sort="", $fields="*") { +/// Get a number of records as an array of objects +/// Can optionally be sorted eg "time ASC" or "time DESC" +/// If "fields" is specified, only those fields are returned +/// The "key" is the first column returned, eg usually "id" + + global $CFG; + + if ($field and $value) { + $select = "WHERE $field = '$value'"; + } + if ($sort) { + $sortorder = "ORDER BY $sort"; + } + + return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sortorder"); +} + + +function get_records_list($table, $field="", $values="", $sort="", $fields="*") { +// Get a number of records as an array of objects +// Differs from get_records() in that the values variable +// can be a comma-separated list of values eg "4,5,6,10" +// Can optionally be sorted eg "time ASC" or "time DESC" +// The "key" is the first column returned, eg usually "id" + + global $CFG; + + if ($field and $value) { + $select = "WHERE $field in ($values)"; + } + if ($sort) { + $sortorder = "ORDER BY $sort"; + } + + return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sortorder"); +} + + +function get_records_sql($sql) { +// Get a number of records as an array of objects +// The "key" is the first column returned, eg usually "id" +// The sql statement is provided as a string. + + global $db; + + $rs = $db->Execute("$sql"); + if (!$rs) return false; + + if ( $rs->RecordCount() > 0 ) { + if ($records = $rs->GetAssoc(true)) { + foreach ($records as $key => $record) { + $objects[$key] = (object) $record; + } + return $objects; + } else { + return false; + } + } else { + return false; + } +} + +function get_records_sql_menu($sql) { +// Given an SQL select, this function returns an associative +// array of the first two columns. This is most useful in +// combination with the choose_from_menu function to create +// a form menu. + + global $db; + + $rs = $db->Execute("$sql"); + if (!$rs) return false; + + if ( $rs->RecordCount() > 0 ) { + while (!$rs->EOF) { + $menu[$rs->fields[0]] = $rs->fields[1]; + $rs->MoveNext(); + } + return $menu; + + } else { + return false; + } +} + +function get_field($table, $return, $field, $value) { +/// Get a single field from a database record + + global $db, $CFG; + + $rs = $db->Execute("SELECT $return FROM $CFG->prefix$table WHERE $field = '$value'"); + if (!$rs) return false; + + if ( $rs->RecordCount() == 1 ) { + return $rs->fields["$return"]; + } else { + return false; + } +} + +function set_field($table, $newfield, $newvalue, $field, $value) { +/// Set a single field in a database record + + global $db, $CFG; + + return $db->Execute("UPDATE $CFG->prefix$table SET $newfield = '$newvalue' WHERE $field = '$value'"); +} + + +function delete_records($table, $field="", $value="", $field2="", $value2="", $field3="", $value3="") { +/// Delete one or more records from a table + + global $db, $CFG; + + if ($field and $value) { + $select = "WHERE $field = '$value'"; + if ($field2 and $value2) { + $select .= " AND $field2 = '$value2'"; + if ($field3 and $value3) { + $select .= " AND $field3 = '$value3'"; + } + } + } + + return $db->Execute("DELETE FROM $CFG->prefix$table $select"); +} + + +function insert_record($table, $dataobject, $returnid=true) { +/// Insert a record into a table and return the "id" field if required +/// If the return ID isn't required, then this just reports success as true/false. +/// $dataobject is an object containing needed data + + global $db, $CFG; + + // Determine all the fields needed + if (! $columns = $db->MetaColumns("$CFG->prefix$table")) { + return false; + } + + $data = (array)$dataobject; + + // Pull out data matching these fields + foreach ($columns as $column) { + if ($column->name <> "id" && isset($data[$column->name]) ) { + $ddd[$column->name] = $data[$column->name]; + } + } + + // Construct SQL queries + if (! $numddd = count($ddd)) { + return false; + } + + $count = 0; + $inscolumns = ""; + $insvalues = ""; + $select = ""; + + foreach ($ddd as $key => $value) { + $count++; + $inscolumns .= "$key"; + $insvalues .= "'$value'"; + $select .= "$key = '$value'"; + if ($count < $numddd) { + $inscolumns .= ", "; + $insvalues .= ", "; + $select .= " AND "; + } + } + + if (! $rs = $db->Execute("INSERT INTO $CFG->prefix$table ($inscolumns) VALUES ($insvalues)")) { + return false; + } + + if ($returnid) { + // Pull it out again to find the id. This is the most cross-platform method. + if ($rs = $db->Execute("SELECT id FROM $CFG->prefix$table WHERE $select")) { + return $rs->fields[0]; + } else { + return false; + } + } else { + return true; + } +} + + +function update_record($table, $dataobject) { +/// Update a record in a table +/// $dataobject is an object containing needed data + + global $db, $CFG; + + if (! isset($dataobject->id) ) { + return false; + } + + // Determine all the fields in the table + if (!$columns = $db->MetaColumns("$CFG->prefix$table")) { + return false; + } + $data = (array)$dataobject; + + // Pull out data matching these fields + foreach ($columns as $column) { + if ($column->name <> "id" && isset($data[$column->name]) ) { + $ddd[$column->name] = $data[$column->name]; + } + } + + // Construct SQL queries + $numddd = count($ddd); + $count = 0; + $update = ""; + + foreach ($ddd as $key => $value) { + $count++; + $update .= "$key = '$value'"; + if ($count < $numddd) { + $update .= ", "; + } + } + + if ($rs = $db->Execute("UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'")) { + return true; + } else { + return false; + } +} + + +function print_object($object) { +/// Mostly just for debugging + + $array = (array)$object; + foreach ($array as $key => $item) { + echo "$key -> $item
"; + } +} + + + + +/// USER DATABASE //////////////////////////////////////////////// + +function get_user_info_from_db($field, $value) { +/// Get a complete user record, which includes all the info +/// in the user record, as well as membership information +/// Suitable for setting as $USER session cookie. + + global $db, $CFG; + + if (!$field || !$value) + return false; + + if (! $result = $db->Execute("SELECT * FROM {$CFG->prefix}user WHERE $field = '$value' AND deleted <> '1'")) { + error("Could not find any active users!"); + } + + if ( $result->RecordCount() == 1 ) { + $user = (object)$result->fields; + + $rs = $db->Execute("SELECT course FROM {$CFG->prefix}user_students WHERE user = '$user->id' "); + while (!$rs->EOF) { + $course = $rs->fields["course"]; + $user->student["$course"] = true; + $rs->MoveNext(); + } + + $rs = $db->Execute("SELECT course FROM {$CFG->prefix}user_teachers WHERE user = '$user->id' "); + while (!$rs->EOF) { + $course = $rs->fields["course"]; + $user->teacher["$course"] = true; + $rs->MoveNext(); + } + + $rs = $db->Execute("SELECT * FROM {$CFG->prefix}user_admins WHERE user = '$user->id' "); + while (!$rs->EOF) { + $user->admin = true; + $rs->MoveNext(); + } + + if ($course = get_site()) { + // Everyone is always a member of the top course + $user->student["$course->id"] = true; + } + + return $user; + + } else { + return false; + } +} + +function update_user_in_db() { +/// Updates user record to record their last access + + global $db, $USER, $REMOTE_ADDR, $CFG; + + if (!isset($USER->id)) + return false; + + $timenow = time(); + if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow' + WHERE id = '$USER->id' ")) { + return true; + } else { + return false; + } +} + + +function adminlogin($username, $md5password) { +/// Does this username and password specify a valid admin user? + + global $CFG; + + return record_exists_sql("SELECT u.id FROM {$CFG->prefix}user u, {$CFG->prefix}user_admins a + WHERE u.id = a.user + AND u.username = '$username' + AND u.password = '$md5password'"); +} + + +function get_site () { +/// Returns $course object of the top-level site. + + if ( $course = get_record("course", "category", 0)) { + return $course; + } else { + return false; + } +} + +function get_admin () { +/// Returns $user object of the main admin user + + global $CFG; + + if ( $admins = get_admins() ) { + foreach ($admins as $admin) { + return $admin; // ie the first one + } + } else { + return false; + } +} + +function get_admins() { +/// Returns list of all admins + + global $CFG; + + return get_records_sql("SELECT u.* FROM {$CFG->prefix}user u, {$CFG->prefix}user_admins a + WHERE a.user = u.id + ORDER BY u.id ASC"); +} + + +function get_teacher($courseid) { +/// Returns $user object of the main teacher for a course + + global $CFG; + + if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) { + foreach ($teachers as $teacher) { + if ($teacher->authority) { + return $teacher; // the highest authority teacher + } + } + } else { + return false; + } +} + +function get_course_students($courseid, $sort="u.lastaccess DESC") { +/// Returns list of all students in this course + + global $CFG; + + return get_records_sql("SELECT u.* FROM {$CFG->prefix}user u, {$CFG->prefix}user_students s + WHERE s.course = '$courseid' AND s.user = u.id AND u.deleted = '0' + ORDER BY $sort"); +} + +function get_course_teachers($courseid, $sort="t.authority ASC") { +/// Returns list of all teachers in this course + + global $CFG; + + return get_records_sql("SELECT u.*,t.authority,t.role FROM {$CFG->prefix}user u, {$CFG->prefix}user_teachers t + WHERE t.course = '$courseid' AND t.user = u.id AND u.deleted = '0' + ORDER BY $sort"); +} + +function get_course_users($courseid, $sort="u.lastaccess DESC") { +/// Using this method because the direct SQL just would not always work! + + $teachers = get_course_teachers($courseid, $sort); + $students = get_course_students($courseid, $sort); + + if ($teachers and $students) { + return array_merge($teachers, $students); + } else if ($teachers) { + return $teachers; + } else { + return $students; + } + +/// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t +/// WHERE (s.course = '$courseid' AND s.user = u.id) OR +/// (t.course = '$courseid' AND t.user = u.id) +/// ORDER BY $sort"); +} + + + +/// MODULE FUNCTIONS ///////////////////////////////////////////////// + +function get_coursemodule_from_instance($modulename, $instance, $courseid) { +/// Given an instance of a module, finds the coursemodule description + + global $CFG; + + return get_record_sql("SELECT cm.*, m.name + FROM {$CFG->prefix}course_modules cm, {$CFG->prefix}modules md, {$CFG->prefix}$modulename m + WHERE cm.course = '$courseid' AND + cm.deleted = '0' AND + cm.instance = m.id AND + md.name = '$modulename' AND + md.id = cm.module AND + m.id = '$instance'"); + +} + +function get_all_instances_in_course($modulename, $courseid, $sort="cw.section") { +/// Returns an array of all the active instances of a particular +/// module in a given course. Returns false on any errors. + + global $CFG; + + return get_records_sql("SELECT m.*,cw.section,cm.id as coursemodule + FROM {$CFG->prefix}course_modules cm, {$CFG->prefix}course_sections cw, + {$CFG->prefix}modules md, {$CFG->prefix}$modulename m + WHERE cm.course = '$courseid' AND + cm.instance = m.id AND + cm.deleted = '0' AND + cm.section = cw.id AND + md.name = '$modulename' AND + md.id = cm.module + ORDER BY $sort"); + +} + +?> -- 2.39.5