#1337: It`s wrong index and triggers creation in the oracle
database(createTablesFromModels)
------------------------+---------------------------------------------------
Reporter: FreeBolik | Owner: jwage
Type: defect | Status: new
Priority: major | Milestone: 1.0.3
Component: Other | Version: 1.0
Resolution: | Keywords: oracle index triggers
Has_test: 1 | Mystatus: Pending Core Response
Has_patch: 0 |
------------------------+---------------------------------------------------
Changes (by adrive):
* milestone: => 1.0.3
Comment:
I have also problems with table/index/trigger generation for Oracle.
Triggers are not included into generated sql's when build-sql
(!GenerateSql task) or insert-sql (!CreateTables task) are runned.
'''`Export.php.patch`''' fix it.
Autoincremented field leads to duplicate primary key generation. For
example simple model Article generate these queries:
{{{
Article:
tableName: ARTICLE
columns:
title: string(1000)
CREATE TABLE ARTICLE (id NUMBER(20), title VARCHAR2(1000), category_id
NUMBER(11), PRIMARY KEY(id));
CREATE SEQUENCE ARTICLE_seq START WITH 1 INCREMENT BY 1 NOCACHE;
ALTER TABLE ARTICLE ADD CONSTRAINT ARTICLE_AI_PK_idx PRIMARY KEY (id);
}}}
The primary key is already generated by CREATE TABLE statement. Additional
altering table terminates with oracle's error. There are two solutions on
this problem.
First one is really simple - just removing the stuff from
Doctrine_Export_Oracle::_makeAutoincrement that generates this alter
`$sql[] = $this->createConstraintSql($table, $indexName, $definition);`,
but maybe there are some scenarios where primary key is not generated by
CREATE TABLE statement, and from whatever reason it will be necessary to
have primary key on autoincremented columns. I do not know why it is
necessary, when incrementing is done by sequence. So I enclose the
constraint creation into Oracle's anonymous block and alter is runned only
when no primary key exists. For examle:
{{{
DECLARE
constraints_Count NUMBER;
BEGIN
SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM
USER_CONSTRAINTS WHERE TABLE_NAME = 'ARTICLE' AND CONSTRAINT_
TYPE = 'P';
IF constraints_Count = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ARTICLE ADD CONSTRAINT
ARTICLE_AI_PK_idx PRIMARY KEY (id)';
END IF;
END;
}}}
This approach is fixed in '''`OracleAlter.patch`''' which also includes
changes in '''`Export.php.patch`'''.
The next problem is with delimiters in file generated by !GenerateSql tak.
When doctrine-insert-sql is runned, every query is runned in one exec. But
when I generate them into schema.sql file and want to run myself, only
first trigger was created. I discovered, that the delimiters of each
queries are wrong. There were two semicolons after each CREATE TRIGGER
statements. I discovered, that semicolon after END; of CREATE TRIGGER
statement is necessary when running doctrine-insert-sql task. Without it,
the process will fail.
In SQL file for oracle, statements should be divided with `\n/\n`, so I
create '''`Doctrine.php.patch`'''. If oracle driver is used, the delimiter
`\n/\n` is used instead of `;\n`.
I can commit these changes, but I want someone else to review them. I am
not sure, whether I found the best way to solve these problems.
--
Ticket URL: <http://trac.doctrine-project.org/ticket/1337#comment:9>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
