2

How can i make archive my old content and save in different db with my cron job.

I want to store in different DB because i want to increase my site performance.

In drupal 7 what's the best way to do it ?

  • Do you want to archive specific content or your entire database? – Patrick Scheffer Feb 1 '16 at 10:34
  • i want to archive specific content types. Because in some content type i have more than 50,000 nodes. – visabhishek Feb 1 '16 at 11:18
4

Here's what you can do:

  • Add your archive database to your settings.php below your default database.

    $databases['my_other_db']['default'] = array(
      // Your secondary database's credentials here.
      // You will be able to explicitly connect to this database from your modules.
    );
    
  • In your cron script, load all nodes you want to archive. In this example I retrieve all nodes older than a month and no more than 50 at a time.

    $nodes_to_archive = array();
    
    $query = db_select('node', 'n')
      ->fields('node')
      ->condition('type', 'your_content_type')
      ->condition('created', REQUEST_TIME - (86400 * 30), '<')
      ->range(0, 50);
    $result = $query->execute();
    
    while($record = $result->fetchAssoc()) {
      $nodes_to_archive[] = $record;
    }
    
  • In the same script, switch from database and add the nodes to the archive (make sure the second database has the same structure as the existing one)

    db_set_active('my_other_db');
    
    foreach ($nodes_to_archive as $node) {
      db_insert('node')
        ->fields($node)
        ->execute();
    }
    
  • Still in the same script, switch back to the default database and remove the archived nodes.

    db_set_active();
    
    $nids = array();
    foreach ($nodes_to_archive as $node) {
      $nids[] = $node->nid;
    }
    
    node_delete_multiple($nids);
    

I haven't tested any of this so you might need to improve it here and there. If anything's unclear, please ask.

Here is a list of helpfull resources:

  • Thanks a lot @Patrick Scheffer. One more doubt how can i join my default db and my_other_db to create some statistic reports ? – visabhishek Feb 1 '16 at 13:23
  • What data do you want to display in those reports? – Patrick Scheffer Feb 1 '16 at 13:25
  • Like all nids count with different criteria, in this count I have to show archive nodes also... – visabhishek Feb 1 '16 at 16:55
  • It's not possible to query two databases at the same time. There are 2 things you can do: You could query them seperately, merge the results and cache it to prevent execution at every request or create a custom table in your active database where you update the stats every archive run. – Patrick Scheffer Feb 2 '16 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.