transfer over 1600 articles

colak · 2018-11-08 15:46:10

I’m trying to think of a good way to transfer over 1600 articles from an old install to a live site which already has about 200 of them, many with the same ids as the old install.

The problem here is the time stamps which I would like them to remain as they are.

ie, the only things I would like to transfer are the titles, body, excerpts, url_only titles, and time stamps.

Is there a way or do I have to copy/paste each one separately?

jakob · 2018-11-08 16:17:51

I’d probably do that manually using dumps of the respective database but there are a few provisos:

Your site needs to have the sections, categories, etc. that you have in your new site, or you may need to be willing to change them.
Your two sites should have the same version of Textpattern – more precisely, the same database structure.
You haven’t been explicitly linking to these articles by their id numbers, so it’s safe for them to change.

If that sounds feasible, it could be quite quick.

First, make a backup of both databases.
Now go into each database in turn (with Sequel Pro or phpMyAdmin) and export just the “Textpattern” table from each as an .sql file (not compressed).
Open these in separate windows of a text editor. At the top, you should see some instructions on how the database table should be structured starting CREATE TABLE `textpattern`…. In your “old” installation only, delete those lines so you see INSERT INTO `textpattern`… VALUES and then a line for each of the articles. There’ll be a long block of these right down to the last article.
Now look at the database dump of the new installation and look up what the last ID number of the articles is. For example, it might be 567.
Go back to the database dump of the “old” installation and we’ll need to manually advance the ID numbers of the new articles so they don’t overlap. Probably the easiest way is to make them start from 1000 (if you have 1600 articles to replace). You’ll need to search and replace the ID numbers (see below).
Once you have the list of articles from the old installation with new non-overlapping id numbers, go back to the dump of the “new” installation. and go to the bottom of the table. There’ll be a ; (semicolon) at the end. Change that to a , (comma).
Now copy all the lines from the “old” installation and paste them directly at the end of the “new” installation.
At the end of the very last entry, make sure it ends with a ; (semicolon).
Save the file.
Now import that Textpattern table back into your new database using phpMysql or Sequel Pro. You should now have all the articles moved over.

If you need to also switch the articles sections, you can also do that with some judicious searching and replacing. Likewise the category names. The trick is to make sure your search and replace values are well-chosen so you don’t inadvertently replace something else.

For the search and replace, set your text editor to do search and replace with “regex”. I’m only semi-skilled in this, so I’d do this in a few successive goes starting with the thousander-IDs, then working backwards digit by digit.

So, begin with

search for: ,\n\(1(\d{3}),
replace with: ,\n\(2$1,

That makes 1xxx to 2xxx.

Then:

search for: ,\n\((\d{3}),
replace with: ,\n\(1$1,

That makes 100-999 to 1100-1999

Then:

search for: ,\n\((\d{2}),
replace with: ,\n\(10$1,

That makes 10-99 to 1010-1099

And finally:

search for: ,\n\((\d{1}),
replace with: ,\n\(100$1,

That makes 1-9 to 1001-1009.

The syntax is
,
\n = newline
\( = bracket
\d = number (just one)
\d{3} = 3 numbers
(\d{3}) = (3 numbers) <- brackets (not preceded by backslash) means store this value for reinsertion, in this case as $1 in the replace string

uli · 2018-11-08 16:22:03

colak wrote #315091:

many with the same ids as the old install.

Hi Yiannis, could my SQL-tip here be a compatible tool for you?

Edit: linkfix

colak · 2018-11-08 16:26:54

This sounds cool and kind of doable.

The old site is on a modified 4.4.1 install but I can export it, install, upgrade, and clean it up before I try your steps in a dev environment.

It is indeed a good idea!!!

uli · 2018-11-08 16:30:04

colak wrote #315095:

before I try your steps in a dev environment.

Yes, stress on “try” and “dev” ;) I did that only once and on a MAMP instance.

jakob · 2018-11-08 16:33:40

Ah, uli, that’s a good one that could be way easier than the manual method.

colak · 2018-11-08 16:40:50

Uli, that will also definitely help too!

colak · 2018-11-08 19:48:23

I’ve started my tests.

1st step, I migrated a copy of the old site in a new server and updated it to te latest txp version without a hitch. The site has a lot of plugins and I updated some of the crucial ones.

2nd step I went to phpMyAdmin and tried UPDATE textpattern SET ID = ID + 200

which returned the following errors

Notice in ./vendor/phpmyadmin/sql-parser/src/Utils/BufferedQuery.php#366
Uninitialized string offset: 0

Backtrace

./libraries/plugins/import/ImportSql.php#129: PhpMyAdmin\SqlParser\Utils\BufferedQuery->extract()
./import.php#535: PMA\libraries\plugins\import\ImportSql->doImport(array)
Notice in ./vendor/phpmyadmin/sql-parser/src/Utils/BufferedQuery.php#366
Uninitialized string offset: 0

Backtrace

./libraries/plugins/import/ImportSql.php#162: PhpMyAdmin\SqlParser\Utils\BufferedQuery->extract(boolean true)
./import.php#535: PMA\libraries\plugins\import\ImportSql->doImport(array)
p. Any idea of what I am doing wrong?

./libraries/plugins/import/ImportSql.php#162: PhpMyAdmin\SqlParser\Utils\BufferedQuery->extract(boolean true)
./import.php#535: PMA\libraries\plugins\import\ImportSql->doImport(array)

Any idea of what I am doing wrong?

>Edited to add that I’ll be afk most of the day tomorrow so apologies in advance if I do not respond on time.

Last edited by colak (2018-11-08 19:49:45)

etc · 2018-11-08 20:16:00

Could it be that ID + 200 is not enough, since you have >1600 articles?

colak · 2018-11-09 05:14:17

etc wrote #315105:

Could it be that ID + 200 is not enough, since you have >1600 articles?

I am moving the 1600 on top of the database which has 200… but I see what you mean here. I should try to do it the other way. ie change the ids of the 200 articles database by adding 1600.

colak · 2018-11-13 08:04:22

Just to check in that I made some progress regarding this issue but I have hit a wall regarding a particular issue.

the old install uses the id/title schema and the new one uses the section/title one. The problem lies that there might be some duplicate url-only titles in the old install, not to mention that there might be some more duplicates once the two dbs merge.

The question: Is there a way to search for duplicate url-only titles?

jakob · 2018-11-13 10:53:59

This is another manual suggestion if you want to do this before joining your databases:

Use txp:tags to output your list of article url titles and id numbers for each site in CSV format, e.g.:

<txp:article_custom section="news" limit="99999" break="br">
sitename, <txp:article_id />, <txp:article_url_title />
</txp:article_custom>

Then copy what you see and paste into a text file.

Do the same for your other site – changing the site name – and copy those lines onto the end of the same text file. Save that file with all entries from both databases as a .csv file.

Now import that file into excel or numbers and follow one of the “find/highlight duplicate” tutorials online. There are plenty online, here’s one.

If you want to do that after merging your databases, then you’ll find some tips on how to do that using MySQL queries online, e.g. here or here on stackoverflow.

Alternatively, you can sort by column with phpMyAdmin or Sequel Pro and manually scan for duplicate line entries.

Textpattern CMS

Textpattern CMS support forum

#1 2018-11-08 15:46:10