public override void CreateOrUpdateTable(TableSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider)
{
const string longTextType = "TEXT";
var columnMappings = new[]
{
new { Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "LONGBLOB", DefaultValue = (string)null },
new { Flags = TypeFlags.Boolean, ColumnType = "TINYINT", DefaultValue = "0" },
new { Flags = TypeFlags.Integer, ColumnType = "INTEGER", DefaultValue = "0" },
new { Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})", DefaultValue = "0" },
new { Flags = TypeFlags.FloatingPoint, ColumnType = "REAL", DefaultValue = "0" },
new { Flags = TypeFlags.String, ColumnType = "VARCHAR({0})", DefaultValue = "''" },
new { Flags = TypeFlags.DateTime, ColumnType = "DATETIME", DefaultValue = (string)null },
new { Flags = TypeFlags.Guid, ColumnType = "VARCHAR(32)", DefaultValue = (string)null },
};
if (recreateTable)
{
recreateIndexes = true;
}
HashSet <string> tableNames = new HashSet <string>(dataProvider.ExecuteSqlReader("SELECT name FROM sqlite_master WHERE type='table'", null).Select(rec => rec["name"].ToString()), StringComparer.OrdinalIgnoreCase);
if (tableNames.Contains(schema.MappedName) && recreateTable)
{
dataProvider.ExecuteSql("DROP TABLE " + QuoteTable(schema.MappedName), null);
}
var existingColumns = dataProvider.ExecuteSqlReader("pragma table_info(" + QuoteTable(schema.MappedName) + ")", null).ToLookup(rec => rec["name"].ToString());
var parts = new List <string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.Inspector().Type.Inspector().Is(mapping.Flags));
if (columnMapping == null)
{
continue;
}
if (existingColumns.Contains(field.MappedName) && !recreateTable)
{
createNew = false;
continue;
}
if (columnMapping.Flags == TypeFlags.String && field.ColumnSize == int.MaxValue)
{
columnMapping = new { columnMapping.Flags, ColumnType = longTextType, DefaultValue = "''" }
}
;
var part = $"{QuoteField(field.MappedName)} {string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale)}";
if (!field.ColumnNullable || field.PrimaryKey)
{
part += " NOT NULL";
if (columnMapping.DefaultValue != null)
{
part += " DEFAULT " + columnMapping.DefaultValue;
}
}
else
{
part += " NULL";
}
if (field.PrimaryKey && schema.PrimaryKeys.Length == 1)
{
part += " PRIMARY KEY";
if (field.AutoIncrement)
{
part += " AUTOINCREMENT";
}
}
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 1)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (parts.Any())
{
if (createNew)
{
dataProvider.ExecuteSql("CREATE TABLE " + QuoteTable(schema.MappedName) + " (" + string.Join(",", parts) + ")", null);
}
else
{
foreach (var part in parts)
{
dataProvider.ExecuteSql("ALTER TABLE " + QuoteTable(schema.MappedName) + " ADD COLUMN " + part + ";", null);
}
}
}
var existingIndexes = dataProvider.ExecuteSqlReader("PRAGMA INDEX_LIST(" + QuoteTable(schema.MappedName) + ")", null).ToLookup(rec => rec["name"].ToString());
foreach (var index in schema.Indexes)
{
if (existingIndexes[index.Name].Any())
{
if (recreateIndexes)
{
dataProvider.ExecuteSql("DROP INDEX " + QuoteTable(index.Name) + " ON " + QuoteTable(schema.MappedName), null);
}
else
{
continue;
}
}
string createIndexSql = "CREATE ";
if (index.Unique)
{
createIndexSql += "UNIQUE ";
}
createIndexSql += "INDEX " + QuoteTable(index.Name) + " ON " + QuoteTable(schema.MappedName) + " (";
createIndexSql += string.Join(",", index.FieldsWithOrder.Select(field => QuoteField(field.Item1.MappedName) + " " + (field.Item2 == SortOrder.Ascending ? "ASC" : "DESC")));
createIndexSql += ")";
dataProvider.ExecuteSql(createIndexSql, null);
}
}
public abstract void CreateOrUpdateTable(TableSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider);