Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am given a WordPress multisite and need to extract information from the site. I have written a stored procedure that recursively builds a tree relationship for posts of a certain kind. Now I am wondering how to push the stored procedure to the databases of all the sites.(Since this is a multisite configuration, each site(subdomain in this case) has a separate database.)

Please help me in this regard.

WordPress version: 3.5.1 Multisite config type: subdomains

//code needs refinement

DELIMITER //
CREATE PROCEDURE getPath(IN post_id INT, IN return_path TEXT)

BEGIN

 DECLARE done INT DEFAULT 0;
 DECLARE a INT;
 DECLARE b TEXT;
 DECLARE cur1 CURSOR FOR (SELECT * FROM TempTable112);     
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 DROP TABLE IF EXISTS TempTable112;

 SET max_sp_recursion_depth := 10;

 CREATE TEMPORARY TABLE TempTable112 AS SELECT id,post_title FROM wp_101_posts WHERE post_parent = post_id and post_status='publish'; 

SET @s = CONCAT('SELECT * INTO OUTFILE ',"'", CONCAT('Sample',FLOOR(1000+RAND()*9999)),'.txt',"'",' FROM TempTable112');
PREPARE stmt2 FROM @s;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

    OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO a,b;
    IF done = 1 THEN
      LEAVE read_loop;
    END IF;
         SET return_path = CONCAT('/',return_path,b);
     CALL getPath(a, return_path);
  END LOOP;
  CLOSE cur1;
END //

DELIMITER ;
share|improve this question
1  
Please show us your code (and add much more info). –  kaiser Apr 3 '13 at 22:02

1 Answer 1

up vote 1 down vote accepted

Here are the Steps:

STEP 01 : Put code in a Text File

Open up a text editor (vi, nano, emacs) and place your code in it.

Save it as /tmp/mynewcode.sql

STEP 02 : Collect All Databases Names

MYSQLCONN=`-uroot -ppassword`
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema','mysql')"
mysql ${MYSQLCONN} -ANe"${SQLSTMT}" > /tmp/dbnames.txt

STEP 03 : Load Stored Procedure into every database mentioned in /tmp/dbnames.txt

for DB in `cat /tmp/dbnames.txt` ; do mysql ${MYSQLCONN} -D${DB} < /tmp/mynewcode.sql ; done

STEP 04 : Remove the files you made (OPTIONAL)

rm -f /tmp/dbnames.txt
rm -f /tmp/mynewcode.sql

That's it.

Give it a Try !!!

share|improve this answer
    
Thank you, Ronaldo! –  Praveen Apr 8 '13 at 11:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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