Doctrine 1

Schema generation on Oracle databases fails due to invalid/unexistant table names

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.2.0
  • Fix Version/s: 1.2.2
  • Component/s: Schema Files
  • Labels:
    None
  • Environment:
    PHP 5.2.4-2ubuntu5.9 with Suhosin-Patch 0.9.6.2 (cli) (built: Nov 26 2009 14:00:44)
    Copyright (c) 1997-2007 The PHP Group
    Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies

    Oracle database 10gR2
    Symfony 1.4.1

Description

Sometimes the ORACLE engine has object names that are not tables, and the doctrine generator tries to
generate classes for them, the following patch solves the problem.

[Solution found thanks to Vladimir Tamara - vtamara AT pasosdejesus DOT org]

diff -u Doctrine/Import/Oracle.php.orig Doctrine/Import/Oracle.php

--- Doctrine/Import/Oracle.php.orig     2010-01-07 17:08:33.000000000
-0500
+++ Doctrine/Import/Oracle.php  2010-01-07 17:08:54.000000000 -0500
@@ -203,7 +203,7 @@
      */
     public function listTables($database = null)
     {
-        $query = "SELECT * FROM user_objects WHERE object_type =
'TABLE'";
+        $query = "SELECT * FROM user_objects WHERE object_type =
'TABLE' and object_name in (select table_name from user_tables)";
         return $this->conn->fetchColumn($query);
     }

@@ -252,4 +252,4 @@
         $query = 'SELECT view_name FROM sys.user_views';
         return $this->conn->fetchColumn($query);
     }
-}
\ No newline at end of file
+}

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Miloslav "adrive" Kmet added a comment -

Hi Andres.

Bear in mind, that materialized views are also acting like a table. But I think, that they should be imported into doctrine's models too. But I never use an schema Import.

I don't see any differences in the results of both queries.

{{
SELECT * FROM user_objects WHERE object_type = 'TABLE';
SELECT * FROM user_objects WHERE object_type = 'TABLE' and object_name in (select table_name from user_tables)
}}

Both are returning the same resultset. I have only basic objects (tables, views, mviews, some synonyms and some database links). No custom types and so on...

Wouldn't be much better to use only "select table_name from user_tables" ?

I am curious what object types are also acted like a table except mviews. Can you provide more info about your database objects?

Show
Miloslav "adrive" Kmet added a comment - Hi Andres. Bear in mind, that materialized views are also acting like a table. But I think, that they should be imported into doctrine's models too. But I never use an schema Import. I don't see any differences in the results of both queries. {{ SELECT * FROM user_objects WHERE object_type = 'TABLE'; SELECT * FROM user_objects WHERE object_type = 'TABLE' and object_name in (select table_name from user_tables) }} Both are returning the same resultset. I have only basic objects (tables, views, mviews, some synonyms and some database links). No custom types and so on... Wouldn't be much better to use only "select table_name from user_tables" ? I am curious what object types are also acted like a table except mviews. Can you provide more info about your database objects?
Hide
Permalink
Andres Molina added a comment -

Miloslav,

The issue I was having was that Oracle was generating some sort of mirror images of existing tables and saving them as table objects with very funky names (names that Doctrine wasn't able to parse correctly). The query modification I supplied makes sure that the tables objects found by the original query are also the tables named in user_tables, and not the aforementioned "mirror images".

It would in fact be simpler to use "select table_name from user_tables", but I wasn't sure whether object information was used somewhere else.

The database I was using was pretty simple. Pretty much your run-of-the-mill relational tables and a couple of sequences. I wasn't using any views or anything of the sort.

As far as I can tell, the user objects that were causing a problem were some sort of temporary objects Oracle creates and stores as table objects.

Show
Andres Molina added a comment - Miloslav, The issue I was having was that Oracle was generating some sort of mirror images of existing tables and saving them as table objects with very funky names (names that Doctrine wasn't able to parse correctly). The query modification I supplied makes sure that the tables objects found by the original query are also the tables named in user_tables, and not the aforementioned "mirror images". It would in fact be simpler to use "select table_name from user_tables", but I wasn't sure whether object information was used somewhere else. The database I was using was pretty simple. Pretty much your run-of-the-mill relational tables and a couple of sequences. I wasn't using any views or anything of the sort. As far as I can tell, the user objects that were causing a problem were some sort of temporary objects Oracle creates and stores as table objects.
Hide
Permalink
Miloslav "adrive" Kmet added a comment -

That sounds like dropped objects in recycle bin. They usually have funky names with all the bells and whistles around alphanumerical keys (eg:"BIN$HGnc55/7rRPgQPeM/qQoRw==$0") .

Just purge your recycle bin with "purge recyclebin;".

Show
Miloslav "adrive" Kmet added a comment - That sounds like dropped objects in recycle bin. They usually have funky names with all the bells and whistles around alphanumerical keys (eg:"BIN$HGnc55/7rRPgQPeM/qQoRw==$0") . Just purge your recycle bin with "purge recyclebin;".
Hide
Permalink
Andres Molina added a comment -

Unfortunately, the database that was giving me issues no longer exists, so I can't test if "purge recyclebin". However, the example object_name you gave is spot-on, so I'm pretty sure that's the cause of the issue.

Either way, I think the modification to the query I submitted is worthwhile as it would avoid this type of issue. If no data from objects is necessary, the query could be replaced altogether with "select table_name from user_tables" (as Miloslav mentioned in the first comment).

Show
Andres Molina added a comment - Unfortunately, the database that was giving me issues no longer exists, so I can't test if "purge recyclebin". However, the example object_name you gave is spot-on, so I'm pretty sure that's the cause of the issue. Either way, I think the modification to the query I submitted is worthwhile as it would avoid this type of issue. If no data from objects is necessary, the query could be replaced altogether with "select table_name from user_tables" (as Miloslav mentioned in the first comment).

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Andres Molina
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: