private List <long> Ids(DateTime lastIndexed) { var idColumn = Wrapper.GetColumnName(ColumnTypeEnum.Id, Alias); long start = 0; var result = new List <long>(); using (var db = DbManager.FromHttpContext("default", 1800000)) { while (true) { var dataQuery = GetBaseQuery(lastIndexed) .Select(idColumn) .Where(Exp.Ge(idColumn, start)) .OrderBy(idColumn, true) .SetFirstResult(QueryLimit) .SetMaxResults(1); var id = db.ExecuteList(dataQuery).Select(r => Convert.ToInt64(r[0])).FirstOrDefault(); if (id != 0) { start = id; result.Add(id); } else { break; } } return(result); } }
private Tuple <long, long, long> Count(DateTime lastIndexed) { var idColumn = Wrapper.GetColumnName(ColumnTypeEnum.Id, Alias); using (var db = DbManager.FromHttpContext("default", 1800000)) { var dataQuery = GetBaseQuery(lastIndexed) .Select(idColumn) .OrderBy(idColumn, true) .SetMaxResults(1); var minid = db.ExecuteList(dataQuery).Select(r => Convert.ToInt64(r[0])).FirstOrDefault(); dataQuery = GetBaseQuery(lastIndexed) .Select(idColumn) .OrderBy(idColumn, false) .SetMaxResults(1); var maxid = db.ExecuteList(dataQuery).Select(r => Convert.ToInt64(r[0])).FirstOrDefault(); dataQuery = GetBaseQuery(lastIndexed) .SelectCount(); var count = db.ExecuteList(dataQuery).Select(r => Convert.ToInt64(r[0])).FirstOrDefault(); return(new Tuple <long, long, long>(count, maxid, minid)); } }
void IIndexer.IndexAll() { var now = DateTime.UtcNow; var idColumn = Wrapper.GetColumnName(ColumnTypeEnum.Id, Alias); var tenantIdColumn = Wrapper.GetColumnName(ColumnTypeEnum.TenantId, Alias); var lastModifiedColumn = Wrapper.GetColumnName(ColumnTypeEnum.LastModified, Alias); DateTime lastIndexed; using (var db = DbManager.FromHttpContext("default")) { lastIndexed = db.ExecuteScalar <DateTime>(new SqlQuery("webstudio_index").Select("last_modified").Where("index_name", Wrapper.IndexName)); } var meta = Count(idColumn, tenantIdColumn, lastModifiedColumn, lastIndexed); Logger.DebugFormat("Index: {0},Count {1},Max: {2},Min: {3}", IndexName, meta.Item1, meta.Item2, meta.Item3); if (meta.Item1 != 0) { var step = (meta.Item2 - meta.Item3 + 1) / meta.Item1; if (step == 0) { step = 1; } if (step < QueryLimit) { step = QueryLimit; } for (var i = meta.Item3; i <= meta.Item2; i += step) { IndexAllGetData(i, step, idColumn, tenantIdColumn, lastModifiedColumn, lastIndexed); } } using (var db = DbManager.FromHttpContext("default")) { db.ExecuteNonQuery( new SqlInsert("webstudio_index", true) .InColumnValue("index_name", Wrapper.IndexName) .InColumnValue("last_modified", now) ); } Logger.DebugFormat("index completed {0}", Wrapper.IndexName); }
private SqlQuery GetBaseQuery(DateTime lastIndexed) { var dataQuery = new SqlQuery(Wrapper.Table + " " + Alias); var tenantIdColumn = Wrapper.GetColumnName(ColumnTypeEnum.TenantId, Alias); var lastModifiedColumn = Wrapper.GetColumnName(ColumnTypeEnum.LastModified, Alias); if (!string.IsNullOrEmpty(tenantIdColumn)) { dataQuery.InnerJoin("tenants_tenants t", Exp.EqColumns(tenantIdColumn, "t.id")) .Where("t.status", TenantStatus.Active); } Wrapper.AddConditions(Alias, dataQuery); if (!DateTime.MinValue.Equals(lastIndexed)) { dataQuery.Where(Exp.Gt(lastModifiedColumn, lastIndexed)); } return(dataQuery); }
private List <T> GetDataFromDb(long start, long stop, DateTime lastIndexed) { var idColumn = Wrapper.GetColumnName(ColumnTypeEnum.Id, Alias); List <object[]> data; var dataQuery = GetBaseQuery(lastIndexed) .Select(Wrapper.GetColumnNames(Alias)) .Where(Exp.Between(idColumn, start, stop)); AddJoins(Wrapper, dataQuery, Alias); using (var db = DbManager.FromHttpContext("default", 1800000)) { db.ExecuteNonQuery("SET SESSION group_concat_max_len = 4294967295;"); data = db.ExecuteList(dataQuery); } var converter = Wrapper.GetDataConverter(); return(data.Select(r => (T)converter(r)).ToList()); }