import data from s9y

[Rei]

Hi, this seems like a dumb question but let me explain why i want to do this...

I imported existing datas from few different blog software into my s9y installation on my local machine, to merge the datas and to get rid of those unwanted posts and comments, restructure the categories etc.

Now i want to import only the categories, posts and comments related data into the hosted s9y (i don't feel like re-configure and re-install plugins for the hosted s9y and i like a neat database), but what is the best way to do it?

Another thing is, how can i change the author of all the existing posts to another current author, since trying to delete the existing author would disallow showing any entries written by him on the frontend.

[garvinhicking]

Hi!

S9y at this point has no importer for its own data. But you could use the plain SQL dumps to migrate from one installation to the other. A few SQL queries should do the job, but of course you need some basic knowledge of SQL/Databases.

Another thing is, how can i change the author of all the existing posts to another current author, since trying to delete the existing author would disallow showing any entries written by him on the frontend.

Also with an SQL query like

Code: Select all

UPDATE serendipity_entries SET authorid = X where authorid = Y

Regards,
Garvin

[Rei]

Hi Garvin,

I followed your instructions and successfully changed the authorid of all posts to #1 (yah, all the posts should be associated to the #1 of the hosted s9y.)

You mentioned that a few SQL queries should do the job i want... now i would really appreciate if you or someone who is free could throw me the necessary SQL queries code for me to complete the data transfer.

Thank you!

[Rei]

Or can i use this?

Code: Select all

INSERT INTO s9yserver.serendipity_entries * FROM s9ylocal.serendipity_entries

It seems like the following tables are what i need?
serendipity_category, serendipity_comments, serendipity_entries, serendipity_entrycat, serendipity_entryproperties, serendipity_entrytags, serendipity_permalinks, serendipity_references, serendipity_staticpage_categorypage

[garvinhicking]

Hi!

To migrate comments, categories and their associations to entries, you will need some sort of glue code. The ID of a category and an entry will change when you insert it to your new database.

To achieve what you want, let's say your hosted environment has the tables serendipity_entries and serendipity_category. Your old environment has those tables which you later need:

* s9y_entries
* s9y_entrycat
* s9y_category
* s9y_comments
* s9y_entryproperties

So, first make a SQL dump of your s9y_* tables. If they are called "serendipity_" as well, you need to use a text editor on your SQL dump and replace "`serendipity_" with "`s9y_" to rename the tables properly. Then import that SQL dump to your host, so that you have the serendipity_* and s9y_* tables in the same database.

Then you can use a PHP script like this, which you put as "migrate.php" into your serendipity directory, and it should look something like this:

Code: Select all

<?php
// Include the s9y framework, so that we can access the same Database.
include 'serendipity_config.inc.php';

// Helper function to upload all table data into a new one, keep
// the IDs for later use.
function migrate($table, $table2, $unsetid, &$assoc, $assoc_key = false, $assoc_array = false, $assoc_key2 = false, $assoc_array2 = false) {

    // Fetch old data
    $old_data = serendipity_db_query("SELECT * from " . $table);

    // Iterate each data
    foreach($old_data AS $idx => $data) {
        // Unset keys that are not allowed in the new db
        $assocval = $data[$unsetid];
        unset($data[$unsetid]);

        // Build SQL VALUES and KEY parts
        $keys   = array();
        $values = array();
        foreach($data AS $key => $val) {
            $keys[]   = "`" . $key . "`";
            $values[] = "'" . serendipity_db_escape_string($val) . "'";
        }
        
        // See if we must fixup the ID before inserting!
        if ($assoc_key) {
            $data[$assoc_key] = $assoc_array[$assoc_key];
        }
        
        if ($assoc_key2) {
            $data[$assoc_key2] = $assoc_array2[$assoc_key2];
        }

        // Insert into new table
        serendipity_db_query("INSERT INTO $table2 (" . implode(', ', $keys) . ") VALUES (" . implode(', ', $values) . ")");
        
        // Store new ID, we need that later!
        $newid = serendipity_db_insert_id();
        $assoc[$assocval] = $newid;
    }
}

// Store old entry ids, and old category ids here:
$assoc = array();

// Fetch all entries from the old DB, store their old and new IDs:
migrate('s9y_entries', 'serendipity_entries', 'id', $assoc['entries']);
migrate('s9y_entryproperties', 'serendipity_entryproperties', 'entryid', $assoc['entryproperties'], 'entryid', $assoc['entries']);
migrate('s9y_comments', 'serendipity_comments', 'id', $assoc['comments'], 'entry_id', $assoc['entries']);
migrate('s9y_category', 'serendipity_category', 'categoryid', $assoc['category']);

// Special case, because both ID values need to be fixed
migrate('s9y_entrycat', 'serendipity_entrycat', 'entryid', $assoc['entrycat'], 'entryid', $assoc['entries'], 'categoryid', $assoc['categoryid']);

I could not test this code here, so be sure to make a backup of all DB tables! Do not run this script more than once, because it would generate duplicates. If it fails, first reimport the backup, and only then run the import again.

I'm quite sure it should work, though.

Regards,
Garvin

[Rei]

Garvin, i am so sorry to bring so much trouble to you. I will test it out and feedback here at a later time.

Anyway, i found that most of the s9y users here are pretty good in programming, may be that's why i find it hard to get the info i need (as a normal user).

[garvinhicking]

Hi!

Garvin, i am so sorry to bring so much trouble to you. I will test it out and feedback here at a later time.

No trouble at all! That's why I'm here for. I hope to be able to help you.

Best regards,
Garvin

[Rei]

i got the following...

Parse error: syntax error, unexpected T_STRING in migrate_edit.php on line 9

any idea? well... when you free...

[garvinhicking]

Hi!

Seems you did a wrong copy and paste?

Try to pick the file from here:

http://nopaste.php-quake.net/down/13382

Regards,
Garvin

[Rei]

Got the file from the supplied link, still got error but it is a different one.

Code: Select all

Warning: Invalid argument supplied for foreach() in nopaste_13382.php on line 13

Not sure what i done wrong...

The following is what i did first and got the 1st error.

What i did:
- both databases (local-old and hosted-new) having the same prefix as s9y_
- made the sql dump of local-old, replace the prefix from s9y_ to serendipity_ [reverse of what you have mentioned]
- made the following changes to the migrate.php

Code: Select all

// Fetch all entries from the old DB, store their old and new IDs: 
migrate('serendipity_entries', 's9y_entries', 'id', $assoc['entries']); 
migrate('serendipity_entryproperties', 's9y_entryproperties', 'entryid', $assoc['entryproperties'], 'entryid', $assoc['entries']); 
migrate('serendipity_comments', 's9y_comments', 'id', $assoc['comments'], 'entry_id', $assoc['entries']); 
migrate('serendipity_category', 's9y_category', 'categoryid', $assoc['category']); 

// Special case, because both ID values need to be fixed 
migrate('serendipity_entrycat', 's9y_entrycat', 'entryid', $assoc['entrycat'], 'entryid', $assoc['entries'], 'categoryid', $assoc['categoryid']); 

- uploaded the migrate.php to the root (i install s9y at the root)
- execute the migrate.php by typing http://xxx.xxx/migrate.php

For the 2nd time, i just uploaded the nopaste_13382.php to the root (without changing the code), run it then got the 2nd error.

[Rei]

errr.. no closing of php?

Code: Select all

?>

[garvinhicking]

Hi!

No, closing ?> is irrelevant.

Check this version: http://nopaste.php-quake.net/13392

I added some debug output.

If your naming of the tables is reverted, you MUST replace the php code and replace "serendipity_" with "s9y_" and "s9y_" with "serendipity_", as you did in your example.

It might only be that one of the tables was empty when importing. Did you make sure you imported your SQL dump properly?

Regards,
Garvin

[Rei]

If your naming of the tables is reverted, you MUST replace the php code and replace "serendipity_" with "s9y_" and "s9y_" with "serendipity_", as you did in your example.

I made the necessary changes.

It might only be that one of the tables was empty when importing. Did you make sure you imported your SQL dump properly?

I am pretty sure the tables are not empty.

serendipity_category [Showing rows 0 - 8 (9 total, Query took 0.0005 sec)]
serendipity_comments [Showing rows 0 - 29 (172 total, Query took 0.0008 sec)]
serendipity_entries [Showing rows 0 - 29 (319 total, Query took 0.0010 sec)]
serendipity_entrycat [Showing rows 0 - 29 (326 total, Query took 0.0005 sec)]
serendipity_entryproperties [Showing rows 0 - 29 (472 total, Query took 0.0006 sec)]

I run the php file at root. What i got is:

SELECT * from serendipity_entries
INSERTED entry into s9y_entries (3 / 3 / 3)
NEW ID: 0
[and a lot of similar entries ]
Done importing table serendipity_entries
SELECT * from serendipity_entryproperties
INSERTED entry into s9y_entryproperties (19 / / 19)
NEW ID: 0
[and a lot of similar entries ]
Done importing table serendipity_entryproperties
SELECT * from serendipity_comments
INSERTED entry into s9y_comments (166 / / 166)
NEW ID: 0
[and a lot of similar entries ]
Done importing table serendipity_comments
SELECT * from serendipity_category
INSERTED entry into s9y_category (7 / 7 / 7)
NEW ID: 0
[and a lot of similar entries ]
Done importing table serendipity_category
SELECT * from serendipity_entrycat
INSERTED entry into s9y_entrycat (3 / / )
NEW ID: 0
[and a lot of similar entries ]
Done importing table serendipity_entrycat

But i found no single data being imported to the table s9y_entries, s9y_entryproperties, s9y_comments, s9y_category and s9y_entrycat.

[garvinhicking]

Hi!

it seems as if the INSERT SQL Statement fails. I made some modifications to that INSERT statement:

http://nopaste.php-quake.net/13397

When you execute it, the "NEW ID: 0" should never occur, there should always be an ID next to it...

Regards,
Garvin

[Rei]

New error.

Code: Select all

SELECT * from serendipity_entries
INSERT INTO s9y_entries (`0`, `1`, `title`, `2`, `timestamp`, `3`, `body`, `4`, `comments`, `5`, `trackbacks`, `6`, `extended`, `7`, `exflag`, `8`, `author`, `9`, `authorid`, `10`, `isdraft`, `11`, `allow_comments`, `12`, `last_modified`, `13`, `moderate_comments`) VALUES ('3', 'Facts', 'Facts', '891619200', '891619200', '', '', '0', '0', '0', '0', '', '', '0', '0', 'Admin', 'Admin', '1', '1', 'false', 'false', 'true', 'true', '891619200', '891619200', 'false', 'false')
/ Unknown column '0' in 'field list'
INSERTED entry into s9y_entries (3 / 3 / 3)
NEW ID: 0

The NEW ID still 0, and now add on the line - "Unknown column '0' in 'field list'.

Edited to add:
I will come back to this abt 5 hours later. It is 2:16am at my area, gotta Zzzz.
Thank you for your help!