Note:

If you want to create a new page for developers, you should create it on the Moodle Developer Resource site.

Database: Difference between revisions

From MoodleDocs
(MDL-76459 - expand database identifiers)
 
(16 intermediate revisions by 11 users not shown)
Line 3: Line 3:
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 definition (XMLDB) editor]].
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 definition (XMLDB) editor]].


# Every table must have an auto-incrementing id field (INT10) as primary index. (see [[IdColumnReasons]])
There are [[Moodle_versions#How_to_increment_version_numbers_in_core|rules for how what numbers to use in version.php files in Moodle core]].
 
# Every table must have an auto-incrementing id field (INT10) as primary key. (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:
# 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
#* id - as described above
Line 9: Line 11:
#* name - the full name of each instance of the module
#* 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).
# 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 [[Database reserved words|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.
# Core tables in general should have single word names non-pluralised, and double word names pluralised only for the last word e.g. 'course', 'course_categories'. The only exceptions should be for reserved words e.g. 'files'. Some tables don't fit this pattern right now for historical reasons, but this will eventually be changed.
# Table and column names should avoid using [[XMLDB_reserved_words|reserved words in any database]]. Please check them before creation. Table names may be up to 53 characters long (only 28 before Moodle 4.3), and Column names up to 63 characters long (only 30 before Moodle 4.3).  
# Column names should be always lowercase, simple and short, following the same rules as for variable names.
# 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)
# 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)
Line 15: Line 18:
# Most tables should have a timemodified field (INT10) which is updated with a current timestamp obtained with the PHP time() function.
# 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)
# 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.
# 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. Note that, since Moodle 4.3 the max allowed prefix length is 10 characters.
# In order to guarantee [[XMLDB problems#Table and column aliases - the AS keyword|cross-db compatibility]] follow these simple rules about the use of the '''AS''' keyword (only if you need table/colum aliases, of course):
# <span id="as_keyword"></span>In order to guarantee [[XMLDB problems#Table and column aliases - the AS keyword|cross-db compatibility]] follow these simple rules about the use of the '''AS''' keyword (only if you need table/column aliases, of course):
#* '''Don't use''' the '''AS''' keyword for '''table aliases'''.
#* '''Don't use''' the '''AS''' keyword for '''table aliases'''.
#* '''Don't use''' '''table aliases''' at all for DELETE statments (Mysql doesn't like it).
#* '''Do use''' the '''AS''' keyword for '''column 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.  
# '''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.  
Line 24: Line 28:
# '''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.
# '''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. ([http://moodle.org/mod/forum/discuss.php?d=80629 This thread explains].)
# 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. ([http://moodle.org/mod/forum/discuss.php?d=80629 This thread explains].)
# Never use double quotes for variable values in SQL queries (e.g. <strike>'SELECT * FROM {user} WHERE username = "someuser"'</strike>). While this is OK for MySQL, which does not respect ANSI standard for databases, Postgresql is treating double quoted variable this as system identifier (e.g. field name).
# Moodle does not support database "views", don't use them. See Petr's comment on [http://tracker.moodle.org/browse/MDL-25407 Task 25407] for more info about this.
[[Category:Coding guidelines|Database]]
[[Category:DB|Database]]
[[Category:XMLDB|Database]]


[[Category:Database]][[Category:DB]][[Category:XMLDB]]
[[es:dev/BasedeDatos]]

Latest revision as of 12:37, 10 September 2023

Database structures

To help you create tables that meet these guidelines, we recommend you use the built in database definition (XMLDB) editor.

There are rules for how what numbers to use in version.php files in Moodle core.

  1. Every table must have an auto-incrementing id field (INT10) as primary key. (see IdColumnReasons)
  2. 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
  3. Other tables associated with a module that contain information about 'things' should be named widget_things (note the plural).
  4. Core tables in general should have single word names non-pluralised, and double word names pluralised only for the last word e.g. 'course', 'course_categories'. The only exceptions should be for reserved words e.g. 'files'. Some tables don't fit this pattern right now for historical reasons, but this will eventually be changed.
  5. Table and column names should avoid using reserved words in any database. Please check them before creation. Table names may be up to 53 characters long (only 28 before Moodle 4.3), and Column names up to 63 characters long (only 30 before Moodle 4.3).
  6. Column names should be always lowercase, simple and short, following the same rules as for variable names.
  7. 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)
  8. Boolean fields should be implemented as small integer fields (eg INT4) containing 0 or 1, to allow for later expansion of values if necessary.
  9. Most tables should have a timemodified field (INT10) which is updated with a current timestamp obtained with the PHP time() function.
  10. Always define a default value for each field (and make it sensible)
  11. 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. Note that, since Moodle 4.3 the max allowed prefix length is 10 characters.
  12. In order to guarantee cross-db compatibility follow these simple rules about the use of the AS keyword (only if you need table/column aliases, of course):
    • Don't use the AS keyword for table aliases.
    • Don't use table aliases at all for DELETE statments (Mysql doesn't like it).
    • Do use the AS keyword for column aliases.
  13. 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.
  14. 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.
  15. 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.
  16. 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.
  17. 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.)
  18. Never use double quotes for variable values in SQL queries (e.g. 'SELECT * FROM {user} WHERE username = "someuser"'). While this is OK for MySQL, which does not respect ANSI standard for databases, Postgresql is treating double quoted variable this as system identifier (e.g. field name).
  19. Moodle does not support database "views", don't use them. See Petr's comment on Task 25407 for more info about this.