dotConnect for SQLite implements support for Entity Framework Code-First Migrations. Entity Framework Code-First Migrations offer many operations that can be performed with database objects. However, not all of them can be supported for every DBMS. The following table lists these operations and contains information on their support in dotConnect providers for different DBMSs.
Table operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| CreateTable |  |
|
|
|
|
| DropTable | |
|
|
|
|
| RenameTable | |
|
|
|
|
| MoveTable |  |
|
|
|
|
Column operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| AddColumn | |
|
|
|
|
| AlterColumn | /* |
|
|
/** |
|
| DropColumn | |
|
|
/** |
|
| RenameColumn | |
|
|
/** |
|
* The ALTER COLUMN statement that changes the column type cannot be executed for a LOB column (CLOB/NCLOB/BLOB) in Oracle. See details in the next section.
** The ALTER TABLE statement is very limited in SQLite (see here). Some operations in dotConnect for SQLite are implemented via re-creating a table and copying data from an old table to the new one.
Constraint operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| AddPrimaryKey | |
|
|
/* |
|
| DropPrimaryKey | |
|
|
/* |
|
| AddForeignKey | |
|
|
/** |
|
| DropForeignKey | |
|
|
/* |
|
* The ALTER TABLE statement is very limited in SQLite (see here). Some operations in dotConnect for SQLite are implemented via re-creating a table and copying data from an old table to the new one.
** The ADD FOREIGN KEY constraint operation for a foreign key, consisting of a single column, is supported in two cases: when a new column for the foreign key is created in the same migration, or when the whole table is created in the same migration.
Index operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| CreateIndex | |
|
|
|
|
| DropIndex | |
|
|
|
|
| RenameIndex* | |
/** |
|
|
|
| AddUniqueConstraint*** | |
|
|
|
|
| DropUniqueConstraint*** | |
|
|
|
|
* This operation is available only in Entity Framework 6 and Entity Framework Core.
** Index renaming is supported starting from MySQL 5.7 and higher. For MySQL 5.6 or lower, delete and recreate the necessary index instead. We cannot automatically drop/recreate index (with 5.6 or lower) because Code-First Migrations don't provide the necessary metadata to the underlying EF-provider. So we have only the names of old and new indexes but the corresponding columns are not known.
*** This operation is available only in Entity Framework Core.
Sequence Operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| CreateSequence* | |
|
|
|
|
| AlterSequence* | |
|
|
|
|
| RestartSequence* | |
|
|
|
|
| DropSequence* | |
|
|
|
|
* This operation is available only in Entity Framework Core.
SQL Operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| SQL | |
|
|
|
|
Data Manipulation Operations
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| InsertData* | |
|
|
|
|
| UpdateData* | |
|
|
|
|
| DeleteData* | |
|
|
|
|
* This operation is available only in Entity Framework Core 2.0.
Schema Operations*
| Operation | Oracle | MySQL | PostgreSQL | SQLite | DB2 |
|---|---|---|---|---|---|
| EnsureSchema** | *** |
|
**** |
*** |
*** |
| DropSchema** | |
|
|
|
|
* As an alternative to these operations, you may use the approach with Database.EnsureCreated()/Database.EnsureDeleted() and behavior configuration using the config.DatabaseScript.Schema.DeleteDatabaseBehaviour option.
** This operation is available only in Entity Framework Core.
*** The operation is simply ignored, no exception is thrown.
**** Behavior of the EnsureSchema operation can be configured with the config.DatabaseScript.Schema.EnsureSchemaBehaviour option.