19 package org.sleuthkit.autopsy.timeline.db;
21 import java.util.Collections;
22 import java.util.List;
24 import java.util.function.Function;
25 import java.util.stream.Collectors;
26 import java.util.stream.Stream;
27 import javax.annotation.Nonnull;
28 import org.apache.commons.lang3.StringUtils;
63 static String useHashHitTablesHelper(RootFilter filter) {
64 HashHitsFilter hashHitFilter = filter.getHashHitsFilter();
65 return hashHitFilter.isActive() ?
" LEFT JOIN hash_set_hits " :
" ";
68 static String useTagTablesHelper(RootFilter filter) {
69 TagsFilter tagsFilter = filter.getTagsFilter();
70 return tagsFilter.isActive() ?
" LEFT JOIN tags " :
" ";
85 static <X> Set<X> unGroupConcat(String groupConcat, Function<String, X> mapper) {
86 return StringUtils.isBlank(groupConcat) ? Collections.emptySet()
87 : Stream.of(groupConcat.split(
","))
89 .collect(Collectors.toSet());
92 private static String getSQLWhere(IntersectionFilter<?> filter) {
93 return filter.getSubFilters().stream()
95 .map(SQLHelper::getSQLWhere)
96 .collect(Collectors.joining(
" and ",
"( ",
")"));
99 private static String getSQLWhere(UnionFilter<?> filter) {
100 return filter.getSubFilters().stream()
102 .collect(Collectors.joining(
" or ",
"( ",
")"));
105 static String getSQLWhere(RootFilter filter) {
106 return getSQLWhere((IntersectionFilter) filter);
117 private static String getSQLWhere(Filter filter) {
119 if (filter == null) {
121 }
else if (filter instanceof DescriptionFilter) {
122 result = getSQLWhere((DescriptionFilter) filter);
123 }
else if (filter instanceof TagsFilter) {
124 result = getSQLWhere((TagsFilter) filter);
125 }
else if (filter instanceof HashHitsFilter) {
126 result = getSQLWhere((HashHitsFilter) filter);
127 }
else if (filter instanceof DataSourceFilter) {
128 result = getSQLWhere((DataSourceFilter) filter);
129 }
else if (filter instanceof DataSourcesFilter) {
130 result = getSQLWhere((DataSourcesFilter) filter);
131 }
else if (filter instanceof HideKnownFilter) {
132 result = getSQLWhere((HideKnownFilter) filter);
133 }
else if (filter instanceof HashHitsFilter) {
134 result = getSQLWhere((HashHitsFilter) filter);
135 }
else if (filter instanceof TextFilter) {
136 result = getSQLWhere((TextFilter) filter);
137 }
else if (filter instanceof TypeFilter) {
138 result = getSQLWhere((TypeFilter) filter);
139 }
else if (filter instanceof IntersectionFilter) {
140 result = getSQLWhere((IntersectionFilter) filter);
141 }
else if (filter instanceof UnionFilter) {
142 result = getSQLWhere((UnionFilter) filter);
144 throw new IllegalArgumentException(
"getSQLWhere not defined for " + filter.getClass().getCanonicalName());
146 result = StringUtils.deleteWhitespace(result).equals(
"(1and1and1)") ?
"1" : result;
147 result = StringUtils.deleteWhitespace(result).equals(
"()") ?
"1" : result;
151 private static String getSQLWhere(HideKnownFilter filter) {
152 if (filter.isActive()) {
153 return "(known_state IS NOT '" + TskData.FileKnown.KNOWN.getFileKnownValue() +
"')";
159 private static String getSQLWhere(DescriptionFilter filter) {
160 if (filter.isActive()) {
161 String likeOrNotLike = (filter.getFilterMode() == DescriptionFilter.FilterMode.INCLUDE ?
"" :
" NOT") +
" LIKE '";
162 return "(" + getDescriptionColumn(filter.getDescriptionLoD()) + likeOrNotLike + filter.getDescription() +
"' )";
168 private static String getSQLWhere(TagsFilter filter) {
169 if (filter.isActive()
170 && (filter.getSubFilters().isEmpty() ==
false)) {
171 String tagNameIDs = filter.getSubFilters().stream()
172 .filter((TagNameFilter t) -> t.isSelected() && !t.isDisabled())
173 .map((TagNameFilter t) -> String.valueOf(t.getTagName().getId()))
174 .collect(Collectors.joining(
", ",
"(",
")"));
175 return "(events.event_id == tags.event_id AND "
176 +
"tags.tag_name_id IN " + tagNameIDs +
") ";
183 private static String getSQLWhere(HashHitsFilter filter) {
184 if (filter.isActive()
185 && (filter.getSubFilters().isEmpty() ==
false)) {
186 String hashSetIDs = filter.getSubFilters().stream()
187 .filter((HashSetFilter t) -> t.isSelected() && !t.isDisabled())
188 .map((HashSetFilter t) -> String.valueOf(t.getHashSetID()))
189 .collect(Collectors.joining(
", ",
"(",
")"));
190 return "(hash_set_hits.hash_set_id IN " + hashSetIDs +
" AND hash_set_hits.event_id == events.event_id)";
196 private static String getSQLWhere(DataSourceFilter filter) {
197 if (filter.isActive()) {
198 return "(datasource_id = '" + filter.getDataSourceID() +
"')";
204 private static String getSQLWhere(DataSourcesFilter filter) {
205 return (filter.isActive()) ?
"(datasource_id in ("
206 + filter.getSubFilters().stream()
208 .map((dataSourceFilter) -> String.valueOf(dataSourceFilter.getDataSourceID()))
209 .collect(Collectors.joining(
", ")) +
"))" :
"1";
212 private static String getSQLWhere(TextFilter filter) {
213 if (filter.isActive()) {
214 if (StringUtils.isBlank(filter.getText())) {
217 String strippedFilterText = StringUtils.strip(filter.getText());
218 return "((med_description like '%" + strippedFilterText +
"%')"
219 +
" or (full_description like '%" + strippedFilterText +
"%')"
220 +
" or (short_description like '%" + strippedFilterText +
"%'))";
234 private static String getSQLWhere(TypeFilter typeFilter) {
235 if (typeFilter.isSelected() ==
false) {
237 }
else if (typeFilter.getEventType() instanceof RootEventType) {
238 if (typeFilter.getSubFilters().stream()
239 .allMatch(subFilter -> subFilter.isActive() && subFilter.getSubFilters().stream().allMatch(
Filter::isActive))) {
243 return "(sub_type IN (" + StringUtils.join(getActiveSubTypes(typeFilter),
",") +
"))";
246 private static List<Integer> getActiveSubTypes(TypeFilter filter) {
247 if (filter.isActive()) {
248 if (filter.getSubFilters().isEmpty()) {
249 return Collections.singletonList(RootEventType.allTypes.indexOf(filter.getEventType()));
251 return filter.getSubFilters().stream().flatMap((Filter t) -> getActiveSubTypes((TypeFilter) t).stream()).collect(Collectors.toList());
254 return Collections.emptyList();
270 static String getStrfTimeFormat(@Nonnull TimeUnits timeUnit) {
273 return "%Y-01-01T00:00:00";
275 return "%Y-%m-01T00:00:00";
277 return "%Y-%m-%dT00:00:00";
279 return "%Y-%m-%dT%H:00:00";
281 return "%Y-%m-%dT%H:%M:00";
284 return "%Y-%m-%dT%H:%M:%S";
288 static String getDescriptionColumn(DescriptionLoD lod) {
291 return "full_description";
293 return "med_description";
296 return "short_description";
300 private SQLHelper() {