History: 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 [http://mariadb.org/|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 ((Database MySQL|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 ((Database Schema Upgrade|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.tiki.org) %%% Once Tiki works, I commit to putting at least one "Real World" project to ((tw:Dogfood)) .
sylvieg	|postgres|
luci 	|postgres|
soulhunter|PostgreSQL| I can do coding and testing.  I definetelly will put a real world project: my own web will run on TikiWiki using PostgreSQL.
dthacker|postgreSQL, SQLite, Oracle|coding, 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 :[http://simaten.free.fr/doc|Dogfood (in French)]
||
!!How it works
SQL commands are added to [http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki.sql|db/tiki.sql]

[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/convertsqls.sh?view=markup|db/convertscripts/convertsqls.sh] is then run.
It calls PHP executing the following scripts:
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3topgsql72.php?view=markup|db/convertscripts/mysql_to_pgsql.php] MySQL to PosgreSQL
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/convertscripts/mysql3tosqlite.php?view=markup|db/convertscripts/mysql_to_sqlite.php] MySQL to SQlite
and those will generate the converted scripts:
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-pgsql.sql?view=markup|db/tiki-4.0-pgsql.sql] PostreSQL install SQL
*[http://tikiwiki.svn.sourceforge.net/viewvc/tikiwiki/trunk/db/tiki-4.0-sqlite.sql?view=markup|db/tiki-4.0-sqlite.sql] SQlite install SQL

!!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.tiki.org/postgresql/
**Trunk as of 2009-08-08 (Future 4.0) won't install http://db.tiki.org/postgresql-trunk/tiki-install.php (with Postgresql 7.4)
*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.tiki.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
*((Database Schema Upgrade|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|PostgreSQL Wiki Converting from MySQL to PostgreSQL]
[http://drupal.org/node/337146|Drupal was in a similar situation] (Ticket from 21. Nov. until 5. Jan. 2009)

!! 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}


!! Some differences between adodb and pdo
* pdo implements ifnull only for mysql
* pdo does not implement the tiki logs

!!Automated testing
{QUOTE(replyto="Alain Désilets")}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..{QUOTE}

!Wishes
!!Open
{wishes filtervalue="96" status="o"}

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

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

!Related
*((Browser compatibility))
*((Operating System independence))
*((Database Layer))