Sanitise/Clean database

[1sweetman]

Hi,

 

My database has become rather large and unwieldy of late (a couple of hundred Mbs) with all the updates/new products I am doing and I was wondering if there is either a php script, SQL command to run through phpmyadmin or a module that allows me to 'clean/sanitise' the history of my products so that only the latest most upto date product information  is retained but the 'history' of it's updates etc is wiped.

 

Thanks in advance for any help/advice/assistance.

 

Stuart

[1sweetman]

While researching solutions I can across this script and was wondering if someone can advise if is safe to run on prestashop database - or suggest alterations to get it working.

 

https://github.com/silverstripe-labs/silverstripe-recipes/blob/master/model/tools/SanitiseTasks.php

 

Thanks in advance for any assistance

[sttaq]

While researching solutions I can across this script and was wondering if someone can advise if is safe to run on prestashop database - or suggest alterations to get it working.

 

https://github.com/silverstripe-labs/silverstripe-recipes/blob/master/model/tools/SanitiseTasks.php

 

Thanks in advance for any assistance

 

I dont think it will work for PrestaShop.

 

The first thing you can do is use the following module:

 

http://www.prestashop.com/forums/topic/275483-module-database-clean-up-optimize-store-db-speed-up-your-store/

 

please read through link. Also please make sure that you backup your database before cleaning up.

[1sweetman]

Hi, thanks for info re the link.  I already have the module installed and use it regularly but it was more the products history I wanted to cleanse.  Some of my products have had 20-30 updates and I was hoping to get rid of some/all of the older entries and just keep the current/latest info.

[sttaq]

Hi, thanks for info re the link.  I already have the module installed and use it regularly but it was more the products history I wanted to cleanse.  Some of my products have had 20-30 updates and I was hoping to get rid of some/all of the older entries and just keep the current/latest info.

 

I don't think that prestashop stores history of changes to products.

 

If you have been adding same products with different IDs then you could end up with duplicate entries but then you should not have done this and I hope this is not the case?

[1sweetman]

Hi, no that is not the case....prestashop as far as I know doesnt store the history but the database does - my most popular product has had some 100+ updates over the last 18 months and each update to the product writes to the database - using a text editor/spreadsheet I can see all of the product changes most of which are redundant now.....I just want to clear them out and keep the most recent version (or even say just the last months changes)

 

Unfortunately the SQL commands are beyond my comprehension.

[parsifal]

Hi, no that is not the case....prestashop as far as I know doesnt store the history but the database does - my most popular product has had some 100+ updates over the last 18 months and each update to the product writes to the database - using a text editor/spreadsheet I can see all of the product changes most of which are redundant now.....I just want to clear them out and keep the most recent version (or even say just the last months changes)

 

Unfortunately the SQL commands are beyond my comprehension.

 

Hi 1sweetman. I'm with sttaq on this one:

1. For the purposes of this discussion, the database IS PrestaShop, because PrestaShop uses only the database to store dynamic data such as information about products.

2. AFAIK, PrestaShop by default doesn't keep older versions of product information (name, description, prices etc), if you change some of them, they are just overwritten in the database and you cannot access or revert to older values. In case you have such functionality, maybe it's because of a 3rd party module you installed at some time?

3. You claim that you used a text editor/spreadsheet and saw these older information in the database. Can you explain how exactly you did this (what steps you took, which database tables you inspected, etc)?

[1sweetman]

Hi sorry for any confusion re database and prestashop.

3 - I'm a curious bugger and like to learn so when database became rather large I experimented - in phpmyadmin I used export function to export a few or the larger tables and have a look.  I used gedit (word type programme on linux) to open them and have a look around.

As of 10 minutes ago I exported ps_product_lang and have selected n example from their (attached file)

From

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`, `description`, `description_short`, `link_rewrite`, `meta_description`, `meta_keywords`, `meta_title`, `name`, `available_now`, `available_later`)

I would expect description, description_short, link_rewrite, meta_description, meta_keywords, meta_title, name, available_now and available_later to change with every addition/alteration to the product.  Correct?

I wouldn't expect id_product and id_shop to change (and they don't)  Correct?

Personally with my little experience of mysql I wouldn't have expected id_lang to change but it does.  Is this correct?

As you will see from the attached file there have been 5 minor changes in this product (liquid volume, font  size, description etc) times 9000 products etc this soon becomes a very large table.

Ideally I only want/need the latest revision.

2 - I am not aware of any module installed that would have altered/adjusted/started this functionality or even if this functionality is 'wrong'

Again thanks for your assistance/help with this matter.

Example.txt