Moodle开发笔记6-Database操作

Moodle db table的规则：

·         the first field is an integer field called id as primary key

·         table name can be no longer than 28 characters

·         Field names can be no longer than 30 characters

·         table character set (collation) should be a UTF8 character set in order to support multi-language text

·         The first part of any plugin table name should contain the plugin name itself

·         Indices should be created for fields that are commonly used as search queries, and combination indices should be created when needed

 

 

创建和管理你的 plugin db table 是使用你的 plugin 的 “ db ”子目录里的 install.xml and upgrade.php 。

 

Install.xml

install.xml file contains XML that defines the tables required for your new plugin. 有了 install.xml ， 当你的 plugin 安装到 moodle 时， moodle 就会 detect 它来 create db table 。

 

下面是一个 install.xml example

<?xml version="1.0" encoding="UTF-8" ?>

<XMLDB PATH="mod/label/db" VERSION="20060905" COMMENT="XMLDB file for Moodle mod/label"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="../../../lib/xmldb/xmldb.xsd"

>

< TABLES >

< TABLE NAME="label" COMMENT="Defines labels">

< FIELDS >

< FIELD NAME="id" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" SEQUENCE="true" ENUM="false" NEXT="course"/>

<FIELD NAME="course" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" ENUM="false" PREVIOUS="id" NEXT="name"/>

<FIELD NAME="name" TYPE="char" LENGTH="255" NOTNULL="true" SEQUENCE="false" ENUM="false" PREVIOUS="course" NEXT="content"/>

<FIELD NAME="content" TYPE="text" LENGTH="small" NOTNULL="true" SEQUENCE="false" ENUM="false" PREVIOUS="name" NEXT="timemodified"/>

<FIELD NAME="timemodified" TYPE="int" LENGTH="10" NOTNULL="true" UNSIGNED="true" DEFAULT="0" SEQUENCE="false" ENUM="false" PREVIOUS="content"/>

</FIELDS>

< KEYS >

< KEY NAME="primary" TYPE="primary" FIELDS="id" />

</KEYS>

< INDEXES >

< INDEX NAME="course" UNIQUE="false" FIELDS="course"/>

</INDEXES>

</TABLE>

</TABLES>

<STATEMENTS>

<STATEMENT NAME="insert log_display" TYPE="insert" TABLE="log_display" COMMENT="Initial insert of records on table log_display">

<SENTENCES>

<SENTENCE TEXT="(module, action, mtable, field) VALUES ('label', 'add', 'label', 'name')"/>

<SENTENCE TEXT="(module, action, mtable, field) VALUES ('label', 'update', 'label', 'name')"/>

</SENTENCES>

</STATEMENT>

</STATEMENTS>

</XMLDB>

 

 

Upgrade.php

如果你想 update 你的 plugin的 db table，就可以使用 upgrade.php。当你把 new version的 plugin安装到 moodle时， moodle detect到是 new version，就会运行 upgrade.php文件来 add new tables/add new fields to existing table/change field format/create new index。

 

Upgrade.php必须包含一个 upgrade function，其格式为： xmldb_[module name]_upgrade, 该函数以 oldversion 作为参数，它是指在安装当前最新 version 的 plugin 之前的旧 version 的版本号

 

注意：不同类型的 plugin的版本号储存在不同的 moodle db table里：

·         Module version numbers are in the module table

·         Block version numbers are in the block table

·         All others (assignment types, question types, and so on) are in the config table

 

Example

<?php //$Id: upgrade.php,v 1.1.2.14 2009/07/09 18:04:27 mchurch Exp $

function xmldb_questionnaire_upgrade($oldversion=0) {

global $CFG;

$result = true;

if ($oldversion < 2008060402) {

   //定义 a table and a field and set field properties

$table = new XMLDBTable('questionnaire_question_type' );

$field = new XMLDBField('response_table' );

$field-> setAttributes (XMLDB_TYPE_CHAR, '32', null, XMLDB_NOTNULL, null, null, null, null, 'has_choices');

$field-> setNotnull (false);

//由于该 table存在，所以下列语句是修改 field的属性

$result &= change_field_notnull($table, $field);

}

 

if ($oldversion < 2008060403) {

   //定义 a table and a index and set index properties

$table = new XMLDBTable('questionnaire_resp_multiple');

$index = new XMLDBIndex('response_question');

$index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('response_id', 'question_id', 'choice_id'));

//为 table添加一个 index named “ response_question ”

$result = $result && add_index ($table, $index);

}

return $result;

}

?>

 

上面的代码中：

·         XMLDBTable, XMLDBField and XMLDBIndex 都是来自 XMLDB libraries (contained in /lib/xmldb/* ) 。 XMLDB libraries 包含了各种 classes 用来定义 all aspects of the databases and data tables 。如 table, field, index 等

 

·         change_field_notnull and add_index 函数是来自 DDLIB (DD stands for Data Definition) library (contained in / lib/ddllib.php ) 。 DDLIB 包含了以 XMLDB 定义的 class objects 作为参数来对 db table 进行修改的函数

 

注意：对于哪些不属于任何 plugin的 db tables，如果我们要修改它们应该怎么办？

那就要 在 moodle_home下的 local目录里创建一个 db目录 ，然后在通过和 plugin db talbe类似的方法来执行。

 

不过有一个很大的 不同之处在于：不需要 install.xml ，只需要 upgrade.php。所有的 db操作都写在 upgrade.php里。同时， 在 ” local ” 目录下，还要有一个 version.php file 来定义 the version information for the upgrade scripts 。例如：

<?php

$local_version = 2009081600 ;

?>

 

在 local/db/upgrade.php里必须定义 xmldb_local_upgrade函数

 

 

 

XMLDB Editor Tool

这是一个很有用的工具，它在 Administration Block > Miscellaneous > XMLDB Editor。 它用来 create and manage 用在 install.xml 里的 XML code

 

你可以用它来：

·         create new xmldb files and edit existing ones

·         create the XML from already-existing data tables in your database

·         check if there is anything wrong with your XML (if you happened to create it by hand)

·         你还可以 通过已存在的 xml file 来 generate the SQL that you need to execute for the database type that you are using. This is handy if you are developing the data tables and want to create these quickly.

·         generate PHP code suitable for the upgrade.php file by using the XMLDB and DDLIB functions

 

 

 

 

Moodle Database API

这些 api在 /lib/dmllib.php file 里

 

 

Read 操作

有３类 read操作，每类有３个函数

 

第一类： Query by parameters

·         get_field($table, $return, $field1, $value1, $field2='', $value2='', $field3='', $value3='')

该函数只返回一个 single field，它是第２个参数所指向的 field的值。 如果该 field没找到，就 return false 。该函数要求是 find only one match ，如果有多个 match ，是只返回第１个还是出错？

 

 

该函数第１个参数是 table name，第２个参数是要返回的值所属的 field，再后面的参数就是 key/value成对出现 (至少要有一对 )，这些 key/value会转化成 where语句里的 key=value，而且各个 key＝ value之间的连接是“ and” 。

 

 

Example:

$username = get_field('user', 'username', 'firstname', 'bob', 'lastname', 'roberts');

返回 user table里 firstname=bob and lastname=roberts的 record的 username field的值。

 

·         get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='', $fields='*')

该函数只返回一个 single record。 如果没有 record match，就 return false 。 如果有多于一个 match ，就会报错。

 

该函数第１个参数是 table name，紧接着的参数就是 key/value成对出现 (至少要有一对 )，这些 key/value会转化成 where语句里的 key=value，而且各个 key＝ value之间的连接是“ and”，最后一个参数是指定要返回的 field，如果设置最后一个参数的值为 ” * ” ，或不设置这个参数，那么就会返回该 record的所有 fields 。

 

Example:

$userrec = get_record ('user', 'fname', 'bob', 'lname', 'roberts', 'username,fname,lname' );

if ($userrec) {

print_r($userrec);

}

返回 user table里 fname=bob and lname=roberts的 record的 username, fname, lname field的值。

 

返回的结果类似于：

stdClass Object

(

[username] => bobroberts

[fname] => Bob

[lname] => Roberts

)

 

·         get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='')

该函数返回 an array of data records as objects 。该 array 的 indexed by 第 5 个参数 $field 里的第 1 个 field 。如果 $field 没有设置或者设置为 ”*” ，那么则 indexed by “ id ” field 。 ( “ indexed by ”指以其作为 array element 的 key ，例如 indexed by “id” field ，表示该 record array 是以“ id ”作为 key ， record object 作为 value ，见下面的例子 )

 

如果 no records are found ， return false.

 

第 2 、 3 个参数是一对 key/value ，用于 where 。如果没有设置这对 key/value （即第 2 、 3 个参数为 empty string ），则返回该 table 的所有 records 。

 

$sort 参数是设置 a field by which the returned array will be ordered 。

$fields参数如果没有设置则返回所有的 fields，如果要设置则 field之间用逗号隔开

$limitfrom参数是设置 starting record

$limitnum参数是设置 the maximum number of records

 

Example:

$userrecs = get_records('user', 'firstname', 'bob', 'lastname', 'id,firstname,lastname');

返回 user table里 firstname=bob的 sort by lastname的 record array（只有 id, firstname and lastname fields），并 indexed by “ id” field。

 

返回的结果类似于：

Array

(

[ 34 ] => stdClass Object

(

[id] => 34

[firstname] => Bob

[lastname] => Roberts

)

[ 123 ] => stdClass Object

(

[id] => 123

[firstname] => Bob

[lastname] => Williams

)

)

可见上面的返回结果的 array element是以 id为 array element的 key，以 record object为 array element的 value。

 

第二类： Query by “ where ” string

这一类和上一类非常类似， 唯一的区别在于它只需要用一个参数来设置 where part of the query, 使 query 更为灵活 。 上一类的函数只能使用 ”fieldname=fieldvalue” 作为 where 的语句，而且各个条件之间设死为“ and ”。而本类的函数则可以使用“ or ”或其他连接符，同时不局限于 ”fieldname=fieldvalue” ，例如 ”fieldname>=fieldvalue” .

 

本类的三个函数（具体参考上类）

l       get_field_select($table, $return, $select)

l       get_record_select($table, $select='', $fields='*')

l       get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum=' ')

 

example

$select = " (firstname = 'bob' OR firstname = 'robert') AND lastname = 'roberts' ";

$userrecs = get_records_select('user', $select) ;

 

 

第三类： Query by full SQL

该类和之前的类也很类似，但更为灵活，直接使用 sql语句来获取 record。

 

本类的三个函数（具体参考上类）

l       get_field_sql($sql)

l       get_record_sql($sql)

l       get_records_sql($sql, $limitfrom='', $limitnum='')

 

example

$sql = "SELECT u.id, u.firstname, u.lastname,

MAX(ul.timeaccess) as lastaccess

FROM {$CFG->prefix}user u

INNER JOIN {$CFG->prefix}user_lastaccess ul

ON ul.userid = u.id

WHERE u.username = 'broberts'"

$userrec = get_record_sql($sql);

 

 

 

Write 操作

有 2类 write操作的 API： by field, by record。

 

常用的有下列 4个函数

l       set_field

该函数用于 set a specific field in a specific record 。格式为：

set_field($table, $newfield, $newvalue, $field1, $value1, $field2='', $value2='', $field3='', $value3='')

详细的参数和上面的函数类似

Example

set_field('user', 'firstname', 'Robert', 'username', 'broberts')

上述代码表示设置 user table的 username = broberts 的 record的 firstname field的值为 robert

 

l       set_field_select

和 set_field类似，但 where语句更为灵活。格式为：

set_field_select($table, $newfield, $newvalue, $select, $localcall = false)

 

l       insert_record

用于 create a new record 。 By default 返回值是 new id 。

格式为：

insert_record($table, $dataobject, $returnid=true, $primarykey='id')

参数 $dataobject是用一个 object来包含 new record info，见下例。

 

example

$newrec = new Object();

$newrec->firstname = 'Bill';

$newrec->lastname = 'Williams';

$newrec->username = 'bwilliams';

$newrec->password = hash_internal_user_password('secret');

$newrec->email = 'bwilliams@email.com';

$newid = insert_record('user', $newrec);

 

l       update_record

update record。格式为：

update_record($table, $dataobject)

 

注意：和 insert_record函数相比，其 $dataobject的 object必须包含 ” id ” value，这是 insert_record函数没有包含的！

 

 

当你开发 moodle database 操作的代码时，下列的规范强烈建议应该遵循：

l       Take only what you need

即尽量只获取你需要的东东。比如，如果你只需要某个 field，那么 call get_field function。 如果你只需要 single record， then call get_record function。还有在参数 $fields里只设置你要获取的 fields。另外如果你要获取多于一个的 record，那么必须 fields list必须以 id作为第一个 field。

 

l       Limiting your returned data

合理使用 $limitfrom and $limitnum参数，可以实现分页和减轻 server的负担不要一次性 load db data。

 

Example: 每次只获取 100个 record

$limitfrom = 0;

$limitnum = 100;

while ($records = get_records('user', 'deleted', 'n', 'id', 'id,firstname,lastname', $limitfrom, $limitnum) {

foreach ($records as $uid => $userrec) {

/// Do some processing ///

}

$limitfrom += $limitnum;

}

 

l       Using recordsets

如果你处理 large recordsets ，使用 get_recordset函数比 get_records函数更为方便 。

 

get_recordset函数 的格式和 get_records函数的 一样。

get_recordset($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='')

 

不过 get_recordset函数需要手动控制 memory use，即你要写代码来 close recordset

 

Example

//Creating a recordset and saving it in a variable

$rs = get_recordset ('course', '', '', '', 'id');

// Fetching the next record from the set

while ( $course = rs_fetch_next_record($rs) ) {

$subcontext = get_context_instance(CONTEXT_COURSE, $course->id );

forum_add_user_default_subscriptions($userid, $subcontext);

}

// Closing the recordset when completed

rs_close($rs);

 

 

l       Optimizing carefully with joins

如果要获取的 data来自多个 table，由于没有 api来获取多个 table的 data，所以要使用 join sql语句。

 

l       Testing on more than one database engine

要在多个数据库上进行测试。