Revision as of 06:54, 30 April 2009 by Martin Dougiamas (New page: ==Database structures== To help you create tables that meet these guidelines, we recommend you use the built in [[XMLDB_defining_an_XML_structure#The_XMLDB_editor|database def...)
To help you create tables that meet these guidelines, we recommend you use the built in database definition (XMLDB) editor.
- Every table must have an auto-incrementing id field (INT10) as primary index. (see IdColumnReasons)
- The main table containing instances of each module must have the same name as the module (eg widget) and contain the following minimum fields:
- id - as described above
- course - the id of the course that each instance belongs to
- name - the full name of each instance of the module
- Other tables associated with a module that contain information about 'things' should be named widget_things (note the plural).
- Table and column names should avoid using reserved words in any database. Please check them before creation. Table names may be up to 28 characters long, and Column names up to 30 characters.
- Column names should be always lowercase, simple and short, following the same rules as for variable names.
- Where possible, columns that contain a reference to the id field of another table (eg widget) should be called widgetid. (Note that this convention is newish and not followed in some older tables)
- Boolean fields should be implemented as small integer fields (eg INT4) containing 0 or 1, to allow for later expansion of values if necessary.
- Most tables should have a timemodified field (INT10) which is updated with a current timestamp obtained with the PHP time() function.
- Always define a default value for each field (and make it sensible)
- Each table name should start with the database prefix ($CFG->prefix). In a lot of cases, this is taken care of for you automatically. Also, under Postgres, the name of every index must start with the prefix too.
- In order to guarantee cross-db compatibility follow these simple rules about the use of the AS keyword (only if you need table/colum aliases, of course):
- Don't use the AS keyword for table aliases.
- Do use the AS keyword for column aliases.
- Never create UNIQUE KEYs (constraints) at all. Instead use UNIQUE INDEXes. In the future, if we decide to add referential integrity to Moodle and we need UNIQUE KEYs they will be used, but not now. Please note that the XMLDB editor allows you to specify both XMLDB-only UNIQUE and FOREIGN constraints (and that's good, in order to have the XML well defined) but only underlying INDEXes will be generated.
- Those XMLDB-only UNIQUE KEYs (read previous point) only must be defined if such field/fields are going to be the target for some (XMLDB-only too) FOREIGN KEY. Else, create them as simple UNIQUE INDEXes.
- Tables associated with one block must follow this convention with their names: $CFG->prefix + "block_" + name_of_the_block + anything_else. For example, assuming that $CFG->prefix is 'mdl_', all the tables for the block "rss_client" must start by 'mdl_block_rss_client' (being possible to add more words at the end, i.e. 'mdl_block_rss_client_anothertable'...). This rule will be 100% enforced with Moodle 2.0, giving time to developers until then. See Task 6786 for more info about this.
- Never make database changes in the STABLE branches. If we did, then users upgrading from one stable version to the next would have duplicate changes occurring, which may cause serious errors.
- When refering to integer variable in SQL queries, do not surround the value in quotes. For example, get_records_select('question', "category=$catid") is right. get_records_select('question', "category='$catid'") is wrong. It hides bugs where $catid is undefined. (This thread explains.)