Yii2: Create/Drop tables improvements

[Ragazzo]

I think QueryBuilder should use additional option from $options param:

$options['ifExists'] = true;

and if it is set, it should be in sql command. Because without this we need to do some dirty hacks like this.

Afaik there already was issue about this, and if it still exists this one can be closed and linked to the previous.
And this can be ported to Yii1.

Will submit PR if needed.

[samdark]

It's MySQL-only feature.

[cebe]

1. Why does a fixture care about creating and dropping the table?
2. In migrations this problem only exists with MySQL. Other DBMS such as Postgres and Sqlite can migrate schema within a transaction. See https://github.com/yiisoft/yii2/issues/1442?source=cc#issuecomment-29997482

[cebe]

Btw: your "hack" can be implemented differently by checking whether table exists in schema.
Also options is a string. I see no good way to add this.

[Ragazzo]

Why does a fixture care about creating and dropping the table?

read this https://github.com/yiisoft/yii2/issues/1956#issuecomment-32675275

In migrations this problem only exists with MySQL. Other DBMS such as Postgres and Sqlite can migrate schema within a transaction. See #1442

this problem also exists and in db-fixtures as you see.

K, so as i see there is no good way to solve this.

[Ragazzo]

And also postgresql support this feature according docs.

[samdark]

OK, what about SQLite, MSSQL and Oracle?

[Ragazzo]

they have their own query-builders, no? I understand suggestions of solution like in_array($table, $schema->getTableNames()) but it would be more native in query, no?

[samdark]

Yes, each DB backend has its own query builder. I meant asking if these DBMS have IF EXISTS support.

[Ragazzo]

After some googling looks like oracle dont have good support of this. So other discussion is senseless i think.

[klimov-paul]

Note: you can always check if some table exists using following code:

if ($this->db->schema->getTableSchema('my_table', true) === null) {
   // table 'my_table' exists!
}

[Ragazzo]

@klimov-paul yeah, noted above.i guess this would be the solution.

[dynasource]

@klimov-paul: your example shows a table that does not exist

[klimov-paul]

Yes, my mistake. Still I suppose you can get the idea.