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);