Loading...
 

Database Schema Upgrade

Introduction

The Tiki code base contains database schema update scripts since before Tiki 3.0 (2009). So if you point an old database to a new Tiki, Tiki will want to upgrade the database schema. There is no supported way to downgrade a Tiki site. We can of course roll back the code, but we can't revert the database schema / structure. So you never do this on a production database without testing first, and a backup.


Database schema & PHP update files require a patch file being placed in installer/schema/. This file handles incremental updates from Git 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). While it's possible to backport database patches, it is not recommended as per Backport guidelines.

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

Keep in mind that this must work on multiple versions of PHP, MySQL, and MariaDB. For example: MySQL and older versions of MariaDB do not support IF EXISTS in ALTER TABLE, so PHP is needed: https://gitlab.com/tikiwiki/tiki/-/merge_requests/1932/diffs

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 convention is checked as part of the release process via https://gitlab.com/tikiwiki/tiki/-/blob/master/doc/devtools/check_schema_naming_convention.php to release script

Example:

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

installer/schema/20080922_new_structures_tiki.php
Copy to clipboard
function pre_20080922_new_structures_tiki($installer) { // Store current structures } function post_20080922_new_structures_tiki($installer) { // Restore current structures in new model }

Update tiki.sql file

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

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

MyISAM vs InnoDB

Historically, Tiki used MyISAM, but nowadays InnoDB is the default for fresh installs. Existing Tikis can opt to run https://gitlab.com/tikiwiki/tiki/-/blob/master/db/tiki_convert_myisam_to_innodb.sql at any time. There is a test script in the CI to avoid inconsistency: https://gitlab.com/tikiwiki/tiki/-/blob/master/.gitlab-ci.yml#L430
The Unified Index with MySQL Full Text Search remains with MyISAM.

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
Administration
Ajax
Articles & Submissions
Backlinks
Banner
Batch
BigBlueButton audio/video/chat/screensharing
Blog
Bookmark
Browser Compatibility
Calendar
Category
Chat
Comment
Communication Center
Consistency
Contacts Address book
Contact us
Content template
Contribution
Cookie
Copyright
Credits
Custom Home (and Group Home Page)
Database MySQL - MyISAM
Database MySQL - InnoDB
Date and Time
Debugger Console
Diagram
Directory (of hyperlinks)
Documentation link from Tiki to doc.tiki.org (Help System)
Docs
DogFood
Draw -superseded by Diagram
Dynamic Content
Preferences
Dynamic Variable
External Authentication
FAQ
Featured links
Feeds (RSS)
File Gallery
Forum
Friendship Network (Community)
Gantt
Group
Groupmail
Help
History
Hotword
HTML Page
i18n (Multilingual, l10n, Babelfish)
Image Gallery
Import-Export
Install
Integrator
Interoperability
Inter-User Messages
InterTiki
jQuery
Kaltura video management
Kanban
Karma
Live Support
Logs (system & action)
Lost edit protection
Mail-in
Map
Menu
Meta Tag
Missing features
Visual Mapping
Mobile
Mods
Modules
MultiTiki
MyTiki
Newsletter
Notepad
OS independence (Non-Linux, Windows/IIS, Mac, BSD)
Organic Groups (Self-managed Teams)
Packages
Payment
PDF
Performance Speed / Load / Compression / Cache
Permission
Poll
Profiles
Quiz
Rating
Realname
Report
Revision Approval
Scheduler
Score
Search engine optimization (SEO)
Search
Security
Semantic links
Share
Shopping Cart
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
Task
Tell a Friend
Terms and Conditions
Theme
TikiTests
Federated Timesheets
Token Access
Toolbar (Quicktags)
Tours
Trackers
TRIM
User Administration
User Files
User Menu
Watch
Webmail and Groupmail
WebServices
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
WYSIWTSN
WYSIWYCA
WYSIWYG
XMLRPC
XMPP




Useful Tools