Truncate table

[MarT]

Hi
I am wonder if I will do

Code: Select all

mysql> truncate table [tablename];

for all tables to reset IDs, will affect functionality of store system?

[admin]

You must not do that because a table 'structure' contains the site's structure.

[srobert72]

How can we do to delete demo data in fresh installation if we want to start with empty new site ?

With an old version of PhotoVideoStore I did that :
SQL : cleanDB.sql

Code: Select all

TRUNCATE `ads`;
TRUNCATE `audio`;
-- TRUNCATE `audio_fields`;
-- TRUNCATE `audio_format`;
-- TRUNCATE `audio_source`;
-- TRUNCATE `audio_types`;
TRUNCATE `blog`;
TRUNCATE `blog_categories`;
TRUNCATE `blog_comments`;
TRUNCATE `category`;
TRUNCATE `commission`;
-- TRUNCATE `components`;
-- TRUNCATE `content_type`;
TRUNCATE `coupons`;
-- TRUNCATE `coupons_types`;
-- TRUNCATE `credits`;
TRUNCATE `credits_list`;
-- TRUNCATE `currency`;
TRUNCATE `downloads`;
-- TRUNCATE `events`;
TRUNCATE `favorite`;
-- TRUNCATE `ffmpeg`;
-- TRUNCATE `files`;
TRUNCATE `friends`;
TRUNCATE `gateway_2checkout`;
TRUNCATE `gateway_authorize`;
TRUNCATE `gateway_cashu`;
TRUNCATE `gateway_ccbill`;
TRUNCATE `gateway_chronopay`;
TRUNCATE `gateway_egold`;
TRUNCATE `gateway_enets`;
TRUNCATE `gateway_epassporte`;
TRUNCATE `gateway_epoch`;
TRUNCATE `gateway_eway`;
TRUNCATE `gateway_google`;
TRUNCATE `gateway_linkpoint`;
TRUNCATE `gateway_moneybookers`;
TRUNCATE `gateway_nochex`;
TRUNCATE `gateway_paypal`;
TRUNCATE `gateway_platnosci`;
TRUNCATE `gateway_secpay`;
TRUNCATE `gateway_segpay`;
TRUNCATE `gateway_webmoney`;
TRUNCATE `gateway_worldpay`;
-- TRUNCATE `handside`;
-- TRUNCATE `homepages`;
-- TRUNCATE `images`;
-- ???? TRUNCATE `items`;
-- TRUNCATE `languages`;
-- TRUNCATE `languages_menu`;
-- TRUNCATE `licenses`;
-- TRUNCATE `menu`;
TRUNCATE `messages`;
-- TRUNCATE `module_column`;
-- TRUNCATE `module_sort`;
-- TRUNCATE `module_system`;
-- TRUNCATE `module_table`;
-- TRUNCATE `navigation`;
TRUNCATE `news`;
TRUNCATE `newsletter`;
-- TRUNCATE `notifications`;
TRUNCATE `orders`;
TRUNCATE `orders_content`;
-- TRUNCATE `pages`;
TRUNCATE `payments`;
-- TRUNCATE `payout`;
-- TRUNCATE `payout_price`;
TRUNCATE `people`;
TRUNCATE `people_access`;
TRUNCATE `photos`;
-- TRUNCATE `potential`;
-- TRUNCATE `prints`;
TRUNCATE `prints_items`;
TRUNCATE `reviews`;
-- TRUNCATE `role`;
-- TRUNCATE `settings`;
-- TRUNCATE `shipping`;
-- TRUNCATE `sideboxes`;
-- TRUNCATE `sizes`;
-- ???? TRUNCATE `structure`;
-- TRUNCATE `subscription`;
TRUNCATE `subscription_list`;
TRUNCATE `support`;
-- TRUNCATE `tax`;
-- TRUNCATE `templates`;
TRUNCATE `testimonials`;
-- TRUNCATE `type_text`;
TRUNCATE `users`;
TRUNCATE `users_access`;
TRUNCATE `users_ip_blocked`;
TRUNCATE `users_login_failed`;
-- TRUNCATE `users_settings`;
-- TRUNCATE `users_types`;
-- TRUNCATE `user_category`;
TRUNCATE `vector`;
-- TRUNCATE `vector_types`;
TRUNCATE `videos`;
-- TRUNCATE `video_fields`;
-- TRUNCATE `video_format`;
-- TRUNCATE `video_frames`;
-- TRUNCATE `video_ratio`;
-- TRUNCATE `video_rendering`;
-- TRUNCATE `video_types`;
TRUNCATE `voteitems`;
-- TRUNCATE `watermark`;


WWW files : cleanWWW.txt

Code: Select all

rm -rf /home/photovideostore/photos/content/upload_*
rm -rf /home/photovideostore/photos/content/photopreupload/*
rm -rf /home/photovideostore/photos/content/audiopreupload/*
rm -rf /home/photovideostore/photos/content/videopreupload/*
rm -rf /home/photovideostore/photos/content/vectorepreupload/*
rm -rf /home/photovideostore/photos/content/????

rm /home/photovideostore/photos/content/photo*.jpg
rm /home/photovideostore/photos/content/user*.jpg
rm /home/photovideostore/photos/content/avatar*.gif


But I didn't update it for last version 01-apr-2011. There are new tables (118 instead of 105).
Maybe it could be a work base to improve it.

[srobert72]

Here is SQL cleanup script upgraded for 118 tables.

SQL : cleanDB.sql

Code: Select all

TRUNCATE ads;
-- TRUNCATE affiliates_settings;
TRUNCATE affiliates_signups;
TRUNCATE affiliates_stats;
TRUNCATE audio;
-- TRUNCATE audio_fields;
-- TRUNCATE audio_format;
-- TRUNCATE audio_source;
-- TRUNCATE audio_types;
TRUNCATE blog;
TRUNCATE blog_categories;
TRUNCATE blog_comments;
-- TRUNCATE caching;
TRUNCATE category;
TRUNCATE commission;
-- TRUNCATE components;
-- TRUNCATE content_type;
TRUNCATE coupons;
-- TRUNCATE coupons_types;
-- TRUNCATE credits;
TRUNCATE credits_list;
-- TRUNCATE currency;
TRUNCATE downloads;
-- TRUNCATE events;
TRUNCATE examinations;
TRUNCATE favorite;
-- TRUNCATE ffmpeg;
-- TRUNCATE files;
-- TRUNCATE filestorage;
TRUNCATE friends;
TRUNCATE gateway_2checkout;
TRUNCATE gateway_authorize;
TRUNCATE gateway_cashu;
TRUNCATE gateway_ccbill;
TRUNCATE gateway_chronopay;
TRUNCATE gateway_clickbank;
TRUNCATE gateway_egold;
TRUNCATE gateway_enets;
TRUNCATE gateway_epassporte;
TRUNCATE gateway_epoch;
TRUNCATE gateway_eway;
TRUNCATE gateway_google;
TRUNCATE gateway_linkpoint;
TRUNCATE gateway_moneybookers;
TRUNCATE gateway_multicards;
TRUNCATE gateway_nochex;
TRUNCATE gateway_paypal;
TRUNCATE gateway_platnosci;
TRUNCATE gateway_secpay;
TRUNCATE gateway_segpay;
TRUNCATE gateway_webmoney;
TRUNCATE gateway_worldpay;
-- TRUNCATE handside;
-- TRUNCATE homepages;
-- TRUNCATE images;
-- ???? TRUNCATE items;
-- TRUNCATE languages;
-- TRUNCATE languages_menu;
-- TRUNCATE licenses;
-- TRUNCATE menu;
TRUNCATE messages;
TRUNCATE models;
-- TRUNCATE module_column;
-- TRUNCATE module_sort;
-- TRUNCATE module_system;
-- TRUNCATE module_table;
-- TRUNCATE navigation;
TRUNCATE news;
TRUNCATE newsletter;
-- TRUNCATE notifications;
TRUNCATE orders;
TRUNCATE orders_content;
-- TRUNCATE pages;
TRUNCATE payments;
-- TRUNCATE payout;
-- TRUNCATE payout_price;
TRUNCATE people;
TRUNCATE people_access;
TRUNCATE photos;
-- TRUNCATE potential;
-- TRUNCATE prints;
TRUNCATE prints_items;
TRUNCATE reviews;
-- TRUNCATE role;
TRUNCATE search_history;
-- TRUNCATE settings;
-- TRUNCATE shipping;
-- TRUNCATE sideboxes;
-- TRUNCATE sizes;
-- ???? TRUNCATE structure;
-- TRUNCATE subscription;
-- TRUNCATE subscription_limit;
TRUNCATE subscription_list;
TRUNCATE support;
-- TRUNCATE tax;
-- TRUNCATE templates;
TRUNCATE testimonials;
-- TRUNCATE type_text;
TRUNCATE users;
TRUNCATE users_access;
TRUNCATE users_ip_blocked;
TRUNCATE users_login_failed;
TRUNCATE users_qauth;
-- TRUNCATE users_settings;
TRUNCATE users_signup;
-- TRUNCATE users_types;
-- TRUNCATE user_category;
TRUNCATE vector;
-- TRUNCATE vector_types;
TRUNCATE videos;
-- TRUNCATE video_fields;
-- TRUNCATE video_format;
-- TRUNCATE video_frames;
-- TRUNCATE video_ratio;
-- TRUNCATE video_rendering;
-- TRUNCATE video_types;
TRUNCATE voteitems;
-- TRUNCATE watermark;


TODO:
I don't know how to cleanup STRUCTURE and ITEMS tables.

[admin]

You MUST NOT modify 'structure' table because it will break everything.

I don't advice to truncate the tables at all.

[mj72]

I had trouble deleting the demo audio/video/vector files, but this seems to work:

1) General settings: Allow audio/video/vector files
2) Delete in Catalog
3) Templates, Caching, empty cache (removes the html-files)