Note

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

History: Database independence

View published page Collapse Into Edit Sessions

Source of version: 43

In {COUNTDOWN(enddate="2009-08-01")}, database independence will be dropped in Tiki unless a maintainer steps up. So Tiki will become officially MySQL-only.{COUNTDOWN} 

__Why?__

Tiki uses a database abstraction layer so it can be used with many databases (PostgreSQL, Oracle, Sybase, SQLite) in addition to MYSQL. However, support for anything but ((Database MySQL|MySQL)) has become more & more problematic. It's not strictly a [http://en.wikipedia.org/wiki/Chicken-and-egg_problem|chicken & egg problem] because Tiki once worked with non-MYSQL. However, without maintainers, it was lost over time. 

We would love to support many databases, especially [http://www.postgresql.org/|PostgreSQL] because it's an open source & community-managed project like Tiki. There are many reasons why we would want database independence and it's no use listing them here because ''it's not that we don't want''. It's that __we don't have the resources to do so__. Tiki is a huge application and we would need active devs that use/maintain Tikis on other databases.

However, since we have no maintainer(s), we have been for a long time now in a "Worst of both worlds" situation.
*Extra code to maintain
**[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/convertsqls.sh?view=markup|convertscripts] and options in the installer.
**MySQL search vs DB independent search
*Bugs that never get fixed (See below for examples of issues related to other databases)
*People that are disappointed (feeling it's "false-advertising")
*Not taking full advantage of MySQL advanced features

Now that we have a more ((DatabaseSchemaUpgrade|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 | PostgreSQL | no coding, but I can supply a test/dev server (ex.: postgresql.tikiwiki.org) %%% Once Tiki works on PG again, I commit to putting at least one "Real World" project to ((tw:Dogfood)) PG.
 | |
||
!!How it works
SQL commands are added [http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki.sql|tiki.sql]

[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/convertsqls.sh?view=markup|convertsqls.sh] is then ran with these convertors:
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3tooci8.php?view=markup|MySQL to Oracle]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3topgsql72.php?view=markup|MySQL to PosgreSQL]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3tosqlite.php?view=markup|MySQL to SQlite]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3tosybase.php?revision=19138&view=markup|MySQL to Sybase]

To generate:
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-oci8.sql?view=markup|Oracle install]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-pgsql.sql?view=markup|PostreSQL install]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-sqlite.sql?view=markup|SQlite install]
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-sybase.sql?view=markup|Sybase install]
!!Todo
*--Contact all developers and people that have reported bugs related to database independence to inform them of the impending deadline.-- Done 2009-06-08
*Identify maintainers
*Monitor http://wiki.postgresql.org/wiki/TikiWiki_CMS/Groupware
*Get Tiki to install with various DBs
**Tiki 3.0 won't install with PostgreSQL http://demo.tikiwiki.org/postgresql/
*Fix bugs, especially in more important features
*update ((Hello World|#To_execute_some_queries_in_the_php|developer documentation)), especially ((tw:DbAbstractionDev)) (which dates from 2004! and needs to be moved to dev.tikiwiki.org) so developers don't introduce regressions
*Monitor [http://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs|commits]
**Ideally have a script which can detect errors.
*Promote Tiki within the Database's community
*((DatabaseSchemaUpgrade|Make upgrade procedure also be DB independant))
*Not directly related, but we need to complete the move to PDO (now in the code but not activated by default)
!!Links
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
[http://drupal.org/node/337146|Drupal is in a similar situation]

!! Some Differences Between PostgreSQL + MySQL
(taken from [http://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL|an article by Joel Burton on wiki.postgresql.org])

{QUOTE(replyto=Joel Burton)}
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. ~np~'foo' || 'bar'~/np~ 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 ~np~||~/np~ for string concatenation (~np~'foo' || 'bar'~/np~ = '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.) 
{QUOTE}



Related
*((Browser compatibility))
*((Operating System independence))

{maketoc maxdepth=1}
!Open
{wishes filtervalue="96" status="o"}

!Pending
{wishes filtervalue="96" status="p"}

!Closed
{wishes filtervalue="96" status="c"}

History

Enable pagination rows per page

Advanced

Information	Version
20 Sep 19 15:51 GMT-0000 drsassafras Mass search and replace	89
20 Sep 19 15:51 GMT-0000 drsassafras Mass search and replace	88
20 Sep 19 15:51 GMT-0000 drsassafras Mass search and replace	87
20 Sep 19 15:51 GMT-0000 drsassafras Mass search and replace	86
30 Jul 14 01:09 GMT-0000 Marc Laporte AutoTOC	85
18 Apr 12 14:26 GMT-0000 Marc Laporte	84
28 Mar 12 23:53 GMT-0000 Marc Laporte	83
29 Apr 11 17:26 GMT-0000 Marc Laporte	82
29 Apr 11 17:13 GMT-0000 Marc Laporte cleanup UTF-8 leftovers	81
29 Apr 11 17:11 GMT-0000 Marc Laporte	80
06 Sep 10 08:34 GMT-0000 Kissaki rollback	79
09 Feb 10 15:28 GMT-0000 vilam [rollback version 75]	77
09 Feb 10 15:28 GMT-0000 vilam	75
18 Jan 10 02:29 GMT-0000 Marc Laporte	74
19 Dec 09 22:00 GMT-0000 Kissaki that's not a "I help" what the paragraph above indicates, and is more of an ad. A suggestion of another DB would be proposed somewhere else, for example on the mailing list where it could be discussed	73
19 Dec 09 08:01 GMT-0000 Marc Laporte	72
19 Dec 09 07:32 GMT-0000 Marc Laporte refactor page	71
26 Nov 09 13:44 GMT-0000 macnific	70
19 Oct 09 23:54 GMT-0000 alain_desilets	69
16 Oct 09 20:45 GMT-0000 Kissaki	68
09 Oct 09 16:35 GMT-0000 Kissaki	67
08 Oct 09 18:37 GMT-0000 Kissaki oracle was removed, nobody working on it, and this info is probably outdated anyway	66
03 Oct 09 14:44 GMT-0000 Marc Laporte This is it.	65
30 Aug 09 16:59 GMT-0000 Marc Laporte	64
30 Aug 09 09:15 GMT-0000 Marc Laporte	63

«
1 (current)
2
3
4
»

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

History: Database independence

Source of version: 43

History

Keywords

Last Changed Items

Last Changed Pages

Useful Tools

About Tiki

Support

Community

Documentation

Development

Legal

Tiki Project Sites

Networks