用于SQLite的实体框架MigrationSqlGenerator [英] Entity Framework MigrationSqlGenerator for SQLite
本文介绍了用于SQLite的实体框架MigrationSqlGenerator的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否有一个用于SQLite的MigrationSqlGenerator用于实体框架？我只找到一个来自devart的商业广告。
is there a MigrationSqlGenerator for SQLite to use with entity framework? I only found one from devart which is commercial.
没有为提供商System.Data.SQLite找到MigrationSqlGenerator。在目标迁移配置
类中使用
SetSqlGenerator方法来注册其他SQL生成器。
No MigrationSqlGenerator found for provider 'System.Data.SQLite'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators.
这是什么我做： http://msdn.microsoft.com/en-gb/data/jj591621
推荐答案
尽管这篇文章已经开放了很久，但我会根据我们的朋友NSGaga展示的内容来发布自己要做的事情
Although the post was open for a long time, I'll post what I got to do based on what our friend NSGaga showed up.
我设法创建了一个基于SQLCE开发的类MigrationSqLiteGenerator。课程的代码太大了，更多的可以在我的博客上下载。
I managed to create a class "MigrationSqLiteGenerator", based on what was developed for SQLCE. The code of the class was too large, more can download it on my blog.
在Google云端硬盘中：
https://drive.google.com/file/d/0B-NCqWnMQpBrQjkxMkxnVlRzb00/view?usp=sharing
On Google Drive: https://drive.google.com/file/d/0B-NCqWnMQpBrQjkxMkxnVlRzb00/view?usp=sharing
这个类只有基础，加上是一个改进的起点。
This class has only the basics, plus is a starting point for improvement.
22.Mar.2017编辑@ 湿婆 ：这是代码内联，所以如果Google云端硬盘链接断开再次，您仍然可以访问代码。刚刚发现，SO在答案中有一个 3万字符限制，所以我不得不删除代码中的注释。 $ code $ 32,000 带有注释的字符：）
22.Mar.2017 EDIT by @shiva : Here's the code inline so if the Google Drive link breaks again, you can still access the code. Just found out SO has a 30,000 character limit in answer posts, so I had to delete the comments in the code. It was over 32,000 character with comments :)
// *-----------------------------------------------------------------*
// * *
// * CRIADO POR...: Julio C. Borges. *
// * DATA.........: 19/07/2013. *
// * MOTIVO.......: *
// * *
// *-----------------------------------------------------------------*
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Sql;
using System.Data.Metadata.Edm;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
namespace ICM_Conexao.SqLite.Classes
{
/// <sumary>
/// MigrationSqLiteGenerator
/// </sumary>
public class MigrationSqLiteGenerator : MigrationSqlGenerator
{
#region Constantes
private const string pstrDefaultDateTime = "yyyy-MM-dd hh:mm:ss";
private const int pintDefaultStringMaxLength = 255;
private const int pintDefaultPrecisaoNumerica = 10;
private const byte pbytDefaultPrecisaoTempo = 7;
private const byte pintDefaultEscala = 0;
//private const string pstrNomeTabelaMigration = "__MigrationHistory";
#endregion
#region Instancias
private DbProviderManifest pprovProviderManifest;
private List<MigrationStatement> plstComandos;
private bool pblnGerouPrimaryKey;
#endregion
#region Método de Geração sobrescrito de MigratioSqlGenerator
public override IEnumerable<MigrationStatement> Generate(
IEnumerable<MigrationOperation> lstOperacoesMigrations, string strManifestoProvider)
{
plstComandos = new List<MigrationStatement>();
InicializaServicosProvider(strManifestoProvider);
GeraComandos(lstOperacoesMigrations);
return plstComandos;
}
#endregion
#region Métodos de geração dos comandos
protected virtual void Generate(CreateTableOperation opeCriacaoTabela)
{
// Preferencialmente não iremos gerar a tabela de dados do Migration
if (opeCriacaoTabela.Name.Contains("MigrationHistory"))
return;
using (var ltextWriter = TextWriter())
{
GeraComandoCriacaoTabela(opeCriacaoTabela, ltextWriter);
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(AddForeignKeyOperation opeChaveEstrangeira)
{
// Inicialmente não havera a criação de chave estrangeira
}
protected virtual void Generate(DropForeignKeyOperation dropForeignKeyOperation)
{
// Inicalmente não haverá a criação de chave estrangeira
}
protected virtual void Generate(CreateIndexOperation opeCriacaoIndex)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("CREATE ");
if (opeCriacaoIndex.IsUnique)
ltextWriter.Write(" UNIQUE ");
ltextWriter.Write("INDEX ");
ltextWriter.Write(opeCriacaoIndex.Name);
ltextWriter.Write(" ON ");
ltextWriter.Write(RemoveDBO(opeCriacaoIndex.Table));
ltextWriter.Write("(");
for (int lintCount = 0; lintCount < opeCriacaoIndex.Columns.Count; lintCount++)
{
var lstrDadosColuna = opeCriacaoIndex.Columns[lintCount];
ltextWriter.Write(lstrDadosColuna);
if (lintCount < opeCriacaoIndex.Columns.Count - 1)
ltextWriter.WriteLine(",");
}
ltextWriter.Write(")");
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(DropIndexOperation opeDropIndex)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("DROP INDEX ");
ltextWriter.Write(opeDropIndex.Name);
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(AddPrimaryKeyOperation opeAdicionaPrimaryKey)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeAdicionaPrimaryKey.Table));
ltextWriter.Write(" ADD CONSTRAINT ");
ltextWriter.Write(opeAdicionaPrimaryKey.Name);
ltextWriter.Write(" PRIMARY KEY ");
ltextWriter.Write("(");
for (int li = 0; li < opeAdicionaPrimaryKey.Columns.Count; li++)
{
var lstrDadosColuna = opeAdicionaPrimaryKey.Columns[li];
ltextWriter.Write(lstrDadosColuna);
if (li < opeAdicionaPrimaryKey.Columns.Count - 1)
ltextWriter.WriteLine(",");
}
ltextWriter.Write(")");
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(DropPrimaryKeyOperation opeDropPrimaryKey)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeDropPrimaryKey.Table));
ltextWriter.Write(" DROP CONSTRAINT ");
ltextWriter.Write(opeDropPrimaryKey.Name);
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(AddColumnOperation opeAdicionaColuna)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeAdicionaColuna.Table));
ltextWriter.Write(" ADD ");
var lcmColuna = opeAdicionaColuna.Column;
Generate(lcmColuna, ltextWriter, null);
if ((lcmColuna.IsNullable != null)
&& !lcmColuna.IsNullable.Value
&& (lcmColuna.DefaultValue == null)
&& (string.IsNullOrWhiteSpace(lcmColuna.DefaultValueSql))
&& !lcmColuna.IsIdentity
&& !lcmColuna.IsTimestamp
&& !lcmColuna.StoreType.Equals("rowversion", StringComparison.InvariantCultureIgnoreCase)
&& !lcmColuna.StoreType.Equals("timestamp", StringComparison.InvariantCultureIgnoreCase))
{
ltextWriter.Write(" DEFAULT ");
if (lcmColuna.Type == PrimitiveTypeKind.DateTime)
{
ltextWriter.Write(Generate(DateTime.Parse("1900-01-01 00:00:00", CultureInfo.InvariantCulture)));
}
else
{
ltextWriter.Write(Generate((dynamic)lcmColuna.ClrDefaultValue));
}
}
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(DropColumnOperation opeRemoveColuna)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeRemoveColuna.Table));
ltextWriter.Write(" DROP COLUMN ");
ltextWriter.Write(opeRemoveColuna.Name);
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(AlterColumnOperation opeAlteraColuna)
{
var lcmColuna = opeAlteraColuna.Column;
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
ltextWriter.Write(" ALTER COLUMN ");
ltextWriter.Write(lcmColuna.Name);
ltextWriter.Write(" ");
ltextWriter.Write(ConstruirTipoColuna(lcmColuna));
if ((lcmColuna.IsNullable != null)
&& !lcmColuna.IsNullable.Value)
{
ltextWriter.Write(" NOT NULL");
}
ComandoSQL(ltextWriter);
}
if ((lcmColuna.DefaultValue == null) && string.IsNullOrWhiteSpace(lcmColuna.DefaultValueSql))
return;
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
ltextWriter.Write(" ALTER COLUMN ");
ltextWriter.Write(lcmColuna.Name);
ltextWriter.Write(" DROP DEFAULT");
ComandoSQL(ltextWriter);
}
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("ALTER TABLE ");
ltextWriter.Write(RemoveDBO(opeAlteraColuna.Table));
ltextWriter.Write(" ALTER COLUMN ");
ltextWriter.Write(lcmColuna.Name);
ltextWriter.Write(" SET DEFAULT ");
ltextWriter.Write(
(lcmColuna.DefaultValue != null)
? Generate((dynamic)lcmColuna.DefaultValue)
: lcmColuna.DefaultValueSql
);
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(DropTableOperation opeDropTable)
{
using (var ltextWriter = TextWriter())
{
ltextWriter.Write("DROP TABLE ");
ltextWriter.Write(RemoveDBO(opeDropTable.Name));
ComandoSQL(ltextWriter);
}
}
protected virtual void Generate(SqlOperation opeSQL)
{
ComandoSQL(opeSQL.Sql, opeSQL.SuppressTransaction);
}
protected virtual void Generate(RenameColumnOperation opeRenomearColuna)
{
// Inicialmente não suportada
}
protected virtual void Generate(RenameTableOperation opeRenameTable)
{
}
protected virtual void Generate(MoveTableOperation opeMoveTable)
{
}
private void Generate(ColumnModel cmDadosColuna, IndentedTextWriter textWriter, PrimaryKeyOperation opePrimaryKey)
{
textWriter.Write(cmDadosColuna.Name);
textWriter.Write(" ");
bool lblnEhPrimaryKey = false;
if (opePrimaryKey != null)
lblnEhPrimaryKey = opePrimaryKey.Columns.Contains(cmDadosColuna.Name);
if (lblnEhPrimaryKey)
{
if ((cmDadosColuna.Type == PrimitiveTypeKind.Int16) ||
(cmDadosColuna.Type == PrimitiveTypeKind.Int32))
textWriter.Write(" INTEGER ");
else
textWriter.Write(ConstruirTipoColuna(cmDadosColuna));
if (cmDadosColuna.IsIdentity)
{
textWriter.Write(" PRIMARY KEY AUTOINCREMENT ");
pblnGerouPrimaryKey = true;
}
}
else
{
textWriter.Write(ConstruirTipoColuna(cmDadosColuna));
if ((cmDadosColuna.IsNullable != null)
&& !cmDadosColuna.IsNullable.Value)
{
textWriter.Write(" NOT NULL");
}
if (cmDadosColuna.DefaultValue != null)
{
textWriter.Write(" DEFAULT ");
textWriter.Write(Generate((dynamic)cmDadosColuna.DefaultValue));
}
else if (!string.IsNullOrWhiteSpace(cmDadosColuna.DefaultValueSql))
{
textWriter.Write(" DEFAULT ");
textWriter.Write(cmDadosColuna.DefaultValueSql);
}
}
}
protected virtual void Generate(HistoryOperation opeHistorico)
{
// Foi removido, pois atualmente não usaremos o Migration
//using (var ltextWriter = TextWriter())
//{
// if (opeHistorico is InsertHistoryOperation)
// {
// InsertHistoryOperation lhisOperacaoInsert = opeHistorico as InsertHistoryOperation;
// ltextWriter.Write(" INSERT INTO ");
// ltextWriter.Write(pstrNomeTabelaMigration);
// ltextWriter.Write(" ( MigrationId, Model, ProductVersion) VALUES ");
// ltextWriter.Write(string.Format(" ('{0}', {1}, '{2}')",
// lhisOperacaoInsert.MigrationId,
// Generate(lhisOperacaoInsert.Model), lhisOperacaoInsert.ProductVersion));
// }
// else if (opeHistorico is DeleteHistoryOperation)
// {
// DeleteHistoryOperation lhisOperacaoInsert = opeHistorico as DeleteHistoryOperation;
// ltextWriter.Write(" DELETE FROM ");
// ltextWriter.Write(pstrNomeTabelaMigration);
// ltextWriter.Write(string.Format(" WHERE MigrationId = '{0}'",
// lhisOperacaoInsert.MigrationId));
// }
// ComandoSQL(ltextWriter);
//}
}
protected virtual string Generate(byte[] bytDefaultValue)
{
var lstrbHexString = new StringBuilder();
foreach (var lbtByte in bytDefaultValue)
lstrbHexString.Append(lbtByte.ToString("X2", CultureInfo.InvariantCulture));
return "x'" + lstrbHexString + "'";
}
protected virtual string Generate(bool blnDefaultValue)
{
return blnDefaultValue ? "1" : "0";
}
protected virtual string Generate(DateTime dtmDefaultValue)
{
return "'" + dtmDefaultValue.ToString(pstrDefaultDateTime, CultureInfo.InvariantCulture) + "'";
}
protected virtual string Generate(DateTimeOffset dtfDefaultValue)
{
return "'" + dtfDefaultValue.ToString(pstrDefaultDateTime, CultureInfo.InvariantCulture) + "'";
}
protected virtual string Generate(Guid guidDefaultValue)
{
return "'" + guidDefaultValue + "'";
}
protected virtual string Generate(string strDefaultValue)
{
return "'" + strDefaultValue + "'";
}
protected virtual string Generate(TimeSpan tsDefaultValue)
{
return "'" + tsDefaultValue + "'";
}
protected virtual string Generate(object objDefaultValue)
{
return string.Format(CultureInfo.InvariantCulture, "{0}", objDefaultValue);
}
#endregion
#region Métodos auxiliares
protected virtual string ConstruirTipoColuna(ColumnModel cmModeloColuna)
{
return cmModeloColuna.IsTimestamp ? "rowversion" : ConstruirTipoPropriedade(cmModeloColuna);
}
private string ConstruirTipoPropriedade(ColumnModel propModeloPropriedade)
{
var lstrOriginalStoreTypeName = propModeloPropriedade.StoreType;
if (string.IsNullOrWhiteSpace(lstrOriginalStoreTypeName))
{
var ltypeUsage = pprovProviderManifest.GetStoreType(propModeloPropriedade.TypeUsage).EdmType;
lstrOriginalStoreTypeName = ltypeUsage.Name;
}
var lstrStoreTypeName = lstrOriginalStoreTypeName;
const string lstrSufixoMax = "(max)";
if (lstrStoreTypeName.EndsWith(lstrSufixoMax, StringComparison.Ordinal))
lstrStoreTypeName = lstrStoreTypeName.Substring(0, lstrStoreTypeName.Length - lstrSufixoMax.Length) + lstrSufixoMax;
switch (lstrOriginalStoreTypeName.ToLowerInvariant())
{
case "decimal":
case "numeric":
lstrStoreTypeName += "(" + (propModeloPropriedade.Precision ?? pintDefaultPrecisaoNumerica)
+ ", " + (propModeloPropriedade.Scale ?? pintDefaultEscala) + ")";
break;
case "datetime":
case "time":
lstrStoreTypeName += "(" + (propModeloPropriedade.Precision ?? pbytDefaultPrecisaoTempo) + ")";
break;
case "blob":
case "varchar2":
case "varchar":
case "char":
case "nvarchar":
case "nvarchar2":
lstrStoreTypeName += "(" + (propModeloPropriedade.MaxLength ?? pintDefaultStringMaxLength) + ")";
break;
}
return lstrStoreTypeName;
}
protected void ComandoSQL(string strInstrucaoSQL, bool blnSuprimeTransacao = false)
{
plstComandos.Add(new MigrationStatement
{
Sql = strInstrucaoSQL,
SuppressTransaction = blnSuprimeTransacao
});
}
protected void ComandoSQL(IndentedTextWriter writer)
{
ComandoSQL(writer.InnerWriter.ToString());
}
protected static IndentedTextWriter TextWriter()
{
return new IndentedTextWriter(new StringWriter(CultureInfo.InvariantCulture));
}
private static string RemoveDBO(string strTexto)
{
return strTexto.Replace("dbo.", string.Empty);
}
private void GeraComandos(IEnumerable<MigrationOperation> lstOperacoesMigrations)
{
foreach (dynamic ldynOperacao in lstOperacoesMigrations)
Generate(ldynOperacao);
}
private void InicializaServicosProvider(string strManifestoProvider)
{
using (var lconConexao = CreateConnection())
{
pprovProviderManifest = DbProviderServices
.GetProviderServices(lconConexao)
.GetProviderManifest(strManifestoProvider);
}
}
protected virtual DbConnection CreateConnection()
{
return new SQLiteConnection();
}
private void GeraComandoCriacaoTabela(CreateTableOperation opeCriacaoTabela, IndentedTextWriter textWriter)
{
textWriter.WriteLine("CREATE TABLE " + RemoveDBO(opeCriacaoTabela.Name) + " (");
textWriter.Indent++;
for (int i = 0; i < opeCriacaoTabela.Columns.Count; i++)
{
ColumnModel lcmDadosColuna = opeCriacaoTabela.Columns.ToList()[i];
Generate(lcmDadosColuna, textWriter, opeCriacaoTabela.PrimaryKey);
if (i < opeCriacaoTabela.Columns.Count - 1)
textWriter.WriteLine(",");
}
if ((opeCriacaoTabela.PrimaryKey != null) && !pblnGerouPrimaryKey)
{
textWriter.WriteLine(",");
textWriter.Write("CONSTRAINT ");
textWriter.Write(RemoveDBO(opeCriacaoTabela.PrimaryKey.Name));
textWriter.Write(" PRIMARY KEY ");
textWriter.Write("(");
for (int li = 0; li < opeCriacaoTabela.PrimaryKey.Columns.Count; li++)
{
var lstrNomeColuna = opeCriacaoTabela.PrimaryKey.Columns[li];
textWriter.Write(lstrNomeColuna);
if (li < opeCriacaoTabela.PrimaryKey.Columns.Count - 1)
textWriter.WriteLine(",");
}
textWriter.WriteLine(")");
}
else
{
textWriter.WriteLine();
}
textWriter.Indent--;
textWriter.Write(")");
}
#endregion
}
}
这篇关于用于SQLite的实体框架MigrationSqlGenerator的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
