New Import Process Required

[Icarus]

Hello everybody,

I dont know if I am alone on this or not, but I feel that the Import functionality is not good enough for production sites.
I mean, it is way too slow and memory consuming, that if you want to import a full catalog of products to a live server, you will have to
a) make sure your files are small enough to avoid reaching memory limits of your hosting provider
disable access to your shop while you import.

On the other hand, when doing a database dump import, this takes less than a second for the entire database.

So, I am now investigating along these lines:
How do I create a database dump that contains ONLY the products and categories?

My desired process is this:
1. Update the catalog offline, on my local server
2. Create a database dump of the products and categories tables
3. Upload all the images that were generated (FTP mirror)
4. Import the SQL dump to the live server

Of course, I want to avoid overriding the live customers and orders data etc.

Can someone please tell me what are the tables that I need to use in order to do that?

And for the PrestaShop team - I wish the database backup menu had these options:
1. Import back (restore from backup)
2. The backup process to generate DROP TABLE IF EXIST before each create table
3. The backup page to have checkboxes of each primary entity in presta, that I can choose if I want it to be included in the backup or not (Products / Categories / Customers etc.)



EDIT:
I think I figured it out.
I have created a backup sql dump with all my catalog.
I have then emptied the catalog, and created another backup
I compared the two files with a DIFF software to see the modified tables, they are:

ps_category_*
ps_image_*
ps_product_*
ps_tag_*

Now, I plan on doing a short script that takes the SQL dump file, removes all the other tables from it, leaving only these ones + adding a DROP TABLE IF EXIST before each table create, and you have a nice SQL that can be easily imported.

[VIXUS]

sounds interesting...

[Icarus]

Yes, I figured I would do it even simpler.
I created a PHP that does the backup irself, only now it will use the mysqldump executable and generate a backup only of the desired tables (with DROP and everything).
This PHP will also be capable of restoring, so it will be a one click "migrate" for me.

This of course, is only good if you have some sort of FTP mirror from your local to your live, or if you otherwise copy all the images created on your local to the live.

I know this may not be the setup that everyone is working by, but personally, I like to arrange my catalog offline, and push it up with one click.
If anyone is interested maybe I can post the script.

Anyways, testing its first version now.

[Paulw01]

Sounds very good.
Do you have any tips regarding streamlining the csv files? I only have 3600 products with 2000 categories and already am having to split into 26 chunks for the products. Cannot upload file in one go. My csv file only (800kb) Products will go up to approx 15000

Thanks

[Icarus]

Oh man....
I think I have a pretty good solution until Presta import becomes more efficient.
Assuming you are not a complete beginner in web development, I would recommend this procedure:

1. I have my own script generating my CSV file - try generating as few as possible, it will be easier to work with. Personally, I generate only one. If you intend to use it on local server only, you should also generate only one. If your server times out, increase the timeout interval in your PHP config.

2. Now that I have my ready CSV, I go into my LOCAL machine to Presta import and import all products to localhost

3. I view the local site, make sure it all looks neat, if not Goto 1

4. Now, I am running a very simple PHP script that is doing a backup on the SQL level - backing up only the tables I mentioned above. This backup.sql is created in some folder INSIDE my htdocs folder (some protected admin folder or wherever you want)

6. I run my FTP mirroring software (not standard FTP client, but mirroring that automatically copies all my modified files up) - this process also copies the backup.sql file and all the images that were generated by the import process.

7. Now, I run a small php script on the LIVE server which is doing the opposite - it essentially doing an SQL import, using mysql executables on the live server. This step is taking half a second, as opposed to the Presta several minutes - so no interruption to the users.

8. View live site, make sure its all good, if not, Panic then Goto 1...

So, to recap, assuming you have FTP mirroring (which you should, regardles of Presta), there are only two small components to this plan:
1. Backup of selected tables - done on local
2. Restore of selected tables - done on live



EDIT:
Note that if I had 15,000 products, I would have probably tried a different approach, completely independent of Presta's import process.
I would have probably attempted to reverse engineer the import process, see what it is doing (e.g. creating images) and create a standalone windows executable (or a more efficient PHP) to do the work.
I imagine that importing 15,000 products to presta on a regular basis could be a nightmare.

[Paulw01]

I'm afraid that's well and truly beyond my ability, just going to have to wade through the uploading of csv's !

Just about managing to get my head around using presta and using everything else thats involved in setting up a site.
I am a complete beginner to this, but am determined to get this site working:)
(reinstall of presta tonight after cleaning up the server (old uk2ecommerce), redo it all from scratch and see what happens)

[Actalex]

Hello Icarus,

I'am currently looking for solution about importing, and updating my shop database, and i'am really interested by your script. If it's working as well as you except, i will be really happy to get it. And i'am thinking about the second solution you have mentioned, by tweaking the current import file.

Thanks.

[Icarus]

Alrighty then.

Attached is the script I am using.
Personally, I have it in a subfolder "pms" (Prestashop Migration Script) under my admin folder as "pms/index.php"
And also, I have created a subfolder underneath for the dump files - "pms/dumps"

I am warning you that this script may not work on your system as is, and you may have to get your hands dirty and dig inside the file to change stuff.
I have tried to place all of the things you may need to configure on top, but still, this script is not meant to be used "as is"

This script is using mysql and mysqldump executables that should be present and available on your xampp system and on your live system. You may have to ask your hosting company if it is.

The basic principle is this:
The file gets an action request in the query string (backup / restore / fullbackup etc)
It will then run mysql or mysqldump command line to backup/restore the needed tables to a dump file.

So I am running a catalog backup on local, uploading all my files (the dump files and images) to live, and running a restore on live.

Currently, there are 4 supported actions:
* Backup catalog (catalog = only the db tables that represent the products)
* Restore catalog
* Backup entire db
* Restore from any sql file

More instructions and comments inside the code but I assume no responsibility for the results and you should NOT use it if you do not feel comfortable that you understand what it does.

pms.php

[Actalex]

Thank you very much for your explantions, i will take care before using the script on a site in production. I'm not sure that all the stuff around my site will be compliant with your script, but anyway, it will certainly help me to understand how the prestashop database is working.

I have studied solutions like database browser : "http://www.dbsoftlab.com/Freeware/Database-Browser.html" directly in my local hosted site, but i'am always frightened to change data that are linked in other table, and then make my data base un consistant.

I have opened the AminImport.php file that handle the CSV import tabs, it's a complex file not easy to tweak.

So, i will try to improve the process with your script, thanks a lot.