two models

Can you tell us the SQL which is being generated by Doctrine and also the SQL that oracle expects?

1. Error in the function lastInsertID - none using " FROM DUAL" file: Doctrine/Sequence/Oracle.php --- Doctrine/Sequence/Oracle.php (revision 13) +++ Doctrine/Sequence/Oracle.php (revision 14) @@ -74,7 +74,7 @@

$seqName = $table . (empty($field) ? : '_'.$field); $sequenceName = $this->conn->quoteIdentifier($this->conn->formatter->getSequenceName($seqName), true);

- return $this->conn->fetchOne('SELECT ' . $sequenceName . '.currval'); + return $this->conn->fetchOne('SELECT ' . $sequenceName . '.currval FROM DUAL');

}

2. Wrong index generation(DoctrineExport?.php) Doctrine sytax(it`s mysql like):

CREATE TABLE test (id INT, test_field INT, INDEX(test_field_idx), PRIMARY KEY (id));

Should be

CREATE TABLE test (id INT PRIMARY KEY USING INDEX (create index id_idx on test (id)), test_field INT);

Or just create it in the another query

CREATE INDEX test_field_idx ON test(test_field);

3. Wrone generation on sequense name in the function processSingleInsert (file Doctrine/Connection/UnitOfWork.php)

for oracle i use.

--- Doctrine/Connection/UnitOfWork.php (revision 13) +++ Doctrine/Connection/UnitOfWork.php (revision 14) @@ -579,7 +579,10 @@

if (strtolower($this->conn->getDriverName()) == 'pgsql') {

$seq = $table->getTableName() . '_' . $identifier[0];

}

- + if (strtolower($this->conn->getDriverName()) == 'oracle') { + $seq = $table->getTableName(); + } +

$id = $this->conn->sequence->lastInsertId($seq);

4. When you put autoincrement=true triggers generates, but - will be no result - the triggers not have been added.

Can you attach that as a patch as it isn't really readable as a comment :(

Nevermind. I can make sense of it.

Would it be possible for you to provide a more complete patch? Since we don't have anyway to test against oracle it is difficult to work on and test this ticket. Anymore help you could provide would be great.

It would be possible. But not now - it may takes few(two or three i think) weeks.

Milestone New deleted

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 ` / , so I create '''Doctrine.php.patch'''. If oracle driver is used, the delimiter / is used instead of ; `.

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.

Fixed delimiter in generated SQL file for Oracle

Added generated triggers into SQL commands

Fixed duplicated primary keys generation when using autoincrement in Oracle

In r4990 this was fixed. Thanks for the report and patches.

As for the patch with Doctrine.php I think we need to create a different ticket for it and discuss it. I don't wanna have conditional driver code in Doctrine.php so we need to talk about it.