What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 127 Stack Exchange communities.

How can the Stack Overflow data in XML be converted into a MySQL dump?

It will be brilliant to have it for mysqlimport.

share|improve this question

migrated from stackoverflow.com Jun 14 '10 at 22:12

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers

It's not MySQL, but I have imported the data from the dump into a SQLite database:

http://sof.modos.org/forum/viewtopic.php?f=7&t=4

If you're interested in analyzing the data, you can use the same sort of queries that you'd use in MySQL in SQLite. I'm also working on making the script I used to import the data work with other databases, so keep an eye on the (unofficial) forum for updates.

share|improve this answer
    
No seeds to this torrent :( –  Assaf Lavie Jun 13 '09 at 16:19
    
I'm seeding it from modos.org, and it looks like there's at least one other seeder - maybe try refreshing the tracker info? –  Kyle Cronin Jun 13 '09 at 16:28
    
...if you really can't get any seeds, I can hook you up with a direct download link. PM me on the forum if you need one. –  Kyle Cronin Jun 13 '09 at 16:34
    
The link does not work anymore. Would you still have this file somewhere? Or even better, the updated sql file? –  user1029296 Jun 3 '13 at 15:47
    
@user1029296 I got your email, I'll see if I can find it and get back to you tonight. –  Kyle Cronin Jun 3 '13 at 16:27
    
Thank you so much! Fingers crossed! I'll be waiting for your email. –  user1029296 Jun 3 '13 at 18:49
add comment

If I'd use PHP for the import, I wouldn't use preg_match_all but XMLReader.
The XML files all have a similar structure. Each element <row> stands for one record and the attributes correspond to a field of that record. Each attribute is present in all records. Now all you have to do is to create tables with field names that are exactly like the attribute names in the XML files and write a simple script that puts the value of an attribute in the table field with the same name.

First attempt, without error/schema handling, almost untested:

<?php
    $pdo = new PDO('mysql:host=???;dbname=???', '???', '???');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // I'm using a Gentoo-patched version of PHP -> default connection charset=utf8
    // You might have to take care of that.

    foreach(array('users','posts','comments','votes', 'badges') as $source) {
        echo $source, "\n";
        $reader = new XMLReader;
        $reader->open($source.'.xml') or die('!open '.$f);
        $bFirst = true;
        while ($reader->read()) {
            if ( XMLREADER::ELEMENT===$reader->nodeType && 'row'===$reader->localName) {
                if ($bFirst) {
                    $bFirst = false;
                    $params = array();
                    $stmt = createStmt($pdo, $reader, $source, $params);
                }
                $reader->moveToFirstAttribute();
                do {
                    $params[$reader->name] = $reader->value;
                } while ( $reader->moveToNextAttribute() );
                $stmt->execute();
            }
        }
    }
    function createStmt($pdo, $reader, $table, &$bindArr) {
        $fields=array();
        $reader->moveToFirstAttribute();
        do {
            $fields[] = $reader->name;
        } while ( $reader->moveToNextAttribute() );
        $query = sprintf('INSERT INTO %s (%s) VALUES(:%s)', $table, join(',',$fields), join(',:',$fields));
        echo 'query: ', $query, "\n";
        $stmt = $pdo->prepare($query);
        foreach($fields as $f) {
            $bindArr[$f] = '';
            $stmt->bindParam(':'.$f, $bindArr[$f]);
        }
        $reader->moveToElement();
        return $stmt;
    }

With the MySQL Connector/Net and System.Xml.XmlReader it should be possible to do almost exactly the same thing with C#/.NET.

I wouldn't put too much effort in error handling, schema testing, "but what if", etc. here. If you need something beyond "just fiddling with the data" there are other tools; you could even use Hibernate for that ;-)

share|improve this answer
    
VolkerK: How long does it take for your code to write all the files to the database? I ask because I'm writing something in C# using the MySQL Connector/Net and System.Xml.XmlReader as you mention at the bottom of your post, but this takes hours to run (about 10 hours writing one statement at a time, and somewhere between 3 and 4 i think when writing 500 per query - e.g. Insert into table values (rec1), (rec2), (rec3). ). I'd like this to be faster, but don't have a baseline for normal times. I've got a question on this site regarding fast ways to do this, but haven't gotten much of a response –  AgentConundrum Sep 24 '09 at 7:30
    
I just let the php script run and it took <20 minutes to import the "Sep 09" data dump on my BenQ S73G notebook (T2400, 1GB ddr2, all on one hdd). Windows XP, PHP 5.3.0, MySQL 5.1.37, MyISAM, no indices. And though this is only a dumb import I think you should be able to beat both the execution time ...and the hardware ;-) the votes table took the longest (8 minutes), followed by posts (7 minutes) –  VolkerK Sep 25 '09 at 0:49
add comment

Just took a look at the data.

It wouldn't be too hard to do a pattern matching preg_match_all using PHP to get all the data and then print it out into SQL statements. (if someone hasn't already done it)

share|improve this answer
4  
PHP has lots of XML parsers, preg_match_all is out of discussion in my opinion. –  Ionuț G. Stan Jun 15 '09 at 4:18
    
true true, i unfortunately am an addict for regular expression :P (not necessarlly a good thing :P) –  Shadi Almosri Jun 15 '09 at 9:03
add comment

I made a program to create a SQL file to load SO data into a PostgreSQL database. Reading it, it seems there are few PostgreSQL-specific things in it so you may try to port it.

share|improve this answer
add comment

You must log in to answer this question.