PeterLudolf PeterLudolf - 1 month ago 6
Java Question

Sqlite relative complement on combined key

First some background about my Problem:

I am building a crawler and I want to monitor some highscore lists.

The highscore lists are defined by two parameters: a category and a collection (together unique).

After a successful download I create a new stats entry (category, collection, createdAt, ...)

Problem: I want to query the highscore list only once per day. So I need a query that will return category and collection that haven't been downloaded in 24h.
The stats Table should be used for this.

I have a List of all possible categories and of all possible collections. They work like a cross join.
So basically i need the relative complement of the cross join with the entries from the last 24h

My Idea: Cross join categories and collections and 'substract' all Pair(category, collection) of stats entries that has been created during last 24 h

Question 1: Is it possible to define categories and collections inside the query and cross join them or do I have to create a table for them?

Question 2: Is my Idea the correct approach? How would you do this in Sqlite?

Ok i realise that this might sound confusing so I drew an image of what I actually want.
enter image description here

I am interested in C.

Here is my current code in java, maybe it helps to understand the problem:

public List<Pair<String, String>> getCollectionsToDownload() throws SQLException {
long threshold = System.currentTimeMillis() - DAY;
QueryBuilder<TopAppStatistics, Long> query = queryBuilder();
List<TopAppStatistics> collectionsNotToQuery = query.where().ge(TopAppStatistics.CREATED_AT, threshold).query();

List<Pair<String, String>> toDownload = crossJoin();
for (TopAppStatistics stat : collectionsNotToQuery) {
toDownload.remove(new Pair<>(stat.getCategory(), stat.getCollection()));
}
return toDownload;
}

private List<Pair<String, String>> crossJoin() {
String[] categories = PlayUrls.CATEGORIES;
String[] collections = PlayUrls.COLLECTIONS;
List<Pair<String, String>> toDownload = new ArrayList<>();
for (String ca : categories) {
for (String co : collections) {
toDownload.add(new Pair<>(ca, co));
}
}
return toDownload;
}

dmg dmg
Answer

The easiest solution to your problem is an EXCEPT. Say you have a subquery that computes A and another one that computes B. These queries can be very complex. The key is that both should return the same number of parameters and comparable data types.

In SQLite you can then do:

<your subquery 1> EXCEPT <your subquery 2>

As simple as that.

For example:

SELECT a, b FROM T where a > 10 
EXCEPT 
SELECT a,b FROM T where b < 5;

Remember, both subqueries must return the same number of columns.

Comments