Loading...
 
How your data is modified when Tiki is updated Type of page »  Developers documentation

Database Schema Upgrade

Introduction

Database schema & PHP update files require a patch file being placed in installer/schema/. This file handles incremental updates from SVN or updates. The db/tiki.sql file also needs to be updated with the same change; it handles all new installs. The database schema from an update and a new install must be the same. The tiki.sql file defines the bulk of the database. All patch files can be verified before committing by running php console.php -n database:update A list of successful patches executed will be given in the terminal. Its worth noting that the installer-console stores the name of each patch in the tiki_schema table so for testing you need to remove that row to get it to run a second time. (usually the last one installed obviously).

A upgrade can be handled in three different ways:


For shell scripts, the PHP interpreter must be available from command line.

Create a New Database Patch

Each file is stored in installer/schema and the naming convention is YYYYMMDD_patch_description_tiki.sql. The SQL file contains a list of semi-colon separated SQL statements.

The db/tiki.sql file still has to be updated to reflect the changes for new installations if the patch is to be made on standard Tiki distribution.

Example:

installer/schema/20080922_new_structures_tiki.sql
DROP TABLE tiki_structures;
CREATE TABLE tiki_structures (
   -- ...
) ENGINE=MyISAM;

installer/schema/20080922_new_structures_tiki.php
function pre_20080922_new_structures_tiki($installer) {
   // Store current structures
}

function post_20080922_new_structures_tiki($installer) {
   // Restore current structures in new model
}

Create New PHP Patch

A PHP patch file may also be defined. The file naming is very similar to that of a SQL update, YYYYMMDD_patch_description_tiki.php. One may simply swap out the .sql for .php. The file can define a pre_ and post_ function (or both) to be executed before and after the patch. These functions can be used to perform operations to be made during the upgrade that would otherwise be too complicated to do with SQL, or for anything more suited to PHP. A function named upgrade_YYYYMMDD_patch_description_tiki($installer) will be automatically executed. $installer may be used to run SQL queries e.g. $installer->query($query, array()); etc.

Examples of patches

How it works


For updates:

  1. The script scans for patches available in the folder
  2. Previously installed patches are pulled from the database
  3. New patches are ordered for installation
  4. For each patch,
    1. Pre-function is executed if available
    2. SQL statements are run
    3. Post function is executed if available
    4. Patch installation is saved
  5. Global scripts are applied


For new installs:

  1. Runs the usual tiki.sql-derived file to create the base install
  2. Mark all patches with the _tiki suffix as installed
  3. Performs update (as above) to apply other patches

Third Party Modifications and Customization


Effectively, any third party modification can add database patches simply by adding the patch file in the patch folder. If the patch does not have the _tiki suffix, it will be applied to new installations. Custom deployments no longer need to maintain the tiki.sql file and will not risk conflicts when updating from the source.

Global scripts


The process also defines global scripts. These scripts are stored in installer/script/ and are PHP scripts. Each file in that folder is executed after the update. The files can be used to perform various operations, like clearing cache or any operation that should be executed after a normal update.

Schema Update History

  • Before Tiki3, database schema patches were handled in a single file. Separating them allows for branch merging and altogether smoother upgrade process.
  • Tiki.sql is taken from a shell script and modified to work with various different databases: SQLite, PostgreSQL so after modifying tiki.sql, make sure you run convertsqls.sh (while you are in its directory, db/convertscripts), and commit back all the affected files. Starting in Tiki5, no need to use convertscripts as Database independence has been dropped.
  • Patches and custom coding, in the past, always had to go trough this path, now they do not, which is much better for core development. Also, it allows any custom changes you make to the database to survive through an upgrade.

Alias

Keywords

The following is a list of keywords that should serve as hubs for navigation within the Tiki development and should correspond to documentation keywords.

Each feature in Tiki has a wiki page which regroups all the bugs, requests for enhancements, etc. It is somewhat a form of wiki-based project management. You can also express your interest in a feature by adding it to your profile. You can also try out the Dynamic filter.

Accessibility (WAI & 508)
Accounting 7.x
Administration
Ajax 2.x
Articles & Submissions
Backlinks
Banner
Batch 6.x
BigBlueButton audio/video/chat/screensharing (5.x)
Blog
Bookmark
Browser Compatibility
Calendar
Category
Chat
Comment
Communication Center
Consistency
Contacts Address book
Contact us
Content template
Contribution 2.x
Cookie
Copyright
Credits 6.x
Custom Home (and Group Home Page)
Database MySQL - MyISAM
Database MySQL - InnoDB
Date and Time
Debugger Console
Directory (of hyperlinks)
Documentation link from Tiki to doc.tiki.org (Help System)
Docs 8.x
DogFood
Draw 7.x
Dynamic Content
Preferences
Dynamic Variable
External Authentication
FAQ
Featured links
Feeds (RSS)
File Gallery
Forum
Friendship Network (Community)
Group
Help
History
Hotword
HTML Page
i18n (Multilingual, l10n, Babelfish)
Image Gallery
Import-Export
Install
Integrator
Interoperability
Inter-User Messages
InterTiki
jQuery
Kaltura video management
Karma
Live Support
Logs (system & action)
Lost edit protection
Mail-in
Map
Menu
Meta Tag
Missing features
Visual Mapping 3.x
Mobile Tiki and Voice Tiki
Mods
Modules
MultiTiki
MyTiki
Newsletter
Notepad
OS independence (Non-Linux, Windows/IIS, Mac, BSD)
Organic Groups (Self-managed Teams)
Payment 5.x
Performance Speed / Load / Compression / Cache
Permission
Poll
Profiles
Quiz
Rating
Realname
Report
Revision Approval
Score
Search engine optimization (SEO)
Search
Security
Semantic links 3.x
Share
Shopping Cart 5.x
Shoutbox
Site Identity
Slideshow
Smarty Template
Social Networking
Spam protection (Anti-bot CATPCHA)
Spellcheck
Spreadsheet
Staging and Approval
Stats
Survey
Syntax Highlighter (Codemirror)
Tablesorter
Tags 2.x
Task
Tell a Friend, alert + Social Bookmarking
Terms and Conditions
Theme
TikiTests 2.x
Timesheet
Token Access
Toolbar (Quicktags)
Tours
Trackers
TRIM
User Administration
User Files
User Menu
Watch
WebHelp
Webmail and Groupmail
WebServices 3.x
Wiki 3D
Wiki History, page rename, etc
Wiki plugins extends basic syntax
Wiki syntax text area, parser, etc
Wiki structure (book and table of content)
Workspace and perspectives 4.x
WYSIWTSN 4.x
WYSIWYCA
WYSIWYG 2.x
XMLRPC




Useful Tools