Question about sharing entries between serendipity instances

[eburrows]

I am in the position where I need to share entries between two serendipity boxes. I need to be able to synchronize the data from one to the other. I have looked at a few options such as using the RSS syndicator and creating mysql dumps and parsing out the relevant insert statements but I am unsure what would be the best way to go about doing this.

I don't think that RSS is the way to go because all of the extended properties are not captured in the RSS feed and it is important to me that I be able to capture the user who submitted the entry. Maybe I am missing something and this is possible?

My other approach as listed above was to create daily backups of my one box and create an event plugin on my end that reads the database dump, parses the data out and then stores the results as a new text file. On the other box I would create an option in the admin panel that provided an interface (a generic SQL query window) where I could paste the contents of that file and then hit a submit button to enter the data into the database.

[eburrows]

Ran out of room with the initial post..

My issue with this is that I obviously can't be a straight insert into the other database because the primary keys on my entries may be different and overlap the entries on the other machine. If I removed the entry ids then I create a whole mess of how to efficiently map to the extendedproperties table to retrieve the rest of the values for the entry.

Has anyone enountered a situation similar to this? Is there a simpler way to go about this that wouldn't require so much data checking and massaging?

Sorry for the long extended post,
Evan

[judebert]

Sorry; I'm on vacation, and not well equipped to answer this one at this time. It's straightforward enough, but I haven't seen anything like it in Serendipity's capabilities.

Do you need to synchronize comments, too?

[eburrows]

No, at this point I am not all that much concerned with the comments. Our scenario is we have a production server and a server that is used for testing and vetting ideas and potential entries. So once good entries are created on the testing machine the goal is to "somehow" get them onto the production machine with everything in tact. Currently, right now like I said any comments that are created on the testing machine in relation to entries would be comments posted by users to determine whether the article is relevant enough to post to the production server.. so in that case those comments won't need to be captured and carried over to the production server. I am assuming that this isn't too big of an issue but I am not a database guru and was worried if i used the insert statements that are created by mysqldump or phpmyadmin and that the primary keys would conflict with the database that we would be syncing with. Maybe another option might be to extend the RSS syndicator and add a few extra fields in the RSS XML feed that describes the author, etc. I am just trying to figure out the simplest way to do this because the syncronization might be on a daily basis and if the process to do this is brutal then it is going to be an extreme downer for me to do this every day.

Thanks for the post and any additional ideas would be great!!
thanks,
Evan

[garvinhicking]

Hi!

There are two solutions for this: The cool and pretty plugin solution, and the nasty but fast PHP solution.

The pretty plugin solution would transform an entry and all related data (entryproperties, entrycat, comments, references) into XML. Then it would post that XML data to an API endpoint. That API endpoint would be the event plugin on the "other side". It would take the XML, and transform it into SQL statements and then automatically insert that into the target database. Posting the XML could be done from the entry backend, in the "extended entry" section with a link that opens an external_plugin hook of the plugin which takes care of the POSTing.

This would take me some time to develop, and it seems rather specific use, so I currently can't really do that for free.

However the quick+dirty solution should work just as good, even though manual interaction would be required.

It works like this: It creates the SQL you need to straight insert that into the "other" database. It makes uses of MySQL's variable assignment variables to migrate the numbered auto-increment key.

Here you go:

Code: Select all

<?php
@define('PLUGIN_IMPORT_EXPORT_TITLE', 'Liberty Inc. Shipping - Import/Export your Goods! [ALPHA]');

class serendipity_event_importexport extends serendipity_event {
    var $title = PLUGIN_IMPORT_EXPORT_TITLE;
    function introspect(&$propbag) {
        global $serendipity;

        $propbag->add('name', PLUGIN_IMPORT_EXPORT_TITLE);
        $propbag->add('description', '');
        $propbag->add('event_hooks',  array('backend_display' => true));
        $propbag->add('author', 'Garvin Hicking');
        $propbag->add('version', '1.0');
        $propbag->add('requirements',  array(
            'serendipity' => '0.8',
            'smarty'      => '2.6.7',
            'php'         => '4.0.0'
        ));
        $propbag->add('groups', array('FRONTEND_EXTERNAL_SERVICES'));
        $propbag->add('stackable', false);
    }

    function generate_content(&$title) {
        $title = PLUGIN_IMPORT_EXPORT_TITLE;
    }

    function export_items($table, $primary_key, $ref_key, $primary_key_value) {
        global $serendipity;

        $result = serendipity_db_Query("SELECT * FROM {$serendipity['dbPrefix']}{$table} WHERE $ref_key = $primary_key_value", false, 'assoc');
        foreach ($result AS $row) {
            $row[$ref_key] = '@last';
            if ($primary_key !== null) {
                unset($row[$primary_key]);
            }
            
            foreach($row AS $key => $val) {
                if ($val != '@last') {
                    $row[$key] = "'" . serendipity_db_escape_string($val) . "'";
                }
            }
            echo "INSERT INTO {$serendipity['dbPrefix']}{$table} (" . implode(', ', array_keys($row)) . ") VALUES (" . implode(', ', $row) . ");\n";
        }
    }

    function event_hook($event, &$bag, &$eventData, $addData = null) {
        global $serendipity;

        $hooks = &$bag->get('event_hooks');

        if (isset($hooks[$event])) {
            switch($event) {
                case 'backend_display':
                    echo '<fieldset><legend>' . PLUGIN_IMPORT_EXPORT_TITLE . '</legend>';
                    
                    if (!isset($eventData['id'])) {
                        echo 'You can only export entries that are already saved';
                        return true;
                    }

                    echo 'Use this SQL query to import the entry you are currently viewing into a seperate MySQL-Database:' . "<br />\n";
                    echo '<textarea style="width: 100%; height: 200px">';
                    $this->export_items('entries', 'id', 'id', $eventData['id']);
                    echo "SELECT @last:=LAST_INSERT_ID();\n";
                    
                    $this->export_items('references',     'id',  'entry_id', $eventData['id']);
                    $this->export_items('entryproperties', null, 'entryid',  $eventData['id']);
                    $this->export_items('entrycat',        null, 'entryid',  $eventData['id']);
                    $this->export_items('comments',        'id', 'entry_id', $eventData['id']);

                    echo '</textarea>';
                    echo '</fieldset>';
                    
                    return true;
                    break;

                default:
                    return false;
                    break;
            }
        } else {
            return false;
        }
    }
}
/* vim: set sts=4 ts=4 expandtab : */

HTH,
Garvin