Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,877
Website

transfer over 1600 articles

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?


Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Online

#2 2018-11-08 16:17:51

jakob
Moderator
From: Germany
Registered: 2005-01-20
Posts: 3,138
Website

Re: transfer over 1600 articles

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.

  1. First, make a backup of both databases.
  2. 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).
  3. 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.
  4. 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.
  5. 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).
  6. 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).
  7. Now copy all the lines from the “old” installation and paste them directly at the end of the “new” installation.
  8. At the end of the very last entry, make sure it ends with a ; (semicolon).
  9. Save the file.
  10. 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


TXP Builders – finely-crafted code, design and txp

Offline

#3 2018-11-08 16:22:03

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,148

Re: transfer over 1600 articles

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


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Online

#4 2018-11-08 16:26:54

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,877
Website

Re: transfer over 1600 articles

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!!!


Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Online

#5 2018-11-08 16:30:04

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,148

Re: transfer over 1600 articles

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.


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Online

#6 2018-11-08 16:33:40

jakob
Moderator
From: Germany
Registered: 2005-01-20
Posts: 3,138
Website

Re: transfer over 1600 articles

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


TXP Builders – finely-crafted code, design and txp

Offline

#7 2018-11-08 16:40:50

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,877
Website

Re: transfer over 1600 articles

Uli, that will also definitely help too!


Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Online

#8 2018-11-08 19:48:23

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,877
Website

Re: transfer over 1600 articles

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)


Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Online

#9 2018-11-08 20:16:00

etc
Developer
Registered: 2010-11-11
Posts: 2,936
Website

Re: transfer over 1600 articles

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


etc_[ query | search | pagination | date | tree | cache ]

Offline

#10 2018-11-09 05:14:17

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,877
Website

Re: transfer over 1600 articles

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.


Yiannis
——————————
neme.org | hblack.net | LABS | State Machines | NeMe @ github

Online

Board footer

Powered by FluxBB