/// <summary> /// 获取对应表与主表关系 /// </summary> /// <param name="tableName">表名</param> /// <returns>关系集合</returns> public IList <DbRelation> GetParentRelations(string tableName) { string sql = "SELECT c2.table_name, c1.constraint_name, c1.r_constraint_name, c1.status" + " FROM user_constraints c1, user_constraints c2" + " WHERE c1.table_name = '" + tableName + "'" + " AND c1.constraint_type = 'R'" + " AND c2.constraint_name = c1.r_constraint_name" + " AND c2.constraint_type IN ('P', 'U')" + " ORDER BY c2.table_name, c1.constraint_name"; DataSet ds = new DataSet(); using (OracleConnection conn = new OracleConnection(DataBaseHelper.ConnectionString)) { conn.Open(); OracleDataAdapter da = new OracleDataAdapter(sql, conn); da.Fill(ds, "ParentRelations"); conn.Close(); } IList <DbRelation> relations = new List <DbRelation>(); foreach (DataRow dr in ds.Tables["ParentRelations"].Rows) { DbRelation rlt = new DbRelation(); rlt.ChildConstraintName = dr["constraint_name"].ToString(); rlt.ParentConstraintName = dr["r_constraint_name"].ToString(); rlt.ParentTableName = dr["table_name"].ToString(); relations.Add(rlt); } return(relations); }
public override void Delete(DbRelation relation) { if (!IsAuthenticated) throw AuthException.NotLoggedIn(); _dataService.Delete(relation); }
public DbRelation Copy(bool includeId = false) { var rel = new DbRelation(FromId, Identifier, ToId, RelationType, SortNum); if (includeId) rel.Id = Id; return rel; }
/// <summary> /// 获取对应表与主表关系 /// </summary> /// <param name="tableName">表名</param> /// <returns>关系集合</returns> public IList <DbRelation> GetParentRelations(string tableName) { string sql = "SELECT Object_name(fk.referenced_object_id) AS table_name,fk.name AS constraint_name,fk.name AS r_constraint_name,tbl.name AS comments" + " FROM sys.tables AS tbl" + " INNER JOIN sys.foreign_keys AS fk ON fk.parent_object_id = tbl.object_id" + " WHERE tbl.name = '" + tableName + "'"; DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(DataBaseHelper.ConnectionString)) { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.Fill(ds, "ParentRelations"); conn.Close(); } IList <DbRelation> relations = new List <DbRelation>(); foreach (DataRow dr in ds.Tables["ParentRelations"].Rows) { DbRelation rlt = new DbRelation(); rlt.ChildConstraintName = dr["constraint_name"].ToString(); rlt.ParentConstraintName = dr["r_constraint_name"].ToString(); rlt.ParentTableName = dr["table_name"].ToString(); relations.Add(rlt); } return(relations); }
public object Save(object obj) { if (obj == null || ProxyMapper.IsProxy(obj)) { return(obj); } var type = obj.GetType(); var container = GetObjectsContainerItemForType(type); var item = new DbItem(); DataService.Insert(item); var relation = new DbRelation { FromId = container.Id, Identifier = type.Name, // This can be anything, really... ToId = item.Id }; DataService.Insert(relation); return(ProxyMapper.Save(obj, item.Id, this)); }
public override void Delete(DbRelation relation) { var request = new ObjectTcpMessage <DbRelation>(TcpRequestType.DeleteRelation); request.Add(relation); Client.SendAndReceive(request); }
private static SelectStatement CreateSelectFromLevelQuery(IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int level, LevelQuerySelectList itemsToSelect) { if (level > Settings.GenericHierarchicalQueryExecutorMaxLevel) throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Messages.GenericHierarchicalQueryExecutor_MaxLevelReachedFormat, Settings.GenericHierarchicalQueryExecutorMaxLevel)); // Target level table must be child table because that is the table which is used in leafFilter. // If leaf filter is used then the we must ensure that proper table name is used for target table. IDbTable targetLevelTable; if (leafFilter != null && !leafFilter.IsEmpty) targetLevelTable = recursiveRelation.Child; else targetLevelTable = rootEntity.Table.Clone("L" + level); SelectStatement selectFromTargetLevel = new SelectStatement(targetLevelTable); CreateSelectListItems(itemsToSelect, targetLevelTable, selectFromTargetLevel); if (leafFilter != null && !leafFilter.IsEmpty) selectFromTargetLevel.Where.Add(leafFilter); IDbTable prevLevel = targetLevelTable; for (int parentLevel = level - 1; parentLevel >= 0; parentLevel--) { IDbTable nextLevel = rootEntity.Table.Clone("L" + parentLevel); DbRelation joinLevels = JoinLevelsInSubTree(prevLevel, nextLevel, recursiveRelation); selectFromTargetLevel.Relations.Add(joinLevels, false, false); prevLevel = nextLevel; } IDbTable subTreeRoot = prevLevel; foreach (IDbColumn rootPkPart in subTreeRoot.PrimaryKey) selectFromTargetLevel.Where.Add(rootPkPart, rootEntity.GetField(rootPkPart)); return selectFromTargetLevel; }
public override void Delete(DbRelation relation) { lock (_lockObj) { _dataService.Delete(relation); ClearRelationCache(relation.FromId); } }
public override void Delete(DbRelation relation) { if (!IsAuthenticated) { throw AuthException.NotLoggedIn(); } _dataService.Delete(relation); }
public override void Delete(DbRelation relation) { if (relation.Id <= 0) return; Execute(string.Format("DELETE FROM " + _relationsTable + " WHERE id = {0}", AsValueNullable(relation.Id))); OnRelationRemoved(relation); }
public override void Insert(DbRelation relation) { lock (_lockObj) { _dataService.Insert(relation); var list = GetCacheRelationsByParent(relation.FromId); list.AddLast(relation); } }
public bool Compare(DbRelation designerFK) { return (_db.Application.Name == designerFK.DbSchemeCommit.Application.Name && SourceTable.Name == designerFK.SourceTable.Name && SourceColumn == designerFK.SourceColumn.Name && TargetTable.Name == designerFK.TargetTable.Name && TargetColumn == designerFK.TargetColumn.Name); }
public DbRelation Copy(bool includeId = false) { var rel = new DbRelation(FromId, Identifier, ToId, RelationType, SortNum); if (includeId) { rel.Id = Id; } return(rel); }
public IList <DbRelation> GetRelations(DbTable table) { Hashtable handledrelations; List <DbRelation> result = new List <DbRelation>(); // 'Straight' relations: using (OracleCommand cmd = new OracleCommand(OracleQueries.RetrieveRelations, connection)) { handledrelations = new Hashtable(); DbRelation rel = null; cmd.Parameters.Add(new OracleParameter(":Owner", table.Schema.Name)); cmd.Parameters.Add(new OracleParameter(":TableName", table.Name)); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string relname = reader.GetString(0); if (!handledrelations.Contains(reader.GetString(0))) { rel = new DbRelation(relname, table, new DbTable(new DbSchema(reader.GetString(5)), reader.GetString(6), false), "One-To-Many", false); handledrelations.Add(relname, rel); result.Add(rel); } rel.FromColumns.Add(reader.GetString(3)); rel.ToColumns.Add(reader.GetString(7)); } } // 'Reverse' relations: using (OracleCommand cmd = new OracleCommand(OracleQueries.RetrieveReverseRelations, connection)) { handledrelations = new Hashtable(); DbRelation rel = null; cmd.Parameters.Add(new OracleParameter(":Owner", table.Schema.Name)); cmd.Parameters.Add(new OracleParameter(":TableName", table.Name)); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string relname = reader.GetString(0); if (!handledrelations.Contains(reader.GetString(0))) { rel = new DbRelation(relname, table, new DbTable(new DbSchema(reader.GetString(1)), reader.GetString(2), false), "One-To-Many", true); handledrelations.Add(relname, rel); result.Add(rel); } rel.FromColumns.Add(reader.GetString(7)); rel.ToColumns.Add(reader.GetString(3)); } } return(result); }
public override void Delete(DbRelation relation) { if (relation.Id <= 0) { return; } Execute(string.Format("DELETE FROM " + _relationsTable + " WHERE id = {0}", AsValueNullable(relation.Id))); OnRelationRemoved(relation); }
public override void Delete(DbRelation relation) { lock (_lockObject) { var relationInList = _relations.FirstOrDefault(i => i.Id == relation.Id); if (relationInList == null) return; _relations.Remove(relationInList); } OnRelationRemoved(relation); }
public override void Insert(DbRelation relation) { var request = new ObjectTcpMessage <DbRelation>(TcpRequestType.InsertRelation); request.Add(relation); var response = Client.SendAndReceive <DbRelation>(request); var responseRelation = response.Item; if (responseRelation != null) { relation.Id = responseRelation.Id; } }
public TestDBWord() { //_primeService = new PrimeService(); defaultDbWord = new DbWord { Word = "test", SynsetId = "b:fzf4687", CreationDate = DateTime.Today }; defaultDbWord2 = new DbWord { Word = "test2", SynsetId = "b:vdqvqdv45", CreationDate = DateTime.Today }; defaultRelation = new DbRelation { WordSourceId = 1, WordTargetId = 2, RelationGroup = "Hyponym", TargetSynsetId = defaultDbWord2.SynsetId, CreationDate = DateTime.Today }; }
public override void Insert(DbRelation relation) { var id = ExecuteScalarAsInt(string.Format("INSERT into " + _relationsTable + "(from_id, identifier, to_id, relation_type, sort_num) values ({0},{1},{2},{3},{4}); select last_insert_id();", AsValueNullable(relation.FromId), AsValueNullable(relation.Identifier), AsValueNullable(relation.ToId), AsValueNullable(relation.RelationType), AsValueNullable(relation.SortNum))); if (relation.Id <= 0) { relation.Id = id; } OnRelationAdded(relation); }
public override void Insert(DbRelation relation) { lock (_lockObject) { if (relation.Id == 0) { relation.Id = _relationIdGenerator.Get(); } _relations.AddLast(relation); } OnRelationAdded(relation); }
/// <summary> /// Renders SQL ON clause. /// Eg: 'ON Countries.Id = Cities.IdCountry' /// </summary> /// <param name="rel">Relation to render as ON clause.</param> /// <param name="dbms">Target DBMS.</param> /// <param name="output">StringBuilder to which ON statement is appended.</param> private static void RenderOnStatement(DbRelation rel, DbmsType dbms, StringBuilder output) { output.Append("ON "); for (int fieldIdxInRelation = 0; fieldIdxInRelation < rel.ParentPrimaryKey.Length; fieldIdxInRelation++) { if (fieldIdxInRelation > 0) output.Append("AND "); rel.ParentPrimaryKey[fieldIdxInRelation].RenderFullName(dbms, output); output.Append(" = "); rel.ChildForeignKey[fieldIdxInRelation].RenderFullName(dbms, output); output.Append(" "); } }
public override void Delete(DbRelation relation) { lock (_lockObject) { var relationInList = _relations.FirstOrDefault(i => i.Id == relation.Id); if (relationInList == null) { return; } _relations.Remove(relationInList); } OnRelationRemoved(relation); }
private static DbRelation JoinLevelsInSubTree(IDbTable prevLevel, IDbTable nextLevel, DbRelation recursiveRelation) { int keyLen = recursiveRelation.ChildForeignKey.Length; IDbColumn[] parentPkCols = new IDbColumn[keyLen]; IDbColumn[] childFkCols = new IDbColumn[keyLen]; for (int keyPart = 0; keyPart < keyLen; keyPart++) { string pkPartName = recursiveRelation.ParentPrimaryKey[keyPart].ColumnName; parentPkCols[keyPart] = nextLevel.Columns.GetByColumnName(pkPartName); string fkPartName = recursiveRelation.ChildForeignKey[keyPart].ColumnName; childFkCols[keyPart] = prevLevel.Columns.GetByColumnName(fkPartName); } DbRelation joinLevels = new DbRelation(nextLevel, parentPkCols, prevLevel, childFkCols, null); return joinLevels; }
private void OnRelationRemoved(DbRelation relation) { if (relation == null || relation.ToId == null || relation.FromId != _parentId) { return; } lock (_lockObject) { var index = IndexOf(relation.ToId.Value); if (index >= 0) { RemoveAt(index); } } }
private void OnRelationAdded(DbRelation relation) { if (relation == null || relation.ToId == null || relation.FromId != _parentId) { return; } lock (_lockObject) { if (Contains(relation.ToId.Value)) { return; } base.Add(ProxyMapper.New <T>(relation.ToId.Value, _objectMapper, _proxyType)); } }
/// <summary> /// 设置主表关系的列信息 /// </summary> /// <param name="relation">主表关系</param> /// <returns>关系对象</returns> public DbRelation SetParentRelationColumns(DbRelation relation) { IList <string> columns = this.columnDao.GetParentRelationColumns(relation.ParentConstraintName, relation.ParentTable.Name); relation.ParentColumns.Clear(); foreach (string colname in columns) { foreach (DbColumn dc in relation.ParentTable.Columns) { if (colname == dc.Name) { relation.ParentColumns.Add(dc); break; } } } return(relation); }
private DbRelation GetRelationByQuery(string query) { DbRelation result = null; Execute(delegate(MySqlConnection connection) { var command = connection.CreateCommand(); command.CommandText = query; using (var reader = command.ExecuteReader()) { if (reader.Read()) { result = GetDbRelation(reader); } } }); return(result); }
/// <summary> /// 设置从表关系的列信息 /// </summary> /// <param name="relation">从表关系</param> /// <returns>关系对象</returns> public DbRelation SetChildRelationColumns(DbRelation relation) { IList <string> columns = this.columnDao.GetChildRelationColumns(relation.ChildConstraintName, relation.ChildTable.Name); relation.ChildColumns.Clear(); foreach (string colname in columns) { foreach (DbColumn dc in relation.ChildTable.Columns) { if (colname == dc.Name) { dc.IsForeignKey = true; dc.IsGenerateField = dc.IsPrimaryKey; relation.ChildColumns.Add(dc); break; } } } return(relation); }
public bool TryAddRelation(DbRelation relation) { if (!File.Exists(DbFile)) { throw new FileNotFoundException("no database"); } if (relation.WordSourceId == null || relation.RelationGroup == null || relation.TargetSynsetId == null) { throw new ArgumentNullException(); } using (SQLiteConnection cnn = SimpleDbConnection()) { try { //https://sql.sh/cours/insert-into string sql = "INSERT INTO `relation` (`wordSourceid`,`relationGroup`,`targetSynsetid`, `wordTargetid`,`creationDate` ) VALUES(@WordSourceId, @RelationGroup,@TargetSynsetId,@WordTargetId,date('now', 'localtime'));"; cnn.Query <DbWord>(sql, new { relation.WordSourceId, relation.RelationGroup, relation.TargetSynsetId, relation.WordTargetId }); return(true); } catch (SqlException ex) { if (ex.Number == 2601 || ex.Number == 2627) { return(false); } throw; } } }
public bool TryAddRelation(string wordSource, string synsetTargetId, string relationGroup) { if (!TryGetWord(wordSource, out DbWord dbwordsource)) { throw new InvalidOperationException(); } if (!TryGetWord(synsetTargetId, out DbWord dbwordtarget, true)) { TryAddWord(null, synsetTargetId); _synsetIdToSearch.Add(synsetTargetId); } DbRelation relation = new DbRelation() { WordSourceId = dbwordsource.WordId, WordSource = dbwordtarget, RelationGroup = relationGroup, TargetSynsetId = synsetTargetId, WordTargetId = dbwordtarget.WordId, WordTarget = dbwordtarget }; return(TryAddRelation(relation)); }
private static int CountWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // Stop condition: final level reached (tree depth is measured). int totalCount = 0; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Count); DataTable results = countAtLevel.Execute(conn); totalCount += Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture); reachedEndLevel = (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return totalCount; }
private static void RenderJoinTable(DbmsType dbms, StringBuilder from, DbRelation currRelation, IDbTable currTable, bool useLeftJoin) { AppendJoinMode(from, useLeftJoin); AppendTableNameAndAlias(dbms, from, currTable); RenderOnStatement(currRelation, dbms, from); }
/// <summary>Counts rows in the hierarchy under the current entity.</summary> /// <param name="conn">Connection-transcation context to use.</param> /// <param name="rootEntity">Root entity.</param> /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param> /// <param name="leafFilter">The search condition which restricts the rows counted. Often a criteria which filters out deactivated records.</param> /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that counting starts at current entity, /// one indicates that counting starts at the level directly beneath it etc.</param> /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that counting ends at current entity, /// one indicates that counting ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param> /// <returns>Total row count in the hierarchy.</returns> public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); int totalCount; if (SearchCondition.IsNullOrEmpty(leafFilter)) totalCount = CountAll(conn, rootEntity, recursiveRelation, beginAtLevel, endAtLevel); else totalCount = CountWithLeafFilter(conn, rootEntity, recursiveRelation, leafFilter, beginAtLevel, endAtLevel); return totalCount; }
public void SaveChanges(DbSchemeCommit SchemeCommit) { DBEntities e = COREobject.i.Context; foreach (DbTable schemeTable in SchemeCommit.Tables) { IEnumerable <DbTable> removeTables = SchemeCommit.Tables.Where(x1 => !e.DbTables.Any(x2 => x2.Id == x1.Id)); e.DbTables.Except <DbTable>(removeTables); //maže všechny tabulky, které se už nenachází na schématu uživatele if (e.DbTables.SingleOrDefault(x => x.Id == schemeTable.Id) == null) //pokud je ve schématu uživatele vytvořena nová tabulka { e.DbTables.Add(schemeTable); } else { DbTable DatabaseTable = e.DbTables.SingleOrDefault(x => x.Id == schemeTable.Id); if (DatabaseTable.Name != schemeTable.Name) { DatabaseTable.Name = schemeTable.Name; } foreach (DbColumn schemeColumn in schemeTable.Columns) { IEnumerable <DbColumn> removeColumns = schemeTable.Columns.Where(x1 => !DatabaseTable.Columns.Any(x2 => x2.Id == x1.Id)); DatabaseTable.Columns.Except <DbColumn>(removeColumns); //maže všechny sloupce tabulky, které se už nenachází na schématu uživatele if (DatabaseTable.Columns.SingleOrDefault(x => x.Id == schemeColumn.Id) == null) //pokud je ve schématu uživatele vytvořen nový sloupec { DatabaseTable.Columns.Add(schemeColumn); } else { DbColumn DatabaseColumn = DatabaseTable.Columns.SingleOrDefault(x => x.Id == schemeColumn.Id); if (DatabaseColumn.Name != schemeColumn.Name) { DatabaseColumn.Name = schemeColumn.Name; } if (DatabaseColumn.DisplayName != schemeColumn.DisplayName) { DatabaseColumn.DisplayName = schemeColumn.DisplayName; } if (DatabaseColumn.PrimaryKey != schemeColumn.PrimaryKey) { DatabaseColumn.PrimaryKey = schemeColumn.PrimaryKey; } if (DatabaseColumn.Type != schemeColumn.Type) { DatabaseColumn.Type = schemeColumn.Type; } if (DatabaseColumn.Unique != schemeColumn.Unique) { DatabaseColumn.Unique = schemeColumn.Unique; } if (DatabaseColumn.AllowNull != schemeColumn.AllowNull) { DatabaseColumn.AllowNull = schemeColumn.AllowNull; } if (DatabaseColumn.ColumnLength != schemeColumn.ColumnLength) { DatabaseColumn.ColumnLength = schemeColumn.ColumnLength; } if (DatabaseColumn.ColumnLengthIsMax != schemeColumn.ColumnLengthIsMax) { DatabaseColumn.ColumnLengthIsMax = schemeColumn.ColumnLengthIsMax; } if (DatabaseColumn.DefaultValue != schemeColumn.DefaultValue) { DatabaseColumn.DefaultValue = schemeColumn.DefaultValue; } } } foreach (DbIndex schemeIndex in schemeTable.Indices) { IEnumerable <DbIndex> removeIndeces = schemeTable.Indices.Where(x1 => !DatabaseTable.Indices.Any(x2 => x2.Id == x1.Id)); DatabaseTable.Indices.Except <DbIndex>(removeIndeces); //maže všechny indexy tabulky, které se už nenachází na schématu uživatele if (DatabaseTable.Indices.SingleOrDefault(x => x.Id == schemeIndex.Id) == null) //pokud je ve schématu uživatele vytvořen nový index { DatabaseTable.Indices.Add(schemeIndex); } else { DbIndex databaseIndex = DatabaseTable.Indices.SingleOrDefault(x => x.Id == schemeIndex.Id); if (databaseIndex.ColumnNames != schemeIndex.ColumnNames) { databaseIndex.ColumnNames = schemeIndex.ColumnNames; } if (databaseIndex.Name != schemeIndex.Name) { databaseIndex.Name = schemeIndex.Name; } if (databaseIndex.Unique != schemeIndex.Unique) { databaseIndex.Unique = schemeIndex.Unique; } } } } } foreach (DbRelation schemeRelation in SchemeCommit.Relations) { IEnumerable <DbRelation> removeRelations = SchemeCommit.Relations.Where(x1 => !e.DbRelation.Any(x2 => x2.Id == x1.Id)); e.DbRelation.Except <DbRelation>(removeRelations); //maže všechny vztahy, které se už nenachází ve schématu uživatele if (e.DbRelation.SingleOrDefault(x => x.Id == schemeRelation.Id) == null) //pokud je ve schématu uživatele vytvořen nový vztah { e.DbRelation.Add(schemeRelation); } else { DbRelation databaseRelation = e.DbRelation.SingleOrDefault(x => x.Id == schemeRelation.Id); databaseRelation.SourceTableId = schemeRelation.SourceTableId; databaseRelation.TargetTableId = schemeRelation.TargetTableId; databaseRelation.SourceColumnId = schemeRelation.SourceColumnId; databaseRelation.TargetColumnId = schemeRelation.TargetColumnId; databaseRelation.Type = schemeRelation.Type; } } foreach (DbView schemeView in SchemeCommit.Views) { IEnumerable <DbView> removeRelations = SchemeCommit.Views.Where(x1 => !e.DbView.Any(x2 => x2.Id == x1.Id)); e.DbView.Except <DbView>(removeRelations); //maže všechny pohledy, které se už nenachází ve schématu uživatele if (e.DbView.SingleOrDefault(x => x.Id == schemeView.Id) == null) //pokud je ve schématu uživatele vytvořen nový pohled { e.DbView.Add(schemeView); } else { DbView databaseView = e.DbView.SingleOrDefault(x => x.Id == schemeView.Id); if (databaseView.Name != schemeView.Name) { databaseView.Name = schemeView.Name; } if (databaseView.Query != schemeView.Query) { databaseView.Query = schemeView.Query; } } } }
private void OnRelationAdded(DbRelation relation) { }
/// <summary>Retrieves the maximum column value in the hierarchy under the current entity.</summary> /// <param name="conn">Connection-transcation context to use.</param> /// <param name="rootEntity">Root entity.</param> /// <param name="column">Column to compare. Must belong to the current entity's table.</param> /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param> /// <param name="leafFilter">The search condition which restricts the rows processed. Often a criteria which filters out deactivated records.</param> /// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that comparison starts at current entity, /// one indicates that comparison starts at the level directly beneath it etc.</param> /// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that comparison ends at current entity, /// one indicates that comparison ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param> /// <returns>Maximum value or <see cref="DBNull.Value"/>.</returns> public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); object maximum; if (SearchCondition.IsNullOrEmpty(leafFilter)) maximum = MaxOfAll(conn, rootEntity, column, recursiveRelation, beginAtLevel, endAtLevel); else maximum = MaxWithLeafFilter(conn, rootEntity, column, recursiveRelation, leafFilter, beginAtLevel, endAtLevel); return maximum; }
private static object MaxOfAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel) { // Stop condition: count returns zero. object maximum = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); bool reachedEndLevel = false; int level = beginAtLevel; do { object levelMax; int nodeCount; FetchMaxFromColumnAndCountAllNodesAtLevel(conn, rootEntity, recursiveRelation, level, column.ColumnName, out levelMax, out nodeCount); maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable); reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } if (maximum == null) maximum = DBNull.Value; return maximum; }
private static int CountAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel) { // Stop condition: count returns zero. int totalCount = 0; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Count); DataTable results = countAtLevel.Execute(conn); int nodeCount = Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture); totalCount += nodeCount; reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return totalCount; }
private static object SumAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel) { // Stop condition: count returns zero. object totalSum = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default); sumAtLevel.SelectList.Clear(); IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName); sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum")); sumAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount")); DataTable results = sumAtLevel.Execute(conn); object levelSum = results.Rows[0]["levelSum"]; int nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture); totalSum = SumNullableValues(column, totalSum, levelSum); reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return totalSum; }
public IList <DbRelation> GetRelations(DbTable table) { // Source of queries: // http://msdn.microsoft.com/en-us/library/aa175805(SQL.80).aspx Hashtable handledrelations; List <DbRelation> result = new List <DbRelation>(); using (SqlCommand cmd = new SqlCommand(String.Format("USE \"{0}\"", table.Schema.Name), connection)) { cmd.ExecuteNonQuery(); } // 'Straight' relations: using (SqlCommand cmd = new SqlCommand(MSSqlQueries.RetrieveRelations, connection)) { handledrelations = new Hashtable(); DbRelation rel = null; cmd.Parameters.Add(new SqlParameter("@Schema", table.Name.Split(new char[] { '.' }, 2)[0])); cmd.Parameters.Add(new SqlParameter("@Table", table.Name.Split(new char[] { '.' }, 2)[1])); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string relname = reader.GetString(0); if (!handledrelations.Contains(reader.GetString(0))) { rel = new DbRelation(relname, table, new DbTable(new DbSchema(reader.GetString(3)), reader.GetString(4), false), "One-To-Many", false); handledrelations.Add(relname, rel); result.Add(rel); } rel.FromColumns.Add(reader.GetString(11)); rel.ToColumns.Add(reader.GetString(5)); } } } // 'Reverse' relations: using (SqlCommand cmd = new SqlCommand(MSSqlQueries.RetrieveReverseRelations, connection)) { handledrelations = new Hashtable(); DbRelation rel = null; cmd.Parameters.Add(new SqlParameter("@Schema", table.Name.Split(new char[] { '.' }, 2)[0])); cmd.Parameters.Add(new SqlParameter("@Table", table.Name.Split(new char[] { '.' }, 2)[1])); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string relname = reader.GetString(0); if (!handledrelations.Contains(reader.GetString(0))) { rel = new DbRelation(relname, table, new DbTable(new DbSchema(reader.GetString(9)), reader.GetString(10), false), "One-To-Many", true); handledrelations.Add(relname, rel); result.Add(rel); } rel.FromColumns.Add(reader.GetString(5)); rel.ToColumns.Add(reader.GetString(11)); } } } return(result); }
private static int DetermineTreeDepthInternal(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation) { EnsureRecursiveRelation(recursiveRelation); int depth = 0; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); bool reachedEndLevel = false; do { SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, depth, LevelQuerySelectList.Count); DataTable results = countAtLevel.Execute(conn); int nodeCount = Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture); if (nodeCount > 0) depth++; else reachedEndLevel = true; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return depth; }
private static DataTable SelectAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel) { // Stop condition: select returns nothing. DataTable mergedData = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement fetchLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.AllColumns); DataTable levelData = fetchLevel.Execute(conn); if (levelData.Rows.Count > 0) AppendData(levelData, ref mergedData); reachedEndLevel = (levelData.Rows.Count == 0) || (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return mergedData; }
private static DataTable SelectWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // Stop condition: final level reached (tree depth is measured). // It's possible that at some intermediate levels no records satisfy the specified criteria. // Nevertheless, the algorithm must proceed to the next level where it might find matching records. // This behavior simulates the behavior of recursive CTEs. DataTable mergedData = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement fetchLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.AllColumns); DataTable levelData = fetchLevel.Execute(conn); if (levelData.Rows.Count > 0) AppendData(levelData, ref mergedData); reachedEndLevel = (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return mergedData; }
/// <summary>Selects rows in the hierarchy under the current entity.</summary> /// <param name="conn">Connection-transcation context to use.</param> /// <param name="rootEntity">Root entity.</param> /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param> /// <param name="leafFilter">The search condition which restricts the rows returned. Often a criteria which filters out deactivated records.</param> /// <param name="sorter">Order by.</param> /// <param name="sortOnDb">Specifies whether sorting will take place on database or in DataTable after data has been fetched.</param> /// <param name="beginAtLevel">Zero-based index of the first level to fetch. Zero indicates that fetching starts at current entity, /// one indicates that fetching starts at the level directly beneath it etc.</param> /// <param name="endAtLevel">Zero-based index of the last level to fetch. Zero indicates that fetching ends at current entity, /// one indicates that fetching ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param> /// <returns>Rows in the hierarchy under the current entity.</returns> public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); DataTable mergedData; if (SearchCondition.IsNullOrEmpty(leafFilter)) mergedData = SelectAll(conn, rootEntity, recursiveRelation, beginAtLevel, endAtLevel); else mergedData = SelectWithLeafFilter(conn, rootEntity, recursiveRelation, leafFilter, beginAtLevel, endAtLevel); if (mergedData == null) mergedData = rootEntity.Table.NewDataTable(); if (mergedData.Rows.Count > 0) mergedData = SortDataTable(mergedData, sorter); return mergedData; }
private static int GetEndLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int endAtLevel) { // Levels are zero-based indexes. Minimum depth for any tree is 1. Max level = depth - 1. int treeDepth = DetermineTreeDepthInternal(conn, rootEntity, recursiveRelation); int maxLevelInTree = treeDepth - 1; if (endAtLevel > maxLevelInTree) endAtLevel = maxLevelInTree; return endAtLevel; }
private static void EnsureRecursiveRelation(DbRelation recursiveRelation) { bool isRecursive = (recursiveRelation.Parent.TableName == recursiveRelation.Child.TableName); if (!isRecursive) throw new ArgumentException(Messages.GenericHierarchicalQueryExecutor_RecursiveRelationIsRequired, "recursiveRelation"); }
private static object SumWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // Stop condition: final level reached (tree depth is measured). object totalSum = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default); sumAtLevel.SelectList.Clear(); IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName); sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum")); DataTable results = sumAtLevel.Execute(conn); object levelSum = results.Rows[0]["levelSum"]; totalSum = SumNullableValues(column, totalSum, levelSum); reachedEndLevel = (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } return totalSum; }
/// <summary>Updates rows in the hierarchy under the current entity.</summary> /// <param name="conn">Connection-transcation context to use.</param> /// <param name="rootEntity">Root entity.</param> /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param> /// <param name="setExpressions">The list of columns to be updated and expressions that compute/contain the new values.</param> /// <param name="leafFilter">The search condition which limits the number of rows that are updated. Often a criteria which filters out deactivated records.</param> /// <param name="beginAtLevel">Zero-based index of the first level to update. Zero indicates that updating starts at current entity, /// one indicates that updating starts at the level directly beneath it etc.</param> /// <param name="endAtLevel">Zero-based index of the last level to update. Zero indicates that updating ends at current entity, /// one indicates that updating ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param> /// <returns>Number of rows affected.</returns> public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { EnsureRecursiveRelation(recursiveRelation); EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel); int totalRowsAffected; if (SearchCondition.IsNullOrEmpty(leafFilter)) totalRowsAffected = UpdateAll(conn, rootEntity, recursiveRelation, setExpressions, beginAtLevel, endAtLevel); else totalRowsAffected = UpdateWithLeafFilter(conn, rootEntity, recursiveRelation, setExpressions, leafFilter, beginAtLevel, endAtLevel); return totalRowsAffected; }
private static int UpdateWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // Stop condition: final level reached (tree depth is measured). // It's possible that at some intermediate levels no records satisfy the specified criteria. // Nevertheless, the algorithm must proceed to the next level where it might find matching records. // This behavior simulates the behavior of recursive CTEs. int totalRowsAffected = 0; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.BeginTransaction(); endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel); bool reachedEndLevel = false; int level = beginAtLevel; do { UpdateStatement updateAtLevel = CreateUpdateLevelStatement(rootEntity, recursiveRelation, setExpressions, leafFilter, level); totalRowsAffected += updateAtLevel.Execute(conn); reachedEndLevel = (level >= endAtLevel); level++; } while (!reachedEndLevel); if (isLocalConn) conn.CommitTransaction(); } finally { if (isLocalConn) conn.CloseConnection(); } return totalRowsAffected; }
private static int UpdateAll(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, int beginAtLevel, int endAtLevel) { // Stop condition: rowsAffected == 0 int totalRowsAffected = 0; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.BeginTransaction(); // We need to perform COUNT at least once to determine whether rowsAffacted returned by UPDATE can be trusted. RowsAffectedCredibility trustRowsAffected = RowsAffectedCredibility.NotDetermined; bool reachedEndLevel = false; int level = beginAtLevel; do { // Dependening on DBMS configuration, update statements may automatically return the number of rows affected. int rowsAffectedReturnedByEngine = UpdateAtLevel(conn, rootEntity, recursiveRelation, setExpressions, level); // We need to perform manual counting for as long as rows affected cannot be trusted. int? manualNodeCount = CountNodesAtLevelIfRowsAffectedCannotBeTrusted(conn, rootEntity, trustRowsAffected, recursiveRelation, level); // Evaluation which determines whether rows affected may be trusted takes place only once, ie. if not yet determined. bool evaluateRowsAffectedCredibility = (trustRowsAffected == RowsAffectedCredibility.NotDetermined) && (manualNodeCount.HasValue); if (evaluateRowsAffectedCredibility) { bool engineCountMatchesManualCount = (rowsAffectedReturnedByEngine == manualNodeCount.Value); trustRowsAffected = (engineCountMatchesManualCount) ? RowsAffectedCredibility.Trusted : RowsAffectedCredibility.Untrusted; } // Manual node count is null if we have determined that rows affected value returned by engine is credible. int rowsUpdatedInCurrentLevel = manualNodeCount ?? rowsAffectedReturnedByEngine; totalRowsAffected += rowsUpdatedInCurrentLevel; // Inspect stop condition before level variable is increased. reachedEndLevel = IsEndLevelReached(endAtLevel, level, rowsUpdatedInCurrentLevel); // Next level. level++; } while (!reachedEndLevel); if (isLocalConn) conn.CommitTransaction(); } finally { if (isLocalConn) conn.CloseConnection(); } return totalRowsAffected; }
private static object MaxWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel) { // Stop condition: final level reached (tree depth is measured). object maximum = null; bool isLocalConn = !conn.IsOpen; try { if (isLocalConn) conn.OpenConnection(); endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel); bool reachedEndLevel = false; int level = beginAtLevel; do { SelectStatement maxAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default); maxAtLevel.SelectList.Clear(); IDbColumn comparedColumn = maxAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName); maxAtLevel.SelectList.Add(AggregateFunctionFactory.Max(comparedColumn, "levelMax")); DataTable results = maxAtLevel.Execute(conn); object levelMax = results.Rows[0]["levelMax"]; maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable); reachedEndLevel = (level >= endAtLevel); level++; } while (!reachedEndLevel); } finally { if (isLocalConn) conn.CloseConnection(); } if (maximum == null) maximum = DBNull.Value; return maximum; }
private static int? CountNodesAtLevelIfRowsAffectedCannotBeTrusted(IConnectionProvider conn, IEntity rootEntity, RowsAffectedCredibility trustRowsAffected, DbRelation recursiveRelation, int level) { int? manualNodeCount = null; if (trustRowsAffected != RowsAffectedCredibility.Trusted) { SelectStatement countAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Count); DataTable countResults = countAtLevel.Execute(conn); manualNodeCount = Convert.ToInt32(countResults.Rows[0][0], CultureInfo.InvariantCulture); } return manualNodeCount; }
private static void FetchMaxFromColumnAndCountAllNodesAtLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, int level, string maxFromColumnName, out object levelMax, out int nodeCount) { SelectStatement maxAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default); maxAtLevel.SelectList.Clear(); IDbColumn comparedColumn = maxAtLevel.FromTable.Columns.GetByColumnName(maxFromColumnName); maxAtLevel.SelectList.Add(AggregateFunctionFactory.Max(comparedColumn, "levelMax")); maxAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount")); DataTable results = maxAtLevel.Execute(conn); levelMax = results.Rows[0]["levelMax"]; nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture); }
private static int UpdateAtLevel(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, int level) { UpdateStatement updateAtLevel = CreateUpdateLevelStatement(rootEntity, recursiveRelation, setExpressions, null, level); int rowsAffected = updateAtLevel.Execute(conn); return rowsAffected; }
private void OnRelationRemoved(DbRelation relation) { }
private static UpdateStatement CreateUpdateLevelStatement(IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int level) { // Leaf filter, if provided, must be rendered in UPDATE-WHERE clause, not in SELECT-WHERE clause. // Target table of the subquery will an automatically genereted name - which is not used in leaf filter. IDbTable table = rootEntity.Table; UpdateStatement updateAtLevel = new UpdateStatement(table); updateAtLevel.UpdateList = setExpressions; SelectStatement queryLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.PrimaryKey); foreach (IDbColumn pkPart in table.PrimaryKey) { IDbColumn subQueryPkPart = queryLevel.FromTable.Columns.GetByColumnName(pkPart.ColumnName); queryLevel.Where.Add(PredicateFactory.Compare(pkPart, "=", subQueryPkPart)); } updateAtLevel.Where.Add(PredicateFactory.Exists(queryLevel)); if (leafFilter != null && !leafFilter.IsEmpty) updateAtLevel.Where.Add(leafFilter); return updateAtLevel; }
/// <summary>Counts the number of levels in the hieararchy starting with the current entity.</summary> /// <param name="conn">Connection-transcation context to use.</param> /// <param name="rootEntity">Root entity.</param> /// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param> /// <returns>Sub-tree depth.</returns> public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation) { return DetermineTreeDepthInternal(conn, rootEntity, recursiveRelation); }
public JoinElement(TableElement leftTable, DbRelation relation) : base(leftTable.Query, relation.ToTable) { this.leftTable = leftTable; this.relation = relation; }