class SQLiteIndex extends AbstractAttributeIndex<A, O> implements SortedKeyStatisticsAttributeIndex<A, O>, NonHeapTypeIndex {

    @Override
    public ResultSet<O> retrieve(final Query<O> query, final QueryOptions queryOptions) {
        final ConnectionManager connectionManager = getConnectionManager(queryOptions);

        final CloseableResourceGroup closeableResourceGroup = CloseableRequestResources.forQueryOptions(queryOptions).addGroup();

        if (query instanceof FilterQuery){
            @SuppressWarnings("unchecked")
            final FilterQuery<O, A> filterQuery =  (FilterQuery<O, A>)query;
            return new ResultSet<O>() {
                @Override
                public Iterator<O> iterator() {
                    final Connection searchConnection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);

                    final java.sql.ResultSet searchResultSet = DBQueries.getAllIndexEntries(tableName, searchConnection);
                    closeableResourceGroup.add(DBUtils.wrapAsCloseable(searchResultSet));

                    return new LazyIterator<O>() {
                        @Override
                        protected O computeNext() {
                            try {
                                while (true) {

                                    if (!searchResultSet.next()) {
                                        close();
                                        return endOfData();
                                    }

                                    final K objectKey = DBUtils.getValueFromResultSet(1, searchResultSet, primaryKeyAttribute.getAttributeType());
                                    final A objectValue = DBUtils.getValueFromResultSet(2, searchResultSet, attribute.getAttributeType());
                                    if (filterQuery.matchesValue(objectValue, queryOptions)) {
                                        return foreignKeyAttribute.getValue(objectKey, queryOptions);
                                    }
                                }

                            } catch (Exception e) {
                                endOfData();
                                close();
                                throw new IllegalStateException("Unable to retrieve the ResultSet item.", e);
                            }
                        }
                    };

                }

                @Override
                public boolean contains(O object) {

                    Connection connection = null;
                    java.sql.ResultSet searchResultSet = null;
                    try {
                        connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);
                        searchResultSet = DBQueries.getIndexEntryByObjectKey(primaryKeyAttribute.getValue(object, queryOptions), tableName, connection);

                        return lazyMatchingValuesIterable(searchResultSet).iterator().hasNext();

                    }finally {
                        DBUtils.closeQuietly(searchResultSet);
                    }

                }

                @Override
                public boolean matches(O object) {
                    return query.matches(object, queryOptions);
                }

                @Override
                public Query<O> getQuery() {
                    return query;
                }

                @Override
                public QueryOptions getQueryOptions() {
                    return queryOptions;
                }

                @Override
                public int getRetrievalCost() {
                    return INDEX_RETRIEVAL_COST_FILTERING;// choose between indexes
                }

                @Override
                public int getMergeCost() {
                    //choose between branches.
                    final Connection connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);
                    return DBQueries.count(has(primaryKeyAttribute), tableName, connection); // no need to eliminate duplicates
                }

                @Override
                public int size() {
                    Connection connection = null;
                    java.sql.ResultSet searchResultSet = null;
                    try {
                        connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);
                        searchResultSet = DBQueries.getAllIndexEntries(tableName, connection);

                        final Iterable<K> iterator = lazyMatchingValuesIterable(searchResultSet);
                        return IteratorUtil.countElements(iterator);
                    }finally {
                        DBUtils.closeQuietly(searchResultSet);
                    }
                }

                @Override
                public void close() {
                    closeableResourceGroup.close();
                }

                // Method to retrieve all the distinct keys for the matching values from the index. Used in count and contains
                Iterable<K> lazyMatchingValuesIterable(final java.sql.ResultSet searchResultSet) {
                    return new Iterable<K>() {
                        @Override
                        public Iterator<K> iterator() {
                            return new LazyIterator<K>() {

                                K currentKey = null;

                                @Override
                                protected K computeNext() {
                                    try {
                                        while (true) {

                                            if (!searchResultSet.next()) {
                                                close();
                                                return endOfData();
                                            }

                                            final K objectKey = DBUtils.getValueFromResultSet(1, searchResultSet, primaryKeyAttribute.getAttributeType());
                                            if (currentKey == null || !currentKey.equals(objectKey)) {
                                                final A objectValue = DBUtils.getValueFromResultSet(2, searchResultSet, attribute.getAttributeType());
                                                if (filterQuery.matchesValue(objectValue, queryOptions)) {
                                                    currentKey = objectKey;
                                                    return objectKey;
                                                }
                                            }
                                        }

                                    } catch (Exception e) {
                                        endOfData();
                                        close();
                                        throw new IllegalStateException("Unable to retrieve the ResultSet item.", e);
                                    }
                                }
                            };
                        }
                    };
                }

            };
        }else {

            return new ResultSet<O>() {

                @Override
                public Iterator<O> iterator() {

                    final Connection searchConnection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);

                    final java.sql.ResultSet searchResultSet = DBQueries.search(query, tableName, searchConnection); // eliminates duplicates
                    closeableResourceGroup.add(DBUtils.wrapAsCloseable(searchResultSet));

                    return new LazyIterator<O>() {
                        @Override
                        protected O computeNext() {
                            try {
                                if (!searchResultSet.next()) {
                                    close();
                                    return endOfData();
                                }
                                final K objectKey = DBUtils.getValueFromResultSet(1, searchResultSet, primaryKeyAttribute.getAttributeType());
                                return foreignKeyAttribute.getValue(objectKey, queryOptions);
                            } catch (Exception e) {
                                endOfData();
                                close();
                                throw new IllegalStateException("Unable to retrieve the ResultSet item.", e);
                            }
                        }
                    };
                }

                @Override
                public int getRetrievalCost() {
                    return INDEX_RETRIEVAL_COST;
                }

                @Override
                public int getMergeCost() {
                    final Connection connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);
                    return DBQueries.count(query, tableName, connection); // no need to eliminate duplicates
                }

                @Override
                public boolean contains(O object) {
                    final K objectKey = primaryKeyAttribute.getValue(object, queryOptions);
                    final Connection connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);
                    return DBQueries.contains(objectKey, query, tableName, connection);
                }

                @Override
                public boolean matches(O object) {
                    return query.matches(object, queryOptions);
                }

                @Override
                public int size() {
                    final Connection connection = connectionManager.getConnection(SQLiteIndex.this, queryOptions);

                    boolean attributeHasAtMostOneValue = (attribute instanceof SimpleAttribute || attribute instanceof SimpleNullableAttribute);
                    boolean queryIsADisjointInQuery = query instanceof In && ((In) query).isDisjoint();

                    if (queryIsADisjointInQuery || attributeHasAtMostOneValue) {
                        return DBQueries.count(query, tableName, connection); // No need to eliminates duplicates
                    }else{
                        return DBQueries.countDistinct(query, tableName, connection); // eliminates duplicates
                    }
                }

                @Override
                public void close() {
                    closeableResourceGroup.close();
                }

                @Override
                public Query<O> getQuery() {
                    return query;
                }

                @Override
                public QueryOptions getQueryOptions() {
                    return queryOptions;
                }
            };
        }
    }

    public  SQLiteIndex(final Attribute<O, A> attribute,
                       final SimpleAttribute<O, K> primaryKeyAttribute,
                       final SimpleAttribute<K, O> foreignKeyAttribute,
                       final String tableNameSuffix);

}