Doctrine 1

[pgsql] importer does not fetch varchar max length

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.2.2
  • Fix Version/s: 1.2.4
  • Component/s: Import/Export
  • Labels:
    None
  • Environment:
    PostgreSql

Description

The importer doe not look for the max length of string columns.

For example, the task generate-models-db is generating definitions like
$this->hasColumn('code', 'string', null, array(/**/));

instead of
$this->hasColumn('code', 'string', 10, array(/**/));

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Francesco Montefoschi added a comment -

I created a patch on GitHub: http://github.com/fmntf/doctrine1/tree/DC-713.

Reading from `information_schema` we can also get the string length.
I tried to `generate-models-db` with original 1.2.2 code and patched code: it seems there are no regressions.

Show
Francesco Montefoschi added a comment - I created a patch on GitHub: http://github.com/fmntf/doctrine1/tree/DC-713 . Reading from `information_schema` we can also get the string length. I tried to `generate-models-db` with original 1.2.2 code and patched code: it seems there are no regressions.
Hide
Permalink
Francesco Montefoschi added a comment -

I don't know what happened, but the `listTableColumns` query is

SELECT fields, t.typtype AS typtype FROM information_schema.columns WHERE ..

and the t relation is not specified. By the way, commenting out the typtype, the string length is no more detected.
I think there is something wrong around line 170 - the "type" column should contain "varchar", not "character varying".

Show
Francesco Montefoschi added a comment - I don't know what happened, but the `listTableColumns` query is SELECT fields, t.typtype AS typtype FROM information_schema.columns WHERE .. and the t relation is not specified. By the way, commenting out the typtype, the string length is no more detected. I think there is something wrong around line 170 - the "type" column should contain "varchar", not "character varying".
Hide
Permalink
Francesco Montefoschi added a comment - - edited

I attached the query output, commenting the typtype column.

Show
Francesco Montefoschi added a comment - - edited I attached the query output, commenting the typtype column.
Hide
Permalink
Ricardo Simoes added a comment -

My workaround for `listTableColumns` query:
Doctrine/Import/Pgsql.php ln 101-102:

'listTableColumns' => "SELECT
ordinal_position as attnum,
column_name as field,
udt_name as type,
data_type as complete_type,
--t.typtype AS typtype,
(SELECT t.typtype FROM pg_type t WHERE t.typname = udt_name) AS typtype,
is_nullable as isnotnull,
column_default as default,
(
SELECT 't'
FROM pg_index, pg_attribute a, pg_class c, pg_type t
WHERE c.relname = table_name AND a.attname = column_name
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
AND c.oid = pg_index.indrelid AND a.attnum = ANY (pg_index.indkey)
AND pg_index.indisprimary = 't'
AND format_type(a.atttypid, a.atttypmod) NOT LIKE 'information_schema%%'
) as pri,
character_maximum_length as length
FROM information_schema.COLUMNS
WHERE table_name = %s
ORDER BY ordinal_position", FROM pg_type t WHERE t.typname = udt_name) AS typtype,

Show
Ricardo Simoes added a comment - My workaround for `listTableColumns` query: Doctrine/Import/Pgsql.php ln 101-102: 'listTableColumns' => "SELECT ordinal_position as attnum, column_name as field, udt_name as type, data_type as complete_type, --t.typtype AS typtype, (SELECT t.typtype FROM pg_type t WHERE t.typname = udt_name) AS typtype, is_nullable as isnotnull, column_default as default, ( SELECT 't' FROM pg_index, pg_attribute a, pg_class c, pg_type t WHERE c.relname = table_name AND a.attname = column_name AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.oid = pg_index.indrelid AND a.attnum = ANY (pg_index.indkey) AND pg_index.indisprimary = 't' AND format_type(a.atttypid, a.atttypmod) NOT LIKE 'information_schema%%' ) as pri, character_maximum_length as length FROM information_schema.COLUMNS WHERE table_name = %s ORDER BY ordinal_position", FROM pg_type t WHERE t.typname = udt_name) AS typtype,
Hide
Permalink
Jonathan H. Wage added a comment -

Why was this re-opened? Your comment is not clear to me. Can you provide a test case or a proper patch so we can see the changes.

Show
Jonathan H. Wage added a comment - Why was this re-opened? Your comment is not clear to me. Can you provide a test case or a proper patch so we can see the changes.
Hide
Permalink
Francesco Montefoschi added a comment -

Sorry if the comment was not clear.

I proposed you a patch, I tested it before sending it back to you.
You applied something else to Doctrine source code that is not working. Try to copy and paste the query in http://svn.doctrine-project.org/tags/1.2.3/lib/Doctrine/Import/Pgsql.php (listTableColumns) to pgadmin and execute it. The postgres server will refuse to execute that query.
The error is due to a call to a "t" relation, which is not declared in the FROM (the query is something like: "SELECT a.field, ..., t.field FROM a").

The error is:
-----------------------------------------------------
ERROR: missing FROM-clause entry for table "t"
LINE 6: ... t.typtype ...
^
-----------------------------------------------------

Show
Francesco Montefoschi added a comment - Sorry if the comment was not clear. I proposed you a patch, I tested it before sending it back to you. You applied something else to Doctrine source code that is not working. Try to copy and paste the query in http://svn.doctrine-project.org/tags/1.2.3/lib/Doctrine/Import/Pgsql.php (listTableColumns) to pgadmin and execute it. The postgres server will refuse to execute that query. The error is due to a call to a "t" relation, which is not declared in the FROM (the query is something like: "SELECT a.field, ..., t.field FROM a"). The error is: ----------------------------------------------------- ERROR: missing FROM-clause entry for table "t" LINE 6: ... t.typtype ... ^ -----------------------------------------------------
Hide
Permalink
Jonathan H. Wage added a comment -

It had looked like this patch: http://github.com/fmntf/doctrine1/commit/5a1123f78974fef7203500b082df754e1c69cbf6

was already applied. I did not apply any patch today. I am confused now, which patch is the right one?

Show
Jonathan H. Wage added a comment - It had looked like this patch: http://github.com/fmntf/doctrine1/commit/5a1123f78974fef7203500b082df754e1c69cbf6 was already applied. I did not apply any patch today. I am confused now, which patch is the right one?
Hide
Permalink
Francesco Montefoschi added a comment -

The patch was not applied correctly.

In 1.2.3 importer the query has 8 direct fields in SELECT + 1 field as subquery
My patch has 7 direct fields + 1 as subquery.

Compare also the subquery. Mine is one line shorter.

Read the two queries line by line. They are different, and the one in 1.2.3 cannot be executed

Show
Francesco Montefoschi added a comment - The patch was not applied correctly. In 1.2.3 importer the query has 8 direct fields in SELECT + 1 field as subquery My patch has 7 direct fields + 1 as subquery. Compare also the subquery. Mine is one line shorter. Read the two queries line by line. They are different, and the one in 1.2.3 cannot be executed
Hide
Permalink
Jonathan H. Wage added a comment -

Can you generate a patch and paste it in a code block?

Show
Jonathan H. Wage added a comment - Can you generate a patch and paste it in a code block?
Hide
Permalink
Jonathan H. Wage added a comment -

Hopefully this fixes it for good.

http://trac.doctrine-project.org/changeset/7689

Show
Jonathan H. Wage added a comment - Hopefully this fixes it for good. http://trac.doctrine-project.org/changeset/7689
Hide
Permalink
Francesco Montefoschi added a comment -

It seems to be fine!

Show
Francesco Montefoschi added a comment - It seems to be fine!
Hide
Permalink
Miha Vrhovnik added a comment - - edited

Ok.. this is complete mess, somebody with permissions should reopen it.
This one works for Francesco as he is running with notices disabled.
if ttype is removed or commented out , then you get a notices that ttype key doesn't exist.
and it seems that ttype is required for enums to work.

in one of the comments Ricardo suggested replacing ...
t.typtype AS typtype with
(SELECT t.typtype FROM pg_type t WHERE t.typname = udt_name) AS typtype,

adding this get's rid of notices, but i don't know what happens for other things mentioned within comments of if this is a correct fix... The one who provided support for enums should take a look

I think Francesco should run import with notices enabled and then add the upper sub-query to get rid f them.. then it should also test if the rest of the import still works correctly.

Show
Miha Vrhovnik added a comment - - edited Ok.. this is complete mess, somebody with permissions should reopen it. This one works for Francesco as he is running with notices disabled. if ttype is removed or commented out , then you get a notices that ttype key doesn't exist. and it seems that ttype is required for enums to work. in one of the comments Ricardo suggested replacing ... t.typtype AS typtype with (SELECT t.typtype FROM pg_type t WHERE t.typname = udt_name) AS typtype, adding this get's rid of notices, but i don't know what happens for other things mentioned within comments of if this is a correct fix... The one who provided support for enums should take a look I think Francesco should run import with notices enabled and then add the upper sub-query to get rid f them.. then it should also test if the rest of the import still works correctly.
Hide
Permalink
Francesco Montefoschi added a comment -

My apologies, I developed the patch on a non development machine, so I used the default PHP configuration.

Show
Francesco Montefoschi added a comment - My apologies, I developed the patch on a non development machine, so I used the default PHP configuration.

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Francesco Montefoschi
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: