Рекурсивное выполнение обработки данных с помощью Java и SQLite

AFAICT можно использовать иерархические запросы (Google для "ПОДКЛЮЧЕНИЯ" "ЗАПУСКАЮТСЯ С") в SQLite...

Это могло бы быть offtopic, но.. Вы рассмотрели использование сериализации?

Буферы Протокола Google могли использоваться для сериализации данных очень эффективным способом (время и пространство), необходимо будет затем создать подходящую древовидную структуру (взгляд в любой книге CS) для помощи с поиском.

Я упомянул буферы протокола, потому что, будучи библиотекой Google они могут быть доступными на Android.

Просто мысль.

I listened to Soonil and gave a try to the « closure table ». I added the following table :

################
#   Closure    #
################
# ancestor_id  #
#   item_id    #
################

If like me you never used that model before, it works that way :

You add a row for every direct or indirect relationship in the hierarchy. If C is a child of B, and B a child of A, you've got :

ancestor    item
   B         C
   A         B
   A         C      # you add the indirect relationship   
   A         A
   B         B
   C         C      # don't forget any item is in relation with himself 

Nevertheless, with this scheme, you are missing an important information : what are the direct relationships ? What if you want only the direct children of an item ?

For that, you can add a column is_direct with a bool in the closure table, or you can just keep the column parent_id in the item table. That what I did because it prevents me from rewriting a lot of my previous code.

The nice part is that I can now check if an item matches a date or a geocontext in one single query.

E.G, if I am browsing all the items contained in the item number 4 and want to get only the ones matching or containing a children matching the date D :

SELECT ti.parent_id, ti.id, ti.title 
FROM item AS di                                  # item to filter with the date
              JOIN closure AS c                  # closure table
                  ON (di.id = c.item_id) 
              JOIN item AS ti 
                  ON (c.ancestor_id = ti.id)     # top item to display
WHERE di.date = D                                # here you filter by date   
AND ti.parent_id = 4                             # here you ensure you got only the top items

So I can throw away all my *_cache tables. I still have a lot of work to do one UPDATE / DELETE / CREATE, but everything is centralized and most of it is procedural, not recursive. Pretty cool.

The only pain is that I must recursively add an item to all its ancestor. But getting the ancestors is a one query shot, so it's really reasonable. And of course the closure table take a lot of space, but in my case I just don't care. Don't forget to index it if you are looking for perfs...

Love this SQL trick, thanks a lot guys ! It's a bit tricky to get at first glance, but so obvious once you have it done.