public SqlExpressionTranslator(SqlDialect sqlDialect, OrmSchema schema, string tableAlias)
{
_schema = schema;
_tableAlias = tableAlias;
_sqlDialect = sqlDialect;
_subQueries.Push(new SubQuery());
}
public static HashSet<OrmSchema.Relation> FindRelations(LambdaExpression expression, OrmSchema schema)
{
var finder = new LambdaRelationFinder(schema.Repository.Context);
finder.Visit(expression.Body);
return finder._relations;
}
private static LambdaExpression CreateToManyFilterExpression(OrmSchema.Relation relation, Expression localExpression, LambdaExpression filterLambda, ParameterExpression lambdaParameter)
{
var expression = Expression.Equal(
Expression.MakeMemberAccess(localExpression, relation.LocalField.FieldInfo.AsMember),
Expression.MakeMemberAccess(lambdaParameter, relation.ForeignField.FieldInfo.AsMember)
);
if (filterLambda != null)
expression = Expression.AndAlso(filterLambda.Body, expression);
return Expression.Lambda(expression, lambdaParameter);
}
public bool DeleteObject(SerializedEntity o, OrmSchema schema)
{
using (var bucket = GetBucket(schema))
{
for (int i = 0; i < bucket.Objects.Count; i++)
{
if (schema.PrimaryKeys.All(primaryKey => Equals(bucket.Objects[i][primaryKey.MappedName], o[primaryKey.MappedName])))
{
bucket.IndexedObjects.Remove(bucket.IndexedObjects.First(kv => kv.Value == bucket.Objects[i]).Key);
bucket.Objects.RemoveAt(i);
return true;
}
}
}
return false;
}
public static HashSet<OrmSchema.Relation> FindRelations(IEnumerable<LambdaExpression> expressions, OrmSchema schema)
{
if (expressions == null)
return null;
HashSet<OrmSchema.Relation> relations = null;
foreach (var lambdaExpression in expressions)
{
if (relations == null)
relations = new HashSet<OrmSchema.Relation>(FindRelations(lambdaExpression,schema));
else
relations.UnionWith(FindRelations(lambdaExpression, schema));
}
return relations;
}
protected Repository(Type type, Vx.Context context)
{
Context = context;
Schema = new OrmSchema(type, this);
}
protected internal IEnumerable<object> GetRelationObjects(QuerySpec filter, OrmSchema.Relation parentRelation, object parentObject)
{
var objects = from o in DataProvider.GetObjects(filter.Native,Schema)
select Vx.WithLoadedRelations(Schema.UpdateObject(Activator.CreateInstance(Schema.ObjectType), o),Schema.DatasetRelations) ;
if (parentRelation?.ReverseRelation != null)
objects = from o in objects select parentRelation.ReverseRelation.SetField(o, parentObject);
if (filter.Code != null)
objects = from o in objects where filter.Code.IsFilterMatch(o) select o;
return objects;
}
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider)
{
const string longTextType = "TEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Integer, ColumnType = "INTEGER"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.FloatingPoint, ColumnType = "REAL"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "LONGBLOB"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
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.TypeInspector.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 };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable || field.PrimaryKey)
part += " NOT";
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(' " + 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 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 virtual FieldProperties GetFieldProperties(OrmSchema schema, OrmSchema.Field field)
{
var fieldProperties = new FieldProperties();
if (Regex.IsMatch(field.FieldName, IsFieldIndexedRegex, RegexOptions.IgnoreCase))
fieldProperties.Indexed = true;
string pkName = PrimaryKeyName.Replace(CLASS_NAME, schema.ObjectType.Name);
if (field.FieldName.Equals(pkName, StringComparison.OrdinalIgnoreCase))
{
fieldProperties.PrimaryKey = true;
fieldProperties.AutoIncrement = UseAutoIncrement && field.FieldInfo.TypeInspector.Is(TypeFlags.Integer);
fieldProperties.Indexed = false;
}
return fieldProperties;
}
public virtual OrmSchema.Field GetRelationField(OrmSchema.Relation relation)
{
//if (relation.LocalSchema.PrimaryKeys.Length < 1 || relation.ForeignSchema.PrimaryKeys.Length < 1)
// return null;
string relationKeyName = (relation.IsToOne ? ManyToOneLocalKeyName : OneToManyForeignKeyName)
.Replace(RELATION_CLASS_PRIMARYKEY, relation.ForeignSchema.PrimaryKeys.Length > 0 ? relation.ForeignSchema.PrimaryKeys[0].FieldName : "?")
.Replace(RELATION_CLASS_NAME, relation.ForeignSchema.ObjectType.Name)
.Replace(CLASS_PRIMARYKEY, relation.LocalSchema.PrimaryKeys.Length > 0 ? relation.LocalSchema.PrimaryKeys[0].FieldName : "?")
.Replace(CLASS_NAME, relation.LocalSchema.ObjectType.Name);
return relation.IsToOne ? relation.LocalSchema.FieldsByFieldName[relationKeyName] : relation.ForeignSchema.FieldsByFieldName[relationKeyName];
}
public void Purge(OrmSchema schema)
{
using (var bucket = GetBucket(schema))
{
bucket.Purge();
}
}
public QuerySpec CreateQuerySpec(FilterSpec filter, ScalarSpec expression, SortOrderSpec sortOrder, int? skip, int? take, OrmSchema schema)
{
throw new NotSupportedException();
}
public IEnumerable<SerializedEntity> GetObjectsWithPrefetch(INativeQuerySpec filter, OrmSchema schema, IEnumerable<OrmSchema.Relation> prefetchRelations, out IEnumerable<Dictionary<OrmSchema.Relation, SerializedEntity>> relatedEntities)
{
throw new NotSupportedException();
}
public CompositeKey(OrmSchema schema, SerializedEntity o)
{
_keyValues = schema.PrimaryKeys.ToDictionary(pk => pk.MappedName, pk => o[pk.MappedName]);
}
public bool CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes)
{
return true; // NOP
}
private StorageBucket.BucketAccessor GetBucket(OrmSchema schema)
{
return (_buckets[schema] ?? (_buckets[schema] = new StorageBucket())).Accessor();
}
public ObjectWriteResult WriteObject(SerializedEntity o, bool createNew, OrmSchema schema)
{
var result = new ObjectWriteResult();
using (var bucket = GetBucket(schema))
{
if (createNew)
{
foreach (var incrementKey in schema.IncrementKeys.Where(incrementKey => o[incrementKey.MappedName].Convert<long>() == 0))
{
o[incrementKey.MappedName] = bucket.NextIncrementCounter(incrementKey.FieldName).Convert(incrementKey.FieldType);
result.OriginalUpdated = true;
}
var storedObject = o.AsDictionary();
bucket.Objects.Add(storedObject);
bucket.IndexedObjects[new CompositeKey(schema,o)] = storedObject;
result.Added = true;
result.Success = true;
}
else
{
if (schema.PrimaryKeys.Length > 0)
{
for (int i = 0; i < bucket.Objects.Count; i++)
{
if (schema.PrimaryKeys.All(primaryKey => Equals(bucket.Objects[i][primaryKey.MappedName], o[primaryKey.MappedName])))
{
var compositeKey = new CompositeKey(schema, o);
var storedObject = o.AsDictionary();
bucket.Objects[i] = storedObject;
bucket.IndexedObjects[compositeKey] = storedObject;
result.Success = true;
break;
}
}
}
else
{
result.Success = false;
}
}
}
return result;
}
public SerializedEntity ReadObject(Dictionary<string,object> keys, OrmSchema schema)
{
using (var bucket = GetBucket(schema))
{
var compositeKey = new CompositeKey(keys);
if (!bucket.IndexedObjects.ContainsKey(compositeKey))
return null;
var storedObject = bucket.IndexedObjects[compositeKey];
return new SerializedEntity(storedObject);
}
}
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider)
{
const string longTextType = "TEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "BIT"},
new {Flags = TypeFlags.Integer8, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Integer16, ColumnType = "SMALLINT"},
new {Flags = TypeFlags.Integer32, ColumnType = "INT"},
new {Flags = TypeFlags.Integer64, ColumnType = "BIGINT"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.Double, ColumnType = "FLOAT"},
new {Flags = TypeFlags.Single, ColumnType = "REAL"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "IMAGE"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
string[] tableNameParts = schema.MappedName.Split('.');
string tableSchemaName = tableNameParts.Length == 1 ? "dbo" : tableNameParts[0];
string tableName = tableNameParts.Length == 1 ? tableNameParts[0] : tableNameParts[1];
var existingColumns = dataProvider.ExecuteSqlReader("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=@schema and TABLE_NAME=@name",
new QueryParameterCollection(new { schema = tableSchemaName, name = tableName })).ToLookup(rec => rec["COLUMN_NAME"].ToString());
var tableExists = dataProvider.ExecuteSqlReader("select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=@schema and TABLE_NAME=@name",
new QueryParameterCollection(new {schema = tableSchemaName, name = tableName})).Select(rec => rec.First().Value.Convert<int>()).First() == 1;
var parts = new List<string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.TypeInspector.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 };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable || field.PrimaryKey)
part += " NOT";
part += " NULL";
if (field.AutoIncrement)
part += " IDENTITY(1,1)";
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 0)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (recreateTable && tableExists)
dataProvider.ExecuteSql("DROP TABLE " + QuoteTable(schema.MappedName), null);
if (parts.Any())
{
string sql = (createNew ? "CREATE TABLE " : "ALTER TABLE ") + QuoteTable(schema.MappedName);
sql += createNew ? " (" : " ADD ";
sql += string.Join(",", parts);
if (createNew)
sql += ")";
dataProvider.ExecuteSql(sql, null);
}
var existingIndexes = dataProvider.ExecuteSqlReader("SELECT ind.name as IndexName FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE ind.name is not null and ind.is_primary_key = 0 AND t.is_ms_shipped = 0 AND t.name=@tableName",
new QueryParameterCollection(new { tableName })).ToLookup(rec => rec["IndexName"].ToString());
foreach (var index in schema.Indexes)
{
if (existingIndexes["IX_" + index.Name].Any())
{
if (recreateIndexes || recreateTable)
dataProvider.ExecuteSql($"DROP INDEX {QuoteTable("IX_" + index.Name)} ON {QuoteTable(schema.MappedName)}", null);
else
continue;
}
string createIndexSql = $"CREATE INDEX {QuoteTable("IX_" + 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(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider dataProvider);
public override HashSet<OrmSchema.Relation> FindRelations(OrmSchema schema)
{
return LambdaRelationFinder.FindRelations(Expression, schema);
}
public IEnumerable<SerializedEntity> GetObjects(INativeQuerySpec querySpec, OrmSchema schema)
{
if (querySpec != null)
throw new NotSupportedException();
using (var bucket = GetBucket(schema))
{
return from o in bucket.Objects select new SerializedEntity(o);
}
}
private void PrepareForNewExpression(ParameterExpression rootIterator, string tableAlias, OrmSchema schema)
{
if (_rootIterator == null)
{
_rootIterator = rootIterator;
_relationAliases[rootIterator] = new Dictionary<object, string> { { schema, tableAlias } };
}
_metaData[rootIterator] = new ExpressionMetaData
{
Relation = null,
Iterator = rootIterator,
Schema = schema
};
_rootIterators[rootIterator] = _rootIterator; // make additional root iterators equivalent to first one
}
public object GetScalar(Aggregate aggregate, INativeQuerySpec nativeQuerySpec, OrmSchema schema)
{
throw new NotSupportedException();
}
public bool DeleteObjects(INativeQuerySpec filter, OrmSchema schema)
{
throw new NotSupportedException();
}
public override void CreateOrUpdateTable(OrmSchema schema, bool recreateTable, bool recreateIndexes, SqlDataProvider datProvider)
{
const string longTextType = "LONGTEXT";
var columnMappings = new[]
{
new {Flags = TypeFlags.Boolean, ColumnType = "BOOLEAN"},
new {Flags = TypeFlags.Byte, ColumnType = "TINYINT UNSIGNED"},
new {Flags = TypeFlags.SByte, ColumnType = "TINYINT"},
new {Flags = TypeFlags.Int16, ColumnType = "SMALLINT"},
new {Flags = TypeFlags.UInt16, ColumnType = "SMALLINT UNSIGNED"},
new {Flags = TypeFlags.Int32, ColumnType = "INT"},
new {Flags = TypeFlags.UInt32, ColumnType = "INT UNSIGNED"},
new {Flags = TypeFlags.Int64, ColumnType = "BIGINT"},
new {Flags = TypeFlags.UInt64, ColumnType = "BIGINT UNSIGNED"},
new {Flags = TypeFlags.Decimal, ColumnType = "DECIMAL({0},{1})"},
new {Flags = TypeFlags.Double, ColumnType = "DOUBLE"},
new {Flags = TypeFlags.Single, ColumnType = "FLOAT"},
new {Flags = TypeFlags.String, ColumnType = "VARCHAR({0})"},
new {Flags = TypeFlags.Array | TypeFlags.Byte, ColumnType = "LONGBLOB"},
new {Flags = TypeFlags.DateTime, ColumnType = "DATETIME"}
};
if (recreateTable)
recreateIndexes = true;
var existingColumns = datProvider.ExecuteSqlReader("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=DATABASE() and TABLE_NAME=@name", new QueryParameterCollection(new { name = schema.MappedName })).ToLookup(rec => rec["COLUMN_NAME"].ToString());
var parts = new List<string>();
bool createNew = true;
foreach (var field in schema.Fields)
{
var columnMapping = columnMappings.FirstOrDefault(mapping => field.FieldInfo.TypeInspector.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 };
var part = string.Format("{0} {1}", QuoteField(field.MappedName), string.Format(columnMapping.ColumnType, field.ColumnSize, field.ColumnScale));
if (!field.ColumnNullable)
part += " NOT";
part += " NULL";
// if (field.PrimaryKey)
// part += " PRIMARY KEY";
if (field.AutoIncrement)
part += " AUTO_INCREMENT";
parts.Add(part);
}
if (parts.Any() && schema.PrimaryKeys.Length > 0 && createNew)
{
parts.Add("PRIMARY KEY (" + string.Join(",", schema.PrimaryKeys.Select(pk => QuoteField(pk.MappedName))) + ")");
}
if (!parts.Any())
return;
string sql = (createNew ? "CREATE TABLE " : "ALTER TABLE ") + QuoteTable(schema.MappedName);
if (createNew)
sql += " (";
if (createNew)
sql += string.Join(",", parts);
else
sql += string.Join(",", parts.Select(s => "ADD COLUMN " + s));
if (createNew)
sql += ")";
datProvider.ExecuteSql(sql, null);
}
