VisitManagers should delete expired entries in tg_visit

[grover]

Otherwise tg_visit will just keep all its stale visit entries forever. It would also be nice if when an entry in tg_visit was deleted, its corresponding entry in VisitIdentity? was deleted too.

[alberto]

Batch moved into unclassified from 1.1 to properly track progress on the later

[gkalab]

Patch to delete visit entries that are expired from the database (only for SA yet). Diffed against 1.0 branch.

[gkalab]

I use a background process to access TG every few seconds. Visit entries are created on these anonymous requests. So the database was growing rapidly and I had the need to delete expired visits from the database.

The suggested patch implements this for SQLAlchemy only and it is only tested with PostgreSQL. With PostgreSQL stale entries in the visit_identity table can also be deleted easily with the proposed patch and the following table setup:

visits_table = Table("visit", metadata,
    Column("visit_key", String(40), primary_key=True),
    Column("created", DateTime, nullable=False, default=datetime.datetime.now),
    Column("expiry", DateTime, index=True)
)

visit_identity_table = Table("visit_identity", metadata,
    Column("visit_key", String(40), ForeignKey("visit.visit_key", ondelete="CASCADE"),
           primary_key=True),
    Column("user_id", Integer, ForeignKey("tg_user.user_id"), index=True)
)

[Chris Arndt]

While the issue is still valid, the approach taken by the patch does not seem a good one for me. Checking for expired visits in the VisitFilter would mean that this would be done at every request, which will most probably impact performance.

I suggest that the cleanup procedure is called in the VisitManager.run method, like it is done with update_queued_visits.

Then, of course we need a SQLObject implementation and tests, before this can be applied.

[chrisz]

Right, the VisitManager seems to be the appropriate place for deleting expired visits.

In one project I'm also dealing with many users created on the fly from a remote LDAP repository. In this case, in addition to the visit table, expired entries can also accumulate in the visit_identity and tg_user tables. Maybe we should also provide a solution for that.

As a temporary solution, I have added an example in  http://docs.turbogears.org/1.0/Scheduler that shows how you clean up the entries using the scheduler.

[Chris Arndt]

For the time being, I added a recipe for a function to clean out old visits to the doc wiki:

 http://docs.turbogears.org/1.0/UsingVisitFramework#housekeeping

It's not the most effective one, but I wanted to keep the example simple for the sake of clarity.

[chrisz]

Moved to  https://sourceforge.net/p/turbogears1/tickets/11/.