public static List<string> GetTablePrimaryFields(IDbConnection connection, string schema, string tableName) { var inf = InformationSchema(connection); List<string> primaryFields = new List<string>(); var columns = ((DbConnection)((WrappedConnection)connection).ActualConnection).GetSchema("Columns", new string[] { null, schema, tableName, null }); foreach (DataRow row in columns.Rows) { try { var isPrimaryKey = row["PRIMARY_KEY"] as Boolean?; if (isPrimaryKey == true) primaryFields.Add((string)row["COLUMN_NAME"]); } catch (Exception) { } } if (primaryFields.Count == 0) { var query = new SqlQuery().Select( "KCU.COLUMN_NAME") .From( inf + "TABLE_CONSTRAINTS AS TC INNER JOIN " + inf + "KEY_COLUMN_USAGE AS KCU " + "ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND " + "KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND " + "KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND " + "KCU.TABLE_NAME = TC.TABLE_NAME") .Where( new Criteria("TC.CONSTRAINT_TYPE") == "PRIMARY KEY" & new Criteria("KCU.TABLE_NAME") == tableName) .OrderBy( "KCU.ORDINAL_POSITION"); query.ForEach(connection, delegate(IDataReader reader) { primaryFields.Add(reader.GetString(0)); }); } return primaryFields; }
public static AuditLogListResponse List(string schema, AuditLogListRequest request) { #if COREFX var fld = Dependency.Resolve<IAuditLogRow>(); #else var fld = Dependency.Resolve<IAuditLogRow>(schema); #endif Authorization.ValidateLoggedIn(); var response = new AuditLogListResponse(); using (var connection = SqlConnections.NewByKey("Default")) { response.Entities = new List<Row>(); var row = ((Row)fld).CreateNew(); if (request.Sort == null || request.Sort.Length == 0) request.Sort = new SortBy[] { new SortBy(fld.DateField.Name, true) }; var query = new SqlQuery().From(row) .Select( (Field)fld.IdField, fld.EntityTypeIdField, fld.EntityIdField, fld.ParentTypeIdField, fld.OldParentIdField, fld.NewParentIdField, fld.DateField, (Field)fld.UserIdField, fld.AuditTypeIdField, fld.OldAuditDataField, fld.NewAuditDataField) .OrderBy( (Field)fld.IdField) .ApplySkipTakeAndCount(request.Skip, request.Take, request.ExcludeTotalCount) .ApplySort(request.Sort); if (request.EntityTypeId != null && request.EntityId != null) { var pEntityId = query.AddParam(request.EntityId); var pEntityTypeId = query.AddParam(request.EntityTypeId); query.Where(~( ~( new Criteria(0, fld.EntityTypeIdField) == pEntityTypeId & new Criteria(0, fld.EntityIdField) == pEntityId) | ~( new Criteria(0, fld.ParentTypeIdField) == pEntityTypeId & ~( new Criteria(0, fld.OldParentIdField) == pEntityId | new Criteria(0, fld.NewParentIdField) == pEntityId)))); } else { if (request.EntityTypeId != null) query.WhereEqual(fld.EntityTypeIdField, request.EntityTypeId);//Convert.ToInt32(request.EntityTypeId.Value)); if (request.EntityId != null) query.WhereEqual(fld.EntityIdField, request.EntityId.Value); } response.TotalCount = query.ForEach(connection, delegate() { response.Entities.Add(row.Clone()); }); response.SetSkipTakeTotal(query); response.IdNameLookups = new Dictionary<EntityType, Dictionary<long, string>>(); response.FieldTitles = new Dictionary<EntityType, Dictionary<string, string>>(); response.ForeignEntityTypes = new Dictionary<EntityType, Dictionary<string, string>>(); response.EntityTitles = new Dictionary<EntityType, string>(); var lookups = response.IdNameLookups; var titles = response.FieldTitles; var foreigns = response.ForeignEntityTypes; var entities = response.EntityTitles; Action<EntityType, Int64> addLookup = (entityType, id) => { Dictionary<long, string> lookup; if (!lookups.TryGetValue(entityType, out lookup)) { lookup = new Dictionary<long, string>(); lookups[entityType] = lookup; } if (!lookup.ContainsKey(id)) lookup[id] = null; }; Action<EntityType, string> addTitle = (entityType, field) => { Dictionary<string, string> lookup; if (!titles.TryGetValue(entityType, out lookup)) { lookup = new Dictionary<string, string>(); titles[entityType] = lookup; } if (!lookup.ContainsKey(field)) lookup[field] = null; }; Action<EntityType> addEntity = (entityType) => { if (!entities.ContainsKey(entityType)) { //Row r; String s = null; // TODO: FIX! //if (schema.TypeToTable.TryGetValue(entityType, out r)) // s = LocalText.TryGet(1055, "Db." + r.Table + ".EntitySingular", false); s = s ?? Enum.GetName(typeof(EntityType), entityType); entities[entityType] = s; } }; Action<EntityType, string, EntityType> addForeign = (entityType, field, foreignType) => { Dictionary<string, string> foreign; if (!foreigns.TryGetValue(entityType, out foreign)) { foreign = new Dictionary<string, string>(); foreigns[entityType] = foreign; } if (!foreign.ContainsKey(field)) foreign[field] = Enum.GetName(typeof(EntityType), foreignType); }; foreach (var entity in response.Entities) { addEntity(fld.EntityTypeIdField[entity]); addLookup(fld.EntityTypeIdField[entity], fld.EntityIdField[entity].Value); if (fld.ParentTypeIdField[entity] != null) addEntity(fld.ParentTypeIdField[entity]); //if (entity.UserId != null) // addLookup(UserRow.TableName, entity.UserId.Value); Row theRow; if (((AuditType?)fld.AuditTypeIdField[entity] == AuditType.Insert || (AuditType?)fld.AuditTypeIdField[entity] == AuditType.Update) && (fld.OldAuditDataField[entity] != null || fld.NewAuditDataField[entity] != null)) { theRow = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, fld.EntityTypeIdField[entity]); if (theRow == null) continue; UpdateAuditDataDictionary ud = new UpdateAuditDataDictionary(); if (fld.OldAuditDataField[entity] != null) ud.Old = JsonConvert.DeserializeObject<Dictionary<string, object>>(fld.OldAuditDataField[entity].TrimToNull() ?? "{}", JsonSettings.Tolerant); if (fld.NewAuditDataField[entity] != null) ud.New = JsonConvert.DeserializeObject<Dictionary<string, object>>(fld.OldAuditDataField[entity].TrimToNull() ?? "{}", JsonSettings.Tolerant); for (var i = 0; i < 2; i++) { var d = (i == 0) ? ud.Old : ud.New; if (d != null) foreach (var p in d) { addTitle(fld.EntityTypeIdField[entity], p.Key); if (p.Value != null && p.Value is Int16 || p.Value is Int32 || p.Value is Int64) { var f = theRow.FindField(p.Key); if (!ReferenceEquals(null, f) && f.ForeignTable != null) { //EntityType foreignType; //if (schema.TableToType.TryGetValue(f.ForeignTable, out foreignType)) { addForeign(fld.EntityTypeIdField[entity], p.Key, f.ForeignTable); addLookup(f.ForeignTable, Convert.ToInt64(p.Value)); } } } } } } } foreach (var pair in response.IdNameLookups) { Row entity = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, pair.Key); if (entity != null) { var idRow = entity as IIdRow; var nameRow = entity as INameRow; if (idRow != null && nameRow != null) { var lookup = pair.Value; var idName = GetIdNameDictionary(connection, (IIdRow)entity, ((INameRow)entity).NameField, lookup.Keys); foreach (var p in idName) lookup[p.Key] = p.Value; } } } foreach (var pair in response.FieldTitles) { Row entity = RowRegistry.GetConnectionRow(RowRegistry.DefaultConnectionKey, pair.Key); if (entity != null) { var lookup = pair.Value; var keys = new string[lookup.Keys.Count]; lookup.Keys.CopyTo(keys, 0); foreach (var key in keys) { Field f; if (key.EndsWith("Id")) { var s = key.Substring(0, key.Length - 2); f = entity.FindField(s); if (!ReferenceEquals(null, f)) { lookup[key] = f.Title; continue; } } f = entity.FindField(key); if (!ReferenceEquals(null, f)) lookup[key] = f.Title; } } } return response; } }
public static List<string> GetTablePrimaryFields(IDbConnection connection, string schema, string tableName) { var inf = InformationSchema(connection); List<string> primaryFields = new List<string>(); if (connection.GetDialect().ServerType == "Firebird") { var query = @" select cast(RC.RDB$RELATION_NAME as varchar(31)) as TABLE_NAME, cast(ISGMT.RDB$FIELD_NAME as varchar(31)) as COLUMN_NAME, CAST((ISGMT.RDB$FIELD_POSITION + 1) as SMALLINT) as KEY_SEQ, cast(RC.RDB$CONSTRAINT_NAME as varchar(31)) as PK_NAME from RDB$RELATION_CONSTRAINTS RC INNER JOIN RDB$INDEX_SEGMENTS ISGMT ON RC.RDB$INDEX_NAME = ISGMT.RDB$INDEX_NAME where CAST(RC.RDB$RELATION_NAME AS VARCHAR(40)) = '{0}' and RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' order by 3"; foreach (var k in connection.Query(String.Format(query, tableName))) { primaryFields.Add(((string)k.COLUMN_NAME).TrimEnd()); } return primaryFields; } var columns = ((DbConnection)((WrappedConnection)connection).ActualConnection).GetSchema("Columns", new string[] { null, schema, tableName, null }); if (columns.Columns.Contains("PRIMARY_KEY")) { foreach (DataRow row in columns.Rows) { try { var isPrimaryKey = row["PRIMARY_KEY"] as Boolean?; if (isPrimaryKey == true) primaryFields.Add((string)row["COLUMN_NAME"]); } catch (Exception) { } } return primaryFields; } if (connection.GetDialect().ServerType.StartsWith("MySql", StringComparison.OrdinalIgnoreCase) || connection.GetDialect().ServerType.StartsWith("SqlServer", StringComparison.OrdinalIgnoreCase)) { var query = new SqlQuery().Select( "KCU.COLUMN_NAME") .From( inf + "TABLE_CONSTRAINTS AS TC INNER JOIN " + inf + "KEY_COLUMN_USAGE AS KCU " + "ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND " + "KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND " + "KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND " + "KCU.TABLE_NAME = TC.TABLE_NAME") .Where( new Criteria("TC.CONSTRAINT_TYPE") == "PRIMARY KEY" & new Criteria("KCU.TABLE_NAME") == tableName) .OrderBy( "KCU.ORDINAL_POSITION"); query.ForEach(connection, delegate(IDataReader reader) { primaryFields.Add(reader.GetString(0)); }); } return primaryFields; }
public static List<string> GetTablePrimaryFields(IDbConnection connection, string schema, string tableName) { var inf = InformationSchema(connection); List<string> primaryFields = new List<string>(); if (connection.GetDialect().ServerType == "Firebird") { var q = @" select cast(RC.RDB$RELATION_NAME as varchar(31)) as TABLE_NAME, cast(ISGMT.RDB$FIELD_NAME as varchar(31)) as COLUMN_NAME, CAST((ISGMT.RDB$FIELD_POSITION + 1) as SMALLINT) as KEY_SEQ, cast(RC.RDB$CONSTRAINT_NAME as varchar(31)) as PK_NAME from RDB$RELATION_CONSTRAINTS RC INNER JOIN RDB$INDEX_SEGMENTS ISGMT ON RC.RDB$INDEX_NAME = ISGMT.RDB$INDEX_NAME where CAST(RC.RDB$RELATION_NAME AS VARCHAR(40)) = '{0}' and RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY' order by 3"; foreach (IDictionary<string, object> k in connection.Query(String.Format(q, tableName))) { primaryFields.Add((k["COLUMN_NAME"] as string).TrimEnd()); } return primaryFields; } var query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@schema and TABLE_NAME = @tableName"; bool sqlite = connection.GetDialect().ServerType.StartsWith("Sqlite", StringComparison.OrdinalIgnoreCase); if (sqlite) query = "PRAGMA table_info(@tableName)"; var columns = connection.Query(query, new { schema, tableName }); foreach (IDictionary<string, object> column in columns) { try { var isPrimaryKey = column["PRIMARY_KEY"] as Boolean?; if (isPrimaryKey == true) primaryFields.Add(column["COLUMN_NAME"] as string); } catch { break; } return primaryFields; } if (connection.GetDialect().ServerType.StartsWith("MySql", StringComparison.OrdinalIgnoreCase) || connection.GetDialect().ServerType.StartsWith("SqlServer", StringComparison.OrdinalIgnoreCase)) { var query2 = new SqlQuery().Select( "KCU.COLUMN_NAME") .From( inf + "TABLE_CONSTRAINTS AS TC INNER JOIN " + inf + "KEY_COLUMN_USAGE AS KCU " + "ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND " + "KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND " + "KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND " + "KCU.TABLE_NAME = TC.TABLE_NAME") .Where( new Criteria("TC.CONSTRAINT_TYPE") == "PRIMARY KEY" & new Criteria("KCU.TABLE_NAME") == tableName) .OrderBy( "KCU.ORDINAL_POSITION"); query2.ForEach(connection, delegate(IDataReader reader) { primaryFields.Add(reader.GetString(0)); }); } return primaryFields; }