Separate DB by yearly archive of WordPress

1 comment

1. I needed this for a client recently (he wanted to do the same but be able to create a archive by himself in the wordpress backend) and couldn’t find a good plugin for it. It’s pretty easy to custom code this actually. You just need to make your install a network install and then place a custom install.php in your wp-content folder to overrite the wp_install_defaults() function. In this function you just have to write a few SQL statements that move over the content from one site to the other.

   In the end the only thing I needed to do was to create this custom install.php (you may need to modify this to suit your needs, but I guess it’s a good starting point):

   <?php
   
   
   function wp_install_defaults($user_id) {
       global $wpdb, $table_prefix;
   
   
       // we'll truncate the options table later on, these are the options we want to keep, therefor we fetch them here.
       $options = array('siteurl', 'blogname', 'blog_description', 'home');
       $options = dlx_archive::getOptions($options, $table_prefix);
   
       $wpdb->query("TRUNCATE {$table_prefix}options");
   
       // also alter the term table (plugin install will hook later, but we can't transfer now if the table scheme isn't synced).
       $wpdb->query("ALTER TABLE {$table_prefix}terms ADD `avhec_term_order` INT( 4 ) NULL DEFAULT '0'");
   
       $tables = array(
                   'terms',
                   'term_taxonomy',
                   'term_relationships',
                   'posts',
                   'postmeta',
                   'avhec_category_groups',
                   'options'
                   );
   
       // this will sync the data between main blog and archive.
       dlx_archive::copyData($tables, $wpdb->base_prefix , $table_prefix);
   
       // write the options back to the table.
       dlx_archive::writeOptions($options, $table_prefix);
   
       // delete all posts from the main blog (explude epaper category (term 18)
       $wpdb->query("DELETE {$wpdb->base_prefix}posts FROM {$wpdb->base_prefix}posts
                   LEFT JOIN {$wpdb->base_prefix}term_relationships wptr ON wptr.`object_id` = {$wpdb->base_prefix}posts.`ID` 
                   WHERE {$wpdb->base_prefix}posts.post_type = 'post'
                   AND wptr.term_taxonomy_id != 18");
   
       // we just imported new permalinks settings to the wp_options table, flush the rewrite rules now.
       flush_rewrite_rules();
   
   }
   
   
   class dlx_archive {
   
       static function copyData(array $tables, $source_prefix, $target_prefix) {
   
           global $wpdb;
   
           foreach($tables as $table) {
                   $sql = "INSERT " . $target_prefix . $table . " SELECT * FROM " . $source_prefix . $table;
                   $wpdb->query($sql);
           }
       }
   
       static function getOptions($array, $table_prefix) {
   
           foreach($array as $option) {
               $options[$option] = self::getOptionValueFromDB($option, $table_prefix);
           }
   
           return $options;
       }
   
   
       static function getOptionValueFromDB($key, $table_prefix) {
   
           global $wpdb;
   
           $option = $wpdb->get_row("SELECT * FROM {$table_prefix}options WHERE option_name = '{$key}'");
   
           return $option->option_value;
       }
   
   
       static function writeOptions($options, $table_prefix) {
   
           global $wpdb;
   
           foreach($options as $key => $val) {
                   $sql = "UPDATE {$table_prefix}options SET option_value = '{$val}' WHERE option_name = '{$key}'";
                   $wpdb->query($sql);
           }
       }
   }
   

   If you have a lot of plugins installed you also need to care about the custom tables individually. I only had one plugin installed (needed to modify avhec_term_order) only.

   Basically what will happen if you create a new site in the network administration then, is it will move all the posts from your main blog into your archive blog.

   Performance wise MySQL just doesn’t handle large tables well, there is no performance benefit in using a total seperate database. It’s enough to use seperate tables in my experience. But if you for some reason really want to have seperate database (the above solution is only based on tables as this is how WPMU works) you can have a look at HyperDB.

Comments are closed.