Concept

[aljawaid]

Add an interface to cleanup the Kanboard database

• Clean sessions table (remember me function)
• Archive projects, mark as disabled if not active within x years (code guide available in .cli commands)
• Leftover database tables from uninstalled plugins
• ...

[aljawaid]

https://kanboard.discourse.group/t/can-we-delete-entries-from-the-sessions-table/2611/14

[aljawaid]

#2

[alfredbuehler]

Keep this in mind.
After deleting a former plugins tables, delete also its records in the table plugin_schema_versions.

[aljawaid]

yes thanks for that reminder... thats it right? to completely remove a plugin with tables... delete new tables and the entry from plugin schema

[alfredbuehler]

There is no reason to keep that record, or I'm not aware of any. It will be rebuilt if the plugin is installed again.
But It might disturb in case you want to install an earlier version of that plugin.

[creecros]

This might be related:
7ea71ce
4b97a6f

added ability to select extra columns and remove them. However, once removed, if you re-install the plugin, it is not re-adding the columns.

[aljawaid]

if you re-install the plugin, it is not re-adding the columns.

• Why not though? maybe a caching problem?

[creecros]

definitely not a caching problem. it must be associated to the plugin_schema_versions @alfredbuehler is referring to.

[creecros]

yes, I pointed that out already below.

I don't care if the actions are separate or automatic. but all the functions are now built. extra columns are columns that are extra, i.e. not defualt and most likely from a plugin. name it something different fine with me, but that's what I called it while testing functionality, because I needed to test everything.

[creecros]

renamed "extra columns" maybe that will make more sense:

[creecros]

Why this separation, two buttons? If you decide to remove a plugin, you have to remove ALL the plugins tables AND the record in plugin_schema_versions. Only with this, you can start over with the plugin, with any version. Just my 2 cents.

Added a single button that combines all these actions into 1, no separation. But left the separate buttons as well.

As for an argument for separation:

It is plausible, that someone does not want to remove a table for a plugin that they uninstalled, so that they can reinstall later and their data will remain. That is my only argument though. That said, once a plugin is uninstalled, there is nothing to identify where a table came from, so that user would need to know which tables belong to that plugin, unless i start checking repo homepage urls and searching their schemas online....it's a thought, but meh.

[aljawaid]

2 buttons and 1 button will both be good.

I can create a setting for developer mode.
In developer mode, it will be 2 buttons.

In normal mode, it will be one button.

Use case, if somebody like me wants to keep his data in the tables he knows about, he can just delete the setting from the schema tables and reinstall later.

I dunno, maybe I am chatting crap. Will be looking into this repo tomorrow.

[aljawaid]

renamed "extra columns" maybe that will make more sense:

Thanks.

[creecros]

I tested, and thats what it was, I deleted the schema related to group_assign, and then it remade the columns and tables. I'll look at how i can remove those when we delete, and now knowing those are there, there won't be any Unkown plugins, I should be able to check the extra columns and tables against those to find a name for uninstalled plugins

[creecros]

actually I take that back, plugin_schema_versions doesn't give me enough info to tell which plugin a table or column came from. So, that being said, all I can do is compare installed plugins to the plugin names listed in plugin_schema_versions table and remove any plugins that are not installed.

[creecros]

1. delete plugin tables
2. delete entry from plugin_schema_versions
3. delete extra columns (maybe a third button)

you have all 3 features now, i'll leave styling and how to present them up to you, but for now, i just made them function

[aljawaid]

as a reminder, and i would either note somewhere visible or have the plugin do these things automatically. But, When a plugin is not installed, there is no way to determine which plugin they came from, so the recommendation would be to remove ALL unknown tables, and ALL unknown columns, and then Purge plugin_schema_versions table. Otherwise, when a person re-installs a plugin that related to 1 of those tables, there could be an issue installing.

I will come back to this.

[creecros]

ya, I've thoroughly tested in sqlite, so cross your fingers with mysql. let me know.

[aljawaid]

Internal Error: SQL Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list'

Ive just pulled all your changes this week to have a look and carry on... I get this on loading the page /cleaner.php but Im not sure where it is coming from

[aljawaid]

Internal Error: SQL Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list'

Ive just pulled all your changes this week to have a look and carry on... I get this on loading the page /cleaner.php but Im not sure where it is coming from

6822792 seems to have fixed it.... the page loads now... let's see which other mysql errors come up... if the page loads, hopefully this may be the only one

[aljawaid]

Ive done this part too

[creecros]

Added a button to Automatically remove: ALL unknown tables, ALL unknown columns, and Purge plugin_schema_versions table. In that order.

[aljawaid]

I've always been confused in the coding world... is purge the same as delete?

[alfredbuehler]

In the case of the plugin_schema_versions table, it means deleting obsoleted records. But to purge means “cleaning up” and this is not always to delete something.

[aljawaid]

I've finished this part. Hope I have worded it correctly

[alfredbuehler]

Any thoughts regarding the settings table? There is AFAIK no official convention for assigning a record to a plugin. Personally, I use a prefix, but this not always clear and voluntary.

[aljawaid]

Hello I think the settings table can only be reset to whatever is shown as the default on creation. There is no way to link a record to the plugin. Afte 10 plugins I learnt that it is advised to prefix a record with the plugin name and i think most others would pobably have not done it too.

[creecros]

Officially, no. I think ot could be done tho. Wouldn't be much different than figuring out what tables or columns came from plugins.
Use what we know and then use that to make deductive reasoning.

[creecros]

I'll give an example.

Hypothetically, Let's say KB out of the box has 10 columns in the settings table.

1. Check settings table, and we determine there are 15 columns.
2. Now we know there are 5 extra columns.
3. We iterate through each extra column name and perform a set of functions that search the code of every plugin installed, asking: does this name appear in the code?
4. If so, it came from this plugin. If not, it came from an unknown plugin that no longer is installed.
5. Then you can determine what is not needed.

This is similar to what is going on with the tables and columns algorithms, although, it is a bit simpler since we only have to check the schema files for each plugin. In this case, we need to check, just about every php file in each plugin.

[aljawaid]

I just want to say, thanks to both of you for your support and advice on this plugin but also on other plugins.

I worked on the interface for this plugin all night and it's now starting to make sense to me about all the functions.

@creecros has been fantastic in the functions and to be honest without them functions, this was just one confused plugin from my side.

I think I should be able to release it soon. I'm just going to do the sessions table today and finish off designing the modals and then we have an initial release

I have numbered the jobs, per section so in the forums, for quick fixes, people can mention the solution with the specific job number.

[aljawaid]

Interface update so far