Actions

Developer Area/Database conventions: Difference between revisions

From Mahara Wiki

< Developer Area
No edit summary
No edit summary
Line 1: Line 1:
* each plugin type gets {$plugintype}_config, cron, installed and event_subscription.
* Each plugin ''type'' gets {$plugintype}_config, {$plugintype}_cron, {$plugintype}_installed and {$plugintype}_event_subscription.
* any tables individual plugins want to install must be prefixed by $plugintype_$pluginname_ (example: blog posts would be artefact_blog_post)
* Any tables individual plugins want to install must be prefixed by $plugintype_$pluginname_ (example: blog posts would be artefact_blog_post)
* any timestamp fields must be called ctime, mtime, atime (creation, modification, access)
* Standard timestamp fields must be called ctime, mtime, atime (creation, modification, access)
* when getting data from the database, it's best to format the timestamps as unix timestamps, using the <span style="white-space: nowrap">db_format_tsfield</span> function.
* When getting data from the database, it's best to format the timestamps as unix timestamps, using the <span style="white-space: nowrap">db_format_tsfield</span> function.
* use of integer-sequences for primary keys is only for when there is no clear individual field primary key and a primary key is needed.
* In the case of having a unique name and a display name, the fields are to be called name and displayname.
* In the case of having a unique name and a display name, the fields are to be called name and displayname.
* For tables that just join tables together, foreign key each field to the appropriate table, and create a multi-column primary key where appropriate. Eg usr_group_member joins usr and usr_group, so has two foreign keys and one two-column primary key
* As we're not following the "Every Table Must Have an 'id' Field" school of thought, foreign key columns shouldn't be called (eg) userid, but given an appropriate name that reflects their purpose (eg 'owner')
* Database fields, like variable names, don't have underscores in them.
* Database fields, like variable names, don't have underscores in them.
 
* Every new table should have an auto-incrementing integer "id" field as its primary key (i.e. a [http://en.wikipedia.org/wiki/Surrogate_key|surrogate key]
<div id="section_1">
** Mahara didn't used to follow this practice, so many older tables don't have an "id" column.
** Please use the "id" column as the foreign key column in other tables.


===Database upgrades===
===Database upgrades===
Line 20: Line 18:


In general, upgrade code should only use low-level functions from dml and xmldb rather than functions defined elsewhere in Mahara.  It's tempting to write an installation function in lib/upgrade.php and then use the same function in the upgrade script, but we've found that it should be avoided.  Code in the upgrade script should be pretty-much 'frozen' so that it uses the db schema as it was when the upgrade was written.  Mahara library functions get rewritten, and can refer, for example, to database columns that haven't always existed -- then an early upgrade that calls a rewritten function will fail because the db column is not added until some later yet-to-be-executed upgrade.
In general, upgrade code should only use low-level functions from dml and xmldb rather than functions defined elsewhere in Mahara.  It's tempting to write an installation function in lib/upgrade.php and then use the same function in the upgrade script, but we've found that it should be avoided.  Code in the upgrade script should be pretty-much 'frozen' so that it uses the db schema as it was when the upgrade was written.  Mahara library functions get rewritten, and can refer, for example, to database columns that haven't always existed -- then an early upgrade that calls a rewritten function will fail because the db column is not added until some later yet-to-be-executed upgrade.
</div>

Revision as of 12:35, 22 Ocak 2014

  • Each plugin type gets {$plugintype}_config, {$plugintype}_cron, {$plugintype}_installed and {$plugintype}_event_subscription.
  • Any tables individual plugins want to install must be prefixed by $plugintype_$pluginname_ (example: blog posts would be artefact_blog_post)
  • Standard timestamp fields must be called ctime, mtime, atime (creation, modification, access)
  • When getting data from the database, it's best to format the timestamps as unix timestamps, using the db_format_tsfield function.
  • In the case of having a unique name and a display name, the fields are to be called name and displayname.
  • Database fields, like variable names, don't have underscores in them.
  • Every new table should have an auto-incrementing integer "id" field as its primary key (i.e. a key
    • Mahara didn't used to follow this practice, so many older tables don't have an "id" column.
    • Please use the "id" column as the foreign key column in other tables.

Database upgrades

Core upgrades: On every page load (!), Mahara compares the version number of the code (stored in htdocs/lib/version.php) against the version number in the database (stored in the config table in the 'version' row).  If the db version is less than the code version, an upgrade is triggered, and the code in htdocs/lib/db/upgrade.php is run.  Each upgrade sits inside a 'if ($oldversion < ...) { ... }' block, so that only upgrades greater than the current db version ($oldversion) are run.

Errors in an upgrade will usually cause an SQLException to be thrown, and the upgrade will stop with a nasty error message on the screen.  The upgrade is wrapped in a transcation, so with a Postgres db, an upgrade failure should rollback to the state before the first upgrade.  Upgrades which fail while making changes to files in dataroot or changes to a MySQL db can leave your data in an inconsistent half-upgraded state.

Plugin upgrades work in a similar way to core upgrades.  Plugins are usually not upgraded until after the core is upgraded, but this behaviour is sometimes overridden when a core upgrade manually triggers a plugin upgrade.

In general, upgrade code should only use low-level functions from dml and xmldb rather than functions defined elsewhere in Mahara.  It's tempting to write an installation function in lib/upgrade.php and then use the same function in the upgrade script, but we've found that it should be avoided.  Code in the upgrade script should be pretty-much 'frozen' so that it uses the db schema as it was when the upgrade was written.  Mahara library functions get rewritten, and can refer, for example, to database columns that haven't always existed -- then an early upgrade that calls a rewritten function will fail because the db column is not added until some later yet-to-be-executed upgrade.

Retrieved from ‘https://wiki.mahara.org/index.php?title=Developer_Area/Database_conventions&oldid=5082’