DBClean

[onli]

Some time ago, i wrote upon request a plugin to clean the database. Development stopped after i got suddenly no feedback anymore, so i forgot it myself. Well, i remembered and finished it today. Have a look, i will commit it to spartacus soon if no error-reports arrive here.

[garvinhicking]

Hi!

Great, thanks for sharing! One thing I see is that you use unix_timestmap(). Could you use simple INT casting with PHP timestamps? This would make it possible to use on other DB systems like pgsql and sqlite...

Like this:

Code: Select all

            $sql = "DELETE
                    FROM {$serendipity['dbPrefix']}$table
                    WHERE timestamp < " . (time()-($days*24*60*60));

I also saw some strings like "Delete?" and "all entries" which were not yet put into constants.

And then ,please feel free to put it into spartacus. What about maybe merging this into the dashboard plugin?

Regards,
Garvin

[onli]

Thanks, i oversaw the headings. Fixed, also unix_timestamp().

Are there additional tables the plugin could clean?

What about maybe merging this into the dashboard plugin?

I don't really see the connection between those? To clean the database is a distinct purpose, think that belongs into an own plugin.
However, it might work to use the dashboard-plugin to notify the user that a table is "full".

[garvinhicking]

Hi!

Hm, there are no other tables that come to my mind...

About dashboard: this more and more becomes a global "maintenance" and "notification" plugin, so maybe that we could even merge the autoupdater plugin into there?

Regards,
Garvin

[Lux]

Are there additional tables the plugin could clean?

I have about 400.000 entries in "exit" and 1.500.000 in "visitors".

Deleting a lot of entries in these tables can lead to a timeout, so I recommend doing a query before to decide how many entries would be deleted. Since this is based on time, there should be an index on the timestamp column.

Cheers

Dirk

[onli]

I have about 400.000 entries in "exit" and 1.500.000 in "visitors".

I added exits, visitors was already in the selection.

Since this is based on time, there should be an index on the timestamp column.

I'd be prefer not to mess with the database-structure with the plugin. Is the index something that should be generated by the creator of these tables?

About dashboard: this more and more becomes a global "maintenance" and "notification" plugin, so maybe that we could even merge the autoupdater plugin into there?

Hm, i don't like the idea of merging these plugins together. Merging would create one very big plugin, and the functionality is quite distinct... Yes, the dashboard-plugin is a global notification-area. And it is quite nice to offer a "react on this" to a notification when already in an admin-area. But the functionality itself don't have to be part of the dashboard-plugin for that to achieve.
Maybe the dashboard-plugin should have a hook for adding generic notifications to it, kind of a duplicate of backend_frontpage_display (but when the dasboard is on, we know the user wants to use the dashboard-area).

[Lux]

I'd be prefer not to mess with the database-structure with the plugin. Is the index something that should be generated by the creator of these tables?

I added quite some indexes which came out of the analysis of the slow query log. This is normal optimisation work.

Cheers

Dirk

[onli]

I just commited the plugin.

Dirk, it is perfectly fine to add those indexes on a blog you administrate yourself. But, if not absoluteley necessary, i don't want to change the db with a plugin - maybe some other plugin wants also to create an index on timestamp and chooses another name or something like that.

That's why i asked if the index is always a good idea, because then it would be possible to create the index when creating the table, in the proper place. If the missing index really creates problems, we should check that way.