Club Drupal

# In the expat website, when we try to migarte it, the images not found

# and the only solution we found is to use this script to patch its database

# and so repair the links

use strict;

use warnings;

use DBI;

my $db = 'expat';

my $server = 'localhost';

my $user = 'root';

my $password = 'tata';

my $port = '';

#### Create DB connection

my $dbh = DBI->connect("DBI:mysql:database=$db;host=$server;port=$port", $user, $password, { RaiseError => 1, }) or die "Cannont connect to database $db";

# Update user's pictures

my $sql_select =<<"SQL";

SELECT uid, picture FROM expat_users

SQL

my $sql_update =<<"SQL";

UPDATE expat_users SET picture = ? WHERE uid = ?

SQL

my $req_select = $dbh->prepare($sql_select);

my $req_update = $dbh->prepare($sql_update);

$req_select->execute;

while (my ($uid, $picture) = $req_select->fetchrow_array)

{

    $picture =~ s#sites/assos.centrale-marseille.fr.expat/(.*)#sites/default/$1#;

    $req_update->execute($picture, $uid);

}

$req_update->finish;

$req_select->finish;

# Update other file

$sql_select = "SELECT fid, filepath FROM expat_files";

$sql_update = "UPDATE expat_files SET filepath = ? WHERE fid = ?";

$req_select = $dbh->prepare($sql_select);

$req_update = $dbh->prepare($sql_update);

$req_select->execute;

while (my ($fid, $file_path) = $req_select->fetchrow_array)

{

    $file_path =~ s#sites/assos.centrale-marseille.fr.expat/(.*)#sites/default/$1#;

    $req_update->execute($file_path, $fid);

}

$req_update->finish;

$req_select->finish;

$dbh->disconnect;

#!/usr/bin/env python3

# In the expat website, when we try to migarte it, the images not found

# and the only solution we found is to use this script to patch its database

# and so repair the links

import pymysql as mysql

import re

db = mysql.connect(host="localhost", user="root", passwd="tata", db="expat", charset='utf8')

with db:

    cur = db.cursor()

    ## Update user's pictures

    sql_select = """

    SELECT uid, picture FROM users

    """

    sql_update = """

    UPDATE users SET picture = %s WHERE uid = %s

    """

    cur.execute(sql_select)

    rows = cur.fetchall()

    for uid, picture in rows:

        picture = re.sub(r'sites/assos.centrale-marseille.fr.expat/files/(.*)', r'\1', picture)

        cur.execute(sql_update, (picture, uid))

    ## Update other files

    sql_select = """

    SELECT fid, filepath FROM files

    """

    sql_update = """

    UPDATE files SET filepath = %s WHERE fid = %s

    """

    cur.execute(sql_select)

    rows = cur.fetchall()

    for fid, file_path in rows:

        file_path = re.sub(r'sites/assos.centrale-marseille.fr.expat/files/(.*)', r'\1', file_path)

        cur.execute(sql_update, (file_path, fid))

    db.commit()

#!/usr/bin/env python3

import pymysql as mysql

import re

db = mysql.connect(host="localhost", user="root", passwd="tata", db="expatd7", charset='utf8')

with db:

    cur = db.cursor()

    # We fix the text format for comments and nodes

    update_text_format = "UPDATE {table} SET {format} = 'filtered_html' WHERE {format} = -1"

    cur.execute(update_text_format.format(table='field_data_body', format='body_format'))

    cur.execute(update_text_format.format(table='field_data_comment_body', format='comment_body_format'))

    ### We fix the images

    fetch_d6_infos = """

    SELECT node.title, files.filepath

    FROM node

    JOIN image ON node.nid = image.nid

    JOIN files ON files.fid = image.fid

    WHERE image.image_size =  '_original'"""

    # We prepare the infos from D6

    results = []

    rows = ()

    db_d6 = mysql.connect(host="localhost", user="root", passwd="tata", db="expat", charset="utf8")

    with db_d6:

        cur_d6 = db_d6.cursor()

        cur_d6.execute(fetch_d6_infos)

        rows = cur_d6.fetchall()

        for row in rows:

            results.append(list(row))

        for r in results:

            # filepath is sites/default/files/… instead of …

            r[1] = re.sub(r'sites/default/files/(.*)', r'\1', r[1])

    # To find the nid and fid in D7

    fetch_nid = """

    SELECT nid FROM node WHERE title = %s

    """

    fetch_fid = """

    SELECT fid FROM file_managed WHERE uri = %s

    """

    # We do the update

    update_file_usage = """INSERT INTO file_usage (fid, module, type, id, count)

        VALUES (%s, 'node', 'file', %s, 1)"""

    update_field_data_image = """

    INSERT INTO field_data_field_image

    (entity_type, bundle, deleted, entity_id, revision_id, language, delta, field_image_fid, field_image_alt, field_image_title, field_image_width, field_image_height)

    VALUES ('node', 'image_dans_la_galerie', 0, %s, %s, 'und', 0, %s, '', '', 275, 183)

    """

    for r in results:

        cur.execute(fetch_nid, (r[0]))

        nid = cur.fetchall()[0][0] # returns something like ((133L,),)

        cur.execute(fetch_fid, ('public://' + r[1]))

        fid = cur.fetchall()[0][0]

        cur.execute(update_file_usage, (fid, nid))

        cur.execute(update_field_data_image, (nid, nid, fid))

    ### We fix the taxonomy

    vid2termName_d6 = {3: 'Image Galleries', 4: 'Pays', 5: 'Tags', 6: 'Université partenaire'}

    vid2termName_d7 = {1: 'Tags', 2: 'Pays', 3: 'Université partenaire', 4: 'Image Galleries'}

    termName2vid_d7 = {value: key for key, value in vid2termName_d7.items()}

    vocabulary2table = {'Tags': 'field_data_field_tags', 'Pays': 'field_data_field_pays', 'Université partenaire': 'field_data_field_universite_partenaire',\

    'Image Galleries': 'field_data_field_image_galleries'}

    vocabulary2column = {'Tags': 'field_tags_tid', 'Pays': 'field_pays_tid', 'Université partenaire': 'field_universite_partenaire_tid',\

    'Image Galleries': 'field_image_galleries_tid'}

    # NB: profile are not migrated automatically

    fetch_d6_nodeTitle_termName = """

    SELECT node.title, term_data.name FROM node

    JOIN term_node ON node.nid = term_node.nid

    JOIN term_data ON term_node.tid = term_data.tid

    WHERE term_data.vid = %s AND type <> 'profile' ORDER BY node.nid

    """

    fetch_d7_tids = """

    SELECT name, tid FROM taxonomy_term_data

    """

    fetch_d7_nid_created = """

    SELECT nid, created FROM node WHERE title = %s

    """

    update_taxonomy_index = """

    INSERT INTO taxonomy_index (nid, tid, sticky, created) VALUES

    (%s, %s, 0, %s)

    """

    update_field_data_field = """

    INSERT INTO {}

    (entity_type, bundle, deleted, entity_id, revision_id, language, delta, {})

    VALUES

    ('node', 'image_dans_la_galerie', 0, %s, %s, 'und', %s, %s)

    """

    # We fetch node titles and taxonomy term name for each vocabulary in drupal 6

    nodeTitle_termName = {}

    db_d6 = mysql.connect(host="localhost", user="root", passwd="tata", db="expat", charset="utf8")

    with db_d6:

        cur_d6 = db_d6.cursor()

        for vid in vid2termName_d6.keys():

            cur_d6.execute(fetch_d6_nodeTitle_termName, (vid))

            vocabulary = vid2termName_d6[vid]

            nodeTitle_termName[vocabulary] = cur_d6.fetchall()

    # We fetch tids for each term in drupal 7

    termName2tid = {}

    cur.execute(fetch_d7_tids)

    rows = cur.fetchall()

    for row in rows:

        termName2tid[row[0]] = row[1]

    # We perform the update

    delta, previous_title = 0, '' # We need this for multiple term taxonomy

    for vocabulary, t in nodeTitle_termName.items():

        for content in t:

            title, term_name = content

            cur.execute(fetch_d7_nid_created, (title))

            rows = cur.fetchall()

            nid, created = rows[0]

            tid = termName2tid[term_name]

            # We take into account the value of delta

            if previous_title == title:

                delta += 1

            else:

                delta = 0

                previous_title = title

            cur.execute(update_taxonomy_index,\

                        (nid, tid, created))

            cur.execute(update_field_data_field.\

                        format(vocabulary2table[vocabulary],\

                               vocabulary2column[vocabulary]),

                         (nid, nid, delta, tid))

    db.commit()

#!/bin/bash

cd /var/www/expat

drush cc all

# We uninstall the good modules in drupal 6

modules2uninstall=(mapbox contemplate openlayers_ui openlayers_views openlayers flowplayer flowplayer3 wijering wijering4 swftools geotaxonomy views_export views_cloud content_profile_registration content_profile fivestar_comment)

for i in ${!modules2uninstall[*]} ; do

    drush -y dis ${modules2uninstall[$i]}

    drush -y pm-uninstall ${modules2uninstall[$i]}

done

cd /var/www/expatd7

# We install the good module in drupal 7

modules2install=(module_filter token addthis blog cas comment_notify imce lightbox2 masquerade pathauto ctools contact ckeditor views views_ui fivestar media)

for i in ${!modules2install[*]} ; do

    drush -n dl ${modules2install[$i]}

    drush -y en ${modules2install[$i]}

done

#modules2install_assos=(addthis lightbox2 votingapi fivestar)

#!/usr/bin/env python3

# This was created in order to delete the prefix of drupal database

# These prefix were useful when all sites were in the same database

import pymysql as mysql

import sys

import re

db = sys.argv[1]

prefix = sys.argv[2]

host = 'localhost'

passwd = 'tata'

user = 'root'

port = ''

db = mysql.connect(host=host, user=user, passwd=passwd, db=db, charset='utf8')

with db:

    cur = db.cursor()

    cur.execute('SHOW TABLES')

    tables = cur.fetchall()

    for table in tables:

        table = table[0]

        if re.match('^{}'.format(prefix), table):

            new_table = re.sub('^{}(.*)'.format(prefix), r'\1', table)

            cur.execute('DROP TABLE IF EXISTS {}'.format(new_table))

            cur.execute('RENAME TABLE {} TO {}'.format(table, new_table))

    db.commit()