Exception with Subsonic 2.2, SQLite and Migrations

Question

Hi, I'm playing with Migrations and created a simple migration like

public class Migration001 : Migration
    {
        public override void Up()
        {
            TableSchema.Table testTable = CreateTableWithKey("TestTable");
        }

        public override void Down()
        {

        }
    }

after executing sonic.exe migrate I'm getting the following output:

Setting ConfigPath: 'App.config'
Building configuration from c:\tmp\MigrationTest\MigrationTest\App.config
Adding connection to SQLiteProvider
Found 1 migration files
Current DB Version is 0
Migrating to 001_Init (1)
There was an error running migration (001_Init):
 SQLite error
near "IDENTITY": syntax error
Stack Trace:
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] argum
ents, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle
 typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] argume
nts, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwn
er)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invoke
Attr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisib
ilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invoke
Attr, Binder binder, Object[] parameters, CultureInfo culture)
   at SubSonic.CodeRunner.RunAndExecute(ICodeLanguage lang, String sourceCode, S
tring methodName, Object[] parameters) in D:\@SubSonic\SubSonic\SubSonic.Migrati
ons\CodeRunner.cs:line 95
   at SubSonic.Migrations.Migrator.ExecuteMigrationCode(String migrationFile) in
 D:\@SubSonic\SubSonic\SubSonic.Migrations\Migrator.cs:line 177
   at SubSonic.Migrations.Migrator.Migrate() in D:\@SubSonic\SubSonic\SubSonic.M
igrations\Migrator.cs:line 141

Any hints?

Answer 1

You are probably the only one on the Earth planet to have tried this. I don't think the migration code was ever completed or tested with sqlite, as evidenced by the IDENTITY keyword that shouldn't be used with sqlite. Subsonic is highly sql server centric, so there are many changes required to get all the features working for other databases. There is an improved sqlite provider in one of the forks of subsonic2 on github, but subsonic2 is (currently) a dead project and no github pull requests are being honored.