Re: BUG #13883: Very Important Facility

On 1/22/16, djm7(at)nym(dot)hush(dot)com <djm7(at)nym(dot)hush(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 13883
> Logged by: Dominic Marchand
> Email address: djm7(at)nym(dot)hush(dot)com
> PostgreSQL version: 9.2.10
> Operating system: Linux Centos 7
> Description:
>
> Joomla has had over 50 million downloads. Now it supports PostgreSQL.
> People
> want to use PostgreSQL but find many Joomla extensions do not support
> PostgreSQL.
>
> The most important backup facility (extension) in Joomla is Akeeba backup
> https://www.akeebabackup.com.
>
> It does not support PostgreSQL. They do not support it because PostgreSQL
> does not have a SHOW CREATE command.
>
> See:
> https://www.akeebabackup.com/support/akeeba-backup-3x/16284-installer-crashes-with-postgresql-database.html
>
> It would be advantageous to PostgreSQL to collaborate with akeeba and
> discuss a way for them to provide support for postgresql perhaps even add
> what they need SHOW CREATE to postgresql.
>
> https://www.akeebabackup.com/contact-us.html

1. It is not a bug (as Gavin has already mentioned[3]).

2. "SHOW CREATE" is the only MySQL (MariaDB) _statement_, there is no
such statement neither in Postgres nor MSSQL nor Oracle (but it has
get_ddl[1] _function_[2]).

3. Even if Postgres has such statement (or function) it is not enough
for the Akeeba project, because:

3a. they have to keep a database schema in a DBMS-independent format
(all databases have different options in their DDL syntax [e.g.
"AUTO_INCREMENT" option is the only MySQL extension and a type
"NUMBER" is supported by Oracle but neither by Postgres nor MySQL] and
they would have their nuances in the output of "SHOW CREATE"-alike
functions);

3b. they have to create DDL according to the destination database
rules (SQL standard quoting identifiers is the only doublequote ("),
not backquote (`), which is used widely in MySQL) from their new
DBMS-independent format.

So if the Akeeba project wants to support not only MySQL, they have to
either follow SQL standard (there are several dialects: SQL-92,
SQL:1999, SQL:2003, SQL:2006, SQL:2008 and SQL:2011) and get the
DBMS-independent schema from the "information_schema" catalog or use a
DBMS-dependent adapters. In the last case they can use the
"pg_catalog" schema for Postgres.

In both cases it is a task for Akeeba project rather than for Postgres project.

[1]http://stackoverflow.com/questions/18264584/show-create-table-equivalent-in-oracle-sql
[2]http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm
[3]http://www.postgresql.org/message-id/56A670DE.7010407@archidevsys.co.nz
--
Best regards,
Vitaly Burovoy