Multi database application on dibi (Oracle/Postgresql) = optional escape quotes

[BigOHenry]

Version: 4.0.2

Bug Description

We are making multi database support app on Nette 3 and Dibi 4. We will have support for Oracle and Postgresql (for now).

1) For example, create table:

CREATE TABLE table_name
(
    id INT,
    name VARCHAR(200) NOT NULL
);

Result:

• Oracle - all identifiers (table, columns) created with uppercase
• Postgre - all identifiers (table, columns) created with lowercase

2) Oracle and Postgre identifiers are case insenstive: This will works on both databases:

SELECT id, name FROM table_name
SELECT ID, NAME FROM TABLE_NAME
SELECT Id, naMe FROM Table_name

3) Dibi\Fluent: a) select with multiple columns and table alias:

        $q = $this->db
            ->select("id, name ")
            ->from("table_name t");

• generated SQL: SELECT id, name FROM table_name t
• this will execute correctly on both databases

b) select with single columns and table without alias - lowercase:

        $q = $this->db
            ->select("id")
            ->from("table_name");

• generated SQL: SELECT "id" FROM "table_name"
• this will execute correctly only on postgre.

c) select with single columns and table without alias - uppercase:

        $q = $this->db
            ->select("ID")
            ->from("TABLE_NAME");

• generated SQL: SELECT "ID" FROM "TABLE_NAME"
• this will execute correctly only on oracle.

Conclusion:

1) There is a different behavior when using Fluent with multiple columns (solution is use array) or table with alias (dont know solution).

2) I tried to postgre and oracle with uppercase identifiers, but i cannot get functional SELECT using Fluent for both databases (oracle works as is, but dont know how to write working example for 3) a) for postgre).

3) I tried to postgre and oracle with lowercase, but result is same like all uppercase with working postgre and oracle not.

4) Adding double quotes to identifiers removed nice case-insensitive behavior from both databases.

5) Huge problem for me is using arrays with dibi (array for values to insert or update), columns are aways with double quotes.

Possible Solution

The option at config for adding quotes.

If there are not added quotes to identifiers, queries will be working on both databases with case-insensitive feature. For now, i dont see any way how to effectively use Dibi for multi-database support without any additional conversion functions (convert case before query) or having 2 different database layers.

I dont know if i missed something, but this is my conclusion for now. Thank you for your help!

[JanTvrdik]

You should quote the identifiers when creating the table so that the resulting databases look the same.

CREATE TABLE "table_name"
(
    "id" INT,
    "name" VARCHAR(200) NOT NULL
);

[BigOHenry]

I already tried that (Conclusion no.3),

The main problem is when i write this Fluent query:

$q = $this->db
            ->select("id, name ")
            ->from("table_name t");

SQL is: SELECT id, name FROM table_name t

Got this error: ORA-00942: table or view does not exist

[JanTvrdik]

You can write that as

$this->db->select(['id', 'name'])->from('table_name')->as('t')

[BigOHenry]

Thank you for quick response.

Code: $this->db->select(['id', 'name'])->from('table_name')->as('t');

SQL: SELECT "id", "name" FROM "table_name" AS "t"

Error: ORA-00907: missing right parenthesis

There cannot be AS for table alias at Oracle.

I tried another test with join:

Code:

$this->db
            ->select(['t.id','t.name','t2.name'])
            ->from('table_name')->as('t')
            ->join('table_name_2')->as('t2')
                ->on('t.id = t2.id_table_name');

SQL: (manually removed AS)

SELECT "t"."id", "t"."name", "t2"."name"
FROM "table_name" "t" JOIN "table_name_2" "t2" ON t.id = t2.id_table_name

Error: ORA-00904: "T2"."ID_TABLE_NAME": invalid identifier

I dont see any way get working Oracle with lowercase and Dibi effectively (Same problem is Postgre with uppercase). i think the best solution would be support to turn escaping with double quotes off at dibi config section.

[milo]

These automatic case conversions are annoing. IMHO, @JanTvrdik solution is correct for multi-type databases. So create every database object in exact case:

-- dibi syntax
CREATE TABLE [table_name]
(
    [id] INT,
    [name] VARCHAR(200) NOT NULL
);

You can write fluent in many ways. For example:

$this->db
    ->select('[t.id], [t.name], [t2.name]')
    ->from('[table_name] [t]')
    ->join('[table_name_2] [t2]')
    ->on('[t.id] = [t2.id_table_name]');

Probably removing AS by fluent for Oracle would be a good fix.

[BigOHenry]

Thank you, i will try it. At this time i have own version of dibi with modification for Oracle. All identifiers are lower case. It seems to work for now. This should be an option at dibi :)