As I previously mentioned, I’m starting to work on FluentMigrator which is a database schema generator and versioning library. I’m doing this for my own need, but am trying to continue to respect the potential for others. I’ve found some syntax errors in generated sql statements, and one in particular causing a test to fail. It’s a t-sql versus SqLite problem. Ideally, one could define a migration that would be valid for both SqlServer and SqLite as long as the generator builds the proper sql statements. It’s not as easy as you (or at least me) would think at first. Consider this migration code:
1: public override void Up()
2: {
3: Create.Table("Users")
4: .WithColumn("UserId").AsInt32().Identity().PrimaryKey()
5: .WithColumn("UserName").AsString(32).NotNullable()
6: .WithColumn("Password").AsString(32).NotNullable();
7: }
8:
9: public override void Down()
10: {
11: Delete.Table("Users");
12: }
It’s a really simple example to be sure, but look at the difference in the ideal sql statements:
[SQL Server]
1: CREATE TABLE Users
2: (
3: UserId int IDENTITY(1,1) PRIMARY KEY,
4: UserName nvarchar(32) NOT NULL,
5: Password nvarchar(32) NOT NULL
6: )
[SQLite]
1: CREATE TABLE Users
2: (
3: UserId integer PRIMARY KEY AUTOINCREMENT,
4: UserName varchar(32) NOT NULL,
5: Password varchar(32) NOT NULL
6: )
But those aren’t the sql statements it currently generates.
Problem:
For SQLite to have the same behavior as SQL Server, you need to define the column as “PRIMARY KEY AUTOINCREMENT” instead of “IDENTITY(1,1) PRIMARY KEY”. But the migration generator is just trying to translate the syntax .Identity() into the database specific keyword(s). Both SQLite and SQL Server have an IDENTITY keyword but it means something different in SQLite. It only means that the column is the identity for the row but doesn’t auto-populate it as it would in SQL Server.
Solution:
I’m working on it. Obviously, we want this to be handled. I just don’t want to spend a lot of time making the SQLite generator too smart. Especially since it’s not something I need. I’m sure the answer is out there. If you happen to know, please contact me.
-j