Syntax error in SQL Statements to create DB Schema

I don't understand the issue or your proposed fix. Re-open if you have more information and a potential patch to fix the issue.

If the folowing schema is used

Foo:
bar:
type: boolean
default: false
notnull: true

doctrine creates the following sql statement (to build the oracle database schema)

CREATE TABLE "Foo" ("bar" NUMBER(1) DEFAULT NOT NULL)

There is no default value between "DEFAULT" and "NOT NULL" which cause an error.

That is because in YAML

Foo:
  bar: 
    type: boolean
    default: false
    notnull: true

The default: false evaluates to PHP boolean false, which evaluates to '' PHP string. You need to wrap false in single quotes so that it does not evaluate to `` when being concatenated.

Foo:
  bar: 
    type: boolean
    default: 'false'
    notnull: true

I do understand this with "false" - but the result is the same if "true" as default value is used (which will be evaluated as "1" php string.

And should this quoting not be the job of $this->conn->getDbh()>quote() which is used on the end of the swicth /case statement? I have tested $this>conn->getDbh()->quote () and it has returned an empty string any time.

$dbh = new PDO($dsn, $user, $password);
$str = "1";
echo "quote($str): " . $dbh->quote($str) . "\n";

There is a quote() function in Doctrine_Adapter_Oracle - this would work fine but is not used

I think you should be doing $str = "true"; Does that work? I think it should.

No, it does not

$dsn = 'oci:dbname=XE';
$user = 'test';
$password = 'test';
$dbh = new PDO($dsn, $user, $password);
echo '>' . $dbh->quote('true') . '<';

: php test.php
><

Ok, so can you give some more information about the issue. I don't totally understand the issue yet. What are the errors from the database you are getting? What does your initial fix do to fix the problem?

The error message from database is: ORA-00936: missing expression

The reason is that $this->conn->getDbh()->quote () returned an emtpy string.

It happens if a default value is used in the yml schema for one of the below datatypes and Doctrine::createTablesFromModels() tries to create the database schema.

The empty return value of $this->conn->getDbh()->quote () results in SQL statements like

CREATE TABLE "Foo" ("bar" NUMBER(1) DEFAULT <here is nothing and this is a syntax error> NOT NULL)

The error will appear if a default value is used for any of the following data types

case 'date':
case 'time':
case 'timestamp':
case 'string':
case 'char':
case 'varchar':
case 'text':
case 'gzip':
case 'blob':
case 'clob':
case 'enum':
case 'boolean':

At the end of the above switch / case in Doctrine_Formatter return $this->conn->getDbh()->quote () is used to qoute the data.

As a workaround i have changed this to return "'" . str_replace("'","''",$input) . "'";

P.S: The need to use default values starts with the soft delete behaviour. Doctrine uses a timestamp to indicate that a record is deleted and a null value if not. Since oracle cant use indexes on columns with null values this results in a very very poor performance (full table scan over 3 Mill.. rows). Due to this i have tried to used a default value 01/01/1970 instead of null.

I do not understand why this issue has been closed without fixing the problem.