Loading...
 

 Note

This page is to document "what Tiki should do". For feature documentation (what Tiki does), please see corresponding page on doc site

Database independence

At lot of effort was put into Tiki since 2003 to add and maintain database independence (very much like we have Browser Compatibility and Operating System independence). However, despite the efforts, the support was never really of acceptable quality, leading to disappointments. Also, this was preventing us from taking advantage of MySQL's more advanced features. Several "last chances" were given, and as PostgreSQL was not working on 2009-10-08, the goal of database independence was dropped for the foreseeable future (so we support MySQL and MariaDB) only. Tiki has a huge code base and it would take a massive effort to get it to work and no one in the community has the combination of the will and the capacity to make it happen.


The remainder of this page is kept for posterity.


Reference:
http://article.gmane.org/gmane.comp.cms.tiki.devel/13906

Why Database independence?


Tiki uses a database abstraction layer so it can be used with many databases (PostgreSQL, SQLite) in addition to MYSQL. However, anything but MySQL is no longer officially supported.
We're working on fixing remaining issues of bringing back the alternatives and although default installations are partly working, we can not yet guarantee a stable or bug-free usage of Tiki with databases other than MySQL.
You can expect other databases for later versions though.

Things to note and discuss

  • MySQL search vs DB independent search (we're using fulltext-indices which not all DBS support)
  • Some code is using MySQL BLOB, whereas PostgreSQLs equivalent is BYTEA.


Now (old statement here) that we have a more flexible/powerful way to handle DB upgrades, we can use simpler SQL commands and put more logic in PHP. This can make DB independence simpler than before.

I will do something about it

If you want to help, please indicate your name below and which DB you would like to work on.

Name Database Comment
Marc Laporte in general no coding, but I can supply a test/dev server (ex.: postgresql.tikiwiki.org)
Once Tiki works, I commit to putting at least one "Real World" project to Dogfood .
sylvieg postgres
luci postgres
soulhunterPostgreSQL I can do coding and testing. I definetelly will put a real world project: my own web will run on TikiWiki using PostgreSQL.
dthackerpostgreSQL, SQLite, Oraclecoding, testing and dog-fooding. Will also test with other databases (Informix, Firebird)
Kissaki SQLite, PostgreSQL I'll do these 2!
vilam SQLite Testing SQLite3 with PDO and tw 4.1 :Dogfood (in French)

How it works

SQL commands are added to db/tiki.sql

db/convertscripts/convertsqls.sh is then run.
It calls PHP executing the following scripts:

and those will generate the converted scripts:

Todo

PostgreSQL Wiki Converting from MySQL to PostgreSQL
Drupal was in a similar situation (Ticket from 21. Nov. until 5. Jan. 2009)

Some Differences Between PostgreSQL + MySQL

(taken from an article by Joel Burton on wiki.postgresql.org)

Joel Burton wrote:
In general, PostgreSQL makes a strong effort to conform to existing database standards, where MySQL has a mixed background on this. If you're coming from a background using MySQL or Microsoft Access, some of the changes can seem strange (such as not using double quotes to quote string values).

* MySQL uses nonstandard '#' to begin a comment line; PostgreSQL doesn't. Instead, use '--' (double dash), as this is the ANSI standard, and both databases understand it.
* MySQL uses ' or " to quote values (i.e. WHERE name = "John"). This is not the ANSI standard for databases. PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').
* MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.
* PostgreSQL is case-sensitive for string comparisons. The field "Smith" is not the same as the field "smith". This is a big change for many users from MySQL and other small database systems, like Microsoft Access. In PostgreSQL, you can either:
** Use the correct case in your query. (i.e. WHERE lname='Smith')
** Use a conversion function, like lower() to search. (i.e. WHERE lower(lname)='smith')
** Use a case-insensitive operator, like ILIKE or *~
* Database, table, field and columns names in PostgreSQL are case-independent, unless you created them with double-quotes around their name, in which case they are case-sensitive. In MySQL, table names can be case-sensitive or not, depending on which operating system you are using.
* PostgreSQL and MySQL seem to differ most in handling of dates, and the names of functions that handle dates.
* MySQL uses C-language operators for logic (i.e. 'foo' || 'bar' means 'foo' OR 'bar', 'foo' && 'bar' means 'foo' and 'bar'). This might be marginally helpful for C programmers, but violates database standards and rules in a significant way. PostgreSQL, following the standard, uses || for string concatenation ('foo' || 'bar' = 'foobar').
* There are other differences between the two, such as the names of functions for finding the current user. MySQL has a tool, Crash-Me, which can useful for digging this out. (Ostensibly, Crash-Me is a comparison tool for databases; however, it tends to seriously downplay MySQL's deficiencies, and isn't very objective in what it lists: the entire idea of having procedural languages (a very important feature for many users!) is relegated to a single line on the bottom fifth of the document, while the fact that MySQL allows you to use || for logical-or (definitely non-standard), is listed way before this, as a feature. Be careful about its interpretations.)


Some differences between adodb and pdo

  • pdo implements ifnull only for mysql
  • pdo does not implement the tiki logs

Automated testing

Alain Désilets wrote:
Note: It would also be nice to be able to use SQLite for automated testing purposes. A suite of automated tests typically needs to restore the DB to a pristine starting state several hundreds of time. With DBMS like MySQL, restoring the TIKi db can take something like 30 secs. With SQLite, it's about 5 secs (cause everything is in memory). Note that most of the time, the DB used for testing is very small and only contains a few objects and pages,so keeping it all in memory is not a problem..

Wishes

Open

 RatingSubjectCategoryPriorityImportanceEasy to solve?Submitted byVolunteered to solveLastModifComs
open -2 -1 1 2 (1) Help Add SQL/DB Link Tracker field typeFeature request63
9
7
Dimitrios Tanis
 
2014-01-301
diggy-30 Jan 14
open -2 -1 1 2 (0) Help Crash on a replication of site from Unix to Windows - and windows installationBug: Error
Bug: conflict of two features (each works well independently)
7
 
Bernard TREMBLAY
 
2010-03-124
trebly-08 Feb 10
open -2 -1 1 2 (0) Help Scope operator to access PDO properties and methods for $Tikidb fails : execution fail wit php 3.3.1Bug: Error
Bug: Consistency
7
 
Bernard TREMBLAY
 
2010-03-020
open -2 -1 1 2 (0) Help Start of tikiWiki with Windows and PDO extension (Apache-php-mysql win) - DB Main Classes problem and functions calls - Makes unable to start TikiwikiBug: Consistency
Bug: conflict of two features (each works well independently)
8
 
 
2010-04-070
open -2 -1 1 2 (0) Help Scope for object singleton TikiDb::get() access to Pdo function : incompatibility with php 5.3.2 (on windows)Bug: Error
Bug: Regression
Bug: Consistency
9 high
 
 
2010-04-140
open -2 -1 1 2 (0) Help DSN-Parsing not suitable for FirebirdBug: Error
3
10 easy
chaosben
 
2012-10-251
marclaporte-02 Nov 12

Pending

Closed

[+]

Related



Search Wishes (subject only) [Toggle]

Keywords [Toggle]

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
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
Module
MultiTiki
MyTiki
Newsletter
Notepad
OS independence (Non-Linux, Windows/IIS, Mac, BSD)
Payment 5.x
Performance Speed / Load / Compression / Cache
Permission
Poll
Profiles
Quiz
Rating
Report
Score
Search engine optimization (SEO)
Search
Security
Semantic links 3.x
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)
Tags 2.x
Task
Tell a Friend, alert + Social Bookmarking
TikiTests 2.x
Theme
Toolbar (Quicktags)
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




Spaces [Toggle]